diff options
Diffstat (limited to 'mysql-test/r/subselect_no_semijoin.result')
| -rw-r--r-- | mysql-test/r/subselect_no_semijoin.result | 20 |
1 files changed, 10 insertions, 10 deletions
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index a0524812da5..5521d1ef7e1 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -1300,7 +1300,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index 2 SUBQUERY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` from `test`.`t1` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where ((`test`.`t2`.`a` = `materialized subselect`.`a`)))))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` from `test`.`t1` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where ((`test`.`t2`.`a` = `<subquery2>`.`a`)))))) select * from t2 where t2.a in (select a from t1 where t1.b <> 30); a 2 @@ -1310,7 +1310,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index 2 SUBQUERY t1 ALL NULL NULL NULL NULL 4 100.00 Using where Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` from `test`.`t1` where (`test`.`t1`.`b` <> 30) ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where ((`test`.`t2`.`a` = `materialized subselect`.`a`)))))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` from `test`.`t1` where (`test`.`t1`.`b` <> 30) ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where ((`test`.`t2`.`a` = `<subquery2>`.`a`)))))) select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); a 2 @@ -1321,7 +1321,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 SUBQUERY t3 index PRIMARY PRIMARY 4 NULL 3 100.00 Using index 2 SUBQUERY t1 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (flat, BNL join) Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`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 where ((`test`.`t2`.`a` = `materialized subselect`.`a`)))))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`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 where ((`test`.`t2`.`a` = `<subquery2>`.`a`)))))) drop table t1, t2, t3; create table t1 (a int, b int, index a (a,b)); create table t2 (a int, index a (a)); @@ -1343,7 +1343,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index 2 SUBQUERY t1 index NULL a 10 NULL 10004 100.00 Using index Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` from `test`.`t1` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where ((`test`.`t2`.`a` = `materialized subselect`.`a`)))))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` from `test`.`t1` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where ((`test`.`t2`.`a` = `<subquery2>`.`a`)))))) select * from t2 where t2.a in (select a from t1 where t1.b <> 30); a 2 @@ -1353,7 +1353,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index 2 SUBQUERY t1 index NULL a 10 NULL 10004 100.00 Using where; Using index Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` from `test`.`t1` where (`test`.`t1`.`b` <> 30) ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where ((`test`.`t2`.`a` = `materialized subselect`.`a`)))))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` from `test`.`t1` where (`test`.`t1`.`b` <> 30) ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where ((`test`.`t2`.`a` = `<subquery2>`.`a`)))))) select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); a 2 @@ -1364,7 +1364,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 SUBQUERY t3 index a a 5 NULL 3 100.00 Using index 2 SUBQUERY t1 index NULL a 10 NULL 10004 100.00 Using where; Using index; Using join buffer (flat, BNL join) Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`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 where ((`test`.`t2`.`a` = `materialized subselect`.`a`)))))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`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 where ((`test`.`t2`.`a` = `<subquery2>`.`a`)))))) insert into t1 values (3,31); select * from t2 where t2.a in (select a from t1 where t1.b <> 30); a @@ -1380,7 +1380,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index 2 SUBQUERY t1 index NULL a 10 NULL 10005 100.00 Using where; Using index Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` from `test`.`t1` where (`test`.`t1`.`b` <> 30) ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where ((`test`.`t2`.`a` = `materialized subselect`.`a`)))))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` from `test`.`t1` where (`test`.`t1`.`b` <> 30) ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where ((`test`.`t2`.`a` = `<subquery2>`.`a`)))))) drop table t0, t1, t2, t3; create table t1 (a int, b int); create table t2 (a int, b int); @@ -2834,7 +2834,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Using where 2 SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where <expr_cache><`test`.`t1`.`two`,`test`.`t1`.`one`>(<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),(`test`.`t1`.`one`,`test`.`t1`.`two`) in ( <materialize> (select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = 'N') ), <primary_index_lookup>(`test`.`t1`.`one` in <temporary table> on distinct_key where ((`test`.`t1`.`one` = `materialized subselect`.`one`) and (`test`.`t1`.`two` = `materialized subselect`.`two`)))))) +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where <expr_cache><`test`.`t1`.`two`,`test`.`t1`.`one`>(<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),(`test`.`t1`.`one`,`test`.`t1`.`two`) in ( <materialize> (select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = 'N') ), <primary_index_lookup>(`test`.`t1`.`one` in <temporary table> on distinct_key where ((`test`.`t1`.`one` = `<subquery2>`.`one`) and (`test`.`t1`.`two` = `<subquery2>`.`two`)))))) 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; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 @@ -4323,13 +4323,13 @@ 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 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where <expr_cache><1>(<in_optimizer>(1,1 in ( <materialize> (select 1 from `test`.`t1` group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 = `materialized subselect`.`1`)))))) +Note 1003 select 1 AS `1` from `test`.`t1` where <expr_cache><1>(<in_optimizer>(1,1 in ( <materialize> (select 1 from `test`.`t1` group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 = `<subquery2>`.`1`)))))) EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a); 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 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where <expr_cache><1>(<in_optimizer>(1,1 in ( <materialize> (select 1 from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 = `materialized subselect`.`1`)))))) +Note 1003 select 1 AS `1` from `test`.`t1` where <expr_cache><1>(<in_optimizer>(1,1 in ( <materialize> (select 1 from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 = `<subquery2>`.`1`)))))) DROP TABLE t1; # # Bug#45061: Incorrectly market field caused wrong result. |