bzr commit into mysql-5.0-bugteam branch (Alexey.Kopytov:2843) Bug#48472

[Alexey Kopytov]

#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/[e-mail]