diff options
Diffstat (limited to '')
| -rw-r--r-- | mysql-test/main/subselect_sj_jcl6.test | 19 |
1 files changed, 11 insertions, 8 deletions
diff --git a/mysql-test/main/subselect_sj_jcl6.test b/mysql-test/main/subselect_sj_jcl6.test index f4f605c0..e39a6887 100644 --- a/mysql-test/main/subselect_sj_jcl6.test +++ b/mysql-test/main/subselect_sj_jcl6.test @@ -3,6 +3,7 @@ # --source include/no_valgrind_without_big.inc --source include/default_optimizer_switch.inc +--source include/have_sequence.inc set @save_optimizer_switch_jcl6=@@optimizer_switch; set @@optimizer_switch='optimize_join_buffer_size=on'; @@ -132,6 +133,8 @@ CREATE TABLE t3 (c varchar(1), d varchar(1), INDEX idx_c(c) ); INSERT INTO t3 VALUES ('v','v'), ('v','v'), ('s','s'), ('j','j'); INSERT INTO t3 VALUES ('m','m'), ('d','d'), ('k','k'), ('m','m'); +insert into t1 select 'z','z' from seq_1_to_20; + set @tmp_otimizer_switch= @@optimizer_switch; set @tmp_join_cache_level=@@join_cache_level; set optimizer_switch = 'materialization=on,semijoin=on,join_cache_hashed=on'; @@ -140,29 +143,29 @@ set join_cache_level=0; EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c = b) - WHERE (a, b) IN (SELECT a, b FROM t1 t); + WHERE (a, b) IN (SELECT a, b FROM t1 t) having t1.a !='z'; SELECT * FROM t1 LEFT JOIN t2 ON (c = b) - WHERE (a, b) IN (SELECT a, b FROM t1 t); + WHERE (a, b) IN (SELECT a, b FROM t1 t) having t1.a !='z'; EXPLAIN SELECT * FROM t1 LEFT JOIN t3 ON (c = b) - WHERE (a, b) IN (SELECT a, b FROM t1 t); + WHERE (a, b) IN (SELECT a, b FROM t1 t) having t1.a !='z'; SELECT * FROM t1 LEFT JOIN t3 ON (c = b) - WHERE (a, b) IN (SELECT a, b FROM t1 t); + WHERE (a, b) IN (SELECT a, b FROM t1 t) having t1.a !='z'; set join_cache_level=6; EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c = b) - WHERE (a, b) IN (SELECT a, b FROM t1 t); + WHERE (a, b) IN (SELECT a, b FROM t1 t) having t1.a !='z'; SELECT * FROM t1 LEFT JOIN t2 ON (c = b) - WHERE (a, b) IN (SELECT a, b FROM t1 t); + WHERE (a, b) IN (SELECT a, b FROM t1 t) having t1.a !='z'; EXPLAIN SELECT * FROM t1 LEFT JOIN t3 ON (c = b) - WHERE (a, b) IN (SELECT a, b FROM t1 t); + WHERE (a, b) IN (SELECT a, b FROM t1 t) having t1.a !='z'; SELECT * FROM t1 LEFT JOIN t3 ON (c = b) - WHERE (a, b) IN (SELECT a, b FROM t1 t); + WHERE (a, b) IN (SELECT a, b FROM t1 t) having t1.a !='z'; set optimizer_switch=@tmp_optimizer_switch; set join_cache_level=@tmp_join_cache_level; |