diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/derived_view.result | 4 | ||||
| -rw-r--r-- | mysql-test/r/explain.result | 8 | ||||
| -rw-r--r-- | mysql-test/r/join_cache.result | 8 | ||||
| -rw-r--r-- | mysql-test/r/subselect.result | 4 | ||||
| -rw-r--r-- | mysql-test/r/subselect3.result | 48 | ||||
| -rw-r--r-- | mysql-test/r/subselect3_jcl6.result | 48 | ||||
| -rw-r--r-- | mysql-test/r/subselect_extra.result | 16 | ||||
| -rw-r--r-- | mysql-test/r/subselect_no_mat.result | 8 | ||||
| -rw-r--r-- | mysql-test/r/subselect_no_scache.result | 4 | ||||
| -rw-r--r-- | mysql-test/r/subselect_sj.result | 36 | ||||
| -rw-r--r-- | mysql-test/r/subselect_sj2.result | 6 | ||||
| -rw-r--r-- | mysql-test/r/subselect_sj2_jcl6.result | 6 | ||||
| -rw-r--r-- | mysql-test/r/subselect_sj2_mat.result | 6 | ||||
| -rw-r--r-- | mysql-test/r/subselect_sj_aria.result | 59 | ||||
| -rw-r--r-- | mysql-test/r/subselect_sj_jcl6.result | 48 | ||||
| -rw-r--r-- | mysql-test/r/subselect_sj_mat.result | 98 | ||||
| -rw-r--r-- | mysql-test/r/type_datetime.result | 8 | ||||
| -rw-r--r-- | mysql-test/suite/pbxt/r/subselect.result | 4 | ||||
| -rw-r--r-- | mysql-test/t/subselect_sj2.test | 2 | ||||
| -rw-r--r-- | mysql-test/t/subselect_sj_aria.test | 76 |
20 files changed, 314 insertions, 183 deletions
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index d1dc2c7919d..040b3b266d9 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -1479,8 +1479,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 +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 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/explain.result b/mysql-test/r/explain.result index ab48ccdcf15..856e33258c1 100644 --- a/mysql-test/r/explain.result +++ b/mysql-test/r/explain.result @@ -199,16 +199,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 +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 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 +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 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 diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index af3d238c1fd..be579c9240f 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@ -5137,8 +5137,8 @@ EXPLAIN SELECT * FROM (SELECT DISTINCT * FROM t1) t WHERE t.a IN (SELECT t2.a FROM t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> ALL key0 NULL NULL NULL 3 Start temporary -1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where; End temporary; Using join buffer (flat, BNL join) +1 PRIMARY <derived2> ALL key0 NULL NULL NULL 3 +1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 2 DERIVED t1 ALL NULL NULL NULL NULL 3 Using temporary SELECT * FROM (SELECT DISTINCT * FROM t1) t WHERE t.a IN (SELECT t2.a FROM t2); @@ -5170,8 +5170,8 @@ SET SESSION join_cache_level=3; EXPLAIN SELECT * FROM t1 WHERE (t1.b) IN (SELECT c FROM t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Start temporary -1 PRIMARY t2 hash_index c #hash#c:c 5:5 test.t1.b 8 End temporary; Using join buffer (flat, BNLH join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t2 hash_index c #hash#c:c 5:5 test.t1.b 8 Start temporary; End temporary; Using join buffer (flat, BNLH join) SELECT * FROM t1 WHERE (t1.b) IN (SELECT c FROM t2); a b 3914 17 diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index a4e24732181..cf501b3fb28 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -5201,8 +5201,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 2c27419506d..8b1d7e46e4e 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -1048,8 +1048,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 @@ -1193,27 +1193,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; set @@optimizer_switch=@save_optimizer_switch; @@ -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 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 @@ -1286,14 +1286,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,materialization=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; @@ -1304,8 +1304,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 @@ -1317,8 +1317,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 @@ -1331,8 +1331,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 b15758bb11f..8bf699fbc4d 100644 --- a/mysql-test/r/subselect3_jcl6.result +++ b/mysql-test/r/subselect3_jcl6.result @@ -1057,8 +1057,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 @@ -1202,27 +1202,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; set @@optimizer_switch=@save_optimizer_switch; @@ -1234,8 +1234,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 @@ -1295,14 +1295,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,materialization=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; @@ -1313,8 +1313,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 @@ -1326,8 +1326,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 @@ -1340,8 +1340,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 fd0407c3b42..308e6715fe6 100644 --- a/mysql-test/r/subselect_extra.result +++ b/mysql-test/r/subselect_extra.result @@ -15,16 +15,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 @@ -67,8 +67,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)) @@ -79,8 +79,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)) diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 7ea7ae77127..8a5443c70de 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -5154,8 +5154,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; @@ -5200,8 +5200,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 2dd9ca12511..b1c62cfc7dd 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -5205,8 +5205,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 a49c22f87fa..d5ab105207f 100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -1242,8 +1242,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 +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 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 @@ -1439,8 +1439,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 +1 PRIMARY t0 ALL NULL NULL NULL NULL 2 +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Start temporary 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 @@ -1498,8 +1498,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 +1 PRIMARY t0 ALL NULL NULL NULL NULL 2 +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Start temporary 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 @@ -1516,8 +1516,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 +1 PRIMARY t0 ALL NULL NULL NULL NULL 2 +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Start temporary 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 @@ -1711,10 +1711,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 +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary 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 @@ -1772,9 +1772,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 +1 PRIMARY t3 ALL b NULL NULL NULL 5 Using where; Start temporary; End temporary SELECT * FROM t1, t2 WHERE (t2.a , t1.b) IN (SELECT a, b FROM t3); b a 5 6 @@ -1877,10 +1877,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 +1 PRIMARY SQ3_alias1 ALL NULL NULL NULL NULL 20 Using where; Start temporary 1 PRIMARY SQ3_alias2 index NULL PRIMARY 4 NULL 20 Using index; End temporary 2 DERIVED t2 ALL NULL NULL NULL NULL 20 create table t3 as @@ -2040,8 +2040,8 @@ set optimizer_switch='semijoin_with_cache=on'; explain select * from t1 where t1.a in (select t2.a from t2); 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 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 4 +1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) select * from t1 where t1.a in (select t2.a from t2); a 1 @@ -2050,8 +2050,8 @@ set optimizer_switch='semijoin_with_cache=off'; explain select * from t1 where t1.a in (select t2.a from t2); 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 t2 ALL NULL NULL NULL NULL 6 Using where; End temporary +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 +1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; Start temporary; End temporary select * from t1 where t1.a in (select t2.a from t2); a 1 diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result index 39d8eac2fcf..d476de2dea1 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); @@ -331,8 +331,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_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result index c9f68a9556e..bd330dd8143 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); @@ -340,8 +340,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; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan -1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t1.Country,const 1 Using index condition; Using where; End temporary; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +1 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t1.Country 1 Using where; End temporary; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t1.Country,const 1 Using index condition; Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan set optimizer_switch=@bug35674_save_optimizer_switch; DROP TABLE t1,t2,t3; CREATE TABLE t1 ( diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result index 8effea0ae85..4a61266a317 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_aria.result b/mysql-test/r/subselect_sj_aria.result new file mode 100644 index 00000000000..ea6cc8d14c4 --- /dev/null +++ b/mysql-test/r/subselect_sj_aria.result @@ -0,0 +1,59 @@ +drop table if exists t1,t2,t3,t4; +# +# BUG#887468: Second assertion `keypart_map' failed in maria_rkey with semijoin +# +CREATE TABLE t1 ( +pk int(11) NOT NULL AUTO_INCREMENT, +col_int_key int(11) DEFAULT NULL, +col_varchar_key varchar(1) DEFAULT NULL, +dummy char(30), +PRIMARY KEY (pk), +KEY col_varchar_key (col_varchar_key,col_int_key) +) ENGINE=Aria AUTO_INCREMENT=30 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1; +INSERT INTO t1 (pk, col_varchar_key, col_int_key) VALUES +(10,NULL,0), (11,'d',4), (12,'g',8), (13,'x',NULL), (14,'f',NULL), +(15,'p',0), (16,'j',NULL), (17,'c',8), (18,'z',8), (19,'j',6), (20,NULL,2), +(21,'p',3), (22,'w',1), (23,'c',NULL), (24,'j',1), (25,'f',10), (26,'v',2), +(27,'f',103), (28,'q',3), (29,'y',6); +CREATE TABLE t2 ( +pk int(11) NOT NULL AUTO_INCREMENT, +col_int_key int(11) DEFAULT NULL, +dummy char(36), +PRIMARY KEY (pk), +KEY col_int_key (col_int_key) +) ENGINE=Aria AUTO_INCREMENT=101 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1; +INSERT INTO t2 ( pk, col_int_key) VALUES +(1,8), (2,2), (3,9), (4,6), (5,NULL), (6,NULL), (7,48), (8,228), (9,3), (10,5), +(11,39), (12,6), (13,8), (14,3), (15,NULL), (16,2), (17,6), (18,3), (19,1), (20,4), +(21,3), (22,1), (23,NULL), (24,97), (25,0), (26,0), (27,9), (28,5), (29,9), (30,0), +(31,2), (32,172), (33,NULL), (34,5), (35,119), (36,1), (37,4), (38,8), (39,NULL), (40,6), +(41,5), (42,5), (43,1), (44,7), (45,2), (46,8), (47,9), (48,NULL), (49,NULL), (50,3), +(51,172), (52,NULL), (53,6), (54,6), (55,5), (56,4), (57,3), (58,2), (59,7), (60,4), +(61,6), (62,0), (63,8), (64,5), (65,8), (66,2), (67,9), (68,7), (69,5), (70,7), +(71,0), (72,4), (73,3), (74,1), (75,0), (76,6), (77,2), (78,NULL), (79,8), (80,NULL), +(81,NULL), (82,NULL), (83,3), (84,7), (85,3), (86,5), (87,5), (88,1), (89,2), (90,1), +(91,7), (92,1), (93,9), (94,9), (95,8), (96,3), (97,7), (98,4), (99,9), (100,0); +CREATE TABLE t3 ( +pk int(11) NOT NULL AUTO_INCREMENT, +dummy char(34), +col_varchar_key varchar(1) DEFAULT NULL, +col_int_key int(11) DEFAULT NULL, +PRIMARY KEY (pk), +KEY col_varchar_key (col_varchar_key,col_int_key) +) ENGINE=Aria AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1; +INSERT INTO t3 (pk, col_varchar_key) VALUES (1,'v'), (2,'c'), (3,NULL); +CREATE TABLE t4 ( +pk int(11) NOT NULL AUTO_INCREMENT, +dummy char (38), +PRIMARY KEY (pk) +) ENGINE=Aria AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1; +INSERT INTO t4 (pk) VALUES (1), (2), (3); +SELECT * +FROM t1 +JOIN t2 +ON ( t2.col_int_key = t1.pk ) +WHERE t1.col_varchar_key IN ( +SELECT t3.col_varchar_key FROM t3, t4 +); +pk col_int_key col_varchar_key dummy pk col_int_key dummy +drop table t1, t2, t3, t4; diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index 5cabff499f8..871f58199e7 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -1253,8 +1253,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 @@ -1450,8 +1450,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 @@ -1509,8 +1509,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 @@ -1527,8 +1527,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 @@ -1722,10 +1722,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 @@ -1783,9 +1783,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 @@ -1888,10 +1888,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 @@ -2051,8 +2051,8 @@ set optimizer_switch='semijoin_with_cache=on'; explain select * from t1 where t1.a in (select t2.a from t2); 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 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 4 +1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) select * from t1 where t1.a in (select t2.a from t2); a 1 @@ -2061,8 +2061,8 @@ set optimizer_switch='semijoin_with_cache=off'; explain select * from t1 where t1.a in (select t2.a from t2); 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 t2 ALL NULL NULL NULL NULL 6 Using where; End temporary +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 +1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; Start temporary; End temporary select * from t1 where t1.a in (select t2.a from t2); a 1 @@ -2123,8 +2123,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); @@ -2156,8 +2156,8 @@ EXPLAIN SELECT * FROM t1, t2 WHERE t2.a IN (SELECT b FROM t3 WHERE t3.d <= t1.a); 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 t3 ALL d NULL NULL NULL 5 Range checked for each record (index map: 0x2) +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY t3 ALL d NULL NULL NULL 5 Range checked for each record (index map: 0x2); Start temporary 1 PRIMARY t2 hash_ALL PRIMARY #hash#PRIMARY 4 test.t3.b 4 End temporary; Using join buffer (flat, BNLH join) SELECT * FROM t1, t2 WHERE t2.a IN (SELECT b FROM t3 WHERE t3.d <= t1.a); @@ -2171,8 +2171,8 @@ EXPLAIN SELECT * FROM t1, t2 WHERE t2.a IN (SELECT b FROM t3 WHERE t3.d <= t1.a); 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 t3 ALL d NULL NULL NULL 5 Range checked for each record (index map: 0x2) +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY t3 ALL d NULL NULL NULL 5 Range checked for each record (index map: 0x2); Start temporary 1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t3.b 1 End temporary; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan SELECT * FROM t1, t2 WHERE t2.a IN (SELECT b FROM t3 WHERE t3.d <= t1.a); diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index 350d3fe62d7..ec2ee967b22 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/r/type_datetime.result b/mysql-test/r/type_datetime.result index e4c289711a1..966343fa80f 100644 --- a/mysql-test/r/type_datetime.result +++ b/mysql-test/r/type_datetime.result @@ -538,8 +538,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 +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 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)) @@ -550,8 +550,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 +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 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)) diff --git a/mysql-test/suite/pbxt/r/subselect.result b/mysql-test/suite/pbxt/r/subselect.result index ce017889762..6b1ed9ea052 100644 --- a/mysql-test/suite/pbxt/r/subselect.result +++ b/mysql-test/suite/pbxt/r/subselect.result @@ -1360,8 +1360,8 @@ a 3 explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index; Start temporary -1 PRIMARY t1 ref a a 5 test.t2.a 1 100.00 Using index +1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index +1 PRIMARY t1 ref a a 5 test.t2.a 1 100.00 Using index; Start temporary 1 PRIMARY t3 index a a 5 NULL 3 100.00 Using where; Using index; End temporary Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1` join `test`.`t3`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`b`)) 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/mysql-test/t/subselect_sj_aria.test b/mysql-test/t/subselect_sj_aria.test new file mode 100644 index 00000000000..806688b3f87 --- /dev/null +++ b/mysql-test/t/subselect_sj_aria.test @@ -0,0 +1,76 @@ +# +# Semi-join tests that require Aria +# +--disable_warnings +drop table if exists t1,t2,t3,t4; +--enable_warnings + + +--echo # +--echo # BUG#887468: Second assertion `keypart_map' failed in maria_rkey with semijoin +--echo # + +CREATE TABLE t1 ( + pk int(11) NOT NULL AUTO_INCREMENT, + col_int_key int(11) DEFAULT NULL, + col_varchar_key varchar(1) DEFAULT NULL, + dummy char(30), + PRIMARY KEY (pk), + KEY col_varchar_key (col_varchar_key,col_int_key) +) ENGINE=Aria AUTO_INCREMENT=30 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1; + +INSERT INTO t1 (pk, col_varchar_key, col_int_key) VALUES +(10,NULL,0), (11,'d',4), (12,'g',8), (13,'x',NULL), (14,'f',NULL), +(15,'p',0), (16,'j',NULL), (17,'c',8), (18,'z',8), (19,'j',6), (20,NULL,2), +(21,'p',3), (22,'w',1), (23,'c',NULL), (24,'j',1), (25,'f',10), (26,'v',2), +(27,'f',103), (28,'q',3), (29,'y',6); + +CREATE TABLE t2 ( + pk int(11) NOT NULL AUTO_INCREMENT, + col_int_key int(11) DEFAULT NULL, + dummy char(36), + PRIMARY KEY (pk), + KEY col_int_key (col_int_key) +) ENGINE=Aria AUTO_INCREMENT=101 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1; + +INSERT INTO t2 ( pk, col_int_key) VALUES +(1,8), (2,2), (3,9), (4,6), (5,NULL), (6,NULL), (7,48), (8,228), (9,3), (10,5), +(11,39), (12,6), (13,8), (14,3), (15,NULL), (16,2), (17,6), (18,3), (19,1), (20,4), +(21,3), (22,1), (23,NULL), (24,97), (25,0), (26,0), (27,9), (28,5), (29,9), (30,0), +(31,2), (32,172), (33,NULL), (34,5), (35,119), (36,1), (37,4), (38,8), (39,NULL), (40,6), +(41,5), (42,5), (43,1), (44,7), (45,2), (46,8), (47,9), (48,NULL), (49,NULL), (50,3), +(51,172), (52,NULL), (53,6), (54,6), (55,5), (56,4), (57,3), (58,2), (59,7), (60,4), +(61,6), (62,0), (63,8), (64,5), (65,8), (66,2), (67,9), (68,7), (69,5), (70,7), +(71,0), (72,4), (73,3), (74,1), (75,0), (76,6), (77,2), (78,NULL), (79,8), (80,NULL), +(81,NULL), (82,NULL), (83,3), (84,7), (85,3), (86,5), (87,5), (88,1), (89,2), (90,1), +(91,7), (92,1), (93,9), (94,9), (95,8), (96,3), (97,7), (98,4), (99,9), (100,0); + +CREATE TABLE t3 ( + pk int(11) NOT NULL AUTO_INCREMENT, + dummy char(34), + col_varchar_key varchar(1) DEFAULT NULL, + col_int_key int(11) DEFAULT NULL, + PRIMARY KEY (pk), + KEY col_varchar_key (col_varchar_key,col_int_key) +) ENGINE=Aria AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1; + +INSERT INTO t3 (pk, col_varchar_key) VALUES (1,'v'), (2,'c'), (3,NULL); + +CREATE TABLE t4 ( + pk int(11) NOT NULL AUTO_INCREMENT, + dummy char (38), + PRIMARY KEY (pk) +) ENGINE=Aria AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1; + +INSERT INTO t4 (pk) VALUES (1), (2), (3); + +SELECT * +FROM t1 +JOIN t2 +ON ( t2.col_int_key = t1.pk ) +WHERE t1.col_varchar_key IN ( + SELECT t3.col_varchar_key FROM t3, t4 +); + +drop table t1, t2, t3, t4; + |