maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #04756
Re: 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@xxxxxxxxxxxx-20120529211853-hww47vl7d4u4ae23
> parent: timour@xxxxxxxxxxxx-20120524110828-r0mm8sm1vn8a095e
> committer: timour@xxxxxxxxxxxx
> 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@xxxxxxxxxxx
> 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
