Bug #10649 "where not in" with subquery produces incorrect results
Submitted: 15 May 2005 23:19 Modified: 30 Sep 2006 12:18
Reporter: Willis Blackburn Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.11 OS:Linux (Red Hat Linux 9)
Assigned to: CPU Architecture:Any

[15 May 2005 23:19] Willis Blackburn
Description:
I have two tables:

CREATE TABLE `memberships` (
  `id` int(11) NOT NULL auto_increment,
  `memberId` int(11) NOT NULL default '0',
  `type` varchar(20) NOT NULL default '',
  `flags` int(11) NOT NULL default '0',
  `startDate` datetime NOT NULL default '0000-00-00 00:00:00',
  `endDate` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `memberId` (`memberId`),
  CONSTRAINT `0_33` FOREIGN KEY (`memberId`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `membershipPasses` (
  `id` int(11) NOT NULL auto_increment,
  `code` varchar(16) NOT NULL default '',
  `membershipType` varchar(20) NOT NULL default '',
  `flags` int(11) NOT NULL default '0',
  `created` datetime NOT NULL default '0000-00-00 00:00:00',
  `ownerId` int(11) default NULL,
  `membershipId` int(11) default NULL,
  `paymentId` int(11) default NULL,
  `complimentaryPassId` int(11) default NULL,
  `recipientEmail` varchar(40) default NULL,
  `validDate` datetime NOT NULL default '0000-00-00 00:00:00',
  `expirationDate` datetime NOT NULL default '0000-00-00 00:00:00',
  `recipientName` varchar(60) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `code` (`code`),
  UNIQUE KEY `complimentaryPassId` (`complimentaryPassId`,`recipientEmail`),
  UNIQUE KEY `paymentId` (`paymentId`),
  KEY `membershipPasses_i1` (`ownerId`),
  KEY `membershipPasses_i2` (`membershipId`),
  CONSTRAINT `0_37` FOREIGN KEY (`complimentaryPassId`) REFERENCES `complimentaryPasses` (`id`),
  CONSTRAINT `0_38` FOREIGN KEY (`ownerId`) REFERENCES `users` (`id`),
  CONSTRAINT `0_39` FOREIGN KEY (`membershipId`) REFERENCES `memberships` (`id`),
  CONSTRAINT `0_40` FOREIGN KEY (`paymentId`) REFERENCES `payments` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

memberships has 7,331 rows and membershipPasses has 10,278 rows.  There are three rows in memberships for which the id column value is not present in the membershipId column of membershipPasses.

This query works:

mysql> select count(*) from memberships m where not exists (select * from membershipPasses mp where mp.membershipId = m.id);
+----------+
| count(*) |
+----------+
|        3 |
+----------+

and this one also works:

mysql> select count(*) from memberships m left outer join membershipPasses mp on (m.id = mp.membershipId) where mp.membershipId is null;
+----------+
| count(*) |
+----------+
|        3 |
+----------+

but this one doesn't, even though it should produce the same result as the other two:

mysql> select count(*) from memberships where id not in (select membershipId from membershipPasses);
+----------+
| count(*) |
+----------+
|        0 |
+----------+

How to repeat:
Create tables as described above.  Create additional tables to satisfy the foreign keys or don't create the foreign keys.  (I don't know if this affects the repro or not).  Add some data to the tables, creating the situation above, and run the queries.

Suggested fix:
Make the third query return 3.
[18 May 2005 11:43] Hartmut Holzgraefe
Can you provide us with the actual table data (as i can't reproduce this using test data)?
[18 Jun 2005 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[30 Sep 2006 12:18] Tonci Grgin
Reporter in BUG#22855 has provided simple test case so I'm setting this report to  "Duplicate".