Wrong result with subquery containing DISTINCT and ORDER BY

[Timour Katchaounov]

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.

[Timour Katchaounov (timour)]

The test case was recorded as part of the following commit:

 2647 Igor Babaev 2008-07-26
      Fixed bug #38191.
      Calling List<Cached_item>::delete_elements for the same list twice
      caused a crash of the server in the function JOIN::cleaunup.
      Ensured that delete_elements() in JOIN::cleanup would be called only once.