Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
None
Description
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:
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:
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)) |
|
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|