Bug #67176 DATE functions return incorrect collation if compared with NULL
Submitted: 10 Oct 2012 10:16 Modified: 11 Oct 2012 8:16
Reporter: Mikhail Goryachkin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.5.27 OS:Linux (Linux 2.6.18-164.el5 #1 SMP Thu Sep 3 03:28:30 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux)
Assigned to: CPU Architecture:Any
Tags: date functions, incorrect collations, NULL collation in user functions

[10 Oct 2012 10:16] Mikhail Goryachkin
Description:
mysql> set names utf8 collate utf8_general_ci;

mysql> drop procedure if exists tmp_mike_test_collation;
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter $$
mysql> create procedure tmp_mike_test_collation(i_date datetime)
    ->   begin
    ->     declare v_current_date datetime default now();
    -> 
    ->     if i_date > date_add(v_current_date, interval 10 minute) then
    ->           select 1 ;
    ->     end if;
    -> 
    ->   end
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call tmp_mike_test_collation(now());
Query OK, 0 rows affected (0.00 sec)

mysql> call tmp_mike_test_collation(null);
ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,NUMERIC) for operation '>'

mysql> show variables like '%collat%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

If you are working with JDBC and set collation_connection to utf8_general_ci, you can't pass null values into procedures as a parameter because they will be converted into utf8_general_ci encoding, but collation of date_add function is  latin1_swedish_ci (do not know why, there is no such collations in database at all as you can see). If set collation manually all works.

mysql> select convert(null using utf8) collate utf8_general_ci > date_add(now(), interval 1 second);
ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,EXPLICIT) and (latin1_swedish_ci,NUMERIC) for operation '>'

mysql> select convert(null using latin1) collate latin1_swedish_ci > date_add(now(), interval 1 second) f1;
Field   1:  `f1`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     1
Max_length: 0
Decimals:   0
Flags:      BINARY NUM 

+------+
| f1   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

How to repeat:
See the description

Suggested fix:
Do not use latin1_swedish_ci as a default collation for DATE functions
[10 Oct 2012 12:53] Peter Laursen
I cannot reproduce (no matter how hard I try) on 5.5.28 (Win7/64):

SELECT NOW() > DATE_ADD(NOW(), INTERVAL 10 MINUTE);  -- 0
SELECT NULL > DATE_ADD(NOW(), INTERVAL 10 MINUTE); -- NULL
SELECT NOW() > DATE_ADD(NULL, INTERVAL 10 MINUTE); -- NULL

DELIMITER $$

CREATE PROCEDURE tmp_mike_test_collation(i_date DATETIME)
   BEGIN
   DECLARE v_current_date DATETIME DEFAULT NOW();
    IF i_date > DATE_ADD(v_current_date, INTERVAL 10 MINUTE) THEN
     SELECT 1 ;
   END IF;
   END $$
   
DELIMITER ;

CALL tmp_mike_test_collation(NULL); -- success

SHOW VARIABLES LIKE '%collat%';
/*
Variable_name         Value            
--------------------  -----------------
collation_connection  utf8_general_ci  
collation_database    utf8_general_ci  
collation_server      utf8_general_ci   */

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        C:\Program Files\MySQL\MySQL Server 5.5\share\charsets\  
*/

SET @@global.collation_server = utf8_unicode_ci;

CALL tmp_mike_test_collation(NULL); -- still success

DROP PROCEDURE IF exist tmp_mike_test_collation;

DELIMITER $$

CREATE PROCEDURE tmp_mike_test_collation(i_date DATETIME)
   BEGIN
   DECLARE v_current_date DATETIME DEFAULT NOW();
    IF i_date > DATE_ADD(v_current_date, INTERVAL 10 MINUTE) THEN
     SELECT 1 ;
   END IF;
   END $$
   
DELIMITER ;

CALL tmp_mike_test_collation(NULL); -- still success 

SELECT VERSION(); -- 5.5.28

Not sure if I missed something though!

Peter
(not a MySQL/Oracle person)
[11 Oct 2012 8:16] Mikhail Goryachkin
Yes you are right! 
The problem is fixed in 5.5.28. Thanks for comments.

mysql> set names utf8 collate utf8_general_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%collat%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

mysql> drop procedure if exists tmp_mike_test_collation;
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter $$
mysql> create procedure tmp_mike_test_collation(i_date datetime)
    ->   begin
    ->     declare v_current_date   datetime default now();
    -> 
    ->     if i_date > date_add(v_current_date, interval 10 minute) then
    ->       select 1;
    ->     end if;
    ->   end
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call tmp_mike_test_collation(now());
Query OK, 0 rows affected (0.00 sec)

mysql> call tmp_mike_test_collation(null);
Query OK, 0 rows affected (0.01 sec)

+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| version                 | 5.5.28-log          |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | Linux               |
+-------------------------+---------------------+