MySQL Bugs: #15501: WHERE-clause is ignored

Bug #15501	WHERE-clause is ignored
Submitted:	5 Dec 2005 22:11	Modified:	6 Dec 2005 17:36
Reporter:	Kai Ruhnau	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.16	OS:	Linux (Gentoo Linux)
Assigned to:		CPU Architecture:	Any

Description:
I have a self join on a table using the primary key and I want to join another table, linked against the primary key. The result is filtered with where.

If I use the primary key from the first selfjoin table (FROM ...) as link source to the second table, all is fine and MySQL filters with where.

However, when I use the second table selfjoin table (INNER JOIN ...) as link source to the second table MySQL completly ignores the WHERE-condition.

How to repeat:
DROP TABLE IF EXISTS `foo`;
CREATE TABLE `foo` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `foo` VALUES (1),(2),(3);

DROP TABLE IF EXISTS `bar`;
CREATE TABLE `bar` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `ID_foo` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`ID`),
  KEY `ID_foo` (`ID_foo`),
  CONSTRAINT `bar_ibfk_1` FOREIGN KEY (`ID_foo`) REFERENCES `foo` (`ID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `bar` VALUES (2,1),(1,2),(3,3);

SELECT * FROM foo AS foo_first INNER JOIN foo AS foo_second ON foo_second.ID=foo_first.ID INNER JOIN bar ON bar.ID_foo=foo_first.ID WHERE foo_first.ID=1;
+----+----+----+--------+
| ID | ID | ID | ID_foo |
+----+----+----+--------+
|  1 |  1 |  2 |      1 |
+----+----+----+--------+

SELECT * FROM foo AS foo_first INNER JOIN foo AS foo_second ON foo_second.ID=foo_first.ID INNER JOIN bar ON bar.ID_foo=foo_second.ID WHERE foo_first.ID=1;
+----+----+----+--------+
| ID | ID | ID | ID_foo |
+----+----+----+--------+
|  1 |  1 |  2 |      1 |
|  2 |  2 |  1 |      2 |
|  3 |  3 |  3 |      3 |
+----+----+----+--------+

SELECT * FROM foo AS foo_first INNER JOIN foo AS foo_second ON foo_second.ID=foo_first.ID INNER JOIN bar ON bar.ID_foo=foo_second.ID;
+----+----+----+--------+
| ID | ID | ID | ID_foo |
+----+----+----+--------+
|  1 |  1 |  2 |      1 |
|  2 |  2 |  1 |      2 |
|  3 |  3 |  3 |      3 |
+----+----+----+--------+

Suggested fix:
Both queries are equivalent, they should return the same result.

Thank you for a problem report. Sorry, but I was not able to repeat it on 5.0.17-BK (ChangeSet@1.1973, 2005-12-03 20:52:34+01:00):

mysql> DROP TABLE IF EXISTS `foo`;
Query OK, 0 rows affected, 1 warning (0,00 sec)

mysql> CREATE TABLE `foo` (
    ->   `ID` int(10) unsigned NOT NULL auto_increment,
    ->   PRIMARY KEY  (`ID`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0,04 sec)

mysql> INSERT INTO `foo` VALUES (1),(2),(3);

Query OK, 3 rows affected (0,00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> DROP TABLE IF EXISTS `bar`;
Query OK, 0 rows affected, 1 warning (0,00 sec)

mysql> CREATE TABLE `bar` (
    ->   `ID` int(10) unsigned NOT NULL auto_increment,
    ->   `ID_foo` int(10) unsigned NOT NULL,
    ->   PRIMARY KEY  (`ID`),
    ->   KEY `ID_foo` (`ID_foo`),
    ->   CONSTRAINT `bar_ibfk_1` FOREIGN KEY (`ID_foo`) REFERENCES `foo` (`ID`) ON
    -> DELETE CASCADE
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Query OK, 0 rows affected (0,05 sec)

mysql> INSERT INTO `bar` VALUES (2,1),(1,2),(3,3);

Query OK, 3 rows affected (0,01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM foo AS foo_first INNER JOIN foo AS foo_second ON
    -> foo_second.ID=foo_first.ID INNER JOIN bar ON bar.ID_foo=foo_first.ID WHERE
    -> foo_first.ID=1;
+----+----+----+--------+
| ID | ID | ID | ID_foo |
+----+----+----+--------+
|  1 |  1 |  2 |      1 |
+----+----+----+--------+
1 row in set (0,00 sec)

mysql> SELECT * FROM foo AS foo_first INNER JOIN foo AS foo_second ON
    -> foo_second.ID=foo_first.ID INNER JOIN bar ON bar.ID_foo=foo_second.ID WHERE
    -> foo_first.ID=1;
+----+----+----+--------+
| ID | ID | ID | ID_foo |
+----+----+----+--------+
|  1 |  1 |  2 |      1 |
+----+----+----+--------+
1 row in set (0,01 sec)

mysql> SELECT * FROM foo AS foo_first INNER JOIN foo AS foo_second ON
    -> foo_second.ID=foo_first.ID INNER JOIN bar ON bar.ID_foo=foo_second.ID;
+----+----+----+--------+
| ID | ID | ID | ID_foo |
+----+----+----+--------+
|  1 |  1 |  2 |      1 |
|  2 |  2 |  1 |      2 |
|  3 |  3 |  3 |      3 |
+----+----+----+--------+
3 rows in set (0,00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.17    |
+-----------+
1 row in set (0,00 sec)

So, please, try to use 5.0.16 and/or wait 5.0.17 to be released.

FYI: MySQL 5.0.16 does not work, too.