Elena Stepanova added a comment - 2017-06-26 11:37 Thank you for the report and test case. Reproducible on 10.0, 10.1, 10.2, with InnoDB and MyISAM. Exact same case, just put together for copy-paste: CREATE TABLE `tab1` ( `Id` int (11) NOT NULL , PRIMARY KEY (`Id`) ); 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`) ); INSERT INTO `tab2` (`tab1_Id`, `col1`) VALUES (1, NULL ), (1, NULL );   SELECT Id FROM tab1 WHERE EXISTS ( SELECT 1 AS `C1` FROM tab2 WHERE tab1.Id = tab2.tab1_Id AND tab2.col1 IS NULL ); DROP TABLE tab1, tab2; Actual result MariaDB [test]> SELECT Id FROM tab1 WHERE EXISTS -> (SELECT 1 AS `C1` FROM tab2 WHERE tab1.Id = tab2.tab1_Id AND tab2.col1 IS NULL); +----+ | Id | +----+ | 1 | | 1 | +----+ 2 rows in set (0.00 sec) Expected result MariaDB [test]> SELECT Id FROM tab1 WHERE EXISTS -> (SELECT 1 AS `C1` FROM tab2 WHERE tab1.Id = tab2.tab1_Id AND tab2.col1 IS NULL); +----+ | Id | +----+ | 1 | +----+ 1 row in set (0.00 sec)

Martin Štěpař added a comment - 2017-11-03 10:10 - edited Not only Windows, reproduced on Ubuntu 14.04.5 LTS too. Affects IN statement as well. Probably same processing algorithm?

Oleksandr Byelkin added a comment - 2017-11-06 14:30 actually what server execute is: select 1 AS `Id` from `test`.`tab2` where ((`test`.`tab2`.`tab1_Id` = 1) and isnull(`test`.`tab2`.`col1`)) probably converted to semijoin than one table elimitnated

Oleksandr Byelkin added a comment - 2017-11-06 14:46 - edited It is not EXISTS to IN problem because corespondent IN has the same problem: SELECT Id FROM tab1 WHERE Id in (SELECT Id FROM tab2 WHERE tab2.col1 IS NULL); Id 1 1

Oleksandr Byelkin added a comment - 2017-11-06 14:54 simplify_joins somehow decides that outer join can be converted to inner one. Decision based on fact that condition is null rejecting, I do not see why conversion is legal.

Oleksandr Byelkin added a comment - 2018-06-11 14:43 need to discussing with psergey

Martin Štěpař added a comment - 2019-09-30 15:10 Hi guys, any progress here? I understand there is a lot of things to do, but it's over 2 years since issue was reported.

Oleksandr Byelkin added a comment - 2019-10-15 08:34 - edited 5.5 test suite CREATE TABLE `tab1` ( `Id` int(11) NOT NULL, PRIMARY KEY (`Id`) ); 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`) ); INSERT INTO `tab2` (`tab1_Id`, `col1`) VALUES (1, NULL), (1, NULL); SELECT Id FROM tab1 WHERE Id in (SELECT tab1_Id FROM tab2 WHERE tab2.col1 IS NULL);   DROP TABLE tab1, tab2;