Debian Codegraph @

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

raw
1 SET optimizer_trace_max_mem_size=1048576;
2 SET @@session.optimizer_trace="enabled=on";
3 set optimizer_switch="derived_condition_pushdown=on";
4 set optimizer_switch="derived_merge=off";
5 set sql_mode="";
6 CREATE TABLE t1 (f1 INTEGER, f2 INTEGER, f3 INTEGER,
7 KEY(f1), KEY(f1,f2), KEY(f3));
8 INSERT INTO t1 VALUES (1,26,1);
9 INSERT INTO t1 VALUES (2,32,1);
10 INSERT INTO t1 VALUES (3,90,1);
11 ANALYZE TABLE t1;
12 Table Op Msg_type Msg_text
13 test.t1 analyze status OK
14 SELECT * FROM (SELECT f1, f2, SUM(f3) OVER(PARTITION BY f1,f2)
15 FROM t1 GROUP BY f1) as dt WHERE f1 > 2 and f2 > 30 and (f1+f2) > 40;
16 f1 f2 SUM(f3) OVER(PARTITION BY f1,f2)
17 3 90 1
18 SELECT trace FROM information_schema.optimizer_trace;
19 trace
20 {
21 "steps": [
22 {
23 "join_preparation": {
24 "select#": 1,
25 "steps": [
26 {
27 "join_preparation": {
28 "select#": 2,
29 "steps": [
30 {
31 "expanded_query": "/* select#2 */ select `t1`.`f1` AS `f1`,`t1`.`f2` AS `f2`,sum(`t1`.`f3`) OVER (PARTITION BY `t1`.`f1`,`t1`.`f2` ) AS `SUM(f3) OVER(PARTITION BY f1,f2)` from `t1` group by `t1`.`f1`"
32 }
33 ]
34 }
35 },
36 {
37 "derived": {
38 "table": " `dt`",
39 "select#": 2,
40 "materialized": true
41 }
42 },
43 {
44 "expanded_query": "/* select#1 */ select `dt`.`f1` AS `f1`,`dt`.`f2` AS `f2`,`dt`.`SUM(f3) OVER(PARTITION BY f1,f2)` AS `SUM(f3) OVER(PARTITION BY f1,f2)` from (/* select#2 */ select `t1`.`f1` AS `f1`,`t1`.`f2` AS `f2`,sum(`t1`.`f3`) OVER (PARTITION BY `t1`.`f1`,`t1`.`f2` ) AS `SUM(f3) OVER(PARTITION BY f1,f2)` from `t1` group by `t1`.`f1`) `dt` where ((`dt`.`f1` > 2) and (`dt`.`f2` > 30) and ((`dt`.`f1` + `dt`.`f2`) > 40))"
45 },
46 {
47 "condition_pushdown_to_derived": {
48 "table": " `dt`",
49 "original_condition": "((`dt`.`f1` > 2) and (`dt`.`f2` > 30) and ((`dt`.`f1` + `dt`.`f2`) > 40))",
50 "condition_to_push": "((`dt`.`f1` > 2) and (`dt`.`f2` > 30) and ((`dt`.`f1` + `dt`.`f2`) > 40))",
51 "remaining_condition": null,
52 "pushdown_to_query_blocks": [
53 {
54 "query_block": 2,
55 "pushing_past_window_functions": {
56 "condition_to_push_to_having": "((`dt`.`f1` > 2) and (`dt`.`f2` > 30) and ((`dt`.`f1` + `dt`.`f2`) > 40))",
57 "remaining_condition": null
58 },
59 "pushing_past_group_by": {
60 "condition_to_push_to_having": "((`dt`.`f2` > 30) and ((`dt`.`f1` + `dt`.`f2`) > 40))",
61 "condition_to_push_to_where": "(`dt`.`f1` > 2)",
62 "remaining_condition": null
63 },
64 "pushed_having_condition": "((`dt`.`f2` > 30) and ((`dt`.`f1` + `dt`.`f2`) > 40))",
65 "pushed_where_condition": "(`dt`.`f1` > 2)",
66 "remaining_condition": null
67 }
68 ]
69 }
70 }
71 ]
72 }
73 },
74 {
75 "join_optimization": {
76 "select#": 1,
77 "steps": [
78 {
79 "join_optimization": {
80 "select#": 2,
81 "steps": [
82 {
83 "condition_processing": {
84 "condition": "WHERE",
85 "original_condition": "(`t1`.`f1` > 2)",
86 "steps": [
87 {
88 "transformation": "equality_propagation",
89 "resulting_condition": "(`t1`.`f1` > 2)"
90 },
91 {
92 "transformation": "constant_propagation",
93 "resulting_condition": "(`t1`.`f1` > 2)"
94 },
95 {
96 "transformation": "trivial_condition_removal",
97 "resulting_condition": "(`t1`.`f1` > 2)"
98 }
99 ]
100 }
101 },
102 {
103 "condition_processing": {
104 "condition": "HAVING",
105 "original_condition": "((`t1`.`f2` > 30) and ((`t1`.`f1` + `t1`.`f2`) > 40))",
106 "steps": [
107 {
108 "transformation": "constant_propagation",
109 "resulting_condition": "((`t1`.`f2` > 30) and ((`t1`.`f1` + `t1`.`f2`) > 40))"
110 },
111 {
112 "transformation": "trivial_condition_removal",
113 "resulting_condition": "((`t1`.`f2` > 30) and ((`t1`.`f1` + `t1`.`f2`) > 40))"
114 }
115 ]
116 }
117 },
118 {
119 "substitute_generated_columns": {
120 }
121 },
122 {
123 "table_dependencies": [
124 {
125 "table": "`t1`",
126 "row_may_be_null": false,
127 "map_bit": 0,
128 "depends_on_map_bits": [
129 ]
130 }
131 ]
132 },
133 {
134 "ref_optimizer_key_uses": [
135 ]
136 },
137 {
138 "rows_estimation": [
139 {
140 "table": "`t1`",
141 "range_analysis": {
142 "table_scan": {
143 "rows": 3,
144 "cost": 2.65
145 },
146 "potential_range_indexes": [
147 {
148 "index": "f1",
149 "usable": true,
150 "key_parts": [
151 "f1"
152 ]
153 },
154 {
155 "index": "f1_2",
156 "usable": true,
157 "key_parts": [
158 "f1",
159 "f2"
160 ]
161 },
162 {
163 "index": "f3",
164 "usable": false,
165 "cause": "not_applicable"
166 }
167 ],
168 "setup_range_conditions": [
169 ],
170 "group_index_range": {
171 "potential_group_range_indexes": [
172 {
173 "index": "f1",
174 "usable": false,
175 "cause": "not_covering"
176 },
177 {
178 "index": "f1_2",
179 "usable": false,
180 "cause": "not_covering"
181 }
182 ]
183 },
184 "skip_scan_range": {
185 "chosen": false,
186 "cause": "has_group_by"
187 },
188 "analyzing_range_alternatives": {
189 "range_scan_alternatives": [
190 {
191 "index": "f1",
192 "ranges": [
193 "2 < f1"
194 ],
195 "index_dives_for_eq_ranges": true,
196 "rowid_ordered": false,
197 "using_mrr": false,
198 "index_only": false,
199 "in_memory": 1,
200 "rows": 1,
201 "cost": 0.61,
202 "chosen": true
203 },
204 {
205 "index": "f1_2",
206 "ranges": [
207 "2 < f1"
208 ],
209 "index_dives_for_eq_ranges": true,
210 "rowid_ordered": false,
211 "using_mrr": false,
212 "index_only": false,
213 "in_memory": 1,
214 "rows": 1,
215 "cost": 0.61,
216 "chosen": false,
217 "cause": "cost"
218 }
219 ],
220 "analyzing_roworder_intersect": {
221 "usable": false,
222 "cause": "too_few_roworder_scans"
223 }
224 },
225 "chosen_range_access_summary": {
226 "range_access_plan": {
227 "type": "range_scan",
228 "index": "f1",
229 "rows": 1,
230 "ranges": [
231 "2 < f1"
232 ]
233 },
234 "rows_for_plan": 1,
235 "cost_for_plan": 0.61,
236 "chosen": true
237 }
238 }
239 }
240 ]
241 },
242 {
243 "considered_execution_plans": [
244 {
245 "plan_prefix": [
246 ],
247 "table": "`t1`",
248 "best_access_path": {
249 "considered_access_paths": [
250 {
251 "rows_to_scan": 1,
252 "filtering_effect": [
253 ],
254 "final_filtering_effect": 1,
255 "access_type": "range",
256 "range_details": {
257 "used_index": "f1"
258 },
259 "resulting_rows": 1,
260 "cost": 0.71,
261 "chosen": true,
262 "use_tmp_table": true
263 }
264 ]
265 },
266 "condition_filtering_pct": 100,
267 "rows_for_plan": 1,
268 "cost_for_plan": 0.71,
269 "sort_cost": 1,
270 "new_cost_for_plan": 1.71,
271 "chosen": true,
272 "windowing_sort_cost": 1,
273 "new_cost_for_plan": 2.71
274 }
275 ]
276 },
277 {
278 "attaching_conditions_to_tables": {
279 "original_condition": "(`t1`.`f1` > 2)",
280 "attached_conditions_computation": [
281 ],
282 "attached_conditions_summary": [
283 {
284 "table": "`t1`",
285 "attached": "(`t1`.`f1` > 2)"
286 }
287 ]
288 }
289 },
290 {
291 "optimizing_distinct_group_by_order_by": {
292 "simplifying_group_by": {
293 "original_clause": "`t1`.`f1`",
294 "items": [
295 {
296 "item": "`t1`.`f1`"
297 }
298 ],
299 "resulting_clause_is_simple": false,
300 "resulting_clause": "`t1`.`f1`"
301 }
302 }
303 },
304 {
305 "finalizing_table_conditions": [
306 {
307 "table": "`t1`",
308 "original_table_condition": "(`t1`.`f1` > 2)",
309 "final_table_condition ": "(`t1`.`f1` > 2)"
310 }
311 ]
312 },
313 {
314 "refine_plan": [
315 {
316 "table": "`t1`",
317 "pushed_index_condition": "(`t1`.`f1` > 2)",
318 "table_condition_attached": null
319 }
320 ]
321 },
322 {
323 "considering_tmp_tables": [
324 {
325 "adding_tmp_table_in_plan_at_position": 1,
326 "write_method": "write_all_rows"
327 },
328 {
329 "adding_tmp_table_in_plan_at_position": 2,
330 "cause": "output_for_window_functions",
331 "with_buffer": true,
332 "write_method": "write_all_rows"
333 },
334 {
335 "creating_tmp_table": {
336 "tmp_table_info": {
337 "table": "intermediate_tmp_table",
338 "columns": 3,
339 "row_length": 13,
340 "key_length": 0,
341 "unique_constraint": false,
342 "makes_grouped_rows": false,
343 "cannot_insert_duplicates": false,
344 "location": "TempTable"
345 }
346 }
347 },
348 {
349 "filesort": {
350 "adding_sort_to_table": ""
351 }
352 }
353 ]
354 }
355 ]
356 }
357 },
358 {
359 "table_dependencies": [
360 {
361 "table": " `dt`",
362 "row_may_be_null": false,
363 "map_bit": 0,
364 "depends_on_map_bits": [
365 ]
366 }
367 ]
368 },
369 {
370 "rows_estimation": [
371 {
372 "table": " `dt`",
373 "table_scan": {
374 "rows": 2,
375 "cost": 2.525
376 }
377 }
378 ]
379 },
380 {
381 "considered_execution_plans": [
382 {
383 "plan_prefix": [
384 ],
385 "table": " `dt`",
386 "best_access_path": {
387 "considered_access_paths": [
388 {
389 "rows_to_scan": 2,
390 "access_type": "scan",
391 "resulting_rows": 2,
392 "cost": 2.725,
393 "chosen": true
394 }
395 ]
396 },
397 "condition_filtering_pct": 100,
398 "rows_for_plan": 2,
399 "cost_for_plan": 2.725,
400 "chosen": true
401 }
402 ]
403 },
404 {
405 "attaching_conditions_to_tables": {
406 "original_condition": null,
407 "attached_conditions_computation": [
408 ],
409 "attached_conditions_summary": [
410 {
411 "table": " `dt`",
412 "attached": null
413 }
414 ]
415 }
416 },
417 {
418 "finalizing_table_conditions": [
419 ]
420 },
421 {
422 "refine_plan": [
423 {
424 "table": " `dt`"
425 }
426 ]
427 }
428 ]
429 }
430 },
431 {
432 "join_execution": {
433 "select#": 1,
434 "steps": [
435 {
436 "creating_tmp_table": {
437 "tmp_table_info": {
438 "table": "dt",
439 "in_plan_at_position": 0,
440 "columns": 3,
441 "row_length": 24,
442 "key_length": 0,
443 "unique_constraint": false,
444 "makes_grouped_rows": false,
445 "cannot_insert_duplicates": false,
446 "location": "TempTable"
447 }
448 }
449 },
450 {
451 "materialize": {
452 "select#": 2,
453 "steps": [
454 {
455 "creating_tmp_table": {
456 "tmp_table_info": {
457 "table": "<temporary>",
458 "in_plan_at_position": 1,
459 "columns": 3,
460 "row_length": 15,
461 "key_length": 5,
462 "unique_constraint": false,
463 "makes_grouped_rows": true,
464 "cannot_insert_duplicates": false,
465 "location": "TempTable"
466 }
467 }
468 },
469 {
470 "materialize": {
471 "select#": 2,
472 "steps": [
473 ]
474 }
475 },
476 {
477 "sorting_table": "<temporary>",
478 "filesort_information": [
479 {
480 "direction": "asc",
481 "expression": "`t1`.`f1`"
482 },
483 {
484 "direction": "asc",
485 "expression": "`t1`.`f2`"
486 }
487 ],
488 "filesort_priority_queue_optimization": {
489 "usable": false,
490 "cause": "not applicable (no LIMIT)"
491 },
492 "filesort_execution": [
493 ],
494 "filesort_summary": {
495 "memory_available": 262144,
496 "key_size": 18,
497 "row_size": 31,
498 "max_rows_per_buffer": 15,
499 "num_rows_estimate": 15,
500 "num_rows_found": 1,
501 "num_initial_chunks_spilled_to_disk": 0,
502 "peak_memory_used": "NNN",
503 "sort_algorithm": "none",
504 "unpacked_addon_fields": "skip_heuristic",
505 "sort_mode": "<fixed_sort_key, additional_fields>"
506 }
507 }
508 ]
509 }
510 }
511 ]
512 }
513 }
514 ]
515 }
516 SELECT * FROM
517 (SELECT (i+j) AS g, f1 FROM
518 (SELECT (f1+f2) AS i, (f1-f2) AS j FROM
519 (SELECT f1+10 AS f1, f2+10 AS f2 FROM t1) AS dt0) AS dt1,
520 (SELECT f1, f2 FROM t1) AS dt2) AS dt3
521 WHERE g > 26 and g+f1 > 31;
522 g f1
523 SELECT trace FROM information_schema.optimizer_trace;
524 trace
525 {
526 "steps": [
527 {
528 "join_preparation": {
529 "select#": 1,
530 "steps": [
531 {
532 "join_preparation": {
533 "select#": 2,
534 "steps": [
535 {
536 "join_preparation": {
537 "select#": 3,
538 "steps": [
539 {
540 "join_preparation": {
541 "select#": 4,
542 "steps": [
543 {
544 "expanded_query": "/* select#4 */ select (`t1`.`f1` + 10) AS `f1`,(`t1`.`f2` + 10) AS `f2` from `t1`"
545 }
546 ]
547 }
548 },
549 {
550 "derived": {
551 "table": " `dt0`",
552 "select#": 4,
553 "materialized": true
554 }
555 },
556 {
557 "expanded_query": "/* select#3 */ select (`dt0`.`f1` + `dt0`.`f2`) AS `i`,(`dt0`.`f1` - `dt0`.`f2`) AS `j` from (/* select#4 */ select (`t1`.`f1` + 10) AS `f1`,(`t1`.`f2` + 10) AS `f2` from `t1`) `dt0`"
558 }
559 ]
560 }
561 },
562 {
563 "derived": {
564 "table": " `dt1`",
565 "select#": 3,
566 "materialized": true
567 }
568 },
569 {
570 "join_preparation": {
571 "select#": 5,
572 "steps": [
573 {
574 "expanded_query": "/* select#5 */ select `t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1`"
575 }
576 ]
577 }
578 },
579 {
580 "derived": {
581 "table": " `dt2`",
582 "select#": 5,
583 "materialized": true
584 }
585 },
586 {
587 "expanded_query": "/* select#2 */ select (`dt1`.`i` + `dt1`.`j`) AS `g`,`dt2`.`f1` AS `f1` from (/* select#3 */ select (`dt0`.`f1` + `dt0`.`f2`) AS `i`,(`dt0`.`f1` - `dt0`.`f2`) AS `j` from (/* select#4 */ select (`t1`.`f1` + 10) AS `f1`,(`t1`.`f2` + 10) AS `f2` from `t1`) `dt0`) `dt1` join (/* select#5 */ select `t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1`) `dt2`"
588 }
589 ]
590 }
591 },
592 {
593 "derived": {
594 "table": " `dt3`",
595 "select#": 2,
596 "materialized": true
597 }
598 },
599 {
600 "expanded_query": "/* select#1 */ select `dt3`.`g` AS `g`,`dt3`.`f1` AS `f1` from (/* select#2 */ select (`dt1`.`i` + `dt1`.`j`) AS `g`,`dt2`.`f1` AS `f1` from (/* select#3 */ select (`dt0`.`f1` + `dt0`.`f2`) AS `i`,(`dt0`.`f1` - `dt0`.`f2`) AS `j` from (/* select#4 */ select (`t1`.`f1` + 10) AS `f1`,(`t1`.`f2` + 10) AS `f2` from `t1`) `dt0`) `dt1` join (/* select#5 */ select `t1`.`f1` AS `f1`,`t1`.`f2` AS `f2` from `t1`) `dt2`) `dt3` where ((`dt3`.`g` > 26) and ((`dt3`.`g` + `dt3`.`f1`) > 31))"
601 },
602 {
603 "condition_pushdown_to_derived": {
604 "table": " `dt3`",
605 "original_condition": "((`dt3`.`g` > 26) and ((`dt3`.`g` + `dt3`.`f1`) > 31))",
606 "condition_to_push": "((`dt3`.`g` > 26) and ((`dt3`.`g` + `dt3`.`f1`) > 31))",
607 "remaining_condition": null,
608 "pushdown_to_query_blocks": [
609 {
610 "query_block": 2,
611 "pushed_having_condition": null,
612 "pushed_where_condition": "((`dt3`.`g` > 26) and ((`dt3`.`g` + `dt3`.`f1`) > 31))",
613 "remaining_condition": null
614 }
615 ]
616 }
617 },
618 {
619 "condition_pushdown_to_derived": {
620 "table": " `dt1`",
621 "original_condition": "(((`dt1`.`i` + `dt1`.`j`) > 26) and (((`dt1`.`i` + `dt1`.`j`) + `dt2`.`f1`) > 31))",
622 "condition_to_push": "((`dt1`.`i` + `dt1`.`j`) > 26)",
623 "remaining_condition": "(((`dt1`.`i` + `dt1`.`j`) + `dt2`.`f1`) > 31)",
624 "pushdown_to_query_blocks": [
625 {
626 "query_block": 3,
627 "pushed_having_condition": null,
628 "pushed_where_condition": "((`dt1`.`i` + `dt1`.`j`) > 26)",
629 "remaining_condition": "(((`dt1`.`i` + `dt1`.`j`) + `dt2`.`f1`) > 31)"
630 }
631 ]
632 }
633 },
634 {
635 "condition_pushdown_to_derived": {
636 "table": " `dt2`",
637 "original_condition": "(((`dt1`.`i` + `dt1`.`j`) + `dt2`.`f1`) > 31)",
638 "condition_to_push": null,
639 "remaining_condition": "(((`dt1`.`i` + `dt1`.`j`) + `dt2`.`f1`) > 31)"
640 }
641 },
642 {
643 "condition_pushdown_to_derived": {
644 "table": " `dt0`",
645 "original_condition": "(((`dt0`.`f1` + `dt0`.`f2`) + (`dt0`.`f1` - `dt0`.`f2`)) > 26)",
646 "condition_to_push": "(((`dt0`.`f1` + `dt0`.`f2`) + (`dt0`.`f1` - `dt0`.`f2`)) > 26)",
647 "remaining_condition": null,
648 "pushdown_to_query_blocks": [
649 {
650 "query_block": 4,
651 "pushed_having_condition": null,
652 "pushed_where_condition": "(((`dt0`.`f1` + `dt0`.`f2`) + (`dt0`.`f1` - `dt0`.`f2`)) > 26)",
653 "remaining_condition": null
654 }
655 ]
656 }
657 }
658 ]
659 }
660 },
661 {
662 "join_optimization": {
663 "select#": 1,
664 "steps": [
665 {
666 "join_optimization": {
667 "select#": 2,
668 "steps": [
669 {
670 "join_optimization": {
671 "select#": 3,
672 "steps": [
673 {
674 "join_optimization": {
675 "select#": 4,
676 "steps": [
677 {
678 "condition_processing": {
679 "condition": "WHERE",
680 "original_condition": "((((`t1`.`f1` + 10) + (`t1`.`f2` + 10)) + ((`t1`.`f1` + 10) - (`t1`.`f2` + 10))) > 26)",
681 "steps": [
682 {
683 "transformation": "equality_propagation",
684 "resulting_condition": "((((`t1`.`f1` + 10) + (`t1`.`f2` + 10)) + ((`t1`.`f1` + 10) - (`t1`.`f2` + 10))) > 26)"
685 },
686 {
687 "transformation": "constant_propagation",
688 "resulting_condition": "((((`t1`.`f1` + 10) + (`t1`.`f2` + 10)) + ((`t1`.`f1` + 10) - (`t1`.`f2` + 10))) > 26)"
689 },
690 {
691 "transformation": "trivial_condition_removal",
692 "resulting_condition": "((((`t1`.`f1` + 10) + (`t1`.`f2` + 10)) + ((`t1`.`f1` + 10) - (`t1`.`f2` + 10))) > 26)"
693 }
694 ]
695 }
696 },
697 {
698 "substitute_generated_columns": {
699 }
700 },
701 {
702 "table_dependencies": [
703 {
704 "table": "`t1`",
705 "row_may_be_null": false,
706 "map_bit": 0,
707 "depends_on_map_bits": [
708 ]
709 }
710 ]
711 },
712 {
713 "ref_optimizer_key_uses": [
714 ]
715 },
716 {
717 "rows_estimation": [
718 {
719 "table": "`t1`",
720 "range_analysis": {
721 "table_scan": {
722 "rows": 3,
723 "cost": 2.65
724 },
725 "potential_range_indexes": [
726 {
727 "index": "f1",
728 "usable": false,
729 "cause": "not_applicable"
730 },
731 {
732 "index": "f1_2",
733 "usable": true,
734 "key_parts": [
735 "f1",
736 "f2"
737 ]
738 },
739 {
740 "index": "f3",
741 "usable": false,
742 "cause": "not_applicable"
743 }
744 ],
745 "best_covering_index_scan": {
746 "index": "f1_2",
747 "cost": 0.550975,
748 "chosen": true
749 },
750 "setup_range_conditions": [
751 ],
752 "group_index_range": {
753 "chosen": false,
754 "cause": "not_group_by_or_distinct"
755 },
756 "skip_scan_range": {
757 "chosen": false,
758 "cause": "disjuntive_predicate_present"
759 }
760 }
761 }
762 ]
763 },
764 {
765 "considered_execution_plans": [
766 {
767 "plan_prefix": [
768 ],
769 "table": "`t1`",
770 "best_access_path": {
771 "considered_access_paths": [
772 {
773 "rows_to_scan": 3,
774 "filtering_effect": [
775 ],
776 "final_filtering_effect": 1,
777 "access_type": "scan",
778 "resulting_rows": 3,
779 "cost": 0.55,
780 "chosen": true
781 }
782 ]
783 },
784 "condition_filtering_pct": 100,
785 "rows_for_plan": 3,
786 "cost_for_plan": 0.55,
787 "chosen": true
788 }
789 ]
790 },
791 {
792 "attaching_conditions_to_tables": {
793 "original_condition": "((((`t1`.`f1` + 10) + (`t1`.`f2` + 10)) + ((`t1`.`f1` + 10) - (`t1`.`f2` + 10))) > 26)",
794 "attached_conditions_computation": [
795 ],
796 "attached_conditions_summary": [
797 {
798 "table": "`t1`",
799 "attached": "((((`t1`.`f1` + 10) + (`t1`.`f2` + 10)) + ((`t1`.`f1` + 10) - (`t1`.`f2` + 10))) > 26)"
800 }
801 ]
802 }
803 },
804 {
805 "finalizing_table_conditions": [
806 {
807 "table": "`t1`",
808 "original_table_condition": "((((`t1`.`f1` + 10) + (`t1`.`f2` + 10)) + ((`t1`.`f1` + 10) - (`t1`.`f2` + 10))) > 26)",
809 "final_table_condition ": "((((`t1`.`f1` + 10) + (`t1`.`f2` + 10)) + ((`t1`.`f1` + 10) - (`t1`.`f2` + 10))) > 26)"
810 }
811 ]
812 },
813 {
814 "refine_plan": [
815 {
816 "table": "`t1`"
817 }
818 ]
819 }
820 ]
821 }
822 },
823 {
824 "table_dependencies": [
825 {
826 "table": " `dt0`",
827 "row_may_be_null": false,
828 "map_bit": 0,
829 "depends_on_map_bits": [
830 ]
831 }
832 ]
833 },
834 {
835 "rows_estimation": [
836 {
837 "table": " `dt0`",
838 "table_scan": {
839 "rows": 3,
840 "cost": 2.5375
841 }
842 }
843 ]
844 },
845 {
846 "considered_execution_plans": [
847 {
848 "plan_prefix": [
849 ],
850 "table": " `dt0`",
851 "best_access_path": {
852 "considered_access_paths": [
853 {
854 "rows_to_scan": 3,
855 "access_type": "scan",
856 "resulting_rows": 3,
857 "cost": 2.8375,
858 "chosen": true
859 }
860 ]
861 },
862 "condition_filtering_pct": 100,
863 "rows_for_plan": 3,
864 "cost_for_plan": 2.8375,
865 "chosen": true
866 }
867 ]
868 },
869 {
870 "attaching_conditions_to_tables": {
871 "original_condition": null,
872 "attached_conditions_computation": [
873 ],
874 "attached_conditions_summary": [
875 {
876 "table": " `dt0`",
877 "attached": null
878 }
879 ]
880 }
881 },
882 {
883 "finalizing_table_conditions": [
884 ]
885 },
886 {
887 "refine_plan": [
888 {
889 "table": " `dt0`"
890 }
891 ]
892 }
893 ]
894 }
895 },
896 {
897 "join_optimization": {
898 "select#": 5,
899 "steps": [
900 {
901 "table_dependencies": [
902 {
903 "table": "`t1`",
904 "row_may_be_null": false,
905 "map_bit": 0,
906 "depends_on_map_bits": [
907 ]
908 }
909 ]
910 },
911 {
912 "rows_estimation": [
913 {
914 "table": "`t1`",
915 "table_scan": {
916 "rows": 3,
917 "cost": 0.25
918 }
919 }
920 ]
921 },
922 {
923 "considered_execution_plans": [
924 {
925 "plan_prefix": [
926 ],
927 "table": "`t1`",
928 "best_access_path": {
929 "considered_access_paths": [
930 {
931 "rows_to_scan": 3,
932 "access_type": "scan",
933 "resulting_rows": 3,
934 "cost": 0.55,
935 "chosen": true
936 }
937 ]
938 },
939 "condition_filtering_pct": 100,
940 "rows_for_plan": 3,
941 "cost_for_plan": 0.55,
942 "chosen": true
943 }
944 ]
945 },
946 {
947 "attaching_conditions_to_tables": {
948 "original_condition": null,
949 "attached_conditions_computation": [
950 ],
951 "attached_conditions_summary": [
952 {
953 "table": "`t1`",
954 "attached": null
955 }
956 ]
957 }
958 },
959 {
960 "finalizing_table_conditions": [
961 ]
962 },
963 {
964 "refine_plan": [
965 {
966 "table": "`t1`"
967 }
968 ]
969 }
970 ]
971 }
972 },
973 {
974 "condition_processing": {
975 "condition": "WHERE",
976 "original_condition": "(((`dt1`.`i` + `dt1`.`j`) + `dt2`.`f1`) > 31)",
977 "steps": [
978 {
979 "transformation": "equality_propagation",
980 "resulting_condition": "(((`dt1`.`i` + `dt1`.`j`) + `dt2`.`f1`) > 31)"
981 },
982 {
983 "transformation": "constant_propagation",
984 "resulting_condition": "(((`dt1`.`i` + `dt1`.`j`) + `dt2`.`f1`) > 31)"
985 },
986 {
987 "transformation": "trivial_condition_removal",
988 "resulting_condition": "(((`dt1`.`i` + `dt1`.`j`) + `dt2`.`f1`) > 31)"
989 }
990 ]
991 }
992 },
993 {
994 "substitute_generated_columns": {
995 }
996 },
997 {
998 "table_dependencies": [
999 {
1000 "table": " `dt1`",
1001 "row_may_be_null": false,
1002 "map_bit": 0,
1003 "depends_on_map_bits": [
1004 ]
1005 },
1006 {
1007 "table": " `dt2`",
1008 "row_may_be_null": false,
1009 "map_bit": 1,
1010 "depends_on_map_bits": [
1011 ]
1012 }
1013 ]
1014 },
1015 {
1016 "ref_optimizer_key_uses": [
1017 ]
1018 },
1019 {
1020 "rows_estimation": [
1021 {
1022 "table": " `dt1`",
1023 "table_scan": {
1024 "rows": 3,
1025 "cost": 2.5375
1026 }
1027 },
1028 {
1029 "table": " `dt2`",
1030 "table_scan": {
1031 "rows": 3,
1032 "cost": 2.5375
1033 }
1034 }
1035 ]
1036 },
1037 {
1038 "considered_execution_plans": [
1039 {
1040 "plan_prefix": [
1041 ],
1042 "table": " `dt1`",
1043 "best_access_path": {
1044 "considered_access_paths": [
1045 {
1046 "rows_to_scan": 3,
1047 "filtering_effect": [
1048 ],
1049 "final_filtering_effect": 1,
1050 "access_type": "scan",
1051 "resulting_rows": 3,
1052 "cost": 2.8375,
1053 "chosen": true
1054 }
1055 ]
1056 },
1057 "condition_filtering_pct": 100,
1058 "rows_for_plan": 3,
1059 "cost_for_plan": 2.8375,
1060 "rest_of_plan": [
1061 {
1062 "plan_prefix": [
1063 " `dt1`"
1064 ],
1065 "table": " `dt2`",
1066 "best_access_path": {
1067 "considered_access_paths": [
1068 {
1069 "rows_to_scan": 3,
1070 "filtering_effect": [
1071 ],
1072 "final_filtering_effect": 1,
1073 "access_type": "scan",
1074 "using_join_cache": true,
1075 "buffers_needed": 1,
1076 "resulting_rows": 3,
1077 "cost": 3.43799,
1078 "chosen": true
1079 }
1080 ]
1081 },
1082 "condition_filtering_pct": 100,
1083 "rows_for_plan": 9,
1084 "cost_for_plan": 6.27549,
1085 "chosen": true
1086 }
1087 ]
1088 },
1089 {
1090 "plan_prefix": [
1091 ],
1092 "table": " `dt2`",
1093 "best_access_path": {
1094 "considered_access_paths": [
1095 {
1096 "rows_to_scan": 3,
1097 "filtering_effect": [
1098 ],
1099 "final_filtering_effect": 1,
1100 "access_type": "scan",
1101 "resulting_rows": 3,
1102 "cost": 2.8375,
1103 "chosen": true
1104 }
1105 ]
1106 },
1107 "condition_filtering_pct": 100,
1108 "rows_for_plan": 3,
1109 "cost_for_plan": 2.8375,
1110 "pruned_by_heuristic": true
1111 }
1112 ]
1113 },
1114 {
1115 "attaching_conditions_to_tables": {
1116 "original_condition": "(((`dt1`.`i` + `dt1`.`j`) + `dt2`.`f1`) > 31)",
1117 "attached_conditions_computation": [
1118 ],
1119 "attached_conditions_summary": [
1120 {
1121 "table": " `dt1`",
1122 "attached": null
1123 },
1124 {
1125 "table": " `dt2`",
1126 "attached": "(((`dt1`.`i` + `dt1`.`j`) + `dt2`.`f1`) > 31)"
1127 }
1128 ]
1129 }
1130 },
1131 {
1132 "finalizing_table_conditions": [
1133 {
1134 "table": " `dt2`",
1135 "original_table_condition": "(((`dt1`.`i` + `dt1`.`j`) + `dt2`.`f1`) > 31)",
1136 "final_table_condition ": "(((`dt1`.`i` + `dt1`.`j`) + `dt2`.`f1`) > 31)"
1137 }
1138 ]
1139 },
1140 {
1141 "refine_plan": [
1142 {
1143 "table": " `dt1`"
1144 },
1145 {
1146 "table": " `dt2`"
1147 }
1148 ]
1149 }
1150 ]
1151 }
1152 },
1153 {
1154 "table_dependencies": [
1155 {
1156 "table": " `dt3`",
1157 "row_may_be_null": false,
1158 "map_bit": 0,
1159 "depends_on_map_bits": [
1160 ]
1161 }
1162 ]
1163 },
1164 {
1165 "rows_estimation": [
1166 {
1167 "table": " `dt3`",
1168 "table_scan": {
1169 "rows": 9,
1170 "cost": 2.6125
1171 }
1172 }
1173 ]
1174 },
1175 {
1176 "considered_execution_plans": [
1177 {
1178 "plan_prefix": [
1179 ],
1180 "table": " `dt3`",
1181 "best_access_path": {
1182 "considered_access_paths": [
1183 {
1184 "rows_to_scan": 9,
1185 "access_type": "scan",
1186 "resulting_rows": 9,
1187 "cost": 3.5125,
1188 "chosen": true
1189 }
1190 ]
1191 },
1192 "condition_filtering_pct": 100,
1193 "rows_for_plan": 9,
1194 "cost_for_plan": 3.5125,
1195 "chosen": true
1196 }
1197 ]
1198 },
1199 {
1200 "attaching_conditions_to_tables": {
1201 "original_condition": null,
1202 "attached_conditions_computation": [
1203 ],
1204 "attached_conditions_summary": [
1205 {
1206 "table": " `dt3`",
1207 "attached": null
1208 }
1209 ]
1210 }
1211 },
1212 {
1213 "finalizing_table_conditions": [
1214 ]
1215 },
1216 {
1217 "refine_plan": [
1218 {
1219 "table": " `dt3`"
1220 }
1221 ]
1222 }
1223 ]
1224 }
1225 },
1226 {
1227 "join_execution": {
1228 "select#": 1,
1229 "steps": [
1230 {
1231 "creating_tmp_table": {
1232 "tmp_table_info": {
1233 "table": "dt3",
1234 "in_plan_at_position": 0,
1235 "columns": 2,
1236 "row_length": 13,
1237 "key_length": 0,
1238 "unique_constraint": false,
1239 "makes_grouped_rows": false,
1240 "cannot_insert_duplicates": false,
1241 "location": "TempTable"
1242 }
1243 }
1244 },
1245 {
1246 "materialize": {
1247 "select#": 2,
1248 "steps": [
1249 {
1250 "creating_tmp_table": {
1251 "tmp_table_info": {
1252 "table": "dt1",
1253 "in_plan_at_position": 0,
1254 "columns": 2,
1255 "row_length": 17,
1256 "key_length": 0,
1257 "unique_constraint": false,
1258 "makes_grouped_rows": false,
1259 "cannot_insert_duplicates": false,
1260 "location": "TempTable"
1261 }
1262 }
1263 },
1264 {
1265 "materialize": {
1266 "select#": 3,
1267 "steps": [
1268 {
1269 "creating_tmp_table": {
1270 "tmp_table_info": {
1271 "table": "dt0",
1272 "in_plan_at_position": 0,
1273 "columns": 2,
1274 "row_length": 17,
1275 "key_length": 0,
1276 "unique_constraint": false,
1277 "makes_grouped_rows": false,
1278 "cannot_insert_duplicates": false,
1279 "location": "TempTable"
1280 }
1281 }
1282 },
1283 {
1284 "materialize": {
1285 "select#": 4,
1286 "steps": [
1287 ]
1288 }
1289 }
1290 ]
1291 }
1292 }
1293 ]
1294 }
1295 }
1296 ]
1297 }
1298 }
1299 ]
1300 }
1301 SELECT * FROM
1302 (SELECT f1, SUM(f1) OVER() AS f2 FROM t1 GROUP BY f1 UNION
1303 SELECT f1, CAST(f1 AS DECIMAL(10,2)) FROM t1
1304 ) as dt1 WHERE f1 > 26 and f2 > 31;
1305 f1 f2
1306 SELECT trace FROM information_schema.optimizer_trace;
1307 trace
1308 {
1309 "steps": [
1310 {
1311 "join_preparation": {
1312 "select#": 1,
1313 "steps": [
1314 {
1315 "join_preparation": {
1316 "select#": 2,
1317 "steps": [
1318 {
1319 "expanded_query": "/* select#2 */ select `t1`.`f1` AS `f1`,sum(`t1`.`f1`) OVER () AS `f2` from `t1` group by `t1`.`f1`"
1320 }
1321 ]
1322 }
1323 },
1324 {
1325 "join_preparation": {
1326 "select#": 3,
1327 "steps": [
1328 {
1329 "expanded_query": "/* select#3 */ select `t1`.`f1` AS `f1`,cast(`t1`.`f1` as decimal(10,2)) AS `CAST(f1 AS DECIMAL(10,2))` from `t1`"
1330 }
1331 ]
1332 }
1333 },
1334 {
1335 "creating_tmp_table": {
1336 "tmp_table_info": {
1337 "table": "<union temporary>",
1338 "columns": 2,
1339 "row_length": 21,
1340 "key_length": 22,
1341 "unique_constraint": false,
1342 "makes_grouped_rows": false,
1343 "cannot_insert_duplicates": true,
1344 "location": "TempTable"
1345 }
1346 }
1347 },
1348 {
1349 "join_preparation": {
1350 "select#": 4,
1351 "steps": [
1352 {
1353 "expanded_query": "/* select#4 */ select `f1` AS `f1`,`f2` AS `f2` from dual"
1354 }
1355 ]
1356 }
1357 },
1358 {
1359 "derived": {
1360 "table": " `dt1`",
1361 "select#": 2,
1362 "materialized": true
1363 }
1364 },
1365 {
1366 "expanded_query": "/* select#1 */ select `dt1`.`f1` AS `f1`,`dt1`.`f2` AS `f2` from (/* select#2 */ select `t1`.`f1` AS `f1`,sum(`t1`.`f1`) OVER () AS `f2` from `t1` group by `t1`.`f1` union /* select#3 */ select `t1`.`f1` AS `f1`,cast(`t1`.`f1` as decimal(10,2)) AS `CAST(f1 AS DECIMAL(10,2))` from `t1`) `dt1` where ((`dt1`.`f1` > 26) and (`dt1`.`f2` > 31))"
1367 },
1368 {
1369 "condition_pushdown_to_derived": {
1370 "table": " `dt1`",
1371 "original_condition": "((`dt1`.`f1` > 26) and (`dt1`.`f2` > 31))",
1372 "condition_to_push": "((`dt1`.`f1` > 26) and (`dt1`.`f2` > 31))",
1373 "remaining_condition": null,
1374 "pushdown_to_query_blocks": [
1375 {
1376 "query_block": 2,
1377 "pushing_past_window_functions": {
1378 "condition_to_push_to_having": null,
1379 "remaining_condition": "((`dt1`.`f1` > 26) and (`dt1`.`f2` > 31))"
1380 }
1381 },
1382 {
1383 "query_block": 3,
1384 "pushed_having_condition": null,
1385 "pushed_where_condition": "((`dt1`.`f1` > 26) and (`dt1`.`f2` > 31))",
1386 "remaining_condition": "((`dt1`.`f1` > 26) and (`dt1`.`f2` > 31))"
1387 }
1388 ]
1389 }
1390 }
1391 ]
1392 }
1393 },
1394 {
1395 "join_optimization": {
1396 "select#": 1,
1397 "steps": [
1398 {
1399 "join_optimization": {
1400 "select#": 2,
1401 "steps": [
1402 {
1403 "substitute_generated_columns": {
1404 }
1405 },
1406 {
1407 "table_dependencies": [
1408 {
1409 "table": "`t1`",
1410 "row_may_be_null": false,
1411 "map_bit": 0,
1412 "depends_on_map_bits": [
1413 ]
1414 }
1415 ]
1416 },
1417 {
1418 "rows_estimation": [
1419 {
1420 "table": "`t1`",
1421 "const_keys_added": {
1422 "keys": [
1423 "f1",
1424 "f1_2"
1425 ],
1426 "cause": "group_by"
1427 },
1428 "range_analysis": {
1429 "table_scan": {
1430 "rows": 3,
1431 "cost": 2.65
1432 },
1433 "potential_range_indexes": [
1434 {
1435 "index": "f1",
1436 "usable": true,
1437 "key_parts": [
1438 "f1"
1439 ]
1440 },
1441 {
1442 "index": "f1_2",
1443 "usable": true,
1444 "key_parts": [
1445 "f1",
1446 "f2"
1447 ]
1448 },
1449 {
1450 "index": "f3",
1451 "usable": false,
1452 "cause": "not_applicable"
1453 }
1454 ],
1455 "best_covering_index_scan": {
1456 "index": "f1",
1457 "cost": 0.550671,
1458 "chosen": true
1459 },
1460 "group_index_range": {
1461 "potential_group_range_indexes": [
1462 {
1463 "index": "f1",
1464 "covering": true,
1465 "rows": 4,
1466 "cost": 0.85
1467 },
1468 {
1469 "index": "f1_2",
1470 "covering": true,
1471 "rows": 4,
1472 "cost": 0.85
1473 }
1474 ]
1475 },
1476 "best_group_range_summary": {
1477 "type": "index_group",
1478 "index": "f1",
1479 "group_attribute": null,
1480 "min_aggregate": false,
1481 "max_aggregate": false,
1482 "distinct_aggregate": false,
1483 "rows": 4,
1484 "cost": 0.85,
1485 "key_parts_used_for_access": [
1486 "f1"
1487 ],
1488 "ranges": [
1489 ],
1490 "chosen": false,
1491 "cause": "cost"
1492 },
1493 "skip_scan_range": {
1494 "chosen": false,
1495 "cause": "has_group_by"
1496 }
1497 }
1498 }
1499 ]
1500 },
1501 {
1502 "considered_execution_plans": [
1503 {
1504 "plan_prefix": [
1505 ],
1506 "table": "`t1`",
1507 "best_access_path": {
1508 "considered_access_paths": [
1509 {
1510 "rows_to_scan": 3,
1511 "access_type": "scan",
1512 "resulting_rows": 3,
1513 "cost": 0.55,
1514 "chosen": true,
1515 "use_tmp_table": true
1516 }
1517 ]
1518 },
1519 "condition_filtering_pct": 100,
1520 "rows_for_plan": 3,
1521 "cost_for_plan": 0.55,
1522 "sort_cost": 3,
1523 "new_cost_for_plan": 3.55,
1524 "chosen": true
1525 }
1526 ]
1527 },
1528 {
1529 "attaching_conditions_to_tables": {
1530 "original_condition": null,
1531 "attached_conditions_computation": [
1532 ],
1533 "attached_conditions_summary": [
1534 {
1535 "table": "`t1`",
1536 "attached": null
1537 }
1538 ]
1539 }
1540 },
1541 {
1542 "optimizing_distinct_group_by_order_by": {
1543 "simplifying_group_by": {
1544 "original_clause": "`t1`.`f1`",
1545 "items": [
1546 {
1547 "item": "`t1`.`f1`"
1548 }
1549 ],
1550 "resulting_clause_is_simple": false,
1551 "resulting_clause": "`t1`.`f1`"
1552 }
1553 }
1554 },
1555 {
1556 "finalizing_table_conditions": [
1557 ]
1558 },
1559 {
1560 "refine_plan": [
1561 {
1562 "table": "`t1`"
1563 }
1564 ]
1565 },
1566 {
1567 "considering_tmp_tables": [
1568 {
1569 "adding_tmp_table_in_plan_at_position": 1,
1570 "write_method": "write_all_rows"
1571 },
1572 {
1573 "adding_tmp_table_in_plan_at_position": 2,
1574 "cause": "output_for_window_functions",
1575 "with_buffer": true,
1576 "write_method": "write_all_rows"
1577 },
1578 {
1579 "creating_tmp_table": {
1580 "tmp_table_info": {
1581 "table": "intermediate_tmp_table",
1582 "columns": 2,
1583 "row_length": 9,
1584 "key_length": 0,
1585 "unique_constraint": false,
1586 "makes_grouped_rows": false,
1587 "cannot_insert_duplicates": false,
1588 "location": "TempTable"
1589 }
1590 }
1591 }
1592 ]
1593 }
1594 ]
1595 }
1596 },
1597 {
1598 "join_optimization": {
1599 "select#": 3,
1600 "steps": [
1601 {
1602 "condition_processing": {
1603 "condition": "WHERE",
1604 "original_condition": "((`t1`.`f1` > 26) and (cast(`t1`.`f1` as decimal(10,2)) > 31))",
1605 "steps": [
1606 {
1607 "transformation": "equality_propagation",
1608 "resulting_condition": "((`t1`.`f1` > 26) and (cast(`t1`.`f1` as decimal(10,2)) > 31))"
1609 },
1610 {
1611 "transformation": "constant_propagation",
1612 "resulting_condition": "((`t1`.`f1` > 26) and (cast(`t1`.`f1` as decimal(10,2)) > 31))"
1613 },
1614 {
1615 "transformation": "trivial_condition_removal",
1616 "resulting_condition": "((`t1`.`f1` > 26) and (cast(`t1`.`f1` as decimal(10,2)) > 31))"
1617 }
1618 ]
1619 }
1620 },
1621 {
1622 "substitute_generated_columns": {
1623 }
1624 },
1625 {
1626 "table_dependencies": [
1627 {
1628 "table": "`t1`",
1629 "row_may_be_null": false,
1630 "map_bit": 0,
1631 "depends_on_map_bits": [
1632 ]
1633 }
1634 ]
1635 },
1636 {
1637 "ref_optimizer_key_uses": [
1638 ]
1639 },
1640 {
1641 "rows_estimation": [
1642 {
1643 "table": "`t1`",
1644 "range_analysis": {
1645 "table_scan": {
1646 "rows": 3,
1647 "cost": 2.65
1648 },
1649 "potential_range_indexes": [
1650 {
1651 "index": "f1",
1652 "usable": true,
1653 "key_parts": [
1654 "f1"
1655 ]
1656 },
1657 {
1658 "index": "f1_2",
1659 "usable": true,
1660 "key_parts": [
1661 "f1",
1662 "f2"
1663 ]
1664 },
1665 {
1666 "index": "f3",
1667 "usable": false,
1668 "cause": "not_applicable"
1669 }
1670 ],
1671 "best_covering_index_scan": {
1672 "index": "f1",
1673 "cost": 0.550671,
1674 "chosen": true
1675 },
1676 "setup_range_conditions": [
1677 ],
1678 "group_index_range": {
1679 "chosen": false,
1680 "cause": "not_group_by_or_distinct"
1681 },
1682 "skip_scan_range": {
1683 "potential_skip_scan_indexes": [
1684 {
1685 "index": "f1",
1686 "usable": false,
1687 "cause": "prefix_not_const_equality"
1688 },
1689 {
1690 "index": "f1_2",
1691 "usable": false,
1692 "cause": "prefix_not_const_equality"
1693 }
1694 ]
1695 },
1696 "analyzing_range_alternatives": {
1697 "range_scan_alternatives": [
1698 {
1699 "index": "f1",
1700 "ranges": [
1701 "26 < f1"
1702 ],
1703 "index_dives_for_eq_ranges": true,
1704 "rowid_ordered": false,
1705 "using_mrr": false,
1706 "index_only": true,
1707 "in_memory": 1,
1708 "rows": 1,
1709 "cost": 0.36,
1710 "chosen": true
1711 },
1712 {
1713 "index": "f1_2",
1714 "ranges": [
1715 "26 < f1"
1716 ],
1717 "index_dives_for_eq_ranges": true,
1718 "rowid_ordered": false,
1719 "using_mrr": false,
1720 "index_only": true,
1721 "in_memory": 1,
1722 "rows": 1,
1723 "cost": 0.36,
1724 "chosen": false,
1725 "cause": "cost"
1726 }
1727 ],
1728 "analyzing_roworder_intersect": {
1729 "usable": false,
1730 "cause": "too_few_roworder_scans"
1731 }
1732 },
1733 "chosen_range_access_summary": {
1734 "range_access_plan": {
1735 "type": "range_scan",
1736 "index": "f1",
1737 "rows": 1,
1738 "ranges": [
1739 "26 < f1"
1740 ]
1741 },
1742 "rows_for_plan": 1,
1743 "cost_for_plan": 0.36,
1744 "chosen": true
1745 }
1746 }
1747 }
1748 ]
1749 },
1750 {
1751 "considered_execution_plans": [
1752 {
1753 "plan_prefix": [
1754 ],
1755 "table": "`t1`",
1756 "best_access_path": {
1757 "considered_access_paths": [
1758 {
1759 "rows_to_scan": 1,
1760 "filtering_effect": [
1761 ],
1762 "final_filtering_effect": 1,
1763 "access_type": "range",
1764 "range_details": {
1765 "used_index": "f1"
1766 },
1767 "resulting_rows": 1,
1768 "cost": 0.46,
1769 "chosen": true
1770 }
1771 ]
1772 },
1773 "condition_filtering_pct": 100,
1774 "rows_for_plan": 1,
1775 "cost_for_plan": 0.46,
1776 "chosen": true
1777 }
1778 ]
1779 },
1780 {
1781 "attaching_conditions_to_tables": {
1782 "original_condition": "((`t1`.`f1` > 26) and (cast(`t1`.`f1` as decimal(10,2)) > 31))",
1783 "attached_conditions_computation": [
1784 ],
1785 "attached_conditions_summary": [
1786 {
1787 "table": "`t1`",
1788 "attached": "((`t1`.`f1` > 26) and (cast(`t1`.`f1` as decimal(10,2)) > 31))"
1789 }
1790 ]
1791 }
1792 },
1793 {
1794 "finalizing_table_conditions": [
1795 {
1796 "table": "`t1`",
1797 "original_table_condition": "((`t1`.`f1` > 26) and (cast(`t1`.`f1` as decimal(10,2)) > 31))",
1798 "final_table_condition ": "((`t1`.`f1` > 26) and (cast(`t1`.`f1` as decimal(10,2)) > 31))"
1799 }
1800 ]
1801 },
1802 {
1803 "refine_plan": [
1804 {
1805 "table": "`t1`"
1806 }
1807 ]
1808 }
1809 ]
1810 }
1811 },
1812 {
1813 "join_optimization": {
1814 "select#": 4,
1815 "steps": [
1816 {
1817 "table_dependencies": [
1818 {
1819 "table": "``",
1820 "row_may_be_null": false,
1821 "map_bit": 0,
1822 "depends_on_map_bits": [
1823 ]
1824 }
1825 ]
1826 },
1827 {
1828 "rows_estimation": [
1829 {
1830 "table": "``",
1831 "table_scan": {
1832 "rows": 0,
1833 "cost": 2.5
1834 }
1835 }
1836 ]
1837 },
1838 {
1839 "considered_execution_plans": [
1840 {
1841 "plan_prefix": [
1842 ],
1843 "table": "``",
1844 "best_access_path": {
1845 "considered_access_paths": [
1846 {
1847 "rows_to_scan": 0,
1848 "access_type": "scan",
1849 "resulting_rows": 0,
1850 "cost": 2.5,
1851 "chosen": true
1852 }
1853 ]
1854 },
1855 "condition_filtering_pct": 100,
1856 "rows_for_plan": 0,
1857 "cost_for_plan": 2.5,
1858 "chosen": true
1859 }
1860 ]
1861 },
1862 {
1863 "attaching_conditions_to_tables": {
1864 "original_condition": null,
1865 "attached_conditions_computation": [
1866 ],
1867 "attached_conditions_summary": [
1868 {
1869 "table": "``",
1870 "attached": null
1871 }
1872 ]
1873 }
1874 },
1875 {
1876 "finalizing_table_conditions": [
1877 ]
1878 },
1879 {
1880 "refine_plan": [
1881 {
1882 "table": "``"
1883 }
1884 ]
1885 }
1886 ]
1887 }
1888 },
1889 {
1890 "condition_processing": {
1891 "condition": "WHERE",
1892 "original_condition": "((`dt1`.`f1` > 26) and (`dt1`.`f2` > 31))",
1893 "steps": [
1894 {
1895 "transformation": "equality_propagation",
1896 "resulting_condition": "((`dt1`.`f1` > 26) and (`dt1`.`f2` > 31))"
1897 },
1898 {
1899 "transformation": "constant_propagation",
1900 "resulting_condition": "((`dt1`.`f1` > 26) and (`dt1`.`f2` > 31))"
1901 },
1902 {
1903 "transformation": "trivial_condition_removal",
1904 "resulting_condition": "((`dt1`.`f1` > 26) and (`dt1`.`f2` > 31.00))"
1905 }
1906 ]
1907 }
1908 },
1909 {
1910 "substitute_generated_columns": {
1911 }
1912 },
1913 {
1914 "table_dependencies": [
1915 {
1916 "table": " `dt1`",
1917 "row_may_be_null": false,
1918 "map_bit": 0,
1919 "depends_on_map_bits": [
1920 ]
1921 }
1922 ]
1923 },
1924 {
1925 "ref_optimizer_key_uses": [
1926 ]
1927 },
1928 {
1929 "rows_estimation": [
1930 {
1931 "table": " `dt1`"
1932 }
1933 ]
1934 },
1935 {
1936 "considered_execution_plans": [
1937 {
1938 "plan_prefix": [
1939 ],
1940 "table": " `dt1`",
1941 "best_access_path": {
1942 "considered_access_paths": [
1943 {
1944 "rows_to_scan": 5,
1945 "access_type": "scan",
1946 "resulting_rows": 5,
1947 "cost": 3.0625,
1948 "chosen": true
1949 }
1950 ]
1951 },
1952 "condition_filtering_pct": 100,
1953 "rows_for_plan": 5,
1954 "cost_for_plan": 3.0625,
1955 "chosen": true
1956 }
1957 ]
1958 },
1959 {
1960 "attaching_conditions_to_tables": {
1961 "original_condition": "((`dt1`.`f1` > 26) and (`dt1`.`f2` > 31.00))",
1962 "attached_conditions_computation": [
1963 ],
1964 "attached_conditions_summary": [
1965 {
1966 "table": " `dt1`",
1967 "attached": "((`dt1`.`f1` > 26) and (`dt1`.`f2` > 31.00))"
1968 }
1969 ]
1970 }
1971 },
1972 {
1973 "finalizing_table_conditions": [
1974 {
1975 "table": " `dt1`",
1976 "original_table_condition": "((`dt1`.`f1` > 26) and (`dt1`.`f2` > 31.00))",
1977 "final_table_condition ": "((`dt1`.`f1` > 26) and (`dt1`.`f2` > 31.00))"
1978 }
1979 ]
1980 },
1981 {
1982 "refine_plan": [
1983 {
1984 "table": " `dt1`"
1985 }
1986 ]
1987 }
1988 ]
1989 }
1990 },
1991 {
1992 "join_execution": {
1993 "select#": 1,
1994 "steps": [
1995 {
1996 "creating_tmp_table": {
1997 "tmp_table_info": {
1998 "table": "dt1",
1999 "in_plan_at_position": 0,
2000 "columns": 2,
2001 "row_length": 21,
2002 "key_length": 22,
2003 "unique_constraint": false,
2004 "makes_grouped_rows": false,
2005 "cannot_insert_duplicates": true,
2006 "location": "TempTable"
2007 }
2008 }
2009 },
2010 {
2011 "materialize": {
2012 "select#": 2,
2013 "steps": [
2014 {
2015 "creating_tmp_table": {
2016 "tmp_table_info": {
2017 "table": "<temporary>",
2018 "in_plan_at_position": 1,
2019 "columns": 2,
2020 "row_length": 11,
2021 "key_length": 5,
2022 "unique_constraint": false,
2023 "makes_grouped_rows": true,
2024 "cannot_insert_duplicates": false,
2025 "location": "TempTable"
2026 }
2027 }
2028 },
2029 {
2030 "materialize": {
2031 "select#": 2,
2032 "steps": [
2033 ]
2034 }
2035 }
2036 ]
2037 }
2038 },
2039 {
2040 "materialize": {
2041 "select#": 3,
2042 "steps": [
2043 ]
2044 }
2045 }
2046 ]
2047 }
2048 }
2049 ]
2050 }
2051 set optimizer_switch=default;
2052 set sql_mode=default;
2053 DROP TABLE t1;