diff options
Diffstat (limited to 'mysql-test/t/subselect4.test')
| -rw-r--r-- | mysql-test/t/subselect4.test | 62 |
1 files changed, 47 insertions, 15 deletions
diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index a5a4e0a3cfe..db4f1635999 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -528,6 +528,27 @@ SELECT * FROM t1 WHERE f3 = ( SELECT f3 FROM t1 WHERE ( f10, f10 ) IN ( SELECT f11, f11 FROM t2 GROUP BY f11 )); +SET @@optimizer_switch = 'materialization=on,in_to_exists=off,semijoin=off'; + +EXPLAIN +SELECT * FROM t1 +WHERE f3 = ( + SELECT t1.f3 FROM t1 + WHERE ( t1.f10 ) IN ( SELECT max(f11) FROM t2 GROUP BY f11 )); +SELECT * FROM t1 +WHERE f3 = ( + SELECT t1.f3 FROM t1 + WHERE ( t1.f10 ) IN ( SELECT max(f11) FROM t2 GROUP BY f11 )); + +EXPLAIN +SELECT * FROM t1 +WHERE f3 = ( + SELECT f3 FROM t1 + WHERE ( f10, f10 ) IN ( SELECT max(f11), f11 FROM t2 GROUP BY f11 )); +SELECT * FROM t1 +WHERE f3 = ( + SELECT f3 FROM t1 + WHERE ( f10, f10 ) IN ( SELECT max(f11), f11 FROM t2 GROUP BY f11 )); SET SESSION optimizer_switch = @old_optimizer_switch; drop table t1,t2; @@ -856,18 +877,27 @@ INSERT INTO t3 VALUES ('a'), ('b'), ('c'); CREATE TABLE t4 (c1 varchar(1) primary key); INSERT INTO t4 VALUES ('k'), ('d'); +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; +SET optimizer_switch='semijoin_with_cache=off'; + +SET optimizer_switch='materialization=off'; EXPLAIN SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2); SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2); EXPLAIN SELECT * FROM t2 LEFT JOIN t1 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2); SELECT * FROM t2 LEFT JOIN t1 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2); +SET optimizer_switch='materialization=on'; EXPLAIN SELECT * FROM (t2 LEFT JOIN t1 ON t1.c1) LEFT JOIN t3 on t3.c1 WHERE 's' IN (SELECT c1 FROM t2); SELECT * FROM (t2 LEFT JOIN t1 ON t1.c1) LEFT JOIN t3 on t3.c1 WHERE 's' IN (SELECT c1 FROM t2); EXPLAIN SELECT * FROM t4 LEFT JOIN t2 ON t4.c1 WHERE 's' IN (SELECT c1 FROM t2); SELECT * FROM t4 LEFT JOIN t2 ON t4.c1 WHERE 's' IN (SELECT c1 FROM t2); + +SET optimizer_switch=@save_optimizer_switch; + drop table t1, t2, t3, t4; --echo # @@ -943,7 +973,7 @@ EXPLAIN SELECT * FROM t1 WHERE (SELECT f2 FROM t2 WHERE f4 <= ALL - (SELECT SQ1_t1.f4 + (SELECT max(SQ1_t1.f4) FROM t3 AS SQ1_t1 JOIN t3 AS SQ1_t3 ON SQ1_t3.f4 GROUP BY SQ1_t1.f4)); @@ -1162,47 +1192,49 @@ INSERT INTO t2 VALUES (10,5,'d1d'); set @save_optimizer_switch=@@optimizer_switch; -set @@optimizer_switch = 'materialization=off'; +SET optimizer_switch='outer_join_with_cache=off'; + +set @@optimizer_switch = 'in_to_exists=on,materialization=off,semijoin=off'; EXPLAIN SELECT alias2.f1 , alias2.f2 FROM t0 AS alias1 RIGHT JOIN t0 AS alias2 ON alias2.f10 -WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT f2 , f1 FROM t0 GROUP BY f2 , f1 ); +WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT max(f2) , f1 FROM t0 GROUP BY f2 , f1 ); SELECT alias2.f1 , alias2.f2 FROM t0 AS alias1 RIGHT JOIN t0 AS alias2 ON alias2.f10 -WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT f2 , f1 FROM t0 GROUP BY f2 , f1 ); +WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT max(f2) , f1 FROM t0 GROUP BY f2 , f1 ); EXPLAIN -SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT f1a, f2a FROM t1 GROUP BY f1a, f2a); -SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT f1a, f2a FROM t1 GROUP BY f1a, f2a); +SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT max(f1a), f2a FROM t1 GROUP BY f1a, f2a); +SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT max(f1a), f2a FROM t1 GROUP BY f1a, f2a); EXPLAIN -SELECT * FROM t2 WHERE (f1b) IN (SELECT f1a FROM t1 GROUP BY f1a, f2a); -SELECT * FROM t2 WHERE (f1b) IN (SELECT f1a FROM t1 GROUP BY f1a, f2a); +SELECT * FROM t2 WHERE (f1b) IN (SELECT max(f1a) FROM t1 GROUP BY f1a, f2a); +SELECT * FROM t2 WHERE (f1b) IN (SELECT max(f1a) FROM t1 GROUP BY f1a, f2a); -SET @@optimizer_switch = 'materialization=on'; +set @@optimizer_switch = 'in_to_exists=off,materialization=on,semijoin=off'; EXPLAIN SELECT alias2.f1 , alias2.f2 FROM t0 AS alias1 RIGHT JOIN t0 AS alias2 ON alias2.f10 -WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT f2 , f1 FROM t0 GROUP BY f2 , f1 ); +WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT max(f2) , f1 FROM t0 GROUP BY f2 , f1 ); SELECT alias2.f1 , alias2.f2 FROM t0 AS alias1 RIGHT JOIN t0 AS alias2 ON alias2.f10 -WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT f2 , f1 FROM t0 GROUP BY f2 , f1 ); +WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT max(f2) , f1 FROM t0 GROUP BY f2 , f1 ); EXPLAIN -SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT f1a, f2a FROM t1 GROUP BY f1a, f2a); -SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT f1a, f2a FROM t1 GROUP BY f1a, f2a); +SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT max(f1a), f2a FROM t1 GROUP BY f1a, f2a); +SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT max(f1a), f2a FROM t1 GROUP BY f1a, f2a); EXPLAIN -SELECT * FROM t2 WHERE (f1b) IN (SELECT f1a FROM t1 GROUP BY f1a, f2a); -SELECT * FROM t2 WHERE (f1b) IN (SELECT f1a FROM t1 GROUP BY f1a, f2a); +SELECT * FROM t2 WHERE (f1b) IN (SELECT max(f1a) FROM t1 GROUP BY f1a, f2a); +SELECT * FROM t2 WHERE (f1b) IN (SELECT max(f1a) FROM t1 GROUP BY f1a, f2a); set @@optimizer_switch=@save_optimizer_switch; |