Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.3.9
-
None
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;
|
Attachments
Issue Links
- links to
Activity
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.
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.