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, 37 insertions, 0 deletions

diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 93566dbc281..ad0abb798d1 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -8374,6 +8374,7 @@ TRP_GROUP_MIN_MAX::make_quick(PARAM *param, bool retrieve_full_rows,
     quick->quick_prefix_select= NULL;
 
   quick->update_key_stat();
+  quick->adjust_prefix_ranges();
 
   DBUG_RETURN(quick);
 }
@@ -8604,6 +8605,42 @@ bool QUICK_GROUP_MIN_MAX_SELECT::add_range(SEL_ARG *sel_range)
 
 
 /*
+  Opens the ranges if there are more conditions in quick_prefix_select than
+  the ones used for jumping through the prefixes.
+
+  SYNOPSIS
+    QUICK_GROUP_MIN_MAX_SELECT::adjust_prefix_ranges()
+
+  NOTES
+    quick_prefix_select is made over the conditions on the whole key.
+    It defines a number of ranges of length x. 
+    However when jumping through the prefixes we use only the the first 
+    few most significant keyparts in the range key. However if there
+    are more keyparts to follow the ones we are using we must make the 
+    condition on the key inclusive (because x < "ab" means 
+    x[0] < 'a' OR (x[0] == 'a' AND x[1] < 'b').
+    To achive the above we must turn off the NEAR_MIN/NEAR_MAX
+*/
+void QUICK_GROUP_MIN_MAX_SELECT::adjust_prefix_ranges ()
+{
+  if (quick_prefix_select &&
+      group_prefix_len < quick_prefix_select->max_used_key_length)
+  {
+    DYNAMIC_ARRAY *arr;
+    uint inx;
+
+    for (inx= 0, arr= &quick_prefix_select->ranges; inx < arr->elements; inx++)
+    {
+      QUICK_RANGE *range;
+
+      get_dynamic(arr, (gptr)&range, inx);
+      range->flag &= ~(NEAR_MIN | NEAR_MAX);
+    }
+  }
+}
+
+
+/*
   Determine the total number and length of the keys that will be used for
   index lookup.