From: Jorgen Loland
Date: December 22 2011 1:36pm
Subject: bzr push into mysql-trunk branch (jorgen.loland:3678 to 3679) Bug#12773464
List-Archive: http://lists.mysql.com/commits/142239
X-Bug: 12773464
Message-Id: <20111222133627.8D81CC9@atum21.no.oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

 3679 Jorgen Loland	2011-12-22
      Bug#12773464 - 61925: WRONG RESULT WITH AGGREGATE + 
                     NOT BETWEEN + KEY
      
      The problem: opt_sum_query() was invoked to replace MAX(a) with
      a constant in the query:
         SELECT MAX(a) FROM t1 WHERE a NOT BETWEEN 3 AND 9;
      
      However, opt_sum_query() mistook NOT BETWEEN for a BETWEEN
      and therefore found the highest values lower than or equal to 9
      and then tested the predicate on this value. The predicate 
      obviously evaluated to false and MAX(a) therefore evaluated to
      NULL.
      
      opt_sum_query() can only handle conjunctions ("AND" predicates)
      but "x NOT BETWEEN a AND b" is equivalent to "x < a OR x > b" 
      and is therefore not a conjunction. The fix is to return
      from the function without doing const replacement for 
      NOT BETWEENs.
     @ mysql-test/r/func_group.result
        Add test for Bug#12773464
     @ mysql-test/t/func_group.test
        Add test for Bug#12773464
     @ sql/opt_sum.cc
        opt_sum_query() can only handle conjunctions, so const 
        substitution cannot happen when there is a NOT BETWEEN predicate

    modified:
      mysql-test/r/func_group.result
      mysql-test/t/func_group.test
      sql/opt_sum.cc
 3678 Sneha Modi	2011-12-22 [merge]
      Bug#11754150:  A TEST CASE FOR BUG#6857 IS DISABLED IN SP.TEST
      
      merging from mysql-5.5 -> mysql-trunk

    modified:
      mysql-test/r/sp.result
      mysql-test/t/sp.test
=== modified file 'mysql-test/r/func_group.result'
--- a/mysql-test/r/func_group.result	2011-08-03 11:29:20 +0000
+++ b/mysql-test/r/func_group.result	2011-12-22 13:36:08 +0000
@@ -1804,3 +1804,12 @@ AVG(DISTINCT outr.col_int_nokey)
 7.5000
 DROP TABLE t1;
 # End of the bug#57932
+#
+# BUG#12773464 - 61925: WRONG RESULT WITH AGGREGATE + NOT BETWEEN + KEY
+#
+CREATE TABLE t1 (a int, KEY (a));
+INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
+SELECT MAX(a) FROM t1 WHERE a NOT BETWEEN 3 AND 9;
+MAX(a)
+10
+DROP TABLE t1;

=== modified file 'mysql-test/t/func_group.test'
--- a/mysql-test/t/func_group.test	2011-04-12 10:31:30 +0000
+++ b/mysql-test/t/func_group.test	2011-12-22 13:36:08 +0000
@@ -1184,3 +1184,12 @@ SELECT AVG(DISTINCT outr.col_int_nokey) 
 outr.col_int_nokey = outr2.col_int_nokey;
 DROP TABLE t1;
 --echo # End of the bug#57932
+
+--echo #
+--echo # BUG#12773464 - 61925: WRONG RESULT WITH AGGREGATE + NOT BETWEEN + KEY
+--echo #
+CREATE TABLE t1 (a int, KEY (a));
+INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
+SELECT MAX(a) FROM t1 WHERE a NOT BETWEEN 3 AND 9;
+
+DROP TABLE t1;

=== modified file 'sql/opt_sum.cc'
--- a/sql/opt_sum.cc	2011-07-21 17:51:38 +0000
+++ b/sql/opt_sum.cc	2011-12-22 13:36:08 +0000
@@ -674,6 +674,11 @@ static bool matching_cond(bool max_fl, T
     break;
   case Item_func::BETWEEN:
     between= 1;
+
+    // NOT BETWEEN is equivalent to OR and is therefore not a conjunction
+    if (((Item_func_between*)cond)->negated)
+      DBUG_RETURN(false);
+
     break;
   case Item_func::MULT_EQUAL_FUNC:
     eq_type= 1;

No bundle (reason: useless for push emails).