[MDEV-3687] LP:813418 - Wrong result with aggregate + NOT BETWEEN + key Created: 2011-07-20  Updated: 2012-10-04  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Philip Stoev (Inactive) Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug813418.xml    

 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;

 Comments   
Comment by Timour Katchaounov (Inactive) [ 2011-08-23 ]

Re: Wrong result with aggregate + NOT BETWEEN + key
Assigning to Sanja, because he already dealt with some MIN/MAX
optimization bug recently.

Comment by Oleksandr Byelkin [ 2011-09-07 ]

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).

Comment by Oleksandr Byelkin [ 2011-09-07 ]

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...

Comment by Oleksandr Byelkin [ 2011-09-08 ]

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).

Comment by Oleksandr Byelkin [ 2011-09-08 ]

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:

Comment by Rasmus Johansson (Inactive) [ 2011-12-13 ]

Launchpad bug id: 813418

Generated at Thu Feb 08 06:50:28 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.