Details
Major
Description
The following test case produces wrong result:
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;
Gliffy Diagrams
Attachments
Issue Links
- links to
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
The bug manifests itself with the above example in MariaDB 5.3.
In MariaDB 5.5, MySQL 5.5, and MySQL 5.6 the bug cannot be reproduced directly by the above example because the cost of loose scan is higher than that of index scan.
However, if one changes the cost in GDB, loose scan is chosen, which produces wrong result. So the final test case for 5.5 and above must be designed to trigger loose scan.
There is a patch, the patch has been reviewed by Sergey. The review requires more analysis and possibly a more generic solution.
The wrong result is present in MariaDB 5.3 and on, and in MySQL 5.5/5.6 (older versions should be affected too).
In addition MySQL crashes by calling EXPLAIN on the query after executing it or doing a show create table (in 5.5.), or 'create table t2 like t1' (in 5.6). MariaDB works fine, just the wrong result is a problem.
The MySQL crash was filed as http://bugs.mysql.com/?id=68179, let's see if they'll hide it.
They did: "You do not have access to bug #68179."
pushed to 5.3
Re: Wrong result with GROUP BY + scalar subquery + multipart key
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`