From 94cfcbd9dffdfe5e38dd29c542738de85beaeb06 Mon Sep 17 00:00:00 2001
From: Igor Babaev <igor@askmonty.org>
Date: Tue, 31 Aug 2010 09:34:21 -0700
Subject: The main patch for MWL#128: Implement Block Nested Loop Hash Join.
---
mysql-test/t/join_cache.test | 259 +++++++++++++++++++++++++----------
mysql-test/t/join_nested_jcl6.test | 6 +
mysql-test/t/join_outer_jcl6.test | 6 +
mysql-test/t/select_jcl6.test | 6 +
mysql-test/t/subselect3.test | 15 +-
mysql-test/t/subselect3_jcl6.test | 6 +
mysql-test/t/subselect_sj.test | 14 +-
mysql-test/t/subselect_sj2_jcl6.test | 7 +
mysql-test/t/subselect_sj_jcl6.test | 6 +
9 files changed, 241 insertions(+), 84 deletions(-)
mode change 100644 => 100755 mysql-test/t/join_cache.test
mode change 100644 => 100755 mysql-test/t/join_nested_jcl6.test
mode change 100644 => 100755 mysql-test/t/join_outer_jcl6.test
mode change 100644 => 100755 mysql-test/t/select_jcl6.test
mode change 100644 => 100755 mysql-test/t/subselect3.test
mode change 100644 => 100755 mysql-test/t/subselect3_jcl6.test
mode change 100644 => 100755 mysql-test/t/subselect_sj.test
mode change 100644 => 100755 mysql-test/t/subselect_sj2_jcl6.test
mode change 100644 => 100755 mysql-test/t/subselect_sj_jcl6.test
(limited to 'mysql-test/t')
diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test
old mode 100644
new mode 100755
index 0cb1c139161..68693fe70eb
--- a/mysql-test/t/join_cache.test
+++ b/mysql-test/t/join_cache.test
@@ -3,6 +3,10 @@ DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11;
DROP DATABASE IF EXISTS world;
--enable_warnings
+set @save_optimizer_switch=@@optimizer_switch;
+set @@optimizer_switch='semijoin_with_cache=on';
+set @@optimizer_switch='outer_join_with_cache=on';
+
set names utf8;
CREATE DATABASE world;
@@ -160,7 +164,7 @@ use world;
--enable_query_log
show variables like 'join_buffer_size';
-set join_cache_level=5;
+set join_cache_level=3;
show variables like 'join_cache_level';
EXPLAIN
@@ -187,14 +191,55 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
---echo # !!!NB igor: after backporting the SJ code the following should return
---echo # EXPLAIN
---echo # SELECT Name FROM City
---echo # WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
---echo # City.Population > 100000;
---echo # id select_type table type possible_keys key key_len ref rows Extra
---echo # 1 PRIMARY Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR
---echo # 1 PRIMARY City ref Population,Country Country 3 world.Country.Code 18 Using where; Using join buffer
+EXPLAIN
+SELECT Name FROM City
+ WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
+ City.Population > 100000;
+
+SELECT Name FROM City
+ WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
+ City.Population > 100000;
+
+EXPLAIN
+SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
+ FROM Country LEFT JOIN CountryLanguage ON
+ (CountryLanguage.Country=Country.Code AND Language='English')
+ WHERE
+ Country.Population > 10000000;
+
+SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
+ FROM Country LEFT JOIN CountryLanguage ON
+ (CountryLanguage.Country=Country.Code AND Language='English')
+ WHERE
+ Country.Population > 10000000;
+
+show variables like 'join_buffer_size';
+set join_cache_level=4;
+show variables like 'join_cache_level';
+
+EXPLAIN
+SELECT City.Name, Country.Name FROM City,Country
+ WHERE City.Country=Country.Code AND
+ Country.Name LIKE 'L%' AND City.Population > 100000;
+
+SELECT City.Name, Country.Name FROM City,Country
+ WHERE City.Country=Country.Code AND
+ Country.Name LIKE 'L%' AND City.Population > 100000;
+
+EXPLAIN
+SELECT City.Name, Country.Name, CountryLanguage.Language
+ FROM City,Country,CountryLanguage
+ WHERE City.Country=Country.Code AND
+ CountryLanguage.Country=Country.Code AND
+ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
+ CountryLanguage.Percentage > 50;
+
+SELECT City.Name, Country.Name, CountryLanguage.Language
+ FROM City,Country,CountryLanguage
+ WHERE City.Country=Country.Code AND
+ CountryLanguage.Country=Country.Code AND
+ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
+ CountryLanguage.Percentage > 50;
EXPLAIN
SELECT Name FROM City
@@ -218,7 +263,9 @@ SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.P
WHERE
Country.Population > 10000000;
-set join_cache_level=6;
+
+show variables like 'join_buffer_size';
+set join_cache_level=5;
show variables like 'join_cache_level';
EXPLAIN
@@ -245,14 +292,54 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
---echo # !!!NB igor: after backporting the SJ code the following should return
---echo # EXPLAIN
---echo # SELECT Name FROM City
---echo # WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
---echo # City.Population > 100000;
---echo # id select_type table type possible_keys key key_len ref rows Extra
---echo # 1 PRIMARY Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR
---echo # 1 PRIMARY City ref Population,Country Country 3 world.Country.Code 18 Using where; Using join buffer
+EXPLAIN
+SELECT Name FROM City
+ WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
+ City.Population > 100000;
+
+SELECT Name FROM City
+ WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
+ City.Population > 100000;
+
+EXPLAIN
+SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
+ FROM Country LEFT JOIN CountryLanguage ON
+ (CountryLanguage.Country=Country.Code AND Language='English')
+ WHERE
+ Country.Population > 10000000;
+
+SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
+ FROM Country LEFT JOIN CountryLanguage ON
+ (CountryLanguage.Country=Country.Code AND Language='English')
+ WHERE
+ Country.Population > 10000000;
+
+set join_cache_level=6;
+show variables like 'join_cache_level';
+
+EXPLAIN
+SELECT City.Name, Country.Name FROM City,Country
+ WHERE City.Country=Country.Code AND
+ Country.Name LIKE 'L%' AND City.Population > 100000;
+
+SELECT City.Name, Country.Name FROM City,Country
+ WHERE City.Country=Country.Code AND
+ Country.Name LIKE 'L%' AND City.Population > 100000;
+
+EXPLAIN
+SELECT City.Name, Country.Name, CountryLanguage.Language
+ FROM City,Country,CountryLanguage
+ WHERE City.Country=Country.Code AND
+ CountryLanguage.Country=Country.Code AND
+ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
+ CountryLanguage.Percentage > 50;
+
+SELECT City.Name, Country.Name, CountryLanguage.Language
+ FROM City,Country,CountryLanguage
+ WHERE City.Country=Country.Code AND
+ CountryLanguage.Country=Country.Code AND
+ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
+ CountryLanguage.Percentage > 50;
EXPLAIN
SELECT Name FROM City
@@ -303,15 +390,6 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
---echo # !!!NB igor: after backporting the SJ code the following should return
---echo # EXPLAIN
---echo # SELECT Name FROM City
---echo # WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
---echo # City.Population > 100000;
---echo # id select_type table type possible_keys key key_len ref rows Extra
---echo # 1 PRIMARY Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR
---echo # 1 PRIMARY City ref Population,Country Country 3 world.Country.Code 18 Using where; Using join buffer
-
EXPLAIN
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
@@ -361,15 +439,6 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
---echo # !!!NB igor: after backporting the SJ code the following should return
---echo # EXPLAIN
---echo # SELECT Name FROM City
---echo # WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
---echo # City.Population > 100000;
---echo # id select_type table type possible_keys key key_len ref rows Extra
---echo # 1 PRIMARY Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR
---echo # 1 PRIMARY City ref Population,Country Country 3 world.Country.Code 18 Using where; Using join buffer
-
EXPLAIN
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
@@ -395,7 +464,7 @@ SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.P
set join_buffer_size=256;
show variables like 'join_buffer_size';
-set join_cache_level=5;
+set join_cache_level=3;
show variables like 'join_cache_level';
EXPLAIN
@@ -422,14 +491,41 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
---echo # !!!NB igor: after backporting the SJ code the following should return
---echo # EXPLAIN
---echo # SELECT Name FROM City
---echo # WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
---echo # City.Population > 100000;
---echo # id select_type table type possible_keys key key_len ref rows Extra
---echo # 1 PRIMARY Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR
---echo # 1 PRIMARY City ref Population,Country Country 3 world.Country.Code 18 Using where; Using join buffer
+EXPLAIN
+SELECT Name FROM City
+ WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
+ City.Population > 100000;
+
+SELECT Name FROM City
+ WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
+ City.Population > 100000;
+
+set join_cache_level=4;
+show variables like 'join_cache_level';
+
+EXPLAIN
+SELECT City.Name, Country.Name FROM City,Country
+ WHERE City.Country=Country.Code AND
+ Country.Name LIKE 'L%' AND City.Population > 100000;
+
+SELECT City.Name, Country.Name FROM City,Country
+ WHERE City.Country=Country.Code AND
+ Country.Name LIKE 'L%' AND City.Population > 100000;
+
+EXPLAIN
+SELECT City.Name, Country.Name, CountryLanguage.Language
+ FROM City,Country,CountryLanguage
+ WHERE City.Country=Country.Code AND
+ CountryLanguage.Country=Country.Code AND
+ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
+ CountryLanguage.Percentage > 50;
+
+SELECT City.Name, Country.Name, CountryLanguage.Language
+ FROM City,Country,CountryLanguage
+ WHERE City.Country=Country.Code AND
+ CountryLanguage.Country=Country.Code AND
+ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
+ CountryLanguage.Percentage > 50;
EXPLAIN
SELECT Name FROM City
@@ -440,7 +536,7 @@ SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
-set join_cache_level=6;
+set join_cache_level=5;
show variables like 'join_cache_level';
EXPLAIN
@@ -467,14 +563,41 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
---echo # !!!NB igor: after backporting the SJ code the following should return
---echo # EXPLAIN
---echo # SELECT Name FROM City
---echo # WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
---echo # City.Population > 100000;
---echo # id select_type table type possible_keys key key_len ref rows Extra
---echo # 1 PRIMARY Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR
---echo # 1 PRIMARY City ref Population,Country Country 3 world.Country.Code 18 Using where; Using join buffer
+EXPLAIN
+SELECT Name FROM City
+ WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
+ City.Population > 100000;
+
+SELECT Name FROM City
+ WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
+ City.Population > 100000;
+
+set join_cache_level=6;
+show variables like 'join_cache_level';
+
+EXPLAIN
+SELECT City.Name, Country.Name FROM City,Country
+ WHERE City.Country=Country.Code AND
+ Country.Name LIKE 'L%' AND City.Population > 100000;
+
+SELECT City.Name, Country.Name FROM City,Country
+ WHERE City.Country=Country.Code AND
+ Country.Name LIKE 'L%' AND City.Population > 100000;
+
+EXPLAIN
+SELECT City.Name, Country.Name, CountryLanguage.Language
+ FROM City,Country,CountryLanguage
+ WHERE City.Country=Country.Code AND
+ CountryLanguage.Country=Country.Code AND
+ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
+ CountryLanguage.Percentage > 50;
+
+SELECT City.Name, Country.Name, CountryLanguage.Language
+ FROM City,Country,CountryLanguage
+ WHERE City.Country=Country.Code AND
+ CountryLanguage.Country=Country.Code AND
+ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
+ CountryLanguage.Percentage > 50;
EXPLAIN
SELECT Name FROM City
@@ -512,15 +635,6 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
---echo # !!!NB igor: after backporting the SJ code the following should return
---echo # EXPLAIN
---echo # SELECT Name FROM City
---echo # WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
---echo # City.Population > 100000;
---echo # id select_type table type possible_keys key key_len ref rows Extra
---echo # 1 PRIMARY Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR
---echo # 1 PRIMARY City ref Population,Country Country 3 world.Country.Code 18 Using where; Using join buffer
-
EXPLAIN
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
@@ -557,15 +671,6 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
---echo # !!!NB igor: after backporting the SJ code the following should return
---echo # EXPLAIN
---echo # SELECT Name FROM City
---echo # WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
---echo # City.Population > 100000;
---echo # id select_type table type possible_keys key key_len ref rows Extra
---echo # 1 PRIMARY Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR
---echo # 1 PRIMARY City ref Population,Country Country 3 world.Country.Code 18 Using where; Using join buffer
-
EXPLAIN
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
@@ -587,7 +692,7 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND City.Population > 3000000;
set join_cache_level=8;
-set join_buffer_size=256;
+set join_buffer_size=384;
--replace_column 9 #
EXPLAIN
@@ -997,7 +1102,7 @@ select * from t1 left join t2 on (1=0);
explain select * from t1 left join t2 on (1=0) where a=40;
select * from t1 left join t2 on (1=0) where a=40;
-set join_cache_level=1;
+set join_cache_level=0;
explain select * from t1 left join t2 on (1=0);
set join_cache_level=default;
@@ -1131,6 +1236,8 @@ INSERT INTO t3(a,b) VALUES
(5,30), (5,40), (5,50), (5,60), (5,70), (5,80),
(7,30), (7,40), (7,50), (7,60), (7,70), (7,80);
+set join_cache_level=0;
+
SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val
FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b)
WHERE t1.a=t2.a;
@@ -1148,7 +1255,7 @@ SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val
WHERE t1.a=t2.a;
DROP INDEX idx ON t3;
-set join_cache_level=4;
+set join_cache_level=2;
EXPLAIN
SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val
@@ -1847,3 +1954,5 @@ select t1.* from t1,t2,t3;
set join_cache_level=default;
drop table t1,t2,t3;
+
+set @@optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/t/join_nested_jcl6.test b/mysql-test/t/join_nested_jcl6.test
old mode 100644
new mode 100755
index caa656ecd87..5737cfe115f
--- a/mysql-test/t/join_nested_jcl6.test
+++ b/mysql-test/t/join_nested_jcl6.test
@@ -2,6 +2,10 @@
# Run join_nested.test with BKA enabled
#
+set @save_optimizer_switch_jcl6=@@optimizer_switch;
+set @@optimizer_switch='semijoin_with_cache=on';
+set @@optimizer_switch='outer_join_with_cache=on';
+
set join_cache_level=6;
show variables like 'join_cache_level';
@@ -93,3 +97,5 @@ DROP TABLE t5,t6,t7,t8;
set join_cache_level=default;
show variables like 'join_cache_level';
+
+set @@optimizer_switch=@save_optimizer_switch_jcl6;
diff --git a/mysql-test/t/join_outer_jcl6.test b/mysql-test/t/join_outer_jcl6.test
old mode 100644
new mode 100755
index 16543296f27..be98e7503ad
--- a/mysql-test/t/join_outer_jcl6.test
+++ b/mysql-test/t/join_outer_jcl6.test
@@ -2,6 +2,10 @@
# Run join_outer.test with BKA enabled
#
+set @save_optimizer_switch_jcl6=@@optimizer_switch;
+set @@optimizer_switch='semijoin_with_cache=on';
+set @@optimizer_switch='outer_join_with_cache=on';
+
set join_cache_level=6;
show variables like 'join_cache_level';
@@ -9,3 +13,5 @@ show variables like 'join_cache_level';
set join_cache_level=default;
show variables like 'join_cache_level';
+
+set @@optimizer_switch=@save_optimizer_switch_jcl6;
diff --git a/mysql-test/t/select_jcl6.test b/mysql-test/t/select_jcl6.test
old mode 100644
new mode 100755
index 3247ab6e343..9f9a3a40e0f
--- a/mysql-test/t/select_jcl6.test
+++ b/mysql-test/t/select_jcl6.test
@@ -2,6 +2,10 @@
# Run select.test with BKA enabled
#
+set @save_optimizer_switch_jcl6=@@optimizer_switch;
+set @@optimizer_switch='semijoin_with_cache=on';
+set @@optimizer_switch='outer_join_with_cache=on';
+
set join_cache_level=6;
show variables like 'join_cache_level';
@@ -9,3 +13,5 @@ show variables like 'join_cache_level';
set join_cache_level=default;
show variables like 'join_cache_level';
+
+set @@optimizer_switch=@save_optimizer_switch_jcl6;
diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test
old mode 100644
new mode 100755
index a621c4c776c..2a5aa1c5429
--- a/mysql-test/t/subselect3.test
+++ b/mysql-test/t/subselect3.test
@@ -2,6 +2,8 @@
drop table if exists t0, t1, t2, t3, t4, t5, t11, t12, t21, t22;
--enable_warnings
+set @save_optimizer_switch=@@optimizer_switch;
+
#
# 1. Subquery with GROUP/HAVING
#
@@ -888,7 +890,7 @@ set @@optimizer_switch='firstmatch=off';
explain
select (select max(Y.a) from t1 Y where a in (select a from t1 Z) and a < X.a) as subq from t1 X;
select (select max(Y.a) from t1 Y where a in (select a from t1 Z) and a < X.a) as subq from t1 X;
-set @@optimizer_switch=default;
+set @@optimizer_switch=@save_optimizer_switch;
drop table t1;
@@ -906,7 +908,7 @@ select * from t1 where 2 in (select a from t0);
set @@optimizer_switch='default,materialization=off';
explain select * from t1 where 2 in (select a from t0);
select * from t1 where 2 in (select a from t0);
-set @@optimizer_switch=default;
+set @@optimizer_switch=@save_optimizer_switch;
#
@@ -952,7 +954,7 @@ flush status;
select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E);
show status like 'Created_tmp_disk_tables';
set @save_max_heap_table_size=@@max_heap_table_size;
-set @@optimizer_switch=default;
+set @@optimizer_switch=@save_optimizer_switch;
drop table t0, t1;
#
@@ -990,7 +992,7 @@ create table t1 (a decimal);
insert into t1 values (1),(2);
explain select * from t1 where a in (select a from t1);
drop table t1;
-set @@optimizer_switch=default;
+set @@optimizer_switch=@save_optimizer_switch;
#
# SJ-Materialization-scan for non-first table
@@ -1051,7 +1053,7 @@ set @save_optimizer_search_depth=@@optimizer_search_depth;
set @@optimizer_search_depth=63;
explain select * from t1 where (a,b) in (select a,b from t2);
set @@optimizer_search_depth=@save_optimizer_search_depth;
-set @@optimizer_switch=default;
+set @@optimizer_switch=@save_optimizer_switch;
drop table t0, t1, t2;
@@ -1181,3 +1183,6 @@ ALTER TABLE t2 CHANGE COLUMN f1 my_column INT;
CALL p1;
DROP PROCEDURE p1;
DROP TABLE t1, t2;
+
+# The following command must be the last one the file
+set @@optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/t/subselect3_jcl6.test b/mysql-test/t/subselect3_jcl6.test
old mode 100644
new mode 100755
index 9ee23288d99..6d9611f83f3
--- a/mysql-test/t/subselect3_jcl6.test
+++ b/mysql-test/t/subselect3_jcl6.test
@@ -2,6 +2,10 @@
# Run subselect3.test with BKA enabled
#
+set @save_optimizer_switch=@@optimizer_switch;
+set @@optimizer_switch='semijoin_with_cache=on';
+set @@optimizer_switch='outer_join_with_cache=on';
+
set join_cache_level=6;
show variables like 'join_cache_level';
@@ -9,3 +13,5 @@ show variables like 'join_cache_level';
set join_cache_level=default;
show variables like 'join_cache_level';
+
+set @@optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test
old mode 100644
new mode 100755
index 33f3e936482..783429ed836
--- a/mysql-test/t/subselect_sj.test
+++ b/mysql-test/t/subselect_sj.test
@@ -5,6 +5,8 @@
drop table if exists t0, t1, t2, t3, t4, t10, t11, t12;
--enable_warnings
+set @save_optimizer_switch=@@optimizer_switch;
+
#
# 1. Subqueries that are converted into semi-joins
#
@@ -224,7 +226,8 @@ INSERT INTO WORKS VALUES ('E4','P2',20);
INSERT INTO WORKS VALUES ('E4','P4',40);
INSERT INTO WORKS VALUES ('E4','P5',80);
-set optimizer_switch='default,materialization=off';
+set optimizer_switch=@save_optimizer_switch;
+set optimizer_switch='materialization=off';
explain SELECT EMPNUM, EMPNAME
FROM STAFF
@@ -240,7 +243,7 @@ WHERE EMPNUM IN
WHERE PNUM IN
(SELECT PNUM FROM PROJ));
-set optimizer_switch='default';
+set optimizer_switch=@save_optimizer_switch;
drop table STAFF,WORKS,PROJ;
@@ -359,7 +362,7 @@ drop table t1, t2;
drop view v1;
drop procedure p1;
-set SESSION optimizer_switch='default';
+set SESSION optimizer_switch=@save_optimizer_switch;
--echo # End of bug#46744
@@ -526,7 +529,7 @@ DROP TABLE t1,t2;
DROP VIEW v1,v2;
DROP PROCEDURE p1;
-set SESSION optimizer_switch='default';
+set SESSION optimizer_switch=@save_optimizer_switch;
--echo # End of BUG#48834
@@ -935,3 +938,6 @@ DROP TABLE t2;
DROP TABLE t3;
--echo # End of Bug#48623
+
+# The following command must be the last one the file
+set @@optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/t/subselect_sj2_jcl6.test b/mysql-test/t/subselect_sj2_jcl6.test
old mode 100644
new mode 100755
index 202ea139e5f..5292a56f266
--- a/mysql-test/t/subselect_sj2_jcl6.test
+++ b/mysql-test/t/subselect_sj2_jcl6.test
@@ -2,6 +2,10 @@
# Run subselect_sj2.test with BKA enabled
#
+set @save_optimizer_switch_jcl6=@@optimizer_switch;
+set @@optimizer_switch='semijoin_with_cache=on';
+set @@optimizer_switch='outer_join_with_cache=on';
+
set join_cache_level=6;
show variables like 'join_cache_level';
@@ -9,3 +13,6 @@ show variables like 'join_cache_level';
set join_cache_level=default;
show variables like 'join_cache_level';
+
+set @@optimizer_switch=@save_optimizer_switch_jcl6;
+
diff --git a/mysql-test/t/subselect_sj_jcl6.test b/mysql-test/t/subselect_sj_jcl6.test
old mode 100644
new mode 100755
index f821e1864be..27c864fc699
--- a/mysql-test/t/subselect_sj_jcl6.test
+++ b/mysql-test/t/subselect_sj_jcl6.test
@@ -2,6 +2,10 @@
# Run subselect_sj.test with BKA enabled
#
+set @save_optimizer_switch_jcl6=@@optimizer_switch;
+set @@optimizer_switch='semijoin_with_cache=on';
+set @@optimizer_switch='outer_join_with_cache=on';
+
set join_cache_level=6;
show variables like 'join_cache_level';
@@ -37,3 +41,5 @@ drop table t0, t1, t2;
set join_cache_level=default;
show variables like 'join_cache_level';
+
+set @@optimizer_switch=@save_optimizer_switch_jcl6;
--
cgit v1.2.1
From f4503f39ee09a11d3b5f35eb0290d59e5d0e902b Mon Sep 17 00:00:00 2001
From: Igor Babaev <igor@askmonty.org>
Date: Tue, 21 Sep 2010 16:41:53 -0700
Subject: Fixed bug #52394 / LP bug #623209. When an incremental join cache is
used to join a table whose fields are not referenced anywhere in the query
the association pointer to the last record in the such cache can be the same
as the pointer to the end of the buffer. The function
JOIN_CACHE_BKA::get_next_key must take into consideration this when iterating
over the keys of the records from the join buffer. The assertion in
JOIN_TAB_SCAN_MRR::next also must take this into consideration. Borrowed a
slightly changed test case from a patch attached to the bug #52394.
---
mysql-test/t/join_cache.test | 28 ++++++++++++++++++++++++++++
1 file changed, 28 insertions(+)
(limited to 'mysql-test/t')
diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test
index 68693fe70eb..7bc22fd4f23 100755
--- a/mysql-test/t/join_cache.test
+++ b/mysql-test/t/join_cache.test
@@ -1956,3 +1956,31 @@ set join_cache_level=default;
drop table t1,t2,t3;
set @@optimizer_switch=@save_optimizer_switch;
+
+--echo #
+--echo # Bug #52394: using join buffer for 3 table join with ref access
+--echo # LP #623209: and no references to the columns of the middle table
+--echo #
+
+
+set join_cache_level=6;
+
+CREATE TABLE t1 (a int(11), b varchar(1));
+INSERT INTO t1 VALUES (6,'r'),(27,'o');
+
+CREATE TABLE t2(a int);
+INSERT INTO t2 VALUES(1),(2),(3),(4),(5);
+
+CREATE TABLE t3 (a int(11) primary key, b varchar(1));
+INSERT INTO t3 VALUES
+(14,'d'),(15,'z'),(16,'e'),(17,'h'),(18,'b'),(19,'s'),(20,'e'),
+(21,'j'),(22,'e'),(23,'f'),(24,'v'),(25,'x'),(26,'m'),(27,'o');
+
+EXPLAIN
+SELECT t3.a FROM t1,t2,t3 WHERE t1.a = t3.a AND t1.b = t3.b;
+SELECT t3.a FROM t1,t2,t3 WHERE t1.a = t3.a AND t1.b = t3.b;
+
+DROP TABLE t1,t2,t3;
+
+set join_cache_level=default;
+
--
cgit v1.2.1
From db37f2db82a8fc58439dcd5e554df3ca33380461 Mon Sep 17 00:00:00 2001
From: Igor Babaev <igor@askmonty.org>
Date: Thu, 23 Sep 2010 11:25:45 -0700
Subject: Applied the fix for bug #51084 taken from a mysql tree.
---
mysql-test/t/join_cache.test | 48 ++++++++++++++++++++++++++++++++++++++++++--
1 file changed, 46 insertions(+), 2 deletions(-)
(limited to 'mysql-test/t')
diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test
index 7bc22fd4f23..f65f521f21a 100755
--- a/mysql-test/t/join_cache.test
+++ b/mysql-test/t/join_cache.test
@@ -1955,8 +1955,6 @@ set join_cache_level=default;
drop table t1,t2,t3;
-set @@optimizer_switch=@save_optimizer_switch;
-
--echo #
--echo # Bug #52394: using join buffer for 3 table join with ref access
--echo # LP #623209: and no references to the columns of the middle table
@@ -1984,3 +1982,49 @@ DROP TABLE t1,t2,t3;
set join_cache_level=default;
+--echo #
+--echo # Bug#51084: Batched key access crashes for SELECT with
+--echo # derived table and LEFT JOIN
+--echo #
+
+CREATE TABLE t1 (
+ carrier int,
+ id int PRIMARY KEY
+);
+INSERT INTO t1 VALUES (1,11),(1,12),(2,13);
+
+CREATE TABLE t2 (
+ scan_date int,
+ package_id int
+);
+INSERT INTO t2 VALUES (2008,21),(2008,22);
+
+CREATE TABLE t3 (
+ carrier int PRIMARY KEY,
+ id int
+);
+INSERT INTO t3 VALUES (1,31);
+
+CREATE TABLE t4 (
+ carrier_id int,
+ INDEX carrier_id(carrier_id)
+);
+INSERT INTO t4 VALUES (31),(32);
+
+SET join_cache_level=8;
+
+SELECT COUNT(*)
+ FROM (t2 JOIN t1) LEFT JOIN (t3 JOIN t4 ON t3.id = t4.carrier_id)
+ ON t3.carrier = t1.carrier;
+
+EXPLAIN
+SELECT COUNT(*)
+ FROM (t2 JOIN t1) LEFT JOIN (t3 JOIN t4 ON t3.id = t4.carrier_id)
+ ON t3.carrier = t1.carrier;
+
+SET 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;
--
cgit v1.2.1
From 21b1b5f0405f541bae2cda8882cd16696d39a4fd Mon Sep 17 00:00:00 2001
From: Igor Babaev <igor@askmonty.org>
Date: Tue, 28 Sep 2010 12:39:33 -0700
Subject: Fixed bug #52636. Applied the fix for bug #47217 from the mysql-6.0
codebase. The patch adds not null predicates generated for the left parts of
the equality predicates used for ref accesses. This is done for such
predicates both in where conditions and on conditions. For the where
conditions the not null predicates were generated but in 5.0/5.1 they
actually never were used due to some lame merge from 4.1 to 5.0. The fix for
bug #47217 made these predicates to be used in the condition pushed to the
tables. Yet only this patch generates not null predicates for equality
predicated from on conditions of outer joins. This patch introduces a
performance regression that can be observed on a test case from
null_key.test. The regression will disappear after the fix for bug #57024
from mariadb-5.1 is pulled into mariadb-5.3. The patch contains many changes
in the outputs of the EXPLAIN commands since generated not null predicates
are considered as parts of the conditions pushed to join tables and may add
'Usingwhere' in some rows of EXPLAINs where there used to be no such
comments.
---
mysql-test/t/join.test | 19 +++++++++++++++++
mysql-test/t/join_cache.test | 50 ++++++++++++++++++++++++++++++++++++++++++++
2 files changed, 69 insertions(+)
(limited to 'mysql-test/t')
diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test
index 43b373c9703..cb865528aa2 100644
--- a/mysql-test/t/join.test
+++ b/mysql-test/t/join.test
@@ -851,4 +851,23 @@ ENGINE=MERGE UNION=(t1,t2);
SELECT t1.a FROM mm1,t1;
DROP TABLE t1, t2, mm1;
+--echo #
+--echo # BUG#47217 Lost optimization caused slowdown & wrong result.
+--echo #
+CREATE TABLE t1 (pk INT, v VARCHAR(2), PRIMARY KEY(pk));
+CREATE INDEX ix1 ON t1(v);
+CREATE TABLE t2 (pk INT, v VARCHAR(2), PRIMARY KEY(pk));
+CREATE INDEX ix2 ON t2(v);
+INSERT INTO t1 VALUES (1,'a'),(2,NULL);
+INSERT INTO t2 VALUES (1,NULL);
+EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.v = t2.v ORDER BY 1;
+EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.v = t2.v;
+INSERT INTO t1 VALUES (3,'b'),(4,NULL),(5,'c'),(6,'cc'),(7,'d'),
+ (8,'dd'),(9,'e'),(10,'ee');
+INSERT INTO t2 VALUES (2,NULL);
+FLUSH STATUS;
+SELECT * FROM t1 JOIN t2 ON t1.v = t2.v WHERE t2.v IS NULL ORDER BY 1;
+SHOW STATUS LIKE 'Handler_read_%';
+DROP TABLE t1, t2;
+
--echo End of 5.1 tests
diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test
index f65f521f21a..a71db1081a1 100755
--- a/mysql-test/t/join_cache.test
+++ b/mysql-test/t/join_cache.test
@@ -2026,5 +2026,55 @@ SET join_cache_level=default;
DROP TABLE t1,t2,t3,t4;
+--echo #
+--echo # BUG#52636: allowing JOINs on NULL values w/ join_cache_level = 5-8
+--echo #
+
+CREATE TABLE t1 (b int);
+INSERT INTO t1 VALUES (NULL),(3);
+
+CREATE TABLE t2 (a int, b int, KEY (b));
+INSERT INTO t2 VALUES (100,NULL),(150,200);
+
+set join_cache_level = 5;
+explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b;
+--sorted_result
+SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b;
+
+set join_cache_level = 8;
+explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b;
+--sorted_result
+SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b;
+
+# test crash when no key is worth collecting by BKA for t2's ref
+delete from t1;
+INSERT INTO t1 VALUES (NULL),(NULL);
+set join_cache_level = 5;
+explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b;
+--sorted_result
+SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b;
+
+DROP TABLE t1,t2;
+
+# test varchar keys
+CREATE TABLE t1 (b varchar(100));
+INSERT INTO t1 VALUES (NULL),("some varchar");
+
+CREATE TABLE t2 (a int, b varchar(100), KEY (b));
+INSERT INTO t2 VALUES (100,NULL),(150,"varchar"),(200,NULL),(250,"long long varchar");
+
+set join_cache_level = 5;
+explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b;
+--sorted_result
+SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b;
+
+set join_cache_level = 8;
+explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b;
+--sorted_result
+SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b;
+
+set join_cache_level = default;
+DROP TABLE t1,t2;
+
# this must be the last command in the file
set @@optimizer_switch=@save_optimizer_switch;
--
cgit v1.2.1
From 1320f6073c24c0e2a12fe1599a1fbab7d953968c Mon Sep 17 00:00:00 2001
From: Igor Babaev <igor@sophia>
Date: Fri, 1 Oct 2010 10:08:10 -0700
Subject: Fixed bug #54539. Added a possibility not to factor out the condition
pushed to the access index out of the condition pushed to a joined table.
This is useful for the condition pushed to the index when a hashed join
buffer for BKA is employed. In this case the index condition may be false for
some, but for all records with the same key. So the condition must be checked
not only after index lookup, but after fetching row data as well, and it
makes sense not to factor out the condition from the condition checked after
reading row data, The bug happened because the condition pushed to an index
always was factor out from the condition pushed to the accessed table.
---
mysql-test/t/join_cache.test | 28 ++++++++++++++++++++++++++++
1 file changed, 28 insertions(+)
(limited to 'mysql-test/t')
diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test
index a71db1081a1..c9a6673c8c3 100755
--- a/mysql-test/t/join_cache.test
+++ b/mysql-test/t/join_cache.test
@@ -2076,5 +2076,33 @@ SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b;
set join_cache_level = default;
DROP TABLE t1,t2;
+--echo #
+--echo # BUG#54359 "Extra rows with join_cache_level=7,8 and two joins
+--echo # --and multi-column index"
+--echo #
+
+CREATE TABLE t1 (
+ pk int NOT NULL,
+ a int DEFAULT NULL,
+ b varchar(16) DEFAULT NULL,
+ c varchar(16) DEFAULT NULL,
+ INDEX idx (b,a))
+;
+
+INSERT INTO t1 VALUES (4,9,'k','k');
+INSERT INTO t1 VALUES (12,5,'k','k');
+
+set join_cache_level = 8;
+
+EXPLAIN
+SELECT t.a FROM t1 t, t1 s FORCE INDEX(idx)
+ WHERE s.pk AND s.a >= t.pk AND s.b = t.c;
+
+SELECT t.a FROM t1 t, t1 s FORCE INDEX(idx)
+ WHERE s.pk AND s.a >= t.pk AND s.b = t.c;
+
+set join_cache_level = default;
+DROP TABLE t1;
+
# this must be the last command in the file
set @@optimizer_switch=@save_optimizer_switch;
--
cgit v1.2.1
From f1d42ec9407bf4c7248d13bc4d2fd1bd8daf0910 Mon Sep 17 00:00:00 2001
From: Igor Babaev <igor@askmonty.org>
Date: Sun, 3 Oct 2010 18:45:46 -0700
Subject: Applied the fix for bug #54235 taken from one of the mysql trees. The
fix aligns join_null_complements() with join_matching_records() making both
call generate_full_extensions(). There should not be any difference between
how the WHERE clause is applied to NULL-complemented records from a partial
join and how it is applied to other partially joined records:the latter
happens in join_matching_records(), precisely in generate_full_extensions().
---
mysql-test/t/join_cache.test | 32 ++++++++++++++++++++++++++++++++
1 file changed, 32 insertions(+)
(limited to 'mysql-test/t')
diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test
index c9a6673c8c3..8ab9165457b 100755
--- a/mysql-test/t/join_cache.test
+++ b/mysql-test/t/join_cache.test
@@ -2104,5 +2104,37 @@ SELECT t.a FROM t1 t, t1 s FORCE INDEX(idx)
set join_cache_level = default;
DROP TABLE t1;
+
+--echo #
+--echo # Bug#54235 Extra rows with join_cache_level=6,8 and two LEFT JOINs
+--echo #
+
+CREATE TABLE t1 (a int);
+CREATE TABLE t2 (a int);
+CREATE TABLE t3 (a int);
+CREATE TABLE t4 (a int);
+
+INSERT INTO t1 VALUES (null), (2), (null), (1);
+
+set join_cache_level = 6;
+EXPLAIN
+SELECT t1.a
+ FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.a) ON 0
+ WHERE t1.a OR t3.a;
+SELECT t1.a
+ FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.a) ON 0
+ WHERE t1.a OR t3.a;
+
+EXPLAIN
+SELECT t1.a
+ FROM t1 LEFT JOIN (t2 LEFT JOIN (t3 LEFT JOIN t4 ON 1) ON t2.a) ON 0
+ WHERE t1.a OR t4.a;
+SELECT t1.a
+ FROM t1 LEFT JOIN (t2 LEFT JOIN (t3 LEFT JOIN t4 ON 1) ON t2.a) ON 0
+ WHERE t1.a OR t4.a;
+
+set 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;
--
cgit v1.2.1
From 79087c9e072ea2c1af3f1372d5c37729545f618c Mon Sep 17 00:00:00 2001
From: Igor Babaev <igor@askmonty.org>
Date: Wed, 6 Oct 2010 13:27:12 -0700
Subject: Ported the fix for bug #57024 (a performance issue for outer joins).
Employed the same kind of optimization as in the fix for the cases when join
buffer is used. The optimization performs early evaluation of the conditions
from on expression with table references to only outer tables of an outer
join.
---
mysql-test/t/join_outer.test | 44 ++++++++++++++++++++++++++++++++++++++++++++
1 file changed, 44 insertions(+)
(limited to 'mysql-test/t')
diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test
index 2c243aabeb6..44dbe83ffe3 100644
--- a/mysql-test/t/join_outer.test
+++ b/mysql-test/t/join_outer.test
@@ -914,4 +914,48 @@ WHERE (COALESCE(t1.f1, t2.f1), f3) IN ((1, 3), (2, 2));
DROP TABLE t1, t2;
+--echo #
+--echo # Bug#57024: Poor performance when conjunctive condition over the outer
+--echo # table is used in the on condition of an outer join
+--echo #
+
+create table t1 (a int);
+insert into t1 values (NULL), (NULL), (NULL), (NULL);
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 values (4), (2), (1), (3);
+
+create table t2 like t1;
+insert into t2 select if(t1.a is null, 10, t1.a) from t1;
+
+create table t3 (a int, b int, index idx(a));
+insert into t3 values (1, 100), (3, 301), (4, 402), (1, 102), (1, 101);
+
+analyze table t1,t2,t3;
+
+flush status;
+select sum(t3.b) from t1 left join t3 on t3.a=t1.a and t1.a is not null;
+show status like "handler_read%";
+flush status;
+select sum(t3.b) from t2 left join t3 on t3.a=t2.a and t2.a <> 10;
+show status like "handler_read%";
+
+drop table t1,t2,t3;
+
--echo End of 5.1 tests
--
cgit v1.2.1
From ca862231f731dd23b8eaa83d3d052b84cc60a04b Mon Sep 17 00:00:00 2001
From: Igor Babaev <igor@askmonty.org>
Date: Fri, 22 Oct 2010 10:53:29 -0700
Subject: Fixed LP bug #663840. When adding a new record into the join buffer
that is employed by BNLH join algorithm the writing procedure
JOIN_CACHE::write_record_data checks whether there is enough space for the
record in the buffer. When doing this it must take into account a possible
new key entry added to the buffer. It might happen, as it has been
demonstrated by the bug test case, that there is enough remaining space in
the buffer for the record, but not for the additional key entry for this
record. In this case the key entry overwrites the end of the record that
might cause a crash or wrong results. Fixed by taking into account a possible
addition of new key entry when estimating the remaining free space in the
buffer.
---
mysql-test/t/join_cache.test | 59 ++++++++++++++++++++++++++++++++++++++------
1 file changed, 52 insertions(+), 7 deletions(-)
(limited to 'mysql-test/t')
diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test
index 8ab9165457b..d83646b5e99 100755
--- a/mysql-test/t/join_cache.test
+++ b/mysql-test/t/join_cache.test
@@ -1983,8 +1983,8 @@ DROP TABLE t1,t2,t3;
set join_cache_level=default;
--echo #
---echo # Bug#51084: Batched key access crashes for SELECT with
---echo # derived table and LEFT JOIN
+--echo # Bug #51084: Batched key access crashes for SELECT with
+--echo # derived table and LEFT JOIN
--echo #
CREATE TABLE t1 (
@@ -2027,7 +2027,7 @@ SET join_cache_level=default;
DROP TABLE t1,t2,t3,t4;
--echo #
---echo # BUG#52636: allowing JOINs on NULL values w/ join_cache_level = 5-8
+--echo # Bug #52636: allowing JOINs on NULL values w/ join_cache_level = 5-8
--echo #
CREATE TABLE t1 (b int);
@@ -2077,8 +2077,8 @@ set join_cache_level = default;
DROP TABLE t1,t2;
--echo #
---echo # BUG#54359 "Extra rows with join_cache_level=7,8 and two joins
---echo # --and multi-column index"
+--echo # Bug #54359: Extra rows with join_cache_level=7,8 and two joins
+--echo # and multi-column index"
--echo #
CREATE TABLE t1 (
@@ -2104,9 +2104,8 @@ SELECT t.a FROM t1 t, t1 s FORCE INDEX(idx)
set join_cache_level = default;
DROP TABLE t1;
-
--echo #
---echo # Bug#54235 Extra rows with join_cache_level=6,8 and two LEFT JOINs
+--echo # Bug #54235: Extra rows with join_cache_level=6,8 and two LEFT JOINs
--echo #
CREATE TABLE t1 (a int);
@@ -2136,5 +2135,51 @@ SELECT t1.a
set join_cache_level = default;
DROP TABLE t1,t2,t3,t4;
+--echo #
+--echo # Bug #663840: Memory overwrite causing crash with hash join
+--echo #
+
+SET SESSION join_cache_level=3;
+SET SESSION join_buffer_size=100;
+
+CREATE TABLE t3 (
+ i int NOT NULL,
+ j int NOT NULL,
+ d date NOT NULL,
+ t time NOT NULL,
+ v varchar(1) NOT NULL,
+ u varchar(1) NOT NULL,
+ INDEX idx (v)
+) COLLATE=latin1_bin;
+
+INSERT INTO t3 VALUES
+ (3,8,'2008-12-04','00:00:00','v','v'), (3,8,'2009-03-28','00:00:00','f','f'),
+ (3,5,'1900-01-01','00:55:47','v','v'), (2,8,'2009-10-02','00:00:00','s','s'),
+ (1,8,'1900-01-01','20:51:59','a','a'), (0,6,'2008-06-04','09:47:27','p','p'),
+ (8,7,'2009-01-13','21:58:29','z','z'), (5,2,'1900-01-01','22:45:53','a','a'),
+ (9,5,'2008-01-28','14:06:48','h','h'), (5,7,'2004-09-18','22:17:16','h','h'),
+ (4,2,'2006-10-14','14:59:37','v','v'), (2,9,'1900-01-01','23:37:40','v','v'),
+ (33,142,'2000-11-28','14:14:01','b','b'), (5,3,'2008-04-04','02:54:19','y','y'),
+ (1,0,'2002-07-13','06:34:26','v','v'), (9,3,'2003-01-03','18:07:38','m','m'),
+ (1,5,'2006-04-02','13:55:23','z','z'), (3,9,'2006-10-19','20:32:28','n','n'),
+ (8,1,'2005-06-08','11:57:44','d','d'), (231,107,'2006-12-26','03:10:35','a','a');
+
+CREATE TABLE t1 SELECT * FROM t3;
+DELETE FROM t1 WHERE i > 8;
+CREATE TABLE t2 SELECT * FROM t3;
+DELETE FROM t2 WHERE j > 10;
+
+EXPLAIN
+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;
+
+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;
+
+DROP TABLE t1,t2,t3;
+
+SET SESSION join_cache_level=DEFAULT;
+SET SESSION join_buffer_size=DEFAULT;
+
# this must be the last command in the file
set @@optimizer_switch=@save_optimizer_switch;
--
cgit v1.2.1
From de69dbae0cf63f3bc4fae96fac84bfff17596969 Mon Sep 17 00:00:00 2001
From: Igor Babaev <igor@askmonty.org>
Date: Fri, 22 Oct 2010 15:30:47 -0700
Subject: Fixed LP bug #663818. After the patch for bug 663840 had been applied
the test case for bug 663818 triggered the assert introduced by this patch.
It happened because the the patch turned out to be incomplete: the space
needed for a key entry must be taken into account for the record written into
the buffer, and, for the next record as well, when figuring out whether the
record being written is the last for the buffer or not.
---
mysql-test/t/innodb_mysql.test | 36 ++++++++++++++++++++++++++++++++++++
1 file changed, 36 insertions(+)
(limited to 'mysql-test/t')
diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test
index ada4323dcee..845f8731a16 100644
--- a/mysql-test/t/innodb_mysql.test
+++ b/mysql-test/t/innodb_mysql.test
@@ -634,3 +634,39 @@ SELECT SECOND(c)-@bug47453 FROM t1 JOIN t2 ON d=a;
DROP TABLE t1, t2;
--echo End of 5.1 tests
+
+--echo #
+--echo # Bug #663818: wrong result when BNLH is used
+--echo #
+
+CREATE TABLE t1(pk int NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+ (1), (2), (11), (12), (13), (14),
+ (15), (16), (17), (18), (19);
+CREATE TABLE t2(pk int NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+ (1), (10), (11), (12), (13), (14),
+ (15), (16), (17), (18), (19), (20), (21);
+
+SET SESSION join_buffer_size=10000;
+
+SET SESSION join_cache_level=3;
+EXPLAIN
+SELECT t1.pk FROM t1,t2
+ WHERE t1.pk = t2.pk AND t2.pk <> 8;
+SELECT t1.pk FROM t1,t2
+ WHERE t1.pk = t2.pk AND t2.pk <> 8;
+
+SET SESSION join_cache_level=1;
+EXPLAIN
+SELECT t1.pk FROM t1,t2
+ WHERE t1.pk = t2.pk AND t2.pk <> 8;
+SELECT t1.pk FROM t1,t2
+ WHERE t1.pk = t2.pk AND t2.pk <> 8;
+
+DROP TABLE t1,t2;
+
+SET SESSION join_cache_level=DEFAULT;
+SET SESSION join_buffer_size=DEFAULT;
+
+--echo End of 5.3 tests
--
cgit v1.2.1
From 0c53cd1ec9cbc50daf5b8d7c9f11e10f4d1fe564 Mon Sep 17 00:00:00 2001
From: Igor Babaev <igor@askmonty.org>
Date: Sun, 24 Oct 2010 14:22:01 -0700
Subject: Fixed LP bug #664508. When join buffers are employed no index scan
for the first table with grouping columns can be used.
mysql-test/r/join_cache.result:
Added a test case for bug #664508.
Sorted results for some other test cases.
mysql-test/t/join_cache.test:
Added a test case for bug #664508.
Sorted results for some other test cases.
---
mysql-test/t/join_cache.test | 62 ++++++++++++++++++++++++++++++++++++++++++++
1 file changed, 62 insertions(+)
(limited to 'mysql-test/t')
diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test
index d83646b5e99..010abed3c2b 100755
--- a/mysql-test/t/join_cache.test
+++ b/mysql-test/t/join_cache.test
@@ -699,6 +699,7 @@ EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND City.Population > 3000000;
+--sorted_result
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND City.Population > 3000000;
@@ -2173,6 +2174,7 @@ EXPLAIN
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;
+--sorted_result
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;
@@ -2181,5 +2183,65 @@ DROP TABLE t1,t2,t3;
SET SESSION join_cache_level=DEFAULT;
SET SESSION join_buffer_size=DEFAULT;
+
+--echo #
+--echo # Bug #664508: 'Simple' GROUP BY + ORDER BY
+--echo # when join buffers are used
+--echo #
+
+CREATE TABLE t1 (
+ pk int NOT NULL, i int NOT NULL, v varchar(1) NOT NULL,
+ PRIMARY KEY (pk), INDEX idx1(i), INDEX idx2 (v,i)
+);
+INSERT INTO t1 VALUES
+ (10,8,'v'), (11,8,'f'), (12,5,'v'), (13,8,'s'), (14,8,'a'),
+ (15,6,'p'), (16,7,'z'), (17,2,'a'), (18,5,'h'), (19,7,'h'),
+ (25,3,'m'), (26,5,'z'), (27,9,'n'), (28,1,'d'), (29,107,'a');
+
+CREATE TABLE t2 (
+ pk int NOT NULL, i int NOT NULL, v varchar(1) NOT NULL,
+ PRIMARY KEY (pk), INDEX idx1(i), INDEX idx2(v,i)
+);
+INSERT INTO t2 VALUES
+ (10,8,'v'), (11,8,'f'), (12,5,'v'), (13,8,'s'), (14,8,'a'),
+ (15,6,'p'), (16,7,'z'), (17,2,'a'), (18,5,'h'), (19,7,'h'),
+ (20,2,'v'), (21,9,'v'), (22,142,'b'), (23,3,'y'), (24,0,'v'),
+ (25,3,'m'), (26,5,'z'), (27,9,'n'), (28,1,'d'), (29,107,'a');
+
+CREATE TABLE t3 (
+ pk int NOT NULL, i int(11) NOT NULL, v varchar(1) NOT NULL,
+ PRIMARY KEY (pk), INDEX idx1(i), INDEX idx2(v,i)
+);
+INSERT INTO t3 VALUES
+ (1,9,'x'), (2,5,'g'), (3,1,'o'), (4,0,'g'), (5,1,'v'),
+ (6,190,'m'), (7,6,'x'), (8,3,'c'), (9,4,'z'), (10,3,'i'),
+ (11,186,'x'), (12,1,'g'), (13,8,'q'), (14,226,'m'), (15,133,'p'),
+ (16,6,'e'), (17,3,'t'), (18,8,'j'), (19,5,'h'), (20,7,'w');
+
+SET SESSION join_cache_level=1;
+EXPLAIN
+SELECT t2.v FROM t1, t2, t3 WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v
+ GROUP BY t2.v ORDER BY t1.pk,t2.v;
+SELECT t2.v FROM t1, t2, t3 WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v
+ GROUP BY t2.v ORDER BY t1.pk,t2.v;
+
+SET SESSION join_cache_level=6;
+EXPLAIN
+SELECT t2.v FROM t1, t2, t3 WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v
+ GROUP BY t2.v ORDER BY t1.pk,t2.v;
+SELECT t2.v FROM t1, t2, t3 WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v
+ GROUP BY t2.v ORDER BY t1.pk,t2.v;
+
+SET SESSION join_cache_level=4;
+EXPLAIN
+SELECT t2.v FROM t1, t2, t3 WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v
+ GROUP BY t2.v ORDER BY t1.pk,t2.v;
+SELECT t2.v FROM t1, t2, t3 WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v
+ GROUP BY t2.v ORDER BY t1.pk,t2.v;
+
+DROP TABLE t1,t2,t3;
+
+SET SESSION join_cache_level=DEFAULT;
+
# this must be the last command in the file
set @@optimizer_switch=@save_optimizer_switch;
--
cgit v1.2.1