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
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
|
#
# Run subselect_sj2.test with BKA enabled
#
set @save_optimizer_switch_jcl6=@@optimizer_switch;
set @@optimizer_switch='optimize_join_buffer_size=on';
set @@optimizer_switch='semijoin_with_cache=on';
set @@optimizer_switch='outer_join_with_cache=on';
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_sj2_test=@@optimizer_switch;
set @join_cache_level_for_subselect_sj2_test=@@join_cache_level;
--source subselect_sj2.test
set @innodb_stats_persistent_save= @@innodb_stats_persistent;
set @innodb_stats_persistent_sample_pages_save=
@@innodb_stats_persistent_sample_pages;
set global innodb_stats_persistent= 1;
set global innodb_stats_persistent_sample_pages=100;
--echo #
--echo # Bug #898073: potential incremental join cache for semijoin
--echo #
CREATE TABLE t1 (a int, b varchar(1), KEY (b,a));
INSERT INTO t1 VALUES (0,'x'), (5,'r');
CREATE TABLE t2 (a int) ENGINE=InnoDB;
INSERT INTO t2 VALUES (8);
CREATE TABLE t3 (b varchar(1), c varchar(1)) ENGINE=InnoDB;
INSERT INTO t3 VALUES ('x','x');
CREATE TABLE t4 (a int NOT NULL, b varchar(1)) ENGINE=InnoDB;
INSERT INTO t4 VALUES (20,'r'), (10,'x');
set @tmp_optimizer_switch=@@optimizer_switch;
SET SESSION optimizer_switch='semijoin_with_cache=on';
SET SESSION join_cache_level=2;
EXPLAIN
SELECT t3.* FROM t1 JOIN t3 ON t3.b = t1.b
WHERE c IN (SELECT t4.b FROM t4 JOIN t2);
SELECT t3.* FROM t1 JOIN t3 ON t3.b = t1.b
WHERE c IN (SELECT t4.b FROM t4 JOIN t2);
set optimizer_switch=@tmp_optimizer_switch;
set join_cache_level=default;
DROP TABLE t1,t2,t3,t4;
--echo #
--echo # Bug #899696: potential incremental join cache for semijoin
--echo #
CREATE TABLE t1 (pk int PRIMARY KEY, a int);
INSERT INTO t1 VALUES (1, 6), (2, 8);
CREATE TABLE t2 (b int) ENGINE=InnoDB;
INSERT INTO t2 VALUES (8);
CREATE TABLE t3 (pk int PRIMARY KEY, a int);
INSERT INTO t3 VALUES (1, 6), (2, 8);
CREATE TABLE t4 (b int) ENGINE=InnoDB;
INSERT INTO t4 VALUES (2),(88),(99);
set @tmp_optimizer_switch=@@optimizer_switch;
SET optimizer_switch = 'semijoin_with_cache=on';
SET join_cache_level = 2;
EXPLAIN
SELECT * FROM t1, t2 WHERE b IN (SELECT a FROM t3, t4 WHERE b = pk);
SELECT * FROM t1, t2 WHERE b IN (SELECT a FROM t3, t4 WHERE b = pk);
set optimizer_switch=@tmp_optimizer_switch;
set join_cache_level=default;
DROP TABLE t1,t2,t3,t4;
--echo #
--echo # Bug #899962: materialized subquery with join_cache_level=3
--echo #
CREATE TABLE t1 (a varchar(1), b varchar(1)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('v','v');
CREATE TABLE t2 (a varchar(1), b varchar(1)) ENGINE=InnoDB;
INSERT INTO t2 VALUES ('v','v');
set @tmp_optimizer_switch=@@optimizer_switch;
SET optimizer_switch = 'semijoin_with_cache=on';
SET join_cache_level = 3;
EXPLAIN
SELECT * FROM t1 WHERE b IN (SELECT a FROM t2 GROUP BY a);
SELECT * FROM t1 WHERE b IN (SELECT a FROM t2 GROUP BY a);
EXPLAIN
SELECT * FROM t1 WHERE b IN (SELECT max(a) FROM t2 GROUP BY a);
SELECT * FROM t1 WHERE b IN (SELECT max(a) FROM t2 GROUP BY a);
set optimizer_switch=@tmp_optimizer_switch;
set join_cache_level=default;
DROP TABLE t1,t2;
set global innodb_stats_persistent= @innodb_stats_persistent_save;
set global innodb_stats_persistent_sample_pages=
@innodb_stats_persistent_sample_pages_save;
set @@optimizer_switch=@save_optimizer_switch_jcl6;
set @optimizer_switch_for_subselect_sj2_test=NULL;
set @join_cache_level_subselect_sj2_test=NULL;
|
