diff options
Diffstat (limited to 'mysql-test/main/subselect_no_semijoin.result')
| -rw-r--r-- | mysql-test/main/subselect_no_semijoin.result | 32 |
1 files changed, 17 insertions, 15 deletions
diff --git a/mysql-test/main/subselect_no_semijoin.result b/mysql-test/main/subselect_no_semijoin.result index d9058965082..32cb9acd477 100644 --- a/mysql-test/main/subselect_no_semijoin.result +++ b/mysql-test/main/subselect_no_semijoin.result @@ -925,8 +925,8 @@ a t1.a in (select t2.a from t2,t3 where t3.a=t2.a) explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index -2 MATERIALIZED t3 ALL NULL NULL NULL NULL 3 100.00 -2 MATERIALIZED t2 index a a 5 NULL 3 75.00 Using where; Using index; Using join buffer (flat, BNL join) +2 MATERIALIZED t3 ALL NULL NULL NULL NULL 3 100.00 Using where +2 MATERIALIZED t2 ref a a 5 test.t3.a 2 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `test`.`t2`.`a` from `test`.`t2` join `test`.`t3` where `test`.`t2`.`a` = `test`.`t3`.`a` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`a`)))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` drop table t1,t2,t3; @@ -1463,8 +1463,8 @@ a explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); 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 MATERIALIZED t1 ALL PRIMARY NULL NULL NULL 4 100.00 -2 MATERIALIZED t3 index PRIMARY PRIMARY 4 NULL 3 75.00 Using where; Using index; Using join buffer (flat, BNL join) +2 MATERIALIZED t1 ALL PRIMARY NULL NULL NULL 4 100.00 Using where +2 MATERIALIZED t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 Using index Warnings: Note 1003 /* select#1 */ 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#2 */ select `test`.`t1`.`a` from `test`.`t1` join `test`.`t3` where `test`.`t3`.`a` = `test`.`t1`.`b` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where `test`.`t2`.`a` = `<subquery2>`.`a`)))) drop table t1, t2, t3; @@ -2421,15 +2421,17 @@ a 3 DROP TABLE t1; create table t1 (a int, b int); -insert into t1 values (1,2),(3,4); +insert into t1 values (1,2),(3,4),(5,6),(7,8); select * from t1 up where exists (select * from t1 where t1.a=up.a); a b 1 2 3 4 +5 6 +7 8 explain extended select * from t1 up where exists (select * from t1 where t1.a=up.a); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY up ALL NULL NULL NULL NULL 2 100.00 Using where -2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00 +1 PRIMARY up ALL NULL NULL NULL NULL 4 100.00 Using where +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 4 100.00 Warnings: Note 1276 Field or reference 'test.up.a' of SELECT #2 was resolved in SELECT #1 Note 1003 /* select#1 */ select `test`.`up`.`a` AS `a`,`test`.`up`.`b` AS `b` from `test`.`t1` `up` where <expr_cache><`test`.`up`.`a`>(<in_optimizer>(`test`.`up`.`a`,`test`.`up`.`a` in ( <materialize> (/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` where 1 ), <primary_index_lookup>(`test`.`up`.`a` in <temporary table> on distinct_key where `test`.`up`.`a` = `<subquery2>`.`a`)))) @@ -3101,7 +3103,7 @@ retailerID statusID changed drop table t1; create table t1(a int, primary key (a)); insert into t1 values (10); -create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b)); +create table t2 (a int primary key, b varchar(32), c int, unique key cb(c, b)); insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989'); insert into t2(a, c, b) values (4,10,'360'), (5,10,'35998'), (6,10,'35999'); analyze table t1; @@ -3114,7 +3116,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 1 PRIMARY r const PRIMARY PRIMARY 4 const 1 -2 SUBQUERY t2 range b b 40 NULL 3 Using where +2 SUBQUERY t2 ref cb cb 5 const 1 Using where SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; @@ -3126,7 +3128,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 1 PRIMARY r const PRIMARY PRIMARY 4 const 1 -2 SUBQUERY t2 range b b 40 NULL 3 Using index condition +2 SUBQUERY t2 ref cb cb 5 const 1 Using index condition; Using where SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10; @@ -4437,7 +4439,7 @@ out_a MIN(b) DROP TABLE t1; CREATE TABLE t1 (a INT); CREATE TABLE t2 (a INT); -INSERT INTO t1 VALUES (1),(2); +INSERT INTO t1 VALUES (1),(2),(3),(4); INSERT INTO t2 VALUES (1),(2); SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a)); 2 @@ -4446,7 +4448,7 @@ SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a)); EXPLAIN EXTENDED SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.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 +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 Using where 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00 Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 @@ -4455,7 +4457,7 @@ EXPLAIN EXTENDED SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION (SELECT 1 FROM t2 WHERE t1.a = t2.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 +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 Using where 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where 3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 2 100.00 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL @@ -5731,8 +5733,8 @@ SET join_cache_level=0; EXPLAIN SELECT * FROM t1 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON 1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where -2 MATERIALIZED t2 index NULL PRIMARY 4 NULL 3 Using index -2 MATERIALIZED it index PRIMARY PRIMARY 4 NULL 3 Using index +2 DEPENDENT SUBQUERY it eq_ref PRIMARY PRIMARY 4 func 1 Using index +2 DEPENDENT SUBQUERY t2 index NULL PRIMARY 4 NULL 3 Using index SELECT * FROM t1 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON 1); pk i 11 0 |