#At file:///data/src/bzr/bugteam/bug48472/my50-bug48472/ based on revid:alexey.kopytov@stripped
2843 Alexey Kopytov 2009-11-17
Bug #48472: Loose index scan inappropriately chosen for some
WHERE conditions
check_group_min_max() checks if the loose index scan
optimization is applicable for a given WHERE condition, that is
if the MIN/MAX attribute participates only in range predicates
comparing the corresponding field with constants.
The problem was that it considered the whole predicate suitable
for the loose index scan optimization as soon as it encountered
a constant as a predicate argument. This is obviously wrong for
cases when a constant is the first argument of a predicate
which does not satisfy the above condition.
Fixed check_group_min_max() so that all arguments of the input
predicate are considered to decide if it passes the test, even
though a constant has already been encountered.
@ mysql-test/r/group_min_max.result
Added a test case for bug #48472.
@ mysql-test/t/group_min_max.test
Added a test case for bug #48472.
@ sql/opt_range.cc
Fixed check_group_min_max() so that all arguments of the input
predicate are considered to decide if it passes the test, even
though a constant has already been encountered.
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-07-13 15:11:16 +0000
+++ b/mysql-test/r/group_min_max.result 2009-11-17 14:07:14 +0000
@@ -2480,4 +2480,15 @@ SELECT a, MAX(b) FROM t WHERE b > 0 AND
a MAX(b)
2 1
DROP TABLE t;
+#
+# Bug #48472: Loose index scan inappropriately chosen for some WHERE
+# conditions
+#
+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;
+SELECT a, MAX(b) FROM t WHERE 0=b+0 GROUP BY a;
+a MAX(b)
+2 0
+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-07-13 15:11:16 +0000
+++ b/mysql-test/t/group_min_max.test 2009-11-17 14:07:14 +0000
@@ -991,5 +991,17 @@ SELECT a, MAX(b) FROM t WHERE b > 0 AND
DROP TABLE t;
+--echo #
+--echo # Bug #48472: Loose index scan inappropriately chosen for some WHERE
+--echo # conditions
+--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;
+
+SELECT a, MAX(b) FROM t WHERE 0=b+0 GROUP BY a;
+
+DROP TABLE t;
--echo End of 5.0 tests
=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc 2009-10-23 11:26:48 +0000
+++ b/sql/opt_range.cc 2009-11-17 14:07:14 +0000
@@ -8244,7 +8244,11 @@ check_group_min_max_predicates(COND *con
}
else if (cur_arg->const_item())
{
- DBUG_RETURN(TRUE);
+ /*
+ For predicates of the form "const OP expr" we also have to check 'expr'
+ to make a decision.
+ */
+ continue;
}
else
DBUG_RETURN(FALSE);
Attachment: [text/bzr-bundle] bzr/alexey.kopytov@sun.com-20091117140714-mnqa2zv7l4rwoq9y.bundle
Thread |
---|
• bzr commit into mysql-5.0-bugteam branch (Alexey.Kopytov:2843)Bug#48472 | Alexey Kopytov | 17 Nov |