bzr commit into mysql-5.0-bugteam branch (joro:2770) Bug#45386

[Georgi Kodinov]

#At file:///Users/kgeorge/mysql/work/B45386-5.0-bugteam/ based on revid:davi.arnaut@stripped

 2770 Georgi Kodinov	2009-06-12
      Bug #45386: Wrong query result with MIN function in field list, 
      WHERE and GROUP BY clause
      
      Loose index scan may use range conditions on the argument of 
      the MIN/MAX aggregate functions to find the beginning/end of 
      the interval that satisfies the range conditions in a single go.
      These range conditions may have open or closed minimum/maximum 
      values. When the comparison returns 0 (equal) the code should 
      check the type of the min/max values of the current interval 
      and accept or reject the row based on whether the limit is 
      open or not.
      There was a wrong composite condition on checking this and it was
      not working in all cases.
      Fixed by simplifying the conditions and reversing the logic.
     @ mysql-test/r/group_min_max.result
        Bug #45386: test case
     @ mysql-test/t/group_min_max.test
        Bug #45386: test case
     @ sql/opt_range.cc
        Bug #45386: fix the check whether to use the value if on the
        interval boundry

    modified:
      mysql-test/r/group_min_max.result
      mysql-test/t/group_min_max.test
      sql/opt_range.cc
=== modified file 'mysql-test/r/group_min_max.result'
--- a/mysql-test/r/group_min_max.result	2009-02-27 13:25:06 +0000
+++ b/mysql-test/r/group_min_max.result	2009-06-12 12:38:55 +0000
@@ -2443,4 +2443,43 @@ c
 1
 2
 DROP TABLE t1;
+#
+# Bug #45386: Wrong query result with MIN function in field list, 
+#  WHERE and GROUP BY clause
+#
+CREATE TABLE t (a INT, b INT, INDEX (a,b));
+INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1);
+INSERT INTO t SELECT * FROM t;
+# test MIN
+#should use range with index for group by
+EXPLAIN
+SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t	range	NULL	a	10	NULL	9	Using where; Using index for group-by
+#should return 1 row
+SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a;
+a	MIN(b)
+2	1
+# test MAX
+#should use range with index for group by
+EXPLAIN
+SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t	range	NULL	a	10	NULL	9	Using where; Using index for group-by
+#should return 1 row
+SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a;
+a	MAX(b)
+2	0
+# test 3 ranges and use the middle one
+INSERT INTO t SELECT a, 2 FROM t;
+#should use range with index for group by
+EXPLAIN
+SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t	range	NULL	a	10	NULL	9	Using where; Using index for group-by
+#should return 1 row
+SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a;
+a	MAX(b)
+2	1
+DROP TABLE t;
 End of 5.0 tests

=== modified file 'mysql-test/t/group_min_max.test'
--- a/mysql-test/t/group_min_max.test	2009-02-27 13:25:06 +0000
+++ b/mysql-test/t/group_min_max.test	2009-06-12 12:38:55 +0000
@@ -957,4 +957,39 @@ SELECT DISTINCT c FROM t1 WHERE d=4;
 
 DROP TABLE t1;
 
+--echo #
+--echo # Bug #45386: Wrong query result with MIN function in field list, 
+--echo #  WHERE and GROUP BY clause
+--echo #
+
+CREATE TABLE t (a INT, b INT, INDEX (a,b));
+INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1);
+INSERT INTO t SELECT * FROM t;
+
+--echo # test MIN
+--echo #should use range with index for group by
+EXPLAIN
+SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a;
+--echo #should return 1 row
+SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a;
+
+--echo # test MAX
+--echo #should use range with index for group by
+EXPLAIN
+SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a;
+--echo #should return 1 row
+SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a;
+
+--echo # test 3 ranges and use the middle one
+INSERT INTO t SELECT a, 2 FROM t;
+
+--echo #should use range with index for group by
+EXPLAIN
+SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a;
+--echo #should return 1 row
+SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a;
+
+DROP TABLE t;
+
+
 --echo End of 5.0 tests

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2009-06-09 16:11:21 +0000
+++ b/sql/opt_range.cc	2009-06-12 12:38:55 +0000
@@ -9395,8 +9395,14 @@ int QUICK_GROUP_MIN_MAX_SELECT::next_min
       /* Compare the found key with max_key. */
       int cmp_res= key_cmp(index_info->key_part, max_key,
                            real_prefix_len + min_max_arg_len);
-      if (!(((cur_range->flag & NEAR_MAX) && (cmp_res == -1)) ||
-            (cmp_res <= 0)))
+      /*
+        The key is outside of the range if: 
+        the interval is open and the key is equal to the maximum boundry
+        or
+        the key is greater than the maximum
+      */
+      if (((cur_range->flag & NEAR_MAX) && cmp_res == 0) ||
+          cmp_res > 0)
       {
         result = HA_ERR_KEY_NOT_FOUND;
         continue;
@@ -9511,8 +9517,14 @@ int QUICK_GROUP_MIN_MAX_SELECT::next_max
       /* Compare the found key with min_key. */
       int cmp_res= key_cmp(index_info->key_part, min_key,
                            real_prefix_len + min_max_arg_len);
-      if (!(((cur_range->flag & NEAR_MIN) && (cmp_res == 1)) ||
-            (cmp_res >= 0)))
+      /*
+        The key is outside of the range if: 
+        the interval is open and the key is equal to the minimum boundry
+        or
+        the key is less than the minimum
+      */
+      if (((cur_range->flag & NEAR_MIN) && cmp_res == 0) ||
+          cmp_res < 0)
         continue;
     }
     /* If we got to this point, the current key qualifies as MAX. */


Attachment: [text/bzr-bundle] bzr/[e-mail]