Debian Codegraph @

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

raw
1 # Test for optimizer tracing of subqueries
2
3 --source include/have_optimizer_trace.inc
4 --source include/have_64bit.inc
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 (a INT);
11 CREATE TABLE t2 (a INT, b INT);
12 INSERT INTO t1 VALUES (2);
13 INSERT INTO t2 VALUES (1,7),(2,7);
14
15 --echo # Subselect execute is traced every time it is executed
16 SET @@optimizer_trace_features="greedy_search=off,repeated_subselect=on";
17 SELECT (SELECT a FROM t1 WHERE t1.a=t2.a), a FROM t2;
18 --echo
19 --skip_if_hypergraph # Does not support the same optimizer trace.
20 SELECT * FROM information_schema.OPTIMIZER_TRACE;
21 --echo
22
23 --echo # Subselect execute is traced only the first time it is executed
24 SET @@optimizer_trace_features="greedy_search=off,repeated_subselect=off";
25 SELECT (SELECT a FROM t1 WHERE t1.a=t2.a), a FROM t2;
26 --echo
27 --skip_if_hypergraph # Does not support the same optimizer trace.
28 SELECT * FROM information_schema.OPTIMIZER_TRACE;
29 --echo
30
31 DROP TABLE t1,t2;
32 SET @@optimizer_trace_features="default";
33
34
35 CREATE TABLE t1 (a FLOAT(5,4) zerofill);
36 CREATE TABLE t2 (a FLOAT(5,4),b FLOAT(2,0));
37
38 # evaluate_subselect_cond_steps for build_equal_item()
39 SELECT t1.a
40 FROM t1
41 WHERE t1.a= (SELECT b FROM t2 LIMIT 1) AND NOT
42 t1.a= (SELECT a FROM t2 LIMIT 1) ;
43 --echo
44 --skip_if_hypergraph # Does not support the same optimizer trace.
45 SELECT * FROM information_schema.OPTIMIZER_TRACE;
46 --echo
47
48 # evaluate_subselect_cond_steps for remove_eq_conds
49 SELECT 1 FROM DUAL
50 WHERE NOT EXISTS
51 (SELECT * FROM t2 WHERE a = 50 AND b = 3);
52 --echo
53 --skip_if_hypergraph # Does not support the same optimizer trace.
54 SELECT * FROM information_schema.OPTIMIZER_TRACE;
55 --echo
56
57 # Distinct, order and group is removed from subquery. Note: For PS,
58 # removal happens during prepare so the only visible effect is that
59 # the subquery does not contain those clauses.
60 SELECT 1 FROM DUAL WHERE NOT EXISTS (SELECT DISTINCT(a) FROM t2 GROUP BY a ORDER BY b);
61 --echo
62 --skip_if_hypergraph # Does not support the same optimizer trace.
63 SELECT * FROM information_schema.OPTIMIZER_TRACE;
64 --echo
65
66 DROP TABLE t1,t2;
67
68 --echo #
69 --echo # BUG#12905521 - ASSERT IN OPT_TRACE_STMT::SYNTAX_ERROR ON SELECT
70 --echo # DISTINCT/MIN/JOIN/SUBQ QUERY
71 --echo #
72
73 CREATE TABLE t1 (
74 pk INTEGER,
75 col_int_nokey INTEGER,
76 col_int_key INTEGER,
77 col_varchar_key VARCHAR(1),
78 col_varchar_nokey VARCHAR(1),
79 PRIMARY KEY (pk),
80 KEY (col_varchar_key,col_int_key)
81 ) ENGINE=MYISAM;
82 CREATE TABLE t2 (
83 pk INTEGER,
84 col_int_nokey INTEGER,
85 col_int_key INTEGER,
86 col_varchar_key VARCHAR(1),
87 col_varchar_nokey VARCHAR(1),
88 PRIMARY KEY (pk),
89 KEY (col_varchar_key,col_int_key)
90 ) ENGINE=MYISAM;
91 CREATE TABLE t3 (
92 pk INTEGER,
93 col_int_nokey INTEGER,
94 col_int_key INTEGER,
95 col_time_key TIME,
96 col_datetime_nokey DATETIME,
97 col_varchar_key VARCHAR(1),
98 col_varchar_nokey VARCHAR(1),
99 PRIMARY KEY (pk),
100 KEY (col_time_key),
101 KEY (col_varchar_key,col_int_key)
102 ) ENGINE=MYISAM;
103 CREATE TABLE t4 (
104 pk INTEGER,
105 col_int_nokey INTEGER,
106 col_int_key INTEGER,
107 col_date_key DATE,
108 col_date_nokey DATE,
109 col_time_key TIME,
110 col_time_nokey TIME,
111 col_datetime_key DATETIME,
112 col_datetime_nokey DATETIME,
113 col_varchar_key VARCHAR(1),
114 col_varchar_nokey VARCHAR(1),
115 PRIMARY KEY (pk),
116 KEY (col_varchar_key,col_int_key)
117 ) ENGINE=MYISAM;
118 INSERT IGNORE INTO t4 (
119 col_int_key,col_int_nokey,
120 col_date_key,col_date_nokey,
121 col_time_key,col_time_nokey,
122 col_datetime_key,col_datetime_nokey,
123 col_varchar_key,col_varchar_nokey
124 ) VALUES
125 (8,7,'2008-10-02','2008-10-02','04:07:22.028954','04:07:22.028954','2001-10-08 00:00:00','2001-10-08 00:00:00','g','g');
126 CREATE TABLE t5 (
127 pk INTEGER AUTO_INCREMENT,
128 col_int_nokey INTEGER,
129 col_int_key INTEGER,
130 col_date_key DATE,
131 col_date_nokey DATE,
132 col_time_key TIME,
133 col_time_nokey TIME,
134 col_datetime_key DATETIME,
135 col_datetime_nokey DATETIME,
136 col_varchar_key VARCHAR(1),
137 col_varchar_nokey VARCHAR(1),
138 PRIMARY KEY (pk),
139 KEY (col_int_key),
140 KEY (col_varchar_key,col_int_key)
141 ) ENGINE=MYISAM;
142 INSERT INTO t5 (
143 col_int_key,col_int_nokey,
144 col_date_key,col_date_nokey,
145 col_time_key,col_time_nokey,
146 col_datetime_key,col_datetime_nokey,
147 col_varchar_key,col_varchar_nokey
148 ) VALUES
149 (8,NULL,'2000-12-03','2000-12-03','22:55:23.019225','22:55:23.019225','2005-07-20 00:00:00','2005-07-20 00:00:00','x','x'),
150 (7,8,'2008-05-03','2008-05-03','10:19:31.050677','10:19:31.050677','2007-10-06 17:56:40.056051','2007-10-06 17:56:40.056051','d','d'),
151 (8,6,'2000-09-20','2000-09-20','14:11:27.044095','14:11:27.044095','2003-06-13 23:19:49.018300','2003-06-13 23:19:49.018300','c','c');
152
153 set @old_opt_switch=@@optimizer_switch;
154 if (`select locate('semijoin', @@optimizer_switch) > 0`)
155 {
156 --disable_query_log
157 set optimizer_switch="semijoin=off";
158 --enable_query_log
159 }
160
161 select distinct
162 alias1.`col_varchar_key` as field1 ,alias1.`col_date_key` as
163 field2 ,( select min( sq1_alias1.`col_varchar_nokey` ) as sq1_field1 from ( t1
164 as sq1_alias1 inner join ( t5 as sq1_alias2 left join t5 as sq1_alias3 on
165 (sq1_alias3.`col_varchar_nokey` = sq1_alias2.`col_varchar_key` ) ) on
166 (sq1_alias3.`col_varchar_nokey` = sq1_alias2.`col_varchar_key` ) ) where
167 exists ( select distinct c_sq1_alias2.`col_int_nokey` as c_sq1_field1 from (
168 t3 as c_sq1_alias1 right join t4 as c_sq1_alias2 on (c_sq1_alias2.`col_int_nokey` = c_sq1_alias1.`pk` ) ) where
169 c_sq1_alias2.`col_varchar_key` = sq1_alias2.`col_varchar_nokey` ) ) as field3
170 ,( select max( sq2_alias1.`pk` ) as sq2_field1 from t5 as sq2_alias1 ) as
171 field4 ,alias2.`col_varchar_nokey` as field5 ,alias2.`col_varchar_nokey` as
172 field6 from ( t5 as alias1 right outer join ( ( ( select sq3_alias2.* from ( t5 as sq3_alias1 ,t4 as sq3_alias2 ) ) as alias2 right join t4
173 as alias3 on (alias3.`col_varchar_key` = alias2.`col_varchar_key` ) ) ) on
174 (alias3.`col_int_key` = alias2.`pk` ) ) where ( alias1.`col_varchar_nokey` in
175 ( select sq4_alias1.`col_varchar_key` as sq4_field1 from ( t3 as sq4_alias1
176 inner join ( t2 as sq4_alias2 right outer join t3 as sq4_alias3 on
177 (sq4_alias3.`pk` = sq4_alias2.`col_int_key` ) ) on
178 (sq4_alias3.`col_varchar_nokey` = sq4_alias2.`col_varchar_key` ) ) where
179 sq4_alias2.`col_int_key` < alias1.`col_int_nokey` and
180 sq4_alias3.`col_varchar_nokey` <> alias1.`col_varchar_key` ) ) and
181 alias1.`col_int_key` not in (214) group by field1,field2,field3,
182 field4,field5,field6;
183
184 --replace_regex /("sort_buffer_size":) [0-9]+/\1 "NNN"/
185 --skip_if_hypergraph # Does not support the same optimizer trace.
186 select * from information_schema.optimizer_trace;
187 set optimizer_switch=@old_opt_switch;
188 drop table t1,t2,t3,t4,t5;
189
190 --echo #
191 --echo # BUG#12905758 - ASSERT IN OPT_TRACE_STMT::SYNTAX_ERROR ON
192 --echo # SELECT/SUBQ/SUM QUERY
193 --echo #
194
195 CREATE TABLE t1 (
196 pk INTEGER AUTO_INCREMENT,
197 col_int_nokey INTEGER,
198 col_int_key INTEGER,
199 col_date_key DATE,
200 col_date_nokey DATE,
201 col_time_key TIME,
202 col_time_nokey TIME,
203 col_datetime_key DATETIME,
204 col_datetime_nokey DATETIME,
205 col_varchar_key VARCHAR(1),
206 col_varchar_nokey VARCHAR(1),
207 PRIMARY KEY (pk),
208 KEY (col_varchar_key,col_int_key)
209 ) ENGINE=MYISAM, CHARSET utf8mb4;
210 INSERT INTO t1 (
211 col_int_key,col_int_nokey,
212 col_date_key,col_date_nokey,
213 col_time_key,col_time_nokey,
214 col_datetime_key,col_datetime_nokey,
215 col_varchar_key,col_varchar_nokey
216 ) VALUES
217 (8,NULL,'2000-12-03','2000-12-03','22:55:23.019225','22:55:23.019225','2005-07-20 00:00:00','2005-07-20 00:00:00','x','x'),
218 (8,6,'2000-09-20','2000-09-20','14:11:27.044095','14:11:27.044095','2003-06-13 23:19:49.018300','2003-06-13 23:19:49.018300','c','c');
219 CREATE TABLE t2 (I INTEGER);
220
221 select ( select sum( subquery1_t1.`col_int_nokey` ) as subquery1_field1 from
222 t1 as subquery1_t1 ) as field1 from ( t1 as table1 straight_join t1 as table2
223 on (table2.`col_varchar_key` = table1.`col_varchar_key` ) ) where (
224 table2.`col_int_nokey` <> any ( select 5 from t2 ) ) and table1.`pk` in
225 (192,18) order by field1 desc;
226
227 --skip_if_hypergraph # Does not support the same optimizer trace.
228 select * from information_schema.optimizer_trace;
229 drop table t1,t2;
230
231 --echo
232 --echo #
233 --echo # Tracing of semijoin loosescan
234 --echo #
235
236 --source include/show_json_object.inc
237
238 create table t0 (a int);
239 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
240 create table t1 (a int, b int, filler char(100), key(a,b)) charset utf8mb4;
241 insert into t1 select A.a, B.a, 'filler' from t0 A, t0 B;
242 create table t2 as select * from t1;
243 set @old_opt_switch=@@optimizer_switch;
244 set optimizer_switch="firstmatch=off,materialization=off,duplicateweedout=off";
245 set @old_opt_prune_level=@@optimizer_prune_level;
246 set optimizer_prune_level=0;
247
248 explain select * from t2 where a in (select b from t1 where a=3);
249
250 --echo # Equality-propagation involving inner field => 1st sj equality is bound
251 explain select * from t2 where (b+0,a+0) in (select a,b from t1 where a=3);
252
253 --echo # Equality-propagation involving outer field => 3rd sj equality is bound.
254 explain select * from t2 where (b,a,filler) in (select a,b,a*3 from t1) and filler='abc';
255 # Show that LS is possible (even though too costly in the end):
256 --skip_if_hypergraph # Does not support the same optimizer trace.
257 SELECT show_json_object('"recalculate_access_paths_and_cost": {', TRACE)
258 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
259
260 --echo # Remove the condition on 'filler' => 3rd sj equality is not bound.
261 explain select * from t2 where (b,a,filler) in (select a,b,a*3 from t1);
262 # Show that LS is impossible:
263 --skip_if_hypergraph # Does not support the same optimizer trace.
264 SELECT show_json_object('"searching_loose_scan_index": {', TRACE)
265 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
266
267 --echo # Equality-propagation involving outer field => 3rd sj equality is bound.
268 explain select * from t2 as t3, t2
269 where t2.filler=t3.filler and
270 (t2.b,t2.a,t2.filler) in (select a,b,a*3 from t1);
271 --skip_if_hypergraph # Does not support the same optimizer trace.
272 SELECT show_json_object('"recalculate_access_paths_and_cost": {', TRACE)
273 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
274
275 --echo # In plan t3-t1-t2, 3rd outer expression is dependent only on
276 --echo # previous tables => 3rd sj equality is bound.
277 --echo # If t1 is before t3, 3rd sj equality is not bound.
278 explain select * from t2 as t3 left join t2 on t2.filler+10=t3.filler+20
279 where (t2.b,t2.a,t3.filler+2) in (select a,b,a*3 from t1);
280 # Show all LS plans (possible and impossible):
281 --skip_if_hypergraph # Does not support the same optimizer trace.
282 SELECT show_json_object('"considered_execution_plans": [', TRACE)
283 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
284
285 drop table t0,t1,t2;
286
287 --echo #
288 --echo # Discover bound equality thanks to equality propagation
289 --echo # specific of ON clause.
290 --echo #
291
292 CREATE TABLE t1 (
293 a int(11) DEFAULT NULL,
294 b varchar(100) DEFAULT NULL,
295 c int(11) DEFAULT NULL,
296 KEY b_c_a (b,c,a)
297 ) ENGINE=InnoDB, CHARSET utf8mb4;
298
299 explain select *
300 from t1 left join t1 as t2
301 on (t2.a= t1.a and (t2.a,t2.b) in (select a,b from t1 as t3))
302 where t1.a < 5;
303
304 drop table t1;
305
306 --echo #
307 --echo # Show that loosescan planning is not dependent on order of
308 --echo # creation of indexes anymore.
309 --echo #
310
311 create table it(a int, b int, index a_b (a,b), index a (a))
312 engine=InnoDB;
313 insert into it values(1,1),(2,3),(4,3);
314
315 let $query=
316 select * from it as ot
317 where (ot.a,ot.b) in (select it.a,it.b from it where it.b=3);
318 eval $query;
319
320 # The index on "A" is able to handle needed semi-join equalities,
321 # because it handles the first one, and the second one is bound due to
322 # b=3.
323
324 let $read_trace=
325 select TRACE LIKE '%"index_handles_needed_semijoin_equalities": false%'
326 from information_schema.optimizer_trace;
327 --skip_if_hypergraph # Does not support the same optimizer trace.
328 eval $read_trace;
329
330 # Switch order of indexes:
331 drop table it;
332 create table it(a int, b int, index a (a),index a_b (a,b))
333 engine=InnoDB;
334 insert into it values(1,1),(2,3),(4,3);
335
336 eval $query;
337 eval $read_trace;
338
339 drop table it;
340
341 --echo #
342 --echo # Show that we reject LooseScan if no handled key parts
343 --echo #
344
345 CREATE TABLE t1 (a INT, b INT, KEY (a)) ENGINE=INNODB;
346 CREATE TABLE t2 (a INT, b INT) ENGINE=INNODB;
347 EXPLAIN SELECT * FROM t2 AS t3, t2
348 WHERE t2.b=t3.b AND
349 (t2.b) IN (SELECT b*3 FROM t1 WHERE a=10);
350
351 --skip_if_hypergraph # Does not support the same optimizer trace.
352 SELECT TRACE LIKE '%"some_index_part_used": false%'
353 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
354
355 DROP TABLE t1,t2;
356
357 --echo #
358 --echo # Show that we detect a hole in sequence of key parts
359 --echo #
360
361 CREATE TABLE ot1 (a INTEGER);
362 INSERT INTO ot1 VALUES (0),(1),(3),(7);
363
364 CREATE TABLE it1 (a VARCHAR(1), b INTEGER, KEY (a,b)) CHARSET utf8mb4;
365 INSERT INTO it1 VALUES ('a',7), ('b',7);
366
367 CREATE TABLE it2 (a VARCHAR(1), b INTEGER, KEY (a,b)) CHARSET utf8mb4;
368 INSERT INTO it2 VALUES ('a',7), ('b',7);
369
370 let $query=
371 SELECT * FROM ot1
372 WHERE a IN (
373 SELECT it1.b
374 FROM it1 JOIN it2
375 ON it1.a = it2.a
376 );
377
378 eval explain $query;
379
380 --skip_if_hypergraph # Does not support the same optimizer trace.
381 SELECT TRACE LIKE '%"index_can_remove_duplicates": false%'
382 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
383
384 DROP TABLE ot1, it1, it2;
385
386 --echo #
387 --echo # Show that handled keyparts cannot be on prefix
388 --echo #
389
390 create table t1 (a int, b varchar(100), key a_b (a,b)) charset utf8mb4;
391 insert into t1 values(25,'111111'),(25,'1111112');
392 let $query=
393 select * from t1 as t2 where t2.b in (select b from t1 where a=25);
394 eval explain $query;
395 eval $query;
396
397 alter table t1 drop key a_b, add key a_b_prefix (a,b(2));
398 eval explain $query;
399 # If we had not detected that key is on prefix, result would be only 1 row:
400 eval $query;
401 --skip_if_hypergraph # Does not support the same optimizer trace.
402 select TRACE LIKE '%"index_handles_needed_semijoin_equalities": false%'
403 from information_schema.optimizer_trace;
404
405 drop table t1;
406
407 set optimizer_switch=@old_opt_switch;
408 set optimizer_prune_level=@old_opt_prune_level;
409
410 --echo # Tracing of CTEs using the same tmp table: observe
411 --echo # "reusing_tmp_table" and only one "creating_tmp_table".
412 with q(a) as (select 1 union all select 2)
413 select * from q, q q1 where q.a=1 and q1.a=2;
414 --skip_if_hypergraph # Does not support the same optimizer trace.
415 select TRACE from information_schema.optimizer_trace;
416
417 --echo # Tracing of repetitions of the recursive member
418 --echo # can be silenced with repeated_subselect=off
419
420 let $query=
421 with recursive qn(n) as
422 (select 1 union all select n+1 from qn where n<4)
423 select count(*) from qn;
424 eval $query;
425 --skip_if_hypergraph # Does not support the same optimizer trace.
426 SELECT show_json_object('"join_execution": {', TRACE)
427 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
428 SET @@optimizer_trace_features="repeated_subselect=off";
429 eval $query;
430 --skip_if_hypergraph # Does not support the same optimizer trace.
431 SELECT show_json_object('"join_execution": {', TRACE)
432 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
433 SET @@optimizer_trace_features=default;
434
435 drop function show_json_object;