MDEV-13933: Wrong results in COUNT() query with EXISTS and exists_to_inbb-5.5-MDEV-13933

Roll back to most general duplicate removing strategi in case of different stratagies for one position.

6 files changed, 158 insertions, 0 deletions

diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 8fc9cd38728..e2f2b6521c8 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -7146,3 +7146,29 @@ SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2;
 (SELECT MAX(sq.f2) FROM t1)
 NULL
 drop table t1, t2;
+#
+# MDEV-13933: Wrong results in COUNT() query with EXISTS and exists_to_in
+# (5.5 test)
+#
+SET @optimiser_switch_save= @@optimizer_switch;
+CREATE TABLE t1 (a INT NOT NULL);
+INSERT INTO t1 VALUES (1),(1),(1),(5),(5);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (5),(1);
+CREATE TABLE t3 (c INT, KEY(c));
+INSERT INTO t3 VALUES (5),(5);
+SET optimizer_switch='semijoin=on';
+select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`)
+and t1.a in (select `test`.`t3`.`c` from `test`.`t3`);
+a
+5
+5
+SET optimizer_switch='semijoin=off';
+select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`)
+and t1.a in (select `test`.`t3`.`c` from `test`.`t3`);
+a
+5
+5
+SET @@optimizer_switch= @optimiser_switch_save;
+DROP TABLE t1, t2, t3;
+End of 5.5 tests
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index a71df97e6bc..25ef4a76962 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -7143,6 +7143,32 @@ SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2;
 (SELECT MAX(sq.f2) FROM t1)
 NULL
 drop table t1, t2;
+#
+# MDEV-13933: Wrong results in COUNT() query with EXISTS and exists_to_in
+# (5.5 test)
+#
+SET @optimiser_switch_save= @@optimizer_switch;
+CREATE TABLE t1 (a INT NOT NULL);
+INSERT INTO t1 VALUES (1),(1),(1),(5),(5);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (5),(1);
+CREATE TABLE t3 (c INT, KEY(c));
+INSERT INTO t3 VALUES (5),(5);
+SET optimizer_switch='semijoin=on';
+select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`)
+and t1.a in (select `test`.`t3`.`c` from `test`.`t3`);
+a
+5
+5
+SET optimizer_switch='semijoin=off';
+select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`)
+and t1.a in (select `test`.`t3`.`c` from `test`.`t3`);
+a
+5
+5
+SET @@optimizer_switch= @optimiser_switch_save;
+DROP TABLE t1, t2, t3;
+End of 5.5 tests
 set optimizer_switch=default;
 select @@optimizer_switch like '%materialization=on%';
 @@optimizer_switch like '%materialization=on%'
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index 2f8c67fa167..074874fbd5b 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -7141,4 +7141,30 @@ SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2;
 (SELECT MAX(sq.f2) FROM t1)
 NULL
 drop table t1, t2;
+#
+# MDEV-13933: Wrong results in COUNT() query with EXISTS and exists_to_in
+# (5.5 test)
+#
+SET @optimiser_switch_save= @@optimizer_switch;
+CREATE TABLE t1 (a INT NOT NULL);
+INSERT INTO t1 VALUES (1),(1),(1),(5),(5);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (5),(1);
+CREATE TABLE t3 (c INT, KEY(c));
+INSERT INTO t3 VALUES (5),(5);
+SET optimizer_switch='semijoin=on';
+select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`)
+and t1.a in (select `test`.`t3`.`c` from `test`.`t3`);
+a
+5
+5
+SET optimizer_switch='semijoin=off';
+select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`)
+and t1.a in (select `test`.`t3`.`c` from `test`.`t3`);
+a
+5
+5
+SET @@optimizer_switch= @optimiser_switch_save;
+DROP TABLE t1, t2, t3;
+End of 5.5 tests
 set @optimizer_switch_for_subselect_test=null;
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index 8dda0a4fd36..de49585b562 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -7152,6 +7152,32 @@ SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2;
 (SELECT MAX(sq.f2) FROM t1)
 NULL
 drop table t1, t2;
+#
+# MDEV-13933: Wrong results in COUNT() query with EXISTS and exists_to_in
+# (5.5 test)
+#
+SET @optimiser_switch_save= @@optimizer_switch;
+CREATE TABLE t1 (a INT NOT NULL);
+INSERT INTO t1 VALUES (1),(1),(1),(5),(5);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (5),(1);
+CREATE TABLE t3 (c INT, KEY(c));
+INSERT INTO t3 VALUES (5),(5);
+SET optimizer_switch='semijoin=on';
+select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`)
+and t1.a in (select `test`.`t3`.`c` from `test`.`t3`);
+a
+5
+5
+SET optimizer_switch='semijoin=off';
+select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`)
+and t1.a in (select `test`.`t3`.`c` from `test`.`t3`);
+a
+5
+5
+SET @@optimizer_switch= @optimiser_switch_save;
+DROP TABLE t1, t2, t3;
+End of 5.5 tests
 set optimizer_switch=default;
 select @@optimizer_switch like '%subquery_cache=on%';
 @@optimizer_switch like '%subquery_cache=on%'
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index 339e2b89786..46a46c91ddc 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -7141,5 +7141,31 @@ SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2;
 (SELECT MAX(sq.f2) FROM t1)
 NULL
 drop table t1, t2;
+#
+# MDEV-13933: Wrong results in COUNT() query with EXISTS and exists_to_in
+# (5.5 test)
+#
+SET @optimiser_switch_save= @@optimizer_switch;
+CREATE TABLE t1 (a INT NOT NULL);
+INSERT INTO t1 VALUES (1),(1),(1),(5),(5);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (5),(1);
+CREATE TABLE t3 (c INT, KEY(c));
+INSERT INTO t3 VALUES (5),(5);
+SET optimizer_switch='semijoin=on';
+select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`)
+and t1.a in (select `test`.`t3`.`c` from `test`.`t3`);
+a
+5
+5
+SET optimizer_switch='semijoin=off';
+select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`)
+and t1.a in (select `test`.`t3`.`c` from `test`.`t3`);
+a
+5
+5
+SET @@optimizer_switch= @optimiser_switch_save;
+DROP TABLE t1, t2, t3;
+End of 5.5 tests
 set @optimizer_switch_for_subselect_test=null;
 set @join_cache_level_for_subselect_test=NULL;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 50d9517043a..e6233e9de78 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -6033,3 +6033,31 @@ CREATE TABLE t2 (f2 INT, KEY(f2)) ENGINE=MyISAM;
 INSERT t2 VALUES (6),(9);
 SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2;
 drop table t1, t2;
+
+--echo #
+--echo # MDEV-13933: Wrong results in COUNT() query with EXISTS and exists_to_in
+--echo # (5.5 test)
+--echo #
+SET @optimiser_switch_save= @@optimizer_switch;
+ 
+CREATE TABLE t1 (a INT NOT NULL);
+INSERT INTO t1 VALUES (1),(1),(1),(5),(5);
+ 
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (5),(1);
+ 
+CREATE TABLE t3 (c INT, KEY(c));
+INSERT INTO t3 VALUES (5),(5);
+ 
+SET optimizer_switch='semijoin=on';
+select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`)
+and t1.a in (select `test`.`t3`.`c` from `test`.`t3`);
+ 
+SET optimizer_switch='semijoin=off';
+select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`)
+and t1.a in (select `test`.`t3`.`c` from `test`.`t3`);
+ 
+SET @@optimizer_switch= @optimiser_switch_save;
+DROP TABLE t1, t2, t3;
+
+--echo End of 5.5 tests