Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
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;
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Wrong result with aggregate + NOT BETWEEN + key
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).
Re: Wrong result with aggregate + NOT BETWEEN + key
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...
Re: Wrong result with aggregate + NOT BETWEEN + key
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).
Re: Wrong result with aggregate + NOT BETWEEN + key
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:
Launchpad bug id: 813418
Re: Wrong result with aggregate + NOT BETWEEN + key
Assigning to Sanja, because he already dealt with some MIN/MAX
optimization bug recently.