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