summaryrefslogtreecommitdiff
path: root/mysql-test/r/subselect3_jcl6.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r/subselect3_jcl6.result')
-rw-r--r--mysql-test/r/subselect3_jcl6.result200
1 files changed, 100 insertions, 100 deletions
diff --git a/mysql-test/r/subselect3_jcl6.result b/mysql-test/r/subselect3_jcl6.result
index a2f6a74a1d3..e254fad2300 100644
--- a/mysql-test/r/subselect3_jcl6.result
+++ b/mysql-test/r/subselect3_jcl6.result
@@ -1,8 +1,12 @@
+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';
Variable_name Value
join_cache_level 6
drop table if exists t0, t1, t2, t3, t4, t5, t11, t12, t21, t22;
+set @save_optimizer_switch=@@optimizer_switch;
create table t1 (oref int, grp int, ie int) ;
insert into t1 (oref, grp, ie) values
(1, 1, 1),
@@ -31,7 +35,7 @@ select a, oref, a in (select max(ie)
from t1 where oref=t2.oref group by grp) Z from t2;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary
Warnings:
Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`oref`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`))))))) AS `Z` from `test`.`t2`
@@ -40,7 +44,7 @@ select a, oref from t2
where a in (select max(ie) from t1 where oref=t2.oref group by grp);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary
Warnings:
Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`,`test`.`t2`.`oref`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having (<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`))))))
@@ -72,7 +76,7 @@ set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=o
explain extended select a in (select max(ie) from t1 where oref=4 group by grp) from t3;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary
Warnings:
Note 1003 select <expr_cache><`test`.`t3`.`a`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(select max(`test`.`t1`.`ie`) from `test`.`t1` where (`test`.`t1`.`oref` = 4) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t3`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`))))))) AS `a in (select max(ie) from t1 where oref=4 group by grp)` from `test`.`t3`
set @@optimizer_switch=@save_optimizer_switch;
@@ -106,7 +110,7 @@ oref a
1 1
show status like '%Handler_read_rnd_next';
Variable_name Value
-Handler_read_rnd_next 5
+Handler_read_rnd_next 11
delete from t2;
insert into t2 values (NULL, 0),(NULL, 0), (NULL, 0), (NULL, 0);
set optimizer_switch='subquery_cache=off';
@@ -124,7 +128,7 @@ Handler_read_key 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
-Handler_read_rnd_next 29
+Handler_read_rnd_next 35
select 'No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1.' Z;
Z
No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1.
@@ -163,10 +167,10 @@ from t3;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00
2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 4 100.00 Using where; Full scan on NULL key
-2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 100.00 Using where; Using join buffer
+2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 100.00 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
Warnings:
Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1
-Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<expr_cache><`test`.`t3`.`a`,`test`.`t3`.`oref`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond(((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)))) having trigcond(<is_not_null_test>(`test`.`t1`.`a`))))) AS `Z` from `test`.`t3`
+Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<expr_cache><`test`.`t3`.`a`,`test`.`t3`.`oref`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(select `test`.`t1`.`a` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond(((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and (`test`.`t2`.`a` = `test`.`t1`.`b`)) having trigcond(<is_not_null_test>(`test`.`t1`.`a`))))) AS `Z` from `test`.`t3`
drop table t1, t2, t3;
create table t1 (a int NOT NULL, b int NOT NULL, key(a));
insert into t1 values
@@ -191,10 +195,10 @@ from t3;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00
2 DEPENDENT SUBQUERY t1 ref a a 4 func 2 100.00 Using where; Full scan on NULL key
-2 DEPENDENT SUBQUERY t2 ref a a 4 test.t1.b 1 100.00 Using where; Using join buffer
+2 DEPENDENT SUBQUERY t2 ref a a 4 test.t1.b 1 100.00 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
Warnings:
Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1
-Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<expr_cache><`test`.`t3`.`a`,`test`.`t3`.`oref`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`)))))) AS `Z` from `test`.`t3`
+Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<expr_cache><`test`.`t3`.`a`,`test`.`t3`.`oref`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(select `test`.`t1`.`a` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`)) and (`test`.`t2`.`a` = `test`.`t1`.`b`))))) AS `Z` from `test`.`t3`
drop table t1,t2,t3;
create table t1 (oref int, grp int);
insert into t1 (oref, grp) values
@@ -215,7 +219,7 @@ select a, oref,
a in (select count(*) from t1 group by grp having grp=t2.oref) Z from t2;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary
Warnings:
Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`oref`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(select count(0) from `test`.`t1` group by `test`.`t1`.`grp` having ((`test`.`t1`.`grp` = `test`.`t2`.`oref`) and trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(count(0)))))))) AS `Z` from `test`.`t2`
@@ -264,7 +268,7 @@ from t2;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00
2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 2 100.00 Using where; Full scan on NULL key
-2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 100.00 Using where; Using join buffer
+2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 100.00 Using where; Using join buffer (flat, BNL join)
Warnings:
Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<expr_cache><`test`.`t2`.`b`,`test`.`t2`.`a`,`test`.`t2`.`oref`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(select `test`.`t1`.`a`,`test`.`t1`.`b` from `test`.`t1` join `test`.`t4` where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`)))))) AS `Z` from `test`.`t2`
@@ -466,7 +470,7 @@ group by grp having min(ie) > 1) Z
from t2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 7
-2 DEPENDENT SUBQUERY t1 ref idx idx 5 test.t2.oref 2 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ref idx idx 5 test.t2.oref 2 Using where; Using temporary
select oref, a,
a in (select min(ie) from t1 where oref=t2.oref
group by grp having min(ie) > 1) Z
@@ -627,7 +631,7 @@ explain
select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 7
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 Using where; Using temporary
select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
oref a Z
ee NULL 0
@@ -714,7 +718,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 Using index
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
Warnings:
-Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`b` = `test`.`t1`.`a`) and (not(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 from `test`.`t1` where ((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)) having <is_not_null_test>(`test`.`t1`.`a`)))))))
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`b` = `test`.`t1`.`a`) and (not(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select `test`.`t1`.`a` from `test`.`t1` where trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) having trigcond(<is_not_null_test>(`test`.`t1`.`a`))))))))
SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1));
a
SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1 WHERE a > 4));
@@ -735,7 +739,7 @@ WHERE t3.name='xxx' AND t2.id=t3.id);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where
2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 func 1 Using where; Using index; Full scan on NULL key
-2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 func 1 Using index condition(BKA); Using where; Full scan on NULL key; Using join buffer
+2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 test.t2.id 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
SELECT * FROM t1
WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3
WHERE t3.name='xxx' AND t2.id=t3.id);
@@ -844,11 +848,16 @@ x ROW(11, 12) = (SELECT MAX(x), 22) ROW(11, 12) IN (SELECT MAX(x), 22)
1 0 0
2 0 0
11 0 0
-# 2nd and 3rd columns should be same for x == 11 only
+# 2nd and 3rd columns should be same
+EXPLAIN SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 12), ROW(11, 12) IN (SELECT MAX(x), 12) FROM t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3
+3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
+2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 12), ROW(11, 12) IN (SELECT MAX(x), 12) FROM t1;
x ROW(11, 12) = (SELECT MAX(x), 12) ROW(11, 12) IN (SELECT MAX(x), 12)
-1 0 1
-2 0 1
+1 0 0
+2 0 0
11 1 1
DROP TABLE t1;
# both columns should be same
@@ -974,7 +983,7 @@ i1 i2
# Baseline:
SHOW STATUS LIKE '%Handler_read_rnd_next';
Variable_name Value
-Handler_read_rnd_next 18
+Handler_read_rnd_next 17
INSERT INTO t1 VALUES (NULL, NULL);
FLUSH STATUS;
@@ -991,7 +1000,7 @@ i1 i2
# (read record from t1, but do not read from t2)
SHOW STATUS LIKE '%Handler_read_rnd_next';
Variable_name Value
-Handler_read_rnd_next 19
+Handler_read_rnd_next 18
set @@optimizer_switch=@save_optimizer_switch2;
DROP TABLE t1,t2;
End of 5.1 tests
@@ -1026,16 +1035,14 @@ update t22 set c = '2005-12-08 15:58:27' where a = 255;
explain select t21.* from t21,t22 where t21.a = t22.a and
t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 8 Using temporary; Using filesort
-1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer
-1 PRIMARY t22 ALL NULL NULL NULL NULL 26 Using where; Using join buffer
-2 SUBQUERY t11 ALL NULL NULL NULL NULL 8 Using where
-2 SUBQUERY t12 ALL NULL NULL NULL NULL 8 Using where; Using join buffer
+1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort; Start temporary
+1 PRIMARY t12 hash_ALL NULL #hash#$hj 4 test.t11.a 8 Using where; Using join buffer (flat, BNLH join)
+1 PRIMARY t22 hash_ALL NULL #hash#$hj 4 test.t11.a 26 Using where; End temporary; Using join buffer (incremental, BNLH join)
+1 PRIMARY t21 hash_ALL NULL #hash#$hj 4 test.t11.a 26 Using where; Using join buffer (incremental, BNLH join)
select t21.* from t21,t22 where t21.a = t22.a and
t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
a b c
256 67 NULL
-256 67 NULL
drop table t1, t11, t12, t21, t22;
create table t1(a int);
insert into t1 values (0),(1);
@@ -1044,14 +1051,13 @@ 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;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY X ALL NULL NULL NULL NULL 2
-2 DEPENDENT SUBQUERY Y ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY subselect3 eq_ref unique_key unique_key 5 func 1
-3 SUBQUERY Z ALL NULL NULL NULL NULL 2
+2 DEPENDENT SUBQUERY Y ALL NULL NULL NULL NULL 2 Using where; Start temporary
+2 DEPENDENT SUBQUERY Z hash_ALL NULL #hash#$hj 5 test.Y.a 2 Using where; End temporary; Using join buffer (flat, BNLH join)
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;
subq
NULL
0
-set @@optimizer_switch=default;
+set @@optimizer_switch=@save_optimizer_switch;
drop table t1;
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
@@ -1062,7 +1068,7 @@ insert into t0 values(2);
explain select * from t1 where 2 in (select a from t0);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t0 ALL NULL NULL NULL NULL 11 Using where; Start temporary; End temporary
-1 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using join buffer
+1 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using join buffer (flat, BNL join)
select * from t1 where 2 in (select a from t0);
a
0
@@ -1089,7 +1095,7 @@ set @@optimizer_switch='default,materialization=off';
explain select * from t1 where 2 in (select a from t0);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t0 ALL NULL NULL NULL NULL 11 Using where; FirstMatch
-1 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using join buffer
+1 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using join buffer (flat, BNL join)
select * from t1 where 2 in (select a from t0);
a
0
@@ -1112,11 +1118,11 @@ a
17
18
19
-set @@optimizer_switch=default;
+set @@optimizer_switch=@save_optimizer_switch;
explain select * from (select a from t0) X where a in (select a from t1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t0 ALL NULL NULL NULL NULL 11
-1 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using where; FirstMatch(t0); Using join buffer
+1 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using where; FirstMatch(t0); Using join buffer (flat, BNL join)
drop table t0, t1;
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
@@ -1127,37 +1133,37 @@ create table t3 (a int);
insert into t3 select A.a + 10*B.a from t0 A, t0 B;
explain select * from t3 where a in (select kp1 from t1 where kp1<20);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Using index; LooseScan
-1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where
+1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using index; FirstMatch(t3)
create table t4 (pk int primary key);
insert into t4 select a from t3;
explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20
and t4.pk=t1.c);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using index condition; Using MRR; LooseScan
-1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; FirstMatch(t1)
-1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where
+1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using where
+1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; FirstMatch(t3)
drop table t1, t3, t4;
create table t1 (a int) as select * from t0 where a < 5;
set @save_max_heap_table_size=@@max_heap_table_size;
set @@optimizer_switch='firstmatch=off,materialization=off';
set @@max_heap_table_size= 16384;
-explain select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E);
+explain select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E where a+1 < 10000 + A.a + B.a +C.a+D.a);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY E ALL NULL NULL NULL NULL 5 Start temporary
-1 PRIMARY A ALL NULL NULL NULL NULL 10 Using join buffer
-1 PRIMARY B ALL NULL NULL NULL NULL 10 Using join buffer
-1 PRIMARY C ALL NULL NULL NULL NULL 10 Using join buffer
-1 PRIMARY D ALL NULL NULL NULL NULL 10 Using where; End temporary; Using join buffer
+1 PRIMARY E ALL NULL NULL NULL NULL 5 Using where; Start temporary
+1 PRIMARY A ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join)
+1 PRIMARY B ALL NULL NULL NULL NULL 10 Using join buffer (incremental, BNL join)
+1 PRIMARY C ALL NULL NULL NULL NULL 10 Using where; Using join buffer (incremental, BNL join)
+1 PRIMARY D hash_ALL NULL #hash#$hj 5 test.E.a 10 Using where; End temporary; Using join buffer (incremental, BNLH join)
flush status;
-select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E);
+select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E where a+1 < 10000 + A.a + B.a +C.a+D.a);
count(*)
5000
show status like 'Created_tmp_disk_tables';
Variable_name Value
Created_tmp_disk_tables 1
set @save_max_heap_table_size=@@max_heap_table_size;
-set @@optimizer_switch=default;
+set @@optimizer_switch=@save_optimizer_switch;
drop table t0, t1;
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
@@ -1167,9 +1173,8 @@ create table t3 ( a int , filler char(100), key(a));
insert into t3 select A.a + 10*B.a, 'filler' from t0 A, t0 B;
explain select * from t3 where a in (select a from t2) and (a > 5 or a < 10);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 2
-1 PRIMARY t3 ref a a 5 test.t2.a 1 Using join buffer
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Start temporary
+1 PRIMARY t3 ref a a 5 test.t2.a 1 End temporary; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
select * from t3 where a in (select a from t2);
a filler
1 filler
@@ -1185,29 +1190,29 @@ insert into t3 select * from t1;
insert into t3 values (1),(2);
explain select * from t2 where a in (select a from t1);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Start temporary
-1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; End temporary; Using join buffer
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Start temporary
+1 PRIMARY t1 hash_ALL NULL #hash#$hj 4 test.t2.a 4 Using where; End temporary; Using join buffer (flat, BNLH join)
explain select * from t2 where a in (select a from t2);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Start temporary
-1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Start temporary
+1 PRIMARY t2 hash_ALL NULL #hash#$hj 5 test.t2.a 2 Using where; End temporary; Using join buffer (flat, BNLH join)
explain select * from t2 where a in (select a from t3);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Start temporary
-1 PRIMARY t3 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer
+1 PRIMARY t3 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (flat, BNL join)
explain select * from t1 where a in (select a from t3);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Start temporary
-1 PRIMARY t3 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer
+1 PRIMARY t3 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (flat, BNL join)
drop table t1, t2, t3;
create table t1 (a decimal);
insert into t1 values (1),(2);
explain select * from t1 where a in (select a from t1);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Start temporary
-1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Start temporary
+1 PRIMARY t1 hash_ALL NULL #hash#$hj 6 test.t1.a 2 Using where; End temporary; Using join buffer (flat, BNLH join)
drop table t1;
-set @@optimizer_switch=default;
+set @@optimizer_switch=@save_optimizer_switch;
create table t1 (a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t2 as select * from t1;
@@ -1215,47 +1220,44 @@ create table t3 (a int, b int, filler char(100), key(a));
insert into t3 select A.a + 10*B.a, A.a + 10*B.a, 'filler' from t1 A, t1 B, t1 C;
explain select * from t1, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30) and t1.a =3;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where
-1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 10
-1 PRIMARY t3 ref a a 5 test.t2.a 10 Using join buffer
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 10 Using where
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where; Start temporary
+1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY t3 ref a a 5 test.t2.a 10 End temporary; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
explain select straight_join * from t1 A, t1 B where A.a in (select a from t2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY A ALL NULL NULL NULL NULL 10 Using where
-1 PRIMARY B ALL NULL NULL NULL NULL 10 Using join buffer
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 10
+1 PRIMARY B ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join)
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 10 Using where
explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
-2 SUBQUERY A ALL NULL NULL NULL NULL 10
-2 SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer
+2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join)
explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
-2 SUBQUERY A ALL NULL NULL NULL NULL 10
-2 SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer
+2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join)
explain select straight_join * from t2 X, t2 Y
where X.a in (select straight_join A.a from t1 A, t1 B);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY X ALL NULL NULL NULL NULL 10 Using where
-1 PRIMARY Y ALL NULL NULL NULL NULL 10 Using join buffer
-2 SUBQUERY A ALL NULL NULL NULL NULL 10
-2 SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer
+1 PRIMARY Y ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join)
+2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join)
create table t0 (a int, b int);
insert into t0 values(1,1);
explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t0 system NULL NULL NULL NULL 1
-1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 10
-1 PRIMARY t3 ref a a 5 test.t2.a 10 Using join buffer
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 10 Using where
+1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where; Start temporary
+1 PRIMARY t3 ref a a 5 test.t2.a 10 End temporary; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
create table t4 as select a as x, a as y from t1;
explain select * from t0, t3 where (t3.a, t3.b) in (select x,y from t4) and (t3.a < 10 or t3.a >30);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t0 system NULL NULL NULL NULL 1
-1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 10
-1 PRIMARY t3 ref a a 5 test.t4.x 10 Using where; Using join buffer
-2 SUBQUERY t4 ALL NULL NULL NULL NULL 10 Using where
+1 PRIMARY t4 ALL NULL NULL NULL NULL 10 Using where; Start temporary
+1 PRIMARY t3 ref a a 5 test.t4.x 10 Using where; End temporary; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
drop table t0,t1,t2,t3,t4;
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
@@ -1265,11 +1267,11 @@ create table t2 as select * from t1;
explain select * from t2 where a in (select b from t1 where a=3);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range a a 5 NULL 8 Using where; Using index; LooseScan
-1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer
+1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join)
explain select * from t2 where (b,a) in (select a,b from t1 where a=3);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range a a 5 NULL 8 Using where; Using index; LooseScan
-1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer
+1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join)
drop table t1,t2;
create table t1 (a int, b int);
insert into t1 select a,a from t0;
@@ -1278,18 +1280,16 @@ insert into t2 select A.a + 10*B.a, A.a + 10*B.a from t0 A, t0 B;
set @@optimizer_switch='firstmatch=off';
explain select * from t1 where (a,b) in (select a,b from t2);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 10
-1 PRIMARY subselect2 eq_ref unique_key unique_key 10 func 1
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 100
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where; Start temporary
+1 PRIMARY t2 hash_ALL NULL #hash#$hj 10 test.t1.a,test.t1.b 100 Using where; End temporary; Using join buffer (flat, BNLH join)
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);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 10
-1 PRIMARY subselect2 eq_ref unique_key unique_key 10 func 1
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 100
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where; Start temporary
+1 PRIMARY t2 hash_ALL NULL #hash#$hj 10 test.t1.a,test.t1.b 100 Using where; End temporary; Using join buffer (flat, BNLH join)
set @@optimizer_search_depth=@save_optimizer_search_depth;
-set @@optimizer_switch=default;
+set @@optimizer_switch=@save_optimizer_switch;
drop table t0, t1, t2;
create table t0 (a decimal(4,2));
insert into t0 values (10.24), (22.11);
@@ -1297,8 +1297,8 @@ create table t1 as select * from t0;
insert into t1 select * from t0;
explain select * from t0 where a in (select a from t1);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t0 ALL NULL NULL NULL NULL 2
-1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; FirstMatch(t0); Using join buffer
+1 PRIMARY t0 ALL NULL NULL NULL NULL 2 Using where; Start temporary
+1 PRIMARY t1 hash_ALL NULL #hash#$hj 3 test.t0.a 4 Using where; End temporary; Using join buffer (flat, BNLH join)
select * from t0 where a in (select a from t1);
a
10.24
@@ -1310,8 +1310,8 @@ create table t1 as select * from t0;
insert into t1 select * from t0;
explain select * from t0 where a in (select a from t1);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t0 ALL NULL NULL NULL NULL 2
-1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; FirstMatch(t0); Using join buffer
+1 PRIMARY t0 ALL NULL NULL NULL NULL 2 Using where; Start temporary
+1 PRIMARY t1 hash_ALL NULL #hash#$hj 4 test.t0.a 4 Using where; End temporary; Using join buffer (flat, BNLH join)
select * from t0 where a in (select a from t1);
a
2008-01-01
@@ -1324,11 +1324,10 @@ create table t2 as select a as a, a as b from t0 where a < 3;
insert into t2 select * from t2;
explain select * from t1 where (a,b,c) in (select X.a, Y.a, Z.a from t2 X, t2 Y, t2 Z where X.b=33);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3
-1 PRIMARY subselect2 eq_ref unique_key unique_key 15 func 1
-2 SUBQUERY X ALL NULL NULL NULL NULL 6 Using where
-2 SUBQUERY Y ALL NULL NULL NULL NULL 6 Using join buffer
-2 SUBQUERY Z ALL NULL NULL NULL NULL 6 Using join buffer
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Start temporary
+1 PRIMARY X hash_ALL NULL #hash#$hj 5 test.t1.a 6 Using where; Using join buffer (flat, BNLH join)
+1 PRIMARY Y hash_ALL NULL #hash#$hj 5 test.t1.b 6 Using where; Using join buffer (incremental, BNLH join)
+1 PRIMARY Z hash_ALL NULL #hash#$hj 5 test.t1.c 6 Using where; End temporary; Using join buffer (incremental, BNLH join)
drop table t0,t1,t2;
BUG#37842: Assertion in DsMrr_impl::dsmrr_init, at handler.cc:4307
@@ -1398,10 +1397,9 @@ INNER JOIN t2 c ON c.idContact=cona.idContact
WHERE cona.postalStripped='T2H3B2'
);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 2 1.00
-1 PRIMARY a index PRIMARY PRIMARY 4 NULL 2 100.00 Using where; Using index; Using join buffer
-2 SUBQUERY cona ALL NULL NULL NULL NULL 2 100.00 Using where
-2 SUBQUERY c eq_ref PRIMARY PRIMARY 4 test.cona.idContact 1 100.00 Using join buffer
+1 PRIMARY cona ALL NULL NULL NULL NULL 2 100.00 Using where; Start temporary
+1 PRIMARY c eq_ref PRIMARY PRIMARY 4 test.cona.idContact 1 100.00 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.c.idObj 1 100.00 Using index; End temporary
Warnings:
Note 1003 select `test`.`a`.`idIndividual` AS `idIndividual` from `test`.`t1` `a` semi join (`test`.`t3` `cona` join `test`.`t2` `c`) where ((`test`.`c`.`idContact` = `test`.`cona`.`idContact`) and (`test`.`a`.`idIndividual` = `test`.`c`.`idObj`) and (`test`.`cona`.`postalStripped` = 'T2H3B2'))
drop table t1,t2,t3;
@@ -1430,7 +1428,9 @@ CALL p1;
ERROR 42S22: Unknown column 'f1' in 'where clause'
DROP PROCEDURE p1;
DROP TABLE t1, t2;
+set @@optimizer_switch=@save_optimizer_switch;
set join_cache_level=default;
show variables like 'join_cache_level';
Variable_name Value
join_cache_level 1
+set @@optimizer_switch=@save_optimizer_switch;
View Lorry Controller Status