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

[Philip Stoev]

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.

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

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`

[Philip Stoev (pstoev-askmonty)]

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;

[Philip Stoev (pstoev-askmonty)]

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

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

I checked the bug in 5.1 and it is repeatable.

[Sergey Petrunia (sergefp)]

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

[Timour Katchaounov (timour)]

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