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