summaryrefslogtreecommitdiff
path: root/mysql-test/r/join_cache.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r/join_cache.result')
-rw-r--r--mysql-test/r/join_cache.result246
1 files changed, 228 insertions, 18 deletions
diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result
index 1ef17b0763e..07ceb573f85 100644
--- a/mysql-test/r/join_cache.result
+++ b/mysql-test/r/join_cache.result
@@ -40,6 +40,7 @@ COUNT(*)
show variables like 'join_buffer_size';
Variable_name Value
join_buffer_size 131072
+set join_cache_level=1;
show variables like 'join_cache_level';
Variable_name Value
join_cache_level 1
@@ -313,8 +314,8 @@ CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE Country ALL NULL NULL NULL NULL 239 Using where
-1 SIMPLE City hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join)
-1 SIMPLE CountryLanguage hash_ALL NULL #hash#$hj 3 world.Country.Code 984 Using where; Using join buffer (incremental, BNLH join)
+1 SIMPLE CountryLanguage hash_ALL NULL #hash#$hj 3 world.Country.Code 984 Using where; Using join buffer (flat, BNLH join)
+1 SIMPLE City hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 Using where; Using join buffer (incremental, BNLH join)
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -453,7 +454,7 @@ Côte d?Ivoire 14786000 NULL NULL
Czech Republic 10278100 NULL NULL
DROP INDEX City_Population ON City;
DROP INDEX City_Name ON City;
-set join_cache_level=default;
+set join_cache_level=1;
set join_buffer_size=256;
show variables like 'join_buffer_size';
Variable_name Value
@@ -776,7 +777,7 @@ Variable_name Value
join_buffer_size 131072
show variables like 'join_cache_level';
Variable_name Value
-join_cache_level 1
+join_cache_level 2
DROP DATABASE world;
CREATE DATABASE world;
use world;
@@ -2690,7 +2691,7 @@ Variable_name Value
join_buffer_size 131072
show variables like 'join_cache_level';
Variable_name Value
-join_cache_level 1
+join_cache_level 2
set join_cache_level=1;
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND City.Population > 3000000;
@@ -3878,6 +3879,7 @@ create table t2 (a int, b int);
insert into t2 values (1,1),(2,2);
create table t3 (a int, b int);
insert into t3 values (1,1),(2,2);
+set join_cache_level=1;
explain select t1.* from t1,t2,t3;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2
@@ -3928,8 +3930,8 @@ EXPLAIN
SELECT t3.a FROM t1,t2,t3 WHERE t1.a = t3.a AND t1.b = t3.b;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
-1 SIMPLE t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join)
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1 SIMPLE t2 ALL NULL NULL NULL NULL 5 Using join buffer (incremental, BNL join)
SELECT t3.a FROM t1,t2,t3 WHERE t1.a = t3.a AND t1.b = t3.b;
a
27
@@ -4401,18 +4403,13 @@ INSERT INTO t2 VALUES
(1, 12, 102), (8, 81, 801), (7, 70, 700), (12, 120, 1200),
(8, 82, 802), (1, 13, 103), (1, 14, 104), (3, 31, 301),
(1, 15, 105), (8, 83, 803), (7, 71, 701);
-INSERT INTO t2 VALUES
-(108, 80, 800), (101, 10, 100), (101, 11, 101), (103, 30, 300),
-(101, 12, 102), (108, 81, 801), (107, 70, 700), (1012, 120, 1200),
-(108, 82, 802), (101, 13, 103), (101, 14, 104), (103, 31, 301),
-(101, 15, 105), (108, 83, 803), (107, 71, 701);
SET SESSION join_cache_level = 4;
SET SESSION join_buffer_size = 256;
EXPLAIN
SELECT t1.a, t2.c FROM t1,t2 WHERE t1.a=t2.a AND t2.b=99;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 36
-1 SIMPLE t2 ALL idx NULL NULL NULL 30 Using where; Using join buffer (flat, BNL join)
+1 SIMPLE t2 ALL idx NULL NULL NULL 15 Using where
+1 SIMPLE t1 hash_ALL NULL #hash#$hj 5 test.t2.a 36 Using where; Using join buffer (flat, BNLH join)
SELECT t1.a, t2.c FROM t1,t2 WHERE t1.a=t2.a AND t2.b=99;
a c
SET SESSION join_cache_level = DEFAULT;
@@ -5140,8 +5137,8 @@ EXPLAIN
SELECT * FROM (SELECT DISTINCT * FROM t1) t
WHERE t.a IN (SELECT t2.a FROM t2);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY <derived2> ALL key0 NULL NULL NULL 3 Start temporary
-1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where; End temporary; Using join buffer (flat, BNL join)
+1 PRIMARY <derived2> ALL key0 NULL NULL NULL 3
+1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
2 DERIVED t1 ALL NULL NULL NULL NULL 3 Using temporary
SELECT * FROM (SELECT DISTINCT * FROM t1) t
WHERE t.a IN (SELECT t2.a FROM t2);
@@ -5173,8 +5170,8 @@ SET SESSION join_cache_level=3;
EXPLAIN
SELECT * FROM t1 WHERE (t1.b) IN (SELECT c FROM t2);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Start temporary
-1 PRIMARY t2 hash_index c #hash#c:c 5:5 test.t1.b 8 End temporary; Using join buffer (flat, BNLH join)
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
+1 PRIMARY t2 hash_index c #hash#c:c 5:5 test.t1.b 8 Start temporary; End temporary; Using join buffer (flat, BNLH join)
SELECT * FROM t1 WHERE (t1.b) IN (SELECT c FROM t2);
a b
3914 17
@@ -5182,4 +5179,217 @@ a b
SET SESSION join_cache_level = DEFAULT;
SET optimizer_switch=@tmp887479_optimizer_switch;
DROP TABLE t1,t2;
+#
+# Bug #899777: join_cache_level=4 + semijoin=on
+#
+CREATE TABLE t1 (a int, b int, c int, UNIQUE INDEX idx (a));
+INSERT INTO t1 VALUES (1,8,6), (2,2,8);
+CREATE TABLE t2 (a int, b int, c int, UNIQUE INDEX idx (a));
+INSERT INTO t2 VALUES (1,8,6), (2,2,8);
+CREATE TABLE t3 (a int, b int, c int, UNIQUE INDEX idx (a));
+INSERT INTO t3 VALUES (1,8,6), (2,2,8);
+CREATE TABLE t4 (a int, b int, c int, UNIQUE INDEX idx (a));
+INSERT INTO t4 VALUES (1,8,6), (2,2,8);
+SET @tmp_optimizer_switch=@@optimizer_switch;
+SET SESSION optimizer_switch='semijoin=on';
+SET SESSION optimizer_switch='semijoin_with_cache=on';
+SET SESSION join_cache_level=1;
+EXPLAIN
+SELECT t1.* FROM t1,t2
+WHERE (t1.b,t2.b) IN (SELECT t3.b,t4.b FROM t3,t4 WHERE t4.c=t3.b)
+AND t1.a = 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 const idx idx 5 const 1
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2
+1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; Start temporary; Using join buffer (flat, BNL join)
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (flat, BNL join)
+SELECT t1.* FROM t1,t2
+WHERE (t1.b,t2.b) IN (SELECT t3.b,t4.b FROM t3,t4 WHERE t4.c=t3.b)
+AND t1.a = 1;
+a b c
+1 8 6
+SET SESSION join_cache_level=4;
+EXPLAIN
+SELECT t1.* FROM t1,t2
+WHERE (t1.b,t2.b) IN (SELECT t3.b,t4.b FROM t3,t4 WHERE t4.c=t3.b)
+AND t1.a = 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 const idx idx 5 const 1
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
+1 PRIMARY t3 hash_ALL NULL #hash#$hj 5 const 2 Using where; Start temporary; Using join buffer (flat, BNLH join)
+1 PRIMARY t4 hash_ALL NULL #hash#$hj 10 const,test.t2.b 2 Using where; End temporary; Using join buffer (incremental, BNLH join)
+SELECT t1.* FROM t1,t2
+WHERE (t1.b,t2.b) IN (SELECT t3.b,t4.b FROM t3,t4 WHERE t4.c=t3.b)
+AND t1.a = 1;
+a b c
+1 8 6
+SET SESSION join_cache_level = DEFAULT;
+SET optimizer_switch=@tmp_optimizer_switch;
+DROP TABLE t1,t2,t3,t4;
+#
+# Bug #899509: an attempt to use hash join with join_cache_level=0
+#
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (8), (7);
+CREATE TABLE t2 (a int);
+INSERT INTO t2 VALUES (8), (7);
+CREATE TABLE t3 (a int);
+INSERT INTO t3 VALUES (8), (7);
+SET @tmp_optimizer_switch=@@optimizer_switch;
+set optimizer_switch=default;
+set @@optimizer_switch='semijoin_with_cache=off';
+set @@optimizer_switch='outer_join_with_cache=off';
+SET optimizer_switch='derived_merge=off,derived_with_keys=off';
+SET join_cache_level=0;
+EXPLAIN
+SELECT * FROM (SELECT t1.* FROM t1, t2) t WHERE t.a IN (SELECT * FROM t3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 Using where
+3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2
+2 DERIVED t1 ALL NULL NULL NULL NULL 2
+2 DERIVED t2 ALL NULL NULL NULL NULL 2
+SELECT * FROM (SELECT t1.* FROM t1, t2) t WHERE t.a IN (SELECT * FROM t3);
+a
+8
+8
+7
+7
+SELECT * FROM ( SELECT ta.* FROM t1 AS ta, t1 ) tb WHERE a IN ( SELECT * FROM t1 );
+a
+8
+8
+7
+7
+SET SESSION join_cache_level = DEFAULT;
+SET optimizer_switch=@tmp_optimizer_switch;
+DROP TABLE t1,t2,t3;
+#
+# Bug #900469: semijoin + BNLH + ORDER BY
+#
+CREATE TABLE t1 (a int, b int);
+INSERT INTO t1 VALUES (8,10);
+CREATE TABLE t2 (c int, d int);
+INSERT INTO t2 VALUES (8,10);
+INSERT INTO t2 VALUES (9,11);
+CREATE TABLE t3 (c int, d int);
+INSERT INTO t3 VALUES (8,10);
+INSERT INTO t3 VALUES (9,11);
+SET @tmp_optimizer_switch=@@optimizer_switch;
+set @@optimizer_switch='semijoin_with_cache=on';
+SET join_cache_level=1;
+EXPLAIN
+SELECT * FROM t1,t2 WHERE b IN (SELECT d FROM t3 WHERE c <= t2.c) ORDER BY a,d;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 system NULL NULL NULL NULL 1 Using temporary; Using filesort
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2
+1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
+SELECT * FROM t1,t2 WHERE b IN (SELECT d FROM t3 WHERE c <= t2.c) ORDER BY a,d;
+a b c d
+8 10 8 10
+8 10 9 11
+SET join_cache_level=3;
+EXPLAIN
+SELECT * FROM t1,t2 WHERE b IN (SELECT d FROM t3 WHERE c <= t2.c);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 system NULL NULL NULL NULL 1
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2
+1 PRIMARY t3 hash_ALL NULL #hash#$hj 5 const 2 Using where; Start temporary; End temporary; Using join buffer (flat, BNLH join)
+SELECT * FROM t1,t2 WHERE b IN (SELECT d FROM t3 WHERE c <= t2.c);
+a b c d
+8 10 8 10
+8 10 9 11
+SET join_cache_level=3;
+EXPLAIN
+SELECT * FROM t1,t2 WHERE b IN (SELECT d FROM t3 WHERE c <= t2.c) ORDER BY a,d;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 system NULL NULL NULL NULL 1 Using temporary; Using filesort
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2
+1 PRIMARY t3 hash_ALL NULL #hash#$hj 5 const 2 Using where; Start temporary; End temporary; Using join buffer (flat, BNLH join)
+SELECT * FROM t1,t2 WHERE b IN (SELECT d FROM t3 WHERE c <= t2.c) ORDER BY a,d;
+a b c d
+8 10 8 10
+8 10 9 11
+SET SESSION join_cache_level = DEFAULT;
+SET optimizer_switch=@tmp_optimizer_switch;
+DROP TABLE t1,t2,t3;
+#
+# Bug #901478: semijoin + ORDER BY + join_cache_level=4|6
+#
+CREATE TABLE t1 (a char(1));
+INSERT INTO t1 VALUES ('x');
+CREATE TABLE t2 (a int, b int, c int, KEY(a), KEY(b), KEY(c));
+INSERT INTO t2 VALUES
+(9,1,0), (7,2,8), (2,3,5), (4,2,9), (8,3,8), (3,4,1), (5,5,4);
+CREATE TABLE t3 (a CHAR(1));
+INSERT INTO t3 VALUES ('x');
+CREATE TABLE t4 (a int, b int, c int, KEY(b), KEY(c));
+INSERT INTO t4 VALUES
+(9,1,0), (7,2,8), (2,3,5), (4,2,9), (8,3,8), (3,4,1), (5,5,4);
+INSERT INTO t4 VALUES
+(19,11,10), (17,12,18), (12,13,15), (14,12,19),
+(18,13,18), (13,14,11), (15,15,14);
+SET @tmp_optimizer_switch=@@optimizer_switch;
+SET @@optimizer_switch='semijoin=on';
+SET @@optimizer_switch='firstmatch=off';
+SET @@optimizer_switch='mrr=off';
+SET @@optimizer_switch='semijoin_with_cache=off';
+set join_cache_level=1;
+EXPLAIN
+SELECT * FROM t1,t2
+WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND
+t2.a BETWEEN 4 and 5
+ORDER BY t2.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 system NULL NULL NULL NULL 1 Using filesort
+1 PRIMARY t3 system NULL NULL NULL NULL 1
+1 PRIMARY t2 range a,c a 5 NULL 1 Using index condition; Using where
+1 PRIMARY t4 ref c c 5 test.t2.c 2 Using where; Start temporary; End temporary
+SELECT * FROM t1,t2
+WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND
+t2.a BETWEEN 4 and 5
+ORDER BY t2.b;
+a a b c
+x 4 2 9
+x 5 5 4
+set join_cache_level=4;
+EXPLAIN
+SELECT * FROM t1,t2
+WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND
+t2.a BETWEEN 4 and 5
+ORDER BY t2.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 system NULL NULL NULL NULL 1 Using temporary; Using filesort
+1 PRIMARY t3 system NULL NULL NULL NULL 1
+1 PRIMARY t2 range a,c a 5 NULL 1 Using index condition; Using where
+1 PRIMARY t4 ref c c 5 test.t2.c 2 Using where; Start temporary; End temporary
+SELECT * FROM t1,t2
+WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND
+t2.a BETWEEN 4 and 5
+ORDER BY t2.b;
+a a b c
+x 4 2 9
+x 5 5 4
+SET @@optimizer_switch='semijoin_with_cache=on';
+set join_cache_level=6;
+EXPLAIN
+SELECT * FROM t1,t2
+WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND
+t2.a BETWEEN 4 and 5
+ORDER BY t2.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 system NULL NULL NULL NULL 1 Using temporary; Using filesort
+1 PRIMARY t3 system NULL NULL NULL NULL 1
+1 PRIMARY t2 range a,c a 5 NULL 1 Using index condition; Using where
+1 PRIMARY t4 ref c c 5 test.t2.c 2 Using where; Start temporary; End temporary
+SELECT * FROM t1,t2
+WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND
+t2.a BETWEEN 4 and 5
+ORDER BY t2.b;
+a a b c
+x 4 2 9
+x 5 5 4
+SET join_cache_level = DEFAULT;
+SET optimizer_switch=@tmp_optimizer_switch;
+DROP TABLE t1,t2,t3,t4;
set @@optimizer_switch=@save_optimizer_switch;
View Lorry Controller Status