MDEV-13172: Wrong result / SELECT ... WHERE EXISTS ... (with UNIQUE Key)bb-5.5-MDEV-13172

IS NULL or <=> with unique field does not mean unique row,
because several NULL possible,
so we can not convert to normal join in this case.

1 files changed, 26 insertions, 0 deletions

diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test
index 6b59049bc4f..befa2c7af5a 100644
--- a/mysql-test/t/subselect_sj.test
+++ b/mysql-test/t/subselect_sj.test
@@ -2881,6 +2881,32 @@ insert into t1 select 'xxx' from dual where 'xxx' in (select a2 from t2);
 
 drop table t1,t2;
 
+
+--echo #
+--echo # MDEV-13172: Wrong result / SELECT ... WHERE EXISTS ... (with UNIQUE Key)
+--echo #
+
+CREATE TABLE `t1` (
+  `Id` int(11) NOT NULL,
+  PRIMARY KEY (`Id`)
+);
+
+INSERT INTO `t1` (`Id`) VALUES (1);
+
+CREATE TABLE `t2` (
+  `t1_Id` int(11) NOT NULL DEFAULT 0,
+  `col1` int(11) DEFAULT NULL,
+  UNIQUE KEY `col1` (`col1`)
+);
+
+INSERT INTO `t2` (`t1_Id`, `col1`) VALUES (1, NULL), (1, NULL);
+
+SELECT Id FROM t1 WHERE Id in (SELECT t1_Id  FROM t2 WHERE t2.col1 IS NULL);
+explain extended
+SELECT Id FROM t1 WHERE Id in (SELECT t1_Id  FROM t2 WHERE t2.col1 IS NULL);
+
+DROP TABLE t1, t2;
+
 --echo # End of 5.5 test
 
 # The following command must be the last one the file