[Commits] Rev 2854: Fixed LP bug #674431. in file:///home/igor/maria/maria-5.3-mwl128-bug52540/

Igor Babaev igor at askmonty.org
Sat Nov 13 16:13:35 EET 2010


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

------------------------------------------------------------
revno: 2854
revision-id: igor at askmonty.org-20101113141334-cgratzkztn1i6i0g
parent: igor at askmonty.org-20101112005908-18gtyr4if2dyqo8c
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.3-mwl128-bug52540
timestamp: Sat 2010-11-13 06:13:34 -0800
message:
  Fixed LP bug #674431.
  A non-incremental join buffer cannot be used for inner tables of nested
  outer joins. That's why when join_cache_level is set to 7 it must
  be downgraded to level 6 for the inner tables of nested outer joins.
  For the same reason with join_cache_level set to 3 no join buffer is
  used for the inner tables of outer joins (we could downgrade it to
  level 2, but this level does not support ref access).
-------------- next part --------------
=== modified file 'mysql-test/r/join_cache.result'
--- a/mysql-test/r/join_cache.result	2010-11-12 00:59:08 +0000
+++ b/mysql-test/r/join_cache.result	2010-11-13 14:13:34 +0000
@@ -870,7 +870,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, BNLH join)
-1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition; Using where
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -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 (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)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -2722,7 +2722,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, BNLH join)
-1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition; Using where
 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 (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)
 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 (flat, BKAH join)
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using where; Using join buffer (incremental, BKA 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;
@@ -5407,7 +5407,7 @@
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	16	
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer (flat, BNL join)
-1	SIMPLE	t3	ref	idx	idx	3	test.t2.u	2	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	t3	ref	idx	idx	3	test.t2.u	2	Using where
 SELECT t1.i, t1.d,  t1.v, t2.i, t2.d, t2.t, t2.v FROM t1,t2,t3
 WHERE t3.u <='a' AND t2.j < 5 AND t3.v = t2.u;
 i	d	v	i	d	t	v
@@ -5769,4 +5769,44 @@
 2	aa	42	aa
 SET SESSION join_cache_level = DEFAULT;
 DROP TABLE t1,t2;
+#
+# Bug #674431: nested outer join when join_cache_level is set to 7
+#
+CREATE TABLE t1 (a int, b varchar(32)) ;
+INSERT INTO t1 VALUES (5,'h'), (NULL,'j');
+CREATE TABLE t2 (a int, b varchar(32), c int) ;
+INSERT INTO t2 VALUES (5,'h',100), (NULL,'j',200);
+CREATE TABLE t3 (a int, b varchar(32), INDEX idx(b));
+INSERT INTO t3 VALUES (77,'h'), (88,'g');
+SET SESSION optimizer_switch = 'outer_join_with_cache=on';
+SET SESSION join_cache_level = 7;
+SELECT t3.a
+FROM t1 LEFT JOIN
+(t2 LEFT OUTER JOIN t3 ON t2.b = t3.b) ON t2.a = t1.b
+WHERE t3.a BETWEEN 3 AND 11 OR t1.a <= t2.c;
+a
+SET SESSION optimizer_switch = 'outer_join_with_cache=off';
+SET SESSION join_cache_level = DEFAULT;
+DROP TABLE t1,t2,t3;
+#
+# Bug #52540: nested outer join when join_cache_level is set to 3
+#
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (2);
+CREATE TABLE t2 (a varchar(10));
+INSERT INTO t2 VALUES ('f'),('x');
+CREATE TABLE t3 (pk int(11) PRIMARY KEY);
+INSERT INTO t3 VALUES (2);
+CREATE TABLE t4 (a varchar(10));
+SET SESSION optimizer_switch = 'outer_join_with_cache=on';
+SET SESSION join_cache_level = 3;
+SELECT *
+FROM t2 LEFT JOIN
+((t1 JOIN t3 ON t1.a = t3.pk) LEFT JOIN t4 ON 1) ON 1;
+a	a	pk	a
+f	2	2	NULL
+x	2	2	NULL
+SET SESSION optimizer_switch = 'outer_join_with_cache=off';
+SET SESSION join_cache_level = DEFAULT;
+DROP TABLE t1,t2,t3,t4;
 set @@optimizer_switch=@save_optimizer_switch;

=== modified file 'mysql-test/t/join_cache.test'
--- a/mysql-test/t/join_cache.test	2010-11-12 00:59:08 +0000
+++ b/mysql-test/t/join_cache.test	2010-11-13 14:13:34 +0000
@@ -2448,5 +2448,52 @@
 
 DROP TABLE t1,t2;
 
+--echo #
+--echo # Bug #674431: nested outer join when join_cache_level is set to 7
+--echo #
+
+CREATE TABLE t1 (a int, b varchar(32)) ;
+INSERT INTO t1 VALUES (5,'h'), (NULL,'j');
+CREATE TABLE t2 (a int, b varchar(32), c int) ;
+INSERT INTO t2 VALUES (5,'h',100), (NULL,'j',200);
+CREATE TABLE t3 (a int, b varchar(32), INDEX idx(b));
+INSERT INTO t3 VALUES (77,'h'), (88,'g');
+
+SET SESSION optimizer_switch = 'outer_join_with_cache=on';
+SET SESSION join_cache_level = 7;
+SELECT t3.a
+  FROM t1 LEFT JOIN
+       (t2 LEFT OUTER JOIN t3 ON t2.b = t3.b) ON t2.a = t1.b
+    WHERE t3.a BETWEEN 3 AND 11 OR t1.a <= t2.c;
+
+SET SESSION optimizer_switch = 'outer_join_with_cache=off';
+SET SESSION join_cache_level = DEFAULT;
+
+DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # Bug #52540: nested outer join when join_cache_level is set to 3
+--echo #
+
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (2);
+CREATE TABLE t2 (a varchar(10));
+INSERT INTO t2 VALUES ('f'),('x');
+CREATE TABLE t3 (pk int(11) PRIMARY KEY);
+INSERT INTO t3 VALUES (2);
+CREATE TABLE t4 (a varchar(10));
+
+SET SESSION optimizer_switch = 'outer_join_with_cache=on';
+SET SESSION join_cache_level = 3;
+
+SELECT *
+       FROM t2 LEFT JOIN
+           ((t1 JOIN t3 ON t1.a = t3.pk) LEFT JOIN t4 ON 1) ON 1;
+
+SET SESSION optimizer_switch = 'outer_join_with_cache=off';
+SET SESSION join_cache_level = DEFAULT;
+
+DROP TABLE t1,t2,t3,t4;
+
 # 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-12 00:38:55 +0000
+++ b/sql/sql_select.cc	2010-11-13 14:13:34 +0000
@@ -7634,6 +7634,10 @@
   if (cache_level == 0 || i == join->const_tables || !prev_tab)
     return 0;
 
+  if (force_unlinked_cache && 
+      (cache_level & JOIN_CACHE_INCREMENTAL_BIT))
+    cache_level--;
+
   if (options & SELECT_NO_JOIN_CACHE)
     goto no_join_cache;
   /* 
@@ -7732,7 +7736,11 @@
           tab->make_scan_filter())
         goto no_join_cache;
       if (cache_level == 3)
+      {
+        if (prev_tab->cache)
+          goto no_join_cache;
         prev_cache= 0;
+      }
       if ((tab->cache= new JOIN_CACHE_BNLH(join, tab, prev_cache)) &&
           ((options & SELECT_DESCRIBE) || !tab->cache->init()))
       {
@@ -7747,6 +7755,9 @@
     if ((flags & HA_MRR_NO_ASSOCIATION) &&
 	(cache_level <= 6 || no_hashed_cache))
       goto no_join_cache;
+
+    if (prev_tab->cache and cache_level==7)
+      cache_level= 6;
     
     if ((rows != HA_POS_ERROR) && !(flags & HA_MRR_USE_DEFAULT_IMPL))
     {

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2010-11-07 23:19:30 +0000
+++ b/sql/sql_select.h	2010-11-13 14:13:34 +0000
@@ -398,6 +398,10 @@
 } JOIN_TAB;
 
 
+#define JOIN_CACHE_INCREMENTAL_BIT           1
+#define JOIN_CACHE_HASHED_BIT                2
+#define JOIN_CACHE_BKA_BIT                   4
+
 /* 
   Categories of data fields of variable length written into join cache buffers.
   The value of any of these fields is written into cache together with the



More information about the commits mailing list