Bug #1848 using subselect in IN(..) to query indirect one-to-many relationship goes wrong

• Submitted: 15 Nov 2003 23:31
• Modified: 17 Nov 2003 2:34
• Reporter: [ name withheld ]
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.0-alpha
• OS: Windows (Windows 2000)
• Assigned to: Oleksandr Byelkin
• CPU Architecture: Any

[15 Nov 2003 23:31] [ name withheld ]

Description:
There are 3 tables, a, b, and b_c, 
which express the following relationship:

A -------------> B -----------------> C
     1 to 1            1 to many

table `a`:   (id int, b_id int)
table `b`:   (id int)
table `b_c`: (b_id int, c int)

where I try to query instances of A of which the corresponding
instances of B contain a given C value with the following SQL:

select a.id from a, b where a.b_id=b.id and ? in (select c from b_c where b_id=b.id)

the results are wrong for all C values I've tried.
For example, when the tables contains the following values:

table `a`                table `b`         table `b_c`
(id,    b_id)            ( id )            (b_id, c)
-------------------------------------------------------------
( 1,     101)  ------->  ( 101 )  -------> (101,  11)
                                     \---> (101,  13)

( 2,     102)  ------->  ( 102 )  -------> (102,  12)

The result is as follow:

mysql> select a.id from a, b where a.b_id=b.id and 11 in (select c from b_c where b_id=b.id);
Empty set (0.00 sec)
==> Wrong, should be 
   +----+
   | id |
   +----+
   |  1 |
   +----+

mysql> select a.id from a, b where a.b_id=b.id and 12 in (select c from b_c where b_id=b.id);
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)
==> Wrong, should be 
   +----+
   | id |
   +----+
   |  2 |
   +----+

mysql> select a.id from a, b where a.b_id=b.id and 13 in (select c from b_c where b_id=b.id);
Empty set (0.01 sec)
==> Wrong, should be 
   +----+
   | id |
   +----+
   |  1 |
   +----+

How to repeat:
CREATE TABLE `a` (
  `id` int(11) NOT NULL default '0',
  `b_id` int(11) default NULL,
  PRIMARY KEY  (`id`)
);

CREATE TABLE `b` (
  `id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
);

CREATE TABLE `b_c` (
  `b_id` int(11) NOT NULL default '0',
  `c` int(11) NOT NULL default '0'
);

insert into a values (1, 101);
insert into a values (2, 102);

insert into b values (101);
insert into b values (102);

insert into b_c values (101, 11);
insert into b_c values (101, 13);
insert into b_c values (102, 12);

select a.id from a, b where a.b_id=b.id and 11 in (select c from b_c where b_id=b.id);

select a.id from a, b where a.b_id=b.id and 12 in (select c from b_c where b_id=b.id);

select a.id from a, b where a.b_id=b.id and 13 in (select c from b_c where b_id=b.id);

[16 Nov 2003 13:10] Oleksandr Byelkin

I can't repeat this bug: 
 
+ CREATE TABLE `a` (`id` int(11) NOT NULL default '0',`b_id` int(11) default 
NULL,PRIMARY KEY  (`id`)); 
+ CREATE TABLE `b` (`id` int(11) NOT NULL default '0',PRIMARY KEY  (`id`)); 
+ CREATE TABLE `b_c` (`b_id` int(11) NOT NULL default '0',`c` int(11) NOT NULL 
default '0'); 
+ insert into a values (1, 101),(2, 102); 
+ insert into b values (101),(102); 
+ insert into b_c values (101, 11),(101, 13),(102, 12); 
+ select a.id from a, b where a.b_id=b.id and 11 in (select c from b_c where 
b_id=b.id); 
+ id 
+ 1 
+ select a.id from a, b where a.b_id=b.id and 12 in (select c from b_c where 
b_id=b.id); 
+ id 
+ 2 
+ select a.id from a, b where a.b_id=b.id and 13 in (select c from b_c where 
b_id=b.id); 
+ id 
+ 1 
 
 
Which version of serever you used for testing?

[16 Nov 2003 17:47] [ name withheld ]

I installed and run tests on the 4.0.1-alpha for Win32.

[16 Nov 2003 17:48] [ name withheld ]

Sorry, it's 4.1.0-alpha, not 4.0.1-alpha.

[17 Nov 2003 2:34] Oleksandr Byelkin

thus it looks like bug is fixed in current repository of 4.1