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

 3534 Ole John Aske	2011-01-07
      Updated fix for bug#58490, 'Incorrect result in multi level OUTER JOIN in combination with IS NULL'
            
      Based on even more testing, and review feedback from Guilhem B. and Roy L.
      
      There are cases where we may exit the while(first_unmatched && found)' loop
      with both found= true and rc= NESTED_LOOP_NO_MORE_ROWS. This simply
      indicates that we should proceed with the 'if(found)' section in
      evaluate_join_record() and produce results from the 'found' row.
      
      It should be noted that this behaviour is identical to what we will
      get when we remove the 'not_exists_optimize' flag (Which is 
      a sensible sanity test as not_exists_optimize is a pure optimization only.)
      
      Testcases has been added to the original fix to cover this situation
      (found= true and rc= NESTED_LOOP_NO_MORE_ROWS.)
      
      As this bug is a duplicate of bug#49322, it also include tescases
      covering this bugreport
      
      --- original description ---
      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-10-29 08:23:06 +0000
+++ b/mysql-test/r/join_outer.result	2011-01-07 13:56:31 +0000
@@ -1427,4 +1427,146 @@ WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AN
 GROUP BY t2.f1, t2.f2;
 f1	f1	f2
 DROP TABLE t1,t2;
+#
+# 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
+SELECT * FROM
+t1 LEFT JOIN
+( ( t2 LEFT JOIN
+t3
+ON t3.i = t2.i
+)
+)
+ON t2.i = t1.i
+WHERE t3.i IS NULL;
+i	i	i
+2	NULL	NULL
+3	3	NULL
+SELECT * FROM
+t1 LEFT JOIN
+( ( t2 LEFT JOIN
+t3
+ON t3.i = t2.i
+)
+JOIN t4
+ON t4.i=t2.i
+)
+ON t2.i = t1.i
+WHERE t3.i IS NULL;
+i	i	i	i
+2	NULL	NULL	NULL
+3	3	NULL	3
+4	NULL	NULL	NULL
+SELECT * FROM
+t1 LEFT JOIN
+( ( t2 LEFT JOIN
+t3
+ON t3.i = t2.i
+)
+JOIN (t4 AS t4a JOIN t4 AS t4b ON t4a.i=t4b.i)
+ON t4a.i=t2.i
+)
+ON t2.i = t1.i
+WHERE t3.i IS NULL;
+i	i	i	i	i
+2	NULL	NULL	NULL	NULL
+3	3	NULL	3	3
+4	NULL	NULL	NULL	NULL
+SELECT * FROM
+t1 LEFT JOIN
+( ( t2 LEFT JOIN
+t3
+ON t3.i = t2.i
+)
+JOIN (t4 AS t4a, t4 AS t4b)
+ON t4a.i=t2.i
+)
+ON t2.i = t1.i
+WHERE t3.i IS NULL;
+i	i	i	i	i
+2	NULL	NULL	NULL	NULL
+3	3	NULL	3	0
+3	3	NULL	3	1
+3	3	NULL	3	2
+3	3	NULL	3	3
+4	NULL	NULL	NULL	NULL
+DROP TABLE t1,t2,t3,t4;
+#
+# Bug#49322(Duplicate): Server is adding extra NULL row
+# on processing a WHERE clause
+#
+CREATE TABLE h (pk INT NOT NULL, col_int_key INT);
+INSERT INTO h VALUES (1,NULL),(4,2),(5,2),(3,4),(2,8);
+CREATE TABLE m (pk INT NOT NULL, col_int_key INT);
+INSERT INTO m VALUES (1,2),(2,7),(3,5),(4,7),(5,5),(6,NULL),(7,NULL),(8,9);
+CREATE TABLE k (pk INT NOT NULL, col_int_key INT);
+INSERT INTO k VALUES (1,9),(2,2),(3,5),(4,2),(5,7),(6,0),(7,5);
+SELECT TABLE1.pk FROM k TABLE1
+RIGHT JOIN h TABLE2 ON TABLE1.col_int_key=TABLE2.col_int_key 
+RIGHT JOIN m TABLE4 ON TABLE2.col_int_key=TABLE4.col_int_key;
+pk
+2
+4
+2
+4
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+SELECT TABLE1.pk FROM k TABLE1
+RIGHT JOIN h TABLE2 ON TABLE1.col_int_key=TABLE2.col_int_key 
+RIGHT JOIN m TABLE4 ON TABLE2.col_int_key=TABLE4.col_int_key
+WHERE TABLE1.pk IS NULL;
+pk
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+DROP TABLE h,m,k;
 End of 5.1 tests

=== modified file 'mysql-test/t/join_outer.test'
--- a/mysql-test/t/join_outer.test	2010-10-29 08:23:06 +0000
+++ b/mysql-test/t/join_outer.test	2011-01-07 13:56:31 +0000
@@ -1010,4 +1010,128 @@ GROUP BY t2.f1, t2.f2;
 
 DROP TABLE t1,t2;
 
+
+--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;
+
+
+# Most simplified testcase to reproduce the bug.
+# (Has to be at least a two level nested outer join)
+SELECT * FROM
+ t1 LEFT JOIN
+ ( ( t2 LEFT JOIN
+     t3
+     ON t3.i = t2.i
+   )
+ )
+ ON t2.i = t1.i
+ WHERE t3.i IS NULL;
+
+
+# Extended testing:
+# We then add some equi-join inside the query above:
+# (There Used to be some problems here with first 
+#  proposed patch for this bug)
+SELECT * FROM
+ t1 LEFT JOIN
+ ( ( t2 LEFT JOIN
+     t3
+     ON t3.i = t2.i
+   )
+   JOIN t4
+   ON t4.i=t2.i
+ )
+ ON t2.i = t1.i
+ WHERE t3.i IS NULL;
+
+SELECT * FROM
+ t1 LEFT JOIN
+ ( ( t2 LEFT JOIN
+     t3
+     ON t3.i = t2.i
+   )
+   JOIN (t4 AS t4a JOIN t4 AS t4b ON t4a.i=t4b.i)
+   ON t4a.i=t2.i
+ )
+ ON t2.i = t1.i
+ WHERE t3.i IS NULL;
+
+SELECT * FROM
+ t1 LEFT JOIN
+ ( ( t2 LEFT JOIN
+     t3
+     ON t3.i = t2.i
+   )
+   JOIN (t4 AS t4a, t4 AS t4b)
+   ON t4a.i=t2.i
+ )
+ ON t2.i = t1.i
+ WHERE t3.i IS NULL;
+
+
+DROP TABLE t1,t2,t3,t4;
+
+## Bug#49322 & bug#58490 are duplicates. However, we include testcases
+## for both.
+--echo #
+--echo # Bug#49322(Duplicate): Server is adding extra NULL row
+--echo # on processing a WHERE clause
+--echo #
+
+CREATE TABLE h (pk INT NOT NULL, col_int_key INT);
+INSERT INTO h VALUES (1,NULL),(4,2),(5,2),(3,4),(2,8);
+
+CREATE TABLE m (pk INT NOT NULL, col_int_key INT);
+INSERT INTO m VALUES (1,2),(2,7),(3,5),(4,7),(5,5),(6,NULL),(7,NULL),(8,9);
+CREATE TABLE k (pk INT NOT NULL, col_int_key INT);
+INSERT INTO k VALUES (1,9),(2,2),(3,5),(4,2),(5,7),(6,0),(7,5);
+
+# Baseline query wo/ 'WHERE ... IS NULL' - was correct
+SELECT TABLE1.pk FROM k TABLE1
+RIGHT JOIN h TABLE2 ON TABLE1.col_int_key=TABLE2.col_int_key 
+RIGHT JOIN m TABLE4 ON TABLE2.col_int_key=TABLE4.col_int_key;
+
+# Adding 'WHERE ... IS NULL' -> incorrect result
+SELECT TABLE1.pk FROM k TABLE1
+RIGHT JOIN h TABLE2 ON TABLE1.col_int_key=TABLE2.col_int_key 
+RIGHT JOIN m TABLE4 ON TABLE2.col_int_key=TABLE4.col_int_key
+WHERE TABLE1.pk IS NULL;
+
+DROP TABLE h,m,k;
+
 --echo End of 5.1 tests

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-12-28 23:47:05 +0000
+++ b/sql/sql_select.cc	2011-01-07 13:56:31 +0000
@@ -11478,6 +11478,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)
   {
     /*
@@ -11501,7 +11502,18 @@ 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;
+        {
+          /*
+            There is a WHERE ... IS NULL condition which this tab cannot
+            possibly satisfy. At this stage this condition may not be enabled
+            yet, due to trig_cond conditions on the 'found' members of JOIN_TABs
+            and those members not all being true now.
+            So just mark that we should skip evaluation of next rows of 'tab',
+            after this row's complete evaluation is finished.
+           */
+           rc= NESTED_LOOP_NO_MORE_ROWS;
+        }
+
         /* Check all predicates that has just been activated. */
         /*
           Actually all predicates non-guarded by first_unmatched->found
@@ -11520,7 +11532,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;
           }
         }
       }
@@ -11546,7 +11558,6 @@ evaluate_join_record(JOIN *join, JOIN_TA
 
     if (found)
     {
-      enum enum_nested_loop_state rc;
       /* 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)
@@ -11574,7 +11585,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-20110107135631-kek8dgnv3mast6hd.bundle
Thread
bzr commit into mysql-5.1 branch (ole.john.aske:3534) Bug#58490Ole John Aske7 Jan