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: | |
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 |
[26 Aug 2004 6:00]
Rob Blick
[26 Aug 2004 7:35]
MySQL Verification Team
Verified against 4.1.4 BK source. Thanks for the bug report.
[8 Sep 2004 18:43]
MySQL Verification Team
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)
[8 Sep 2004 18:59]
Oleksandr Byelkin
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.