[Commits] Rev 2930: Merge in file:///home/igor/maria/maria-5.1/

igor at askmonty.org igor at askmonty.org
Sun Sep 12 23:23:16 EEST 2010


At file:///home/igor/maria/maria-5.1/

------------------------------------------------------------
revno: 2930 [merge]
revision-id: igor at askmonty.org-20100912202316-jc4086t2swf4jomp
parent: monty at askmonty.org-20100910202726-yic84gpzydgijhqa
parent: igor at askmonty.org-20100912200131-ywgcofz3kv8rsxvb
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.1
timestamp: Sun 2010-09-12 13:23:16 -0700
message:
  Merge
modified:
  mysql-test/r/join_nested.result sp1f-join_nested.result-20040611052448-f6budidy5a6ethsb7f4oabx4zzvnazuv
  mysql-test/t/join_nested.test  sp1f-join_nested.test-20040611052637-z3yzmier3nrbd4eqdftnu6vfz43qinu5
  sql/sql_select.cc              sp1f-sql_select.cc-19700101030959-egb7whpkh76zzvikycs5nsnuviu4fdlb
-------------- next part --------------
=== modified file 'mysql-test/r/join_nested.result'
--- a/mysql-test/r/join_nested.result	2007-07-13 14:36:10 +0000
+++ b/mysql-test/r/join_nested.result	2010-09-10 04:16:01 +0000
@@ -1743,4 +1743,62 @@
 COUNT(*)
 6
 DROP TABLE t1,t2,t3,t4,t5;
+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;
+pk	a	pk	a	pk	a
+1	2	4	2	2	2
+1	2	4	2	4	2
+1	2	5	2	2	2
+1	2	5	2	4	2
+2	7	NULL	NULL	NULL	NULL
+3	5	NULL	NULL	NULL	NULL
+4	7	NULL	NULL	NULL	NULL
+5	5	NULL	NULL	NULL	NULL
+6	NULL	NULL	NULL	NULL	NULL
+7	NULL	NULL	NULL	NULL	NULL
+8	9	NULL	NULL	NULL	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 t2.pk IS NULL;
+pk	a	pk	a	pk	a
+2	7	NULL	NULL	NULL	NULL
+3	5	NULL	NULL	NULL	NULL
+4	7	NULL	NULL	NULL	NULL
+5	5	NULL	NULL	NULL	NULL
+6	NULL	NULL	NULL	NULL	NULL
+7	NULL	NULL	NULL	NULL	NULL
+8	9	NULL	NULL	NULL	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;
+pk	a	pk	a	pk	a
+2	7	NULL	NULL	NULL	NULL
+3	5	NULL	NULL	NULL	NULL
+4	7	NULL	NULL	NULL	NULL
+5	5	NULL	NULL	NULL	NULL
+6	NULL	NULL	NULL	NULL	NULL
+7	NULL	NULL	NULL	NULL	NULL
+8	9	NULL	NULL	NULL	NULL
+DROP TABLE t1, t2, t3;
 End of 5.0 tests

=== modified file 'mysql-test/t/join_nested.test'
--- a/mysql-test/t/join_nested.test	2007-07-12 01:45:35 +0000
+++ b/mysql-test/t/join_nested.test	2010-09-10 04:16:01 +0000
@@ -1196,5 +1196,45 @@
 
 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
 

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-09-03 16:20:30 +0000
+++ b/sql/sql_select.cc	2010-09-10 04:16:01 +0000
@@ -11738,6 +11738,7 @@
       condition is true => a match is found.
     */
     bool found= 1;
+    bool use_not_exists_opt= 0;
     while (join_tab->first_unmatched && found)
     {
       /*
@@ -11754,7 +11755,7 @@
       for (JOIN_TAB *tab= first_unmatched; tab <= join_tab; tab++)
       {
         if (tab->table->reginfo.not_exists_optimize)
-          return NESTED_LOOP_NO_MORE_ROWS;
+          use_not_exists_opt= 1;
         /* Check all predicates that has just been activated. */
         /*
           Actually all predicates non-guarded by first_unmatched->found
@@ -11787,6 +11788,9 @@
       join_tab->first_unmatched= first_unmatched;
     }
 
+    if (use_not_exists_opt)
+      return NESTED_LOOP_NO_MORE_ROWS;
+
     /*
       It was not just a return to lower loop level when one
       of the newly activated predicates is evaluated as false



More information about the commits mailing list