1--disable_warnings 2DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11; 3DROP DATABASE IF EXISTS world; 4--enable_warnings 5--source include/default_optimizer_switch.inc 6--source include/default_charset.inc 7 8set @org_optimizer_switch=@@optimizer_switch; 9set @save_join_cache_level=@@join_cache_level; 10set @save_join_buffer_space_limit=@@join_buffer_space_limit; 11set @save_join_buffer_size=@@join_buffer_size; 12set @save_expensive_subquery_limit=@@expensive_subquery_limit; 13 14set @@optimizer_switch='optimize_join_buffer_size=on'; 15set @@optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; 16set @@optimizer_switch='semijoin_with_cache=on'; 17set @@optimizer_switch='outer_join_with_cache=on'; 18set @@optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; 19set @local_optimizer_switch=@@optimizer_switch; 20 21set names utf8; 22 23CREATE DATABASE world; 24 25use world; 26 27--source include/world_schema1.inc 28 29--disable_query_log 30--disable_result_log 31--disable_warnings 32--source include/world.inc 33--enable_warnings 34--enable_result_log 35--enable_query_log 36 37SELECT COUNT(*) FROM Country; 38SELECT COUNT(*) FROM City; 39SELECT COUNT(*) FROM CountryLanguage; 40 41show variables like 'join_buffer_size'; 42 43set join_cache_level=1; 44 45show variables like 'join_cache_level'; 46 47EXPLAIN 48SELECT City.Name, Country.Name FROM City,Country 49 WHERE City.Country=Country.Code AND 50 Country.Name LIKE 'L%' AND City.Population > 100000; 51 52SELECT City.Name, Country.Name FROM City,Country 53 WHERE City.Country=Country.Code AND 54 Country.Name LIKE 'L%' AND City.Population > 100000; 55 56EXPLAIN 57SELECT City.Name, Country.Name, CountryLanguage.Language 58 FROM City,Country,CountryLanguage 59 WHERE City.Country=Country.Code AND 60 CountryLanguage.Country=Country.Code AND 61 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 62 CountryLanguage.Percentage > 50 AND 63 LENGTH(Language) < LENGTH(City.Name) - 2; 64 65SELECT City.Name, Country.Name, CountryLanguage.Language 66 FROM City,Country,CountryLanguage 67 WHERE City.Country=Country.Code AND 68 CountryLanguage.Country=Country.Code AND 69 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 70 CountryLanguage.Percentage > 50 AND 71 LENGTH(Language) < LENGTH(City.Name) - 2; 72 73set join_cache_level=2; 74show variables like 'join_cache_level'; 75 76EXPLAIN 77SELECT City.Name, Country.Name FROM City,Country 78 WHERE City.Country=Country.Code AND 79 Country.Name LIKE 'L%' AND City.Population > 100000; 80 81SELECT City.Name, Country.Name FROM City,Country 82 WHERE City.Country=Country.Code AND 83 Country.Name LIKE 'L%' AND City.Population > 100000; 84 85EXPLAIN 86SELECT City.Name, Country.Name, CountryLanguage.Language 87 FROM City,Country,CountryLanguage 88 WHERE City.Country=Country.Code AND 89 CountryLanguage.Country=Country.Code AND 90 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 91 CountryLanguage.Percentage > 50 AND 92 LENGTH(Language) < LENGTH(City.Name) - 2; 93 94SELECT City.Name, Country.Name, CountryLanguage.Language 95 FROM City,Country,CountryLanguage 96 WHERE City.Country=Country.Code AND 97 CountryLanguage.Country=Country.Code AND 98 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 99 CountryLanguage.Percentage > 50 AND 100 LENGTH(Language) < LENGTH(City.Name) - 2; 101 102set join_cache_level=3; 103show variables like 'join_cache_level'; 104 105EXPLAIN 106SELECT City.Name, Country.Name FROM City,Country 107 WHERE City.Country=Country.Code AND 108 Country.Name LIKE 'L%' AND City.Population > 100000; 109 110SELECT City.Name, Country.Name FROM City,Country 111 WHERE City.Country=Country.Code AND 112 Country.Name LIKE 'L%' AND City.Population > 100000; 113 114EXPLAIN 115SELECT City.Name, Country.Name, CountryLanguage.Language 116 FROM City,Country,CountryLanguage 117 WHERE City.Country=Country.Code AND 118 CountryLanguage.Country=Country.Code AND 119 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 120 CountryLanguage.Percentage > 50 AND 121 LENGTH(Language) < LENGTH(City.Name) - 2; 122 123 124SELECT City.Name, Country.Name, CountryLanguage.Language 125 FROM City,Country,CountryLanguage 126 WHERE City.Country=Country.Code AND 127 CountryLanguage.Country=Country.Code AND 128 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 129 CountryLanguage.Percentage > 50 AND 130 LENGTH(Language) < LENGTH(City.Name) - 2; 131 132 133set join_cache_level=4; 134show variables like 'join_cache_level'; 135 136EXPLAIN 137SELECT City.Name, Country.Name FROM City,Country 138 WHERE City.Country=Country.Code AND 139 Country.Name LIKE 'L%' AND City.Population > 100000; 140 141SELECT City.Name, Country.Name FROM City,Country 142 WHERE City.Country=Country.Code AND 143 Country.Name LIKE 'L%' AND City.Population > 100000; 144 145EXPLAIN 146SELECT City.Name, Country.Name, CountryLanguage.Language 147 FROM City,Country,CountryLanguage 148 WHERE City.Country=Country.Code AND 149 CountryLanguage.Country=Country.Code AND 150 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 151 CountryLanguage.Percentage > 50 AND 152 LENGTH(Language) < LENGTH(City.Name) - 2; 153 154SELECT City.Name, Country.Name, CountryLanguage.Language 155 FROM City,Country,CountryLanguage 156 WHERE City.Country=Country.Code AND 157 CountryLanguage.Country=Country.Code AND 158 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 159 CountryLanguage.Percentage > 50 AND 160 LENGTH(Language) < LENGTH(City.Name) - 2; 161 162 163SELECT Country.Name, Country.Population, City.Name, City.Population 164 FROM Country LEFT JOIN City 165 ON City.Country=Country.Code AND City.Population > 5000000 166 WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; 167 168SELECT Country.Name, Country.Population, City.Name, City.Population 169 FROM Country LEFT JOIN City 170 ON City.Country=Country.Code AND 171 (City.Population > 5000000 OR City.Name LIKE 'Za%') 172 WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; 173 174CREATE INDEX City_Population ON City(Population); 175CREATE INDEX City_Name ON City(Name); 176 177--disable_result_log 178ANALYZE TABLE City; 179--enable_result_log 180 181EXPLAIN 182SELECT Country.Name, Country.Population, City.Name, City.Population 183 FROM Country LEFT JOIN City 184 ON City.Country=Country.Code AND City.Population > 5000000 185 WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; 186 187SELECT Country.Name, Country.Population, City.Name, City.Population 188 FROM Country LEFT JOIN City 189 ON City.Country=Country.Code AND City.Population > 5000000 190 WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; 191 192EXPLAIN 193SELECT Country.Name, Country.Population, City.Name, City.Population 194 FROM Country LEFT JOIN City 195 ON City.Country=Country.Code AND 196 (City.Population > 5000000 OR City.Name LIKE 'Za%') 197 WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; 198 199SELECT Country.Name, Country.Population, City.Name, City.Population 200 FROM Country LEFT JOIN City 201 ON City.Country=Country.Code AND 202 (City.Population > 5000000 OR City.Name LIKE 'Za%') 203 WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; 204 205DROP INDEX City_Population ON City; 206DROP INDEX City_Name ON City; 207 208set join_cache_level=1; 209 210set join_buffer_size=256; 211show variables like 'join_buffer_size'; 212 213show variables like 'join_cache_level'; 214 215EXPLAIN 216SELECT City.Name, Country.Name FROM City,Country 217 WHERE City.Country=Country.Code AND 218 Country.Name LIKE 'L%' AND City.Population > 100000; 219 220SELECT City.Name, Country.Name FROM City,Country 221 WHERE City.Country=Country.Code AND 222 Country.Name LIKE 'L%' AND City.Population > 100000; 223 224EXPLAIN 225SELECT City.Name, Country.Name, CountryLanguage.Language 226 FROM City,Country,CountryLanguage 227 WHERE City.Country=Country.Code AND 228 CountryLanguage.Country=Country.Code AND 229 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 230 CountryLanguage.Percentage > 50 AND 231 LENGTH(Language) < LENGTH(City.Name) - 2; 232 233SELECT City.Name, Country.Name, CountryLanguage.Language 234 FROM City,Country,CountryLanguage 235 WHERE City.Country=Country.Code AND 236 CountryLanguage.Country=Country.Code AND 237 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 238 CountryLanguage.Percentage > 50 AND 239 LENGTH(Language) < LENGTH(City.Name) - 2; 240 241set join_cache_level=2; 242show variables like 'join_cache_level'; 243 244EXPLAIN 245SELECT City.Name, Country.Name FROM City,Country 246 WHERE City.Country=Country.Code AND 247 Country.Name LIKE 'L%' AND City.Population > 100000; 248 249SELECT City.Name, Country.Name FROM City,Country 250 WHERE City.Country=Country.Code AND 251 Country.Name LIKE 'L%' AND City.Population > 100000; 252 253EXPLAIN 254SELECT City.Name, Country.Name, CountryLanguage.Language 255 FROM City,Country,CountryLanguage 256 WHERE City.Country=Country.Code AND 257 CountryLanguage.Country=Country.Code AND 258 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 259 CountryLanguage.Percentage > 50 AND 260 LENGTH(Language) < LENGTH(City.Name) - 2; 261 262SELECT City.Name, Country.Name, CountryLanguage.Language 263 FROM City,Country,CountryLanguage 264 WHERE City.Country=Country.Code AND 265 CountryLanguage.Country=Country.Code AND 266 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 267 CountryLanguage.Percentage > 50 AND 268 LENGTH(Language) < LENGTH(City.Name) - 2; 269 270set join_cache_level=3; 271show variables like 'join_cache_level'; 272 273EXPLAIN 274SELECT City.Name, Country.Name FROM City,Country 275 WHERE City.Country=Country.Code AND 276 Country.Name LIKE 'L%' AND City.Population > 100000; 277 278SELECT City.Name, Country.Name FROM City,Country 279 WHERE City.Country=Country.Code AND 280 Country.Name LIKE 'L%' AND City.Population > 100000; 281 282EXPLAIN 283SELECT City.Name, Country.Name, CountryLanguage.Language 284 FROM City,Country,CountryLanguage 285 WHERE City.Country=Country.Code AND 286 CountryLanguage.Country=Country.Code AND 287 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 288 CountryLanguage.Percentage > 50 AND 289 LENGTH(Language) < LENGTH(City.Name) - 2; 290 291SELECT City.Name, Country.Name, CountryLanguage.Language 292 FROM City,Country,CountryLanguage 293 WHERE City.Country=Country.Code AND 294 CountryLanguage.Country=Country.Code AND 295 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 296 CountryLanguage.Percentage > 50 AND 297 LENGTH(Language) < LENGTH(City.Name) - 2; 298 299set join_cache_level=4; 300show variables like 'join_cache_level'; 301 302EXPLAIN 303SELECT City.Name, Country.Name FROM City,Country 304 WHERE City.Country=Country.Code AND 305 Country.Name LIKE 'L%' AND City.Population > 100000; 306 307SELECT City.Name, Country.Name FROM City,Country 308 WHERE City.Country=Country.Code AND 309 Country.Name LIKE 'L%' AND City.Population > 100000; 310 311EXPLAIN 312SELECT City.Name, Country.Name, CountryLanguage.Language 313 FROM City,Country,CountryLanguage 314 WHERE City.Country=Country.Code AND 315 CountryLanguage.Country=Country.Code AND 316 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 317 CountryLanguage.Percentage > 50 AND 318 LENGTH(Language) < LENGTH(City.Name) - 2; 319 320SELECT City.Name, Country.Name, CountryLanguage.Language 321 FROM City,Country,CountryLanguage 322 WHERE City.Country=Country.Code AND 323 CountryLanguage.Country=Country.Code AND 324 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 325 CountryLanguage.Percentage > 50 AND 326 LENGTH(Language) < LENGTH(City.Name) - 2; 327 328set join_cache_level=@save_join_cache_level; 329set join_buffer_size=@save_join_buffer_size; 330 331DROP DATABASE world; 332 333 334CREATE DATABASE world; 335 336use world; 337 338--source include/world_schema.inc 339 340--disable_query_log 341--disable_result_log 342--disable_warnings 343--source include/world.inc 344--enable_warnings 345--enable_result_log 346--enable_query_log 347 348show variables like 'join_buffer_size'; 349set join_cache_level=3; 350show variables like 'join_cache_level'; 351 352EXPLAIN 353SELECT City.Name, Country.Name FROM City,Country 354 WHERE City.Country=Country.Code AND 355 Country.Name LIKE 'L%' AND City.Population > 100000; 356 357SELECT City.Name, Country.Name FROM City,Country 358 WHERE City.Country=Country.Code AND 359 Country.Name LIKE 'L%' AND City.Population > 100000; 360 361EXPLAIN 362SELECT City.Name, Country.Name, CountryLanguage.Language 363 FROM City,Country,CountryLanguage 364 WHERE City.Country=Country.Code AND 365 CountryLanguage.Country=Country.Code AND 366 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 367 CountryLanguage.Percentage > 50 AND 368 LENGTH(Language) < LENGTH(City.Name) - 2; 369 370SELECT City.Name, Country.Name, CountryLanguage.Language 371 FROM City,Country,CountryLanguage 372 WHERE City.Country=Country.Code AND 373 CountryLanguage.Country=Country.Code AND 374 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 375 CountryLanguage.Percentage > 50 AND 376 LENGTH(Language) < LENGTH(City.Name) - 2; 377 378EXPLAIN 379SELECT Name FROM City 380 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND 381 City.Population > 100000; 382 383SELECT Name FROM City 384 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND 385 City.Population > 100000; 386 387EXPLAIN 388SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) 389 FROM Country LEFT JOIN CountryLanguage ON 390 (CountryLanguage.Country=Country.Code AND Language='English') 391 WHERE 392 Country.Population > 10000000; 393 394SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) 395 FROM Country LEFT JOIN CountryLanguage ON 396 (CountryLanguage.Country=Country.Code AND Language='English') 397 WHERE 398 Country.Population > 10000000; 399 400show variables like 'join_buffer_size'; 401set join_cache_level=4; 402show variables like 'join_cache_level'; 403 404EXPLAIN 405SELECT City.Name, Country.Name FROM City,Country 406 WHERE City.Country=Country.Code AND 407 Country.Name LIKE 'L%' AND City.Population > 100000; 408 409SELECT City.Name, Country.Name FROM City,Country 410 WHERE City.Country=Country.Code AND 411 Country.Name LIKE 'L%' AND City.Population > 100000; 412 413EXPLAIN 414SELECT City.Name, Country.Name, CountryLanguage.Language 415 FROM City,Country,CountryLanguage 416 WHERE City.Country=Country.Code AND 417 CountryLanguage.Country=Country.Code AND 418 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 419 CountryLanguage.Percentage > 50 AND 420 LENGTH(Language) < LENGTH(City.Name) - 2; 421 422SELECT City.Name, Country.Name, CountryLanguage.Language 423 FROM City,Country,CountryLanguage 424 WHERE City.Country=Country.Code AND 425 CountryLanguage.Country=Country.Code AND 426 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 427 CountryLanguage.Percentage > 50 AND 428 LENGTH(Language) < LENGTH(City.Name) - 2; 429 430EXPLAIN 431SELECT Name FROM City 432 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND 433 City.Population > 100000; 434 435SELECT Name FROM City 436 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND 437 City.Population > 100000; 438 439EXPLAIN 440SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) 441 FROM Country LEFT JOIN CountryLanguage ON 442 (CountryLanguage.Country=Country.Code AND Language='English') 443 WHERE 444 Country.Population > 10000000; 445 446SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) 447 FROM Country LEFT JOIN CountryLanguage ON 448 (CountryLanguage.Country=Country.Code AND Language='English') 449 WHERE 450 Country.Population > 10000000; 451 452 453--replace_column 9 # 454EXPLAIN 455SELECT Country.Name, Country.Population, City.Name, City.Population 456 FROM Country LEFT JOIN City 457 ON City.Country=Country.Code AND City.Population > 5000000 458 WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; 459 460SELECT Country.Name, Country.Population, City.Name, City.Population 461 FROM Country LEFT JOIN City 462 ON City.Country=Country.Code AND City.Population > 5000000 463 WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; 464 465CREATE INDEX City_Name ON City(Name); 466 467EXPLAIN 468SELECT Country.Name, Country.Population, City.Name, City.Population 469 FROM Country LEFT JOIN City 470 ON City.Country=Country.Code AND 471 (City.Population > 5000000 OR City.Name LIKE 'Za%') 472 WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; 473 474SELECT Country.Name, Country.Population, City.Name, City.Population 475 FROM Country LEFT JOIN City 476 ON City.Country=Country.Code AND 477 (City.Population > 5000000 OR City.Name LIKE 'Za%') 478 WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; 479 480DROP INDEX City_Name ON City; 481 482show variables like 'join_buffer_size'; 483set join_cache_level=5; 484show variables like 'join_cache_level'; 485 486EXPLAIN 487SELECT City.Name, Country.Name FROM City,Country 488 WHERE City.Country=Country.Code AND 489 Country.Name LIKE 'L%' AND City.Population > 100000; 490 491SELECT City.Name, Country.Name FROM City,Country 492 WHERE City.Country=Country.Code AND 493 Country.Name LIKE 'L%' AND City.Population > 100000; 494 495EXPLAIN 496SELECT City.Name, Country.Name, CountryLanguage.Language 497 FROM City,Country,CountryLanguage 498 WHERE City.Country=Country.Code AND 499 CountryLanguage.Country=Country.Code AND 500 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 501 CountryLanguage.Percentage > 50 AND 502 LENGTH(Language) < LENGTH(City.Name) - 2; 503 504SELECT City.Name, Country.Name, CountryLanguage.Language 505 FROM City,Country,CountryLanguage 506 WHERE City.Country=Country.Code AND 507 CountryLanguage.Country=Country.Code AND 508 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 509 CountryLanguage.Percentage > 50 AND 510 LENGTH(Language) < LENGTH(City.Name) - 2; 511 512EXPLAIN 513SELECT Name FROM City 514 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND 515 City.Population > 100000; 516 517SELECT Name FROM City 518 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND 519 City.Population > 100000; 520 521EXPLAIN 522SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) 523 FROM Country LEFT JOIN CountryLanguage ON 524 (CountryLanguage.Country=Country.Code AND Language='English') 525 WHERE 526 Country.Population > 10000000; 527 528SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) 529 FROM Country LEFT JOIN CountryLanguage ON 530 (CountryLanguage.Country=Country.Code AND Language='English') 531 WHERE 532 Country.Population > 10000000; 533 534set join_cache_level=6; 535show variables like 'join_cache_level'; 536 537EXPLAIN 538SELECT City.Name, Country.Name FROM City,Country 539 WHERE City.Country=Country.Code AND 540 Country.Name LIKE 'L%' AND City.Population > 100000; 541 542SELECT City.Name, Country.Name FROM City,Country 543 WHERE City.Country=Country.Code AND 544 Country.Name LIKE 'L%' AND City.Population > 100000; 545 546EXPLAIN 547SELECT City.Name, Country.Name, CountryLanguage.Language 548 FROM City,Country,CountryLanguage 549 WHERE City.Country=Country.Code AND 550 CountryLanguage.Country=Country.Code AND 551 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 552 CountryLanguage.Percentage > 50 AND 553 LENGTH(Language) < LENGTH(City.Name) - 2; 554 555SELECT City.Name, Country.Name, CountryLanguage.Language 556 FROM City,Country,CountryLanguage 557 WHERE City.Country=Country.Code AND 558 CountryLanguage.Country=Country.Code AND 559 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 560 CountryLanguage.Percentage > 50 AND 561 LENGTH(Language) < LENGTH(City.Name) - 2; 562 563EXPLAIN 564SELECT Name FROM City 565 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND 566 City.Population > 100000; 567 568SELECT Name FROM City 569 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND 570 City.Population > 100000; 571 572EXPLAIN 573SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) 574 FROM Country LEFT JOIN CountryLanguage ON 575 (CountryLanguage.Country=Country.Code AND Language='English') 576 WHERE 577 Country.Population > 10000000; 578 579SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) 580 FROM Country LEFT JOIN CountryLanguage ON 581 (CountryLanguage.Country=Country.Code AND Language='English') 582 WHERE 583 Country.Population > 10000000; 584 585set join_cache_level=7; 586show variables like 'join_cache_level'; 587 588EXPLAIN 589SELECT City.Name, Country.Name FROM City,Country 590 WHERE City.Country=Country.Code AND 591 Country.Name LIKE 'L%' AND City.Population > 100000; 592 593SELECT City.Name, Country.Name FROM City,Country 594 WHERE City.Country=Country.Code AND 595 Country.Name LIKE 'L%' AND City.Population > 100000; 596 597EXPLAIN 598SELECT City.Name, Country.Name, CountryLanguage.Language 599 FROM City,Country,CountryLanguage 600 WHERE City.Country=Country.Code AND 601 CountryLanguage.Country=Country.Code AND 602 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 603 CountryLanguage.Percentage > 50 AND 604 LENGTH(Language) < LENGTH(City.Name) - 2; 605 606SELECT City.Name, Country.Name, CountryLanguage.Language 607 FROM City,Country,CountryLanguage 608 WHERE City.Country=Country.Code AND 609 CountryLanguage.Country=Country.Code AND 610 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 611 CountryLanguage.Percentage > 50 AND 612 LENGTH(Language) < LENGTH(City.Name) - 2; 613 614EXPLAIN 615SELECT Name FROM City 616 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND 617 City.Population > 100000; 618 619SELECT Name FROM City 620 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND 621 City.Population > 100000; 622 623EXPLAIN 624SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) 625 FROM Country LEFT JOIN CountryLanguage ON 626 (CountryLanguage.Country=Country.Code AND Language='English') 627 WHERE 628 Country.Population > 10000000; 629 630SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) 631 FROM Country LEFT JOIN CountryLanguage ON 632 (CountryLanguage.Country=Country.Code AND Language='English') 633 WHERE 634 Country.Population > 10000000; 635 636set join_cache_level=8; 637show variables like 'join_cache_level'; 638 639EXPLAIN 640SELECT City.Name, Country.Name FROM City,Country 641 WHERE City.Country=Country.Code AND 642 Country.Name LIKE 'L%' AND City.Population > 100000; 643 644SELECT City.Name, Country.Name FROM City,Country 645 WHERE City.Country=Country.Code AND 646 Country.Name LIKE 'L%' AND City.Population > 100000; 647 648EXPLAIN 649SELECT City.Name, Country.Name, CountryLanguage.Language 650 FROM City,Country,CountryLanguage 651 WHERE City.Country=Country.Code AND 652 CountryLanguage.Country=Country.Code AND 653 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 654 CountryLanguage.Percentage > 50 AND 655 LENGTH(Language) < LENGTH(City.Name) - 2; 656 657SELECT City.Name, Country.Name, CountryLanguage.Language 658 FROM City,Country,CountryLanguage 659 WHERE City.Country=Country.Code AND 660 CountryLanguage.Country=Country.Code AND 661 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 662 CountryLanguage.Percentage > 50 AND 663 LENGTH(Language) < LENGTH(City.Name) - 2; 664 665EXPLAIN 666SELECT Name FROM City 667 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND 668 City.Population > 100000; 669 670SELECT Name FROM City 671 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND 672 City.Population > 100000; 673 674EXPLAIN 675SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) 676 FROM Country LEFT JOIN CountryLanguage ON 677 (CountryLanguage.Country=Country.Code AND Language='English') 678 WHERE 679 Country.Population > 10000000; 680 681SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) 682 FROM Country LEFT JOIN CountryLanguage ON 683 (CountryLanguage.Country=Country.Code AND Language='English') 684 WHERE 685 Country.Population > 10000000; 686 687set join_buffer_size=256; 688show variables like 'join_buffer_size'; 689 690set join_cache_level=3; 691show variables like 'join_cache_level'; 692 693EXPLAIN 694SELECT City.Name, Country.Name FROM City,Country 695 WHERE City.Country=Country.Code AND 696 Country.Name LIKE 'L%' AND City.Population > 100000; 697 698SELECT City.Name, Country.Name FROM City,Country 699 WHERE City.Country=Country.Code AND 700 Country.Name LIKE 'L%' AND City.Population > 100000; 701 702EXPLAIN 703SELECT City.Name, Country.Name, CountryLanguage.Language 704 FROM City,Country,CountryLanguage 705 WHERE City.Country=Country.Code AND 706 CountryLanguage.Country=Country.Code AND 707 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 708 CountryLanguage.Percentage > 50 AND 709 LENGTH(Language) < LENGTH(City.Name) - 2; 710 711SELECT City.Name, Country.Name, CountryLanguage.Language 712 FROM City,Country,CountryLanguage 713 WHERE City.Country=Country.Code AND 714 CountryLanguage.Country=Country.Code AND 715 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 716 CountryLanguage.Percentage > 50 AND 717 LENGTH(Language) < LENGTH(City.Name) - 2; 718 719EXPLAIN 720SELECT Name FROM City 721 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND 722 City.Population > 100000; 723 724SELECT Name FROM City 725 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND 726 City.Population > 100000; 727 728set join_cache_level=4; 729show variables like 'join_cache_level'; 730 731EXPLAIN 732SELECT City.Name, Country.Name FROM City,Country 733 WHERE City.Country=Country.Code AND 734 Country.Name LIKE 'L%' AND City.Population > 100000; 735 736SELECT City.Name, Country.Name FROM City,Country 737 WHERE City.Country=Country.Code AND 738 Country.Name LIKE 'L%' AND City.Population > 100000; 739 740EXPLAIN 741SELECT City.Name, Country.Name, CountryLanguage.Language 742 FROM City,Country,CountryLanguage 743 WHERE City.Country=Country.Code AND 744 CountryLanguage.Country=Country.Code AND 745 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 746 CountryLanguage.Percentage > 50 AND 747 LENGTH(Language) < LENGTH(City.Name) - 2; 748 749SELECT City.Name, Country.Name, CountryLanguage.Language 750 FROM City,Country,CountryLanguage 751 WHERE City.Country=Country.Code AND 752 CountryLanguage.Country=Country.Code AND 753 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 754 CountryLanguage.Percentage > 50 AND 755 LENGTH(Language) < LENGTH(City.Name) - 2; 756 757EXPLAIN 758SELECT Name FROM City 759 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND 760 City.Population > 100000; 761 762SELECT Name FROM City 763 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND 764 City.Population > 100000; 765 766set join_cache_level=5; 767show variables like 'join_cache_level'; 768 769EXPLAIN 770SELECT City.Name, Country.Name FROM City,Country 771 WHERE City.Country=Country.Code AND 772 Country.Name LIKE 'L%' AND City.Population > 100000; 773 774SELECT City.Name, Country.Name FROM City,Country 775 WHERE City.Country=Country.Code AND 776 Country.Name LIKE 'L%' AND City.Population > 100000; 777 778EXPLAIN 779SELECT City.Name, Country.Name, CountryLanguage.Language 780 FROM City,Country,CountryLanguage 781 WHERE City.Country=Country.Code AND 782 CountryLanguage.Country=Country.Code AND 783 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 784 CountryLanguage.Percentage > 50 AND 785 LENGTH(Language) < LENGTH(City.Name) - 2; 786 787SELECT City.Name, Country.Name, CountryLanguage.Language 788 FROM City,Country,CountryLanguage 789 WHERE City.Country=Country.Code AND 790 CountryLanguage.Country=Country.Code AND 791 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 792 CountryLanguage.Percentage > 50 AND 793 LENGTH(Language) < LENGTH(City.Name) - 2; 794 795EXPLAIN 796SELECT Name FROM City 797 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND 798 City.Population > 100000; 799 800SELECT Name FROM City 801 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND 802 City.Population > 100000; 803 804set join_cache_level=6; 805show variables like 'join_cache_level'; 806 807EXPLAIN 808SELECT City.Name, Country.Name FROM City,Country 809 WHERE City.Country=Country.Code AND 810 Country.Name LIKE 'L%' AND City.Population > 100000; 811 812SELECT City.Name, Country.Name FROM City,Country 813 WHERE City.Country=Country.Code AND 814 Country.Name LIKE 'L%' AND City.Population > 100000; 815 816EXPLAIN 817SELECT City.Name, Country.Name, CountryLanguage.Language 818 FROM City,Country,CountryLanguage 819 WHERE City.Country=Country.Code AND 820 CountryLanguage.Country=Country.Code AND 821 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 822 CountryLanguage.Percentage > 50 AND 823 LENGTH(Language) < LENGTH(City.Name) - 2; 824 825SELECT City.Name, Country.Name, CountryLanguage.Language 826 FROM City,Country,CountryLanguage 827 WHERE City.Country=Country.Code AND 828 CountryLanguage.Country=Country.Code AND 829 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 830 CountryLanguage.Percentage > 50 AND 831 LENGTH(Language) < LENGTH(City.Name) - 2; 832 833EXPLAIN 834SELECT Name FROM City 835 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND 836 City.Population > 100000; 837 838SELECT Name FROM City 839 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND 840 City.Population > 100000; 841 842set join_cache_level=7; 843show variables like 'join_cache_level'; 844 845EXPLAIN 846SELECT City.Name, Country.Name FROM City,Country 847 WHERE City.Country=Country.Code AND 848 Country.Name LIKE 'L%' AND City.Population > 100000; 849 850SELECT City.Name, Country.Name FROM City,Country 851 WHERE City.Country=Country.Code AND 852 Country.Name LIKE 'L%' AND City.Population > 100000; 853 854EXPLAIN 855SELECT City.Name, Country.Name, CountryLanguage.Language 856 FROM City,Country,CountryLanguage 857 WHERE City.Country=Country.Code AND 858 CountryLanguage.Country=Country.Code AND 859 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 860 CountryLanguage.Percentage > 50 AND 861 LENGTH(Language) < LENGTH(City.Name) - 2; 862 863SELECT City.Name, Country.Name, CountryLanguage.Language 864 FROM City,Country,CountryLanguage 865 WHERE City.Country=Country.Code AND 866 CountryLanguage.Country=Country.Code AND 867 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 868 CountryLanguage.Percentage > 50 AND 869 LENGTH(Language) < LENGTH(City.Name) - 2; 870 871EXPLAIN 872SELECT Name FROM City 873 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND 874 City.Population > 100000; 875 876SELECT Name FROM City 877 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND 878 City.Population > 100000; 879 880set join_cache_level=8; 881show variables like 'join_cache_level'; 882 883EXPLAIN 884SELECT City.Name, Country.Name FROM City,Country 885 WHERE City.Country=Country.Code AND 886 Country.Name LIKE 'L%' AND City.Population > 100000; 887 888SELECT City.Name, Country.Name FROM City,Country 889 WHERE City.Country=Country.Code AND 890 Country.Name LIKE 'L%' AND City.Population > 100000; 891 892EXPLAIN 893SELECT City.Name, Country.Name, CountryLanguage.Language 894 FROM City,Country,CountryLanguage 895 WHERE City.Country=Country.Code AND 896 CountryLanguage.Country=Country.Code AND 897 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 898 CountryLanguage.Percentage > 50 AND 899 LENGTH(Language) < LENGTH(City.Name) - 2; 900 901SELECT City.Name, Country.Name, CountryLanguage.Language 902 FROM City,Country,CountryLanguage 903 WHERE City.Country=Country.Code AND 904 CountryLanguage.Country=Country.Code AND 905 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 906 CountryLanguage.Percentage > 50 AND 907 LENGTH(Language) < LENGTH(City.Name) - 2; 908 909EXPLAIN 910SELECT Name FROM City 911 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND 912 City.Population > 100000; 913 914SELECT Name FROM City 915 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND 916 City.Population > 100000; 917 918set join_cache_level=@save_join_cache_level; 919set join_buffer_size=@save_join_buffer_size; 920 921set join_cache_level=1; 922 923SELECT City.Name, Country.Name FROM City,Country 924 WHERE City.Country=Country.Code AND City.Population > 3000000; 925 926set join_cache_level=8; 927set join_buffer_size=384; 928 929--replace_column 9 # 930EXPLAIN 931SELECT City.Name, Country.Name FROM City,Country 932 WHERE City.Country=Country.Code AND City.Population > 3000000; 933 934--sorted_result 935SELECT City.Name, Country.Name FROM City,Country 936 WHERE City.Country=Country.Code AND City.Population > 3000000; 937 938set join_buffer_size=@save_join_buffer_size; 939 940set join_cache_level=6; 941 942ALTER TABLE Country MODIFY Name varchar(52) NOT NULL default ''; 943 944SELECT City.Name, Country.Name FROM City,Country 945 WHERE City.Country=Country.Code AND 946 Country.Name LIKE 'L%' AND City.Population > 100000; 947 948ALTER TABLE Country MODIFY Name varchar(300) NOT NULL default ''; 949 950SELECT City.Name, Country.Name FROM City,Country 951 WHERE City.Country=Country.Code AND 952 Country.Name LIKE 'L%' AND City.Population > 100000; 953 954ALTER TABLE Country ADD COLUMN PopulationBar text; 955UPDATE Country 956 SET PopulationBar=REPEAT('x', CAST(Population/100000 AS unsigned int)); 957 958SELECT City.Name, Country.Name, Country.PopulationBar FROM City,Country 959 WHERE City.Country=Country.Code AND 960 Country.Name LIKE 'L%' AND City.Population > 100000; 961 962set join_buffer_size=256; 963 964SELECT City.Name, Country.Name, Country.PopulationBar FROM City,Country 965 WHERE City.Country=Country.Code AND 966 Country.Name LIKE 'L%' AND City.Population > 100000; 967 968set join_cache_level=@save_join_cache_level; 969set join_buffer_size=@save_join_buffer_size; 970 971 972--echo # 973--echo # MDEV-17752: Plan changes from hash_index_merge to index_merge with new optimizer defaults 974--echo # 975 976set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; 977set @save_use_stat_tables=@@use_stat_tables; 978set optimizer_use_condition_selectivity=4; 979set use_stat_tables='preferably'; 980 981use world; 982set join_cache_level=4; 983CREATE INDEX City_Name ON City(Name); 984 985--disable_result_log 986ANALYZE TABLE City, Country; 987--enable_result_log 988 989EXPLAIN 990SELECT Country.Name, Country.Population, City.Name, City.Population 991 FROM Country LEFT JOIN City 992 ON City.Country=Country.Code AND City.Population > 5000000 993 WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; 994 995EXPLAIN 996SELECT Country.Name, Country.Population, City.Name, City.Population 997 FROM Country LEFT JOIN City 998 ON City.Country=Country.Code AND 999 (City.Population > 5000000 OR City.Name LIKE 'Za%') 1000 WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; 1001set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; 1002set @@use_stat_tables=@save_use_stat_tables; 1003set @@join_cache_level=@save_join_cache_level; 1004 1005DROP DATABASE world; 1006 1007use test; 1008 1009# 1010# Bug #35685: assertion abort when initializing a BKA cache 1011# 1012 1013CREATE TABLE t1( 1014 affiliatetometaid int NOT NULL default '0', 1015 uniquekey int NOT NULL default '0', 1016 metaid int NOT NULL default '0', 1017 affiliateid int NOT NULL default '0', 1018 xml text, 1019 isactive char(1) NOT NULL default 'Y', 1020 PRIMARY KEY (affiliatetometaid) 1021); 1022CREATE UNIQUE INDEX t1_uniquekey ON t1(uniquekey); 1023CREATE INDEX t1_affiliateid ON t1(affiliateid); 1024CREATE INDEX t1_metaid on t1 (metaid); 1025INSERT INTO t1 VALUES 1026 (1616, 1571693233, 1391, 2, NULL, 'Y'), (1943, 1993216749, 1726, 2, NULL, 'Y'); 1027 1028CREATE TABLE t2( 1029 metaid int NOT NULL default '0', 1030 name varchar(80) NOT NULL default '', 1031 dateadded timestamp NOT NULL , 1032 xml text, 1033 status int default NULL, 1034 origin int default NULL, 1035 gid int NOT NULL default '1', 1036 formattypeid int default NULL, 1037 PRIMARY KEY (metaid) 1038); 1039CREATE INDEX t2_status ON t2(status); 1040CREATE INDEX t2_gid ON t2(gid); 1041CREATE INDEX t2_formattypeid ON t2(formattypeid); 1042INSERT INTO t2 VALUES 1043 (1391, "I Just Died", "2003-10-02 10:07:37", "", 1, NULL, 3, NULL), 1044 (1726, "Me, Myself & I", "2003-12-05 11:24:36", " ", 1, NULL, 3, NULL); 1045 1046CREATE TABLE t3( 1047 mediaid int NOT NULL , 1048 metaid int NOT NULL default '0', 1049 formatid int NOT NULL default '0', 1050 status int default NULL, 1051 path varchar(100) NOT NULL default '', 1052 datemodified timestamp NOT NULL , 1053 resourcetype int NOT NULL default '1', 1054 parameters text, 1055 signature int default NULL, 1056 quality int NOT NULL default '255', 1057 PRIMARY KEY (mediaid) 1058); 1059CREATE INDEX t3_metaid ON t3(metaid); 1060CREATE INDEX t3_formatid ON t3(formatid); 1061CREATE INDEX t3_status ON t3(status); 1062CREATE INDEX t3_metaidformatid ON t3(metaid,formatid); 1063CREATE INDEX t3_signature ON t3(signature); 1064CREATE INDEX t3_quality ON t3(quality); 1065INSERT INTO t3 VALUES 1066 (6, 4, 8, 0, "010101_anastacia_spmidi.mid", "2004-03-16 13:40:00", 1, NULL, NULL, 255), 1067 (3343, 3, 8, 1, "010102_4VN4bsPwnxRQUJW5Zp1RhG2IL9vvl_8.mid", "2004-03-16 13:40:00", 1, NULL, NULL, 255); 1068 1069CREATE TABLE t4( 1070 formatid int NOT NULL , 1071 name varchar(60) NOT NULL default '', 1072 formatclassid int NOT NULL default '0', 1073 mime varchar(60) default NULL, 1074 extension varchar(10) default NULL, 1075 priority int NOT NULL default '0', 1076 canaddtocapability char(1) NOT NULL default 'Y', 1077 PRIMARY KEY (formatid) 1078); 1079CREATE INDEX t4_formatclassid ON t4(formatclassid); 1080CREATE INDEX t4_formats_idx ON t4(canaddtocapability); 1081INSERT INTO t4 VALUES 1082 (19, "XHTML", 11, "text/html", "xhtml", 10, 'Y'), 1083 (54, "AMR (wide band)", 13, "audio/amr-wb", "awb", 0, 'Y'); 1084 1085CREATE TABLE t5( 1086 formatclassid int NOT NULL , 1087 name varchar(60) NOT NULL default '', 1088 priority int NOT NULL default '0', 1089 formattypeid int NOT NULL default '0', 1090 PRIMARY KEY (formatclassid) 1091); 1092CREATE INDEX t5_formattypeid on t5(formattypeid); 1093INSERT INTO t5 VALUES 1094 (11, "Info", 0, 4), (13, "Digital Audio", 0, 2); 1095 1096CREATE TABLE t6( 1097 formattypeid int NOT NULL , 1098 name varchar(60) NOT NULL default '', 1099 priority int default NULL, 1100 PRIMARY KEY (formattypeid) 1101); 1102INSERT INTO t6 VALUES 1103 (2, "Ringtones", 0); 1104 1105CREATE TABLE t7( 1106 metaid int NOT NULL default '0', 1107 artistid int NOT NULL default '0', 1108 PRIMARY KEY (metaid,artistid) 1109); 1110INSERT INTO t7 VALUES 1111 (4, 5), (3, 4); 1112 1113CREATE TABLE t8( 1114 artistid int NOT NULL , 1115 name varchar(80) NOT NULL default '', 1116 PRIMARY KEY (artistid) 1117); 1118INSERT INTO t8 VALUES 1119 (5, "Anastacia"), (4, "John Mayer"); 1120 1121CREATE TABLE t9( 1122 subgenreid int NOT NULL default '0', 1123 metaid int NOT NULL default '0', 1124 PRIMARY KEY (subgenreid,metaid) 1125) ; 1126CREATE INDEX t9_subgenreid ON t9(subgenreid); 1127CREATE INDEX t9_metaid ON t9(metaid); 1128INSERT INTO t9 VALUES 1129 (138, 4), (31, 3); 1130 1131CREATE TABLE t10( 1132 subgenreid int NOT NULL , 1133 genreid int NOT NULL default '0', 1134 name varchar(80) NOT NULL default '', 1135 PRIMARY KEY (subgenreid) 1136) ; 1137CREATE INDEX t10_genreid ON t10(genreid); 1138INSERT INTO t10 VALUES 1139 (138, 19, ''), (31, 3, ''); 1140 1141CREATE TABLE t11( 1142 genreid int NOT NULL default '0', 1143 name char(80) NOT NULL default '', 1144 priority int NOT NULL default '0', 1145 masterclip char(1) default NULL, 1146 PRIMARY KEY (genreid) 1147) ; 1148CREATE INDEX t11_masterclip ON t11( masterclip); 1149INSERT INTO t11 VALUES 1150 (19, "Pop & Dance", 95, 'Y'), (3, "Rock & Alternative", 100, 'Y'); 1151 1152set join_cache_level=6; 1153 1154EXPLAIN 1155SELECT t1.uniquekey, t1.xml AS affiliateXml, 1156 t8.name AS artistName, t8.artistid, 1157 t11.name AS genreName, t11.genreid, t11.priority AS genrePriority, 1158 t10.subgenreid, t10.name AS subgenreName, 1159 t2.name AS metaName, t2.metaid, t2.xml AS metaXml, 1160 t4.priority + t5.priority + t6.priority AS overallPriority, 1161 t3.path AS path, t3.mediaid, 1162 t4.formatid, t4.name AS formatName, 1163 t5.formatclassid, t5.name AS formatclassName, 1164 t6.formattypeid, t6.name AS formattypeName 1165FROM t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11 1166WHERE t7.metaid = t2.metaid AND t7.artistid = t8.artistid AND 1167 t9.metaid = t2.metaid AND t9.subgenreid = t10.subgenreid AND 1168 t10.genreid = t11.genreid AND t3.metaid = t2.metaid AND 1169 t3.formatid = t4.formatid AND t4.formatclassid = t5.formatclassid AND 1170 t4.canaddtocapability = 'Y' AND t5.formattypeid = t6.formattypeid AND 1171 t6.formattypeid IN (2) AND (t3.formatid IN (31, 8, 76)) AND 1172 t1.metaid = t2.metaid AND t1.affiliateid = '2'; 1173 1174SELECT t1.uniquekey, t1.xml AS affiliateXml, 1175 t8.name AS artistName, t8.artistid, 1176 t11.name AS genreName, t11.genreid, t11.priority AS genrePriority, 1177 t10.subgenreid, t10.name AS subgenreName, 1178 t2.name AS metaName, t2.metaid, t2.xml AS metaXml, 1179 t4.priority + t5.priority + t6.priority AS overallPriority, 1180 t3.path AS path, t3.mediaid, 1181 t4.formatid, t4.name AS formatName, 1182 t5.formatclassid, t5.name AS formatclassName, 1183 t6.formattypeid, t6.name AS formattypeName 1184FROM t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11 1185WHERE t7.metaid = t2.metaid AND t7.artistid = t8.artistid AND 1186 t9.metaid = t2.metaid AND t9.subgenreid = t10.subgenreid AND 1187 t10.genreid = t11.genreid AND t3.metaid = t2.metaid AND 1188 t3.formatid = t4.formatid AND t4.formatclassid = t5.formatclassid AND 1189 t4.canaddtocapability = 'Y' AND t5.formattypeid = t6.formattypeid AND 1190 t6.formattypeid IN (2) AND (t3.formatid IN (31, 8, 76)) AND 1191 t1.metaid = t2.metaid AND t1.affiliateid = '2'; 1192 1193DROP TABLE t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11; 1194 1195# 1196# Bug #37131: 3-way join query with BKA used with a small buffer and 1197# only for the third table 1198# 1199 1200CREATE TABLE t1 (a1 int, filler1 char(64) default ' ' ); 1201CREATE TABLE t2 ( 1202 a2 int, b2 int, filler2 char(64) default ' ', 1203 PRIMARY KEY idx(a2,b2,filler2) 1204) ; 1205CREATE TABLE t3 (b3 int, c3 int, INDEX idx(b3)); 1206 1207INSERT INTO t1(a1) VALUES 1208 (4), (7), (1), (9), (8), (5), (3), (6), (2); 1209INSERT INTO t2(a2,b2) VALUES 1210 (1,30), (3,40), (2,61), (6,73), (8,92), (9,27), (4,18), (5,84), (7,56), 1211 (4,14), (6,76), (8,98), (7,55), (1,39), (2,68), (3,45), (9,21), (5,81), 1212 (5,88), (2,65), (6,74), (9,23), (1,37), (3,44), (4,17), (8,99), (7,51), 1213 (9,28), (7,52), (1,33), (4,13), (5,87), (3,43), (8,91), (2,62), (6,79), 1214 (3,49), (8,93), (7,34), (5,82), (6,78), (2,63), (1,32), (9,22), (4,11); 1215INSERT INTO t3 VALUES 1216 (30,302), (92,923), (18,187), (45,459), (30,309), 1217 (39,393), (68,685), (45,458), (21,210), (81,817), 1218 (40,405), (61,618), (73,738), (92,929), (27,275), 1219 (18,188), (84,846), (56,564), (14,144), (76,763), 1220 (98,982), (55,551), (17,174), (99,998), (51,513), 1221 (28,282), (52,527), (33,336), (13,138), (87,878), 1222 (43,431), (91,916), (62,624), (79,797), (49,494), 1223 (93,933), (34,347), (82,829), (78,780), (63,634), 1224 (32,329), (22,228), (11,114), (74,749), (23,236); 1225 1226set join_cache_level=1; 1227 1228EXPLAIN 1229SELECT a1<>a2, a1, a2, b2, b3, c3, 1230 SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2 1231FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7; 1232 1233SELECT a1<>a2, a1, a2, b2, b3, c3, 1234 SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2 1235FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7; 1236 1237set join_cache_level=5; 1238set join_buffer_size=512; 1239 1240EXPLAIN 1241SELECT a1<>a2, a1, a2, b2, b3, c3, 1242 SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2 1243FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7; 1244 1245SELECT a1<>a2, a1, a2, b2, b3, c3, 1246 SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2 1247FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7; 1248 1249DROP TABLE t1,t2,t3; 1250 1251# 1252# Bug #37690: crash with a tiny buffer when using BKA_JOIN_CACHE_UNIQUE 1253# 1254 1255CREATE TABLE t1 (a int, b int, INDEX idx(b)); 1256CREATE TABLE t2 (a int, b int, INDEX idx(a)); 1257INSERT INTO t1 VALUES (5,30), (3,20), (7,40), (2,10), (8,30), (1,10), (4,20); 1258INSERT INTO t2 VALUES (7,10), (1,20), (2,20), (8,20), (8,10), (1,20); 1259INSERT INTO t2 VALUES (1,10), (4,20), (3,20), (7,20), (7,10), (1,20); 1260INSERT INTO t2 VALUES (17,10), (11,20), (12,20), (18,20), (18,10), (11,20); 1261INSERT INTO t2 VALUES (11,10), (14,20), (13,20), (17,20), (17,10), (11,20); 1262 1263set join_buffer_size=32; 1264set join_cache_level=8; 1265 1266EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b >= 30; 1267--sorted_result 1268SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b >= 30; 1269 1270DROP TABLE t1,t2; 1271 1272--echo # 1273--echo # Bug #40134: outer join with not exists optimization and join buffer 1274--echo # 1275 1276set join_cache_level=@save_join_cache_level; 1277set join_buffer_size=@save_join_buffer_size; 1278 1279CREATE TABLE t1 (a int NOT NULL); 1280INSERT INTO t1 VALUES (2), (4), (3), (5), (1); 1281CREATE TABLE t2 (a int NOT NULL, b int NOT NULL, INDEX i_a(a)); 1282INSERT INTO t2 VALUES (4,10), (2,10), (2,30), (2,20), (4,20); 1283INSERT INTO t2 VALUES (14,10), (12,10), (15,30), (12,20), (14,20); 1284 1285EXPLAIN 1286SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL; 1287SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL; 1288 1289SET join_cache_level=6; 1290EXPLAIN 1291SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL; 1292SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL; 1293 1294DROP TABLE t1, t2; 1295 1296set join_cache_level=@save_join_cache_level; 1297set join_buffer_size=@save_join_buffer_size; 1298 1299--echo # 1300--echo # BUG#40136: Group by is ignored when join buffer is used for an outer join 1301--echo # 1302create table t1(a int PRIMARY KEY, b int); 1303insert into t1 values 1304 (5, 10), (2, 70), (7, 80), (6, 20), (1, 50), (9, 40), (8, 30), (3, 60); 1305create table t2 (p int, a int, INDEX i_a(a)); 1306insert into t2 values 1307 (103, 7), (109, 3), (102, 3), (108, 1), (106, 3), 1308 (107, 7), (105, 1), (101, 3), (100, 7), (110, 1); 1309set @save_join_cache_level=@@join_cache_level; 1310set join_cache_level=6; 1311--echo The following must not show "using join cache": 1312explain 1313select t1.a, count(t2.p) as count 1314 from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a; 1315select t1.a, count(t2.p) as count 1316 from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a; 1317set join_cache_level=@save_join_cache_level; 1318drop table t1, t2; 1319 1320--echo # 1321--echo # BUG#40268: Nested outer join with not null-rejecting where condition 1322--echo # over an inner table which is not the last in the nest 1323--echo # 1324 1325CREATE TABLE t2 (a int, b int, c int); 1326CREATE TABLE t3 (a int, b int, c int); 1327CREATE TABLE t4 (a int, b int, c int); 1328 1329INSERT INTO t2 VALUES (3,3,0), (4,2,0), (5,3,0); 1330INSERT INTO t3 VALUES (1,2,0), (2,2,0); 1331INSERT INTO t4 VALUES (3,2,0), (4,2,0); 1332 1333set join_cache_level=6; 1334 1335SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b 1336 FROM t2 LEFT JOIN (t3, t4) ON t2.b=t4.b 1337 WHERE t3.a+2<t2.a OR t3.c IS NULL; 1338 1339set join_cache_level=@save_join_cache_level; 1340DROP TABLE t2, t3, t4; 1341 1342--echo # 1343--echo # Bug #40192: outer join with where clause when using BNL 1344--echo # 1345 1346create table t1 (a int, b int); 1347insert into t1 values (2, 20), (3, 30), (1, 10); 1348create table t2 (a int, c int); 1349insert into t2 values (1, 101), (3, 102), (1, 100); 1350 1351set join_cache_level=6; 1352 1353select * from t1 left join t2 on t1.a=t2.a; 1354explain select * from t1 left join t2 on t1.a=t2.a where t2.c=102 or t2.c is null; 1355select * from t1 left join t2 on t1.a=t2.a where t2.c=102 or t2.c is null; 1356 1357set join_cache_level=@save_join_cache_level; 1358drop table t1, t2; 1359 1360--echo # 1361--echo # Bug #40317: outer join with with constant on expression equal to FALSE 1362--echo # 1363 1364create table t1 (a int); 1365insert into t1 values (30), (40), (20); 1366create table t2 (b int); 1367insert into t2 values (200), (100); 1368 1369set join_cache_level=6; 1370 1371select * from t1 left join t2 on (1=0); 1372explain select * from t1 left join t2 on (1=0) where a=40; 1373select * from t1 left join t2 on (1=0) where a=40; 1374 1375set join_cache_level=0; 1376explain select * from t1 left join t2 on (1=0); 1377 1378set join_cache_level=@save_join_cache_level; 1379drop table t1, t2; 1380 1381--echo # 1382--echo # Bug #41204: small buffer with big rec_per_key for ref access 1383--echo # 1384 1385CREATE TABLE t1 (a int); 1386 1387INSERT INTO t1 VALUES (0); 1388INSERT INTO t1(a) SELECT a FROM t1; 1389INSERT INTO t1(a) SELECT a FROM t1; 1390INSERT INTO t1(a) SELECT a FROM t1; 1391INSERT INTO t1(a) SELECT a FROM t1; 1392INSERT INTO t1(a) SELECT a FROM t1; 1393INSERT INTO t1(a) SELECT a FROM t1; 1394INSERT INTO t1(a) SELECT a FROM t1; 1395INSERT INTO t1(a) SELECT a FROM t1; 1396INSERT INTO t1(a) SELECT a FROM t1; 1397INSERT INTO t1(a) SELECT a FROM t1; 1398INSERT INTO t1(a) SELECT a FROM t1; 1399INSERT INTO t1 VALUES (20000), (10000); 1400 1401CREATE TABLE t2 (pk int AUTO_INCREMENT PRIMARY KEY, b int, c int, INDEX idx(b)); 1402INSERT INTO t2(b,c) VALUES (10000, 3), (20000, 7), (20000, 1), (10000, 9), (20000, 5); 1403INSERT INTO t2(b,c) SELECT b,c FROM t2; 1404INSERT INTO t2(b,c) SELECT b,c FROM t2; 1405INSERT INTO t2(b,c) SELECT b,c FROM t2; 1406INSERT INTO t2(b,c) SELECT b,c FROM t2; 1407INSERT INTO t2(b,c) SELECT b,c FROM t2; 1408INSERT INTO t2(b,c) SELECT b,c FROM t2; 1409INSERT INTO t2(b,c) SELECT b,c FROM t2; 1410INSERT INTO t2(b,c) SELECT b,c FROM t2; 1411 1412--disable_result_log 1413ANALYZE TABLE t1,t2; 1414--enable_result_log 1415 1416set join_cache_level=6; 1417set join_buffer_size=1024; 1418 1419EXPLAIN SELECT AVG(c) FROM t1,t2 WHERE t1.a=t2.b; 1420SELECT AVG(c) FROM t1,t2 WHERE t1.a=t2.b; 1421 1422set join_buffer_size=@save_join_buffer_size; 1423set join_cache_level=@save_join_cache_level; 1424 1425DROP TABLE t1, t2; 1426 1427--echo # 1428--echo # Bug #41894: big join buffer of level 7 used to join records 1429--echo # with null values in place of varchar strings 1430--echo # 1431 1432CREATE TABLE t1 (a int NOT NULL AUTO_INCREMENT PRIMARY KEY, 1433 b varchar(127) DEFAULT NULL); 1434 1435INSERT INTO t1(a) VALUES (1); 1436INSERT INTO t1(b) SELECT b FROM t1; 1437INSERT INTO t1(b) SELECT b FROM t1; 1438INSERT INTO t1(b) SELECT b FROM t1; 1439INSERT INTO t1(b) SELECT b FROM t1; 1440INSERT INTO t1(b) SELECT b FROM t1; 1441INSERT INTO t1(b) SELECT b FROM t1; 1442INSERT INTO t1(b) SELECT b FROM t1; 1443INSERT INTO t1(b) SELECT b FROM t1; 1444INSERT INTO t1(b) SELECT b FROM t1; 1445INSERT INTO t1(b) SELECT b FROM t1; 1446INSERT INTO t1(b) SELECT b FROM t1; 1447INSERT INTO t1(b) SELECT b FROM t1; 1448INSERT INTO t1(b) SELECT b FROM t1; 1449INSERT INTO t1(b) SELECT b FROM t1; 1450 1451CREATE TABLE t2 (a int NOT NULL PRIMARY KEY, b varchar(127) DEFAULT NULL); 1452INSERT INTO t2 SELECT * FROM t1; 1453 1454CREATE TABLE t3 (a int NOT NULL PRIMARY KEY, b varchar(127) DEFAULT NULL); 1455INSERT INTO t3 SELECT * FROM t1; 1456 1457set join_cache_level=7; 1458set join_buffer_size=1024*1024; 1459 1460EXPLAIN 1461SELECT COUNT(*) FROM t1,t2,t3 1462 WHERE t1.a=t2.a AND t2.a=t3.a AND 1463 t1.b IS NULL AND t2.b IS NULL AND t3.b IS NULL; 1464 1465SELECT COUNT(*) FROM t1,t2,t3 1466 WHERE t1.a=t2.a AND t2.a=t3.a AND 1467 t1.b IS NULL AND t2.b IS NULL AND t3.b IS NULL; 1468 1469set join_buffer_size=@save_join_buffer_size; 1470set join_cache_level=@save_join_cache_level; 1471 1472DROP TABLE t1,t2,t3; 1473 1474--echo # 1475--echo # Bug #42020: join buffer is used for outer join with fields of 1476--echo # several outer tables in join buffer 1477--echo # 1478 1479CREATE TABLE t1 ( 1480 a bigint NOT NULL, 1481 PRIMARY KEY (a) 1482); 1483INSERT INTO t1 VALUES 1484 (2), (1); 1485 1486CREATE TABLE t2 ( 1487 a bigint NOT NULL, 1488 b bigint NOT NULL, 1489 PRIMARY KEY (a,b) 1490); 1491INSERT INTO t2 VALUES 1492 (2,30), (2,40), (2,50), (2,60), (2,70), (2,80), 1493 (1,10), (1, 20), (1,30), (1,40), (1,50); 1494 1495CREATE TABLE t3 ( 1496 pk bigint NOT NULL AUTO_INCREMENT, 1497 a bigint NOT NULL, 1498 b bigint NOT NULL, 1499 val bigint DEFAULT '0', 1500 PRIMARY KEY (pk), 1501 KEY idx (a,b) 1502); 1503INSERT INTO t3(a,b) VALUES 1504 (2,30), (2,40), (2,50), (2,60), (2,70), (2,80), 1505 (4,30), (4,40), (4,50), (4,60), (4,70), (4,80), 1506 (5,30), (5,40), (5,50), (5,60), (5,70), (5,80), 1507 (7,30), (7,40), (7,50), (7,60), (7,70), (7,80); 1508 1509set join_cache_level=0; 1510 1511SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val 1512 FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) 1513 WHERE t1.a=t2.a; 1514 1515set join_cache_level=6; 1516set join_buffer_size=256; 1517 1518EXPLAIN 1519SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val 1520 FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) 1521 WHERE t1.a=t2.a; 1522--sorted_result 1523SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val 1524 FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) 1525 WHERE t1.a=t2.a; 1526 1527DROP INDEX idx ON t3; 1528set join_cache_level=2; 1529 1530EXPLAIN 1531SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val 1532 FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) 1533 WHERE t1.a=t2.a; 1534 1535--sorted_result 1536SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val 1537 FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) 1538 WHERE t1.a=t2.a; 1539 1540set join_buffer_size=@save_join_buffer_size; 1541set join_cache_level=@save_join_cache_level; 1542 1543DROP TABLE t1,t2,t3; 1544 1545# 1546# WL#4424 Full index condition pushdown with batched key access join 1547# 1548create table t1(f1 int, f2 int); 1549insert into t1 values (1,1),(2,2),(3,3); 1550create table t2(f1 int not null, f2 int not null, f3 char(200), key(f1,f2)); 1551insert into t2 values (1,1, 'qwerty'),(1,2, 'qwerty'),(1,3, 'qwerty'); 1552insert into t2 values (2,1, 'qwerty'),(2,2, 'qwerty'),(2,3, 'qwerty'), 1553 (2,4, 'qwerty'),(2,5, 'qwerty'); 1554insert into t2 values (3,1, 'qwerty'),(3,4, 'qwerty'); 1555insert into t2 values (4,1, 'qwerty'),(4,2, 'qwerty'),(4,3, 'qwerty'), 1556 (4,4, 'qwerty'); 1557insert into t2 values (1,1, 'qwerty'),(1,2, 'qwerty'),(1,3, 'qwerty'); 1558insert into t2 values (2,1, 'qwerty'),(2,2, 'qwerty'),(2,3, 'qwerty'), 1559 (2,4, 'qwerty'),(2,5, 'qwerty'); 1560insert into t2 values (3,1, 'qwerty'),(3,4, 'qwerty'); 1561insert into t2 values (4,1, 'qwerty'),(4,2, 'qwerty'),(4,3, 'qwerty'), 1562 (4,4, 'qwerty'); 1563 1564flush status; 1565set join_cache_level=5; 1566select t2.f1, t2.f2, t2.f3 from t1,t2 1567where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1; 1568 1569explain select t2.f1, t2.f2, t2.f3 from t1,t2 1570where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2; 1571show status like "Handler_icp%"; 1572 1573set join_cache_level=6; 1574select t2.f1, t2.f2, t2.f3 from t1,t2 1575where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1; 1576 1577explain select t2.f1, t2.f2, t2.f3 from t1,t2 1578where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2; 1579show status like "Handler_icp%"; 1580 1581set join_cache_level=7; 1582select t2.f1, t2.f2, t2.f3 from t1,t2 1583where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1; 1584 1585explain select t2.f1, t2.f2, t2.f3 from t1,t2 1586where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2; 1587show status like "Handler_icp%"; 1588 1589set join_cache_level=8; 1590select t2.f1, t2.f2, t2.f3 from t1,t2 1591where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1; 1592 1593explain select t2.f1, t2.f2, t2.f3 from t1,t2 1594where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2; 1595show status like "Handler_icp%"; 1596 1597drop table t1,t2; 1598set join_cache_level=@save_join_cache_level; 1599 1600--echo # 1601--echo # Bug #42955: join with GROUP BY/ORDER BY and when BKA is enabled 1602--echo # 1603 1604create table t1 (d int, id1 int, index idx1 (d, id1)); 1605insert into t1 values 1606 (3, 20), (2, 40), (3, 10), (1, 10), (3, 20), (1, 40), (2, 30), (3, 30); 1607 1608create table t2 (id1 int, id2 int, index idx2 (id1)); 1609insert into t2 values 1610 (20, 100), (30, 400), (20, 400), (30, 200), (10, 300), (10, 200), (40, 100), 1611 (40, 200), (30, 300), (10, 400), (20, 200), (20, 300); 1612insert into t2 values 1613 (21, 10), (31, 400), (21, 400), (31, 200), (11, 300), (11, 200), (41, 100), 1614 (41, 200), (31, 300), (11, 400), (21, 200), (21, 300); 1615 1616set join_cache_level=6; 1617 1618explain 1619select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1 1620 where t1.d=3 group by t1.id1; 1621 1622select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1 1623 where t1.d=3 group by t1.id1; 1624 1625explain 1626select t1.id1 from t1 join t2 on t1.id1=t2.id1 1627 where t1.d=3 and t2.id2 > 200 order by t1.id1; 1628 1629select t1.id1 from t1 join t2 on t1.id1=t2.id1 1630 where t1.d=3 and t2.id2 > 200 order by t1.id1; 1631 1632set join_cache_level=@save_join_cache_level; 1633 1634drop table t1,t2; 1635 1636--echo # 1637--echo # Bug #44019: star-like multi-join query executed join_cache_level=6 1638--echo # 1639 1640create table t1 (a int, b int, c int, d int); 1641create table t2 (b int, e varchar(16), index idx(b)); 1642create table t3 (d int, f varchar(16), index idx(d)); 1643create table t4 (c int, g varchar(16), index idx(c)); 1644 1645insert into t1 values 1646 (5, 50, 500, 5000), (3, 30, 300, 3000), (9, 90, 900, 9000), 1647 (2, 20, 200, 2000), (4, 40, 400, 4000), (8, 80, 800, 800), 1648 (7, 70, 700, 7000); 1649insert into t2 values 1650 (30, 'bbb'), (10, 'b'), (70, 'bbbbbbb'), (60, 'bbbbbb'), 1651 (31, 'bbb'), (11, 'b'), (71, 'bbbbbbb'), (61, 'bbbbbb'), 1652 (32, 'bbb'), (12, 'b'), (72, 'bbbbbbb'), (62, 'bbbbbb'); 1653insert into t2 values 1654 (130, 'bbb'), (110, 'b'), (170, 'bbbbbbb'), (160, 'bbbbbb'), 1655 (131, 'bbb'), (111, 'b'), (171, 'bbbbbbb'), (161, 'bbbbbb'), 1656 (132, 'bbb'), (112, 'b'), (172, 'bbbbbbb'), (162, 'bbbbbb'); 1657insert into t3 values 1658 (4000, 'dddd'), (3000, 'ddd'), (1000, 'd'), (8000, 'dddddddd'), 1659 (4001, 'dddd'), (3001, 'ddd'), (1001, 'd'), (8001, 'dddddddd'), 1660 (4002, 'dddd'), (3002, 'ddd'), (1002, 'd'), (8002, 'dddddddd'); 1661insert into t3 values 1662 (14000, 'dddd'), (13000, 'ddd'), (11000, 'd'), (18000, 'dddddddd'), 1663 (14001, 'dddd'), (13001, 'ddd'), (11001, 'd'), (18001, 'dddddddd'), 1664 (4002, 'dddd'), (3002, 'ddd'), (1002, 'd'), (8002, 'dddddddd'); 1665insert into t4 values 1666 (200, 'cc'), (600, 'cccccc'), (300, 'ccc'), (500, 'ccccc'), 1667 (201, 'cc'), (601, 'cccccc'), (301, 'ccc'), (501, 'ccccc'), 1668 (202, 'cc'), (602, 'cccccc'), (302, 'ccc'), (502, 'ccccc'); 1669insert into t4 values 1670 (1200, 'cc'), (1600, 'cccccc'), (1300, 'ccc'), (1500, 'ccccc'), 1671 (1201, 'cc'), (1601, 'cccccc'), (1301, 'ccc'), (1501, 'ccccc'), 1672 (1202, 'cc'), (1602, 'cccccc'), (1302, 'ccc'), (1502, 'ccccc'); 1673 1674--disable_result_log 1675--disable_warnings 1676analyze table t2,t3,t4; 1677--enable_warnings 1678--enable_result_log 1679 1680set join_cache_level=1; 1681explain 1682select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4 1683 where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c; 1684 1685select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4 1686 where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c; 1687 1688set join_cache_level=6; 1689explain 1690select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4 1691 where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c; 1692 1693select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4 1694 where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c; 1695 1696set join_cache_level=@save_join_cache_level; 1697 1698drop table t1,t2,t3,t4; 1699 1700--echo # 1701--echo # Bug #44250: Corruption of linked join buffers when using BKA 1702--echo # 1703 1704CREATE TABLE t1 ( 1705 id1 bigint(20) DEFAULT NULL, 1706 id2 bigint(20) DEFAULT NULL, 1707 id3 bigint(20) DEFAULT NULL, 1708 num1 bigint(20) DEFAULT NULL, 1709 num2 int(11) DEFAULT NULL, 1710 num3 bigint(20) DEFAULT NULL 1711); 1712 1713CREATE TABLE t2 ( 1714 id3 bigint(20) NOT NULL DEFAULT '0', 1715 id4 bigint(20) DEFAULT NULL, 1716 enum1 enum('Enabled','Disabled','Paused') DEFAULT NULL, 1717 PRIMARY KEY (id3) 1718); 1719 1720CREATE TABLE t3 ( 1721 id4 bigint(20) NOT NULL DEFAULT '0', 1722 text1 text, 1723 PRIMARY KEY (id4) 1724); 1725 1726CREATE TABLE t4 ( 1727 id2 bigint(20) NOT NULL DEFAULT '0', 1728 dummy int(11) DEFAULT '0', 1729 PRIMARY KEY (id2) 1730); 1731 1732CREATE TABLE t5 ( 1733 id1 bigint(20) NOT NULL DEFAULT '0', 1734 id2 bigint(20) NOT NULL DEFAULT '0', 1735 enum2 enum('Active','Deleted','Paused') DEFAULT NULL, 1736 PRIMARY KEY (id1,id2) 1737); 1738 1739--disable_query_log 1740--disable_result_log 1741--disable_warnings 1742 1743INSERT INTO t1 VALUES 1744(228172702,72485641,2667134182,10,1,14),(228172702,94266195,2667134182,134,0,134), 1745(228172702,94266195,2667134182,15,0,15),(228172702,94266195,2667134182,2,0,3), 1746(228172702,818095880,2667134182,1,1,1),(228172702,1004959639,2667134182,3,0,3), 1747(228172702,1297484422,2667134182,1,2,1),(228172702,1730911800,2667134182,11,0,28), 1748(228172702,1730911800,2667134182,4,0,4),(228172702,2182755982,2667134182,5,0,15), 1749(228172702,2182755982,2667134182,1,0,1),(228172702,2968841184,2667134182,1,0,1), 1750(228172702,4765525626,2667134182,2,0,3),(228172702,4765525626,2667134182,29,0,38), 1751(228172702,4765525626,2667134182,7,0,7),(228172702,4765525626,2667134182,7,0,8), 1752(228172702,5330573302,2667134182,1,0,1),(228512602,191149872,935692942,3,0,17), 1753(228512602,259118753,935692942,13,7,13),(228512602,259118753,935692942,83,33,83), 1754(228512602,585705465,935692942,1,0,1),(228512602,585716775,935692942,1,0,1), 1755(228512602,585716775,935692942,6,6,6),(228512602,585716775,935692942,1,1,1), 1756(228512602,1105371172,935692942,2,0,3),(228512602,1105371172,935692942,7,2,7), 1757(228512602,1314223462,935692942,1,0,1),(228512602,1314223642,935692942,1,1,1), 1758(228512602,1411060522,935692942,1,0,1),(228512602,1467398182,935692942,1,0,1), 1759(228512602,1467398182,935692942,3,0,4),(228512602,1467398242,935692942,10,0,41), 1760(228512602,1467398242,935692942,28,0,40),(228512602,1467398242,935692942,0,0,0), 1761(228512602,1467398242,935692942,29,2,33),(228512602,1734178942,935692942,1,0,1), 1762(228512602,1734179122,935692942,1,0,4),(228512602,1734179122,935692942,3,0,6), 1763(228512602,1953612870,935692942,1,0,1),(228512602,2271510562,935692942,1,1,1), 1764(228512602,2271525022,935692942,0,0,0),(228512602,3058831402,935692942,1,1,1), 1765(228512602,3723638842,935692942,1,1,1),(228512602,3723638842,935692942,4,3,4), 1766(228512602,3723836602,935692942,1,1,1),(228512602,3723836842,935692942,1,1,1), 1767(228512602,3723836962,935692942,1,1,1),(228512602,3723988102,935692942,11,4,11), 1768(228512602,3723989182,935692942,8,3,8),(228512602,5920283002,935692942,1,0,1), 1769(228512602,5920314232,935692942,1,0,1),(228512602,191149872,1241589892,0,0,0), 1770(228512602,191149872,1241589892,2,0,4),(228512602,191149872,1241589892,0,0,0), 1771(228512602,259118753,1241589892,8,4,8),(228512602,259118753,1241589892,70,33,70), 1772(228512602,259118753,1241589892,1,1,1),(228512602,585716775,1241589892,8,7,8), 1773(228512602,1105371172,1241589892,1,0,1),(228512602,1105371172,1241589892,9,0,9), 1774(228512602,1314223462,1241589892,1,0,1),(228512602,1411060522,1241589892,1,1,1), 1775(228512602,1467398182,1241589892,1,0,1),(228512602,1467398182,1241589892,4,1,4), 1776(228512602,1467398182,1241589892,1,0,1),(228512602,1467398242,1241589892,10,0,28), 1777(228512602,1467398242,1241589892,37,1,78),(228512602,1467398242,1241589892,28,9,30), 1778(228512602,1467398242,1241589892,5,0,6),(228512602,1734179122,1241589892,3,1,18), 1779(228512602,1734179122,1241589892,1,1,1),(228512602,1734179122,1241589892,2,0,3), 1780(228512602,1953611430,1241589892,1,1,1),(228512602,1953611430,1241589892,1,1,1), 1781(228512602,1953612870,1241589892,1,0,1),(228512602,2026844250,1241589892,1,0,1), 1782(228512602,2271510562,1241589892,1,1,1),(228512602,2271525022,1241589892,1,0,1), 1783(228512602,2941612417,1241589892,1,0,1),(228512602,3723988102,1241589892,1,0,1); 1784INSERT INTO t1 VALUES 1785(228512602,3723988102,1241589892,11,4,11),(228512602,3723989002,1241589892,1,0,1), 1786(228512602,3752960902,1241589892,2,2,4),(228808822,17304242,935693782,6,0,17), 1787(228808822,17304242,935693782,28,1,50),(228808822,17304242,935693782,29,3,61), 1788(228808822,17304242,935693782,6,0,13),(228808822,30931012,935693782,21,0,60), 1789(228808822,30931012,935693782,5,0,13),(228808822,37254452,935693782,3,0,3), 1790(228808822,42726891,935693782,1,0,4),(228808822,42726891,935693782,3,0,6), 1791(228808822,76261151,935693782,8,0,18),(228808822,88240139,935693782,1,0,1), 1792(228808822,88240139,935693782,3,0,3),(228808822,94730895,935693782,2,0,4), 1793(228808822,179737402,935693782,10,0,13),(228808822,179737402,935693782,7,0,8), 1794(228808822,179737402,935693782,3,0,4),(228808822,271288782,935693782,1,0,6), 1795(228808822,304690943,935693782,5,2,10),(228808822,304691183,935693782,4,0,16), 1796(228808822,568994960,935693782,1,0,1),(228808822,631705925,935693782,1,0,1), 1797(228808822,631745165,935693782,1,0,1),(228808822,631749605,935693782,1,0,4), 1798(228808822,1057787002,935693782,1,0,1),(228808822,1057787002,935693782,2,1,4), 1799(228808822,1057787002,935693782,12,1,20),(228808822,1057788022,935693782,2,0,40), 1800(228808822,1057788022,935693782,2,1,3),(228808822,1057788022,935693782,9,2,16), 1801(228808822,1335646822,935693782,3,1,6),(228808822,1335646882,935693782,1,0,3), 1802(228808822,1335646882,935693782,1,0,3),(228808822,1335646942,935693782,7,2,15), 1803(228808822,5510586183,935693782,1,1,1),(228808822,17304242,2482416112,11,0,28), 1804(228808822,17304242,2482416112,34,0,62),(228808822,17304242,2482416112,43,2,89), 1805(228808822,17304242,2482416112,9,0,19),(228808822,30931012,2482416112,32,2,84), 1806(228808822,30931012,2482416112,6,0,14),(228808822,30931012,2482416112,2,0,9), 1807(228808822,37254452,2482416112,1,1,1),(228808822,42726891,2482416112,2,0,10), 1808(228808822,76261151,2482416112,11,0,26),(228808822,88240139,2482416112,3,0,3), 1809(228808822,88240139,2482416112,1,0,1),(228808822,88240139,2482416112,3,0,4), 1810(228808822,94730895,2482416112,1,0,3),(228808822,125469602,2482416112,0,0,0), 1811(228808822,179737402,2482416112,4,0,10),(228808822,179737402,2482416112,8,1,9), 1812(228808822,179737402,2482416112,7,1,9),(228808822,179737402,2482416112,1,0,1), 1813(228808822,271288782,2482416112,2,0,14),(228808822,304690943,2482416112,3,0,6), 1814(228808822,304691183,2482416112,1,0,4),(228808822,555689643,2482416112,2,1,8), 1815(228808822,555689643,2482416112,1,0,4),(228808822,631705925,2482416112,1,0,1), 1816(228808822,631712555,2482416112,1,0,1),(228808822,631745165,2482416112,1,0,1), 1817(228808822,710348755,2482416112,1,0,1),(228808822,753718113,2482416112,1,0,1), 1818(228808822,1057787002,2482416112,1,0,4),(228808822,1057787002,2482416112,1,0,1), 1819(228808822,1057787002,2482416112,4,1,7),(228808822,1057788022,2482416112,7,0,12), 1820(228808822,1057788022,2482416112,3,0,37),(228808822,1057788022,2482416112,0,0,0), 1821(228808822,1057788022,2482416112,12,0,15),(228808822,1335646822,2482416112,14,1,28), 1822(228808822,1335646882,2482416112,1,1,3),(228808822,1335646942,2482416112,5,1,9), 1823(228808822,1335646942,2482416112,1,0,1),(230941762,16069490,2691187582,0,0,0), 1824(230941762,16705991,2691187582,16,0,30),(230941762,16705991,2691187582,12,3,12); 1825INSERT INTO t1 VALUES 1826(230941762,16705991,2691187582,1,0,1),(230941762,27714032,2691187582,6,0,16), 1827(230941762,27714032,2691187582,1,0,1),(230941762,27714032,2691187582,9,0,14), 1828(230941762,28676710,2691187582,3,1,4),(230941762,370319272,2691187582,7,0,7), 1829(230941762,1409814802,2691187582,1,0,3),(230941762,1409814982,2691187582,1,0,1), 1830(230941762,1409814982,2691187582,1,1,1),(230941762,2069703256,2691187582,1,0,3), 1831(230941762,16705991,2691187672,8,1,20),(230941762,16705991,2691187672,11,6,11), 1832(230941762,16705991,2691187672,1,0,1),(230941762,27714032,2691187672,5,0,20), 1833(230941762,27714032,2691187672,1,0,10),(230941762,27714032,2691187672,12,2,17), 1834(230941762,28676710,2691187672,1,0,1),(230941762,142889951,2691187672,2,0,10), 1835(230941762,172526592,2691187672,1,1,1),(230941762,293109282,2691187672,1,0,1), 1836(230941762,370319272,2691187672,10,0,10),(230941762,1409814802,2691187672,1,0,3), 1837(230941762,1409814922,2691187672,1,0,1),(230941762,1409814982,2691187672,1,0,1), 1838(230941762,16069490,2694472582,1,1,1),(230941762,16069490,2694472582,1,1,1), 1839(230941762,16705991,2694472582,15,0,45),(230941762,16705991,2694472582,13,2,15), 1840(230941762,27714032,2694472582,9,0,34),(230941762,27714032,2694472582,2,0,4), 1841(230941762,27714032,2694472582,10,2,14),(230941762,28676710,2694472582,4,0,12), 1842(230941762,28676710,2694472582,1,0,1),(230941762,172526592,2694472582,1,0,4), 1843(230941762,293109282,2694472582,1,0,1),(230941762,370319272,2694472582,6,0,6), 1844(230941762,1409814802,2694472582,1,0,3),(230941762,1409814862,2694472582,1,0,4), 1845(230941762,1409814982,2694472582,1,0,1),(230941762,2680867980,2694472582,1,0,3), 1846(230942122,25451690,935695702,1,0,9),(230942122,31549341,935695702,2,0,18), 1847(230942122,31549341,935695702,2,0,4),(230942122,38900150,935695702,4,0,29), 1848(230942122,38900150,935695702,4,1,13),(230942122,906919252,935695702,39,0,271), 1849(230942122,906919252,935695702,20,0,83),(230942122,906919252,935695702,2,1,9), 1850(230942122,1409816782,935695702,3,0,18),(230942122,1409816842,935695702,1,0,7), 1851(230942122,1409816842,935695702,1,0,3),(230942122,1409816902,935695702,1,0,6), 1852(230942122,2145075862,935695702,4,1,4),(230942122,25451690,935695822,2,0,16), 1853(230942122,38900150,935695822,3,0,26),(230942122,38900150,935695822,1,0,3), 1854(230942122,906919252,935695822,24,0,176),(230942122,906919252,935695822,20,0,74), 1855(230942122,906919252,935695822,1,0,3),(230942122,1409816782,935695822,2,0,21), 1856(230942122,1409816782,935695822,2,0,21),(230942122,1409816842,935695822,1,0,3), 1857(230942122,1409816902,935695822,1,0,7),(231112162,1413675742,935696902,1,0,1), 1858(231112162,1413675742,935696962,0,0,0),(231112162,1413675742,935696962,4,2,4), 1859(231112162,1413675922,935696962,1,0,1),(231112162,1413675922,935696962,1,0,1), 1860(231112162,1413675742,1248588922,1,0,1),(231112162,1413675922,1248588922,3,0,3), 1861(233937022,12641121,935697562,2,0,13),(233937022,12653871,935697562,1,0,1), 1862(233937022,12693551,935697562,1,0,1),(233937022,12910461,935697562,2,0,6), 1863(233937022,12910461,935697562,26,0,65),(233937022,12910461,935697562,44,8,45), 1864(233937022,12910481,935697562,12,0,19),(233937022,12910481,935697562,7,2,9), 1865(233937022,12910481,935697562,1,0,1),(233937022,12910511,935697562,8,0,8); 1866INSERT INTO t1 VALUES 1867(233937022,12910511,935697562,20,6,22),(233937022,30879781,935697562,34,0,34), 1868(233937022,30879781,935697562,3,0,4),(233937022,30879781,935697562,1,0,1), 1869(233937022,45631730,935697562,8,0,39),(233937022,54079090,935697562,12,0,12), 1870(233937022,54079090,935697562,7,0,11),(233937022,54079090,935697562,14,0,16), 1871(233937022,94431735,935697562,6,0,31),(233937022,96876131,935697562,3,0,4), 1872(233937022,105436492,935697562,4,0,4),(233937022,128981555,935697562,3,0,3), 1873(233937022,145211004,935697562,1,0,1),(233937022,146382622,935697562,1,0,1), 1874(233937022,175678702,935697562,1,0,4),(233937022,298998998,935697562,1,0,1), 1875(233937022,335995773,935697562,3,0,3),(233937022,335995773,935697562,2,0,3), 1876(233937022,347447636,935697562,0,0,0),(233937022,459295955,935697562,3,0,3), 1877(233937022,459376625,935697562,1,0,1),(233937022,495877773,935697562,1,0,1), 1878(233937022,497008702,935697562,1,0,3),(233937022,561944105,935697562,1,0,1), 1879(233937022,561944105,935697562,1,0,1),(233937022,586535965,935697562,3,0,3), 1880(233937022,631549775,935697562,1,0,7),(233937022,647138479,935697562,1,0,1), 1881(233937022,655870453,935697562,4,0,7),(233937022,694832725,935697562,1,0,1), 1882(233937022,864475057,935697562,1,0,1),(233937022,1010757503,935697562,1,0,4), 1883(233937022,1010847736,935697562,2,0,9),(233937022,1287437116,935697562,2,0,4), 1884(233937022,1337693056,935697562,1,0,1),(233937022,1569279742,935697562,1,1,1), 1885(233937022,1569280102,935697562,2,0,7),(233937022,1569280882,935697562,2,1,3), 1886(233937022,1569281062,935697562,1,0,1),(233937022,1569281962,935697562,1,0,3), 1887(233937022,2823580588,935697562,2,0,8),(233937022,2823580588,935697562,3,1,10), 1888(233937022,2842066134,935697562,1,0,1),(233937022,2904542181,935697562,1,0,1), 1889(233937022,3058483627,935697562,1,0,1),(233937022,4507287318,935697562,1,0,1), 1890(233937022,5283489892,935697562,1,0,1),(233937022,11890554322,935697562,16,0,16), 1891(233937022,11890756102,935697562,3,1,3),(233937022,12641121,953996482,1,0,7), 1892(233937022,12641851,953996482,1,0,1),(233937022,12641851,953996482,1,0,1), 1893(233937022,12910461,953996482,4,0,14),(233937022,12910461,953996482,20,2,23), 1894(233937022,12910461,953996482,43,5,43),(233937022,12910461,953996482,1,0,1), 1895(233937022,12910481,953996482,17,2,30),(233937022,12910511,953996482,7,1,8), 1896(233937022,12910511,953996482,23,5,23),(233937022,14913951,953996482,2,0,3), 1897(233937022,21835210,953996482,1,1,1),(233937022,26481052,953996482,1,1,1), 1898(233937022,26481052,953996482,1,0,1),(233937022,30879781,953996482,2,0,3), 1899(233937022,30879781,953996482,22,0,22),(233937022,35617681,953996482,1,0,1), 1900(233937022,45631730,953996482,3,0,11),(233937022,54079090,953996482,13,0,13), 1901(233937022,54079090,953996482,11,0,16),(233937022,54079090,953996482,29,0,34), 1902(233937022,94431735,953996482,3,0,9),(233937022,96876131,953996482,3,0,4), 1903(233937022,105436492,953996482,1,0,1),(233937022,105437952,953996482,3,1,3), 1904(233937022,123639716,953996482,1,0,6),(233937022,145211004,953996482,2,0,3), 1905(233937022,145211004,953996482,2,1,3),(233937022,146382622,953996482,1,0,1), 1906(233937022,146382622,953996482,1,0,1),(233937022,155454324,953996482,1,0,1); 1907INSERT INTO t1 VALUES 1908(233937022,298998998,953996482,1,1,1),(233937022,335995773,953996482,1,0,1), 1909(233937022,335995773,953996482,7,2,9),(233937022,459295955,953996482,2,0,4), 1910(233937022,561944105,953996482,1,0,1),(233937022,655870453,953996482,5,0,9), 1911(233937022,694832725,953996482,1,0,1),(233937022,694832725,953996482,1,0,1), 1912(233937022,864475057,953996482,4,1,4),(233937022,897886118,953996482,1,0,1), 1913(233937022,897886118,953996482,1,0,3),(233937022,1005147016,953996482,1,0,1), 1914(233937022,1010757503,953996482,1,0,1),(233937022,1082217873,953996482,1,0,1), 1915(233937022,1286925326,953996482,1,0,1),(233937022,1337693056,953996482,4,0,4), 1916(233937022,1407236408,953996482,2,0,3),(233937022,1569280102,953996482,1,0,6), 1917(233937022,1569280222,953996482,1,0,1),(233937022,1569281062,953996482,1,0,1), 1918(233937022,1569284362,953996482,1,0,3),(233937022,2823580588,953996482,1,0,3), 1919(233937022,2904542181,953996482,3,0,7),(233937022,4371581485,953996482,1,0,1), 1920(233937022,5283491332,953996482,1,0,1),(233937022,7300486013,953996482,1,1,1), 1921(233937022,11890554322,953996482,16,0,16),(233937022,11890754392,953996482,1,0,1), 1922(233937022,11890754392,953996482,0,0,0); 1923 1924INSERT INTO t2 VALUES 1925(2667134182,2567095402,'Enabled'),(935692942,826927822,'Enabled'), 1926(1241589892,1130891152,'Enabled'),(935693782,826928662,'Enabled'), 1927(2482416112,2381969632,'Enabled'),(2691187582,2591198842,'Enabled'), 1928(2691187672,2591198932,'Enabled'),(2694472582,2594492212,'Paused'), 1929(935695702,826930582,'Enabled'),(935695822,826930702,'Enabled'), 1930(935696902,826931782,'Enabled'),(935696962,826931842,'Enabled'), 1931(1248588922,1137805582,'Enabled'),(935697562,826932442,'Paused'), 1932(953996482,845181202,'Enabled'),(2702549092,2602579882,'Enabled'), 1933(2702549182,2602579972,'Enabled'),(2702550712,2602581502,'Enabled'), 1934(1125312412,1015179502,'Enabled'),(2708245462,2608290202,'Enabled'), 1935(2708247262,2608292002,'Enabled'),(935699242,826934122,'Enabled'), 1936(1125312502,1015179592,'Enabled'),(1125312592,1015179682,'Enabled'), 1937(2711450452,2611502302,'Enabled'),(2711452252,2611504102,'Enabled'), 1938(935699902,826934782,'Enabled'),(935700262,826935142,'Enabled'), 1939(1215381442,1104677032,'Enabled'),(2503848082,2403457762,'Enabled'), 1940(935701762,826936642,'Enabled'),(935701822,826936702,'Enabled'), 1941(1468810282,1355227402,'Enabled'),(935702842,826937722,'Enabled'), 1942(1125312682,1015179772,'Enabled'),(2713816102,2613869392,'Enabled'), 1943(2688452032,2588455012,'Enabled'),(2688452212,2588455192,'Enabled'), 1944(2701527412,2601556942,'Enabled'),(1623918712,1510242412,'Enabled'), 1945(2701521922,2601551452,'Enabled'),(2701527772,2601557302,'Enabled'); 1946 1947INSERT INTO `t3` VALUES 1948(2567095402,'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'), 1949(826927822,'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB'),(1130891152,'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB'), 1950(826928662,'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC'), 1951(2381969632,'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC'), 1952(2591198842,'DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD'), 1953(2591198932,'EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE'), 1954(2594492212,'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF'), 1955(826930582,'GGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG'), 1956(826930702,'GGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG'), 1957(826931782,'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB'), 1958(826931842,'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB'), 1959(1137805582,'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB'); 1960 1961INSERT INTO t4 VALUES 1962(12618121,0),(12641121,0),(12641851,0),(12653871,0),(12665801,0),(12666811,0), 1963(12693551,0),(12910461,0),(12910481,0),(12910511,0),(14787251,0),(14913941,0), 1964(14913951,0),(16069490,0),(16705901,0),(16705991,0),(17291062,0),(17304242,0), 1965(20737411,0),(21524370,0),(21835210,0),(25300361,0),(25451690,0),(25728842,0), 1966(26481052,0),(27714032,0),(28676710,0),(30879781,0),(30931012,0),(31549341,0), 1967(35617681,0),(37254452,0),(38619430,0),(38895490,0),(38900150,0),(39798990,0), 1968(42726891,0),(42867050,0),(43439030,0),(45631730,0),(47171711,0),(49539832,0), 1969(54079090,0),(60442241,0),(65320501,0),(72485641,0),(76261151,0),(87949714,0), 1970(88240139,0),(94266195,0),(94431735,0),(94730895,0),(96876131,0); 1971 1972INSERT INTO t5 VALUES 1973(228172702,72485641,'Active'),(228172702,94266195,'Active'), 1974(228172702,818095880,'Active'),(228172702,1004959639,'Active'), 1975(228172702,1297484242,'Active'),(228172702,1297484422,'Active'), 1976(228172702,1730911800,'Active'),(228172702,1808277389,'Active'), 1977(228172702,2182755982,'Active'),(228172702,2968841184,'Active'), 1978(228172702,3015116542,'Active'),(228172702,3752383170,'Active'), 1979(228172702,4765525626,'Active'),(228172702,5330573302,'Active'), 1980(228512602,191149872,'Active'),(228512602,259118753,'Active'), 1981(228512602,585705465,'Active'),(228512602,585716775,'Active'), 1982(228512602,1105371172,'Active'),(228512602,1314223462,'Active'), 1983(228512602,1314223642,'Active'),(228512602,1411060522,'Active'), 1984(228512602,1467398182,'Active'),(228512602,1467398242,'Active'), 1985(228512602,1734178942,'Active'),(228512602,1734179122,'Active'), 1986(228512602,1953612870,'Active'),(228512602,2271510562,'Active'), 1987(228512602,2271525022,'Active'),(228512602,2941612417,'Active'), 1988(228512602,3058831402,'Active'),(228512602,3723638842,'Active'), 1989(228512602,3723836602,'Active'),(228512602,3723836842,'Active'), 1990(228512602,3723836962,'Active'),(228512602,3723988102,'Active'), 1991(228512602,3723989182,'Active'),(228512602,5920283002,'Active'), 1992(228512602,5920314232,'Active'),(228512602,585717615,'Active'), 1993(228512602,1953611430,'Active'),(228512602,2026844250,'Active'), 1994(228512602,3058831462,'Active'),(228512602,3723836902,'Active'), 1995(228512602,3723989002,'Active'),(228512602,3752960902,'Active'), 1996(228808822,17304242,'Active'),(228808822,30931012,'Active'), 1997(228808822,37254452,'Active'),(228808822,42726891,'Active'), 1998(228808822,76261151,'Active'),(228808822,88240139,'Active'), 1999(228808822,94730895,'Active'),(228808822,125469622,'Active'), 2000(228808822,179737402,'Active'),(228808822,271288782,'Active'), 2001(228808822,304690943,'Active'),(228808822,304691183,'Active'), 2002(228808822,496123368,'Active'),(228808822,555689643,'Active'), 2003(228808822,568994960,'Active'),(228808822,631705925,'Active'), 2004(228808822,631745165,'Active'),(228808822,631749605,'Active'), 2005(228808822,1057787002,'Active'),(228808822,1057788022,'Active'), 2006(228808822,1335646822,'Active'),(228808822,1335646882,'Active'), 2007(228808822,1335646942,'Active'),(228808822,1612792238,'Active'), 2008(228808822,5510586183,'Active'),(228808822,47171711,'Active'), 2009(228808822,125469602,'Active'),(228808822,631712555,'Active'), 2010(228808822,710348755,'Active'),(228808822,753718113,'Active'), 2011(230941762,16069490,'Active'),(230941762,16705991,'Active'), 2012(230941762,27714032,'Active'),(230941762,28676710,'Active'); 2013INSERT INTO t5 VALUES 2014(230941762,370319272,'Active'),(230941762,1409814802,'Active'), 2015(230941762,1409814982,'Active'),(230941762,2069703256,'Active'), 2016(230941762,142889951,'Active'),(230941762,172526592,'Active'), 2017(230941762,293109282,'Active'),(230941762,1409814922,'Active'), 2018(230941762,1409814862,'Active'),(230941762,2680867980,'Active'), 2019(230942122,25451690,'Active'),(230942122,31549341,'Active'), 2020(230942122,38900150,'Active'),(230942122,464554745,'Active'), 2021(230942122,906919252,'Active'),(230942122,1409816782,'Active'), 2022(230942122,1409816842,'Active'),(230942122,1409816902,'Active'), 2023(230942122,2145075862,'Active'),(231112162,1413675742,'Active'), 2024(231112162,1413675922,'Active'),(231112162,1413675562,'Active'), 2025(231112162,1413675802,'Active'),(233937022,12641121,'Active'), 2026(233937022,12653871,'Active'),(233937022,12693551,'Active'), 2027(233937022,12910461,'Active'),(233937022,12910481,'Active'), 2028(233937022,12910511,'Active'),(233937022,14913941,'Active'), 2029(233937022,30879781,'Active'),(233937022,45631730,'Active'), 2030(233937022,54079090,'Active'),(233937022,65320501,'Active'), 2031(233937022,94431735,'Active'),(233937022,96876131,'Active'), 2032(233937022,105436492,'Active'),(233937022,105437952,'Active'), 2033(233937022,128981555,'Active'),(233937022,145211004,'Active'), 2034(233937022,146382622,'Active'),(233937022,148832422,'Active'), 2035(233937022,175678702,'Active'),(233937022,260507673,'Active'), 2036(233937022,298998998,'Active'),(233937022,335995773,'Active'), 2037(233937022,347447636,'Active'),(233937022,459295955,'Active'), 2038(233937022,459376625,'Active'),(233937022,495877773,'Active'), 2039(233937022,497008702,'Active'),(233937022,561944105,'Active'), 2040(233937022,586535965,'Active'),(233937022,631549775,'Active'), 2041(233937022,647138479,'Active'),(233937022,655870453,'Active'), 2042(233937022,694832725,'Active'),(233937022,835712045,'Active'), 2043(233937022,864475057,'Active'),(233937022,864484777,'Active'), 2044(233937022,1010757503,'Active'),(233937022,1010847736,'Active'), 2045(233937022,1091554836,'Active'),(233937022,1287437116,'Active'), 2046(233937022,1337693056,'Active'),(233937022,1569279742,'Active'), 2047(233937022,1569280102,'Active'),(233937022,1569280222,'Active'), 2048(233937022,1569280582,'Active'),(233937022,1569280882,'Active'), 2049(233937022,1569281062,'Active'),(233937022,1569281962,'Active'), 2050(233937022,1569284362,'Active'),(233937022,1743317015,'Active'), 2051(233937022,2698799002,'Active'),(233937022,2698800742,'Active'), 2052(233937022,2823580588,'Active'),(233937022,2842066134,'Active'), 2053(233937022,2904542181,'Active'),(233937022,3058483627,'Active'); 2054INSERT INTO t5 VALUES 2055(233937022,4507287318,'Active'),(233937022,5283489892,'Active'), 2056(233937022,11890554322,'Active'),(233937022,11890756102,'Active'), 2057(233937022,12641851,'Active'),(233937022,14913951,'Active'), 2058(233937022,21835210,'Active'),(233937022,26481052,'Active'), 2059(233937022,35617681,'Active'),(233937022,123639716,'Active'), 2060(233937022,155454324,'Active'),(233937022,299001668,'Active'), 2061(233937022,897886118,'Active'),(233937022,1005147016,'Active'), 2062(233937022,1082217873,'Active'),(233937022,1286925326,'Active'), 2063(233937022,1407236408,'Active'),(233937022,4371581485,'Active'), 2064(233937022,5283491332,'Active'),(233937022,7300486013,'Active'), 2065(233937022,11890754392,'Active'); 2066 2067--enable_warnings 2068--enable_result_log 2069--enable_query_log 2070 2071set join_cache_level=8; 2072set join_buffer_size=2048; 2073 2074EXPLAIN 2075SELECT STRAIGHT_JOIN t1.id1, t1.num3, t3.text1, t3.id4, t2.id3, t4.dummy 2076 FROM t1 JOIN t2 JOIN t3 JOIN t4 JOIN t5 2077 WHERE t1.id1=t5.id1 AND t1.id2=t5.id2 and t4.id2=t1.id2 AND 2078 t5.enum2='Active' AND t3.id4=t2.id4 AND t2.id3=t1.id3 AND t3.text1<'D'; 2079 2080SELECT STRAIGHT_JOIN t1.id1, t1.num3, t3.text1, t3.id4, t2.id3, t4.dummy 2081 FROM t1 JOIN t2 JOIN t3 JOIN t4 JOIN t5 2082 WHERE t1.id1=t5.id1 AND t1.id2=t5.id2 and t4.id2=t1.id2 AND 2083 t5.enum2='Active' AND t3.id4=t2.id4 AND t2.id3=t1.id3 AND t3.text1<'D'; 2084 2085set join_buffer_size=@save_join_buffer_size; 2086set join_cache_level=@save_join_cache_level; 2087 2088DROP TABLE t1,t2,t3,t4,t5; 2089 2090--echo # 2091--echo # Bug#45267: Incomplete check caused wrong result. 2092--echo # 2093CREATE TABLE t1 ( 2094 `pk` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY 2095); 2096CREATE TABLE t3 ( 2097 `pk` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY 2098); 2099INSERT INTO t3 VALUES 2100(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15), 2101(16),(17),(18),(19),(20); 2102CREATE TABLE t2 ( 2103 `pk` int(11) NOT NULL AUTO_INCREMENT, 2104 `int_nokey` int(11) NOT NULL, 2105 `time_key` time NOT NULL, 2106 PRIMARY KEY (`pk`), 2107 KEY `time_key` (`time_key`) 2108); 2109INSERT INTO t2 VALUES (10,9,'22:36:46'),(11,0,'08:46:46'); 2110 2111SELECT DISTINCT t1.`pk` 2112FROM t1 RIGHT JOIN t2 STRAIGHT_JOIN t3 ON t2.`int_nokey` ON t2.`time_key` 2113GROUP BY 1; 2114 2115DROP TABLE IF EXISTS t1, t2, t3; 2116 2117--echo # 2118--echo # Bug #46328: Use of aggregate function without GROUP BY clause 2119--echo # returns many rows (vs. one ) 2120--echo # 2121 2122CREATE TABLE t1 ( 2123 int_key int(11) NOT NULL, 2124 KEY int_key (int_key) 2125); 2126 2127INSERT INTO t1 VALUES 2128(0),(2),(2),(2),(3),(4),(5),(5),(6),(6),(8),(8),(9),(9); 2129 2130CREATE TABLE t2 ( 2131 int_key int(11) NOT NULL, 2132 KEY int_key (int_key) 2133); 2134 2135INSERT INTO t2 VALUES (2),(3); 2136 2137--echo 2138 2139--echo # The query shall return 1 record with a max value 9 and one of the 2140--echo # int_key values inserted above (undefined which one). A changed 2141--echo # execution plan may change the value in the second column 2142SELECT MAX(t1.int_key), t1.int_key 2143FROM t1 STRAIGHT_JOIN t2 2144ORDER BY t1.int_key; 2145 2146--echo 2147 2148explain 2149SELECT MAX(t1.int_key), t1.int_key 2150FROM t1 STRAIGHT_JOIN t2 2151ORDER BY t1.int_key; 2152 2153--echo 2154 2155DROP TABLE t1,t2; 2156 2157SET join_cache_level=@save_join_cache_level; 2158 2159--echo # 2160--echo # Regression test for 2161--echo # Bug#46733 - NULL value not returned for aggregate on empty result 2162--echo # set w/ semijoin on 2163--echo # 2164 2165CREATE TABLE t1 ( 2166 i int(11) NOT NULL, 2167 v varchar(1) DEFAULT NULL, 2168 PRIMARY KEY (i) 2169); 2170 2171INSERT INTO t1 VALUES (10,'a'),(11,'b'),(12,'c'),(13,'d'); 2172 2173CREATE TABLE t2 ( 2174 i int(11) NOT NULL, 2175 v varchar(1) DEFAULT NULL, 2176 PRIMARY KEY (i) 2177); 2178 2179INSERT INTO t2 VALUES (1,'x'),(2,'y'); 2180 2181--echo 2182 2183SELECT MAX(t1.i) 2184FROM t1 JOIN t2 ON t2.v 2185ORDER BY t2.v; 2186 2187--echo 2188 2189EXPLAIN 2190SELECT MAX(t1.i) 2191FROM t1 JOIN t2 ON t2.v 2192ORDER BY t2.v; 2193 2194--echo 2195 2196DROP TABLE t1,t2; 2197 2198--echo # 2199--echo # Bug #45092: join buffer contains two blob columns one of which is 2200--echo # used in the key employed to access the joined table 2201--echo # 2202 2203CREATE TABLE t1 (c1 int, c2 int, key (c2)); 2204INSERT INTO t1 VALUES (1,1); 2205INSERT INTO t1 VALUES (2,2); 2206 2207CREATE TABLE t2 (c1 text, c2 text); 2208INSERT INTO t2 VALUES('tt', 'uu'); 2209INSERT INTO t2 VALUES('zzzz', 'xxxxxxxxx'); 2210 2211--disable_result_log 2212ANALYZE TABLE t1,t2; 2213--enable_result_log 2214 2215set join_cache_level=6; 2216 2217SELECT t1.*, t2.*, LENGTH(t2.c1), LENGTH(t2.c2) FROM t1,t2 2218 WHERE t1.c2=LENGTH(t2.c2) and t1.c1=LENGTH(t2.c1); 2219 2220set join_cache_level=@save_join_cache_level; 2221 2222DROP TABLE t1,t2; 2223 2224--echo # 2225--echo # Bug #51092: linked join buffer is used for a 3-way cross join query 2226--echo # that selects only records of the first table 2227--echo # 2228 2229create table t1 (a int, b int); 2230insert into t1 values (1,1),(2,2); 2231create table t2 (a int, b int); 2232insert into t2 values (1,1),(2,2); 2233create table t3 (a int, b int); 2234insert into t3 values (1,1),(2,2); 2235 2236set join_cache_level=1; 2237 2238explain select t1.* from t1,t2,t3; 2239select t1.* from t1,t2,t3; 2240 2241set join_cache_level=2; 2242 2243explain select t1.* from t1,t2,t3; 2244select t1.* from t1,t2,t3; 2245 2246set join_cache_level=@save_join_cache_level; 2247 2248drop table t1,t2,t3; 2249 2250--echo # 2251--echo # Bug #52394: using join buffer for 3 table join with ref access 2252--echo # LP #623209: and no references to the columns of the middle table 2253--echo # 2254 2255 2256set join_cache_level=6; 2257 2258CREATE TABLE t1 (a int(11), b varchar(1)); 2259INSERT INTO t1 VALUES (6,'r'),(27,'o'); 2260 2261CREATE TABLE t2(a int); 2262INSERT INTO t2 VALUES(1),(2),(3),(4),(5); 2263 2264CREATE TABLE t3 (a int(11) primary key, b varchar(1)); 2265INSERT INTO t3 VALUES 2266(14,'d'),(15,'z'),(16,'e'),(17,'h'),(18,'b'),(19,'s'),(20,'e'), 2267(21,'j'),(22,'e'),(23,'f'),(24,'v'),(25,'x'),(26,'m'),(27,'o'); 2268 2269EXPLAIN 2270SELECT t3.a FROM t1,t2,t3 WHERE t1.a = t3.a AND t1.b = t3.b; 2271SELECT t3.a FROM t1,t2,t3 WHERE t1.a = t3.a AND t1.b = t3.b; 2272 2273DROP TABLE t1,t2,t3; 2274 2275set join_cache_level=@save_join_cache_level; 2276 2277--echo # 2278--echo # Bug #51084: Batched key access crashes for SELECT with 2279--echo # derived table and LEFT JOIN 2280--echo # 2281 2282CREATE TABLE t1 ( 2283 carrier int, 2284 id int PRIMARY KEY 2285); 2286INSERT INTO t1 VALUES (1,11),(1,12),(2,13); 2287 2288CREATE TABLE t2 ( 2289 scan_date int, 2290 package_id int 2291); 2292INSERT INTO t2 VALUES (2008,21),(2008,22); 2293 2294CREATE TABLE t3 ( 2295 carrier int PRIMARY KEY, 2296 id int 2297); 2298INSERT INTO t3 VALUES (1,31); 2299 2300CREATE TABLE t4 ( 2301 carrier_id int, 2302 INDEX carrier_id(carrier_id) 2303); 2304INSERT INTO t4 VALUES (31),(32); 2305 2306SET join_cache_level=8; 2307 2308SELECT COUNT(*) 2309 FROM (t2 JOIN t1) LEFT JOIN (t3 JOIN t4 ON t3.id = t4.carrier_id) 2310 ON t3.carrier = t1.carrier; 2311 2312EXPLAIN 2313SELECT COUNT(*) 2314 FROM (t2 JOIN t1) LEFT JOIN (t3 JOIN t4 ON t3.id = t4.carrier_id) 2315 ON t3.carrier = t1.carrier; 2316 2317SET join_cache_level=@save_join_cache_level; 2318 2319DROP TABLE t1,t2,t3,t4; 2320 2321--echo # 2322--echo # Bug #52636: allowing JOINs on NULL values w/ join_cache_level = 5-8 2323--echo # 2324 2325CREATE TABLE t1 (b int); 2326INSERT INTO t1 VALUES (NULL),(3); 2327 2328CREATE TABLE t2 (a int, b int, KEY (b)); 2329INSERT INTO t2 VALUES 2330 (100,NULL),(150,200),(50,150),(250,350),(180,210),(100,150), 2331 (101,NULL),(151,200),(51,150),(251,350),(181,210),(101,150); 2332 2333set join_cache_level = 5; 2334explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; 2335--sorted_result 2336SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; 2337 2338set join_cache_level = 8; 2339explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; 2340--sorted_result 2341SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; 2342 2343# test crash when no key is worth collecting by BKA for t2's ref 2344delete from t1; 2345INSERT INTO t1 VALUES (NULL),(NULL); 2346set join_cache_level = 5; 2347explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; 2348--sorted_result 2349SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; 2350 2351DROP TABLE t1,t2; 2352 2353# test varchar keys 2354CREATE TABLE t1 (b varchar(100)); 2355INSERT INTO t1 VALUES (NULL),("some varchar"); 2356 2357CREATE TABLE t2 (a int, b varchar(100), KEY (b)); 2358INSERT INTO t2 VALUES (100,NULL),(150,"varchar"),(200,NULL),(250,"long long varchar"); 2359INSERT INTO t2 VALUES (100,NULL),(150,"long varchar"),(200,"varchar"),(250,"long long long varchar"); 2360 2361set join_cache_level = 5; 2362explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; 2363--sorted_result 2364SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; 2365 2366set join_cache_level = 8; 2367explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; 2368--sorted_result 2369SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; 2370 2371set join_cache_level=@save_join_cache_level; 2372DROP TABLE t1,t2; 2373 2374--echo # 2375--echo # Bug #54359: Extra rows with join_cache_level=7,8 and two joins 2376--echo # and multi-column index" 2377--echo # 2378 2379CREATE TABLE t1 ( 2380 pk int NOT NULL, 2381 a int DEFAULT NULL, 2382 b varchar(16) DEFAULT NULL, 2383 c varchar(16) DEFAULT NULL, 2384 INDEX idx (b,a)) 2385; 2386 2387INSERT INTO t1 VALUES (4,9,'k','k'); 2388INSERT INTO t1 VALUES (12,5,'k','k'); 2389 2390set join_cache_level = 8; 2391 2392EXPLAIN 2393SELECT t.a FROM t1 t, t1 s FORCE INDEX(idx) 2394 WHERE s.pk AND s.a >= t.pk AND s.b = t.c; 2395 2396SELECT t.a FROM t1 t, t1 s FORCE INDEX(idx) 2397 WHERE s.pk AND s.a >= t.pk AND s.b = t.c; 2398 2399set join_cache_level=@save_join_cache_level; 2400DROP TABLE t1; 2401 2402--echo # 2403--echo # Bug #54235: Extra rows with join_cache_level=6,8 and two LEFT JOINs 2404--echo # 2405 2406CREATE TABLE t1 (a int); 2407CREATE TABLE t2 (a int); 2408CREATE TABLE t3 (a int); 2409CREATE TABLE t4 (a int); 2410 2411INSERT INTO t1 VALUES (null), (2), (null), (1); 2412 2413set join_cache_level = 6; 2414EXPLAIN 2415SELECT t1.a 2416 FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.a) ON 0 2417 WHERE t1.a OR t3.a; 2418SELECT t1.a 2419 FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.a) ON 0 2420 WHERE t1.a OR t3.a; 2421 2422EXPLAIN 2423SELECT t1.a 2424 FROM t1 LEFT JOIN (t2 LEFT JOIN (t3 LEFT JOIN t4 ON 1) ON t2.a) ON 0 2425 WHERE t1.a OR t4.a; 2426SELECT t1.a 2427 FROM t1 LEFT JOIN (t2 LEFT JOIN (t3 LEFT JOIN t4 ON 1) ON t2.a) ON 0 2428 WHERE t1.a OR t4.a; 2429 2430set join_cache_level=@save_join_cache_level; 2431DROP TABLE t1,t2,t3,t4; 2432 2433--echo # 2434--echo # Bug #663840: Memory overwrite causing crash with hash join 2435--echo # 2436 2437SET SESSION join_cache_level=3; 2438SET SESSION join_buffer_size=100; 2439 2440CREATE TABLE t3 ( 2441 i int NOT NULL, 2442 j int NOT NULL, 2443 d date NOT NULL, 2444 t time NOT NULL, 2445 v varchar(1) NOT NULL, 2446 u varchar(1) NOT NULL, 2447 INDEX idx (v) 2448) COLLATE=latin1_bin; 2449 2450INSERT INTO t3 VALUES 2451 (3,8,'2008-12-04','00:00:00','v','v'), (3,8,'2009-03-28','00:00:00','f','f'), 2452 (3,5,'1900-01-01','00:55:47','v','v'), (2,8,'2009-10-02','00:00:00','s','s'), 2453 (1,8,'1900-01-01','20:51:59','a','a'), (0,6,'2008-06-04','09:47:27','p','p'), 2454 (8,7,'2009-01-13','21:58:29','z','z'), (5,2,'1900-01-01','22:45:53','a','a'), 2455 (9,5,'2008-01-28','14:06:48','h','h'), (5,7,'2004-09-18','22:17:16','h','h'), 2456 (4,2,'2006-10-14','14:59:37','v','v'), (2,9,'1900-01-01','23:37:40','v','v'), 2457 (33,142,'2000-11-28','14:14:01','b','b'), (5,3,'2008-04-04','02:54:19','y','y'), 2458 (1,0,'2002-07-13','06:34:26','v','v'), (9,3,'2003-01-03','18:07:38','m','m'), 2459 (1,5,'2006-04-02','13:55:23','z','z'), (3,9,'2006-10-19','20:32:28','n','n'), 2460 (8,1,'2005-06-08','11:57:44','d','d'), (231,107,'2006-12-26','03:10:35','a','a'); 2461INSERT INTO t3 VALUES 2462 (103,108,'2008-12-04','00:00:00','a','v'), (103,108,'2009-03-28','00:00:00','b','f'), 2463 (103,105,'1900-01-01','00:55:47','c','v'), (102,108,'2009-10-02','00:00:00','d','s'), 2464 (100,108,'1900-01-01','20:51:59','e','a'), (100,106,'2008-06-04','09:47:27','f','p'), 2465 (108,107,'2009-01-13','21:58:29','g','z'), (105,102,'1900-01-01','22:45:53','h','a'), 2466 (109,105,'2008-01-28','14:06:48','i','h'), (105,107,'2004-09-18','22:17:16','j','h'), 2467 (104,102,'2006-10-14','14:59:37','k','v'), (102,109,'1900-01-01','23:37:40','l','v'), 2468 (1033,1142,'2000-11-28','14:14:01','m','b'), (105,103,'2008-04-04','02:54:19','n','y'), 2469 (100,100,'2002-07-13','06:34:26','o','v'), (109,103,'2003-01-03','18:07:38','p','m'), 2470 (100,105,'2006-04-02','13:55:23','q','z'), (103,109,'2006-10-19','20:32:28','s','n'), 2471 (108,100,'2005-06-08','11:57:44','t','d'), (1231,1107,'2006-12-26','03:10:35','v','a'); 2472 2473CREATE TABLE t1 SELECT * FROM t3; 2474DELETE FROM t1 WHERE i > 8; 2475CREATE TABLE t2 SELECT * FROM t3; 2476DELETE FROM t2 WHERE j > 10; 2477 2478EXPLAIN 2479SELECT t1.i, t1.d, t1.v, t2.i, t2.d, t2.t, t2.v FROM t1,t2,t3 2480 WHERE t3.u <='a' AND t2.j < 5 AND t3.v = t2.u; 2481 2482--sorted_result 2483SELECT t1.i, t1.d, t1.v, t2.i, t2.d, t2.t, t2.v FROM t1,t2,t3 2484 WHERE t3.u <='a' AND t2.j < 5 AND t3.v = t2.u; 2485 2486DROP TABLE t1,t2,t3; 2487 2488SET SESSION join_cache_level=@save_join_cache_level; 2489SET SESSION join_buffer_size=@save_join_buffer_size; 2490 2491 2492--echo # 2493--echo # Bug #664508: 'Simple' GROUP BY + ORDER BY 2494--echo # when join buffers are used 2495--echo # 2496 2497CREATE TABLE t1 ( 2498 pk int NOT NULL, i int NOT NULL, v varchar(1) NOT NULL, 2499 PRIMARY KEY (pk), INDEX idx1(i), INDEX idx2 (v,i) 2500) COLLATE latin1_bin; 2501INSERT INTO t1 VALUES 2502 (10,8,'v'), (11,8,'f'), (13,8,'s'), (14,8,'a'), 2503 (15,6,'p'), (16,7,'z'), (17,2,'a'), (18,5,'h'), (19,7,'h'), 2504 (25,3,'m'), (26,5,'a'), (27,9,'n'), (28,1,'d'), (29,107,'a'); 2505INSERT INTO t1 VALUES 2506 (110,8,'x'), (111,8,'y'), (112,5,'v'), (113,8,'z'), (114,8,'i'), 2507 (115,6,'j'), (116,7,'t'), (117,2,'b'), (118,5,'j'), (119,7,'w'), 2508 (125,3,'q'), (126,5,'o'), (127,9,'n'), (128,1,'e'), (129,107,'c'); 2509INSERT INTO t1 VALUES 2510 (210,8,'b'), (211,8,'c'), (212,5,'d'), (213,8,'e'), (214,8,'g'), 2511 (215,6,'f'), (216,7,'h'), (217,2,'i'), (218,5,'j'), (219,7,'k'), 2512 (225,3,'l'), (226,5,'m'), (227,9,'n'), (228,1,'o'), (229,107,'p'); 2513 2514CREATE TABLE t2 ( 2515 pk int NOT NULL, i int NOT NULL, v varchar(1) NOT NULL, 2516 PRIMARY KEY (pk), INDEX idx1(i), INDEX idx2(v,i) 2517) COLLATE latin1_bin; 2518INSERT INTO t2 VALUES 2519 (10,8,'v'), (11,8,'f'), (12,5,'v'), (13,8,'s'), (14,8,'a'), 2520 (15,6,'p'), (16,7,'z'), (17,2,'a'), (18,5,'h'), (19,7,'h'), 2521 (20,2,'v'), (21,9,'v'), (22,142,'b'), (23,3,'y'), (24,0,'v'), 2522 (25,3,'m'), (26,5,'b'), (27,9,'n'), (28,1,'d'), (29,107,'a'); 2523 2524CREATE TABLE t3 ( 2525 pk int NOT NULL, i int NOT NULL, v varchar(1) NOT NULL, 2526 PRIMARY KEY (pk), INDEX idx1(i), INDEX idx2(v,i) 2527) COLLATE latin1_bin; 2528INSERT INTO t3 VALUES 2529 (1,9,'x'), (2,5,'g'), (3,1,'o'), (4,0,'g'), (5,1,'v'), 2530 (6,190,'m'), (7,6,'x'), (8,3,'c'), (9,4,'z'), (10,3,'i'), 2531 (11,186,'x'), (12,1,'g'), (13,8,'q'), (14,226,'m'), (15,133,'p'), 2532 (16,6,'e'), (17,3,'t'), (18,8,'j'), (19,5,'h'), (20,7,'w'); 2533 2534SET SESSION join_cache_level=1; 2535EXPLAIN 2536SELECT t2.v FROM t1, t2, t3 2537WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 2538 GROUP BY t2.v ORDER BY t1.pk,t2.v; 2539SELECT t2.v FROM t1, t2, t3 2540WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 2541 GROUP BY t2.v ORDER BY t1.pk,t2.v; 2542 2543# MDEV-8189 field<>const and const<>field are not symmetric 2544# Do the same EXPLAIN and SELECT 2545# for "t2.v <> t3.v" instead of "t3.v <> t2.v" 2546 2547EXPLAIN 2548SELECT t2.v FROM t1, t2, t3 2549WHERE t2.v <> t3.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 2550 GROUP BY t2.v ORDER BY t1.pk,t2.v; 2551SELECT t2.v FROM t1, t2, t3 2552WHERE t2.v <> t3.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 2553 GROUP BY t2.v ORDER BY t1.pk,t2.v; 2554 2555SET SESSION join_cache_level=6; 2556EXPLAIN 2557SELECT t2.v FROM t1, t2, t3 2558WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 2559 GROUP BY t2.v ORDER BY t1.pk,t2.v; 2560SELECT t2.v FROM t1, t2, t3 2561WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 2562 GROUP BY t2.v ORDER BY t1.pk,t2.v; 2563 2564# MDEV-8189 field<>const and const<>field are not symmetric 2565# Do the same EXPLAIN and SELECT 2566# for "t2.v <> t3.v" instead of "t3.v <> t2.v" 2567EXPLAIN 2568SELECT t2.v FROM t1, t2, t3 2569WHERE t2.v <> t3.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 2570 GROUP BY t2.v ORDER BY t1.pk,t2.v; 2571SELECT t2.v FROM t1, t2, t3 2572WHERE t2.v <> t3.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 2573 GROUP BY t2.v ORDER BY t1.pk,t2.v; 2574 2575SET SESSION join_cache_level=4; 2576EXPLAIN 2577SELECT t2.v FROM t1, t2, t3 2578WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 2579 GROUP BY t2.v ORDER BY t1.pk,t2.v; 2580SELECT t2.v FROM t1, t2, t3 2581WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 2582 GROUP BY t2.v ORDER BY t1.pk,t2.v; 2583 2584# MDEV-8189 field<>const and const<>field are not symmetric 2585# Do the same EXPLAIN and SELECT 2586# for "t2.v <> t3.v" instead of "t3.v <> t2.v" 2587EXPLAIN 2588SELECT t2.v FROM t1, t2, t3 2589WHERE t2.v <> t3.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 2590 GROUP BY t2.v ORDER BY t1.pk,t2.v; 2591SELECT t2.v FROM t1, t2, t3 2592WHERE t2.v <> t3.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 2593 GROUP BY t2.v ORDER BY t1.pk,t2.v; 2594 2595DROP TABLE t1,t2,t3; 2596 2597SET SESSION join_cache_level=@save_join_cache_level; 2598 2599--echo # 2600--echo # Bug #668290: hash join with non-binary collations 2601--echo # 2602 2603CREATE TABLE t1 ( 2604 i int DEFAULT NULL, 2605 cl varchar(10) CHARACTER SET latin1 DEFAULT NULL, 2606 cu varchar(10) CHARACTER SET utf8 DEFAULT NULL, 2607 INDEX cl (cl), 2608 INDEX cu (cu) 2609); 2610INSERT INTO t1 VALUES 2611 (650903552,'cmxffkpsel','z'), (535298048,'tvtjrcmxff','y'), 2612 (1626865664,'when','for'), (39649280,'rcvljitvtj','ercvljitvt'), 2613 (792068096,'ttercvljit','jttercvlji'); 2614INSERT INTO t1 SELECT * FROM t1; 2615 2616CREATE TABLE t2 ( 2617 cu varchar(10) CHARACTER SET utf8 DEFAULT NULL, 2618 i int DEFAULT NULL, 2619 cl varchar(10) CHARACTER SET latin1 DEFAULT NULL, 2620 INDEX cu (cu), 2621 INDEX cl (cl) 2622); 2623INSERT INTO t2 VALUES 2624 ('g',7,'like'), ('fujttercvl',6,'y'), 2625 ('s',2,'e'), ('didn\'t',0,'v'), 2626 ('gvdrodpedk',8,'chogvdrodp'), ('jichogvdro',7,'will'); 2627 2628EXPLAIN 2629SELECT t2.i FROM t1,t2 WHERE t1.cu = t2.cl ; 2630SELECT t2.i FROM t1,t2 WHERE t1.cu = t2.cl ; 2631 2632SET SESSION join_cache_level = 4; 2633 2634EXPLAIN 2635SELECT t2.i FROM t1,t2 WHERE t1.cu = t2.cl ; 2636SELECT t2.i FROM t1,t2 WHERE t1.cu = t2.cl ; 2637 2638SET SESSION join_cache_level=@save_join_cache_level; 2639 2640DROP TABLE t1,t2; 2641 2642--echo # 2643--echo # Bug #669382: hash join using a ref with constant key parts 2644--echo # 2645 2646CREATE TABLE t1 (a int); 2647INSERT INTO t1 VALUES 2648 (9), (11), (7), (8), (4), (1), (12), (3), (5); 2649INSERT INTO t1 SELECT * FROM t1; 2650INSERT INTO t1 SELECT * FROM t1; 2651 2652CREATE TABLE t2 (a int, b int, c int, INDEX idx (a,b)); 2653INSERT INTO t2 VALUES 2654 (8, 80, 800), (1, 10, 100), (1, 11, 101), (3, 30, 300), 2655 (1, 12, 102), (8, 81, 801), (7, 70, 700), (12, 120, 1200), 2656 (8, 82, 802), (1, 13, 103), (1, 14, 104), (3, 31, 301), 2657 (1, 15, 105), (8, 83, 803), (7, 71, 701); 2658 2659SET SESSION join_cache_level = 4; 2660SET SESSION join_buffer_size = 256; 2661 2662EXPLAIN 2663SELECT t1.a, t2.c FROM t1,t2 WHERE t1.a=t2.a AND t2.b=99; 2664SELECT t1.a, t2.c FROM t1,t2 WHERE t1.a=t2.a AND t2.b=99; 2665 2666SET SESSION join_cache_level=@save_join_cache_level; 2667SET SESSION join_buffer_size=@save_join_buffer_size; 2668 2669DROP TABLE t1,t2; 2670 2671--echo # 2672--echo # Bug #671901: hash join using a ref to a varchar field 2673--echo # 2674 2675CREATE TABLE t1 ( 2676 v varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, 2677 i int DEFAULT NULL 2678); 2679INSERT INTO t1 VALUES 2680 ('k',8), ('abcdefjh',-575340544), ('f',77), ('because', 2), ('f',-517472256), 2681 ('abcdefjhj',5), ('z',7); 2682 2683CREATE TABLE t2 ( 2684 v varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, 2685 i int DEFAULT NULL, 2686 INDEX idx (v) 2687); 2688INSERT INTO t2 VALUES 2689 ('did',5), ('was',-1631322112), ('are',3), ('abcdefjhjk',3), 2690 ('abcdefjhjk',4), ('tell',-824573952), ('t',0),('v',-1711013888), 2691 ('abcdefjhjk',1015414784), ('or',4), ('now',0), ('abcdefjhjk',-32702464), 2692 ('abcdefjhjk',4), ('time',1078394880), ('f',4), ('m',-1845559296), 2693 ('ff', 5), ('abcdefjhjk',-1074397184); 2694INSERT INTO t2 VALUES 2695 ('dig',5), ('were',-1631322112), ('is',3), ('abcdefjhjl',3), 2696 ('abcdefjh',4), ('told',-824573952), ('tt',0),('vv',-1711013888), 2697 ('abcdefjhjj',1015414784), ('and',4), ('here',0), ('abcdefjhjm',-32702464), 2698 ('abcdefjhji',4), ('space',1078394880), ('fs',4), ('mn',-1845559296), 2699 ('fq', 5), ('abcdefjhjp',-1074397184); 2700 2701EXPLAIN 2702SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = t1.v; 2703SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = t1.v; 2704EXPLAIN 2705SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = concat(t1.v, t1.v); 2706SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = concat(t1.v, t1.v); 2707 2708SET SESSION join_cache_level = 4; 2709EXPLAIN 2710SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = t1.v; 2711SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = t1.v; 2712EXPLAIN 2713SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = concat(t1.v, t1.v); 2714SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = concat(t1.v, t1.v); 2715 2716SET SESSION join_cache_level=@save_join_cache_level; 2717 2718DROP TABLE t1,t2; 2719 2720#--echo # 2721--echo # Bug #672497: 3 way join with tiny incremental join buffer with 2722--echo # and a ref access from the first table 2723--echo # 2724 2725CREATE TABLE t1 ( 2726 pk int PRIMARY KEY, 2727 v varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, 2728 INDEX idx (v) 2729); 2730INSERT INTO t1 VALUES 2731 (1,'abcdefjhjk'), (2,'i'),(3,'abcdefjhjk'), (4,'well'), (5,'abcdefjhjk'), 2732 (6,'abcdefjhjk'), (7,'that'); 2733 2734CREATE TABLE t2 ( 2735 pk int PRIMARY KEY, 2736 i int DEFAULT NULL, 2737 v varchar(1000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, 2738 INDEX idx (v) 2739); 2740INSERT INTO t2 VALUES 2741 (1,6,'yes'), (2,NULL,'will'), (3,NULL,'o'), (4,NULL,'k'), (5,NULL,'she'), 2742 (6,-1450835968,'abcdefjhjkl'), (7,-975831040,'abcdefjhjkl'), (8,NULL,'z'), 2743 (10,-343932928,'t'), 2744 (11,6,'yes'), (12,NULL,'will'), (13,NULL,'o'), (14,NULL,'k'), (15,NULL,'she'), 2745 (16,-1450835968,'abcdefjhjkl'), (17,-975831040,'abcdefjhjkl'), (18,NULL,'z'), 2746 (19,-343932928,'t'); 2747INSERT INTO t2 VALUES 2748 (101,6,'yes'), (102,NULL,'will'), (103,NULL,'o'), (104,NULL,'k'), (105,NULL,'she'), 2749 (106,-1450835968,'abcdefjhjkl'), (107,-975831040,'abcdefjhjkl'), (108,NULL,'z'), 2750 (100,-343932928,'t'), 2751 (111,6,'yes'), (112,NULL,'will'), (113,NULL,'o'), (114,NULL,'k'), (115,NULL,'she'), 2752 (116,-1450835968,'abcdefjhjkl'), (117,-975831040,'abcdefjhjkl'), (118,NULL,'z'), 2753 (119,-343932928,'t'); 2754 2755CREATE TABLE t3 ( 2756 pk int NOT NULL PRIMARY KEY, 2757 i int, 2758 v varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, 2759 INDEX idx (v(333)) 2760); 2761INSERT INTO t3 VALUES 2762(1,7,'abcdefjhjkl'),(2,6,'y'), (3,NULL,'to'),(4,7,'n'),(5,7,'look'), (6,NULL,'all'), 2763(7,1443168256,'c'), (8,1427046400,'right'), 2764(11,7,'abcdefjhjkl'), (12,6,'y'), (13,NULL,'to'), (14,7,'n'), (15,7,'look'), 2765(16,NULL,'all'), (17,1443168256,'c'), (18,1427046400,'right'), 2766(21,7,'abcdefjhjkl'), (22,6,'y'), (23,NULL,'to'), (24,7,'n'), (25,7,'look'), 2767(26,NULL,'all'), (27,1443168256,'c'), (28,1427046400,'right'), 2768(31,7,'abcdefjhjkl'), (32,6,'y'), (33,NULL,'to'), (34,7,'n'), (35,7,'look'), 2769(36,NULL,'all'), (37,1443168256,'c'), (38,1427046400,'right'); 2770INSERT INTO t3 VALUES 2771(101,7,'abcdefjhjkl'),(102,6,'y'), (103,NULL,'to'),(104,7,'n'),(105,7,'look'), 2772(106,NULL,'all'), (107,1443168256,'c'), (108,1427046400,'right'), 2773(111,7,'abcdefjhjkl'), (112,6,'y'), (113,NULL,'to'), (114,7,'n'), (115,7,'look'), 2774(116,NULL,'all'), (117,1443168256,'c'), (118,1427046400,'right'), 2775(121,7,'abcdefjhjkl'), (122,6,'y'), (123,NULL,'to'), (124,7,'n'), (125,7,'look'), 2776(126,NULL,'all'), (127,1443168256,'c'), (128,1427046400,'right'), 2777(131,7,'abcdefjhjkl'), (132,6,'y'), (133,NULL,'to'), (134,7,'n'), (135,7,'look'), 2778(136,NULL,'all'), (137,1443168256,'c'), (138,1427046400,'right'); 2779 2780SET SESSION join_buffer_size = 256; 2781 2782SET SESSION join_cache_level = 4; 2783EXPLAIN 2784SELECT t3.i FROM t1,t2,t3 2785 WHERE t1.v = t2.v AND t3.v = t1.v AND t2.i <> 0; 2786SELECT t3.i FROM t1,t2,t3 2787 WHERE t1.v = t2.v AND t3.v = t1.v AND t2.i <> 0; 2788 2789SET SESSION join_cache_level=@save_join_cache_level; 2790SET SESSION join_buffer_size=@save_join_buffer_size; 2791 2792DROP TABLE t1,t2,t3; 2793 2794--echo # 2795--echo # Bug #672551: hash join over a long varchar field 2796--echo # 2797 2798CREATE TABLE t1 ( 2799 pk int PRIMARY KEY, 2800 a varchar(512) CHARSET latin1 COLLATE latin1_bin DEFAULT NULL, 2801 INDEX idx (a) 2802); 2803INSERT INTO t1 VALUES (2, 'aa'), (5, 'ccccccc'), (3, 'bb'); 2804 2805CREATE TABLE t2( 2806 pk int PRIMARY KEY, 2807 a varchar(512) CHARSET latin1 COLLATE latin1_bin DEFAULT NULL, 2808 INDEX idx (a) 2809); 2810INSERT INTO t2 VALUES 2811 (10, 'a'), (20, 'c'), (30, 'aa'), (4, 'bb'), 2812 (11, 'a'), (21, 'c'), (31, 'aa'), (41, 'cc'), 2813 (12, 'a'), (22, 'c'), (32, 'bb'), (42, 'aa'); 2814INSERT INTO t2 VALUES 2815 (110, 'a'), (120, 'c'), (130, 'aa'), (14, 'bb'), 2816 (111, 'a'), (121, 'c'), (131, 'aa'), (141, 'cc'), 2817 (112, 'a'), (122, 'c'), (132, 'bb'), (142, 'aa'); 2818 2819SELECT * FROM t1,t2 WHERE t2.a=t1.a; 2820 2821SET SESSION join_cache_level = 4; 2822EXPLAIN 2823SELECT * FROM t1,t2 WHERE t2.a=t1.a; 2824SELECT * FROM t1,t2 WHERE t2.a=t1.a; 2825 2826SET SESSION join_cache_level=@save_join_cache_level; 2827 2828DROP TABLE t1,t2; 2829 2830--echo # 2831--echo # Bug #674431: nested outer join when join_cache_level is set to 7 2832--echo # 2833 2834CREATE TABLE t1 (a int, b varchar(32)) ; 2835INSERT INTO t1 VALUES (5,'h'), (NULL,'j'); 2836CREATE TABLE t2 (a int, b varchar(32), c int) ; 2837INSERT INTO t2 VALUES (5,'h',100), (NULL,'j',200); 2838CREATE TABLE t3 (a int, b varchar(32), INDEX idx(b)); 2839INSERT INTO t3 VALUES (77,'h'), (88,'g'); 2840 2841SET SESSION optimizer_switch = 'outer_join_with_cache=on'; 2842SET SESSION join_cache_level = 7; 2843SELECT t3.a 2844 FROM t1 LEFT JOIN 2845 (t2 LEFT OUTER JOIN t3 ON t2.b = t3.b) ON t2.a = t1.b 2846 WHERE t3.a BETWEEN 3 AND 11 OR t1.a <= t2.c; 2847 2848SET SESSION optimizer_switch=@local_optimizer_switch; 2849SET SESSION join_cache_level=@save_join_cache_level; 2850 2851DROP TABLE t1,t2,t3; 2852 2853--echo # 2854--echo # Bug #52540: nested outer join when join_cache_level is set to 3 2855--echo # 2856 2857CREATE TABLE t1 (a int); 2858INSERT INTO t1 VALUES (2); 2859CREATE TABLE t2 (a varchar(10)); 2860INSERT INTO t2 VALUES ('f'),('x'); 2861CREATE TABLE t3 (pk int(11) PRIMARY KEY); 2862INSERT INTO t3 VALUES (2); 2863CREATE TABLE t4 (a varchar(10)); 2864 2865SET SESSION optimizer_switch = 'outer_join_with_cache=on'; 2866SET SESSION join_cache_level = 3; 2867 2868SELECT * 2869 FROM t2 LEFT JOIN 2870 ((t1 JOIN t3 ON t1.a = t3.pk) LEFT JOIN t4 ON 1) ON 1; 2871 2872SET SESSION optimizer_switch=@local_optimizer_switch; 2873SET SESSION join_cache_level=@save_join_cache_level; 2874 2875DROP TABLE t1,t2,t3,t4; 2876 2877--echo # 2878--echo # Bug #674423: outer join with ON expression over only outer tables 2879--echo # 2880 2881CREATE TABLE t1 (a int) ; 2882INSERT INTO t1 VALUES ('9'); 2883 2884CREATE TABLE t2 (pk int, a int) ; 2885INSERT INTO t2 VALUES ('9',NULL), ('1',NULL); 2886 2887SET SESSION optimizer_switch = 'outer_join_with_cache=on'; 2888 2889SET SESSION join_cache_level = 0; 2890EXPLAIN 2891SELECT * FROM t2 LEFT JOIN t1 ON t2.a <> 0 WHERE t1.a <> 0 OR t2.pk < 9; 2892SELECT * FROM t2 LEFT JOIN t1 ON t2.a <> 0 WHERE t1.a <>0 OR t2.pk < 9; 2893 2894SET SESSION join_cache_level = 1; 2895EXPLAIN 2896SELECT * FROM t2 LEFT JOIN t1 ON t2.a <> 0 WHERE t1.a <> 0 OR t2.pk < 9; 2897SELECT * FROM t2 LEFT JOIN t1 ON t2.a <> 0 WHERE t1.a <> 0 OR t2.pk < 9; 2898 2899SET SESSION optimizer_switch=@local_optimizer_switch; 2900SET SESSION join_cache_level=@save_join_cache_level; 2901 2902DROP TABLE t1,t2; 2903 2904--echo # 2905--echo # Bug #675095: nested outer join using join buffer 2906--echo # 2907 2908CREATE TABLE t1 (pk int, a1 int) ; 2909INSERT IGNORE INTO t1 VALUES (2,NULL), (8,0); 2910 2911CREATE TABLE t2 (pk int, a2 int, c2 int, d2 int) ; 2912INSERT IGNORE INTO t2 VALUES (9,0,0,2), (1,0,0,7); 2913 2914CREATE TABLE t3 (pk int, a3 int, c3 int, d3 int) ; 2915INSERT IGNORE INTO t3 VALUES (9,0,0,2), (1,0,0,7); 2916 2917CREATE TABLE t4 (pk int, a4 int, INDEX idx(a4)) ; 2918INSERT IGNORE INTO t4 VALUES (2,NULL), (8,0); 2919INSERT IGNORE INTO t4 VALUES (12,10), (18,20); 2920INSERT IGNORE INTO t4 VALUES (22,11), (28,21); 2921INSERT IGNORE INTO t4 VALUES (32,12), (38,22); 2922 2923CREATE TABLE t5 (pk int, a5 int) ; 2924INSERT IGNORE INTO t5 VALUES (2,0), (8,0); 2925 2926 2927SET SESSION optimizer_switch = 'outer_join_with_cache=on'; 2928 2929SET SESSION join_cache_level = 0; 2930 2931EXPLAIN EXTENDED 2932SELECT * 2933 FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.c2 = t3.a3) ON t1.pk = t2.d2) 2934 LEFT JOIN t4 ON t1.a1 = t4.a4) LEFT JOIN t5 ON t3.a3 = t5.a5; 2935SELECT * 2936 FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.c2 = t3.a3) ON t1.pk = t2.d2) 2937 LEFT JOIN t4 ON t1.a1 = t4.a4) LEFT JOIN t5 ON t3.a3 = t5.a5; 2938 2939SET SESSION join_cache_level = 2; 2940 2941EXPLAIN EXTENDED 2942SELECT * 2943 FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.c2 = t3.a3) ON t1.pk = t2.d2) 2944 LEFT JOIN t4 ON t1.a1 = t4.a4) LEFT JOIN t5 ON t3.a3 = t5.a5; 2945SELECT * 2946 FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.c2 = t3.a3) ON t1.pk = t2.d2) 2947 LEFT JOIN t4 ON t1.a1 = t4.a4) LEFT JOIN t5 ON t3.a3 = t5.a5; 2948 2949SET SESSION join_cache_level = 1; 2950 2951EXPLAIN EXTENDED 2952SELECT * 2953 FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.c2 = t3.a3) ON t1.pk = t2.d2) 2954 LEFT JOIN t4 ON t1.a1 = t4.a4) LEFT JOIN t5 ON t3.a3 = t5.a5; 2955SELECT * 2956 FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.c2 = t3.a3) ON t1.pk = t2.d2) 2957 LEFT JOIN t4 ON t1.a1 = t4.a4) LEFT JOIN t5 ON t3.a3 = t5.a5; 2958 2959SET SESSION optimizer_switch=@local_optimizer_switch; 2960SET SESSION join_cache_level=@save_join_cache_level; 2961 2962DROP TABLE t1,t2,t3,t4,t5; 2963 2964--echo # 2965--echo # Bug #675516: nested outer join with 3 tables in the nest 2966--echo # using BNL + BNLH 2967--echo # 2968 2969CREATE TABLE t1 (a1 int, b1 int, c1 int) ; 2970INSERT INTO t1 VALUES (7,8,0), (6,4,0); 2971 2972CREATE TABLE t2 (a2 int) ; 2973INSERT INTO t2 VALUES (5); 2974 2975CREATE TABLE t3 (a3 int, b3 int, c3 int, PRIMARY KEY (b3)) ; 2976INSERT INTO t3 VALUES (2,5,0); 2977 2978CREATE TABLE t4 (a4 int, b4 int, c4 int) ; 2979INSERT INTO t4 VALUES (7,8,0); 2980 2981SET SESSION optimizer_switch = 'outer_join_with_cache=on'; 2982 2983SET SESSION join_cache_level = 4; 2984EXPLAIN 2985SELECT * FROM 2986 t1 LEFT JOIN 2987 ((t2 JOIN t3 ON t2.a2 = t3.b3) JOIN t4 ON t4.b4 <> 0) ON t1.c1 = t3.c3 2988 WHERE t3.a3 IS NULL; 2989SELECT * FROM 2990 t1 LEFT JOIN 2991 ((t2 JOIN t3 ON t2.a2 = t3.b3) JOIN t4 ON t4.b4 <> 0) ON t1.c1 = t3.c3 2992 WHERE t3.a3 IS NULL; 2993 2994SET SESSION join_cache_level = 0; 2995EXPLAIN 2996SELECT * FROM 2997 t1 LEFT JOIN 2998 ((t2 JOIN t3 ON t2.a2 = t3.b3) JOIN t4 ON t4.b4 <> 0) ON t1.c1 = t3.c3 2999 WHERE t3.a3 IS NULL; 3000SELECT * FROM 3001 t1 LEFT JOIN 3002 ((t2 JOIN t3 ON t2.a2 = t3.b3) JOIN t4 ON t4.b4 <> 0) ON t1.c1 = t3.c3 3003 WHERE t3.a3 IS NULL; 3004 3005SET SESSION optimizer_switch=@local_optimizer_switch; 3006SET SESSION join_cache_level=@save_join_cache_level; 3007 3008DROP TABLE t1,t2,t3,t4; 3009 3010--echo # 3011--echo # Bug #660963: nested outer join with join_cache_level set to 5 3012--echo # 3013 3014CREATE TABLE t1 (a1 int) ; 3015INSERT INTO t1 VALUES (0),(0); 3016 3017CREATE TABLE t2 (a2 int, b2 int, PRIMARY KEY (a2)) ; 3018INSERT INTO t2 VALUES (2,1); 3019 3020CREATE TABLE t3 (a3 int, b3 int, PRIMARY KEY (a3)) ; 3021INSERT INTO t3 VALUES (2,1); 3022 3023SET SESSION optimizer_switch = 'outer_join_with_cache=on'; 3024 3025SET SESSION join_cache_level = 6; 3026EXPLAIN 3027SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0; 3028SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0; 3029 3030SET SESSION join_cache_level = 5; 3031EXPLAIN 3032SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0; 3033SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0; 3034 3035SET SESSION optimizer_switch=@local_optimizer_switch; 3036SET SESSION join_cache_level=@save_join_cache_level; 3037 3038DROP TABLE t1,t2,t3; 3039 3040--echo # 3041--echo # Bug #675922: incremental buffer for BKA with access from previous 3042--echo # buffers from non-nullable columns whose values may be null 3043--echo # 3044 3045CREATE TABLE t1 (a1 varchar(32)) ; 3046INSERT INTO t1 VALUES ('s'),('k'); 3047 3048CREATE TABLE t2 (a2 int PRIMARY KEY, b2 varchar(32)) ; 3049INSERT INTO t2 VALUES (7,'s'); 3050 3051CREATE TABLE t3 (a3 int PRIMARY KEY, b3 varchar(32)) ; 3052INSERT INTO t3 VALUES (7,'s'); 3053 3054CREATE TABLE t4 (a4 int) ; 3055INSERT INTO t4 VALUES (9); 3056 3057CREATE TABLE t5(a5 int PRIMARY KEY, b5 int) ; 3058INSERT INTO t5 VALUES (7,0); 3059 3060SET SESSION optimizer_switch = 'outer_join_with_cache=on'; 3061 3062SET SESSION join_cache_level = 0; 3063EXPLAIN 3064SELECT t4.a4, t5.b5 3065 FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1) 3066 LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2; 3067SELECT t4.a4, t5.b5 3068 FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1) 3069 LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2; 3070 3071SET SESSION join_cache_level = 6; 3072EXPLAIN 3073SELECT t4.a4, t5.b5 3074 FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1) 3075 LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2; 3076SELECT t4.a4, t5.b5 3077 FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1) 3078 LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2; 3079 3080SET SESSION optimizer_switch=@local_optimizer_switch; 3081SET SESSION join_cache_level=@save_join_cache_level; 3082 3083DROP TABLE t1,t2,t3,t4,t5; 3084 3085--echo # 3086--echo # Bug #670380: hash join for non-binary collation 3087--echo # 3088 3089 3090CREATE TABLE t1 (pk int PRIMARY KEY, a varchar(32)); 3091CREATE TABLE t2 (pk int PRIMARY KEY, a varchar(32), INDEX idx(a)); 3092INSERT INTO t1 VALUES 3093 (10,'AAA'), (20,'BBBB'), (30,'Cc'), (40,'DD'), (50,'ee'); 3094INSERT INTO t2 VALUES 3095 (1,'Bbbb'), (2,'BBB'), (3,'bbbb'), (4,'AaA'), (5,'CC'), 3096 (6,'cC'), (7,'CCC'), (8,'AAA'), (9,'bBbB'), (10,'aaaa'), 3097 (11,'a'), (12,'dd'), (13,'EE'), (14,'ee'), (15,'D'), 3098 (101,'Bbbb'), (102,'BBB'), (103,'bbbb'), (104,'AaA'), (105,'CC'), 3099 (106,'cC'), (107,'CCC'), (108,'AAA'), (109,'bBbB'), (110,'aaaa'), 3100 (111,'a'), (112,'dd'), (113,'EE'), (114,'ee'), (115,'D'); 3101 3102SET SESSION join_cache_level = 4; 3103 3104EXPLAIN 3105SELECT * FROM t1,t2 WHERE t1.a=t2.a; 3106SELECT * FROM t1,t2 WHERE t1.a=t2.a; 3107 3108SET SESSION join_cache_level=@save_join_cache_level; 3109 3110DROP TABLE t1,t2; 3111 3112--echo # 3113--echo # Bug #694092: incorrect detection of index only pushdown conditions 3114--echo # 3115 3116CREATE TABLE t1 ( 3117 f1 varchar(10), f3 int(11), PRIMARY KEY (f3) 3118); 3119INSERT INTO t1 VALUES ('y',1),('or',5); 3120 3121CREATE TABLE t2 ( 3122 f3 int(11), f2 varchar(1024), f4 varchar(10), PRIMARY KEY (f3) 3123); 3124INSERT INTO t2 VALUES (6,'RPOYT','y'),(10,'JINQE','m'); 3125 3126SET SESSION join_cache_level = 1; 3127 3128SET SESSION optimizer_switch = 'index_condition_pushdown=off'; 3129EXPLAIN 3130SELECT * FROM t1,t2 3131 WHERE t1.f1 = t2.f4 AND (t1.f3 = 1 AND t2.f3 = 4 OR t1.f3 = 2 AND t2.f3 = 6); 3132SELECT * FROM t1,t2 3133 WHERE t1.f1 = t2.f4 AND (t1.f3 = 1 AND t2.f3 = 4 OR t1.f3 = 2 AND t2.f3 = 6); 3134 3135SET SESSION optimizer_switch = 'index_condition_pushdown=on'; 3136EXPLAIN 3137SELECT * FROM t1,t2 3138 WHERE t1.f1 = t2.f4 AND (t1.f3 = 1 AND t2.f3 = 4 OR t1.f3 = 2 AND t2.f3 = 6); 3139SELECT * FROM t1,t2 3140 WHERE t1.f1 = t2.f4 AND (t1.f3 = 1 AND t2.f3 = 4 OR t1.f3 = 2 AND t2.f3 = 6); 3141 3142SET SESSION join_cache_level=@save_join_cache_level; 3143SET SESSION optimizer_switch=@local_optimizer_switch; 3144 3145DROP TABLE t1,t2; 3146 3147# The same cause of the problem but no join buffer is used (see bug #695442) 3148 3149CREATE TABLE t1 (f1 int, f2 varchar(10), KEY (f1), KEY (f2)) ; 3150INSERT INTO t1 VALUES 3151 (4,'e'), (891879424,'l'), (-243400704,'ectlyqupbk'), (1851981824,'of'), 3152 (-1495203840,'you'), (4,'no'), (-1436942336,'c'), (891420672,'DQQYO'), 3153 (608698368,'qergldqmec'), (1,'x'); 3154 3155CREATE TABLE t2 (f3 varchar(64), KEY (f3)); 3156INSERT INTO t2 VALUES 3157 ('d'), ('UALLN'), ('d'), ('z'), ('r'), ('YVAKV'), ('d'), ('TNGZK'), ('e'), 3158 ('xucupaxdyythsgiw'), ('why'), ('ttugkxucupaxdyyt'), ('l'), ('LHTKN'), 3159 ('d'), ('o'), ('v'), ('KGLCJ'), ('your'); 3160 3161 3162SET SESSION optimizer_switch='index_merge_sort_intersection=off'; 3163 3164SET SESSION optimizer_switch = 'index_condition_pushdown=off'; 3165EXPLAIN SELECT * FROM t1,t2 3166 WHERE t2.f3 = t1.f2 AND t1.f1 IN (9, 0, 100) ORDER BY t1.f2 LIMIT 1; 3167SELECT * FROM t1,t2 3168 WHERE t2.f3 = t1.f2 AND t1.f1 IN (9, 0 ,100) ORDER BY t1.f2 LIMIT 1; 3169SET SESSION optimizer_switch=@local_optimizer_switch; 3170 3171SET SESSION optimizer_switch = 'index_condition_pushdown=on'; 3172EXPLAIN SELECT * FROM t1,t2 3173 WHERE t2.f3 = t1.f2 AND t1.f1 IN (9, 0 ,100) ORDER BY t1.f2 LIMIT 1; 3174SELECT * FROM t1,t2 3175 WHERE t2.f3 = t1.f2 AND t1.f1 IN (9, 0 ,100) ORDER BY t1.f2 LIMIT 1; 3176 3177SET SESSION optimizer_switch=@local_optimizer_switch; 3178 3179DROP TABLE t1,t2; 3180 3181--echo # 3182--echo # Bug #694443: hash join using IS NULL the an equi-join condition 3183--echo # 3184 3185CREATE TABLE t1 (a int PRIMARY KEY); 3186INSERT INTO t1 VALUES 3187 (7), (4), (9), (1), (3), (8), (2); 3188 3189CREATE TABLE t2 (a int, b int, INDEX idx (a)); 3190INSERT INTO t2 VALUES 3191 (NULL,10), (4,80), (7,70), (6,11), (7,90), (NULL,40), 3192 (4,77), (4,50), (NULL,41), (7,99), (7,88), (8,12), 3193 (1,21), (4,90), (7,91), (8,22), (6,92), (NULL,42), 3194 (2,78), (2,51), (1,43), (5,97), (5,89); 3195 3196SET SESSION join_cache_level = 1; 3197EXPLAIN 3198SELECT * FROM t1,t2 WHERE t1.a < 3 and t2.a IS NULL; 3199SELECT * FROM t1,t2 WHERE t1.a < 3 and t2.a IS NULL; 3200 3201SET SESSION join_cache_level = 4; 3202EXPLAIN 3203SELECT * FROM t1,t2 WHERE t1.a < 3 and t2.a IS NULL; 3204SELECT * FROM t1,t2 WHERE t1.a < 3 and t2.a IS NULL; 3205 3206SET SESSION join_cache_level=@save_join_cache_level; 3207 3208DROP TABLE t1,t2; 3209 3210--echo # 3211--echo # Bug #697557: hash join on a varchar field 3212--echo # 3213 3214CREATE TABLE t1 ( f1 varchar(10) , f2 int(11) , KEY (f1)); 3215INSERT INTO t1 VALUES ('r',1), ('m',2); 3216 3217CREATE TABLE t2 ( f1 varchar(10) , f2 int(11) , KEY (f1)); 3218INSERT INTO t2 VALUES 3219 ('hgtofubn',1), ('GDOXZ',91), ('n',2), ('fggxgalh',88), 3220 ('hgtofu',1), ('GDO',101), ('n',3), ('fggxga',55), 3221 ('hgtofu',3), ('GDO',33), ('nn',3), ('fggxgarrr',77), 3222 ('jgtofu',3), ('JDO',33), ('mn',3), ('jggxgarrr',77), 3223 ('igtofu',3), ('IDO',33), ('ln',3), ('iggxgarrr',77); 3224 3225 3226SET SESSION join_cache_level=3; 3227 3228EXPLAIN 3229SELECT * FROM t1,t2 WHERE t2.f1 = t1.f1; 3230SELECT * FROM t1,t2 WHERE t2.f1 = t1.f1; 3231 3232SET SESSION join_cache_level=@save_join_cache_level; 3233 3234DROP TABLE t1,t2; 3235 3236--echo # 3237--echo # Bug #707827: hash join on varchar column with NULLs 3238--echo # 3239 3240CREATE TABLE t1 (v varchar(1)); 3241INSERT INTO t1 VALUES ('o'), ('u'); 3242 3243CREATE TABLE t2 (a int, v varchar(1), INDEX idx (v)) ; 3244INSERT INTO t2 VALUES 3245 (8,NULL), (10,'b'), (5,'k'), (4,NULL), 3246 (1,NULL), (11,'u'), (7,NULL), (2,'d'), 3247 (18,'u'), (11,'b'), (15,'k'), (12,'d'), 3248 (18,'x'), (11,'y'), (15,'l'), (12,'e'); 3249 3250SET SESSION join_buffer_size = 256; 3251 3252SET SESSION join_cache_level = 4; 3253EXPLAIN 3254SELECT a FROM t1,t2 WHERE t2.v = t1.v ; 3255SELECT a FROM t1,t2 WHERE t2.v = t1.v ; 3256 3257SET SESSION join_cache_level = 1; 3258EXPLAIN 3259SELECT a FROM t1,t2 WHERE t2.v = t1.v ; 3260SELECT a FROM t1,t2 WHERE t2.v = t1.v ; 3261 3262SET SESSION join_cache_level=@save_join_cache_level; 3263SET SESSION join_buffer_size=@save_join_buffer_size; 3264 3265DROP TABLE t1,t2; 3266 3267--echo # 3268--echo # Bug #802860: crash on join cache + derived + duplicate_weedout 3269--echo # 3270 3271SET SESSION optimizer_switch= 3272 'semijoin=on,materialization=off,firstmatch=off,loosescan=off,derived_with_keys=on'; 3273 3274CREATE TABLE t1 (a int) ; 3275INSERT IGNORE INTO t1 VALUES (0), (1), (0); 3276 3277CREATE TABLE t2 (a int) ; 3278INSERT IGNORE INTO t2 VALUES (0), (3), (0), (2); 3279 3280SET SESSION join_cache_level = 0; 3281 3282EXPLAIN 3283SELECT * FROM (SELECT DISTINCT * FROM t1) t 3284 WHERE t.a IN (SELECT t2.a FROM t2); 3285SELECT * FROM (SELECT DISTINCT * FROM t1) t 3286 WHERE t.a IN (SELECT t2.a FROM t2); 3287 3288SET SESSION join_cache_level = 1; 3289 3290EXPLAIN 3291SELECT * FROM (SELECT DISTINCT * FROM t1) t 3292 WHERE t.a IN (SELECT t2.a FROM t2); 3293SELECT * FROM (SELECT DISTINCT * FROM t1) t 3294 WHERE t.a IN (SELECT t2.a FROM t2); 3295 3296DROP TABLE t1, t2; 3297 3298SET SESSION join_cache_level=@save_join_cache_level; 3299 3300# Note that next tests are run with same optimizer_switch as previous one! 3301 3302--echo # 3303--echo # Bug #887479: join_cache_level=3 + semijoin=on 3304--echo # 3305 3306CREATE TABLE t1 (a int, b int); 3307INSERT INTO t1 VALUES (3914,17), (3710,5), (3888,20); 3308 3309CREATE TABLE t2 (c int, KEY (c)); 3310INSERT INTO t2 VALUES (27), (17), (33), (20), (3), (7), (18), (2); 3311 3312SET @tmp_optimizer_switch=@@optimizer_switch; 3313SET SESSION optimizer_switch='semijoin=on'; 3314SET SESSION optimizer_switch='semijoin_with_cache=on'; 3315 3316SET SESSION join_cache_level=1; 3317EXPLAIN 3318SELECT * FROM t1 WHERE (t1.b) IN (SELECT c FROM t2); 3319SELECT * FROM t1 WHERE (t1.b) IN (SELECT c FROM t2); 3320 3321SET SESSION join_cache_level=3; 3322EXPLAIN 3323SELECT * FROM t1 WHERE (t1.b) IN (SELECT c FROM t2); 3324SELECT * FROM t1 WHERE (t1.b) IN (SELECT c FROM t2); 3325 3326SET SESSION join_cache_level=@save_join_cache_level; 3327set @@optimizer_switch=@tmp_optimizer_switch; 3328 3329DROP TABLE t1,t2; 3330 3331--echo # 3332--echo # Bug #899777: join_cache_level=4 + semijoin=on 3333--echo # 3334 3335CREATE TABLE t1 (a int, b int, c int, UNIQUE INDEX idx (a)); 3336INSERT INTO t1 VALUES (1,8,6), (2,2,8); 3337CREATE TABLE t2 (a int, b int, c int, UNIQUE INDEX idx (a)); 3338INSERT INTO t2 VALUES (1,8,6), (2,2,8); 3339CREATE TABLE t3 (a int, b int, c int, UNIQUE INDEX idx (a)); 3340INSERT INTO t3 VALUES (1,8,6), (2,2,8); 3341CREATE TABLE t4 (a int, b int, c int, UNIQUE INDEX idx (a)); 3342INSERT INTO t4 VALUES (1,8,6), (2,2,8); 3343 3344SET @tmp_optimizer_switch=@@optimizer_switch; 3345SET SESSION optimizer_switch='semijoin=on'; 3346SET SESSION optimizer_switch='semijoin_with_cache=on'; 3347 3348SET SESSION join_cache_level=1; 3349EXPLAIN 3350SELECT t1.* FROM t1,t2 3351 WHERE (t1.b,t2.b) IN (SELECT t3.b,t4.b FROM t3,t4 WHERE t4.c=t3.b) 3352 AND t1.a = 1; 3353SELECT t1.* FROM t1,t2 3354 WHERE (t1.b,t2.b) IN (SELECT t3.b,t4.b FROM t3,t4 WHERE t4.c=t3.b) 3355 AND t1.a = 1; 3356 3357SET SESSION join_cache_level=4; 3358EXPLAIN 3359SELECT t1.* FROM t1,t2 3360 WHERE (t1.b,t2.b) IN (SELECT t3.b,t4.b FROM t3,t4 WHERE t4.c=t3.b) 3361 AND t1.a = 1; 3362SELECT t1.* FROM t1,t2 3363 WHERE (t1.b,t2.b) IN (SELECT t3.b,t4.b FROM t3,t4 WHERE t4.c=t3.b) 3364 AND t1.a = 1; 3365 3366SET SESSION join_cache_level=@save_join_cache_level; 3367set @@optimizer_switch=@local_optimizer_switch; 3368 3369DROP TABLE t1,t2,t3,t4; 3370 3371--echo # 3372--echo # Bug #899509: an attempt to use hash join with join_cache_level=0 3373--echo # 3374 3375CREATE TABLE t1 (a int); 3376INSERT INTO t1 VALUES (8), (7); 3377CREATE TABLE t2 (a int); 3378INSERT INTO t2 VALUES (8), (7); 3379CREATE TABLE t3 (a int); 3380INSERT INTO t3 VALUES (8), (7); 3381 3382set @@optimizer_switch='semijoin_with_cache=off'; 3383set @@optimizer_switch='outer_join_with_cache=off'; 3384set @@optimizer_switch='derived_merge=off,derived_with_keys=off'; 3385SET join_cache_level=0; 3386 3387EXPLAIN 3388SELECT * FROM (SELECT t1.* FROM t1, t2) t WHERE t.a IN (SELECT * FROM t3); 3389SELECT * FROM (SELECT t1.* FROM t1, t2) t WHERE t.a IN (SELECT * FROM t3); 3390 3391SELECT * FROM ( SELECT ta.* FROM t1 AS ta, t1 ) tb WHERE a IN ( SELECT * FROM t1 ); 3392 3393SET SESSION join_cache_level=@save_join_cache_level; 3394set @@optimizer_switch=@local_optimizer_switch; 3395 3396DROP TABLE t1,t2,t3; 3397 3398--echo # 3399--echo # Bug #900469: semijoin + BNLH + ORDER BY 3400--echo # 3401 3402CREATE TABLE t1 (a int, b int); 3403INSERT INTO t1 VALUES (8,10); 3404 3405CREATE TABLE t2 (c int, d int); 3406INSERT INTO t2 VALUES (8,10); 3407INSERT INTO t2 VALUES (9,11); 3408 3409CREATE TABLE t3 (c int, d int); 3410INSERT INTO t3 VALUES (8,10); 3411INSERT INTO t3 VALUES (9,11); 3412 3413set @@optimizer_switch='semijoin_with_cache=on'; 3414set @@optimizer_switch='firstmatch=off'; 3415 3416SET join_cache_level=1; 3417EXPLAIN 3418SELECT * FROM t1,t2 WHERE b IN (SELECT d FROM t3 WHERE c <= t2.c) ORDER BY a,d; 3419SELECT * FROM t1,t2 WHERE b IN (SELECT d FROM t3 WHERE c <= t2.c) ORDER BY a,d; 3420 3421SET join_cache_level=3; 3422EXPLAIN 3423SELECT * FROM t1,t2 WHERE b IN (SELECT d FROM t3 WHERE c <= t2.c); 3424SELECT * FROM t1,t2 WHERE b IN (SELECT d FROM t3 WHERE c <= t2.c); 3425 3426SET join_cache_level=3; 3427EXPLAIN 3428SELECT * FROM t1,t2 WHERE b IN (SELECT d FROM t3 WHERE c <= t2.c) ORDER BY a,d; 3429SELECT * FROM t1,t2 WHERE b IN (SELECT d FROM t3 WHERE c <= t2.c) ORDER BY a,d; 3430 3431SET SESSION join_cache_level=@save_join_cache_level; 3432set @@optimizer_switch=@local_optimizer_switch; 3433 3434DROP TABLE t1,t2,t3; 3435 3436--echo # 3437--echo # Bug #901478: semijoin + ORDER BY + join_cache_level=4|6 3438--echo # 3439 3440CREATE TABLE t1 (a char(1)); 3441INSERT INTO t1 VALUES ('x'); 3442CREATE TABLE t2 (a int, b int, c int, KEY(a), KEY(b), KEY(c)); 3443INSERT INTO t2 VALUES 3444 (9,1,0), (7,2,8), (2,3,5), (4,2,9), (8,3,8), (3,4,1), (5,5,4); 3445CREATE TABLE t3 (a CHAR(1)); 3446INSERT INTO t3 VALUES ('x'); 3447CREATE TABLE t4 (a int, b int, c int, KEY(b), KEY(c)); 3448INSERT INTO t4 VALUES 3449 (9,1,0), (7,2,8), (2,3,5), (4,2,9), (8,3,8), (3,4,1), (5,5,4); 3450INSERT INTO t4 VALUES 3451 (19,11,10), (17,12,18), (12,13,15), (14,12,19), 3452 (18,13,18), (13,14,11), (15,15,14); 3453 3454SET @@optimizer_switch='semijoin=on'; 3455SET @@optimizer_switch='firstmatch=off'; 3456SET @@optimizer_switch='mrr=off'; 3457SET @@optimizer_switch='semijoin_with_cache=off'; 3458 3459set join_cache_level=1; 3460EXPLAIN 3461SELECT * FROM t1,t2 3462 WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND 3463 t2.a BETWEEN 4 and 5 3464 ORDER BY t2.b; 3465SELECT * FROM t1,t2 3466 WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND 3467 t2.a BETWEEN 4 and 5 3468 ORDER BY t2.b; 3469 3470set join_cache_level=4; 3471EXPLAIN 3472SELECT * FROM t1,t2 3473 WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND 3474 t2.a BETWEEN 4 and 5 3475 ORDER BY t2.b; 3476SELECT * FROM t1,t2 3477 WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND 3478 t2.a BETWEEN 4 and 5 3479 ORDER BY t2.b; 3480 3481SET @@optimizer_switch='semijoin_with_cache=on'; 3482set join_cache_level=6; 3483EXPLAIN 3484SELECT * FROM t1,t2 3485 WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND 3486 t2.a BETWEEN 4 and 5 3487 ORDER BY t2.b; 3488SELECT * FROM t1,t2 3489 WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND 3490 t2.a BETWEEN 4 and 5 3491 ORDER BY t2.b; 3492 3493set join_cache_level=@save_join_cache_level; 3494set @@optimizer_switch=@local_optimizer_switch; 3495 3496DROP TABLE t1,t2,t3,t4; 3497 3498--echo # 3499--echo # Bug#53305 Duplicate weedout + join buffer (join cache --level=7,8) 3500--echo # 3501 3502create table t1 (uid int, fid int, index(uid)); 3503insert into t1 values 3504 (1,1), (1,2), (1,3), (1,4), 3505 (2,5), (2,6), (2,7), (2,8), 3506 (3,1), (3,2), (3,9); 3507 3508create table t2 (uid int primary key, name varchar(128), index(name)); 3509insert into t2 values 3510 (1, "A"), (2, "B"), (3, "C"), (4, "D"), (5, "E"), 3511 (6, "F"), (7, "G"), (8, "H"), (9, "I"); 3512 3513create table t3 (uid int, fid int, index(uid)); 3514insert into t3 values 3515 (1,1), (1,2), (1,3),(1,4), 3516 (2,5), (2,6), (2,7), (2,8), 3517 (3,1), (3,2), (3,9); 3518 3519set @@optimizer_switch='semijoin=on'; 3520set @@optimizer_switch='materialization=off'; 3521set @@optimizer_switch='loosescan=off,firstmatch=off'; 3522set @@optimizer_switch='mrr_sort_keys=off'; 3523set join_cache_level=7; 3524 3525create table t4 (uid int primary key, name varchar(128), index(name)); 3526insert into t4 values 3527 (1, "A"), (2, "B"), (3, "C"), (4, "D"), (5, "E"), 3528 (6, "F"), (7, "G"), (8, "H"), (9, "I"); 3529 3530explain select name from t2, t1 3531 where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid) 3532 and t2.uid=t1.fid; 3533 3534--sorted_result 3535select name from t2, t1 3536 where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid) 3537 and t2.uid=t1.fid; 3538 3539set join_cache_level=@save_join_cache_level; 3540set @@optimizer_switch=@local_optimizer_switch; 3541 3542drop table t1,t2,t3,t4; 3543 3544--echo # 3545--echo # Bug#50358 - semijoin execution of subquery with outerjoin 3546--echo # emplying join buffer 3547--echo # 3548 3549CREATE TABLE t1 (i int); 3550CREATE TABLE t2 (i int); 3551CREATE TABLE t3 (i int); 3552INSERT INTO t1 VALUES (1), (2); 3553INSERT INTO t2 VALUES (6); 3554INSERT INTO t3 VALUES (1), (2); 3555 3556set @@optimizer_switch='semijoin=on'; 3557set @@optimizer_switch='materialization=on'; 3558 3559set join_cache_level=0; 3560EXPLAIN 3561SELECT * FROM t1 WHERE t1.i IN 3562 (SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i); 3563SELECT * FROM t1 WHERE t1.i IN 3564 (SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i); 3565 3566set join_cache_level=2; 3567EXPLAIN 3568SELECT * FROM t1 WHERE t1.i IN 3569 (SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i); 3570SELECT * FROM t1 WHERE t1.i IN 3571 (SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i); 3572 3573set join_cache_level=@save_join_cache_level; 3574set @@optimizer_switch=@local_optimizer_switch; 3575 3576DROP TABLE t1,t2,t3; 3577 3578--echo # 3579--echo # Bug #12546542: missing row with semijoin=off + join cache 3580--echo # (LP bug #922971) 3581--echo # 3582 3583CREATE TABLE t1 (a varchar(1024)); 3584INSERT INTO t1 VALUES ('v'), ('we'); 3585CREATE TABLE t2 ( 3586 a varchar(1024) CHARACTER SET utf8 DEFAULT NULL, b int, c int 3587); 3588INSERT INTO t2 VALUES ('we',4,NULL), ('v',1305673728,6); 3589CREATE TABLE t3 (b int, c int); 3590INSERT INTO t3 VALUES (4,4); 3591 3592set @@optimizer_switch='semijoin=off'; 3593set @@optimizer_switch='materialization=off'; 3594 3595set join_cache_level=0; 3596EXPLAIN 3597SELECT * FROM t1 3598 WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b 3599 WHERE t2.c < 10 OR t3.c > 1); 3600 3601SELECT * FROM t1 3602 WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b 3603 WHERE t2.c < 10 OR t3.c > 1); 3604 3605set join_cache_level=2; 3606EXPLAIN 3607SELECT * FROM t1 3608 WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b 3609 WHERE t2.c < 10 OR t3.c > 1); 3610SELECT * FROM t1 3611 WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b 3612 WHERE t2.c < 10 OR t3.c > 1); 3613 3614set join_cache_level=@save_join_cache_level; 3615set @@optimizer_switch=@local_optimizer_switch; 3616 3617DROP TABLE t1,t2,t3; 3618 3619--echo # 3620--echo # lp:925985 LEFT JOIN with optimize_join_buffer_size=off + 3621--echo # join_buffer_size > join_buffer_space_limit 3622--echo # 3623 3624CREATE TABLE t1 (a int); 3625INSERT INTO t1 VALUES (5), (3); 3626 3627CREATE TABLE t2 (a int, b int); 3628INSERT INTO t2 VALUES 3629 (3,30), (1,10), (7,70), (2,20), 3630 (3,31), (1,11), (7,71), (2,21), 3631 (3,32), (1,12), (7,72), (2,22); 3632 3633CREATE TABLE t3 (b int, c int); 3634INSERT INTO t3 VALUES (32, 302), (42,400), (30,300); 3635 3636set @@optimizer_switch='optimize_join_buffer_size=off'; 3637set @@optimizer_switch='outer_join_with_cache=on'; 3638set join_buffer_space_limit=4096; 3639set join_buffer_size=4096*2; 3640set join_cache_level=2; 3641 3642EXPLAIN 3643SELECT * FROM t1, t2 LEFT JOIN t3 ON t2.b=t3.b WHERE t1.a=t2.a; 3644SELECT * FROM t1, t2 LEFT JOIN t3 ON t2.b=t3.b WHERE t1.a=t2.a; 3645 3646set join_buffer_space_limit=@save_join_buffer_space_limit; 3647set join_buffer_size=@save_join_buffer_size; 3648set join_cache_level=@save_join_cache_level; 3649set @@optimizer_switch=@local_optimizer_switch; 3650 3651DROP TABLE t1,t2,t3; 3652 3653--echo # 3654--echo # Bug #1058071: LEFT JOIN using blobs 3655--echo # (MDEV-564) when join buffer size is small 3656--echo # 3657 3658CREATE TABLE t1 ( 3659 col269 decimal(31,10) unsigned DEFAULT NULL, 3660 col280 geometry DEFAULT NULL, 3661 col281 tinyint(1) DEFAULT NULL, 3662 col282 time NOT NULL, 3663 col284 datetime DEFAULT NULL, 3664 col286 date DEFAULT NULL, 3665 col287 datetime DEFAULT NULL, 3666 col288 decimal(30,29) DEFAULT NULL, 3667 col291 time DEFAULT NULL, 3668 col292 time DEFAULT NULL 3669) ENGINE=Aria; 3670 3671INSERT INTO t1 VALUES 3672(0.0,PointFromText('POINT(9 0)'),0,'11:24:05','2013-04-14 21:30:28',NULL,'2011-12-20 06:00:34',9.9,'13:04:39',NULL), 3673(0.0,NULL,127,'05:43:12','2012-09-05 06:15:27','2027-01-01','2011-10-29 10:48:29',0.0,'06:24:05','11:33:37'), 3674(0.0,NULL,127,'12:54:41','2013-01-12 11:32:58','2011-11-03','2013-01-03 02:00:34',00,'11:54:15','20:19:15'), 3675(0.0,PointFromText('POINT(9 0)'),0,'19:48:07','2012-07-16 15:45:25','2012-03-25','2013-09-07 17:21:52',0.5,'17:36:54','21:24:19'), 3676(0.0,PointFromText('POINT(9 0)'),0,'03:43:48','2012-09-28 00:00:00','2012-06-26','2011-11-16 05:01:09',00,'01:25:42','19:30:06'), 3677(0.0,LineStringFromText('LINESTRING(0 0,9 9,0 0,9 0,0 0)'),127,'11:33:21','2012-03-31 10:29:22','2012-10-10','2012-04-21 19:21:06',NULL,'05:13:22','09:48:34'), 3678(NULL,PointFromText('POINT(9 0)'),127,'00:00:00','0000-00-00','2012-04-04 21:26:12','2013-03-04',0.0,'12:54:30',NULL), 3679(NULL,PointFromText('POINT(9 0)'),1,'00:00:00','2013-05-01 22:37:49','2013-06-26','2012-09-22 17:31:03',0.0,'08:09:57','11:15:36'); 3680 3681CREATE TABLE t2 (b int) ENGINE=Aria; 3682INSERT INTO t2 VALUES (NULL); 3683CREATE TABLE t3 (c int) ENGINE=Aria; 3684INSERT INTO t3 VALUES (NULL); 3685 3686set @@optimizer_switch = 'outer_join_with_cache=on,join_cache_incremental=on'; 3687set join_buffer_size=128; 3688 3689EXPLAIN 3690SELECT 1 AS c FROM t1 NATURAL LEFT JOIN t2 LEFT OUTER JOIN t3 ON 1 3691 GROUP BY elt(t1.col282,1,t1.col280); 3692 3693SELECT 1 AS c FROM t1 NATURAL LEFT JOIN t2 LEFT OUTER JOIN t3 ON 1 3694 GROUP BY elt(t1.col282,1,t1.col280); 3695 3696DROP table t1,t2,t3; 3697set join_buffer_size=@save_join_buffer_size; 3698 3699# 3700# --echo switch to use orginal test suite optimizer switch 3701# 3702 3703set @@optimizer_switch=@org_optimizer_switch,@local_optimizer_switch= @org_optimizer_switch; 3704 3705 3706--echo # 3707--echo # MDEV-5293: outer join, join buffering, and order by - invalid query plan 3708--echo # 3709create table t0 (a int primary key) engine=myisam; 3710insert into t0 values (1); 3711 3712create table t1(a int) engine=myisam; 3713insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 3714alter table t1 add b int; 3715 3716create table t2 like t1; 3717insert into t2 select * from t1; 3718--echo #The following must use "Using temporary; Using filesort" and not just "Using filesort": 3719explain select * from t0,t1 left join t2 on t1.b=t2.b order by t0.a, t1.a; 3720 3721drop table t0,t1,t2; 3722 3723--echo # MDEV-6292: huge performance degradation for a sequence 3724--echo # of LEFT JOIN operations when using join buffer 3725--echo # 3726 3727--source include/have_innodb.inc 3728 3729CREATE TABLE t1 ( 3730 id int(11) NOT NULL AUTO_INCREMENT, 3731 col1 varchar(255) NOT NULL DEFAULT '', 3732 PRIMARY KEY (id) 3733) ENGINE=INNODB; 3734 3735CREATE TABLE t2 ( 3736 id int(11) NOT NULL AUTO_INCREMENT, 3737 parent_id smallint(3) NOT NULL DEFAULT '0', 3738 col2 varchar(25) NOT NULL DEFAULT '', 3739 PRIMARY KEY (id) 3740) ENGINE=INNODB; 3741 3742set join_buffer_size=8192; 3743 3744set join_cache_level=0; 3745 3746set @init_time:=now(); 3747SELECT t.* 3748FROM 3749 t1 t 3750 LEFT JOIN t2 c1 ON c1.parent_id = t.id AND c1.col2 = "val" 3751 LEFT JOIN t2 c2 ON c2.parent_id = t.id AND c2.col2 = "val" 3752 LEFT JOIN t2 c3 ON c3.parent_id = t.id AND c3.col2 = "val" 3753 LEFT JOIN t2 c4 ON c4.parent_id = t.id AND c4.col2 = "val" 3754 LEFT JOIN t2 c5 ON c5.parent_id = t.id AND c5.col2 = "val" 3755 LEFT JOIN t2 c6 ON c6.parent_id = t.id AND c6.col2 = "val" 3756 LEFT JOIN t2 c7 ON c7.parent_id = t.id AND c7.col2 = "val" 3757 LEFT JOIN t2 c8 ON c8.parent_id = t.id AND c8.col2 = "val" 3758 LEFT JOIN t2 c9 ON c9.parent_id = t.id AND c9.col2 = "val" 3759 LEFT JOIN t2 c10 ON c10.parent_id = t.id AND c10.col2 = "val" 3760 LEFT JOIN t2 c11 ON c11.parent_id = t.id AND c11.col2 = "val" 3761 LEFT JOIN t2 c12 ON c12.parent_id = t.id AND c12.col2 = "val" 3762 LEFT JOIN t2 c13 ON c13.parent_id = t.id AND c13.col2 = "val" 3763 LEFT JOIN t2 c14 ON c14.parent_id = t.id AND c14.col2 = "val" 3764 LEFT JOIN t2 c15 ON c15.parent_id = t.id AND c15.col2 = "val" 3765 LEFT JOIN t2 c16 ON c16.parent_id = t.id AND c16.col2 = "val" 3766 LEFT JOIN t2 c17 ON c17.parent_id = t.id AND c17.col2 = "val" 3767 LEFT JOIN t2 c18 ON c18.parent_id = t.id AND c18.col2 = "val" 3768 LEFT JOIN t2 c19 ON c19.parent_id = t.id AND c19.col2 = "val" 3769 LEFT JOIN t2 c20 ON c20.parent_id = t.id AND c20.col2 = "val" 3770 LEFT JOIN t2 c21 ON c21.parent_id = t.id AND c21.col2 = "val" 3771 LEFT JOIN t2 c22 ON c22.parent_id = t.id AND c22.col2 = "val" 3772 LEFT JOIN t2 c23 ON c23.parent_id = t.id AND c23.col2 = "val" 3773 LEFT JOIN t2 c24 ON c24.parent_id = t.id AND c24.col2 = "val" 3774 LEFT JOIN t2 c25 ON c25.parent_id = t.id AND c25.col2 = "val" 3775 LEFT JOIN t2 c26 ON c26.parent_id = t.id AND c26.col2 = "val" 3776 LEFT JOIN t2 c27 ON c27.parent_id = t.id AND c27.col2 = "val" 3777ORDER BY 3778 col1; 3779select timestampdiff(second, @init_time, now()) <= 5; 3780 3781set join_cache_level=2; 3782 3783set @init_time:=now(); 3784SELECT t.* 3785FROM 3786 t1 t 3787 LEFT JOIN t2 c1 ON c1.parent_id = t.id AND c1.col2 = "val" 3788 LEFT JOIN t2 c2 ON c2.parent_id = t.id AND c2.col2 = "val" 3789 LEFT JOIN t2 c3 ON c3.parent_id = t.id AND c3.col2 = "val" 3790 LEFT JOIN t2 c4 ON c4.parent_id = t.id AND c4.col2 = "val" 3791 LEFT JOIN t2 c5 ON c5.parent_id = t.id AND c5.col2 = "val" 3792 LEFT JOIN t2 c6 ON c6.parent_id = t.id AND c6.col2 = "val" 3793 LEFT JOIN t2 c7 ON c7.parent_id = t.id AND c7.col2 = "val" 3794 LEFT JOIN t2 c8 ON c8.parent_id = t.id AND c8.col2 = "val" 3795 LEFT JOIN t2 c9 ON c9.parent_id = t.id AND c9.col2 = "val" 3796 LEFT JOIN t2 c10 ON c10.parent_id = t.id AND c10.col2 = "val" 3797 LEFT JOIN t2 c11 ON c11.parent_id = t.id AND c11.col2 = "val" 3798 LEFT JOIN t2 c12 ON c12.parent_id = t.id AND c12.col2 = "val" 3799 LEFT JOIN t2 c13 ON c13.parent_id = t.id AND c13.col2 = "val" 3800 LEFT JOIN t2 c14 ON c14.parent_id = t.id AND c14.col2 = "val" 3801 LEFT JOIN t2 c15 ON c15.parent_id = t.id AND c15.col2 = "val" 3802 LEFT JOIN t2 c16 ON c16.parent_id = t.id AND c16.col2 = "val" 3803 LEFT JOIN t2 c17 ON c17.parent_id = t.id AND c17.col2 = "val" 3804 LEFT JOIN t2 c18 ON c18.parent_id = t.id AND c18.col2 = "val" 3805 LEFT JOIN t2 c19 ON c19.parent_id = t.id AND c19.col2 = "val" 3806 LEFT JOIN t2 c20 ON c20.parent_id = t.id AND c20.col2 = "val" 3807 LEFT JOIN t2 c21 ON c21.parent_id = t.id AND c21.col2 = "val" 3808 LEFT JOIN t2 c22 ON c22.parent_id = t.id AND c22.col2 = "val" 3809 LEFT JOIN t2 c23 ON c23.parent_id = t.id AND c23.col2 = "val" 3810 LEFT JOIN t2 c24 ON c24.parent_id = t.id AND c24.col2 = "val" 3811 LEFT JOIN t2 c25 ON c25.parent_id = t.id AND c25.col2 = "val" 3812 LEFT JOIN t2 c26 ON c26.parent_id = t.id AND c26.col2 = "val" 3813 LEFT JOIN t2 c27 ON c27.parent_id = t.id AND c27.col2 = "val" 3814ORDER BY 3815 col1; 3816select timestampdiff(second, @init_time, now()) <= 5; 3817 3818EXPLAIN 3819SELECT t.* 3820FROM 3821 t1 t 3822 LEFT JOIN t2 c1 ON c1.parent_id = t.id AND c1.col2 = "val" 3823 LEFT JOIN t2 c2 ON c2.parent_id = t.id AND c2.col2 = "val" 3824 LEFT JOIN t2 c3 ON c3.parent_id = t.id AND c3.col2 = "val" 3825 LEFT JOIN t2 c4 ON c4.parent_id = t.id AND c4.col2 = "val" 3826 LEFT JOIN t2 c5 ON c5.parent_id = t.id AND c5.col2 = "val" 3827 LEFT JOIN t2 c6 ON c6.parent_id = t.id AND c6.col2 = "val" 3828 LEFT JOIN t2 c7 ON c7.parent_id = t.id AND c7.col2 = "val" 3829 LEFT JOIN t2 c8 ON c8.parent_id = t.id AND c8.col2 = "val" 3830 LEFT JOIN t2 c9 ON c9.parent_id = t.id AND c9.col2 = "val" 3831 LEFT JOIN t2 c10 ON c10.parent_id = t.id AND c10.col2 = "val" 3832 LEFT JOIN t2 c11 ON c11.parent_id = t.id AND c11.col2 = "val" 3833 LEFT JOIN t2 c12 ON c12.parent_id = t.id AND c12.col2 = "val" 3834 LEFT JOIN t2 c13 ON c13.parent_id = t.id AND c13.col2 = "val" 3835 LEFT JOIN t2 c14 ON c14.parent_id = t.id AND c14.col2 = "val" 3836 LEFT JOIN t2 c15 ON c15.parent_id = t.id AND c15.col2 = "val" 3837 LEFT JOIN t2 c16 ON c16.parent_id = t.id AND c16.col2 = "val" 3838 LEFT JOIN t2 c17 ON c17.parent_id = t.id AND c17.col2 = "val" 3839 LEFT JOIN t2 c18 ON c18.parent_id = t.id AND c18.col2 = "val" 3840 LEFT JOIN t2 c19 ON c19.parent_id = t.id AND c19.col2 = "val" 3841 LEFT JOIN t2 c20 ON c20.parent_id = t.id AND c20.col2 = "val" 3842 LEFT JOIN t2 c21 ON c21.parent_id = t.id AND c21.col2 = "val" 3843 LEFT JOIN t2 c22 ON c22.parent_id = t.id AND c22.col2 = "val" 3844 LEFT JOIN t2 c23 ON c23.parent_id = t.id AND c23.col2 = "val" 3845 LEFT JOIN t2 c24 ON c24.parent_id = t.id AND c24.col2 = "val" 3846 LEFT JOIN t2 c25 ON c25.parent_id = t.id AND c25.col2 = "val" 3847 LEFT JOIN t2 c26 ON c26.parent_id = t.id AND c26.col2 = "val" 3848 LEFT JOIN t2 c27 ON c27.parent_id = t.id AND c27.col2 = "val" 3849ORDER BY 3850 col1; 3851 3852set join_buffer_size=@save_join_buffer_size; 3853set join_cache_level=@save_join_cache_level; 3854 3855DROP TABLE t1,t2; 3856 3857--echo # 3858--echo # MDEV-14960: BNLH used for materialized semi-join 3859--echo # 3860 3861CREATE TABLE t1 (i1 int); 3862CREATE TABLE t2 (e1 int); 3863CREATE TABLE t4 (e1 int); 3864CREATE TABLE t5 (e1 int); 3865 3866INSERT INTO t1 VALUES 3867 (1),(2),(3),(4),(5),(6),(7),(8); 3868INSERT INTO t1 SELECT i1+8 FROM t1; 3869INSERT INTO t1 SELECT i1+16 FROM t1; 3870INSERT INTO t1 SELECT i1+32 FROM t1; 3871INSERT INTO t1 SELECT i1+64 FROM t1; 3872INSERT INTO t2 SELECT * FROM t1; 3873INSERT INTO t4 SELECT * FROM t1; 3874INSERT INTO t5 SELECT * FROM t1; 3875 3876SET join_cache_level = 6; 3877SET join_buffer_size=4096; 3878SET join_buffer_space_limit=4096; 3879set @@optimizer_switch = 'join_cache_hashed=on,optimize_join_buffer_size=on'; 3880 3881let $q= 3882SELECT * FROM t1 3883WHERE 3884 i1 < 10 AND 3885 i1 IN 3886 (SELECT i1 FROM 3887 (SELECT (t4.e1) i1 FROM t4 3888 LEFT JOIN t5 ON t4.e1 = t5.e1 3889 LEFT JOIN (SELECT e1 FROM t2 ) AS d ON t4.e1 = d.e1) a); 3890 3891eval EXPLAIN $q; 3892eval $q; 3893 3894set join_cache_level=@save_join_cache_level; 3895SET join_buffer_size=@save_join_buffer_size; 3896SET join_buffer_space_limit=@save_join_buffer_space_limit; 3897set @@optimizer_switch=@local_optimizer_switch; 3898 3899DROP TABLE t1,t4,t5,t2; 3900 3901--echo # 3902--echo # MDEV-16603: BNLH for query with materialized semi-join 3903--echo # 3904 3905--source include/have_innodb.inc 3906 3907set join_cache_level=4; 3908 3909CREATE TABLE t1 ( i1 int, v1 varchar(1)) ENGINE=InnoDB; 3910INSERT INTO t1 VALUES (7,'x'); 3911 3912CREATE TABLE t2 (i1 int, v1 varchar(1), KEY v1 (v1,i1)) ENGINE=InnoDB; 3913 3914INSERT INTO t2 VALUES 3915 (NULL,'x'),(1,'x'),(3,'x'),(5,'x'),(8,'x'),(48,'x'), 3916 (228,'x'),(3,'y'),(1,'z'),(9,'z'); 3917 3918CREATE TABLE temp 3919SELECT t1.i1 AS f1, t1.v1 AS f2 FROM (t2 JOIN t1 ON (t1.v1 = t2.v1)); 3920 3921let $q = 3922SELECT * FROM temp 3923WHERE (f1,f2) IN (SELECT t1.i1, t1.v1 FROM (t2 JOIN t1 ON (t1.v1 = t2.v1))); 3924 3925eval $q; 3926eval EXPLAIN EXTENDED $q; 3927 3928DROP TABLE t1,t2,temp; 3929 3930set join_cache_level=@save_join_cache_level; 3931 3932--echo # 3933--echo # MDEV-5123 Remove duplicated conditions pushed both to join_tab->select_cond and join_tab->cache_select->cond for blocked joins. 3934--echo # 3935 3936set expensive_subquery_limit=0; 3937 3938create table t1 (c1 int); 3939create table t2 (c2 int); 3940create table t3 (c3 int); 3941 3942insert into t1 values (1), (2); 3943insert into t2 values (1), (2); 3944insert into t3 values (2); 3945 3946explain 3947select count(*) from t1 straight_join t2 3948where c1 = c2-0 and c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1); 3949 3950set @counter=0; 3951 3952select count(*) from t1 straight_join t2 3953where c1 = c2-0 and c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1); 3954 3955select @counter; 3956 3957explain 3958select count(*) from t1 straight_join t2 3959where c1 = c2-0 and 3960 c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1) and 3961 c2 / 2 = 1; 3962 3963set @counter=0; 3964 3965select count(*) from t1 straight_join t2 3966where c1 = c2-0 and 3967 c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1) and 3968 c2 / 2 = 1; 3969 3970select @counter; 3971 3972drop table t1,t2,t3; 3973set expensive_subquery_limit=@save_expensive_subquery_limit; 3974 3975--echo # 3976--echo # MDEV-6071: EXPLAIN chooses to use join buffer while execution turns it down 3977--echo # 3978 3979create table t1 (a int); 3980insert into t1 values 3981(7), (9), (1), (4), (2), (3), (5), (8), (11), (6), (10); 3982 3983explain select count(*) from t1, t1 t2 where t1.a=t2.a; 3984 3985set join_buffer_space_limit=1024*8; 3986 3987explain select count(*) from t1, t1 t2 where t1.a=t2.a; 3988 3989set join_buffer_space_limit=@save_join_buffer_space_limit; 3990 3991drop table t1; 3992 3993--echo # 3994--echo # MDEV-6687: Assertion `0' failed in Protocol::end_statement on query 3995--echo # 3996SET join_cache_level = 3; 3997--echo # The following should have 3998--echo # - table order PROFILING,user, 3999--echo # - table user accessed with hash_ALL: 4000explain 4001SELECT * FROM INFORMATION_SCHEMA.PROFILING, mysql.user WHERE password_expired = PAGE_FAULTS_MINOR; 4002 4003set join_cache_level=@save_join_cache_level; 4004 4005# 4006# MDEV-12580 Wrong query result in join when using an index (Version > "10.2.3") 4007# 4008create table t1 (c1 date not null, key (c1)) engine=innodb; 4009insert t1 values ('2017-12-27'); 4010create table t2 (pk int, f1 int, f2 int); 4011insert t2 values (4,1,1), (6,1,1); 4012set join_buffer_size = 222222208; 4013select f2 from t2,t1 where f2 = 0; 4014drop table t1, t2; 4015set join_buffer_size=@save_join_buffer_size; 4016 4017 4018--echo # 4019--echo # MDEV-21104: BNLH used for multi-join query with embedded outer join 4020--echo # and possible 'not exists' optimization 4021--echo # 4022 4023set join_cache_level=4; 4024 4025CREATE TABLE t1 (a int) ENGINE=MyISAM; 4026INSERT INTO t1 VALUES (1),(2); 4027CREATE TABLE t2 (b int, c int) ENGINE=MyISAM; 4028INSERT INTO t2 VALUES (1,2),(2,4); 4029CREATE TABLE t3 (d int, KEY(d)) ENGINE=MyISAM; 4030INSERT INTO t3 VALUES (1),(2); 4031CREATE TABLE t4 (e int primary key) ENGINE=MyISAM; 4032INSERT INTO t4 VALUES (1),(2); 4033ANALYZE TABLE t1,t2,t3,t4; 4034 4035SELECT * FROM t2 LEFT JOIN t3 ON c = d; 4036SELECT * FROM (t2 LEFT JOIN t3 ON c = d ) JOIN t4; 4037 4038let $q1= 4039SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e; 4040eval EXPLAIN $q1; 4041eval $q1; 4042 4043let $q2= 4044SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e 4045 WHERE e IS NULL; 4046eval EXPLAIN $q2; 4047eval $q2; 4048 4049DROP TABLE t1,t2,t3,t4; 4050 4051set join_cache_level=@save_join_cache_level; 4052 4053--echo # 4054--echo # MDEV-24767: forced BNLH used for equi-join supported by compound index 4055--echo # 4056 4057create table t1 (a int, b int, c int ) engine=myisam ; 4058create table t2 (a int, b int, c int, primary key (c,a,b)) engine=myisam ; 4059insert into t1 values (3,4,2), (5,6,4); 4060insert into t2 values (3,4,2), (5,6,4); 4061 4062let $q= 4063select t1.a, t1.b, t1.c from t1,t2 4064 where t2.a = t1.a and t2.b = t1.b and t2.c=t1.c; 4065 4066eval $q; 4067eval explain $q; 4068 4069set join_cache_level=3; 4070eval $q; 4071eval explain $q; 4072 4073drop table t1,t2; 4074 4075set join_cache_level=@save_join_cache_level; 4076 4077--echo # 4078--echo # MDEV-21243: Join buffer: condition is checked in wrong place for range access 4079--echo # 4080create table t1(a int primary key); 4081insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 4082create table t2 (a int); 4083insert into t2 select A.a + 10*B.a from t1 A, t1 B; 4084 4085create table t3 ( 4086 kp1 int, 4087 kp2 int, 4088 col1 int, 4089 col2 int, 4090 key (kp1, kp2) 4091); 4092 4093insert into t3 4094select 4095 A.a, 4096 B.a, 4097 A.a + 100*B.a, 4098 A.a + 100*B.a 4099from 4100 t2 A, t2 B; 4101analyze table t3; 4102 4103--echo # The following must have "B.col1 + 1 < 33333" attached to table B 4104--echo # and not to the block-nl-join node: 4105explain format=json 4106select * 4107from t1 a, t3 b 4108where 4109 b.kp1=a.a and 4110 b.kp1 <= 10 and 4111 b.kp2 <= 10 and 4112 b.col1 +1 < 33333; 4113 4114drop table t1,t2,t3; 4115 4116--echo # End of 10.3 tests 4117 4118# The following command must be the last one in the file 4119set @@optimizer_switch=@save_optimizer_switch; 4120