| Bug #72711 | HOUR() of a DATETIME with date zero occasionally (unpredictably) returns NULL | ||
|---|---|---|---|
| Submitted: | 21 May 2014 18:33 | Modified: | 22 May 2014 8:32 |
| Reporter: | Arthur O'Dwyer | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.5.31, 5.5.38 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[22 May 2014 8:32]
MySQL Verification Team
Hello Arthur, Thank you for the bug report and test case. Verified as described. Thanks, Umesh
[22 May 2014 8:33]
MySQL Verification Team
// 5.5.38 - affected
mysql> drop database if exists d;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create database d;
Query OK, 1 row affected (0.00 sec)
mysql> use d;
Database changed
mysql> set sql_mode='ansi';
Query OK, 0 rows affected (0.00 sec)
mysql> create table t (dt datetime);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t values ('0000-00-00 12:34:56');
Query OK, 1 row affected (0.00 sec)
mysql> select dt, hour(dt) from t;
+---------------------+----------+
| dt | hour(dt) |
+---------------------+----------+
| 0000-00-00 12:34:56 | NULL |
+---------------------+----------+
1 row in set (0.00 sec)
mysql> select dt, hour(dt) from (select cast('0000-00-00 12:34:56' as datetime) dt) sub;
+---------------------+----------+
| dt | hour(dt) |
+---------------------+----------+
| 0000-00-00 12:34:56 | NULL |
+---------------------+----------+
1 row in set (0.00 sec)
mysql> select cast('0000-00-00 12:34:56' as datetime) `dt`, hour(cast('0000-00-00 12:34:56' as datetime)) `hour(dt)` from dual;
+---------------------+----------+
| dt | hour(dt) |
+---------------------+----------+
| 0000-00-00 12:34:56 | 12 |
+---------------------+----------+
1 row in set (0.00 sec)
mysql> select version();
+------------------+
| version() |
+------------------+
| 5.5.38-debug-log |
+------------------+
1 row in set (0.00 sec)
// 5.6 - not affected
mysql> drop database if exists d;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create database d;
Query OK, 1 row affected (0.00 sec)
mysql> use d;
Database changed
mysql> set sql_mode='ansi';
Query OK, 0 rows affected (0.00 sec)
mysql> create table t (dt datetime);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t values ('0000-00-00 12:34:56');
Query OK, 1 row affected (0.01 sec)
mysql> select dt, hour(dt) from t;
+---------------------+----------+
| dt | hour(dt) |
+---------------------+----------+
| 0000-00-00 12:34:56 | 12 |
+---------------------+----------+
1 row in set (0.00 sec)
mysql> select dt, hour(dt) from (select cast('0000-00-00 12:34:56' as datetime) dt) sub;
+---------------------+----------+
| dt | hour(dt) |
+---------------------+----------+
| 0000-00-00 12:34:56 | 12 |
+---------------------+----------+
1 row in set (0.00 sec)
mysql> select cast('0000-00-00 12:34:56' as datetime) `dt`, hour(cast('0000-00-00 12:34:56' as datetime)) `hour(dt)` from dual;
+---------------------+----------+
| dt | hour(dt) |
+---------------------+----------+
| 0000-00-00 12:34:56 | 12 |
+---------------------+----------+
1 row in set (0.00 sec)
mysql>
mysql> select version();
+-------------------------------------------+
| version() |
+-------------------------------------------+
| 5.6.17-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)
// 5.7.5 - Not affected
mysql> drop database if exists d;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create database d;
Query OK, 1 row affected (0.00 sec)
mysql> use d;
Database changed
mysql> set sql_mode='ansi';
Query OK, 0 rows affected (0.00 sec)
mysql> create table t (dt datetime);
Query OK, 0 rows affected (0.06 sec)
mysql> insert into t values ('0000-00-00 12:34:56');
Query OK, 1 row affected (0.00 sec)
mysql> select dt, hour(dt) from t;
+---------------------+----------+
| dt | hour(dt) |
+---------------------+----------+
| 0000-00-00 12:34:56 | 12 |
+---------------------+----------+
1 row in set (0.00 sec)
mysql> select dt, hour(dt) from (select cast('0000-00-00 12:34:56' as datetime) dt) sub;
+---------------------+----------+
| dt | hour(dt) |
+---------------------+----------+
| 0000-00-00 12:34:56 | 12 |
+---------------------+----------+
1 row in set (0.00 sec)
mysql> select cast('0000-00-00 12:34:56' as datetime) `dt`, hour(cast('0000-00-00 12:34:56' as datetime)) `hour(dt)` from dual;
+---------------------+----------+
| dt | hour(dt) |
+---------------------+----------+
| 0000-00-00 12:34:56 | 12 |
+---------------------+----------+
1 row in set (0.00 sec)
mysql> select version();
+----------------------------------------------+
| version() |
+----------------------------------------------+
| 5.7.5-m15-enterprise-commercial-advanced-log |
+----------------------------------------------+
1 row in set (0.00 sec)
Description: Selecting HOUR(dt) where dt is a datetime column with a value of the form "0000-00-00 HH:MM:SS" for any HH:MM:SS will *sometimes* return HH, and *sometimes* return NULL, depending on... well, I'm not sure what it depends on, exactly. Most noticeably, the results differ depending on whether the DATETIME value came from a DATETIME column or from an expression such as CAST(... AS DATETIME). How to repeat: cat > test.sql <<EOF drop database if exists d; create database d; use d; set sql_mode='ansi'; create table t (dt datetime); insert into t values ('0000-00-00 12:34:56'); select dt, hour(dt) from t; select dt, hour(dt) from (select cast('0000-00-00 12:34:56' as datetime) dt) sub; select cast('0000-00-00 12:34:56' as datetime) `dt`, hour(cast('0000-00-00 12:34:56' as datetime)) `hour(dt)` from dual; EOF mysql < test.sql dt hour(dt) 0000-00-00 12:34:56 NULL dt hour(dt) 0000-00-00 12:34:56 NULL dt hour(dt) 0000-00-00 12:34:56 12 Suggested fix: The most user-friendly behavior here would be to return HH all the time. That is, HOUR(NULL) => NULL HOUR('YYYY-MM-DD HH:mm:ss') => HH, regardless of the value of YYYY-MM