List:Commits« Previous MessageNext Message »
From:Igor Babaev Date:October 9 2008 12:31am
Subject:bzr commit into mysql-6.0-bka-preview branch (igor:2648) Bug#35835
View as plain text  
#At file:///home/igor/dev-bzr/mysql-6.0-bug35835/

 2648 Igor Babaev	2008-10-08
      Fixed several bugs reported in the bug entry of bug #35835.
      All these bugs concerned null complemented rows in the
      result sets of queries with nested outer joins and are 
      closely interconnected.
            
      Added test cases for bug #35835.
      Fixed a wrong result set for one of the previous tests.
modified:
  mysql-test/r/join_nested_jcl6.result
  mysql-test/t/join_nested_jcl6.test
  sql/sql_select.cc

per-file messages:
  mysql-test/r/join_nested_jcl6.result
    Added test cases for bug #35835.
    Fixed a wrong result set for one of the previous tests.
  mysql-test/t/join_nested_jcl6.test
    Added test cases for bug #35835.
  sql/sql_select.cc
    Fixed several bugs reported in the bug entry of bug #35835.
    All these bugs concerned null complemented rows in the
    result sets of queries with nested outer joins and are 
    closely interconnected.
    The bugs caused:
      - generation of superfluous null complemented rows
      - missing some null complemented rows
      - not null values for some columns of null complements.
    The erroneous code was fixed in the functions JOIN_CACHE::join_records,
    JOIN_CACHE::check_match and JOIN_CACHE::join_null_complements. 
    The code of the function check_join_cache_usage was modified to 
    prohibit usage of  a join buffer to join an inner table of an outer 
    join or a semi-join unless each of the inner tables is joined 
    using a join buffer. In other words either any of the inner tables of 
    an outer join of a semi-join must be joined using a join buffer or
    none of them is joined with a join buffer.
    Also now a join buffer can be used to join tables with the JT_CONST 
    and JT_SYSTEM access type.
=== modified file 'mysql-test/r/join_nested_jcl6.result'
--- a/mysql-test/r/join_nested_jcl6.result	2008-04-06 07:20:43 +0000
+++ b/mysql-test/r/join_nested_jcl6.result	2008-10-09 00:31:34 +0000
@@ -1120,8 +1120,6 @@ a	b	a	b	a	b	a	b	a	b	a	b	a	b	a	b	a	b	a	b
 1	2	3	2	5	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL	1	2
 1	2	3	2	5	3	NULL	NULL	NULL	NULL	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	1
 1	2	3	2	5	3	NULL	NULL	NULL	NULL	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	2
-1	2	2	2	NULL	NULL	1	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1	1
-1	2	2	2	NULL	NULL	1	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1	2
 1	2	2	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1	1
 1	2	2	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1	2
 SELECT t2.a,t2.b
@@ -1750,6 +1748,108 @@ COUNT(*)
 6
 DROP TABLE t1,t2,t3,t4,t5;
 End of 5.0 tests
+CREATE TABLE t5 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
+CREATE TABLE t6 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
+CREATE TABLE t7 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
+CREATE TABLE t8 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
+INSERT INTO t5 VALUES (1,1,0), (2,2,0), (3,3,0);
+INSERT INTO t6 VALUES (1,2,0), (3,2,0), (6,1,0);
+INSERT INTO t7 VALUES (1,1,0), (2,2,0);
+INSERT INTO t8 VALUES (0,2,0), (1,2,0);
+EXPLAIN
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+FROM t5 
+LEFT JOIN 
+( 
+(t6, t7)
+LEFT JOIN 
+t8
+ON t7.b=t8.b AND t6.b < 10
+)
+ON t6.b >= 2 AND t5.b=t7.b AND
+(t8.a > 0 OR t8.c IS NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t7	ref	b_i	b_i	5	test.t5.b	2	Using join buffer
+1	SIMPLE	t6	ALL	b_i	NULL	NULL	NULL	3	Using where; Using join buffer
+1	SIMPLE	t8	ref	b_i	b_i	5	test.t7.b	2	Using where; Using join buffer
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+FROM t5 
+LEFT JOIN 
+( 
+(t6, t7)
+LEFT JOIN 
+t8
+ON t7.b=t8.b AND t6.b < 10
+)
+ON t6.b >= 2 AND t5.b=t7.b AND
+(t8.a > 0 OR t8.c IS NULL);
+a	b	a	b	a	b	a	b
+2	2	1	2	2	2	1	2
+2	2	3	2	2	2	1	2
+1	1	1	2	1	1	NULL	NULL
+1	1	3	2	1	1	NULL	NULL
+3	3	NULL	NULL	NULL	NULL	NULL	NULL
+DELETE FROM t5;
+DELETE FROM t6;
+DELETE FROM t7;
+DELETE FROM t8;
+INSERT INTO t5 VALUES (1,3,0), (3,2,0);
+INSERT INTO t6 VALUES (3,3,0);
+INSERT INTO t7 VALUES (1,2,0);
+INSERT INTO t8 VALUES (1,1,0);
+EXPLAIN
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+FROM t5 LEFT JOIN                
+(t6 LEFT JOIN t7 ON t7.a=1, t8)
+ON (t5.b=t8.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	1	Using join buffer
+1	SIMPLE	t7	const	PRIMARY	PRIMARY	4	const	1	Using join buffer
+1	SIMPLE	t8	ALL	b_i	NULL	NULL	NULL	1	Using where; Using join buffer
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+FROM t5 LEFT JOIN                
+(t6 LEFT JOIN t7 ON t7.a=1, t8)
+ON (t5.b=t8.b);
+a	b	a	b	a	b	a	b
+1	3	NULL	NULL	NULL	NULL	NULL	NULL
+3	2	NULL	NULL	NULL	NULL	NULL	NULL
+EXPLAIN
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+FROM t5 LEFT JOIN                
+(t6 LEFT JOIN t7 ON t7.b=2, t8)
+ON (t5.b=t8.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	1	Using join buffer
+1	SIMPLE	t7	ref	b_i	b_i	5	const	0	Using join buffer
+1	SIMPLE	t8	ALL	b_i	NULL	NULL	NULL	1	Using where; Using join buffer
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+FROM t5 LEFT JOIN                
+(t6 LEFT JOIN t7 ON t7.b=2, t8)
+ON (t5.b=t8.b);
+a	b	a	b	a	b	a	b
+1	3	NULL	NULL	NULL	NULL	NULL	NULL
+3	2	NULL	NULL	NULL	NULL	NULL	NULL
+EXPLAIN
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+FROM t5 LEFT JOIN                
+(t8, t6 LEFT JOIN t7 ON t7.a=1)
+ON (t5.b=t8.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t8	ALL	b_i	NULL	NULL	NULL	1	Using where; Using join buffer
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	1	Using join buffer
+1	SIMPLE	t7	const	PRIMARY	PRIMARY	4	const	1	Using join buffer
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+FROM t5 LEFT JOIN                
+(t8, t6 LEFT JOIN t7 ON t7.a=1)
+ON (t5.b=t8.b);
+a	b	a	b	a	b	a	b
+1	3	NULL	NULL	NULL	NULL	NULL	NULL
+3	2	NULL	NULL	NULL	NULL	NULL	NULL
+DROP TABLE t5,t6,t7,t8;
 set join_cache_level=default;
 show variables like 'join_cache_level';
 Variable_name	Value

=== modified file 'mysql-test/t/join_nested_jcl6.test'
--- a/mysql-test/t/join_nested_jcl6.test	2008-04-06 07:20:43 +0000
+++ b/mysql-test/t/join_nested_jcl6.test	2008-10-09 00:31:34 +0000
@@ -7,5 +7,89 @@ show variables like 'join_cache_level';
 
 --source t/join_nested.test
 
+#
+# BUG#35835: queries with nested outer joins with BKA enabled
+#            
+
+CREATE TABLE t5 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
+CREATE TABLE t6 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
+CREATE TABLE t7 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
+CREATE TABLE t8 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
+
+INSERT INTO t5 VALUES (1,1,0), (2,2,0), (3,3,0);
+INSERT INTO t6 VALUES (1,2,0), (3,2,0), (6,1,0);
+INSERT INTO t7 VALUES (1,1,0), (2,2,0);
+INSERT INTO t8 VALUES (0,2,0), (1,2,0);
+
+EXPLAIN
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+  FROM t5 
+       LEFT JOIN 
+       ( 
+         (t6, t7)
+         LEFT JOIN 
+         t8
+         ON t7.b=t8.b AND t6.b < 10
+       )
+       ON t6.b >= 2 AND t5.b=t7.b AND
+          (t8.a > 0 OR t8.c IS NULL);
+
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+  FROM t5 
+       LEFT JOIN 
+       ( 
+         (t6, t7)
+         LEFT JOIN 
+         t8
+         ON t7.b=t8.b AND t6.b < 10
+       )
+       ON t6.b >= 2 AND t5.b=t7.b AND
+          (t8.a > 0 OR t8.c IS NULL);
+
+DELETE FROM t5;
+DELETE FROM t6;
+DELETE FROM t7;
+DELETE FROM t8;
+
+INSERT INTO t5 VALUES (1,3,0), (3,2,0);
+INSERT INTO t6 VALUES (3,3,0);
+INSERT INTO t7 VALUES (1,2,0);
+INSERT INTO t8 VALUES (1,1,0);
+
+EXPLAIN
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+  FROM t5 LEFT JOIN                
+       (t6 LEFT JOIN t7 ON t7.a=1, t8)
+       ON (t5.b=t8.b);
+
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+  FROM t5 LEFT JOIN                
+       (t6 LEFT JOIN t7 ON t7.a=1, t8)
+       ON (t5.b=t8.b);
+
+EXPLAIN
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+  FROM t5 LEFT JOIN                
+       (t6 LEFT JOIN t7 ON t7.b=2, t8)
+       ON (t5.b=t8.b);
+
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+  FROM t5 LEFT JOIN                
+       (t6 LEFT JOIN t7 ON t7.b=2, t8)
+       ON (t5.b=t8.b);
+
+EXPLAIN
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+  FROM t5 LEFT JOIN                
+       (t8, t6 LEFT JOIN t7 ON t7.a=1)
+       ON (t5.b=t8.b);
+
+SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
+  FROM t5 LEFT JOIN                
+       (t8, t6 LEFT JOIN t7 ON t7.a=1)
+       ON (t5.b=t8.b);
+
+DROP TABLE t5,t6,t7,t8;
+
 set join_cache_level=default;
 show variables like 'join_cache_level';

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2008-09-04 18:41:27 +0000
+++ b/sql/sql_select.cc	2008-10-09 00:31:34 +0000
@@ -8336,16 +8336,12 @@ void revise_cache_usage(JOIN_TAB *join_t
   if (join_tab->first_inner)
   {
     JOIN_TAB *end_tab= join_tab;
-    JOIN_TAB *last_inner= join_tab->first_inner->last_inner;
     for (first_inner= join_tab->first_inner; 
-         first_inner && first_inner->last_inner == last_inner;
+         first_inner;
          first_inner= first_inner->first_upper)           
     {
       for (tab= end_tab-1; tab >= first_inner; tab--)
-      {
-        if (tab->first_inner->last_inner == last_inner)
-          set_join_cache_denial(tab);
-      }
+        set_join_cache_denial(tab);
       end_tab= first_inner;
     }
   }
@@ -8368,12 +8364,12 @@ bool check_join_cache_usage(JOIN_TAB *ta
 {
   uint flags;
   COST_VECT cost;
+  ha_rows rows;
   uint bufsz= 4096;
   JOIN_CACHE *prev_cache=0;
   uint cache_level= join->thd->variables.join_cache_level;
   bool force_unlinked_cache= test(cache_level & 1);
   uint i= tab-join->join_tab;
-  ha_rows rows;
   
   if (cache_level == 0)
     return FALSE;
@@ -8393,10 +8389,32 @@ bool check_join_cache_usage(JOIN_TAB *ta
     goto no_join_cache;
   for (JOIN_TAB *first_inner= tab->first_inner; first_inner;
        first_inner= first_inner->first_upper)
-  { 
+  {
     if (first_inner != tab && !first_inner->use_join_cache)
       goto no_join_cache;
   }
+  if (tab->first_sj_inner_tab && !tab->first_sj_inner_tab->use_join_cache)
+    goto no_join_cache;
+  if (!tab[-1].use_join_cache)
+  {
+    /* 
+      Check whether table tab and the previous one belong to the same nest of
+      inner tables and if so do not use join buffer when joining table tab. 
+    */
+    if (tab->first_inner)
+    {
+      for (JOIN_TAB *first_inner= tab[-1].first_inner;
+           first_inner;
+           first_inner= first_inner->first_upper)
+      {
+        if (first_inner == tab->first_inner)
+          goto no_join_cache;
+      }
+    }
+    else if (tab->first_sj_inner_tab &&
+             tab->first_sj_inner_tab == tab[-1].first_sj_inner_tab)
+      goto no_join_cache; 
+  }       
 
   if (!force_unlinked_cache)
     prev_cache= tab[-1].cache;
@@ -8411,6 +8429,8 @@ bool check_join_cache_usage(JOIN_TAB *ta
         !tab->cache->init())
       return TRUE;
     goto no_join_cache;
+  case JT_SYSTEM:
+  case JT_CONST:
   case JT_REF:
   case JT_EQ_REF:
     if (cache_level <= 4)
@@ -8492,21 +8512,28 @@ make_join_readinfo(JOIN *join, ulonglong
         return TRUE;
     }
     switch (tab->type) {
-    case JT_SYSTEM:				// Only happens with left join
+    case JT_SYSTEM:			        
+    case JT_CONST:
+      /* Only happens with outer joins */  			      
       table->status=STATUS_NO_RECORD;
-      tab->read_first_record= join_read_system;
-      tab->read_record.read_record= join_no_more_records;
-      break;
-    case JT_CONST:				// Only happens with left join
-      table->status=STATUS_NO_RECORD;
-      tab->read_first_record= join_read_const;
+      tab->read_first_record= tab->type == JT_SYSTEM ?
+	                        join_read_system :join_read_const;
       tab->read_record.read_record= join_no_more_records;
+      using_join_cache= FALSE;
+      if (check_join_cache_usage(tab, join, options, no_jbuf_after))
+      {
+        using_join_cache= TRUE;
+	tab[-1].next_select=sub_select_cache;
+      }
+      tab->use_join_cache= using_join_cache;        
       if (table->covering_keys.is_set(tab->ref.key) &&
-          !table->no_keyread)
+	  !table->no_keyread)
       {
-        table->key_read=1;
-        table->file->extra(HA_EXTRA_KEYREAD);
+	table->key_read=1;
+	table->file->extra(HA_EXTRA_KEYREAD);
       }
+      else
+        push_index_cond(tab, tab->ref.key, !using_join_cache);
       break;
     case JT_EQ_REF:
       table->status=STATUS_NO_RECORD;
@@ -18398,10 +18425,10 @@ enum_nested_loop_state JOIN_CACHE::join_
   enum_nested_loop_state rc= NESTED_LOOP_OK;
   bool outer_join_first_inner= join_tab->is_first_inner_for_outer_join();
 
-  if (outer_join_first_inner)
-    join_tab->not_null_compl= TRUE;
+  if (outer_join_first_inner && !join_tab->first_unmatched)
+    join_tab->not_null_compl= TRUE;   
 
-  if (!join_tab->first_inner || join_tab->first_inner->not_null_compl)
+  if (!join_tab->first_unmatched)
   {
     /* Find all records from join_tab that match records from join buffer */
     rc= join_matching_records(skip_last);   
@@ -18428,7 +18455,7 @@ enum_nested_loop_state JOIN_CACHE::join_
         tab->first_unmatched= join_tab->first_inner;
     }
   }
-  if (join_tab->first_unmatched && !join_tab->first_unmatched->not_null_compl)
+  if (join_tab->first_unmatched)
   {
     /* 
       Generate all null complementing extensions for the records from
@@ -18534,7 +18561,7 @@ enum_nested_loop_state JOIN_CACHE_BNL::j
   { 
     /* A dynamic range access was used last. Clean up after it */
     delete join_tab->select->quick;
-    join_tab->select->quick=0;
+    join_tab->select->quick= 0;
   }
 
   for (tab= join->join_tab; tab != join_tab ; tab++)
@@ -18544,7 +18571,7 @@ enum_nested_loop_state JOIN_CACHE_BNL::j
   }
 
   /* Start retrieving all records of the joined table */
-  if ((error=join_init_read_record(join_tab)))
+  if ((error= join_init_read_record(join_tab))) 
   {
     rc= error < 0 ? NESTED_LOOP_NO_MORE_ROWS: NESTED_LOOP_ERROR;
     goto finish;
@@ -18745,7 +18772,7 @@ inline bool JOIN_CACHE::check_match(ucha
   do
   {
     if (!set_match_flag_if_none(first_inner, rec_ptr))
-      return TRUE;
+      continue;
     if (first_inner->check_only_first_match() &&
         !first_inner->first_upper)
       return TRUE;
@@ -18766,7 +18793,8 @@ inline bool JOIN_CACHE::check_match(ucha
         return FALSE;
     }
   }
-  while ((first_inner= first_inner->first_upper));
+  while ((first_inner= first_inner->first_upper) &&
+         first_inner->last_inner == join_tab);
   
   return TRUE;
 } 
@@ -18832,7 +18860,7 @@ enum_nested_loop_state JOIN_CACHE::join_
       if (is_last_inner)
       { 
         JOIN_TAB *first_upper= join_tab->first_unmatched->first_upper;
-        while (first_upper)
+        while (first_upper && first_upper->last_inner == join_tab)
         {
           if (!set_match_flag_if_none(first_upper, get_curr_rec()))
             break;

Thread
bzr commit into mysql-6.0-bka-preview branch (igor:2648) Bug#35835Igor Babaev9 Oct