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 20:26	Modified:	17 Aug 2005 20:17
Reporter:	Are you mortal? Then prepare to die.
Status:	Closed
Category:	Server: Optimizer	Severity:	S2 (Serious)
Version:	4.1.11-standard-log	OS:	dec-osf5.1b-alphaev67
Assigned to:	Bugs System	Target Version:

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.