Bug #22342: No results returned for query using max and group by

 When using index for group by and range access the server isolates    
 a set of ranges based on the conditions over the key parts of the
 index used. Then it uses only the ranges over the GROUP BY fields to
 jump from one group to another. Since the GROUP BY fields may form a
 prefix over the index, we may use only a prefix of the ranges produced
 by the range optimizer.
 Each range contains a notion on whether it includes its border values.
 The problem is that when using a range prefix, the last range is open
 because it assumes that there is a range on the next keypart. Thus when
 we use a prefix range as it is, it excludes all border values.
 The solution is when ignoring the suffix of the range conditions 
 (to jump over the GROUP BY prefix only) the server must change the 
 remaining intervals so they always contain their borders, e.g. 
 if the whole range was :
 (1,-inf) <= (<group_by_col>,<min_max_arg_col>) < (1, 3) we must make
 (1) <= (<group_by_col>) <= (1) because (a,b) < (c1,c2) means :
 a < c1 OR (a = c1 AND b < c2).


mysql-test/r/group_min_max.result:
  Bug #22342: No results returned for query using max and group by
   - test case
mysql-test/t/group_min_max.test:
  Bug #22342: No results returned for query using max and group by
   - test case
sql/opt_range.cc:
  Bug #22342: No results returned for query using max and group by
   - open the intervals for prefix select when there are more conditions
     than used for the prefix search.
sql/opt_range.h:
  Bug #22342: No results returned for query using max and group by
   - open the intervals for prefix select when there are more conditions
     than used for the prefix search.

1 files changed, 16 insertions, 0 deletions

diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test
index 5427727a8f4..9f3fb5ea51e 100644
--- a/mysql-test/t/group_min_max.test
+++ b/mysql-test/t/group_min_max.test
@@ -794,3 +794,19 @@ SELECT * FROM
 t1;
 
 DROP TABLE t1,t2,t3,t4,t5,t6;
+
+#
+# Bug#22342: No results returned for query using max and group by
+#
+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b));
+INSERT INTO t1 VALUES (1,1),(1,2);
+
+explain SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
+SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
+SELECT MIN(b), a FROM t1 WHERE b > 1 AND a = 1 GROUP BY a;
+CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a,b,c));
+INSERT INTO t2 SELECT a,b,b FROM t1;
+explain SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
+SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
+
+DROP TABLE t1,t2;