Wrong result with aggregate + NOT BETWEEN + key

[Philip Stoev]

Repeatable in mysql-5.1,5.5, maria 5.1, 5.2, 5.5. The following query:

SELECT MAX(a) FROM t1 WHERE a NOT BETWEEN 3 AND 9;

returns NULL even though there are obviously rows that match the WHERE predicate.

explain:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No matching min/max row

test case:

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;

[Timour Katchaounov (timour)]

Assigning to Sanja, because he already dealt with some MIN/MAX
optimization bug recently.

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

The problem is that when optimizer trying to calculate maximum by the index (opt_sum_query) it gets 9 (right value is 10).

The problem is not engine related (myisam/aria/innodb return the same result).

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

The problem is that get_index_max_value has ref->key_length set and its looks not for maximum of the all index but for maximum with some prefix...

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

The problem is in matching_cond, the code

      /* Update endpoints for MAX/MIN, see function comment. */
      Item *value= args[between && max_fl ? 2 : 1];

does not takes into account that BETWEEN could be negated (Item_func_between inherited from Item_func_opt_neg).

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

It is possible just switch off optimization for negated BETWEEN:

=== modified file 'sql/opt_sum.cc'
--- sql/opt_sum.cc 2011-05-03 16:10:10 +0000
+++ sql/opt_sum.cc 2011-09-08 00:47:11 +0000
@@ -657,6 +657,8 @@ static bool matching_cond(bool max_fl, T
   case Item_func::GE_FUNC:
     break;
   case Item_func::BETWEEN:
+ if (((Item_func_between*) cond)->negated)
+ DBUG_RETURN(FALSE);
     between= 1;
     break;
   case Item_func::MULT_EQUAL_FUNC: