MySQL Bugs: #5220: Subquery returns wrong results with certain PK order (InnoDB)

Bug #5220	Subquery returns wrong results with certain PK order (InnoDB)
Submitted:	26 Aug 2004 6:00	Modified:	8 Sep 2004 18:59
Reporter:	Rob Blick	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	4.1.3 beta/4.1.4	OS:	Linux (Redhat 9/Slackware)
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

Description:
A simple statement like the following:

SELECT R.unit, R.ingredient FROM TEST_RI R WHERE R.ingredient IN (SELECT N.ingredient FROM TEST_N N WHERE N.unit = R.unit);

will return incorrect (i.e., no) results depending on whether a primary key is specified.

See the following trivial example as a test case...

How to repeat:
CREATE TABLE `TEST_RI` (
  `unit` varchar(50) NOT NULL default '',
  `ingredient` varchar(50) NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `TEST_N` (
  `ingredient` varchar(50) NOT NULL default '',
  `unit` varchar(50) NOT NULL default '',
  PRIMARY KEY (ingredient, unit)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `TEST_RI` VALUES ('xx','bozo');
INSERT INTO `TEST_N` VALUES ('bozo','xx');

mysql> SELECT R.unit, R.ingredient FROM TEST_RI R WHERE R.ingredient IN (SELECT N.ingredient FROM TEST_N N WHERE N.unit = R.unit);
Empty set (0.00 sec)  **note: this should return 1 row

But, if you delete the key, it works:
ALTER TABLE TEST_N DROP PRIMARY KEY;

mysql> SELECT R.unit, R.ingredient FROM TEST_RI R WHERE R.ingredient IN (SELECT N.ingredient FROM TEST_N N WHERE N.unit = R.unit);
+------+------------+
| unit | ingredient |
+------+------------+
| xx   | bozo       |
+------+------------+
1 row in set (0.00 sec)

Verified against 4.1.4 BK source.
Thanks for the bug report.

I tested this with the server from the package release and it was
already fixed:

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.4-gamma

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `TEST_RI` (
    ->   `unit` varchar(50) NOT NULL default '',
    ->   `ingredient` varchar(50) NOT NULL default ''
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.18 sec)

mysql>
mysql> CREATE TABLE `TEST_N` (
    ->   `ingredient` varchar(50) NOT NULL default '',
    ->   `unit` varchar(50) NOT NULL default '',
    ->   PRIMARY KEY (ingredient, unit)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.11 sec)

mysql>
mysql> INSERT INTO `TEST_RI` VALUES ('xx','bozo');
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO `TEST_N` VALUES ('bozo','xx');
Query OK, 1 row affected (0.04 sec)

mysql>  SELECT R.unit, R.ingredient FROM TEST_RI R WHERE R.ingredient IN (SELECT
    -> N.ingredient FROM TEST_N N WHERE N.unit = R.unit);
+------+------------+
| unit | ingredient |
+------+------------+
| xx   | bozo       |
+------+------------+
1 row in set (0.01 sec)

Thank you for bugreport! 
 
This bug appeared to be fixed already, but we have included your test in our regress tests 
set, so you can be sure that this kind of queries will work on next release and all higher 
versions of MySQL server.