diff options
author | Sergei Golubchik <serg@mariadb.org> | 2017-05-09 13:24:52 +0200 |
---|---|---|
committer | Sergei Golubchik <serg@mariadb.org> | 2017-05-09 13:24:52 +0200 |
commit | c91ecf9e9bebf3cf2dafbd3193de4df94be09870 (patch) | |
tree | a9ab429c8e8a6a50c38557cbdd38d9612480f49c /mysql-test/r/subselect_sj_mat.result | |
parent | 2645bda5f20ab10bc26dc1cb69f91b5505c1faae (diff) | |
parent | c92168fcd26aad35bd4cb5d65175e3545133f201 (diff) | |
download | mariadb-git-c91ecf9e9bebf3cf2dafbd3193de4df94be09870.tar.gz |
Merge branch '10.1' into 10.2
Revert commit db0917f68f, because the fix for MDEV-12696
is coming from 5.5 and 10.1 in this merge.
Diffstat (limited to 'mysql-test/r/subselect_sj_mat.result')
-rw-r--r-- | mysql-test/r/subselect_sj_mat.result | 111 |
1 files changed, 111 insertions, 0 deletions
diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index a3f3cc4213c..72f875cf8a7 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -2232,6 +2232,117 @@ execute stmt; a 0 drop table t1; +# +# MDEV-12429: IN subquery used in WHERE of EXISTS subquery +# +CREATE TABLE t1 ( +pk INT, f1 INT NOT NULL, f2 VARCHAR(3), f3 INT NULL, PRIMARY KEY(pk)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,1,'foo',8), (2,5,'bar',7); +SELECT sq1.f2 FROM t1 AS sq1 +WHERE EXISTS ( SELECT * FROM t1 AS sq2 +WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); +f2 +foo +set @save_optimizer_switch= @@optimizer_switch; +set optimizer_switch='exists_to_in=off'; +EXPLAIN +SELECT sq1.f2 FROM t1 AS sq1 +WHERE EXISTS ( SELECT * FROM t1 AS sq2 +WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY sq1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 func 1 +2 DEPENDENT SUBQUERY sq2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +3 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 +# this checks the result set above +set optimizer_switch= 'materialization=off,semijoin=off'; +SELECT sq1.f2 FROM t1 AS sq1 +WHERE EXISTS ( SELECT * FROM t1 AS sq2 +WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 ); +f2 +foo +set optimizer_switch= @save_optimizer_switch; +DROP TABLE t1; +# +# MDEV-12145: IN subquery used in WHERE of EXISTS subquery +# +CREATE TABLE t1 (f1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (4),(6); +CREATE TABLE t2 (i2 INT, KEY(i2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (8),(7),(1); +CREATE TABLE t3 (f3 INT, i3 INT, KEY(i3)) ENGINE=MyISAM; +INSERT INTO t3 VALUES (8,0),(6,3),(2,8),(3,8),(1,6),(0,0),(1,0),(1,5); +set @save_optimizer_switch= @@optimizer_switch; +set optimizer_switch='exists_to_in=off'; +SELECT * FROM t1 +WHERE EXISTS ( SELECT * FROM t2, t3 +WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) ); +f1 +6 +EXPLAIN EXTENDED +SELECT * FROM t1 +WHERE EXISTS ( SELECT * FROM t2, t3 +WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where +2 DEPENDENT SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 +2 DEPENDENT SUBQUERY t2 index i2 i2 5 NULL 3 100.00 Using where; Using index; Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t3 ref i3 i3 5 test.t2.i2 2 100.00 Using index +3 MATERIALIZED t3 ALL NULL NULL NULL NULL 8 100.00 +Warnings: +Note 1276 Field or reference 'test.t1.f1' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where <expr_cache><`test`.`t1`.`f1`>(exists(select 1 from `test`.`t2` semi join (`test`.`t3`) join `test`.`t3` where `test`.`t3`.`i3` = `test`.`t2`.`i2` and `test`.`t1`.`f1` = `test`.`t3`.`f3`)) +# this checks the result set above +set optimizer_switch= 'materialization=off,semijoin=off'; +SELECT * FROM t1 +WHERE EXISTS ( SELECT * FROM t2, t3 +WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) ); +f1 +6 +set optimizer_switch= @save_optimizer_switch; +DROP TABLE t1,t2,t3; +# +# MDEV-9686: IN subquery used in WHERE of a subquery from select list +# +CREATE TABLE t1 (pk INT PRIMARY KEY, f1 INT); +INSERT INTO t1 VALUES (1, 4),(2, 3),(3, 3),(4, 6),(5, 3); +CREATE TABLE t2 (f2 INT); +INSERT INTO t2 VALUES (1),(2),(3),(4),(5); +# t1.pk is always IN ( SELECT f2 FROM t2 ), +# so the IN condition should be true for every row, +# and thus COUNT(*) should always return 5 +SELECT pk, f1, ( SELECT COUNT(*) FROM t2 +WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1; +pk f1 sq +1 4 5 +2 3 5 +3 3 5 +4 6 5 +5 3 5 +EXPLAIN EXTENDED +SELECT pk, f1, ( SELECT COUNT(*) FROM t2 +WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 +2 DEPENDENT SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (flat, BNL join) +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00 +Warnings: +Note 1276 Field or reference 'test.t1.pk' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`f1` AS `f1`,<expr_cache><`test`.`t1`.`pk`>((select count(0) from `test`.`t2` semi join (`test`.`t2`) where `test`.`t1`.`pk` = `test`.`t2`.`f2`)) AS `sq` from `test`.`t1` +# this checks the result set above +set @save_optimizer_switch= @@optimizer_switch; +set optimizer_switch= 'materialization=off,semijoin=off'; +SELECT pk, f1, ( SELECT COUNT(*) FROM t2 +WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1; +pk f1 sq +1 4 5 +2 3 5 +3 3 5 +4 6 5 +5 3 5 +set optimizer_switch= @save_optimizer_switch; +DROP TABLE t1,t2; # End of 5.5 tests # # MDEV-7220: Materialization strategy is not used for REPLACE ... SELECT |