Bug #11867 Wrong result with "... WHERE ROW( a, b ) IN ( SELECT DISTINCT a, b WHERE ...);"
Submitted: 11 Jul 2005 18:26 Modified: 17 Aug 2005 18:17
Reporter: Are you mortal Then prepare to die. Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:4.1.11-standard-log OS:dec-osf5.1b-alphaev67
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[11 Jul 2005 18:26] Are you mortal Then prepare to die.
Description:

Combining the following elements results in a consistent error in the returned results...

SELECT ... WHERE ROW( ... )  IN ( SELECT DISTINCT ... WHERE ... );

It seems the problem is the DISTINCT in the subquery, which is somehow optimized incorrectly? EXPLAIN dosn't suggest that the optimizer is kicking in, however.

Here is what EXPLAIN says (first with DISTINCT, then without);

+----+--------------------+--------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type        | table  | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+--------------------+--------+------+---------------+------+---------+------+------+------------------------------+
|  1 | PRIMARY            | PIFFLE | ALL  | NULL          | NULL |    NULL | NULL |    5 | Using where                  |
|  2 | DEPENDENT SUBQUERY | PIFFLE | ALL  | NULL          | NULL |    NULL | NULL |    5 | Using where; Using temporary |
+----+--------------------+--------+------+---------------+------+---------+------+------+------------------------------+
2 rows in set (0.00 sec)

+----+--------------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type        | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+--------------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY            | PIFFLE | ALL  | NULL          | NULL |    NULL | NULL |    5 | Using where |
|  2 | DEPENDENT SUBQUERY | PIFFLE | ALL  | NULL          | NULL |    NULL | NULL |    5 | Using where |
+----+--------------------+--------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.01 sec)

I haven't rigorously tested or verified 'similar' bugs, but my example is nice and simple, and highlights the problem repeatably on my system...

How to repeat:

DROP   TABLE PIFFLE;
CREATE TABLE PIFFLE (ONE INT, TWO INT, FLAG CHAR(1));
INSERT INTO PIFFLE VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');

SELECT * FROM PIFFLE WHERE ROW(ONE,TWO) IN (SELECT DISTINCT ONE,TWO FROM PIFFLE WHERE FLAG = 'N');

Expected result;
+------+------+------+
| ONE  | TWO  | FLAG |
+------+------+------+
|    5 |    6 | N    |
|    7 |    8 | N    |
+------+------+------+

Observed result;
Empty set (0.01 sec)

The above expected result is exactly what you do get with...

SELECT * FROM PIFFLE WHERE ROW(ONE,TWO) NOT IN (SELECT ONE,TWO FROM PIFFLE WHERE FLAG = 'N');

i.e. if you remove the 'DISTINCT' part of the sub query.

Like I said, I haven't tested around, but it does have to be a 'ROW( ... )  IN ( subquery )' rather than just a simple 'IN'. The same results are observed with 'NOT IN', and when using different tables, so 

DROP   TABLE PIFFLE;
CREATE TABLE PIFFLE (ONE INT, TWO INT, FLAG CHAR(1));

DROP   TABLE BIFFLE;
CREATE TABLE BIFFLE (ONE INT, TWO INT, FLAG CHAR(1));

INSERT INTO PIFFLE VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');
INSERT INTO BIFFLE VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N');

SELECT * FROM PIFFLE WHERE ROW(ONE,TWO) IN (SELECT DISTINCT ONE,TWO FROM BIFFLE WHERE FLAG = 'N');

Gives the same (INCORRECT) result.

Suggested fix:
By monday?
[11 Jul 2005 18:31] Are you mortal Then prepare to die.
The second part of my example should of course read;
----
The above expected result is exactly what you do get with...

SELECT * FROM PIFFLE WHERE ROW(ONE,TWO) IN (SELECT ONE,TWO FROM PIFFLE WHERE FLAG = 'N');

i.e. if you remove the 'DISTINCT' part of the sub query.
----
Sorry if that is / was a bit confusing.
[12 Jul 2005 3:29] Jorge del Conde
Hi

Thanks for your bug report.  I was able to reproduce this bug using 4.1.13 from bk!
[7 Aug 2005 19:10] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27980
[8 Aug 2005 21:26] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/28037
[13 Aug 2005 4:45] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/28245
[13 Aug 2005 11:31] Oleksandr Byelkin
Thank you for bugreport.

bugfix pushed to 4.1.14 &  5.0.12
[17 Aug 2005 18:17] Paul DuBois
Noted in 4.1.14, 5.0.12 changelogs.