[Commits] Rev 2855: Merge in file:///home/igor/maria/maria-5.3-mwl128/

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


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

------------------------------------------------------------
revno: 2855 [merge]
revision-id: igor at askmonty.org-20101113143554-g1q7317b61h4ced5
parent: igor at askmonty.org-20101113005320-y4cgp5huw5vo9u2b
parent: igor at askmonty.org-20101113141334-cgratzkztn1i6i0g
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.3-mwl128
timestamp: Sat 2010-11-13 06:35:54 -0800
message:
  Merge
modified:
  mysql-test/r/join_cache.result join_cache.result-20091221012827-jfu65h0x5bmixhh3-1
  mysql-test/t/join_cache.test   join_cache.test-20091221012705-n3szmbc9blgmmu84-1
  sql/sql_select.cc              sp1f-sql_select.cc-19700101030959-egb7whpkh76zzvikycs5nsnuviu4fdlb
  sql/sql_select.h               sp1f-sql_select.h-19700101030959-oqegfxr76xlgmrzd6qlevonoibfnwzoz
-------------- 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