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
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
|
#
# Collection of test cases to verify that the hypergraph optimizer is used
#
SET optimizer_trace='enabled=on';
#
# Bug #33296454: Use the hypergraph optimizer for SET statements
#
CREATE TABLE t(x INT);
INSERT INTO t VALUES (1), (2), (3);
SET @x = (SELECT COUNT(*) FROM t);
SELECT COUNT(*) FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE
WHERE TRACE LIKE '%Constructed hypergraph%';
COUNT(*)
1
SELECT @x;
@x
3
SET @x = (SELECT COUNT(*) FROM t WHERE x >= 2);
SELECT COUNT(*) FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE
WHERE TRACE LIKE '%Constructed hypergraph%';
COUNT(*)
1
SELECT @x;
@x
2
SET @x = 1 + (SELECT COUNT(*) FROM t);
SELECT COUNT(*) FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE
WHERE TRACE LIKE '%Constructed hypergraph%';
COUNT(*)
1
SELECT @x;
@x
4
SET @x = 1 IN (SELECT x FROM t);
SELECT COUNT(*) FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE
WHERE TRACE LIKE '%Constructed hypergraph%';
COUNT(*)
1
SELECT @x;
@x
1
SET @x = EXISTS (SELECT x FROM t);
SELECT COUNT(*) FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE
WHERE TRACE LIKE '%Constructed hypergraph%';
COUNT(*)
1
SELECT @x;
@x
1
# Case 1.a: Preparation: 'hypergraph_optimizer=on', execution: 'hypergraph_optimizer=on'
PREPARE ps_set FROM 'SET @x = (SELECT COUNT(*) FROM t)';
PREPARE ps_select FROM 'SELECT COUNT(*) FROM t';
EXECUTE ps_set;
SELECT COUNT(*) FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE
WHERE TRACE LIKE '%Constructed hypergraph%';
COUNT(*)
1
EXECUTE ps_select;
COUNT(*)
3
SELECT COUNT(*) FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE
WHERE TRACE LIKE '%Constructed hypergraph%';
COUNT(*)
1
# Case 1.b: Preparation: 'hypergraph_optimizer=on', execution: 'hypergraph_optimizer=off'
SET optimizer_switch = 'hypergraph_optimizer=off';
EXECUTE ps_set;
SELECT COUNT(*) FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE
WHERE TRACE LIKE '%Constructed hypergraph%';
COUNT(*)
1
EXECUTE ps_select;
COUNT(*)
3
SELECT COUNT(*) FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE
WHERE TRACE LIKE '%Constructed hypergraph%';
COUNT(*)
1
# Case 2.a: Preparation: 'hypergraph_optimizer=off', execution: 'hypergraph_optimizer=on'
SET optimizer_switch = 'hypergraph_optimizer=off';
PREPARE ps_set FROM 'SET @x = (SELECT COUNT(*) FROM t)';
PREPARE ps_select FROM 'SELECT COUNT(*) FROM t';
EXECUTE ps_set;
SELECT COUNT(*) FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE
WHERE TRACE LIKE '%Constructed hypergraph%';
COUNT(*)
0
EXECUTE ps_select;
COUNT(*)
3
SELECT COUNT(*) FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE
WHERE TRACE LIKE '%Constructed hypergraph%';
COUNT(*)
0
# Case 2.b: Preparation: 'hypergraph_optimizer=off', execution: 'hypergraph_optimizer=off'
SET optimizer_switch = 'hypergraph_optimizer=off';
EXECUTE ps_set;
SELECT COUNT(*) FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE
WHERE TRACE LIKE '%Constructed hypergraph%';
COUNT(*)
0
EXECUTE ps_select;
COUNT(*)
3
SELECT COUNT(*) FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE
WHERE TRACE LIKE '%Constructed hypergraph%';
COUNT(*)
0
SET optimizer_switch = 'hypergraph_optimizer=off';
SET @x = (SELECT COUNT(*) FROM t);
SELECT COUNT(*) FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE
WHERE TRACE LIKE '%Constructed hypergraph%';
COUNT(*)
0
SELECT @x;
@x
3
# Clean up for Bug #33296454
DROP TABLE t;
# Bug #33296454 done
#
# Bug #33296504: Use the hypergraph optimizer for subqueries in INSERT statements
#
CREATE TABLE t1(x INT);
CREATE TABLE t2(x INT);
INSERT INTO t1 VALUES ((SELECT COUNT(*) FROM t2));
SELECT COUNT(*) FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE
WHERE TRACE LIKE '%Constructed hypergraph%';
COUNT(*)
1
SELECT x FROM t1;
x
0
TRUNCATE t1;
INSERT INTO t1 VALUES (1 + (SELECT COUNT(*) FROM t2));
SELECT COUNT(*) FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE
WHERE TRACE LIKE '%Constructed hypergraph%';
COUNT(*)
1
SELECT x FROM t1;
x
1
TRUNCATE t1;
INSERT INTO t2 VALUES (1), (2), (3);
INSERT INTO t1 VALUES ((SELECT COUNT(*) FROM t2 WHERE x <= 2));
SELECT COUNT(*) FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE
WHERE TRACE LIKE '%Constructed hypergraph%';
COUNT(*)
1
SELECT x FROM t1;
x
2
TRUNCATE t1;
TRUNCATE t2;
CREATE TABLE t3(x INT PRIMARY KEY);
INSERT INTO t3 VALUES (1);
INSERT INTO t3 VALUES (1) ON DUPLICATE KEY UPDATE x = (SELECT COUNT(*) FROM t1);
SELECT COUNT(*) FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE
WHERE TRACE LIKE '%Constructed hypergraph%';
COUNT(*)
1
SELECT x FROM t3;
x
0
TRUNCATE t3;
SET optimizer_switch = 'hypergraph_optimizer=off';
INSERT INTO t1 VALUES ((SELECT COUNT(*) FROM t2));
SELECT COUNT(*) FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE
WHERE TRACE LIKE '%Constructed hypergraph%';
COUNT(*)
0
# Clean up for Bug #33296504
DROP TABLE t1, t2, t3;
# Bug #33296504 done
#
# WL#14673: Enable the hypergraph optimizer for DELETE
#
CREATE TABLE t (x INTEGER, y INTEGER);
DELETE t FROM t WHERE x > 0;
SELECT COUNT(*) FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE
WHERE TRACE LIKE '%Constructed hypergraph%';
COUNT(*)
1
DELETE FROM t WHERE x > 0;
SELECT COUNT(*) FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE
WHERE TRACE LIKE '%Constructed hypergraph%';
COUNT(*)
1
DROP TABLE t;
#
# WL#14672: Enable the hypergraph optimizer for UPDATE
#
CREATE TABLE t1 (x INTEGER, y INTEGER);
CREATE TABLE t2 (z INTEGER);
INSERT INTO t1 VALUES (1, 2), (2, 3), (3, 4);
INSERT INTO t2 VALUES (1), (3), (5);
UPDATE t1, t2 SET y = y + 1 WHERE x = z;
SELECT COUNT(*) FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE
WHERE TRACE LIKE '%Constructed hypergraph%';
COUNT(*)
1
UPDATE t1 SET y = y + 1;
SELECT COUNT(*) FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE
WHERE TRACE LIKE '%Constructed hypergraph%';
COUNT(*)
1
SELECT * FROM t1;
x y
1 4
2 4
3 6
SELECT * FROM t2;
z
1
3
5
DROP TABLE t1, t2;
# Clean up for test file using_hypergraph_optimizer
SET optimizer_trace='enabled=off';
SET optimizer_switch=default;
|
