Debian Codegraph @

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

raw
1 SET optimizer_trace_max_mem_size=1048576;
2 SET end_markers_in_json=on;
3 SET optimizer_trace="enabled=on,one_line=off";
4 CREATE TABLE t1 (a INT);
5 CREATE TABLE t2 (a INT, b INT);
6 INSERT INTO t1 VALUES (2);
7 INSERT INTO t2 VALUES (1,7),(2,7);
8 # Subselect execute is traced every time it is executed
9 SET @@optimizer_trace_features="greedy_search=off,repeated_subselect=on";
10 SELECT (SELECT a FROM t1 WHERE t1.a=t2.a), a FROM t2;
11 (SELECT a FROM t1 WHERE t1.a=t2.a) a
12 NULL 1
13 2 2
14
15 SELECT * FROM information_schema.OPTIMIZER_TRACE;
16 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
17 SELECT (SELECT a FROM t1 WHERE t1.a=t2.a), a FROM t2 {
18 "steps": [
19 {
20 "join_preparation": {
21 "select#": 1,
22 "steps": [
23 {
24 "join_preparation": {
25 "select#": 2,
26 "steps": [
27 {
28 "expanded_query": "/* select#2 */ select `t1`.`a` from `t1` where (`t1`.`a` = `t2`.`a`)"
29 }
30 ] /* steps */
31 } /* join_preparation */
32 },
33 {
34 "expanded_query": "/* select#1 */ select (/* select#2 */ select `t1`.`a` from `t1` where (`t1`.`a` = `t2`.`a`)) AS `(SELECT a FROM t1 WHERE t1.a=t2.a)`,`t2`.`a` AS `a` from `t2`"
35 }
36 ] /* steps */
37 } /* join_preparation */
38 },
39 {
40 "join_optimization": {
41 "select#": 1,
42 "steps": [
43 {
44 "table_dependencies": [
45 {
46 "table": "`t2`",
47 "row_may_be_null": false,
48 "map_bit": 0,
49 "depends_on_map_bits": [
50 ] /* depends_on_map_bits */
51 }
52 ] /* table_dependencies */
53 },
54 {
55 "rows_estimation": [
56 {
57 "table": "`t2`",
58 "table_scan": {
59 "rows": 2,
60 "cost": 0.501099
61 } /* table_scan */
62 }
63 ] /* rows_estimation */
64 },
65 {
66 "considered_execution_plans": "..."
67 },
68 {
69 "attaching_conditions_to_tables": {
70 "original_condition": null,
71 "attached_conditions_computation": [
72 ] /* attached_conditions_computation */,
73 "attached_conditions_summary": [
74 {
75 "table": "`t2`",
76 "attached": null
77 }
78 ] /* attached_conditions_summary */
79 } /* attaching_conditions_to_tables */
80 },
81 {
82 "finalizing_table_conditions": [
83 ] /* finalizing_table_conditions */
84 },
85 {
86 "refine_plan": [
87 {
88 "table": "`t2`"
89 }
90 ] /* refine_plan */
91 }
92 ] /* steps */
93 } /* join_optimization */
94 },
95 {
96 "join_optimization": {
97 "select#": 2,
98 "steps": [
99 {
100 "condition_processing": {
101 "condition": "WHERE",
102 "original_condition": "(`t1`.`a` = `t2`.`a`)",
103 "steps": [
104 {
105 "transformation": "equality_propagation",
106 "resulting_condition": "(`t1`.`a` = `t2`.`a`)"
107 },
108 {
109 "transformation": "constant_propagation",
110 "resulting_condition": "(`t1`.`a` = `t2`.`a`)"
111 },
112 {
113 "transformation": "trivial_condition_removal",
114 "resulting_condition": "(`t1`.`a` = `t2`.`a`)"
115 }
116 ] /* steps */
117 } /* condition_processing */
118 },
119 {
120 "substitute_generated_columns": {
121 } /* substitute_generated_columns */
122 },
123 {
124 "table_dependencies": [
125 {
126 "table": "`t1`",
127 "row_may_be_null": false,
128 "map_bit": 0,
129 "depends_on_map_bits": [
130 ] /* depends_on_map_bits */
131 }
132 ] /* table_dependencies */
133 },
134 {
135 "ref_optimizer_key_uses": [
136 ] /* ref_optimizer_key_uses */
137 },
138 {
139 "rows_estimation": [
140 {
141 "table": "`t1`",
142 "rows": 1,
143 "cost": 1,
144 "table_type": "system",
145 "empty": false
146 }
147 ] /* rows_estimation */
148 },
149 {
150 "attaching_conditions_to_tables": {
151 "original_condition": "('2' = `t2`.`a`)",
152 "attached_conditions_computation": [
153 ] /* attached_conditions_computation */,
154 "attached_conditions_summary": [
155 ] /* attached_conditions_summary */
156 } /* attaching_conditions_to_tables */
157 },
158 {
159 "refine_plan": [
160 ] /* refine_plan */
161 }
162 ] /* steps */
163 } /* join_optimization */
164 },
165 {
166 "join_execution": {
167 "select#": 1,
168 "steps": [
169 {
170 "subselect_execution": {
171 "select#": 2,
172 "steps": [
173 {
174 "join_execution": {
175 "select#": 2,
176 "steps": [
177 ] /* steps */
178 } /* join_execution */
179 }
180 ] /* steps */
181 } /* subselect_execution */
182 },
183 {
184 "subselect_execution": {
185 "select#": 2,
186 "steps": [
187 {
188 "join_execution": {
189 "select#": 2,
190 "steps": [
191 ] /* steps */
192 } /* join_execution */
193 }
194 ] /* steps */
195 } /* subselect_execution */
196 }
197 ] /* steps */
198 } /* join_execution */
199 }
200 ] /* steps */
201 } 0 0
202
203 # Subselect execute is traced only the first time it is executed
204 SET @@optimizer_trace_features="greedy_search=off,repeated_subselect=off";
205 SELECT (SELECT a FROM t1 WHERE t1.a=t2.a), a FROM t2;
206 (SELECT a FROM t1 WHERE t1.a=t2.a) a
207 NULL 1
208 2 2
209
210 SELECT * FROM information_schema.OPTIMIZER_TRACE;
211 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
212 SELECT (SELECT a FROM t1 WHERE t1.a=t2.a), a FROM t2 {
213 "steps": [
214 {
215 "join_preparation": {
216 "select#": 1,
217 "steps": [
218 {
219 "join_preparation": {
220 "select#": 2,
221 "steps": [
222 {
223 "expanded_query": "/* select#2 */ select `t1`.`a` from `t1` where (`t1`.`a` = `t2`.`a`)"
224 }
225 ] /* steps */
226 } /* join_preparation */
227 },
228 {
229 "expanded_query": "/* select#1 */ select (/* select#2 */ select `t1`.`a` from `t1` where (`t1`.`a` = `t2`.`a`)) AS `(SELECT a FROM t1 WHERE t1.a=t2.a)`,`t2`.`a` AS `a` from `t2`"
230 }
231 ] /* steps */
232 } /* join_preparation */
233 },
234 {
235 "join_optimization": {
236 "select#": 1,
237 "steps": [
238 {
239 "table_dependencies": [
240 {
241 "table": "`t2`",
242 "row_may_be_null": false,
243 "map_bit": 0,
244 "depends_on_map_bits": [
245 ] /* depends_on_map_bits */
246 }
247 ] /* table_dependencies */
248 },
249 {
250 "rows_estimation": [
251 {
252 "table": "`t2`",
253 "table_scan": {
254 "rows": 2,
255 "cost": 0.501099
256 } /* table_scan */
257 }
258 ] /* rows_estimation */
259 },
260 {
261 "considered_execution_plans": "..."
262 },
263 {
264 "attaching_conditions_to_tables": {
265 "original_condition": null,
266 "attached_conditions_computation": [
267 ] /* attached_conditions_computation */,
268 "attached_conditions_summary": [
269 {
270 "table": "`t2`",
271 "attached": null
272 }
273 ] /* attached_conditions_summary */
274 } /* attaching_conditions_to_tables */
275 },
276 {
277 "finalizing_table_conditions": [
278 ] /* finalizing_table_conditions */
279 },
280 {
281 "refine_plan": [
282 {
283 "table": "`t2`"
284 }
285 ] /* refine_plan */
286 }
287 ] /* steps */
288 } /* join_optimization */
289 },
290 {
291 "join_optimization": {
292 "select#": 2,
293 "steps": [
294 {
295 "condition_processing": {
296 "condition": "WHERE",
297 "original_condition": "(`t1`.`a` = `t2`.`a`)",
298 "steps": [
299 {
300 "transformation": "equality_propagation",
301 "resulting_condition": "(`t1`.`a` = `t2`.`a`)"
302 },
303 {
304 "transformation": "constant_propagation",
305 "resulting_condition": "(`t1`.`a` = `t2`.`a`)"
306 },
307 {
308 "transformation": "trivial_condition_removal",
309 "resulting_condition": "(`t1`.`a` = `t2`.`a`)"
310 }
311 ] /* steps */
312 } /* condition_processing */
313 },
314 {
315 "substitute_generated_columns": {
316 } /* substitute_generated_columns */
317 },
318 {
319 "table_dependencies": [
320 {
321 "table": "`t1`",
322 "row_may_be_null": false,
323 "map_bit": 0,
324 "depends_on_map_bits": [
325 ] /* depends_on_map_bits */
326 }
327 ] /* table_dependencies */
328 },
329 {
330 "ref_optimizer_key_uses": [
331 ] /* ref_optimizer_key_uses */
332 },
333 {
334 "rows_estimation": [
335 {
336 "table": "`t1`",
337 "rows": 1,
338 "cost": 1,
339 "table_type": "system",
340 "empty": false
341 }
342 ] /* rows_estimation */
343 },
344 {
345 "attaching_conditions_to_tables": {
346 "original_condition": "('2' = `t2`.`a`)",
347 "attached_conditions_computation": [
348 ] /* attached_conditions_computation */,
349 "attached_conditions_summary": [
350 ] /* attached_conditions_summary */
351 } /* attaching_conditions_to_tables */
352 },
353 {
354 "refine_plan": [
355 ] /* refine_plan */
356 }
357 ] /* steps */
358 } /* join_optimization */
359 },
360 {
361 "join_execution": {
362 "select#": 1,
363 "steps": [
364 {
365 "subselect_execution": {
366 "select#": 2,
367 "steps": [
368 {
369 "join_execution": {
370 "select#": 2,
371 "steps": [
372 ] /* steps */
373 } /* join_execution */
374 }
375 ] /* steps */
376 } /* subselect_execution */
377 }
378 ] /* steps */
379 } /* join_execution */
380 }
381 ] /* steps */
382 } 0 0
383
384 DROP TABLE t1,t2;
385 SET @@optimizer_trace_features="default";
386 CREATE TABLE t1 (a FLOAT(5,4) zerofill);
387 Warnings:
388 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
389 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
390 CREATE TABLE t2 (a FLOAT(5,4),b FLOAT(2,0));
391 Warnings:
392 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
393 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
394 SELECT t1.a
395 FROM t1
396 WHERE t1.a= (SELECT b FROM t2 LIMIT 1) AND NOT
397 t1.a= (SELECT a FROM t2 LIMIT 1) ;
398 a
399
400 SELECT * FROM information_schema.OPTIMIZER_TRACE;
401 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
402 SELECT t1.a
403 FROM t1
404 WHERE t1.a= (SELECT b FROM t2 LIMIT 1) AND NOT
405 t1.a= (SELECT a FROM t2 LIMIT 1) {
406 "steps": [
407 {
408 "join_preparation": {
409 "select#": 1,
410 "steps": [
411 {
412 "join_preparation": {
413 "select#": 2,
414 "steps": [
415 {
416 "expanded_query": "/* select#2 */ select `t2`.`b` from `t2` limit 1"
417 }
418 ] /* steps */
419 } /* join_preparation */
420 },
421 {
422 "join_preparation": {
423 "select#": 3,
424 "steps": [
425 {
426 "expanded_query": "/* select#3 */ select `t2`.`a` from `t2` limit 1"
427 }
428 ] /* steps */
429 } /* join_preparation */
430 },
431 {
432 "expanded_query": "/* select#1 */ select `t1`.`a` AS `a` from `t1` where ((`t1`.`a` = (/* select#2 */ select `t2`.`b` from `t2` limit 1)) and (`t1`.`a` <> (/* select#3 */ select `t2`.`a` from `t2` limit 1)))"
433 }
434 ] /* steps */
435 } /* join_preparation */
436 },
437 {
438 "join_optimization": {
439 "select#": 1,
440 "steps": [
441 {
442 "condition_processing": {
443 "condition": "WHERE",
444 "original_condition": "((`t1`.`a` = (/* select#2 */ select `t2`.`b` from `t2` limit 1)) and (`t1`.`a` <> (/* select#3 */ select `t2`.`a` from `t2` limit 1)))",
445 "steps": [
446 {
447 "transformation": "equality_propagation",
448 "subselect_evaluation": [
449 ] /* subselect_evaluation */,
450 "resulting_condition": "(((/* select#2 */ select `t2`.`b` from `t2` limit 1) <> (/* select#3 */ select `t2`.`a` from `t2` limit 1)) and multiple equal((/* select#2 */ select `t2`.`b` from `t2` limit 1), `t1`.`a`))"
451 },
452 {
453 "transformation": "constant_propagation",
454 "subselect_evaluation": [
455 ] /* subselect_evaluation */,
456 "resulting_condition": "(((/* select#2 */ select `t2`.`b` from `t2` limit 1) <> (/* select#3 */ select `t2`.`a` from `t2` limit 1)) and multiple equal((/* select#2 */ select `t2`.`b` from `t2` limit 1), `t1`.`a`))"
457 },
458 {
459 "transformation": "trivial_condition_removal",
460 "subselect_evaluation": [
461 {
462 "subselect_execution": {
463 "select#": 2,
464 "steps": [
465 {
466 "join_optimization": {
467 "select#": 2,
468 "steps": [
469 {
470 "table_dependencies": [
471 {
472 "table": "`t2`",
473 "row_may_be_null": false,
474 "map_bit": 0,
475 "depends_on_map_bits": [
476 ] /* depends_on_map_bits */
477 }
478 ] /* table_dependencies */
479 },
480 {
481 "rows_estimation": [
482 {
483 "table": "`t2`",
484 "rows": 1,
485 "cost": 1,
486 "table_type": "system",
487 "empty": true
488 }
489 ] /* rows_estimation */
490 }
491 ] /* steps */,
492 "empty_result": {
493 "cause": "no matching row in const table"
494 } /* empty_result */
495 } /* join_optimization */
496 },
497 {
498 "join_execution": {
499 "select#": 2,
500 "steps": [
501 ] /* steps */
502 } /* join_execution */
503 }
504 ] /* steps */
505 } /* subselect_execution */
506 }
507 ] /* subselect_evaluation */,
508 "resulting_condition": null
509 }
510 ] /* steps */
511 } /* condition_processing */
512 }
513 ] /* steps */,
514 "empty_result": {
515 "cause": "Impossible WHERE"
516 } /* empty_result */
517 } /* join_optimization */
518 },
519 {
520 "join_execution": {
521 "select#": 1,
522 "steps": [
523 ] /* steps */
524 } /* join_execution */
525 }
526 ] /* steps */
527 } 0 0
528
529 SELECT 1 FROM DUAL
530 WHERE NOT EXISTS
531 (SELECT * FROM t2 WHERE a = 50 AND b = 3);
532 1
533 1
534
535 SELECT * FROM information_schema.OPTIMIZER_TRACE;
536 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
537 SELECT 1 FROM DUAL
538 WHERE NOT EXISTS
539 (SELECT * FROM t2 WHERE a = 50 AND b = 3) {
540 "steps": [
541 {
542 "join_preparation": {
543 "select#": 1,
544 "steps": [
545 {
546 "join_preparation": {
547 "select#": 2,
548 "steps": [
549 {
550 "expanded_query": "/* select#2 */ select 1 from `t2` where ((`t2`.`a` = 50) and (`t2`.`b` = 3))"
551 }
552 ] /* steps */
553 } /* join_preparation */
554 },
555 {
556 "expanded_query": "/* select#1 */ select 1 AS `1` from DUAL where exists(/* select#2 */ select 1 from `t2` where ((`t2`.`a` = 50) and (`t2`.`b` = 3))) is false"
557 }
558 ] /* steps */
559 } /* join_preparation */
560 },
561 {
562 "join_optimization": {
563 "select#": 1,
564 "steps": [
565 {
566 "condition_processing": {
567 "condition": "WHERE",
568 "original_condition": "exists(/* select#2 */ select 1 from `t2` where ((`t2`.`a` = 50) and (`t2`.`b` = 3))) is false",
569 "steps": [
570 {
571 "transformation": "equality_propagation",
572 "subselect_evaluation": [
573 ] /* subselect_evaluation */,
574 "resulting_condition": "exists(/* select#2 */ select 1 from `t2` where ((`t2`.`a` = 50) and (`t2`.`b` = 3))) is false"
575 },
576 {
577 "transformation": "constant_propagation",
578 "subselect_evaluation": [
579 ] /* subselect_evaluation */,
580 "resulting_condition": "exists(/* select#2 */ select 1 from `t2` where ((`t2`.`a` = 50) and (`t2`.`b` = 3))) is false"
581 },
582 {
583 "transformation": "trivial_condition_removal",
584 "subselect_evaluation": [
585 {
586 "subselect_execution": {
587 "select#": 2,
588 "steps": [
589 {
590 "join_optimization": {
591 "select#": 2,
592 "steps": [
593 {
594 "condition_processing": {
595 "condition": "WHERE",
596 "original_condition": "((`t2`.`a` = 50) and (`t2`.`b` = 3))",
597 "steps": [
598 {
599 "transformation": "equality_propagation",
600 "resulting_condition": "((`t2`.`a` = 50) and (`t2`.`b` = 3))"
601 },
602 {
603 "transformation": "constant_propagation",
604 "resulting_condition": "((`t2`.`a` = 50) and (`t2`.`b` = 3))"
605 },
606 {
607 "transformation": "trivial_condition_removal",
608 "resulting_condition": null
609 }
610 ] /* steps */
611 } /* condition_processing */
612 }
613 ] /* steps */,
614 "empty_result": {
615 "cause": "Impossible WHERE"
616 } /* empty_result */
617 } /* join_optimization */
618 },
619 {
620 "join_execution": {
621 "select#": 2,
622 "steps": [
623 ] /* steps */
624 } /* join_execution */
625 }
626 ] /* steps */
627 } /* subselect_execution */
628 }
629 ] /* subselect_evaluation */,
630 "resulting_condition": null
631 }
632 ] /* steps */
633 } /* condition_processing */
634 }
635 ] /* steps */
636 } /* join_optimization */
637 },
638 {
639 "join_execution": {
640 "select#": 1,
641 "steps": [
642 ] /* steps */
643 } /* join_execution */
644 }
645 ] /* steps */
646 } 0 0
647
648 SELECT 1 FROM DUAL WHERE NOT EXISTS (SELECT DISTINCT(a) FROM t2 GROUP BY a ORDER BY b);
649 1
650 1
651
652 SELECT * FROM information_schema.OPTIMIZER_TRACE;
653 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
654 SELECT 1 FROM DUAL WHERE NOT EXISTS (SELECT DISTINCT(a) FROM t2 GROUP BY a ORDER BY b) {
655 "steps": [
656 {
657 "join_preparation": {
658 "select#": 1,
659 "steps": [
660 {
661 "join_preparation": {
662 "select#": 2,
663 "steps": [
664 {
665 "transformations_to_subquery": [
666 "removed_ordering",
667 "removed_distinct",
668 "removed_grouping"
669 ] /* transformations_to_subquery */
670 },
671 {
672 "expanded_query": "/* select#2 */ select `t2`.`a` from `t2`"
673 }
674 ] /* steps */
675 } /* join_preparation */
676 },
677 {
678 "expanded_query": "/* select#1 */ select 1 AS `1` from DUAL where exists(/* select#2 */ select `t2`.`a` from `t2`) is false"
679 }
680 ] /* steps */
681 } /* join_preparation */
682 },
683 {
684 "join_optimization": {
685 "select#": 1,
686 "steps": [
687 {
688 "condition_processing": {
689 "condition": "WHERE",
690 "original_condition": "exists(/* select#2 */ select `t2`.`a` from `t2`) is false",
691 "steps": [
692 {
693 "transformation": "equality_propagation",
694 "subselect_evaluation": [
695 ] /* subselect_evaluation */,
696 "resulting_condition": "exists(/* select#2 */ select `t2`.`a` from `t2`) is false"
697 },
698 {
699 "transformation": "constant_propagation",
700 "subselect_evaluation": [
701 ] /* subselect_evaluation */,
702 "resulting_condition": "exists(/* select#2 */ select `t2`.`a` from `t2`) is false"
703 },
704 {
705 "transformation": "trivial_condition_removal",
706 "subselect_evaluation": [
707 {
708 "subselect_execution": {
709 "select#": 2,
710 "steps": [
711 {
712 "join_optimization": {
713 "select#": 2,
714 "steps": [
715 {
716 "table_dependencies": [
717 {
718 "table": "`t2`",
719 "row_may_be_null": false,
720 "map_bit": 0,
721 "depends_on_map_bits": [
722 ] /* depends_on_map_bits */
723 }
724 ] /* table_dependencies */
725 },
726 {
727 "rows_estimation": [
728 {
729 "table": "`t2`",
730 "rows": 1,
731 "cost": 1,
732 "table_type": "system",
733 "empty": true
734 }
735 ] /* rows_estimation */
736 }
737 ] /* steps */,
738 "empty_result": {
739 "cause": "no matching row in const table"
740 } /* empty_result */
741 } /* join_optimization */
742 },
743 {
744 "join_execution": {
745 "select#": 2,
746 "steps": [
747 ] /* steps */
748 } /* join_execution */
749 }
750 ] /* steps */
751 } /* subselect_execution */
752 }
753 ] /* subselect_evaluation */,
754 "resulting_condition": null
755 }
756 ] /* steps */
757 } /* condition_processing */
758 }
759 ] /* steps */
760 } /* join_optimization */
761 },
762 {
763 "join_execution": {
764 "select#": 1,
765 "steps": [
766 ] /* steps */
767 } /* join_execution */
768 }
769 ] /* steps */
770 } 0 0
771
772 DROP TABLE t1,t2;
773 #
774 # BUG#12905521 - ASSERT IN OPT_TRACE_STMT::SYNTAX_ERROR ON SELECT
775 # DISTINCT/MIN/JOIN/SUBQ QUERY
776 #
777 CREATE TABLE t1 (
778 pk INTEGER,
779 col_int_nokey INTEGER,
780 col_int_key INTEGER,
781 col_varchar_key VARCHAR(1),
782 col_varchar_nokey VARCHAR(1),
783 PRIMARY KEY (pk),
784 KEY (col_varchar_key,col_int_key)
785 ) ENGINE=MYISAM;
786 CREATE TABLE t2 (
787 pk INTEGER,
788 col_int_nokey INTEGER,
789 col_int_key INTEGER,
790 col_varchar_key VARCHAR(1),
791 col_varchar_nokey VARCHAR(1),
792 PRIMARY KEY (pk),
793 KEY (col_varchar_key,col_int_key)
794 ) ENGINE=MYISAM;
795 CREATE TABLE t3 (
796 pk INTEGER,
797 col_int_nokey INTEGER,
798 col_int_key INTEGER,
799 col_time_key TIME,
800 col_datetime_nokey DATETIME,
801 col_varchar_key VARCHAR(1),
802 col_varchar_nokey VARCHAR(1),
803 PRIMARY KEY (pk),
804 KEY (col_time_key),
805 KEY (col_varchar_key,col_int_key)
806 ) ENGINE=MYISAM;
807 CREATE TABLE t4 (
808 pk INTEGER,
809 col_int_nokey INTEGER,
810 col_int_key INTEGER,
811 col_date_key DATE,
812 col_date_nokey DATE,
813 col_time_key TIME,
814 col_time_nokey TIME,
815 col_datetime_key DATETIME,
816 col_datetime_nokey DATETIME,
817 col_varchar_key VARCHAR(1),
818 col_varchar_nokey VARCHAR(1),
819 PRIMARY KEY (pk),
820 KEY (col_varchar_key,col_int_key)
821 ) ENGINE=MYISAM;
822 INSERT IGNORE INTO t4 (
823 col_int_key,col_int_nokey,
824 col_date_key,col_date_nokey,
825 col_time_key,col_time_nokey,
826 col_datetime_key,col_datetime_nokey,
827 col_varchar_key,col_varchar_nokey
828 ) VALUES
829 (8,7,'2008-10-02','2008-10-02','04:07:22.028954','04:07:22.028954','2001-10-08 00:00:00','2001-10-08 00:00:00','g','g');
830 Warnings:
831 Warning 1364 Field 'pk' doesn't have a default value
832 CREATE TABLE t5 (
833 pk INTEGER AUTO_INCREMENT,
834 col_int_nokey INTEGER,
835 col_int_key INTEGER,
836 col_date_key DATE,
837 col_date_nokey DATE,
838 col_time_key TIME,
839 col_time_nokey TIME,
840 col_datetime_key DATETIME,
841 col_datetime_nokey DATETIME,
842 col_varchar_key VARCHAR(1),
843 col_varchar_nokey VARCHAR(1),
844 PRIMARY KEY (pk),
845 KEY (col_int_key),
846 KEY (col_varchar_key,col_int_key)
847 ) ENGINE=MYISAM;
848 INSERT INTO t5 (
849 col_int_key,col_int_nokey,
850 col_date_key,col_date_nokey,
851 col_time_key,col_time_nokey,
852 col_datetime_key,col_datetime_nokey,
853 col_varchar_key,col_varchar_nokey
854 ) VALUES
855 (8,NULL,'2000-12-03','2000-12-03','22:55:23.019225','22:55:23.019225','2005-07-20 00:00:00','2005-07-20 00:00:00','x','x'),
856 (7,8,'2008-05-03','2008-05-03','10:19:31.050677','10:19:31.050677','2007-10-06 17:56:40.056051','2007-10-06 17:56:40.056051','d','d'),
857 (8,6,'2000-09-20','2000-09-20','14:11:27.044095','14:11:27.044095','2003-06-13 23:19:49.018300','2003-06-13 23:19:49.018300','c','c');
858 set @old_opt_switch=@@optimizer_switch;
859 select distinct
860 alias1.`col_varchar_key` as field1 ,alias1.`col_date_key` as
861 field2 ,( select min( sq1_alias1.`col_varchar_nokey` ) as sq1_field1 from ( t1
862 as sq1_alias1 inner join ( t5 as sq1_alias2 left join t5 as sq1_alias3 on
863 (sq1_alias3.`col_varchar_nokey` = sq1_alias2.`col_varchar_key` ) ) on
864 (sq1_alias3.`col_varchar_nokey` = sq1_alias2.`col_varchar_key` ) ) where
865 exists ( select distinct c_sq1_alias2.`col_int_nokey` as c_sq1_field1 from (
866 t3 as c_sq1_alias1 right join t4 as c_sq1_alias2 on (c_sq1_alias2.`col_int_nokey` = c_sq1_alias1.`pk` ) ) where
867 c_sq1_alias2.`col_varchar_key` = sq1_alias2.`col_varchar_nokey` ) ) as field3
868 ,( select max( sq2_alias1.`pk` ) as sq2_field1 from t5 as sq2_alias1 ) as
869 field4 ,alias2.`col_varchar_nokey` as field5 ,alias2.`col_varchar_nokey` as
870 field6 from ( t5 as alias1 right outer join ( ( ( select sq3_alias2.* from ( t5 as sq3_alias1 ,t4 as sq3_alias2 ) ) as alias2 right join t4
871 as alias3 on (alias3.`col_varchar_key` = alias2.`col_varchar_key` ) ) ) on
872 (alias3.`col_int_key` = alias2.`pk` ) ) where ( alias1.`col_varchar_nokey` in
873 ( select sq4_alias1.`col_varchar_key` as sq4_field1 from ( t3 as sq4_alias1
874 inner join ( t2 as sq4_alias2 right outer join t3 as sq4_alias3 on
875 (sq4_alias3.`pk` = sq4_alias2.`col_int_key` ) ) on
876 (sq4_alias3.`col_varchar_nokey` = sq4_alias2.`col_varchar_key` ) ) where
877 sq4_alias2.`col_int_key` < alias1.`col_int_nokey` and
878 sq4_alias3.`col_varchar_nokey` <> alias1.`col_varchar_key` ) ) and
879 alias1.`col_int_key` not in (214) group by field1,field2,field3,
880 field4,field5,field6;
881 field1 field2 field3 field4 field5 field6
882 select * from information_schema.optimizer_trace;
883 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
884 select distinct
885 alias1.`col_varchar_key` as field1 ,alias1.`col_date_key` as
886 field2 ,( select min( sq1_alias1.`col_varchar_nokey` ) as sq1_field1 from ( t1
887 as sq1_alias1 inner join ( t5 as sq1_alias2 left join t5 as sq1_alias3 on
888 (sq1_alias3.`col_varchar_nokey` = sq1_alias2.`col_varchar_key` ) ) on
889 (sq1_alias3.`col_varchar_nokey` = sq1_alias2.`col_varchar_key` ) ) where
890 exists ( select distinct c_sq1_alias2.`col_int_nokey` as c_sq1_field1 from (
891 t3 as c_sq1_alias1 right join t4 as c_sq1_alias2 on (c_sq1_alias2.`col_int_nokey` = c_sq1_alias1.`pk` ) ) where
892 c_sq1_alias2.`col_varchar_key` = sq1_alias2.`col_varchar_nokey` ) ) as field3
893 ,( select max( sq2_alias1.`pk` ) as sq2_field1 from t5 as sq2_alias1 ) as
894 field4 ,alias2.`col_varchar_nokey` as field5 ,alias2.`col_varchar_nokey` as
895 field6 from ( t5 as alias1 right outer join ( ( ( select sq3_alias2.* from ( t5 as sq3_alias1 ,t4 as sq3_alias2 ) ) as alias2 right join t4
896 as alias3 on (alias3.`col_varchar_key` = alias2.`col_varchar_key` ) ) ) on
897 (alias3.`col_int_key` = alias2.`pk` ) ) where ( alias1.`col_varchar_nokey` in
898 ( select sq4_alias1.`col_varchar_key` as sq4_field1 from ( t3 as sq4_alias1
899 inner join ( t2 as sq4_alias2 right outer join t3 as sq4_alias3 on
900 (sq4_alias3.`pk` = sq4_alias2.`col_int_key` ) ) on
901 (sq4_alias3.`col_varchar_nokey` = sq4_alias2.`col_varchar_key` ) ) where
902 sq4_alias2.`col_int_key` < alias1.`col_int_nokey` and
903 sq4_alias3.`col_varchar_nokey` <> alias1.`col_varchar_key` ) ) and
904 alias1.`col_int_key` not in (214) group by field1,field2,field3,
905 field4,field5,field6 {
906 "steps": [
907 {
908 "join_preparation": {
909 "select#": 1,
910 "steps": [
911 {
912 "join_preparation": {
913 "select#": 5,
914 "steps": [
915 {
916 "expanded_query": "/* select#5 */ select `sq3_alias2`.`pk` AS `pk`,`sq3_alias2`.`col_int_nokey` AS `col_int_nokey`,`sq3_alias2`.`col_int_key` AS `col_int_key`,`sq3_alias2`.`col_date_key` AS `col_date_key`,`sq3_alias2`.`col_date_nokey` AS `col_date_nokey`,`sq3_alias2`.`col_time_key` AS `col_time_key`,`sq3_alias2`.`col_time_nokey` AS `col_time_nokey`,`sq3_alias2`.`col_datetime_key` AS `col_datetime_key`,`sq3_alias2`.`col_datetime_nokey` AS `col_datetime_nokey`,`sq3_alias2`.`col_varchar_key` AS `col_varchar_key`,`sq3_alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from (`t5` `sq3_alias1` join `t4` `sq3_alias2`)"
917 }
918 ] /* steps */
919 } /* join_preparation */
920 },
921 {
922 "derived": {
923 "table": "`` `alias2`",
924 "select#": 5,
925 "merged": true
926 } /* derived */
927 },
928 {
929 "join_preparation": {
930 "select#": 2,
931 "steps": [
932 {
933 "join_preparation": {
934 "select#": 3,
935 "steps": [
936 {
937 "transformations_to_subquery": [
938 "removed_distinct"
939 ] /* transformations_to_subquery */
940 },
941 {
942 "expanded_query": "/* select#3 */ select `c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`t4` `c_sq1_alias2` left join `t3` `c_sq1_alias1` on((`c_sq1_alias2`.`col_int_nokey` = `c_sq1_alias1`.`pk`))) where (`c_sq1_alias2`.`col_varchar_key` = `sq1_alias2`.`col_varchar_nokey`)"
943 }
944 ] /* steps */
945 } /* join_preparation */
946 },
947 {
948 "expanded_query": "/* select#2 */ select min(`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from (`t1` `sq1_alias1` join (`t5` `sq1_alias2` left join `t5` `sq1_alias3` on((`sq1_alias3`.`col_varchar_nokey` = `sq1_alias2`.`col_varchar_key`))) on((`sq1_alias3`.`col_varchar_nokey` = `sq1_alias2`.`col_varchar_key`))) where exists(/* select#3 */ select `c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`t4` `c_sq1_alias2` left join `t3` `c_sq1_alias1` on((`c_sq1_alias2`.`col_int_nokey` = `c_sq1_alias1`.`pk`))) where (`c_sq1_alias2`.`col_varchar_key` = `sq1_alias2`.`col_varchar_nokey`))"
949 }
950 ] /* steps */
951 } /* join_preparation */
952 },
953 {
954 "join_preparation": {
955 "select#": 4,
956 "steps": [
957 {
958 "expanded_query": "/* select#4 */ select max(`sq2_alias1`.`pk`) AS `sq2_field1` from `t5` `sq2_alias1`"
959 }
960 ] /* steps */
961 } /* join_preparation */
962 },
963 {
964 "join_preparation": {
965 "select#": 6,
966 "steps": [
967 {
968 "expanded_query": "/* select#6 */ select `sq4_alias1`.`col_varchar_key` AS `sq4_field1` from (`t3` `sq4_alias1` join (`t3` `sq4_alias3` left join `t2` `sq4_alias2` on((`sq4_alias3`.`pk` = `sq4_alias2`.`col_int_key`))) on((`sq4_alias3`.`col_varchar_nokey` = `sq4_alias2`.`col_varchar_key`))) where ((`sq4_alias2`.`col_int_key` < `alias1`.`col_int_nokey`) and (`sq4_alias3`.`col_varchar_nokey` <> `alias1`.`col_varchar_key`))"
969 },
970 {
971 "transformation": {
972 "select#": 6,
973 "from": "IN (SELECT)",
974 "to": "EXISTS (CORRELATED SELECT)",
975 "chosen": true,
976 "evaluating_constant_where_conditions": [
977 ] /* evaluating_constant_where_conditions */
978 } /* transformation */
979 }
980 ] /* steps */
981 } /* join_preparation */
982 },
983 {
984 "expanded_query": "/* select#1 */ select `alias1`.`col_varchar_key` AS `field1`,`alias1`.`col_date_key` AS `field2`,(/* select#2 */ select min(`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from (`t1` `sq1_alias1` join (`t5` `sq1_alias2` left join `t5` `sq1_alias3` on((`sq1_alias3`.`col_varchar_nokey` = `sq1_alias2`.`col_varchar_key`))) on((`sq1_alias3`.`col_varchar_nokey` = `sq1_alias2`.`col_varchar_key`))) where exists(/* select#3 */ select `c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`t4` `c_sq1_alias2` left join `t3` `c_sq1_alias1` on((`c_sq1_alias2`.`col_int_nokey` = `c_sq1_alias1`.`pk`))) where (`c_sq1_alias2`.`col_varchar_key` = `sq1_alias2`.`col_varchar_nokey`))) AS `field3`,(/* select#4 */ select max(`sq2_alias1`.`pk`) AS `sq2_field1` from `t5` `sq2_alias1`) AS `field4`,`sq3_alias2`.`col_varchar_nokey` AS `field5`,`sq3_alias2`.`col_varchar_nokey` AS `field6` from ((`t4` `alias3` left join ((`t5` `sq3_alias1` join `t4` `sq3_alias2`)) on((`alias3`.`col_varchar_key` = `sq3_alias2`.`col_varchar_key`))) left join `t5` `alias1` on((`alias3`.`col_int_key` = `sq3_alias2`.`pk`))) where (<in_optimizer>(`alias1`.`col_varchar_nokey`,<exists>(/* select#6 */ select `sq4_alias1`.`col_varchar_key` AS `sq4_field1` from (`t3` `sq4_alias1` join (`t3` `sq4_alias3` left join `t2` `sq4_alias2` on((`sq4_alias3`.`pk` = `sq4_alias2`.`col_int_key`))) on((`sq4_alias3`.`col_varchar_nokey` = `sq4_alias2`.`col_varchar_key`))) where ((`sq4_alias2`.`col_int_key` < `alias1`.`col_int_nokey`) and (`sq4_alias3`.`col_varchar_nokey` <> `alias1`.`col_varchar_key`) and (<cache>(`alias1`.`col_varchar_nokey`) = `sq4_alias1`.`col_varchar_key`)))) and (`alias1`.`col_int_key` <> 214)) group by `field1`,`field2`,`field3`,`field4`,`field5`,`field6`"
985 },
986 {
987 "transformations_to_nested_joins": {
988 "transformations": [
989 "outer_join_to_inner_join",
990 "JOIN_condition_to_WHERE",
991 "parenthesis_removal"
992 ] /* transformations */,
993 "expanded_query": "/* select#6 */ select `sq4_alias1`.`col_varchar_key` AS `sq4_field1` from `t3` `sq4_alias1` join `t3` `sq4_alias3` join `t2` `sq4_alias2` where ((`sq4_alias2`.`col_int_key` < `alias1`.`col_int_nokey`) and (`sq4_alias3`.`col_varchar_nokey` <> `alias1`.`col_varchar_key`) and (<cache>(`alias1`.`col_varchar_nokey`) = `sq4_alias1`.`col_varchar_key`) and (`sq4_alias3`.`col_varchar_nokey` = `sq4_alias2`.`col_varchar_key`) and (`sq4_alias3`.`pk` = `sq4_alias2`.`col_int_key`))"
994 } /* transformations_to_nested_joins */
995 },
996 {
997 "transformations_to_nested_joins": {
998 "transformations": [
999 "parenthesis_removal"
1000 ] /* transformations */,
1001 "expanded_query": "/* select#3 */ select `c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from `t4` `c_sq1_alias2` left join `t3` `c_sq1_alias1` on((`c_sq1_alias2`.`col_int_nokey` = `c_sq1_alias1`.`pk`)) where (`c_sq1_alias2`.`col_varchar_key` = `sq1_alias2`.`col_varchar_nokey`)"
1002 } /* transformations_to_nested_joins */
1003 },
1004 {
1005 "transformations_to_nested_joins": {
1006 "transformations": [
1007 "outer_join_to_inner_join",
1008 "JOIN_condition_to_WHERE",
1009 "parenthesis_removal"
1010 ] /* transformations */,
1011 "expanded_query": "/* select#2 */ select min(`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from `t1` `sq1_alias1` join `t5` `sq1_alias2` join `t5` `sq1_alias3` where (exists(/* select#3 */ select `c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from `t4` `c_sq1_alias2` left join `t3` `c_sq1_alias1` on((`c_sq1_alias2`.`col_int_nokey` = `c_sq1_alias1`.`pk`)) where (`c_sq1_alias2`.`col_varchar_key` = `sq1_alias2`.`col_varchar_nokey`)) and (`sq1_alias3`.`col_varchar_nokey` = `sq1_alias2`.`col_varchar_key`) and (`sq1_alias3`.`col_varchar_nokey` = `sq1_alias2`.`col_varchar_key`))"
1012 } /* transformations_to_nested_joins */
1013 },
1014 {
1015 "transformations_to_nested_joins": {
1016 "transformations": [
1017 "outer_join_to_inner_join",
1018 "JOIN_condition_to_WHERE",
1019 "parenthesis_removal"
1020 ] /* transformations */,
1021 "expanded_query": "/* select#1 */ select `alias1`.`col_varchar_key` AS `field1`,`alias1`.`col_date_key` AS `field2`,(/* select#2 */ select min(`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from `t1` `sq1_alias1` join `t5` `sq1_alias2` join `t5` `sq1_alias3` where (exists(/* select#3 */ select `c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from `t4` `c_sq1_alias2` left join `t3` `c_sq1_alias1` on((`c_sq1_alias2`.`col_int_nokey` = `c_sq1_alias1`.`pk`)) where (`c_sq1_alias2`.`col_varchar_key` = `sq1_alias2`.`col_varchar_nokey`)) and (`sq1_alias3`.`col_varchar_nokey` = `sq1_alias2`.`col_varchar_key`) and (`sq1_alias3`.`col_varchar_nokey` = `sq1_alias2`.`col_varchar_key`))) AS `field3`,(/* select#4 */ select max(`sq2_alias1`.`pk`) AS `sq2_field1` from `t5` `sq2_alias1`) AS `field4`,`sq3_alias2`.`col_varchar_nokey` AS `field5`,`sq3_alias2`.`col_varchar_nokey` AS `field6` from `t4` `alias3` join `t5` `sq3_alias1` join `t4` `sq3_alias2` join `t5` `alias1` where (<in_optimizer>(`alias1`.`col_varchar_nokey`,<exists>(/* select#6 */ select `sq4_alias1`.`col_varchar_key` AS `sq4_field1` from `t3` `sq4_alias1` join `t3` `sq4_alias3` join `t2` `sq4_alias2` where ((`sq4_alias2`.`col_int_key` < `alias1`.`col_int_nokey`) and (`sq4_alias3`.`col_varchar_nokey` <> `alias1`.`col_varchar_key`) and (<cache>(`alias1`.`col_varchar_nokey`) = `sq4_alias1`.`col_varchar_key`) and (`sq4_alias3`.`col_varchar_nokey` = `sq4_alias2`.`col_varchar_key`) and (`sq4_alias3`.`pk` = `sq4_alias2`.`col_int_key`)))) and (`alias1`.`col_int_key` <> 214) and (`alias3`.`col_int_key` = `sq3_alias2`.`pk`) and (`alias3`.`col_varchar_key` = `sq3_alias2`.`col_varchar_key`)) group by `field1`,`field2`,`field3`,`field4`,`field5`,`field6`"
1022 } /* transformations_to_nested_joins */
1023 }
1024 ] /* steps */
1025 } /* join_preparation */
1026 },
1027 {
1028 "join_optimization": {
1029 "select#": 1,
1030 "steps": [
1031 {
1032 "condition_processing": {
1033 "condition": "WHERE",
1034 "original_condition": "(<in_optimizer>(`alias1`.`col_varchar_nokey`,<exists>(/* select#6 */ select `sq4_alias1`.`col_varchar_key` AS `sq4_field1` from `t3` `sq4_alias1` join `t3` `sq4_alias3` join `t2` `sq4_alias2` where ((`sq4_alias2`.`col_int_key` < `alias1`.`col_int_nokey`) and (`sq4_alias3`.`col_varchar_nokey` <> `alias1`.`col_varchar_key`) and (<cache>(`alias1`.`col_varchar_nokey`) = `sq4_alias1`.`col_varchar_key`) and (`sq4_alias3`.`col_varchar_nokey` = `sq4_alias2`.`col_varchar_key`) and (`sq4_alias3`.`pk` = `sq4_alias2`.`col_int_key`)))) and (`alias1`.`col_int_key` <> 214) and (`alias3`.`col_int_key` = `sq3_alias2`.`pk`) and (`alias3`.`col_varchar_key` = `sq3_alias2`.`col_varchar_key`))",
1035 "steps": [
1036 {
1037 "transformation": "equality_propagation",
1038 "subselect_evaluation": [
1039 ] /* subselect_evaluation */,
1040 "resulting_condition": "(<in_optimizer>(`alias1`.`col_varchar_nokey`,<exists>(/* select#6 */ select `sq4_alias1`.`col_varchar_key` AS `sq4_field1` from `t3` `sq4_alias1` join `t3` `sq4_alias3` join `t2` `sq4_alias2` where ((`sq4_alias2`.`col_int_key` < `alias1`.`col_int_nokey`) and (`sq4_alias3`.`col_varchar_nokey` <> `alias1`.`col_varchar_key`) and (<cache>(`alias1`.`col_varchar_nokey`) = `sq4_alias1`.`col_varchar_key`) and (`sq4_alias3`.`col_varchar_nokey` = `sq4_alias2`.`col_varchar_key`) and (`sq4_alias3`.`pk` = `sq4_alias2`.`col_int_key`)))) and (`alias1`.`col_int_key` <> 214) and multiple equal(`alias3`.`col_int_key`, `sq3_alias2`.`pk`) and multiple equal(`alias3`.`col_varchar_key`, `sq3_alias2`.`col_varchar_key`))"
1041 },
1042 {
1043 "transformation": "constant_propagation",
1044 "subselect_evaluation": [
1045 ] /* subselect_evaluation */,
1046 "resulting_condition": "(<in_optimizer>(`alias1`.`col_varchar_nokey`,<exists>(/* select#6 */ select `sq4_alias1`.`col_varchar_key` AS `sq4_field1` from `t3` `sq4_alias1` join `t3` `sq4_alias3` join `t2` `sq4_alias2` where ((`sq4_alias2`.`col_int_key` < `alias1`.`col_int_nokey`) and (`sq4_alias3`.`col_varchar_nokey` <> `alias1`.`col_varchar_key`) and (<cache>(`alias1`.`col_varchar_nokey`) = `sq4_alias1`.`col_varchar_key`) and (`sq4_alias3`.`col_varchar_nokey` = `sq4_alias2`.`col_varchar_key`) and (`sq4_alias3`.`pk` = `sq4_alias2`.`col_int_key`)))) and (`alias1`.`col_int_key` <> 214) and multiple equal(`alias3`.`col_int_key`, `sq3_alias2`.`pk`) and multiple equal(`alias3`.`col_varchar_key`, `sq3_alias2`.`col_varchar_key`))"
1047 },
1048 {
1049 "transformation": "trivial_condition_removal",
1050 "subselect_evaluation": [
1051 ] /* subselect_evaluation */,
1052 "resulting_condition": "(<in_optimizer>(`alias1`.`col_varchar_nokey`,<exists>(/* select#6 */ select `sq4_alias1`.`col_varchar_key` AS `sq4_field1` from `t3` `sq4_alias1` join `t3` `sq4_alias3` join `t2` `sq4_alias2` where ((`sq4_alias2`.`col_int_key` < `alias1`.`col_int_nokey`) and (`sq4_alias3`.`col_varchar_nokey` <> `alias1`.`col_varchar_key`) and (<cache>(`alias1`.`col_varchar_nokey`) = `sq4_alias1`.`col_varchar_key`) and (`sq4_alias3`.`col_varchar_nokey` = `sq4_alias2`.`col_varchar_key`) and (`sq4_alias3`.`pk` = `sq4_alias2`.`col_int_key`)))) and (`alias1`.`col_int_key` <> 214) and multiple equal(`alias3`.`col_int_key`, `sq3_alias2`.`pk`) and multiple equal(`alias3`.`col_varchar_key`, `sq3_alias2`.`col_varchar_key`))"
1053 }
1054 ] /* steps */
1055 } /* condition_processing */
1056 },
1057 {
1058 "substitute_generated_columns": {
1059 } /* substitute_generated_columns */
1060 },
1061 {
1062 "table_dependencies": [
1063 {
1064 "table": "`t4` `alias3`",
1065 "row_may_be_null": false,
1066 "map_bit": 0,
1067 "depends_on_map_bits": [
1068 ] /* depends_on_map_bits */
1069 },
1070 {
1071 "table": "`t5` `sq3_alias1`",
1072 "row_may_be_null": true,
1073 "map_bit": 1,
1074 "depends_on_map_bits": [
1075 ] /* depends_on_map_bits */
1076 },
1077 {
1078 "table": "`t4` `sq3_alias2`",
1079 "row_may_be_null": true,
1080 "map_bit": 2,
1081 "depends_on_map_bits": [
1082 ] /* depends_on_map_bits */
1083 },
1084 {
1085 "table": "`t5` `alias1`",
1086 "row_may_be_null": true,
1087 "map_bit": 3,
1088 "depends_on_map_bits": [
1089 ] /* depends_on_map_bits */
1090 }
1091 ] /* table_dependencies */
1092 },
1093 {
1094 "ref_optimizer_key_uses": [
1095 {
1096 "table": "`t4` `alias3`",
1097 "field": "col_varchar_key",
1098 "equals": "`sq3_alias2`.`col_varchar_key`",
1099 "null_rejecting": true
1100 },
1101 {
1102 "table": "`t4` `alias3`",
1103 "field": "col_int_key",
1104 "equals": "`sq3_alias2`.`pk`",
1105 "null_rejecting": true
1106 },
1107 {
1108 "table": "`t4` `sq3_alias2`",
1109 "field": "pk",
1110 "equals": "`alias3`.`col_int_key`",
1111 "null_rejecting": true
1112 },
1113 {
1114 "table": "`t4` `sq3_alias2`",
1115 "field": "col_varchar_key",
1116 "equals": "`alias3`.`col_varchar_key`",
1117 "null_rejecting": true
1118 }
1119 ] /* ref_optimizer_key_uses */
1120 },
1121 {
1122 "rows_estimation": [
1123 {
1124 "table": "`t4` `alias3`",
1125 "rows": 1,
1126 "cost": 1,
1127 "table_type": "system",
1128 "empty": false
1129 },
1130 {
1131 "table": "`t5` `sq3_alias1`",
1132 "table_scan": {
1133 "rows": 3,
1134 "cost": 0.508545
1135 } /* table_scan */
1136 },
1137 {
1138 "table": "`t4` `sq3_alias2`",
1139 "rows": 1,
1140 "cost": 1,
1141 "table_type": "system",
1142 "empty": false
1143 },
1144 {
1145 "table": "`t5` `alias1`",
1146 "range_analysis": {
1147 "table_scan": {
1148 "rows": 3,
1149 "cost": 2.90854
1150 } /* table_scan */,
1151 "potential_range_indexes": [
1152 {
1153 "index": "PRIMARY",
1154 "usable": false,
1155 "cause": "not_applicable"
1156 },
1157 {
1158 "index": "col_int_key",
1159 "usable": true,
1160 "key_parts": [
1161 "col_int_key"
1162 ] /* key_parts */
1163 },
1164 {
1165 "index": "col_varchar_key",
1166 "usable": false,
1167 "cause": "not_applicable"
1168 }
1169 ] /* potential_range_indexes */,
1170 "setup_range_conditions": [
1171 ] /* setup_range_conditions */,
1172 "impossible_range": true
1173 } /* range_analysis */,
1174 "rows": 0,
1175 "cause": "impossible_where_condition"
1176 }
1177 ] /* rows_estimation */
1178 },
1179 {
1180 "considered_execution_plans": [
1181 {
1182 "plan_prefix": [
1183 "`t4` `alias3`",
1184 "`t4` `sq3_alias2`",
1185 "`t5` `alias1`"
1186 ] /* plan_prefix */,
1187 "table": "`t5` `sq3_alias1`",
1188 "best_access_path": {
1189 "considered_access_paths": [
1190 {
1191 "rows_to_scan": 3,
1192 "access_type": "scan",
1193 "resulting_rows": 3,
1194 "cost": 0.808545,
1195 "chosen": true
1196 }
1197 ] /* considered_access_paths */
1198 } /* best_access_path */,
1199 "condition_filtering_pct": 100,
1200 "rows_for_plan": 3,
1201 "cost_for_plan": 0.808545,
1202 "chosen": true
1203 }
1204 ] /* considered_execution_plans */
1205 }
1206 ] /* steps */,
1207 "empty_result": {
1208 "cause": "no matching row in const table"
1209 } /* empty_result */
1210 } /* join_optimization */
1211 },
1212 {
1213 "join_execution": {
1214 "select#": 1,
1215 "steps": [
1216 ] /* steps */
1217 } /* join_execution */
1218 }
1219 ] /* steps */
1220 } 0 0
1221 set optimizer_switch=@old_opt_switch;
1222 drop table t1,t2,t3,t4,t5;
1223 #
1224 # BUG#12905758 - ASSERT IN OPT_TRACE_STMT::SYNTAX_ERROR ON
1225 # SELECT/SUBQ/SUM QUERY
1226 #
1227 CREATE TABLE t1 (
1228 pk INTEGER AUTO_INCREMENT,
1229 col_int_nokey INTEGER,
1230 col_int_key INTEGER,
1231 col_date_key DATE,
1232 col_date_nokey DATE,
1233 col_time_key TIME,
1234 col_time_nokey TIME,
1235 col_datetime_key DATETIME,
1236 col_datetime_nokey DATETIME,
1237 col_varchar_key VARCHAR(1),
1238 col_varchar_nokey VARCHAR(1),
1239 PRIMARY KEY (pk),
1240 KEY (col_varchar_key,col_int_key)
1241 ) ENGINE=MYISAM, CHARSET utf8mb4;
1242 INSERT INTO t1 (
1243 col_int_key,col_int_nokey,
1244 col_date_key,col_date_nokey,
1245 col_time_key,col_time_nokey,
1246 col_datetime_key,col_datetime_nokey,
1247 col_varchar_key,col_varchar_nokey
1248 ) VALUES
1249 (8,NULL,'2000-12-03','2000-12-03','22:55:23.019225','22:55:23.019225','2005-07-20 00:00:00','2005-07-20 00:00:00','x','x'),
1250 (8,6,'2000-09-20','2000-09-20','14:11:27.044095','14:11:27.044095','2003-06-13 23:19:49.018300','2003-06-13 23:19:49.018300','c','c');
1251 CREATE TABLE t2 (I INTEGER);
1252 select ( select sum( subquery1_t1.`col_int_nokey` ) as subquery1_field1 from
1253 t1 as subquery1_t1 ) as field1 from ( t1 as table1 straight_join t1 as table2
1254 on (table2.`col_varchar_key` = table1.`col_varchar_key` ) ) where (
1255 table2.`col_int_nokey` <> any ( select 5 from t2 ) ) and table1.`pk` in
1256 (192,18) order by field1 desc;
1257 field1
1258 select * from information_schema.optimizer_trace;
1259 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
1260 select ( select sum( subquery1_t1.`col_int_nokey` ) as subquery1_field1 from
1261 t1 as subquery1_t1 ) as field1 from ( t1 as table1 straight_join t1 as table2
1262 on (table2.`col_varchar_key` = table1.`col_varchar_key` ) ) where (
1263 table2.`col_int_nokey` <> any ( select 5 from t2 ) ) and table1.`pk` in
1264 (192,18) order by field1 desc {
1265 "steps": [
1266 {
1267 "join_preparation": {
1268 "select#": 1,
1269 "steps": [
1270 {
1271 "join_preparation": {
1272 "select#": 2,
1273 "steps": [
1274 {
1275 "expanded_query": "/* select#2 */ select sum(`subquery1_t1`.`col_int_nokey`) AS `subquery1_field1` from `t1` `subquery1_t1`"
1276 }
1277 ] /* steps */
1278 } /* join_preparation */
1279 },
1280 {
1281 "join_preparation": {
1282 "select#": 3,
1283 "steps": [
1284 {
1285 "expanded_query": "/* select#3 */ select 5 from `t2`"
1286 },
1287 {
1288 "transformation": {
1289 "select#": 3,
1290 "from": "IN (SELECT)",
1291 "to": "EXISTS (CORRELATED SELECT)",
1292 "chosen": true,
1293 "evaluating_constant_where_conditions": [
1294 ] /* evaluating_constant_where_conditions */
1295 } /* transformation */
1296 }
1297 ] /* steps */
1298 } /* join_preparation */
1299 },
1300 {
1301 "IN_uses_bisection": true
1302 },
1303 {
1304 "expanded_query": "/* select#1 */ select (/* select#2 */ select sum(`subquery1_t1`.`col_int_nokey`) AS `subquery1_field1` from `t1` `subquery1_t1`) AS `field1` from (`t1` `table1` straight_join `t1` `table2` on((`table2`.`col_varchar_key` = `table1`.`col_varchar_key`))) where (<nop>(<in_optimizer>(`table2`.`col_int_nokey`,<exists>(/* select#3 */ select 5 from `t2` where <if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)))) and (`table1`.`pk` in (192,18))) order by `field1` desc"
1305 },
1306 {
1307 "transformations_to_nested_joins": {
1308 "transformations": [
1309 "JOIN_condition_to_WHERE",
1310 "parenthesis_removal"
1311 ] /* transformations */,
1312 "expanded_query": "/* select#1 */ select (/* select#2 */ select sum(`subquery1_t1`.`col_int_nokey`) AS `subquery1_field1` from `t1` `subquery1_t1`) AS `field1` from `t1` `table1` straight_join `t1` `table2` where (<nop>(<in_optimizer>(`table2`.`col_int_nokey`,<exists>(/* select#3 */ select 5 from `t2` where <if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)))) and (`table1`.`pk` in (192,18)) and (`table2`.`col_varchar_key` = `table1`.`col_varchar_key`)) order by `field1` desc"
1313 } /* transformations_to_nested_joins */
1314 }
1315 ] /* steps */
1316 } /* join_preparation */
1317 },
1318 {
1319 "join_optimization": {
1320 "select#": 1,
1321 "steps": [
1322 {
1323 "condition_processing": {
1324 "condition": "WHERE",
1325 "original_condition": "(<nop>(<in_optimizer>(`table2`.`col_int_nokey`,<exists>(/* select#3 */ select 5 from `t2` where <if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)))) and (`table1`.`pk` in (192,18)) and (`table2`.`col_varchar_key` = `table1`.`col_varchar_key`))",
1326 "steps": [
1327 {
1328 "transformation": "equality_propagation",
1329 "subselect_evaluation": [
1330 ] /* subselect_evaluation */,
1331 "resulting_condition": "(<nop>(<in_optimizer>(`table2`.`col_int_nokey`,<exists>(/* select#3 */ select 5 from `t2` where <if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)))) and (`table1`.`pk` in (192,18)) and multiple equal(`table2`.`col_varchar_key`, `table1`.`col_varchar_key`))"
1332 },
1333 {
1334 "transformation": "constant_propagation",
1335 "subselect_evaluation": [
1336 ] /* subselect_evaluation */,
1337 "resulting_condition": "(<nop>(<in_optimizer>(`table2`.`col_int_nokey`,<exists>(/* select#3 */ select 5 from `t2` where <if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)))) and (`table1`.`pk` in (192,18)) and multiple equal(`table2`.`col_varchar_key`, `table1`.`col_varchar_key`))"
1338 },
1339 {
1340 "transformation": "trivial_condition_removal",
1341 "subselect_evaluation": [
1342 ] /* subselect_evaluation */,
1343 "resulting_condition": "(<nop>(<in_optimizer>(`table2`.`col_int_nokey`,<exists>(/* select#3 */ select 5 from `t2` where <if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)))) and (`table1`.`pk` in (192,18)) and multiple equal(`table2`.`col_varchar_key`, `table1`.`col_varchar_key`))"
1344 }
1345 ] /* steps */
1346 } /* condition_processing */
1347 },
1348 {
1349 "substitute_generated_columns": {
1350 } /* substitute_generated_columns */
1351 },
1352 {
1353 "table_dependencies": [
1354 {
1355 "table": "`t1` `table1`",
1356 "row_may_be_null": false,
1357 "map_bit": 0,
1358 "depends_on_map_bits": [
1359 ] /* depends_on_map_bits */
1360 },
1361 {
1362 "table": "`t1` `table2`",
1363 "row_may_be_null": false,
1364 "map_bit": 1,
1365 "depends_on_map_bits": [
1366 0
1367 ] /* depends_on_map_bits */
1368 }
1369 ] /* table_dependencies */
1370 },
1371 {
1372 "ref_optimizer_key_uses": [
1373 {
1374 "table": "`t1` `table1`",
1375 "field": "col_varchar_key",
1376 "equals": "`table2`.`col_varchar_key`",
1377 "null_rejecting": true
1378 },
1379 {
1380 "table": "`t1` `table2`",
1381 "field": "col_varchar_key",
1382 "equals": "`table1`.`col_varchar_key`",
1383 "null_rejecting": true
1384 }
1385 ] /* ref_optimizer_key_uses */
1386 },
1387 {
1388 "rows_estimation": [
1389 {
1390 "table": "`t1` `table1`",
1391 "range_analysis": {
1392 "table_scan": {
1393 "rows": 2,
1394 "cost": 2.80562
1395 } /* table_scan */,
1396 "potential_range_indexes": [
1397 {
1398 "index": "PRIMARY",
1399 "usable": true,
1400 "key_parts": [
1401 "pk"
1402 ] /* key_parts */
1403 },
1404 {
1405 "index": "col_varchar_key",
1406 "usable": false,
1407 "cause": "not_applicable"
1408 }
1409 ] /* potential_range_indexes */,
1410 "setup_range_conditions": [
1411 ] /* setup_range_conditions */,
1412 "group_index_range": {
1413 "chosen": false,
1414 "cause": "not_single_table"
1415 } /* group_index_range */,
1416 "skip_scan_range": {
1417 "chosen": false,
1418 "cause": "not_single_table"
1419 } /* skip_scan_range */,
1420 "analyzing_range_alternatives": {
1421 "range_scan_alternatives": [
1422 {
1423 "index": "PRIMARY",
1424 "ranges": [
1425 "pk = 18",
1426 "pk = 192"
1427 ] /* ranges */,
1428 "index_dives_for_eq_ranges": true,
1429 "rowid_ordered": false,
1430 "using_mrr": false,
1431 "index_only": false,
1432 "in_memory": -1,
1433 "rows": 2,
1434 "cost": 1.21,
1435 "chosen": true
1436 }
1437 ] /* range_scan_alternatives */,
1438 "analyzing_roworder_intersect": {
1439 "usable": false,
1440 "cause": "too_few_roworder_scans"
1441 } /* analyzing_roworder_intersect */
1442 } /* analyzing_range_alternatives */,
1443 "chosen_range_access_summary": {
1444 "range_access_plan": {
1445 "type": "range_scan",
1446 "index": "PRIMARY",
1447 "rows": 2,
1448 "ranges": [
1449 "pk = 18",
1450 "pk = 192"
1451 ] /* ranges */
1452 } /* range_access_plan */,
1453 "rows_for_plan": 2,
1454 "cost_for_plan": 1.21,
1455 "chosen": true
1456 } /* chosen_range_access_summary */
1457 } /* range_analysis */
1458 },
1459 {
1460 "table": "`t1` `table2`",
1461 "table_scan": {
1462 "rows": 2,
1463 "cost": 0.505615
1464 } /* table_scan */
1465 }
1466 ] /* rows_estimation */
1467 },
1468 {
1469 "considered_execution_plans": [
1470 {
1471 "plan_prefix": [
1472 ] /* plan_prefix */,
1473 "table": "`t1` `table1`",
1474 "best_access_path": {
1475 "considered_access_paths": [
1476 {
1477 "access_type": "ref",
1478 "index": "col_varchar_key",
1479 "usable": false,
1480 "chosen": false
1481 },
1482 {
1483 "rows_to_scan": 2,
1484 "filtering_effect": [
1485 ] /* filtering_effect */,
1486 "final_filtering_effect": 1,
1487 "access_type": "range",
1488 "range_details": {
1489 "used_index": "PRIMARY"
1490 } /* range_details */,
1491 "resulting_rows": 2,
1492 "cost": 1.41,
1493 "chosen": true
1494 }
1495 ] /* considered_access_paths */
1496 } /* best_access_path */,
1497 "condition_filtering_pct": 100,
1498 "rows_for_plan": 2,
1499 "cost_for_plan": 1.41,
1500 "rest_of_plan": [
1501 {
1502 "plan_prefix": [
1503 "`t1` `table1`"
1504 ] /* plan_prefix */,
1505 "table": "`t1` `table2`",
1506 "best_access_path": {
1507 "considered_access_paths": [
1508 {
1509 "access_type": "ref",
1510 "index": "col_varchar_key",
1511 "rows": 2,
1512 "cost": 1.4,
1513 "chosen": true
1514 },
1515 {
1516 "rows_to_scan": 2,
1517 "filtering_effect": [
1518 ] /* filtering_effect */,
1519 "final_filtering_effect": 1,
1520 "access_type": "scan",
1521 "using_join_cache": true,
1522 "buffers_needed": 1,
1523 "resulting_rows": 2,
1524 "cost": 0.905658,
1525 "chosen": true
1526 }
1527 ] /* considered_access_paths */
1528 } /* best_access_path */,
1529 "condition_filtering_pct": 100,
1530 "rows_for_plan": 4,
1531 "cost_for_plan": 2.31566,
1532 "chosen": true
1533 }
1534 ] /* rest_of_plan */
1535 }
1536 ] /* considered_execution_plans */
1537 },
1538 {
1539 "attaching_conditions_to_tables": {
1540 "original_condition": "((`table2`.`col_varchar_key` = `table1`.`col_varchar_key`) and <nop>(<in_optimizer>(`table2`.`col_int_nokey`,<exists>(/* select#3 */ select 5 from `t2` where <if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)))) and (`table1`.`pk` in (192,18)))",
1541 "attached_conditions_computation": [
1542 {
1543 "table": "`t1` `table2`",
1544 "rechecking_index_usage": {
1545 "recheck_reason": "not_first_table",
1546 "range_analysis": {
1547 "table_scan": {
1548 "rows": 2,
1549 "cost": 2.80562
1550 } /* table_scan */,
1551 "potential_range_indexes": [
1552 {
1553 "index": "PRIMARY",
1554 "usable": false,
1555 "cause": "not_applicable"
1556 },
1557 {
1558 "index": "col_varchar_key",
1559 "usable": true,
1560 "key_parts": [
1561 "col_varchar_key",
1562 "col_int_key"
1563 ] /* key_parts */
1564 }
1565 ] /* potential_range_indexes */,
1566 "setup_range_conditions": [
1567 ] /* setup_range_conditions */,
1568 "group_index_range": {
1569 "chosen": false,
1570 "cause": "not_single_table"
1571 } /* group_index_range */,
1572 "skip_scan_range": {
1573 "chosen": false,
1574 "cause": "not_single_table"
1575 } /* skip_scan_range */,
1576 "analyzing_range_alternatives": {
1577 "range_scan_alternatives": [
1578 {
1579 "index": "col_varchar_key",
1580 "chosen": false,
1581 "cause": "depends_on_unread_values"
1582 }
1583 ] /* range_scan_alternatives */,
1584 "analyzing_roworder_intersect": {
1585 "usable": false,
1586 "cause": "too_few_roworder_scans"
1587 } /* analyzing_roworder_intersect */
1588 } /* analyzing_range_alternatives */
1589 } /* range_analysis */
1590 } /* rechecking_index_usage */
1591 }
1592 ] /* attached_conditions_computation */,
1593 "attached_conditions_summary": [
1594 {
1595 "table": "`t1` `table1`",
1596 "attached": "(`table1`.`pk` in (192,18))"
1597 },
1598 {
1599 "table": "`t1` `table2`",
1600 "attached": "((`table2`.`col_varchar_key` = `table1`.`col_varchar_key`) and <nop>(<in_optimizer>(`table2`.`col_int_nokey`,<exists>(/* select#3 */ select 5 from `t2` where <if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)))))"
1601 }
1602 ] /* attached_conditions_summary */
1603 } /* attaching_conditions_to_tables */
1604 },
1605 {
1606 "optimizing_distinct_group_by_order_by": {
1607 "simplifying_order_by": {
1608 "original_clause": "`field1` desc",
1609 "items": [
1610 {
1611 "item": "(/* select#2 */ select sum(`subquery1_t1`.`col_int_nokey`) AS `subquery1_field1` from `t1` `subquery1_t1`)",
1612 "subselect_evaluation": [
1613 {
1614 "subselect_execution": {
1615 "select#": 2,
1616 "steps": [
1617 {
1618 "join_optimization": {
1619 "select#": 2,
1620 "steps": [
1621 {
1622 "table_dependencies": [
1623 {
1624 "table": "`t1` `subquery1_t1`",
1625 "row_may_be_null": false,
1626 "map_bit": 0,
1627 "depends_on_map_bits": [
1628 ] /* depends_on_map_bits */
1629 }
1630 ] /* table_dependencies */
1631 },
1632 {
1633 "rows_estimation": [
1634 {
1635 "table": "`t1` `subquery1_t1`",
1636 "table_scan": {
1637 "rows": 2,
1638 "cost": 0.505615
1639 } /* table_scan */
1640 }
1641 ] /* rows_estimation */
1642 },
1643 {
1644 "considered_execution_plans": [
1645 {
1646 "plan_prefix": [
1647 ] /* plan_prefix */,
1648 "table": "`t1` `subquery1_t1`",
1649 "best_access_path": {
1650 "considered_access_paths": [
1651 {
1652 "rows_to_scan": 2,
1653 "access_type": "scan",
1654 "resulting_rows": 2,
1655 "cost": 0.705615,
1656 "chosen": true
1657 }
1658 ] /* considered_access_paths */
1659 } /* best_access_path */,
1660 "condition_filtering_pct": 100,
1661 "rows_for_plan": 2,
1662 "cost_for_plan": 0.705615,
1663 "chosen": true
1664 }
1665 ] /* considered_execution_plans */
1666 },
1667 {
1668 "attaching_conditions_to_tables": {
1669 "original_condition": null,
1670 "attached_conditions_computation": [
1671 ] /* attached_conditions_computation */,
1672 "attached_conditions_summary": [
1673 {
1674 "table": "`t1` `subquery1_t1`",
1675 "attached": null
1676 }
1677 ] /* attached_conditions_summary */
1678 } /* attaching_conditions_to_tables */
1679 },
1680 {
1681 "optimizing_distinct_group_by_order_by": {
1682 } /* optimizing_distinct_group_by_order_by */
1683 },
1684 {
1685 "finalizing_table_conditions": [
1686 ] /* finalizing_table_conditions */
1687 },
1688 {
1689 "refine_plan": [
1690 {
1691 "table": "`t1` `subquery1_t1`"
1692 }
1693 ] /* refine_plan */
1694 },
1695 {
1696 "considering_tmp_tables": [
1697 ] /* considering_tmp_tables */
1698 }
1699 ] /* steps */
1700 } /* join_optimization */
1701 },
1702 {
1703 "join_execution": {
1704 "select#": 2,
1705 "steps": [
1706 ] /* steps */
1707 } /* join_execution */
1708 }
1709 ] /* steps */
1710 } /* subselect_execution */
1711 }
1712 ] /* subselect_evaluation */,
1713 "uses_only_constant_tables": true
1714 }
1715 ] /* items */,
1716 "resulting_clause_is_simple": true,
1717 "resulting_clause": ""
1718 } /* simplifying_order_by */
1719 } /* optimizing_distinct_group_by_order_by */
1720 },
1721 {
1722 "finalizing_table_conditions": [
1723 {
1724 "table": "`t1` `table1`",
1725 "original_table_condition": "(`table1`.`pk` in (192,18))",
1726 "final_table_condition ": "(`table1`.`pk` in (192,18))"
1727 },
1728 {
1729 "table": "`t1` `table2`",
1730 "original_table_condition": "((`table2`.`col_varchar_key` = `table1`.`col_varchar_key`) and <nop>(<in_optimizer>(`table2`.`col_int_nokey`,<exists>(/* select#3 */ select 5 from `t2` where <if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)))))",
1731 "final_table_condition ": "((`table2`.`col_varchar_key` = `table1`.`col_varchar_key`) and <nop>(<in_optimizer>(`table2`.`col_int_nokey`,<exists>(/* select#3 */ select 5 from `t2` where <if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)))))"
1732 }
1733 ] /* finalizing_table_conditions */
1734 },
1735 {
1736 "refine_plan": [
1737 {
1738 "table": "`t1` `table1`",
1739 "pushed_index_condition": "(`table1`.`pk` in (192,18))",
1740 "table_condition_attached": null
1741 },
1742 {
1743 "table": "`t1` `table2`"
1744 }
1745 ] /* refine_plan */
1746 }
1747 ] /* steps */
1748 } /* join_optimization */
1749 },
1750 {
1751 "join_optimization": {
1752 "select#": 3,
1753 "steps": [
1754 {
1755 "condition_processing": {
1756 "condition": "WHERE",
1757 "original_condition": "<if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)",
1758 "steps": [
1759 {
1760 "transformation": "equality_propagation",
1761 "resulting_condition": "<if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)"
1762 },
1763 {
1764 "transformation": "constant_propagation",
1765 "resulting_condition": "<if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)"
1766 },
1767 {
1768 "transformation": "trivial_condition_removal",
1769 "resulting_condition": "<if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)"
1770 }
1771 ] /* steps */
1772 } /* condition_processing */
1773 },
1774 {
1775 "substitute_generated_columns": {
1776 } /* substitute_generated_columns */
1777 },
1778 {
1779 "table_dependencies": [
1780 {
1781 "table": "`t2`",
1782 "row_may_be_null": false,
1783 "map_bit": 0,
1784 "depends_on_map_bits": [
1785 ] /* depends_on_map_bits */
1786 }
1787 ] /* table_dependencies */
1788 },
1789 {
1790 "ref_optimizer_key_uses": [
1791 ] /* ref_optimizer_key_uses */
1792 },
1793 {
1794 "rows_estimation": [
1795 {
1796 "table": "`t2`",
1797 "rows": 1,
1798 "cost": 1,
1799 "table_type": "system",
1800 "empty": true
1801 }
1802 ] /* rows_estimation */
1803 },
1804 {
1805 "transformation": {
1806 "select#": 3,
1807 "from": "IN (SELECT)",
1808 "to": "materialization",
1809 "possible": false,
1810 "cause": "not an IN predicate"
1811 } /* transformation */
1812 }
1813 ] /* steps */,
1814 "empty_result": {
1815 "cause": "no matching row in const table"
1816 } /* empty_result */
1817 } /* join_optimization */
1818 },
1819 {
1820 "join_execution": {
1821 "select#": 1,
1822 "steps": [
1823 ] /* steps */
1824 } /* join_execution */
1825 }
1826 ] /* steps */
1827 } 0 0
1828 drop table t1,t2;
1829
1830 #
1831 # Tracing of semijoin loosescan
1832 #
1833 create table t0 (a int);
1834 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1835 create table t1 (a int, b int, filler char(100), key(a,b)) charset utf8mb4;
1836 insert into t1 select A.a, B.a, 'filler' from t0 A, t0 B;
1837 create table t2 as select * from t1;
1838 set @old_opt_switch=@@optimizer_switch;
1839 set optimizer_switch="firstmatch=off,materialization=off,duplicateweedout=off";
1840 set @old_opt_prune_level=@@optimizer_prune_level;
1841 set optimizer_prune_level=0;
1842 explain select * from t2 where a in (select b from t1 where a=3);
1843 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1844 1 SIMPLE t1 NULL ref a a 5 const 8 100.00 Using index; LooseScan
1845 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 100 10.00 Using where; Using join buffer (hash join)
1846 Warnings:
1847 Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`filler` AS `filler` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` = 3))
1848 # Equality-propagation involving inner field => 1st sj equality is bound
1849 explain select * from t2 where (b+0,a+0) in (select a,b from t1 where a=3);
1850 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1851 1 SIMPLE t1 NULL ref a a 5 const 8 100.00 Using index; LooseScan
1852 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 100 100.00 Using where; Using join buffer (hash join)
1853 Warnings:
1854 Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`filler` AS `filler` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = 3) and ((`test`.`t2`.`b` + 0) = 3) and ((`test`.`t2`.`a` + 0) = `test`.`t1`.`b`))
1855 # Equality-propagation involving outer field => 3rd sj equality is bound.
1856 explain select * from t2 where (b,a,filler) in (select a,b,a*3 from t1) and filler='abc';
1857 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1858 1 SIMPLE t1 NULL index a a 10 NULL 100 100.00 Using index; LooseScan
1859 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 100 1.00 Using where; Using join buffer (hash join)
1860 Warnings:
1861 Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`filler` AS `filler` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t1`.`a`) and (`test`.`t2`.`filler` = 'abc') and (cast(`test`.`t2`.`filler` as double) = cast((`test`.`t1`.`a` * 3) as double)))
1862 SELECT show_json_object('"recalculate_access_paths_and_cost": {', TRACE)
1863 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
1864 show_json_object('"recalculate_access_paths_and_cost": {', TRACE)
1865 "recalculate_access_paths_and_cost": {
1866 "tables": [
1867 {
1868 "table": "`t1`",
1869 "best_access_path": {
1870 "considered_access_paths": [
1871 {
1872 "access_type": "ref",
1873 "index": "a",
1874 "usable": false,
1875 "chosen": false
1876 },
1877 {
1878 "rows_to_scan": 100,
1879 "filtering_effect": [
1880 ] /* filtering_effect */,
1881 "final_filtering_effect": 1,
1882 "access_type": "scan",
1883 "resulting_rows": 100,
1884 "cost": 12.9963,
1885 "chosen": true
1886 }
1887 ] /* considered_access_paths */
1888 } /* best_access_path */,
1889 "unknown_key_1": {
1890 "searching_loose_scan_index": {
1891 "indexes": [
1892 {
1893 "index": "a",
1894 "covering_scan": {
1895 "cost": 1.04839,
1896 "chosen": true
1897 } /* covering_scan */
1898 }
1899 ] /* indexes */
1900 } /* searching_loose_scan_index */
1901 }
1902 },
1903 {
1904 "table": "`t2`",
1905 "best_access_path": {
1906 "considered_access_paths": [
1907 {
1908 "rows_to_scan": 100,
1909 "filtering_effect": [
1910 ] /* filtering_effect */,
1911 "final_filtering_effect": 0.1,
1912 "access_type": "scan",
1913 "using_join_cache": true,
1914 "buffers_needed": 1,
1915 "resulting_rows": 10,
1916 "cost": 112.038,
1917 "chosen": true
1918 }
1919 ] /* considered_access_paths */
1920 } /* best_access_path */
1921 }
1922 ] /* tables */
1923 }
1924 # Remove the condition on 'filler' => 3rd sj equality is not bound.
1925 explain select * from t2 where (b,a,filler) in (select a,b,a*3 from t1);
1926 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1927 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 100 100.00 Using where
1928 1 SIMPLE t1 NULL ref a a 10 test.t2.b,test.t2.a 1 100.00 Using index; Start temporary; End temporary
1929 Warnings:
1930 Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`filler` AS `filler` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`b` = `test`.`t2`.`a`) and (`test`.`t1`.`a` = `test`.`t2`.`b`) and (cast(`test`.`t2`.`filler` as double) = cast((`test`.`t2`.`b` * 3) as double)))
1931 SELECT show_json_object('"searching_loose_scan_index": {', TRACE)
1932 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
1933 show_json_object('"searching_loose_scan_index": {', TRACE)
1934 "searching_loose_scan_index": {
1935 "indexes": [
1936 {
1937 "index": "a",
1938 "index_handles_needed_semijoin_equalities": false
1939 }
1940 ] /* indexes */
1941 }
1942 # Equality-propagation involving outer field => 3rd sj equality is bound.
1943 explain select * from t2 as t3, t2
1944 where t2.filler=t3.filler and
1945 (t2.b,t2.a,t2.filler) in (select a,b,a*3 from t1);
1946 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1947 1 SIMPLE t3 NULL ALL NULL NULL NULL NULL 100 100.00 NULL
1948 1 SIMPLE t1 NULL index a a 10 NULL 100 100.00 Using where; Using index; LooseScan
1949 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 100 1.00 Using where; Using join buffer (hash join)
1950 Warnings:
1951 Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`filler` AS `filler`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`filler` AS `filler` from `test`.`t2` `t3` join `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t1`.`a`) and (`test`.`t2`.`filler` = `test`.`t3`.`filler`) and (cast(`test`.`t3`.`filler` as double) = cast((`test`.`t1`.`a` * 3) as double)))
1952 SELECT show_json_object('"recalculate_access_paths_and_cost": {', TRACE)
1953 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
1954 show_json_object('"recalculate_access_paths_and_cost": {', TRACE)
1955 "recalculate_access_paths_and_cost": {
1956 "tables": [
1957 {
1958 "table": "`t1`",
1959 "best_access_path": {
1960 "considered_access_paths": [
1961 {
1962 "access_type": "ref",
1963 "index": "a",
1964 "usable": false,
1965 "chosen": false
1966 },
1967 {
1968 "rows_to_scan": 100,
1969 "filtering_effect": [
1970 ] /* filtering_effect */,
1971 "final_filtering_effect": 1,
1972 "access_type": "scan",
1973 "resulting_rows": 100,
1974 "cost": 1299.63,
1975 "chosen": true
1976 }
1977 ] /* considered_access_paths */
1978 } /* best_access_path */,
1979 "unknown_key_1": {
1980 "searching_loose_scan_index": {
1981 "indexes": [
1982 {
1983 "index": "a",
1984 "covering_scan": {
1985 "cost": 1.04839,
1986 "chosen": true
1987 } /* covering_scan */
1988 }
1989 ] /* indexes */
1990 } /* searching_loose_scan_index */
1991 }
1992 },
1993 {
1994 "table": "`t2`",
1995 "best_access_path": {
1996 "considered_access_paths": [
1997 {
1998 "rows_to_scan": 100,
1999 "filtering_effect": [
2000 ] /* filtering_effect */,
2001 "final_filtering_effect": 1,
2002 "access_type": "scan",
2003 "using_join_cache": true,
2004 "buffers_needed": 16,
2005 "resulting_rows": 100,
2006 "cost": 100051,
2007 "chosen": true
2008 }
2009 ] /* considered_access_paths */
2010 } /* best_access_path */
2011 }
2012 ] /* tables */
2013 }
2014 # In plan t3-t1-t2, 3rd outer expression is dependent only on
2015 # previous tables => 3rd sj equality is bound.
2016 # If t1 is before t3, 3rd sj equality is not bound.
2017 explain select * from t2 as t3 left join t2 on t2.filler+10=t3.filler+20
2018 where (t2.b,t2.a,t3.filler+2) in (select a,b,a*3 from t1);
2019 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2020 1 SIMPLE t3 NULL ALL NULL NULL NULL NULL 100 100.00 NULL
2021 1 SIMPLE t1 NULL index a a 10 NULL 100 100.00 Using where; Using index; LooseScan
2022 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 100 1.00 Using where; Using join buffer (hash join)
2023 Warnings:
2024 Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`filler` AS `filler`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`filler` AS `filler` from `test`.`t2` `t3` join `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t1`.`a`) and ((`test`.`t3`.`filler` + 2) = (`test`.`t1`.`a` * 3)) and ((`test`.`t2`.`filler` + 10) = (`test`.`t3`.`filler` + 20)))
2025 SELECT show_json_object('"considered_execution_plans": [', TRACE)
2026 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
2027 show_json_object('"considered_execution_plans": [', TRACE)
2028 "considered_execution_plans": [
2029 {
2030 "plan_prefix": [
2031 ] /* plan_prefix */,
2032 "table": "`t2` `t3`",
2033 "best_access_path": {
2034 "considered_access_paths": [
2035 {
2036 "rows_to_scan": 100,
2037 "filtering_effect": [
2038 ] /* filtering_effect */,
2039 "final_filtering_effect": 1,
2040 "access_type": "scan",
2041 "resulting_rows": 100,
2042 "cost": 12.9963,
2043 "chosen": true
2044 }
2045 ] /* considered_access_paths */
2046 } /* best_access_path */,
2047 "condition_filtering_pct": 100,
2048 "rows_for_plan": 100,
2049 "cost_for_plan": 12.9963,
2050 "semijoin_strategy_choice": [
2051 ] /* semijoin_strategy_choice */,
2052 "rest_of_plan": [
2053 {
2054 "plan_prefix": [
2055 "`t2` `t3`"
2056 ] /* plan_prefix */,
2057 "table": "`t2`",
2058 "best_access_path": {
2059 "considered_access_paths": [
2060 {
2061 "rows_to_scan": 100,
2062 "filtering_effect": [
2063 ] /* filtering_effect */,
2064 "final_filtering_effect": 1,
2065 "access_type": "scan",
2066 "using_join_cache": true,
2067 "buffers_needed": 1,
2068 "resulting_rows": 100,
2069 "cost": 1003.46,
2070 "chosen": true
2071 }
2072 ] /* considered_access_paths */
2073 } /* best_access_path */,
2074 "condition_filtering_pct": 100,
2075 "rows_for_plan": 10000,
2076 "cost_for_plan": 1016.46,
2077 "semijoin_strategy_choice": [
2078 ] /* semijoin_strategy_choice */,
2079 "rest_of_plan": [
2080 {
2081 "plan_prefix": [
2082 "`t2` `t3`",
2083 "`t2`"
2084 ] /* plan_prefix */,
2085 "table": "`t1`",
2086 "best_access_path": {
2087 "considered_access_paths": [
2088 {
2089 "access_type": "ref",
2090 "index": "a",
2091 "rows": 1,
2092 "cost": 3500,
2093 "chosen": true
2094 },
2095 {
2096 "rows_to_scan": 100,
2097 "filtering_effect": [
2098 ] /* filtering_effect */,
2099 "final_filtering_effect": 1,
2100 "access_type": "scan",
2101 "using_join_cache": true,
2102 "buffers_needed": 32,
2103 "resulting_rows": 100,
2104 "cost": 100097,
2105 "chosen": false
2106 }
2107 ] /* considered_access_paths */
2108 } /* best_access_path */,
2109 "condition_filtering_pct": 100,
2110 "rows_for_plan": 10000,
2111 "cost_for_plan": 4516.46,
2112 "semijoin_strategy_choice": [
2113 {
2114 "strategy": "DuplicatesWeedout",
2115 "cost": 6517.46,
2116 "rows": 10000,
2117 "duplicate_tables_left": true,
2118 "chosen": true
2119 }
2120 ] /* semijoin_strategy_choice */,
2121 "chosen": true
2122 }
2123 ] /* rest_of_plan */
2124 },
2125 {
2126 "plan_prefix": [
2127 "`t2` `t3`"
2128 ] /* plan_prefix */,
2129 "table": "`t1`",
2130 "best_access_path": {
2131 "considered_access_paths": [
2132 {
2133 "access_type": "ref",
2134 "index": "a",
2135 "usable": false,
2136 "chosen": false
2137 },
2138 {
2139 "rows_to_scan": 100,
2140 "filtering_effect": [
2141 ] /* filtering_effect */,
2142 "final_filtering_effect": 1,
2143 "access_type": "scan",
2144 "using_join_cache": true,
2145 "buffers_needed": 1,
2146 "resulting_rows": 100,
2147 "cost": 1003.46,
2148 "chosen": true
2149 }
2150 ] /* considered_access_paths */
2151 } /* best_access_path */,
2152 "condition_filtering_pct": 100,
2153 "rows_for_plan": 10000,
2154 "cost_for_plan": 1016.46,
2155 "semijoin_strategy_choice": [
2156 ] /* semijoin_strategy_choice */,
2157 "rest_of_plan": [
2158 {
2159 "plan_prefix": [
2160 "`t2` `t3`",
2161 "`t1`"
2162 ] /* plan_prefix */,
2163 "table": "`t2`",
2164 "best_access_path": {
2165 "considered_access_paths": [
2166 {
2167 "rows_to_scan": 100,
2168 "filtering_effect": [
2169 ] /* filtering_effect */,
2170 "final_filtering_effect": 1,
2171 "access_type": "scan",
2172 "using_join_cache": true,
2173 "buffers_needed": 16,
2174 "resulting_rows": 100,
2175 "cost": 100051,
2176 "chosen": true
2177 }
2178 ] /* considered_access_paths */
2179 } /* best_access_path */,
2180 "condition_filtering_pct": 1,
2181 "rows_for_plan": 10000,
2182 "cost_for_plan": 101067,
2183 "semijoin_strategy_choice": [
2184 {
2185 "strategy": "LooseScan",
2186 "recalculate_access_paths_and_cost": {
2187 "tables": [
2188 {
2189 "table": "`t1`",
2190 "best_access_path": {
2191 "considered_access_paths": [
2192 {
2193 "access_type": "ref",
2194 "index": "a",
2195 "usable": false,
2196 "chosen": false
2197 },
2198 {
2199 "rows_to_scan": 100,
2200 "filtering_effect": [
2201 ] /* filtering_effect */,
2202 "final_filtering_effect": 1,
2203 "access_type": "scan",
2204 "resulting_rows": 100,
2205 "cost": 1299.63,
2206 "chosen": true
2207 }
2208 ] /* considered_access_paths */
2209 } /* best_access_path */,
2210 "unknown_key_1": {
2211 "searching_loose_scan_index": {
2212 "indexes": [
2213 {
2214 "index": "a",
2215 "covering_scan": {
2216 "cost": 1.04839,
2217 "chosen": true
2218 } /* covering_scan */
2219 }
2220 ] /* indexes */
2221 } /* searching_loose_scan_index */
2222 }
2223 },
2224 {
2225 "table": "`t2`",
2226 "best_access_path": {
2227 "considered_access_paths": [
2228 {
2229 "rows_to_scan": 100,
2230 "filtering_effect": [
2231 ] /* filtering_effect */,
2232 "final_filtering_effect": 1,
2233 "access_type": "scan",
2234 "using_join_cache": true,
2235 "buffers_needed": 16,
2236 "resulting_rows": 100,
2237 "cost": 100051,
2238 "chosen": true
2239 }
2240 ] /* considered_access_paths */
2241 } /* best_access_path */
2242 }
2243 ] /* tables */
2244 } /* recalculate_access_paths_and_cost */,
2245 "cost": 101065,
2246 "rows": 100,
2247 "chosen": true
2248 },
2249 {
2250 "strategy": "DuplicatesWeedout",
2251 "cost": 102078,
2252 "rows": 100,
2253 "duplicate_tables_left": false,
2254 "chosen": false
2255 }
2256 ] /* semijoin_strategy_choice */,
2257 "chosen": true,
2258 "cause": "previous_plan_used_disabled_strategy"
2259 }
2260 ] /* rest_of_plan */
2261 }
2262 ] /* rest_of_plan */
2263 },
2264 {
2265 "plan_prefix": [
2266 ] /* plan_prefix */,
2267 "table": "`t2`",
2268 "best_access_path": {
2269 "considered_access_paths": [
2270 {
2271 "rows_to_scan": 100,
2272 "filtering_effect": [
2273 ] /* filtering_effect */,
2274 "final_filtering_effect": 1,
2275 "access_type": "scan",
2276 "resulting_rows": 100,
2277 "cost": 12.9963,
2278 "chosen": true
2279 }
2280 ] /* considered_access_paths */
2281 } /* best_access_path */,
2282 "condition_filtering_pct": 100,
2283 "rows_for_plan": 100,
2284 "cost_for_plan": 12.9963,
2285 "semijoin_strategy_choice": [
2286 ] /* semijoin_strategy_choice */,
2287 "rest_of_plan": [
2288 {
2289 "plan_prefix": [
2290 "`t2`"
2291 ] /* plan_prefix */,
2292 "table": "`t2` `t3`",
2293 "best_access_path": {
2294 "considered_access_paths": [
2295 {
2296 "rows_to_scan": 100,
2297 "filtering_effect": [
2298 ] /* filtering_effect */,
2299 "final_filtering_effect": 1,
2300 "access_type": "scan",
2301 "using_join_cache": true,
2302 "buffers_needed": 1,
2303 "resulting_rows": 100,
2304 "cost": 1003.46,
2305 "chosen": true
2306 }
2307 ] /* considered_access_paths */
2308 } /* best_access_path */,
2309 "condition_filtering_pct": 100,
2310 "rows_for_plan": 10000,
2311 "cost_for_plan": 1016.46,
2312 "semijoin_strategy_choice": [
2313 ] /* semijoin_strategy_choice */,
2314 "rest_of_plan": [
2315 {
2316 "plan_prefix": [
2317 "`t2`",
2318 "`t2` `t3`"
2319 ] /* plan_prefix */,
2320 "table": "`t1`",
2321 "best_access_path": {
2322 "considered_access_paths": [
2323 {
2324 "access_type": "ref",
2325 "index": "a",
2326 "rows": 1,
2327 "cost": 3500,
2328 "chosen": true
2329 },
2330 {
2331 "rows_to_scan": 100,
2332 "filtering_effect": [
2333 ] /* filtering_effect */,
2334 "final_filtering_effect": 1,
2335 "access_type": "scan",
2336 "using_join_cache": true,
2337 "buffers_needed": 32,
2338 "resulting_rows": 100,
2339 "cost": 100097,
2340 "chosen": false
2341 }
2342 ] /* considered_access_paths */
2343 } /* best_access_path */,
2344 "condition_filtering_pct": 100,
2345 "rows_for_plan": 10000,
2346 "cost_for_plan": 4516.46,
2347 "semijoin_strategy_choice": [
2348 {
2349 "strategy": "DuplicatesWeedout",
2350 "cost": 6517.46,
2351 "rows": 10000,
2352 "duplicate_tables_left": true,
2353 "chosen": true
2354 }
2355 ] /* semijoin_strategy_choice */,
2356 "chosen": false,
2357 "cause": "plan_uses_disabled_strategy"
2358 }
2359 ] /* rest_of_plan */
2360 },
2361 {
2362 "plan_prefix": [
2363 "`t2`"
2364 ] /* plan_prefix */,
2365 "table": "`t1`",
2366 "best_access_path": {
2367 "considered_access_paths": [
2368 {
2369 "access_type": "ref",
2370 "index": "a",
2371 "rows": 1,
2372 "cost": 35,
2373 "chosen": true
2374 },
2375 {
2376 "rows_to_scan": 100,
2377 "filtering_effect": [
2378 ] /* filtering_effect */,
2379 "final_filtering_effect": 1,
2380 "access_type": "scan",
2381 "using_join_cache": true,
2382 "buffers_needed": 1,
2383 "resulting_rows": 100,
2384 "cost": 1003.46,
2385 "chosen": false
2386 }
2387 ] /* considered_access_paths */
2388 } /* best_access_path */,
2389 "condition_filtering_pct": 100,
2390 "rows_for_plan": 100,
2391 "cost_for_plan": 47.9963,
2392 "semijoin_strategy_choice": [
2393 ] /* semijoin_strategy_choice */,
2394 "rest_of_plan": [
2395 {
2396 "plan_prefix": [
2397 "`t2`",
2398 "`t1`"
2399 ] /* plan_prefix */,
2400 "table": "`t2` `t3`",
2401 "best_access_path": {
2402 "considered_access_paths": [
2403 {
2404 "rows_to_scan": 100,
2405 "filtering_effect": [
2406 ] /* filtering_effect */,
2407 "final_filtering_effect": 1,
2408 "access_type": "scan",
2409 "using_join_cache": true,
2410 "buffers_needed": 1,
2411 "resulting_rows": 100,
2412 "cost": 1003.48,
2413 "chosen": true
2414 }
2415 ] /* considered_access_paths */
2416 } /* best_access_path */,
2417 "condition_filtering_pct": 100,
2418 "rows_for_plan": 10000,
2419 "cost_for_plan": 1051.47,
2420 "semijoin_strategy_choice": [
2421 {
2422 "strategy": "LooseScan",
2423 "recalculate_access_paths_and_cost": {
2424 "tables": [
2425 {
2426 "table": "`t1`",
2427 "best_access_path": {
2428 "considered_access_paths": [
2429 {
2430 "access_type": "ref",
2431 "index": "a",
2432 "rows": 1,
2433 "cost": 35,
2434 "chosen": true
2435 },
2436 {
2437 "rows_to_scan": 100,
2438 "filtering_effect": [
2439 ] /* filtering_effect */,
2440 "final_filtering_effect": 1,
2441 "access_type": "scan",
2442 "resulting_rows": 100,
2443 "cost": 1299.63,
2444 "chosen": false
2445 }
2446 ] /* considered_access_paths */
2447 } /* best_access_path */,
2448 "unknown_key_2": {
2449 "searching_loose_scan_index": {
2450 "indexes": [
2451 {
2452 "index": "a",
2453 "index_handles_needed_semijoin_equalities": false
2454 }
2455 ] /* indexes */
2456 } /* searching_loose_scan_index */
2457 }
2458 }
2459 ] /* tables */
2460 } /* recalculate_access_paths_and_cost */,
2461 "chosen": false
2462 },
2463 {
2464 "strategy": "DuplicatesWeedout",
2465 "cost": 3052.47,
2466 "rows": 10000,
2467 "duplicate_tables_left": true,
2468 "chosen": true
2469 }
2470 ] /* semijoin_strategy_choice */,
2471 "chosen": false,
2472 "cause": "plan_uses_disabled_strategy"
2473 }
2474 ] /* rest_of_plan */
2475 }
2476 ] /* rest_of_plan */
2477 },
2478 {
2479 "plan_prefix": [
2480 ] /* plan_prefix */,
2481 "table": "`t1`",
2482 "best_access_path": {
2483 "considered_access_paths": [
2484 {
2485 "access_type": "ref",
2486 "index": "a",
2487 "usable": false,
2488 "chosen": false
2489 },
2490 {
2491 "rows_to_scan": 100,
2492 "filtering_effect": [
2493 ] /* filtering_effect */,
2494 "final_filtering_effect": 1,
2495 "access_type": "scan",
2496 "resulting_rows": 100,
2497 "cost": 12.9963,
2498 "chosen": true
2499 }
2500 ] /* considered_access_paths */
2501 } /* best_access_path */,
2502 "condition_filtering_pct": 100,
2503 "rows_for_plan": 100,
2504 "cost_for_plan": 12.9963,
2505 "semijoin_strategy_choice": [
2506 ] /* semijoin_strategy_choice */,
2507 "rest_of_plan": [
2508 {
2509 "plan_prefix": [
2510 "`t1`"
2511 ] /* plan_prefix */,
2512 "table": "`t2` `t3`",
2513 "best_access_path": {
2514 "considered_access_paths": [
2515 {
2516 "rows_to_scan": 100,
2517 "filtering_effect": [
2518 ] /* filtering_effect */,
2519 "final_filtering_effect": 1,
2520 "access_type": "scan",
2521 "using_join_cache": true,
2522 "buffers_needed": 1,
2523 "resulting_rows": 100,
2524 "cost": 1003.01,
2525 "chosen": true
2526 }
2527 ] /* considered_access_paths */
2528 } /* best_access_path */,
2529 "condition_filtering_pct": 100,
2530 "rows_for_plan": 10000,
2531 "cost_for_plan": 1016,
2532 "semijoin_strategy_choice": [
2533 ] /* semijoin_strategy_choice */,
2534 "rest_of_plan": [
2535 {
2536 "plan_prefix": [
2537 "`t1`",
2538 "`t2` `t3`"
2539 ] /* plan_prefix */,
2540 "table": "`t2`",
2541 "best_access_path": {
2542 "considered_access_paths": [
2543 {
2544 "rows_to_scan": 100,
2545 "filtering_effect": [
2546 ] /* filtering_effect */,
2547 "final_filtering_effect": 1,
2548 "access_type": "scan",
2549 "using_join_cache": true,
2550 "buffers_needed": 16,
2551 "resulting_rows": 100,
2552 "cost": 100051,
2553 "chosen": true
2554 }
2555 ] /* considered_access_paths */
2556 } /* best_access_path */,
2557 "condition_filtering_pct": 1,
2558 "rows_for_plan": 10000,
2559 "cost_for_plan": 101067,
2560 "semijoin_strategy_choice": [
2561 {
2562 "strategy": "LooseScan",
2563 "recalculate_access_paths_and_cost": {
2564 "tables": [
2565 {
2566 "table": "`t1`",
2567 "best_access_path": {
2568 "considered_access_paths": [
2569 {
2570 "access_type": "ref",
2571 "index": "a",
2572 "usable": false,
2573 "chosen": false
2574 },
2575 {
2576 "rows_to_scan": 100,
2577 "filtering_effect": [
2578 ] /* filtering_effect */,
2579 "final_filtering_effect": 1,
2580 "access_type": "scan",
2581 "resulting_rows": 100,
2582 "cost": 12.9963,
2583 "chosen": true
2584 }
2585 ] /* considered_access_paths */
2586 } /* best_access_path */,
2587 "unknown_key_3": {
2588 "searching_loose_scan_index": {
2589 "indexes": [
2590 {
2591 "index": "a",
2592 "index_handles_needed_semijoin_equalities": false
2593 }
2594 ] /* indexes */
2595 } /* searching_loose_scan_index */
2596 }
2597 }
2598 ] /* tables */
2599 } /* recalculate_access_paths_and_cost */,
2600 "chosen": false
2601 },
2602 {
2603 "strategy": "DuplicatesWeedout",
2604 "cost": 102078,
2605 "rows": 100,
2606 "duplicate_tables_left": true,
2607 "chosen": true
2608 }
2609 ] /* semijoin_strategy_choice */,
2610 "pruned_by_cost": true
2611 }
2612 ] /* rest_of_plan */
2613 },
2614 {
2615 "plan_prefix": [
2616 "`t1`"
2617 ] /* plan_prefix */,
2618 "table": "`t2`",
2619 "best_access_path": {
2620 "considered_access_paths": [
2621 {
2622 "rows_to_scan": 100,
2623 "filtering_effect": [
2624 ] /* filtering_effect */,
2625 "final_filtering_effect": 1,
2626 "access_type": "scan",
2627 "using_join_cache": true,
2628 "buffers_needed": 1,
2629 "resulting_rows": 100,
2630 "cost": 1003.01,
2631 "chosen": true
2632 }
2633 ] /* considered_access_paths */
2634 } /* best_access_path */,
2635 "condition_filtering_pct": 1,
2636 "rows_for_plan": 100,
2637 "cost_for_plan": 1016,
2638 "semijoin_strategy_choice": [
2639 ] /* semijoin_strategy_choice */,
2640 "rest_of_plan": [
2641 {
2642 "plan_prefix": [
2643 "`t1`",
2644 "`t2`"
2645 ] /* plan_prefix */,
2646 "table": "`t2` `t3`",
2647 "best_access_path": {
2648 "considered_access_paths": [
2649 {
2650 "rows_to_scan": 100,
2651 "filtering_effect": [
2652 ] /* filtering_effect */,
2653 "final_filtering_effect": 1,
2654 "access_type": "scan",
2655 "using_join_cache": true,
2656 "buffers_needed": 1,
2657 "resulting_rows": 100,
2658 "cost": 1003.48,
2659 "chosen": true
2660 }
2661 ] /* considered_access_paths */
2662 } /* best_access_path */,
2663 "condition_filtering_pct": 100,
2664 "rows_for_plan": 10000,
2665 "cost_for_plan": 2019.48,
2666 "semijoin_strategy_choice": [
2667 {
2668 "strategy": "LooseScan",
2669 "recalculate_access_paths_and_cost": {
2670 "tables": [
2671 {
2672 "table": "`t1`",
2673 "best_access_path": {
2674 "considered_access_paths": [
2675 {
2676 "access_type": "ref",
2677 "index": "a",
2678 "usable": false,
2679 "chosen": false
2680 },
2681 {
2682 "rows_to_scan": 100,
2683 "filtering_effect": [
2684 ] /* filtering_effect */,
2685 "final_filtering_effect": 1,
2686 "access_type": "scan",
2687 "resulting_rows": 100,
2688 "cost": 12.9963,
2689 "chosen": true
2690 }
2691 ] /* considered_access_paths */
2692 } /* best_access_path */,
2693 "unknown_key_4": {
2694 "searching_loose_scan_index": {
2695 "indexes": [
2696 {
2697 "index": "a",
2698 "index_handles_needed_semijoin_equalities": false
2699 }
2700 ] /* indexes */
2701 } /* searching_loose_scan_index */
2702 }
2703 }
2704 ] /* tables */
2705 } /* recalculate_access_paths_and_cost */,
2706 "chosen": false
2707 },
2708 {
2709 "strategy": "DuplicatesWeedout",
2710 "cost": 3030.48,
2711 "rows": 100,
2712 "duplicate_tables_left": true,
2713 "chosen": true
2714 }
2715 ] /* semijoin_strategy_choice */,
2716 "chosen": false,
2717 "cause": "plan_uses_disabled_strategy"
2718 }
2719 ] /* rest_of_plan */
2720 }
2721 ] /* rest_of_plan */
2722 },
2723 {
2724 "final_semijoin_strategy": "LooseScan",
2725 "recalculate_access_paths_and_cost": {
2726 "tables": [
2727 {
2728 "table": "`t1`",
2729 "best_access_path": {
2730 "considered_access_paths": [
2731 {
2732 "access_type": "ref",
2733 "index": "a",
2734 "usable": false,
2735 "chosen": false
2736 },
2737 {
2738 "rows_to_scan": 100,
2739 "filtering_effect": [
2740 ] /* filtering_effect */,
2741 "final_filtering_effect": 1,
2742 "access_type": "scan",
2743 "resulting_rows": 100,
2744 "cost": 1299.63,
2745 "chosen": true
2746 }
2747 ] /* considered_access_paths */
2748 } /* best_access_path */,
2749 "unknown_key_5": {
2750 "searching_loose_scan_index": {
2751 "indexes": [
2752 {
2753 "index": "a",
2754 "covering_scan": {
2755 "cost": 1.04839,
2756 "chosen": true
2757 } /* covering_scan */
2758 }
2759 ] /* indexes */
2760 } /* searching_loose_scan_index */
2761 }
2762 },
2763 {
2764 "table": "`t2`",
2765 "best_access_path": {
2766 "considered_access_paths": [
2767 {
2768 "rows_to_scan": 100,
2769 "filtering_effect": [
2770 ] /* filtering_effect */,
2771 "final_filtering_effect": 1,
2772 "access_type": "scan",
2773 "using_join_cache": true,
2774 "buffers_needed": 32,
2775 "resulting_rows": 100,
2776 "cost": 100097,
2777 "chosen": true
2778 }
2779 ] /* considered_access_paths */
2780 } /* best_access_path */
2781 }
2782 ] /* tables */
2783 } /* recalculate_access_paths_and_cost */
2784 }
2785 ]
2786 drop table t0,t1,t2;
2787 #
2788 # Discover bound equality thanks to equality propagation
2789 # specific of ON clause.
2790 #
2791 CREATE TABLE t1 (
2792 a int(11) DEFAULT NULL,
2793 b varchar(100) DEFAULT NULL,
2794 c int(11) DEFAULT NULL,
2795 KEY b_c_a (b,c,a)
2796 ) ENGINE=InnoDB, CHARSET utf8mb4;
2797 Warnings:
2798 Warning 1681 Integer display width is deprecated and will be removed in a future release.
2799 Warning 1681 Integer display width is deprecated and will be removed in a future release.
2800 explain select *
2801 from t1 left join t1 as t2
2802 on (t2.a= t1.a and (t2.a,t2.b) in (select a,b from t1 as t3))
2803 where t1.a < 5;
2804 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2805 1 SIMPLE t1 NULL index NULL b_c_a 413 NULL 1 100.00 Using where; Using index
2806 1 SIMPLE t3 NULL index b_c_a b_c_a 413 NULL 1 100.00 Using where; Using index; LooseScan
2807 1 SIMPLE t2 NULL ref b_c_a b_c_a 403 test.t3.b 1 100.00 Using where; Using index
2808 Warnings:
2809 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` left join (`test`.`t1` `t2` semi join (`test`.`t1` `t3`)) on(((`test`.`t2`.`b` = `test`.`t3`.`b`) and (`test`.`t3`.`a` = `test`.`t1`.`a`) and (`test`.`t2`.`a` = `test`.`t1`.`a`))) where (`test`.`t1`.`a` < 5)
2810 drop table t1;
2811 #
2812 # Show that loosescan planning is not dependent on order of
2813 # creation of indexes anymore.
2814 #
2815 create table it(a int, b int, index a_b (a,b), index a (a))
2816 engine=InnoDB;
2817 insert into it values(1,1),(2,3),(4,3);
2818 select * from it as ot
2819 where (ot.a,ot.b) in (select it.a,it.b from it where it.b=3);
2820 a b
2821 2 3
2822 4 3
2823 select TRACE LIKE '%"index_handles_needed_semijoin_equalities": false%'
2824 from information_schema.optimizer_trace;
2825 TRACE LIKE '%"index_handles_needed_semijoin_equalities": false%'
2826 0
2827 drop table it;
2828 create table it(a int, b int, index a (a),index a_b (a,b))
2829 engine=InnoDB;
2830 insert into it values(1,1),(2,3),(4,3);
2831 select * from it as ot
2832 where (ot.a,ot.b) in (select it.a,it.b from it where it.b=3);
2833 a b
2834 2 3
2835 4 3
2836 select TRACE LIKE '%"index_handles_needed_semijoin_equalities": false%'
2837 from information_schema.optimizer_trace;
2838 TRACE LIKE '%"index_handles_needed_semijoin_equalities": false%'
2839 0
2840 drop table it;
2841 #
2842 # Show that we reject LooseScan if no handled key parts
2843 #
2844 CREATE TABLE t1 (a INT, b INT, KEY (a)) ENGINE=INNODB;
2845 CREATE TABLE t2 (a INT, b INT) ENGINE=INNODB;
2846 EXPLAIN SELECT * FROM t2 AS t3, t2
2847 WHERE t2.b=t3.b AND
2848 (t2.b) IN (SELECT b*3 FROM t1 WHERE a=10);
2849 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2850 1 SIMPLE t3 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
2851 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (hash join)
2852 1 SIMPLE t1 NULL ref a a 5 const 1 100.00 Using where; Start temporary; End temporary
2853 Warnings:
2854 Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` `t3` join `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = 10) and (`test`.`t2`.`b` = `test`.`t3`.`b`) and (`test`.`t3`.`b` = (`test`.`t1`.`b` * 3)))
2855 SELECT TRACE LIKE '%"some_index_part_used": false%'
2856 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
2857 TRACE LIKE '%"some_index_part_used": false%'
2858 1
2859 DROP TABLE t1,t2;
2860 #
2861 # Show that we detect a hole in sequence of key parts
2862 #
2863 CREATE TABLE ot1 (a INTEGER);
2864 INSERT INTO ot1 VALUES (0),(1),(3),(7);
2865 CREATE TABLE it1 (a VARCHAR(1), b INTEGER, KEY (a,b)) CHARSET utf8mb4;
2866 INSERT INTO it1 VALUES ('a',7), ('b',7);
2867 CREATE TABLE it2 (a VARCHAR(1), b INTEGER, KEY (a,b)) CHARSET utf8mb4;
2868 INSERT INTO it2 VALUES ('a',7), ('b',7);
2869 explain SELECT * FROM ot1
2870 WHERE a IN (
2871 SELECT it1.b
2872 FROM it1 JOIN it2
2873 ON it1.a = it2.a
2874 );
2875 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2876 1 SIMPLE it1 NULL index a a 12 NULL 2 100.00 Using index; Start temporary
2877 1 SIMPLE ot1 NULL ALL NULL NULL NULL NULL 4 25.00 Using where; Using join buffer (hash join)
2878 1 SIMPLE it2 NULL index a a 12 NULL 2 50.00 Using where; Using index; End temporary; Using join buffer (hash join)
2879 Warnings:
2880 Note 1003 /* select#1 */ select `test`.`ot1`.`a` AS `a` from `test`.`ot1` semi join (`test`.`it1` join `test`.`it2`) where ((`test`.`it2`.`a` = `test`.`it1`.`a`) and (`test`.`ot1`.`a` = `test`.`it1`.`b`))
2881 SELECT TRACE LIKE '%"index_can_remove_duplicates": false%'
2882 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
2883 TRACE LIKE '%"index_can_remove_duplicates": false%'
2884 1
2885 DROP TABLE ot1, it1, it2;
2886 #
2887 # Show that handled keyparts cannot be on prefix
2888 #
2889 create table t1 (a int, b varchar(100), key a_b (a,b)) charset utf8mb4;
2890 insert into t1 values(25,'111111'),(25,'1111112');
2891 explain select * from t1 as t2 where t2.b in (select b from t1 where a=25);
2892 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2893 1 SIMPLE t1 NULL ref a_b a_b 5 const 1 100.00 Using index; LooseScan
2894 1 SIMPLE t2 NULL index NULL a_b 408 NULL 2 50.00 Using where; Using index; Using join buffer (hash join)
2895 Warnings:
2896 Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` `t2` semi join (`test`.`t1`) where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t1`.`a` = 25))
2897 select * from t1 as t2 where t2.b in (select b from t1 where a=25);
2898 a b
2899 25 111111
2900 25 1111112
2901 alter table t1 drop key a_b, add key a_b_prefix (a,b(2));
2902 explain select * from t1 as t2 where t2.b in (select b from t1 where a=25);
2903 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2904 1 SIMPLE t1 NULL ref a_b_prefix a_b_prefix 5 const 1 100.00 Start temporary
2905 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where; End temporary; Using join buffer (hash join)
2906 Warnings:
2907 Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` `t2` semi join (`test`.`t1`) where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t1`.`a` = 25))
2908 select * from t1 as t2 where t2.b in (select b from t1 where a=25);
2909 a b
2910 25 111111
2911 25 1111112
2912 select TRACE LIKE '%"index_handles_needed_semijoin_equalities": false%'
2913 from information_schema.optimizer_trace;
2914 TRACE LIKE '%"index_handles_needed_semijoin_equalities": false%'
2915 1
2916 drop table t1;
2917 set optimizer_switch=@old_opt_switch;
2918 set optimizer_prune_level=@old_opt_prune_level;
2919 # Tracing of CTEs using the same tmp table: observe
2920 # "reusing_tmp_table" and only one "creating_tmp_table".
2921 with q(a) as (select 1 union all select 2)
2922 select * from q, q q1 where q.a=1 and q1.a=2;
2923 a a
2924 1 2
2925 select TRACE from information_schema.optimizer_trace;
2926 TRACE
2927 {
2928 "steps": [
2929 {
2930 "join_preparation": {
2931 "select#": 1,
2932 "steps": [
2933 {
2934 "join_preparation": {
2935 "select#": 2,
2936 "steps": [
2937 {
2938 "expanded_query": "/* select#2 */ select 1 AS `1`"
2939 }
2940 ] /* steps */
2941 } /* join_preparation */
2942 },
2943 {
2944 "join_preparation": {
2945 "select#": 3,
2946 "steps": [
2947 {
2948 "expanded_query": "/* select#3 */ select 2 AS `2`"
2949 }
2950 ] /* steps */
2951 } /* join_preparation */
2952 },
2953 {
2954 "derived": {
2955 "table": "`q`",
2956 "select#": 2,
2957 "materialized": true
2958 } /* derived */
2959 },
2960 {
2961 "join_preparation": {
2962 "select#": 5,
2963 "steps": [
2964 {
2965 "expanded_query": "/* select#5 */ select 1 AS `1`"
2966 }
2967 ] /* steps */
2968 } /* join_preparation */
2969 },
2970 {
2971 "join_preparation": {
2972 "select#": 6,
2973 "steps": [
2974 {
2975 "expanded_query": "/* select#6 */ select 2 AS `2`"
2976 }
2977 ] /* steps */
2978 } /* join_preparation */
2979 },
2980 {
2981 "derived": {
2982 "table": "`q` `q1`",
2983 "select#": 5,
2984 "materialized": true,
2985 "reusing_tmp_table": true
2986 } /* derived */
2987 },
2988 {
2989 "expanded_query": "/* select#1 */ select `q`.`a` AS `a`,`q1`.`a` AS `a` from `q` join `q` `q1` where ((`q`.`a` = 1) and (`q1`.`a` = 2))"
2990 }
2991 ] /* steps */
2992 } /* join_preparation */
2993 },
2994 {
2995 "join_optimization": {
2996 "select#": 1,
2997 "steps": [
2998 {
2999 "join_optimization": {
3000 "select#": 2,
3001 "steps": [
3002 ] /* steps */
3003 } /* join_optimization */
3004 },
3005 {
3006 "join_optimization": {
3007 "select#": 3,
3008 "steps": [
3009 ] /* steps */
3010 } /* join_optimization */
3011 },
3012 {
3013 "join_optimization": {
3014 "select#": 5,
3015 "steps": [
3016 ] /* steps */
3017 } /* join_optimization */
3018 },
3019 {
3020 "join_optimization": {
3021 "select#": 6,
3022 "steps": [
3023 ] /* steps */
3024 } /* join_optimization */
3025 },
3026 {
3027 "condition_processing": {
3028 "condition": "WHERE",
3029 "original_condition": "((`q`.`a` = 1) and (`q1`.`a` = 2))",
3030 "steps": [
3031 {
3032 "transformation": "equality_propagation",
3033 "resulting_condition": "(multiple equal(1, `q`.`a`) and multiple equal(2, `q1`.`a`))"
3034 },
3035 {
3036 "transformation": "constant_propagation",
3037 "resulting_condition": "(multiple equal(1, `q`.`a`) and multiple equal(2, `q1`.`a`))"
3038 },
3039 {
3040 "transformation": "trivial_condition_removal",
3041 "resulting_condition": "(multiple equal(1, `q`.`a`) and multiple equal(2, `q1`.`a`))"
3042 }
3043 ] /* steps */
3044 } /* condition_processing */
3045 },
3046 {
3047 "substitute_generated_columns": {
3048 } /* substitute_generated_columns */
3049 },
3050 {
3051 "table_dependencies": [
3052 {
3053 "table": "`q`",
3054 "row_may_be_null": false,
3055 "map_bit": 0,
3056 "depends_on_map_bits": [
3057 ] /* depends_on_map_bits */
3058 },
3059 {
3060 "table": "`q` `q1`",
3061 "row_may_be_null": false,
3062 "map_bit": 1,
3063 "depends_on_map_bits": [
3064 ] /* depends_on_map_bits */
3065 }
3066 ] /* table_dependencies */
3067 },
3068 {
3069 "ref_optimizer_key_uses": [
3070 {
3071 "table": "`q`",
3072 "field": "a",
3073 "equals": "1",
3074 "null_rejecting": true
3075 },
3076 {
3077 "table": "`q`",
3078 "field": "a",
3079 "equals": "1",
3080 "null_rejecting": true
3081 },
3082 {
3083 "table": "`q` `q1`",
3084 "field": "a",
3085 "equals": "2",
3086 "null_rejecting": true
3087 },
3088 {
3089 "table": "`q` `q1`",
3090 "field": "a",
3091 "equals": "2",
3092 "null_rejecting": true
3093 }
3094 ] /* ref_optimizer_key_uses */
3095 },
3096 {
3097 "rows_estimation": [
3098 {
3099 "table": "`q`",
3100 "table_scan": {
3101 "rows": 2,
3102 "cost": 2.525
3103 } /* table_scan */
3104 },
3105 {
3106 "table": "`q` `q1`",
3107 "table_scan": {
3108 "rows": 2,
3109 "cost": 2.525
3110 } /* table_scan */
3111 }
3112 ] /* rows_estimation */
3113 },
3114 {
3115 "considered_execution_plans": [
3116 {
3117 "plan_prefix": [
3118 ] /* plan_prefix */,
3119 "table": "`q`",
3120 "best_access_path": {
3121 "considered_access_paths": [
3122 {
3123 "access_type": "ref",
3124 "index": "<auto_key0>",
3125 "rows": 1,
3126 "cost": 0.6,
3127 "chosen": true
3128 },
3129 {
3130 "access_type": "ref",
3131 "index": "<auto_key1>",
3132 "rows": 1,
3133 "cost": 0.6,
3134 "chosen": false
3135 },
3136 {
3137 "access_type": "scan",
3138 "cost": 2.725,
3139 "rows": 2,
3140 "chosen": false,
3141 "cause": "cost"
3142 }
3143 ] /* considered_access_paths */
3144 } /* best_access_path */,
3145 "condition_filtering_pct": 100,
3146 "rows_for_plan": 1,
3147 "cost_for_plan": 0.6,
3148 "rest_of_plan": [
3149 {
3150 "plan_prefix": [
3151 "`q`"
3152 ] /* plan_prefix */,
3153 "table": "`q` `q1`",
3154 "best_access_path": {
3155 "considered_access_paths": [
3156 {
3157 "access_type": "ref",
3158 "index": "<auto_key0>",
3159 "rows": 1,
3160 "cost": 0.6,
3161 "chosen": true
3162 },
3163 {
3164 "access_type": "ref",
3165 "index": "<auto_key1>",
3166 "rows": 1,
3167 "cost": 0.6,
3168 "chosen": false
3169 },
3170 {
3171 "access_type": "scan",
3172 "cost": 2.725,
3173 "rows": 2,
3174 "chosen": false,
3175 "cause": "cost"
3176 }
3177 ] /* considered_access_paths */
3178 } /* best_access_path */,
3179 "condition_filtering_pct": 100,
3180 "rows_for_plan": 1,
3181 "cost_for_plan": 1.2,
3182 "chosen": true
3183 }
3184 ] /* rest_of_plan */
3185 },
3186 {
3187 "plan_prefix": [
3188 ] /* plan_prefix */,
3189 "table": "`q` `q1`",
3190 "best_access_path": {
3191 "considered_access_paths": [
3192 {
3193 "access_type": "ref",
3194 "index": "<auto_key0>",
3195 "rows": 1,
3196 "cost": 0.6,
3197 "chosen": true
3198 },
3199 {
3200 "access_type": "ref",
3201 "index": "<auto_key1>",
3202 "rows": 1,
3203 "cost": 0.6,
3204 "chosen": false
3205 },
3206 {
3207 "access_type": "scan",
3208 "cost": 2.725,
3209 "rows": 2,
3210 "chosen": false,
3211 "cause": "cost"
3212 }
3213 ] /* considered_access_paths */
3214 } /* best_access_path */,
3215 "condition_filtering_pct": 100,
3216 "rows_for_plan": 1,
3217 "cost_for_plan": 0.6,
3218 "pruned_by_heuristic": true
3219 }
3220 ] /* considered_execution_plans */
3221 },
3222 {
3223 "attaching_conditions_to_tables": {
3224 "original_condition": "((`q1`.`a` = 2) and (`q`.`a` = 1))",
3225 "attached_conditions_computation": [
3226 ] /* attached_conditions_computation */,
3227 "attached_conditions_summary": [
3228 {
3229 "table": "`q`",
3230 "attached": "(`q`.`a` = 1)"
3231 },
3232 {
3233 "table": "`q` `q1`",
3234 "attached": "(`q1`.`a` = 2)"
3235 }
3236 ] /* attached_conditions_summary */
3237 } /* attaching_conditions_to_tables */
3238 },
3239 {
3240 "finalizing_table_conditions": [
3241 {
3242 "table": "`q`",
3243 "original_table_condition": "(`q`.`a` = 1)",
3244 "final_table_condition ": null
3245 },
3246 {
3247 "table": "`q` `q1`",
3248 "original_table_condition": "(`q1`.`a` = 2)",
3249 "final_table_condition ": null
3250 }
3251 ] /* finalizing_table_conditions */
3252 },
3253 {
3254 "refine_plan": [
3255 {
3256 "table": "`q`"
3257 },
3258 {
3259 "table": "`q` `q1`"
3260 }
3261 ] /* refine_plan */
3262 }
3263 ] /* steps */
3264 } /* join_optimization */
3265 },
3266 {
3267 "join_execution": {
3268 "select#": 1,
3269 "steps": [
3270 {
3271 "creating_tmp_table": {
3272 "tmp_table_info": {
3273 "table": "`q`",
3274 "in_plan_at_position": 0,
3275 "columns": 1,
3276 "row_length": 9,
3277 "key_length": 8,
3278 "unique_constraint": false,
3279 "makes_grouped_rows": false,
3280 "cannot_insert_duplicates": false,
3281 "location": "TempTable"
3282 } /* tmp_table_info */
3283 } /* creating_tmp_table */
3284 },
3285 {
3286 "materialize": {
3287 "select#": 2,
3288 "steps": [
3289 ] /* steps */
3290 } /* materialize */
3291 },
3292 {
3293 "materialize": {
3294 "select#": 3,
3295 "steps": [
3296 ] /* steps */
3297 } /* materialize */
3298 }
3299 ] /* steps */
3300 } /* join_execution */
3301 }
3302 ] /* steps */
3303 }
3304 # Tracing of repetitions of the recursive member
3305 # can be silenced with repeated_subselect=off
3306 with recursive qn(n) as
3307 (select 1 union all select n+1 from qn where n<4)
3308 select count(*) from qn;
3309 count(*)
3310 4
3311 SELECT show_json_object('"join_execution": {', TRACE)
3312 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
3313 show_json_object('"join_execution": {', TRACE)
3314 "join_execution": {
3315 "select#": 1,
3316 "steps": [
3317 {
3318 "creating_tmp_table": {
3319 "tmp_table_info": {
3320 "table": "`qn`",
3321 "in_plan_at_position": 0,
3322 "columns": 1,
3323 "row_length": 9,
3324 "key_length": 0,
3325 "unique_constraint": false,
3326 "makes_grouped_rows": false,
3327 "cannot_insert_duplicates": false,
3328 "location": "TempTable"
3329 } /* tmp_table_info */
3330 } /* creating_tmp_table */
3331 },
3332 {
3333 "materialize": {
3334 "select#": 2,
3335 "steps": [
3336 ] /* steps */
3337 } /* materialize */
3338 },
3339 {
3340 "materialize": {
3341 "select#": 3,
3342 "steps": [
3343 ] /* steps */
3344 } /* materialize */
3345 },
3346 {
3347 "materialize": {
3348 "select#": 3,
3349 "steps": [
3350 ] /* steps */
3351 } /* materialize */
3352 }
3353 ] /* steps */
3354 }
3355 SET @@optimizer_trace_features="repeated_subselect=off";
3356 with recursive qn(n) as
3357 (select 1 union all select n+1 from qn where n<4)
3358 select count(*) from qn;
3359 count(*)
3360 4
3361 SELECT show_json_object('"join_execution": {', TRACE)
3362 FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
3363 show_json_object('"join_execution": {', TRACE)
3364 "join_execution": {
3365 "select#": 1,
3366 "steps": [
3367 {
3368 "creating_tmp_table": {
3369 "tmp_table_info": {
3370 "table": "`qn`",
3371 "in_plan_at_position": 0,
3372 "columns": 1,
3373 "row_length": 9,
3374 "key_length": 0,
3375 "unique_constraint": false,
3376 "makes_grouped_rows": false,
3377 "cannot_insert_duplicates": false,
3378 "location": "TempTable"
3379 } /* tmp_table_info */
3380 } /* creating_tmp_table */
3381 },
3382 {
3383 "materialize": {
3384 "select#": 2,
3385 "steps": [
3386 ] /* steps */
3387 } /* materialize */
3388 },
3389 {
3390 "materialize": {
3391 "select#": 3,
3392 "steps": [
3393 ] /* steps */
3394 } /* materialize */
3395 }
3396 ] /* steps */
3397 }
3398 SET @@optimizer_trace_features=default;
3399 drop function show_json_object;