List:Commits« Previous MessageNext Message »
From:Ole John Aske Date:November 25 2010 1:59pm
Subject:bzr commit into mysql-5.1 branch (ole.john.aske:3474) Bug#58490
View as plain text  
#At file:///net/fimafeng09/export/home/tmp/oleja/mysql/mysql-5.1/ based on revid:ole.john.aske@stripped

 3474 Ole John Aske	2010-11-25
      Fix for bug#58490, 'Incorrect result in multi level OUTER JOIN in combination with IS NULL'
      
      Qualifying an OUTER JOIN with '<column> IS NULL' where <column> is declared as 'NOT NULL' 
      causes the 'not_exists_optimize' to be enabled.
      
      In evaluate_join_record() the 'not_exists_optimize' caused 'NESTED_LOOP_NO_MORE_ROWS' to be 
      returned immediately when a matching row was found.
      
      This happened before all 'first_unmatched->found' had been properly evaluated for all 
      embedding outer joins. This may cause incorrect calls to 
      evaluate_null_complemented_join_record() after we had returned back to sub_select().
      
      This fix ensures that evaluate_join_record() completes the itteration of the 'first_unmatched-loop',
      evaluates all conditions for the unmatched JOIN_TAB's and set 'first_unmatched->found' properly before
      possibly returning NESTED_LOOP_NO_MORE_ROWS iff 'not_exists_optimize' was in effect.

    modified:
      mysql-test/r/join_outer.result
      mysql-test/t/join_outer.test
      sql/sql_select.cc
=== modified file 'mysql-test/r/join_outer.result'
--- a/mysql-test/r/join_outer.result	2010-06-01 07:54:06 +0000
+++ b/mysql-test/r/join_outer.result	2010-11-25 13:59:10 +0000
@@ -1397,4 +1397,48 @@ id	select_type	table	type	possible_keys	
 Warnings:
 Note	1003	select straight_join `test`.`jt1`.`f1` AS `f1` from `test`.`t1` `jt6` left join (`test`.`t1` `jt3` join `test`.`t1` `jt4` left join `test`.`t1` `jt5` on(1) left join `test`.`t1` `jt2` on(1)) on((`test`.`jt6`.`f1` and 1)) left join `test`.`t1` `jt1` on(1) where 1
 DROP TABLE t1;
+#
+# Bug#58490: Incorrect result in multi level OUTER JOIN 
+# in combination with IS NULL 
+#
+CREATE TABLE t1 (i INT NOT NULL);
+INSERT INTO t1 VALUES (0),    (2),(3),(4);
+CREATE TABLE t2 (i INT NOT NULL);
+INSERT INTO t2 VALUES (0),(1),    (3),(4);
+CREATE TABLE t3 (i INT NOT NULL);
+INSERT INTO t3 VALUES (0),(1),(2),    (4);
+CREATE TABLE t4 (i INT NOT NULL);
+INSERT INTO t4 VALUES (0),(1),(2),(3)   ;
+SELECT * FROM
+t1 LEFT JOIN
+( t2 LEFT JOIN
+( t3 LEFT JOIN 
+t4
+ON t4.i = t3.i
+)
+ON t3.i = t2.i
+)
+ON t2.i = t1.i
+;
+i	i	i	i
+0	0	0	0
+2	NULL	NULL	NULL
+3	3	NULL	NULL
+4	4	4	NULL
+SELECT * FROM
+t1 LEFT JOIN
+( t2 LEFT JOIN
+( t3 LEFT JOIN 
+t4
+ON t4.i = t3.i
+)
+ON t3.i = t2.i
+)
+ON t2.i = t1.i
+WHERE t4.i IS NULL;
+i	i	i	i
+2	NULL	NULL	NULL
+3	3	NULL	NULL
+4	4	4	NULL
+DROP TABLE t1,t2,t3,t4;
 End of 5.1 tests

=== modified file 'mysql-test/t/join_outer.test'
--- a/mysql-test/t/join_outer.test	2010-06-01 07:54:06 +0000
+++ b/mysql-test/t/join_outer.test	2010-11-25 13:59:10 +0000
@@ -981,4 +981,45 @@ EXPLAIN EXTENDED SELECT STRAIGHT_JOIN jt
 
 DROP TABLE t1;
 
+
+--echo #
+--echo # Bug#58490: Incorrect result in multi level OUTER JOIN 
+--echo # in combination with IS NULL 
+--echo #
+
+CREATE TABLE t1 (i INT NOT NULL);
+INSERT INTO t1 VALUES (0),    (2),(3),(4);
+CREATE TABLE t2 (i INT NOT NULL);
+INSERT INTO t2 VALUES (0),(1),    (3),(4);
+CREATE TABLE t3 (i INT NOT NULL);
+INSERT INTO t3 VALUES (0),(1),(2),    (4);
+CREATE TABLE t4 (i INT NOT NULL);
+INSERT INTO t4 VALUES (0),(1),(2),(3)   ;
+
+SELECT * FROM
+ t1 LEFT JOIN
+ ( t2 LEFT JOIN
+   ( t3 LEFT JOIN 
+     t4
+     ON t4.i = t3.i
+   )
+   ON t3.i = t2.i
+ )
+ ON t2.i = t1.i
+ ;
+
+SELECT * FROM
+ t1 LEFT JOIN
+ ( t2 LEFT JOIN
+   ( t3 LEFT JOIN 
+     t4
+     ON t4.i = t3.i
+   )
+   ON t3.i = t2.i
+ )
+ ON t2.i = t1.i
+ WHERE t4.i IS NULL;
+
+DROP TABLE t1,t2,t3,t4;
+
 --echo End of 5.1 tests

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-10-20 13:22:47 +0000
+++ b/sql/sql_select.cc	2010-11-25 13:59:10 +0000
@@ -11467,6 +11467,7 @@ evaluate_join_record(JOIN *join, JOIN_TA
       return NESTED_LOOP_ERROR;
   }
 
+  enum enum_nested_loop_state rc= NESTED_LOOP_OK;
   if (!select_cond || select_cond_result)
   {
     /*
@@ -11490,7 +11491,8 @@ evaluate_join_record(JOIN *join, JOIN_TA
       for (JOIN_TAB *tab= first_unmatched; tab <= join_tab; tab++)
       {
         if (tab->table->reginfo.not_exists_optimize)
-          return NESTED_LOOP_NO_MORE_ROWS;
+          rc= NESTED_LOOP_NO_MORE_ROWS;
+
         /* Check all predicates that has just been activated. */
         /*
           Actually all predicates non-guarded by first_unmatched->found
@@ -11509,7 +11511,7 @@ evaluate_join_record(JOIN *join, JOIN_TA
               not to the last table of the current nest level.
             */
             join->return_tab= tab;
-            return NESTED_LOOP_OK;
+            return rc;
           }
         }
       }
@@ -11535,7 +11537,7 @@ evaluate_join_record(JOIN *join, JOIN_TA
 
     if (found)
     {
-      enum enum_nested_loop_state rc;
+      DBUG_ASSERT(rc==NESTED_LOOP_OK);
       /* A match from join_tab is found for the current partial join. */
       rc= (*join_tab->next_select)(join, join_tab+1, 0);
       if (rc != NESTED_LOOP_OK && rc != NESTED_LOOP_NO_MORE_ROWS)
@@ -11563,7 +11565,7 @@ evaluate_join_record(JOIN *join, JOIN_TA
     join->thd->row_count++;
     join_tab->read_record.unlock_row(join_tab);
   }
-  return NESTED_LOOP_OK;
+  return rc;
 }
 
 


Attachment: [text/bzr-bundle] bzr/ole.john.aske@oracle.com-20101125135910-nbc0xbj77w1q04q5.bundle
Thread
bzr commit into mysql-5.1 branch (ole.john.aske:3474) Bug#58490Ole John Aske25 Nov