diff options
Diffstat (limited to 'mysql-test/r/subselect.result')
| -rw-r--r-- | mysql-test/r/subselect.result | 42 |
1 files changed, 29 insertions, 13 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 82b8e740cdc..6acea0d1680 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -2969,9 +2969,10 @@ Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cac explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N'); 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 -1 PRIMARY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; FirstMatch(t1) +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 10 func,func 1 100.00 +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` semi join (`test`.`t2`) where ((`test`.`t2`.`two` = `test`.`t1`.`two`) and (`test`.`t2`.`one` = `test`.`t1`.`one`) and (`test`.`t2`.`flag` = 'N')) +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`flag` = 'N')) 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 @@ -3558,7 +3559,8 @@ EXPLAIN SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 23 test.t1.a,test.t1.b 1 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary ALTER TABLE t1 ADD INDEX(a); SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); a b @@ -3568,8 +3570,9 @@ AAA 8 EXPLAIN SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where -2 DEPENDENT SUBQUERY t1 index NULL a 8 NULL 1 +1 PRIMARY t1 ALL a NULL NULL NULL 9 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 23 test.t1.a,test.t1.b 1 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary DROP TABLE t1; create table t1( f1 int,f2 int); insert into t1 values (1,1),(2,2); @@ -4512,15 +4515,17 @@ INSERT INTO t1 VALUES (1),(2); EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 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 -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary +1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 100.00 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where <expr_cache><1>(<in_optimizer>(1,<exists>(select 1 from `test`.`t1` group by `test`.`t1`.`a` having (1 = <ref_null_helper>(1))))) +Note 1003 select 1 AS `1` from <materialize> (select 1 from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` where (`<subquery2>`.`1` = 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 -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary +1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 100.00 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where <expr_cache><1>(<in_optimizer>(1,<exists>(select 1 from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` having (1 = <ref_null_helper>(1))))) +Note 1003 select 1 AS `1` from <materialize> (select 1 from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a`) join `test`.`t1` where (`<subquery2>`.`1` = 1) DROP TABLE t1; # # Bug#45061: Incorrectly market field caused wrong result. @@ -5548,10 +5553,11 @@ explain SELECT * FROM ot1,ot4 WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a FROM it2,it3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ot1 ALL NULL NULL NULL NULL 2 Start temporary -1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) -1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using join buffer (flat, BNL join) -1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; End temporary; Using join buffer (flat, BNL join) +1 PRIMARY ot1 ALL NULL NULL NULL NULL 2 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 24 Using where +1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +2 SUBQUERY it2 ALL NULL NULL NULL NULL 4 +2 SUBQUERY it3 ALL NULL NULL NULL NULL 6 Using join buffer (flat, BNL join) DROP TABLE IF EXISTS ot1, ot4, it2, it3; # # Bug#729039: NULL keys used to evaluate subquery @@ -6016,4 +6022,14 @@ ERROR 21000: Subquery returns more than 1 row SET SESSION sql_mode=@old_sql_mode; DEALLOCATE PREPARE stmt; DROP TABLE t1; +# +# LP BUG#833777 Performance regression with deeply nested subqueries +# +create table t1 (a int not null, b char(10) not null); +insert into t1 values (1, 'a'); +set @@optimizer_switch='in_to_exists=on,semijoin=off,materialization=off,subquery_cache=off'; +select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1))))))))))))))))))))))))))))); +a +1 +drop table t1; set optimizer_switch=@subselect_tmp; |