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

 3477 Ole John Aske	2010-12-02
      Updated fix for bug#58490, 'Incorrect result in multi level OUTER JOIN in combination with IS NULL'
      
      After some more testing, and discussion with Roy L. which has also tested my fix on the next-mr branch
      the DBUG_ASSERT(rc==NESTED_LOOP_OK) a line ~11553 was removed.
      This was replaced by 'if (rc == NESTED_LOOP_NO_MORE_ROWS) found= false;'.
      
      Furthermore testcases for the duplicated bug#49332 has been added.
      
      
      --- 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	2010-12-02 12:09:47 +0000
@@ -1427,4 +1427,86 @@ 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
+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	2010-12-02 12:09:47 +0000
@@ -1010,4 +1010,73 @@ 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;
+
+DROP TABLE t1,t2,t3,t4;
+
+## Bug#49332 & 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-10-29 08:23:06 +0000
+++ b/sql/sql_select.cc	2010-12-02 12:09:47 +0000
@@ -11473,6 +11473,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)
   {
     /*
@@ -11496,7 +11497,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
@@ -11515,7 +11517,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;
           }
         }
       }
@@ -11530,6 +11532,16 @@ evaluate_join_record(JOIN *join, JOIN_TA
     }
 
     /*
+     Setting NESTED_LOOP_NO_MORE_ROWS (if not_exists_optimize) 
+     also implies a 'not found' condition. However we could not
+     set this inside the  loop above as it would prematurely
+     have terminated the 'first_unmatched' / 'first_unmatched->found'
+     calculations above.
+    */
+    if (rc == NESTED_LOOP_NO_MORE_ROWS)
+      found= false;
+
+    /*
       It was not just a return to lower loop level when one
       of the newly activated predicates is evaluated as false
       (See above join->return_tab= tab).
@@ -11541,7 +11553,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)
@@ -11569,7 +11580,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-20101202120947-ub7q353mmhjm4np3.bundle
Thread
bzr commit into mysql-5.1 branch (ole.john.aske:3477) Bug#58490Ole John Aske2 Dec
  • Re: bzr commit into mysql-5.1 branch (ole.john.aske:3477) Bug#58490Guilhem Bichot16 Dec
    • Re: bzr commit into mysql-5.1 branch (ole.john.aske:3477) Bug#58490Roy Lyseng17 Dec