Wrong result with GROUP BY + multipart key + MIN/MAX loose scan
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=
test case:
CREATE TABLE t1 (a int, b int, KEY (b, a)) ;
INSERT INTO t1 VALUES (0,99),
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 |
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 |
Changed in maria: | |
status: | In Progress → Fix Committed |
Changed in maria: | |
status: | Fix Committed → In Progress |
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 |
tags: | added: optimizer upstream wrong-result |
Changed in maria: | |
status: | In Progress → Confirmed |
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 .`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`
+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`
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 .`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`
+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`