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:
None 
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 18:17] Arthur O'Dwyer
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);
[16 May 2014 20:06] Shane Bester
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] Umesh Shastry
Hello Arthur,

Thank you for the bug report and test case.
Verified as described.

Thanks,
Umesh