Fix for MDEV-4140

Analysis:
Range analysis detects that the subquery is expensive and doesn't
build a range access method. Later, the applicability test for loose
scan doesn't take that into account, and builds a loose scan method
without a range scan on the min/max column. As a result loose scan
fetches the first key in each group, rather than the first key that
satisfies the condition on the min/max column.

Solution:
Since there is no SEL_ARG tree to be used for the min/max column,
it is not possible to use loose scan if the min/max column is compared
with an expensive scalar subquery. Make the test for loose scan
applicability to be in sync with the range analysis code by testing if
the min/max argument is compared with an expensive predicate.

3 files changed, 59 insertions, 1 deletions

diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result
index 7c9ff6d9fa1..534e85038fe 100644
--- a/mysql-test/r/group_min_max.result
+++ b/mysql-test/r/group_min_max.result
@@ -3243,4 +3243,40 @@ d	4
 f	7
 g	8
 drop table t1;
+#
+# MDEV-4140 Wrong result with GROUP BY + multipart key + MIN/MAX loose scan and a subquery
+#
+CREATE TABLE t1 (a int, b int, KEY (b, a)) ;
+INSERT INTO t1 VALUES (0,99),(9,99),(4,0),(7,0),(99,0),(7,0),(8,0),(99,0),(1,0);
+CREATE TABLE t2 (c int) ;
+INSERT INTO t2 VALUES (0),(1);
+EXPLAIN
+SELECT MIN(a), b FROM t1 WHERE a > 0 GROUP BY b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	NULL	b	10	NULL	10	Using where; Using index for group-by
+SELECT MIN(a), b FROM t1 WHERE a > 0 GROUP BY b;
+MIN(a)	b
+1	0
+9	99
+EXPLAIN
+SELECT MIN(a), b FROM t1 WHERE a > ( SELECT c FROM t2 WHERE c = 0 ) GROUP BY b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	b	10	NULL	9	Using where; Using index
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT MIN(a), b FROM t1 WHERE a > ( SELECT c FROM t2 WHERE c = 0 ) GROUP BY b;
+MIN(a)	b
+1	0
+9	99
+EXPLAIN
+SELECT MIN(a), b FROM t1 WHERE a > ( SELECT min(c) FROM t2, t1 t1a, t1 t1b WHERE c = 0 ) GROUP BY b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	b	10	NULL	9	Using where; Using index
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	SUBQUERY	t1a	index	NULL	b	10	NULL	9	Using index; Using join buffer (flat, BNL join)
+2	SUBQUERY	t1b	index	NULL	b	10	NULL	9	Using index; Using join buffer (incremental, BNL join)
+SELECT MIN(a), b FROM t1 WHERE a > ( SELECT min(c) FROM t2, t1 t1a, t1 t1b WHERE c = 0 ) GROUP BY b;
+MIN(a)	b
+1	0
+9	99
+drop table t1, t2;
 End of 5.3 tests
diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test
index d081ad6e09b..28d375f7dda 100644
--- a/mysql-test/t/group_min_max.test
+++ b/mysql-test/t/group_min_max.test
@@ -1239,4 +1239,26 @@ SELECT b, min(a) FROM t1 WHERE (a > '0' AND (a > '1' OR b = 'd')) GROUP BY b;
 
 drop table t1;
 
+--echo #
+--echo # MDEV-4140 Wrong result with GROUP BY + multipart key + MIN/MAX loose scan and a subquery
+--echo #
+
+CREATE TABLE t1 (a int, b int, KEY (b, a)) ;
+INSERT INTO t1 VALUES (0,99),(9,99),(4,0),(7,0),(99,0),(7,0),(8,0),(99,0),(1,0);
+CREATE TABLE t2 (c int) ;
+INSERT INTO t2 VALUES (0),(1);
+
+EXPLAIN
+SELECT MIN(a), b FROM t1 WHERE a > 0 GROUP BY b;
+SELECT MIN(a), b FROM t1 WHERE a > 0 GROUP BY b;
+EXPLAIN
+SELECT MIN(a), b FROM t1 WHERE a > ( SELECT c FROM t2 WHERE c = 0 ) GROUP BY b;
+SELECT MIN(a), b FROM t1 WHERE a > ( SELECT c FROM t2 WHERE c = 0 ) GROUP BY b;
+# this test is for 5.5 to ensure that the subquery is expensive
+EXPLAIN
+SELECT MIN(a), b FROM t1 WHERE a > ( SELECT min(c) FROM t2, t1 t1a, t1 t1b WHERE c = 0 ) GROUP BY b;
+SELECT MIN(a), b FROM t1 WHERE a > ( SELECT min(c) FROM t2, t1 t1a, t1 t1b WHERE c = 0 ) GROUP BY b;
+
+drop table t1, t2;
+
 --echo End of 5.3 tests
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 7a6c390f210..29f3945d163 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -12236,7 +12236,7 @@ check_group_min_max_predicates(Item *cond, Item_field *min_max_arg_item,
                                           &has_min_max, &has_other))
         DBUG_RETURN(FALSE);
     }
-    else if (cur_arg->const_item())
+    else if (cur_arg->const_item() && !cur_arg->is_expensive())
     {
       /*
         For predicates of the form "const OP expr" we also have to check 'expr'