[MDEV-13172] Wrong result / SELECT ... WHERE EXISTS ... (with UNIQUE Key) Created: 2017-06-26  Updated: 2019-10-15  Resolved: 2019-10-15

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.0, 10.1, 10.2.6, 5.5.65, 10.2
Fix Version/s: 10.2.28, 5.5.66, 10.1.42, 10.3.19, 10.4.9

Type: Bug Priority: Critical
Reporter: Chris N. Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 1
Labels: EXISTS, UNIQUE
Environment:

Windows 10 Pro, latest Version


 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?

 Comments   
Comment by Elena Stepanova [ 2017-06-26 ]

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)

Comment by Martin Štěpař [ 2017-11-03 ]

Not only Windows, reproduced on Ubuntu 14.04.5 LTS too.

Affects IN statement as well. Probably same processing algorithm?

Comment by Oleksandr Byelkin [ 2017-11-06 ]

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

Comment by Oleksandr Byelkin [ 2017-11-06 ]

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

Comment by Oleksandr Byelkin [ 2017-11-06 ]

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.

Comment by Oleksandr Byelkin [ 2018-06-11 ]

need to discussing with psergey

Comment by Martin Štěpař [ 2019-09-30 ]

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.

Comment by Oleksandr Byelkin [ 2019-10-15 ]

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;

Generated at Thu Feb 08 08:03:28 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.