MySQL Bugs: #11867: Wrong result with "... WHERE ROW( a, b ) IN ( SELECT DISTINCT a, b WHERE ...);"

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

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?

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.

Hi

Thanks for your bug report.  I was able to reproduce this bug using 4.1.13 from bk!

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

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

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

Thank you for bugreport.

bugfix pushed to 4.1.14 &  5.0.12

Noted in 4.1.14, 5.0.12 changelogs.