Debian Codegraph @

mysql-test/suite/opt_trace/r/bugs_ps_prot_all.result

raw
1 set optimizer_switch='semijoin=on,materialization=on,firstmatch=on,loosescan=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=off';
2 set optimizer_trace_max_mem_size=1048576;
3 SET end_markers_in_json=on;
4 SET optimizer_trace="enabled=on,one_line=off";
5 #
6 # BUG#12430646 - SEL_ARG::LEFT AND RIGHT POINTERS INCORRECTLY
7 # USED. CRASHES OPTIMIZER TRACING
8 #
9 CREATE TABLE t1 (
10 a INT,
11 b CHAR(2),
12 c INT,
13 d INT,
14 KEY (c),
15 KEY (d,a,b(2)),
16 KEY (b(1))
17 );
18 INSERT INTO t1 VALUES (NULL, 'a', 1, 2), (NULL, 'a', 1, 2),
19 (1, 'a', 1, 2), (1, 'a', 1, 2);
20 CREATE TABLE t2 (
21 a INT,
22 c INT,
23 e INT,
24 KEY (e)
25 );
26 INSERT INTO t2 VALUES (1, 1, NULL), (1, 1, NULL);
27 SELECT 1
28 FROM t1, t2
29 WHERE t1.d <> '1' AND t1.b > '1'
30 AND t1.a = t2.a AND t1.c = t2.c;
31 1
32 1
33 1
34 1
35 1
36 DROP TABLE t1, t2;
37 #
38 # BUG#12595210 - JSON SYNTAX ERROR ASSERT ON WHERE FIELD NOT IN SUBQUERY
39 #
40 CREATE TABLE t1 (
41 pk INT NOT NULL AUTO_INCREMENT,
42 col_int_key INT DEFAULT NULL,
43 col_varchar_key VARCHAR(1) DEFAULT NULL,
44 PRIMARY KEY (pk)
45 ) ENGINE=InnoDB, CHARSET utf8mb4;
46 CREATE TABLE t2 (
47 pk INT NOT NULL AUTO_INCREMENT,
48 col_int_key INT DEFAULT NULL,
49 col_varchar_key VARCHAR(1) DEFAULT NULL,
50 col_varchar_nokey VARCHAR(1) DEFAULT NULL,
51 PRIMARY KEY (pk)
52 ) ENGINE=InnoDB, CHARSET utf8mb4;
53 CREATE TABLE t3 (
54 pk INT NOT NULL AUTO_INCREMENT,
55 col_int_key INT DEFAULT NULL,
56 col_varchar_key VARCHAR(1) DEFAULT NULL,
57 col_varchar_nokey VARCHAR(1) DEFAULT NULL,
58 PRIMARY KEY (pk)
59 ) ENGINE=InnoDB, CHARSET utf8mb4;
60 CREATE TABLE t4 (
61 pk INT NOT NULL AUTO_INCREMENT,
62 col_int_key INT DEFAULT NULL,
63 col_varchar_key VARCHAR(1) DEFAULT NULL,
64 PRIMARY KEY (pk)
65 ) ENGINE=InnoDB, CHARSET utf8mb4;
66 CREATE VIEW view_t4 AS SELECT * FROM t4;
67 CREATE TABLE where_subselect_19379 SELECT
68 (
69 SELECT SUM(sq1_alias1.pk) AS sq1_field1
70 FROM view_t4 AS sq1_alias1
71 INNER JOIN t1 AS sq1_alias2 ON (sq1_alias2.col_varchar_key =
72 sq1_alias1.col_varchar_key )
73 ) AS field1,
74 alias1.col_varchar_nokey AS field2
75 FROM (t2 AS alias1
76 LEFT JOIN ( t2 AS alias2
77 LEFT OUTER JOIN t2 AS alias3 ON (alias3.col_varchar_nokey =
78 alias2.col_varchar_key )
79 ) ON (alias3.col_varchar_key = alias2.col_varchar_key)
80 )
81 WHERE ( alias2.col_varchar_key IN (
82 SELECT sq2_alias1.col_varchar_nokey AS sq2_field1
83 FROM t3 AS sq2_alias1
84 WHERE sq2_alias1.col_varchar_nokey <= alias1.col_varchar_key
85 )
86 );
87 SELECT * FROM where_subselect_19379 WHERE (field1, field2) NOT IN
88 (SELECT
89 (
90 SELECT SUM(sq1_alias1.pk) AS sq1_field1
91 FROM view_t4 AS sq1_alias1
92 INNER JOIN t1 AS sq1_alias2 ON (sq1_alias2.col_varchar_key =
93 sq1_alias1.col_varchar_key )
94 ) AS field1,
95 alias1.col_varchar_nokey AS field2
96 FROM (t2 AS alias1
97 LEFT JOIN ( t2 AS alias2
98 LEFT OUTER JOIN t2 AS alias3 ON (alias3.col_varchar_nokey =
99 alias2.col_varchar_key )
100 ) ON (alias3.col_varchar_key = alias2.col_varchar_key)
101 )
102 WHERE ( alias2.col_varchar_key IN (
103 SELECT sq2_alias1.col_varchar_nokey AS sq2_field1
104 FROM t3 AS sq2_alias1
105 WHERE sq2_alias1.col_varchar_nokey <= alias1.col_varchar_key
106 )
107 ));
108 field1 field2
109 select * from information_schema.OPTIMIZER_TRACE;
110 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
111 SELECT * FROM where_subselect_19379 WHERE (field1, field2) NOT IN
112 (SELECT
113 (
114 SELECT SUM(sq1_alias1.pk) AS sq1_field1
115 FROM view_t4 AS sq1_alias1
116 INNER JOIN t1 AS sq1_alias2 ON (sq1_alias2.col_varchar_key =
117 sq1_alias1.col_varchar_key )
118 ) AS field1,
119 alias1.col_varchar_nokey AS field2
120 FROM (t2 AS alias1
121 LEFT JOIN ( t2 AS alias2
122 LEFT OUTER JOIN t2 AS alias3 ON (alias3.col_varchar_nokey =
123 alias2.col_varchar_key )
124 ) ON (alias3.col_varchar_key = alias2.col_varchar_key)
125 )
126 WHERE ( alias2.col_varchar_key IN (
127 SELECT sq2_alias1.col_varchar_nokey AS sq2_field1
128 FROM t3 AS sq2_alias1
129 WHERE sq2_alias1.col_varchar_nokey <= alias1.col_varchar_key
130 )
131 )) {
132 "steps": [
133 {
134 "join_optimization": {
135 "select#": 1,
136 "steps": [
137 {
138 "condition_processing": {
139 "condition": "WHERE",
140 "original_condition": "<in_optimizer>((`where_subselect_19379`.`field1`,`where_subselect_19379`.`field2`),<exists>(/* select#2 */ select (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)) AS `field1`,`alias1`.`col_varchar_nokey` AS `field2` from `t2` `alias1` join `t2` `alias2` join `t2` `alias3` semi join (`t3` `sq2_alias1`) where (<if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field1`) = (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))) or ((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)) is null)), true) and <if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field2`) = `alias1`.`col_varchar_nokey`) or (`alias1`.`col_varchar_nokey` is null)), true) and (`sq2_alias1`.`col_varchar_nokey` <= `alias1`.`col_varchar_key`) and (`alias2`.`col_varchar_key` = `sq2_alias1`.`col_varchar_nokey`) and (`alias3`.`col_varchar_key` = `alias2`.`col_varchar_key`) and (`alias3`.`col_varchar_nokey` = `alias2`.`col_varchar_key`)) having (<if>(outer_field_is_not_null, <is_not_null_test>((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`alias1`.`col_varchar_nokey`), true))) is false)",
141 "steps": [
142 {
143 "transformation": "equality_propagation",
144 "subselect_evaluation": [
145 ] /* subselect_evaluation */,
146 "resulting_condition": "<in_optimizer>((`where_subselect_19379`.`field1`,`where_subselect_19379`.`field2`),<exists>(/* select#2 */ select (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)) AS `field1`,`alias1`.`col_varchar_nokey` AS `field2` from `t2` `alias1` join `t2` `alias2` join `t2` `alias3` semi join (`t3` `sq2_alias1`) where (<if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field1`) = (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))) or ((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)) is null)), true) and <if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field2`) = `alias1`.`col_varchar_nokey`) or (`alias1`.`col_varchar_nokey` is null)), true) and (`sq2_alias1`.`col_varchar_nokey` <= `alias1`.`col_varchar_key`) and (`alias2`.`col_varchar_key` = `sq2_alias1`.`col_varchar_nokey`) and (`alias3`.`col_varchar_key` = `alias2`.`col_varchar_key`) and (`alias3`.`col_varchar_nokey` = `alias2`.`col_varchar_key`)) having (<if>(outer_field_is_not_null, <is_not_null_test>((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`alias1`.`col_varchar_nokey`), true))) is false)"
147 },
148 {
149 "transformation": "constant_propagation",
150 "subselect_evaluation": [
151 ] /* subselect_evaluation */,
152 "resulting_condition": "<in_optimizer>((`where_subselect_19379`.`field1`,`where_subselect_19379`.`field2`),<exists>(/* select#2 */ select (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)) AS `field1`,`alias1`.`col_varchar_nokey` AS `field2` from `t2` `alias1` join `t2` `alias2` join `t2` `alias3` semi join (`t3` `sq2_alias1`) where (<if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field1`) = (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))) or ((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)) is null)), true) and <if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field2`) = `alias1`.`col_varchar_nokey`) or (`alias1`.`col_varchar_nokey` is null)), true) and (`sq2_alias1`.`col_varchar_nokey` <= `alias1`.`col_varchar_key`) and (`alias2`.`col_varchar_key` = `sq2_alias1`.`col_varchar_nokey`) and (`alias3`.`col_varchar_key` = `alias2`.`col_varchar_key`) and (`alias3`.`col_varchar_nokey` = `alias2`.`col_varchar_key`)) having (<if>(outer_field_is_not_null, <is_not_null_test>((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`alias1`.`col_varchar_nokey`), true))) is false)"
153 },
154 {
155 "transformation": "trivial_condition_removal",
156 "subselect_evaluation": [
157 ] /* subselect_evaluation */,
158 "resulting_condition": "<in_optimizer>((`where_subselect_19379`.`field1`,`where_subselect_19379`.`field2`),<exists>(/* select#2 */ select (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)) AS `field1`,`alias1`.`col_varchar_nokey` AS `field2` from `t2` `alias1` join `t2` `alias2` join `t2` `alias3` semi join (`t3` `sq2_alias1`) where (<if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field1`) = (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))) or ((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)) is null)), true) and <if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field2`) = `alias1`.`col_varchar_nokey`) or (`alias1`.`col_varchar_nokey` is null)), true) and (`sq2_alias1`.`col_varchar_nokey` <= `alias1`.`col_varchar_key`) and (`alias2`.`col_varchar_key` = `sq2_alias1`.`col_varchar_nokey`) and (`alias3`.`col_varchar_key` = `alias2`.`col_varchar_key`) and (`alias3`.`col_varchar_nokey` = `alias2`.`col_varchar_key`)) having (<if>(outer_field_is_not_null, <is_not_null_test>((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`alias1`.`col_varchar_nokey`), true))) is false)"
159 }
160 ] /* steps */
161 } /* condition_processing */
162 },
163 {
164 "substitute_generated_columns": {
165 } /* substitute_generated_columns */
166 },
167 {
168 "table_dependencies": [
169 {
170 "table": "`where_subselect_19379`",
171 "row_may_be_null": false,
172 "map_bit": 0,
173 "depends_on_map_bits": [
174 ] /* depends_on_map_bits */
175 }
176 ] /* table_dependencies */
177 },
178 {
179 "ref_optimizer_key_uses": [
180 ] /* ref_optimizer_key_uses */
181 },
182 {
183 "rows_estimation": [
184 {
185 "table": "`where_subselect_19379`",
186 "rows": 1,
187 "cost": 1,
188 "table_type": "system",
189 "empty": true
190 }
191 ] /* rows_estimation */
192 }
193 ] /* steps */,
194 "empty_result": {
195 "cause": "no matching row in const table"
196 } /* empty_result */
197 } /* join_optimization */
198 },
199 {
200 "join_execution": {
201 "select#": 1,
202 "steps": [
203 ] /* steps */
204 } /* join_execution */
205 }
206 ] /* steps */
207 } 0 0
208 drop table t1,t2,t3,t4,where_subselect_19379;
209 drop view view_t4;
210 #
211 # BUG#12607524 JSON PARSE ERROR ON SELECT ... FROM ... WHERE .. IN (SUBQUERY)
212 #
213 CREATE TABLE t1 (
214 col_int_key int(11) DEFAULT NULL,
215 col_varchar_key varchar(1) DEFAULT NULL,
216 KEY col_int_key (col_int_key),
217 KEY col_varchar_key (col_varchar_key,col_int_key)
218 ) ENGINE=MyISAM;
219 Warnings:
220 Warning 1681 Integer display width is deprecated and will be removed in a future release.
221 INSERT INTO t1 VALUES (8,'g');
222 CREATE TABLE t2 (
223 col_int_key int(11) DEFAULT NULL,
224 col_varchar_key varchar(1) DEFAULT NULL,
225 KEY col_int_key (col_int_key),
226 KEY col_varchar_key (col_varchar_key,col_int_key)
227 ) ENGINE=MyISAM;
228 Warnings:
229 Warning 1681 Integer display width is deprecated and will be removed in a future release.
230 INSERT INTO t2 VALUES (7,'x');
231 CREATE TABLE where_subselect_19033
232 SELECT
233 ( SELECT col_int_key FROM t2 ) as field1
234 FROM t1
235 ;
236 SELECT * FROM where_subselect_19033;
237 field1
238 7
239 SELECT field1
240 FROM where_subselect_19033
241 WHERE field1 IN
242 ( SELECT
243 ( SELECT col_int_key FROM t2 )
244 FROM t1
245 )
246 ;
247 field1
248 7
249 SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
250 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
251 SELECT field1
252 FROM where_subselect_19033
253 WHERE field1 IN
254 ( SELECT
255 ( SELECT col_int_key FROM t2 )
256 FROM t1
257 ) {
258 "steps": [
259 {
260 "join_optimization": {
261 "select#": 1,
262 "steps": [
263 {
264 "condition_processing": {
265 "condition": "WHERE",
266 "original_condition": "((`where_subselect_19033`.`field1` = (/* select#3 */ select `t2`.`col_int_key` from `t2`)))",
267 "steps": [
268 {
269 "transformation": "equality_propagation",
270 "subselect_evaluation": [
271 ] /* subselect_evaluation */,
272 "resulting_condition": "(multiple equal((/* select#3 */ select `t2`.`col_int_key` from `t2`), `where_subselect_19033`.`field1`))"
273 },
274 {
275 "transformation": "constant_propagation",
276 "resulting_condition": "(multiple equal((/* select#3 */ select `t2`.`col_int_key` from `t2`), `where_subselect_19033`.`field1`))"
277 },
278 {
279 "transformation": "trivial_condition_removal",
280 "resulting_condition": "multiple equal((/* select#3 */ select `t2`.`col_int_key` from `t2`), `where_subselect_19033`.`field1`)"
281 }
282 ] /* steps */
283 } /* condition_processing */
284 },
285 {
286 "substitute_generated_columns": {
287 } /* substitute_generated_columns */
288 },
289 {
290 "table_dependencies": [
291 {
292 "table": "`where_subselect_19033`",
293 "row_may_be_null": false,
294 "map_bit": 0,
295 "depends_on_map_bits": [
296 ] /* depends_on_map_bits */
297 },
298 {
299 "table": "`t1`",
300 "row_may_be_null": false,
301 "map_bit": 1,
302 "depends_on_map_bits": [
303 ] /* depends_on_map_bits */
304 }
305 ] /* table_dependencies */
306 },
307 {
308 "ref_optimizer_key_uses": [
309 ] /* ref_optimizer_key_uses */
310 },
311 {
312 "pulled_out_semijoin_tables": [
313 ] /* pulled_out_semijoin_tables */
314 },
315 {
316 "subselect_execution": {
317 "select#": 3,
318 "steps": [
319 {
320 "join_optimization": {
321 "select#": 3,
322 "steps": [
323 {
324 "table_dependencies": [
325 {
326 "table": "`t2`",
327 "row_may_be_null": false,
328 "map_bit": 0,
329 "depends_on_map_bits": [
330 ] /* depends_on_map_bits */
331 }
332 ] /* table_dependencies */
333 },
334 {
335 "rows_estimation": [
336 {
337 "table": "`t2`",
338 "rows": 1,
339 "cost": 1,
340 "table_type": "system",
341 "empty": false
342 }
343 ] /* rows_estimation */
344 },
345 {
346 "attaching_conditions_to_tables": {
347 "original_condition": null,
348 "attached_conditions_computation": [
349 ] /* attached_conditions_computation */,
350 "attached_conditions_summary": [
351 ] /* attached_conditions_summary */
352 } /* attaching_conditions_to_tables */
353 },
354 {
355 "refine_plan": [
356 ] /* refine_plan */
357 }
358 ] /* steps */
359 } /* join_optimization */
360 },
361 {
362 "join_execution": {
363 "select#": 3,
364 "steps": [
365 ] /* steps */
366 } /* join_execution */
367 }
368 ] /* steps */
369 } /* subselect_execution */
370 },
371 {
372 "rows_estimation": [
373 {
374 "table": "`where_subselect_19033`",
375 "rows": 1,
376 "cost": 1,
377 "table_type": "system",
378 "empty": false
379 },
380 {
381 "table": "`t1`",
382 "table_scan": {
383 "rows": 1,
384 "cost": 0.501221
385 } /* table_scan */
386 }
387 ] /* rows_estimation */
388 },
389 {
390 "execution_plan_for_potential_materialization": {
391 "steps": [
392 {
393 "considered_execution_plans": [
394 {
395 "plan_prefix": [
396 ] /* plan_prefix */,
397 "table": "`t1`",
398 "best_access_path": {
399 "considered_access_paths": [
400 {
401 "rows_to_scan": 1,
402 "access_type": "scan",
403 "resulting_rows": 1,
404 "cost": 0.601221,
405 "chosen": true
406 }
407 ] /* considered_access_paths */
408 } /* best_access_path */,
409 "condition_filtering_pct": 100,
410 "rows_for_plan": 1,
411 "cost_for_plan": 0.601221,
412 "chosen": true
413 }
414 ] /* considered_execution_plans */
415 }
416 ] /* steps */
417 } /* execution_plan_for_potential_materialization */
418 },
419 {
420 "considered_execution_plans": [
421 {
422 "plan_prefix": [
423 "`where_subselect_19033`"
424 ] /* plan_prefix */,
425 "table": "`t1`",
426 "best_access_path": {
427 "considered_access_paths": [
428 {
429 "rows_to_scan": 1,
430 "access_type": "scan",
431 "resulting_rows": 1,
432 "cost": 0.601221,
433 "chosen": true
434 }
435 ] /* considered_access_paths */
436 } /* best_access_path */,
437 "condition_filtering_pct": 100,
438 "rows_for_plan": 1,
439 "cost_for_plan": 0.601221,
440 "semijoin_strategy_choice": [
441 {
442 "strategy": "FirstMatch",
443 "recalculate_access_paths_and_cost": {
444 "tables": [
445 ] /* tables */
446 } /* recalculate_access_paths_and_cost */,
447 "cost": 0.601221,
448 "rows": 1,
449 "chosen": true
450 },
451 {
452 "strategy": "MaterializeLookup",
453 "cost": 1.80122,
454 "rows": 1,
455 "duplicate_tables_left": false,
456 "chosen": false
457 },
458 {
459 "strategy": "DuplicatesWeedout",
460 "cost": 1.80122,
461 "rows": 1,
462 "duplicate_tables_left": false,
463 "chosen": false
464 }
465 ] /* semijoin_strategy_choice */,
466 "chosen": true
467 },
468 {
469 "final_semijoin_strategy": "FirstMatch",
470 "recalculate_access_paths_and_cost": {
471 "tables": [
472 ] /* tables */
473 } /* recalculate_access_paths_and_cost */
474 }
475 ] /* considered_execution_plans */
476 },
477 {
478 "condition_on_constant_tables": "true",
479 "condition_value": true
480 },
481 {
482 "attaching_conditions_to_tables": {
483 "original_condition": "true",
484 "attached_conditions_computation": [
485 ] /* attached_conditions_computation */,
486 "attached_conditions_summary": [
487 {
488 "table": "`t1`",
489 "attached": null
490 }
491 ] /* attached_conditions_summary */
492 } /* attaching_conditions_to_tables */
493 },
494 {
495 "finalizing_table_conditions": [
496 ] /* finalizing_table_conditions */
497 },
498 {
499 "refine_plan": [
500 {
501 "table": "`t1`"
502 }
503 ] /* refine_plan */
504 }
505 ] /* steps */
506 } /* join_optimization */
507 },
508 {
509 "join_execution": {
510 "select#": 1,
511 "steps": [
512 ] /* steps */
513 } /* join_execution */
514 }
515 ] /* steps */
516 } 0 0
517 DROP TABLE where_subselect_19033,t1,t2;
518
519 # BUG#12612201 - SEGFAULT IN
520 # SUBSELECT_UNIQUESUBQUERY_ENGINE::PRINT WITH OPTIMIZER TRACE
521
522 CREATE TABLE t1 (
523 col_int_key int(11) DEFAULT NULL,
524 col_varchar_key varchar(1) DEFAULT NULL,
525 col_varchar_nokey varchar(1) DEFAULT NULL
526 );
527 Warnings:
528 Warning 1681 Integer display width is deprecated and will be removed in a future release.
529 CREATE TABLE t2 (
530 pk int(11) NOT NULL AUTO_INCREMENT,
531 col_int_key int(11) DEFAULT NULL,
532 col_varchar_key varchar(1) DEFAULT NULL,
533 col_varchar_nokey varchar(1) DEFAULT NULL,
534 PRIMARY KEY (pk)
535 );
536 Warnings:
537 Warning 1681 Integer display width is deprecated and will be removed in a future release.
538 Warning 1681 Integer display width is deprecated and will be removed in a future release.
539 INSERT INTO t2 VALUES (1,4,'v','v'),(20,5,'r','r');
540 CREATE TABLE t3 (
541 col_int_key int(11) DEFAULT NULL,
542 col_varchar_key varchar(1) DEFAULT NULL,
543 col_varchar_nokey varchar(1) DEFAULT NULL
544 );
545 Warnings:
546 Warning 1681 Integer display width is deprecated and will be removed in a future release.
547 INSERT INTO t3 VALUES (NULL,'j','j'),(8,'c','c');
548 CREATE TABLE where_updatedelete_20769 select count( alias2 . col_varchar_key ) as field1
549 from (
550 (select sq1_alias1 . *
551 from ( t3 as sq1_alias1
552 straight_join t1 as sq1_alias2
553 on (sq1_alias2 . col_varchar_key = sq1_alias1 . col_varchar_key)
554 )
555 where sq1_alias1 . col_int_key in (
556 select c_sq1_alias1 . pk as c_sq1_field1
557 from t2 as c_sq1_alias1
558 )
559 ) as alias1
560 left outer join t1 as alias2
561 on (alias2 . col_varchar_key = alias1 . col_varchar_key )
562 )
563 where ( alias2 . col_varchar_key in (
564 select sq2_alias1 . col_varchar_nokey as sq2_field1
565 from t2 as sq2_alias1
566 where sq2_alias1 . col_int_key in (
567 select distinct c_sq2_alias1 . col_int_key as c_sq2_field1
568 from t3 as c_sq2_alias1
569 )
570 ) )
571 or alias1 . col_int_key = 2
572 and alias2 . col_varchar_nokey <= alias1 . col_varchar_nokey
573 order by alias1 . col_varchar_key , field1
574 ;
575 UPDATE where_updatedelete_20769 SET field1 = ( select count( alias2 . col_varchar_key ) as field1
576 from (
577 (select sq1_alias1 . *
578 from ( t3 as sq1_alias1
579 straight_join t1 as sq1_alias2
580 on (sq1_alias2 . col_varchar_key = sq1_alias1 . col_varchar_key)
581 )
582 where sq1_alias1 . col_int_key in (
583 select c_sq1_alias1 . pk as c_sq1_field1
584 from t2 as c_sq1_alias1
585 )
586 ) as alias1
587 left outer join t1 as alias2
588 on (alias2 . col_varchar_key = alias1 . col_varchar_key )
589 )
590 where ( alias2 . col_varchar_key in (
591 select sq2_alias1 . col_varchar_nokey as sq2_field1
592 from t2 as sq2_alias1
593 where sq2_alias1 . col_int_key in (
594 select distinct c_sq2_alias1 . col_int_key as c_sq2_field1
595 from t3 as c_sq2_alias1
596 )
597 ) )
598 or alias1 . col_int_key = 2
599 and alias2 . col_varchar_nokey <= alias1 . col_varchar_nokey
600 order by alias1 . col_varchar_key , field1
601 );
602 DROP TABLE where_updatedelete_20769;
603 DROP TABLE t1,t2,t3;
604
605 # BUG#12710761 - INVALID JSON TRACE ON SUBQUERY IN IN-CLAUSE
606
607 CREATE TABLE t1 (col_int_key int, KEY col_int_key (col_int_key));
608 INSERT INTO t1 VALUES (0),(8),(1),(8);
609 CREATE TABLE where_subselect_20070
610 SELECT table2 .col_int_key AS field1,
611 ( SELECT COUNT( col_int_key )
612 FROM t1
613 )
614 FROM t1 AS table1
615 JOIN t1 AS table2
616 ON table2 .col_int_key = table1 .col_int_key;
617 SELECT *
618 FROM where_subselect_20070
619 WHERE (field1, ( SELECT COUNT( col_int_key ) FROM t1 )) IN (
620 SELECT table2 .col_int_key AS field1,
621 ( SELECT COUNT( col_int_key )
622 FROM t1
623 )
624 FROM t1 AS table1
625 JOIN t1 AS table2
626 ON table2 .col_int_key = table1 .col_int_key
627 );
628 field1 ( SELECT COUNT( col_int_key )
629 )
630 0 4
631 1 4
632 8 4
633 8 4
634 8 4
635 8 4
636 FROM t1
637 select * from information_schema.optimizer_trace;
638 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
639 SELECT *
640 FROM where_subselect_20070
641 WHERE (field1, ( SELECT COUNT( col_int_key ) FROM t1 )) IN (
642 SELECT table2 .col_int_key AS field1,
643 ( SELECT COUNT( col_int_key )
644 FROM t1
645 )
646 FROM t1 AS table1
647 JOIN t1 AS table2
648 ON table2 .col_int_key = table1 .col_int_key
649 ) {
650 "steps": [
651 {
652 "join_optimization": {
653 "select#": 1,
654 "steps": [
655 {
656 "condition_processing": {
657 "condition": "WHERE",
658 "original_condition": "((`where_subselect_20070`.`field1` = `table2`.`col_int_key`) and ((/* select#2 */ select count(`t1`.`col_int_key`) from `t1`) = (/* select#4 */ select count(`t1`.`col_int_key`) from `t1`)) and (`table2`.`col_int_key` = `table1`.`col_int_key`))",
659 "steps": [
660 {
661 "transformation": "equality_propagation",
662 "subselect_evaluation": [
663 ] /* subselect_evaluation */,
664 "resulting_condition": "(((/* select#2 */ select count(`t1`.`col_int_key`) from `t1`) = (/* select#4 */ select count(`t1`.`col_int_key`) from `t1`)) and multiple equal(`where_subselect_20070`.`field1`, `table2`.`col_int_key`, `table1`.`col_int_key`))"
665 },
666 {
667 "transformation": "constant_propagation",
668 "subselect_evaluation": [
669 ] /* subselect_evaluation */,
670 "resulting_condition": "(((/* select#2 */ select count(`t1`.`col_int_key`) from `t1`) = (/* select#4 */ select count(`t1`.`col_int_key`) from `t1`)) and multiple equal(`where_subselect_20070`.`field1`, `table2`.`col_int_key`, `table1`.`col_int_key`))"
671 },
672 {
673 "transformation": "trivial_condition_removal",
674 "subselect_evaluation": [
675 {
676 "subselect_execution": {
677 "select#": 2,
678 "steps": [
679 {
680 "join_optimization": {
681 "select#": 2,
682 "steps": [
683 {
684 "table_dependencies": [
685 {
686 "table": "`t1`",
687 "row_may_be_null": false,
688 "map_bit": 0,
689 "depends_on_map_bits": [
690 ] /* depends_on_map_bits */
691 }
692 ] /* table_dependencies */
693 },
694 {
695 "rows_estimation": [
696 {
697 "table": "`t1`",
698 "table_scan": {
699 "rows": 4,
700 "cost": 0.501709
701 } /* table_scan */
702 }
703 ] /* rows_estimation */
704 },
705 {
706 "considered_execution_plans": [
707 {
708 "plan_prefix": [
709 ] /* plan_prefix */,
710 "table": "`t1`",
711 "best_access_path": {
712 "considered_access_paths": [
713 {
714 "rows_to_scan": 4,
715 "access_type": "scan",
716 "resulting_rows": 4,
717 "cost": 0.901709,
718 "chosen": true
719 }
720 ] /* considered_access_paths */
721 } /* best_access_path */,
722 "condition_filtering_pct": 100,
723 "rows_for_plan": 4,
724 "cost_for_plan": 0.901709,
725 "chosen": true
726 }
727 ] /* considered_execution_plans */
728 },
729 {
730 "attaching_conditions_to_tables": {
731 "original_condition": null,
732 "attached_conditions_computation": [
733 ] /* attached_conditions_computation */,
734 "attached_conditions_summary": [
735 {
736 "table": "`t1`",
737 "attached": null
738 }
739 ] /* attached_conditions_summary */
740 } /* attaching_conditions_to_tables */
741 },
742 {
743 "optimizing_distinct_group_by_order_by": {
744 } /* optimizing_distinct_group_by_order_by */
745 },
746 {
747 "finalizing_table_conditions": [
748 ] /* finalizing_table_conditions */
749 },
750 {
751 "refine_plan": [
752 {
753 "table": "`t1`"
754 }
755 ] /* refine_plan */
756 },
757 {
758 "considering_tmp_tables": [
759 ] /* considering_tmp_tables */
760 }
761 ] /* steps */
762 } /* join_optimization */
763 },
764 {
765 "join_execution": {
766 "select#": 2,
767 "steps": [
768 ] /* steps */
769 } /* join_execution */
770 }
771 ] /* steps */
772 } /* subselect_execution */
773 },
774 {
775 "subselect_execution": {
776 "select#": 4,
777 "steps": [
778 {
779 "join_optimization": {
780 "select#": 4,
781 "steps": [
782 {
783 "table_dependencies": [
784 {
785 "table": "`t1`",
786 "row_may_be_null": false,
787 "map_bit": 0,
788 "depends_on_map_bits": [
789 ] /* depends_on_map_bits */
790 }
791 ] /* table_dependencies */
792 },
793 {
794 "rows_estimation": [
795 {
796 "table": "`t1`",
797 "table_scan": {
798 "rows": 4,
799 "cost": 0.501709
800 } /* table_scan */
801 }
802 ] /* rows_estimation */
803 },
804 {
805 "considered_execution_plans": [
806 {
807 "plan_prefix": [
808 ] /* plan_prefix */,
809 "table": "`t1`",
810 "best_access_path": {
811 "considered_access_paths": [
812 {
813 "rows_to_scan": 4,
814 "access_type": "scan",
815 "resulting_rows": 4,
816 "cost": 0.901709,
817 "chosen": true
818 }
819 ] /* considered_access_paths */
820 } /* best_access_path */,
821 "condition_filtering_pct": 100,
822 "rows_for_plan": 4,
823 "cost_for_plan": 0.901709,
824 "chosen": true
825 }
826 ] /* considered_execution_plans */
827 },
828 {
829 "attaching_conditions_to_tables": {
830 "original_condition": null,
831 "attached_conditions_computation": [
832 ] /* attached_conditions_computation */,
833 "attached_conditions_summary": [
834 {
835 "table": "`t1`",
836 "attached": null
837 }
838 ] /* attached_conditions_summary */
839 } /* attaching_conditions_to_tables */
840 },
841 {
842 "optimizing_distinct_group_by_order_by": {
843 } /* optimizing_distinct_group_by_order_by */
844 },
845 {
846 "finalizing_table_conditions": [
847 ] /* finalizing_table_conditions */
848 },
849 {
850 "refine_plan": [
851 {
852 "table": "`t1`"
853 }
854 ] /* refine_plan */
855 },
856 {
857 "considering_tmp_tables": [
858 ] /* considering_tmp_tables */
859 }
860 ] /* steps */
861 } /* join_optimization */
862 },
863 {
864 "join_execution": {
865 "select#": 4,
866 "steps": [
867 ] /* steps */
868 } /* join_execution */
869 }
870 ] /* steps */
871 } /* subselect_execution */
872 }
873 ] /* subselect_evaluation */,
874 "resulting_condition": "multiple equal(`where_subselect_20070`.`field1`, `table2`.`col_int_key`, `table1`.`col_int_key`)"
875 }
876 ] /* steps */
877 } /* condition_processing */
878 },
879 {
880 "substitute_generated_columns": {
881 } /* substitute_generated_columns */
882 },
883 {
884 "table_dependencies": [
885 {
886 "table": "`where_subselect_20070`",
887 "row_may_be_null": false,
888 "map_bit": 0,
889 "depends_on_map_bits": [
890 ] /* depends_on_map_bits */
891 },
892 {
893 "table": "`t1` `table1`",
894 "row_may_be_null": false,
895 "map_bit": 1,
896 "depends_on_map_bits": [
897 ] /* depends_on_map_bits */
898 },
899 {
900 "table": "`t1` `table2`",
901 "row_may_be_null": false,
902 "map_bit": 2,
903 "depends_on_map_bits": [
904 ] /* depends_on_map_bits */
905 }
906 ] /* table_dependencies */
907 },
908 {
909 "ref_optimizer_key_uses": [
910 {
911 "table": "`t1` `table1`",
912 "field": "col_int_key",
913 "equals": "`where_subselect_20070`.`field1`",
914 "null_rejecting": true
915 },
916 {
917 "table": "`t1` `table1`",
918 "field": "col_int_key",
919 "equals": "`table2`.`col_int_key`",
920 "null_rejecting": true
921 },
922 {
923 "table": "`t1` `table2`",
924 "field": "col_int_key",
925 "equals": "`where_subselect_20070`.`field1`",
926 "null_rejecting": true
927 },
928 {
929 "table": "`t1` `table2`",
930 "field": "col_int_key",
931 "equals": "`table1`.`col_int_key`",
932 "null_rejecting": true
933 }
934 ] /* ref_optimizer_key_uses */
935 },
936 {
937 "pulled_out_semijoin_tables": [
938 ] /* pulled_out_semijoin_tables */
939 },
940 {
941 "rows_estimation": [
942 {
943 "table": "`where_subselect_20070`",
944 "table_scan": {
945 "rows": 6,
946 "cost": 0.504761
947 } /* table_scan */
948 },
949 {
950 "table": "`t1` `table1`",
951 "table_scan": {
952 "rows": 4,
953 "cost": 0.501709
954 } /* table_scan */
955 },
956 {
957 "table": "`t1` `table2`",
958 "table_scan": {
959 "rows": 4,
960 "cost": 0.501709
961 } /* table_scan */
962 }
963 ] /* rows_estimation */
964 },
965 {
966 "execution_plan_for_potential_materialization": {
967 "steps": [
968 {
969 "considered_execution_plans": [
970 {
971 "plan_prefix": [
972 ] /* plan_prefix */,
973 "table": "`t1` `table1`",
974 "best_access_path": {
975 "considered_access_paths": [
976 {
977 "access_type": "ref",
978 "index": "col_int_key",
979 "usable": false,
980 "chosen": false
981 },
982 {
983 "rows_to_scan": 4,
984 "filtering_effect": [
985 ] /* filtering_effect */,
986 "final_filtering_effect": 1,
987 "access_type": "scan",
988 "resulting_rows": 4,
989 "cost": 0.901709,
990 "chosen": true
991 }
992 ] /* considered_access_paths */
993 } /* best_access_path */,
994 "condition_filtering_pct": 100,
995 "rows_for_plan": 4,
996 "cost_for_plan": 0.901709,
997 "rest_of_plan": [
998 {
999 "plan_prefix": [
1000 "`t1` `table1`"
1001 ] /* plan_prefix */,
1002 "table": "`t1` `table2`",
1003 "best_access_path": {
1004 "considered_access_paths": [
1005 {
1006 "access_type": "ref",
1007 "index": "col_int_key",
1008 "rows": 2,
1009 "cost": 1.82326,
1010 "chosen": true
1011 },
1012 {
1013 "rows_to_scan": 4,
1014 "filtering_effect": [
1015 ] /* filtering_effect */,
1016 "final_filtering_effect": 1,
1017 "access_type": "scan",
1018 "using_join_cache": true,
1019 "buffers_needed": 1,
1020 "resulting_rows": 4,
1021 "cost": 2.10175,
1022 "chosen": false
1023 }
1024 ] /* considered_access_paths */
1025 } /* best_access_path */,
1026 "condition_filtering_pct": 100,
1027 "rows_for_plan": 8,
1028 "cost_for_plan": 2.72496,
1029 "chosen": true
1030 }
1031 ] /* rest_of_plan */
1032 },
1033 {
1034 "plan_prefix": [
1035 ] /* plan_prefix */,
1036 "table": "`t1` `table2`",
1037 "best_access_path": {
1038 "considered_access_paths": [
1039 {
1040 "access_type": "ref",
1041 "index": "col_int_key",
1042 "usable": false,
1043 "chosen": false
1044 },
1045 {
1046 "rows_to_scan": 4,
1047 "filtering_effect": [
1048 ] /* filtering_effect */,
1049 "final_filtering_effect": 1,
1050 "access_type": "scan",
1051 "resulting_rows": 4,
1052 "cost": 0.901709,
1053 "chosen": true
1054 }
1055 ] /* considered_access_paths */
1056 } /* best_access_path */,
1057 "condition_filtering_pct": 100,
1058 "rows_for_plan": 4,
1059 "cost_for_plan": 0.901709,
1060 "rest_of_plan": [
1061 {
1062 "plan_prefix": [
1063 "`t1` `table2`"
1064 ] /* plan_prefix */,
1065 "table": "`t1` `table1`",
1066 "best_access_path": {
1067 "considered_access_paths": [
1068 {
1069 "access_type": "ref",
1070 "index": "col_int_key",
1071 "rows": 2,
1072 "cost": 1.82326,
1073 "chosen": true
1074 },
1075 {
1076 "rows_to_scan": 4,
1077 "filtering_effect": [
1078 ] /* filtering_effect */,
1079 "final_filtering_effect": 1,
1080 "access_type": "scan",
1081 "using_join_cache": true,
1082 "buffers_needed": 1,
1083 "resulting_rows": 4,
1084 "cost": 2.10175,
1085 "chosen": false
1086 }
1087 ] /* considered_access_paths */
1088 } /* best_access_path */,
1089 "condition_filtering_pct": 100,
1090 "rows_for_plan": 8,
1091 "cost_for_plan": 2.72496,
1092 "pruned_by_cost": true
1093 }
1094 ] /* rest_of_plan */
1095 }
1096 ] /* considered_execution_plans */
1097 }
1098 ] /* steps */
1099 } /* execution_plan_for_potential_materialization */
1100 },
1101 {
1102 "considered_execution_plans": [
1103 {
1104 "plan_prefix": [
1105 ] /* plan_prefix */,
1106 "table": "`where_subselect_20070`",
1107 "best_access_path": {
1108 "considered_access_paths": [
1109 {
1110 "rows_to_scan": 6,
1111 "filtering_effect": [
1112 ] /* filtering_effect */,
1113 "final_filtering_effect": 1,
1114 "access_type": "scan",
1115 "resulting_rows": 6,
1116 "cost": 1.10476,
1117 "chosen": true
1118 }
1119 ] /* considered_access_paths */
1120 } /* best_access_path */,
1121 "condition_filtering_pct": 100,
1122 "rows_for_plan": 6,
1123 "cost_for_plan": 1.10476,
1124 "semijoin_strategy_choice": [
1125 ] /* semijoin_strategy_choice */,
1126 "rest_of_plan": [
1127 {
1128 "plan_prefix": [
1129 "`where_subselect_20070`"
1130 ] /* plan_prefix */,
1131 "table": "`t1` `table1`",
1132 "best_access_path": {
1133 "considered_access_paths": [
1134 {
1135 "access_type": "ref",
1136 "index": "col_int_key",
1137 "rows": 2,
1138 "cost": 2.73488,
1139 "chosen": true
1140 },
1141 {
1142 "rows_to_scan": 4,
1143 "filtering_effect": [
1144 ] /* filtering_effect */,
1145 "final_filtering_effect": 1,
1146 "access_type": "scan",
1147 "using_join_cache": true,
1148 "buffers_needed": 1,
1149 "resulting_rows": 4,
1150 "cost": 2.90186,
1151 "chosen": false
1152 }
1153 ] /* considered_access_paths */
1154 } /* best_access_path */,
1155 "condition_filtering_pct": 100,
1156 "rows_for_plan": 12,
1157 "cost_for_plan": 3.83964,
1158 "semijoin_strategy_choice": [
1159 ] /* semijoin_strategy_choice */,
1160 "rest_of_plan": [
1161 {
1162 "plan_prefix": [
1163 "`where_subselect_20070`",
1164 "`t1` `table1`"
1165 ] /* plan_prefix */,
1166 "table": "`t1` `table2`",
1167 "best_access_path": {
1168 "considered_access_paths": [
1169 {
1170 "access_type": "ref",
1171 "index": "col_int_key",
1172 "rows": 2,
1173 "cost": 5.46977,
1174 "chosen": true
1175 },
1176 {
1177 "rows_to_scan": 4,
1178 "filtering_effect": [
1179 ] /* filtering_effect */,
1180 "final_filtering_effect": 1,
1181 "access_type": "scan",
1182 "using_join_cache": true,
1183 "buffers_needed": 1,
1184 "resulting_rows": 4,
1185 "cost": 5.30212,
1186 "chosen": true
1187 }
1188 ] /* considered_access_paths */
1189 } /* best_access_path */,
1190 "condition_filtering_pct": 25,
1191 "rows_for_plan": 12,
1192 "cost_for_plan": 9.14177,
1193 "semijoin_strategy_choice": [
1194 {
1195 "strategy": "FirstMatch",
1196 "recalculate_access_paths_and_cost": {
1197 "tables": [
1198 {
1199 "table": "`t1` `table2`",
1200 "best_access_path": {
1201 "considered_access_paths": [
1202 {
1203 "access_type": "ref",
1204 "index": "col_int_key",
1205 "rows": 2,
1206 "cost": 5.46977,
1207 "chosen": true
1208 },
1209 {
1210 "rows_to_scan": 4,
1211 "filtering_effect": [
1212 ] /* filtering_effect */,
1213 "final_filtering_effect": 1,
1214 "access_type": "scan",
1215 "resulting_rows": 4,
1216 "cost": 10.8205,
1217 "chosen": false
1218 }
1219 ] /* considered_access_paths */
1220 } /* best_access_path */
1221 }
1222 ] /* tables */
1223 } /* recalculate_access_paths_and_cost */,
1224 "cost": 9.30941,
1225 "rows": 6,
1226 "chosen": true
1227 },
1228 {
1229 "strategy": "MaterializeLookup",
1230 "cost": 6.22973,
1231 "rows": 6,
1232 "duplicate_tables_left": false,
1233 "chosen": true
1234 },
1235 {
1236 "strategy": "DuplicatesWeedout",
1237 "cost": 11.9418,
1238 "rows": 6,
1239 "duplicate_tables_left": false,
1240 "chosen": false
1241 }
1242 ] /* semijoin_strategy_choice */,
1243 "chosen": true
1244 }
1245 ] /* rest_of_plan */
1246 },
1247 {
1248 "plan_prefix": [
1249 "`where_subselect_20070`"
1250 ] /* plan_prefix */,
1251 "table": "`t1` `table2`",
1252 "best_access_path": {
1253 "considered_access_paths": [
1254 {
1255 "access_type": "ref",
1256 "index": "col_int_key",
1257 "rows": 2,
1258 "cost": 2.73488,
1259 "chosen": true
1260 },
1261 {
1262 "rows_to_scan": 4,
1263 "filtering_effect": [
1264 ] /* filtering_effect */,
1265 "final_filtering_effect": 1,
1266 "access_type": "scan",
1267 "using_join_cache": true,
1268 "buffers_needed": 1,
1269 "resulting_rows": 4,
1270 "cost": 2.90186,
1271 "chosen": false
1272 }
1273 ] /* considered_access_paths */
1274 } /* best_access_path */,
1275 "condition_filtering_pct": 100,
1276 "rows_for_plan": 12,
1277 "cost_for_plan": 3.83964,
1278 "semijoin_strategy_choice": [
1279 ] /* semijoin_strategy_choice */,
1280 "rest_of_plan": [
1281 {
1282 "plan_prefix": [
1283 "`where_subselect_20070`",
1284 "`t1` `table2`"
1285 ] /* plan_prefix */,
1286 "table": "`t1` `table1`",
1287 "best_access_path": {
1288 "considered_access_paths": [
1289 {
1290 "access_type": "ref",
1291 "index": "col_int_key",
1292 "rows": 2,
1293 "cost": 5.46977,
1294 "chosen": true
1295 },
1296 {
1297 "rows_to_scan": 4,
1298 "filtering_effect": [
1299 ] /* filtering_effect */,
1300 "final_filtering_effect": 1,
1301 "access_type": "scan",
1302 "using_join_cache": true,
1303 "buffers_needed": 1,
1304 "resulting_rows": 4,
1305 "cost": 5.30212,
1306 "chosen": true
1307 }
1308 ] /* considered_access_paths */
1309 } /* best_access_path */,
1310 "condition_filtering_pct": 25,
1311 "rows_for_plan": 12,
1312 "cost_for_plan": 9.14177,
1313 "semijoin_strategy_choice": [
1314 {
1315 "strategy": "FirstMatch",
1316 "recalculate_access_paths_and_cost": {
1317 "tables": [
1318 {
1319 "table": "`t1` `table1`",
1320 "best_access_path": {
1321 "considered_access_paths": [
1322 {
1323 "access_type": "ref",
1324 "index": "col_int_key",
1325 "rows": 2,
1326 "cost": 5.46977,
1327 "chosen": true
1328 },
1329 {
1330 "rows_to_scan": 4,
1331 "filtering_effect": [
1332 ] /* filtering_effect */,
1333 "final_filtering_effect": 1,
1334 "access_type": "scan",
1335 "resulting_rows": 4,
1336 "cost": 10.8205,
1337 "chosen": false
1338 }
1339 ] /* considered_access_paths */
1340 } /* best_access_path */
1341 }
1342 ] /* tables */
1343 } /* recalculate_access_paths_and_cost */,
1344 "cost": 9.30941,
1345 "rows": 6,
1346 "chosen": true
1347 },
1348 {
1349 "strategy": "MaterializeLookup",
1350 "cost": 6.22973,
1351 "rows": 6,
1352 "duplicate_tables_left": false,
1353 "chosen": true
1354 },
1355 {
1356 "strategy": "DuplicatesWeedout",
1357 "cost": 11.9418,
1358 "rows": 6,
1359 "duplicate_tables_left": false,
1360 "chosen": false
1361 }
1362 ] /* semijoin_strategy_choice */,
1363 "pruned_by_cost": true
1364 }
1365 ] /* rest_of_plan */
1366 }
1367 ] /* rest_of_plan */
1368 },
1369 {
1370 "plan_prefix": [
1371 ] /* plan_prefix */,
1372 "table": "`t1` `table1`",
1373 "best_access_path": {
1374 "considered_access_paths": [
1375 {
1376 "access_type": "ref",
1377 "index": "col_int_key",
1378 "usable": false,
1379 "chosen": false
1380 },
1381 {
1382 "rows_to_scan": 4,
1383 "filtering_effect": [
1384 ] /* filtering_effect */,
1385 "final_filtering_effect": 1,
1386 "access_type": "scan",
1387 "resulting_rows": 4,
1388 "cost": 0.901709,
1389 "chosen": true
1390 }
1391 ] /* considered_access_paths */
1392 } /* best_access_path */,
1393 "condition_filtering_pct": 100,
1394 "rows_for_plan": 4,
1395 "cost_for_plan": 0.901709,
1396 "semijoin_strategy_choice": [
1397 ] /* semijoin_strategy_choice */,
1398 "rest_of_plan": [
1399 {
1400 "plan_prefix": [
1401 "`t1` `table1`"
1402 ] /* plan_prefix */,
1403 "table": "`where_subselect_20070`",
1404 "best_access_path": {
1405 "considered_access_paths": [
1406 {
1407 "rows_to_scan": 6,
1408 "filtering_effect": [
1409 ] /* filtering_effect */,
1410 "final_filtering_effect": 1,
1411 "access_type": "scan",
1412 "using_join_cache": true,
1413 "buffers_needed": 1,
1414 "resulting_rows": 6,
1415 "cost": 2.9048,
1416 "chosen": true
1417 }
1418 ] /* considered_access_paths */
1419 } /* best_access_path */,
1420 "condition_filtering_pct": 16.6667,
1421 "rows_for_plan": 4,
1422 "cost_for_plan": 3.80651,
1423 "semijoin_strategy_choice": [
1424 ] /* semijoin_strategy_choice */,
1425 "rest_of_plan": [
1426 {
1427 "plan_prefix": [
1428 "`t1` `table1`",
1429 "`where_subselect_20070`"
1430 ] /* plan_prefix */,
1431 "table": "`t1` `table2`",
1432 "best_access_path": {
1433 "considered_access_paths": [
1434 {
1435 "access_type": "ref",
1436 "index": "col_int_key",
1437 "rows": 2,
1438 "cost": 1.82326,
1439 "chosen": true
1440 },
1441 {
1442 "rows_to_scan": 4,
1443 "filtering_effect": [
1444 ] /* filtering_effect */,
1445 "final_filtering_effect": 1,
1446 "access_type": "scan",
1447 "using_join_cache": true,
1448 "buffers_needed": 1,
1449 "resulting_rows": 4,
1450 "cost": 2.10185,
1451 "chosen": false
1452 }
1453 ] /* considered_access_paths */
1454 } /* best_access_path */,
1455 "condition_filtering_pct": 100,
1456 "rows_for_plan": 8,
1457 "cost_for_plan": 5.62976,
1458 "semijoin_strategy_choice": [
1459 {
1460 "strategy": "DuplicatesWeedout",
1461 "cost": 7.52976,
1462 "rows": 1,
1463 "duplicate_tables_left": true,
1464 "chosen": true
1465 }
1466 ] /* semijoin_strategy_choice */,
1467 "pruned_by_cost": true
1468 }
1469 ] /* rest_of_plan */
1470 },
1471 {
1472 "plan_prefix": [
1473 "`t1` `table1`"
1474 ] /* plan_prefix */,
1475 "table": "`t1` `table2`",
1476 "best_access_path": {
1477 "considered_access_paths": [
1478 {
1479 "access_type": "ref",
1480 "index": "col_int_key",
1481 "rows": 2,
1482 "cost": 1.82326,
1483 "chosen": true
1484 },
1485 {
1486 "rows_to_scan": 4,
1487 "filtering_effect": [
1488 ] /* filtering_effect */,
1489 "final_filtering_effect": 1,
1490 "access_type": "scan",
1491 "using_join_cache": true,
1492 "buffers_needed": 1,
1493 "resulting_rows": 4,
1494 "cost": 2.10175,
1495 "chosen": false
1496 }
1497 ] /* considered_access_paths */
1498 } /* best_access_path */,
1499 "condition_filtering_pct": 100,
1500 "rows_for_plan": 8,
1501 "cost_for_plan": 2.72496,
1502 "semijoin_strategy_choice": [
1503 {
1504 "strategy": "MaterializeScan",
1505 "choice": "deferred"
1506 }
1507 ] /* semijoin_strategy_choice */,
1508 "rest_of_plan": [
1509 {
1510 "plan_prefix": [
1511 "`t1` `table1`",
1512 "`t1` `table2`"
1513 ] /* plan_prefix */,
1514 "table": "`where_subselect_20070`",
1515 "best_access_path": {
1516 "considered_access_paths": [
1517 {
1518 "rows_to_scan": 6,
1519 "filtering_effect": [
1520 ] /* filtering_effect */,
1521 "final_filtering_effect": 1,
1522 "access_type": "scan",
1523 "using_join_cache": true,
1524 "buffers_needed": 1,
1525 "resulting_rows": 6,
1526 "cost": 5.30491,
1527 "chosen": true
1528 }
1529 ] /* considered_access_paths */
1530 } /* best_access_path */,
1531 "condition_filtering_pct": 16.6667,
1532 "rows_for_plan": 8,
1533 "cost_for_plan": 8.02988,
1534 "semijoin_strategy_choice": [
1535 {
1536 "strategy": "LooseScan",
1537 "recalculate_access_paths_and_cost": {
1538 "tables": [
1539 {
1540 "table": "`t1` `table1`",
1541 "best_access_path": {
1542 "considered_access_paths": [
1543 {
1544 "access_type": "ref",
1545 "index": "col_int_key",
1546 "usable": false,
1547 "chosen": false
1548 },
1549 {
1550 "rows_to_scan": 4,
1551 "filtering_effect": [
1552 ] /* filtering_effect */,
1553 "final_filtering_effect": 1,
1554 "access_type": "scan",
1555 "resulting_rows": 4,
1556 "cost": 0.901709,
1557 "chosen": true
1558 }
1559 ] /* considered_access_paths */
1560 } /* best_access_path */,
1561 "unknown_key_1": {
1562 "searching_loose_scan_index": {
1563 "indexes": [
1564 {
1565 "index": "col_int_key",
1566 "index_handles_needed_semijoin_equalities": false
1567 }
1568 ] /* indexes */
1569 } /* searching_loose_scan_index */
1570 }
1571 }
1572 ] /* tables */
1573 } /* recalculate_access_paths_and_cost */,
1574 "chosen": false
1575 },
1576 {
1577 "strategy": "MaterializeScan",
1578 "recalculate_access_paths_and_cost": {
1579 "tables": [
1580 {
1581 "table": "`where_subselect_20070`",
1582 "best_access_path": {
1583 "considered_access_paths": [
1584 {
1585 "rows_to_scan": 6,
1586 "filtering_effect": [
1587 ] /* filtering_effect */,
1588 "final_filtering_effect": 1,
1589 "access_type": "scan",
1590 "using_join_cache": true,
1591 "buffers_needed": 1,
1592 "resulting_rows": 6,
1593 "cost": 2.90484,
1594 "chosen": true
1595 }
1596 ] /* considered_access_paths */
1597 } /* best_access_path */
1598 }
1599 ] /* tables */
1600 } /* recalculate_access_paths_and_cost */,
1601 "cost": 7.8298,
1602 "rows": 1,
1603 "duplicate_tables_left": true,
1604 "chosen": true
1605 },
1606 {
1607 "strategy": "DuplicatesWeedout",
1608 "cost": 9.92988,
1609 "rows": 1,
1610 "duplicate_tables_left": false,
1611 "chosen": false
1612 }
1613 ] /* semijoin_strategy_choice */,
1614 "pruned_by_cost": true
1615 }
1616 ] /* rest_of_plan */
1617 }
1618 ] /* rest_of_plan */
1619 },
1620 {
1621 "plan_prefix": [
1622 ] /* plan_prefix */,
1623 "table": "`t1` `table2`",
1624 "best_access_path": {
1625 "considered_access_paths": [
1626 {
1627 "access_type": "ref",
1628 "index": "col_int_key",
1629 "usable": false,
1630 "chosen": false
1631 },
1632 {
1633 "rows_to_scan": 4,
1634 "filtering_effect": [
1635 ] /* filtering_effect */,
1636 "final_filtering_effect": 1,
1637 "access_type": "scan",
1638 "resulting_rows": 4,
1639 "cost": 0.901709,
1640 "chosen": true
1641 }
1642 ] /* considered_access_paths */
1643 } /* best_access_path */,
1644 "condition_filtering_pct": 100,
1645 "rows_for_plan": 4,
1646 "cost_for_plan": 0.901709,
1647 "semijoin_strategy_choice": [
1648 ] /* semijoin_strategy_choice */,
1649 "rest_of_plan": [
1650 {
1651 "plan_prefix": [
1652 "`t1` `table2`"
1653 ] /* plan_prefix */,
1654 "table": "`where_subselect_20070`",
1655 "best_access_path": {
1656 "considered_access_paths": [
1657 {
1658 "rows_to_scan": 6,
1659 "filtering_effect": [
1660 ] /* filtering_effect */,
1661 "final_filtering_effect": 1,
1662 "access_type": "scan",
1663 "using_join_cache": true,
1664 "buffers_needed": 1,
1665 "resulting_rows": 6,
1666 "cost": 2.9048,
1667 "chosen": true
1668 }
1669 ] /* considered_access_paths */
1670 } /* best_access_path */,
1671 "condition_filtering_pct": 16.6667,
1672 "rows_for_plan": 4,
1673 "cost_for_plan": 3.80651,
1674 "semijoin_strategy_choice": [
1675 ] /* semijoin_strategy_choice */,
1676 "rest_of_plan": [
1677 {
1678 "plan_prefix": [
1679 "`t1` `table2`",
1680 "`where_subselect_20070`"
1681 ] /* plan_prefix */,
1682 "table": "`t1` `table1`",
1683 "best_access_path": {
1684 "considered_access_paths": [
1685 {
1686 "access_type": "ref",
1687 "index": "col_int_key",
1688 "rows": 2,
1689 "cost": 1.82326,
1690 "chosen": true
1691 },
1692 {
1693 "rows_to_scan": 4,
1694 "filtering_effect": [
1695 ] /* filtering_effect */,
1696 "final_filtering_effect": 1,
1697 "access_type": "scan",
1698 "using_join_cache": true,
1699 "buffers_needed": 1,
1700 "resulting_rows": 4,
1701 "cost": 2.10185,
1702 "chosen": false
1703 }
1704 ] /* considered_access_paths */
1705 } /* best_access_path */,
1706 "condition_filtering_pct": 100,
1707 "rows_for_plan": 8,
1708 "cost_for_plan": 5.62976,
1709 "semijoin_strategy_choice": [
1710 {
1711 "strategy": "DuplicatesWeedout",
1712 "cost": 7.52976,
1713 "rows": 1,
1714 "duplicate_tables_left": true,
1715 "chosen": true
1716 }
1717 ] /* semijoin_strategy_choice */,
1718 "pruned_by_cost": true
1719 }
1720 ] /* rest_of_plan */
1721 },
1722 {
1723 "plan_prefix": [
1724 "`t1` `table2`"
1725 ] /* plan_prefix */,
1726 "table": "`t1` `table1`",
1727 "best_access_path": {
1728 "considered_access_paths": [
1729 {
1730 "access_type": "ref",
1731 "index": "col_int_key",
1732 "rows": 2,
1733 "cost": 1.82326,
1734 "chosen": true
1735 },
1736 {
1737 "rows_to_scan": 4,
1738 "filtering_effect": [
1739 ] /* filtering_effect */,
1740 "final_filtering_effect": 1,
1741 "access_type": "scan",
1742 "using_join_cache": true,
1743 "buffers_needed": 1,
1744 "resulting_rows": 4,
1745 "cost": 2.10175,
1746 "chosen": false
1747 }
1748 ] /* considered_access_paths */
1749 } /* best_access_path */,
1750 "condition_filtering_pct": 100,
1751 "rows_for_plan": 8,
1752 "cost_for_plan": 2.72496,
1753 "semijoin_strategy_choice": [
1754 {
1755 "strategy": "MaterializeScan",
1756 "choice": "deferred"
1757 }
1758 ] /* semijoin_strategy_choice */,
1759 "rest_of_plan": [
1760 {
1761 "plan_prefix": [
1762 "`t1` `table2`",
1763 "`t1` `table1`"
1764 ] /* plan_prefix */,
1765 "table": "`where_subselect_20070`",
1766 "best_access_path": {
1767 "considered_access_paths": [
1768 {
1769 "rows_to_scan": 6,
1770 "filtering_effect": [
1771 ] /* filtering_effect */,
1772 "final_filtering_effect": 1,
1773 "access_type": "scan",
1774 "using_join_cache": true,
1775 "buffers_needed": 1,
1776 "resulting_rows": 6,
1777 "cost": 5.30491,
1778 "chosen": true
1779 }
1780 ] /* considered_access_paths */
1781 } /* best_access_path */,
1782 "condition_filtering_pct": 16.6667,
1783 "rows_for_plan": 8,
1784 "cost_for_plan": 8.02988,
1785 "semijoin_strategy_choice": [
1786 {
1787 "strategy": "LooseScan",
1788 "recalculate_access_paths_and_cost": {
1789 "tables": [
1790 {
1791 "table": "`t1` `table2`",
1792 "best_access_path": {
1793 "considered_access_paths": [
1794 {
1795 "access_type": "ref",
1796 "index": "col_int_key",
1797 "usable": false,
1798 "chosen": false
1799 },
1800 {
1801 "rows_to_scan": 4,
1802 "filtering_effect": [
1803 ] /* filtering_effect */,
1804 "final_filtering_effect": 1,
1805 "access_type": "scan",
1806 "resulting_rows": 4,
1807 "cost": 0.901709,
1808 "chosen": true
1809 }
1810 ] /* considered_access_paths */
1811 } /* best_access_path */,
1812 "unknown_key_2": {
1813 "searching_loose_scan_index": {
1814 "indexes": [
1815 {
1816 "index": "col_int_key",
1817 "covering_scan": {
1818 "cost": 0.267442,
1819 "chosen": true
1820 } /* covering_scan */
1821 }
1822 ] /* indexes */
1823 } /* searching_loose_scan_index */
1824 }
1825 },
1826 {
1827 "table": "`where_subselect_20070`",
1828 "best_access_path": {
1829 "considered_access_paths": [
1830 {
1831 "rows_to_scan": 6,
1832 "filtering_effect": [
1833 ] /* filtering_effect */,
1834 "final_filtering_effect": 1,
1835 "access_type": "scan",
1836 "using_join_cache": true,
1837 "buffers_needed": 1,
1838 "resulting_rows": 6,
1839 "cost": 5.30491,
1840 "chosen": true
1841 }
1842 ] /* considered_access_paths */
1843 } /* best_access_path */
1844 }
1845 ] /* tables */
1846 } /* recalculate_access_paths_and_cost */,
1847 "cost": 7.79561,
1848 "rows": 1,
1849 "chosen": true
1850 },
1851 {
1852 "strategy": "MaterializeScan",
1853 "recalculate_access_paths_and_cost": {
1854 "tables": [
1855 {
1856 "table": "`where_subselect_20070`",
1857 "best_access_path": {
1858 "considered_access_paths": [
1859 {
1860 "rows_to_scan": 6,
1861 "filtering_effect": [
1862 ] /* filtering_effect */,
1863 "final_filtering_effect": 1,
1864 "access_type": "scan",
1865 "using_join_cache": true,
1866 "buffers_needed": 1,
1867 "resulting_rows": 6,
1868 "cost": 2.90484,
1869 "chosen": true
1870 }
1871 ] /* considered_access_paths */
1872 } /* best_access_path */
1873 }
1874 ] /* tables */
1875 } /* recalculate_access_paths_and_cost */,
1876 "cost": 7.8298,
1877 "rows": 1,
1878 "duplicate_tables_left": false,
1879 "chosen": false
1880 },
1881 {
1882 "strategy": "DuplicatesWeedout",
1883 "cost": 9.92988,
1884 "rows": 1,
1885 "duplicate_tables_left": false,
1886 "chosen": false
1887 }
1888 ] /* semijoin_strategy_choice */,
1889 "pruned_by_cost": true
1890 }
1891 ] /* rest_of_plan */
1892 }
1893 ] /* rest_of_plan */
1894 },
1895 {
1896 "final_semijoin_strategy": "MaterializeLookup"
1897 }
1898 ] /* considered_execution_plans */
1899 },
1900 {
1901 "creating_tmp_table": {
1902 "tmp_table_info": {
1903 "table": "<subquery3>",
1904 "columns": 2,
1905 "row_length": 13,
1906 "key_length": 14,
1907 "unique_constraint": false,
1908 "makes_grouped_rows": false,
1909 "cannot_insert_duplicates": true,
1910 "location": "TempTable"
1911 } /* tmp_table_info */
1912 } /* creating_tmp_table */
1913 },
1914 {
1915 "subselect_execution": {
1916 "select#": 2,
1917 "steps": [
1918 ] /* steps */
1919 } /* subselect_execution */
1920 },
1921 {
1922 "attaching_conditions_to_tables": {
1923 "original_condition": "((`<subquery3>`.`field1` = `where_subselect_20070`.`field1`) and (`table2`.`col_int_key` = `table1`.`col_int_key`))",
1924 "attached_conditions_computation": [
1925 {
1926 "table": "`t1` `table1`",
1927 "rechecking_index_usage": {
1928 "recheck_reason": "not_first_table",
1929 "range_analysis": {
1930 "table_scan": {
1931 "rows": 4,
1932 "cost": 3.00171
1933 } /* table_scan */,
1934 "potential_range_indexes": [
1935 {
1936 "index": "col_int_key",
1937 "usable": true,
1938 "key_parts": [
1939 "col_int_key"
1940 ] /* key_parts */
1941 }
1942 ] /* potential_range_indexes */,
1943 "best_covering_index_scan": {
1944 "index": "col_int_key",
1945 "cost": 0.667442,
1946 "chosen": true
1947 } /* best_covering_index_scan */,
1948 "setup_range_conditions": [
1949 ] /* setup_range_conditions */,
1950 "group_index_range": {
1951 "chosen": false,
1952 "cause": "not_original_query_table"
1953 } /* group_index_range */,
1954 "skip_scan_range": {
1955 "chosen": false,
1956 "cause": "not_single_table"
1957 } /* skip_scan_range */,
1958 "analyzing_range_alternatives": {
1959 "range_scan_alternatives": [
1960 {
1961 "index": "col_int_key",
1962 "ranges": [
1963 "NULL < col_int_key"
1964 ] /* ranges */,
1965 "index_dives_for_eq_ranges": true,
1966 "rowid_ordered": false,
1967 "using_mrr": false,
1968 "index_only": true,
1969 "in_memory": -1,
1970 "rows": 4,
1971 "cost": 0.677442,
1972 "chosen": false,
1973 "cause": "cost"
1974 }
1975 ] /* range_scan_alternatives */,
1976 "analyzing_roworder_intersect": {
1977 "usable": false,
1978 "cause": "too_few_roworder_scans"
1979 } /* analyzing_roworder_intersect */
1980 } /* analyzing_range_alternatives */
1981 } /* range_analysis */
1982 } /* rechecking_index_usage */
1983 }
1984 ] /* attached_conditions_computation */,
1985 "attached_conditions_summary": [
1986 {
1987 "table": "`where_subselect_20070`",
1988 "attached": "(`where_subselect_20070`.`field1` is not null)"
1989 },
1990 {
1991 "table": "`<subquery3>`",
1992 "attached": "(`<subquery3>`.`field1` = `where_subselect_20070`.`field1`)"
1993 },
1994 {
1995 "table": "`t1` `table1`",
1996 "attached": "(`table1`.`col_int_key` is not null)"
1997 },
1998 {
1999 "table": "`t1` `table2`",
2000 "attached": "(`table2`.`col_int_key` = `table1`.`col_int_key`)"
2001 }
2002 ] /* attached_conditions_summary */
2003 } /* attaching_conditions_to_tables */
2004 },
2005 {
2006 "finalizing_table_conditions": [
2007 {
2008 "table": "`where_subselect_20070`",
2009 "original_table_condition": "(`where_subselect_20070`.`field1` is not null)",
2010 "final_table_condition ": "(`where_subselect_20070`.`field1` is not null)"
2011 },
2012 {
2013 "table": "`<subquery3>`",
2014 "original_table_condition": "(`<subquery3>`.`field1` = `where_subselect_20070`.`field1`)",
2015 "final_table_condition ": null
2016 },
2017 {
2018 "table": "`t1` `table1`",
2019 "original_table_condition": "(`table1`.`col_int_key` is not null)",
2020 "final_table_condition ": "(`table1`.`col_int_key` is not null)"
2021 },
2022 {
2023 "table": "`t1` `table2`",
2024 "original_table_condition": "(`table2`.`col_int_key` = `table1`.`col_int_key`)",
2025 "final_table_condition ": null
2026 }
2027 ] /* finalizing_table_conditions */
2028 },
2029 {
2030 "refine_plan": [
2031 {
2032 "table": "`where_subselect_20070`"
2033 },
2034 {
2035 "table": "`<subquery3>`"
2036 },
2037 {
2038 "table": "`t1` `table1`"
2039 },
2040 {
2041 "table": "`t1` `table2`"
2042 }
2043 ] /* refine_plan */
2044 }
2045 ] /* steps */
2046 } /* join_optimization */
2047 },
2048 {
2049 "join_execution": {
2050 "select#": 1,
2051 "steps": [
2052 {
2053 "materialize": {
2054 "select#": 1,
2055 "steps": [
2056 {
2057 "subselect_execution": {
2058 "select#": 4,
2059 "steps": [
2060 ] /* steps */
2061 } /* subselect_execution */
2062 },
2063 {
2064 "subselect_execution": {
2065 "select#": 4,
2066 "steps": [
2067 ] /* steps */
2068 } /* subselect_execution */
2069 },
2070 {
2071 "subselect_execution": {
2072 "select#": 4,
2073 "steps": [
2074 ] /* steps */
2075 } /* subselect_execution */
2076 },
2077 {
2078 "subselect_execution": {
2079 "select#": 4,
2080 "steps": [
2081 ] /* steps */
2082 } /* subselect_execution */
2083 },
2084 {
2085 "subselect_execution": {
2086 "select#": 4,
2087 "steps": [
2088 ] /* steps */
2089 } /* subselect_execution */
2090 },
2091 {
2092 "subselect_execution": {
2093 "select#": 4,
2094 "steps": [
2095 ] /* steps */
2096 } /* subselect_execution */
2097 },
2098 {
2099 "subselect_execution": {
2100 "select#": 4,
2101 "steps": [
2102 ] /* steps */
2103 } /* subselect_execution */
2104 },
2105 {
2106 "subselect_execution": {
2107 "select#": 4,
2108 "steps": [
2109 ] /* steps */
2110 } /* subselect_execution */
2111 },
2112 {
2113 "subselect_execution": {
2114 "select#": 4,
2115 "steps": [
2116 ] /* steps */
2117 } /* subselect_execution */
2118 },
2119 {
2120 "subselect_execution": {
2121 "select#": 4,
2122 "steps": [
2123 ] /* steps */
2124 } /* subselect_execution */
2125 },
2126 {
2127 "subselect_execution": {
2128 "select#": 4,
2129 "steps": [
2130 ] /* steps */
2131 } /* subselect_execution */
2132 },
2133 {
2134 "subselect_execution": {
2135 "select#": 4,
2136 "steps": [
2137 ] /* steps */
2138 } /* subselect_execution */
2139 }
2140 ] /* steps */
2141 } /* materialize */
2142 }
2143 ] /* steps */
2144 } /* join_execution */
2145 }
2146 ] /* steps */
2147 } 0 0
2148 DROP TABLE where_subselect_20070,t1;
2149 #
2150 # Bug#13430443 - ASSERTION `NEW_TYPE[0] != 'U'' FAILED. WHEN
2151 # OPTIMIZER_TRACE IS ENABLED
2152 #
2153 CREATE TABLE t1
2154 (a INT,b INT,c INT, KEY(a),KEY (a,c)) ENGINE=INNODB;
2155 SELECT 1 FROM t1 WHERE 1 LIKE
2156 (SELECT a FROM t1 WHERE a = 1 ORDER BY c);
2157 1
2158 SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
2159 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
2160 SELECT 1 FROM t1 WHERE 1 LIKE
2161 (SELECT a FROM t1 WHERE a = 1 ORDER BY c) {
2162 "steps": [
2163 {
2164 "join_optimization": {
2165 "select#": 1,
2166 "steps": [
2167 {
2168 "condition_processing": {
2169 "condition": "WHERE",
2170 "original_condition": "(1 like (/* select#2 */ select `t1`.`a` from `t1` where (`t1`.`a` = 1)))",
2171 "steps": [
2172 {
2173 "transformation": "equality_propagation",
2174 "subselect_evaluation": [
2175 ] /* subselect_evaluation */,
2176 "resulting_condition": "(1 like (/* select#2 */ select `t1`.`a` from `t1` where (`t1`.`a` = 1)))"
2177 },
2178 {
2179 "transformation": "constant_propagation",
2180 "subselect_evaluation": [
2181 ] /* subselect_evaluation */,
2182 "resulting_condition": "(1 like (/* select#2 */ select `t1`.`a` from `t1` where (`t1`.`a` = 1)))"
2183 },
2184 {
2185 "transformation": "trivial_condition_removal",
2186 "subselect_evaluation": [
2187 {
2188 "subselect_execution": {
2189 "select#": 2,
2190 "steps": [
2191 {
2192 "join_optimization": {
2193 "select#": 2,
2194 "steps": [
2195 {
2196 "condition_processing": {
2197 "condition": "WHERE",
2198 "original_condition": "(`t1`.`a` = 1)",
2199 "steps": [
2200 {
2201 "transformation": "equality_propagation",
2202 "resulting_condition": "multiple equal(1, `t1`.`a`)"
2203 },
2204 {
2205 "transformation": "constant_propagation",
2206 "resulting_condition": "multiple equal(1, `t1`.`a`)"
2207 },
2208 {
2209 "transformation": "trivial_condition_removal",
2210 "resulting_condition": "multiple equal(1, `t1`.`a`)"
2211 }
2212 ] /* steps */
2213 } /* condition_processing */
2214 },
2215 {
2216 "substitute_generated_columns": {
2217 } /* substitute_generated_columns */
2218 },
2219 {
2220 "table_dependencies": [
2221 {
2222 "table": "`t1`",
2223 "row_may_be_null": false,
2224 "map_bit": 0,
2225 "depends_on_map_bits": [
2226 ] /* depends_on_map_bits */
2227 }
2228 ] /* table_dependencies */
2229 },
2230 {
2231 "ref_optimizer_key_uses": [
2232 {
2233 "table": "`t1`",
2234 "field": "a",
2235 "equals": "1",
2236 "null_rejecting": true
2237 },
2238 {
2239 "table": "`t1`",
2240 "field": "a",
2241 "equals": "1",
2242 "null_rejecting": true
2243 }
2244 ] /* ref_optimizer_key_uses */
2245 },
2246 {
2247 "rows_estimation": [
2248 {
2249 "table": "`t1`",
2250 "range_analysis": {
2251 "table_scan": {
2252 "rows": 1,
2253 "cost": 2.45
2254 } /* table_scan */,
2255 "potential_range_indexes": [
2256 {
2257 "index": "a",
2258 "usable": true,
2259 "key_parts": [
2260 "a"
2261 ] /* key_parts */
2262 },
2263 {
2264 "index": "a_2",
2265 "usable": true,
2266 "key_parts": [
2267 "a",
2268 "c"
2269 ] /* key_parts */
2270 }
2271 ] /* potential_range_indexes */,
2272 "best_covering_index_scan": {
2273 "index": "a_2",
2274 "cost": 0.35,
2275 "chosen": true
2276 } /* best_covering_index_scan */,
2277 "setup_range_conditions": [
2278 ] /* setup_range_conditions */,
2279 "group_index_range": {
2280 "chosen": false,
2281 "cause": "not_group_by_or_distinct"
2282 } /* group_index_range */,
2283 "skip_scan_range": {
2284 "potential_skip_scan_indexes": [
2285 {
2286 "index": "a",
2287 "usable": false,
2288 "cause": "query_references_nonkey_column"
2289 },
2290 {
2291 "index": "a_2",
2292 "usable": false,
2293 "cause": "no_range_predicate"
2294 }
2295 ] /* potential_skip_scan_indexes */
2296 } /* skip_scan_range */,
2297 "analyzing_range_alternatives": {
2298 "range_scan_alternatives": [
2299 {
2300 "index": "a",
2301 "ranges": [
2302 "a = 1"
2303 ] /* ranges */,
2304 "index_dives_for_eq_ranges": true,
2305 "rowid_ordered": true,
2306 "using_mrr": true,
2307 "index_only": false,
2308 "in_memory": 1,
2309 "rows": 1,
2310 "cost": 0.6,
2311 "chosen": false,
2312 "cause": "cost"
2313 },
2314 {
2315 "index": "a_2",
2316 "ranges": [
2317 "a = 1"
2318 ] /* ranges */,
2319 "index_dives_for_eq_ranges": true,
2320 "rowid_ordered": false,
2321 "using_mrr": false,
2322 "index_only": true,
2323 "in_memory": 1,
2324 "rows": 1,
2325 "cost": 0.36,
2326 "chosen": false,
2327 "cause": "cost"
2328 }
2329 ] /* range_scan_alternatives */,
2330 "analyzing_roworder_intersect": {
2331 "usable": false,
2332 "cause": "too_few_roworder_scans"
2333 } /* analyzing_roworder_intersect */
2334 } /* analyzing_range_alternatives */
2335 } /* range_analysis */
2336 }
2337 ] /* rows_estimation */
2338 },
2339 {
2340 "considered_execution_plans": [
2341 {
2342 "plan_prefix": [
2343 ] /* plan_prefix */,
2344 "table": "`t1`",
2345 "best_access_path": {
2346 "considered_access_paths": [
2347 {
2348 "access_type": "ref",
2349 "index": "a",
2350 "rows": 1,
2351 "cost": 0.35,
2352 "chosen": true
2353 },
2354 {
2355 "access_type": "ref",
2356 "index": "a_2",
2357 "rows": 1,
2358 "cost": 0.35,
2359 "chosen": false
2360 },
2361 {
2362 "access_type": "scan",
2363 "chosen": false,
2364 "cause": "covering_index_better_than_full_scan"
2365 }
2366 ] /* considered_access_paths */
2367 } /* best_access_path */,
2368 "condition_filtering_pct": 100,
2369 "rows_for_plan": 1,
2370 "cost_for_plan": 0.35,
2371 "chosen": true
2372 }
2373 ] /* considered_execution_plans */
2374 },
2375 {
2376 "attaching_conditions_to_tables": {
2377 "original_condition": "(`t1`.`a` = 1)",
2378 "attached_conditions_computation": [
2379 ] /* attached_conditions_computation */,
2380 "attached_conditions_summary": [
2381 {
2382 "table": "`t1`",
2383 "attached": "(`t1`.`a` = 1)"
2384 }
2385 ] /* attached_conditions_summary */
2386 } /* attaching_conditions_to_tables */
2387 },
2388 {
2389 "finalizing_table_conditions": [
2390 {
2391 "table": "`t1`",
2392 "original_table_condition": "(`t1`.`a` = 1)",
2393 "final_table_condition ": null
2394 }
2395 ] /* finalizing_table_conditions */
2396 },
2397 {
2398 "refine_plan": [
2399 {
2400 "table": "`t1`"
2401 }
2402 ] /* refine_plan */
2403 }
2404 ] /* steps */
2405 } /* join_optimization */
2406 },
2407 {
2408 "join_execution": {
2409 "select#": 2,
2410 "steps": [
2411 ] /* steps */
2412 } /* join_execution */
2413 }
2414 ] /* steps */
2415 } /* subselect_execution */
2416 }
2417 ] /* subselect_evaluation */,
2418 "resulting_condition": null
2419 }
2420 ] /* steps */
2421 } /* condition_processing */
2422 }
2423 ] /* steps */,
2424 "empty_result": {
2425 "cause": "Impossible WHERE"
2426 } /* empty_result */
2427 } /* join_optimization */
2428 },
2429 {
2430 "join_execution": {
2431 "select#": 1,
2432 "steps": [
2433 ] /* steps */
2434 } /* join_execution */
2435 }
2436 ] /* steps */
2437 } 0 0
2438 DROP TABLE t1;
2439 #
2440 # Bug #18346750 OPTIMIZER_TRACE & DBUG_PRINT CRASH IN ST_SELECT_LEX::PRINT
2441 #
2442 "Skipped since this engine does not support native partitioning.
2443 #
2444 # Bug#18791851 CRASH IN ST_SELECT_LEX::PRINT WITH OPTIMIZER_TRACE ON SUBQUERY
2445 #
2446 CREATE TABLE t1 (
2447 pk INT NOT NULL,
2448 col_int_nokey INT,
2449 col_int_key INT,
2450 col_time_key time,
2451 col_varchar_key VARCHAR(1),
2452 col_varchar_nokey VARCHAR(1),
2453 PRIMARY KEY (pk),
2454 KEY col_varchar_key (col_varchar_key,col_int_key)
2455 );
2456 CREATE TABLE t2 (
2457 pk INT NOT NULL,
2458 col_int_nokey INT,
2459 col_int_key INT,
2460 col_varchar_key VARCHAR(1),
2461 col_varchar_nokey VARCHAR(1),
2462 PRIMARY KEY (pk),
2463 KEY col_varchar_key (col_varchar_key,col_int_key)
2464 );
2465 CREATE TABLE t3 (
2466 pk INT NOT NULL,
2467 col_int_nokey INT,
2468 col_int_key INT,
2469 col_varchar_key VARCHAR(1),
2470 col_varchar_nokey VARCHAR(1),
2471 PRIMARY KEY (pk),
2472 KEY col_varchar_key (col_varchar_key,col_int_key)
2473 );
2474 CREATE TABLE t4 (
2475 pk INT NOT NULL,
2476 col_int_nokey INT,
2477 col_int_key INT,
2478 col_time_key time,
2479 col_varchar_key VARCHAR(1),
2480 col_varchar_nokey VARCHAR(1),
2481 PRIMARY KEY (pk),
2482 KEY col_varchar_key (col_varchar_key,col_int_key)
2483 );
2484 SELECT alias1.col_int_nokey AS field1,
2485 alias2.col_varchar_key AS field2,
2486 alias1.col_time_key AS field3,
2487 MIN( alias1.col_int_nokey ) AS field4
2488 FROM (
2489 (
2490 SELECT SQ1_alias1.*
2491 FROM ( t1 AS SQ1_alias1, t2 AS SQ1_alias2 )
2492 ) AS alias1,
2493 t4 AS alias2
2494 )
2495 WHERE
2496 EXISTS (
2497 SELECT DISTINCT SQ2_alias2.col_varchar_nokey AS SQ2_field1
2498 FROM t2 AS SQ2_alias1
2499 INNER JOIN (t4 AS SQ2_alias2
2500 INNER JOIN t3 AS SQ2_alias3
2501 ON SQ2_alias3.pk = SQ2_alias2.pk)
2502 ON SQ2_alias3.col_varchar_key = SQ2_alias2.col_varchar_nokey
2503 )
2504 AND alias1.col_int_key = alias2.pk
2505 HAVING alias1.col_int_nokey IN ( SELECT 2 FROM DUAL ) ;
2506 field1 field2 field3 field4
2507 DROP TABLE t1,t2,t3,t4;
2508 CREATE TABLE t1(a INT);
2509 SET @a:=(SELECT ROW(1, 2)=
2510 ROW((SELECT 1 FROM t1 LEFT JOIN t1 t2 ON 1
2511 HAVING 3 IN (SELECT 2 FROM DUAL)),
2512 1));
2513 DROP TABLE t1;
2514 #
2515 # Bug #18945693 CRASH IN PRINT_TABLE_ARRAY AT SQL/SQL_LEX.CC ON 2ND EXEC OF PREPARED STATEMENT
2516 #
2517 CREATE TABLE t1 (
2518 pk INT NOT NULL,
2519 col_int_nokey INT,
2520 col_int_key INT,
2521 col_varchar_key VARCHAR(1),
2522 col_varchar_nokey VARCHAR(1)
2523 );
2524 INSERT INTO t1 VALUES (13,7,3,'y','y'),(14,0,4,'c','c');
2525 CREATE TABLE t2 (
2526 pk INT NOT NULL,
2527 col_int_nokey INT,
2528 col_int_key INT,
2529 col_varchar_key VARCHAR(1),
2530 col_varchar_nokey VARCHAR(1)
2531 );
2532 INSERT INTO t2 VALUES (20,9,8,'e','e');
2533 CREATE TABLE t3 (
2534 pk INT NOT NULL,
2535 col_int_nokey INT,
2536 col_int_key INT,
2537 col_varchar_key VARCHAR(1),
2538 col_varchar_nokey VARCHAR(1)
2539 );
2540 INSERT INTO t3 VALUES (1,1,7,'k','k');
2541 PREPARE prep_stmt FROM "
2542 SELECT
2543 alias1.col_varchar_nokey AS field1,
2544 MAX(alias1.pk) AS field2
2545 FROM t3 AS alias1
2546 GROUP BY field1
2547 HAVING
2548 1 > (
2549 SELECT MAX(SQ4_alias1.col_varchar_nokey)
2550 FROM t1 AS SQ4_alias1
2551 RIGHT OUTER JOIN
2552 (
2553 t2 AS SQ4_alias2
2554 JOIN t1 AS SQ4_alias3
2555 ON SQ4_alias3.col_int_key = SQ4_alias2.col_int_nokey
2556 )
2557 ON SQ4_alias3.pk = SQ4_alias2.col_int_nokey
2558 )
2559 ";
2560 EXECUTE prep_stmt;
2561 field1 field2
2562 EXECUTE prep_stmt;
2563 field1 field2
2564 DROP TABLE t1,t2,t3;
2565 #
2566 # Bug#19063289 CRASH IN PRINT_TABLE_ARRAY ON 2ND EXECUTION OF PS WITH SUBQUERY AND VIEW
2567 #
2568 CREATE TABLE B (
2569 pk INTEGER,
2570 col_int_key INTEGER,
2571 col_varchar_nokey VARCHAR(1)
2572 );
2573 CREATE TABLE C (
2574 col_int_nokey INTEGER,
2575 col_int_key INTEGER,
2576 col_varchar_key VARCHAR(1),
2577 col_varchar_nokey VARCHAR(1)
2578 ) ;
2579 CREATE TABLE CC (
2580 pk INTEGER,
2581 col_int_key INTEGER
2582 ) ENGINE=MYISAM;
2583 INSERT INTO CC VALUES (1, 0),(2, 7);
2584 PREPARE stmt FROM "
2585 SELECT
2586 alias1.col_int_key AS field1 ,
2587 COUNT(alias1.pk) AS field2
2588 FROM
2589 CC AS alias1
2590 GROUP BY field1
2591 HAVING
2592 alias1.col_int_key >
2593 (
2594 SELECT
2595 MAX( SQ2_alias2.col_int_key ) AS SQ2_field1
2596 FROM
2597 C AS SQ2_alias1 LEFT JOIN
2598 ( B AS SQ2_alias2 INNER JOIN C AS SQ2_alias3
2599 ON 1)
2600 ON 1
2601 )
2602 OR field2 < 2
2603 ";
2604 EXECUTE stmt;
2605 field1 field2
2606 0 1
2607 7 1
2608 EXECUTE stmt;
2609 field1 field2
2610 0 1
2611 7 1
2612 DROP TABLE B,C,CC;
2613 #
2614 # Bug #23259872: OPTIMIZER CHOOSES TO USE NON PRIMARY
2615 # INDEX, EVEN THOUGH COST IS HIGHER
2616 #
2617 CREATE TABLE t1 (
2618 a TINYTEXT NOT NULL,
2619 b TINYINT(3) UNSIGNED NOT NULL,
2620 PRIMARY KEY (a(32),b)
2621 ) ENGINE=MyISAM, CHARSET utf8mb4;
2622 Warnings:
2623 Warning 1681 Integer display width is deprecated and will be removed in a future release.
2624 CREATE TABLE t2 (
2625 a TINYTEXT NOT NULL,
2626 b TINYINT(3) UNSIGNED NOT NULL,
2627 PRIMARY KEY (a(32),b)
2628 ) ENGINE=INNODB, CHARSET utf8mb4;
2629 Warnings:
2630 Warning 1681 Integer display width is deprecated and will be removed in a future release.
2631 INSERT INTO t1 VALUES ('a',1),('a',2);
2632 INSERT INTO t2 VALUES ('a',1),('a',2),('a',3),('b',1),('c',1),('c',4),('e',2);
2633 ANALYZE TABLE t1;
2634 Table Op Msg_type Msg_text
2635 test.t1 analyze status OK
2636 ANALYZE TABLE t2;
2637 Table Op Msg_type Msg_text
2638 test.t2 analyze status OK
2639 EXPLAIN SELECT * FROM t1 WHERE a='a' AND b in (1,2);
2640 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2641 1 SIMPLE t1 NULL range PRIMARY PRIMARY 131 NULL 2 100.00 Using where
2642 Warnings:
2643 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = 'a') and (`test`.`t1`.`b` in (1,2)))
2644 SELECT TRACE into @trace FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
2645 SELECT @trace RLIKE "range_uses_more_keyparts";
2646 @trace RLIKE "range_uses_more_keyparts"
2647 1
2648 EXPLAIN SELECT * FROM t2 WHERE a='a' AND b in (1,2);
2649 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2650 1 SIMPLE t2 NULL range PRIMARY PRIMARY 131 NULL 2 100.00 Using where
2651 Warnings:
2652 Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where ((`test`.`t2`.`a` = 'a') and (`test`.`t2`.`b` in (1,2)))
2653 SELECT TRACE into @trace FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
2654 SELECT @trace RLIKE "range_uses_more_keyparts";
2655 @trace RLIKE "range_uses_more_keyparts"
2656 1
2657 DROP TABLE t1, t2;
2658 #
2659 # Bug #23227428: SQL PLAN IS NOT ACCORDING WITH OPTIMIZER_TRACE
2660 #
2661 CREATE TABLE t1(c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
2662 c2 CHAR(12)) ENGINE=INNODB;
2663 ANALYZE TABLE t1;
2664 Table Op Msg_type Msg_text
2665 test.t1 analyze status OK
2666 EXPLAIN SELECT c1, c2 FROM t1 ORDER BY c1 DESC LIMIT 1;
2667 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2668 1 SIMPLE t1 NULL index NULL PRIMARY 4 NULL 1 100.00 Backward index scan
2669 Warnings:
2670 Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` order by `test`.`t1`.`c1` desc limit 1
2671 SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
2672 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
2673 EXPLAIN SELECT c1, c2 FROM t1 ORDER BY c1 DESC LIMIT 1 {
2674 "steps": [
2675 {
2676 "join_preparation": {
2677 "select#": 1,
2678 "steps": [
2679 {
2680 "expanded_query": "/* select#1 */ select `t1`.`c1` AS `c1`,`t1`.`c2` AS `c2` from `t1` order by `t1`.`c1` desc limit 1"
2681 }
2682 ] /* steps */
2683 } /* join_preparation */
2684 },
2685 {
2686 "join_optimization": {
2687 "select#": 1,
2688 "steps": [
2689 {
2690 "substitute_generated_columns": {
2691 } /* substitute_generated_columns */
2692 },
2693 {
2694 "table_dependencies": [
2695 {
2696 "table": "`t1`",
2697 "row_may_be_null": false,
2698 "map_bit": 0,
2699 "depends_on_map_bits": [
2700 ] /* depends_on_map_bits */
2701 }
2702 ] /* table_dependencies */
2703 },
2704 {
2705 "rows_estimation": [
2706 {
2707 "table": "`t1`",
2708 "table_scan": {
2709 "rows": 1000,
2710 "cost": NN
2711 } /* table_scan */
2712 }
2713 ] /* rows_estimation */
2714 },
2715 {
2716 "considered_execution_plans": [
2717 {
2718 "plan_prefix": [
2719 ] /* plan_prefix */,
2720 "table": "`t1`",
2721 "best_access_path": {
2722 "considered_access_paths": [
2723 {
2724 "rows_to_scan": 1000,
2725 "access_type": "scan",
2726 "resulting_rows": 1000,
2727 "cost": NN,
2728 "chosen": true
2729 }
2730 ] /* considered_access_paths */
2731 } /* best_access_path */,
2732 "condition_filtering_pct": 100,
2733 "rows_for_plan": 1000,
2734 "cost_for_plan": NN,
2735 "chosen": true
2736 }
2737 ] /* considered_execution_plans */
2738 },
2739 {
2740 "attaching_conditions_to_tables": {
2741 "original_condition": null,
2742 "attached_conditions_computation": [
2743 ] /* attached_conditions_computation */,
2744 "attached_conditions_summary": [
2745 {
2746 "table": "`t1`",
2747 "attached": null
2748 }
2749 ] /* attached_conditions_summary */
2750 } /* attaching_conditions_to_tables */
2751 },
2752 {
2753 "optimizing_distinct_group_by_order_by": {
2754 "simplifying_order_by": {
2755 "original_clause": "`t1`.`c1` desc",
2756 "items": [
2757 {
2758 "item": "`t1`.`c1`"
2759 }
2760 ] /* items */,
2761 "resulting_clause_is_simple": true,
2762 "resulting_clause": "`t1`.`c1` desc"
2763 } /* simplifying_order_by */
2764 } /* optimizing_distinct_group_by_order_by */
2765 },
2766 {
2767 "reconsidering_access_paths_for_index_ordering": {
2768 "clause": "ORDER BY",
2769 "steps": [
2770 ] /* steps */,
2771 "index_order_summary": {
2772 "table": "`t1`",
2773 "index_provides_order": true,
2774 "order_direction": "desc",
2775 "index": "PRIMARY",
2776 "plan_changed": true,
2777 "access_type": "index"
2778 } /* index_order_summary */
2779 } /* reconsidering_access_paths_for_index_ordering */
2780 },
2781 {
2782 "finalizing_table_conditions": [
2783 ] /* finalizing_table_conditions */
2784 },
2785 {
2786 "refine_plan": [
2787 {
2788 "table": "`t1`"
2789 }
2790 ] /* refine_plan */
2791 },
2792 {
2793 "considering_tmp_tables": [
2794 ] /* considering_tmp_tables */
2795 }
2796 ] /* steps */
2797 } /* join_optimization */
2798 },
2799 {
2800 "join_explain": {
2801 "select#": 1,
2802 "steps": [
2803 ] /* steps */
2804 } /* join_explain */
2805 }
2806 ] /* steps */
2807 } 0 0
2808 DROP TABLE t1;
2809 # Bug#29016383 - fil_report_invalid_page_access
2810 CREATE TABLE t1 (
2811 pk INTEGER PRIMARY KEY,
2812 a INTEGER,
2813 b VARCHAR(20)
2814 );
2815 CREATE TABLE t2 (
2816 pk INTEGER PRIMARY KEY,
2817 c VARCHAR(20),
2818 KEY (c)
2819 );
2820 INSERT INTO t2 VALUES
2821 (1,'koeiwsgpmfyvvuqvtjnc'),
2822 (2,'pmfyvvuqvtjncdsvq'),
2823 (3,'mfyv'),
2824 (4,NULL);
2825 SET end_markers_in_json=off;
2826 SELECT alias1.*
2827 FROM t2 AS alias1, t2 AS alias2
2828 WHERE alias2.c IN (
2829 WITH qn AS (SELECT b FROM t1 WHERE a = alias1.pk)
2830 SELECT * FROM qn);
2831 pk c
2832 # Prove that the predicate from the WHERE clause of the CTE
2833 # is decorrelated:
2834 SELECT json_extract(trace,'$**.decorrelated_predicates')
2835 FROM information_schema.optimizer_trace;
2836 json_extract(trace,'$**.decorrelated_predicates')
2837 NULL
2838 DROP TABLE t1, t2;