1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
|
#
# Run subselect_sj.test with BKA enabled
#
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 join_cache_level=6;
show variables like 'join_cache_level';
set @optimizer_switch_for_subselect_sj_test=@@optimizer_switch;
set @join_cache_level_for_subselect_sj_test=@@join_cache_level;
--source t/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 # End
set join_cache_level=default;
show variables like '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;
|
