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 |
[10 Feb 2014 16:08]
Kenny Gryp
[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.