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
add comment

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.