Wrong result with GROUP BY + multipart key + MIN/MAX loose scan

Bug #825075 reported by Philip Stoev
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MariaDB
Confirmed
High
Timour Katchaounov

Bug Description

The following query:

SELECT MIN(a), b
FROM t1
WHERE a > ( SELECT a FROM t2 WHERE a = 0 )
GROUP BY b;

does not return as many rows as if the subquery is replaced with a constant.

explain:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range NULL b 10 NULL 10 Using where; Using index for group-by
2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where

optimizer switch:

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

test case:

CREATE TABLE t1 (a int, b int, KEY (b, a)) ;
INSERT INTO t1 VALUES (0,99),(9,99),(4,0),(7,0),(99,0),(7,0),(8,0),(99,0),(1,0);
CREATE TABLE t2 (a int) ;
INSERT INTO t2 VALUES (0),(1);
SELECT MIN(a), b
FROM t1
WHERE a > ( SELECT a FROM t2 WHERE a = 0 )
GROUP BY b;

returns:

MIN(a) b
1 0

SELECT MIN(a), b
FROM t1
WHERE a > 0
GROUP BY b;

returns:

MIN(a) b
1 0
9 99

bzr version-info:

revision-id: <email address hidden>
date: 2011-08-11 22:34:41 -0700
build-date: 2011-08-12 12:20:36 +0300
revno: 3151
branch-nick: maria-5.3

Repeatable on maria-5.3. Not repeatable on maria-5.2, mysql-5.5. Not repeatable with other subquery operators. Does not involve NULLs or constant tables.

Changed in maria:
milestone: none → 5.3
Changed in maria:
assignee: nobody → Oleksandr "Sanja" Byelkin (sanja-byelkin)
Changed in maria:
milestone: 5.3 → none
Changed in maria:
importance: Undecided → High
Changed in maria:
importance: High → Medium
milestone: none → 5.3
status: New → In Progress
importance: Medium → High
Revision history for this message
Oleksandr "Sanja" Byelkin (sanja-byelkin) wrote :

It looks like it is important to have range access, explain of incorrect execution:

+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 range NULL b 10 NULL 10 90.00 Using where; Using index for group-by
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
+Warnings:
+Note 1003 select min(`test`.`t1`.`a`) AS `MIN(a)`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` > (select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 0))) group by `test`.`t1`.`b`

If reduce test suite table to INSERT INTO t1 VALUES (0,99),(9,99),(4,0),(7,0),(99,0),(7,0),(8,0),(99,0),(1,0); it works correctly with other explain:

+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 index NULL b 10 NULL 6 100.00 Using where; Using index
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
+Warnings:
+Note 1003 select min(`test`.`t1`.`a`) AS `MIN(a)`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` > (select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 0))) group by `test`.`t1`.`b`

Revision history for this message
Philip Stoev (pstoev-askmonty) wrote :

This is repeatable without a subquery:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( a int, b varchar(1), KEY (b,a));
INSERT INTO t1 VALUES (0,NULL),(9,NULL),(8,'c'),(4,'d'),(7,'d'),(NULL,'f'),(7,'f'),(8,'g'),(NULL,'j');

SELECT a , b FROM t1 WHERE a IS NULL OR b = 'z' ;
SELECT max(a) , b FROM t1 WHERE a IS NULL OR b = 'z' GROUP BY b;

Revision history for this message
Philip Stoev (pstoev-askmonty) wrote :

Also, for a certain set of values, it is also repeatable in mysql 5.5 . So, it may be a legacy thing.

Revision history for this message
Oleksandr "Sanja" Byelkin (sanja-byelkin) wrote :

I checked the bug in 5.1 and it is repeatable.

Changed in maria:
milestone: 5.3 → 5.2
Revision history for this message
Sergey Petrunia (sergefp) wrote :

Re-assigning to Timour because the problem is with min-max loose scan.

Changed in maria:
assignee: Oleksandr "Sanja" Byelkin (sanja-byelkin) → Timour Katchaounov (timour)
Changed in maria:
status: In Progress → Confirmed
summary: - Wrong result with GROUP BY + scalar subquery + multipart key
+ Wrong result with GROUP BY + scalar subquery + multipart key + MIN/MAX
+ loose scan
Changed in maria:
status: Confirmed → In Progress
Revision history for this message
Timour Katchaounov (timour) wrote : Re: Wrong result with GROUP BY + scalar subquery + multipart key + MIN/MAX loose scan

The following query also produces wrong result:

SELECT b, min(a) FROM t1 WHERE a = 7 OR b = 'z' GROUP BY b;
+------+--------+
| b | min(a) |
+------+--------+
| f | 7 |
+------+--------+

While the correct result is:

+------+--------+
| b | min(a) |
+------+--------+
| d | 7 |
| f | 7 |
+------+--------+

Changed in maria:
status: In Progress → Fix Committed
Changed in maria:
status: Fix Committed → In Progress
Elena Stepanova (elenst)
summary: - Wrong result with GROUP BY + scalar subquery + multipart key + MIN/MAX
- loose scan
+ Wrong result with GROUP BY + multipart key + MIN/MAX loose scan
Elena Stepanova (elenst)
tags: added: optimizer upstream wrong-result
Changed in maria:
status: In Progress → Confirmed
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.