We're updating the issue view to help you get more done.Learn more

Wrong result on <not null date column> IS NULL (old documented hack stopped working)

Due to the fix for MDEV-4817 Closed , the ancient bugfeature related to DATE/DATETIME and IS NULL stopped working.

Here is the story: http://bugs.mysql.com/bug.php?id=940
Here is the doc: http://dev.mysql.com/doc/refman/5.5/en/comparison-operators.html

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:

SELECT * FROM tbl_name WHERE date_column IS NULL
This is needed to get some ODBC applications to work because ODBC does not support a '0000-00-00' date value.

Here is how it looks:

CREATE TABLE t1 (id INT, d DATE NOT NULL);
INSERT INTO t1 VALUES (1,'0000-00-00'),(2,'0000-00-00');

SELECT * FROM t1 WHERE d IS NULL;
# +------+------------+
# | id   | d          |
# +------+------------+
# |    1 | 0000-00-00 |
# |    2 | 0000-00-00 |
# +------+------------+
# 2 rows in set (0.01 sec)

But it doesn't work any longer for scenarios affected by MDEV-4817 Closed fix:

CREATE TABLE t1 (id INT, d DATE NOT NULL);
INSERT INTO t1 VALUES (1,'0000-00-00'),(2,'0000-00-00');
CREATE TABLE t2 (i INT);
SELECT * FROM t1 LEFT JOIN t2 ON (id=i) WHERE NULL OR d IS NULL;
# Empty set (0.01 sec)
EXPLAIN EXTENDED SELECT * FROM t1 LEFT JOIN t2 ON (id=i) WHERE NULL OR d IS NULL;
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                           |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
|    1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 |                                                 |
|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where; Using join buffer (flat, BNL join) |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | select `test`.`t1`.`id` AS `id`,`test`.`t1`.`d` AS `d`,`test`.`t2`.`i` AS `i` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`id` = `test`.`t2`.`i`) and (`test`.`t1`.`d` = 0)) |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Status

Assignee

Sergei Petrunia

Reporter

Elena Stepanova