mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 02:46:29 +01:00
218 lines
6.5 KiB
Text
218 lines
6.5 KiB
Text
#
|
|
# Run subselect_sj.test with BKA enabled
|
|
#
|
|
--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';
|
|
set @@optimizer_switch='semijoin=on,firstmatch=on,loosescan=on';
|
|
set @@optimizer_switch='semijoin_with_cache=on';
|
|
set @@optimizer_switch='outer_join_with_cache=on';
|
|
set @@optimizer_switch='join_cache_hashed=off';
|
|
set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
|
|
set @save_join_cache_level=@@join_cache_level;
|
|
set join_cache_level=6;
|
|
|
|
set @optimizer_switch_for_subselect_sj_test=@@optimizer_switch;
|
|
set @join_cache_level_for_subselect_sj_test=@@join_cache_level;
|
|
|
|
--source subselect_sj.test
|
|
|
|
--echo #
|
|
--echo # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off
|
|
--echo #
|
|
CREATE TABLE t0 (a INT);
|
|
INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
|
|
CREATE TABLE t1 (a INT, b INT, KEY(a));
|
|
INSERT INTO t1 SELECT a, a from t0;
|
|
INSERT INTO t1 SELECT a+5, a from t0;
|
|
INSERT INTO t1 SELECT a+10, a from t0;
|
|
CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
|
|
INSERT INTO t2 SELECT * FROM t1;
|
|
UPDATE t1 SET a=3, b=11 WHERE a=4;
|
|
UPDATE t2 SET b=11 WHERE a=3;
|
|
|
|
set @save_optimizer_switch=@@optimizer_switch;
|
|
set optimizer_switch='firstmatch=off';
|
|
|
|
--echo The following should use a join order of t0,t1,t2, with DuplicateElimination:
|
|
explain
|
|
SELECT * FROM t0 WHERE t0.a IN
|
|
(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
|
|
|
|
SELECT * FROM t0 WHERE t0.a IN
|
|
(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
|
|
|
|
set optimizer_switch=@save_optimizer_switch;
|
|
drop table t0, t1, t2;
|
|
|
|
--echo #
|
|
--echo # Bug #891995: IN subquery with join_cache_level >= 3
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a varchar(1));
|
|
INSERT INTO t1 VALUES ('w'),('q');
|
|
|
|
CREATE TABLE t2 (a int NOT NULL, b time, PRIMARY KEY (a));
|
|
INSERT INTO t2 VALUES
|
|
(2,'18:56:33'), (5,'19:11:10'), (3,'18:56:33'), (7,'19:11:10');
|
|
|
|
CREATE TABLE t3 (
|
|
a int NOT NULL, b int, c int, d varchar(1), PRIMARY KEY (a), KEY (d,c)
|
|
);
|
|
INSERT INTO t3 VALUES
|
|
(25,158,10,'f'), (26,5,2,'v'), (27,163,103,'f'), (28,2,3,'q'), (29,8,6,'y');
|
|
|
|
set @save_optimizer_switch=@@optimizer_switch;
|
|
SET SESSION optimizer_switch='semijoin=on';
|
|
|
|
SET SESSION optimizer_switch='join_cache_hashed=on';
|
|
SET SESSION join_cache_level=3;
|
|
EXPLAIN
|
|
SELECT * FROM t1, t2
|
|
WHERE t2.a IN (SELECT b FROM t3 WHERE t3.d <= t1.a);
|
|
SELECT * FROM t1, t2
|
|
WHERE t2.a IN (SELECT b FROM t3 WHERE t3.d <= t1.a);
|
|
|
|
|
|
SET SESSION optimizer_switch='mrr=on';
|
|
SET SESSION join_cache_level=6;
|
|
EXPLAIN
|
|
SELECT * FROM t1, t2
|
|
WHERE t2.a IN (SELECT b FROM t3 WHERE t3.d <= t1.a);
|
|
SELECT * FROM t1, t2
|
|
WHERE t2.a IN (SELECT b FROM t3 WHERE t3.d <= t1.a);
|
|
|
|
set optimizer_switch=@save_optimizer_switch;
|
|
set join_cache_level=default;
|
|
|
|
DROP TABLE t1,t2,t3;
|
|
|
|
--echo #
|
|
--echo # BUG#912513: Wrong result (missing rows) with join_cache_hashed+materialization+semijoin=on
|
|
--echo #
|
|
set @os_912513= @@optimizer_switch;
|
|
set @jcl_912513= @@join_cache_level;
|
|
SET optimizer_switch = 'semijoin=on,materialization=on,join_cache_hashed=on';
|
|
SET join_cache_level = 3;
|
|
|
|
CREATE TABLE t1 ( a INT, b INT, KEY(a) );
|
|
INSERT INTO t1 VALUES
|
|
(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7);
|
|
|
|
CREATE TABLE t2 ( c INT );
|
|
INSERT INTO t2 VALUES (1),(2),(3),(4),(5),(6),(7);
|
|
|
|
SELECT alias1.* FROM
|
|
t1 AS alias1 INNER JOIN t1 AS alias2
|
|
ON alias2.a = alias1.b
|
|
WHERE alias1.b IN (
|
|
SELECT a FROM t1, t2
|
|
);
|
|
|
|
DROP table t1, t2;
|
|
set @@optimizer_switch= @os_912513;
|
|
set @@join_cache_level= @jcl_912513;
|
|
|
|
--echo # End
|
|
|
|
--echo #
|
|
--echo # BUG#934342: outer join + semijoin materialization
|
|
--echo # + join_cache_level > 2
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a varchar(1), b varchar(1), INDEX idx_a(a)) CHARSET=latin1;
|
|
INSERT INTO t1 VALUES ('v','v'), ('w','w'), ('t','t');
|
|
|
|
CREATE TABLE t2 (c varchar(1), INDEX idx_c(c)) CHARSET=latin1;
|
|
INSERT INTO t2 VALUES ('v'), ('v'), ('s'), ('j');
|
|
|
|
CREATE TABLE t3 (c varchar(1), d varchar(1), INDEX idx_c(c)) CHARSET=latin1;
|
|
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';
|
|
|
|
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) having t1.a !='z';
|
|
SELECT * FROM t1 LEFT JOIN t2 ON (c = b)
|
|
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) having t1.a !='z';
|
|
SELECT * FROM t1 LEFT JOIN t3 ON (c = b)
|
|
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) having t1.a !='z';
|
|
SELECT * FROM t1 LEFT JOIN t2 ON (c = b)
|
|
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) having t1.a !='z';
|
|
SELECT * FROM t1 LEFT JOIN t3 ON (c = b)
|
|
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;
|
|
|
|
DROP TABLE t1,t2,t3;
|
|
|
|
--echo # End
|
|
|
|
--echo #
|
|
--echo # BUG#934348: GROUP BY with HAVING + semijoin materialization
|
|
--echo # + join_cache_level > 2
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a varchar(1), INDEX idx_a(a)) CHARSET=latin1;
|
|
INSERT INTO t1 VALUES ('c'), ('v'), ('c');
|
|
|
|
CREATE TABLE t2 (b varchar(1)) CHARSET=latin1;
|
|
INSERT INTO t2 VALUES ('v'), ('c');
|
|
|
|
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';
|
|
|
|
set join_cache_level=0;
|
|
|
|
EXPLAIN
|
|
SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a)
|
|
GROUP BY a HAVING a != 'z';
|
|
SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a)
|
|
GROUP BY a HAVING a != 'z';
|
|
|
|
set join_cache_level=6;
|
|
|
|
EXPLAIN
|
|
SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a)
|
|
GROUP BY a HAVING a != 'z';
|
|
SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a)
|
|
GROUP BY a HAVING a != 'z';
|
|
|
|
set optimizer_switch=@tmp_optimizer_switch;
|
|
set join_cache_level=@tmp_join_cache_level;
|
|
|
|
DROP TABLE t1,t2;
|
|
|
|
--echo # End
|
|
|
|
set join_cache_level=@save_join_cache_level;
|
|
set @@optimizer_switch=@save_optimizer_switch_jcl6;
|
|
set @optimizer_switch_for_subselect_sj_test=NULL;
|
|
set @join_cache_level_subselect_sj_test=NULL;
|