Details
Critical Description
Sorry, but I'm not sure if it's a bug ... but I think so:
CREATE TABLE `tab1` ( |
`Id` int(11) NOT NULL, |
PRIMARY KEY (`Id`) |
) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
|
|
INSERT INTO `tab1` (`Id`) VALUES (1); |
|
|
CREATE TABLE `tab2` ( |
`tab1_Id` int(11) NOT NULL DEFAULT 0, |
`col1` int(11) DEFAULT NULL, |
UNIQUE KEY `col1` (`col1`) |
) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
|
|
INSERT INTO `tab2` (`tab1_Id`, `col1`) VALUES (1, NULL), (1, NULL); |
Now two Statements:
SELECT Id FROM tab1 WHERE EXISTS |
(SELECT 1 AS `C1` FROM tab2 WHERE tab1.Id = tab2.tab1_Id AND tab2.col1 IS NULL); |
SELECT Id FROM tab1 WHERE EXISTS |
(SELECT 1 AS `C1` FROM tab2 WHERE tab1.Id = tab2.tab1_Id); |
Statement 1 gets a result with 2 identical rows, Statement 2 gets a result with (the correct, I mean) 1 row.
If I delete the UNIQUE Key on tab2:
ALTER TABLE `tab2` DROP INDEX `col1`; |
both Statements gets a result with only 1 row. Why does the UNIQUE Key have such a effect? It's a bug?