1267
1267
explain extended select * from t2 where t2.a in (select a from t1);
1268
1268
id select_type table type possible_keys key key_len ref rows filtered Extra
1269
1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index
1270
1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 4 75.00 Using where; Using index; Using join buffer
1269
1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index
1270
2 SUBQUERY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
1272
Note 1003 select `test`.`t2`.`a` AS `a` from (`test`.`t1`) join `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)
1272
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key)))
1273
1273
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1277
1277
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1278
1278
id select_type table type possible_keys key key_len ref rows filtered Extra
1279
1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index
1280
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 75.00 Using where; Using join buffer
1279
1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index
1280
2 SUBQUERY t1 ALL NULL NULL NULL NULL 4 100.00 Using where
1282
Note 1003 select `test`.`t2`.`a` AS `a` from (`test`.`t1`) join `test`.`t2` where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30))
1282
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30) ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key)))
1283
1283
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
1287
1287
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
1288
1288
id select_type table type possible_keys key key_len ref rows filtered Extra
1289
1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index
1290
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 75.00 Using where; Using join buffer
1291
1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 Using index
1289
1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index
1290
2 SUBQUERY t3 index PRIMARY PRIMARY 4 NULL 3 100.00 Using index
1291
2 SUBQUERY t1 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer
1293
Note 1003 select `test`.`t2`.`a` AS `a` from (`test`.`t1` join `test`.`t3`) join `test`.`t2` where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`b`))
1293
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t3` where (`test`.`t1`.`b` = `test`.`t3`.`a`) ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key)))
1294
1294
drop table t1, t2, t3;
1295
1295
create temporary table t1 (a int, b int, index a (a,b)) ENGINE=MyISAM;
1296
1296
create temporary table t2 (a int, index a (a)) ENGINE=MyISAM;
1307
1307
explain extended select * from t2 where t2.a in (select a from t1);
1308
1308
id select_type table type possible_keys key key_len ref rows filtered Extra
1309
1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index
1310
1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using index; FirstMatch(t2)
1309
1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
1310
2 SUBQUERY t1 index NULL a 10 NULL 10004 100.00 Using index
1312
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where (`test`.`t1`.`a` = `test`.`t2`.`a`)
1312
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key)))
1313
1313
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1317
1317
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1318
1318
id select_type table type possible_keys key key_len ref rows filtered Extra
1319
1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index
1320
1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using where; Using index; FirstMatch(t2)
1319
1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
1320
2 SUBQUERY t1 index NULL a 10 NULL 10004 100.00 Using where; Using index
1322
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30))
1322
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30) ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key)))
1323
1323
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
1327
1327
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
1328
1328
id select_type table type possible_keys key key_len ref rows filtered Extra
1329
1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index; Start temporary
1330
1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using index; FirstMatch(t2)
1331
1 PRIMARY t3 index a a 5 NULL 3 100.00 Using where; Using index; End temporary; Using join buffer
1329
1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
1330
2 SUBQUERY t3 index a a 5 NULL 3 100.00 Using index
1331
2 SUBQUERY t1 index NULL a 10 NULL 10004 100.00 Using where; Using index; Using join buffer
1333
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`))
1333
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t3` where (`test`.`t1`.`b` = `test`.`t3`.`a`) ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key)))
1334
1334
insert into t1 values (3,31);
1335
1335
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1344
1344
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
1345
1345
id select_type table type possible_keys key key_len ref rows filtered Extra
1346
1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index
1347
1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using where; Using index; FirstMatch(t2)
1346
1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
1347
2 SUBQUERY t1 index NULL a 10 NULL 10005 100.00 Using where; Using index
1349
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30))
1349
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30) ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key)))
1350
1350
drop table t1, t2, t3;
1352
1352
# alloc_group_fields() working
2813
2813
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
2814
2814
explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
2815
2815
id select_type table type possible_keys key key_len ref rows filtered Extra
2816
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Start temporary
2817
1 PRIMARY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; End temporary; Using join buffer
2816
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Using where
2817
2 SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where
2819
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`two` = `test`.`t1`.`two`) and (`test`.`t2`.`one` = `test`.`t1`.`one`) and (`test`.`t2`.`flag` = 'N'))
2819
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),(`test`.`t1`.`one`,`test`.`t1`.`two`) in ( <materialize> (select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = 'N') ), <primary_index_lookup>(`test`.`t1`.`one` in <temporary table> on distinct_key)))
2820
2820
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
2821
2821
id select_type table type possible_keys key key_len ref rows filtered Extra
2822
2822
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
4113
4113
INSERT INTO t1 VALUES ('a', 'aaa');
4114
4114
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
4116
4118
CREATE INDEX I1 ON t1 (a);
4117
4119
CREATE INDEX I2 ON t1 (b);
4118
4120
EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
4119
4121
id select_type table type possible_keys key key_len ref rows Extra
4120
1 PRIMARY t1 index I1 I1 7 NULL # Using index; LooseScan
4121
1 PRIMARY t1 ref I2 I2 43 test.t1.a # Using where
4122
1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where
4123
2 SUBQUERY t1 index NULL I1 7 NULL # Using index
4122
4124
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
4124
4128
CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));
4125
4129
INSERT INTO t2 SELECT * FROM t1;
4126
4130
CREATE INDEX I1 ON t2 (a);
4127
4131
CREATE INDEX I2 ON t2 (b);
4128
4132
EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
4129
4133
id select_type table type possible_keys key key_len ref rows Extra
4130
1 PRIMARY t2 index I1 I1 7 NULL # Using index; LooseScan
4131
1 PRIMARY t2 ref I2 I2 43 test.t2.a # Using where
4134
1 PRIMARY t2 ALL NULL NULL NULL NULL # Using where
4135
2 SUBQUERY t2 index NULL I1 7 NULL # Using index
4132
4136
SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
4135
4141
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
4136
4142
id select_type table type possible_keys key key_len ref rows Extra
4137
1 PRIMARY t1 index I1 I1 7 NULL # Using where; Using index; LooseScan
4138
1 PRIMARY t1 ref I2 I2 43 test.t1.a # Using where
4143
1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where
4144
2 SUBQUERY t1 index NULL I1 7 NULL # Using where; Using index
4139
4145
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
4141
4149
DROP TABLE t1,t2;
4143
4151
# Bug #32400: Complex SELECT query returns correct result only on some
added
removed
tests/r/subselect.result
