Debian Codegraph @

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