bzr commit into mysql-6.0-bka-preview branch (igor:2661) Bug#40268

[Igor Babaev]

#At file:///home/igor/dev-bzr/mysql-6.0-bug40268/

 2661 Igor Babaev	2008-10-22
      Fixed bug #40268.
      This bug caused generation of extra rows in the result sets for
      some queries with nested outer joins. It could happen if
      the pushdown condition attached to not the last inner table 
      of the outer join was extracted from the where clause.
      It happened because this pushdown condition erroneously
      was not checked against matching row combinations from inner
      tables that were not the first matches for a given partial
      row generated from outer tables.
modified:
  mysql-test/r/join_cache.result
  mysql-test/t/join_cache.test
  sql/sql_select.cc

per-file messages:
  mysql-test/r/join_cache.result
    Added a test case for bug #40268.
  mysql-test/t/join_cache.test
    Added a test case for bug #40268.
  sql/sql_select.cc
    Fixed bug #40268.
    Made sure that any pushdown condition attached to the inner table of
    a nested outer is applied.
    Fixed a typo bug with a misplaced label in the function JOIN_CACHE::join_null_complements that could cause a crash
    for some queries with nested outer joins.
=== modified file 'mysql-test/r/join_cache.result'
--- a/mysql-test/r/join_cache.result	2008-10-16 15:16:24 +0000
+++ b/mysql-test/r/join_cache.result	2008-10-23 04:53:32 +0000
@@ -3271,3 +3271,24 @@ a	count
 9	0
 set join_cache_level=@save_join_cache_level;
 drop table t1, t2;
+#
+# BUG#40268: Nested outer join with not null-rejecting where condition
+#            over an inner table which is not the last in the nest
+#
+CREATE TABLE t2 (a int, b int, c int);
+CREATE TABLE t3 (a int, b int, c int);
+CREATE TABLE t4 (a int, b int, c int);
+INSERT INTO t2 VALUES (3,3,0), (4,2,0), (5,3,0);
+INSERT INTO t3 VALUES (1,2,0), (2,2,0);
+INSERT INTO t4 VALUES (3,2,0), (4,2,0);
+set join_cache_level=6;
+SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
+FROM t2 LEFT JOIN (t3, t4) ON t2.b=t4.b
+WHERE t3.a+2<t2.a OR t3.c IS NULL;
+a	b	a	b	a	b
+4	2	1	2	3	2
+4	2	1	2	4	2
+3	3	NULL	NULL	NULL	NULL
+5	3	NULL	NULL	NULL	NULL
+set join_cache_level=default;
+DROP TABLE t2, t3, t4;

=== modified file 'mysql-test/t/join_cache.test'
--- a/mysql-test/t/join_cache.test	2008-10-16 15:16:24 +0000
+++ b/mysql-test/t/join_cache.test	2008-10-23 04:53:32 +0000
@@ -816,3 +816,25 @@ select t1.a, count(t2.p) as count
   from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
 set join_cache_level=@save_join_cache_level;
 drop table t1, t2;
+
+--echo #
+--echo # BUG#40268: Nested outer join with not null-rejecting where condition
+--echo #            over an inner table which is not the last in the nest
+--echo #
+
+CREATE TABLE t2 (a int, b int, c int);
+CREATE TABLE t3 (a int, b int, c int);
+CREATE TABLE t4 (a int, b int, c int);
+
+INSERT INTO t2 VALUES (3,3,0), (4,2,0), (5,3,0);
+INSERT INTO t3 VALUES (1,2,0), (2,2,0);
+INSERT INTO t4 VALUES (3,2,0), (4,2,0);
+
+set join_cache_level=6;
+
+SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
+  FROM t2 LEFT JOIN (t3, t4) ON t2.b=t4.b
+     WHERE t3.a+2<t2.a OR t3.c IS NULL;
+
+set join_cache_level=default;
+DROP TABLE t2, t3, t4;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2008-10-16 15:16:24 +0000
+++ b/sql/sql_select.cc	2008-10-23 04:53:32 +0000
@@ -18786,8 +18786,7 @@ inline bool JOIN_CACHE::check_match(ucha
   JOIN_TAB *first_inner= join_tab->get_first_inner_table();
   do
   {
-    if (!set_match_flag_if_none(first_inner, rec_ptr))
-      continue;
+    set_match_flag_if_none(first_inner, rec_ptr);
     if (first_inner->check_only_first_match() &&
         !join_tab->first_inner)
       return TRUE;
@@ -18851,8 +18850,7 @@ enum_nested_loop_state JOIN_CACHE::join_
   DBUG_ASSERT(join_tab->first_inner);
 
   for ( ; cnt; cnt--)
-  { 
-   next:
+  {
     if (join->thd->killed)
     {
       /* The user has aborted the execution of the query */
@@ -18876,8 +18874,7 @@ enum_nested_loop_state JOIN_CACHE::join_
         JOIN_TAB *first_upper= join_tab->first_unmatched->first_upper;
         while (first_upper && first_upper->last_inner == join_tab)
         {
-          if (!set_match_flag_if_none(first_upper, get_curr_rec()))
-            break;
+          set_match_flag_if_none(first_upper, get_curr_rec());
           for (JOIN_TAB* tab= first_upper; tab <= join_tab; tab++)
           {
             if (tab->select && tab->select->skip_record())
@@ -18894,6 +18891,8 @@ enum_nested_loop_state JOIN_CACHE::join_
         goto finish;
       }
     }
+  next:
+    ;
   }
 
 finish: