diff options
Diffstat (limited to 'mysql-test/r/subselect_sj.result')
| -rw-r--r-- | mysql-test/r/subselect_sj.result | 40 |
1 files changed, 9 insertions, 31 deletions
diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result index 1fc5955f493..30a42b0c8e7 100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -406,24 +406,6 @@ SELECT t1 .varchar_key from t1 int_key 9 7 -SELECT t0.int_key -FROM t0 -WHERE t0.varchar_nokey IN ( -SELECT t1_1 .varchar_key -FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key -); -int_key -9 -7 -SELECT t0.int_key -FROM t0, t2 -WHERE t0.varchar_nokey IN ( -SELECT t1_1 .varchar_key -FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key -); -int_key -9 -7 DROP TABLE t0, t1, t2; # End of bug#46550 # @@ -774,11 +756,11 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where Warnings: Note 1276 Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1 -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and <nop>(<expr_cache><`test`.`t2`.`d`,`test`.`t1`.`b`>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select 1 from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`))))))) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and <nop>(<expr_cache><`test`.`t2`.`d`,`test`.`t1`.`b`>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select `test`.`t3`.`e` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`))))))) show warnings; Level Code Message Note 1276 Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1 -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and <nop>(<expr_cache><`test`.`t2`.`d`,`test`.`t1`.`b`>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select 1 from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`))))))) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and <nop>(<expr_cache><`test`.`t2`.`d`,`test`.`t1`.`b`>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select `test`.`t3`.`e` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`))))))) select a from t1 where a in (select c from t2 where d >= some(select e from t3 where b=e)); a @@ -811,10 +793,9 @@ INSERT INTO t2 VALUES (1,'i','iiii','iiii','iiii','iiii','ffff','ffff','ffff','f EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (a, b) IN (SELECT a, b FROM t2 WHERE pk > 0); 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 subselect2 eq_ref unique_key unique_key 13 func 1 1.00 -2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Rowid-ordered scan +1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Rowid-ordered scan; FirstMatch(t1) Warnings: -Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`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`.`a` = `test`.`t1`.`a`) and (`test`.`t2`.`pk` > 0)) SELECT pk FROM t1 WHERE (a, b) IN (SELECT a, b FROM t2 WHERE pk > 0); pk 2 @@ -980,10 +961,9 @@ FROM t1 WHERE `varchar_nokey` < 'n' XOR `pk` ) ; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 18 100.00 -1 PRIMARY subselect2 eq_ref unique_key unique_key 8 func 1 1.00 -2 SUBQUERY t1 ALL varchar_key NULL NULL NULL 15 100.00 Using where +1 PRIMARY t1 ref varchar_key varchar_key 3 test.t2.varchar_nokey 2 105.00 Using where; FirstMatch(t2) Warnings: -Note 1003 select `test`.`t2`.`varchar_nokey` AS `varchar_nokey` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`varchar_nokey` = `test`.`t1`.`varchar_key`) and ((`test`.`t1`.`varchar_key` < 'n') xor `test`.`t1`.`pk`)) +Note 1003 select `test`.`t2`.`varchar_nokey` AS `varchar_nokey` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`varchar_key` = `test`.`t2`.`varchar_nokey`) and (`test`.`t1`.`varchar_nokey` = `test`.`t2`.`varchar_nokey`) and ((`test`.`t2`.`varchar_nokey` < 'n') xor `test`.`t1`.`pk`)) SELECT varchar_nokey FROM t2 WHERE ( `varchar_nokey` , `varchar_nokey` ) IN ( @@ -1061,11 +1041,9 @@ WHERE t2.val LIKE 'a%' OR t2.val LIKE 'e%') AND t1.val IN (SELECT t3.val FROM t3 WHERE t3.val LIKE 'a%' OR t3.val LIKE 'e%'); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 5 -1 PRIMARY subselect3 eq_ref unique_key unique_key 14 func 1 -1 PRIMARY subselect2 eq_ref unique_key unique_key 14 func 1 -3 SUBQUERY t3 ALL NULL NULL NULL NULL 5 Using where -2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Start temporary +1 PRIMARY t3 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (flat, BNL join) SELECT * FROM t1 WHERE t1.val IN (SELECT t2.val FROM t2 |