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 From d6f7649d3c7f2e055a77ef9432c245928675ef4c Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Wed, 28 Aug 2013 21:21:12 +0400 Subject: mdev-4942: Add another testcase after merging with other fixes. --- mysql-test/t/join_outer.test | 13 +++++++++++++ 1 file changed, 13 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 765118d4775..7452e81ca23 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -1706,6 +1706,19 @@ 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 # Another testcase +CREATE TABLE t1 (i1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (NULL); + +CREATE TABLE t2 (i2 INT, a INT, b INT) ENGINE=MyISAM; +CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2; + +INSERT INTO t2 VALUES (NULL,1,2),(NULL,2,3); +SELECT * FROM t1 LEFT JOIN v2 ON i1 = i2 WHERE a < b; +SELECT * FROM t1 LEFT JOIN t2 ON i1 = i2 WHERE a < b; + +drop view v2; +drop table t1,t2; --echo # --echo # Bug mdev-4942: LEFT JOIN with conjunctive -- cgit v1.2.1