From 970542ec90951c3e9d68ff310cdf181465854aaa Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Fri, 23 Aug 2013 16:32:56 +0400 Subject: MDEV-4836: Wrong result on IS NULL (old documented hack stopped working) - When applying optimization introduced by MDEV-4817, ignore the conditions that have form "datetime_not_null_col IS NULL". --- mysql-test/t/join_outer.test | 9 +++++++++ 1 file changed, 9 insertions(+) (limited to 'mysql-test/t/join_outer.test') diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index b0000b2b943..7b26c9670ac 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -1686,5 +1686,14 @@ explain select * from t1 left join t0 on t0.a=t1.pk where t0.person_id='fooo' or drop table t0, t1; +--echo # +--echo # MDEV-4836: Wrong result on IS NULL (old documented hack stopped working) +--echo # (this is a regression after fix for MDEV-4817) +--echo # +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; +DROP TABLE t1,t2; SET optimizer_switch=@save_optimizer_switch; -- cgit v1.2.1 From edd980225a9895f46754d54677bd76327152c78b Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Mon, 26 Aug 2013 21:38:04 +0400 Subject: Fix for MDEV-4836 fix: take into account situation where "notnull_col IS NULL" is not a direct child of the WHERE clause item, but rather is embedded inside Item_cond_and or Item_cond_or. --- mysql-test/t/join_outer.test | 11 +++++++++++ 1 file changed, 11 insertions(+) (limited to 'mysql-test/t/join_outer.test') diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index 0ef1ea593a2..f85feb70ccb 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -1696,6 +1696,17 @@ CREATE TABLE t2 (i INT); SELECT * FROM t1 LEFT JOIN t2 ON (id=i) WHERE NULL OR d IS NULL; DROP TABLE t1,t2; + +CREATE TABLE t1 (i1 INT, d1 DATE NOT NULL); +INSERT INTO t1 VALUES (1,'2012-12-21'),(2,'0000-00-00'); + +CREATE TABLE t2 (i2 INT, j2 INT); +INSERT INTO t2 VALUES (1,10),(2,20); + +SELECT * FROM t1 LEFT JOIN t2 ON i1 = j2 WHERE d1 IS NULL AND 1 OR i1 = i2; +DROP TABLE t1,t2; + + --echo # --echo # Bug mdev-4942: LEFT JOIN with conjunctive --echo # IS NULL in WHERE -- cgit v1.2.1