MWL#89

Fixed query plans with loose index scan degraded into index scan.

Analysis:
With MWL#89 subqueries are no longer executed and substituted during
the optimization of the outer query. As a result subquery predicates
that were previously executed and substituted by a constant before
the range optimizer were present as regular subquery predicates during
range optimization. The procedure check_group_min_max_predicates()
had a naive test that ruled out all queries with subqueries in the
WHERE clause. This resulted in worse plans with MWL#89.

Solution:
The solution is to refine the test in check_group_min_max_predicates()
to check if each MIN/MAX argument is referred to by a subquery predicate.

1 files changed, 50 insertions, 0 deletions

diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test
index 8ab7e1c9cb4..1ba06bee942 100644
--- a/mysql-test/t/group_min_max.test
+++ b/mysql-test/t/group_min_max.test
@@ -406,11 +406,61 @@ explain select a1,a2,b,min(c),max(c) from t1
 where exists ( select * from t2 where t2.c = t1.c )
 group by a1,a2,b;
 
+select a1,a2,b,min(c),max(c) from t1
+where exists ( select * from t2 where t2.c = t1.c )
+group by a1,a2,b;
+
 # the sub-select is unrelated to MIN/MAX
 explain select a1,a2,b,min(c),max(c) from t1
 where exists ( select * from t2 where t2.c > 'b1' )
 group by a1,a2,b;
 
+select a1,a2,b,min(c),max(c) from t1
+where exists ( select * from t2 where t2.c > 'b1' )
+group by a1,a2,b;
+
+# correlated subselect that doesn't reference the min/max argument
+explain select a1,a2,b,c,min(c), max(c) from t1
+where exists ( select * from t2 where t1.b > 'a' and t2.c > 'b1' )
+group by a1,a2,b;
+
+select a1,a2,b,c,min(c), max(c) from t1
+where exists ( select * from t2 where t1.b > 'a' and t2.c > 'b1' )
+group by a1,a2,b;
+
+explain select a1,a2,b,c,min(c), max(c) from t1
+where exists ( select * from t2
+               where t2.c in (select c from t3 where t3.c > t1.b) and
+               t2.c > 'b1' )
+group by a1,a2,b;
+
+select a1,a2,b,c,min(c), max(c) from t1
+where exists ( select * from t2
+               where t2.c in (select c from t3 where t3.c > t1.b) and
+               t2.c > 'b1' )
+group by a1,a2,b;
+
+# correlated subselect that references the min/max argument
+explain select a1,a2,b,c,min(c), max(c) from t1
+where exists ( select * from t2 where t1.c > 'a' and t2.c > 'b1' )
+group by a1,a2,b;
+
+select a1,a2,b,c,min(c), max(c) from t1
+where exists ( select * from t2 where t1.c > 'a' and t2.c > 'b1' )
+group by a1,a2,b;
+
+explain select a1,a2,b,c,min(c), max(c) from t1
+where exists ( select * from t2
+               where t2.c in (select c from t3 where t3.c > t1.c) and
+               t2.c > 'b1' )
+group by a1,a2,b;
+
+select a1,a2,b,c,min(c), max(c) from t1
+where exists ( select * from t2
+               where t2.c in (select c from t3 where t3.c > t1.c) and
+               t2.c > 'b1' )
+group by a1,a2,b;
+
 
 # A,B,C) Predicates referencing mixed classes of attributes
 # plans