Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

It seems that 'ifnull' changes the default collation that is set to be used.

I have tried on both mysql and mariadb:

Server version: 5.5.34-MariaDB-1~precise-log mariadb.org binary distribution

These are my database settings:

    show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

show variables like 'collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+

Here is my table definition:

show create table FileDownloads;
| FileDownloads | CREATE TABLE `FileDownloads` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `licenseIdentifierID` int(10) unsigned NOT NULL,
  `ip` varchar(255) DEFAULT NULL,
  `file` varchar(255) NOT NULL,
  `timestamp` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `licenseIdentifierID` (`licenseIdentifierID`),
  CONSTRAINT `FileDownloads_ibfk_1` FOREIGN KEY (`licenseIdentifierID`) REFERENCES `LicenseIdentifiers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3576 DEFAULT CHARSET=utf8 |

show full columns from FileDownloads;
+---------------------+------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| Field               | Type             | Collation       | Null | Key | Default | Extra          | Privileges                      | Comment |
+---------------------+------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| id                  | int(10) unsigned | NULL            | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| licenseIdentifierID | int(10) unsigned | NULL            | NO   | MUL | NULL    |                | select,insert,update,references |         |
| ip                  | varchar(255)     | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references |         |
| file                | varchar(255)     | utf8_general_ci | NO   |     | NULL    |                | select,insert,update,references |         |
| timestamp           | datetime         | NULL            | YES  |     | NULL    |                | select,insert,update,references |         |
+---------------------+------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
show table status \G
*************************** 7. row ***************************
           Name: FileDownloads
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 3724
 Avg_row_length: 109
    Data_length: 409600
Max_data_length: 0
   Index_length: 98304
      Data_free: 11534336
 Auto_increment: 3576
    Create_time: 2013-12-24 13:31:38
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:

The following statement triggers a collation error:

SELECT id, apacheIp, apacheFile, apacheTimestamp
    -> FROM ApacheLog JOIN LicenseIdentifiers ON apacheUser = licenseID
    -> WHERE apacheTimestamp > (SELECT IFNULL(MAX(timestamp), 0) FROM FileDownloads);
ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,NUMERIC) for operation '>'

I've managed to narrow the issue a little bit. This is ok:

SELECT IFNULL(MAX(timestamp), 0) FROM FileDownloads;                                                                                                                              +---------------------------+
| IFNULL(MAX(timestamp), 0) |
+---------------------------+
| 2013-12-23 14:25:10       |
+---------------------------+

, but this is not ok and I don't understand why:

SELECT IFNULL(MAX(timestamp), 0) collate utf8_general_ci FROM FileDownloads;
ERROR 1253 (42000): COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'

Later..

Actually all functions that return a string (in my case ifnull and max) change the collation. Here is an interesting output:

SELECT collation(MAX(timestamp)) FROM FileDownloads;                                                                                     +---------------------------+
| collation(MAX(timestamp)) |
+---------------------------+
| latin1_swedish_ci         |
+---------------------------+
share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.