diff options
Diffstat (limited to 'mysql-test/r/subselect_sj_jcl6.result')
| -rw-r--r-- | mysql-test/r/subselect_sj_jcl6.result | 28 |
1 files changed, 14 insertions, 14 deletions
diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index 1ca8b44a0b8..4e67c1cc01c 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -87,7 +87,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 1 PRIMARY t12 eq_ref PRIMARY PRIMARY 4 test.t10.a 1 100.00 Using index Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t10` join `test`.`t12` join `test`.`t1` where ((`test`.`t10`.`pk` = `test`.`t1`.`a`) and (`test`.`t12`.`pk` = `test`.`t10`.`a`)) +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t10` join `test`.`t12` join `test`.`t1` where ((`test`.`t12`.`pk` = `test`.`t10`.`a`) and (`test`.`t10`.`pk` = `test`.`t1`.`a`)) subqueries within outer joins go into ON expr. explAin extended select * from t1 left join (t2 A, t2 B) on ( A.A= t1.A And B.A in (select pk from t10)); @@ -97,7 +97,7 @@ id select_type tABle type possiBle_keys key key_len ref rows filtered ExtrA 1 PRIMARY B ALL NULL NULL NULL NULL 3 100.00 Using where; Using join Buffer (incrementAl, BNL join) 2 MATERIALIZED t10 index PRIMARY PRIMARY 4 NULL 10 100.00 Using index Warnings: -Note 1003 select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`A`.`A` AS `A`,`test`.`A`.`B` AS `B`,`test`.`B`.`A` AS `A`,`test`.`B`.`B` AS `B` from `test`.`t1` left join (`test`.`t2` `A` join `test`.`t2` `B`) on((<in_optimizer>(`test`.`B`.`A`,`test`.`B`.`A` in ( <mAteriAlize> (select `test`.`t10`.`pk` from `test`.`t10` ), <primAry_index_lookup>(`test`.`B`.`A` in <temporAry tABle> on distinct_key where ((`test`.`B`.`A` = `<suBquery2>`.`pk`))))) And (`test`.`A`.`A` = `test`.`t1`.`A`))) where 1 +Note 1003 select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`A`.`A` AS `A`,`test`.`A`.`B` AS `B`,`test`.`B`.`A` AS `A`,`test`.`B`.`B` AS `B` from `test`.`t1` left join (`test`.`t2` `A` join `test`.`t2` `B`) on(((`test`.`A`.`A` = `test`.`t1`.`A`) And <in_optimizer>(`test`.`B`.`A`,`test`.`B`.`A` in ( <mAteriAlize> (select `test`.`t10`.`pk` from `test`.`t10` ), <primAry_index_lookup>(`test`.`B`.`A` in <temporAry tABle> on distinct_key where ((`test`.`B`.`A` = `<suBquery2>`.`pk`))))))) where 1 t2 should be wrapped into OJ-nest, so we have "t1 LJ (t2 J t10)" explAin extended select * from t1 left join t2 on (t2.A= t1.A And t2.A in (select pk from t10)); @@ -106,7 +106,7 @@ id select_type tABle type possiBle_keys key key_len ref rows filtered ExtrA 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join Buffer (flAt, BNL join) 2 MATERIALIZED t10 index PRIMARY PRIMARY 4 NULL 10 100.00 Using index Warnings: -Note 1003 select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`t2`.`A` AS `A`,`test`.`t2`.`B` AS `B` from `test`.`t1` left join `test`.`t2` on((<in_optimizer>(`test`.`t2`.`A`,`test`.`t2`.`A` in ( <mAteriAlize> (select `test`.`t10`.`pk` from `test`.`t10` ), <primAry_index_lookup>(`test`.`t2`.`A` in <temporAry tABle> on distinct_key where ((`test`.`t2`.`A` = `<suBquery2>`.`pk`))))) And (`test`.`t2`.`A` = `test`.`t1`.`A`))) where 1 +Note 1003 select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`t2`.`A` AS `A`,`test`.`t2`.`B` AS `B` from `test`.`t1` left join `test`.`t2` on(((`test`.`t2`.`A` = `test`.`t1`.`A`) And <in_optimizer>(`test`.`t1`.`A`,`test`.`t1`.`A` in ( <mAteriAlize> (select `test`.`t10`.`pk` from `test`.`t10` ), <primAry_index_lookup>(`test`.`t1`.`A` in <temporAry tABle> on distinct_key where ((`test`.`t1`.`A` = `<suBquery2>`.`pk`))))))) where 1 we shouldn't flatten if we're going to get a join of > MAX_TABLES. explain select * from t1 s00, t1 s01, t1 s02, t1 s03, t1 s04,t1 s05,t1 s06,t1 s07,t1 s08,t1 s09, @@ -514,7 +514,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t0.pk 1 100.00 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 1 PRIMARY t2 ref vkey vkey 4 test.t1.vnokey 2 100.00 Using index; FirstMatch(t1) Warnings: -Note 1003 select `test`.`t0`.`vkey` AS `vkey` from `test`.`t0` `t1` semi join (`test`.`t0` `t2`) join `test`.`t0` where ((`test`.`t2`.`vkey` = `test`.`t1`.`vnokey`) and (`test`.`t1`.`pk` = `test`.`t0`.`pk`)) +Note 1003 select `test`.`t0`.`vkey` AS `vkey` from `test`.`t0` `t1` semi join (`test`.`t0` `t2`) join `test`.`t0` where ((`test`.`t1`.`pk` = `test`.`t0`.`pk`) and (`test`.`t2`.`vkey` = `test`.`t1`.`vnokey`)) SELECT vkey FROM t0 WHERE pk IN (SELECT t1.pk FROM t0 t1 JOIN t0 t2 ON t2.vkey = t1.vnokey); vkey @@ -827,7 +827,7 @@ 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 1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Rowid-ordered scan; FirstMatch(t1); Using join buffer (flat, BNL join) Warnings: -Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`c` = `test`.`t1`.`c`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0)) +Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`c` = `test`.`t1`.`c`) and (`test`.`t2`.`pk` > 0)) SELECT pk FROM t1 WHERE (b, c) IN (SELECT b, c FROM t2 WHERE pk > 0); pk 1 @@ -837,7 +837,7 @@ 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 1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Rowid-ordered scan; FirstMatch(t1); Using join buffer (flat, BNL join) Warnings: -Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`d` = `test`.`t1`.`d`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0)) +Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`d` = `test`.`t1`.`d`) and (`test`.`t2`.`pk` > 0)) SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0); pk 2 @@ -846,7 +846,7 @@ 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 1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Rowid-ordered scan; FirstMatch(t1); Using join buffer (flat, BNL join) Warnings: -Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`e` = `test`.`t1`.`e`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0)) +Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`e` = `test`.`t1`.`e`) and (`test`.`t2`.`pk` > 0)) SELECT pk FROM t1 WHERE (b, e) IN (SELECT b, e FROM t2 WHERE pk > 0); pk 1 @@ -856,7 +856,7 @@ 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 1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Rowid-ordered scan; FirstMatch(t1); Using join buffer (flat, BNL join) Warnings: -Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`f` = `test`.`t1`.`f`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0)) +Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`f` = `test`.`t1`.`f`) and (`test`.`t2`.`pk` > 0)) SELECT pk FROM t1 WHERE (b, f) IN (SELECT b, f FROM t2 WHERE pk > 0); pk 1 @@ -866,7 +866,7 @@ 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 1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Rowid-ordered scan; FirstMatch(t1); Using join buffer (flat, BNL join) Warnings: -Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`g` = `test`.`t1`.`g`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0)) +Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`g` = `test`.`t1`.`g`) and (`test`.`t2`.`pk` > 0)) SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0); pk 1 @@ -876,7 +876,7 @@ 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 1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Rowid-ordered scan; FirstMatch(t1); Using join buffer (flat, BNL join) Warnings: -Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`h` = `test`.`t1`.`h`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0)) +Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`h` = `test`.`t1`.`h`) and (`test`.`t2`.`pk` > 0)) SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0); pk 1 @@ -886,7 +886,7 @@ 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 1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Rowid-ordered scan; FirstMatch(t1); Using join buffer (flat, BNL join) Warnings: -Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`i` = `test`.`t1`.`i`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0)) +Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`i` = `test`.`t1`.`i`) and (`test`.`t2`.`pk` > 0)) SELECT pk FROM t1 WHERE (b, i) IN (SELECT b, i FROM t2 WHERE pk > 0); pk 1 @@ -896,7 +896,7 @@ 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 1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Rowid-ordered scan; FirstMatch(t1); Using join buffer (flat, BNL join) Warnings: -Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`j` = `test`.`t1`.`j`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0)) +Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`j` = `test`.`t1`.`j`) and (`test`.`t2`.`pk` > 0)) SELECT pk FROM t1 WHERE (b, j) IN (SELECT b, j FROM t2 WHERE pk > 0); pk 1 @@ -906,7 +906,7 @@ 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 1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Rowid-ordered scan; FirstMatch(t1); Using join buffer (flat, BNL join) Warnings: -Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`k` = `test`.`t1`.`k`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0)) +Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`k` = `test`.`t1`.`k`) and (`test`.`t2`.`pk` > 0)) SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0); pk 1 @@ -2008,7 +2008,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (incremental, BNL join) 2 MATERIALIZED t4 index f2 f2 5 NULL 2 100.00 Using index Warnings: -Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t2`.`f3` AS `f3`,`test`.`t3`.`f3` AS `f3` from `test`.`t1` semi join (`test`.`t4`) join `test`.`t2` join `test`.`t3` where ((`test`.`t1`.`f2` = `test`.`t2`.`f2`) and (`test`.`t3`.`f1` = `test`.`t1`.`f1`)) +Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t2`.`f3` AS `f3`,`test`.`t3`.`f3` AS `f3` from `test`.`t1` semi join (`test`.`t4`) join `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`f1` = `test`.`t1`.`f1`) and (`test`.`t1`.`f2` = `test`.`t2`.`f2`)) SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3) WHERE t2.f3 IN (SELECT * FROM t4); f1 f2 f3 f3 2 0 0 0 |