1set optimizer_trace_max_mem_size=1048576; 2set end_markers_in_json=on; 3set optimizer_trace="enabled=on"; 4# check that if a sub-statement should not be traced, 5# it is not traced even if inside a traced top statement 6 7set optimizer_trace_offset=0, optimizer_trace_limit=100; 8create function f1(arg char(1)) returns int 9begin 10declare res int; 11declare dummy varchar(1); 12select 1 into res from dual; 13select TRACE+NULL into dummy from information_schema.OPTIMIZER_TRACE limit 1; 14select 2 into res from dual; 15return 3; 16end| 17select f1("c")| 18f1("c") 193 20 21select * from information_schema.OPTIMIZER_TRACE| 22QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES 23select f1("c") { 24 "steps": [ 25 { 26 "join_preparation": { 27 "select#": 1, 28 "steps": [ 29 { 30 "expanded_query": "/* select#1 */ select `f1`('c') AS `f1(\"c\")`" 31 } 32 ] /* steps */ 33 } /* join_preparation */ 34 } 35 ] /* steps */ 36} 0 0 37select f1("c") { 38 "steps": [ 39 { 40 "join_preparation": { 41 "select#": 1, 42 "steps": [ 43 { 44 "expanded_query": "/* select#1 */ select `f1`('c') AS `f1(\"c\")`" 45 } 46 ] /* steps */ 47 } /* join_preparation */ 48 }, 49 { 50 "join_optimization": { 51 "select#": 1, 52 "steps": [ 53 ] /* steps */ 54 } /* join_optimization */ 55 }, 56 { 57 "join_execution": { 58 "select#": 1, 59 "steps": [ 60 ] /* steps */ 61 } /* join_execution */ 62 } 63 ] /* steps */ 64} 0 0 65set res@1 NULL { 66 "steps": [ 67 ] /* steps */ 68} 0 0 69set dummy@2 NULL { 70 "steps": [ 71 ] /* steps */ 72} 0 0 73select 1 into res from dual { 74 "steps": [ 75 { 76 "join_preparation": { 77 "select#": 1, 78 "steps": [ 79 { 80 "expanded_query": "/* select#1 */ select 1 AS `1`" 81 } 82 ] /* steps */ 83 } /* join_preparation */ 84 }, 85 { 86 "join_optimization": { 87 "select#": 1, 88 "steps": [ 89 ] /* steps */ 90 } /* join_optimization */ 91 }, 92 { 93 "join_execution": { 94 "select#": 1, 95 "steps": [ 96 ] /* steps */ 97 } /* join_execution */ 98 } 99 ] /* steps */ 100} 0 0 101select 2 into res from dual { 102 "steps": [ 103 { 104 "join_preparation": { 105 "select#": 1, 106 "steps": [ 107 { 108 "expanded_query": "/* select#1 */ select 2 AS `2`" 109 } 110 ] /* steps */ 111 } /* join_preparation */ 112 }, 113 { 114 "join_optimization": { 115 "select#": 1, 116 "steps": [ 117 ] /* steps */ 118 } /* join_optimization */ 119 }, 120 { 121 "join_execution": { 122 "select#": 1, 123 "steps": [ 124 ] /* steps */ 125 } /* join_execution */ 126 } 127 ] /* steps */ 128} 0 0 129freturn 3 3 { 130 "steps": [ 131 ] /* steps */ 132} 0 0 133set optimizer_trace_offset=default, optimizer_trace_limit=default; 134drop function f1; 135# check that if a tracing gets disabled in a routine's body, 136# substatements are not traced 137 138set optimizer_trace_offset=0, optimizer_trace_limit=100; 139create function f1(arg char(1)) returns int 140begin 141declare res int; 142declare dummy varchar(1); 143set optimizer_trace="enabled=off"; 144select 1 into res from dual; 145select TRACE+NULL into dummy from information_schema.OPTIMIZER_TRACE limit 1; 146select 2 into res from dual; 147return 3; 148end| 149select f1("c")| 150f1("c") 1513 152 153select * from information_schema.OPTIMIZER_TRACE| 154QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES 155select f1("c") { 156 "steps": [ 157 { 158 "join_preparation": { 159 "select#": 1, 160 "steps": [ 161 { 162 "expanded_query": "/* select#1 */ select `f1`('c') AS `f1(\"c\")`" 163 } 164 ] /* steps */ 165 } /* join_preparation */ 166 } 167 ] /* steps */ 168} 0 0 169select f1("c") { 170 "steps": [ 171 { 172 "join_preparation": { 173 "select#": 1, 174 "steps": [ 175 { 176 "expanded_query": "/* select#1 */ select `f1`('c') AS `f1(\"c\")`" 177 } 178 ] /* steps */ 179 } /* join_preparation */ 180 }, 181 { 182 "join_optimization": { 183 "select#": 1, 184 "steps": [ 185 ] /* steps */ 186 } /* join_optimization */ 187 }, 188 { 189 "join_execution": { 190 "select#": 1, 191 "steps": [ 192 ] /* steps */ 193 } /* join_execution */ 194 } 195 ] /* steps */ 196} 0 0 197set res@1 NULL { 198 "steps": [ 199 ] /* steps */ 200} 0 0 201set dummy@2 NULL { 202 "steps": [ 203 ] /* steps */ 204} 0 0 205set optimizer_trace_offset=default, optimizer_trace_limit=default; 206select @@optimizer_trace; 207@@optimizer_trace 208enabled=off,one_line=off 209set optimizer_trace="enabled=on"; 210drop function f1; 211 212# Check that if a sub-statement reads OPTIMIZER_TRACE, 213# thus reading the unfinished trace of its caller statement, 214# there is no crash. 215 216create temporary table optt 217(id int primary key auto_increment, 218QUERY varchar(200), 219TRACE text); 220create table t1 (a int, key(a)); 221insert into t1 values(2); 222set optimizer_trace_offset=0, optimizer_trace_limit=100; 223create function f1(arg char(1)) returns int 224begin 225declare res int; 226insert into optt select NULL, QUERY, TRACE from information_schema.OPTIMIZER_TRACE; 227return 3; 228end| 229select * from t1 where a in (select f1("c") from t1)| 230a 231 232set optimizer_trace="enabled=off"; 233this should find unfinished traces 234select count(*) from optt where TRACE NOT LIKE "%] /* steps */\n}"; 235count(*) 2361 237select count(*)<>0 from optt; 238count(*)<>0 2391 240this should not 241select count(*) from information_schema.OPTIMIZER_TRACE where TRACE NOT LIKE "%] /* steps */\n}"; 242count(*) 2430 244select count(*)<>0 from information_schema.OPTIMIZER_TRACE; 245count(*)<>0 2461 247set optimizer_trace_offset=default, optimizer_trace_limit=default; 248drop temporary table optt; 249drop function f1; 250drop table t1; 251set optimizer_trace="enabled=on"; 252 253# check of crash with I_S.VIEWS (TABLE_LIST::alias==NULL) 254 255create table t1(a int, b int); 256create view v1 as select a from t1; 257select VIEW_DEFINITION from information_schema.VIEWS 258where TABLE_SCHEMA="test" and TABLE_NAME="v1"; 259VIEW_DEFINITION 260select `test`.`t1`.`a` AS `a` from `test`.`t1` 261drop table t1; 262drop view v1; 263 264# check for readable display of BIT values 265 266create table t1 (a bit(5), key(a)); 267insert into t1 values(b'00000'),(b'01101'); 268select cast(a as unsigned) from t1 where a > b'01100'; 269cast(a as unsigned) 27013 271select TRACE from information_schema.OPTIMIZER_TRACE; 272TRACE 273{ 274 "steps": [ 275 { 276 "join_preparation": { 277 "select#": 1, 278 "steps": [ 279 { 280 "expanded_query": "/* select#1 */ select cast(`t1`.`a` as unsigned) AS `cast(a as unsigned)` from `t1` where (`t1`.`a` > 0x0c)" 281 } 282 ] /* steps */ 283 } /* join_preparation */ 284 }, 285 { 286 "join_optimization": { 287 "select#": 1, 288 "steps": [ 289 { 290 "condition_processing": { 291 "condition": "WHERE", 292 "original_condition": "(`t1`.`a` > 0x0c)", 293 "steps": [ 294 { 295 "transformation": "equality_propagation", 296 "resulting_condition": "(`t1`.`a` > 0x0c)" 297 }, 298 { 299 "transformation": "constant_propagation", 300 "resulting_condition": "(`t1`.`a` > 0x0c)" 301 }, 302 { 303 "transformation": "trivial_condition_removal", 304 "resulting_condition": "(`t1`.`a` > 0x0c)" 305 } 306 ] /* steps */ 307 } /* condition_processing */ 308 }, 309 { 310 "substitute_generated_columns": { 311 } /* substitute_generated_columns */ 312 }, 313 { 314 "table_dependencies": [ 315 { 316 "table": "`t1`", 317 "row_may_be_null": false, 318 "map_bit": 0, 319 "depends_on_map_bits": [ 320 ] /* depends_on_map_bits */ 321 } 322 ] /* table_dependencies */ 323 }, 324 { 325 "ref_optimizer_key_uses": [ 326 ] /* ref_optimizer_key_uses */ 327 }, 328 { 329 "rows_estimation": [ 330 { 331 "table": "`t1`", 332 "range_analysis": { 333 "table_scan": { 334 "rows": 2, 335 "cost": 4.5034 336 } /* table_scan */, 337 "potential_range_indexes": [ 338 { 339 "index": "a", 340 "usable": true, 341 "key_parts": [ 342 "a" 343 ] /* key_parts */ 344 } 345 ] /* potential_range_indexes */, 346 "best_covering_index_scan": { 347 "index": "a", 348 "cost": 1.4175, 349 "chosen": true 350 } /* best_covering_index_scan */, 351 "setup_range_conditions": [ 352 ] /* setup_range_conditions */, 353 "group_index_range": { 354 "chosen": false, 355 "cause": "not_group_by_or_distinct" 356 } /* group_index_range */, 357 "analyzing_range_alternatives": { 358 "range_scan_alternatives": [ 359 { 360 "index": "a", 361 "ranges": [ 362 "12 < a" 363 ] /* ranges */, 364 "index_dives_for_eq_ranges": true, 365 "rowid_ordered": false, 366 "using_mrr": false, 367 "index_only": true, 368 "rows": 2, 369 "cost": 1.4275, 370 "chosen": false, 371 "cause": "cost" 372 } 373 ] /* range_scan_alternatives */, 374 "analyzing_roworder_intersect": { 375 "usable": false, 376 "cause": "too_few_roworder_scans" 377 } /* analyzing_roworder_intersect */ 378 } /* analyzing_range_alternatives */ 379 } /* range_analysis */ 380 } 381 ] /* rows_estimation */ 382 }, 383 { 384 "considered_execution_plans": [ 385 { 386 "plan_prefix": [ 387 ] /* plan_prefix */, 388 "table": "`t1`", 389 "best_access_path": { 390 "considered_access_paths": [ 391 { 392 "rows_to_scan": 2, 393 "access_type": "scan", 394 "resulting_rows": 2, 395 "cost": 2.4034, 396 "chosen": true 397 } 398 ] /* considered_access_paths */ 399 } /* best_access_path */, 400 "condition_filtering_pct": 100, 401 "rows_for_plan": 2, 402 "cost_for_plan": 2.4034, 403 "chosen": true 404 } 405 ] /* considered_execution_plans */ 406 }, 407 { 408 "attaching_conditions_to_tables": { 409 "original_condition": "(`t1`.`a` > 0x0c)", 410 "attached_conditions_computation": [ 411 ] /* attached_conditions_computation */, 412 "attached_conditions_summary": [ 413 { 414 "table": "`t1`", 415 "attached": "(`t1`.`a` > 0x0c)" 416 } 417 ] /* attached_conditions_summary */ 418 } /* attaching_conditions_to_tables */ 419 }, 420 { 421 "refine_plan": [ 422 { 423 "table": "`t1`" 424 } 425 ] /* refine_plan */ 426 } 427 ] /* steps */ 428 } /* join_optimization */ 429 }, 430 { 431 "join_execution": { 432 "select#": 1, 433 "steps": [ 434 ] /* steps */ 435 } /* join_execution */ 436 } 437 ] /* steps */ 438} 439drop table t1; 440 441# check that trace lists all pushed down ON conditions 442 443create table t1 (i int not null); 444insert into t1 values (0), (2),(3),(4); 445create table t2 (i int not null); 446insert into t2 values (0),(1), (3),(4); 447create table t3 (i int not null); 448insert into t3 values (0),(1),(2), (4); 449select * from 450t1 LEFT JOIN 451( t2 LEFT JOIN 452( t3 453) 454ON t3.i = t2.i 455) 456ON t2.i = t1.i 457WHERE t3.i IS NULL 458; 459i i i 4603 3 NULL 4612 NULL NULL 462select TRACE from information_schema.OPTIMIZER_TRACE; 463TRACE 464{ 465 "steps": [ 466 { 467 "join_preparation": { 468 "select#": 1, 469 "steps": [ 470 { 471 "expanded_query": "/* select#1 */ select `t1`.`i` AS `i`,`t2`.`i` AS `i`,`t3`.`i` AS `i` from `t1` left join (`t2` left join `t3` on((`t3`.`i` = `t2`.`i`))) on((`t2`.`i` = `t1`.`i`)) where isnull(`t3`.`i`)" 472 } 473 ] /* steps */ 474 } /* join_preparation */ 475 }, 476 { 477 "join_optimization": { 478 "select#": 1, 479 "steps": [ 480 { 481 "condition_processing": { 482 "condition": "WHERE", 483 "original_condition": "isnull(`t3`.`i`)", 484 "steps": [ 485 { 486 "transformation": "equality_propagation", 487 "resulting_condition": "isnull(`t3`.`i`)" 488 }, 489 { 490 "transformation": "constant_propagation", 491 "resulting_condition": "isnull(`t3`.`i`)" 492 }, 493 { 494 "transformation": "trivial_condition_removal", 495 "resulting_condition": "isnull(`t3`.`i`)" 496 } 497 ] /* steps */ 498 } /* condition_processing */ 499 }, 500 { 501 "substitute_generated_columns": { 502 } /* substitute_generated_columns */ 503 }, 504 { 505 "table_dependencies": [ 506 { 507 "table": "`t1`", 508 "row_may_be_null": false, 509 "map_bit": 0, 510 "depends_on_map_bits": [ 511 ] /* depends_on_map_bits */ 512 }, 513 { 514 "table": "`t2`", 515 "row_may_be_null": true, 516 "map_bit": 1, 517 "depends_on_map_bits": [ 518 0 519 ] /* depends_on_map_bits */ 520 }, 521 { 522 "table": "`t3`", 523 "row_may_be_null": true, 524 "map_bit": 2, 525 "depends_on_map_bits": [ 526 0, 527 1 528 ] /* depends_on_map_bits */ 529 } 530 ] /* table_dependencies */ 531 }, 532 { 533 "ref_optimizer_key_uses": [ 534 ] /* ref_optimizer_key_uses */ 535 }, 536 { 537 "rows_estimation": [ 538 { 539 "table": "`t1`", 540 "table_scan": { 541 "rows": 4, 542 "cost": 2 543 } /* table_scan */ 544 }, 545 { 546 "table": "`t2`", 547 "table_scan": { 548 "rows": 4, 549 "cost": 2 550 } /* table_scan */ 551 }, 552 { 553 "table": "`t3`", 554 "table_scan": { 555 "rows": 4, 556 "cost": 2 557 } /* table_scan */ 558 } 559 ] /* rows_estimation */ 560 }, 561 { 562 "considered_execution_plans": [ 563 { 564 "plan_prefix": [ 565 ] /* plan_prefix */, 566 "table": "`t1`", 567 "best_access_path": { 568 "considered_access_paths": [ 569 { 570 "rows_to_scan": 4, 571 "access_type": "scan", 572 "resulting_rows": 4, 573 "cost": 2.8068, 574 "chosen": true 575 } 576 ] /* considered_access_paths */ 577 } /* best_access_path */, 578 "condition_filtering_pct": 100, 579 "rows_for_plan": 4, 580 "cost_for_plan": 2.8068, 581 "rest_of_plan": [ 582 { 583 "plan_prefix": [ 584 "`t1`" 585 ] /* plan_prefix */, 586 "table": "`t2`", 587 "best_access_path": { 588 "considered_access_paths": [ 589 { 590 "rows_to_scan": 4, 591 "access_type": "scan", 592 "using_join_cache": true, 593 "buffers_needed": 1, 594 "resulting_rows": 4, 595 "cost": 5.207, 596 "chosen": true 597 } 598 ] /* considered_access_paths */ 599 } /* best_access_path */, 600 "condition_filtering_pct": 100, 601 "rows_for_plan": 16, 602 "cost_for_plan": 8.0138, 603 "rest_of_plan": [ 604 { 605 "plan_prefix": [ 606 "`t1`", 607 "`t2`" 608 ] /* plan_prefix */, 609 "table": "`t3`", 610 "best_access_path": { 611 "considered_access_paths": [ 612 { 613 "rows_to_scan": 4, 614 "access_type": "scan", 615 "using_join_cache": true, 616 "buffers_needed": 1, 617 "resulting_rows": 1, 618 "cost": 5.8083, 619 "chosen": true 620 } 621 ] /* considered_access_paths */ 622 } /* best_access_path */, 623 "condition_filtering_pct": 100, 624 "rows_for_plan": 16, 625 "cost_for_plan": 13.822, 626 "chosen": true 627 } 628 ] /* rest_of_plan */ 629 } 630 ] /* rest_of_plan */ 631 } 632 ] /* considered_execution_plans */ 633 }, 634 { 635 "attaching_conditions_to_tables": { 636 "original_condition": "isnull(`t3`.`i`)", 637 "attached_conditions_computation": [ 638 ] /* attached_conditions_computation */, 639 "attached_conditions_summary": [ 640 { 641 "table": "`t1`", 642 "attached": null 643 }, 644 { 645 "table": "`t2`", 646 "attached": "<if>(is_not_null_compl(t2..t3), (`t2`.`i` = `t1`.`i`), true)" 647 }, 648 { 649 "table": "`t3`", 650 "attached": "(<if>(found_match(t2..t3), <if>(found_match(t3), isnull(`t3`.`i`), true), true) and <if>(is_not_null_compl(t3), (`t3`.`i` = `t1`.`i`), true))" 651 } 652 ] /* attached_conditions_summary */ 653 } /* attaching_conditions_to_tables */ 654 }, 655 { 656 "refine_plan": [ 657 { 658 "table": "`t1`" 659 }, 660 { 661 "table": "`t2`" 662 }, 663 { 664 "table": "`t3`" 665 } 666 ] /* refine_plan */ 667 } 668 ] /* steps */ 669 } /* join_optimization */ 670 }, 671 { 672 "join_execution": { 673 "select#": 1, 674 "steps": [ 675 ] /* steps */ 676 } /* join_execution */ 677 } 678 ] /* steps */ 679} 680drop table t1,t2,t3; 681 682# test of tracing a query with an HAVING condition, in 683# ps-protocol, does not crash 684 685CREATE TABLE t1 (f1 INT, f2 VARCHAR(1)); 686INSERT INTO t1 VALUES (16,'f'); 687INSERT INTO t1 VALUES (16,'f'); 688CREATE TABLE t2 (f1 INT, f2 VARCHAR(1)); 689INSERT INTO t2 VALUES (13,'f'); 690INSERT INTO t2 VALUES (20,'f'); 691CREATE TABLE t3 (f1 INT, f2 VARCHAR(1)); 692INSERT INTO t3 VALUES (7,'f'); 693EXPLAIN SELECT t1.f2 FROM t1 694STRAIGHT_JOIN (t2 JOIN t3 ON t3.f2 = t2.f2 ) ON t3 .f2 = t2 .f2 695HAVING ('v', 'i') NOT IN (SELECT f2, MIN(f2) FROM t1) 696ORDER BY f2; 697id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6981 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort 6991 PRIMARY t3 NULL ALL NULL NULL NULL NULL 1 100.00 Using join buffer (Block Nested Loop) 7001 PRIMARY t2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where; Using join buffer (Block Nested Loop) 7012 SUBQUERY t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 702Warnings: 703Note 1003 /* select#1 */ select `test`.`t1`.`f2` AS `f2` from `test`.`t1` join `test`.`t2` join `test`.`t3` where (`test`.`t2`.`f2` = `test`.`t3`.`f2`) having (not(<in_optimizer>(<cache>(('v','i')),<exists>(/* select#2 */ select 1,1 from `test`.`t1` having (((<cache>('v') = `test`.`t1`.`f2`) or isnull(`test`.`t1`.`f2`)) and ((<cache>('i') = min(`test`.`t1`.`f2`)) or isnull(min(`test`.`t1`.`f2`))) and <is_not_null_test>(`test`.`t1`.`f2`) and <is_not_null_test>(min(`test`.`t1`.`f2`))))))) order by `test`.`t1`.`f2` 704SELECT t1.f2 FROM t1 705STRAIGHT_JOIN (t2 JOIN t3 ON t3.f2 = t2.f2 ) ON t3 .f2 = t2 .f2 706HAVING ('v', 'i') NOT IN (SELECT f2, MIN(f2) FROM t1) 707ORDER BY f2; 708f2 709f 710f 711f 712f 713select TRACE from information_schema.OPTIMIZER_TRACE; 714TRACE 715{ 716 "steps": [ 717 { 718 "join_preparation": { 719 "select#": 1, 720 "steps": [ 721 { 722 "join_preparation": { 723 "select#": 2, 724 "steps": [ 725 { 726 "expanded_query": "/* select#2 */ select `t1`.`f2`,min(`t1`.`f2`) from `t1` having (((<cache>('v') = `t1`.`f2`) or isnull(`t1`.`f2`)) and ((<cache>('i') = min(`t1`.`f2`)) or isnull(min(`t1`.`f2`))) and <is_not_null_test>(`t1`.`f2`) and <is_not_null_test>(min(`t1`.`f2`)))" 727 }, 728 { 729 "transformation": { 730 "select#": 2, 731 "from": "IN (SELECT)", 732 "to": "semijoin", 733 "chosen": false 734 } /* transformation */ 735 } 736 ] /* steps */ 737 } /* join_preparation */ 738 }, 739 { 740 "expanded_query": "/* select#1 */ select `t1`.`f2` AS `f2` from `t1` join `t2` join `t3` where ((`t3`.`f2` = `t2`.`f2`) and (`t3`.`f2` = `t2`.`f2`)) having (not(<in_optimizer>(('v','i'),<exists>(/* select#2 */ select `t1`.`f2`,min(`t1`.`f2`) from `t1` having (((<cache>('v') = `t1`.`f2`) or isnull(`t1`.`f2`)) and ((<cache>('i') = min(`t1`.`f2`)) or isnull(min(`t1`.`f2`))) and <is_not_null_test>(`t1`.`f2`) and <is_not_null_test>(min(`t1`.`f2`))))))) order by `t1`.`f2`" 741 } 742 ] /* steps */ 743 } /* join_preparation */ 744 }, 745 { 746 "join_optimization": { 747 "select#": 1, 748 "steps": [ 749 { 750 "condition_processing": { 751 "condition": "WHERE", 752 "original_condition": "((`t3`.`f2` = `t2`.`f2`) and (`t3`.`f2` = `t2`.`f2`))", 753 "steps": [ 754 { 755 "transformation": "equality_propagation", 756 "resulting_condition": "(multiple equal(`t3`.`f2`, `t2`.`f2`))" 757 }, 758 { 759 "transformation": "constant_propagation", 760 "resulting_condition": "(multiple equal(`t3`.`f2`, `t2`.`f2`))" 761 }, 762 { 763 "transformation": "trivial_condition_removal", 764 "resulting_condition": "multiple equal(`t3`.`f2`, `t2`.`f2`)" 765 } 766 ] /* steps */ 767 } /* condition_processing */ 768 }, 769 { 770 "condition_processing": { 771 "condition": "HAVING", 772 "original_condition": "(not(<in_optimizer>(('v','i'),<exists>(/* select#2 */ select `t1`.`f2`,min(`t1`.`f2`) from `t1` having (((<cache>('v') = `t1`.`f2`) or isnull(`t1`.`f2`)) and ((<cache>('i') = min(`t1`.`f2`)) or isnull(min(`t1`.`f2`))) and <is_not_null_test>(`t1`.`f2`) and <is_not_null_test>(min(`t1`.`f2`)))))))", 773 "steps": [ 774 { 775 "transformation": "constant_propagation", 776 "subselect_evaluation": [ 777 ] /* subselect_evaluation */, 778 "resulting_condition": "(not(<in_optimizer>(('v','i'),<exists>(/* select#2 */ select `t1`.`f2`,min(`t1`.`f2`) from `t1` having (((<cache>('v') = `t1`.`f2`) or isnull(`t1`.`f2`)) and ((<cache>('i') = min(`t1`.`f2`)) or isnull(min(`t1`.`f2`))) and <is_not_null_test>(`t1`.`f2`) and <is_not_null_test>(min(`t1`.`f2`)))))))" 779 }, 780 { 781 "transformation": "trivial_condition_removal", 782 "subselect_evaluation": [ 783 ] /* subselect_evaluation */, 784 "resulting_condition": "(not(<in_optimizer>(('v','i'),<exists>(/* select#2 */ select `t1`.`f2`,min(`t1`.`f2`) from `t1` having (((<cache>('v') = `t1`.`f2`) or isnull(`t1`.`f2`)) and ((<cache>('i') = min(`t1`.`f2`)) or isnull(min(`t1`.`f2`))) and <is_not_null_test>(`t1`.`f2`) and <is_not_null_test>(min(`t1`.`f2`)))))))" 785 } 786 ] /* steps */ 787 } /* condition_processing */ 788 }, 789 { 790 "substitute_generated_columns": { 791 } /* substitute_generated_columns */ 792 }, 793 { 794 "table_dependencies": [ 795 { 796 "table": "`t1`", 797 "row_may_be_null": false, 798 "map_bit": 0, 799 "depends_on_map_bits": [ 800 ] /* depends_on_map_bits */ 801 }, 802 { 803 "table": "`t2`", 804 "row_may_be_null": false, 805 "map_bit": 1, 806 "depends_on_map_bits": [ 807 0 808 ] /* depends_on_map_bits */ 809 }, 810 { 811 "table": "`t3`", 812 "row_may_be_null": false, 813 "map_bit": 2, 814 "depends_on_map_bits": [ 815 0 816 ] /* depends_on_map_bits */ 817 } 818 ] /* table_dependencies */ 819 }, 820 { 821 "ref_optimizer_key_uses": [ 822 ] /* ref_optimizer_key_uses */ 823 }, 824 { 825 "rows_estimation": [ 826 { 827 "table": "`t1`", 828 "table_scan": { 829 "rows": 2, 830 "cost": 2 831 } /* table_scan */ 832 }, 833 { 834 "table": "`t2`", 835 "table_scan": { 836 "rows": 2, 837 "cost": 2 838 } /* table_scan */ 839 }, 840 { 841 "table": "`t3`", 842 "table_scan": { 843 "rows": 1, 844 "cost": 2 845 } /* table_scan */ 846 } 847 ] /* rows_estimation */ 848 }, 849 { 850 "considered_execution_plans": [ 851 { 852 "plan_prefix": [ 853 ] /* plan_prefix */, 854 "table": "`t1`", 855 "best_access_path": { 856 "considered_access_paths": [ 857 { 858 "rows_to_scan": 2, 859 "access_type": "scan", 860 "resulting_rows": 2, 861 "cost": 2.4098, 862 "chosen": true, 863 "use_tmp_table": true 864 } 865 ] /* considered_access_paths */ 866 } /* best_access_path */, 867 "condition_filtering_pct": 100, 868 "rows_for_plan": 2, 869 "cost_for_plan": 2.4098, 870 "rest_of_plan": [ 871 { 872 "plan_prefix": [ 873 "`t1`" 874 ] /* plan_prefix */, 875 "table": "`t3`", 876 "best_access_path": { 877 "considered_access_paths": [ 878 { 879 "rows_to_scan": 1, 880 "access_type": "scan", 881 "using_join_cache": true, 882 "buffers_needed": 1, 883 "resulting_rows": 1, 884 "cost": 2.4049, 885 "chosen": true 886 } 887 ] /* considered_access_paths */ 888 } /* best_access_path */, 889 "condition_filtering_pct": 100, 890 "rows_for_plan": 2, 891 "cost_for_plan": 4.8147, 892 "rest_of_plan": [ 893 { 894 "plan_prefix": [ 895 "`t1`", 896 "`t3`" 897 ] /* plan_prefix */, 898 "table": "`t2`", 899 "best_access_path": { 900 "considered_access_paths": [ 901 { 902 "rows_to_scan": 2, 903 "access_type": "scan", 904 "using_join_cache": true, 905 "buffers_needed": 1, 906 "resulting_rows": 2, 907 "cost": 2.8099, 908 "chosen": true 909 } 910 ] /* considered_access_paths */ 911 } /* best_access_path */, 912 "condition_filtering_pct": 100, 913 "rows_for_plan": 4, 914 "cost_for_plan": 7.6246, 915 "sort_cost": 4, 916 "new_cost_for_plan": 11.625, 917 "chosen": true 918 } 919 ] /* rest_of_plan */ 920 }, 921 { 922 "plan_prefix": [ 923 "`t1`" 924 ] /* plan_prefix */, 925 "table": "`t2`", 926 "best_access_path": { 927 "considered_access_paths": [ 928 { 929 "rows_to_scan": 2, 930 "access_type": "scan", 931 "using_join_cache": true, 932 "buffers_needed": 1, 933 "resulting_rows": 2, 934 "cost": 2.8098, 935 "chosen": true 936 } 937 ] /* considered_access_paths */ 938 } /* best_access_path */, 939 "condition_filtering_pct": 100, 940 "rows_for_plan": 4, 941 "cost_for_plan": 5.2196, 942 "pruned_by_heuristic": true 943 } 944 ] /* rest_of_plan */ 945 } 946 ] /* considered_execution_plans */ 947 }, 948 { 949 "attaching_conditions_to_tables": { 950 "original_condition": "(`t2`.`f2` = `t3`.`f2`)", 951 "attached_conditions_computation": [ 952 ] /* attached_conditions_computation */, 953 "attached_conditions_summary": [ 954 { 955 "table": "`t1`", 956 "attached": null 957 }, 958 { 959 "table": "`t3`", 960 "attached": null 961 }, 962 { 963 "table": "`t2`", 964 "attached": "(`t2`.`f2` = `t3`.`f2`)" 965 } 966 ] /* attached_conditions_summary */ 967 } /* attaching_conditions_to_tables */ 968 }, 969 { 970 "clause_processing": { 971 "clause": "ORDER BY", 972 "original_clause": "`t1`.`f2`", 973 "items": [ 974 { 975 "item": "`t1`.`f2`" 976 } 977 ] /* items */, 978 "resulting_clause_is_simple": true, 979 "resulting_clause": "`t1`.`f2`" 980 } /* clause_processing */ 981 }, 982 { 983 "refine_plan": [ 984 { 985 "table": "`t1`" 986 }, 987 { 988 "table": "`t3`" 989 }, 990 { 991 "table": "`t2`" 992 } 993 ] /* refine_plan */ 994 }, 995 { 996 "sort_using_internal_table": { 997 "condition_for_sort": "(not(<in_optimizer>(<cache>(('v','i')),<exists>(/* select#2 */ select `t1`.`f2`,min(`t1`.`f2`) from `t1` having (((<cache>('v') = `t1`.`f2`) or isnull(`t1`.`f2`)) and ((<cache>('i') = min(`t1`.`f2`)) or isnull(min(`t1`.`f2`))) and <is_not_null_test>(`t1`.`f2`) and <is_not_null_test>(min(`t1`.`f2`)))))))", 998 "having_after_sort": null 999 } /* sort_using_internal_table */ 1000 } 1001 ] /* steps */ 1002 } /* join_optimization */ 1003 }, 1004 { 1005 "join_optimization": { 1006 "select#": 2, 1007 "steps": [ 1008 { 1009 "condition_processing": { 1010 "condition": "HAVING", 1011 "original_condition": "(((<cache>('v') = `t1`.`f2`) or isnull(`t1`.`f2`)) and ((<cache>('i') = min(`t1`.`f2`)) or isnull(min(`t1`.`f2`))) and <is_not_null_test>(`t1`.`f2`) and <is_not_null_test>(min(`t1`.`f2`)))", 1012 "steps": [ 1013 { 1014 "transformation": "constant_propagation", 1015 "resulting_condition": "(((<cache>('v') = `t1`.`f2`) or isnull(`t1`.`f2`)) and ((<cache>('i') = min(`t1`.`f2`)) or isnull(min(`t1`.`f2`))) and <is_not_null_test>(`t1`.`f2`) and <is_not_null_test>(min(`t1`.`f2`)))" 1016 }, 1017 { 1018 "transformation": "trivial_condition_removal", 1019 "resulting_condition": "(((<cache>('v') = `t1`.`f2`) or isnull(`t1`.`f2`)) and ((<cache>('i') = min(`t1`.`f2`)) or isnull(min(`t1`.`f2`))) and <is_not_null_test>(`t1`.`f2`) and <is_not_null_test>(min(`t1`.`f2`)))" 1020 } 1021 ] /* steps */ 1022 } /* condition_processing */ 1023 }, 1024 { 1025 "table_dependencies": [ 1026 { 1027 "table": "`t1`", 1028 "row_may_be_null": false, 1029 "map_bit": 0, 1030 "depends_on_map_bits": [ 1031 ] /* depends_on_map_bits */ 1032 } 1033 ] /* table_dependencies */ 1034 }, 1035 { 1036 "rows_estimation": [ 1037 { 1038 "table": "`t1`", 1039 "table_scan": { 1040 "rows": 2, 1041 "cost": 2 1042 } /* table_scan */ 1043 } 1044 ] /* rows_estimation */ 1045 }, 1046 { 1047 "considered_execution_plans": [ 1048 { 1049 "plan_prefix": [ 1050 ] /* plan_prefix */, 1051 "table": "`t1`", 1052 "best_access_path": { 1053 "considered_access_paths": [ 1054 { 1055 "rows_to_scan": 2, 1056 "access_type": "scan", 1057 "resulting_rows": 2, 1058 "cost": 2.4098, 1059 "chosen": true 1060 } 1061 ] /* considered_access_paths */ 1062 } /* best_access_path */, 1063 "condition_filtering_pct": 100, 1064 "rows_for_plan": 2, 1065 "cost_for_plan": 2.4098, 1066 "chosen": true 1067 } 1068 ] /* considered_execution_plans */ 1069 }, 1070 { 1071 "transformation": { 1072 "select#": 2, 1073 "from": "IN (SELECT)", 1074 "to": "materialization", 1075 "has_nullable_expressions": true, 1076 "treat_UNKNOWN_as_FALSE": false, 1077 "possible": false, 1078 "cause": "cannot_handle_partial_matches" 1079 } /* transformation */ 1080 }, 1081 { 1082 "attaching_conditions_to_tables": { 1083 "original_condition": null, 1084 "attached_conditions_computation": [ 1085 ] /* attached_conditions_computation */, 1086 "attached_conditions_summary": [ 1087 { 1088 "table": "`t1`", 1089 "attached": null 1090 } 1091 ] /* attached_conditions_summary */ 1092 } /* attaching_conditions_to_tables */ 1093 }, 1094 { 1095 "refine_plan": [ 1096 { 1097 "table": "`t1`" 1098 } 1099 ] /* refine_plan */ 1100 } 1101 ] /* steps */ 1102 } /* join_optimization */ 1103 }, 1104 { 1105 "join_execution": { 1106 "select#": 1, 1107 "steps": [ 1108 { 1109 "creating_tmp_table": { 1110 "tmp_table_info": { 1111 "table": "intermediate_tmp_table", 1112 "row_length": 3, 1113 "key_length": 0, 1114 "unique_constraint": false, 1115 "location": "memory (heap)", 1116 "row_limit_estimate": 349525 1117 } /* tmp_table_info */ 1118 } /* creating_tmp_table */ 1119 }, 1120 { 1121 "filesort_information": [ 1122 { 1123 "direction": "asc", 1124 "table": "intermediate_tmp_table", 1125 "field": "f2" 1126 } 1127 ] /* filesort_information */, 1128 "filesort_priority_queue_optimization": { 1129 "usable": false, 1130 "cause": "not applicable (no LIMIT)" 1131 } /* filesort_priority_queue_optimization */, 1132 "filesort_execution": [ 1133 { 1134 "subselect_execution": { 1135 "select#": 2, 1136 "steps": [ 1137 { 1138 "join_execution": { 1139 "select#": 2, 1140 "steps": [ 1141 ] /* steps */ 1142 } /* join_execution */ 1143 } 1144 ] /* steps */ 1145 } /* subselect_execution */ 1146 }, 1147 { 1148 "subselect_execution": { 1149 "select#": 2, 1150 "steps": [ 1151 ] /* steps */ 1152 } /* subselect_execution */ 1153 }, 1154 { 1155 "subselect_execution": { 1156 "select#": 2, 1157 "steps": [ 1158 ] /* steps */ 1159 } /* subselect_execution */ 1160 }, 1161 { 1162 "subselect_execution": { 1163 "select#": 2, 1164 "steps": [ 1165 ] /* steps */ 1166 } /* subselect_execution */ 1167 } 1168 ] /* filesort_execution */, 1169 "filesort_summary": { 1170 "rows": 4, 1171 "examined_rows": 4, 1172 "number_of_tmp_files": 0, 1173 "sort_buffer_size": "NNN", 1174 "sort_mode": "<sort_key, rowid>" 1175 } /* filesort_summary */ 1176 } 1177 ] /* steps */ 1178 } /* join_execution */ 1179 } 1180 ] /* steps */ 1181} 1182DROP TABLES t1,t2,t3; 1183 1184# Test that tracing a query with a materialized FROM-clause 1185# derived table using a GROUP BY, does not crash 1186 1187create table t1 (a int, b int); 1188insert into t1 values (1,1), (2,null), (3, 4); 1189select max(x) from (select sum(a) as x from t1 group by b) as teeone; 1190max(x) 11913 1192select TRACE from information_schema.OPTIMIZER_TRACE; 1193TRACE 1194{ 1195 "steps": [ 1196 { 1197 "join_preparation": { 1198 "select#": 1, 1199 "steps": [ 1200 { 1201 "join_preparation": { 1202 "select#": 2, 1203 "steps": [ 1204 { 1205 "expanded_query": "/* select#2 */ select sum(`t1`.`a`) AS `x` from `t1` group by `t1`.`b`" 1206 } 1207 ] /* steps */ 1208 } /* join_preparation */ 1209 }, 1210 { 1211 "derived": { 1212 "table": " `teeone`", 1213 "select#": 2, 1214 "materialized": true 1215 } /* derived */ 1216 }, 1217 { 1218 "expanded_query": "/* select#1 */ select max(`teeone`.`x`) AS `max(x)` from (/* select#2 */ select sum(`t1`.`a`) AS `x` from `t1` group by `t1`.`b`) `teeone`" 1219 } 1220 ] /* steps */ 1221 } /* join_preparation */ 1222 }, 1223 { 1224 "join_optimization": { 1225 "select#": 1, 1226 "steps": [ 1227 { 1228 "join_optimization": { 1229 "select#": 2, 1230 "steps": [ 1231 { 1232 "substitute_generated_columns": { 1233 } /* substitute_generated_columns */ 1234 }, 1235 { 1236 "table_dependencies": [ 1237 { 1238 "table": "`t1`", 1239 "row_may_be_null": false, 1240 "map_bit": 0, 1241 "depends_on_map_bits": [ 1242 ] /* depends_on_map_bits */ 1243 } 1244 ] /* table_dependencies */ 1245 }, 1246 { 1247 "rows_estimation": [ 1248 { 1249 "table": "`t1`", 1250 "table_scan": { 1251 "rows": 3, 1252 "cost": 2 1253 } /* table_scan */ 1254 } 1255 ] /* rows_estimation */ 1256 }, 1257 { 1258 "considered_execution_plans": [ 1259 { 1260 "plan_prefix": [ 1261 ] /* plan_prefix */, 1262 "table": "`t1`", 1263 "best_access_path": { 1264 "considered_access_paths": [ 1265 { 1266 "rows_to_scan": 3, 1267 "access_type": "scan", 1268 "resulting_rows": 3, 1269 "cost": 2.6066, 1270 "chosen": true, 1271 "use_tmp_table": true 1272 } 1273 ] /* considered_access_paths */ 1274 } /* best_access_path */, 1275 "condition_filtering_pct": 100, 1276 "rows_for_plan": 3, 1277 "cost_for_plan": 2.6066, 1278 "sort_cost": 3, 1279 "new_cost_for_plan": 5.6066, 1280 "chosen": true 1281 } 1282 ] /* considered_execution_plans */ 1283 }, 1284 { 1285 "attaching_conditions_to_tables": { 1286 "original_condition": null, 1287 "attached_conditions_computation": [ 1288 ] /* attached_conditions_computation */, 1289 "attached_conditions_summary": [ 1290 { 1291 "table": "`t1`", 1292 "attached": null 1293 } 1294 ] /* attached_conditions_summary */ 1295 } /* attaching_conditions_to_tables */ 1296 }, 1297 { 1298 "clause_processing": { 1299 "clause": "GROUP BY", 1300 "original_clause": "`t1`.`b`", 1301 "items": [ 1302 { 1303 "item": "`t1`.`b`" 1304 } 1305 ] /* items */, 1306 "resulting_clause_is_simple": true, 1307 "resulting_clause": "`t1`.`b`" 1308 } /* clause_processing */ 1309 }, 1310 { 1311 "refine_plan": [ 1312 { 1313 "table": "`t1`" 1314 } 1315 ] /* refine_plan */ 1316 } 1317 ] /* steps */ 1318 } /* join_optimization */ 1319 }, 1320 { 1321 "table_dependencies": [ 1322 { 1323 "table": " `teeone`", 1324 "row_may_be_null": false, 1325 "map_bit": 0, 1326 "depends_on_map_bits": [ 1327 ] /* depends_on_map_bits */ 1328 } 1329 ] /* table_dependencies */ 1330 }, 1331 { 1332 "rows_estimation": [ 1333 { 1334 "table": " `teeone`", 1335 "table_scan": { 1336 "rows": 3, 1337 "cost": 10 1338 } /* table_scan */ 1339 } 1340 ] /* rows_estimation */ 1341 }, 1342 { 1343 "considered_execution_plans": [ 1344 { 1345 "plan_prefix": [ 1346 ] /* plan_prefix */, 1347 "table": " `teeone`", 1348 "best_access_path": { 1349 "considered_access_paths": [ 1350 { 1351 "rows_to_scan": 3, 1352 "access_type": "scan", 1353 "resulting_rows": 3, 1354 "cost": 10.75, 1355 "chosen": true 1356 } 1357 ] /* considered_access_paths */ 1358 } /* best_access_path */, 1359 "condition_filtering_pct": 100, 1360 "rows_for_plan": 3, 1361 "cost_for_plan": 10.75, 1362 "chosen": true 1363 } 1364 ] /* considered_execution_plans */ 1365 }, 1366 { 1367 "attaching_conditions_to_tables": { 1368 "original_condition": null, 1369 "attached_conditions_computation": [ 1370 ] /* attached_conditions_computation */, 1371 "attached_conditions_summary": [ 1372 { 1373 "table": " `teeone`", 1374 "attached": null 1375 } 1376 ] /* attached_conditions_summary */ 1377 } /* attaching_conditions_to_tables */ 1378 }, 1379 { 1380 "refine_plan": [ 1381 { 1382 "table": " `teeone`" 1383 } 1384 ] /* refine_plan */ 1385 } 1386 ] /* steps */ 1387 } /* join_optimization */ 1388 }, 1389 { 1390 "join_execution": { 1391 "select#": 1, 1392 "steps": [ 1393 { 1394 "creating_tmp_table": { 1395 "tmp_table_info": { 1396 "table": " `teeone`", 1397 "row_length": 16, 1398 "key_length": 0, 1399 "unique_constraint": false, 1400 "location": "memory (heap)", 1401 "row_limit_estimate": 65536 1402 } /* tmp_table_info */ 1403 } /* creating_tmp_table */ 1404 }, 1405 { 1406 "join_execution": { 1407 "select#": 2, 1408 "steps": [ 1409 { 1410 "creating_tmp_table": { 1411 "tmp_table_info": { 1412 "table": "intermediate_tmp_table", 1413 "row_length": 22, 1414 "key_length": 5, 1415 "unique_constraint": false, 1416 "location": "memory (heap)", 1417 "row_limit_estimate": 47662 1418 } /* tmp_table_info */ 1419 } /* creating_tmp_table */ 1420 }, 1421 { 1422 "filesort_information": [ 1423 { 1424 "direction": "asc", 1425 "table": "intermediate_tmp_table", 1426 "field": "b" 1427 } 1428 ] /* filesort_information */, 1429 "filesort_priority_queue_optimization": { 1430 "usable": false, 1431 "cause": "not applicable (no LIMIT)" 1432 } /* filesort_priority_queue_optimization */, 1433 "filesort_execution": [ 1434 ] /* filesort_execution */, 1435 "filesort_summary": { 1436 "rows": 3, 1437 "examined_rows": 3, 1438 "number_of_tmp_files": 0, 1439 "sort_buffer_size": "NNN", 1440 "sort_mode": "<sort_key, rowid>" 1441 } /* filesort_summary */ 1442 } 1443 ] /* steps */ 1444 } /* join_execution */ 1445 } 1446 ] /* steps */ 1447 } /* join_execution */ 1448 } 1449 ] /* steps */ 1450} 1451drop table t1; 1452 1453# To have no crash above, we had to restore the ref_array at 1454# end of JOIN::exec(). This impacts how the query looks like, 1455# but not too much, as seen in the error message below. 1456# Comes from func_gconcat.test. 1457 1458CREATE TABLE t1(f1 int); 1459INSERT INTO t1 values (0),(0); 1460set optimizer_trace="enabled=off"; 1461SELECT POLYGON((SELECT 1 FROM (SELECT 1 IN (GROUP_CONCAT(t1.f1)) FROM t1, t1 t GROUP BY t.f1 ) d)); 1462ERROR 22007: Illegal non geometric '(select 1 from (select (1 = group_concat(`test`.`t1`.`f1` separator ',')) AS `1 IN (GROUP_CONCAT(t1.f1))` from `test`.`t1` join `test`.`t1` `t` group by `test`.`t`.`f1`) `d`)' value found during parsing 1463set optimizer_trace="enabled=on"; 1464SELECT POLYGON((SELECT 1 FROM (SELECT 1 IN (GROUP_CONCAT(t1.f1)) FROM t1, t1 t GROUP BY t.f1 ) d)); 1465ERROR 22007: Illegal non geometric '(select 1 from (select (1 = group_concat(`test`.`t1`.`f1` separator ',')) AS `1 IN (GROUP_CONCAT(t1.f1))` from `test`.`t1` join `test`.`t1` `t` group by `test`.`t`.`f1`) `d`)' value found during parsing 1466DROP TABLE t1; 1467 1468# Check that SQL PREPARE and SQL EXECUTE each produce one trace. 1469 1470set optimizer_trace_offset=0, optimizer_trace_limit=100; 1471prepare stmt from "select 1"; 1472select * from information_schema.OPTIMIZER_TRACE; 1473QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES 1474select 1 { 1475 "steps": [ 1476 { 1477 "join_preparation": { 1478 "select#": 1, 1479 "steps": [ 1480 { 1481 "expanded_query": "/* select#1 */ select 1 AS `1`" 1482 } 1483 ] /* steps */ 1484 } /* join_preparation */ 1485 } 1486 ] /* steps */ 1487} 0 0 1488set optimizer_trace_offset=0, optimizer_trace_limit=100; 1489execute stmt; 14901 14911 1492select * from information_schema.OPTIMIZER_TRACE; 1493QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES 1494select 1 { 1495 "steps": [ 1496 { 1497 "join_preparation": { 1498 "select#": 1, 1499 "steps": [ 1500 { 1501 "expanded_query": "/* select#1 */ select 1 AS `1`" 1502 } 1503 ] /* steps */ 1504 } /* join_preparation */ 1505 }, 1506 { 1507 "join_optimization": { 1508 "select#": 1, 1509 "steps": [ 1510 ] /* steps */ 1511 } /* join_optimization */ 1512 }, 1513 { 1514 "join_execution": { 1515 "select#": 1, 1516 "steps": [ 1517 ] /* steps */ 1518 } /* join_execution */ 1519 } 1520 ] /* steps */ 1521} 0 0 1522deallocate prepare stmt; 1523set optimizer_trace_offset=default, optimizer_trace_limit=default; 1524 1525# Test of SELECTs in IF in stored routine. 1526# Same test for CASE WHEN. 1527 1528create table t1 (a int); 1529create procedure p1() 1530begin 1531if exists(select 1) then 1532insert into t1 values(1); 1533end if; 1534if exists(select 2) then 1535insert into t1 values(2); 1536end if; 1537if (select count(*) from t1) then 1538insert into t1 values(3); 1539end if; 1540set @a=(select count(a) from t1 where a>0); 1541case (select count(a) from t1 where a>1) 1542when 2 then set @b=2; 1543else set @b=3; 1544end case; 1545end| 1546set optimizer_trace_offset=0, optimizer_trace_limit=100; 1547set @old_max=@@optimizer_trace_max_mem_size; 1548set optimizer_trace_max_mem_size=40000; 1549call p1(); 1550select * from information_schema.OPTIMIZER_TRACE; 1551QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES 1552set @old_max=@@optimizer_trace_max_mem_size { 1553 "steps": [ 1554 ] /* steps */ 1555} 0 0 1556set optimizer_trace_max_mem_size=40000 { 1557 "steps": [ 1558 ] /* steps */ 1559} 0 0 1560call p1() { 1561 "steps": [ 1562 ] /* steps */ 1563} 0 0 1564jump_if_not 2(2) exists(select 1) { 1565 "steps": [ 1566 { 1567 "join_preparation": { 1568 "select#": 2, 1569 "steps": [ 1570 { 1571 "expanded_query": "/* select#2 */ select 1" 1572 } 1573 ] /* steps */ 1574 } /* join_preparation */ 1575 }, 1576 { 1577 "subselect_execution": { 1578 "select#": 2, 1579 "steps": [ 1580 { 1581 "join_optimization": { 1582 "select#": 2, 1583 "steps": [ 1584 ] /* steps */ 1585 } /* join_optimization */ 1586 }, 1587 { 1588 "join_execution": { 1589 "select#": 2, 1590 "steps": [ 1591 ] /* steps */ 1592 } /* join_execution */ 1593 } 1594 ] /* steps */ 1595 } /* subselect_execution */ 1596 } 1597 ] /* steps */ 1598} 0 0 1599insert into t1 values(1) { 1600 "steps": [ 1601 ] /* steps */ 1602} 0 0 1603jump_if_not 4(4) exists(select 2) { 1604 "steps": [ 1605 { 1606 "join_preparation": { 1607 "select#": 2, 1608 "steps": [ 1609 { 1610 "expanded_query": "/* select#2 */ select 2" 1611 } 1612 ] /* steps */ 1613 } /* join_preparation */ 1614 }, 1615 { 1616 "subselect_execution": { 1617 "select#": 2, 1618 "steps": [ 1619 { 1620 "join_optimization": { 1621 "select#": 2, 1622 "steps": [ 1623 ] /* steps */ 1624 } /* join_optimization */ 1625 }, 1626 { 1627 "join_execution": { 1628 "select#": 2, 1629 "steps": [ 1630 ] /* steps */ 1631 } /* join_execution */ 1632 } 1633 ] /* steps */ 1634 } /* subselect_execution */ 1635 } 1636 ] /* steps */ 1637} 0 0 1638insert into t1 values(2) { 1639 "steps": [ 1640 ] /* steps */ 1641} 0 0 1642jump_if_not 6(6) (select count(0) from `test`.`t1`) { 1643 "steps": [ 1644 { 1645 "join_preparation": { 1646 "select#": 2, 1647 "steps": [ 1648 { 1649 "expanded_query": "/* select#2 */ select count(0) from `t1`" 1650 } 1651 ] /* steps */ 1652 } /* join_preparation */ 1653 }, 1654 { 1655 "subselect_execution": { 1656 "select#": 2, 1657 "steps": [ 1658 { 1659 "join_optimization": { 1660 "select#": 2, 1661 "steps": [ 1662 ] /* steps */, 1663 "empty_result": { 1664 "cause": "Select tables optimized away" 1665 } /* empty_result */ 1666 } /* join_optimization */ 1667 }, 1668 { 1669 "join_execution": { 1670 "select#": 2, 1671 "steps": [ 1672 ] /* steps */ 1673 } /* join_execution */ 1674 } 1675 ] /* steps */ 1676 } /* subselect_execution */ 1677 } 1678 ] /* steps */ 1679} 0 0 1680insert into t1 values(3) { 1681 "steps": [ 1682 ] /* steps */ 1683} 0 0 1684SET @a=(select count(a) from t1 where a>0) { 1685 "steps": [ 1686 { 1687 "join_preparation": { 1688 "select#": 2, 1689 "steps": [ 1690 { 1691 "expanded_query": "/* select#2 */ select count(`t1`.`a`) from `t1` where (`t1`.`a` > 0)" 1692 } 1693 ] /* steps */ 1694 } /* join_preparation */ 1695 }, 1696 { 1697 "subselect_execution": { 1698 "select#": 2, 1699 "steps": [ 1700 { 1701 "join_optimization": { 1702 "select#": 2, 1703 "steps": [ 1704 { 1705 "condition_processing": { 1706 "condition": "WHERE", 1707 "original_condition": "(`t1`.`a` > 0)", 1708 "steps": [ 1709 { 1710 "transformation": "equality_propagation", 1711 "resulting_condition": "(`t1`.`a` > 0)" 1712 }, 1713 { 1714 "transformation": "constant_propagation", 1715 "resulting_condition": "(`t1`.`a` > 0)" 1716 }, 1717 { 1718 "transformation": "trivial_condition_removal", 1719 "resulting_condition": "(`t1`.`a` > 0)" 1720 } 1721 ] /* steps */ 1722 } /* condition_processing */ 1723 }, 1724 { 1725 "substitute_generated_columns": { 1726 } /* substitute_generated_columns */ 1727 }, 1728 { 1729 "table_dependencies": [ 1730 { 1731 "table": "`t1`", 1732 "row_may_be_null": false, 1733 "map_bit": 0, 1734 "depends_on_map_bits": [ 1735 ] /* depends_on_map_bits */ 1736 } 1737 ] /* table_dependencies */ 1738 }, 1739 { 1740 "ref_optimizer_key_uses": [ 1741 ] /* ref_optimizer_key_uses */ 1742 }, 1743 { 1744 "rows_estimation": [ 1745 { 1746 "table": "`t1`", 1747 "table_scan": { 1748 "rows": 3, 1749 "cost": 2 1750 } /* table_scan */ 1751 } 1752 ] /* rows_estimation */ 1753 }, 1754 { 1755 "considered_execution_plans": [ 1756 { 1757 "plan_prefix": [ 1758 ] /* plan_prefix */, 1759 "table": "`t1`", 1760 "best_access_path": { 1761 "considered_access_paths": [ 1762 { 1763 "rows_to_scan": 3, 1764 "access_type": "scan", 1765 "resulting_rows": 1, 1766 "cost": 2.6051, 1767 "chosen": true 1768 } 1769 ] /* considered_access_paths */ 1770 } /* best_access_path */, 1771 "condition_filtering_pct": 100, 1772 "rows_for_plan": 1, 1773 "cost_for_plan": 2.6051, 1774 "chosen": true 1775 } 1776 ] /* considered_execution_plans */ 1777 }, 1778 { 1779 "attaching_conditions_to_tables": { 1780 "original_condition": "(`t1`.`a` > 0)", 1781 "attached_conditions_computation": [ 1782 ] /* attached_conditions_computation */, 1783 "attached_conditions_summary": [ 1784 { 1785 "table": "`t1`", 1786 "attached": "(`t1`.`a` > 0)" 1787 } 1788 ] /* attached_conditions_summary */ 1789 } /* attaching_conditions_to_tables */ 1790 }, 1791 { 1792 "refine_plan": [ 1793 { 1794 "table": "`t1`" 1795 } 1796 ] /* refine_plan */ 1797 } 1798 ] /* steps */ 1799 } /* join_optimization */ 1800 }, 1801 { 1802 "join_execution": { 1803 "select#": 2, 1804 "steps": [ 1805 ] /* steps */ 1806 } /* join_execution */ 1807 } 1808 ] /* steps */ 1809 } /* subselect_execution */ 1810 } 1811 ] /* steps */ 1812} 0 0 1813set_case_expr (15) 0 (select count(`a`) from `test`.`t1` where (`a` > 1)) { 1814 "steps": [ 1815 { 1816 "join_preparation": { 1817 "select#": 2, 1818 "steps": [ 1819 { 1820 "expanded_query": "/* select#2 */ select count(`t1`.`a`) from `t1` where (`t1`.`a` > 1)" 1821 } 1822 ] /* steps */ 1823 } /* join_preparation */ 1824 }, 1825 { 1826 "subselect_execution": { 1827 "select#": 2, 1828 "steps": [ 1829 { 1830 "join_optimization": { 1831 "select#": 2, 1832 "steps": [ 1833 { 1834 "condition_processing": { 1835 "condition": "WHERE", 1836 "original_condition": "(`t1`.`a` > 1)", 1837 "steps": [ 1838 { 1839 "transformation": "equality_propagation", 1840 "resulting_condition": "(`t1`.`a` > 1)" 1841 }, 1842 { 1843 "transformation": "constant_propagation", 1844 "resulting_condition": "(`t1`.`a` > 1)" 1845 }, 1846 { 1847 "transformation": "trivial_condition_removal", 1848 "resulting_condition": "(`t1`.`a` > 1)" 1849 } 1850 ] /* steps */ 1851 } /* condition_processing */ 1852 }, 1853 { 1854 "substitute_generated_columns": { 1855 } /* substitute_generated_columns */ 1856 }, 1857 { 1858 "table_dependencies": [ 1859 { 1860 "table": "`t1`", 1861 "row_may_be_null": false, 1862 "map_bit": 0, 1863 "depends_on_map_bits": [ 1864 ] /* depends_on_map_bits */ 1865 } 1866 ] /* table_dependencies */ 1867 }, 1868 { 1869 "ref_optimizer_key_uses": [ 1870 ] /* ref_optimizer_key_uses */ 1871 }, 1872 { 1873 "rows_estimation": [ 1874 { 1875 "table": "`t1`", 1876 "table_scan": { 1877 "rows": 3, 1878 "cost": 2 1879 } /* table_scan */ 1880 } 1881 ] /* rows_estimation */ 1882 }, 1883 { 1884 "considered_execution_plans": [ 1885 { 1886 "plan_prefix": [ 1887 ] /* plan_prefix */, 1888 "table": "`t1`", 1889 "best_access_path": { 1890 "considered_access_paths": [ 1891 { 1892 "rows_to_scan": 3, 1893 "access_type": "scan", 1894 "resulting_rows": 1, 1895 "cost": 2.6051, 1896 "chosen": true 1897 } 1898 ] /* considered_access_paths */ 1899 } /* best_access_path */, 1900 "condition_filtering_pct": 100, 1901 "rows_for_plan": 1, 1902 "cost_for_plan": 2.6051, 1903 "chosen": true 1904 } 1905 ] /* considered_execution_plans */ 1906 }, 1907 { 1908 "attaching_conditions_to_tables": { 1909 "original_condition": "(`t1`.`a` > 1)", 1910 "attached_conditions_computation": [ 1911 ] /* attached_conditions_computation */, 1912 "attached_conditions_summary": [ 1913 { 1914 "table": "`t1`", 1915 "attached": "(`t1`.`a` > 1)" 1916 } 1917 ] /* attached_conditions_summary */ 1918 } /* attaching_conditions_to_tables */ 1919 }, 1920 { 1921 "refine_plan": [ 1922 { 1923 "table": "`t1`" 1924 } 1925 ] /* refine_plan */ 1926 } 1927 ] /* steps */ 1928 } /* join_optimization */ 1929 }, 1930 { 1931 "join_execution": { 1932 "select#": 2, 1933 "steps": [ 1934 ] /* steps */ 1935 } /* join_execution */ 1936 } 1937 ] /* steps */ 1938 } /* subselect_execution */ 1939 } 1940 ] /* steps */ 1941} 0 0 1942jump_if_not_case_when 11(15) (case_expr@0 = 2) { 1943 "steps": [ 1944 ] /* steps */ 1945} 0 0 1946SET @b=2 { 1947 "steps": [ 1948 ] /* steps */ 1949} 0 0 1950select * from t1; 1951a 19521 19532 19543 1955select @a,@b; 1956@a @b 19573 2 1958set optimizer_trace_max_mem_size=@old_max; 1959drop procedure p1; 1960drop table t1; 1961 1962# Test of tracing of DO. 1963 1964set optimizer_trace_offset=0, optimizer_trace_limit=100; 1965do (select 42); 1966select * from information_schema.OPTIMIZER_TRACE; 1967QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES 1968do (select 42) { 1969 "steps": [ 1970 { 1971 "join_preparation": { 1972 "select#": 1, 1973 "steps": [ 1974 { 1975 "join_preparation": { 1976 "select#": 2, 1977 "steps": [ 1978 { 1979 "expanded_query": "/* select#2 */ select 42" 1980 } 1981 ] /* steps */ 1982 } /* join_preparation */ 1983 }, 1984 { 1985 "expanded_query": "/* select#1 */ select (/* select#2 */ select 42) AS `(select 42)`" 1986 } 1987 ] /* steps */ 1988 } /* join_preparation */ 1989 } 1990 ] /* steps */ 1991} 0 0 1992do (select 42) { 1993 "steps": [ 1994 { 1995 "join_preparation": { 1996 "select#": 1, 1997 "steps": [ 1998 { 1999 "join_preparation": { 2000 "select#": 2, 2001 "steps": [ 2002 { 2003 "expanded_query": "/* select#2 */ select 42" 2004 } 2005 ] /* steps */ 2006 } /* join_preparation */ 2007 }, 2008 { 2009 "expanded_query": "/* select#1 */ select (/* select#2 */ select 42) AS `(select 42)`" 2010 } 2011 ] /* steps */ 2012 } /* join_preparation */ 2013 }, 2014 { 2015 "join_optimization": { 2016 "select#": 1, 2017 "steps": [ 2018 ] /* steps */ 2019 } /* join_optimization */ 2020 }, 2021 { 2022 "join_optimization": { 2023 "select#": 2, 2024 "steps": [ 2025 ] /* steps */ 2026 } /* join_optimization */ 2027 }, 2028 { 2029 "join_execution": { 2030 "select#": 1, 2031 "steps": [ 2032 { 2033 "subselect_execution": { 2034 "select#": 2, 2035 "steps": [ 2036 { 2037 "join_execution": { 2038 "select#": 2, 2039 "steps": [ 2040 ] /* steps */ 2041 } /* join_execution */ 2042 } 2043 ] /* steps */ 2044 } /* subselect_execution */ 2045 } 2046 ] /* steps */ 2047 } /* join_execution */ 2048 } 2049 ] /* steps */ 2050} 0 0 2051 2052# Test of tracing of subquery used in parameter of routine call 2053 2054create table t1(a int); 2055insert into t1 values(1),(2); 2056create procedure p1(x int) 2057begin 2058declare b int; 2059set b=(select 2+x from dual); 2060end| 2061set optimizer_trace_offset=0, optimizer_trace_limit=100; 2062call p1((select a from t1 limit 1)); 2063select * from information_schema.OPTIMIZER_TRACE; 2064QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES 2065call p1((select a from t1 limit 1)) { 2066 "steps": [ 2067 { 2068 "join_preparation": { 2069 "select#": 2, 2070 "steps": [ 2071 { 2072 "expanded_query": "/* select#2 */ select `t1`.`a` from `t1` limit 1" 2073 } 2074 ] /* steps */ 2075 } /* join_preparation */ 2076 }, 2077 { 2078 "subselect_execution": { 2079 "select#": 2, 2080 "steps": [ 2081 { 2082 "join_optimization": { 2083 "select#": 2, 2084 "steps": [ 2085 { 2086 "table_dependencies": [ 2087 { 2088 "table": "`t1`", 2089 "row_may_be_null": false, 2090 "map_bit": 0, 2091 "depends_on_map_bits": [ 2092 ] /* depends_on_map_bits */ 2093 } 2094 ] /* table_dependencies */ 2095 }, 2096 { 2097 "rows_estimation": [ 2098 { 2099 "table": "`t1`", 2100 "table_scan": { 2101 "rows": 2, 2102 "cost": 2 2103 } /* table_scan */ 2104 } 2105 ] /* rows_estimation */ 2106 }, 2107 { 2108 "considered_execution_plans": [ 2109 { 2110 "plan_prefix": [ 2111 ] /* plan_prefix */, 2112 "table": "`t1`", 2113 "best_access_path": { 2114 "considered_access_paths": [ 2115 { 2116 "rows_to_scan": 2, 2117 "access_type": "scan", 2118 "resulting_rows": 2, 2119 "cost": 2.4034, 2120 "chosen": true 2121 } 2122 ] /* considered_access_paths */ 2123 } /* best_access_path */, 2124 "condition_filtering_pct": 100, 2125 "rows_for_plan": 2, 2126 "cost_for_plan": 2.4034, 2127 "chosen": true 2128 } 2129 ] /* considered_execution_plans */ 2130 }, 2131 { 2132 "attaching_conditions_to_tables": { 2133 "original_condition": null, 2134 "attached_conditions_computation": [ 2135 ] /* attached_conditions_computation */, 2136 "attached_conditions_summary": [ 2137 { 2138 "table": "`t1`", 2139 "attached": null 2140 } 2141 ] /* attached_conditions_summary */ 2142 } /* attaching_conditions_to_tables */ 2143 }, 2144 { 2145 "refine_plan": [ 2146 { 2147 "table": "`t1`" 2148 } 2149 ] /* refine_plan */ 2150 } 2151 ] /* steps */ 2152 } /* join_optimization */ 2153 }, 2154 { 2155 "join_execution": { 2156 "select#": 2, 2157 "steps": [ 2158 ] /* steps */ 2159 } /* join_execution */ 2160 } 2161 ] /* steps */ 2162 } /* subselect_execution */ 2163 } 2164 ] /* steps */ 2165} 0 0 2166set b@1 NULL { 2167 "steps": [ 2168 ] /* steps */ 2169} 0 0 2170set b@1 (select (2 + x@0)) { 2171 "steps": [ 2172 { 2173 "join_preparation": { 2174 "select#": 2, 2175 "steps": [ 2176 { 2177 "expanded_query": "/* select#2 */ select (2 + x@0)" 2178 } 2179 ] /* steps */ 2180 } /* join_preparation */ 2181 }, 2182 { 2183 "subselect_execution": { 2184 "select#": 2, 2185 "steps": [ 2186 { 2187 "join_optimization": { 2188 "select#": 2, 2189 "steps": [ 2190 ] /* steps */ 2191 } /* join_optimization */ 2192 }, 2193 { 2194 "join_execution": { 2195 "select#": 2, 2196 "steps": [ 2197 ] /* steps */ 2198 } /* join_execution */ 2199 } 2200 ] /* steps */ 2201 } /* subselect_execution */ 2202 } 2203 ] /* steps */ 2204} 0 0 2205drop procedure p1; 2206drop table t1; 2207set optimizer_trace_offset=default, optimizer_trace_limit=default; 2208 2209# Test that printing expanded query does not alter query's 2210# results. 2211# Comes from ctype_utf8mb4_heap.test 2212 2213create table t1 (f1 varchar(1) not null) default charset utf8mb4; 2214insert into t1 values (''), (''); 2215select concat(concat(_latin1'->',f1),_latin1'<-') from t1; 2216concat(concat(_latin1'->',f1),_latin1'<-') 2217-><- 2218-><- 2219select * from information_schema.optimizer_trace; 2220QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES 2221select concat(concat(_latin1'->',f1),_latin1'<-') from t1 { 2222 "steps": [ 2223 { 2224 "join_preparation": { 2225 "select#": 1, 2226 "steps": [ 2227 { 2228 "expanded_query": "/* select#1 */ select concat(concat('->',`t1`.`f1`),'<-') AS `concat(concat(_latin1'->',f1),_latin1'<-')` from `t1`" 2229 } 2230 ] /* steps */ 2231 } /* join_preparation */ 2232 }, 2233 { 2234 "join_optimization": { 2235 "select#": 1, 2236 "steps": [ 2237 { 2238 "table_dependencies": [ 2239 { 2240 "table": "`t1`", 2241 "row_may_be_null": false, 2242 "map_bit": 0, 2243 "depends_on_map_bits": [ 2244 ] /* depends_on_map_bits */ 2245 } 2246 ] /* table_dependencies */ 2247 }, 2248 { 2249 "rows_estimation": [ 2250 { 2251 "table": "`t1`", 2252 "table_scan": { 2253 "rows": 2, 2254 "cost": 2 2255 } /* table_scan */ 2256 } 2257 ] /* rows_estimation */ 2258 }, 2259 { 2260 "considered_execution_plans": [ 2261 { 2262 "plan_prefix": [ 2263 ] /* plan_prefix */, 2264 "table": "`t1`", 2265 "best_access_path": { 2266 "considered_access_paths": [ 2267 { 2268 "rows_to_scan": 2, 2269 "access_type": "scan", 2270 "resulting_rows": 2, 2271 "cost": 2.4098, 2272 "chosen": true 2273 } 2274 ] /* considered_access_paths */ 2275 } /* best_access_path */, 2276 "condition_filtering_pct": 100, 2277 "rows_for_plan": 2, 2278 "cost_for_plan": 2.4098, 2279 "chosen": true 2280 } 2281 ] /* considered_execution_plans */ 2282 }, 2283 { 2284 "attaching_conditions_to_tables": { 2285 "original_condition": null, 2286 "attached_conditions_computation": [ 2287 ] /* attached_conditions_computation */, 2288 "attached_conditions_summary": [ 2289 { 2290 "table": "`t1`", 2291 "attached": null 2292 } 2293 ] /* attached_conditions_summary */ 2294 } /* attaching_conditions_to_tables */ 2295 }, 2296 { 2297 "refine_plan": [ 2298 { 2299 "table": "`t1`" 2300 } 2301 ] /* refine_plan */ 2302 } 2303 ] /* steps */ 2304 } /* join_optimization */ 2305 }, 2306 { 2307 "join_execution": { 2308 "select#": 1, 2309 "steps": [ 2310 ] /* steps */ 2311 } /* join_execution */ 2312 } 2313 ] /* steps */ 2314} 0 0 2315drop table t1; 2316 2317# Bug#12546331 - SEGFAULT IN SUBSELECT_INDEXSUBQUERY_ENGINE::PRINT WITH OPTIMIZER TRACE 2318 2319CREATE TABLE t1 ( 2320col_int_nokey INT, 2321col_int_key INT, 2322col_varchar_key varchar(1), 2323KEY col_int_key (col_int_key), 2324KEY col_varchar_key (col_varchar_key,col_int_key) 2325); 2326INSERT INTO t1 VALUES 2327(NULL,8,'x'), 2328(8,7,'d'), 2329(1,1,'r'), 2330(9,7,'f'), 2331(4,9,'y'), 2332(3,NULL,'u'), 2333(2,1,'m'), 2334(NULL,9,NULL), 2335(2,2,'o'), 2336(NULL,9,'w'), 2337(6,2,'m'), 2338(7,4,'q'), 2339(2,0,NULL), 2340(5,4,'d'), 2341(7,8,'g'), 2342(6,NULL,'x'), 2343(6,NULL,'f'), 2344(2,0,'p'), 2345(9,NULL,'j'), 2346(6,8,'c') 2347; 2348CREATE TABLE t2 ( 2349col_int_nokey INT, 2350col_int_key INT, 2351col_varchar_key varchar(1), 2352KEY col_int_key (col_int_key), 2353KEY col_varchar_key (col_varchar_key,col_int_key) 2354); 2355INSERT INTO t2 VALUES 2356(2,4,'v'), 2357(150,62,'v'), 2358(NULL,7,'c'), 2359(2,1,NULL), 2360(5,0,'x'), 2361(3,7,'i'), 2362(1,7,'e'), 2363(4,1,'p'), 2364(NULL,7,'s'), 2365(2,1,'j'), 2366(6,5,'z'), 2367(6,2,'c'), 2368(8,0,'a'), 2369(2,1,'q'), 2370(6,8,'y'), 2371(8,1,NULL), 2372(3,1,'r'), 2373(3,9,'v'), 2374(9,1,NULL), 2375(6,5,'r') 2376; 2377SELECT col_int_nokey 2378FROM ( 2379SELECT * 2380FROM t2 2381WHERE col_varchar_key > 'a' 2382 OR ( 7 , 5 ) NOT IN ( 2383SELECT col_int_nokey , col_int_key 2384FROM t1 ) 2385) AS alias1; 2386col_int_nokey 23872 2388150 2389NULL 23902 23915 23923 23931 23944 2395NULL 23962 23976 23986 23998 24002 24016 24028 24033 24043 24059 24066 2407DROP TABLE t1; 2408DROP TABLE t2; 2409 2410BUG#12552262 - INVALID JSON WITH TWO CALLS TO TEST_QUICK_SELECT 2411 2412CREATE TABLE t1 ( 2413col_varchar_10_latin1_key varchar(10) DEFAULT NULL, 2414col_int_key INT, 2415KEY col_int_key (col_int_key) 2416); 2417CREATE TABLE t2 ( 2418col_varchar_10_latin1_key varchar(10) DEFAULT NULL, 2419col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 DEFAULT NULL, 2420col_int_key INT, 2421KEY col_varchar_10_utf8_key (col_varchar_10_utf8_key), 2422KEY col_int_key (col_int_key) 2423); 2424INSERT INTO t2 VALUES ('qykbaqfyhz','l',NULL); 2425CREATE TABLE t3 ( 2426col_int_key INT, 2427col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 DEFAULT NULL, 2428col_varchar_10_latin1_key varchar(10) DEFAULT NULL, 2429KEY col_varchar_10_utf8_key (col_varchar_10_utf8_key), 2430KEY col_varchar_10_latin1_key (col_varchar_10_latin1_key) 2431); 2432INSERT INTO t3 VALUES (0,'s','it'); 2433INSERT INTO t3 VALUES (9,'IQTHK','JCAQM'); 2434SELECT table2.col_int_key 2435FROM t3 AS table1 2436LEFT JOIN t1 AS table2 ON table1.col_int_key < table2.col_int_key 2437LEFT JOIN t2 AS table3 ON table2.col_varchar_10_latin1_key >= 2438table3.col_varchar_10_utf8_key 2439; 2440col_int_key 2441NULL 2442NULL 2443select * from information_schema.optimizer_trace; 2444QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES 2445SELECT table2.col_int_key 2446FROM t3 AS table1 2447LEFT JOIN t1 AS table2 ON table1.col_int_key < table2.col_int_key 2448LEFT JOIN t2 AS table3 ON table2.col_varchar_10_latin1_key >= 2449table3.col_varchar_10_utf8_key { 2450 "steps": [ 2451 { 2452 "join_preparation": { 2453 "select#": 1, 2454 "steps": [ 2455 { 2456 "expanded_query": "/* select#1 */ select `table2`.`col_int_key` AS `col_int_key` from `t3` `table1` left join `t1` `table2` on((`table1`.`col_int_key` < `table2`.`col_int_key`)) left join `t2` `table3` on((convert(`table2`.`col_varchar_10_latin1_key` using utf8) >= `table3`.`col_varchar_10_utf8_key`))" 2457 } 2458 ] /* steps */ 2459 } /* join_preparation */ 2460 }, 2461 { 2462 "join_optimization": { 2463 "select#": 1, 2464 "steps": [ 2465 { 2466 "condition_processing": { 2467 "condition": "WHERE", 2468 "original_condition": null, 2469 "steps": [ 2470 { 2471 "transformation": "equality_propagation", 2472 "resulting_condition": null 2473 } 2474 ] /* steps */ 2475 } /* condition_processing */ 2476 }, 2477 { 2478 "table_dependencies": [ 2479 { 2480 "table": "`t3` `table1`", 2481 "row_may_be_null": false, 2482 "map_bit": 0, 2483 "depends_on_map_bits": [ 2484 ] /* depends_on_map_bits */ 2485 }, 2486 { 2487 "table": "`t1` `table2`", 2488 "row_may_be_null": true, 2489 "map_bit": 1, 2490 "depends_on_map_bits": [ 2491 0 2492 ] /* depends_on_map_bits */ 2493 }, 2494 { 2495 "table": "`t2` `table3`", 2496 "row_may_be_null": true, 2497 "map_bit": 2, 2498 "depends_on_map_bits": [ 2499 0, 2500 1 2501 ] /* depends_on_map_bits */ 2502 } 2503 ] /* table_dependencies */ 2504 }, 2505 { 2506 "ref_optimizer_key_uses": [ 2507 ] /* ref_optimizer_key_uses */ 2508 }, 2509 { 2510 "rows_estimation": [ 2511 { 2512 "table": "`t3` `table1`", 2513 "table_scan": { 2514 "rows": 2, 2515 "cost": 2 2516 } /* table_scan */ 2517 }, 2518 { 2519 "table": "`t1` `table2`", 2520 "rows": 1, 2521 "cost": 1, 2522 "table_type": "system", 2523 "empty": true 2524 }, 2525 { 2526 "table": "`t2` `table3`", 2527 "table_scan": { 2528 "rows": 1, 2529 "cost": 2 2530 } /* table_scan */ 2531 } 2532 ] /* rows_estimation */ 2533 }, 2534 { 2535 "considered_execution_plans": [ 2536 { 2537 "plan_prefix": [ 2538 "`t1` `table2`" 2539 ] /* plan_prefix */, 2540 "table": "`t3` `table1`", 2541 "best_access_path": { 2542 "considered_access_paths": [ 2543 { 2544 "rows_to_scan": 2, 2545 "access_type": "scan", 2546 "resulting_rows": 2, 2547 "cost": 2.4107, 2548 "chosen": true 2549 } 2550 ] /* considered_access_paths */ 2551 } /* best_access_path */, 2552 "condition_filtering_pct": 100, 2553 "rows_for_plan": 2, 2554 "cost_for_plan": 2.4107, 2555 "rest_of_plan": [ 2556 { 2557 "plan_prefix": [ 2558 "`t1` `table2`", 2559 "`t3` `table1`" 2560 ] /* plan_prefix */, 2561 "table": "`t2` `table3`", 2562 "best_access_path": { 2563 "considered_access_paths": [ 2564 { 2565 "rows_to_scan": 1, 2566 "access_type": "scan", 2567 "using_join_cache": true, 2568 "buffers_needed": 1, 2569 "resulting_rows": 1, 2570 "cost": 2.405, 2571 "chosen": true 2572 } 2573 ] /* considered_access_paths */ 2574 } /* best_access_path */, 2575 "condition_filtering_pct": 100, 2576 "rows_for_plan": 2, 2577 "cost_for_plan": 4.8157, 2578 "chosen": true 2579 } 2580 ] /* rest_of_plan */ 2581 } 2582 ] /* considered_execution_plans */ 2583 }, 2584 { 2585 "condition_on_constant_tables": "1", 2586 "condition_value": true 2587 }, 2588 { 2589 "attaching_conditions_to_tables": { 2590 "original_condition": "1", 2591 "attached_conditions_computation": [ 2592 { 2593 "table": "`t2` `table3`", 2594 "rechecking_index_usage": { 2595 "recheck_reason": "not_first_table", 2596 "range_analysis": { 2597 "table_scan": { 2598 "rows": 1, 2599 "cost": 4.3049 2600 } /* table_scan */, 2601 "potential_range_indexes": [ 2602 { 2603 "index": "col_varchar_10_utf8_key", 2604 "usable": true, 2605 "key_parts": [ 2606 "col_varchar_10_utf8_key" 2607 ] /* key_parts */ 2608 }, 2609 { 2610 "index": "col_int_key", 2611 "usable": false, 2612 "cause": "not_applicable" 2613 } 2614 ] /* potential_range_indexes */, 2615 "best_covering_index_scan": { 2616 "index": "col_varchar_10_utf8_key", 2617 "cost": 1.2, 2618 "chosen": true 2619 } /* best_covering_index_scan */, 2620 "setup_range_conditions": [ 2621 { 2622 "impossible_condition": { 2623 "cause": "comparison_with_null_always_false" 2624 } /* impossible_condition */ 2625 } 2626 ] /* setup_range_conditions */, 2627 "impossible_range": true 2628 } /* range_analysis */, 2629 "without_ON_clause": { 2630 "range_analysis": { 2631 "table_scan": { 2632 "rows": 1, 2633 "cost": 4.3049 2634 } /* table_scan */, 2635 "potential_range_indexes": [ 2636 { 2637 "index": "col_varchar_10_utf8_key", 2638 "usable": true, 2639 "key_parts": [ 2640 "col_varchar_10_utf8_key" 2641 ] /* key_parts */ 2642 }, 2643 { 2644 "index": "col_int_key", 2645 "usable": false, 2646 "cause": "not_applicable" 2647 } 2648 ] /* potential_range_indexes */, 2649 "best_covering_index_scan": { 2650 "index": "col_varchar_10_utf8_key", 2651 "cost": 1.2, 2652 "chosen": true 2653 } /* best_covering_index_scan */, 2654 "group_index_range": { 2655 "chosen": false, 2656 "cause": "not_single_table" 2657 } /* group_index_range */ 2658 } /* range_analysis */ 2659 } /* without_ON_clause */ 2660 } /* rechecking_index_usage */ 2661 } 2662 ] /* attached_conditions_computation */, 2663 "attached_conditions_summary": [ 2664 { 2665 "table": "`t3` `table1`", 2666 "attached": null 2667 }, 2668 { 2669 "table": "`t2` `table3`", 2670 "attached": "<if>(is_not_null_compl(table3), (convert(NULL using utf8) >= `table3`.`col_varchar_10_utf8_key`), true)" 2671 } 2672 ] /* attached_conditions_summary */ 2673 } /* attaching_conditions_to_tables */ 2674 }, 2675 { 2676 "refine_plan": [ 2677 { 2678 "table": "`t3` `table1`" 2679 }, 2680 { 2681 "table": "`t2` `table3`" 2682 } 2683 ] /* refine_plan */ 2684 } 2685 ] /* steps */ 2686 } /* join_optimization */ 2687 }, 2688 { 2689 "join_execution": { 2690 "select#": 1, 2691 "steps": [ 2692 ] /* steps */ 2693 } /* join_execution */ 2694 } 2695 ] /* steps */ 2696} 0 0 2697DROP TABLE t1,t2,t3; 2698 2699Tests of tracing of the "eq_ref optimization" of plan search 2700 2701create table t0 (a int); 2702insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 2703create table t1 (a int, b int, key(a)); 2704create table t2 (a int, b int, key(a)); 2705create table t3 (a int, b int, key(a)); 2706insert into t1 select a,a from t0; 2707insert into t2 select a,a from t0; 2708insert into t3 select a,a from t0; 2709set @old_opt_switch=@@optimizer_switch; 2710explain select * 2711from t0 where a in 2712(select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a); 2713id select_type table partitions type possible_keys key key_len ref rows filtered Extra 27141 PRIMARY t0 NULL ALL NULL NULL NULL NULL 10 100.00 Using where 27152 DEPENDENT SUBQUERY t1 NULL index a a 5 NULL 10 100.00 Using where; Using index 27162 DEPENDENT SUBQUERY t2 NULL ref a a 5 test.t1.a 1 100.00 Using index 27172 DEPENDENT SUBQUERY t3 NULL ref a a 5 test.t1.a 1 100.00 Using index 2718Warnings: 2719Note 1003 /* select#1 */ select `test`.`t0`.`a` AS `a` from `test`.`t0` where <in_optimizer>(`test`.`t0`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t1` join `test`.`t2` join `test`.`t3` where ((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`) and (<cache>(`test`.`t0`.`a`) = (`test`.`t1`.`a` + `test`.`t1`.`a`))))) 2720select * from information_schema.optimizer_trace; 2721QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES 2722explain select * 2723from t0 where a in 2724(select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a) { 2725 "steps": [ 2726 { 2727 "join_preparation": { 2728 "select#": 1, 2729 "steps": [ 2730 { 2731 "join_preparation": { 2732 "select#": 2, 2733 "steps": [ 2734 { 2735 "expanded_query": "/* select#2 */ select (`t2`.`a` + `t3`.`a`) from (`t1` left join (`t2` join `t3`) on(((`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))))" 2736 }, 2737 { 2738 "transformation": { 2739 "select#": 2, 2740 "from": "IN (SELECT)", 2741 "to": "semijoin", 2742 "chosen": false 2743 } /* transformation */ 2744 }, 2745 { 2746 "transformation": { 2747 "select#": 2, 2748 "from": "IN (SELECT)", 2749 "to": "EXISTS (CORRELATED SELECT)", 2750 "chosen": true, 2751 "evaluating_constant_where_conditions": [ 2752 ] /* evaluating_constant_where_conditions */ 2753 } /* transformation */ 2754 } 2755 ] /* steps */ 2756 } /* join_preparation */ 2757 }, 2758 { 2759 "expanded_query": "/* select#1 */ select `t0`.`a` AS `a` from `t0` where <in_optimizer>(`t0`.`a`,<exists>(/* select#2 */ select (`t2`.`a` + `t3`.`a`) from (`t1` left join (`t2` join `t3`) on(((`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`)))) where (<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`))))" 2760 }, 2761 { 2762 "transformations_to_nested_joins": { 2763 "transformations": [ 2764 "outer_join_to_inner_join", 2765 "JOIN_condition_to_WHERE", 2766 "parenthesis_removal" 2767 ] /* transformations */, 2768 "expanded_query": "/* select#2 */ select (`t2`.`a` + `t3`.`a`) from `t1` join `t2` join `t3` where ((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and (`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))" 2769 } /* transformations_to_nested_joins */ 2770 } 2771 ] /* steps */ 2772 } /* join_preparation */ 2773 }, 2774 { 2775 "join_optimization": { 2776 "select#": 1, 2777 "steps": [ 2778 { 2779 "condition_processing": { 2780 "condition": "WHERE", 2781 "original_condition": "<in_optimizer>(`t0`.`a`,<exists>(/* select#2 */ select (`t2`.`a` + `t3`.`a`) from `t1` join `t2` join `t3` where ((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and (`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))))", 2782 "steps": [ 2783 { 2784 "transformation": "equality_propagation", 2785 "subselect_evaluation": [ 2786 ] /* subselect_evaluation */, 2787 "resulting_condition": "<in_optimizer>(`t0`.`a`,<exists>(/* select#2 */ select (`t2`.`a` + `t3`.`a`) from `t1` join `t2` join `t3` where ((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and (`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))))" 2788 }, 2789 { 2790 "transformation": "constant_propagation", 2791 "subselect_evaluation": [ 2792 ] /* subselect_evaluation */, 2793 "resulting_condition": "<in_optimizer>(`t0`.`a`,<exists>(/* select#2 */ select (`t2`.`a` + `t3`.`a`) from `t1` join `t2` join `t3` where ((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and (`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))))" 2794 }, 2795 { 2796 "transformation": "trivial_condition_removal", 2797 "subselect_evaluation": [ 2798 ] /* subselect_evaluation */, 2799 "resulting_condition": "<in_optimizer>(`t0`.`a`,<exists>(/* select#2 */ select (`t2`.`a` + `t3`.`a`) from `t1` join `t2` join `t3` where ((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and (`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))))" 2800 } 2801 ] /* steps */ 2802 } /* condition_processing */ 2803 }, 2804 { 2805 "substitute_generated_columns": { 2806 } /* substitute_generated_columns */ 2807 }, 2808 { 2809 "table_dependencies": [ 2810 { 2811 "table": "`t0`", 2812 "row_may_be_null": false, 2813 "map_bit": 0, 2814 "depends_on_map_bits": [ 2815 ] /* depends_on_map_bits */ 2816 } 2817 ] /* table_dependencies */ 2818 }, 2819 { 2820 "ref_optimizer_key_uses": [ 2821 ] /* ref_optimizer_key_uses */ 2822 }, 2823 { 2824 "rows_estimation": [ 2825 { 2826 "table": "`t0`", 2827 "table_scan": { 2828 "rows": 10, 2829 "cost": 2 2830 } /* table_scan */ 2831 } 2832 ] /* rows_estimation */ 2833 }, 2834 { 2835 "considered_execution_plans": [ 2836 { 2837 "plan_prefix": [ 2838 ] /* plan_prefix */, 2839 "table": "`t0`", 2840 "best_access_path": { 2841 "considered_access_paths": [ 2842 { 2843 "rows_to_scan": 10, 2844 "access_type": "scan", 2845 "resulting_rows": 10, 2846 "cost": 4.0171, 2847 "chosen": true 2848 } 2849 ] /* considered_access_paths */ 2850 } /* best_access_path */, 2851 "condition_filtering_pct": 100, 2852 "rows_for_plan": 10, 2853 "cost_for_plan": 4.0171, 2854 "chosen": true 2855 } 2856 ] /* considered_execution_plans */ 2857 }, 2858 { 2859 "attaching_conditions_to_tables": { 2860 "original_condition": "<in_optimizer>(`t0`.`a`,<exists>(/* select#2 */ select (`t2`.`a` + `t3`.`a`) from `t1` join `t2` join `t3` where ((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and (`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))))", 2861 "attached_conditions_computation": [ 2862 ] /* attached_conditions_computation */, 2863 "attached_conditions_summary": [ 2864 { 2865 "table": "`t0`", 2866 "attached": "<in_optimizer>(`t0`.`a`,<exists>(/* select#2 */ select (`t2`.`a` + `t3`.`a`) from `t1` join `t2` join `t3` where ((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and (`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))))" 2867 } 2868 ] /* attached_conditions_summary */ 2869 } /* attaching_conditions_to_tables */ 2870 }, 2871 { 2872 "refine_plan": [ 2873 { 2874 "table": "`t0`" 2875 } 2876 ] /* refine_plan */ 2877 } 2878 ] /* steps */ 2879 } /* join_optimization */ 2880 }, 2881 { 2882 "join_optimization": { 2883 "select#": 2, 2884 "steps": [ 2885 { 2886 "condition_processing": { 2887 "condition": "WHERE", 2888 "original_condition": "((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and (`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))", 2889 "steps": [ 2890 { 2891 "transformation": "equality_propagation", 2892 "resulting_condition": "((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and multiple equal(`t2`.`a`, `t1`.`a`, `t3`.`a`))" 2893 }, 2894 { 2895 "transformation": "constant_propagation", 2896 "resulting_condition": "((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and multiple equal(`t2`.`a`, `t1`.`a`, `t3`.`a`))" 2897 }, 2898 { 2899 "transformation": "trivial_condition_removal", 2900 "resulting_condition": "((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and multiple equal(`t2`.`a`, `t1`.`a`, `t3`.`a`))" 2901 } 2902 ] /* steps */ 2903 } /* condition_processing */ 2904 }, 2905 { 2906 "substitute_generated_columns": { 2907 } /* substitute_generated_columns */ 2908 }, 2909 { 2910 "table_dependencies": [ 2911 { 2912 "table": "`t1`", 2913 "row_may_be_null": false, 2914 "map_bit": 0, 2915 "depends_on_map_bits": [ 2916 ] /* depends_on_map_bits */ 2917 }, 2918 { 2919 "table": "`t2`", 2920 "row_may_be_null": true, 2921 "map_bit": 1, 2922 "depends_on_map_bits": [ 2923 ] /* depends_on_map_bits */ 2924 }, 2925 { 2926 "table": "`t3`", 2927 "row_may_be_null": true, 2928 "map_bit": 2, 2929 "depends_on_map_bits": [ 2930 ] /* depends_on_map_bits */ 2931 } 2932 ] /* table_dependencies */ 2933 }, 2934 { 2935 "ref_optimizer_key_uses": [ 2936 { 2937 "table": "`t1`", 2938 "field": "a", 2939 "equals": "`t2`.`a`", 2940 "null_rejecting": true 2941 }, 2942 { 2943 "table": "`t1`", 2944 "field": "a", 2945 "equals": "`t3`.`a`", 2946 "null_rejecting": true 2947 }, 2948 { 2949 "table": "`t2`", 2950 "field": "a", 2951 "equals": "`t1`.`a`", 2952 "null_rejecting": true 2953 }, 2954 { 2955 "table": "`t2`", 2956 "field": "a", 2957 "equals": "`t3`.`a`", 2958 "null_rejecting": true 2959 }, 2960 { 2961 "table": "`t3`", 2962 "field": "a", 2963 "equals": "`t2`.`a`", 2964 "null_rejecting": true 2965 }, 2966 { 2967 "table": "`t3`", 2968 "field": "a", 2969 "equals": "`t1`.`a`", 2970 "null_rejecting": true 2971 } 2972 ] /* ref_optimizer_key_uses */ 2973 }, 2974 { 2975 "rows_estimation": [ 2976 { 2977 "table": "`t1`", 2978 "table_scan": { 2979 "rows": 10, 2980 "cost": 2 2981 } /* table_scan */ 2982 }, 2983 { 2984 "table": "`t2`", 2985 "table_scan": { 2986 "rows": 10, 2987 "cost": 2 2988 } /* table_scan */ 2989 }, 2990 { 2991 "table": "`t3`", 2992 "table_scan": { 2993 "rows": 10, 2994 "cost": 2 2995 } /* table_scan */ 2996 } 2997 ] /* rows_estimation */ 2998 }, 2999 { 3000 "considered_execution_plans": [ 3001 { 3002 "plan_prefix": [ 3003 ] /* plan_prefix */, 3004 "table": "`t1`", 3005 "best_access_path": { 3006 "considered_access_paths": [ 3007 { 3008 "access_type": "ref", 3009 "index": "a", 3010 "usable": false, 3011 "chosen": false 3012 }, 3013 { 3014 "rows_to_scan": 10, 3015 "access_type": "scan", 3016 "resulting_rows": 10, 3017 "cost": 4.022, 3018 "chosen": true 3019 } 3020 ] /* considered_access_paths */ 3021 } /* best_access_path */, 3022 "condition_filtering_pct": 100, 3023 "rows_for_plan": 10, 3024 "cost_for_plan": 4.022, 3025 "rest_of_plan": [ 3026 { 3027 "plan_prefix": [ 3028 "`t1`" 3029 ] /* plan_prefix */, 3030 "table": "`t2`", 3031 "best_access_path": { 3032 "considered_access_paths": [ 3033 { 3034 "access_type": "ref", 3035 "index": "a", 3036 "rows": 1, 3037 "cost": 12, 3038 "chosen": true 3039 }, 3040 { 3041 "rows_to_scan": 10, 3042 "access_type": "scan", 3043 "using_join_cache": true, 3044 "buffers_needed": 1, 3045 "resulting_rows": 10, 3046 "cost": 22.022, 3047 "chosen": false 3048 } 3049 ] /* considered_access_paths */ 3050 } /* best_access_path */, 3051 "condition_filtering_pct": 100, 3052 "rows_for_plan": 10, 3053 "cost_for_plan": 16.022, 3054 "rest_of_plan": [ 3055 { 3056 "plan_prefix": [ 3057 "`t1`", 3058 "`t2`" 3059 ] /* plan_prefix */, 3060 "table": "`t3`", 3061 "best_access_path": { 3062 "considered_access_paths": [ 3063 { 3064 "access_type": "ref", 3065 "index": "a", 3066 "rows": 1, 3067 "cost": 12, 3068 "chosen": true 3069 }, 3070 { 3071 "rows_to_scan": 10, 3072 "access_type": "scan", 3073 "using_join_cache": true, 3074 "buffers_needed": 1, 3075 "resulting_rows": 10, 3076 "cost": 22.023, 3077 "chosen": false 3078 } 3079 ] /* considered_access_paths */ 3080 } /* best_access_path */, 3081 "added_to_eq_ref_extension": true, 3082 "condition_filtering_pct": 100, 3083 "rows_for_plan": 10, 3084 "cost_for_plan": 28.022, 3085 "chosen": true 3086 } 3087 ] /* rest_of_plan */ 3088 } 3089 ] /* rest_of_plan */ 3090 }, 3091 { 3092 "plan_prefix": [ 3093 ] /* plan_prefix */, 3094 "table": "`t2`", 3095 "best_access_path": { 3096 "considered_access_paths": [ 3097 { 3098 "access_type": "ref", 3099 "index": "a", 3100 "usable": false, 3101 "chosen": false 3102 }, 3103 { 3104 "rows_to_scan": 10, 3105 "access_type": "scan", 3106 "resulting_rows": 10, 3107 "cost": 4.022, 3108 "chosen": true 3109 } 3110 ] /* considered_access_paths */ 3111 } /* best_access_path */, 3112 "condition_filtering_pct": 100, 3113 "rows_for_plan": 10, 3114 "cost_for_plan": 4.022, 3115 "rest_of_plan": [ 3116 { 3117 "plan_prefix": [ 3118 "`t2`" 3119 ] /* plan_prefix */, 3120 "table": "`t1`", 3121 "best_access_path": { 3122 "considered_access_paths": [ 3123 { 3124 "access_type": "ref", 3125 "index": "a", 3126 "rows": 1, 3127 "cost": 12, 3128 "chosen": true 3129 }, 3130 { 3131 "rows_to_scan": 10, 3132 "access_type": "scan", 3133 "using_join_cache": true, 3134 "buffers_needed": 1, 3135 "resulting_rows": 10, 3136 "cost": 22.022, 3137 "chosen": false 3138 } 3139 ] /* considered_access_paths */ 3140 } /* best_access_path */, 3141 "condition_filtering_pct": 100, 3142 "rows_for_plan": 10, 3143 "cost_for_plan": 16.022, 3144 "rest_of_plan": [ 3145 { 3146 "plan_prefix": [ 3147 "`t2`", 3148 "`t1`" 3149 ] /* plan_prefix */, 3150 "table": "`t3`", 3151 "best_access_path": { 3152 "considered_access_paths": [ 3153 { 3154 "access_type": "ref", 3155 "index": "a", 3156 "rows": 1, 3157 "cost": 12, 3158 "chosen": true 3159 }, 3160 { 3161 "rows_to_scan": 10, 3162 "access_type": "scan", 3163 "using_join_cache": true, 3164 "buffers_needed": 1, 3165 "resulting_rows": 10, 3166 "cost": 22.023, 3167 "chosen": false 3168 } 3169 ] /* considered_access_paths */ 3170 } /* best_access_path */, 3171 "added_to_eq_ref_extension": true, 3172 "condition_filtering_pct": 100, 3173 "rows_for_plan": 10, 3174 "cost_for_plan": 28.022, 3175 "pruned_by_cost": true 3176 }, 3177 { 3178 "plan_prefix": [ 3179 "`t2`", 3180 "`t1`" 3181 ] /* plan_prefix */, 3182 "table": "`t3`", 3183 "best_access_path": { 3184 "considered_access_paths": [ 3185 { 3186 "access_type": "ref", 3187 "index": "a", 3188 "rows": 1, 3189 "cost": 12, 3190 "chosen": true 3191 }, 3192 { 3193 "rows_to_scan": 10, 3194 "access_type": "scan", 3195 "using_join_cache": true, 3196 "buffers_needed": 1, 3197 "resulting_rows": 10, 3198 "cost": 22.023, 3199 "chosen": false 3200 } 3201 ] /* considered_access_paths */ 3202 } /* best_access_path */, 3203 "condition_filtering_pct": 100, 3204 "rows_for_plan": 10, 3205 "cost_for_plan": 28.022, 3206 "pruned_by_cost": true 3207 } 3208 ] /* rest_of_plan */ 3209 }, 3210 { 3211 "plan_prefix": [ 3212 "`t2`" 3213 ] /* plan_prefix */, 3214 "table": "`t3`", 3215 "best_access_path": { 3216 "considered_access_paths": [ 3217 { 3218 "access_type": "ref", 3219 "index": "a", 3220 "rows": 1, 3221 "cost": 12, 3222 "chosen": true 3223 }, 3224 { 3225 "rows_to_scan": 10, 3226 "access_type": "scan", 3227 "using_join_cache": true, 3228 "buffers_needed": 1, 3229 "resulting_rows": 10, 3230 "cost": 22.022, 3231 "chosen": false 3232 } 3233 ] /* considered_access_paths */ 3234 } /* best_access_path */, 3235 "condition_filtering_pct": 100, 3236 "rows_for_plan": 10, 3237 "cost_for_plan": 16.022, 3238 "pruned_by_heuristic": true 3239 } 3240 ] /* rest_of_plan */ 3241 }, 3242 { 3243 "plan_prefix": [ 3244 ] /* plan_prefix */, 3245 "table": "`t3`", 3246 "best_access_path": { 3247 "considered_access_paths": [ 3248 { 3249 "access_type": "ref", 3250 "index": "a", 3251 "usable": false, 3252 "chosen": false 3253 }, 3254 { 3255 "rows_to_scan": 10, 3256 "access_type": "scan", 3257 "resulting_rows": 10, 3258 "cost": 4.022, 3259 "chosen": true 3260 } 3261 ] /* considered_access_paths */ 3262 } /* best_access_path */, 3263 "condition_filtering_pct": 100, 3264 "rows_for_plan": 10, 3265 "cost_for_plan": 4.022, 3266 "rest_of_plan": [ 3267 { 3268 "plan_prefix": [ 3269 "`t3`" 3270 ] /* plan_prefix */, 3271 "table": "`t1`", 3272 "best_access_path": { 3273 "considered_access_paths": [ 3274 { 3275 "access_type": "ref", 3276 "index": "a", 3277 "rows": 1, 3278 "cost": 12, 3279 "chosen": true 3280 }, 3281 { 3282 "rows_to_scan": 10, 3283 "access_type": "scan", 3284 "using_join_cache": true, 3285 "buffers_needed": 1, 3286 "resulting_rows": 10, 3287 "cost": 22.022, 3288 "chosen": false 3289 } 3290 ] /* considered_access_paths */ 3291 } /* best_access_path */, 3292 "condition_filtering_pct": 100, 3293 "rows_for_plan": 10, 3294 "cost_for_plan": 16.022, 3295 "rest_of_plan": [ 3296 { 3297 "plan_prefix": [ 3298 "`t3`", 3299 "`t1`" 3300 ] /* plan_prefix */, 3301 "table": "`t2`", 3302 "best_access_path": { 3303 "considered_access_paths": [ 3304 { 3305 "access_type": "ref", 3306 "index": "a", 3307 "rows": 1, 3308 "cost": 12, 3309 "chosen": true 3310 }, 3311 { 3312 "rows_to_scan": 10, 3313 "access_type": "scan", 3314 "using_join_cache": true, 3315 "buffers_needed": 1, 3316 "resulting_rows": 10, 3317 "cost": 22.023, 3318 "chosen": false 3319 } 3320 ] /* considered_access_paths */ 3321 } /* best_access_path */, 3322 "added_to_eq_ref_extension": true, 3323 "condition_filtering_pct": 100, 3324 "rows_for_plan": 10, 3325 "cost_for_plan": 28.022, 3326 "pruned_by_cost": true 3327 }, 3328 { 3329 "plan_prefix": [ 3330 "`t3`", 3331 "`t1`" 3332 ] /* plan_prefix */, 3333 "table": "`t2`", 3334 "best_access_path": { 3335 "considered_access_paths": [ 3336 { 3337 "access_type": "ref", 3338 "index": "a", 3339 "rows": 1, 3340 "cost": 12, 3341 "chosen": true 3342 }, 3343 { 3344 "rows_to_scan": 10, 3345 "access_type": "scan", 3346 "using_join_cache": true, 3347 "buffers_needed": 1, 3348 "resulting_rows": 10, 3349 "cost": 22.023, 3350 "chosen": false 3351 } 3352 ] /* considered_access_paths */ 3353 } /* best_access_path */, 3354 "condition_filtering_pct": 100, 3355 "rows_for_plan": 10, 3356 "cost_for_plan": 28.022, 3357 "pruned_by_cost": true 3358 } 3359 ] /* rest_of_plan */ 3360 }, 3361 { 3362 "plan_prefix": [ 3363 "`t3`" 3364 ] /* plan_prefix */, 3365 "table": "`t2`", 3366 "best_access_path": { 3367 "considered_access_paths": [ 3368 { 3369 "access_type": "ref", 3370 "index": "a", 3371 "rows": 1, 3372 "cost": 12, 3373 "chosen": true 3374 }, 3375 { 3376 "rows_to_scan": 10, 3377 "access_type": "scan", 3378 "using_join_cache": true, 3379 "buffers_needed": 1, 3380 "resulting_rows": 10, 3381 "cost": 22.022, 3382 "chosen": false 3383 } 3384 ] /* considered_access_paths */ 3385 } /* best_access_path */, 3386 "condition_filtering_pct": 100, 3387 "rows_for_plan": 10, 3388 "cost_for_plan": 16.022, 3389 "pruned_by_heuristic": true 3390 } 3391 ] /* rest_of_plan */ 3392 } 3393 ] /* considered_execution_plans */ 3394 }, 3395 { 3396 "transformation": { 3397 "select#": 2, 3398 "from": "IN (SELECT)", 3399 "to": "EXISTS (CORRELATED SELECT)", 3400 "put_1_in_SELECT_list": true 3401 } /* transformation */ 3402 }, 3403 { 3404 "attaching_conditions_to_tables": { 3405 "original_condition": "((`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`) and (<cache>(`t0`.`a`) = (`t1`.`a` + `t1`.`a`)))", 3406 "attached_conditions_computation": [ 3407 { 3408 "table": "`t1`", 3409 "rechecking_index_usage": { 3410 "recheck_reason": "not_first_table", 3411 "range_analysis": { 3412 "table_scan": { 3413 "rows": 10, 3414 "cost": 14 3415 } /* table_scan */, 3416 "potential_range_indexes": [ 3417 { 3418 "index": "a", 3419 "usable": true, 3420 "key_parts": [ 3421 "a" 3422 ] /* key_parts */ 3423 } 3424 ] /* potential_range_indexes */, 3425 "best_covering_index_scan": { 3426 "index": "a", 3427 "cost": 3.2093, 3428 "chosen": true 3429 } /* best_covering_index_scan */, 3430 "setup_range_conditions": [ 3431 ] /* setup_range_conditions */, 3432 "group_index_range": { 3433 "chosen": false, 3434 "cause": "not_single_table" 3435 } /* group_index_range */ 3436 } /* range_analysis */ 3437 } /* rechecking_index_usage */ 3438 } 3439 ] /* attached_conditions_computation */, 3440 "attached_conditions_summary": [ 3441 { 3442 "table": "`t1`", 3443 "attached": "(<cache>(`t0`.`a`) = (`t1`.`a` + `t1`.`a`))" 3444 }, 3445 { 3446 "table": "`t2`", 3447 "attached": null 3448 }, 3449 { 3450 "table": "`t3`", 3451 "attached": null 3452 } 3453 ] /* attached_conditions_summary */ 3454 } /* attaching_conditions_to_tables */ 3455 }, 3456 { 3457 "refine_plan": [ 3458 { 3459 "table": "`t1`" 3460 }, 3461 { 3462 "table": "`t2`" 3463 }, 3464 { 3465 "table": "`t3`" 3466 } 3467 ] /* refine_plan */ 3468 } 3469 ] /* steps */ 3470 } /* join_optimization */ 3471 }, 3472 { 3473 "join_explain": { 3474 "select#": 1, 3475 "steps": [ 3476 { 3477 "join_explain": { 3478 "select#": 2, 3479 "steps": [ 3480 ] /* steps */ 3481 } /* join_explain */ 3482 } 3483 ] /* steps */ 3484 } /* join_explain */ 3485 } 3486 ] /* steps */ 3487} 0 0 3488set optimizer_switch=@old_opt_switch; 3489drop table t0,t1,t2,t3; 3490CREATE TABLE t1 ( 3491OBJECTID int(11) NOT NULL default '0', 3492SORTORDER int(11) NOT NULL auto_increment, 3493KEY t1_SortIndex (SORTORDER), 3494KEY t1_IdIndex (OBJECTID) 3495) ENGINE=MyISAM DEFAULT CHARSET=latin1; 3496CREATE TABLE t2 ( 3497ID int(11) default NULL, 3498PARID int(11) default NULL, 3499UNIQUE KEY t2_ID_IDX (ID), 3500KEY t2_PARID_IDX (PARID) 3501) engine=MyISAM DEFAULT CHARSET=latin1; 3502INSERT INTO t2 VALUES (1000,0),(1001,0),(1002,0),(1003,0),(1008,1),(1009,1),(1010,1),(1011,1),(1016,2); 3503CREATE TABLE t3 ( 3504ID int(11) default NULL, 3505DATA decimal(10,2) default NULL, 3506UNIQUE KEY t3_ID_IDX (ID) 3507) engine=MyISAM DEFAULT CHARSET=latin1; 3508INSERT INTO t3 VALUES (1000,0.00),(1001,0.25),(1002,0.50),(1003,0.75),(1008,1.00),(1009,1.25),(1010,1.50),(1011,1.75); 3509select 497, tmp.ID, NULL from (select 497 as ID, MAX(t3.DATA) as DATA from t1 join t2 on (t1.ObjectID = t2.ID) join t3 on (t1.ObjectID = t3.ID) group by t2.ParID order by DATA DESC) as tmp; 3510497 ID NULL 3511select * from information_schema.optimizer_trace; 3512QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES 3513select 497, tmp.ID, NULL from (select 497 as ID, MAX(t3.DATA) as DATA from t1 join t2 on (t1.ObjectID = t2.ID) join t3 on (t1.ObjectID = t3.ID) group by t2.ParID order by DATA DESC) as tmp { 3514 "steps": [ 3515 { 3516 "join_preparation": { 3517 "select#": 1, 3518 "steps": [ 3519 { 3520 "join_preparation": { 3521 "select#": 2, 3522 "steps": [ 3523 { 3524 "expanded_query": "/* select#2 */ select 497 AS `ID`,max(`t3`.`DATA`) AS `DATA` from `t1` join `t2` join `t3` where ((`t1`.`OBJECTID` = `t3`.`ID`) and (`t1`.`OBJECTID` = `t2`.`ID`)) group by `t2`.`PARID` order by `DATA` desc" 3525 } 3526 ] /* steps */ 3527 } /* join_preparation */ 3528 }, 3529 { 3530 "derived": { 3531 "table": " `tmp`", 3532 "select#": 2, 3533 "materialized": true 3534 } /* derived */ 3535 }, 3536 { 3537 "expanded_query": "/* select#1 */ select 497 AS `497`,`tmp`.`ID` AS `ID`,NULL AS `NULL` from (/* select#2 */ select 497 AS `ID`,max(`t3`.`DATA`) AS `DATA` from `t1` join `t2` join `t3` where ((`t1`.`OBJECTID` = `t3`.`ID`) and (`t1`.`OBJECTID` = `t2`.`ID`)) group by `t2`.`PARID` order by `DATA` desc) `tmp`" 3538 } 3539 ] /* steps */ 3540 } /* join_preparation */ 3541 }, 3542 { 3543 "join_optimization": { 3544 "select#": 1, 3545 "steps": [ 3546 { 3547 "join_optimization": { 3548 "select#": 2, 3549 "steps": [ 3550 { 3551 "condition_processing": { 3552 "condition": "WHERE", 3553 "original_condition": "((`t1`.`OBJECTID` = `t3`.`ID`) and (`t1`.`OBJECTID` = `t2`.`ID`))", 3554 "steps": [ 3555 { 3556 "transformation": "equality_propagation", 3557 "resulting_condition": "(multiple equal(`t1`.`OBJECTID`, `t3`.`ID`, `t2`.`ID`))" 3558 }, 3559 { 3560 "transformation": "constant_propagation", 3561 "resulting_condition": "(multiple equal(`t1`.`OBJECTID`, `t3`.`ID`, `t2`.`ID`))" 3562 }, 3563 { 3564 "transformation": "trivial_condition_removal", 3565 "resulting_condition": "multiple equal(`t1`.`OBJECTID`, `t3`.`ID`, `t2`.`ID`)" 3566 } 3567 ] /* steps */ 3568 } /* condition_processing */ 3569 }, 3570 { 3571 "substitute_generated_columns": { 3572 } /* substitute_generated_columns */ 3573 }, 3574 { 3575 "table_dependencies": [ 3576 { 3577 "table": "`t1`", 3578 "row_may_be_null": false, 3579 "map_bit": 0, 3580 "depends_on_map_bits": [ 3581 ] /* depends_on_map_bits */ 3582 }, 3583 { 3584 "table": "`t2`", 3585 "row_may_be_null": false, 3586 "map_bit": 1, 3587 "depends_on_map_bits": [ 3588 ] /* depends_on_map_bits */ 3589 }, 3590 { 3591 "table": "`t3`", 3592 "row_may_be_null": false, 3593 "map_bit": 2, 3594 "depends_on_map_bits": [ 3595 ] /* depends_on_map_bits */ 3596 } 3597 ] /* table_dependencies */ 3598 }, 3599 { 3600 "ref_optimizer_key_uses": [ 3601 { 3602 "table": "`t1`", 3603 "field": "OBJECTID", 3604 "equals": "`t3`.`ID`", 3605 "null_rejecting": true 3606 }, 3607 { 3608 "table": "`t1`", 3609 "field": "OBJECTID", 3610 "equals": "`t2`.`ID`", 3611 "null_rejecting": true 3612 }, 3613 { 3614 "table": "`t2`", 3615 "field": "ID", 3616 "equals": "`t1`.`OBJECTID`", 3617 "null_rejecting": false 3618 }, 3619 { 3620 "table": "`t2`", 3621 "field": "ID", 3622 "equals": "`t3`.`ID`", 3623 "null_rejecting": true 3624 }, 3625 { 3626 "table": "`t3`", 3627 "field": "ID", 3628 "equals": "`t1`.`OBJECTID`", 3629 "null_rejecting": false 3630 }, 3631 { 3632 "table": "`t3`", 3633 "field": "ID", 3634 "equals": "`t2`.`ID`", 3635 "null_rejecting": true 3636 } 3637 ] /* ref_optimizer_key_uses */ 3638 }, 3639 { 3640 "rows_estimation": [ 3641 { 3642 "table": "`t1`", 3643 "rows": 1, 3644 "cost": 1, 3645 "table_type": "system", 3646 "empty": true 3647 }, 3648 { 3649 "table": "`t2`", 3650 "const_keys_added": { 3651 "keys": [ 3652 "t2_PARID_IDX" 3653 ] /* keys */, 3654 "cause": "group_by" 3655 } /* const_keys_added */, 3656 "range_analysis": { 3657 "table_scan": { 3658 "rows": 9, 3659 "cost": 5.9198 3660 } /* table_scan */, 3661 "potential_range_indexes": [ 3662 { 3663 "index": "t2_ID_IDX", 3664 "usable": false, 3665 "cause": "not_applicable" 3666 }, 3667 { 3668 "index": "t2_PARID_IDX", 3669 "usable": true, 3670 "key_parts": [ 3671 "PARID" 3672 ] /* key_parts */ 3673 } 3674 ] /* potential_range_indexes */, 3675 "setup_range_conditions": [ 3676 ] /* setup_range_conditions */, 3677 "group_index_range": { 3678 "chosen": false, 3679 "cause": "not_single_table" 3680 } /* group_index_range */ 3681 } /* range_analysis */ 3682 }, 3683 { 3684 "table": "`t3`", 3685 "table_scan": { 3686 "rows": 8, 3687 "cost": 2 3688 } /* table_scan */ 3689 } 3690 ] /* rows_estimation */ 3691 }, 3692 { 3693 "considered_execution_plans": [ 3694 { 3695 "plan_prefix": [ 3696 "`t1`" 3697 ] /* plan_prefix */, 3698 "table": "`t3`", 3699 "best_access_path": { 3700 "considered_access_paths": [ 3701 { 3702 "access_type": "ref", 3703 "index": "t3_ID_IDX", 3704 "rows": 1, 3705 "cost": 1.2, 3706 "chosen": true 3707 }, 3708 { 3709 "access_type": "scan", 3710 "cost": 3.6, 3711 "rows": 8, 3712 "chosen": false, 3713 "cause": "cost" 3714 } 3715 ] /* considered_access_paths */ 3716 } /* best_access_path */, 3717 "condition_filtering_pct": 100, 3718 "rows_for_plan": 1, 3719 "cost_for_plan": 1.2, 3720 "rest_of_plan": [ 3721 { 3722 "plan_prefix": [ 3723 "`t1`", 3724 "`t3`" 3725 ] /* plan_prefix */, 3726 "table": "`t2`", 3727 "best_access_path": { 3728 "considered_access_paths": [ 3729 { 3730 "access_type": "ref", 3731 "index": "t2_ID_IDX", 3732 "rows": 2, 3733 "cost": 2.4, 3734 "chosen": true 3735 }, 3736 { 3737 "access_type": "scan", 3738 "cost": 3.8, 3739 "rows": 9, 3740 "chosen": false, 3741 "cause": "cost" 3742 } 3743 ] /* considered_access_paths */ 3744 } /* best_access_path */, 3745 "added_to_eq_ref_extension": false 3746 }, 3747 { 3748 "plan_prefix": [ 3749 "`t1`", 3750 "`t3`" 3751 ] /* plan_prefix */, 3752 "table": "`t2`", 3753 "best_access_path": { 3754 "considered_access_paths": [ 3755 { 3756 "access_type": "ref", 3757 "index": "t2_ID_IDX", 3758 "rows": 2, 3759 "cost": 2.4, 3760 "chosen": true 3761 }, 3762 { 3763 "access_type": "scan", 3764 "cost": 3.8, 3765 "rows": 9, 3766 "chosen": false, 3767 "cause": "cost" 3768 } 3769 ] /* considered_access_paths */ 3770 } /* best_access_path */, 3771 "condition_filtering_pct": 100, 3772 "rows_for_plan": 2, 3773 "cost_for_plan": 3.6, 3774 "chosen": true 3775 } 3776 ] /* rest_of_plan */ 3777 }, 3778 { 3779 "plan_prefix": [ 3780 "`t1`" 3781 ] /* plan_prefix */, 3782 "table": "`t2`", 3783 "best_access_path": { 3784 "considered_access_paths": [ 3785 { 3786 "access_type": "ref", 3787 "index": "t2_ID_IDX", 3788 "rows": 1, 3789 "cost": 1.2, 3790 "chosen": true 3791 }, 3792 { 3793 "access_type": "scan", 3794 "cost": 3.8, 3795 "rows": 9, 3796 "chosen": false, 3797 "cause": "cost" 3798 } 3799 ] /* considered_access_paths */ 3800 } /* best_access_path */, 3801 "condition_filtering_pct": 100, 3802 "rows_for_plan": 1, 3803 "cost_for_plan": 1.2, 3804 "pruned_by_heuristic": true 3805 } 3806 ] /* considered_execution_plans */ 3807 } 3808 ] /* steps */, 3809 "empty_result": { 3810 "cause": "no matching row in const table" 3811 } /* empty_result */ 3812 } /* join_optimization */ 3813 }, 3814 { 3815 "table_dependencies": [ 3816 { 3817 "table": " `tmp`", 3818 "row_may_be_null": false, 3819 "map_bit": 0, 3820 "depends_on_map_bits": [ 3821 ] /* depends_on_map_bits */ 3822 } 3823 ] /* table_dependencies */ 3824 }, 3825 { 3826 "rows_estimation": [ 3827 { 3828 "table": " `tmp`", 3829 "table_scan": { 3830 "rows": 2, 3831 "cost": 10 3832 } /* table_scan */ 3833 } 3834 ] /* rows_estimation */ 3835 }, 3836 { 3837 "considered_execution_plans": [ 3838 { 3839 "plan_prefix": [ 3840 ] /* plan_prefix */, 3841 "table": " `tmp`", 3842 "best_access_path": { 3843 "considered_access_paths": [ 3844 { 3845 "rows_to_scan": 2, 3846 "access_type": "scan", 3847 "resulting_rows": 2, 3848 "cost": 10.5, 3849 "chosen": true 3850 } 3851 ] /* considered_access_paths */ 3852 } /* best_access_path */, 3853 "condition_filtering_pct": 100, 3854 "rows_for_plan": 2, 3855 "cost_for_plan": 10.5, 3856 "chosen": true 3857 } 3858 ] /* considered_execution_plans */ 3859 }, 3860 { 3861 "attaching_conditions_to_tables": { 3862 "original_condition": null, 3863 "attached_conditions_computation": [ 3864 ] /* attached_conditions_computation */, 3865 "attached_conditions_summary": [ 3866 { 3867 "table": " `tmp`", 3868 "attached": null 3869 } 3870 ] /* attached_conditions_summary */ 3871 } /* attaching_conditions_to_tables */ 3872 }, 3873 { 3874 "refine_plan": [ 3875 { 3876 "table": " `tmp`" 3877 } 3878 ] /* refine_plan */ 3879 } 3880 ] /* steps */ 3881 } /* join_optimization */ 3882 }, 3883 { 3884 "join_execution": { 3885 "select#": 1, 3886 "steps": [ 3887 { 3888 "creating_tmp_table": { 3889 "tmp_table_info": { 3890 "table": " `tmp`", 3891 "row_length": 10, 3892 "key_length": 0, 3893 "unique_constraint": false, 3894 "location": "memory (heap)", 3895 "row_limit_estimate": 104857 3896 } /* tmp_table_info */ 3897 } /* creating_tmp_table */ 3898 }, 3899 { 3900 "join_execution": { 3901 "select#": 2, 3902 "steps": [ 3903 ] /* steps */ 3904 } /* join_execution */ 3905 } 3906 ] /* steps */ 3907 } /* join_execution */ 3908 } 3909 ] /* steps */ 3910} 0 0 3911drop table t1,t2,t3; 3912CREATE TABLE t1 ( 3913t1_id bigint(21) NOT NULL auto_increment, 3914_field_72 varchar(128) DEFAULT '' NOT NULL, 3915_field_95 varchar(32), 3916_field_115 tinyint(4) DEFAULT '0' NOT NULL, 3917_field_122 tinyint(4) DEFAULT '0' NOT NULL, 3918_field_126 tinyint(4), 3919_field_134 tinyint(4), 3920PRIMARY KEY (t1_id), 3921UNIQUE _field_72 (_field_72), 3922KEY _field_115 (_field_115), 3923KEY _field_122 (_field_122) 3924); 3925INSERT INTO t1 VALUES (1,'admin','21232f297a57a5a743894a0e4a801fc3',0,1,NULL,NULL); 3926INSERT INTO t1 VALUES (2,'hroberts','7415275a8c95952901e42b13a6b78566',0,1,NULL,NULL); 3927INSERT INTO t1 VALUES (3,'guest','d41d8cd98f00b204e9800998ecf8427e',1,0,NULL,NULL); 3928CREATE TABLE t2 ( 3929seq_0_id bigint(21) DEFAULT '0' NOT NULL, 3930seq_1_id bigint(21) DEFAULT '0' NOT NULL, 3931PRIMARY KEY (seq_0_id,seq_1_id) 3932); 3933INSERT INTO t2 VALUES (1,1); 3934INSERT INTO t2 VALUES (2,1); 3935INSERT INTO t2 VALUES (2,2); 3936SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; 3937Warnings: 3938Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 3939CREATE TABLE t3 ( 3940t3_id bigint(21) NOT NULL auto_increment, 3941_field_131 varchar(128), 3942_field_133 tinyint(4) DEFAULT '0' NOT NULL, 3943_field_135 datetime DEFAULT '0000-00-00 00:00:00' NOT NULL, 3944_field_137 tinyint(4), 3945_field_139 datetime DEFAULT '0000-00-00 00:00:00' NOT NULL, 3946_field_140 blob, 3947_field_142 tinyint(4) DEFAULT '0' NOT NULL, 3948_field_145 tinyint(4) DEFAULT '0' NOT NULL, 3949_field_148 tinyint(4) DEFAULT '0' NOT NULL, 3950PRIMARY KEY (t3_id), 3951KEY _field_133 (_field_133), 3952KEY _field_135 (_field_135), 3953KEY _field_139 (_field_139), 3954KEY _field_142 (_field_142), 3955KEY _field_145 (_field_145), 3956KEY _field_148 (_field_148) 3957); 3958INSERT INTO t3 VALUES (1,'test job 1',0,'0000-00-00 00:00:00',0,'1999-02-25 22:43:32','test\r\njob\r\n1',0,0,0); 3959INSERT INTO t3 VALUES (2,'test job 2',0,'0000-00-00 00:00:00',0,'1999-02-26 21:08:04','',0,0,0); 3960SET sql_mode = default; 3961CREATE TABLE t4 ( 3962seq_0_id bigint(21) DEFAULT '0' NOT NULL, 3963seq_1_id bigint(21) DEFAULT '0' NOT NULL, 3964PRIMARY KEY (seq_0_id,seq_1_id) 3965); 3966INSERT INTO t4 VALUES (1,1); 3967INSERT INTO t4 VALUES (2,1); 3968CREATE TABLE t5 ( 3969t5_id bigint(21) NOT NULL auto_increment, 3970_field_149 tinyint(4), 3971_field_156 varchar(128) DEFAULT '' NOT NULL, 3972_field_157 varchar(128) DEFAULT '' NOT NULL, 3973_field_158 varchar(128) DEFAULT '' NOT NULL, 3974_field_159 varchar(128) DEFAULT '' NOT NULL, 3975_field_160 varchar(128) DEFAULT '' NOT NULL, 3976_field_161 varchar(128) DEFAULT '' NOT NULL, 3977PRIMARY KEY (t5_id), 3978KEY _field_156 (_field_156), 3979KEY _field_157 (_field_157), 3980KEY _field_158 (_field_158), 3981KEY _field_159 (_field_159), 3982KEY _field_160 (_field_160), 3983KEY _field_161 (_field_161) 3984); 3985INSERT INTO t5 VALUES (1,0,'tomato','','','','',''); 3986INSERT INTO t5 VALUES (2,0,'cilantro','','','','',''); 3987CREATE TABLE t6 ( 3988seq_0_id bigint(21) DEFAULT '0' NOT NULL, 3989seq_1_id bigint(21) DEFAULT '0' NOT NULL, 3990PRIMARY KEY (seq_0_id,seq_1_id) 3991); 3992INSERT INTO t6 VALUES (1,1); 3993INSERT INTO t6 VALUES (1,2); 3994INSERT INTO t6 VALUES (2,2); 3995CREATE TABLE t7 ( 3996t7_id bigint(21) NOT NULL auto_increment, 3997_field_143 tinyint(4), 3998_field_165 varchar(32), 3999_field_166 smallint(6) DEFAULT '0' NOT NULL, 4000PRIMARY KEY (t7_id), 4001KEY _field_166 (_field_166) 4002); 4003INSERT INTO t7 VALUES (1,0,'High',1); 4004INSERT INTO t7 VALUES (2,0,'Medium',2); 4005INSERT INTO t7 VALUES (3,0,'Low',3); 4006select 4007replace(t3._field_140, "\r","^M"), 4008t3_id, 4009min(t3._field_131), 4010min(t3._field_135), 4011min(t3._field_139), 4012min(t3._field_137), 4013min(link_alias_142._field_165), 4014min(link_alias_133._field_72), 4015min(t3._field_145), 4016min(link_alias_148._field_156), 4017replace(min(t3._field_140), "\r","^M"), 4018t3.t3_id 4019from 4020t3 left join t4 on t4.seq_0_id = t3.t3_id 4021left join t7 link_alias_142 on t4.seq_1_id = link_alias_142.t7_id 4022left join t6 on t6.seq_0_id = t3.t3_id 4023left join t1 link_alias_133 on t6.seq_1_id = link_alias_133.t1_id 4024left join t2 on t2.seq_0_id = t3.t3_id 4025left join t5 link_alias_148 on t2.seq_1_id = link_alias_148.t5_id 4026where 4027t3.t3_id in (1) 4028group by 4029t3.t3_id 4030order by 4031link_alias_142._field_166, 4032_field_139, 4033link_alias_133._field_72, 4034_field_135, 4035link_alias_148._field_156 4036; 4037replace(t3._field_140, "\r","^M") t3_id min(t3._field_131) min(t3._field_135) min(t3._field_139) min(t3._field_137) min(link_alias_142._field_165) min(link_alias_133._field_72) min(t3._field_145) min(link_alias_148._field_156) replace(min(t3._field_140), "\r","^M") t3_id 4038test^M 4039job^M 40401 1 test job 1 0000-00-00 00:00:00 1999-02-25 22:43:32 0 High admin 0 tomato test^M 4041job^M 40421 1 4043select * from information_schema.optimizer_trace; 4044QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES 4045select 4046replace(t3._field_140, "\r","^M"), 4047t3_id, 4048min(t3._field_131), 4049min(t3._field_135), 4050min(t3._field_139), 4051min(t3._field_137), 4052min(link_alias_142._field_165), 4053min(link_alias_133._field_72), 4054min(t3._field_145), 4055min(link_alias_148._field_156), 4056replace(min(t3._field_140), "\r","^M"), 4057t3.t3_id 4058from 4059t3 left join t4 on t4.seq_0_id = t3.t3_id 4060left join t7 link_alias_142 on t4.seq_1_id = link_alias_142.t7_id 4061left join t6 on t6.seq_0_id = t3.t3_id 4062left join t1 link_alias_133 on t6.seq_1_id = link_alias_133.t1_id 4063left join t2 on t2.seq_0_id = t3.t3_id 4064left join t5 link_alias_148 on t2.seq_1_id = link_alias_148.t5_id 4065where 4066t3.t3_id in (1) 4067group by 4068t3.t3_id 4069order by 4070link_alias_142._field_166, 4071_field_139, 4072link_alias_133._field_72, 4073_field_135, 4074link_alias_148._field_156 { 4075 "steps": [ 4076 { 4077 "join_preparation": { 4078 "select#": 1, 4079 "steps": [ 4080 { 4081 "expanded_query": "/* select#1 */ select replace(`t3`.`_field_140`,'\\r','^M') AS `replace(t3._field_140, \"\\r\",\"^M\")`,`t3`.`t3_id` AS `t3_id`,min(`t3`.`_field_131`) AS `min(t3._field_131)`,min(`t3`.`_field_135`) AS `min(t3._field_135)`,min(`t3`.`_field_139`) AS `min(t3._field_139)`,min(`t3`.`_field_137`) AS `min(t3._field_137)`,min(`link_alias_142`.`_field_165`) AS `min(link_alias_142._field_165)`,min(`link_alias_133`.`_field_72`) AS `min(link_alias_133._field_72)`,min(`t3`.`_field_145`) AS `min(t3._field_145)`,min(`link_alias_148`.`_field_156`) AS `min(link_alias_148._field_156)`,replace(min(`t3`.`_field_140`),'\\r','^M') AS `replace(min(t3._field_140), \"\\r\",\"^M\")`,`t3`.`t3_id` AS `t3_id` from `t3` left join `t4` on((`t4`.`seq_0_id` = `t3`.`t3_id`)) left join `t7` `link_alias_142` on((`t4`.`seq_1_id` = `link_alias_142`.`t7_id`)) left join `t6` on((`t6`.`seq_0_id` = `t3`.`t3_id`)) left join `t1` `link_alias_133` on((`t6`.`seq_1_id` = `link_alias_133`.`t1_id`)) left join `t2` on((`t2`.`seq_0_id` = `t3`.`t3_id`)) left join `t5` `link_alias_148` on((`t2`.`seq_1_id` = `link_alias_148`.`t5_id`)) where (`t3`.`t3_id` = 1) group by `t3`.`t3_id` order by `link_alias_142`.`_field_166`,`t3`.`_field_139`,`link_alias_133`.`_field_72`,`t3`.`_field_135`,`link_alias_148`.`_field_156`" 4082 } 4083 ] /* steps */ 4084 } /* join_preparation */ 4085 }, 4086 { 4087 "join_optimization": { 4088 "select#": 1, 4089 "steps": [ 4090 { 4091 "condition_processing": { 4092 "condition": "WHERE", 4093 "original_condition": "(`t3`.`t3_id` = 1)", 4094 "steps": [ 4095 { 4096 "transformation": "equality_propagation", 4097 "resulting_condition": "multiple equal(1, `t3`.`t3_id`)" 4098 }, 4099 { 4100 "transformation": "constant_propagation", 4101 "resulting_condition": "multiple equal(1, `t3`.`t3_id`)" 4102 }, 4103 { 4104 "transformation": "trivial_condition_removal", 4105 "resulting_condition": "multiple equal(1, `t3`.`t3_id`)" 4106 } 4107 ] /* steps */ 4108 } /* condition_processing */ 4109 }, 4110 { 4111 "substitute_generated_columns": { 4112 } /* substitute_generated_columns */ 4113 }, 4114 { 4115 "table_dependencies": [ 4116 { 4117 "table": "`t3`", 4118 "row_may_be_null": false, 4119 "map_bit": 0, 4120 "depends_on_map_bits": [ 4121 ] /* depends_on_map_bits */ 4122 }, 4123 { 4124 "table": "`t4`", 4125 "row_may_be_null": true, 4126 "map_bit": 1, 4127 "depends_on_map_bits": [ 4128 0 4129 ] /* depends_on_map_bits */ 4130 }, 4131 { 4132 "table": "`t7` `link_alias_142`", 4133 "row_may_be_null": true, 4134 "map_bit": 2, 4135 "depends_on_map_bits": [ 4136 0, 4137 1 4138 ] /* depends_on_map_bits */ 4139 }, 4140 { 4141 "table": "`t6`", 4142 "row_may_be_null": true, 4143 "map_bit": 3, 4144 "depends_on_map_bits": [ 4145 0, 4146 1, 4147 2 4148 ] /* depends_on_map_bits */ 4149 }, 4150 { 4151 "table": "`t1` `link_alias_133`", 4152 "row_may_be_null": true, 4153 "map_bit": 4, 4154 "depends_on_map_bits": [ 4155 0, 4156 1, 4157 2, 4158 3 4159 ] /* depends_on_map_bits */ 4160 }, 4161 { 4162 "table": "`t2`", 4163 "row_may_be_null": true, 4164 "map_bit": 5, 4165 "depends_on_map_bits": [ 4166 0, 4167 1, 4168 2, 4169 3, 4170 4 4171 ] /* depends_on_map_bits */ 4172 }, 4173 { 4174 "table": "`t5` `link_alias_148`", 4175 "row_may_be_null": true, 4176 "map_bit": 6, 4177 "depends_on_map_bits": [ 4178 0, 4179 1, 4180 2, 4181 3, 4182 4, 4183 5 4184 ] /* depends_on_map_bits */ 4185 } 4186 ] /* table_dependencies */ 4187 }, 4188 { 4189 "ref_optimizer_key_uses": [ 4190 { 4191 "table": "`t3`", 4192 "field": "t3_id", 4193 "equals": "1", 4194 "null_rejecting": false 4195 }, 4196 { 4197 "table": "`t4`", 4198 "field": "seq_0_id", 4199 "equals": "1", 4200 "null_rejecting": false 4201 }, 4202 { 4203 "table": "`t7` `link_alias_142`", 4204 "field": "t7_id", 4205 "equals": "`t4`.`seq_1_id`", 4206 "null_rejecting": true 4207 }, 4208 { 4209 "table": "`t6`", 4210 "field": "seq_0_id", 4211 "equals": "1", 4212 "null_rejecting": false 4213 }, 4214 { 4215 "table": "`t1` `link_alias_133`", 4216 "field": "t1_id", 4217 "equals": "`t6`.`seq_1_id`", 4218 "null_rejecting": true 4219 }, 4220 { 4221 "table": "`t2`", 4222 "field": "seq_0_id", 4223 "equals": "1", 4224 "null_rejecting": false 4225 }, 4226 { 4227 "table": "`t5` `link_alias_148`", 4228 "field": "t5_id", 4229 "equals": "`t2`.`seq_1_id`", 4230 "null_rejecting": true 4231 } 4232 ] /* ref_optimizer_key_uses */ 4233 }, 4234 { 4235 "rows_estimation": [ 4236 { 4237 "table": "`t3`", 4238 "rows": 1, 4239 "cost": 1, 4240 "table_type": "const", 4241 "empty": false 4242 }, 4243 { 4244 "table": "`t4`", 4245 "range_analysis": { 4246 "table_scan": { 4247 "rows": 2, 4248 "cost": 4.5083 4249 } /* table_scan */, 4250 "potential_range_indexes": [ 4251 { 4252 "index": "PRIMARY", 4253 "usable": true, 4254 "key_parts": [ 4255 "seq_0_id", 4256 "seq_1_id" 4257 ] /* key_parts */ 4258 } 4259 ] /* potential_range_indexes */, 4260 "best_covering_index_scan": { 4261 "index": "PRIMARY", 4262 "cost": 1.4435, 4263 "chosen": true 4264 } /* best_covering_index_scan */, 4265 "setup_range_conditions": [ 4266 ] /* setup_range_conditions */, 4267 "group_index_range": { 4268 "chosen": false, 4269 "cause": "not_single_table" 4270 } /* group_index_range */, 4271 "analyzing_range_alternatives": { 4272 "range_scan_alternatives": [ 4273 { 4274 "index": "PRIMARY", 4275 "ranges": [ 4276 "1 <= seq_0_id <= 1" 4277 ] /* ranges */, 4278 "index_dives_for_eq_ranges": true, 4279 "rowid_ordered": false, 4280 "using_mrr": false, 4281 "index_only": true, 4282 "rows": 1, 4283 "cost": 1.21, 4284 "chosen": true 4285 } 4286 ] /* range_scan_alternatives */, 4287 "analyzing_roworder_intersect": { 4288 "usable": false, 4289 "cause": "too_few_roworder_scans" 4290 } /* analyzing_roworder_intersect */ 4291 } /* analyzing_range_alternatives */, 4292 "chosen_range_access_summary": { 4293 "range_access_plan": { 4294 "type": "range_scan", 4295 "index": "PRIMARY", 4296 "rows": 1, 4297 "ranges": [ 4298 "1 <= seq_0_id <= 1" 4299 ] /* ranges */ 4300 } /* range_access_plan */, 4301 "rows_for_plan": 1, 4302 "cost_for_plan": 1.21, 4303 "chosen": true 4304 } /* chosen_range_access_summary */ 4305 } /* range_analysis */ 4306 }, 4307 { 4308 "table": "`t7` `link_alias_142`", 4309 "table_scan": { 4310 "rows": 3, 4311 "cost": 2 4312 } /* table_scan */ 4313 }, 4314 { 4315 "table": "`t6`", 4316 "range_analysis": { 4317 "table_scan": { 4318 "rows": 3, 4319 "cost": 4.7125 4320 } /* table_scan */, 4321 "potential_range_indexes": [ 4322 { 4323 "index": "PRIMARY", 4324 "usable": true, 4325 "key_parts": [ 4326 "seq_0_id", 4327 "seq_1_id" 4328 ] /* key_parts */ 4329 } 4330 ] /* potential_range_indexes */, 4331 "best_covering_index_scan": { 4332 "index": "PRIMARY", 4333 "cost": 1.687, 4334 "chosen": true 4335 } /* best_covering_index_scan */, 4336 "setup_range_conditions": [ 4337 ] /* setup_range_conditions */, 4338 "group_index_range": { 4339 "chosen": false, 4340 "cause": "not_single_table" 4341 } /* group_index_range */, 4342 "analyzing_range_alternatives": { 4343 "range_scan_alternatives": [ 4344 { 4345 "index": "PRIMARY", 4346 "ranges": [ 4347 "1 <= seq_0_id <= 1" 4348 ] /* ranges */, 4349 "index_dives_for_eq_ranges": true, 4350 "rowid_ordered": false, 4351 "using_mrr": false, 4352 "index_only": true, 4353 "rows": 1, 4354 "cost": 1.21, 4355 "chosen": true 4356 } 4357 ] /* range_scan_alternatives */, 4358 "analyzing_roworder_intersect": { 4359 "usable": false, 4360 "cause": "too_few_roworder_scans" 4361 } /* analyzing_roworder_intersect */ 4362 } /* analyzing_range_alternatives */, 4363 "chosen_range_access_summary": { 4364 "range_access_plan": { 4365 "type": "range_scan", 4366 "index": "PRIMARY", 4367 "rows": 1, 4368 "ranges": [ 4369 "1 <= seq_0_id <= 1" 4370 ] /* ranges */ 4371 } /* range_access_plan */, 4372 "rows_for_plan": 1, 4373 "cost_for_plan": 1.21, 4374 "chosen": true 4375 } /* chosen_range_access_summary */ 4376 } /* range_analysis */ 4377 }, 4378 { 4379 "table": "`t1` `link_alias_133`", 4380 "table_scan": { 4381 "rows": 3, 4382 "cost": 2 4383 } /* table_scan */ 4384 }, 4385 { 4386 "table": "`t2`", 4387 "range_analysis": { 4388 "table_scan": { 4389 "rows": 3, 4390 "cost": 4.7125 4391 } /* table_scan */, 4392 "potential_range_indexes": [ 4393 { 4394 "index": "PRIMARY", 4395 "usable": true, 4396 "key_parts": [ 4397 "seq_0_id", 4398 "seq_1_id" 4399 ] /* key_parts */ 4400 } 4401 ] /* potential_range_indexes */, 4402 "best_covering_index_scan": { 4403 "index": "PRIMARY", 4404 "cost": 1.687, 4405 "chosen": true 4406 } /* best_covering_index_scan */, 4407 "setup_range_conditions": [ 4408 ] /* setup_range_conditions */, 4409 "group_index_range": { 4410 "chosen": false, 4411 "cause": "not_single_table" 4412 } /* group_index_range */, 4413 "analyzing_range_alternatives": { 4414 "range_scan_alternatives": [ 4415 { 4416 "index": "PRIMARY", 4417 "ranges": [ 4418 "1 <= seq_0_id <= 1" 4419 ] /* ranges */, 4420 "index_dives_for_eq_ranges": true, 4421 "rowid_ordered": false, 4422 "using_mrr": false, 4423 "index_only": true, 4424 "rows": 1, 4425 "cost": 1.21, 4426 "chosen": true 4427 } 4428 ] /* range_scan_alternatives */, 4429 "analyzing_roworder_intersect": { 4430 "usable": false, 4431 "cause": "too_few_roworder_scans" 4432 } /* analyzing_roworder_intersect */ 4433 } /* analyzing_range_alternatives */, 4434 "chosen_range_access_summary": { 4435 "range_access_plan": { 4436 "type": "range_scan", 4437 "index": "PRIMARY", 4438 "rows": 1, 4439 "ranges": [ 4440 "1 <= seq_0_id <= 1" 4441 ] /* ranges */ 4442 } /* range_access_plan */, 4443 "rows_for_plan": 1, 4444 "cost_for_plan": 1.21, 4445 "chosen": true 4446 } /* chosen_range_access_summary */ 4447 } /* range_analysis */ 4448 }, 4449 { 4450 "table": "`t5` `link_alias_148`", 4451 "table_scan": { 4452 "rows": 2, 4453 "cost": 2 4454 } /* table_scan */ 4455 } 4456 ] /* rows_estimation */ 4457 }, 4458 { 4459 "considered_execution_plans": [ 4460 { 4461 "plan_prefix": [ 4462 "`t3`" 4463 ] /* plan_prefix */, 4464 "table": "`t4`", 4465 "best_access_path": { 4466 "considered_access_paths": [ 4467 { 4468 "access_type": "ref", 4469 "index": "PRIMARY", 4470 "rows": 1, 4471 "cost": 1.2, 4472 "chosen": true 4473 }, 4474 { 4475 "access_type": "range", 4476 "range_details": { 4477 "used_index": "PRIMARY" 4478 } /* range_details */, 4479 "chosen": false, 4480 "cause": "heuristic_index_cheaper" 4481 } 4482 ] /* considered_access_paths */ 4483 } /* best_access_path */, 4484 "condition_filtering_pct": 100, 4485 "rows_for_plan": 1, 4486 "cost_for_plan": 1.2, 4487 "rest_of_plan": [ 4488 { 4489 "plan_prefix": [ 4490 "`t3`", 4491 "`t4`" 4492 ] /* plan_prefix */, 4493 "table": "`t7` `link_alias_142`", 4494 "best_access_path": { 4495 "considered_access_paths": [ 4496 { 4497 "access_type": "eq_ref", 4498 "index": "PRIMARY", 4499 "rows": 1, 4500 "cost": 1.2, 4501 "chosen": true 4502 }, 4503 { 4504 "access_type": "scan", 4505 "cost": 2.6, 4506 "rows": 3, 4507 "chosen": false, 4508 "cause": "cost" 4509 } 4510 ] /* considered_access_paths */ 4511 } /* best_access_path */, 4512 "added_to_eq_ref_extension": true, 4513 "condition_filtering_pct": 100, 4514 "rows_for_plan": 1, 4515 "cost_for_plan": 2.4, 4516 "rest_of_plan": [ 4517 { 4518 "plan_prefix": [ 4519 "`t3`", 4520 "`t4`", 4521 "`t7` `link_alias_142`" 4522 ] /* plan_prefix */, 4523 "table": "`t6`", 4524 "best_access_path": { 4525 "considered_access_paths": [ 4526 { 4527 "access_type": "ref", 4528 "index": "PRIMARY", 4529 "rows": 1, 4530 "cost": 1.2, 4531 "chosen": true 4532 }, 4533 { 4534 "access_type": "range", 4535 "range_details": { 4536 "used_index": "PRIMARY" 4537 } /* range_details */, 4538 "chosen": false, 4539 "cause": "heuristic_index_cheaper" 4540 } 4541 ] /* considered_access_paths */ 4542 } /* best_access_path */, 4543 "added_to_eq_ref_extension": true, 4544 "condition_filtering_pct": 100, 4545 "rows_for_plan": 1, 4546 "cost_for_plan": 3.6, 4547 "rest_of_plan": [ 4548 { 4549 "plan_prefix": [ 4550 "`t3`", 4551 "`t4`", 4552 "`t7` `link_alias_142`", 4553 "`t6`" 4554 ] /* plan_prefix */, 4555 "table": "`t1` `link_alias_133`", 4556 "best_access_path": { 4557 "considered_access_paths": [ 4558 { 4559 "access_type": "eq_ref", 4560 "index": "PRIMARY", 4561 "rows": 1, 4562 "cost": 1.2, 4563 "chosen": true 4564 }, 4565 { 4566 "access_type": "scan", 4567 "cost": 2.6, 4568 "rows": 3, 4569 "chosen": false, 4570 "cause": "cost" 4571 } 4572 ] /* considered_access_paths */ 4573 } /* best_access_path */, 4574 "added_to_eq_ref_extension": true, 4575 "condition_filtering_pct": 100, 4576 "rows_for_plan": 1, 4577 "cost_for_plan": 4.8, 4578 "rest_of_plan": [ 4579 { 4580 "plan_prefix": [ 4581 "`t3`", 4582 "`t4`", 4583 "`t7` `link_alias_142`", 4584 "`t6`", 4585 "`t1` `link_alias_133`" 4586 ] /* plan_prefix */, 4587 "table": "`t2`", 4588 "best_access_path": { 4589 "considered_access_paths": [ 4590 { 4591 "access_type": "ref", 4592 "index": "PRIMARY", 4593 "rows": 1, 4594 "cost": 1.2, 4595 "chosen": true 4596 }, 4597 { 4598 "access_type": "range", 4599 "range_details": { 4600 "used_index": "PRIMARY" 4601 } /* range_details */, 4602 "chosen": false, 4603 "cause": "heuristic_index_cheaper" 4604 } 4605 ] /* considered_access_paths */ 4606 } /* best_access_path */, 4607 "added_to_eq_ref_extension": true, 4608 "condition_filtering_pct": 100, 4609 "rows_for_plan": 1, 4610 "cost_for_plan": 6, 4611 "rest_of_plan": [ 4612 { 4613 "plan_prefix": [ 4614 "`t3`", 4615 "`t4`", 4616 "`t7` `link_alias_142`", 4617 "`t6`", 4618 "`t1` `link_alias_133`", 4619 "`t2`" 4620 ] /* plan_prefix */, 4621 "table": "`t5` `link_alias_148`", 4622 "best_access_path": { 4623 "considered_access_paths": [ 4624 { 4625 "access_type": "eq_ref", 4626 "index": "PRIMARY", 4627 "rows": 1, 4628 "cost": 1.2, 4629 "chosen": true 4630 }, 4631 { 4632 "access_type": "scan", 4633 "cost": 2.4, 4634 "rows": 2, 4635 "chosen": false, 4636 "cause": "cost" 4637 } 4638 ] /* considered_access_paths */ 4639 } /* best_access_path */, 4640 "added_to_eq_ref_extension": true, 4641 "condition_filtering_pct": 100, 4642 "rows_for_plan": 1, 4643 "cost_for_plan": 7.2, 4644 "chosen": true 4645 } 4646 ] /* rest_of_plan */ 4647 } 4648 ] /* rest_of_plan */ 4649 } 4650 ] /* rest_of_plan */ 4651 } 4652 ] /* rest_of_plan */ 4653 } 4654 ] /* rest_of_plan */ 4655 } 4656 ] /* considered_execution_plans */ 4657 }, 4658 { 4659 "condition_on_constant_tables": "1", 4660 "condition_value": true 4661 }, 4662 { 4663 "attaching_conditions_to_tables": { 4664 "original_condition": "1", 4665 "attached_conditions_computation": [ 4666 ] /* attached_conditions_computation */, 4667 "attached_conditions_summary": [ 4668 { 4669 "table": "`t4`", 4670 "attached": null 4671 }, 4672 { 4673 "table": "`t7` `link_alias_142`", 4674 "attached": null 4675 }, 4676 { 4677 "table": "`t6`", 4678 "attached": null 4679 }, 4680 { 4681 "table": "`t1` `link_alias_133`", 4682 "attached": null 4683 }, 4684 { 4685 "table": "`t2`", 4686 "attached": null 4687 }, 4688 { 4689 "table": "`t5` `link_alias_148`", 4690 "attached": null 4691 } 4692 ] /* attached_conditions_summary */ 4693 } /* attaching_conditions_to_tables */ 4694 }, 4695 { 4696 "clause_processing": { 4697 "clause": "ORDER BY", 4698 "original_clause": "`link_alias_142`.`_field_166`,'1999-02-25 22:43:32',`link_alias_133`.`_field_72`,'0000-00-00 00:00:00',`link_alias_148`.`_field_156`", 4699 "items": [ 4700 { 4701 "item": "`link_alias_142`.`_field_166`" 4702 }, 4703 { 4704 "item": "'1999-02-25 22:43:32'", 4705 "uses_only_constant_tables": true 4706 }, 4707 { 4708 "item": "`link_alias_133`.`_field_72`" 4709 }, 4710 { 4711 "item": "'0000-00-00 00:00:00'", 4712 "uses_only_constant_tables": true 4713 }, 4714 { 4715 "item": "`link_alias_148`.`_field_156`" 4716 } 4717 ] /* items */, 4718 "resulting_clause_is_simple": false, 4719 "resulting_clause": "`link_alias_142`.`_field_166`,`link_alias_133`.`_field_72`,`link_alias_148`.`_field_156`" 4720 } /* clause_processing */ 4721 }, 4722 { 4723 "clause_processing": { 4724 "clause": "GROUP BY", 4725 "original_clause": "'1'", 4726 "items": [ 4727 { 4728 "item": "'1'", 4729 "uses_only_constant_tables": true 4730 } 4731 ] /* items */, 4732 "resulting_clause_is_simple": true, 4733 "resulting_clause": "" 4734 } /* clause_processing */ 4735 }, 4736 { 4737 "refine_plan": [ 4738 { 4739 "table": "`t4`" 4740 }, 4741 { 4742 "table": "`t7` `link_alias_142`" 4743 }, 4744 { 4745 "table": "`t6`" 4746 }, 4747 { 4748 "table": "`t1` `link_alias_133`" 4749 }, 4750 { 4751 "table": "`t2`" 4752 }, 4753 { 4754 "table": "`t5` `link_alias_148`" 4755 } 4756 ] /* refine_plan */ 4757 } 4758 ] /* steps */ 4759 } /* join_optimization */ 4760 }, 4761 { 4762 "join_execution": { 4763 "select#": 1, 4764 "steps": [ 4765 ] /* steps */ 4766 } /* join_execution */ 4767 } 4768 ] /* steps */ 4769} 0 0 4770drop table t1,t2,t3,t4,t5,t6,t7; 4771# 4772# Tracing of ORDER BY & GROUP BY simplification. 4773# 4774CREATE TABLE t1 ( 4775pk INT, col_int_key INT, 4776col_varchar_key VARCHAR(1), col_varchar_nokey VARCHAR(1) 4777); 4778INSERT INTO t1 VALUES 4779(10,7,'v','v'),(11,0,'s','s'),(12,9,'l','l'),(13,3,'y','y'),(14,4,'c','c'), 4780(15,2,'i','i'),(16,5,'h','h'),(17,3,'q','q'),(18,1,'a','a'),(19,3,'v','v'), 4781(20,6,'u','u'),(21,7,'s','s'),(22,5,'y','y'),(23,1,'z','z'),(24,204,'h','h'), 4782(25,224,'p','p'),(26,9,'e','e'),(27,5,'i','i'),(28,0,'y','y'),(29,3,'w','w'); 4783CREATE TABLE t2 ( 4784pk INT, col_int_key INT, 4785col_varchar_key VARCHAR(1), col_varchar_nokey VARCHAR(1), 4786PRIMARY KEY (pk) 4787); 4788INSERT INTO t2 VALUES 4789(1,4,'b','b'),(2,8,'y','y'),(3,0,'p','p'),(4,0,'f','f'),(5,0,'p','p'), 4790(6,7,'d','d'),(7,7,'f','f'),(8,5,'j','j'),(9,3,'e','e'),(10,188,'u','u'), 4791(11,4,'v','v'),(12,9,'u','u'),(13,6,'i','i'),(14,1,'x','x'),(15,5,'l','l'), 4792(16,6,'q','q'),(17,2,'n','n'),(18,4,'r','r'),(19,231,'c','c'),(20,4,'h','h'), 4793(21,3,'k','k'),(22,3,'t','t'),(23,7,'t','t'),(24,6,'k','k'),(25,7,'g','g'), 4794(26,9,'z','z'),(27,4,'n','n'),(28,4,'j','j'),(29,2,'l','l'),(30,1,'d','d'), 4795(31,2,'t','t'),(32,194,'y','y'),(33,2,'i','i'),(34,3,'j','j'),(35,8,'r','r'), 4796(36,4,'b','b'),(37,9,'o','o'),(38,4,'k','k'),(39,5,'a','a'),(40,5,'f','f'), 4797(41,9,'t','t'),(42,3,'c','c'),(43,8,'c','c'),(44,0,'r','r'),(45,98,'k','k'), 4798(46,3,'l','l'),(47,1,'o','o'),(48,0,'t','t'),(49,189,'v','v'),(50,8,'x','x'), 4799(51,3,'j','j'),(52,3,'x','x'),(53,9,'k','k'),(54,6,'o','o'),(55,8,'z','z'), 4800(56,3,'n','n'),(57,9,'c','c'),(58,5,'d','d'),(59,9,'s','s'),(60,2,'j','j'), 4801(61,2,'w','w'),(62,5,'f','f'),(63,8,'p','p'),(64,6,'o','o'),(65,9,'f','f'), 4802(66,0,'x','x'),(67,3,'q','q'),(68,6,'g','g'),(69,5,'x','x'),(70,8,'p','p'), 4803(71,2,'q','q'),(72,120,'q','q'),(73,25,'v','v'),(74,1,'g','g'),(75,3,'l','l'), 4804(76,1,'w','w'),(77,3,'h','h'),(78,153,'c','c'),(79,5,'o','o'),(80,9,'o','o'), 4805(81,1,'v','v'),(82,8,'y','y'),(83,7,'d','d'),(84,6,'p','p'),(85,2,'z','z'), 4806(86,4,'t','t'),(87,7,'b','b'),(88,3,'y','y'),(89,8,'k','k'),(90,4,'c','c'), 4807(91,6,'z','z'),(92,1,'t','t'),(93,7,'o','o'),(94,1,'u','u'),(95,0,'t','t'), 4808(96,2,'k','k'),(97,7,'u','u'),(98,2,'b','b'),(99,1,'m','m'),(100,5,'o','o'); 4809SELECT SUM(alias2.col_varchar_nokey) , alias2.pk AS field2 FROM t1 AS alias1 4810STRAIGHT_JOIN t2 AS alias2 ON alias2.pk = alias1.col_int_key WHERE alias1.pk 4811GROUP BY field2 ORDER BY alias1.col_int_key,alias2.pk ; 4812SUM(alias2.col_varchar_nokey) field2 48130 1 48140 2 48150 3 48160 4 48170 5 48180 6 48190 7 48200 9 4821Warnings: 4822Warning 1292 Truncated incorrect DOUBLE value: 'f' 4823Warning 1292 Truncated incorrect DOUBLE value: 'e' 4824Warning 1292 Truncated incorrect DOUBLE value: 'p' 4825Warning 1292 Truncated incorrect DOUBLE value: 'f' 4826Warning 1292 Truncated incorrect DOUBLE value: 'y' 4827Warning 1292 Truncated incorrect DOUBLE value: 'p' 4828Warning 1292 Truncated incorrect DOUBLE value: 'p' 4829Warning 1292 Truncated incorrect DOUBLE value: 'b' 4830Warning 1292 Truncated incorrect DOUBLE value: 'p' 4831Warning 1292 Truncated incorrect DOUBLE value: 'd' 4832Warning 1292 Truncated incorrect DOUBLE value: 'f' 4833Warning 1292 Truncated incorrect DOUBLE value: 'p' 4834Warning 1292 Truncated incorrect DOUBLE value: 'b' 4835Warning 1292 Truncated incorrect DOUBLE value: 'e' 4836Warning 1292 Truncated incorrect DOUBLE value: 'p' 4837Warning 1292 Truncated incorrect DOUBLE value: 'p' 4838SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; 4839QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES 4840SELECT SUM(alias2.col_varchar_nokey) , alias2.pk AS field2 FROM t1 AS alias1 4841STRAIGHT_JOIN t2 AS alias2 ON alias2.pk = alias1.col_int_key WHERE alias1.pk 4842GROUP BY field2 ORDER BY alias1.col_int_key,alias2.pk { 4843 "steps": [ 4844 { 4845 "join_preparation": { 4846 "select#": 1, 4847 "steps": [ 4848 { 4849 "expanded_query": "/* select#1 */ select sum(`alias2`.`col_varchar_nokey`) AS `SUM(alias2.col_varchar_nokey)`,`alias2`.`pk` AS `field2` from `t1` `alias1` straight_join `t2` `alias2` where (`alias1`.`pk` and (`alias2`.`pk` = `alias1`.`col_int_key`)) group by `field2` order by `alias1`.`col_int_key`,`alias2`.`pk`" 4850 } 4851 ] /* steps */ 4852 } /* join_preparation */ 4853 }, 4854 { 4855 "join_optimization": { 4856 "select#": 1, 4857 "steps": [ 4858 { 4859 "condition_processing": { 4860 "condition": "WHERE", 4861 "original_condition": "(`alias1`.`pk` and (`alias2`.`pk` = `alias1`.`col_int_key`))", 4862 "steps": [ 4863 { 4864 "transformation": "equality_propagation", 4865 "resulting_condition": "(`alias1`.`pk` and multiple equal(`alias2`.`pk`, `alias1`.`col_int_key`))" 4866 }, 4867 { 4868 "transformation": "constant_propagation", 4869 "resulting_condition": "(`alias1`.`pk` and multiple equal(`alias2`.`pk`, `alias1`.`col_int_key`))" 4870 }, 4871 { 4872 "transformation": "trivial_condition_removal", 4873 "resulting_condition": "(`alias1`.`pk` and multiple equal(`alias2`.`pk`, `alias1`.`col_int_key`))" 4874 } 4875 ] /* steps */ 4876 } /* condition_processing */ 4877 }, 4878 { 4879 "substitute_generated_columns": { 4880 } /* substitute_generated_columns */ 4881 }, 4882 { 4883 "table_dependencies": [ 4884 { 4885 "table": "`t1` `alias1`", 4886 "row_may_be_null": false, 4887 "map_bit": 0, 4888 "depends_on_map_bits": [ 4889 ] /* depends_on_map_bits */ 4890 }, 4891 { 4892 "table": "`t2` `alias2`", 4893 "row_may_be_null": false, 4894 "map_bit": 1, 4895 "depends_on_map_bits": [ 4896 0 4897 ] /* depends_on_map_bits */ 4898 } 4899 ] /* table_dependencies */ 4900 }, 4901 { 4902 "ref_optimizer_key_uses": [ 4903 { 4904 "table": "`t2` `alias2`", 4905 "field": "pk", 4906 "equals": "`alias1`.`col_int_key`", 4907 "null_rejecting": true 4908 } 4909 ] /* ref_optimizer_key_uses */ 4910 }, 4911 { 4912 "rows_estimation": [ 4913 { 4914 "table": "`t1` `alias1`", 4915 "table_scan": { 4916 "rows": 20, 4917 "cost": 2 4918 } /* table_scan */ 4919 }, 4920 { 4921 "table": "`t2` `alias2`", 4922 "const_keys_added": { 4923 "keys": [ 4924 "PRIMARY" 4925 ] /* keys */, 4926 "cause": "group_by" 4927 } /* const_keys_added */, 4928 "range_analysis": { 4929 "table_scan": { 4930 "rows": 100, 4931 "cost": 24.588 4932 } /* table_scan */, 4933 "potential_range_indexes": [ 4934 { 4935 "index": "PRIMARY", 4936 "usable": true, 4937 "key_parts": [ 4938 "pk" 4939 ] /* key_parts */ 4940 } 4941 ] /* potential_range_indexes */, 4942 "setup_range_conditions": [ 4943 ] /* setup_range_conditions */, 4944 "group_index_range": { 4945 "chosen": false, 4946 "cause": "not_single_table" 4947 } /* group_index_range */ 4948 } /* range_analysis */ 4949 } 4950 ] /* rows_estimation */ 4951 }, 4952 { 4953 "considered_execution_plans": [ 4954 { 4955 "plan_prefix": [ 4956 ] /* plan_prefix */, 4957 "table": "`t1` `alias1`", 4958 "best_access_path": { 4959 "considered_access_paths": [ 4960 { 4961 "rows_to_scan": 20, 4962 "access_type": "scan", 4963 "resulting_rows": 18, 4964 "cost": 6.0977, 4965 "chosen": true 4966 } 4967 ] /* considered_access_paths */ 4968 } /* best_access_path */, 4969 "condition_filtering_pct": 100, 4970 "rows_for_plan": 18, 4971 "cost_for_plan": 6.0977, 4972 "rest_of_plan": [ 4973 { 4974 "plan_prefix": [ 4975 "`t1` `alias1`" 4976 ] /* plan_prefix */, 4977 "table": "`t2` `alias2`", 4978 "best_access_path": { 4979 "considered_access_paths": [ 4980 { 4981 "access_type": "eq_ref", 4982 "index": "PRIMARY", 4983 "rows": 1, 4984 "cost": 21.6, 4985 "chosen": true 4986 }, 4987 { 4988 "rows_to_scan": 100, 4989 "access_type": "scan", 4990 "using_join_cache": true, 4991 "buffers_needed": 1, 4992 "resulting_rows": 100, 4993 "cost": 362.49, 4994 "chosen": false 4995 } 4996 ] /* considered_access_paths */ 4997 } /* best_access_path */, 4998 "condition_filtering_pct": 100, 4999 "rows_for_plan": 18, 5000 "cost_for_plan": 27.698, 5001 "chosen": true 5002 } 5003 ] /* rest_of_plan */ 5004 } 5005 ] /* considered_execution_plans */ 5006 }, 5007 { 5008 "attaching_conditions_to_tables": { 5009 "original_condition": "((`alias2`.`pk` = `alias1`.`col_int_key`) and `alias1`.`pk`)", 5010 "attached_conditions_computation": [ 5011 ] /* attached_conditions_computation */, 5012 "attached_conditions_summary": [ 5013 { 5014 "table": "`t1` `alias1`", 5015 "attached": "(`alias1`.`pk` and (`alias1`.`col_int_key` is not null))" 5016 }, 5017 { 5018 "table": "`t2` `alias2`", 5019 "attached": null 5020 } 5021 ] /* attached_conditions_summary */ 5022 } /* attaching_conditions_to_tables */ 5023 }, 5024 { 5025 "clause_processing": { 5026 "clause": "ORDER BY", 5027 "original_clause": "`alias1`.`col_int_key`,`alias2`.`pk`", 5028 "items": [ 5029 { 5030 "item": "`alias1`.`col_int_key`" 5031 }, 5032 { 5033 "item": "`alias2`.`pk`", 5034 "eq_ref_to_preceding_items": true 5035 } 5036 ] /* items */, 5037 "resulting_clause_is_simple": true, 5038 "resulting_clause": "`alias1`.`col_int_key`" 5039 } /* clause_processing */ 5040 }, 5041 { 5042 "clause_processing": { 5043 "clause": "GROUP BY", 5044 "original_clause": "`field2`", 5045 "items": [ 5046 { 5047 "item": "`alias2`.`pk`" 5048 } 5049 ] /* items */, 5050 "resulting_clause_is_simple": false, 5051 "resulting_clause": "`field2`" 5052 } /* clause_processing */ 5053 }, 5054 { 5055 "refine_plan": [ 5056 { 5057 "table": "`t1` `alias1`" 5058 }, 5059 { 5060 "table": "`t2` `alias2`" 5061 } 5062 ] /* refine_plan */ 5063 } 5064 ] /* steps */ 5065 } /* join_optimization */ 5066 }, 5067 { 5068 "join_execution": { 5069 "select#": 1, 5070 "steps": [ 5071 { 5072 "creating_tmp_table": { 5073 "tmp_table_info": { 5074 "table": "intermediate_tmp_table", 5075 "row_length": 18, 5076 "key_length": 4, 5077 "unique_constraint": false, 5078 "location": "memory (heap)", 5079 "row_limit_estimate": 58254 5080 } /* tmp_table_info */ 5081 } /* creating_tmp_table */ 5082 }, 5083 { 5084 "filesort_information": [ 5085 { 5086 "direction": "asc", 5087 "table": "intermediate_tmp_table", 5088 "field": "col_int_key" 5089 } 5090 ] /* filesort_information */, 5091 "filesort_priority_queue_optimization": { 5092 "usable": false, 5093 "cause": "not applicable (no LIMIT)" 5094 } /* filesort_priority_queue_optimization */, 5095 "filesort_execution": [ 5096 ] /* filesort_execution */, 5097 "filesort_summary": { 5098 "rows": 8, 5099 "examined_rows": 8, 5100 "number_of_tmp_files": 0, 5101 "sort_buffer_size": "NNN", 5102 "sort_mode": "<sort_key, rowid>" 5103 } /* filesort_summary */ 5104 } 5105 ] /* steps */ 5106 } /* join_execution */ 5107 } 5108 ] /* steps */ 5109} 0 0 5110DROP TABLE t1,t2; 5111# 5112# Trace of "condition on constant tables" 5113# 5114create table t1(a int) engine=myisam; 5115insert into t1 values(26); 5116create table t2(b int primary key, c int) engine=myisam; 5117insert into t2 values(1,100),(2,200),(3,300); 5118select * from t1,t2 where t1.a+t2.c=cos(10) and t2.b=2; 5119a b c 5120SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; 5121QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES 5122select * from t1,t2 where t1.a+t2.c=cos(10) and t2.b=2 { 5123 "steps": [ 5124 { 5125 "join_preparation": { 5126 "select#": 1, 5127 "steps": [ 5128 { 5129 "expanded_query": "/* select#1 */ select `t1`.`a` AS `a`,`t2`.`b` AS `b`,`t2`.`c` AS `c` from `t1` join `t2` where (((`t1`.`a` + `t2`.`c`) = cos(10)) and (`t2`.`b` = 2))" 5130 } 5131 ] /* steps */ 5132 } /* join_preparation */ 5133 }, 5134 { 5135 "join_optimization": { 5136 "select#": 1, 5137 "steps": [ 5138 { 5139 "condition_processing": { 5140 "condition": "WHERE", 5141 "original_condition": "(((`t1`.`a` + `t2`.`c`) = cos(10)) and (`t2`.`b` = 2))", 5142 "steps": [ 5143 { 5144 "transformation": "equality_propagation", 5145 "resulting_condition": "(((`t1`.`a` + `t2`.`c`) = cos(10)) and multiple equal(2, `t2`.`b`))" 5146 }, 5147 { 5148 "transformation": "constant_propagation", 5149 "resulting_condition": "(((`t1`.`a` + `t2`.`c`) = cos(10)) and multiple equal(2, `t2`.`b`))" 5150 }, 5151 { 5152 "transformation": "trivial_condition_removal", 5153 "resulting_condition": "(((`t1`.`a` + `t2`.`c`) = cos(10)) and multiple equal(2, `t2`.`b`))" 5154 } 5155 ] /* steps */ 5156 } /* condition_processing */ 5157 }, 5158 { 5159 "substitute_generated_columns": { 5160 } /* substitute_generated_columns */ 5161 }, 5162 { 5163 "table_dependencies": [ 5164 { 5165 "table": "`t1`", 5166 "row_may_be_null": false, 5167 "map_bit": 0, 5168 "depends_on_map_bits": [ 5169 ] /* depends_on_map_bits */ 5170 }, 5171 { 5172 "table": "`t2`", 5173 "row_may_be_null": false, 5174 "map_bit": 1, 5175 "depends_on_map_bits": [ 5176 ] /* depends_on_map_bits */ 5177 } 5178 ] /* table_dependencies */ 5179 }, 5180 { 5181 "ref_optimizer_key_uses": [ 5182 { 5183 "table": "`t2`", 5184 "field": "b", 5185 "equals": "2", 5186 "null_rejecting": false 5187 } 5188 ] /* ref_optimizer_key_uses */ 5189 }, 5190 { 5191 "rows_estimation": [ 5192 { 5193 "table": "`t1`", 5194 "rows": 1, 5195 "cost": 1, 5196 "table_type": "system", 5197 "empty": false 5198 }, 5199 { 5200 "table": "`t2`", 5201 "rows": 1, 5202 "cost": 1, 5203 "table_type": "const", 5204 "empty": false 5205 } 5206 ] /* rows_estimation */ 5207 }, 5208 { 5209 "condition_on_constant_tables": "(('26' + '200') = cos(10))", 5210 "condition_value": false 5211 } 5212 ] /* steps */, 5213 "empty_result": { 5214 "cause": "Impossible WHERE noticed after reading const tables" 5215 } /* empty_result */ 5216 } /* join_optimization */ 5217 }, 5218 { 5219 "join_execution": { 5220 "select#": 1, 5221 "steps": [ 5222 ] /* steps */ 5223 } /* join_execution */ 5224 } 5225 ] /* steps */ 5226} 0 0 5227drop table t1,t2; 5228# 5229# Trace of non-default db 5230# 5231create table t1(a int); 5232insert into t1 values(1),(2),(3); 5233create database mysqltest2; 5234create table mysqltest2.t2(a int); 5235insert into mysqltest2.t2 values(1),(2); 5236select * from t1,mysqltest2.t2; 5237a a 52381 1 52391 2 52402 1 52412 2 52423 1 52433 2 5244SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; 5245QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES 5246select * from t1,mysqltest2.t2 { 5247 "steps": [ 5248 { 5249 "join_preparation": { 5250 "select#": 1, 5251 "steps": [ 5252 { 5253 "expanded_query": "/* select#1 */ select `t1`.`a` AS `a`,`mysqltest2`.`t2`.`a` AS `a` from `t1` join `mysqltest2`.`t2`" 5254 } 5255 ] /* steps */ 5256 } /* join_preparation */ 5257 }, 5258 { 5259 "join_optimization": { 5260 "select#": 1, 5261 "steps": [ 5262 { 5263 "table_dependencies": [ 5264 { 5265 "table": "`t1`", 5266 "row_may_be_null": false, 5267 "map_bit": 0, 5268 "depends_on_map_bits": [ 5269 ] /* depends_on_map_bits */ 5270 }, 5271 { 5272 "table": "`mysqltest2`.`t2`", 5273 "row_may_be_null": false, 5274 "map_bit": 1, 5275 "depends_on_map_bits": [ 5276 ] /* depends_on_map_bits */ 5277 } 5278 ] /* table_dependencies */ 5279 }, 5280 { 5281 "rows_estimation": [ 5282 { 5283 "table": "`t1`", 5284 "table_scan": { 5285 "rows": 3, 5286 "cost": 2 5287 } /* table_scan */ 5288 }, 5289 { 5290 "table": "`mysqltest2`.`t2`", 5291 "table_scan": { 5292 "rows": 2, 5293 "cost": 2 5294 } /* table_scan */ 5295 } 5296 ] /* rows_estimation */ 5297 }, 5298 { 5299 "considered_execution_plans": [ 5300 { 5301 "plan_prefix": [ 5302 ] /* plan_prefix */, 5303 "table": "`mysqltest2`.`t2`", 5304 "best_access_path": { 5305 "considered_access_paths": [ 5306 { 5307 "rows_to_scan": 2, 5308 "access_type": "scan", 5309 "resulting_rows": 2, 5310 "cost": 2.4034, 5311 "chosen": true 5312 } 5313 ] /* considered_access_paths */ 5314 } /* best_access_path */, 5315 "condition_filtering_pct": 100, 5316 "rows_for_plan": 2, 5317 "cost_for_plan": 2.4034, 5318 "rest_of_plan": [ 5319 { 5320 "plan_prefix": [ 5321 "`mysqltest2`.`t2`" 5322 ] /* plan_prefix */, 5323 "table": "`t1`", 5324 "best_access_path": { 5325 "considered_access_paths": [ 5326 { 5327 "rows_to_scan": 3, 5328 "access_type": "scan", 5329 "using_join_cache": true, 5330 "buffers_needed": 1, 5331 "resulting_rows": 3, 5332 "cost": 3.2052, 5333 "chosen": true 5334 } 5335 ] /* considered_access_paths */ 5336 } /* best_access_path */, 5337 "condition_filtering_pct": 100, 5338 "rows_for_plan": 6, 5339 "cost_for_plan": 5.6086, 5340 "chosen": true 5341 } 5342 ] /* rest_of_plan */ 5343 }, 5344 { 5345 "plan_prefix": [ 5346 ] /* plan_prefix */, 5347 "table": "`t1`", 5348 "best_access_path": { 5349 "considered_access_paths": [ 5350 { 5351 "rows_to_scan": 3, 5352 "access_type": "scan", 5353 "resulting_rows": 3, 5354 "cost": 2.6051, 5355 "chosen": true 5356 } 5357 ] /* considered_access_paths */ 5358 } /* best_access_path */, 5359 "condition_filtering_pct": 100, 5360 "rows_for_plan": 3, 5361 "cost_for_plan": 2.6051, 5362 "pruned_by_heuristic": true 5363 } 5364 ] /* considered_execution_plans */ 5365 }, 5366 { 5367 "attaching_conditions_to_tables": { 5368 "original_condition": null, 5369 "attached_conditions_computation": [ 5370 ] /* attached_conditions_computation */, 5371 "attached_conditions_summary": [ 5372 { 5373 "table": "`mysqltest2`.`t2`", 5374 "attached": null 5375 }, 5376 { 5377 "table": "`t1`", 5378 "attached": null 5379 } 5380 ] /* attached_conditions_summary */ 5381 } /* attaching_conditions_to_tables */ 5382 }, 5383 { 5384 "refine_plan": [ 5385 { 5386 "table": "`mysqltest2`.`t2`" 5387 }, 5388 { 5389 "table": "`t1`" 5390 } 5391 ] /* refine_plan */ 5392 } 5393 ] /* steps */ 5394 } /* join_optimization */ 5395 }, 5396 { 5397 "join_execution": { 5398 "select#": 1, 5399 "steps": [ 5400 ] /* steps */ 5401 } /* join_execution */ 5402 } 5403 ] /* steps */ 5404} 0 0 5405drop table t1; 5406drop database mysqltest2; 5407