diff options
author | Oleksandr Byelkin <sanja@mariadb.com> | 2017-10-06 17:52:35 +0200 |
---|---|---|
committer | Oleksandr Byelkin <sanja@mariadb.com> | 2018-01-10 16:58:04 +0100 |
commit | 9c9cf556a15af84f9133ef859b50596085f7ae8e (patch) | |
tree | ea02026b856606ba8c5c14734fffe8f4e6e04f67 /mysql-test/r | |
parent | a9c55c0059351220674a1dd3d3bf8d4e884b3285 (diff) | |
download | mariadb-git-9c9cf556a15af84f9133ef859b50596085f7ae8e.tar.gz |
MDEV-13933: Wrong results in COUNT() query with EXISTS and exists_to_in
Roll back to most general duplicate removing strategi in case of different stratagies for one position.
Diffstat (limited to 'mysql-test/r')
-rw-r--r-- | mysql-test/r/subselect.result | 26 | ||||
-rw-r--r-- | mysql-test/r/subselect_no_mat.result | 26 | ||||
-rw-r--r-- | mysql-test/r/subselect_no_opts.result | 26 | ||||
-rw-r--r-- | mysql-test/r/subselect_no_scache.result | 26 | ||||
-rw-r--r-- | mysql-test/r/subselect_no_semijoin.result | 26 |
5 files changed, 130 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; |