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)) |
|
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
Attachments
Issue Links
Activity
Debugging, I find the difference to be in the first
sub_select/evaluate_join_record call:
(gdb) p dbug_print_item(select_cond)
$107 = 0x14b53a0 "((`j11`.`t2`.`i2` = NULL) and (`j11`.`t2`.`a` < `j11`.`t2`.`b`))"
(gdb) p dbug_print_item(select_cond)
$16 = 0x14b43a0 "(`v2`.`a` < `v2`.`b`)"
When the VIEW is used, "t2.i2 = NULL" is not present.
I am looking at the trees from:
psergey@askmonty.org-20130826173804-fl2mdyqvottvjvr7 (rev 3863, latest
5.5-test2)
psergey@askmonty.org-20130731093701-10tmxhe668f3u1lx (revno 3842, before any
outer join fixes)
and observe the result difference with inner join:
new:
MariaDB [j11]> SELECT * FROM t1 JOIN v2 ON i1 = i2 WHERE a < b;
------------------+
| i1 | i2 | a | b |
------------------+
| NULL | NULL | 2 | 3 |
| NULL | NULL | 1 | 2 |
------------------+
2 rows in set (0.01 sec)
old:
MariaDB [j12]> SELECT * FROM t1 JOIN v2 ON i1 = i2 WHERE a < b;
Empty set (0.00 sec)
The problem in MDEV-4959 was fixed. I've merged the fix and pushed to 5.5-test1 tree. I need another round of testing.
The EXPLAINs are:
MariaDB [j11]> explain extended SELECT * FROM t1 LEFT JOIN t2 ON i1 = i2 WHERE a < b;
-----
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
--------------------------------------------------------------------------Message: select NULL AS i1,t2.i2 AS i2,t2.a AS a,t2.b AS b from t2 where ((t2.i2 = NULL) and (t2.a < t2.b))
MariaDB [j12]> explain extended SELECT * FROM t1 LEFT JOIN v2 ON i1 = i2 WHERE a < b;
-----
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
--------------------------------------------------------------------------------3 rows in set, 1 warning (0.01 sec)
Message: select NULL AS i1,v2.i2 AS i2,v2.a AS a,v2.b AS b from v2 where ((v2.i2 = NULL) and (v2.a < v2.b))
EXPLAIN EXTENDED line is the same with/without the VIEW.