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 , 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:

1 2 3 4 5 6 7 8 9 10 11 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 fix:

1 2 3 4 5 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)
1 2 3 4 5 6 7 8 9 10 11 12 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

Labels

Fix versions

Priority

Major