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 10:16]
Mikhail Goryachkin
[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 | +-------------------------+---------------------+