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-26 10:36:49+03:00, gkodinov@stripped +4 -0
  Bug #22342: No results returned for query using max and group by
   When jumping to the first record after prefix the optimizer can add
   the range conditions over the arguments of the MIN/MAX functions to
   the range conditions to the GROUP BY fields.
   In doing so it was not opening the appropriate end of the range and
   was causing impossible ranges during the prefix scan, e.g. :
   (<const>) <= (<group by field>,<max_arg>) <
(<const>,<max_const>)
   was trimmed to the impossible (<const>) <= (<group by field>) <
(<const>) 
   while searching for the first/next prefix to group on.
  
   This fix opens the intervals, so the above range actually transforms
   to (<const>) <= (<group by field>) <= (<const>) instead

  mysql-test/r/group_min_max.result@stripped, 2006-09-26 10:36:41+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-26 10:36:41+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-26 10:36:42+03:00, gkodinov@stripped +35 -0
    Bug #22342: No results returned for query using max and group by
     - open the intervals for prefix select when adding the conditions over 
       the MIN/MAX arguments 

  sql/opt_range.h@stripped, 2006-09-26 10:36:43+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 adding the conditions over 
       the MIN/MAX arguments 

# 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-26 10:37:07 +03:00
+++ 1.226/sql/opt_range.cc	2006-09-26 10:37:07 +03:00
@@ -8604,6 +8604,35 @@ bool QUICK_GROUP_MIN_MAX_SELECT::add_ran
 
 
 /*
+  Turn off certain flags for the ranges descriptors.
+
+  SYNOPSIS
+    QUICK_GROUP_MIN_MAX_SELECT::update_ranges_flag()
+    off_flags  flags to turn off 
+
+  NOTES
+    If we are adding the conditions over the MIN/MAX arguments to the quick 
+    select used to find the GROUP BY prefixes we need to open the intervals 
+    at the appropriate ends so as to handle intervals like e.g. 
+    (1) <= (a,b) < (1,3).
+    In order to do that we need to be able turn off the NEAR_MIN/NEAR_MAX
+    flags for all the range elements.
+*/
+void QUICK_GROUP_MIN_MAX_SELECT::update_ranges_flag (uint16 off_flags)
+{
+  QUICK_RANGE *range;
+  DYNAMIC_ARRAY *arr;
+  uint inx;
+
+  for (inx= 0, arr= &quick_prefix_select->ranges; inx < arr->elements;
inx++)
+  {
+    get_dynamic(arr, (gptr)&range, inx);
+    range->flag &= ~off_flags;
+  }
+}
+
+
+/*
   Determine the total number and length of the keys that will be used for
   index lookup.
 
@@ -8638,6 +8667,9 @@ void QUICK_GROUP_MIN_MAX_SELECT::update_
       {
         max_used_key_length+= min_max_arg_len;
         used_key_parts++;
+        /* open the interval because we'll need to find the prefix */
+        if (quick_prefix_select)
+          update_ranges_flag (NEAR_MIN);
         return;
       }
     }
@@ -8648,6 +8680,9 @@ void QUICK_GROUP_MIN_MAX_SELECT::update_
       {
         max_used_key_length+= min_max_arg_len;
         used_key_parts++;
+        /* open the interval because we'll need to find the prefix */
+        if (quick_prefix_select)
+          update_ranges_flag (NEAR_MAX);
         return;
       }
     }

--- 1.62/sql/opt_range.h	2006-09-26 10:37:07 +03:00
+++ 1.63/sql/opt_range.h	2006-09-26 10:37:07 +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  */
@@ -632,6 +633,7 @@ private:
   int  next_max();
   void update_min_result();
   void update_max_result();
+  void update_ranges_flag(uint16 off_flags);
 public:
   QUICK_GROUP_MIN_MAX_SELECT(TABLE *table, JOIN *join, bool have_min,
                              bool have_max, KEY_PART_INFO *min_max_arg_part,

--- 1.25/mysql-test/r/group_min_max.result	2006-09-26 10:37:07 +03:00
+++ 1.26/mysql-test/r/group_min_max.result	2006-09-26 10:37:07 +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-26 10:37:07 +03:00
+++ 1.24/mysql-test/t/group_min_max.test	2006-09-26 10:37:07 +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;