| Bug #77805 | NULLIF cause wrong value with cast datetime | ||
|---|---|---|---|
| Submitted: | 23 Jul 2015 2:01 | Modified: | 23 Jul 2015 4:58 |
| Reporter: | James Fan | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S1 (Critical) |
| Version: | 5.6.25 MySQL Community Server (GPL) | OS: | Ubuntu (Ubuntu 14.04.2 LTS x86_64) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | cast, datetime, NULLIF | ||
[23 Jul 2015 4:58]
MySQL Verification Team
Hello James, Thank you for the report. Thanks, Umesh
[23 Jul 2015 5:01]
MySQL Verification Team
// 5.1.76
[umshastr@hod03]/export/umesh/server/binaries/mysql-5.1.76: bin/mysql -uroot -S /tmp/mysql_ushastry.sock test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.76 Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select CAST(NULLIF('2015-07-04 00:00:00','') AS DATETIME );
+-----------------------------------------------------+
| CAST(NULLIF('2015-07-04 00:00:00','') AS DATETIME ) |
+-----------------------------------------------------+
| 2015-07-04 00:00:00 |
+-----------------------------------------------------+
1 row in set (0.02 sec)
mysql> CREATE TABLE c_test (col DATETIME);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into c_test values('2015-07-04 00:00:00');
Query OK, 1 row affected (0.00 sec)
mysql> select * from c_test;
+---------------------+
| col |
+---------------------+
| 2015-07-04 00:00:00 |
+---------------------+
1 row in set (0.00 sec)
mysql> select CAST(NULLIF(col,'') AS DATETIME ) from c_test;
+-----------------------------------+
| CAST(NULLIF(col,'') AS DATETIME ) |
+-----------------------------------+
| 2015-07-04 00:00:00 |
+-----------------------------------+
1 row in set, 1 warning (0.00 sec)
// 5.5.46
[umshastr@hod03]/export/umesh/server/binaries/mysql-5.5.46: bin/mysql -uroot -S /tmp/mysql_ushastry.sock test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.46 MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select CAST(NULLIF('2015-07-04 00:00:00','') AS DATETIME );
+-----------------------------------------------------+
| CAST(NULLIF('2015-07-04 00:00:00','') AS DATETIME ) |
+-----------------------------------------------------+
| 2015-07-04 00:00:00 |
+-----------------------------------------------------+
1 row in set (0.00 sec)
// 5.6.25
[root@cluster-repo ~]# mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.25-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select CAST(NULLIF('2015-07-04 00:00:00','') AS DATETIME );
+-----------------------------------------------------+
| CAST(NULLIF('2015-07-04 00:00:00','') AS DATETIME ) |
+-----------------------------------------------------+
| NULL |
+-----------------------------------------------------+
1 row in set, 2 warnings (0.00 sec)
mysql> CREATE TABLE c_test (col DATETIME);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into c_test values('2015-07-04 00:00:00');
Query OK, 1 row affected (0.01 sec)
mysql> select * from c_test;
+---------------------+
| col |
+---------------------+
| 2015-07-04 00:00:00 |
+---------------------+
1 row in set (0.01 sec)
mysql> select CAST(NULLIF(col,'') AS DATETIME ) from c_test;
+-----------------------------------+
| CAST(NULLIF(col,'') AS DATETIME ) |
+-----------------------------------+
| 2015-07-04 00:00:00 |
+-----------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------+
| Warning | 1292 | Incorrect datetime value: '' for column 'col' at row 1 |
+---------+------+--------------------------------------------------------+
1 row in set (0.00 sec)
Quoting from manual "Prior to MySQL 5.6.4, when using an explicit CAST() on a TIMESTAMP value in a statement that does not select from any tables, the value is treated by MySQL 5.6 as a string prior to performing any conversion. This results in the value being truncated when casting to a numeric type" - https://dev.mysql.com/doc/refman/5.6/en/cast-functions.html#function_convert
I'm not sure whether this applies to DATE/DATETIME types too.
Description: Expression like below will get NULL value: CAST(NULLIF('2015-07-04 00:00:00','') AS DATETIME ) How to repeat: -- run sql below : select CAST(NULLIF('2015-07-04 00:00:00','') AS DATETIME ); -- will get NULL: +-----------------------------------------------------+ | CAST(NULLIF('2015-07-04 00:00:00','') AS DATETIME ) | +-----------------------------------------------------+ | NULL | +-----------------------------------------------------+