[Commits] Rev 2859: Fixed LP #bug 660963. in file:///home/igor/maria/maria-5.3-mwl128-bug660963/

Igor Babaev igor at askmonty.org
Fri Nov 19 16:20:29 EET 2010


At file:///home/igor/maria/maria-5.3-mwl128-bug660963/

------------------------------------------------------------
revno: 2859
revision-id: igor at askmonty.org-20101119142028-ymffn83m5hyvv2ck
parent: igor at askmonty.org-20101116050732-hpbqelsf8nvae4xt
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.3-mwl128-bug660963
timestamp: Fri 2010-11-19 06:20:28 -0800
message:
  Fixed LP #bug 660963.
  The condition that was supposed to check whether a join table
  is an inner table of a nested outer join or semi-join was not
  quite correct in the code of the function check_join_cache_usage.
  That's why some queries with nested outer joins triggered 
  an assertion failure.
  Encapsulated this condition in the new method called
  JOIN_TAB::is_nested_inner and provided a proper code for it.
  
  Also corrected a bug in the code of check_join_cache_usage()
  that caused a downgrade of not first join buffers of the
  level 5 and 7 to level 4 and 6 correspondingly.
-------------- next part --------------
=== modified file 'mysql-test/r/join_cache.result'
--- a/mysql-test/r/join_cache.result	2010-11-16 05:07:32 +0000
+++ b/mysql-test/r/join_cache.result	2010-11-19 14:20:28 +0000
@@ -2104,7 +2104,7 @@
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BKAH join)
-1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition(BKA); Using where; Using join buffer (incremental, BKA join)
+1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition(BKA); Using where; Using join buffer (flat, BKAH join)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -3578,7 +3578,7 @@
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BKAH join)
-1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition(BKA); Using where; Using join buffer (incremental, BKA join)
+1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition(BKA); Using where; Using join buffer (flat, BKAH join)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -4661,7 +4661,7 @@
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	16384	Using where
 1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using where; Using join buffer (flat, BKAH join)
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using where; Using join buffer (incremental, BKA join)
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using where; Using join buffer (flat, BKAH join)
 SELECT COUNT(*) FROM t1,t2,t3
 WHERE t1.a=t2.a AND t2.a=t3.a AND
 t1.b IS NULL AND t2.b IS NULL AND t3.b IS NULL;
@@ -5969,4 +5969,39 @@
 SET SESSION optimizer_switch = 'outer_join_with_cache=off';
 SET SESSION join_cache_level = DEFAULT;
 DROP TABLE t1,t2,t3,t4;
+#
+# Bug #660963: nested outer join with join_cache_level set to 5
+#
+CREATE TABLE t1 (a1 int) ;
+INSERT INTO t1 VALUES (0),(0);
+CREATE TABLE t2 (a2 int, b2 int, PRIMARY KEY (a2)) ;
+INSERT INTO t2 VALUES (2,1);
+CREATE TABLE t3 (a3 int, b3 int, PRIMARY KEY (a3)) ;
+INSERT INTO t3 VALUES (2,1);
+SET SESSION optimizer_switch = 'outer_join_with_cache=on';
+SET SESSION join_cache_level = 6;
+EXPLAIN
+SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0 ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.a2	1	Using where; Using join buffer (incremental, BKA join)
+SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0 ;
+a1	a2	b2	a3	b3
+0	2	1	2	1
+0	2	1	2	1
+SET SESSION join_cache_level = 5;
+EXPLAIN
+SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0 ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.a2	1	Using where; Using join buffer (incremental, BNLH join)
+SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0 ;
+a1	a2	b2	a3	b3
+0	2	1	2	1
+0	2	1	2	1
+SET SESSION optimizer_switch = 'outer_join_with_cache=off';
+SET SESSION join_cache_level = DEFAULT;
+DROP TABLE t1,t2,t3;
 set @@optimizer_switch=@save_optimizer_switch;

=== modified file 'mysql-test/t/join_cache.test'
--- a/mysql-test/t/join_cache.test	2010-11-16 05:07:32 +0000
+++ b/mysql-test/t/join_cache.test	2010-11-19 14:20:28 +0000
@@ -2625,5 +2625,35 @@
 
 DROP TABLE t1,t2,t3,t4;
 
+--echo #
+--echo # Bug #660963: nested outer join with join_cache_level set to 5
+--echo #
+
+CREATE TABLE t1 (a1 int) ;
+INSERT INTO t1 VALUES (0),(0);
+
+CREATE TABLE t2 (a2 int, b2 int, PRIMARY KEY (a2)) ;
+INSERT INTO t2 VALUES (2,1);
+
+CREATE TABLE t3 (a3 int, b3 int, PRIMARY KEY (a3)) ;
+INSERT INTO t3 VALUES (2,1);
+
+SET SESSION optimizer_switch = 'outer_join_with_cache=on';
+
+SET SESSION join_cache_level = 6;
+EXPLAIN
+SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0 ;
+SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0 ;
+
+SET SESSION join_cache_level = 5;
+EXPLAIN
+SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0 ;
+SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0 ;
+
+SET SESSION optimizer_switch = 'outer_join_with_cache=off';
+SET SESSION join_cache_level = DEFAULT;
+
+DROP TABLE t1,t2,t3;
+
 # this must be the last command in the file
 set @@optimizer_switch=@save_optimizer_switch;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-11-16 05:07:32 +0000
+++ b/sql/sql_select.cc	2010-11-19 14:20:28 +0000
@@ -7635,8 +7635,7 @@
   if (cache_level == 0 || i == join->const_tables || !prev_tab)
     return 0;
 
-  if (force_unlinked_cache && 
-      (cache_level & JOIN_CACHE_INCREMENTAL_BIT))
+  if (force_unlinked_cache && (cache_level%2 == 0))
     cache_level--;
 
   if (options & SELECT_NO_JOIN_CACHE)
@@ -7658,13 +7657,14 @@
   /*
     Non-linked join buffers can't guarantee one match
   */
-  if ((force_unlinked_cache || cache_level == 1) &&  
-      ((tab->is_inner_table_of_semi_join_with_first_match() &&
-        !tab->is_single_inner_of_semi_join_with_first_match()) ||
-       (tab->is_inner_table_of_outer_join() &&
-        !tab->is_single_inner_of_outer_join())))
-   goto no_join_cache;
-
+  if (tab->is_nested_inner())
+  {
+    if (force_unlinked_cache || cache_level == 1)
+      goto no_join_cache;
+    if (cache_level & 1)
+      cache_level--;
+  }
+    
   /*
     Don't use join buffering if we're dictated not to by no_jbuf_after (this
     ...)
@@ -7757,9 +7757,6 @@
 	(cache_level <= 6 || no_hashed_cache))
       goto no_join_cache;
 
-    if (prev_tab->cache && cache_level==7)
-      cache_level= 6;
-    
     if ((rows != HA_POS_ERROR) && !(flags & HA_MRR_USE_DEFAULT_IMPL))
     {
       if (cache_level <= 6 || no_hashed_cache)

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2010-11-13 14:13:34 +0000
+++ b/sql/sql_select.h	2010-11-19 14:20:28 +0000
@@ -359,6 +359,19 @@
     return (first_inner && first_inner->last_inner == this) ||
            last_sj_inner_tab == this;
   }
+  /*
+    Check whether the table belongs to a nest of inner tables of an
+    outer join or to a nest of inner tables of a semi-join
+  */
+  bool is_nested_inner()
+  {
+    if (first_inner && 
+        (first_inner != first_inner->last_inner || first_inner->first_upper))
+      return TRUE;
+    if (first_sj_inner_tab && first_sj_inner_tab != last_sj_inner_tab)
+      return TRUE;
+    return FALSE;
+  }
   struct st_join_table *get_first_inner_table()
   {
     if (first_inner)



More information about the commits mailing list