[MDEV-2518] LP:906322 - Wrong result with subquery containing DISTINCT and ORDER BY Created: 2011-12-19  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Timour Katchaounov (Inactive) Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug906322.xml    

 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.

 Comments   
Comment by Timour Katchaounov (Inactive) [ 2011-12-19 ]

Re: Wrong result with subquery containing DISTINCT and ORDER BY
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.

Comment by Rasmus Johansson (Inactive) [ 2012-02-20 ]

Launchpad bug id: 906322

Generated at Thu Feb 08 06:42:24 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.