summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/subselect.result8
-rw-r--r--mysql-test/r/subselect3.result48
-rw-r--r--mysql-test/r/subselect3_jcl6.result48
-rw-r--r--mysql-test/r/subselect_extra.result20
-rw-r--r--mysql-test/r/subselect_no_mat.result8
-rw-r--r--mysql-test/r/subselect_no_scache.result8
-rw-r--r--mysql-test/r/subselect_sj.result40
-rw-r--r--mysql-test/r/subselect_sj2.result6
-rw-r--r--mysql-test/r/subselect_sj2_jcl6.result6
-rw-r--r--mysql-test/r/subselect_sj2_mat.result6
-rw-r--r--mysql-test/r/subselect_sj_jcl6.result46
-rw-r--r--mysql-test/r/subselect_sj_mat.result98
-rw-r--r--mysql-test/t/subselect_sj2.test2
-rw-r--r--sql/opt_subselect.cc153
-rw-r--r--sql/opt_subselect.h3
-rw-r--r--sql/sql_select.cc10
-rw-r--r--sql/sql_select.h2
17 files changed, 263 insertions, 249 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index b87aa59a037..be6f644a8d2 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -5146,8 +5146,8 @@ explain SELECT * FROM ot1,ot4
WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
FROM it2,it3);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY ot1 ALL NULL NULL NULL NULL 2 Start temporary
-1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2
+1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Using where; Start temporary; Using join buffer (flat, BNL join)
1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using join buffer (flat, BNL join)
1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; End temporary; Using join buffer (flat, BNL join)
DROP TABLE IF EXISTS ot1, ot4, it2, it3;
@@ -5192,8 +5192,8 @@ INSERT INTO t2 VALUES (12,2);
INSERT INTO t2 VALUES (15,4);
EXPLAIN SELECT * FROM t1 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON 1);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 3 Start temporary
-1 PRIMARY t2 index NULL PRIMARY 4 NULL 3 Using index; Using join buffer (flat, BNL join)
+1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 3
+1 PRIMARY t2 index NULL PRIMARY 4 NULL 3 Using index; Start temporary; Using join buffer (flat, BNL join)
1 PRIMARY it eq_ref PRIMARY PRIMARY 4 test.t1.pk 1 Using index; End temporary
SELECT * FROM t1 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON 1);
pk i
diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result
index 1279fc6b8f4..c945efb4448 100644
--- a/mysql-test/r/subselect3.result
+++ b/mysql-test/r/subselect3.result
@@ -1045,8 +1045,8 @@ 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; Start temporary
-2 DEPENDENT SUBQUERY Z ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (flat, BNL join)
+2 DEPENDENT SUBQUERY Y ALL NULL NULL NULL NULL 2 Using where
+2 DEPENDENT SUBQUERY Z ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary; Using join buffer (flat, BNL 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
@@ -1186,27 +1186,27 @@ 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 (flat, BNL join)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Start temporary; End temporary; Using join buffer (flat, BNL 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 (flat, BNL join)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary; Using join buffer (flat, BNL 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 (flat, BNL join)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2
+1 PRIMARY t3 ALL NULL NULL NULL NULL 6 Using where; Start temporary; 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 (flat, BNL join)
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4
+1 PRIMARY t3 ALL NULL NULL NULL NULL 6 Using where; Start temporary; 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 (flat, BNL join)
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
drop table t1;
set @@optimizer_switch=@save_optimizer_switch;
create table t1 (a int);
@@ -1216,8 +1216,8 @@ 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; Start temporary
-1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where
+1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where; Start temporary; Using join buffer (flat, BNL join)
1 PRIMARY t3 ref a a 5 test.t2.a 10 End temporary
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
@@ -1276,14 +1276,14 @@ 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 Start temporary
-1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; End temporary; Using join buffer (flat, BNL join)
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10
+1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Start temporary; End temporary; Using join buffer (flat, BNL 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 Start temporary
-1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; End temporary; Using join buffer (flat, BNL join)
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10
+1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
set @@optimizer_search_depth=@save_optimizer_search_depth;
set @@optimizer_switch=@save_optimizer_switch;
drop table t0, t1, t2;
@@ -1293,8 +1293,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 Start temporary
-1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; End temporary; Using join buffer (flat, BNL join)
+1 PRIMARY t0 ALL NULL NULL NULL NULL 2
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
select * from t0 where a in (select a from t1);
a
10.24
@@ -1306,8 +1306,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 Start temporary
-1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; End temporary; Using join buffer (flat, BNL join)
+1 PRIMARY t0 ALL NULL NULL NULL NULL 2
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
select * from t0 where a in (select a from t1);
a
2008-01-01
@@ -1320,8 +1320,8 @@ 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 Start temporary
-1 PRIMARY X ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3
+1 PRIMARY X ALL NULL NULL NULL NULL 6 Using where; Start temporary; Using join buffer (flat, BNL join)
1 PRIMARY Y ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join)
1 PRIMARY Z ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (flat, BNL join)
drop table t0,t1,t2;
diff --git a/mysql-test/r/subselect3_jcl6.result b/mysql-test/r/subselect3_jcl6.result
index 15dd920d95d..8c8e448a836 100644
--- a/mysql-test/r/subselect3_jcl6.result
+++ b/mysql-test/r/subselect3_jcl6.result
@@ -1054,8 +1054,8 @@ 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; 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)
+2 DEPENDENT SUBQUERY Y ALL NULL NULL NULL NULL 2 Using where
+2 DEPENDENT SUBQUERY Z hash_ALL NULL #hash#$hj 5 test.Y.a 2 Using where; Start temporary; 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
@@ -1195,27 +1195,27 @@ 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 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)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
+1 PRIMARY t1 hash_ALL NULL #hash#$hj 4 test.t2.a 4 Using where; Start temporary; 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 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)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
+1 PRIMARY t2 hash_ALL NULL #hash#$hj 5 test.t2.a 2 Using where; Start temporary; 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 (flat, BNL join)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2
+1 PRIMARY t3 ALL NULL NULL NULL NULL 6 Using where; Start temporary; 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 (flat, BNL join)
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4
+1 PRIMARY t3 ALL NULL NULL NULL NULL 6 Using where; Start temporary; 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 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)
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
+1 PRIMARY t1 hash_ALL NULL #hash#$hj 6 test.t1.a 2 Using where; Start temporary; End temporary; Using join buffer (flat, BNLH join)
drop table t1;
set @@optimizer_switch=@save_optimizer_switch;
create table t1 (a int);
@@ -1225,8 +1225,8 @@ 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; Start temporary
-1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where
+1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where; Start temporary; 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
@@ -1285,14 +1285,14 @@ 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 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)
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where
+1 PRIMARY t2 hash_ALL NULL #hash#$hj 10 test.t1.a,test.t1.b 100 Using where; Start temporary; 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 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)
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where
+1 PRIMARY t2 hash_ALL NULL #hash#$hj 10 test.t1.a,test.t1.b 100 Using where; Start temporary; End temporary; Using join buffer (flat, BNLH join)
set @@optimizer_search_depth=@save_optimizer_search_depth;
set @@optimizer_switch=@save_optimizer_switch;
drop table t0, t1, t2;
@@ -1302,8 +1302,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 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)
+1 PRIMARY t0 ALL NULL NULL NULL NULL 2 Using where
+1 PRIMARY t1 hash_ALL NULL #hash#$hj 3 test.t0.a 4 Using where; Start temporary; End temporary; Using join buffer (flat, BNLH join)
select * from t0 where a in (select a from t1);
a
10.24
@@ -1315,8 +1315,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 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)
+1 PRIMARY t0 ALL NULL NULL NULL NULL 2 Using where
+1 PRIMARY t1 hash_ALL NULL #hash#$hj 4 test.t0.a 4 Using where; Start temporary; End temporary; Using join buffer (flat, BNLH join)
select * from t0 where a in (select a from t1);
a
2008-01-01
@@ -1329,8 +1329,8 @@ 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 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 t1 ALL NULL NULL NULL NULL 3 Using where
+1 PRIMARY X hash_ALL NULL #hash#$hj 5 test.t1.a 6 Using where; Start temporary; 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;
diff --git a/mysql-test/r/subselect_extra.result b/mysql-test/r/subselect_extra.result
index 3dfe2bab4a6..30b5586a0d6 100644
--- a/mysql-test/r/subselect_extra.result
+++ b/mysql-test/r/subselect_extra.result
@@ -12,16 +12,16 @@ insert into t2 values ('2001-01-01 1:1:1'), ('2001-01-01 1:1:1');
flush tables;
EXPLAIN SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN (SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.dt IS NULL );
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using where; Start temporary
-1 PRIMARY INNR ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (flat, BNL join)
+1 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using where
+1 PRIMARY INNR ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
flush tables;
SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN (SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.dt IS NULL );
dt
flush tables;
EXPLAIN SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.t < '2005-11-13 7:41:31' );
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using where; Start temporary
-1 PRIMARY INNR ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (flat, BNL join)
+1 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using where
+1 PRIMARY INNR ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
flush tables;
SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.t < '2005-11-13 7:41:31' );
dt
@@ -64,8 +64,8 @@ explain extended
select * from t1
where id in (select id from t1 as x1 where (t1.cur_date is null));
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Start temporary
-1 PRIMARY x1 ALL NULL NULL NULL NULL 2 100.00 Using where; End temporary; Using join buffer (flat, BNL join)
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
+1 PRIMARY x1 ALL NULL NULL NULL NULL 2 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
Warnings:
Note 1276 Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`cur_date` AS `cur_date` from `test`.`t1` semi join (`test`.`t1` `x1`) where ((`test`.`x1`.`id` = `test`.`t1`.`id`) and (`test`.`t1`.`cur_date` = 0))
@@ -76,8 +76,8 @@ explain extended
select * from t2
where id in (select id from t2 as x1 where (t2.cur_date is null));
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 Using where; Start temporary
-1 PRIMARY x1 ALL NULL NULL NULL NULL 2 100.00 Using where; End temporary; Using join buffer (flat, BNL join)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
+1 PRIMARY x1 ALL NULL NULL NULL NULL 2 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
Warnings:
Note 1276 Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`id` AS `id`,`test`.`t2`.`cur_date` AS `cur_date` from `test`.`t2` semi join (`test`.`t2` `x1`) where ((`test`.`x1`.`id` = `test`.`t2`.`id`) and (`test`.`t2`.`cur_date` = 0))
@@ -427,8 +427,8 @@ WHERE t3.b IN (SELECT v1.b FROM v1, t2
WHERE t2.c = v1.c AND t2.c = v1.b AND v1.b = t3.c);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 system NULL NULL NULL NULL 1
-1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; Start temporary
-1 PRIMARY <derived3> ALL NULL NULL NULL NULL 3 Using where; End temporary; Using join buffer (flat, BNL join)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where
+1 PRIMARY <derived3> ALL NULL NULL NULL NULL 3 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
3 DERIVED t1 ALL NULL NULL NULL NULL 3
SELECT * FROM t3
WHERE t3.b IN (SELECT v1.b FROM v1, t2
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index 4827524ae63..df0d5fe8460 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -5151,8 +5151,8 @@ explain SELECT * FROM ot1,ot4
WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
FROM it2,it3);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY ot1 ALL NULL NULL NULL NULL 2 Start temporary
-1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2
+1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Using where; Start temporary; Using join buffer (flat, BNL join)
1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using join buffer (flat, BNL join)
1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; End temporary; Using join buffer (flat, BNL join)
DROP TABLE IF EXISTS ot1, ot4, it2, it3;
@@ -5197,8 +5197,8 @@ INSERT INTO t2 VALUES (12,2);
INSERT INTO t2 VALUES (15,4);
EXPLAIN SELECT * FROM t1 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON 1);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 3 Start temporary
-1 PRIMARY t2 index NULL PRIMARY 4 NULL 3 Using index; Using join buffer (flat, BNL join)
+1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 3
+1 PRIMARY t2 index NULL PRIMARY 4 NULL 3 Using index; Start temporary; Using join buffer (flat, BNL join)
1 PRIMARY it eq_ref PRIMARY PRIMARY 4 test.t1.pk 1 Using index; End temporary
SELECT * FROM t1 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON 1);
pk i
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index 756431a2c6b..0a88d82e3d8 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -5150,8 +5150,8 @@ explain SELECT * FROM ot1,ot4
WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
FROM it2,it3);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY ot1 ALL NULL NULL NULL NULL 2 Start temporary
-1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2
+1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Using where; Start temporary; Using join buffer (flat, BNL join)
1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using join buffer (flat, BNL join)
1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; End temporary; Using join buffer (flat, BNL join)
DROP TABLE IF EXISTS ot1, ot4, it2, it3;
@@ -5196,8 +5196,8 @@ INSERT INTO t2 VALUES (12,2);
INSERT INTO t2 VALUES (15,4);
EXPLAIN SELECT * FROM t1 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON 1);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 3 Start temporary
-1 PRIMARY t2 index NULL PRIMARY 4 NULL 3 Using index; Using join buffer (flat, BNL join)
+1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 3
+1 PRIMARY t2 index NULL PRIMARY 4 NULL 3 Using index; Start temporary; Using join buffer (flat, BNL join)
1 PRIMARY it eq_ref PRIMARY PRIMARY 4 test.t1.pk 1 Using index; End temporary
SELECT * FROM t1 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON 1);
pk i
diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result
index 99d8e9d2d77..2a4d7cd7f1e 100644
--- a/mysql-test/r/subselect_sj.result
+++ b/mysql-test/r/subselect_sj.result
@@ -1046,9 +1046,9 @@ WHERE t2.val LIKE 'a%' OR t2.val LIKE 'e%')
AND t1.val IN (SELECT t3.val FROM t3
WHERE t3.val LIKE 'a%' OR t3.val LIKE 'e%');
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Start temporary
-1 PRIMARY t3 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join)
-1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (flat, BNL join)
+1 PRIMARY t1 ALL NULL NULL NULL NULL 5
+1 PRIMARY t3 ALL NULL NULL NULL NULL 5 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; FirstMatch(t3)
SELECT *
FROM t1
WHERE t1.val IN (SELECT t2.val FROM t2
@@ -1237,8 +1237,8 @@ A.t1field IN (SELECT A.t1field FROM t2 B) AND
A.t1field IN (SELECT C.t2field FROM t2 C
WHERE C.t2field IN (SELECT D.t2field FROM t2 D));
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY A index PRIMARY PRIMARY 4 NULL 3 Using index; Start temporary
-1 PRIMARY B index NULL PRIMARY 4 NULL 3 Using index; End temporary; Using join buffer (flat, BNL join)
+1 PRIMARY A index PRIMARY PRIMARY 4 NULL 3 Using index
+1 PRIMARY B index NULL PRIMARY 4 NULL 3 Using index; Start temporary; End temporary; Using join buffer (flat, BNL join)
1 PRIMARY C eq_ref PRIMARY PRIMARY 4 test.A.t1field 1 Using index
1 PRIMARY D eq_ref PRIMARY PRIMARY 4 test.A.t1field 1 Using index
SELECT * FROM t1 A
@@ -1266,10 +1266,10 @@ explain
select * from t1 A, t1 B
where A.a = B.a and A.a in (select a from t2 C) and B.a in (select a from t2 D);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY A ALL NULL NULL NULL NULL 3 Start temporary
+1 PRIMARY A ALL NULL NULL NULL NULL 3
+1 PRIMARY C ALL NULL NULL NULL NULL 3 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
1 PRIMARY B ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join)
-1 PRIMARY C ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join)
-1 PRIMARY D ALL NULL NULL NULL NULL 3 Using where; End temporary; Using join buffer (flat, BNL join)
+1 PRIMARY D ALL NULL NULL NULL NULL 3 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
drop table t1, t2;
#
# BUG#784441: Abort on semijoin with a view as the inner table
@@ -1431,8 +1431,8 @@ set optimizer_switch='firstmatch=off,loosescan=off,materialization=off';
explain
select * from t0 where a in (select t1.a from t1 left join t2 on t1.a=t2.a);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t0 ALL NULL NULL NULL NULL 2 Start temporary
-1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY t0 ALL NULL NULL NULL NULL 2
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Start temporary; Using join buffer (flat, BNL join)
1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where; End temporary
select * from t0 where a in (select t1.a from t1 left join t2 on t1.a=t2.a);
a
@@ -1490,8 +1490,8 @@ explain
select * from t0
where a in (select t1.a from t1 left join (t3 join t2 on t3.b=t2.b) on t1.a=t3.a);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t0 ALL NULL NULL NULL NULL 2 Start temporary
-1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY t0 ALL NULL NULL NULL NULL 2
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Start temporary; Using join buffer (flat, BNL join)
1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where
1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where; End temporary
select * from t0
@@ -1508,8 +1508,8 @@ explain
select * from t0
where a in (select t1.a from t1 left join (t3 join t2 on t3.b=t2.b) on t1.a=t3.a);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t0 ALL NULL NULL NULL NULL 2 Start temporary
-1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY t0 ALL NULL NULL NULL NULL 2
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Start temporary; Using join buffer (flat, BNL join)
1 PRIMARY t2 ALL NULL NULL NULL NULL 1
1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End temporary
select * from t0
@@ -1703,10 +1703,10 @@ INSERT INTO t4 VALUES (0),(NULL);
explain
SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3) WHERE IFNULL(t2.f3,'foo') IN (SELECT * FROM t4);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Start temporary
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
1 PRIMARY t3 ALL NULL NULL NULL NULL 5 Using where
-1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (flat, BNL join)
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3 ) WHERE IFNULL(t2.f3,'foo') IN (SELECT * FROM t4);
f1 f2 f3 f3
2 0 0 0
@@ -1764,9 +1764,9 @@ INSERT INTO t3 VALUES (6,5),(6,2),(8,0),(9,1),(6,5);
explain
SELECT * FROM t1, t2 WHERE (t2.a , t1.b) IN (SELECT a, b FROM t3);
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
1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 2 Using index; Using join buffer (flat, BNL join)
-1 PRIMARY t3 ALL b NULL NULL NULL 5 Using where; End temporary; Using join buffer (flat, BNL join)
+1 PRIMARY t3 ALL b NULL NULL NULL 5 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
SELECT * FROM t1, t2 WHERE (t2.a , t1.b) IN (SELECT a, b FROM t3);
b a
5 6
@@ -1869,10 +1869,10 @@ alias1.c IN (SELECT SQ3_alias1.b
FROM t2 AS SQ3_alias1 STRAIGHT_JOIN t2 AS SQ3_alias2)
LIMIT 100;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY <derived2> ALL NULL NULL NULL NULL 20 Start temporary
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 20
1 PRIMARY alias2 ALL NULL NULL NULL NULL 20 Using join buffer (flat, BNL join)
1 PRIMARY t2 ALL NULL NULL NULL NULL 20 Using join buffer (flat, BNL join)
-1 PRIMARY SQ3_alias1 ALL NULL NULL NULL NULL 20 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY SQ3_alias1 ALL NULL NULL NULL NULL 20 Using where; Start temporary; Using join buffer (flat, BNL join)
1 PRIMARY SQ3_alias2 index NULL PRIMARY 4 NULL 20 Using index; End temporary; Using join buffer (flat, BNL join)
2 DERIVED t2 ALL NULL NULL NULL NULL 20
create table t3 as
diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result
index fe9136cfccc..ba347b9ef78 100644
--- a/mysql-test/r/subselect_sj2.result
+++ b/mysql-test/r/subselect_sj2.result
@@ -1,7 +1,7 @@
set @subselect_sj2_tmp= @@optimizer_switch;
set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on';
set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
-drop table if exists t0, t1, t2, t3;
+drop table if exists t0, t1, t2, t3, t4, t5;
drop view if exists v1;
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
@@ -864,10 +864,10 @@ EXPLAIN
SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a
WHERE t3.b IN (SELECT b FROM t4);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Start temporary
+1 PRIMARY t3 ALL NULL NULL NULL NULL 1
1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where
1 PRIMARY <derived3> ALL NULL NULL NULL NULL 2
-1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (flat, BNL join)
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
3 DERIVED t1 ALL NULL NULL NULL NULL 1
SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a
WHERE t3.b IN (SELECT b FROM t4);
diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result
index 0f85f87096d..834ea35c315 100644
--- a/mysql-test/r/subselect_sj2_jcl6.result
+++ b/mysql-test/r/subselect_sj2_jcl6.result
@@ -10,7 +10,7 @@ join_cache_level 6
set @subselect_sj2_tmp= @@optimizer_switch;
set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on';
set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
-drop table if exists t0, t1, t2, t3;
+drop table if exists t0, t1, t2, t3, t4, t5;
drop view if exists v1;
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
@@ -875,10 +875,10 @@ EXPLAIN
SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a
WHERE t3.b IN (SELECT b FROM t4);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where; Start temporary
+1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where
1 PRIMARY t2 hash_ALL NULL #hash#$hj 5 test.t3.a 1 Using where; Using join buffer (flat, BNLH join)
1 PRIMARY <derived3> ALL NULL NULL NULL NULL 2 Using join buffer (incremental, BNL join)
-1 PRIMARY t4 hash_ALL NULL #hash#$hj 5 test.t3.b 2 Using where; End temporary; Using join buffer (incremental, BNLH join)
+1 PRIMARY t4 hash_ALL NULL #hash#$hj 5 test.t3.b 2 Using where; Start temporary; End temporary; Using join buffer (incremental, BNLH join)
3 DERIVED t1 ALL NULL NULL NULL NULL 1
SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a
WHERE t3.b IN (SELECT b FROM t4);
diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result
index 75d61d42c31..21635fa0a55 100644
--- a/mysql-test/r/subselect_sj2_mat.result
+++ b/mysql-test/r/subselect_sj2_mat.result
@@ -3,7 +3,7 @@ set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
set @subselect_sj2_tmp= @@optimizer_switch;
set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on';
set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
-drop table if exists t0, t1, t2, t3;
+drop table if exists t0, t1, t2, t3, t4, t5;
drop view if exists v1;
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
@@ -333,8 +333,8 @@ WHERE Language='English' AND Percentage > 10 AND
t2.Population > 100000);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range Population,Country Population 4 NULL 1 Using index condition; Rowid-ordered scan; Start temporary
-1 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t1.Country 1 Using where
-1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t1.Country,const 1 Using index condition; Using where; End temporary
+1 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t1.Country 1 Using where; End temporary
+1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t1.Country,const 1 Using index condition; Using where
set optimizer_switch=@bug35674_save_optimizer_switch;
DROP TABLE t1,t2,t3;
CREATE TABLE t1 (
diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result
index 2d1da2ea953..6fd1ed810ff 100644
--- a/mysql-test/r/subselect_sj_jcl6.result
+++ b/mysql-test/r/subselect_sj_jcl6.result
@@ -1057,9 +1057,9 @@ WHERE t2.val LIKE 'a%' OR t2.val LIKE 'e%')
AND t1.val IN (SELECT t3.val FROM t3
WHERE t3.val LIKE 'a%' OR t3.val LIKE 'e%');
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Start temporary
-1 PRIMARY t3 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join)
-1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (incremental, BNL join)
+1 PRIMARY t1 ALL NULL NULL NULL NULL 5
+1 PRIMARY t3 ALL NULL NULL NULL NULL 5 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; FirstMatch(t3); Using join buffer (incremental, BNL join)
SELECT *
FROM t1
WHERE t1.val IN (SELECT t2.val FROM t2
@@ -1248,8 +1248,8 @@ A.t1field IN (SELECT A.t1field FROM t2 B) AND
A.t1field IN (SELECT C.t2field FROM t2 C
WHERE C.t2field IN (SELECT D.t2field FROM t2 D));
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY A index PRIMARY PRIMARY 4 NULL 3 Using index; Start temporary
-1 PRIMARY B index NULL PRIMARY 4 NULL 3 Using index; End temporary; Using join buffer (flat, BNL join)
+1 PRIMARY A index PRIMARY PRIMARY 4 NULL 3 Using index
+1 PRIMARY B index NULL PRIMARY 4 NULL 3 Using index; Start temporary; End temporary; Using join buffer (flat, BNL join)
1 PRIMARY C eq_ref PRIMARY PRIMARY 4 test.A.t1field 1 Using index
1 PRIMARY D eq_ref PRIMARY PRIMARY 4 test.A.t1field 1 Using index
SELECT * FROM t1 A
@@ -1277,10 +1277,10 @@ explain
select * from t1 A, t1 B
where A.a = B.a and A.a in (select a from t2 C) and B.a in (select a from t2 D);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY A ALL NULL NULL NULL NULL 3 Start temporary
-1 PRIMARY B ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join)
-1 PRIMARY C ALL NULL NULL NULL NULL 3 Using where; Using join buffer (incremental, BNL join)
-1 PRIMARY D ALL NULL NULL NULL NULL 3 Using where; End temporary; Using join buffer (incremental, BNL join)
+1 PRIMARY A ALL NULL NULL NULL NULL 3
+1 PRIMARY C ALL NULL NULL NULL NULL 3 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
+1 PRIMARY B ALL NULL NULL NULL NULL 3 Using where; Using join buffer (incremental, BNL join)
+1 PRIMARY D ALL NULL NULL NULL NULL 3 Using where; Start temporary; End temporary; Using join buffer (incremental, BNL join)
drop table t1, t2;
#
# BUG#784441: Abort on semijoin with a view as the inner table
@@ -1442,8 +1442,8 @@ set optimizer_switch='firstmatch=off,loosescan=off,materialization=off';
explain
select * from t0 where a in (select t1.a from t1 left join t2 on t1.a=t2.a);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t0 ALL NULL NULL NULL NULL 2 Start temporary
-1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY t0 ALL NULL NULL NULL NULL 2
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Start temporary; Using join buffer (flat, BNL join)
1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where; End temporary; Using join buffer (incremental, BNL join)
select * from t0 where a in (select t1.a from t1 left join t2 on t1.a=t2.a);
a
@@ -1501,8 +1501,8 @@ explain
select * from t0
where a in (select t1.a from t1 left join (t3 join t2 on t3.b=t2.b) on t1.a=t3.a);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t0 ALL NULL NULL NULL NULL 2 Start temporary
-1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY t0 ALL NULL NULL NULL NULL 2
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Start temporary; Using join buffer (flat, BNL join)
1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join)
1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where; End temporary; Using join buffer (incremental, BNL join)
select * from t0
@@ -1519,8 +1519,8 @@ explain
select * from t0
where a in (select t1.a from t1 left join (t3 join t2 on t3.b=t2.b) on t1.a=t3.a);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t0 ALL NULL NULL NULL NULL 2 Start temporary
-1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY t0 ALL NULL NULL NULL NULL 2
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Start temporary; Using join buffer (flat, BNL join)
1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using join buffer (incremental, BNL join)
1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (incremental, BNL join)
select * from t0
@@ -1714,10 +1714,10 @@ INSERT INTO t4 VALUES (0),(NULL);
explain
SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3) WHERE IFNULL(t2.f3,'foo') IN (SELECT * FROM t4);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Start temporary
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
1 PRIMARY t3 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (incremental, BNL join)
-1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (incremental, BNL join)
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary; Using join buffer (incremental, BNL join)
SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3 ) WHERE IFNULL(t2.f3,'foo') IN (SELECT * FROM t4);
f1 f2 f3 f3
2 0 0 0
@@ -1775,9 +1775,9 @@ INSERT INTO t3 VALUES (6,5),(6,2),(8,0),(9,1),(6,5);
explain
SELECT * FROM t1, t2 WHERE (t2.a , t1.b) IN (SELECT a, b FROM t3);
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
1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 2 Using index; Using join buffer (flat, BNL join)
-1 PRIMARY t3 ALL b NULL NULL NULL 5 Using where; End temporary; Using join buffer (incremental, BNL join)
+1 PRIMARY t3 ALL b NULL NULL NULL 5 Using where; Start temporary; End temporary; Using join buffer (incremental, BNL join)
SELECT * FROM t1, t2 WHERE (t2.a , t1.b) IN (SELECT a, b FROM t3);
b a
5 6
@@ -1880,10 +1880,10 @@ alias1.c IN (SELECT SQ3_alias1.b
FROM t2 AS SQ3_alias1 STRAIGHT_JOIN t2 AS SQ3_alias2)
LIMIT 100;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY <derived2> ALL NULL NULL NULL NULL 20 Start temporary
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 20
1 PRIMARY alias2 ALL NULL NULL NULL NULL 20 Using join buffer (flat, BNL join)
1 PRIMARY t2 ALL NULL NULL NULL NULL 20 Using join buffer (incremental, BNL join)
-1 PRIMARY SQ3_alias1 ALL NULL NULL NULL NULL 20 Using where; Using join buffer (incremental, BNL join)
+1 PRIMARY SQ3_alias1 ALL NULL NULL NULL NULL 20 Using where; Start temporary; Using join buffer (incremental, BNL join)
1 PRIMARY SQ3_alias2 index NULL PRIMARY 4 NULL 20 Using index; End temporary; Using join buffer (incremental, BNL join)
2 DERIVED t2 ALL NULL NULL NULL NULL 20
create table t3 as
@@ -2050,8 +2050,8 @@ explain
SELECT * FROM t0 WHERE t0.a IN
(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t0 ALL NULL NULL NULL NULL 5 Using where; Start temporary
-1 PRIMARY t1 ref a a 5 test.t0.a 1 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1 PRIMARY t0 ALL NULL NULL NULL NULL 5 Using where
+1 PRIMARY t1 ref a a 5 test.t0.a 1 Start temporary; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using where; End temporary; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
SELECT * FROM t0 WHERE t0.a IN
(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result
index 465e482fb66..e6566d70ba3 100644
--- a/mysql-test/r/subselect_sj_mat.result
+++ b/mysql-test/r/subselect_sj_mat.result
@@ -297,11 +297,11 @@ where (a1, a2) in (select b1, b2 from t2 where b1 > '0') and
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 18 func,func 1 100.00
1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 18 func,func 1 100.00
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 18 func,func 1 100.00
3 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
3 SUBQUERY t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 80.00 Using where; Using index; Using join buffer (flat, BNL join)
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where
Warnings:
Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3`) where ((`test`.`t2i`.`b2` = `test`.`t3`.`c2`) and (`test`.`t2i`.`b1` = `test`.`t3`.`c1`) and (`test`.`t2`.`b1` > '0') and (`test`.`t3`.`c2` > '0'))
select * from t1
@@ -317,14 +317,12 @@ where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and
(a1, a2) in (select c1, c2 from t3i
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1i index it1i1,it1i2,it1i3 # # # 3 100.00 #
-1 PRIMARY <subquery2> eq_ref distinct_key # # # 1 100.00 #
-1 PRIMARY <subquery3> eq_ref distinct_key # # # 1 100.00 #
-2 SUBQUERY t2i index it2i1,it2i2,it2i3 # # # 5 100.00 #
-3 SUBQUERY t2i index it2i1,it2i2,it2i3 # # # 5 100.00 #
-3 SUBQUERY t3i index it3i1,it3i2,it3i3 # # # 4 75.00 #
+1 PRIMARY t2i index it2i1,it2i2,it2i3 # # # 5 40.00 #
+1 PRIMARY t1i ref it1i1,it1i2,it1i3 # # # 1 100.00 #
+1 PRIMARY t3i ref it3i1,it3i2,it3i3 # # # 1 100.00 #
+1 PRIMARY t2i ref it2i1,it2i2,it2i3 # # # 2 100.00 #
Warnings:
-Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) semi join (`test`.`t2i` join `test`.`t3i`) where ((`test`.`t3i`.`c2` = `test`.`t2i`.`b2`) and (`test`.`t3i`.`c1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0') and (`test`.`t2i`.`b2` > '0'))
+Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) semi join (`test`.`t2i` join `test`.`t3i`) where ((`test`.`t1i`.`a2` = `test`.`t2i`.`b2`) and (`test`.`t3i`.`c2` = `test`.`t2i`.`b2`) and (`test`.`t2i`.`b2` = `test`.`t2i`.`b2`) and (`test`.`t1i`.`a1` = `test`.`t2i`.`b1`) and (`test`.`t3i`.`c1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0') and (`test`.`t2i`.`b2` > '0'))
select * from t1i
where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and
(a1, a2) in (select c1, c2 from t3i
@@ -341,11 +339,11 @@ b2 in (select c2 from t3 where c2 LIKE '%03')) and
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 18 func,func 1 100.00
1 PRIMARY <subquery5> eq_ref distinct_key distinct_key 18 func,func 1 100.00
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 18 func,func 1 100.00
5 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
5 SUBQUERY t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 80.00 Using where; Using index; Using join buffer (flat, BNL join)
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where
4 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
3 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
Warnings:
@@ -366,16 +364,16 @@ b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and
(a1, a2) in (select c1, c2 from t3 t3c
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Start temporary
-1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; End temporary; Using join buffer (flat, BNL join)
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
1 PRIMARY <subquery5> eq_ref distinct_key distinct_key 18 func,func 1 100.00
+1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
5 SUBQUERY t3c ALL NULL NULL NULL NULL 4 100.00 Using where
5 SUBQUERY t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 80.00 Using where; Using index; Using join buffer (flat, BNL join)
4 SUBQUERY t3b ALL NULL NULL NULL NULL 4 100.00 Using where
3 DEPENDENT SUBQUERY t3a ALL NULL NULL NULL NULL 4 100.00 Using where
Warnings:
Note 1276 Field or reference 'test.t1.a1' of SELECT #3 was resolved in SELECT #1
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3` `t3c`) where ((`test`.`t2`.`b2` = `test`.`t1`.`a2`) and (`test`.`t2i`.`b2` = `test`.`t3c`.`c2`) and (`test`.`t2`.`b1` = `test`.`t1`.`a1`) and (`test`.`t2i`.`b1` = `test`.`t3c`.`c1`) and (<expr_cache><`test`.`t2`.`b2`,`test`.`t1`.`a1`>(<in_optimizer>(`test`.`t2`.`b2`,<exists>(select `test`.`t3a`.`c2` from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and (<cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`))))) or <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3b`.`c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery4>`.`c2`))))))) and (`test`.`t3c`.`c2` > '0'))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3` `t3c`) where ((`test`.`t2i`.`b2` = `test`.`t3c`.`c2`) and (`test`.`t2`.`b2` = `test`.`t1`.`a2`) and (`test`.`t2i`.`b1` = `test`.`t3c`.`c1`) and (`test`.`t2`.`b1` = `test`.`t1`.`a1`) and (<expr_cache><`test`.`t2`.`b2`,`test`.`t1`.`a1`>(<in_optimizer>(`test`.`t2`.`b2`,<exists>(select `test`.`t3a`.`c2` from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and (<cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`))))) or <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3b`.`c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery4>`.`c2`))))))) and (`test`.`t3c`.`c2` > '0'))
select * from t1
where (a1, a2) in (select b1, b2 from t2
where b2 in (select c2 from t3 t3a where c1 = a1) or
@@ -406,15 +404,13 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 SUBQUERY t2 ALL NULL # # # 5 100.00 #
4 SUBQUERY t3 ALL NULL # # # 4 100.00 #
3 SUBQUERY t3 ALL NULL # # # 4 100.00 #
-7 UNION t1i index it1i1,it1i2,it1i3 # # # 3 100.00 #
-7 UNION <subquery8> eq_ref distinct_key # # # 1 100.00 #
-7 UNION <subquery9> eq_ref distinct_key # # # 1 100.00 #
-8 SUBQUERY t2i index it2i1,it2i2,it2i3 # # # 5 100.00 #
-9 SUBQUERY t2i index it2i1,it2i2,it2i3 # # # 5 100.00 #
-9 SUBQUERY t3i index it3i1,it3i2,it3i3 # # # 4 75.00 #
+7 UNION t2i index it2i1,it2i2,it2i3 # # # 5 40.00 #
+7 UNION t1i ref it1i1,it1i2,it1i3 # # # 1 100.00 #
+7 UNION t3i ref it3i1,it3i2,it3i3 # # # 1 100.00 #
+7 UNION t2i ref it2i1,it2i2,it2i3 # # # 2 100.00 #
NULL UNION RESULT <union1,7> ALL NULL # # # NULL NULL #
Warnings:
-Note 1003 (select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where (<expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery3>`.`c2`)))))) or <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery4>`.`c2`))))))) group by `test`.`t2`.`b1`,`test`.`t2`.`b2`) semi join (`test`.`t2i` join `test`.`t3`) join `test`.`t1` where ((`test`.`t3`.`c2` = `<subquery2>`.`b2`) and (`test`.`t1`.`a2` = `<subquery2>`.`b2`) and (`test`.`t2i`.`b2` = `<subquery2>`.`b2`) and (`test`.`t3`.`c1` = `<subquery2>`.`b1`) and (`test`.`t1`.`a1` = `<subquery2>`.`b1`) and (`test`.`t2i`.`b1` = `<subquery2>`.`b1`) and (`<subquery2>`.`b2` > '0'))) union (select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) semi join (`test`.`t2i` join `test`.`t3i`) where ((`test`.`t3i`.`c2` = `test`.`t2i`.`b2`) and (`test`.`t3i`.`c1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0') and (`test`.`t2i`.`b2` > '0')))
+Note 1003 (select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where (<expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery3>`.`c2`)))))) or <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery4>`.`c2`))))))) group by `test`.`t2`.`b1`,`test`.`t2`.`b2`) semi join (`test`.`t2i` join `test`.`t3`) join `test`.`t1` where ((`test`.`t3`.`c2` = `<subquery2>`.`b2`) and (`test`.`t1`.`a2` = `<subquery2>`.`b2`) and (`test`.`t2i`.`b2` = `<subquery2>`.`b2`) and (`test`.`t3`.`c1` = `<subquery2>`.`b1`) and (`test`.`t1`.`a1` = `<subquery2>`.`b1`) and (`test`.`t2i`.`b1` = `<subquery2>`.`b1`) and (`<subquery2>`.`b2` > '0'))) union (select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) semi join (`test`.`t2i` join `test`.`t3i`) where ((`test`.`t1i`.`a2` = `test`.`t2i`.`b2`) and (`test`.`t3i`.`c2` = `test`.`t2i`.`b2`) and (`test`.`t2i`.`b2` = `test`.`t2i`.`b2`) and (`test`.`t1i`.`a1` = `test`.`t2i`.`b1`) and (`test`.`t3i`.`c1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0') and (`test`.`t2i`.`b2` > '0')))
(select * from t1
where (a1, a2) in (select b1, b2 from t2
where b2 in (select c2 from t3 where c2 LIKE '%02') or
@@ -505,16 +501,16 @@ b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and
(a1, a2) in (select c1, c2 from t3 t3c
where (c1, c2) in (select b1, b2 from t2i where b2 > '0' or b2 = a2));
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary
-1 PRIMARY t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t1.a1,test.t1.a2 2 100.00 Using index
-1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
+1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
+1 PRIMARY t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t1.a1,test.t1.a2 2 100.00 Using index; Start temporary
1 PRIMARY t3c ALL NULL NULL NULL NULL 4 100.00 Using where; End temporary; Using join buffer (flat, BNL join)
4 SUBQUERY t3b ALL NULL NULL NULL NULL 4 100.00 Using where
3 DEPENDENT SUBQUERY t3a ALL NULL NULL NULL NULL 4 100.00 Using where
Warnings:
Note 1276 Field or reference 'test.t1.a1' of SELECT #3 was resolved in SELECT #1
Note 1276 Field or reference 'test.t1.a2' of SELECT #6 was resolved in SELECT #1
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3` `t3c`) where ((`test`.`t2i`.`b2` = `test`.`t1`.`a2`) and (`test`.`t2`.`b2` = `test`.`t1`.`a2`) and (`test`.`t3c`.`c2` = `test`.`t1`.`a2`) and (`test`.`t2i`.`b1` = `test`.`t1`.`a1`) and (`test`.`t2`.`b1` = `test`.`t1`.`a1`) and (`test`.`t3c`.`c1` = `test`.`t1`.`a1`) and (<expr_cache><`test`.`t2`.`b2`,`test`.`t1`.`a1`>(<in_optimizer>(`test`.`t2`.`b2`,<exists>(select `test`.`t3a`.`c2` from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and (<cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`))))) or <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3b`.`c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery4>`.`c2`))))))))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3` `t3c`) where ((`test`.`t2`.`b2` = `test`.`t1`.`a2`) and (`test`.`t2i`.`b2` = `test`.`t1`.`a2`) and (`test`.`t3c`.`c2` = `test`.`t1`.`a2`) and (`test`.`t2`.`b1` = `test`.`t1`.`a1`) and (`test`.`t2i`.`b1` = `test`.`t1`.`a1`) and (`test`.`t3c`.`c1` = `test`.`t1`.`a1`) and (<expr_cache><`test`.`t2`.`b2`,`test`.`t1`.`a1`>(<in_optimizer>(`test`.`t2`.`b2`,<exists>(select `test`.`t3a`.`c2` from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and (<cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`))))) or <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3b`.`c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery4>`.`c2`))))))))
explain extended
select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01');
id select_type table type possible_keys key key_len ref rows filtered Extra
@@ -617,8 +613,8 @@ explain extended select left(a1,7), left(a2,7)
from t1_16
where a1 in (select b1 from t2_16 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary
-1 PRIMARY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where; End temporary; Using join buffer (flat, BNL join)
+1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where
+1 PRIMARY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` semi join (`test`.`t2_16`) where ((`test`.`t2_16`.`b1` = `test`.`t1_16`.`a1`) and (`test`.`t1_16`.`a1` > '0'))
select left(a1,7), left(a2,7)
@@ -631,8 +627,8 @@ explain extended select left(a1,7), left(a2,7)
from t1_16
where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary
-1 PRIMARY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where; End temporary; Using join buffer (flat, BNL join)
+1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where
+1 PRIMARY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` semi join (`test`.`t2_16`) where ((`test`.`t2_16`.`b2` = `test`.`t1_16`.`a2`) and (`test`.`t2_16`.`b1` = `test`.`t1_16`.`a1`) and (`test`.`t1_16`.`a1` > '0'))
select left(a1,7), left(a2,7)
@@ -695,8 +691,8 @@ where (a1, a2) IN
where t2.b2 = substring(t2_16.b2,1,6) and
t2.b1 IN (select c1 from t3 where c2 > '0')));
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Start temporary
-1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary; Using join buffer (flat, BNL join)
1 PRIMARY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
1 PRIMARY t3 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (flat, BNL join)
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; End temporary; Using join buffer (flat, BNL join)
@@ -732,8 +728,8 @@ explain extended select left(a1,7), left(a2,7)
from t1_512
where a1 in (select b1 from t2_512 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary
-1 PRIMARY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using where; End temporary; Using join buffer (flat, BNL join)
+1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where
+1 PRIMARY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` semi join (`test`.`t2_512`) where ((`test`.`t2_512`.`b1` = `test`.`t1_512`.`a1`) and (`test`.`t1_512`.`a1` > '0'))
select left(a1,7), left(a2,7)
@@ -746,8 +742,8 @@ explain extended select left(a1,7), left(a2,7)
from t1_512
where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary
-1 PRIMARY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using where; End temporary; Using join buffer (flat, BNL join)
+1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where
+1 PRIMARY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` semi join (`test`.`t2_512`) where ((`test`.`t2_512`.`b2` = `test`.`t1_512`.`a2`) and (`test`.`t2_512`.`b1` = `test`.`t1_512`.`a1`) and (`test`.`t1_512`.`a1` > '0'))
select left(a1,7), left(a2,7)
@@ -828,8 +824,8 @@ explain extended select left(a1,7), left(a2,7)
from t1_1024
where a1 in (select b1 from t2_1024 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary
-1 PRIMARY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using where; End temporary; Using join buffer (flat, BNL join)
+1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where
+1 PRIMARY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` semi join (`test`.`t2_1024`) where ((`test`.`t2_1024`.`b1` = `test`.`t1_1024`.`a1`) and (`test`.`t1_1024`.`a1` > '0'))
select left(a1,7), left(a2,7)
@@ -842,8 +838,8 @@ explain extended select left(a1,7), left(a2,7)
from t1_1024
where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary
-1 PRIMARY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using where; End temporary; Using join buffer (flat, BNL join)
+1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where
+1 PRIMARY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` semi join (`test`.`t2_1024`) where ((`test`.`t2_1024`.`b2` = `test`.`t1_1024`.`a2`) and (`test`.`t2_1024`.`b1` = `test`.`t1_1024`.`a1`) and (`test`.`t1_1024`.`a1` > '0'))
select left(a1,7), left(a2,7)
@@ -856,8 +852,8 @@ explain extended select left(a1,7), left(a2,7)
from t1_1024
where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Start temporary
-1 PRIMARY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using where; End temporary; Using join buffer (flat, BNL join)
+1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` semi join (`test`.`t2_1024`) where ((`test`.`t2_1024`.`b1` > '0') and (`test`.`t1_1024`.`a1` = substr(`test`.`t2_1024`.`b1`,1,1024)))
select left(a1,7), left(a2,7)
@@ -923,8 +919,8 @@ explain extended select left(a1,7), left(a2,7)
from t1_1025
where a1 in (select b1 from t2_1025 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary
-1 PRIMARY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using where; End temporary; Using join buffer (flat, BNL join)
+1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where
+1 PRIMARY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` semi join (`test`.`t2_1025`) where ((`test`.`t2_1025`.`b1` = `test`.`t1_1025`.`a1`) and (`test`.`t1_1025`.`a1` > '0'))
select left(a1,7), left(a2,7)
@@ -937,8 +933,8 @@ explain extended select left(a1,7), left(a2,7)
from t1_1025
where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary
-1 PRIMARY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using where; End temporary; Using join buffer (flat, BNL join)
+1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where
+1 PRIMARY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` semi join (`test`.`t2_1025`) where ((`test`.`t2_1025`.`b2` = `test`.`t1_1025`.`a2`) and (`test`.`t2_1025`.`b1` = `test`.`t1_1025`.`a1`) and (`test`.`t1_1025`.`a1` > '0'))
select left(a1,7), left(a2,7)
@@ -951,8 +947,8 @@ explain extended select left(a1,7), left(a2,7)
from t1_1025
where a1 in (select substring(b1,1,1025) from t2_1025 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Start temporary
-1 PRIMARY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using where; End temporary; Using join buffer (flat, BNL join)
+1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` semi join (`test`.`t2_1025`) where ((`test`.`t2_1025`.`b1` > '0') and (`test`.`t1_1025`.`a1` = substr(`test`.`t2_1025`.`b1`,1,1025)))
select left(a1,7), left(a2,7)
@@ -1025,8 +1021,8 @@ explain extended select bin(a1), a2
from t1bb
where (a1, a2) in (select b1, b2 from t2bb);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1bb ALL NULL NULL NULL NULL 3 100.00 Start temporary
-1 PRIMARY t2bb ALL NULL NULL NULL NULL 3 100.00 Using where; End temporary; Using join buffer (flat, BNL join)
+1 PRIMARY t1bb ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t2bb ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select conv(`test`.`t1bb`.`a1`,10,2) AS `bin(a1)`,`test`.`t1bb`.`a2` AS `a2` from `test`.`t1bb` semi join (`test`.`t2bb`) where ((`test`.`t2bb`.`b2` = `test`.`t1bb`.`a2`) and (`test`.`t2bb`.`b1` = `test`.`t1bb`.`a1`))
select bin(a1), a2
@@ -1274,8 +1270,8 @@ GROUP BY t3i
);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
-1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 5 const 1 Start temporary
-1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (flat, BNL join)
+1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 5 const 1
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
3 SUBQUERY t3 ALL NULL NULL NULL NULL 2 Using temporary
DROP TABLE t1,t2,t3,t4;
diff --git a/mysql-test/t/subselect_sj2.test b/mysql-test/t/subselect_sj2.test
index b2721574deb..2ec15d4dfae 100644
--- a/mysql-test/t/subselect_sj2.test
+++ b/mysql-test/t/subselect_sj2.test
@@ -7,7 +7,7 @@ set @subselect_sj2_tmp= @@optimizer_switch;
set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on';
set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
--disable_warnings
-drop table if exists t0, t1, t2, t3;
+drop table if exists t0, t1, t2, t3, t4, t5;
drop view if exists v1;
--enable_warnings
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index c7d07c21746..79eb0cb9551 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -2168,7 +2168,8 @@ bool find_eq_ref_candidate(TABLE *table, table_map sj_inner_tables)
See setup_semijoin_dups_elimination() for a description of what kinds of
join prefixes each strategy can handle.
*/
-bool is_multiple_semi_joins(POSITION *prefix, uint idx, table_map inner_tables)
+
+bool is_multiple_semi_joins(JOIN *join, POSITION *prefix, uint idx, table_map inner_tables)
{
for (int i= (int)idx; i >= 0; i--)
{
@@ -2176,7 +2177,8 @@ bool is_multiple_semi_joins(POSITION *prefix, uint idx, table_map inner_tables)
if ((emb_sj_nest= prefix[i].table->emb_sj_nest))
{
if (inner_tables & emb_sj_nest->sj_inner_tables)
- return !test(inner_tables == emb_sj_nest->sj_inner_tables);
+ return !test(inner_tables == (emb_sj_nest->sj_inner_tables &
+ ~join->const_table_map));
}
}
return FALSE;
@@ -2206,6 +2208,7 @@ void advance_sj_state(JOIN *join, table_map remaining_tables, uint idx,
- attempts to build semi-join strategies here will confuse
the optimizer, so bail out.
*/
+ pos->sj_strategy= SJ_OPT_NONE;
return;
}
@@ -2290,7 +2293,7 @@ void advance_sj_state(JOIN *join, table_map remaining_tables, uint idx,
join->cur_dups_producing_tables &= ~handled_fanout;
//TODO: update bitmap of semi-joins that were handled together with
// others.
- if (is_multiple_semi_joins(join->positions, idx, handled_fanout))
+ if (is_multiple_semi_joins(join, join->positions, idx, handled_fanout))
pos->inner_tables_handled_with_other_sjs |= handled_fanout;
}
else
@@ -3906,6 +3909,80 @@ int do_sj_dups_weedout(THD *thd, SJ_TMP_TABLE *sjtbl)
}
+int init_dups_weedout(JOIN *join, uint first_table, int first_fanout_table, uint n_tables)
+{
+ THD *thd= join->thd;
+ DBUG_ENTER("init_dups_weedout");
+ SJ_TMP_TABLE::TAB sjtabs[MAX_TABLES];
+ SJ_TMP_TABLE::TAB *last_tab= sjtabs;
+ uint jt_rowid_offset= 0; // # tuple bytes are already occupied (w/o NULL bytes)
+ uint jt_null_bits= 0; // # null bits in tuple bytes
+ /*
+ Walk through the range and remember
+ - tables that need their rowids to be put into temptable
+ - the last outer table
+ */
+ for (JOIN_TAB *j=join->join_tab + first_table;
+ j < join->join_tab + first_table + n_tables; j++)
+ {
+ if (sj_table_is_included(join, j))
+ {
+ last_tab->join_tab= j;
+ last_tab->rowid_offset= jt_rowid_offset;
+ jt_rowid_offset += j->table->file->ref_length;
+ if (j->table->maybe_null)
+ {
+ last_tab->null_byte= jt_null_bits / 8;
+ last_tab->null_bit= jt_null_bits++;
+ }
+ last_tab++;
+ j->table->prepare_for_position();
+ j->keep_current_rowid= TRUE;
+ }
+ }
+
+ SJ_TMP_TABLE *sjtbl;
+ if (jt_rowid_offset) /* Temptable has at least one rowid */
+ {
+ size_t tabs_size= (last_tab - sjtabs) * sizeof(SJ_TMP_TABLE::TAB);
+ if (!(sjtbl= (SJ_TMP_TABLE*)thd->alloc(sizeof(SJ_TMP_TABLE))) ||
+ !(sjtbl->tabs= (SJ_TMP_TABLE::TAB*) thd->alloc(tabs_size)))
+ DBUG_RETURN(TRUE); /* purecov: inspected */
+ memcpy(sjtbl->tabs, sjtabs, tabs_size);
+ sjtbl->is_degenerate= FALSE;
+ sjtbl->tabs_end= sjtbl->tabs + (last_tab - sjtabs);
+ sjtbl->rowid_len= jt_rowid_offset;
+ sjtbl->null_bits= jt_null_bits;
+ sjtbl->null_bytes= (jt_null_bits + 7)/8;
+ sjtbl->tmp_table=
+ create_duplicate_weedout_tmp_table(thd,
+ sjtbl->rowid_len +
+ sjtbl->null_bytes,
+ sjtbl);
+ join->sj_tmp_tables.push_back(sjtbl->tmp_table);
+ }
+ else
+ {
+ /*
+ This is a special case where the entire subquery predicate does
+ not depend on anything at all, ie this is
+ WHERE const IN (uncorrelated select)
+ */
+ if (!(sjtbl= (SJ_TMP_TABLE*)thd->alloc(sizeof(SJ_TMP_TABLE))))
+ DBUG_RETURN(TRUE); /* purecov: inspected */
+ sjtbl->tmp_table= NULL;
+ sjtbl->is_degenerate= TRUE;
+ sjtbl->have_degenerate_row= FALSE;
+ }
+
+ sjtbl->next_flush_table= join->join_tab[first_table].flush_weedout_table;
+ join->join_tab[first_table].flush_weedout_table= sjtbl;
+ join->join_tab[first_fanout_table].first_weedout_table= sjtbl;
+ join->join_tab[first_table + n_tables - 1].check_weed_out_table= sjtbl;
+ DBUG_RETURN(0);
+}
+
+
/*
Setup the strategies to eliminate semi-join duplicates.
@@ -4006,9 +4083,9 @@ int setup_semijoin_dups_elimination(JOIN *join, ulonglong options,
uint no_jbuf_after)
{
uint i;
- THD *thd= join->thd;
DBUG_ENTER("setup_semijoin_dups_elimination");
-
+
+
POSITION *pos= join->best_positions + join->const_tables;
for (i= join->const_tables ; i < join->top_join_tab_count; )
{
@@ -4049,6 +4126,7 @@ int setup_semijoin_dups_elimination(JOIN *join, ulonglong options,
forwards, but do not destroy other duplicate elimination methods.
*/
uint first_table= i;
+
uint join_cache_level= join->thd->variables.join_cache_level;
for (uint j= i; j < i + pos->n_sj_tables; j++)
{
@@ -4072,70 +4150,7 @@ int setup_semijoin_dups_elimination(JOIN *join, ulonglong options,
}
}
- SJ_TMP_TABLE::TAB sjtabs[MAX_TABLES];
- SJ_TMP_TABLE::TAB *last_tab= sjtabs;
- uint jt_rowid_offset= 0; // # tuple bytes are already occupied (w/o NULL bytes)
- uint jt_null_bits= 0; // # null bits in tuple bytes
- /*
- Walk through the range and remember
- - tables that need their rowids to be put into temptable
- - the last outer table
- */
- for (JOIN_TAB *j=join->join_tab + first_table;
- j < join->join_tab + i + pos->n_sj_tables; j++)
- {
- if (sj_table_is_included(join, j))
- {
- last_tab->join_tab= j;
- last_tab->rowid_offset= jt_rowid_offset;
- jt_rowid_offset += j->table->file->ref_length;
- if (j->table->maybe_null)
- {
- last_tab->null_byte= jt_null_bits / 8;
- last_tab->null_bit= jt_null_bits++;
- }
- last_tab++;
- j->table->prepare_for_position();
- j->keep_current_rowid= TRUE;
- }
- }
-
- SJ_TMP_TABLE *sjtbl;
- if (jt_rowid_offset) /* Temptable has at least one rowid */
- {
- size_t tabs_size= (last_tab - sjtabs) * sizeof(SJ_TMP_TABLE::TAB);
- if (!(sjtbl= (SJ_TMP_TABLE*)thd->alloc(sizeof(SJ_TMP_TABLE))) ||
- !(sjtbl->tabs= (SJ_TMP_TABLE::TAB*) thd->alloc(tabs_size)))
- DBUG_RETURN(TRUE); /* purecov: inspected */
- memcpy(sjtbl->tabs, sjtabs, tabs_size);
- sjtbl->is_degenerate= FALSE;
- sjtbl->tabs_end= sjtbl->tabs + (last_tab - sjtabs);
- sjtbl->rowid_len= jt_rowid_offset;
- sjtbl->null_bits= jt_null_bits;
- sjtbl->null_bytes= (jt_null_bits + 7)/8;
- sjtbl->tmp_table=
- create_duplicate_weedout_tmp_table(thd,
- sjtbl->rowid_len +
- sjtbl->null_bytes,
- sjtbl);
- join->sj_tmp_tables.push_back(sjtbl->tmp_table);
- }
- else
- {
- /*
- This is a special case where the entire subquery predicate does
- not depend on anything at all, ie this is
- WHERE const IN (uncorrelated select)
- */
- if (!(sjtbl= (SJ_TMP_TABLE*)thd->alloc(sizeof(SJ_TMP_TABLE))))
- DBUG_RETURN(TRUE); /* purecov: inspected */
- sjtbl->tmp_table= NULL;
- sjtbl->is_degenerate= TRUE;
- sjtbl->have_degenerate_row= FALSE;
- }
- join->join_tab[first_table].flush_weedout_table= sjtbl;
- join->join_tab[i + pos->n_sj_tables - 1].check_weed_out_table= sjtbl;
-
+ init_dups_weedout(join, first_table, i, i + pos->n_sj_tables - first_table);
i+= pos->n_sj_tables;
pos+= pos->n_sj_tables;
break;
diff --git a/sql/opt_subselect.h b/sql/opt_subselect.h
index 7d560588995..0e7fa22cfe7 100644
--- a/sql/opt_subselect.h
+++ b/sql/opt_subselect.h
@@ -358,8 +358,7 @@ public:
ENGINE_COLUMNDEF *start_recinfo;
ENGINE_COLUMNDEF *recinfo;
- /* Pointer to next table (next->start_idx > this->end_idx) */
- SJ_TMP_TABLE *next;
+ SJ_TMP_TABLE *next_flush_table;
};
int setup_semijoin_dups_elimination(JOIN *join, ulonglong options,
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index ce20dfea32e..f56cb920a3d 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -15087,10 +15087,12 @@ sub_select(JOIN *join,JOIN_TAB *join_tab,bool end_of_records)
int error;
enum_nested_loop_state rc= NESTED_LOOP_OK;
READ_RECORD *info= &join_tab->read_record;
-
- if (join_tab->flush_weedout_table)
+
+ for (SJ_TMP_TABLE *flush_dups_table= join_tab->flush_weedout_table;
+ flush_dups_table;
+ flush_dups_table= flush_dups_table->next_flush_table)
{
- do_sj_reset(join_tab->flush_weedout_table);
+ do_sj_reset(flush_dups_table);
}
if (!join_tab->preread_init_done && join_tab->preread_init())
@@ -21013,7 +21015,7 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order,
extra.append(STRING_WITH_LEN("; LooseScan"));
}
- if (tab->flush_weedout_table)
+ if (tab->first_weedout_table)
extra.append(STRING_WITH_LEN("; Start temporary"));
if (tab->check_weed_out_table)
extra.append(STRING_WITH_LEN("; End temporary"));
diff --git a/sql/sql_select.h b/sql/sql_select.h
index 21c5dc7d65e..2b307c20dbe 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -341,6 +341,8 @@ typedef struct st_join_table {
/* Variables for semi-join duplicate elimination */
SJ_TMP_TABLE *flush_weedout_table;
SJ_TMP_TABLE *check_weed_out_table;
+ /* for EXPLAIN only: */
+ SJ_TMP_TABLE *first_weedout_table;
/*
If set, means we should stop join enumeration after we've got the first
View Lorry Controller Status