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.
