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 "transformations_to_subquery": [ 721 "removed_ordering", 722 "removed_distinct", 723 "removed_grouping" 724 ] /* transformations_to_subquery */ 725 }, 726 { 727 "expanded_query": "/* select#2 */ select `t2`.`a` from `t2`" 728 } 729 ] /* steps */ 730 } /* join_preparation */ 731 }, 732 { 733 "expanded_query": "/* select#1 */ select 1 AS `1` from DUAL where (not(exists(/* select#2 */ select `t2`.`a` from `t2`)))" 734 } 735 ] /* steps */ 736 } /* join_preparation */ 737 }, 738 { 739 "join_optimization": { 740 "select#": 1, 741 "steps": [ 742 { 743 "condition_processing": { 744 "condition": "WHERE", 745 "original_condition": "(not(exists(/* select#2 */ select `t2`.`a` from `t2`)))", 746 "steps": [ 747 { 748 "transformation": "equality_propagation", 749 "subselect_evaluation": [ 750 ] /* subselect_evaluation */, 751 "resulting_condition": "(not(exists(/* select#2 */ select `t2`.`a` from `t2`)))" 752 }, 753 { 754 "transformation": "constant_propagation", 755 "subselect_evaluation": [ 756 ] /* subselect_evaluation */, 757 "resulting_condition": "(not(exists(/* select#2 */ select `t2`.`a` from `t2`)))" 758 }, 759 { 760 "transformation": "trivial_condition_removal", 761 "subselect_evaluation": [ 762 { 763 "subselect_execution": { 764 "select#": 2, 765 "steps": [ 766 { 767 "join_optimization": { 768 "select#": 2, 769 "steps": [ 770 { 771 "table_dependencies": [ 772 { 773 "table": "`t2`", 774 "row_may_be_null": false, 775 "map_bit": 0, 776 "depends_on_map_bits": [ 777 ] /* depends_on_map_bits */ 778 } 779 ] /* table_dependencies */ 780 }, 781 { 782 "rows_estimation": [ 783 { 784 "table": "`t2`", 785 "rows": 1, 786 "cost": 1, 787 "table_type": "system", 788 "empty": true 789 } 790 ] /* rows_estimation */ 791 } 792 ] /* steps */, 793 "empty_result": { 794 "cause": "no matching row in const table" 795 } /* empty_result */ 796 } /* join_optimization */ 797 }, 798 { 799 "join_execution": { 800 "select#": 2, 801 "steps": [ 802 ] /* steps */ 803 } /* join_execution */ 804 } 805 ] /* steps */ 806 } /* subselect_execution */ 807 } 808 ] /* subselect_evaluation */, 809 "resulting_condition": null 810 } 811 ] /* steps */ 812 } /* condition_processing */ 813 } 814 ] /* steps */ 815 } /* join_optimization */ 816 }, 817 { 818 "join_execution": { 819 "select#": 1, 820 "steps": [ 821 ] /* steps */ 822 } /* join_execution */ 823 } 824 ] /* steps */ 825} 0 0 826 827DROP TABLE t1,t2; 828# 829# BUG#12905521 - ASSERT IN OPT_TRACE_STMT::SYNTAX_ERROR ON SELECT 830# DISTINCT/MIN/JOIN/SUBQ QUERY 831# 832CREATE TABLE t1 ( 833pk INTEGER, 834col_int_nokey INTEGER, 835col_int_key INTEGER, 836col_varchar_key VARCHAR(1), 837col_varchar_nokey VARCHAR(1), 838PRIMARY KEY (pk), 839KEY (col_varchar_key,col_int_key) 840) ENGINE=MYISAM; 841CREATE TABLE t2 ( 842pk INTEGER, 843col_int_nokey INTEGER, 844col_int_key INTEGER, 845col_varchar_key VARCHAR(1), 846col_varchar_nokey VARCHAR(1), 847PRIMARY KEY (pk), 848KEY (col_varchar_key,col_int_key) 849) ENGINE=MYISAM; 850CREATE TABLE t3 ( 851pk INTEGER, 852col_int_nokey INTEGER, 853col_int_key INTEGER, 854col_time_key TIME, 855col_datetime_nokey DATETIME, 856col_varchar_key VARCHAR(1), 857col_varchar_nokey VARCHAR(1), 858PRIMARY KEY (pk), 859KEY (col_time_key), 860KEY (col_varchar_key,col_int_key) 861) ENGINE=MYISAM; 862CREATE TABLE t4 ( 863pk INTEGER, 864col_int_nokey INTEGER, 865col_int_key INTEGER, 866col_date_key DATE, 867col_date_nokey DATE, 868col_time_key TIME, 869col_time_nokey TIME, 870col_datetime_key DATETIME, 871col_datetime_nokey DATETIME, 872col_varchar_key VARCHAR(1), 873col_varchar_nokey VARCHAR(1), 874PRIMARY KEY (pk), 875KEY (col_varchar_key,col_int_key) 876) ENGINE=MYISAM; 877INSERT IGNORE INTO t4 ( 878col_int_key,col_int_nokey, 879col_date_key,col_date_nokey, 880col_time_key,col_time_nokey, 881col_datetime_key,col_datetime_nokey, 882col_varchar_key,col_varchar_nokey 883) VALUES 884(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'); 885Warnings: 886Warning 1364 Field 'pk' doesn't have a default value 887CREATE TABLE t5 ( 888pk INTEGER AUTO_INCREMENT, 889col_int_nokey INTEGER, 890col_int_key INTEGER, 891col_date_key DATE, 892col_date_nokey DATE, 893col_time_key TIME, 894col_time_nokey TIME, 895col_datetime_key DATETIME, 896col_datetime_nokey DATETIME, 897col_varchar_key VARCHAR(1), 898col_varchar_nokey VARCHAR(1), 899PRIMARY KEY (pk), 900KEY (col_int_key), 901KEY (col_varchar_key,col_int_key) 902) ENGINE=MYISAM; 903INSERT INTO t5 ( 904col_int_key,col_int_nokey, 905col_date_key,col_date_nokey, 906col_time_key,col_time_nokey, 907col_datetime_key,col_datetime_nokey, 908col_varchar_key,col_varchar_nokey 909) VALUES 910(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'), 911(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'), 912(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'); 913set @old_opt_switch=@@optimizer_switch; 914select distinct 915alias1.`col_varchar_key` as field1 ,alias1.`col_date_key` as 916field2 ,( select min( sq1_alias1.`col_varchar_nokey` ) as sq1_field1 from ( t1 917as sq1_alias1 inner join ( t5 as sq1_alias2 left join t5 as sq1_alias3 on 918(sq1_alias3.`col_varchar_nokey` = sq1_alias2.`col_varchar_key` ) ) on 919(sq1_alias3.`col_varchar_nokey` = sq1_alias2.`col_varchar_key` ) ) where 920exists ( select distinct c_sq1_alias2.`col_int_nokey` as c_sq1_field1 from ( 921t3 as c_sq1_alias1 right join t4 as c_sq1_alias2 on (c_sq1_alias2.`col_int_nokey` = c_sq1_alias1.`pk` ) ) where 922c_sq1_alias2.`col_varchar_key` = sq1_alias2.`col_varchar_nokey` ) ) as field3 923,( select max( sq2_alias1.`pk` ) as sq2_field1 from t5 as sq2_alias1 ) as 924field4 ,alias2.`col_varchar_nokey` as field5 ,alias2.`col_varchar_nokey` as 925field6 from ( t5 as alias1 right outer join ( ( ( select sq3_alias2.* from ( t5 as sq3_alias1 ,t4 as sq3_alias2 ) ) as alias2 right join t4 926as alias3 on (alias3.`col_varchar_key` = alias2.`col_varchar_key` ) ) ) on 927(alias3.`col_int_key` = alias2.`pk` ) ) where ( alias1.`col_varchar_nokey` in 928( select sq4_alias1.`col_varchar_key` as sq4_field1 from ( t3 as sq4_alias1 929inner join ( t2 as sq4_alias2 right outer join t3 as sq4_alias3 on 930(sq4_alias3.`pk` = sq4_alias2.`col_int_key` ) ) on 931(sq4_alias3.`col_varchar_nokey` = sq4_alias2.`col_varchar_key` ) ) where 932sq4_alias2.`col_int_key` < alias1.`col_int_nokey` and 933sq4_alias3.`col_varchar_nokey` <> alias1.`col_varchar_key` ) ) and 934alias1.`col_int_key` not in (214) group by field1,field2,field3, 935field4,field5,field6; 936field1 field2 field3 field4 field5 field6 937select * from information_schema.optimizer_trace; 938QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES 939select distinct 940alias1.`col_varchar_key` as field1 ,alias1.`col_date_key` as 941field2 ,( select min( sq1_alias1.`col_varchar_nokey` ) as sq1_field1 from ( t1 942as sq1_alias1 inner join ( t5 as sq1_alias2 left join t5 as sq1_alias3 on 943(sq1_alias3.`col_varchar_nokey` = sq1_alias2.`col_varchar_key` ) ) on 944(sq1_alias3.`col_varchar_nokey` = sq1_alias2.`col_varchar_key` ) ) where 945exists ( select distinct c_sq1_alias2.`col_int_nokey` as c_sq1_field1 from ( 946t3 as c_sq1_alias1 right join t4 as c_sq1_alias2 on (c_sq1_alias2.`col_int_nokey` = c_sq1_alias1.`pk` ) ) where 947c_sq1_alias2.`col_varchar_key` = sq1_alias2.`col_varchar_nokey` ) ) as field3 948,( select max( sq2_alias1.`pk` ) as sq2_field1 from t5 as sq2_alias1 ) as 949field4 ,alias2.`col_varchar_nokey` as field5 ,alias2.`col_varchar_nokey` as 950field6 from ( t5 as alias1 right outer join ( ( ( select sq3_alias2.* from ( t5 as sq3_alias1 ,t4 as sq3_alias2 ) ) as alias2 right join t4 951as alias3 on (alias3.`col_varchar_key` = alias2.`col_varchar_key` ) ) ) on 952(alias3.`col_int_key` = alias2.`pk` ) ) where ( alias1.`col_varchar_nokey` in 953( select sq4_alias1.`col_varchar_key` as sq4_field1 from ( t3 as sq4_alias1 954inner join ( t2 as sq4_alias2 right outer join t3 as sq4_alias3 on 955(sq4_alias3.`pk` = sq4_alias2.`col_int_key` ) ) on 956(sq4_alias3.`col_varchar_nokey` = sq4_alias2.`col_varchar_key` ) ) where 957sq4_alias2.`col_int_key` < alias1.`col_int_nokey` and 958sq4_alias3.`col_varchar_nokey` <> alias1.`col_varchar_key` ) ) and 959alias1.`col_int_key` not in (214) group by field1,field2,field3, 960field4,field5,field6 { 961 "steps": [ 962 { 963 "join_preparation": { 964 "select#": 1, 965 "steps": [ 966 { 967 "join_preparation": { 968 "select#": 5, 969 "steps": [ 970 { 971 "expanded_query": "/* select#5 */ select `sq3_alias2`.`pk` AS `pk`,`sq3_alias2`.`col_int_nokey` AS `col_int_nokey`,`sq3_alias2`.`col_int_key` AS `col_int_key`,`sq3_alias2`.`col_date_key` AS `col_date_key`,`sq3_alias2`.`col_date_nokey` AS `col_date_nokey`,`sq3_alias2`.`col_time_key` AS `col_time_key`,`sq3_alias2`.`col_time_nokey` AS `col_time_nokey`,`sq3_alias2`.`col_datetime_key` AS `col_datetime_key`,`sq3_alias2`.`col_datetime_nokey` AS `col_datetime_nokey`,`sq3_alias2`.`col_varchar_key` AS `col_varchar_key`,`sq3_alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from (`t5` `sq3_alias1` join `t4` `sq3_alias2`)" 972 } 973 ] /* steps */ 974 } /* join_preparation */ 975 }, 976 { 977 "derived": { 978 "table": "``.`` `alias2`", 979 "select#": 5, 980 "merged": true 981 } /* derived */ 982 }, 983 { 984 "join_preparation": { 985 "select#": 2, 986 "steps": [ 987 { 988 "join_preparation": { 989 "select#": 3, 990 "steps": [ 991 { 992 "transformations_to_subquery": [ 993 "removed_distinct" 994 ] /* transformations_to_subquery */ 995 }, 996 { 997 "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`)" 998 } 999 ] /* steps */ 1000 } /* join_preparation */ 1001 }, 1002 { 1003 "expanded_query": "/* select#2 */ select min(`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from (`t1` `sq1_alias1` join (`t5` `sq1_alias2` left join `t5` `sq1_alias3` on((`sq1_alias3`.`col_varchar_nokey` = `sq1_alias2`.`col_varchar_key`))) on((`sq1_alias3`.`col_varchar_nokey` = `sq1_alias2`.`col_varchar_key`))) where exists(/* select#3 */ select `c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`t4` `c_sq1_alias2` left join `t3` `c_sq1_alias1` on((`c_sq1_alias2`.`col_int_nokey` = `c_sq1_alias1`.`pk`))) where (`c_sq1_alias2`.`col_varchar_key` = `sq1_alias2`.`col_varchar_nokey`))" 1004 } 1005 ] /* steps */ 1006 } /* join_preparation */ 1007 }, 1008 { 1009 "join_preparation": { 1010 "select#": 4, 1011 "steps": [ 1012 { 1013 "expanded_query": "/* select#4 */ select max(`sq2_alias1`.`pk`) AS `sq2_field1` from `t5` `sq2_alias1`" 1014 } 1015 ] /* steps */ 1016 } /* join_preparation */ 1017 }, 1018 { 1019 "join_preparation": { 1020 "select#": 6, 1021 "steps": [ 1022 { 1023 "expanded_query": "/* select#6 */ select `sq4_alias1`.`col_varchar_key` AS `sq4_field1` from (`t3` `sq4_alias1` join (`t3` `sq4_alias3` left join `t2` `sq4_alias2` on((`sq4_alias3`.`pk` = `sq4_alias2`.`col_int_key`))) on((`sq4_alias3`.`col_varchar_nokey` = `sq4_alias2`.`col_varchar_key`))) where ((`sq4_alias2`.`col_int_key` < `alias1`.`col_int_nokey`) and (`sq4_alias3`.`col_varchar_nokey` <> `alias1`.`col_varchar_key`))" 1024 }, 1025 { 1026 "transformation": { 1027 "select#": 6, 1028 "from": "IN (SELECT)", 1029 "to": "semijoin", 1030 "chosen": false 1031 } /* transformation */ 1032 }, 1033 { 1034 "transformation": { 1035 "select#": 6, 1036 "from": "IN (SELECT)", 1037 "to": "EXISTS (CORRELATED SELECT)", 1038 "chosen": true, 1039 "evaluating_constant_where_conditions": [ 1040 ] /* evaluating_constant_where_conditions */ 1041 } /* transformation */ 1042 } 1043 ] /* steps */ 1044 } /* join_preparation */ 1045 }, 1046 { 1047 "expanded_query": "/* select#1 */ select `alias1`.`col_varchar_key` AS `field1`,`alias1`.`col_date_key` AS `field2`,(/* select#2 */ select min(`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from (`t1` `sq1_alias1` join (`t5` `sq1_alias2` left join `t5` `sq1_alias3` on((`sq1_alias3`.`col_varchar_nokey` = `sq1_alias2`.`col_varchar_key`))) on((`sq1_alias3`.`col_varchar_nokey` = `sq1_alias2`.`col_varchar_key`))) where exists(/* select#3 */ select `c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`t4` `c_sq1_alias2` left join `t3` `c_sq1_alias1` on((`c_sq1_alias2`.`col_int_nokey` = `c_sq1_alias1`.`pk`))) where (`c_sq1_alias2`.`col_varchar_key` = `sq1_alias2`.`col_varchar_nokey`))) AS `field3`,(/* select#4 */ select max(`sq2_alias1`.`pk`) AS `sq2_field1` from `t5` `sq2_alias1`) AS `field4`,`sq3_alias2`.`col_varchar_nokey` AS `field5`,`sq3_alias2`.`col_varchar_nokey` AS `field6` from ((`t4` `alias3` left join ((`t5` `sq3_alias1` join `t4` `sq3_alias2`)) on((`alias3`.`col_varchar_key` = `sq3_alias2`.`col_varchar_key`))) left join `t5` `alias1` on((`alias3`.`col_int_key` = `sq3_alias2`.`pk`))) where (<in_optimizer>(`alias1`.`col_varchar_nokey`,<exists>(/* select#6 */ select `sq4_alias1`.`col_varchar_key` AS `sq4_field1` from (`t3` `sq4_alias1` join (`t3` `sq4_alias3` left join `t2` `sq4_alias2` on((`sq4_alias3`.`pk` = `sq4_alias2`.`col_int_key`))) on((`sq4_alias3`.`col_varchar_nokey` = `sq4_alias2`.`col_varchar_key`))) where ((`sq4_alias2`.`col_int_key` < `alias1`.`col_int_nokey`) and (`sq4_alias3`.`col_varchar_nokey` <> `alias1`.`col_varchar_key`) and (<cache>(`alias1`.`col_varchar_nokey`) = `sq4_alias1`.`col_varchar_key`)))) and (`alias1`.`col_int_key` <> 214)) group by `field1`,`field2`,`field3`,`field4`,`field5`,`field6`" 1048 }, 1049 { 1050 "transformations_to_nested_joins": { 1051 "transformations": [ 1052 "outer_join_to_inner_join", 1053 "JOIN_condition_to_WHERE", 1054 "parenthesis_removal" 1055 ] /* transformations */, 1056 "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`))" 1057 } /* transformations_to_nested_joins */ 1058 }, 1059 { 1060 "transformations_to_nested_joins": { 1061 "transformations": [ 1062 "parenthesis_removal" 1063 ] /* transformations */, 1064 "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`)" 1065 } /* transformations_to_nested_joins */ 1066 }, 1067 { 1068 "transformations_to_nested_joins": { 1069 "transformations": [ 1070 "outer_join_to_inner_join", 1071 "JOIN_condition_to_WHERE", 1072 "parenthesis_removal" 1073 ] /* transformations */, 1074 "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`))" 1075 } /* transformations_to_nested_joins */ 1076 }, 1077 { 1078 "transformations_to_nested_joins": { 1079 "transformations": [ 1080 "outer_join_to_inner_join", 1081 "JOIN_condition_to_WHERE", 1082 "parenthesis_removal" 1083 ] /* transformations */, 1084 "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`" 1085 } /* transformations_to_nested_joins */ 1086 } 1087 ] /* steps */ 1088 } /* join_preparation */ 1089 }, 1090 { 1091 "join_optimization": { 1092 "select#": 1, 1093 "steps": [ 1094 { 1095 "condition_processing": { 1096 "condition": "WHERE", 1097 "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`))", 1098 "steps": [ 1099 { 1100 "transformation": "equality_propagation", 1101 "subselect_evaluation": [ 1102 ] /* subselect_evaluation */, 1103 "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`))" 1104 }, 1105 { 1106 "transformation": "constant_propagation", 1107 "subselect_evaluation": [ 1108 ] /* subselect_evaluation */, 1109 "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`))" 1110 }, 1111 { 1112 "transformation": "trivial_condition_removal", 1113 "subselect_evaluation": [ 1114 ] /* subselect_evaluation */, 1115 "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`))" 1116 } 1117 ] /* steps */ 1118 } /* condition_processing */ 1119 }, 1120 { 1121 "substitute_generated_columns": { 1122 } /* substitute_generated_columns */ 1123 }, 1124 { 1125 "table_dependencies": [ 1126 { 1127 "table": "`t5` `alias1`", 1128 "row_may_be_null": true, 1129 "map_bit": 0, 1130 "depends_on_map_bits": [ 1131 ] /* depends_on_map_bits */ 1132 }, 1133 { 1134 "table": "`t5` `sq3_alias1`", 1135 "row_may_be_null": true, 1136 "map_bit": 1, 1137 "depends_on_map_bits": [ 1138 ] /* depends_on_map_bits */ 1139 }, 1140 { 1141 "table": "`t4` `sq3_alias2`", 1142 "row_may_be_null": true, 1143 "map_bit": 2, 1144 "depends_on_map_bits": [ 1145 ] /* depends_on_map_bits */ 1146 }, 1147 { 1148 "table": "`t4` `alias3`", 1149 "row_may_be_null": false, 1150 "map_bit": 3, 1151 "depends_on_map_bits": [ 1152 ] /* depends_on_map_bits */ 1153 } 1154 ] /* table_dependencies */ 1155 }, 1156 { 1157 "ref_optimizer_key_uses": [ 1158 { 1159 "table": "`t4` `sq3_alias2`", 1160 "field": "pk", 1161 "equals": "`alias3`.`col_int_key`", 1162 "null_rejecting": true 1163 }, 1164 { 1165 "table": "`t4` `sq3_alias2`", 1166 "field": "col_varchar_key", 1167 "equals": "`alias3`.`col_varchar_key`", 1168 "null_rejecting": true 1169 }, 1170 { 1171 "table": "`t4` `alias3`", 1172 "field": "col_varchar_key", 1173 "equals": "`sq3_alias2`.`col_varchar_key`", 1174 "null_rejecting": true 1175 }, 1176 { 1177 "table": "`t4` `alias3`", 1178 "field": "col_int_key", 1179 "equals": "`sq3_alias2`.`pk`", 1180 "null_rejecting": true 1181 } 1182 ] /* ref_optimizer_key_uses */ 1183 }, 1184 { 1185 "rows_estimation": [ 1186 { 1187 "table": "`t5` `alias1`", 1188 "range_analysis": { 1189 "table_scan": { 1190 "rows": 3, 1191 "cost": 4.7342 1192 } /* table_scan */, 1193 "potential_range_indexes": [ 1194 { 1195 "index": "PRIMARY", 1196 "usable": false, 1197 "cause": "not_applicable" 1198 }, 1199 { 1200 "index": "col_int_key", 1201 "usable": true, 1202 "key_parts": [ 1203 "col_int_key" 1204 ] /* key_parts */ 1205 }, 1206 { 1207 "index": "col_varchar_key", 1208 "usable": false, 1209 "cause": "not_applicable" 1210 } 1211 ] /* potential_range_indexes */, 1212 "setup_range_conditions": [ 1213 ] /* setup_range_conditions */, 1214 "impossible_range": true 1215 } /* range_analysis */, 1216 "rows": 0, 1217 "cause": "impossible_where_condition" 1218 }, 1219 { 1220 "table": "`t5` `sq3_alias1`", 1221 "table_scan": { 1222 "rows": 3, 1223 "cost": 2 1224 } /* table_scan */ 1225 }, 1226 { 1227 "table": "`t4` `sq3_alias2`", 1228 "rows": 1, 1229 "cost": 1, 1230 "table_type": "system", 1231 "empty": false 1232 }, 1233 { 1234 "table": "`t4` `alias3`", 1235 "rows": 1, 1236 "cost": 1, 1237 "table_type": "system", 1238 "empty": false 1239 } 1240 ] /* rows_estimation */ 1241 }, 1242 { 1243 "considered_execution_plans": [ 1244 { 1245 "plan_prefix": [ 1246 "`t4` `sq3_alias2`", 1247 "`t4` `alias3`", 1248 "`t5` `alias1`" 1249 ] /* plan_prefix */, 1250 "table": "`t5` `sq3_alias1`", 1251 "best_access_path": { 1252 "considered_access_paths": [ 1253 { 1254 "rows_to_scan": 3, 1255 "access_type": "scan", 1256 "resulting_rows": 3, 1257 "cost": 2.6342, 1258 "chosen": true 1259 } 1260 ] /* considered_access_paths */ 1261 } /* best_access_path */, 1262 "condition_filtering_pct": 100, 1263 "rows_for_plan": 3, 1264 "cost_for_plan": 2.6342, 1265 "chosen": true 1266 } 1267 ] /* considered_execution_plans */ 1268 } 1269 ] /* steps */, 1270 "empty_result": { 1271 "cause": "no matching row in const table" 1272 } /* empty_result */ 1273 } /* join_optimization */ 1274 }, 1275 { 1276 "join_optimization": { 1277 "select#": 6, 1278 "steps": [ 1279 { 1280 "condition_processing": { 1281 "condition": "WHERE", 1282 "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`))", 1283 "steps": [ 1284 { 1285 "transformation": "equality_propagation", 1286 "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`))" 1287 }, 1288 { 1289 "transformation": "constant_propagation", 1290 "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`))" 1291 }, 1292 { 1293 "transformation": "trivial_condition_removal", 1294 "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`))" 1295 } 1296 ] /* steps */ 1297 } /* condition_processing */ 1298 }, 1299 { 1300 "substitute_generated_columns": { 1301 } /* substitute_generated_columns */ 1302 }, 1303 { 1304 "table_dependencies": [ 1305 { 1306 "table": "`t3` `sq4_alias1`", 1307 "row_may_be_null": false, 1308 "map_bit": 0, 1309 "depends_on_map_bits": [ 1310 ] /* depends_on_map_bits */ 1311 }, 1312 { 1313 "table": "`t2` `sq4_alias2`", 1314 "row_may_be_null": true, 1315 "map_bit": 1, 1316 "depends_on_map_bits": [ 1317 ] /* depends_on_map_bits */ 1318 }, 1319 { 1320 "table": "`t3` `sq4_alias3`", 1321 "row_may_be_null": false, 1322 "map_bit": 2, 1323 "depends_on_map_bits": [ 1324 ] /* depends_on_map_bits */ 1325 } 1326 ] /* table_dependencies */ 1327 }, 1328 { 1329 "ref_optimizer_key_uses": [ 1330 { 1331 "table": "`t3` `sq4_alias1`", 1332 "field": "col_varchar_key", 1333 "equals": "<cache>(`alias1`.`col_varchar_nokey`)", 1334 "null_rejecting": false 1335 }, 1336 { 1337 "table": "`t2` `sq4_alias2`", 1338 "field": "col_varchar_key", 1339 "equals": "`sq4_alias3`.`col_varchar_nokey`", 1340 "null_rejecting": true 1341 }, 1342 { 1343 "table": "`t2` `sq4_alias2`", 1344 "field": "col_int_key", 1345 "equals": "`sq4_alias3`.`pk`", 1346 "null_rejecting": false 1347 }, 1348 { 1349 "table": "`t3` `sq4_alias3`", 1350 "field": "pk", 1351 "equals": "`sq4_alias2`.`col_int_key`", 1352 "null_rejecting": true 1353 } 1354 ] /* ref_optimizer_key_uses */ 1355 }, 1356 { 1357 "rows_estimation": [ 1358 { 1359 "table": "`t3` `sq4_alias1`", 1360 "rows": 1, 1361 "cost": 1, 1362 "table_type": "system", 1363 "empty": true 1364 }, 1365 { 1366 "table": "`t2` `sq4_alias2`", 1367 "rows": 1, 1368 "cost": 1, 1369 "table_type": "system", 1370 "empty": true 1371 }, 1372 { 1373 "table": "`t3` `sq4_alias3`", 1374 "rows": 1, 1375 "cost": 1, 1376 "table_type": "system", 1377 "empty": true 1378 } 1379 ] /* rows_estimation */ 1380 }, 1381 { 1382 "transformation": { 1383 "select#": 6, 1384 "from": "IN (SELECT)", 1385 "to": "materialization", 1386 "possible": false, 1387 "cause": "correlated" 1388 } /* transformation */ 1389 }, 1390 { 1391 "transformation": { 1392 "select#": 6, 1393 "from": "IN (SELECT)", 1394 "to": "EXISTS (CORRELATED SELECT)", 1395 "put_1_in_SELECT_list": true 1396 } /* transformation */ 1397 } 1398 ] /* steps */, 1399 "empty_result": { 1400 "cause": "no matching row in const table" 1401 } /* empty_result */ 1402 } /* join_optimization */ 1403 }, 1404 { 1405 "join_optimization": { 1406 "select#": 4, 1407 "steps": [ 1408 ] /* steps */, 1409 "empty_result": { 1410 "cause": "Select tables optimized away" 1411 } /* empty_result */ 1412 } /* join_optimization */ 1413 }, 1414 { 1415 "join_optimization": { 1416 "select#": 2, 1417 "steps": [ 1418 { 1419 "condition_processing": { 1420 "condition": "WHERE", 1421 "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`))", 1422 "steps": [ 1423 { 1424 "transformation": "equality_propagation", 1425 "subselect_evaluation": [ 1426 ] /* subselect_evaluation */, 1427 "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`))" 1428 }, 1429 { 1430 "transformation": "constant_propagation", 1431 "subselect_evaluation": [ 1432 ] /* subselect_evaluation */, 1433 "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`))" 1434 }, 1435 { 1436 "transformation": "trivial_condition_removal", 1437 "subselect_evaluation": [ 1438 ] /* subselect_evaluation */, 1439 "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`))" 1440 } 1441 ] /* steps */ 1442 } /* condition_processing */ 1443 }, 1444 { 1445 "substitute_generated_columns": { 1446 } /* substitute_generated_columns */ 1447 }, 1448 { 1449 "table_dependencies": [ 1450 { 1451 "table": "`t1` `sq1_alias1`", 1452 "row_may_be_null": false, 1453 "map_bit": 0, 1454 "depends_on_map_bits": [ 1455 ] /* depends_on_map_bits */ 1456 }, 1457 { 1458 "table": "`t5` `sq1_alias2`", 1459 "row_may_be_null": false, 1460 "map_bit": 1, 1461 "depends_on_map_bits": [ 1462 ] /* depends_on_map_bits */ 1463 }, 1464 { 1465 "table": "`t5` `sq1_alias3`", 1466 "row_may_be_null": true, 1467 "map_bit": 2, 1468 "depends_on_map_bits": [ 1469 ] /* depends_on_map_bits */ 1470 } 1471 ] /* table_dependencies */ 1472 }, 1473 { 1474 "ref_optimizer_key_uses": [ 1475 { 1476 "table": "`t5` `sq1_alias2`", 1477 "field": "col_varchar_key", 1478 "equals": "`sq1_alias3`.`col_varchar_nokey`", 1479 "null_rejecting": true 1480 } 1481 ] /* ref_optimizer_key_uses */ 1482 }, 1483 { 1484 "rows_estimation": [ 1485 { 1486 "table": "`t1` `sq1_alias1`", 1487 "rows": 1, 1488 "cost": 1, 1489 "table_type": "system", 1490 "empty": true 1491 }, 1492 { 1493 "table": "`t5` `sq1_alias2`", 1494 "table_scan": { 1495 "rows": 3, 1496 "cost": 2 1497 } /* table_scan */ 1498 }, 1499 { 1500 "table": "`t5` `sq1_alias3`", 1501 "table_scan": { 1502 "rows": 3, 1503 "cost": 2 1504 } /* table_scan */ 1505 } 1506 ] /* rows_estimation */ 1507 }, 1508 { 1509 "considered_execution_plans": [ 1510 { 1511 "plan_prefix": [ 1512 "`t1` `sq1_alias1`" 1513 ] /* plan_prefix */, 1514 "table": "`t5` `sq1_alias3`", 1515 "best_access_path": { 1516 "considered_access_paths": [ 1517 { 1518 "rows_to_scan": 3, 1519 "access_type": "scan", 1520 "resulting_rows": 3, 1521 "cost": 2.6342, 1522 "chosen": true 1523 } 1524 ] /* considered_access_paths */ 1525 } /* best_access_path */, 1526 "condition_filtering_pct": 100, 1527 "rows_for_plan": 3, 1528 "cost_for_plan": 2.6342, 1529 "rest_of_plan": [ 1530 { 1531 "plan_prefix": [ 1532 "`t1` `sq1_alias1`", 1533 "`t5` `sq1_alias3`" 1534 ] /* plan_prefix */, 1535 "table": "`t5` `sq1_alias2`", 1536 "best_access_path": { 1537 "considered_access_paths": [ 1538 { 1539 "access_type": "ref", 1540 "index": "col_varchar_key", 1541 "rows": 2, 1542 "cost": 7.2, 1543 "chosen": true 1544 }, 1545 { 1546 "rows_to_scan": 3, 1547 "access_type": "scan", 1548 "using_join_cache": true, 1549 "buffers_needed": 1, 1550 "resulting_rows": 3, 1551 "cost": 3.8343, 1552 "chosen": true 1553 } 1554 ] /* considered_access_paths */ 1555 } /* best_access_path */, 1556 "condition_filtering_pct": 33.333, 1557 "rows_for_plan": 3, 1558 "cost_for_plan": 6.4685, 1559 "chosen": true 1560 } 1561 ] /* rest_of_plan */ 1562 }, 1563 { 1564 "plan_prefix": [ 1565 "`t1` `sq1_alias1`" 1566 ] /* plan_prefix */, 1567 "table": "`t5` `sq1_alias2`", 1568 "best_access_path": { 1569 "considered_access_paths": [ 1570 { 1571 "access_type": "ref", 1572 "index": "col_varchar_key", 1573 "usable": false, 1574 "chosen": false 1575 }, 1576 { 1577 "rows_to_scan": 3, 1578 "access_type": "scan", 1579 "resulting_rows": 3, 1580 "cost": 2.6342, 1581 "chosen": true 1582 } 1583 ] /* considered_access_paths */ 1584 } /* best_access_path */, 1585 "condition_filtering_pct": 100, 1586 "rows_for_plan": 3, 1587 "cost_for_plan": 2.6342, 1588 "pruned_by_heuristic": true 1589 } 1590 ] /* considered_execution_plans */ 1591 } 1592 ] /* steps */, 1593 "empty_result": { 1594 "cause": "no matching row in const table" 1595 } /* empty_result */ 1596 } /* join_optimization */ 1597 }, 1598 { 1599 "join_optimization": { 1600 "select#": 3, 1601 "steps": [ 1602 { 1603 "condition_processing": { 1604 "condition": "WHERE", 1605 "original_condition": "(`c_sq1_alias2`.`col_varchar_key` = `sq1_alias2`.`col_varchar_nokey`)", 1606 "steps": [ 1607 { 1608 "transformation": "equality_propagation", 1609 "resulting_condition": "(`c_sq1_alias2`.`col_varchar_key` = `sq1_alias2`.`col_varchar_nokey`)" 1610 }, 1611 { 1612 "transformation": "constant_propagation", 1613 "resulting_condition": "(`c_sq1_alias2`.`col_varchar_key` = `sq1_alias2`.`col_varchar_nokey`)" 1614 }, 1615 { 1616 "transformation": "trivial_condition_removal", 1617 "resulting_condition": "(`c_sq1_alias2`.`col_varchar_key` = `sq1_alias2`.`col_varchar_nokey`)" 1618 } 1619 ] /* steps */ 1620 } /* condition_processing */ 1621 }, 1622 { 1623 "substitute_generated_columns": { 1624 } /* substitute_generated_columns */ 1625 }, 1626 { 1627 "table_dependencies": [ 1628 { 1629 "table": "`t3` `c_sq1_alias1`", 1630 "row_may_be_null": true, 1631 "map_bit": 0, 1632 "depends_on_map_bits": [ 1633 1 1634 ] /* depends_on_map_bits */ 1635 }, 1636 { 1637 "table": "`t4` `c_sq1_alias2`", 1638 "row_may_be_null": false, 1639 "map_bit": 1, 1640 "depends_on_map_bits": [ 1641 ] /* depends_on_map_bits */ 1642 } 1643 ] /* table_dependencies */ 1644 }, 1645 { 1646 "ref_optimizer_key_uses": [ 1647 { 1648 "table": "`t3` `c_sq1_alias1`", 1649 "field": "pk", 1650 "equals": "`c_sq1_alias2`.`col_int_nokey`", 1651 "null_rejecting": true 1652 }, 1653 { 1654 "table": "`t4` `c_sq1_alias2`", 1655 "field": "col_varchar_key", 1656 "equals": "`sq1_alias2`.`col_varchar_nokey`", 1657 "null_rejecting": true 1658 } 1659 ] /* ref_optimizer_key_uses */ 1660 }, 1661 { 1662 "rows_estimation": [ 1663 { 1664 "table": "`t3` `c_sq1_alias1`", 1665 "rows": 1, 1666 "cost": 1, 1667 "table_type": "system", 1668 "empty": true 1669 }, 1670 { 1671 "table": "`t4` `c_sq1_alias2`", 1672 "rows": 1, 1673 "cost": 1, 1674 "table_type": "system", 1675 "empty": false 1676 } 1677 ] /* rows_estimation */ 1678 }, 1679 { 1680 "attaching_conditions_to_tables": { 1681 "original_condition": "('g' = `sq1_alias2`.`col_varchar_nokey`)", 1682 "attached_conditions_computation": [ 1683 ] /* attached_conditions_computation */, 1684 "attached_conditions_summary": [ 1685 ] /* attached_conditions_summary */ 1686 } /* attaching_conditions_to_tables */ 1687 }, 1688 { 1689 "refine_plan": [ 1690 ] /* refine_plan */ 1691 } 1692 ] /* steps */ 1693 } /* join_optimization */ 1694 }, 1695 { 1696 "join_execution": { 1697 "select#": 1, 1698 "steps": [ 1699 ] /* steps */ 1700 } /* join_execution */ 1701 } 1702 ] /* steps */ 1703} 0 0 1704set optimizer_switch=@old_opt_switch; 1705drop table t1,t2,t3,t4,t5; 1706# 1707# BUG#12905758 - ASSERT IN OPT_TRACE_STMT::SYNTAX_ERROR ON 1708# SELECT/SUBQ/SUM QUERY 1709# 1710CREATE TABLE t1 ( 1711pk INTEGER AUTO_INCREMENT, 1712col_int_nokey INTEGER, 1713col_int_key INTEGER, 1714col_date_key DATE, 1715col_date_nokey DATE, 1716col_time_key TIME, 1717col_time_nokey TIME, 1718col_datetime_key DATETIME, 1719col_datetime_nokey DATETIME, 1720col_varchar_key VARCHAR(1), 1721col_varchar_nokey VARCHAR(1), 1722PRIMARY KEY (pk), 1723KEY (col_varchar_key,col_int_key) 1724) ENGINE=MYISAM; 1725INSERT INTO t1 ( 1726col_int_key,col_int_nokey, 1727col_date_key,col_date_nokey, 1728col_time_key,col_time_nokey, 1729col_datetime_key,col_datetime_nokey, 1730col_varchar_key,col_varchar_nokey 1731) VALUES 1732(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'), 1733(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'); 1734CREATE TABLE t2 (I INTEGER); 1735select ( select sum( subquery1_t1.`col_int_nokey` ) as subquery1_field1 from 1736t1 as subquery1_t1 ) as field1 from ( t1 as table1 straight_join t1 as table2 1737on (table2.`col_varchar_key` = table1.`col_varchar_key` ) ) where ( 1738table2.`col_int_nokey` <> any ( select 5 from t2 ) ) and table1.`pk` in 1739(192,18) order by field1 desc; 1740field1 1741select * from information_schema.optimizer_trace; 1742QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES 1743select ( select sum( subquery1_t1.`col_int_nokey` ) as subquery1_field1 from 1744t1 as subquery1_t1 ) as field1 from ( t1 as table1 straight_join t1 as table2 1745on (table2.`col_varchar_key` = table1.`col_varchar_key` ) ) where ( 1746table2.`col_int_nokey` <> any ( select 5 from t2 ) ) and table1.`pk` in 1747(192,18) order by field1 desc { 1748 "steps": [ 1749 { 1750 "join_preparation": { 1751 "select#": 1, 1752 "steps": [ 1753 { 1754 "join_preparation": { 1755 "select#": 2, 1756 "steps": [ 1757 { 1758 "expanded_query": "/* select#2 */ select sum(`subquery1_t1`.`col_int_nokey`) AS `subquery1_field1` from `t1` `subquery1_t1`" 1759 } 1760 ] /* steps */ 1761 } /* join_preparation */ 1762 }, 1763 { 1764 "join_preparation": { 1765 "select#": 3, 1766 "steps": [ 1767 { 1768 "expanded_query": "/* select#3 */ select 5 from `t2`" 1769 }, 1770 { 1771 "transformation": { 1772 "select#": 3, 1773 "from": "IN (SELECT)", 1774 "to": "EXISTS (CORRELATED SELECT)", 1775 "chosen": true, 1776 "evaluating_constant_where_conditions": [ 1777 ] /* evaluating_constant_where_conditions */ 1778 } /* transformation */ 1779 } 1780 ] /* steps */ 1781 } /* join_preparation */ 1782 }, 1783 { 1784 "IN_uses_bisection": true 1785 }, 1786 { 1787 "expanded_query": "/* select#1 */ select (/* select#2 */ select sum(`subquery1_t1`.`col_int_nokey`) AS `subquery1_field1` from `t1` `subquery1_t1`) AS `field1` from (`t1` `table1` straight_join `t1` `table2` on((`table2`.`col_varchar_key` = `table1`.`col_varchar_key`))) where (<nop>(<in_optimizer>(`table2`.`col_int_nokey`,<exists>(/* select#3 */ select 5 from `t2` where <if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)))) and (`table1`.`pk` in (192,18))) order by `field1` desc" 1788 }, 1789 { 1790 "transformations_to_nested_joins": { 1791 "transformations": [ 1792 "JOIN_condition_to_WHERE", 1793 "parenthesis_removal" 1794 ] /* transformations */, 1795 "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" 1796 } /* transformations_to_nested_joins */ 1797 } 1798 ] /* steps */ 1799 } /* join_preparation */ 1800 }, 1801 { 1802 "join_optimization": { 1803 "select#": 1, 1804 "steps": [ 1805 { 1806 "condition_processing": { 1807 "condition": "WHERE", 1808 "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`))", 1809 "steps": [ 1810 { 1811 "transformation": "equality_propagation", 1812 "subselect_evaluation": [ 1813 ] /* subselect_evaluation */, 1814 "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`))" 1815 }, 1816 { 1817 "transformation": "constant_propagation", 1818 "subselect_evaluation": [ 1819 ] /* subselect_evaluation */, 1820 "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`))" 1821 }, 1822 { 1823 "transformation": "trivial_condition_removal", 1824 "subselect_evaluation": [ 1825 ] /* subselect_evaluation */, 1826 "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`))" 1827 } 1828 ] /* steps */ 1829 } /* condition_processing */ 1830 }, 1831 { 1832 "substitute_generated_columns": { 1833 } /* substitute_generated_columns */ 1834 }, 1835 { 1836 "table_dependencies": [ 1837 { 1838 "table": "`t1` `table1`", 1839 "row_may_be_null": false, 1840 "map_bit": 0, 1841 "depends_on_map_bits": [ 1842 ] /* depends_on_map_bits */ 1843 }, 1844 { 1845 "table": "`t1` `table2`", 1846 "row_may_be_null": false, 1847 "map_bit": 1, 1848 "depends_on_map_bits": [ 1849 0 1850 ] /* depends_on_map_bits */ 1851 } 1852 ] /* table_dependencies */ 1853 }, 1854 { 1855 "ref_optimizer_key_uses": [ 1856 { 1857 "table": "`t1` `table1`", 1858 "field": "col_varchar_key", 1859 "equals": "`table2`.`col_varchar_key`", 1860 "null_rejecting": true 1861 }, 1862 { 1863 "table": "`t1` `table2`", 1864 "field": "col_varchar_key", 1865 "equals": "`table1`.`col_varchar_key`", 1866 "null_rejecting": true 1867 } 1868 ] /* ref_optimizer_key_uses */ 1869 }, 1870 { 1871 "rows_estimation": [ 1872 { 1873 "table": "`t1` `table1`", 1874 "range_analysis": { 1875 "table_scan": { 1876 "rows": 2, 1877 "cost": 4.5225 1878 } /* table_scan */, 1879 "potential_range_indexes": [ 1880 { 1881 "index": "PRIMARY", 1882 "usable": true, 1883 "key_parts": [ 1884 "pk" 1885 ] /* key_parts */ 1886 }, 1887 { 1888 "index": "col_varchar_key", 1889 "usable": false, 1890 "cause": "not_applicable" 1891 } 1892 ] /* potential_range_indexes */, 1893 "setup_range_conditions": [ 1894 ] /* setup_range_conditions */, 1895 "group_index_range": { 1896 "chosen": false, 1897 "cause": "not_single_table" 1898 } /* group_index_range */, 1899 "analyzing_range_alternatives": { 1900 "range_scan_alternatives": [ 1901 { 1902 "index": "PRIMARY", 1903 "ranges": [ 1904 "18 <= pk <= 18", 1905 "192 <= pk <= 192" 1906 ] /* ranges */, 1907 "index_dives_for_eq_ranges": true, 1908 "rowid_ordered": false, 1909 "using_mrr": false, 1910 "index_only": false, 1911 "rows": 2, 1912 "cost": 4.41, 1913 "chosen": true 1914 } 1915 ] /* range_scan_alternatives */, 1916 "analyzing_roworder_intersect": { 1917 "usable": false, 1918 "cause": "too_few_roworder_scans" 1919 } /* analyzing_roworder_intersect */ 1920 } /* analyzing_range_alternatives */, 1921 "chosen_range_access_summary": { 1922 "range_access_plan": { 1923 "type": "range_scan", 1924 "index": "PRIMARY", 1925 "rows": 2, 1926 "ranges": [ 1927 "18 <= pk <= 18", 1928 "192 <= pk <= 192" 1929 ] /* ranges */ 1930 } /* range_access_plan */, 1931 "rows_for_plan": 2, 1932 "cost_for_plan": 4.41, 1933 "chosen": true 1934 } /* chosen_range_access_summary */ 1935 } /* range_analysis */ 1936 }, 1937 { 1938 "table": "`t1` `table2`", 1939 "table_scan": { 1940 "rows": 2, 1941 "cost": 2 1942 } /* table_scan */ 1943 } 1944 ] /* rows_estimation */ 1945 }, 1946 { 1947 "considered_execution_plans": [ 1948 { 1949 "plan_prefix": [ 1950 ] /* plan_prefix */, 1951 "table": "`t1` `table1`", 1952 "best_access_path": { 1953 "considered_access_paths": [ 1954 { 1955 "access_type": "ref", 1956 "index": "col_varchar_key", 1957 "usable": false, 1958 "chosen": false 1959 }, 1960 { 1961 "rows_to_scan": 2, 1962 "access_type": "range", 1963 "range_details": { 1964 "used_index": "PRIMARY" 1965 } /* range_details */, 1966 "resulting_rows": 2, 1967 "cost": 4.81, 1968 "chosen": true 1969 } 1970 ] /* considered_access_paths */ 1971 } /* best_access_path */, 1972 "condition_filtering_pct": 100, 1973 "rows_for_plan": 2, 1974 "cost_for_plan": 4.81, 1975 "rest_of_plan": [ 1976 { 1977 "plan_prefix": [ 1978 "`t1` `table1`" 1979 ] /* plan_prefix */, 1980 "table": "`t1` `table2`", 1981 "best_access_path": { 1982 "considered_access_paths": [ 1983 { 1984 "access_type": "ref", 1985 "index": "col_varchar_key", 1986 "rows": 2, 1987 "cost": 4.8, 1988 "chosen": true 1989 }, 1990 { 1991 "rows_to_scan": 2, 1992 "access_type": "scan", 1993 "using_join_cache": true, 1994 "buffers_needed": 1, 1995 "resulting_rows": 2, 1996 "cost": 2.8226, 1997 "chosen": true 1998 } 1999 ] /* considered_access_paths */ 2000 } /* best_access_path */, 2001 "condition_filtering_pct": 100, 2002 "rows_for_plan": 4, 2003 "cost_for_plan": 7.6326, 2004 "chosen": true 2005 } 2006 ] /* rest_of_plan */ 2007 } 2008 ] /* considered_execution_plans */ 2009 }, 2010 { 2011 "attaching_conditions_to_tables": { 2012 "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)))", 2013 "attached_conditions_computation": [ 2014 { 2015 "table": "`t1` `table2`", 2016 "rechecking_index_usage": { 2017 "recheck_reason": "not_first_table", 2018 "range_analysis": { 2019 "table_scan": { 2020 "rows": 2, 2021 "cost": 4.5225 2022 } /* table_scan */, 2023 "potential_range_indexes": [ 2024 { 2025 "index": "PRIMARY", 2026 "usable": false, 2027 "cause": "not_applicable" 2028 }, 2029 { 2030 "index": "col_varchar_key", 2031 "usable": true, 2032 "key_parts": [ 2033 "col_varchar_key", 2034 "col_int_key" 2035 ] /* key_parts */ 2036 } 2037 ] /* potential_range_indexes */, 2038 "setup_range_conditions": [ 2039 ] /* setup_range_conditions */, 2040 "group_index_range": { 2041 "chosen": false, 2042 "cause": "not_single_table" 2043 } /* group_index_range */, 2044 "analyzing_range_alternatives": { 2045 "range_scan_alternatives": [ 2046 { 2047 "index": "col_varchar_key", 2048 "chosen": false, 2049 "cause": "depends_on_unread_values" 2050 } 2051 ] /* range_scan_alternatives */, 2052 "analyzing_roworder_intersect": { 2053 "usable": false, 2054 "cause": "too_few_roworder_scans" 2055 } /* analyzing_roworder_intersect */ 2056 } /* analyzing_range_alternatives */ 2057 } /* range_analysis */ 2058 } /* rechecking_index_usage */ 2059 } 2060 ] /* attached_conditions_computation */, 2061 "attached_conditions_summary": [ 2062 { 2063 "table": "`t1` `table1`", 2064 "attached": "(`table1`.`pk` in (192,18))" 2065 }, 2066 { 2067 "table": "`t1` `table2`", 2068 "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)))))" 2069 } 2070 ] /* attached_conditions_summary */ 2071 } /* attaching_conditions_to_tables */ 2072 }, 2073 { 2074 "clause_processing": { 2075 "clause": "ORDER BY", 2076 "original_clause": "`field1` desc", 2077 "items": [ 2078 { 2079 "item": "(/* select#2 */ select sum(`subquery1_t1`.`col_int_nokey`) AS `subquery1_field1` from `t1` `subquery1_t1`)", 2080 "subselect_evaluation": [ 2081 { 2082 "subselect_execution": { 2083 "select#": 2, 2084 "steps": [ 2085 { 2086 "join_optimization": { 2087 "select#": 2, 2088 "steps": [ 2089 { 2090 "table_dependencies": [ 2091 { 2092 "table": "`t1` `subquery1_t1`", 2093 "row_may_be_null": false, 2094 "map_bit": 0, 2095 "depends_on_map_bits": [ 2096 ] /* depends_on_map_bits */ 2097 } 2098 ] /* table_dependencies */ 2099 }, 2100 { 2101 "rows_estimation": [ 2102 { 2103 "table": "`t1` `subquery1_t1`", 2104 "table_scan": { 2105 "rows": 2, 2106 "cost": 2 2107 } /* table_scan */ 2108 } 2109 ] /* rows_estimation */ 2110 }, 2111 { 2112 "considered_execution_plans": [ 2113 { 2114 "plan_prefix": [ 2115 ] /* plan_prefix */, 2116 "table": "`t1` `subquery1_t1`", 2117 "best_access_path": { 2118 "considered_access_paths": [ 2119 { 2120 "rows_to_scan": 2, 2121 "access_type": "scan", 2122 "resulting_rows": 2, 2123 "cost": 2.4225, 2124 "chosen": true 2125 } 2126 ] /* considered_access_paths */ 2127 } /* best_access_path */, 2128 "condition_filtering_pct": 100, 2129 "rows_for_plan": 2, 2130 "cost_for_plan": 2.4225, 2131 "chosen": true 2132 } 2133 ] /* considered_execution_plans */ 2134 }, 2135 { 2136 "attaching_conditions_to_tables": { 2137 "original_condition": null, 2138 "attached_conditions_computation": [ 2139 ] /* attached_conditions_computation */, 2140 "attached_conditions_summary": [ 2141 { 2142 "table": "`t1` `subquery1_t1`", 2143 "attached": null 2144 } 2145 ] /* attached_conditions_summary */ 2146 } /* attaching_conditions_to_tables */ 2147 }, 2148 { 2149 "refine_plan": [ 2150 { 2151 "table": "`t1` `subquery1_t1`" 2152 } 2153 ] /* refine_plan */ 2154 } 2155 ] /* steps */ 2156 } /* join_optimization */ 2157 }, 2158 { 2159 "join_execution": { 2160 "select#": 2, 2161 "steps": [ 2162 ] /* steps */ 2163 } /* join_execution */ 2164 } 2165 ] /* steps */ 2166 } /* subselect_execution */ 2167 } 2168 ] /* subselect_evaluation */, 2169 "uses_only_constant_tables": true 2170 } 2171 ] /* items */, 2172 "resulting_clause_is_simple": true, 2173 "resulting_clause": "" 2174 } /* clause_processing */ 2175 }, 2176 { 2177 "refine_plan": [ 2178 { 2179 "table": "`t1` `table1`", 2180 "pushed_index_condition": "(`table1`.`pk` in (192,18))", 2181 "table_condition_attached": null 2182 }, 2183 { 2184 "table": "`t1` `table2`", 2185 "unknown_key_1": { 2186 "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))))" 2187 } 2188 } 2189 ] /* refine_plan */ 2190 } 2191 ] /* steps */ 2192 } /* join_optimization */ 2193 }, 2194 { 2195 "join_optimization": { 2196 "select#": 3, 2197 "steps": [ 2198 { 2199 "condition_processing": { 2200 "condition": "WHERE", 2201 "original_condition": "<if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)", 2202 "steps": [ 2203 { 2204 "transformation": "equality_propagation", 2205 "resulting_condition": "<if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)" 2206 }, 2207 { 2208 "transformation": "constant_propagation", 2209 "resulting_condition": "<if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)" 2210 }, 2211 { 2212 "transformation": "trivial_condition_removal", 2213 "resulting_condition": "<if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)" 2214 } 2215 ] /* steps */ 2216 } /* condition_processing */ 2217 }, 2218 { 2219 "substitute_generated_columns": { 2220 } /* substitute_generated_columns */ 2221 }, 2222 { 2223 "table_dependencies": [ 2224 { 2225 "table": "`t2`", 2226 "row_may_be_null": false, 2227 "map_bit": 0, 2228 "depends_on_map_bits": [ 2229 ] /* depends_on_map_bits */ 2230 } 2231 ] /* table_dependencies */ 2232 }, 2233 { 2234 "ref_optimizer_key_uses": [ 2235 ] /* ref_optimizer_key_uses */ 2236 }, 2237 { 2238 "rows_estimation": [ 2239 { 2240 "table": "`t2`", 2241 "rows": 1, 2242 "cost": 1, 2243 "table_type": "system", 2244 "empty": true 2245 } 2246 ] /* rows_estimation */ 2247 }, 2248 { 2249 "transformation": { 2250 "select#": 3, 2251 "from": "IN (SELECT)", 2252 "to": "materialization", 2253 "possible": false, 2254 "cause": "not an IN predicate" 2255 } /* transformation */ 2256 }, 2257 { 2258 "transformation": { 2259 "select#": 3, 2260 "from": "IN (SELECT)", 2261 "to": "EXISTS (CORRELATED SELECT)", 2262 "put_1_in_SELECT_list": true 2263 } /* transformation */ 2264 } 2265 ] /* steps */, 2266 "empty_result": { 2267 "cause": "no matching row in const table" 2268 } /* empty_result */ 2269 } /* join_optimization */ 2270 }, 2271 { 2272 "join_execution": { 2273 "select#": 1, 2274 "steps": [ 2275 ] /* steps */ 2276 } /* join_execution */ 2277 } 2278 ] /* steps */ 2279} 0 0 2280drop table t1,t2; 2281 2282# 2283# Tracing of semijoin loosescan 2284# 2285create table t0 (a int); 2286insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 2287create table t1 (a int, b int, filler char(100), key(a,b)); 2288insert into t1 select A.a, B.a, 'filler' from t0 A, t0 B; 2289create table t2 as select * from t1; 2290set @old_opt_switch=@@optimizer_switch; 2291set optimizer_switch="firstmatch=off,materialization=off,duplicateweedout=off"; 2292set @old_opt_prune_level=@@optimizer_prune_level; 2293set optimizer_prune_level=0; 2294explain select * from t2 where a in (select b from t1 where a=3); 2295id select_type table partitions type possible_keys key key_len ref rows filtered Extra 22961 SIMPLE t1 NULL ref a a 5 const 8 100.00 Using index; LooseScan 22971 SIMPLE t2 NULL ALL NULL NULL NULL NULL 100 10.00 Using where; Using join buffer (Block Nested Loop) 2298Warnings: 2299Note 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)) 2300# Equality-propagation involving inner field => 1st sj equality is bound 2301explain select * from t2 where (b+0,a+0) in (select a,b from t1 where a=3); 2302id select_type table partitions type possible_keys key key_len ref rows filtered Extra 23031 SIMPLE t1 NULL ref a a 5 const 8 100.00 Using index; LooseScan 23041 SIMPLE t2 NULL ALL NULL NULL NULL NULL 100 100.00 Using where; Using join buffer (Block Nested Loop) 2305Warnings: 2306Note 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`)) 2307# Equality-propagation involving outer field => 3rd sj equality is bound. 2308explain select * from t2 where (b,a,filler) in (select a,b,a*3 from t1) and filler='abc'; 2309id select_type table partitions type possible_keys key key_len ref rows filtered Extra 23101 SIMPLE t1 NULL index a a 10 NULL 100 100.00 Using index; LooseScan 23111 SIMPLE t2 NULL ALL NULL NULL NULL NULL 100 1.00 Using where; Using join buffer (Block Nested Loop) 2312Warnings: 2313Note 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))) 2314SELECT show_json_object('"recalculate_access_paths_and_cost": {', TRACE) 2315FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; 2316show_json_object('"recalculate_access_paths_and_cost": {', TRACE) 2317"recalculate_access_paths_and_cost": { 2318 "tables": [ 2319 { 2320 "table": "`t1`", 2321 "best_access_path": { 2322 "considered_access_paths": [ 2323 { 2324 "access_type": "ref", 2325 "index": "a", 2326 "usable": false, 2327 "chosen": false 2328 }, 2329 { 2330 "rows_to_scan": 100, 2331 "access_type": "scan", 2332 "resulting_rows": 100, 2333 "cost": 24.661, 2334 "chosen": true 2335 } 2336 ] /* considered_access_paths */ 2337 } /* best_access_path */, 2338 "unknown_key_1": { 2339 "searching_loose_scan_index": { 2340 "indexes": [ 2341 { 2342 "index": "a", 2343 "covering_scan": { 2344 "cost": 4.1935, 2345 "chosen": true 2346 } /* covering_scan */ 2347 } 2348 ] /* indexes */ 2349 } /* searching_loose_scan_index */ 2350 } 2351 }, 2352 { 2353 "table": "`t2`", 2354 "best_access_path": { 2355 "considered_access_paths": [ 2356 { 2357 "rows_to_scan": 100, 2358 "access_type": "scan", 2359 "using_join_cache": true, 2360 "buffers_needed": 1, 2361 "resulting_rows": 10, 2362 "cost": 222.74, 2363 "chosen": true 2364 } 2365 ] /* considered_access_paths */ 2366 } /* best_access_path */ 2367 } 2368 ] /* tables */ 2369 } 2370# Remove the condition on 'filler' => 3rd sj equality is not bound. 2371explain select * from t2 where (b,a,filler) in (select a,b,a*3 from t1); 2372id select_type table partitions type possible_keys key key_len ref rows filtered Extra 23731 SIMPLE t2 NULL ALL NULL NULL NULL NULL 100 100.00 Using where 23741 SIMPLE t1 NULL ref a a 10 test.t2.b,test.t2.a 1 100.00 Using index; Start temporary; End temporary 2375Warnings: 2376Note 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))) 2377SELECT show_json_object('"searching_loose_scan_index": {', TRACE) 2378FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; 2379show_json_object('"searching_loose_scan_index": {', TRACE) 2380"searching_loose_scan_index": { 2381 "indexes": [ 2382 { 2383 "index": "a", 2384 "index_handles_needed_semijoin_equalities": false 2385 } 2386 ] /* indexes */ 2387 } 2388# Equality-propagation involving outer field => 3rd sj equality is bound. 2389explain select * from t2 as t3, t2 2390where t2.filler=t3.filler and 2391(t2.b,t2.a,t2.filler) in (select a,b,a*3 from t1); 2392id select_type table partitions type possible_keys key key_len ref rows filtered Extra 23931 SIMPLE t3 NULL ALL NULL NULL NULL NULL 100 100.00 NULL 23941 SIMPLE t1 NULL index a a 10 NULL 100 100.00 Using where; Using index; LooseScan 23951 SIMPLE t2 NULL ALL NULL NULL NULL NULL 100 1.00 Using where; Using join buffer (Block Nested Loop) 2396Warnings: 2397Note 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))) 2398SELECT show_json_object('"recalculate_access_paths_and_cost": {', TRACE) 2399FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; 2400show_json_object('"recalculate_access_paths_and_cost": {', TRACE) 2401"recalculate_access_paths_and_cost": { 2402 "tables": [ 2403 { 2404 "table": "`t1`", 2405 "best_access_path": { 2406 "considered_access_paths": [ 2407 { 2408 "access_type": "ref", 2409 "index": "a", 2410 "usable": false, 2411 "chosen": false 2412 }, 2413 { 2414 "rows_to_scan": 100, 2415 "access_type": "scan", 2416 "resulting_rows": 100, 2417 "cost": 2466.1, 2418 "chosen": true 2419 } 2420 ] /* considered_access_paths */ 2421 } /* best_access_path */, 2422 "unknown_key_1": { 2423 "searching_loose_scan_index": { 2424 "indexes": [ 2425 { 2426 "index": "a", 2427 "covering_scan": { 2428 "cost": 4.1935, 2429 "chosen": true 2430 } /* covering_scan */ 2431 } 2432 ] /* indexes */ 2433 } /* searching_loose_scan_index */ 2434 } 2435 }, 2436 { 2437 "table": "`t2`", 2438 "best_access_path": { 2439 "considered_access_paths": [ 2440 { 2441 "rows_to_scan": 100, 2442 "access_type": "scan", 2443 "using_join_cache": true, 2444 "buffers_needed": 5, 2445 "resulting_rows": 100, 2446 "cost": 200026, 2447 "chosen": true 2448 } 2449 ] /* considered_access_paths */ 2450 } /* best_access_path */ 2451 } 2452 ] /* tables */ 2453 } 2454# In plan t3-t1-t2, 3rd outer expression is dependent only on 2455# previous tables => 3rd sj equality is bound. 2456# If t1 is before t3, 3rd sj equality is not bound. 2457explain select * from t2 as t3 left join t2 on t2.filler+10=t3.filler+20 2458where (t2.b,t2.a,t3.filler+2) in (select a,b,a*3 from t1); 2459id select_type table partitions type possible_keys key key_len ref rows filtered Extra 24601 SIMPLE t3 NULL ALL NULL NULL NULL NULL 100 100.00 NULL 24611 SIMPLE t1 NULL index a a 10 NULL 100 100.00 Using where; Using index; LooseScan 24621 SIMPLE t2 NULL ALL NULL NULL NULL NULL 100 1.00 Using where; Using join buffer (Block Nested Loop) 2463Warnings: 2464Note 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))) 2465SELECT show_json_object('"considered_execution_plans": [', TRACE) 2466FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; 2467show_json_object('"considered_execution_plans": [', TRACE) 2468"considered_execution_plans": [ 2469 { 2470 "plan_prefix": [ 2471 ] /* plan_prefix */, 2472 "table": "`t2` `t3`", 2473 "best_access_path": { 2474 "considered_access_paths": [ 2475 { 2476 "rows_to_scan": 100, 2477 "access_type": "scan", 2478 "resulting_rows": 100, 2479 "cost": 24.661, 2480 "chosen": true 2481 } 2482 ] /* considered_access_paths */ 2483 } /* best_access_path */, 2484 "condition_filtering_pct": 100, 2485 "rows_for_plan": 100, 2486 "cost_for_plan": 24.661, 2487 "semijoin_strategy_choice": [ 2488 ] /* semijoin_strategy_choice */, 2489 "rest_of_plan": [ 2490 { 2491 "plan_prefix": [ 2492 "`t2` `t3`" 2493 ] /* plan_prefix */, 2494 "table": "`t2`", 2495 "best_access_path": { 2496 "considered_access_paths": [ 2497 { 2498 "rows_to_scan": 100, 2499 "access_type": "scan", 2500 "using_join_cache": true, 2501 "buffers_needed": 1, 2502 "resulting_rows": 100, 2503 "cost": 2004.9, 2504 "chosen": true 2505 } 2506 ] /* considered_access_paths */ 2507 } /* best_access_path */, 2508 "condition_filtering_pct": 100, 2509 "rows_for_plan": 10000, 2510 "cost_for_plan": 2029.5, 2511 "semijoin_strategy_choice": [ 2512 ] /* semijoin_strategy_choice */, 2513 "rest_of_plan": [ 2514 { 2515 "plan_prefix": [ 2516 "`t2` `t3`", 2517 "`t2`" 2518 ] /* plan_prefix */, 2519 "table": "`t1`", 2520 "best_access_path": { 2521 "considered_access_paths": [ 2522 { 2523 "access_type": "ref", 2524 "index": "a", 2525 "rows": 1, 2526 "cost": 12000, 2527 "chosen": true 2528 }, 2529 { 2530 "rows_to_scan": 100, 2531 "access_type": "scan", 2532 "using_join_cache": true, 2533 "buffers_needed": 9, 2534 "resulting_rows": 100, 2535 "cost": 200044, 2536 "chosen": false 2537 } 2538 ] /* considered_access_paths */ 2539 } /* best_access_path */, 2540 "condition_filtering_pct": 100, 2541 "rows_for_plan": 10000, 2542 "cost_for_plan": 14030, 2543 "semijoin_strategy_choice": [ 2544 { 2545 "strategy": "DuplicatesWeedout", 2546 "cost": 18032, 2547 "rows": 10000, 2548 "duplicate_tables_left": true, 2549 "chosen": true 2550 } 2551 ] /* semijoin_strategy_choice */, 2552 "chosen": true 2553 } 2554 ] /* rest_of_plan */ 2555 }, 2556 { 2557 "plan_prefix": [ 2558 "`t2` `t3`" 2559 ] /* plan_prefix */, 2560 "table": "`t1`", 2561 "best_access_path": { 2562 "considered_access_paths": [ 2563 { 2564 "access_type": "ref", 2565 "index": "a", 2566 "usable": false, 2567 "chosen": false 2568 }, 2569 { 2570 "rows_to_scan": 100, 2571 "access_type": "scan", 2572 "using_join_cache": true, 2573 "buffers_needed": 1, 2574 "resulting_rows": 100, 2575 "cost": 2004.9, 2576 "chosen": true 2577 } 2578 ] /* considered_access_paths */ 2579 } /* best_access_path */, 2580 "condition_filtering_pct": 100, 2581 "rows_for_plan": 10000, 2582 "cost_for_plan": 2029.5, 2583 "semijoin_strategy_choice": [ 2584 ] /* semijoin_strategy_choice */, 2585 "rest_of_plan": [ 2586 { 2587 "plan_prefix": [ 2588 "`t2` `t3`", 2589 "`t1`" 2590 ] /* plan_prefix */, 2591 "table": "`t2`", 2592 "best_access_path": { 2593 "considered_access_paths": [ 2594 { 2595 "rows_to_scan": 100, 2596 "access_type": "scan", 2597 "using_join_cache": true, 2598 "buffers_needed": 5, 2599 "resulting_rows": 100, 2600 "cost": 200026, 2601 "chosen": true 2602 } 2603 ] /* considered_access_paths */ 2604 } /* best_access_path */, 2605 "condition_filtering_pct": 1, 2606 "rows_for_plan": 10000, 2607 "cost_for_plan": 202055, 2608 "semijoin_strategy_choice": [ 2609 { 2610 "strategy": "LooseScan", 2611 "recalculate_access_paths_and_cost": { 2612 "tables": [ 2613 { 2614 "table": "`t1`", 2615 "best_access_path": { 2616 "considered_access_paths": [ 2617 { 2618 "access_type": "ref", 2619 "index": "a", 2620 "usable": false, 2621 "chosen": false 2622 }, 2623 { 2624 "rows_to_scan": 100, 2625 "access_type": "scan", 2626 "resulting_rows": 100, 2627 "cost": 2466.1, 2628 "chosen": true 2629 } 2630 ] /* considered_access_paths */ 2631 } /* best_access_path */, 2632 "unknown_key_1": { 2633 "searching_loose_scan_index": { 2634 "indexes": [ 2635 { 2636 "index": "a", 2637 "covering_scan": { 2638 "cost": 4.1935, 2639 "chosen": true 2640 } /* covering_scan */ 2641 } 2642 ] /* indexes */ 2643 } /* searching_loose_scan_index */ 2644 } 2645 }, 2646 { 2647 "table": "`t2`", 2648 "best_access_path": { 2649 "considered_access_paths": [ 2650 { 2651 "rows_to_scan": 100, 2652 "access_type": "scan", 2653 "using_join_cache": true, 2654 "buffers_needed": 5, 2655 "resulting_rows": 100, 2656 "cost": 200026, 2657 "chosen": true 2658 } 2659 ] /* considered_access_paths */ 2660 } /* best_access_path */ 2661 } 2662 ] /* tables */ 2663 } /* recalculate_access_paths_and_cost */, 2664 "cost": 202054, 2665 "rows": 100, 2666 "chosen": true 2667 }, 2668 { 2669 "strategy": "DuplicatesWeedout", 2670 "cost": 204077, 2671 "rows": 100, 2672 "duplicate_tables_left": false, 2673 "chosen": false 2674 } 2675 ] /* semijoin_strategy_choice */, 2676 "chosen": true, 2677 "cause": "previous_plan_used_disabled_strategy" 2678 } 2679 ] /* rest_of_plan */ 2680 } 2681 ] /* rest_of_plan */ 2682 }, 2683 { 2684 "plan_prefix": [ 2685 ] /* plan_prefix */, 2686 "table": "`t2`", 2687 "best_access_path": { 2688 "considered_access_paths": [ 2689 { 2690 "rows_to_scan": 100, 2691 "access_type": "scan", 2692 "resulting_rows": 100, 2693 "cost": 24.661, 2694 "chosen": true 2695 } 2696 ] /* considered_access_paths */ 2697 } /* best_access_path */, 2698 "condition_filtering_pct": 100, 2699 "rows_for_plan": 100, 2700 "cost_for_plan": 24.661, 2701 "semijoin_strategy_choice": [ 2702 ] /* semijoin_strategy_choice */, 2703 "rest_of_plan": [ 2704 { 2705 "plan_prefix": [ 2706 "`t2`" 2707 ] /* plan_prefix */, 2708 "table": "`t2` `t3`", 2709 "best_access_path": { 2710 "considered_access_paths": [ 2711 { 2712 "rows_to_scan": 100, 2713 "access_type": "scan", 2714 "using_join_cache": true, 2715 "buffers_needed": 1, 2716 "resulting_rows": 100, 2717 "cost": 2004.9, 2718 "chosen": true 2719 } 2720 ] /* considered_access_paths */ 2721 } /* best_access_path */, 2722 "condition_filtering_pct": 100, 2723 "rows_for_plan": 10000, 2724 "cost_for_plan": 2029.5, 2725 "semijoin_strategy_choice": [ 2726 ] /* semijoin_strategy_choice */, 2727 "rest_of_plan": [ 2728 { 2729 "plan_prefix": [ 2730 "`t2`", 2731 "`t2` `t3`" 2732 ] /* plan_prefix */, 2733 "table": "`t1`", 2734 "best_access_path": { 2735 "considered_access_paths": [ 2736 { 2737 "access_type": "ref", 2738 "index": "a", 2739 "rows": 1, 2740 "cost": 12000, 2741 "chosen": true 2742 }, 2743 { 2744 "rows_to_scan": 100, 2745 "access_type": "scan", 2746 "using_join_cache": true, 2747 "buffers_needed": 9, 2748 "resulting_rows": 100, 2749 "cost": 200044, 2750 "chosen": false 2751 } 2752 ] /* considered_access_paths */ 2753 } /* best_access_path */, 2754 "condition_filtering_pct": 100, 2755 "rows_for_plan": 10000, 2756 "cost_for_plan": 14030, 2757 "semijoin_strategy_choice": [ 2758 { 2759 "strategy": "DuplicatesWeedout", 2760 "cost": 18032, 2761 "rows": 10000, 2762 "duplicate_tables_left": true, 2763 "chosen": true 2764 } 2765 ] /* semijoin_strategy_choice */, 2766 "chosen": false, 2767 "cause": "plan_uses_disabled_strategy" 2768 } 2769 ] /* rest_of_plan */ 2770 }, 2771 { 2772 "plan_prefix": [ 2773 "`t2`" 2774 ] /* plan_prefix */, 2775 "table": "`t1`", 2776 "best_access_path": { 2777 "considered_access_paths": [ 2778 { 2779 "access_type": "ref", 2780 "index": "a", 2781 "rows": 1, 2782 "cost": 120, 2783 "chosen": true 2784 }, 2785 { 2786 "rows_to_scan": 100, 2787 "access_type": "scan", 2788 "using_join_cache": true, 2789 "buffers_needed": 1, 2790 "resulting_rows": 100, 2791 "cost": 2004.9, 2792 "chosen": false 2793 } 2794 ] /* considered_access_paths */ 2795 } /* best_access_path */, 2796 "condition_filtering_pct": 100, 2797 "rows_for_plan": 100, 2798 "cost_for_plan": 144.66, 2799 "semijoin_strategy_choice": [ 2800 ] /* semijoin_strategy_choice */, 2801 "rest_of_plan": [ 2802 { 2803 "plan_prefix": [ 2804 "`t2`", 2805 "`t1`" 2806 ] /* plan_prefix */, 2807 "table": "`t2` `t3`", 2808 "best_access_path": { 2809 "considered_access_paths": [ 2810 { 2811 "rows_to_scan": 100, 2812 "access_type": "scan", 2813 "using_join_cache": true, 2814 "buffers_needed": 1, 2815 "resulting_rows": 100, 2816 "cost": 2004.9, 2817 "chosen": true 2818 } 2819 ] /* considered_access_paths */ 2820 } /* best_access_path */, 2821 "condition_filtering_pct": 100, 2822 "rows_for_plan": 10000, 2823 "cost_for_plan": 2149.5, 2824 "semijoin_strategy_choice": [ 2825 { 2826 "strategy": "LooseScan", 2827 "recalculate_access_paths_and_cost": { 2828 "tables": [ 2829 { 2830 "table": "`t1`", 2831 "best_access_path": { 2832 "considered_access_paths": [ 2833 { 2834 "access_type": "ref", 2835 "index": "a", 2836 "rows": 1, 2837 "cost": 120, 2838 "chosen": true 2839 }, 2840 { 2841 "rows_to_scan": 100, 2842 "access_type": "scan", 2843 "resulting_rows": 100, 2844 "cost": 2466.1, 2845 "chosen": false 2846 } 2847 ] /* considered_access_paths */ 2848 } /* best_access_path */, 2849 "unknown_key_2": { 2850 "searching_loose_scan_index": { 2851 "indexes": [ 2852 { 2853 "index": "a", 2854 "index_handles_needed_semijoin_equalities": false 2855 } 2856 ] /* indexes */ 2857 } /* searching_loose_scan_index */ 2858 } 2859 } 2860 ] /* tables */ 2861 } /* recalculate_access_paths_and_cost */, 2862 "chosen": false 2863 }, 2864 { 2865 "strategy": "DuplicatesWeedout", 2866 "cost": 6151.5, 2867 "rows": 10000, 2868 "duplicate_tables_left": true, 2869 "chosen": true 2870 } 2871 ] /* semijoin_strategy_choice */, 2872 "chosen": false, 2873 "cause": "plan_uses_disabled_strategy" 2874 } 2875 ] /* rest_of_plan */ 2876 } 2877 ] /* rest_of_plan */ 2878 }, 2879 { 2880 "plan_prefix": [ 2881 ] /* plan_prefix */, 2882 "table": "`t1`", 2883 "best_access_path": { 2884 "considered_access_paths": [ 2885 { 2886 "access_type": "ref", 2887 "index": "a", 2888 "usable": false, 2889 "chosen": false 2890 }, 2891 { 2892 "rows_to_scan": 100, 2893 "access_type": "scan", 2894 "resulting_rows": 100, 2895 "cost": 24.661, 2896 "chosen": true 2897 } 2898 ] /* considered_access_paths */ 2899 } /* best_access_path */, 2900 "condition_filtering_pct": 100, 2901 "rows_for_plan": 100, 2902 "cost_for_plan": 24.661, 2903 "semijoin_strategy_choice": [ 2904 ] /* semijoin_strategy_choice */, 2905 "rest_of_plan": [ 2906 { 2907 "plan_prefix": [ 2908 "`t1`" 2909 ] /* plan_prefix */, 2910 "table": "`t2` `t3`", 2911 "best_access_path": { 2912 "considered_access_paths": [ 2913 { 2914 "rows_to_scan": 100, 2915 "access_type": "scan", 2916 "using_join_cache": true, 2917 "buffers_needed": 1, 2918 "resulting_rows": 100, 2919 "cost": 2004.7, 2920 "chosen": true 2921 } 2922 ] /* considered_access_paths */ 2923 } /* best_access_path */, 2924 "condition_filtering_pct": 100, 2925 "rows_for_plan": 10000, 2926 "cost_for_plan": 2029.3, 2927 "semijoin_strategy_choice": [ 2928 ] /* semijoin_strategy_choice */, 2929 "rest_of_plan": [ 2930 { 2931 "plan_prefix": [ 2932 "`t1`", 2933 "`t2` `t3`" 2934 ] /* plan_prefix */, 2935 "table": "`t2`", 2936 "best_access_path": { 2937 "considered_access_paths": [ 2938 { 2939 "rows_to_scan": 100, 2940 "access_type": "scan", 2941 "using_join_cache": true, 2942 "buffers_needed": 5, 2943 "resulting_rows": 100, 2944 "cost": 200026, 2945 "chosen": true 2946 } 2947 ] /* considered_access_paths */ 2948 } /* best_access_path */, 2949 "condition_filtering_pct": 1, 2950 "rows_for_plan": 10000, 2951 "cost_for_plan": 202055, 2952 "semijoin_strategy_choice": [ 2953 { 2954 "strategy": "LooseScan", 2955 "recalculate_access_paths_and_cost": { 2956 "tables": [ 2957 { 2958 "table": "`t1`", 2959 "best_access_path": { 2960 "considered_access_paths": [ 2961 { 2962 "access_type": "ref", 2963 "index": "a", 2964 "usable": false, 2965 "chosen": false 2966 }, 2967 { 2968 "rows_to_scan": 100, 2969 "access_type": "scan", 2970 "resulting_rows": 100, 2971 "cost": 24.661, 2972 "chosen": true 2973 } 2974 ] /* considered_access_paths */ 2975 } /* best_access_path */, 2976 "unknown_key_3": { 2977 "searching_loose_scan_index": { 2978 "indexes": [ 2979 { 2980 "index": "a", 2981 "index_handles_needed_semijoin_equalities": false 2982 } 2983 ] /* indexes */ 2984 } /* searching_loose_scan_index */ 2985 } 2986 } 2987 ] /* tables */ 2988 } /* recalculate_access_paths_and_cost */, 2989 "chosen": false 2990 }, 2991 { 2992 "strategy": "DuplicatesWeedout", 2993 "cost": 204077, 2994 "rows": 100, 2995 "duplicate_tables_left": true, 2996 "chosen": true 2997 } 2998 ] /* semijoin_strategy_choice */, 2999 "pruned_by_cost": true 3000 } 3001 ] /* rest_of_plan */ 3002 }, 3003 { 3004 "plan_prefix": [ 3005 "`t1`" 3006 ] /* plan_prefix */, 3007 "table": "`t2`", 3008 "best_access_path": { 3009 "considered_access_paths": [ 3010 { 3011 "rows_to_scan": 100, 3012 "access_type": "scan", 3013 "using_join_cache": true, 3014 "buffers_needed": 1, 3015 "resulting_rows": 100, 3016 "cost": 2004.7, 3017 "chosen": true 3018 } 3019 ] /* considered_access_paths */ 3020 } /* best_access_path */, 3021 "condition_filtering_pct": 1, 3022 "rows_for_plan": 100, 3023 "cost_for_plan": 2029.3, 3024 "semijoin_strategy_choice": [ 3025 ] /* semijoin_strategy_choice */, 3026 "rest_of_plan": [ 3027 { 3028 "plan_prefix": [ 3029 "`t1`", 3030 "`t2`" 3031 ] /* plan_prefix */, 3032 "table": "`t2` `t3`", 3033 "best_access_path": { 3034 "considered_access_paths": [ 3035 { 3036 "rows_to_scan": 100, 3037 "access_type": "scan", 3038 "using_join_cache": true, 3039 "buffers_needed": 1, 3040 "resulting_rows": 100, 3041 "cost": 2004.9, 3042 "chosen": true 3043 } 3044 ] /* considered_access_paths */ 3045 } /* best_access_path */, 3046 "condition_filtering_pct": 100, 3047 "rows_for_plan": 10000, 3048 "cost_for_plan": 4034.2, 3049 "semijoin_strategy_choice": [ 3050 { 3051 "strategy": "LooseScan", 3052 "recalculate_access_paths_and_cost": { 3053 "tables": [ 3054 { 3055 "table": "`t1`", 3056 "best_access_path": { 3057 "considered_access_paths": [ 3058 { 3059 "access_type": "ref", 3060 "index": "a", 3061 "usable": false, 3062 "chosen": false 3063 }, 3064 { 3065 "rows_to_scan": 100, 3066 "access_type": "scan", 3067 "resulting_rows": 100, 3068 "cost": 24.661, 3069 "chosen": true 3070 } 3071 ] /* considered_access_paths */ 3072 } /* best_access_path */, 3073 "unknown_key_4": { 3074 "searching_loose_scan_index": { 3075 "indexes": [ 3076 { 3077 "index": "a", 3078 "index_handles_needed_semijoin_equalities": false 3079 } 3080 ] /* indexes */ 3081 } /* searching_loose_scan_index */ 3082 } 3083 } 3084 ] /* tables */ 3085 } /* recalculate_access_paths_and_cost */, 3086 "chosen": false 3087 }, 3088 { 3089 "strategy": "DuplicatesWeedout", 3090 "cost": 6056.2, 3091 "rows": 100, 3092 "duplicate_tables_left": true, 3093 "chosen": true 3094 } 3095 ] /* semijoin_strategy_choice */, 3096 "chosen": false, 3097 "cause": "plan_uses_disabled_strategy" 3098 } 3099 ] /* rest_of_plan */ 3100 } 3101 ] /* rest_of_plan */ 3102 }, 3103 { 3104 "final_semijoin_strategy": "LooseScan", 3105 "recalculate_access_paths_and_cost": { 3106 "tables": [ 3107 { 3108 "table": "`t1`", 3109 "best_access_path": { 3110 "considered_access_paths": [ 3111 { 3112 "access_type": "ref", 3113 "index": "a", 3114 "usable": false, 3115 "chosen": false 3116 }, 3117 { 3118 "rows_to_scan": 100, 3119 "access_type": "scan", 3120 "resulting_rows": 100, 3121 "cost": 2466.1, 3122 "chosen": true 3123 } 3124 ] /* considered_access_paths */ 3125 } /* best_access_path */, 3126 "unknown_key_5": { 3127 "searching_loose_scan_index": { 3128 "indexes": [ 3129 { 3130 "index": "a", 3131 "covering_scan": { 3132 "cost": 4.1935, 3133 "chosen": true 3134 } /* covering_scan */ 3135 } 3136 ] /* indexes */ 3137 } /* searching_loose_scan_index */ 3138 } 3139 }, 3140 { 3141 "table": "`t2`", 3142 "best_access_path": { 3143 "considered_access_paths": [ 3144 { 3145 "rows_to_scan": 100, 3146 "access_type": "scan", 3147 "using_join_cache": true, 3148 "buffers_needed": 9, 3149 "resulting_rows": 100, 3150 "cost": 200044, 3151 "chosen": true 3152 } 3153 ] /* considered_access_paths */ 3154 } /* best_access_path */ 3155 } 3156 ] /* tables */ 3157 } /* recalculate_access_paths_and_cost */ 3158 } 3159 ] 3160drop table t0,t1,t2; 3161# 3162# Discover bound equality thanks to equality propagation 3163# specific of ON clause. 3164# 3165CREATE TABLE t1 ( 3166a int(11) DEFAULT NULL, 3167b varchar(100) DEFAULT NULL, 3168c int(11) DEFAULT NULL, 3169KEY b_c_a (b,c,a) 3170) ENGINE=InnoDB; 3171explain select * 3172from t1 left join t1 as t2 3173on (t2.a= t1.a and (t2.a,t2.b) in (select a,b from t1 as t3)) 3174where t1.a < 5; 3175id select_type table partitions type possible_keys key key_len ref rows filtered Extra 31761 SIMPLE t1 NULL index NULL b_c_a 113 NULL 1 100.00 Using where; Using index 31771 SIMPLE t3 NULL index b_c_a b_c_a 113 NULL 1 100.00 Using where; Using index; LooseScan 31781 SIMPLE t2 NULL ref b_c_a b_c_a 103 test.t3.b 1 100.00 Using where; Using index 3179Warnings: 3180Note 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) 3181drop table t1; 3182# 3183# Show that loosescan planning is not dependent on order of 3184# creation of indexes anymore. 3185# 3186create table it(a int, b int, index a_b (a,b), index a (a)) 3187engine=InnoDB; 3188insert into it values(1,1),(2,3),(4,3); 3189select * from it as ot 3190where (ot.a,ot.b) in (select it.a,it.b from it where it.b=3); 3191a b 31922 3 31934 3 3194select TRACE LIKE '%"index_handles_needed_semijoin_equalities": false%' 3195from information_schema.optimizer_trace; 3196TRACE LIKE '%"index_handles_needed_semijoin_equalities": false%' 31970 3198drop table it; 3199create table it(a int, b int, index a (a),index a_b (a,b)) 3200engine=InnoDB; 3201insert into it values(1,1),(2,3),(4,3); 3202select * from it as ot 3203where (ot.a,ot.b) in (select it.a,it.b from it where it.b=3); 3204a b 32052 3 32064 3 3207select TRACE LIKE '%"index_handles_needed_semijoin_equalities": false%' 3208from information_schema.optimizer_trace; 3209TRACE LIKE '%"index_handles_needed_semijoin_equalities": false%' 32100 3211drop table it; 3212# 3213# Show that we reject LooseScan if no handled key parts 3214# 3215CREATE TABLE t1 (a INT, b INT, KEY (a)) ENGINE=INNODB; 3216CREATE TABLE t2 (a INT, b INT) ENGINE=INNODB; 3217EXPLAIN SELECT * FROM t2 AS t3, t2 3218WHERE t2.b=t3.b AND 3219(t2.b) IN (SELECT b*3 FROM t1 WHERE a=10); 3220id select_type table partitions type possible_keys key key_len ref rows filtered Extra 32211 SIMPLE t3 NULL ALL NULL NULL NULL NULL 1 100.00 NULL 32221 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (Block Nested Loop) 32231 SIMPLE t1 NULL ref a a 5 const 1 100.00 Using where; Start temporary; End temporary 3224Warnings: 3225Note 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))) 3226SELECT TRACE LIKE '%"some_index_part_used": false%' 3227FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; 3228TRACE LIKE '%"some_index_part_used": false%' 32291 3230DROP TABLE t1,t2; 3231# 3232# Show that we detect a hole in sequence of key parts 3233# 3234CREATE TABLE ot1 (a INTEGER); 3235INSERT INTO ot1 VALUES (0),(1),(3),(7); 3236CREATE TABLE it1 (a VARCHAR(1), b INTEGER, KEY (a,b)); 3237INSERT INTO it1 VALUES ('a',7), ('b',7); 3238CREATE TABLE it2 (a VARCHAR(1), b INTEGER, KEY (a,b)); 3239INSERT INTO it2 VALUES ('a',7), ('b',7); 3240explain SELECT * FROM ot1 3241WHERE a IN ( 3242SELECT it1.b 3243FROM it1 JOIN it2 3244ON it1.a = it2.a 3245); 3246id select_type table partitions type possible_keys key key_len ref rows filtered Extra 32471 SIMPLE it1 NULL index a a 9 NULL 2 100.00 Using index; Start temporary 32481 SIMPLE ot1 NULL ALL NULL NULL NULL NULL 4 25.00 Using where; Using join buffer (Block Nested Loop) 32491 SIMPLE it2 NULL index a a 9 NULL 2 50.00 Using where; Using index; End temporary; Using join buffer (Block Nested Loop) 3250Warnings: 3251Note 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`)) 3252SELECT TRACE LIKE '%"index_can_remove_duplicates": false%' 3253FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; 3254TRACE LIKE '%"index_can_remove_duplicates": false%' 32551 3256DROP TABLE ot1, it1, it2; 3257# 3258# Show that handled keyparts cannot be on prefix 3259# 3260create table t1 (a int, b varchar(100), key a_b (a,b)); 3261insert into t1 values(25,'111111'),(25,'1111112'); 3262explain select * from t1 as t2 where t2.b in (select b from t1 where a=25); 3263id select_type table partitions type possible_keys key key_len ref rows filtered Extra 32641 SIMPLE t1 NULL ref a_b a_b 5 const 1 100.00 Using index; LooseScan 32651 SIMPLE t2 NULL index NULL a_b 108 NULL 2 50.00 Using where; Using index; Using join buffer (Block Nested Loop) 3266Warnings: 3267Note 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)) 3268select * from t1 as t2 where t2.b in (select b from t1 where a=25); 3269a b 327025 111111 327125 1111112 3272alter table t1 drop key a_b, add key a_b_prefix (a,b(2)); 3273explain select * from t1 as t2 where t2.b in (select b from t1 where a=25); 3274id select_type table partitions type possible_keys key key_len ref rows filtered Extra 32751 SIMPLE t1 NULL ref a_b_prefix a_b_prefix 5 const 1 100.00 Start temporary 32761 SIMPLE t2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where; End temporary; Using join buffer (Block Nested Loop) 3277Warnings: 3278Note 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)) 3279select * from t1 as t2 where t2.b in (select b from t1 where a=25); 3280a b 328125 111111 328225 1111112 3283select TRACE LIKE '%"index_handles_needed_semijoin_equalities": false%' 3284from information_schema.optimizer_trace; 3285TRACE LIKE '%"index_handles_needed_semijoin_equalities": false%' 32861 3287drop table t1; 3288set optimizer_switch=@old_opt_switch; 3289set optimizer_prune_level=@old_opt_prune_level; 3290drop function show_json_object; 3291