Bug #4435 subquery results depend on order of columns in primary key

• Submitted: 7 Jul 2004 15:36
• Modified: 23 Aug 2004 22:39
• Reporter: Hans Benedict
• Status: Closed
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 4.1.3
• OS: Linux (Linux)
• Assigned to: Oleksandr Byelkin
• CPU Architecture: Any

[7 Jul 2004 15:36] Hans Benedict

Description:
A query using a "not  in" subquery gives different results depending on the column order in a primary key used in the subquery.

How to repeat:
CREATE TABLE `t1` (
  `aid` int(11) NOT NULL default '0',
  `bid` int(11) NOT NULL default '0',
  PRIMARY KEY  (`aid`,`bid`)
);

CREATE TABLE `t2` (
  `aid` int(11) NOT NULL default '0',
  `bid` int(11) NOT NULL default '0',
  PRIMARY KEY  (`aid`,`bid`)
);

insert into t1 values (1,1);
insert into t1 values (1,2);
insert into t1 values (2,1);
insert into t1 values (2,2);

insert into t2 values (1,2);
insert into t2 values (2,2);

select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);

+-----+-----+
| aid | bid |
+-----+-----+
|   1 |   1 |
|   1 |   2 |
|   2 |   1 |
|   2 |   2 |
+-----+-----+

this is obviously wrong!

now we switch the columns in t2's primary key:

alter table t2 drop primary key;
alter table t2 add primary key (bid,aid);

select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid);

+-----+-----+
| aid | bid |
+-----+-----+
|   1 |   1 |
|   2 |   1 |
+-----+-----+

this is the result I would expect.

[12 Aug 2004 16:37] Oleksandr Byelkin

ChangeSet 
  1.1957 04/08/12 17:31:23 [e-mail] +3 -0 
  in case of compound index fill all parts in optimized IN (BUG#4435)

[23 Aug 2004 22:39] Oleksandr Byelkin

Thank you for bug report. Bugfix is pushed in our internal source repository and will be 
present in next server release.