| 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: | |
| 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 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 |
+-------------------------+---------------------+
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