From f1ab00891ad050711557c6cdc62b17fff896aed9 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 5 Jun 2012 17:25:10 +0300 Subject: Fixed bug lp:1000649 Analysis: When the method JOIN::choose_subquery_plan() decided to apply the IN-TO-EXISTS strategy, it set the unit and select_lex uncacheable flag to UNCACHEABLE_DEPENDENT_INJECTED unconditionally. As result, even if IN-TO-EXISTS injected non-correlated predicates, the subquery was still treated as correlated. Solution: Set the subquery as correlated only if the injected predicate(s) depend on the outer query. --- mysql-test/r/subselect_no_mat.result | 10 +++++----- 1 file changed, 5 insertions(+), 5 deletions(-) (limited to 'mysql-test/r/subselect_no_mat.result') diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 7efda485e18..671a5df213b 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -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 (0,(select 1 from `test`.`t1` `a` where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)` INSERT INTO t1 (pseudo) VALUES ('test1'); @@ -1337,7 +1337,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 (0,(select 1 from `test`.`t1` `a` where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)` drop table t1; @@ -4476,13 +4476,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>((1,(select min(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`a` having (1 = (min(`test`.`t1`.`a`)))))) 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 t1 ALL NULL NULL NULL NULL 2 100.00 -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary +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 <1>((1,(select min(`test`.`t1`.`a`) from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` having (1 = (min(`test`.`t1`.`a`)))))) SET join_cache_level=@save_join_cache_level; @@ -5318,7 +5318,7 @@ WHERE ('0') IN ( SELECT a FROM t1 GROUP BY a ) 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; -- cgit v1.2.1