Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
None
-
None
Description
The following test case from subselect.test has recorded incorrect result:
CREATE TABLE t1(pk INT PRIMARY KEY, a INT, INDEX idx(a));
INSERT INTO t1 VALUES (1, 10), (3, 30), (2, 20);
CREATE TABLE t2(pk INT PRIMARY KEY, a INT, b INT, INDEX idxa(a));
INSERT INTO t2 VALUES (2, 20, 700), (1, 10, 200), (4, 10, 100);
EXPLAIN
SELECT * FROM t1 WHERE EXISTS (SELECT DISTINCT a FROM t2 WHERE t1.a < t2.a ORDER BY b);
------------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
------------------------------------------------------------------------------------------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
| 2 | DEPENDENT SUBQUERY | t2 | index | idxa | idxa | 5 | NULL | 3 | Using where; Using temporary; Using filesort |
------------------------------------------------------------------------------------------------------------+
SELECT * FROM t1 WHERE EXISTS (SELECT DISTINCT a FROM t2 WHERE t1.a < t2.a ORDER BY b);
--------+
| pk | a |
--------+
| 1 | 10 |
| 3 | 30 |
| 2 | 20 |
--------+
The correct result is only one row, as shown by the same example without the
indexes on column 'a':
CREATE TABLE t1(pk INT PRIMARY KEY, a INT);
INSERT INTO t1 VALUES (1, 10), (3, 30), (2, 20);
CREATE TABLE t2(pk INT PRIMARY KEY, a INT, b INT);
INSERT INTO t2 VALUES (2, 20, 700), (1, 10, 200), (4, 10, 100);
EXPLAIN
SELECT * FROM t1 WHERE EXISTS (SELECT DISTINCT a FROM t2 WHERE t1.a < t2.a ORDER BY b);
-----------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-----------------------------------------------------------------------------------------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
| 2 | DEPENDENT SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 3 | Using where; Using temporary; Using filesort |
-----------------------------------------------------------------------------------------------------------+
SELECT * FROM t1 WHERE EXISTS (SELECT DISTINCT a FROM t2 WHERE t1.a < t2.a ORDER BY b);
--------+
| pk | a |
--------+
| 1 | 10 |
--------+
In MariaDB 5.3/5.5 and MySQL 5.6 this bug is masked by a transformation that
removes DISTINCT, and GROUP BY from subqueries. However it still needs to
be investigated what is the cause of the wrong result.