From 3e82a86ed2dea416c3262a912fb2f323b35a3dc4 Mon Sep 17 00:00:00 2001
From: Georgi Kodinov <joro@sun.com>
Date: Fri, 12 Jun 2009 15:38:55 +0300
Subject: 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
---
 mysql-test/r/group_min_max.result | 39 +++++++++++++++++++++++++++++++++++++++
 1 file changed, 39 insertions(+)

(limited to 'mysql-test/r')

diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result
index 58be2af1bb8..1138a5e1d88 100644
--- a/mysql-test/r/group_min_max.result
+++ b/mysql-test/r/group_min_max.result
@@ -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
-- 
cgit v1.2.1