Re: [Maria-developers] Please review: [Commits] Rev 3406: Patch for mdev-287: CHEAP SQ: A query with subquery in SELECT list, EXISTS, inner joins takes hundreds times longer in file:///home/tsk/mprog/src/5.5-lpb944706/

Hi Timour, Ok to push. I was wondering what are your plans with regards to pushing 5.5-timour to main? Is this the last issue? On Wed, May 30, 2012 at 12:22:06AM +0300, Timour Katchaounov wrote:

Sergey,

Please review the following bug fix that we discussed today.

Timour

------------------------------------------------------------ revno: 3406 revision-id: timour@askmonty.org-20120529211853-hww47vl7d4u4ae23 parent: timour@askmonty.org-20120524110828-r0mm8sm1vn8a095e committer: timour@askmonty.org branch nick: 5.5-lpb944706 timestamp: Wed 2012-05-30 00:18:53 +0300 message: Patch for mdev-287: CHEAP SQ: A query with subquery in SELECT list, EXISTS, inner joins takes hundreds times longer

Analysis:

The fix for lp:944706 introduces early subquery optimization. While a subquery is being optimized some of its predicates may be removed. In the test case, the EXISTS subquery is constant, and is evaluated to TRUE. As a result the whole OR is TRUE, and thus the correlated condition "b = alias1.b" is optimized away. The subquery becomes non-correlated.

The subquery cache is designed to work only for correlated subqueries. If constant subquery optimization is disallowed, then the constant subquery is not evaluated, the subquery remains correlated, and its execution is cached. As a result execution is fast.

However, when the constant subquery was optimized away, it was neither cached by the subquery cache, nor it was cached by the internal subquery caching. The latter was due to the fact that the subquery still appeared as correlated to the subselect_XYZ_engine::exec methods, and they re-executed the subquery on each call to Item_subselect::exec.

Solution:

The solution is to update the correlated status of the subquery after it has been optimized. This status consists of: - st_select_lex::is_correlated - Item_subselect::is_correlated - SELECT_LEX::uncacheable - SELECT_LEX_UNIT::uncacheable The status is updated by st_select_lex::update_correlated_cache(), and its caller st_select_lex::optimize_unflattened_subqueries. The solution relies on the fact that the optimizer already called st_select_lex::update_used_tables() for each subquery. This allows to efficiently update the correlated status of each subquery without walking the whole subquery tree.

Notice that his patch is an improvement over MySQL 5.6 and older, where subqueries are not pre-optimized, and the above analysis is not possible.

=== modified file 'mysql-test/r/derived_opt.result' --- a/mysql-test/r/derived_opt.result 2012-02-14 14:52:56 +0000 +++ b/mysql-test/r/derived_opt.result 2012-05-29 21:18:53 +0000 @@ -161,24 +161,24 @@ prepare stmt1 from @stmt ; execute stmt1 ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -5 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -4 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +5 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables execute stmt1 ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -5 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -4 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +5 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain SELECT (SELECT SUM(c1 + c12 + 0.0) FROM t2 where (t1.c2 - 0e-3) = t2.c2 GROUP BY t1.c15 LIMIT 1) as scalar_s, exists (select 1.0e+0 from t2 where t2.c3 * 9.0000000000 = t1.c4) as exists_s, c5 * 4 in (select c6 + 0.3e+1 from t2) as in_s, (c7 - 4, c8 - 4) in (select c9 + 4.0, c10 + 40e-1 from t2) as in_row_s FROM t1, (select c25 x, c32 y from t2) tt WHERE x * 1 = c25; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -5 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -4 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +5 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables deallocate prepare stmt1; drop tables t1,t2; set @@optimizer_switch=@save_optimizer_switch;

=== modified file 'mysql-test/r/explain.result' --- a/mysql-test/r/explain.result 2012-05-17 10:46:05 +0000 +++ b/mysql-test/r/explain.result 2012-05-29 21:18:53 +0000 @@ -225,10 +225,10 @@ INSERT INTO t2 VALUES (NULL), (0); EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1 -Note 1003 select <expr_cache><NULL>((select 1 from `test`.`t2` where (`test`.`t2`.`d` = NULL))) AS `(SELECT 1 FROM t2 WHERE d = c)` from dual +Note 1003 select (select 1 from `test`.`t2` where (`test`.`t2`.`d` = NULL)) AS `(SELECT 1 FROM t2 WHERE d = c)` from dual DROP TABLE t1, t2; # # Bug#30302: Tables that were optimized away are printed in the

=== modified file 'mysql-test/r/index_merge_innodb.result' --- a/mysql-test/r/index_merge_innodb.result 2012-02-21 19:51:56 +0000 +++ b/mysql-test/r/index_merge_innodb.result 2012-05-29 21:18:53 +0000 @@ -654,7 +654,7 @@ EXPLAIN SELECT t1.f1 FROM t1 WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using index -2 DEPENDENT SUBQUERY t2 index_merge f2,f3 f3,f2 2,5 NULL 1 Using intersect(f3,f2); Using where; Using index +2 SUBQUERY t2 index_merge f2,f3 f3,f2 2,5 NULL 1 Using intersect(f3,f2); Using where; Using index DROP TABLE t1,t2; # # BUG#56862/640419: Wrong result with sort_union index merge when one

=== modified file 'mysql-test/r/index_merge_myisam.result' --- a/mysql-test/r/index_merge_myisam.result 2012-02-15 17:08:08 +0000 +++ b/mysql-test/r/index_merge_myisam.result 2012-05-29 21:18:53 +0000 @@ -1487,7 +1487,7 @@ EXPLAIN SELECT t1.f1 FROM t1 WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 -2 DEPENDENT SUBQUERY t2 ref f2,f3 f2 5 const 1 Using where +2 SUBQUERY t2 ref f2,f3 f2 5 const 1 Using where DROP TABLE t1,t2; create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

=== modified file 'mysql-test/r/join_outer.result' --- a/mysql-test/r/join_outer.result 2012-05-17 10:46:05 +0000 +++ b/mysql-test/r/join_outer.result 2012-05-29 21:18:53 +0000 @@ -1872,7 +1872,7 @@ SELECT t2.a FROM t1 LEFT JOIN t2 ON (6) id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00 -2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where +2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1003 select NULL AS `a` from `test`.`t2` where 1 DROP TABLE t1,t2,t3;

=== modified file 'mysql-test/r/join_outer_jcl6.result' --- a/mysql-test/r/join_outer_jcl6.result 2012-05-17 10:46:05 +0000 +++ b/mysql-test/r/join_outer_jcl6.result 2012-05-29 21:18:53 +0000 @@ -1883,7 +1883,7 @@ SELECT t2.a FROM t1 LEFT JOIN t2 ON (6) id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00 -2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where +2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1003 select NULL AS `a` from `test`.`t2` where 1 DROP TABLE t1,t2,t3;

=== modified file 'mysql-test/r/ps.result' --- a/mysql-test/r/ps.result 2012-04-10 06:28:13 +0000 +++ b/mysql-test/r/ps.result 2012-05-29 21:18:53 +0000 @@ -160,26 +160,26 @@ execute stmt1 ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 6 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table -5 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -4 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +5 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables execute stmt1 ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 6 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table -5 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -4 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +5 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain SELECT (SELECT SUM(c1 + c12 + 0.0) FROM t2 where (t1.c2 - 0e-3) = t2.c2 GROUP BY t1.c15 LIMIT 1) as scalar_s, exists (select 1.0e+0 from t2 where t2.c3 * 9.0000000000 = t1.c4) as exists_s, c5 * 4 in (select c6 + 0.3e+1 from t2) as in_s, (c7 - 4, c8 - 4) in (select c9 + 4.0, c10 + 40e-1 from t2) as in_row_s FROM t1, (select c25 x, c32 y from t2) tt WHERE x * 1 = c25; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 6 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table -5 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -4 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +5 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables deallocate prepare stmt1; set optimizer_switch=@tmp_optimizer_switch; drop tables t1,t2;

=== modified file 'mysql-test/r/subselect.result' --- a/mysql-test/r/subselect.result 2012-05-18 11:52:01 +0000 +++ b/mysql-test/r/subselect.result 2012-05-29 21:18:53 +0000 @@ -51,12 +51,12 @@ ERROR 42S22: Reference 'a' not supported EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> system NULL NULL NULL NULL 1 100.00 -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1 Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1 -Note 1003 select 1 AS `1` from dual having (<expr_cache><1>((select 1)) = 1) +Note 1003 select 1 AS `1` from dual having ((select 1) = 1) SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; 1 1 @@ -904,7 +904,7 @@ a t1.a in (select t2.a from t2) explain extended SELECT t1.a, t1.a in (select t2.a from t2) 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 DEPENDENT SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index +2 SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in t2 on a checking NULL having <is_not_null_test>(`test`.`t2`.`a`))))) AS `t1.a in (select t2.a from t2)` from `test`.`t1` CREATE TABLE t3 (a int(11) default '0'); @@ -1317,7 +1317,7 @@ SELECT 0 IN (SELECT 1 FROM t1 a); EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: Note 1003 select <in_optimizer>(0,<exists>(select 1 from dual where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)` INSERT INTO t1 (pseudo) VALUES ('test1'); @@ -1327,7 +1327,7 @@ SELECT 0 IN (SELECT 1 FROM t1 a); EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: Note 1003 select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)` drop table t1; @@ -1605,25 +1605,25 @@ a3 1 explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Using where; Full scan on NULL key +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Using where; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < 'a2') having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1` drop table t1,t2; @@ -3097,7 +3097,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c 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 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition +2 SUBQUERY t2 range b b 40 NULL 2 Using index condition 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; @@ -3109,7 +3109,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c 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 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition +2 SUBQUERY t2 range b b 40 NULL 2 Using index condition 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; @@ -3160,7 +3160,7 @@ INSERT INTO t2 VALUES (1),(2),(3); EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key +2 SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key SELECT a, a IN (SELECT a FROM t1) FROM t2; a a IN (SELECT a FROM t1) 1 1 @@ -3696,7 +3696,7 @@ ORDER BY t1.t DESC LIMIT 1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 system NULL NULL NULL NULL 1 1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index -2 DEPENDENT SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index +2 SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index SELECT * FROM t1,t2 WHERE t1.t = (SELECT t1.t FROM t1 WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1 @@ -4577,7 +4577,7 @@ FROM t1 WHERE a = 230; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) FROM t1 WHERE a = 230; @@ -5713,7 +5713,7 @@ WHERE ('0') IN ( SELECT a FROM t1 GROUP GROUP BY b; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where SELECT b FROM t1 WHERE ('0') IN ( SELECT a FROM t1 GROUP BY a ) GROUP BY b; @@ -6530,7 +6530,7 @@ EXPLAIN SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 0 NULL @@ -6538,7 +6538,7 @@ EXPLAIN SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 0 0 @@ -6573,7 +6573,7 @@ WHERE alias1.a = alias2.a OR alias1.a = HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible HAVING -2 DEPENDENT SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where +2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where SELECT MAX( alias2.a ) AS field FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 WHERE alias1.a = alias2.a OR alias1.a = 'y' @@ -6587,7 +6587,7 @@ id select_type table type possible_keys 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 alias3 index NULL a 19 NULL 11 Using index; Using join buffer (flat, BNL join) -2 DEPENDENT SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where +2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where SELECT MAX( alias2.a ) FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 ); @@ -6615,5 +6615,90 @@ SELECT * FROM t1 WHERE a1 = (SELECT COUNT(*) FROM t1 WHERE a1 IN (SELECT a1 FROM t1, t2)); a1 drop table t1, t2; +# +# MDEV-287 CHEAP SQ: A query with subquery in SELECT list, EXISTS, +# inner joins takes hundreds times longer +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(7); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (4),(5); +CREATE TABLE t3 (c INT); +INSERT INTO t3 VALUES (8),(3); +set @@expensive_subquery_limit= 0; +EXPLAIN +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY alias1 ALL NULL NULL NULL NULL 2 +1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +1 PRIMARY alias3 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2 +flush status; +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +(SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 6 +Subquery_cache_miss 2 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 8 +Handler_read_last 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 22 +set @@expensive_subquery_limit= default; +EXPLAIN +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY alias1 ALL NULL NULL NULL NULL 2 +1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +1 PRIMARY alias3 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2 +flush status; +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +(SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 0 +Subquery_cache_miss 0 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 0 +Handler_read_last 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 16 +drop table t1, t2, t3; # return optimizer switch changed in the beginning of this test set optimizer_switch=@subselect_tmp;

=== modified file 'mysql-test/r/subselect4.result' --- a/mysql-test/r/subselect4.result 2012-05-17 10:46:05 +0000 +++ b/mysql-test/r/subselect4.result 2012-05-29 21:18:53 +0000 @@ -226,10 +226,10 @@ NULL EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) AS RESULT FROM t1 ; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1 -Note 1003 select <expr_cache><>((select 1 from `test`.`t2` where 0)) AS `RESULT` from dual +Note 1003 select (select 1 from `test`.`t2` where 0) AS `RESULT` from dual first equivalent variant SELECT (SELECT 1 FROM t2 WHERE d = IFNULL(c,NULL)) AS RESULT FROM t1 GROUP BY c ; RESULT @@ -237,10 +237,10 @@ NULL EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = IFNULL(c,NULL)) AS RESULT FROM t1 GROUP BY c; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1 -Note 1003 select <expr_cache><>((select 1 from `test`.`t2` where 0)) AS `RESULT` from dual group by NULL +Note 1003 select (select 1 from `test`.`t2` where 0) AS `RESULT` from dual group by NULL second equivalent variant SELECT (SELECT 1 FROM t2 WHERE d = c) AS RESULT FROM t1 GROUP BY c ; RESULT @@ -248,10 +248,10 @@ NULL EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) AS RESULT FROM t1 GROUP BY c ; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1 -Note 1003 select <expr_cache><>((select 1 from `test`.`t2` where 0)) AS `RESULT` from dual group by NULL +Note 1003 select (select 1 from `test`.`t2` where 0) AS `RESULT` from dual group by NULL DROP TABLE t1,t2; # # BUG#45928 "Differing query results depending on MRR and @@ -649,7 +649,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2) as not_in; not_in NULL @@ -671,7 +671,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2) as not_in; not_in NULL @@ -679,7 +679,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) > 7) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) > 7) as not_in; not_in 1 @@ -687,7 +687,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) is null) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) is null) as not_in; not_in NULL @@ -695,7 +695,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2) as not_in; not_in NULL @@ -703,7 +703,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2) as not_in; not_in NULL @@ -719,28 +719,28 @@ EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2); f1 f2 EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2); f1 f2 EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2); f1 f2 EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2) as not_in; not_in NULL @@ -748,21 +748,21 @@ EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2); f1 f2 EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2); f1 f2 EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2) as not_in; not_in NULL @@ -770,7 +770,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) > 7) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) > 7) as not_in; not_in 1 @@ -778,7 +778,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) is null) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) is null) as not_in; not_in NULL @@ -786,7 +786,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2) as not_in; not_in NULL @@ -794,7 +794,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2) as not_in; not_in NULL @@ -835,7 +835,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10) as not_in; not_in NULL @@ -857,7 +857,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10) as not_in; not_in NULL @@ -865,7 +865,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) > 7) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) > 7) as not_in; not_in 1 @@ -873,7 +873,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) is null) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) is null) as not_in; not_in NULL @@ -881,7 +881,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2 WHERE f3 > 10) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2 WHERE f3 > 10) as not_in; not_in NULL @@ -889,7 +889,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2 WHERE f3 > 10) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2 WHERE f3 > 10) as not_in; not_in NULL @@ -905,28 +905,28 @@ EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10); f1 f2 EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2 WHERE f3 > 10); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2 WHERE f3 > 10); f1 f2 EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2 WHERE f3 > 10); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2 WHERE f3 > 10); f1 f2 EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10) as not_in; not_in NULL @@ -934,21 +934,21 @@ EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10); f1 f2 EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2 WHERE f3 > 10); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2 WHERE f3 > 10); f1 f2 EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10) as not_in; not_in NULL @@ -956,7 +956,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) > 7) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) > 7) as not_in; not_in 1 @@ -964,7 +964,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) is null) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) is null) as not_in; not_in NULL @@ -972,7 +972,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2 WHERE f3 > 10) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2 WHERE f3 > 10) as not_in; not_in NULL @@ -980,7 +980,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2 WHERE f3 > 10) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2 WHERE f3 > 10) as not_in; not_in NULL @@ -1240,7 +1240,7 @@ EXPLAIN SELECT i FROM t1 WHERE (1) NOT IN (SELECT i FROM t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 -2 DEPENDENT SUBQUERY t2 index_subquery k k 5 const 2 Using index +2 SUBQUERY t2 index_subquery k k 5 const 2 Using index DROP TABLE t2; DROP TABLE t1; # @@ -1258,8 +1258,8 @@ WHERE ('v') IN (SELECT f4 FROM t2) GROUP BY f9; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(t2.f3), (SELECT COUNT(f3) FROM t1 WHERE t2.f1) AS f9 FROM t2 JOIN t1 ON t1.f3 @@ -1274,8 +1274,8 @@ WHERE ('v') IN (SELECT f4 FROM t2) ORDER BY f9; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(t2.f3), (SELECT COUNT(f3) FROM t1 WHERE t2.f1) AS f9 FROM t2 JOIN t1 ON t1.f3 @@ -1291,8 +1291,8 @@ WHERE ('v') IN (SELECT f4 FROM t2) GROUP BY f9; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(t2.f3), (SELECT t2.f1 FROM t1 limit 1) AS f9 FROM t2 JOIN t1 @@ -1307,8 +1307,8 @@ WHERE ('v') IN (SELECT f4 FROM t2) ORDER BY f9; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(t2.f3), (SELECT t2.f1 FROM t1 limit 1) AS f9 FROM t2 JOIN t1 @@ -1390,7 +1390,7 @@ EXPLAIN SELECT 'bug' FROM DUAL WHERE ( 5 ) IN ( SELECT * FROM v1 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where 3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used 4 UNION NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL @@ -1400,7 +1400,7 @@ EXPLAIN SELECT ( 5 ) IN ( SELECT * FROM v1 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where 3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used 4 UNION NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL @@ -1411,7 +1411,7 @@ EXPLAIN SELECT 'bug' FROM DUAL WHERE ( 5 ) IN (SELECT * FROM v2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where 3 DERIVED t1 system NULL NULL NULL NULL 1 4 UNION t2 system NULL NULL NULL NULL 1 NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL @@ -1431,7 +1431,7 @@ EXPLAIN SELECT ( 5 ) IN ( SELECT * FROM v2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where 3 DERIVED t1 system NULL NULL NULL NULL 1 4 UNION t2 system NULL NULL NULL NULL 1 NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL @@ -1443,7 +1443,7 @@ EXPLAIN SELECT 'bug' FROM DUAL WHERE ( 5 ) IN ( SELECT * FROM v1 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where 3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used 4 UNION NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL @@ -1453,7 +1453,7 @@ EXPLAIN SELECT ( 5 ) IN ( SELECT * FROM v1 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where 3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used 4 UNION NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL @@ -1464,7 +1464,7 @@ EXPLAIN SELECT 'bug' FROM DUAL WHERE ( 5 ) IN (SELECT * FROM v2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where 3 DERIVED t1 system NULL NULL NULL NULL 1 4 UNION t2 system NULL NULL NULL NULL 1 NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL @@ -1474,7 +1474,7 @@ EXPLAIN SELECT 'bug' FROM t3 WHERE ( 5 ) IN (SELECT * FROM v2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 system NULL NULL NULL NULL 1 -2 DEPENDENT SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where 3 DERIVED t1 system NULL NULL NULL NULL 1 4 UNION t2 system NULL NULL NULL NULL 1 NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL @@ -1484,7 +1484,7 @@ EXPLAIN SELECT ( 5 ) IN ( SELECT * FROM v2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 Using where 3 DERIVED t1 system NULL NULL NULL NULL 1 4 UNION t2 system NULL NULL NULL NULL 1 NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL @@ -1635,8 +1635,8 @@ EXPLAIN SELECT * FROM t1 WHERE ( 6 ) NOT IN ( SELECT t2.f3 FROM t2 JOIN t3 ON t3.f10 = t2.f10); 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 DEPENDENT SUBQUERY t3 system NULL NULL NULL NULL 1 -2 DEPENDENT SUBQUERY t2 ref_or_null f10 f10 10 const,const 2 Using where; Using index +2 SUBQUERY t3 system NULL NULL NULL NULL 1 +2 SUBQUERY t2 ref_or_null f10 f10 10 const,const 2 Using where; Using index SELECT * FROM t1 WHERE ( 6 ) NOT IN ( SELECT t2.f3 FROM t2 JOIN t3 ON t3.f10 = t2.f10); f4 drop table t1,t2,t3;

=== modified file 'mysql-test/r/subselect_extra_no_semijoin.result' --- a/mysql-test/r/subselect_extra_no_semijoin.result 2012-01-13 14:50:02 +0000 +++ b/mysql-test/r/subselect_extra_no_semijoin.result 2012-05-29 21:18:53 +0000 @@ -46,7 +46,7 @@ select * from t1 where id in (select id from t1 as x1 where (t1.cur_date is null)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: Note 1276 Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1 Note 1003 select 1 AS `id`,'2007-04-25 18:30:22' AS `cur_date` from dual where <expr_cache><1>(<in_optimizer>(1,<exists>(select `test`.`x1`.`id` from `test`.`t1` `x1` where 0))) @@ -58,7 +58,7 @@ select * from t2 where id in (select id from t2 as x1 where (t2.cur_date is null)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00 -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: Note 1276 Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1 Note 1003 select 1 AS `id`,'2007-04-25' AS `cur_date` from dual where <expr_cache><1>(<in_optimizer>(1,<exists>(select `test`.`x1`.`id` from `test`.`t2` `x1` where 0)))

=== modified file 'mysql-test/r/subselect_mat.result' --- a/mysql-test/r/subselect_mat.result 2012-05-17 10:46:05 +0000 +++ b/mysql-test/r/subselect_mat.result 2012-05-29 21:18:53 +0000 @@ -1163,7 +1163,7 @@ set @@optimizer_switch='materialization= explain select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); min(a1) NULL @@ -1182,7 +1182,7 @@ set @@optimizer_switch='materialization= explain select min(a1) from t1 where 7 in (select b1 from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table select min(a1) from t1 where 7 in (select b1 from t2); min(a1) NULL @@ -1190,7 +1190,7 @@ NULL explain select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4; min(a1) NULL @@ -2162,7 +2162,7 @@ EXPLAIN SELECT (SELECT f3a FROM t3) NOT IN (SELECT f1a FROM t1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where +3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT (SELECT f3a FROM t3) NOT IN (SELECT f1a FROM t1); (SELECT f3a FROM t3) NOT IN (SELECT f1a FROM t1) @@ -2179,7 +2179,7 @@ EXPLAIN SELECT (SELECT f3a, f3b FROM t3) NOT IN (SELECT f1a, f1b FROM t1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where +3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT (SELECT f3a, f3b FROM t3) NOT IN (SELECT f1a, f1b FROM t1); (SELECT f3a, f3b FROM t3) NOT IN (SELECT f1a, f1b FROM t1) @@ -2234,7 +2234,7 @@ EXPLAIN EXTENDED SELECT MAX(t1.b) AS max_res FROM t1 WHERE (9) IN (SELECT a FROM t2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1003 select max(`test`.`t1`.`b`) AS `max_res` from `test`.`t1` where 0 DROP TABLE t1,t2;

=== modified file 'mysql-test/r/subselect_mat_cost.result' --- a/mysql-test/r/subselect_mat_cost.result 2012-03-01 22:22:22 +0000 +++ b/mysql-test/r/subselect_mat_cost.result 2012-05-29 21:18:53 +0000 @@ -496,7 +496,7 @@ from City where City.population > 10000000; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY City range Population Population 5 NULL 4 Using index condition; Rowid-ordered scan -2 DEPENDENT SUBQUERY Country index_subquery CountryCapital CountryCapital 5 func 2 Using index; Using where +2 SUBQUERY Country index_subquery CountryCapital CountryCapital 5 func 2 Using index; Using where select Name, City.id in (select capital from Country where capital is not null) as is_capital from City where City.population > 10000000;

=== modified file 'mysql-test/r/subselect_mat_cost_bugs.result' --- a/mysql-test/r/subselect_mat_cost_bugs.result 2012-05-17 10:46:05 +0000 +++ b/mysql-test/r/subselect_mat_cost_bugs.result 2012-05-29 21:18:53 +0000 @@ -124,7 +124,7 @@ FROM t3 RIGHT JOIN t1 ON t1.pk = t3.f1 WHERE t3.f3 OR ( 3 ) IN ( SELECT f2 FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT t1.* FROM t3 RIGHT JOIN t1 ON t1.pk = t3.f1 WHERE t3.f3 OR ( 3 ) IN ( SELECT f2 FROM t2 );

=== modified file 'mysql-test/r/subselect_no_mat.result' --- a/mysql-test/r/subselect_no_mat.result 2012-05-18 11:52:01 +0000 +++ b/mysql-test/r/subselect_no_mat.result 2012-05-29 21:18:53 +0000 @@ -58,12 +58,12 @@ ERROR 42S22: Reference 'a' not supported EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> system NULL NULL NULL NULL 1 100.00 -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1 Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1 -Note 1003 select 1 AS `1` from dual having (<expr_cache><1>((select 1)) = 1) +Note 1003 select 1 AS `1` from dual having ((select 1) = 1) SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; 1 1 @@ -911,7 +911,7 @@ a t1.a in (select t2.a from t2) explain extended SELECT t1.a, t1.a in (select t2.a from t2) 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 DEPENDENT SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index +2 SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in t2 on a checking NULL having <is_not_null_test>(`test`.`t2`.`a`))))) AS `t1.a in (select t2.a from t2)` from `test`.`t1` CREATE TABLE t3 (a int(11) default '0'); @@ -1324,7 +1324,7 @@ SELECT 0 IN (SELECT 1 FROM t1 a); EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: Note 1003 select <in_optimizer>(0,<exists>(select 1 from dual where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)` INSERT INTO t1 (pseudo) VALUES ('test1'); @@ -1334,7 +1334,7 @@ SELECT 0 IN (SELECT 1 FROM t1 a); EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: Note 1003 select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)` drop table t1; @@ -1612,25 +1612,25 @@ a3 1 explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Using where; Full scan on NULL key +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Using where; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < 'a2') having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1` drop table t1,t2; @@ -3103,7 +3103,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c 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 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition +2 SUBQUERY t2 range b b 40 NULL 2 Using index condition 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; @@ -3115,7 +3115,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c 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 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition +2 SUBQUERY t2 range b b 40 NULL 2 Using index condition 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; @@ -3166,7 +3166,7 @@ INSERT INTO t2 VALUES (1),(2),(3); EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key +2 SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key SELECT a, a IN (SELECT a FROM t1) FROM t2; a a IN (SELECT a FROM t1) 1 1 @@ -3700,7 +3700,7 @@ ORDER BY t1.t DESC LIMIT 1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 system NULL NULL NULL NULL 1 1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index -2 DEPENDENT SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index +2 SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index SELECT * FROM t1,t2 WHERE t1.t = (SELECT t1.t FROM t1 WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1 @@ -4526,13 +4526,13 @@ SET join_cache_level=0; EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) 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 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary Warnings: Note 1003 select 1 AS `1` from `test`.`t1` where 1 EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 WHERE a > 3 GROUP BY a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary +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 0 SET join_cache_level=@save_join_cache_level; @@ -4579,7 +4579,7 @@ FROM t1 WHERE a = 230; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) FROM t1 WHERE a = 230; @@ -5714,7 +5714,7 @@ WHERE ('0') IN ( SELECT a FROM t1 GROUP GROUP BY b; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where SELECT b FROM t1 WHERE ('0') IN ( SELECT a FROM t1 GROUP BY a ) GROUP BY b; @@ -6529,7 +6529,7 @@ EXPLAIN SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 0 NULL @@ -6537,7 +6537,7 @@ EXPLAIN SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 0 0 @@ -6572,7 +6572,7 @@ WHERE alias1.a = alias2.a OR alias1.a = HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible HAVING -2 DEPENDENT SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where +2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where SELECT MAX( alias2.a ) AS field FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 WHERE alias1.a = alias2.a OR alias1.a = 'y' @@ -6586,7 +6586,7 @@ id select_type table type possible_keys 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 alias3 index NULL a 19 NULL 11 Using index; Using join buffer (flat, BNL join) -2 DEPENDENT SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where +2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where SELECT MAX( alias2.a ) FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 ); @@ -6613,6 +6613,91 @@ SELECT * FROM t1 WHERE a1 = (SELECT COUNT(*) FROM t1 WHERE a1 IN (SELECT a1 FROM t1, t2)); a1 drop table t1, t2; +# +# MDEV-287 CHEAP SQ: A query with subquery in SELECT list, EXISTS, +# inner joins takes hundreds times longer +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(7); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (4),(5); +CREATE TABLE t3 (c INT); +INSERT INTO t3 VALUES (8),(3); +set @@expensive_subquery_limit= 0; +EXPLAIN +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY alias1 ALL NULL NULL NULL NULL 2 +1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +1 PRIMARY alias3 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2 +flush status; +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +(SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 6 +Subquery_cache_miss 2 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 8 +Handler_read_last 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 22 +set @@expensive_subquery_limit= default; +EXPLAIN +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY alias1 ALL NULL NULL NULL NULL 2 +1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +1 PRIMARY alias3 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2 +flush status; +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +(SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 0 +Subquery_cache_miss 0 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 0 +Handler_read_last 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 16 +drop table t1, t2, t3; # return optimizer switch changed in the beginning of this test set optimizer_switch=@subselect_tmp; set optimizer_switch=default;

=== modified file 'mysql-test/r/subselect_no_opts.result' --- a/mysql-test/r/subselect_no_opts.result 2012-05-18 11:52:01 +0000 +++ b/mysql-test/r/subselect_no_opts.result 2012-05-29 21:18:53 +0000 @@ -54,7 +54,7 @@ ERROR 42S22: Reference 'a' not supported EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> system NULL NULL NULL NULL 1 100.00 -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1 @@ -907,7 +907,7 @@ a t1.a in (select t2.a from t2) explain extended SELECT t1.a, t1.a in (select t2.a from t2) 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 DEPENDENT SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index +2 SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in t2 on a checking NULL having <is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2)` from `test`.`t1` CREATE TABLE t3 (a int(11) default '0'); @@ -1320,7 +1320,7 @@ SELECT 0 IN (SELECT 1 FROM t1 a); EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: Note 1003 select <in_optimizer>(0,<exists>(select 1 from dual where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)` INSERT INTO t1 (pseudo) VALUES ('test1'); @@ -1330,7 +1330,7 @@ SELECT 0 IN (SELECT 1 FROM t1 a); EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: Note 1003 select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)` drop table t1; @@ -1608,25 +1608,25 @@ a3 1 explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Using where; Full scan on NULL key +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Using where; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < 'a2') having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1` drop table t1,t2; @@ -3099,7 +3099,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c 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 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition +2 SUBQUERY t2 range b b 40 NULL 2 Using index condition 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; @@ -3111,7 +3111,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c 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 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition +2 SUBQUERY t2 range b b 40 NULL 2 Using index condition 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; @@ -3162,7 +3162,7 @@ INSERT INTO t2 VALUES (1),(2),(3); EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key +2 SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key SELECT a, a IN (SELECT a FROM t1) FROM t2; a a IN (SELECT a FROM t1) 1 1 @@ -3696,7 +3696,7 @@ ORDER BY t1.t DESC LIMIT 1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 system NULL NULL NULL NULL 1 1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index -2 DEPENDENT SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index +2 SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index SELECT * FROM t1,t2 WHERE t1.t = (SELECT t1.t FROM t1 WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1 @@ -4522,13 +4522,13 @@ SET join_cache_level=0; EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) 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 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary Warnings: Note 1003 select 1 AS `1` from `test`.`t1` where 1 EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 WHERE a > 3 GROUP BY a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary +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 0 SET join_cache_level=@save_join_cache_level; @@ -4575,7 +4575,7 @@ FROM t1 WHERE a = 230; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) FROM t1 WHERE a = 230; @@ -5710,7 +5710,7 @@ WHERE ('0') IN ( SELECT a FROM t1 GROUP GROUP BY b; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where SELECT b FROM t1 WHERE ('0') IN ( SELECT a FROM t1 GROUP BY a ) GROUP BY b; @@ -6525,7 +6525,7 @@ EXPLAIN SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 0 NULL @@ -6533,7 +6533,7 @@ EXPLAIN SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 0 0 @@ -6568,7 +6568,7 @@ WHERE alias1.a = alias2.a OR alias1.a = HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible HAVING -2 DEPENDENT SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where +2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where SELECT MAX( alias2.a ) AS field FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 WHERE alias1.a = alias2.a OR alias1.a = 'y' @@ -6582,7 +6582,7 @@ id select_type table type possible_keys 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 alias3 index NULL a 19 NULL 11 Using index; Using join buffer (flat, BNL join) -2 DEPENDENT SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where +2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where SELECT MAX( alias2.a ) FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 ); @@ -6610,6 +6610,91 @@ SELECT * FROM t1 WHERE a1 = (SELECT COUNT(*) FROM t1 WHERE a1 IN (SELECT a1 FROM t1, t2)); a1 drop table t1, t2; +# +# MDEV-287 CHEAP SQ: A query with subquery in SELECT list, EXISTS, +# inner joins takes hundreds times longer +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(7); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (4),(5); +CREATE TABLE t3 (c INT); +INSERT INTO t3 VALUES (8),(3); +set @@expensive_subquery_limit= 0; +EXPLAIN +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY alias1 ALL NULL NULL NULL NULL 2 +1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +1 PRIMARY alias3 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2 +flush status; +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +(SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 6 +Subquery_cache_miss 2 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 8 +Handler_read_last 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 22 +set @@expensive_subquery_limit= default; +EXPLAIN +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY alias1 ALL NULL NULL NULL NULL 2 +1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +1 PRIMARY alias3 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2 +flush status; +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +(SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 0 +Subquery_cache_miss 0 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 0 +Handler_read_last 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 16 +drop table t1, t2, t3; # return optimizer switch changed in the beginning of this test set optimizer_switch=@subselect_tmp; set @optimizer_switch_for_subselect_test=null;

=== modified file 'mysql-test/r/subselect_no_scache.result' --- a/mysql-test/r/subselect_no_scache.result 2012-05-18 11:52:01 +0000 +++ b/mysql-test/r/subselect_no_scache.result 2012-05-29 21:18:53 +0000 @@ -57,7 +57,7 @@ ERROR 42S22: Reference 'a' not supported EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> system NULL NULL NULL NULL 1 100.00 -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1 @@ -910,7 +910,7 @@ a t1.a in (select t2.a from t2) explain extended SELECT t1.a, t1.a in (select t2.a from t2) 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 DEPENDENT SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index +2 SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in t2 on a checking NULL having <is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2)` from `test`.`t1` CREATE TABLE t3 (a int(11) default '0'); @@ -1323,7 +1323,7 @@ SELECT 0 IN (SELECT 1 FROM t1 a); EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: Note 1003 select <in_optimizer>(0,<exists>(select 1 from dual where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)` INSERT INTO t1 (pseudo) VALUES ('test1'); @@ -1333,7 +1333,7 @@ SELECT 0 IN (SELECT 1 FROM t1 a); EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: Note 1003 select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)` drop table t1; @@ -1611,25 +1611,25 @@ a3 1 explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1` explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index -2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Using where; Full scan on NULL key +2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Using where; Full scan on NULL key Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < 'a2') having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1` drop table t1,t2; @@ -3103,7 +3103,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c 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 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition +2 SUBQUERY t2 range b b 40 NULL 2 Using index condition 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; @@ -3115,7 +3115,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c 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 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition +2 SUBQUERY t2 range b b 40 NULL 2 Using index condition 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; @@ -3166,7 +3166,7 @@ INSERT INTO t2 VALUES (1),(2),(3); EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key +2 SUBQUERY t1 index_subquery a a 5 func 2 Using index; Full scan on NULL key SELECT a, a IN (SELECT a FROM t1) FROM t2; a a IN (SELECT a FROM t1) 1 1 @@ -3702,7 +3702,7 @@ ORDER BY t1.t DESC LIMIT 1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 system NULL NULL NULL NULL 1 1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index -2 DEPENDENT SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index +2 SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index SELECT * FROM t1,t2 WHERE t1.t = (SELECT t1.t FROM t1 WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1 @@ -4583,7 +4583,7 @@ FROM t1 WHERE a = 230; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) FROM t1 WHERE a = 230; @@ -5719,7 +5719,7 @@ WHERE ('0') IN ( SELECT a FROM t1 GROUP GROUP BY b; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where SELECT b FROM t1 WHERE ('0') IN ( SELECT a FROM t1 GROUP BY a ) GROUP BY b; @@ -6536,7 +6536,7 @@ EXPLAIN SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 0 NULL @@ -6544,7 +6544,7 @@ EXPLAIN SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 0 0 @@ -6579,7 +6579,7 @@ WHERE alias1.a = alias2.a OR alias1.a = HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible HAVING -2 DEPENDENT SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where +2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where SELECT MAX( alias2.a ) AS field FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 WHERE alias1.a = alias2.a OR alias1.a = 'y' @@ -6593,7 +6593,7 @@ id select_type table type possible_keys 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 alias3 index NULL a 19 NULL 11 Using index; Using join buffer (flat, BNL join) -2 DEPENDENT SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where +2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where SELECT MAX( alias2.a ) FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 ); @@ -6621,6 +6621,91 @@ SELECT * FROM t1 WHERE a1 = (SELECT COUNT(*) FROM t1 WHERE a1 IN (SELECT a1 FROM t1, t2)); a1 drop table t1, t2; +# +# MDEV-287 CHEAP SQ: A query with subquery in SELECT list, EXISTS, +# inner joins takes hundreds times longer +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(7); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (4),(5); +CREATE TABLE t3 (c INT); +INSERT INTO t3 VALUES (8),(3); +set @@expensive_subquery_limit= 0; +EXPLAIN +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY alias1 ALL NULL NULL NULL NULL 2 +1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +1 PRIMARY alias3 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2 +flush status; +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +(SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 0 +Subquery_cache_miss 0 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 0 +Handler_read_last 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 58 +set @@expensive_subquery_limit= default; +EXPLAIN +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY alias1 ALL NULL NULL NULL NULL 2 +1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +1 PRIMARY alias3 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2 +flush status; +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +(SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 0 +Subquery_cache_miss 0 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 0 +Handler_read_last 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 16 +drop table t1, t2, t3; # return optimizer switch changed in the beginning of this test set optimizer_switch=@subselect_tmp; set optimizer_switch=default;

=== modified file 'mysql-test/r/subselect_no_semijoin.result' --- a/mysql-test/r/subselect_no_semijoin.result 2012-05-18 11:52:01 +0000 +++ b/mysql-test/r/subselect_no_semijoin.result 2012-05-29 21:18:53 +0000 @@ -54,12 +54,12 @@ ERROR 42S22: Reference 'a' not supported EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> system NULL NULL NULL NULL 1 100.00 -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1 Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1 -Note 1003 select 1 AS `1` from dual having (<expr_cache><1>((select 1)) = 1) +Note 1003 select 1 AS `1` from dual having ((select 1) = 1) SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1; 1 1 @@ -1320,7 +1320,7 @@ SELECT 0 IN (SELECT 1 FROM t1 a); EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: Note 1003 select <in_optimizer>(0,<exists>(select 1 from dual where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)` INSERT INTO t1 (pseudo) VALUES ('test1'); @@ -1330,7 +1330,7 @@ SELECT 0 IN (SELECT 1 FROM t1 a); EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: Note 1003 select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)` drop table t1; @@ -3099,7 +3099,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c 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 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition +2 SUBQUERY t2 range b b 40 NULL 2 Using index condition 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; @@ -3111,7 +3111,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c 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 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition +2 SUBQUERY t2 range b b 40 NULL 2 Using index condition 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; @@ -3696,7 +3696,7 @@ ORDER BY t1.t DESC LIMIT 1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 system NULL NULL NULL NULL 1 1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index -2 DEPENDENT SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index +2 SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index SELECT * FROM t1,t2 WHERE t1.t = (SELECT t1.t FROM t1 WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1 @@ -4522,13 +4522,13 @@ SET join_cache_level=0; EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) 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 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary Warnings: Note 1003 select 1 AS `1` from `test`.`t1` where 1 EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 WHERE a > 3 GROUP BY a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary +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 0 SET join_cache_level=@save_join_cache_level; @@ -4575,7 +4575,7 @@ FROM t1 WHERE a = 230; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) FROM t1 WHERE a = 230; @@ -5710,7 +5710,7 @@ WHERE ('0') IN ( SELECT a FROM t1 GROUP GROUP BY b; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where SELECT b FROM t1 WHERE ('0') IN ( SELECT a FROM t1 GROUP BY a ) GROUP BY b; @@ -6525,7 +6525,7 @@ EXPLAIN SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 0 NULL @@ -6533,7 +6533,7 @@ EXPLAIN SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 0 0 @@ -6568,7 +6568,7 @@ WHERE alias1.a = alias2.a OR alias1.a = HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible HAVING -2 DEPENDENT SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where +2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where SELECT MAX( alias2.a ) AS field FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 WHERE alias1.a = alias2.a OR alias1.a = 'y' @@ -6582,7 +6582,7 @@ id select_type table type possible_keys 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 alias3 index NULL a 19 NULL 11 Using index; Using join buffer (flat, BNL join) -2 DEPENDENT SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where +2 SUBQUERY t1 index_subquery a a 19 const 1 Using index; Using where SELECT MAX( alias2.a ) FROM t1 AS alias1, t1 AS alias2, t1 AS alias3 WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 ); @@ -6610,6 +6610,91 @@ SELECT * FROM t1 WHERE a1 = (SELECT COUNT(*) FROM t1 WHERE a1 IN (SELECT a1 FROM t1, t2)); a1 drop table t1, t2; +# +# MDEV-287 CHEAP SQ: A query with subquery in SELECT list, EXISTS, +# inner joins takes hundreds times longer +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(7); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (4),(5); +CREATE TABLE t3 (c INT); +INSERT INTO t3 VALUES (8),(3); +set @@expensive_subquery_limit= 0; +EXPLAIN +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY alias1 ALL NULL NULL NULL NULL 2 +1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +1 PRIMARY alias3 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2 +flush status; +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +(SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 6 +Subquery_cache_miss 2 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 8 +Handler_read_last 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 22 +set @@expensive_subquery_limit= default; +EXPLAIN +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY alias1 ALL NULL NULL NULL NULL 2 +1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +1 PRIMARY alias3 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2 +flush status; +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; +(SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 0 +Subquery_cache_miss 0 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 0 +Handler_read_last 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 16 +drop table t1, t2, t3; # return optimizer switch changed in the beginning of this test set optimizer_switch=@subselect_tmp; set @optimizer_switch_for_subselect_test=null;

=== modified file 'mysql-test/r/subselect_partial_match.result' --- a/mysql-test/r/subselect_partial_match.result 2011-12-04 21:31:42 +0000 +++ b/mysql-test/r/subselect_partial_match.result 2012-05-29 21:18:53 +0000 @@ -879,7 +879,7 @@ EXPLAIN SELECT * FROM t1 WHERE (6, 4 ) NOT IN (SELECT b, a FROM t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where SELECT * FROM t1 WHERE (6, 4 ) NOT IN (SELECT b, a FROM t2); c 0 @@ -888,7 +888,7 @@ EXPLAIN SELECT * FROM t1 WHERE (6, 4 ) NOT IN (SELECT a, b FROM t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where SELECT * FROM t1 WHERE (6, 4 ) NOT IN (SELECT a, b FROM t2); c 0 @@ -913,7 +913,7 @@ set @@optimizer_switch='in_to_exists=on, EXPLAIN SELECT * FROM t2 WHERE ( 3 , 1 ) NOT IN ( SELECT f1 , f2 FROM t1 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 system NULL NULL NULL NULL 1 -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where SELECT * FROM t2 WHERE ( 3 , 1 ) NOT IN ( SELECT f1 , f2 FROM t1 ); f3 5

=== modified file 'mysql-test/r/subselect_sj_mat.result' --- a/mysql-test/r/subselect_sj_mat.result 2012-05-17 10:46:05 +0000 +++ b/mysql-test/r/subselect_sj_mat.result 2012-05-29 21:18:53 +0000 @@ -1198,7 +1198,7 @@ set @@optimizer_switch='materialization= explain select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); min(a1) NULL @@ -1207,7 +1207,7 @@ set @@optimizer_switch='semijoin=off'; explain select min(a1) from t1 where 7 in (select b1 from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table select min(a1) from t1 where 7 in (select b1 from t2); min(a1) NULL @@ -1224,7 +1224,7 @@ NULL explain select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4; min(a1) NULL

=== modified file 'mysql-test/t/subselect.test' --- a/mysql-test/t/subselect.test 2012-05-18 11:52:01 +0000 +++ b/mysql-test/t/subselect.test 2012-05-29 21:18:53 +0000 @@ -5580,5 +5580,49 @@ WHERE a1 = (SELECT COUNT(*) FROM t1 WHER

drop table t1, t2;

+--echo # +--echo # MDEV-287 CHEAP SQ: A query with subquery in SELECT list, EXISTS, +--echo # inner joins takes hundreds times longer +--echo # + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(7); + +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (4),(5); + +CREATE TABLE t3 (c INT); +INSERT INTO t3 VALUES (8),(3); + +set @@expensive_subquery_limit= 0; + +EXPLAIN +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; + +flush status; + +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; + +show status like "subquery_cache%"; +show status like '%Handler_read%'; + +set @@expensive_subquery_limit= default; + +EXPLAIN +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; + +flush status; + +SELECT (SELECT MIN(b) FROM t1, t2 WHERE b = a AND (b = alias1.b OR EXISTS (SELECT * FROM t3))) +FROM t2 alias1, t1 alias2, t1 alias3; + +show status like "subquery_cache%"; +show status like '%Handler_read%'; + +drop table t1, t2, t3; + --echo # return optimizer switch changed in the beginning of this test set optimizer_switch=@subselect_tmp;

=== modified file 'sql/sql_lex.cc' --- a/sql/sql_lex.cc 2012-05-17 10:46:05 +0000 +++ b/sql/sql_lex.cc 2012-05-29 21:18:53 +0000 @@ -3443,6 +3443,7 @@ bool st_select_lex::optimize_unflattened }

bool empty_union_result= true; + bool is_correlated_unit= false; /* If the subquery is a UNION, optimize all the subqueries in the UNION. If there is no UNION, then the loop will execute once for the subquery. @@ -3467,6 +3468,8 @@ bool st_select_lex::optimize_unflattened inner_join->select_options|= SELECT_DESCRIBE; } res= inner_join->optimize(); + sl->update_correlated_cache(); + is_correlated_unit|= sl->is_correlated; inner_join->select_options= save_options; un->thd->lex->current_select= save_select; if (empty_union_result) @@ -3482,6 +3485,9 @@ bool st_select_lex::optimize_unflattened } if (empty_union_result) subquery_predicate->no_rows_in_result(); + if (!is_correlated_unit) + un->uncacheable&= ~UNCACHEABLE_DEPENDENT; + subquery_predicate->is_correlated= is_correlated_unit; } } return FALSE; @@ -3850,6 +3856,61 @@ void SELECT_LEX::update_used_tables() }

+/** + @brief + Update is_correlated cache for this select + + @details +*/ + +void st_select_lex::update_correlated_cache() +{ + TABLE_LIST *tl; + List_iterator<TABLE_LIST> ti(leaf_tables); + + is_correlated= false; + + while ((tl= ti++)) + { + if (tl->on_expr) + is_correlated|= test(tl->on_expr->used_tables() & OUTER_REF_TABLE_BIT); + for (TABLE_LIST *embedding= tl->embedding ; embedding ; + embedding= embedding->embedding) + { + if (embedding->on_expr) + is_correlated|= test(embedding->on_expr->used_tables() & + OUTER_REF_TABLE_BIT); + } + } + + if (join->conds) + is_correlated|= test(join->conds->used_tables() & OUTER_REF_TABLE_BIT); + + if (join->having) + is_correlated|= test(join->having->used_tables() & OUTER_REF_TABLE_BIT); + + if (join->tmp_having) + is_correlated|= test(join->tmp_having->used_tables() & OUTER_REF_TABLE_BIT); + + Item *item; + List_iterator_fast<Item> it(join->fields_list); + while ((item= it++)) + is_correlated|= test(item->used_tables() & OUTER_REF_TABLE_BIT); + + for (ORDER *order= group_list.first; order; order= order->next) + is_correlated|= test((*order->item)->used_tables() & OUTER_REF_TABLE_BIT); + + if (!master_unit()->is_union()) + { + for (ORDER *order= order_list.first; order; order= order->next) + is_correlated|= test((*order->item)->used_tables() & OUTER_REF_TABLE_BIT); + } + + if (!is_correlated) + uncacheable&= ~UNCACHEABLE_DEPENDENT; +} + + /** Set the EXPLAIN type for this subquery. */

=== modified file 'sql/sql_lex.h' --- a/sql/sql_lex.h 2012-05-17 10:46:05 +0000 +++ b/sql/sql_lex.h 2012-05-29 21:18:53 +0000 @@ -1018,6 +1018,7 @@ class st_select_lex: public st_select_le void mark_as_belong_to_derived(TABLE_LIST *derived); void increase_derived_records(ha_rows records); void update_used_tables(); + void update_correlated_cache(); void mark_const_derived(bool empty);

bool save_leaf_tables(THD *thd);

_______________________________________________ commits mailing list commits@mariadb.org https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

-- BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog