Debian Codegraph @

mysql-test/suite/opt_trace/include/range.inc

raw
1 # Test for optimizer tracing of range analysis
2
3 --source include/have_optimizer_trace.inc
4 # InnoDB page size influences cost => makes trace vary.
5
6 SET optimizer_trace_max_mem_size=1048576; # 1MB
7 SET end_markers_in_json=on;
8 SET optimizer_trace="enabled=on,one_line=off";
9
10 CREATE TABLE t1
11 (
12 key1 INT NOT NULL,
13 INDEX i1(key1)
14 );
15
16 --echo Inserting 1024 records into t1
17
18 --disable_query_log
19 INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8);
20
21 let $1=7;
22 set @d=8;
23 while ($1)
24 {
25 EVAL INSERT INTO t1 SELECT key1+@d FROM t1;
26 EVAL SET @d=@d*2;
27 DEC $1;
28 }
29 --enable_query_log
30
31 ALTER TABLE t1 ADD key2 INT NOT NULL, ADD INDEX i2(key2);
32 ALTER TABLE t1 ADD key3 INT NOT NULL, ADD INDEX i3(key3);
33 ALTER TABLE t1 ADD key4 INT NOT NULL, ADD INDEX i4(key4);
34 ALTER TABLE t1 ADD key5 INT NOT NULL, ADD INDEX i5(key5);
35 ALTER TABLE t1 ADD key6 INT NOT NULL, ADD INDEX i6(key6);
36 ALTER TABLE t1 ADD key7 INT NOT NULL, ADD INDEX i7(key7);
37 ALTER TABLE t1 ADD key8 INT NOT NULL, ADD INDEX i8(key8);
38
39 UPDATE t1 SET
40 key2=key1,
41 key3=key1,
42 key4=key1,
43 key5=key1,
44 key6=key1,
45 key7=key1,
46 key8=1024-key1;
47
48 CREATE TABLE t2 (
49 key1a INT NOT NULL,
50 key1b INT NOT NULL,
51 key2 INT NOT NULL,
52 key2_1 INT NOT NULL,
53 key2_2 INT NOT NULL,
54 key3 INT NOT NULL,
55 primary key i1a (key1a, key1b),
56 INDEX i1b (key1b, key1a),
57 INDEX i2_1(key2, key2_1),
58 INDEX i2_2(key2, key2_1)
59 );
60
61 INSERT INTO t2 SELECT key1,key1,key1 div 10, key1 % 10, key1 % 10, key1 FROM t1;
62
63 # multiple ranges on one key
64 --echo
65 EXPLAIN SELECT * FROM t1 WHERE key2 < 5 OR key2 > 1020;
66 --echo
67 --replace_numeric_round 2
68 --skip_if_hypergraph # Does not support the same optimizer trace.
69 SELECT * FROM information_schema.OPTIMIZER_TRACE;
70
71 # multiple ranges on one key, turn off range_optimizer tracing
72 set @@optimizer_trace_features="range_optimizer=off";
73 --echo
74 EXPLAIN SELECT * FROM t1 WHERE key2 < 5 OR key2 > 1020;
75 --echo
76 --replace_numeric_round 2
77 --skip_if_hypergraph # Does not support the same optimizer trace.
78 SELECT * FROM information_schema.OPTIMIZER_TRACE;
79 set @@optimizer_trace_features="range_optimizer=on";
80
81
82 # index merge
83 --echo
84 EXPLAIN SELECT * FROM t1 WHERE key1 < 3 OR key2 > 1020;
85 --echo
86 --replace_numeric_round 2
87 --skip_if_hypergraph # Does not support the same optimizer trace.
88 SELECT * FROM information_schema.OPTIMIZER_TRACE;
89
90 # group without range
91 --echo
92 EXPLAIN SELECT key2, MIN(key2_1) FROM t2 GROUP BY key2;
93 --echo
94 --replace_numeric_round 2
95 --skip_if_hypergraph # Does not support the same optimizer trace.
96 SELECT * FROM information_schema.OPTIMIZER_TRACE;
97
98 # distinct - group quick select without grouping attribute
99 EXPLAIN SELECT DISTINCT key2 FROM t2;
100 --echo
101 --replace_numeric_round 2
102 --skip_if_hypergraph # Does not support the same optimizer trace.
103 SELECT * FROM information_schema.OPTIMIZER_TRACE;
104
105 # group with range
106 --echo
107 EXPLAIN SELECT key2, MIN(key2_1) FROM t2
108 WHERE key2 = 5 or key2 = 4 or key2 = 3 or key2 = 2 or key2 = 1
109 GROUP BY key2;
110 --echo
111 --replace_numeric_round 2
112 --skip_if_hypergraph # Does not support the same optimizer trace.
113 SELECT * FROM information_schema.OPTIMIZER_TRACE;
114
115 #intersect
116 --echo
117 EXPLAIN SELECT * FROM t2 WHERE key2 = 1 AND (key2_1 = 1 OR key3 = 5);
118 --echo
119 --replace_numeric_round 2
120 --skip_if_hypergraph # Does not support the same optimizer trace.
121 SELECT * FROM information_schema.OPTIMIZER_TRACE;
122
123 # union
124 --echo
125 EXPLAIN SELECT * FROM t1 WHERE key2=10 OR key3=3 OR key4 <=> null;
126 --echo
127 --replace_numeric_round 2
128 --skip_if_hypergraph # Does not support the same optimizer trace.
129 SELECT * FROM information_schema.OPTIMIZER_TRACE;
130
131 # range_scan_possible=false
132 --echo
133 EXPLAIN SELECT * FROM t2 WHERE key2_1 < 79 OR key2 = 2;
134 --echo
135 --replace_numeric_round 2
136 --skip_if_hypergraph # Does not support the same optimizer trace.
137 SELECT * FROM information_schema.OPTIMIZER_TRACE;
138
139 # Multiple key parts in same index
140 --echo
141 EXPLAIN SELECT * FROM t2 WHERE key1a = 5 and key1b < 10;
142 --echo
143 --replace_numeric_round 2
144 --skip_if_hypergraph # Does not support the same optimizer trace.
145 SELECT * FROM information_schema.OPTIMIZER_TRACE;
146
147 # Multiple ranges on key parts in same index
148 --echo
149 EXPLAIN SELECT * FROM t2 WHERE (key1a = 5 and key1b < 10 and key1b > 2) or
150 (key1a = 4 and key1b < 7 and key1b > 3);
151 --echo
152 --replace_numeric_round 2
153 --skip_if_hypergraph # Does not support the same optimizer trace.
154 SELECT * FROM information_schema.OPTIMIZER_TRACE;
155
156 # Multiple ranges on key parts in same index
157 --echo
158 EXPLAIN SELECT * FROM t2 WHERE (key1b < 10 and key1b > 7) and
159 (key1a = 4 or key1a = 5);
160 --echo
161 --replace_numeric_round 2
162 --skip_if_hypergraph # Does not support the same optimizer trace.
163 SELECT * FROM information_schema.OPTIMIZER_TRACE;
164
165 # more_expensive_than_table_scan
166 --echo
167 EXPLAIN SELECT * FROM t1 WHERE (key1 > 1 OR key2 > 2);
168 --echo
169 --replace_numeric_round 2
170 --skip_if_hypergraph # Does not support the same optimizer trace.
171 SELECT * FROM information_schema.OPTIMIZER_TRACE;
172
173 # Range analysis on straight join
174 --echo
175 EXPLAIN SELECT STRAIGHT_JOIN * FROM t1, t2
176 WHERE t1.key1=t2.key1a AND t1.key2 > 1020;
177 --echo
178 --replace_numeric_round 2
179 --skip_if_hypergraph # Does not support the same optimizer trace.
180 SELECT * FROM information_schema.OPTIMIZER_TRACE;
181
182 DROP TABLE t1,t2;
183
184 CREATE TABLE t1 (
185 cola char(3) not null,
186 colb char(3) not null,
187 filler char(200),
188 key(cola),
189 key(colb)
190 ) CHARSET utf8mb4;
191 INSERT INTO t1 VALUES ('foo','bar', 'ZZ'),('fuz','baz', 'ZZ');
192
193 --echo Inserting records
194 --disable_query_log
195 let $1=9;
196 while ($1)
197 {
198 eval INSERT INTO t1 SELECT * FROM t1 WHERE cola = 'foo';
199 dec $1;
200 }
201 LET $1=13;
202 WHILE ($1)
203 {
204 eval INSERT INTO t1 SELECT * FROM t1 WHERE cola <> 'foo';
205 dec $1;
206 }
207
208 --enable_query_log
209
210 --echo
211 # Index roworder intersect
212 EXPLAIN SELECT * FROM t1 WHERE cola = 'foo' AND colb = 'bar';
213 --echo
214 --skip_if_hypergraph # Does not support the same optimizer trace.
215 SELECT * FROM information_schema.OPTIMIZER_TRACE;
216
217 --echo
218 # Range with escaped character should be printed escaped
219 EXPLAIN SELECT * FROM t1 WHERE cola = 'f\no';
220 --echo
221 --skip_if_hypergraph # Does not support the same optimizer trace.
222 SELECT * FROM information_schema.OPTIMIZER_TRACE;
223
224 DROP TABLE t1;
225
226 # Test that range optimization is not shown for every outer record
227 # when there is a dynamic range.
228 CREATE TABLE t1(c INT);
229 INSERT INTO t1 VALUES (),();
230 CREATE TABLE t2 (b INT, KEY(b));
231 INSERT INTO t2 VALUES (),(),();
232
233 # First, enable dynamic range optimization tracing
234 SET optimizer_trace_features="greedy_search=off,dynamic_range=on";
235 EXPLAIN SELECT 1 FROM
236 (SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2;
237 --echo
238 --skip_if_hypergraph # Does not support the same optimizer trace.
239 SELECT * FROM information_schema.OPTIMIZER_TRACE;
240 --echo
241
242 # Second, disable dynamic range optimization tracing
243 SET optimizer_trace_features="greedy_search=off,dynamic_range=off";
244 EXPLAIN SELECT 1 FROM
245 (SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2;
246 --echo
247 --skip_if_hypergraph # Does not support the same optimizer trace.
248 SELECT * FROM information_schema.OPTIMIZER_TRACE;
249 --echo
250
251 DROP TABLE t1,t2;
252
253 SET optimizer_trace_features=default;
254
255 # Range analysis in test_if_skip_sort_order
256 # (records_estimation_for_index_ordering)
257 CREATE TABLE t1 (
258 i1 int,
259 i2 int,
260 c char(1),
261 KEY k1 (i1),
262 KEY k2 (i1, i2)
263 ) CHARSET utf8mb4;
264 INSERT INTO t1 VALUES (0,1,'2'),(3,2,'1');
265
266 EXPLAIN SELECT * FROM t1 WHERE i1 > '2' ORDER BY i1, i2;
267 --echo
268 --skip_if_hypergraph # Does not support the same optimizer trace.
269 SELECT * FROM information_schema.OPTIMIZER_TRACE;
270 --echo
271
272 EXPLAIN SELECT DISTINCT i1 FROM t1 WHERE i1 >= '1' ORDER BY i1 DESC;
273 --echo
274 --skip_if_hypergraph # Does not support the same optimizer trace.
275 SELECT * FROM information_schema.OPTIMIZER_TRACE;
276 --echo
277
278 DROP TABLE t1;
279
280 # Analyze whether to use covering roworder intersect
281 CREATE TABLE t1 (
282 pk INT PRIMARY KEY,
283 i1 INT,
284 i2 INT,
285 v varchar(1),
286 INDEX i1_idx (i1),
287 INDEX v_idx (v,i1)
288 ) ENGINE=InnoDB, CHARSET utf8mb4;
289
290 INSERT INTO t1 VALUES (1, 1, 9,'a'), (2, 2, 8,'b'), (3, 3, 7,'c'),
291 (4, 4, 6,'d'), (5, 5, 5,'e');
292
293 -- disable_query_log
294 -- disable_result_log
295 ANALYZE TABLE t1;
296 -- enable_result_log
297 -- enable_query_log
298
299 --echo
300 --echo # Covering ROR intersect not chosen: Index with more keyparts found.
301 EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AND v = 'a' AND pk < 3;
302 --echo
303 --skip_if_hypergraph # Does not support the same optimizer trace.
304 SELECT * FROM information_schema.OPTIMIZER_TRACE;
305
306 --echo
307 --echo # Chooses ROR intersect
308 EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AND pk < 3;
309 --echo
310 --skip_if_hypergraph # Does not support the same optimizer trace.
311 SELECT * FROM information_schema.OPTIMIZER_TRACE;
312
313 --echo
314 DROP TABLE t1;
315
316 # Optimizer first decides to use ref, then changes mind to use range instead
317 CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b)) ENGINE=InnoDB STATS_PERSISTENT=0;
318 INSERT INTO t1 VALUES (1,1),(1,2),(1,0),(1,3);
319
320 # -- disable_query_log
321 # -- disable_result_log
322 # ANALYZE TABLE t1;
323 # -- enable_result_log
324 # -- enable_query_log
325
326 --echo
327 --echo # Test trace for unreliable_ref_cost_and_range_uses_more_keyparts to
328 --echo # ignore ref access on index with overly optimistic cost-estimate
329 EXPLAIN SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
330 --echo
331 --skip_if_hypergraph # Does not support the same optimizer trace.
332 SELECT * FROM information_schema.OPTIMIZER_TRACE;
333
334 --echo
335 drop table t1;
336
337 --echo #
338 --echo # Tracing of when test_if_skip_sort_order() switches to another
339 --echo # index and we abandon ICP,
340 --echo # see "disabling_pushed_condition_on_old_index" in trace.
341 --echo #
342
343 CREATE TABLE t1 (
344 c1 VARCHAR(2) NOT NULL,
345 i1 INTEGER NOT NULL,
346 c2 VARCHAR(2) NOT NULL,
347 KEY k1 (c1),
348 KEY k2 (c1, i1)
349 ) CHARSET utf8mb4;
350
351 INSERT INTO t1 VALUES ('0',3,'0'),('0',2,'1');
352
353 EXPLAIN SELECT * FROM t1 WHERE c1 = '1' ORDER BY i1;
354
355 --skip_if_hypergraph # Does not support the same optimizer trace.
356 SELECT * FROM information_schema.OPTIMIZER_TRACE;
357
358 --echo #
359 --echo # Bug#30266767 OPTIMIZER TRACE CONTAINS INVALID JSON FOR FORCE INDEX QUERIES
360 --echo #
361
362 # Validate that the "infinite cost" value present in the previous
363 # trace is parsable; if not, JSON_TYPE returns an error; if the regex
364 # is not matched, NULL.
365
366 --skip_if_hypergraph # Does not support the same optimizer trace.
367 SELECT JSON_TYPE(REGEXP_SUBSTR(TRACE, "1.*e[+]?308")) AS json_type
368 FROM information_schema.OPTIMIZER_TRACE;
369
370 DROP TABLE t1;