diff options
Diffstat (limited to 'mysql-test/r/subselect3_jcl6.result')
| -rw-r--r-- | mysql-test/r/subselect3_jcl6.result | 136 |
1 files changed, 62 insertions, 74 deletions
diff --git a/mysql-test/r/subselect3_jcl6.result b/mysql-test/r/subselect3_jcl6.result index a3ed949cda2..b5a78da238d 100644 --- a/mysql-test/r/subselect3_jcl6.result +++ b/mysql-test/r/subselect3_jcl6.result @@ -128,7 +128,7 @@ Handler_read_key 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 -Handler_read_rnd_next 29 +Handler_read_rnd_next 35 select 'No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1.' Z; Z No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1. @@ -170,7 +170,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 100.00 Using where; Using join buffer (flat, BKA join) Warnings: Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1 -Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<expr_cache><`test`.`t3`.`a`,`test`.`t3`.`oref`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond(((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)))) having trigcond(<is_not_null_test>(`test`.`t1`.`a`))))) AS `Z` from `test`.`t3` +Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<expr_cache><`test`.`t3`.`a`,`test`.`t3`.`oref`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(select `test`.`t1`.`a` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`b` = `test`.`t3`.`oref`) and (`test`.`t2`.`a` = `test`.`t1`.`b`) and trigcond(((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)))) having trigcond(<is_not_null_test>(`test`.`t1`.`a`))))) AS `Z` from `test`.`t3` drop table t1, t2, t3; create table t1 (a int NOT NULL, b int NOT NULL, key(a)); insert into t1 values @@ -198,7 +198,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t2 ref a a 4 test.t1.b 1 100.00 Using where; Using join buffer (flat, BKA join) Warnings: Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1 -Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<expr_cache><`test`.`t3`.`a`,`test`.`t3`.`oref`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`)))))) AS `Z` from `test`.`t3` +Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<expr_cache><`test`.`t3`.`a`,`test`.`t3`.`oref`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(select `test`.`t1`.`a` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`b` = `test`.`t3`.`oref`) and (`test`.`t2`.`a` = `test`.`t1`.`b`) and trigcond((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`)))))) AS `Z` from `test`.`t3` drop table t1,t2,t3; create table t1 (oref int, grp int); insert into t1 (oref, grp) values @@ -718,7 +718,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 Using index 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`b` = `test`.`t1`.`a`) and (not(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 from `test`.`t1` where ((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)) having <is_not_null_test>(`test`.`t1`.`a`))))))) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`b` = `test`.`t1`.`a`) and (not(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select `test`.`t1`.`a` from `test`.`t1` where trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) having trigcond(<is_not_null_test>(`test`.`t1`.`a`)))))))) SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1)); a SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1 WHERE a > 4)); @@ -739,7 +739,7 @@ WHERE t3.name='xxx' AND t2.id=t3.id); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where 2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 func 1 Using where; Using index; Full scan on NULL key -2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 func 1 Using index condition(BKA); Using where; Full scan on NULL key; Using join buffer (flat, BKA join) +2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 test.t2.id 1 Using where; Using join buffer (flat, BKA join) SELECT * FROM t1 WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3 WHERE t3.name='xxx' AND t2.id=t3.id); @@ -848,11 +848,16 @@ x ROW(11, 12) = (SELECT MAX(x), 22) ROW(11, 12) IN (SELECT MAX(x), 22) 1 0 0 2 0 0 11 0 0 -# 2nd and 3rd columns should be same for x == 11 only +# 2nd and 3rd columns should be same +EXPLAIN SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 12), ROW(11, 12) IN (SELECT MAX(x), 12) FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 +3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 12), ROW(11, 12) IN (SELECT MAX(x), 12) FROM t1; x ROW(11, 12) = (SELECT MAX(x), 12) ROW(11, 12) IN (SELECT MAX(x), 12) -1 0 1 -2 0 1 +1 0 0 +2 0 0 11 1 1 DROP TABLE t1; # both columns should be same @@ -978,7 +983,7 @@ i1 i2 # Baseline: SHOW STATUS LIKE '%Handler_read_rnd_next'; Variable_name Value -Handler_read_rnd_next 18 +Handler_read_rnd_next 17 INSERT INTO t1 VALUES (NULL, NULL); FLUSH STATUS; @@ -995,7 +1000,7 @@ i1 i2 # (read record from t1, but do not read from t2) SHOW STATUS LIKE '%Handler_read_rnd_next'; Variable_name Value -Handler_read_rnd_next 19 +Handler_read_rnd_next 18 set @@optimizer_switch=@save_optimizer_switch2; DROP TABLE t1,t2; End of 5.1 tests @@ -1030,11 +1035,10 @@ update t22 set c = '2005-12-08 15:58:27' where a = 255; explain select t21.* from t21,t22 where t21.a = t22.a and t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 8 Using temporary; Using filesort -1 PRIMARY t21 hash_ALL NULL #hash#$hj 4 test.t11.a 26 Using where; Using join buffer (flat, BNLH join) -1 PRIMARY t22 hash_ALL NULL #hash#$hj 4 test.t11.a 26 Using where; Using join buffer (incremental, BNLH join) -2 SUBQUERY t11 ALL NULL NULL NULL NULL 8 Using where -2 SUBQUERY t12 hash_ALL NULL #hash#$hj 4 test.t11.a 8 Using where; Using join buffer (flat, BNLH join) +1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort; Start temporary +1 PRIMARY t12 hash_ALL NULL #hash#$hj 4 test.t11.a 8 Using where; Using join buffer (flat, BNLH join) +1 PRIMARY t21 hash_ALL NULL #hash#$hj 4 test.t11.a 26 Using where; Using join buffer (incremental, BNLH join) +1 PRIMARY t22 hash_ALL NULL #hash#$hj 4 test.t11.a 26 Using where; End temporary; Using join buffer (incremental, BNLH join) select t21.* from t21,t22 where t21.a = t22.a and t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a; a b c @@ -1047,9 +1051,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 -2 DEPENDENT SUBQUERY subselect3 eq_ref unique_key unique_key 5 func 1 -3 SUBQUERY Z ALL NULL NULL NULL NULL 2 +2 DEPENDENT SUBQUERY Y ALL NULL NULL NULL NULL 2 Using where; Start temporary +2 DEPENDENT SUBQUERY Z hash_ALL NULL #hash#$hj 5 test.Y.a 2 Using where; End temporary; Using join buffer (flat, BNLH join) select (select max(Y.a) from t1 Y where a in (select a from t1 Z) and a < X.a) as subq from t1 X; subq NULL @@ -1119,8 +1122,7 @@ set @@optimizer_switch=@save_optimizer_switch; explain select * from (select a from t0) X where a in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 11 -1 PRIMARY subselect3 eq_ref unique_key unique_key 5 func 1 -3 SUBQUERY t1 ALL NULL NULL NULL NULL 20 +1 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using where; FirstMatch(<derived2>); Using join buffer (flat, BNL join) 2 DERIVED t0 ALL NULL NULL NULL NULL 11 drop table t0, t1; create table t0 (a int); @@ -1132,18 +1134,16 @@ create table t3 (a int); insert into t3 select A.a + 10*B.a from t0 A, t0 B; explain select * from t3 where a in (select kp1 from t1 where kp1<20); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 100 -1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 -2 SUBQUERY t1 range kp1 kp1 5 NULL 48 Using where; Using index +1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where +1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using index; FirstMatch(t3) create table t4 (pk int primary key); insert into t4 select a from t3; explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20 and t4.pk=t1.c); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 100 -1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 -2 SUBQUERY t1 range kp1 kp1 5 NULL 48 Using index condition; Using where; Using MRR -2 SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index +1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where +1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using where +1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; FirstMatch(t3) drop table t1, t3, t4; create table t1 (a int) as select * from t0 where a < 5; set @save_max_heap_table_size=@@max_heap_table_size; @@ -1174,9 +1174,8 @@ create table t3 ( a int , filler char(100), key(a)); insert into t3 select A.a + 10*B.a, 'filler' from t0 A, t0 B; explain select * from t3 where a in (select a from t2) and (a > 5 or a < 10); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 2 -1 PRIMARY t3 ref a a 5 test.t2.a 1 Using join buffer (flat, BKA join) -2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Start temporary +1 PRIMARY t3 ref a a 5 test.t2.a 1 End temporary; Using join buffer (flat, BKA join) select * from t3 where a in (select a from t2); a filler 1 filler @@ -1222,47 +1221,44 @@ create table t3 (a int, b int, filler char(100), key(a)); insert into t3 select A.a + 10*B.a, A.a + 10*B.a, 'filler' from t1 A, t1 B, t1 C; explain select * from t1, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30) and t1.a =3; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 10 -1 PRIMARY t3 ref a a 5 test.t2.a 10 Using join buffer (flat, BKA join) -2 SUBQUERY t2 ALL NULL NULL NULL NULL 10 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where; Start temporary +1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t3 ref a a 5 test.t2.a 10 End temporary; Using join buffer (incremental, BKA join) explain select straight_join * from t1 A, t1 B where A.a in (select a from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY A ALL NULL NULL NULL NULL 10 Using where 1 PRIMARY B ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) -2 SUBQUERY t2 ALL NULL NULL NULL NULL 10 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 10 Using where explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where -2 SUBQUERY A ALL NULL NULL NULL NULL 10 -2 SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where +2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where -2 SUBQUERY A ALL NULL NULL NULL NULL 10 -2 SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where +2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) explain select straight_join * from t2 X, t2 Y where X.a in (select straight_join A.a from t1 A, t1 B); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY X ALL NULL NULL NULL NULL 10 Using where 1 PRIMARY Y ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) -2 SUBQUERY A ALL NULL NULL NULL NULL 10 -2 SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where +2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) create table t0 (a int, b int); insert into t0 values(1,1); explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t0 system NULL NULL NULL NULL 1 -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 10 -1 PRIMARY t3 ref a a 5 test.t2.a 10 Using join buffer (flat, BKA join) -2 SUBQUERY t2 ALL NULL NULL NULL NULL 10 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where; Start temporary +1 PRIMARY t3 ref a a 5 test.t2.a 10 End temporary; Using join buffer (flat, BKA join) create table t4 as select a as x, a as y from t1; explain select * from t0, t3 where (t3.a, t3.b) in (select x,y from t4) and (t3.a < 10 or t3.a >30); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t0 system NULL NULL NULL NULL 1 -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 10 -1 PRIMARY t3 ref a a 5 test.t4.x 10 Using where; Using join buffer (flat, BKA join) -2 SUBQUERY t4 ALL NULL NULL NULL NULL 10 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 10 Using where; Start temporary +1 PRIMARY t3 ref a a 5 test.t4.x 10 Using where; End temporary; Using join buffer (flat, BKA join) drop table t0,t1,t2,t3,t4; create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); @@ -1271,14 +1267,12 @@ insert into t1 select A.a, B.a, 'filler' from t0 A, t0 B; create table t2 as select * from t1; explain select * from t2 where a in (select b from t1 where a=3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where -1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 -2 SUBQUERY t1 ref a a 10 const,test.t2.a 8 Using index +1 PRIMARY t1 range a a 5 NULL 8 Using where; Using index; LooseScan +1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) explain select * from t2 where (b,a) in (select a,b from t1 where a=3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where -1 PRIMARY subselect2 eq_ref unique_key unique_key 10 func 1 -2 SUBQUERY t1 ref a a 10 const,test.t2.a 8 Using index +1 PRIMARY t1 range a a 5 NULL 8 Using where; Using index; LooseScan +1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) drop table t1,t2; create table t1 (a int, b int); insert into t1 select a,a from t0; @@ -1287,16 +1281,14 @@ insert into t2 select A.a + 10*B.a, A.a + 10*B.a from t0 A, t0 B; set @@optimizer_switch='firstmatch=off'; explain select * from t1 where (a,b) in (select a,b from t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 10 -1 PRIMARY subselect2 eq_ref unique_key unique_key 10 func 1 -2 SUBQUERY t2 ALL NULL NULL NULL NULL 100 +1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where; Start temporary +1 PRIMARY t2 hash_ALL NULL #hash#$hj 10 test.t1.a,test.t1.b 100 Using where; End temporary; Using join buffer (flat, BNLH join) set @save_optimizer_search_depth=@@optimizer_search_depth; set @@optimizer_search_depth=63; explain select * from t1 where (a,b) in (select a,b from t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 10 -1 PRIMARY subselect2 eq_ref unique_key unique_key 10 func 1 -2 SUBQUERY t2 ALL NULL NULL NULL NULL 100 +1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where; Start temporary +1 PRIMARY t2 hash_ALL NULL #hash#$hj 10 test.t1.a,test.t1.b 100 Using where; End temporary; Using join buffer (flat, BNLH join) set @@optimizer_search_depth=@save_optimizer_search_depth; set @@optimizer_switch=@save_optimizer_switch; drop table t0, t1, t2; @@ -1306,9 +1298,8 @@ create table t1 as select * from t0; insert into t1 select * from t0; explain select * from t0 where a in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t0 ALL NULL NULL NULL NULL 2 -1 PRIMARY subselect2 eq_ref unique_key unique_key 3 func 1 -2 SUBQUERY t1 ALL NULL NULL NULL NULL 4 +1 PRIMARY t0 ALL NULL NULL NULL NULL 2 Using where; Start temporary +1 PRIMARY t1 hash_ALL NULL #hash#$hj 3 test.t0.a 4 Using where; End temporary; Using join buffer (flat, BNLH join) select * from t0 where a in (select a from t1); a 10.24 @@ -1320,9 +1311,8 @@ create table t1 as select * from t0; insert into t1 select * from t0; explain select * from t0 where a in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t0 ALL NULL NULL NULL NULL 2 -1 PRIMARY subselect2 eq_ref unique_key unique_key 4 func 1 -2 SUBQUERY t1 ALL NULL NULL NULL NULL 4 +1 PRIMARY t0 ALL NULL NULL NULL NULL 2 Using where; Start temporary +1 PRIMARY t1 hash_ALL NULL #hash#$hj 4 test.t0.a 4 Using where; End temporary; Using join buffer (flat, BNLH join) select * from t0 where a in (select a from t1); a 2008-01-01 @@ -1335,11 +1325,10 @@ create table t2 as select a as a, a as b from t0 where a < 3; insert into t2 select * from t2; explain select * from t1 where (a,b,c) in (select X.a, Y.a, Z.a from t2 X, t2 Y, t2 Z where X.b=33); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -1 PRIMARY subselect2 eq_ref unique_key unique_key 15 func 1 -2 SUBQUERY X ALL NULL NULL NULL NULL 6 Using where -2 SUBQUERY Y ALL NULL NULL NULL NULL 6 Using join buffer (flat, BNL join) -2 SUBQUERY Z ALL NULL NULL NULL NULL 6 Using join buffer (incremental, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Start temporary +1 PRIMARY X hash_ALL NULL #hash#$hj 5 test.t1.a 6 Using where; Using join buffer (flat, BNLH join) +1 PRIMARY Y hash_ALL NULL #hash#$hj 5 test.t1.b 6 Using where; Using join buffer (incremental, BNLH join) +1 PRIMARY Z hash_ALL NULL #hash#$hj 5 test.t1.c 6 Using where; End temporary; Using join buffer (incremental, BNLH join) drop table t0,t1,t2; BUG#37842: Assertion in DsMrr_impl::dsmrr_init, at handler.cc:4307 @@ -1409,10 +1398,9 @@ INNER JOIN t2 c ON c.idContact=cona.idContact WHERE cona.postalStripped='T2H3B2' ); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 2 1.00 -1 PRIMARY a index PRIMARY PRIMARY 4 NULL 2 100.00 Using where; Using index; Using join buffer (flat, BNL join) -2 SUBQUERY cona ALL NULL NULL NULL NULL 2 100.00 Using where -2 SUBQUERY c eq_ref PRIMARY PRIMARY 4 test.cona.idContact 1 100.00 Using join buffer (flat, BKA join) +1 PRIMARY cona ALL NULL NULL NULL NULL 2 100.00 Using where; Start temporary +1 PRIMARY c eq_ref PRIMARY PRIMARY 4 test.cona.idContact 1 100.00 Using where; Using join buffer (flat, BKA join) +1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.c.idObj 1 100.00 Using index; End temporary Warnings: Note 1003 select `test`.`a`.`idIndividual` AS `idIndividual` from `test`.`t1` `a` semi join (`test`.`t3` `cona` join `test`.`t2` `c`) where ((`test`.`c`.`idContact` = `test`.`cona`.`idContact`) and (`test`.`a`.`idIndividual` = `test`.`c`.`idObj`) and (`test`.`cona`.`postalStripped` = 'T2H3B2')) drop table t1,t2,t3; |