Details
Major
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)) | +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
The EXPLAINs are:
MariaDB [j11]> explain extended SELECT * FROM t1 LEFT JOIN t2 ON i1 = i2 WHERE a < b;
-------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-------------------------------------------------------------------------------
| 1 | SIMPLE | t1 | system | NULL | NULL | NULL | NULL | 1 | 100.00 | |
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where |
-------------------------------------------------------------------------------
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;
-------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-------------------------------------------------------------------------------------
| 1 | PRIMARY | t1 | system | NULL | NULL | NULL | NULL | 1 | 100.00 | |
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 5 | const | 0 | 0.00 | Using where |
| 2 | DERIVED | t2 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 |
-------------------------------------------------------------------------------------
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.
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)
Branching off this problem to MDEV-4959
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.
5.5.32 is not affected, the regression was introduced later (I mistakenly put 5.5.32 as a fix version earlier, thanks for correcting it).