Bug #12889 IN not working as expected

• Submitted: 30 Aug 2005 18:07
• Modified: 30 Aug 2005 18:13
• Reporter: Jason Schulz
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.10a
• OS: Linux (CentOS 4)
• CPU Architecture: Any

[30 Aug 2005 18:07] Jason Schulz

Description:
The problem is in regards to a query containing a LEFT JOIN (probably other JOINs as well) between tables, and having a WHERE clause containing an AND with an IN(...).  If the IN statement has an argument that refers to a field not satisfied in the LEFT JOIN (filled with NULL in the results), the IN fails regardless of the other arguments.  So even if the "expr IN (...)" can be satisfied by another argument, it fails.

How to repeat:
CREATE TABLE bar (
  id int(10) NOT NULL auto_increment,
  string varchar(10) default NULL,
  PRIMARY KEY  (id)
) TYPE=MyISAM;

INSERT INTO bar VALUES (1,'one');
INSERT INTO bar VALUES (2,'two');
INSERT INTO bar VALUES (3,'three');

CREATE TABLE foo (
  id int(10) NOT NULL auto_increment,
  bar_id int(10) NOT NULL default '0',
  PRIMARY KEY  (id)
) TYPE=MyISAM;

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

SELECT * FROM foo AS F LEFT JOIN bar AS B ON (F.bar_id = B.id) WHERE F.bar_id IN (4, B.id) AND F.id = 4;

(will return Empty Set)

However, in MySQL 4.0.25-r2 (Gentoo) it will return:

+----+--------+------+--------+
| id | bar_id | id   | string |
+----+--------+------+--------+
|  4 |      4 | NULL | NULL   |
+----+--------+------+--------+

[30 Aug 2005 18:13] MySQL Verification Team

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

master >select version();
+------------------+
| version()        |
+------------------+
| 4.1.15-debug-log |
+------------------+
1 row in set (0.00 sec)

master >use test;
Database changed
master >CREATE TABLE bar (
    ->   id int(10) NOT NULL auto_increment,
    ->   string varchar(10) default NULL,
    ->   PRIMARY KEY  (id)
    -> ) TYPE=MyISAM;
Query OK, 0 rows affected, 1 warning (0.07 sec)

master >
master >INSERT INTO bar VALUES (1,'one');
Query OK, 1 row affected (0.00 sec)

master >INSERT INTO bar VALUES (2,'two');
Query OK, 1 row affected (0.00 sec)

master >INSERT INTO bar VALUES (3,'three');
Query OK, 1 row affected (0.00 sec)

master >CREATE TABLE foo (
    ->   id int(10) NOT NULL auto_increment,
    ->   bar_id int(10) NOT NULL default '0',
    ->   PRIMARY KEY  (id)
    -> ) TYPE=MyISAM;
Query OK, 0 rows affected, 1 warning (0.04 sec)

master >
master >INSERT INTO foo VALUES (1,1);
Query OK, 1 row affected (0.00 sec)

master >INSERT INTO foo VALUES (2,2);
Query OK, 1 row affected (0.00 sec)

master >INSERT INTO foo VALUES (3,3);
Query OK, 1 row affected (0.01 sec)

master >INSERT INTO foo VALUES (4,4);
Query OK, 1 row affected (0.00 sec)

master >
master >SELECT * FROM foo AS F LEFT JOIN bar AS B ON (F.bar_id = B.id) WHERE F.bar_id IN
    -> (4, B.id) AND F.id = 4;
+----+--------+------+--------+
| id | bar_id | id   | string |
+----+--------+------+--------+
|  4 |      4 | NULL | NULL   |
+----+--------+------+--------+
1 row in set (0.00 sec)

master >
master >