bk commit into 5.0 tree (gkodinov:1.2278) BUG#22342

[kgeorge]

Below is the list of changes that have just been committed into a local
5.0 repository of kgeorge. When kgeorge does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2006-09-29 10:01:17+03:00, gkodinov@stripped +4 -0
  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 prefix of these ranges to jump
   over the possible GROUP BY column value combinations.
   Each range contains a notion on whether it includes its border values.
   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@stripped, 2006-09-29 10:01:09+03:00, gkodinov@stripped +12 -0
    Bug #22342: No results returned for query using max and group by
     - test case

  mysql-test/t/group_min_max.test@stripped, 2006-09-29 10:01:10+03:00, gkodinov@stripped +12 -0
    Bug #22342: No results returned for query using max and group by
     - test case

  sql/opt_range.cc@stripped, 2006-09-29 10:01:10+03:00, gkodinov@stripped +37 -0
    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@stripped, 2006-09-29 10:01:11+03:00, gkodinov@stripped +2 -0
    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. 

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	gkodinov
# Host:	macbook.gmz
# Root:	/Users/kgeorge/mysql/work/B22342-5.0-opt

--- 1.225/sql/opt_range.cc	2006-09-29 10:01:35 +03:00
+++ 1.226/sql/opt_range.cc	2006-09-29 10:01:35 +03:00
@@ -8374,6 +8374,7 @@ TRP_GROUP_MIN_MAX::make_quick(PARAM *par
     quick->quick_prefix_select= NULL;
 
   quick->update_key_stat();
+  quick->adjust_prefix_ranges();
 
   DBUG_RETURN(quick);
 }
@@ -8600,6 +8601,42 @@ bool QUICK_GROUP_MIN_MAX_SELECT::add_ran
   if (insert_dynamic(&min_max_ranges, (gptr)&range))
     return TRUE;
   return FALSE;
+}
+
+
+/*
+  Opens the ranges if there are more conditions in quick_prefix_select then
+  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 (just like strings). 
+    However when jumping through the prefixes we use only the the first 
+    few most significant positions in the range "strings". However if there
+    are more "characters" to follow the ones we are using we must make the 
+    condition on the "strings" 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);
+    }
+  }
 }
 
 

--- 1.62/sql/opt_range.h	2006-09-29 10:01:35 +03:00
+++ 1.63/sql/opt_range.h	2006-09-29 10:01:35 +03:00
@@ -294,6 +294,7 @@ protected:
   friend class QUICK_SELECT_DESC;
   friend class QUICK_INDEX_MERGE_SELECT;
   friend class QUICK_ROR_INTERSECT_SELECT;
+  friend class QUICK_GROUP_MIN_MAX_SELECT;
 
   DYNAMIC_ARRAY ranges;     /* ordered array of range ptrs */
   QUICK_RANGE **cur_range;  /* current element in ranges  */
@@ -642,6 +643,7 @@ public:
   ~QUICK_GROUP_MIN_MAX_SELECT();
   bool add_range(SEL_ARG *sel_range);
   void update_key_stat();
+  void adjust_prefix_ranges();
   bool alloc_buffers();
   int init();
   int reset();

--- 1.25/mysql-test/r/group_min_max.result	2006-09-29 10:01:35 +03:00
+++ 1.26/mysql-test/r/group_min_max.result	2006-09-29 10:01:35 +03:00
@@ -2142,3 +2142,15 @@ t1;
 id2	id3	id5	id4	id3	id6	id5	id1
 1	1	1	1	1	1	1	1
 DROP TABLE t1,t2,t3,t4,t5,t6;
+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;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY,b	PRIMARY	8	NULL	2	Using where; Using index for group-by
+SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
+MAX(b)	a
+1	1
+SELECT MIN(b), a FROM t1 WHERE b > 1 AND a = 1 GROUP BY a;
+MIN(b)	a
+2	1
+DROP TABLE t1;

--- 1.23/mysql-test/t/group_min_max.test	2006-09-29 10:01:35 +03:00
+++ 1.24/mysql-test/t/group_min_max.test	2006-09-29 10:01:35 +03:00
@@ -794,3 +794,15 @@ 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;
+
+DROP TABLE t1;