diff options
Diffstat (limited to 'mysql-test/main/subselect_no_opts.result')
| -rw-r--r-- | mysql-test/main/subselect_no_opts.result | 20 |
1 files changed, 10 insertions, 10 deletions
diff --git a/mysql-test/main/subselect_no_opts.result b/mysql-test/main/subselect_no_opts.result index 1d51bd6b976..f9561178ca5 100644 --- a/mysql-test/main/subselect_no_opts.result +++ b/mysql-test/main/subselect_no_opts.result @@ -1509,7 +1509,7 @@ explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1 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 DEPENDENT SUBQUERY t1 ref a a 5 func 1001 100.00 Using index -2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 66.67 Using where; Using index; Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 33.33 Using where; Using index; Using join buffer (flat, BNL join) Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` join `test`.`t3` where `test`.`t3`.`a` = `test`.`t1`.`b` and <cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)) select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); @@ -4407,7 +4407,7 @@ CREATE INDEX I2 ON t1 (b); EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY t1 index_subquery I1 I1 2 func 2 Using index; Using where +2 DEPENDENT SUBQUERY t1 index_subquery I1 I1 2 func 1 Using index; Using where SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1); a b CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10)); @@ -4417,14 +4417,14 @@ CREATE INDEX I2 ON t2 (b); EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY t2 index_subquery I1 I1 4 func 2 Using index; Using where +2 DEPENDENT SUBQUERY t2 index_subquery I1 I1 4 func 1 Using index; Using where SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2); a b EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY t1 index_subquery I1 I1 2 func 2 Using index; Using where +2 DEPENDENT SUBQUERY t1 index_subquery I1 I1 2 func 1 Using index; Using where SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500); a b DROP TABLE t1,t2; @@ -6096,7 +6096,7 @@ WHERE col_varchar_nokey IN (SELECT col_varchar_key FROM it1 WHERE col_int_key IS NULL); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot system NULL NULL NULL NULL 1 -2 DEPENDENT SUBQUERY it1 index_subquery idx_cvk_cik idx_cvk_cik 9 func,const 2 Using index; Using where +2 DEPENDENT SUBQUERY it1 index_subquery idx_cvk_cik idx_cvk_cik 9 func,const 1 Using index; Using where SELECT col_int_nokey FROM ot WHERE col_varchar_nokey IN (SELECT col_varchar_key FROM it1 WHERE col_int_key IS NULL); @@ -6652,7 +6652,7 @@ SET @@optimizer_switch='semijoin=off,materialization=off,in_to_exists=on,subquer EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT a AS field1 FROM t1 GROUP BY field1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL a 4 NULL 2 Using where; Using index -2 DEPENDENT SUBQUERY t1 index_subquery a a 4 func 2 Using index +2 DEPENDENT SUBQUERY t1 index_subquery a a 4 func 1 Using index SELECT * FROM t1 WHERE a IN (SELECT a AS field1 FROM t1 GROUP BY field1); a 2009-01-01 @@ -6855,7 +6855,7 @@ FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY alias1 index a a 19 NULL 11 Using where; Using index -1 PRIMARY alias2 ref a a 19 test.alias1.a 2 Using index +1 PRIMARY alias2 ref a a 19 test.alias1.a 1 Using index 1 PRIMARY alias3 index NULL a 19 NULL 11 Using index; Using join buffer (flat, BNL join) 2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where SELECT MAX( alias2.a ) @@ -7006,7 +7006,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where -1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index +1 PRIMARY t3 ref d d 5 test.t2.b 1 Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @tmp_mdev410=@@global.userstat; set global userstat=on; @@ -7038,7 +7038,7 @@ EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP B id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 2 SUBQUERY t1 range a a 5 NULL 2 Using where; Using index -2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index +2 SUBQUERY t2 ref b b 5 test.t1.a 1 Using index DROP TABLE t1,t2; # # MDEV-435: Expensive subqueries may be evaluated during optimization in merge_key_fields @@ -7072,7 +7072,7 @@ EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP B id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 2 SUBQUERY t1 range a a 5 NULL 2 Using where; Using index -2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index +2 SUBQUERY t2 ref b b 5 test.t1.a 1 Using index DROP TABLE t1,t2; # # MDEV-5991: crash in Item_field::used_tables |