| Bug #71658 | Comparison of DATE_ADD(null...) with NOW() Fails in 5.6 | ||
|---|---|---|---|
| Submitted: | 10 Feb 2014 16:08 | Modified: | 18 Jan 2018 13:20 |
| Reporter: | Kenny Gryp | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Charsets | Severity: | S3 (Non-critical) |
| Version: | 5.6.15, 5.6.16, 5.5.36, 5.7.14 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[11 Feb 2014 12:01]
MySQL Verification Team
Hello Kenny, Thank you for the bug report. Verified as described. Thanks, Umesh
[11 Feb 2014 12:03]
MySQL Verification Team
// 5.5.36 mysql> select version(); +------------+ | version() | +------------+ | 5.5.36-log | +------------+ 1 row in set (0.00 sec) mysql> set @datetime=null; Query OK, 0 rows affected (0.00 sec) mysql> select date_add(@datetime, interval 1 day) > now(); ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,NUMERIC) for operation '>' mysql>
[23 Sep 2016 8:42]
Kenny Gryp
This still exists on 5.7.14: mysql> 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 | +--------------------------+-------------------------------------+ 8 rows in set (0.00 sec) mysql> set @datetime=null; Query OK, 0 rows affected (0.00 sec) mysql> select date_add(@datetime, interval 1 day) > now(); ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,NUMERIC) for operation '>'
[23 Sep 2016 8:43]
Kenny Gryp
However, if you change the client/connection charset to latin1, it works: Additionally, I also found out that in latin1, this works as expected: perconaserver mysql> set names latin1; Query OK, 0 rows affected (0.00 sec) mysql> select date_add(@datetime, interval 1 day) > now(); +---------------------------------------------+ | date_add(@datetime, interval 1 day) > now() | +---------------------------------------------+ | NULL | +---------------------------------------------+ 1 row in set (0.00 sec)
[18 Jan 2018 13:20]
Erlend Dahl
Fixed in 8.0.2 DMR.
Description: I am testing an upgrade to MySQL 5.6 (Currently on 5.1). Queries fail when trying to compare date_add(null, interal 1 day) with NOW(). This does work in MySQL 5.1.73 How to repeat: mysql> 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/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec) mysql> set @datetime=null; Query OK, 0 rows affected (0.00 sec) mysql> select date_add(@datetime, interval 1 day) > now(); ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,NUMERIC) for operation '>' mysql> set @datetime="2014-01-01"; Query OK, 0 rows affected (0.00 sec) mysql> select date_add(@datetime, interval 1 day) > now(); +---------------------------------------------+ | date_add(@datetime, interval 1 day) > now() | +---------------------------------------------+ | 0 | +---------------------------------------------+ 1 row in set (0.00 sec) mysql> set @datetime="2015-01-01"; Query OK, 0 rows affected (0.00 sec) mysql> select date_add(@datetime, interval 1 day) > now(); +---------------------------------------------+ | date_add(@datetime, interval 1 day) > now() | +---------------------------------------------+ | 1 | +---------------------------------------------+ 1 row in set (0.00 sec) In MySQL 5.1 this expression returns NULL which seems to make more sense than just fail because of character set issues. mysql5.1.73> select date_add(null, interval 1 day) > now(); +----------------------------------------+ | date_add(null, interval 1 day) > now() | +----------------------------------------+ | NULL | +----------------------------------------+ 1 row in set (0.00 sec) Suggested fix: Some workarounds I have found: mysql> select date_add(null, interval 1 day) > cast(now() as char); +------------------------------------------------------+ | date_add(null, interval 1 day) > cast(now() as char) | +------------------------------------------------------+ | NULL | +------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select DATE_ADD(cast(null as datetime), INTERVAL 1 DAY) > NOW() -> ; +---------------------------------------------------------------+ | DATE_ADD(cast(null as datetime), INTERVAL 1 DAY) > NOW() | +---------------------------------------------------------------+ | NULL | +---------------------------------------------------------------+ 1 row in set (0.00 sec)