From b3cd40e0d2aeafa64a1fa8b81a1d3905c67f4726 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Thu, 9 Sep 2010 21:16:01 -0700 Subject: Fixed bug #49322. When not-exists optimization was applied to a table that happened to be an inner table of two outer joins, one embedded into another, then setting the match flag for the embedding outer join on could be skipped. This caused generation of extra null complemented rows. Made sure that the match flags are set correctly in all cases when not-exists optimization is used. --- mysql-test/t/join_nested.test | 40 ++++++++++++++++++++++++++++++++++++++++ 1 file changed, 40 insertions(+) (limited to 'mysql-test/t/join_nested.test') diff --git a/mysql-test/t/join_nested.test b/mysql-test/t/join_nested.test index 5b07d8966f1..6ae7fb6dfee 100644 --- a/mysql-test/t/join_nested.test +++ b/mysql-test/t/join_nested.test @@ -1196,5 +1196,45 @@ SELECT COUNT(*) DROP TABLE t1,t2,t3,t4,t5; +# +# BUG#49322: Nested left joins + not-exist optimization +# + +CREATE TABLE t1 ( + pk int NOT NULL AUTO_INCREMENT PRIMARY KEY, + a int DEFAULT NULL, + KEY idx(a) +); +CREATE TABLE t2 ( + pk int NOT NULL AUTO_INCREMENT PRIMARY KEY, + a int DEFAULT NULL, + KEY idx(a) +); +CREATE TABLE t3 ( + pk int NOT NULL AUTO_INCREMENT PRIMARY KEY, + a int DEFAULT NULL, + KEY idx(a) +); + +INSERT INTO t1 VALUES + (1,2), (2,7), (3,5), (4,7), (5,5), (6,NULL), (7,NULL), (8,9); +INSERT INTO t2 VALUES + (1,NULL), (4,2), (5,2), (3,4), (2,8); +INSERT INTO t3 VALUES + (1,9), (2,2), (3,5), (4,2), (5,7), (6,0), (7,5); + +SELECT t1.pk, t1.a, t2.pk, t2.a,t3.pk, t3.a + FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t3.a=t2.a) ON t2.a=t1.a; + +SELECT t1.pk, t1.a, t2.pk, t2.a,t3.pk, t3.a + FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t3.a=t2.a) ON t2.a=t1.a + WHERE t2.pk IS NULL; + +SELECT t1.pk, t1.a, t2.pk, t2.a,t3.pk, t3.a + FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t3.a=t2.a) ON t2.a=t1.a + WHERE t3.pk IS NULL; + +DROP TABLE t1, t2, t3; + --echo End of 5.0 tests -- cgit v1.2.1 From c9150472b09e4f15fd322528713d382283050740 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Thu, 14 Oct 2010 11:45:46 -0700 Subject: Turned off the test case for bug 49322 when join_cache_level=6. It should be turned on back when the tree for MWL#128 is merged into the main 5.3 merge. --- mysql-test/t/join_nested.test | 4 ++++ 1 file changed, 4 insertions(+) (limited to 'mysql-test/t/join_nested.test') diff --git a/mysql-test/t/join_nested.test b/mysql-test/t/join_nested.test index 6ae7fb6dfee..166aab99ccd 100644 --- a/mysql-test/t/join_nested.test +++ b/mysql-test/t/join_nested.test @@ -1196,6 +1196,9 @@ SELECT COUNT(*) DROP TABLE t1,t2,t3,t4,t5; +# !!!Remove the following if brackets after having merged the code of MWL#128 +if (`SELECT @@join_cache_level=1`) +{ # # BUG#49322: Nested left joins + not-exist optimization # @@ -1235,6 +1238,7 @@ SELECT t1.pk, t1.a, t2.pk, t2.a,t3.pk, t3.a WHERE t3.pk IS NULL; DROP TABLE t1, t2, t3; +} --echo End of 5.0 tests -- cgit v1.2.1