| Bug #72672 | Zero-valued DATETIME column can be simultaneously NULL and NOT NULL | ||
|---|---|---|---|
| Submitted: | 16 May 2014 18:17 | Modified: | 22 May 2014 9:17 |
| Reporter: | Arthur O'Dwyer | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S2 (Serious) |
| Version: | 5.5.31,5.5.34, 5.7.5, 5.6.19 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[16 May 2014 20:06]
MySQL Verification Team
http://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_is-null "For DATE and DATETIME columns that are declared as NOT NULL, you can find the special date '0000-00-00' by using a statement like this:"
[22 May 2014 9:17]
MySQL Verification Team
Hello Arthur, Thank you for the bug report and test case. Verified as described. Thanks, Umesh
Description: A column of type `DATETIME NOT NULL` sometimes incorrectly reports that it `IS NULL`. This seems to happen only in a WHERE-clause; it does not happen in the project list, nor in a HAVING-clause. This leads to crazy optimizer bugs, such as in the "How to repeat" section below, where the server reports (a IS NULL) and simultaneously NOT(a IS NULL). How to repeat: DROP DATABASE IF EXISTS db1; CREATE DATABASE db1; USE db1; CREATE TABLE t1 (a DATETIME NOT NULL); INSERT INTO t1 VALUES (0); SELECT a, (a IS NULL), (a IS NOT NULL) FROM t1 WHERE (a IS NULL) AND NOT(a IS NULL); +---------------------+-------------+-----------------+ | a | (a IS NULL) | (a IS NOT NULL) | +---------------------+-------------+-----------------+ | 0000-00-00 00:00:00 | 0 | 1 | +---------------------+-------------+-----------------+ The same bogus results show up with SELECT * FROM t1 WHERE (a IS NULL) AND NOT(a IS NULL); SELECT * FROM t1 WHERE (a IS NULL) AND (a IS NOT NULL);