1set optimizer_switch='block_nested_loop=on'; 2set optimizer_switch='mrr_cost_based=off'; 3DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11; 4DROP DATABASE IF EXISTS world; 5set names utf8; 6CREATE DATABASE world; 7use world; 8CREATE TABLE Country ( 9Code char(3) NOT NULL default '', 10Name char(52) NOT NULL default '', 11SurfaceArea float(10,2) NOT NULL default '0.00', 12Population int(11) NOT NULL default '0', 13Capital int(11) default NULL 14); 15CREATE TABLE City ( 16ID int(11) NOT NULL, 17Name char(35) NOT NULL default '', 18Country char(3) NOT NULL default '', 19Population int(11) NOT NULL default '0' 20); 21CREATE TABLE CountryLanguage ( 22Country char(3) NOT NULL default '', 23Language char(30) NOT NULL default '', 24Percentage float(3,1) NOT NULL default '0.0' 25); 26SELECT COUNT(*) FROM Country; 27COUNT(*) 28239 29SELECT COUNT(*) FROM City; 30COUNT(*) 314079 32SELECT COUNT(*) FROM CountryLanguage; 33COUNT(*) 34984 35show variables like 'join_buffer_size'; 36Variable_name Value 37join_buffer_size 262144 38EXPLAIN 39SELECT City.Name, Country.Name FROM City,Country 40WHERE City.Country=Country.Code AND 41Country.Name LIKE 'L%' AND City.Population > 100000; 42id select_type table type possible_keys key key_len ref rows Extra 431 SIMPLE Country ALL NULL NULL NULL NULL 239 Using where 441 SIMPLE City ALL NULL NULL NULL NULL 4079 Using where; Using join buffer (Block Nested Loop) 45SELECT City.Name, Country.Name FROM City,Country 46WHERE City.Country=Country.Code AND 47Country.Name LIKE 'L%' AND City.Population > 100000; 48Name Name 49?iauliai Lithuania 50Beirut Lebanon 51Bengasi Libyan Arab Jamahiriya 52Daugavpils Latvia 53Kaunas Lithuania 54Klaipeda Lithuania 55Maseru Lesotho 56Misrata Libyan Arab Jamahiriya 57Monrovia Liberia 58Panevezys Lithuania 59Riga Latvia 60Tripoli Lebanon 61Tripoli Libyan Arab Jamahiriya 62Vientiane Laos 63Vilnius Lithuania 64EXPLAIN 65SELECT City.Name, Country.Name, CountryLanguage.Language 66FROM City,Country,CountryLanguage 67WHERE City.Country=Country.Code AND 68CountryLanguage.Country=Country.Code AND 69City.Name LIKE 'L%' AND Country.Population > 3000000 AND 70CountryLanguage.Percentage > 50; 71id select_type table type possible_keys key key_len ref rows Extra 721 SIMPLE Country ALL NULL NULL NULL NULL 239 Using where 731 SIMPLE CountryLanguage ALL NULL NULL NULL NULL 984 Using where; Using join buffer (Block Nested Loop) 741 SIMPLE City ALL NULL NULL NULL NULL 4079 Using where; Using join buffer (Block Nested Loop) 75SELECT City.Name, Country.Name, CountryLanguage.Language 76FROM City,Country,CountryLanguage 77WHERE City.Country=Country.Code AND 78CountryLanguage.Country=Country.Code AND 79City.Name LIKE 'L%' AND Country.Population > 3000000 AND 80CountryLanguage.Percentage > 50; 81Name Name Language 82La Ceiba Honduras Spanish 83La Habana Cuba Spanish 84La Matanza Argentina Spanish 85La Paz Bolivia Spanish 86La Paz Mexico Spanish 87La Paz Mexico Spanish 88La Plata Argentina Spanish 89La Rioja Argentina Spanish 90La Romana Dominican Republic Spanish 91La Serena Chile Spanish 92La Spezia Italy Italian 93Lafayette United States English 94Lages Brazil Portuguese 95Lagos de Moreno Mexico Spanish 96Lahti Finland Finnish 97Laiwu China Chinese 98Laiyang China Chinese 99Laizhou China Chinese 100Lakewood United States English 101Lalitapur Nepal Nepali 102Lambaré Paraguay Spanish 103Lancaster United States English 104Langfang China Chinese 105Lansing United States English 106Lanzhou China Chinese 107Lanús Argentina Spanish 108Laohekou China Chinese 109Laredo United States English 110Larisa Greece Greek 111Las Heras Argentina Spanish 112Las Margaritas Mexico Spanish 113Las Palmas de Gran Canaria Spain Spanish 114Las Vegas United States English 115Lashio (Lasho) Myanmar Burmese 116Latakia Syria Arabic 117Latina Italy Italian 118Lauro de Freitas Brazil Portuguese 119Lausanne Switzerland German 120Laval Canada English 121Le Havre France French 122Le Mans France French 123Le-Cap-Haïtien Haiti Haiti Creole 124Lecce Italy Italian 125Leeds United Kingdom English 126Leganés Spain Spanish 127Legnica Poland Polish 128Leicester United Kingdom English 129Leiden Netherlands Dutch 130Leipzig Germany German 131Leiyang China Chinese 132Lengshuijiang China Chinese 133Leninsk-Kuznetski Russian Federation Russian 134Lerdo Mexico Spanish 135Lerma Mexico Spanish 136Leshan China Chinese 137Leverkusen Germany German 138Lexington-Fayette United States English 139León Mexico Spanish 140León Nicaragua Spanish 141León Spain Spanish 142Lhasa China Chinese 143Liangcheng China Chinese 144Lianyuan China Chinese 145Lianyungang China Chinese 146Liaocheng China Chinese 147Liaoyang China Chinese 148Liaoyuan China Chinese 149Liberec Czech Republic Czech 150Lida Belarus Belorussian 151Liling China Chinese 152Lille France French 153Lilongwe Malawi Chichewa 154Lima Peru Spanish 155Limeira Brazil Portuguese 156Limoges France French 157Linchuan China Chinese 158Lincoln United States English 159Linfen China Chinese 160Linhai China Chinese 161Linhares Brazil Portuguese 162Linhe China Chinese 163Linköping Sweden Swedish 164Linqing China Chinese 165Linyi China Chinese 166Linz Austria German 167Lipetsk Russian Federation Russian 168Lisboa Portugal Portuguese 169Little Rock United States English 170Liupanshui China Chinese 171Liuzhou China Chinese 172Liu´an China Chinese 173Liverpool United Kingdom English 174Livonia United States English 175Livorno Italy Italian 176Liyang China Chinese 177Liège Belgium Dutch 178Ljubertsy Russian Federation Russian 179Lleida (Lérida) Spain Spanish 180Logroño Spain Spanish 181Loja Ecuador Spanish 182Lomas de Zamora Argentina Spanish 183London Canada English 184London United Kingdom English 185Londrina Brazil Portuguese 186Long Beach United States English 187Long Xuyen Vietnam Vietnamese 188Longjing China Chinese 189Longkou China Chinese 190Longueuil Canada English 191Longyan China Chinese 192Los Angeles Chile Spanish 193Los Angeles United States English 194Los Cabos Mexico Spanish 195Los Teques Venezuela Spanish 196Loudi China Chinese 197Louisville United States English 198Lowell United States English 199Lower Hutt New Zealand English 200Lubbock United States English 201Lublin Poland Polish 202Luchou Taiwan Min 203Ludwigshafen am Rhein Germany German 204Lugansk Ukraine Ukrainian 205Lund Sweden Swedish 206Lungtan Taiwan Min 207Luohe China Chinese 208Luoyang China Chinese 209Luton United Kingdom English 210Lutsk Ukraine Ukrainian 211Luxor Egypt Arabic 212Luzhou China Chinese 213Luziânia Brazil Portuguese 214Lviv Ukraine Ukrainian 215Lyon France French 216Lysyt?ansk Ukraine Ukrainian 217L´Hospitalet de Llobregat Spain Spanish 218Lázaro Cárdenas Mexico Spanish 219Lódz Poland Polish 220Lübeck Germany German 221Lünen Germany German 222set join_buffer_size=256; 223show variables like 'join_buffer_size'; 224Variable_name Value 225join_buffer_size 256 226EXPLAIN 227SELECT City.Name, Country.Name FROM City,Country 228WHERE City.Country=Country.Code AND 229Country.Name LIKE 'L%' AND City.Population > 100000; 230id select_type table type possible_keys key key_len ref rows Extra 2311 SIMPLE Country ALL NULL NULL NULL NULL 239 Using where 2321 SIMPLE City ALL NULL NULL NULL NULL 4079 Using where; Using join buffer (Block Nested Loop) 233SELECT City.Name, Country.Name FROM City,Country 234WHERE City.Country=Country.Code AND 235Country.Name LIKE 'L%' AND City.Population > 100000; 236Name Name 237?iauliai Lithuania 238Beirut Lebanon 239Bengasi Libyan Arab Jamahiriya 240Daugavpils Latvia 241Kaunas Lithuania 242Klaipeda Lithuania 243Maseru Lesotho 244Misrata Libyan Arab Jamahiriya 245Monrovia Liberia 246Panevezys Lithuania 247Riga Latvia 248Tripoli Lebanon 249Tripoli Libyan Arab Jamahiriya 250Vientiane Laos 251Vilnius Lithuania 252EXPLAIN 253SELECT City.Name, Country.Name, CountryLanguage.Language 254FROM City,Country,CountryLanguage 255WHERE City.Country=Country.Code AND 256CountryLanguage.Country=Country.Code AND 257City.Name LIKE 'L%' AND Country.Population > 3000000 AND 258CountryLanguage.Percentage > 50; 259id select_type table type possible_keys key key_len ref rows Extra 2601 SIMPLE Country ALL NULL NULL NULL NULL 239 Using where 2611 SIMPLE CountryLanguage ALL NULL NULL NULL NULL 984 Using where; Using join buffer (Block Nested Loop) 2621 SIMPLE City ALL NULL NULL NULL NULL 4079 Using where; Using join buffer (Block Nested Loop) 263SELECT City.Name, Country.Name, CountryLanguage.Language 264FROM City,Country,CountryLanguage 265WHERE City.Country=Country.Code AND 266CountryLanguage.Country=Country.Code AND 267City.Name LIKE 'L%' AND Country.Population > 3000000 AND 268CountryLanguage.Percentage > 50; 269Name Name Language 270La Ceiba Honduras Spanish 271La Habana Cuba Spanish 272La Matanza Argentina Spanish 273La Paz Bolivia Spanish 274La Paz Mexico Spanish 275La Paz Mexico Spanish 276La Plata Argentina Spanish 277La Rioja Argentina Spanish 278La Romana Dominican Republic Spanish 279La Serena Chile Spanish 280La Spezia Italy Italian 281Lafayette United States English 282Lages Brazil Portuguese 283Lagos de Moreno Mexico Spanish 284Lahti Finland Finnish 285Laiwu China Chinese 286Laiyang China Chinese 287Laizhou China Chinese 288Lakewood United States English 289Lalitapur Nepal Nepali 290Lambaré Paraguay Spanish 291Lancaster United States English 292Langfang China Chinese 293Lansing United States English 294Lanzhou China Chinese 295Lanús Argentina Spanish 296Laohekou China Chinese 297Laredo United States English 298Larisa Greece Greek 299Las Heras Argentina Spanish 300Las Margaritas Mexico Spanish 301Las Palmas de Gran Canaria Spain Spanish 302Las Vegas United States English 303Lashio (Lasho) Myanmar Burmese 304Latakia Syria Arabic 305Latina Italy Italian 306Lauro de Freitas Brazil Portuguese 307Lausanne Switzerland German 308Laval Canada English 309Le Havre France French 310Le Mans France French 311Le-Cap-Haïtien Haiti Haiti Creole 312Lecce Italy Italian 313Leeds United Kingdom English 314Leganés Spain Spanish 315Legnica Poland Polish 316Leicester United Kingdom English 317Leiden Netherlands Dutch 318Leipzig Germany German 319Leiyang China Chinese 320Lengshuijiang China Chinese 321Leninsk-Kuznetski Russian Federation Russian 322Lerdo Mexico Spanish 323Lerma Mexico Spanish 324Leshan China Chinese 325Leverkusen Germany German 326Lexington-Fayette United States English 327León Mexico Spanish 328León Nicaragua Spanish 329León Spain Spanish 330Lhasa China Chinese 331Liangcheng China Chinese 332Lianyuan China Chinese 333Lianyungang China Chinese 334Liaocheng China Chinese 335Liaoyang China Chinese 336Liaoyuan China Chinese 337Liberec Czech Republic Czech 338Lida Belarus Belorussian 339Liling China Chinese 340Lille France French 341Lilongwe Malawi Chichewa 342Lima Peru Spanish 343Limeira Brazil Portuguese 344Limoges France French 345Linchuan China Chinese 346Lincoln United States English 347Linfen China Chinese 348Linhai China Chinese 349Linhares Brazil Portuguese 350Linhe China Chinese 351Linköping Sweden Swedish 352Linqing China Chinese 353Linyi China Chinese 354Linz Austria German 355Lipetsk Russian Federation Russian 356Lisboa Portugal Portuguese 357Little Rock United States English 358Liupanshui China Chinese 359Liuzhou China Chinese 360Liu´an China Chinese 361Liverpool United Kingdom English 362Livonia United States English 363Livorno Italy Italian 364Liyang China Chinese 365Liège Belgium Dutch 366Ljubertsy Russian Federation Russian 367Lleida (Lérida) Spain Spanish 368Logroño Spain Spanish 369Loja Ecuador Spanish 370Lomas de Zamora Argentina Spanish 371London Canada English 372London United Kingdom English 373Londrina Brazil Portuguese 374Long Beach United States English 375Long Xuyen Vietnam Vietnamese 376Longjing China Chinese 377Longkou China Chinese 378Longueuil Canada English 379Longyan China Chinese 380Los Angeles Chile Spanish 381Los Angeles United States English 382Los Cabos Mexico Spanish 383Los Teques Venezuela Spanish 384Loudi China Chinese 385Louisville United States English 386Lowell United States English 387Lower Hutt New Zealand English 388Lubbock United States English 389Lublin Poland Polish 390Luchou Taiwan Min 391Ludwigshafen am Rhein Germany German 392Lugansk Ukraine Ukrainian 393Lund Sweden Swedish 394Lungtan Taiwan Min 395Luohe China Chinese 396Luoyang China Chinese 397Luton United Kingdom English 398Lutsk Ukraine Ukrainian 399Luxor Egypt Arabic 400Luzhou China Chinese 401Luziânia Brazil Portuguese 402Lviv Ukraine Ukrainian 403Lyon France French 404Lysyt?ansk Ukraine Ukrainian 405L´Hospitalet de Llobregat Spain Spanish 406Lázaro Cárdenas Mexico Spanish 407Lódz Poland Polish 408Lübeck Germany German 409Lünen Germany German 410set join_buffer_size=default; 411show variables like 'join_buffer_size'; 412Variable_name Value 413join_buffer_size 262144 414DROP DATABASE world; 415CREATE DATABASE world; 416use world; 417CREATE TABLE Country ( 418Code char(3) NOT NULL default '', 419Name char(52) NOT NULL default '', 420SurfaceArea float(10,2) NOT NULL default '0.00', 421Population int(11) NOT NULL default '0', 422Capital int(11) default NULL, 423PRIMARY KEY (Code), 424UNIQUE INDEX (Name) 425); 426CREATE TABLE City ( 427ID int(11) NOT NULL auto_increment, 428Name char(35) NOT NULL default '', 429Country char(3) NOT NULL default '', 430Population int(11) NOT NULL default '0', 431PRIMARY KEY (ID), 432INDEX (Population), 433INDEX (Country) 434); 435CREATE TABLE CountryLanguage ( 436Country char(3) NOT NULL default '', 437Language char(30) NOT NULL default '', 438Percentage float(3,1) NOT NULL default '0.0', 439PRIMARY KEY (Country, Language), 440INDEX (Percentage) 441); 442show variables like 'join_buffer_size'; 443Variable_name Value 444join_buffer_size 262144 445EXPLAIN 446SELECT City.Name, Country.Name FROM City,Country 447WHERE City.Country=Country.Code AND 448Country.Name LIKE 'L%' AND City.Population > 100000; 449id select_type table type possible_keys key key_len ref rows Extra 4501 SIMPLE Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR 4511 SIMPLE City ref Population,Country Country 3 world.Country.Code 18 Using where 452SELECT City.Name, Country.Name FROM City,Country 453WHERE City.Country=Country.Code AND 454Country.Name LIKE 'L%' AND City.Population > 100000; 455Name Name 456?iauliai Lithuania 457Beirut Lebanon 458Bengasi Libyan Arab Jamahiriya 459Daugavpils Latvia 460Kaunas Lithuania 461Klaipeda Lithuania 462Maseru Lesotho 463Misrata Libyan Arab Jamahiriya 464Monrovia Liberia 465Panevezys Lithuania 466Riga Latvia 467Tripoli Lebanon 468Tripoli Libyan Arab Jamahiriya 469Vientiane Laos 470Vilnius Lithuania 471EXPLAIN 472SELECT City.Name, Country.Name, CountryLanguage.Language 473FROM City,Country,CountryLanguage 474WHERE City.Country=Country.Code AND 475CountryLanguage.Country=Country.Code AND 476City.Name LIKE 'L%' AND Country.Population > 3000000 AND 477CountryLanguage.Percentage > 50; 478id select_type table type possible_keys key key_len ref rows Extra 4791 SIMPLE CountryLanguage range PRIMARY,Percentage Percentage 4 NULL # Using index condition; Using MRR 4801 SIMPLE Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where 4811 SIMPLE City ref Country Country 3 world.CountryLanguage.Country 18 Using where 482SELECT City.Name, Country.Name, CountryLanguage.Language 483FROM City,Country,CountryLanguage 484WHERE City.Country=Country.Code AND 485CountryLanguage.Country=Country.Code AND 486City.Name LIKE 'L%' AND Country.Population > 3000000 AND 487CountryLanguage.Percentage > 50; 488Name Name Language 489La Ceiba Honduras Spanish 490La Habana Cuba Spanish 491La Matanza Argentina Spanish 492La Paz Bolivia Spanish 493La Paz Mexico Spanish 494La Paz Mexico Spanish 495La Plata Argentina Spanish 496La Rioja Argentina Spanish 497La Romana Dominican Republic Spanish 498La Serena Chile Spanish 499La Spezia Italy Italian 500Lafayette United States English 501Lages Brazil Portuguese 502Lagos de Moreno Mexico Spanish 503Lahti Finland Finnish 504Laiwu China Chinese 505Laiyang China Chinese 506Laizhou China Chinese 507Lakewood United States English 508Lalitapur Nepal Nepali 509Lambaré Paraguay Spanish 510Lancaster United States English 511Langfang China Chinese 512Lansing United States English 513Lanzhou China Chinese 514Lanús Argentina Spanish 515Laohekou China Chinese 516Laredo United States English 517Larisa Greece Greek 518Las Heras Argentina Spanish 519Las Margaritas Mexico Spanish 520Las Palmas de Gran Canaria Spain Spanish 521Las Vegas United States English 522Lashio (Lasho) Myanmar Burmese 523Latakia Syria Arabic 524Latina Italy Italian 525Lauro de Freitas Brazil Portuguese 526Lausanne Switzerland German 527Laval Canada English 528Le Havre France French 529Le Mans France French 530Le-Cap-Haïtien Haiti Haiti Creole 531Lecce Italy Italian 532Leeds United Kingdom English 533Leganés Spain Spanish 534Legnica Poland Polish 535Leicester United Kingdom English 536Leiden Netherlands Dutch 537Leipzig Germany German 538Leiyang China Chinese 539Lengshuijiang China Chinese 540Leninsk-Kuznetski Russian Federation Russian 541Lerdo Mexico Spanish 542Lerma Mexico Spanish 543Leshan China Chinese 544Leverkusen Germany German 545Lexington-Fayette United States English 546León Mexico Spanish 547León Nicaragua Spanish 548León Spain Spanish 549Lhasa China Chinese 550Liangcheng China Chinese 551Lianyuan China Chinese 552Lianyungang China Chinese 553Liaocheng China Chinese 554Liaoyang China Chinese 555Liaoyuan China Chinese 556Liberec Czech Republic Czech 557Lida Belarus Belorussian 558Liling China Chinese 559Lille France French 560Lilongwe Malawi Chichewa 561Lima Peru Spanish 562Limeira Brazil Portuguese 563Limoges France French 564Linchuan China Chinese 565Lincoln United States English 566Linfen China Chinese 567Linhai China Chinese 568Linhares Brazil Portuguese 569Linhe China Chinese 570Linköping Sweden Swedish 571Linqing China Chinese 572Linyi China Chinese 573Linz Austria German 574Lipetsk Russian Federation Russian 575Lisboa Portugal Portuguese 576Little Rock United States English 577Liupanshui China Chinese 578Liuzhou China Chinese 579Liu´an China Chinese 580Liverpool United Kingdom English 581Livonia United States English 582Livorno Italy Italian 583Liyang China Chinese 584Liège Belgium Dutch 585Ljubertsy Russian Federation Russian 586Lleida (Lérida) Spain Spanish 587Logroño Spain Spanish 588Loja Ecuador Spanish 589Lomas de Zamora Argentina Spanish 590London Canada English 591London United Kingdom English 592Londrina Brazil Portuguese 593Long Beach United States English 594Long Xuyen Vietnam Vietnamese 595Longjing China Chinese 596Longkou China Chinese 597Longueuil Canada English 598Longyan China Chinese 599Los Angeles Chile Spanish 600Los Angeles United States English 601Los Cabos Mexico Spanish 602Los Teques Venezuela Spanish 603Loudi China Chinese 604Louisville United States English 605Lowell United States English 606Lower Hutt New Zealand English 607Lubbock United States English 608Lublin Poland Polish 609Luchou Taiwan Min 610Ludwigshafen am Rhein Germany German 611Lugansk Ukraine Ukrainian 612Lund Sweden Swedish 613Lungtan Taiwan Min 614Luohe China Chinese 615Luoyang China Chinese 616Luton United Kingdom English 617Lutsk Ukraine Ukrainian 618Luxor Egypt Arabic 619Luzhou China Chinese 620Luziânia Brazil Portuguese 621Lviv Ukraine Ukrainian 622Lyon France French 623Lysyt?ansk Ukraine Ukrainian 624L´Hospitalet de Llobregat Spain Spanish 625Lázaro Cárdenas Mexico Spanish 626Lódz Poland Polish 627Lübeck Germany German 628Lünen Germany German 629EXPLAIN 630SELECT Name FROM City 631WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND 632City.Population > 100000; 633id select_type table type possible_keys key key_len ref rows Extra 6341 SIMPLE Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR 6351 SIMPLE City ref Population,Country Country 3 world.Country.Code 18 Using where 636SELECT Name FROM City 637WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND 638City.Population > 100000; 639Name 640?iauliai 641Beirut 642Bengasi 643Daugavpils 644Kaunas 645Klaipeda 646Maseru 647Misrata 648Monrovia 649Panevezys 650Riga 651Tripoli 652Tripoli 653Vientiane 654Vilnius 655EXPLAIN 656SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) 657FROM Country LEFT JOIN CountryLanguage ON 658(CountryLanguage.Country=Country.Code AND Language='English') 659WHERE 660Country.Population > 10000000; 661id select_type table type possible_keys key key_len ref rows Extra 6621 SIMPLE Country ALL NULL NULL NULL NULL 239 Using where 6631 SIMPLE CountryLanguage eq_ref PRIMARY PRIMARY 33 world.Country.Code,const 1 Using where 664SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) 665FROM Country LEFT JOIN CountryLanguage ON 666(CountryLanguage.Country=Country.Code AND Language='English') 667WHERE 668Country.Population > 10000000; 669Name IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) 670Afghanistan NULL 671Algeria NULL 672Angola NULL 673Argentina NULL 674Australia 81.2 675Bangladesh NULL 676Belarus NULL 677Belgium NULL 678Brazil NULL 679Burkina Faso NULL 680Cambodia NULL 681Cameroon NULL 682Canada 60.4 683Chile NULL 684China NULL 685Colombia NULL 686Congo, The Democratic Republic of the NULL 687Cuba NULL 688Czech Republic NULL 689Côte d?Ivoire NULL 690Ecuador NULL 691Egypt NULL 692Ethiopia NULL 693France NULL 694Germany NULL 695Ghana NULL 696Greece NULL 697Guatemala NULL 698Hungary NULL 699India NULL 700Indonesia NULL 701Iran NULL 702Iraq NULL 703Italy NULL 704Japan 0.1 705Kazakstan NULL 706Kenya NULL 707Madagascar NULL 708Malawi NULL 709Malaysia 1.6 710Mali NULL 711Mexico NULL 712Morocco NULL 713Mozambique NULL 714Myanmar NULL 715Nepal NULL 716Netherlands NULL 717Niger NULL 718Nigeria NULL 719North Korea NULL 720Pakistan NULL 721Peru NULL 722Philippines NULL 723Poland NULL 724Romania NULL 725Russian Federation NULL 726Saudi Arabia NULL 727Somalia NULL 728South Africa 8.5 729South Korea NULL 730Spain NULL 731Sri Lanka NULL 732Sudan NULL 733Syria NULL 734Taiwan NULL 735Tanzania NULL 736Thailand NULL 737Turkey NULL 738Uganda NULL 739Ukraine NULL 740United Kingdom 97.3 741United States 86.2 742Uzbekistan NULL 743Venezuela NULL 744Vietnam NULL 745Yemen NULL 746Yugoslavia NULL 747Zimbabwe 2.2 748set join_buffer_size=256; 749show variables like 'join_buffer_size'; 750Variable_name Value 751join_buffer_size 256 752EXPLAIN 753SELECT City.Name, Country.Name FROM City,Country 754WHERE City.Country=Country.Code AND 755Country.Name LIKE 'L%' AND City.Population > 100000; 756id select_type table type possible_keys key key_len ref rows Extra 7571 SIMPLE Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR 7581 SIMPLE City ref Population,Country Country 3 world.Country.Code 18 Using where 759SELECT City.Name, Country.Name FROM City,Country 760WHERE City.Country=Country.Code AND 761Country.Name LIKE 'L%' AND City.Population > 100000; 762Name Name 763?iauliai Lithuania 764Beirut Lebanon 765Bengasi Libyan Arab Jamahiriya 766Daugavpils Latvia 767Kaunas Lithuania 768Klaipeda Lithuania 769Maseru Lesotho 770Misrata Libyan Arab Jamahiriya 771Monrovia Liberia 772Panevezys Lithuania 773Riga Latvia 774Tripoli Lebanon 775Tripoli Libyan Arab Jamahiriya 776Vientiane Laos 777Vilnius Lithuania 778EXPLAIN 779SELECT City.Name, Country.Name, CountryLanguage.Language 780FROM City,Country,CountryLanguage 781WHERE City.Country=Country.Code AND 782CountryLanguage.Country=Country.Code AND 783City.Name LIKE 'L%' AND Country.Population > 3000000 AND 784CountryLanguage.Percentage > 50; 785id select_type table type possible_keys key key_len ref rows Extra 7861 SIMPLE CountryLanguage range PRIMARY,Percentage Percentage 4 NULL # Using index condition; Using MRR 7871 SIMPLE Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where 7881 SIMPLE City ref Country Country 3 world.CountryLanguage.Country 18 Using where 789SELECT City.Name, Country.Name, CountryLanguage.Language 790FROM City,Country,CountryLanguage 791WHERE City.Country=Country.Code AND 792CountryLanguage.Country=Country.Code AND 793City.Name LIKE 'L%' AND Country.Population > 3000000 AND 794CountryLanguage.Percentage > 50; 795Name Name Language 796La Ceiba Honduras Spanish 797La Habana Cuba Spanish 798La Matanza Argentina Spanish 799La Paz Bolivia Spanish 800La Paz Mexico Spanish 801La Paz Mexico Spanish 802La Plata Argentina Spanish 803La Rioja Argentina Spanish 804La Romana Dominican Republic Spanish 805La Serena Chile Spanish 806La Spezia Italy Italian 807Lafayette United States English 808Lages Brazil Portuguese 809Lagos de Moreno Mexico Spanish 810Lahti Finland Finnish 811Laiwu China Chinese 812Laiyang China Chinese 813Laizhou China Chinese 814Lakewood United States English 815Lalitapur Nepal Nepali 816Lambaré Paraguay Spanish 817Lancaster United States English 818Langfang China Chinese 819Lansing United States English 820Lanzhou China Chinese 821Lanús Argentina Spanish 822Laohekou China Chinese 823Laredo United States English 824Larisa Greece Greek 825Las Heras Argentina Spanish 826Las Margaritas Mexico Spanish 827Las Palmas de Gran Canaria Spain Spanish 828Las Vegas United States English 829Lashio (Lasho) Myanmar Burmese 830Latakia Syria Arabic 831Latina Italy Italian 832Lauro de Freitas Brazil Portuguese 833Lausanne Switzerland German 834Laval Canada English 835Le Havre France French 836Le Mans France French 837Le-Cap-Haïtien Haiti Haiti Creole 838Lecce Italy Italian 839Leeds United Kingdom English 840Leganés Spain Spanish 841Legnica Poland Polish 842Leicester United Kingdom English 843Leiden Netherlands Dutch 844Leipzig Germany German 845Leiyang China Chinese 846Lengshuijiang China Chinese 847Leninsk-Kuznetski Russian Federation Russian 848Lerdo Mexico Spanish 849Lerma Mexico Spanish 850Leshan China Chinese 851Leverkusen Germany German 852Lexington-Fayette United States English 853León Mexico Spanish 854León Nicaragua Spanish 855León Spain Spanish 856Lhasa China Chinese 857Liangcheng China Chinese 858Lianyuan China Chinese 859Lianyungang China Chinese 860Liaocheng China Chinese 861Liaoyang China Chinese 862Liaoyuan China Chinese 863Liberec Czech Republic Czech 864Lida Belarus Belorussian 865Liling China Chinese 866Lille France French 867Lilongwe Malawi Chichewa 868Lima Peru Spanish 869Limeira Brazil Portuguese 870Limoges France French 871Linchuan China Chinese 872Lincoln United States English 873Linfen China Chinese 874Linhai China Chinese 875Linhares Brazil Portuguese 876Linhe China Chinese 877Linköping Sweden Swedish 878Linqing China Chinese 879Linyi China Chinese 880Linz Austria German 881Lipetsk Russian Federation Russian 882Lisboa Portugal Portuguese 883Little Rock United States English 884Liupanshui China Chinese 885Liuzhou China Chinese 886Liu´an China Chinese 887Liverpool United Kingdom English 888Livonia United States English 889Livorno Italy Italian 890Liyang China Chinese 891Liège Belgium Dutch 892Ljubertsy Russian Federation Russian 893Lleida (Lérida) Spain Spanish 894Logroño Spain Spanish 895Loja Ecuador Spanish 896Lomas de Zamora Argentina Spanish 897London Canada English 898London United Kingdom English 899Londrina Brazil Portuguese 900Long Beach United States English 901Long Xuyen Vietnam Vietnamese 902Longjing China Chinese 903Longkou China Chinese 904Longueuil Canada English 905Longyan China Chinese 906Los Angeles Chile Spanish 907Los Angeles United States English 908Los Cabos Mexico Spanish 909Los Teques Venezuela Spanish 910Loudi China Chinese 911Louisville United States English 912Lowell United States English 913Lower Hutt New Zealand English 914Lubbock United States English 915Lublin Poland Polish 916Luchou Taiwan Min 917Ludwigshafen am Rhein Germany German 918Lugansk Ukraine Ukrainian 919Lund Sweden Swedish 920Lungtan Taiwan Min 921Luohe China Chinese 922Luoyang China Chinese 923Luton United Kingdom English 924Lutsk Ukraine Ukrainian 925Luxor Egypt Arabic 926Luzhou China Chinese 927Luziânia Brazil Portuguese 928Lviv Ukraine Ukrainian 929Lyon France French 930Lysyt?ansk Ukraine Ukrainian 931L´Hospitalet de Llobregat Spain Spanish 932Lázaro Cárdenas Mexico Spanish 933Lódz Poland Polish 934Lübeck Germany German 935Lünen Germany German 936EXPLAIN 937SELECT Name FROM City 938WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND 939City.Population > 100000; 940id select_type table type possible_keys key key_len ref rows Extra 9411 SIMPLE Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR 9421 SIMPLE City ref Population,Country Country 3 world.Country.Code 18 Using where 943SELECT Name FROM City 944WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND 945City.Population > 100000; 946Name 947?iauliai 948Beirut 949Bengasi 950Daugavpils 951Kaunas 952Klaipeda 953Maseru 954Misrata 955Monrovia 956Panevezys 957Riga 958Tripoli 959Tripoli 960Vientiane 961Vilnius 962set join_buffer_size=default; 963show variables like 'join_buffer_size'; 964Variable_name Value 965join_buffer_size 262144 966SELECT City.Name, Country.Name FROM City,Country 967WHERE City.Country=Country.Code AND City.Population > 3000000; 968Name Name 969Alexandria Egypt 970Ankara Turkey 971Baghdad Iraq 972Bangkok Thailand 973Berlin Germany 974Cairo Egypt 975Calcutta [Kolkata] India 976Chengdu China 977Chennai (Madras) India 978Chongqing China 979Ciudad de México Mexico 980Delhi India 981Dhaka Bangladesh 982Harbin China 983Ho Chi Minh City Vietnam 984Istanbul Turkey 985Jakarta Indonesia 986Jokohama [Yokohama] Japan 987Kanton [Guangzhou] China 988Karachi Pakistan 989Kinshasa Congo, The Democratic Republic of the 990Lahore Pakistan 991Lima Peru 992London United Kingdom 993Los Angeles United States 994Moscow Russian Federation 995Mumbai (Bombay) India 996New York United States 997Peking China 998Pusan South Korea 999Rangoon (Yangon) Myanmar 1000Rio de Janeiro Brazil 1001Riyadh Saudi Arabia 1002Santafé de Bogotá Colombia 1003Santiago de Chile Chile 1004Seoul South Korea 1005Shanghai China 1006Shenyang China 1007Singapore Singapore 1008St Petersburg Russian Federation 1009Sydney Australia 1010São Paulo Brazil 1011Teheran Iran 1012Tianjin China 1013Tokyo Japan 1014Wuhan China 1015set join_buffer_size=256; 1016EXPLAIN 1017SELECT City.Name, Country.Name FROM City,Country 1018WHERE City.Country=Country.Code AND City.Population > 3000000; 1019id select_type table type possible_keys key key_len ref rows Extra 10201 SIMPLE City range Population,Country Population 4 NULL # Using index condition; Using MRR 10211 SIMPLE Country eq_ref PRIMARY PRIMARY 3 world.City.Country # NULL 1022SELECT City.Name, Country.Name FROM City,Country 1023WHERE City.Country=Country.Code AND City.Population > 3000000; 1024Name Name 1025Alexandria Egypt 1026Ankara Turkey 1027Baghdad Iraq 1028Bangkok Thailand 1029Berlin Germany 1030Cairo Egypt 1031Calcutta [Kolkata] India 1032Chengdu China 1033Chennai (Madras) India 1034Chongqing China 1035Ciudad de México Mexico 1036Delhi India 1037Dhaka Bangladesh 1038Harbin China 1039Ho Chi Minh City Vietnam 1040Istanbul Turkey 1041Jakarta Indonesia 1042Jokohama [Yokohama] Japan 1043Kanton [Guangzhou] China 1044Karachi Pakistan 1045Kinshasa Congo, The Democratic Republic of the 1046Lahore Pakistan 1047Lima Peru 1048London United Kingdom 1049Los Angeles United States 1050Moscow Russian Federation 1051Mumbai (Bombay) India 1052New York United States 1053Peking China 1054Pusan South Korea 1055Rangoon (Yangon) Myanmar 1056Rio de Janeiro Brazil 1057Riyadh Saudi Arabia 1058Santafé de Bogotá Colombia 1059Santiago de Chile Chile 1060Seoul South Korea 1061Shanghai China 1062Shenyang China 1063Singapore Singapore 1064St Petersburg Russian Federation 1065Sydney Australia 1066São Paulo Brazil 1067Teheran Iran 1068Tianjin China 1069Tokyo Japan 1070Wuhan China 1071set join_buffer_size=default; 1072ALTER TABLE Country MODIFY Name varchar(52) NOT NULL default ''; 1073SELECT City.Name, Country.Name FROM City,Country 1074WHERE City.Country=Country.Code AND 1075Country.Name LIKE 'L%' AND City.Population > 100000; 1076Name Name 1077?iauliai Lithuania 1078Beirut Lebanon 1079Bengasi Libyan Arab Jamahiriya 1080Daugavpils Latvia 1081Kaunas Lithuania 1082Klaipeda Lithuania 1083Maseru Lesotho 1084Misrata Libyan Arab Jamahiriya 1085Monrovia Liberia 1086Panevezys Lithuania 1087Riga Latvia 1088Tripoli Lebanon 1089Tripoli Libyan Arab Jamahiriya 1090Vientiane Laos 1091Vilnius Lithuania 1092ALTER TABLE Country MODIFY Name varchar(300) NOT NULL default ''; 1093SELECT City.Name, Country.Name FROM City,Country 1094WHERE City.Country=Country.Code AND 1095Country.Name LIKE 'L%' AND City.Population > 100000; 1096Name Name 1097?iauliai Lithuania 1098Beirut Lebanon 1099Bengasi Libyan Arab Jamahiriya 1100Daugavpils Latvia 1101Kaunas Lithuania 1102Klaipeda Lithuania 1103Maseru Lesotho 1104Misrata Libyan Arab Jamahiriya 1105Monrovia Liberia 1106Panevezys Lithuania 1107Riga Latvia 1108Tripoli Lebanon 1109Tripoli Libyan Arab Jamahiriya 1110Vientiane Laos 1111Vilnius Lithuania 1112ALTER TABLE Country ADD COLUMN PopulationBar text; 1113UPDATE Country 1114SET PopulationBar=REPEAT('x', CAST(Population/100000 AS unsigned int)); 1115SELECT City.Name, Country.Name, Country.PopulationBar FROM City,Country 1116WHERE City.Country=Country.Code AND 1117Country.Name LIKE 'L%' AND City.Population > 100000; 1118Name Name PopulationBar 1119?iauliai Lithuania xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1120Beirut Lebanon xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1121Bengasi Libyan Arab Jamahiriya xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1122Daugavpils Latvia xxxxxxxxxxxxxxxxxxxxxxxx 1123Kaunas Lithuania xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1124Klaipeda Lithuania xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1125Maseru Lesotho xxxxxxxxxxxxxxxxxxxxxx 1126Misrata Libyan Arab Jamahiriya xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1127Monrovia Liberia xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1128Panevezys Lithuania xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1129Riga Latvia xxxxxxxxxxxxxxxxxxxxxxxx 1130Tripoli Lebanon xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1131Tripoli Libyan Arab Jamahiriya xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1132Vientiane Laos xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1133Vilnius Lithuania xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1134set join_buffer_size=256; 1135SELECT City.Name, Country.Name, Country.PopulationBar FROM City,Country 1136WHERE City.Country=Country.Code AND 1137Country.Name LIKE 'L%' AND City.Population > 100000; 1138Name Name PopulationBar 1139?iauliai Lithuania xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1140Beirut Lebanon xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1141Bengasi Libyan Arab Jamahiriya xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1142Daugavpils Latvia xxxxxxxxxxxxxxxxxxxxxxxx 1143Kaunas Lithuania xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1144Klaipeda Lithuania xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1145Maseru Lesotho xxxxxxxxxxxxxxxxxxxxxx 1146Misrata Libyan Arab Jamahiriya xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1147Monrovia Liberia xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1148Panevezys Lithuania xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1149Riga Latvia xxxxxxxxxxxxxxxxxxxxxxxx 1150Tripoli Lebanon xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1151Tripoli Libyan Arab Jamahiriya xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1152Vientiane Laos xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1153Vilnius Lithuania xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1154set join_buffer_size=default; 1155DROP DATABASE world; 1156use test; 1157CREATE TABLE t1( 1158affiliatetometaid int NOT NULL default '0', 1159uniquekey int NOT NULL default '0', 1160metaid int NOT NULL default '0', 1161affiliateid int NOT NULL default '0', 1162xml text, 1163isactive char(1) NOT NULL default 'Y', 1164PRIMARY KEY (affiliatetometaid) 1165); 1166CREATE UNIQUE INDEX t1_uniquekey ON t1(uniquekey); 1167CREATE INDEX t1_affiliateid ON t1(affiliateid); 1168CREATE INDEX t1_metaid on t1 (metaid); 1169INSERT INTO t1 VALUES 1170(1616, 1571693233, 1391, 2, NULL, 'Y'), (1943, 1993216749, 1726, 2, NULL, 'Y'); 1171CREATE TABLE t2( 1172metaid int NOT NULL default '0', 1173name varchar(80) NOT NULL default '', 1174dateadded timestamp NOT NULL , 1175xml text, 1176status int default NULL, 1177origin int default NULL, 1178gid int NOT NULL default '1', 1179formattypeid int default NULL, 1180PRIMARY KEY (metaid) 1181); 1182CREATE INDEX t2_status ON t2(status); 1183CREATE INDEX t2_gid ON t2(gid); 1184CREATE INDEX t2_formattypeid ON t2(formattypeid); 1185INSERT INTO t2 VALUES 1186(1391, "I Just Died", "2003-10-02 10:07:37", "", 1, NULL, 3, NULL), 1187(1726, "Me, Myself & I", "2003-12-05 11:24:36", " ", 1, NULL, 3, NULL); 1188CREATE TABLE t3( 1189mediaid int NOT NULL , 1190metaid int NOT NULL default '0', 1191formatid int NOT NULL default '0', 1192status int default NULL, 1193path varchar(100) NOT NULL default '', 1194datemodified timestamp NOT NULL , 1195resourcetype int NOT NULL default '1', 1196parameters text, 1197signature int default NULL, 1198quality int NOT NULL default '255', 1199PRIMARY KEY (mediaid) 1200); 1201CREATE INDEX t3_metaid ON t3(metaid); 1202CREATE INDEX t3_formatid ON t3(formatid); 1203CREATE INDEX t3_status ON t3(status); 1204CREATE INDEX t3_metaidformatid ON t3(metaid,formatid); 1205CREATE INDEX t3_signature ON t3(signature); 1206CREATE INDEX t3_quality ON t3(quality); 1207INSERT INTO t3 VALUES 1208(6, 4, 8, 0, "010101_anastacia_spmidi.mid", "2004-03-16 13:40:00", 1, NULL, NULL, 255), 1209(3343, 3, 8, 1, "010102_4VN4bsPwnxRQUJW5Zp1RhG2IL9vvl_8.mid", "2004-03-16 13:40:00", 1, NULL, NULL, 255); 1210CREATE TABLE t4( 1211formatid int NOT NULL , 1212name varchar(60) NOT NULL default '', 1213formatclassid int NOT NULL default '0', 1214mime varchar(60) default NULL, 1215extension varchar(10) default NULL, 1216priority int NOT NULL default '0', 1217canaddtocapability char(1) NOT NULL default 'Y', 1218PRIMARY KEY (formatid) 1219); 1220CREATE INDEX t4_formatclassid ON t4(formatclassid); 1221CREATE INDEX t4_formats_idx ON t4(canaddtocapability); 1222INSERT INTO t4 VALUES 1223(19, "XHTML", 11, "text/html", "xhtml", 10, 'Y'), 1224(54, "AMR (wide band)", 13, "audio/amr-wb", "awb", 0, 'Y'); 1225CREATE TABLE t5( 1226formatclassid int NOT NULL , 1227name varchar(60) NOT NULL default '', 1228priority int NOT NULL default '0', 1229formattypeid int NOT NULL default '0', 1230PRIMARY KEY (formatclassid) 1231); 1232CREATE INDEX t5_formattypeid on t5(formattypeid); 1233INSERT INTO t5 VALUES 1234(11, "Info", 0, 4), (13, "Digital Audio", 0, 2); 1235CREATE TABLE t6( 1236formattypeid int NOT NULL , 1237name varchar(60) NOT NULL default '', 1238priority int default NULL, 1239PRIMARY KEY (formattypeid) 1240); 1241INSERT INTO t6 VALUES 1242(2, "Ringtones", 0); 1243CREATE TABLE t7( 1244metaid int NOT NULL default '0', 1245artistid int NOT NULL default '0', 1246PRIMARY KEY (metaid,artistid) 1247); 1248INSERT INTO t7 VALUES 1249(4, 5), (3, 4); 1250CREATE TABLE t8( 1251artistid int NOT NULL , 1252name varchar(80) NOT NULL default '', 1253PRIMARY KEY (artistid) 1254); 1255INSERT INTO t8 VALUES 1256(5, "Anastacia"), (4, "John Mayer"); 1257CREATE TABLE t9( 1258subgenreid int NOT NULL default '0', 1259metaid int NOT NULL default '0', 1260PRIMARY KEY (subgenreid,metaid) 1261) ; 1262CREATE INDEX t9_subgenreid ON t9(subgenreid); 1263CREATE INDEX t9_metaid ON t9(metaid); 1264INSERT INTO t9 VALUES 1265(138, 4), (31, 3); 1266CREATE TABLE t10( 1267subgenreid int NOT NULL , 1268genreid int NOT NULL default '0', 1269name varchar(80) NOT NULL default '', 1270PRIMARY KEY (subgenreid) 1271) ; 1272CREATE INDEX t10_genreid ON t10(genreid); 1273INSERT INTO t10 VALUES 1274(138, 19, ''), (31, 3, ''); 1275CREATE TABLE t11( 1276genreid int NOT NULL default '0', 1277name char(80) NOT NULL default '', 1278priority int NOT NULL default '0', 1279masterclip char(1) default NULL, 1280PRIMARY KEY (genreid) 1281) ; 1282CREATE INDEX t11_masterclip ON t11( masterclip); 1283INSERT INTO t11 VALUES 1284(19, "Pop & Dance", 95, 'Y'), (3, "Rock & Alternative", 100, 'Y'); 1285EXPLAIN 1286SELECT t1.uniquekey, t1.xml AS affiliateXml, 1287t8.name AS artistName, t8.artistid, 1288t11.name AS genreName, t11.genreid, t11.priority AS genrePriority, 1289t10.subgenreid, t10.name AS subgenreName, 1290t2.name AS metaName, t2.metaid, t2.xml AS metaXml, 1291t4.priority + t5.priority + t6.priority AS overallPriority, 1292t3.path AS path, t3.mediaid, 1293t4.formatid, t4.name AS formatName, 1294t5.formatclassid, t5.name AS formatclassName, 1295t6.formattypeid, t6.name AS formattypeName 1296FROM t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11 1297WHERE t7.metaid = t2.metaid AND t7.artistid = t8.artistid AND 1298t9.metaid = t2.metaid AND t9.subgenreid = t10.subgenreid AND 1299t10.genreid = t11.genreid AND t3.metaid = t2.metaid AND 1300t3.formatid = t4.formatid AND t4.formatclassid = t5.formatclassid AND 1301t4.canaddtocapability = 'Y' AND t5.formattypeid = t6.formattypeid AND 1302t6.formattypeid IN (2) AND (t3.formatid IN (31, 8, 76)) AND 1303t1.metaid = t2.metaid AND t1.affiliateid = '2'; 1304id select_type table type possible_keys key key_len ref rows Extra 13051 SIMPLE t6 system PRIMARY NULL NULL NULL 1 NULL 13061 SIMPLE t1 ref t1_affiliateid,t1_metaid t1_affiliateid 4 const 1 NULL 13071 SIMPLE t4 ref PRIMARY,t4_formatclassid,t4_formats_idx t4_formats_idx 1 const 1 Using index condition; Using where 13081 SIMPLE t5 eq_ref PRIMARY,t5_formattypeid PRIMARY 4 test.t4.formatclassid 1 Using where 13091 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.metaid 1 NULL 13101 SIMPLE t7 ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using index 13111 SIMPLE t8 eq_ref PRIMARY PRIMARY 4 test.t7.artistid 1 NULL 13121 SIMPLE t3 ref t3_metaid,t3_formatid,t3_metaidformatid t3_metaid 4 test.t1.metaid 2 Using where 13131 SIMPLE t9 index PRIMARY,t9_subgenreid,t9_metaid PRIMARY 8 NULL 2 Using where; Using index; Using join buffer (Block Nested Loop) 13141 SIMPLE t10 eq_ref PRIMARY,t10_genreid PRIMARY 4 test.t9.subgenreid 1 NULL 13151 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t10.genreid 1 NULL 1316SELECT t1.uniquekey, t1.xml AS affiliateXml, 1317t8.name AS artistName, t8.artistid, 1318t11.name AS genreName, t11.genreid, t11.priority AS genrePriority, 1319t10.subgenreid, t10.name AS subgenreName, 1320t2.name AS metaName, t2.metaid, t2.xml AS metaXml, 1321t4.priority + t5.priority + t6.priority AS overallPriority, 1322t3.path AS path, t3.mediaid, 1323t4.formatid, t4.name AS formatName, 1324t5.formatclassid, t5.name AS formatclassName, 1325t6.formattypeid, t6.name AS formattypeName 1326FROM t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11 1327WHERE t7.metaid = t2.metaid AND t7.artistid = t8.artistid AND 1328t9.metaid = t2.metaid AND t9.subgenreid = t10.subgenreid AND 1329t10.genreid = t11.genreid AND t3.metaid = t2.metaid AND 1330t3.formatid = t4.formatid AND t4.formatclassid = t5.formatclassid AND 1331t4.canaddtocapability = 'Y' AND t5.formattypeid = t6.formattypeid AND 1332t6.formattypeid IN (2) AND (t3.formatid IN (31, 8, 76)) AND 1333t1.metaid = t2.metaid AND t1.affiliateid = '2'; 1334uniquekey affiliateXml artistName artistid genreName genreid genrePriority subgenreid subgenreName metaName metaid metaXml overallPriority path mediaid formatid formatName formatclassid formatclassName formattypeid formattypeName 1335DROP TABLE t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11; 1336CREATE TABLE t1 (a1 int, filler1 char(64) default ' ' ); 1337CREATE TABLE t2 ( 1338a2 int, b2 int, filler2 char(64) default ' ', 1339PRIMARY KEY idx(a2,b2,filler2) 1340) ; 1341CREATE TABLE t3 (b3 int, c3 int, INDEX idx(b3)); 1342INSERT INTO t1(a1) VALUES 1343(4), (7), (1), (9), (8), (5), (3), (6), (2); 1344INSERT INTO t2(a2,b2) VALUES 1345(1,30), (3,40), (2,61), (6,73), (8,92), (9,27), (4,18), (5,84), (7,56), 1346(4,14), (6,76), (8,98), (7,55), (1,39), (2,68), (3,45), (9,21), (5,81), 1347(5,88), (2,65), (6,74), (9,23), (1,37), (3,44), (4,17), (8,99), (7,51), 1348(9,28), (7,52), (1,33), (4,13), (5,87), (3,43), (8,91), (2,62), (6,79), 1349(3,49), (8,93), (7,34), (5,82), (6,78), (2,63), (1,32), (9,22), (4,11); 1350INSERT INTO t3 VALUES 1351(30,302), (92,923), (18,187), (45,459), (30,309), 1352(39,393), (68,685), (45,458), (21,210), (81,817), 1353(40,405), (61,618), (73,738), (92,929), (27,275), 1354(18,188), (84,846), (56,564), (14,144), (76,763), 1355(98,982), (55,551), (17,174), (99,998), (51,513), 1356(28,282), (52,527), (33,336), (13,138), (87,878), 1357(43,431), (91,916), (62,624), (79,797), (49,494), 1358(93,933), (34,347), (82,829), (78,780), (63,634), 1359(32,329), (22,228), (11,114), (74,749), (23,236); 1360EXPLAIN 1361SELECT a1<>a2, a1, a2, b2, b3, c3, 1362SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2 1363FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7; 1364id select_type table type possible_keys key key_len ref rows Extra 13651 SIMPLE t1 ALL NULL NULL NULL NULL 9 Using where 13661 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a1 1 Using index 13671 SIMPLE t3 ref idx idx 5 test.t2.b2 5 Using where 1368SELECT a1<>a2, a1, a2, b2, b3, c3, 1369SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2 1370FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7; 1371a1<>a2 a1 a2 b2 b3 c3 s1 s2 13720 1 1 30 30 309 13730 1 1 32 32 329 13740 2 2 61 61 618 13750 3 3 45 45 458 13760 3 3 45 45 459 13770 4 4 13 13 138 13780 4 4 18 18 188 13790 5 5 82 82 829 13800 5 5 87 87 878 13810 6 6 73 73 738 13820 6 6 74 74 749 13830 8 8 92 92 929 13840 8 8 99 99 998 13850 9 9 22 22 228 1386set join_buffer_size=512; 1387EXPLAIN 1388SELECT a1<>a2, a1, a2, b2, b3, c3, 1389SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2 1390FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7; 1391id select_type table type possible_keys key key_len ref rows Extra 13921 SIMPLE t1 ALL NULL NULL NULL NULL 9 Using where 13931 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a1 1 Using index 13941 SIMPLE t3 ref idx idx 5 test.t2.b2 5 Using where 1395SELECT a1<>a2, a1, a2, b2, b3, c3, 1396SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2 1397FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7; 1398a1<>a2 a1 a2 b2 b3 c3 s1 s2 13990 1 1 30 30 309 14000 1 1 32 32 329 14010 2 2 61 61 618 14020 3 3 45 45 458 14030 3 3 45 45 459 14040 4 4 13 13 138 14050 4 4 18 18 188 14060 5 5 82 82 829 14070 5 5 87 87 878 14080 6 6 73 73 738 14090 6 6 74 74 749 14100 8 8 92 92 929 14110 8 8 99 99 998 14120 9 9 22 22 228 1413DROP TABLE t1,t2,t3; 1414CREATE TABLE t1 (a int, b int, INDEX idx(b)); 1415CREATE TABLE t2 (a int, b int, INDEX idx(a)); 1416INSERT INTO t1 VALUES (5,30), (3,20), (7,40), (2,10), (8,30), (1,10), (4,20); 1417INSERT INTO t2 VALUES (7,10), (1,20), (2,20), (8,20), (8,10), (1,20); 1418INSERT INTO t2 VALUES (1,10), (4,20), (3,20), (7,20), (7,10), (1,20); 1419set join_buffer_size=32; 1420Warnings: 1421Warning 1292 Truncated incorrect join_buffer_size value: '32' 1422EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b >= 30; 1423id select_type table type possible_keys key key_len ref rows Extra 14241 SIMPLE t1 range idx idx 5 NULL 4 Using index condition; Using where; Using MRR 14251 SIMPLE t2 ref idx idx 5 test.t1.a 2 NULL 1426SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b >= 30; 1427a b a b 14287 40 7 10 14297 40 7 10 14307 40 7 20 14318 30 8 10 14328 30 8 20 1433DROP TABLE t1,t2; 1434 1435BUG#40136: Group by is ignored when join buffer is used for an outer join 1436 1437create table t1(a int PRIMARY KEY, b int); 1438insert into t1 values 1439(5, 10), (2, 70), (7, 80), (6, 20), (1, 50), (9, 40), (8, 30), (3, 60); 1440create table t2 (p int, a int, INDEX i_a(a)); 1441insert into t2 values 1442(103, 7), (109, 3), (102, 3), (108, 1), (106, 3), 1443(107, 7), (105, 1), (101, 3), (100, 7), (110, 1); 1444explain 1445select t1.a, count(t2.p) as count 1446from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a; 1447id select_type table type possible_keys key key_len ref rows Extra 14481 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 8 Using index; Using temporary; Using filesort 14491 SIMPLE t2 ALL i_a NULL NULL NULL 10 Using where; Using join buffer (Block Nested Loop) 1450select t1.a, count(t2.p) as count 1451from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a; 1452a count 14531 1 14542 0 14553 2 14565 0 14576 0 14587 2 14598 0 14609 0 1461drop table t1, t2; 1462# 1463# Bug #40134: outer join with not exists optimization and join buffer 1464# 1465set join_buffer_size=default; 1466CREATE TABLE t1 (a int NOT NULL); 1467INSERT INTO t1 VALUES (2), (4), (3), (5), (1); 1468CREATE TABLE t2 (a int NOT NULL, b int NOT NULL, INDEX i_a(a)); 1469INSERT INTO t2 VALUES (4,10), (2,10), (2,30), (2,20), (4,20); 1470EXPLAIN 1471SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL; 1472id select_type table type possible_keys key key_len ref rows Extra 14731 SIMPLE t1 ALL NULL NULL NULL NULL 5 NULL 14741 SIMPLE t2 ALL i_a NULL NULL NULL 5 Using where; Not exists; Using join buffer (Block Nested Loop) 1475SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL; 1476a a b 14773 NULL NULL 14785 NULL NULL 14791 NULL NULL 1480DROP TABLE t1, t2; 1481# 1482# BUG#40268: Nested outer join with not null-rejecting where condition 1483# over an inner table which is not the last in the nest 1484# 1485CREATE TABLE t2 (a int, b int, c int); 1486CREATE TABLE t3 (a int, b int, c int); 1487CREATE TABLE t4 (a int, b int, c int); 1488INSERT INTO t2 VALUES (3,3,0), (4,2,0), (5,3,0); 1489INSERT INTO t3 VALUES (1,2,0), (2,2,0); 1490INSERT INTO t4 VALUES (3,2,0), (4,2,0); 1491SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b 1492FROM t2 LEFT JOIN (t3, t4) ON t2.b=t4.b 1493WHERE t3.a+2<t2.a OR t3.c IS NULL; 1494a b a b a b 14953 3 NULL NULL NULL NULL 14964 2 1 2 3 2 14974 2 1 2 4 2 14985 3 NULL NULL NULL NULL 1499DROP TABLE t2, t3, t4; 1500# 1501# Bug #40192: outer join with where clause when using BNL 1502# 1503create table t1 (a int, b int); 1504insert into t1 values (2, 20), (3, 30), (1, 10); 1505create table t2 (a int, c int); 1506insert into t2 values (1, 101), (3, 102), (1, 100); 1507select * from t1 left join t2 on t1.a=t2.a; 1508a b a c 15091 10 1 100 15101 10 1 101 15112 20 NULL NULL 15123 30 3 102 1513explain select * from t1 left join t2 on t1.a=t2.a where t2.c=102 or t2.c is null; 1514id select_type table type possible_keys key key_len ref rows Extra 15151 SIMPLE t1 ALL NULL NULL NULL NULL 3 NULL 15161 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (Block Nested Loop) 1517select * from t1 left join t2 on t1.a=t2.a where t2.c=102 or t2.c is null; 1518a b a c 15192 20 NULL NULL 15203 30 3 102 1521drop table t1, t2; 1522# 1523# Bug #40317: outer join with with constant on expression equal to FALSE 1524# 1525create table t1 (a int); 1526insert into t1 values (30), (40), (20); 1527create table t2 (b int); 1528insert into t2 values (200), (100); 1529select * from t1 left join t2 on (1=0); 1530a b 153130 NULL 153240 NULL 153320 NULL 1534explain select * from t1 left join t2 on (1=0) where a=40; 1535id select_type table type possible_keys key key_len ref rows Extra 15361 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where 15371 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop) 1538select * from t1 left join t2 on (1=0) where a=40; 1539a b 154040 NULL 1541drop table t1, t2; 1542# 1543# Bug #41204: small buffer with big rec_per_key for ref access 1544# 1545CREATE TABLE t1 (a int); 1546INSERT INTO t1 VALUES (0); 1547INSERT INTO t1(a) SELECT a FROM t1; 1548INSERT INTO t1(a) SELECT a FROM t1; 1549INSERT INTO t1(a) SELECT a FROM t1; 1550INSERT INTO t1(a) SELECT a FROM t1; 1551INSERT INTO t1(a) SELECT a FROM t1; 1552INSERT INTO t1(a) SELECT a FROM t1; 1553INSERT INTO t1(a) SELECT a FROM t1; 1554INSERT INTO t1(a) SELECT a FROM t1; 1555INSERT INTO t1(a) SELECT a FROM t1; 1556INSERT INTO t1(a) SELECT a FROM t1; 1557INSERT INTO t1(a) SELECT a FROM t1; 1558INSERT INTO t1 VALUES (20000), (10000); 1559CREATE TABLE t2 (pk int AUTO_INCREMENT PRIMARY KEY, b int, c int, INDEX idx(b)); 1560INSERT INTO t2(b,c) VALUES (10000, 3), (20000, 7), (20000, 1), (10000, 9), (20000, 5); 1561INSERT INTO t2(b,c) SELECT b,c FROM t2; 1562INSERT INTO t2(b,c) SELECT b,c FROM t2; 1563INSERT INTO t2(b,c) SELECT b,c FROM t2; 1564INSERT INTO t2(b,c) SELECT b,c FROM t2; 1565INSERT INTO t2(b,c) SELECT b,c FROM t2; 1566INSERT INTO t2(b,c) SELECT b,c FROM t2; 1567INSERT INTO t2(b,c) SELECT b,c FROM t2; 1568INSERT INTO t2(b,c) SELECT b,c FROM t2; 1569ANALYZE TABLE t1,t2; 1570set join_buffer_size=1024; 1571EXPLAIN SELECT AVG(c) FROM t1,t2 WHERE t1.a=t2.b; 1572id select_type table type possible_keys key key_len ref rows Extra 15731 SIMPLE t1 ALL NULL NULL NULL NULL 2050 Using where 15741 SIMPLE t2 ref idx idx 5 test.t1.a 640 NULL 1575SELECT AVG(c) FROM t1,t2 WHERE t1.a=t2.b; 1576AVG(c) 15775.0000 1578set join_buffer_size=default; 1579DROP TABLE t1, t2; 1580# 1581# Bug #41894: big join buffer of level 7 used to join records 1582# with null values in place of varchar strings 1583# 1584CREATE TABLE t1 (a int NOT NULL AUTO_INCREMENT PRIMARY KEY, 1585b varchar(127) DEFAULT NULL); 1586INSERT INTO t1(a) VALUES (1); 1587INSERT INTO t1(b) SELECT b FROM t1; 1588INSERT INTO t1(b) SELECT b FROM t1; 1589INSERT INTO t1(b) SELECT b FROM t1; 1590INSERT INTO t1(b) SELECT b FROM t1; 1591INSERT INTO t1(b) SELECT b FROM t1; 1592INSERT INTO t1(b) SELECT b FROM t1; 1593INSERT INTO t1(b) SELECT b FROM t1; 1594INSERT INTO t1(b) SELECT b FROM t1; 1595INSERT INTO t1(b) SELECT b FROM t1; 1596INSERT INTO t1(b) SELECT b FROM t1; 1597INSERT INTO t1(b) SELECT b FROM t1; 1598INSERT INTO t1(b) SELECT b FROM t1; 1599INSERT INTO t1(b) SELECT b FROM t1; 1600INSERT INTO t1(b) SELECT b FROM t1; 1601CREATE TABLE t2 (a int NOT NULL PRIMARY KEY, b varchar(127) DEFAULT NULL); 1602INSERT INTO t2 SELECT * FROM t1; 1603CREATE TABLE t3 (a int NOT NULL PRIMARY KEY, b varchar(127) DEFAULT NULL); 1604INSERT INTO t3 SELECT * FROM t1; 1605set join_buffer_size=1024*1024; 1606EXPLAIN 1607SELECT COUNT(*) FROM t1,t2,t3 1608WHERE t1.a=t2.a AND t2.a=t3.a AND 1609t1.b IS NULL AND t2.b IS NULL AND t3.b IS NULL; 1610id select_type table type possible_keys key key_len ref rows Extra 16111 SIMPLE t1 ALL PRIMARY NULL NULL NULL 16384 Using where 16121 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where 16131 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where 1614SELECT COUNT(*) FROM t1,t2,t3 1615WHERE t1.a=t2.a AND t2.a=t3.a AND 1616t1.b IS NULL AND t2.b IS NULL AND t3.b IS NULL; 1617COUNT(*) 161816384 1619set join_buffer_size=default; 1620DROP TABLE t1,t2,t3; 1621# 1622# Bug #42020: join buffer is used for outer join with fields of 1623# several outer tables in join buffer 1624# 1625CREATE TABLE t1 ( 1626a bigint NOT NULL, 1627PRIMARY KEY (a) 1628); 1629INSERT INTO t1 VALUES 1630(2), (1); 1631CREATE TABLE t2 ( 1632a bigint NOT NULL, 1633b bigint NOT NULL, 1634PRIMARY KEY (a,b) 1635); 1636INSERT INTO t2 VALUES 1637(2,30), (2,40), (2,50), (2,60), (2,70), (2,80), 1638(1,10), (1, 20), (1,30), (1,40), (1,50); 1639CREATE TABLE t3 ( 1640pk bigint NOT NULL AUTO_INCREMENT, 1641a bigint NOT NULL, 1642b bigint NOT NULL, 1643val bigint DEFAULT '0', 1644PRIMARY KEY (pk), 1645KEY idx (a,b) 1646); 1647INSERT INTO t3(a,b) VALUES 1648(2,30), (2,40), (2,50), (2,60), (2,70), (2,80), 1649(4,30), (4,40), (4,50), (4,60), (4,70), (4,80), 1650(5,30), (5,40), (5,50), (5,60), (5,70), (5,80), 1651(7,30), (7,40), (7,50), (7,60), (7,70), (7,80); 1652SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val 1653FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) 1654WHERE t1.a=t2.a; 1655a a a b b val 16561 1 NULL 10 NULL NULL 16571 1 NULL 20 NULL NULL 16581 1 NULL 30 NULL NULL 16591 1 NULL 40 NULL NULL 16601 1 NULL 50 NULL NULL 16612 2 2 30 30 0 16622 2 2 40 40 0 16632 2 2 50 50 0 16642 2 2 60 60 0 16652 2 2 70 70 0 16662 2 2 80 80 0 1667set join_buffer_size=256; 1668EXPLAIN 1669SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val 1670FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) 1671WHERE t1.a=t2.a; 1672id select_type table type possible_keys key key_len ref rows Extra 16731 SIMPLE t1 index PRIMARY PRIMARY 8 NULL 2 Using index 16741 SIMPLE t2 ref PRIMARY PRIMARY 8 test.t1.a 1 Using index 16751 SIMPLE t3 ref idx idx 16 test.t1.a,test.t2.b 2 NULL 1676SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val 1677FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) 1678WHERE t1.a=t2.a; 1679a a a b b val 16801 1 NULL 10 NULL NULL 16811 1 NULL 20 NULL NULL 16821 1 NULL 30 NULL NULL 16831 1 NULL 40 NULL NULL 16841 1 NULL 50 NULL NULL 16852 2 2 30 30 0 16862 2 2 40 40 0 16872 2 2 50 50 0 16882 2 2 60 60 0 16892 2 2 70 70 0 16902 2 2 80 80 0 1691DROP INDEX idx ON t3; 1692EXPLAIN 1693SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val 1694FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) 1695WHERE t1.a=t2.a; 1696id select_type table type possible_keys key key_len ref rows Extra 16971 SIMPLE t1 index PRIMARY PRIMARY 8 NULL 2 Using index 16981 SIMPLE t2 ref PRIMARY PRIMARY 8 test.t1.a 1 Using index 16991 SIMPLE t3 ALL NULL NULL NULL NULL 24 Using where; Using join buffer (Block Nested Loop) 1700SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val 1701FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) 1702WHERE t1.a=t2.a; 1703a a a b b val 17041 1 NULL 10 NULL NULL 17051 1 NULL 20 NULL NULL 17061 1 NULL 30 NULL NULL 17071 1 NULL 40 NULL NULL 17081 1 NULL 50 NULL NULL 17092 2 2 30 30 0 17102 2 2 40 40 0 17112 2 2 50 50 0 17122 2 2 60 60 0 17132 2 2 70 70 0 17142 2 2 80 80 0 1715set join_buffer_size=default; 1716DROP TABLE t1,t2,t3; 1717create table t1(f1 int, f2 int); 1718insert into t1 values (1,1),(2,2),(3,3); 1719create table t2(f1 int not null, f2 int not null, f3 char(200), key(f1,f2)); 1720insert into t2 values (1,1, 'qwerty'),(1,2, 'qwerty'),(1,3, 'qwerty'); 1721insert into t2 values (2,1, 'qwerty'),(2,2, 'qwerty'),(2,3, 'qwerty'), 1722(2,4, 'qwerty'),(2,5, 'qwerty'); 1723insert into t2 values (3,1, 'qwerty'),(3,4, 'qwerty'); 1724insert into t2 values (4,1, 'qwerty'),(4,2, 'qwerty'),(4,3, 'qwerty'), 1725(4,4, 'qwerty'); 1726insert into t2 values (1,1, 'qwerty'),(1,2, 'qwerty'),(1,3, 'qwerty'); 1727insert into t2 values (2,1, 'qwerty'),(2,2, 'qwerty'),(2,3, 'qwerty'), 1728(2,4, 'qwerty'),(2,5, 'qwerty'); 1729insert into t2 values (3,1, 'qwerty'),(3,4, 'qwerty'); 1730insert into t2 values (4,1, 'qwerty'),(4,2, 'qwerty'),(4,3, 'qwerty'), 1731(4,4, 'qwerty'); 1732select t2.f1, t2.f2, t2.f3 from t1,t2 1733where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1; 1734f1 f2 f3 17351 1 qwerty 17361 1 qwerty 17372 2 qwerty 17382 2 qwerty 1739explain select t2.f1, t2.f2, t2.f3 from t1,t2 1740where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2; 1741id select_type table type possible_keys key key_len ref rows Extra 17421 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where 17431 SIMPLE t2 ref f1 f1 4 test.t1.f1 3 Using index condition 1744drop table t1,t2; 1745# 1746# Bug #42955: join with GROUP BY/ORDER BY and when BKA is enabled 1747# 1748create table t1 (d int, id1 int, index idx1 (d, id1)); 1749insert into t1 values 1750(3, 20), (2, 40), (3, 10), (1, 10), (3, 20), (1, 40), (2, 30), (3, 30); 1751create table t2 (id1 int, id2 int, index idx2 (id1)); 1752insert into t2 values 1753(20, 100), (30, 400), (20, 400), (30, 200), (10, 300), (10, 200), (40, 100), 1754(40, 200), (30, 300), (10, 400), (20, 200), (20, 300); 1755explain 1756select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1 1757where t1.d=3 group by t1.id1; 1758id select_type table type possible_keys key key_len ref rows Extra 17591 SIMPLE t1 ref idx1 idx1 5 const 4 Using where; Using index 17601 SIMPLE t2 ref idx2 idx2 5 test.t1.id1 2 NULL 1761select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1 1762where t1.d=3 group by t1.id1; 1763id1 sum(t2.id2) 176410 900 176520 2000 176630 900 1767explain 1768select t1.id1 from t1 join t2 on t1.id1=t2.id1 1769where t1.d=3 and t2.id2 > 200 order by t1.id1; 1770id select_type table type possible_keys key key_len ref rows Extra 17711 SIMPLE t1 ref idx1 idx1 5 const 4 Using where; Using index 17721 SIMPLE t2 ref idx2 idx2 5 test.t1.id1 2 Using where 1773select t1.id1 from t1 join t2 on t1.id1=t2.id1 1774where t1.d=3 and t2.id2 > 200 order by t1.id1; 1775id1 177610 177710 177820 177920 178020 178120 178230 178330 1784drop table t1,t2; 1785# 1786# Bug #44019: star-like multi-join query executed optimizer_join_cache_level=6 1787# 1788create table t1 (a int, b int, c int, d int); 1789create table t2 (b int, e varchar(16), index idx(b)); 1790create table t3 (d int, f varchar(16), index idx(d)); 1791create table t4 (c int, g varchar(16), index idx(c)); 1792insert into t1 values 1793(5, 50, 500, 5000), (3, 30, 300, 3000), (9, 90, 900, 9000), 1794(2, 20, 200, 2000), (4, 40, 400, 4000), (8, 80, 800, 800), 1795(7, 70, 700, 7000); 1796insert into t2 values 1797(30, 'bbb'), (10, 'b'), (70, 'bbbbbbb'), (60, 'bbbbbb'), 1798(31, 'bbb'), (11, 'b'), (71, 'bbbbbbb'), (61, 'bbbbbb'), 1799(32, 'bbb'), (12, 'b'), (72, 'bbbbbbb'), (62, 'bbbbbb'); 1800insert into t3 values 1801(4000, 'dddd'), (3000, 'ddd'), (1000, 'd'), (8000, 'dddddddd'), 1802(4001, 'dddd'), (3001, 'ddd'), (1001, 'd'), (8001, 'dddddddd'), 1803(4002, 'dddd'), (3002, 'ddd'), (1002, 'd'), (8002, 'dddddddd'); 1804insert into t4 values 1805(200, 'cc'), (600, 'cccccc'), (300, 'ccc'), (500, 'ccccc'), 1806(201, 'cc'), (601, 'cccccc'), (301, 'ccc'), (501, 'ccccc'), 1807(202, 'cc'), (602, 'cccccc'), (302, 'ccc'), (502, 'ccccc'); 1808analyze table t2,t3,t4; 1809explain 1810select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4 1811where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c; 1812id select_type table type possible_keys key key_len ref rows Extra 18131 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where 18141 SIMPLE t2 ref idx idx 5 test.t1.b 1 NULL 18151 SIMPLE t3 ref idx idx 5 test.t1.d 1 NULL 18161 SIMPLE t4 ref idx idx 5 test.t1.c 1 NULL 1817select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4 1818where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c; 1819a b c d e f g 18203 30 300 3000 bbb ddd ccc 1821drop table t1,t2,t3,t4; 1822# 1823# Bug #44250: Corruption of linked join buffers when using BKA 1824# 1825CREATE TABLE t1 ( 1826id1 bigint(20) DEFAULT NULL, 1827id2 bigint(20) DEFAULT NULL, 1828id3 bigint(20) DEFAULT NULL, 1829num1 bigint(20) DEFAULT NULL, 1830num2 int(11) DEFAULT NULL, 1831num3 bigint(20) DEFAULT NULL 1832); 1833CREATE TABLE t2 ( 1834id3 bigint(20) NOT NULL DEFAULT '0', 1835id4 bigint(20) DEFAULT NULL, 1836enum1 enum('Enabled','Disabled','Paused') DEFAULT NULL, 1837PRIMARY KEY (id3) 1838); 1839CREATE TABLE t3 ( 1840id4 bigint(20) NOT NULL DEFAULT '0', 1841text1 text, 1842PRIMARY KEY (id4) 1843); 1844CREATE TABLE t4 ( 1845id2 bigint(20) NOT NULL DEFAULT '0', 1846dummy int(11) DEFAULT '0', 1847PRIMARY KEY (id2) 1848); 1849CREATE TABLE t5 ( 1850id1 bigint(20) NOT NULL DEFAULT '0', 1851id2 bigint(20) NOT NULL DEFAULT '0', 1852enum2 enum('Active','Deleted','Paused') DEFAULT NULL, 1853PRIMARY KEY (id1,id2) 1854); 1855set join_buffer_size=2048; 1856EXPLAIN 1857SELECT STRAIGHT_JOIN t1.id1, t1.num3, t3.text1, t3.id4, t2.id3, t4.dummy 1858FROM t1 JOIN t2 JOIN t3 JOIN t4 JOIN t5 1859WHERE t1.id1=t5.id1 AND t1.id2=t5.id2 and t4.id2=t1.id2 AND 1860t5.enum2='Active' AND t3.id4=t2.id4 AND t2.id3=t1.id3 AND t3.text1<'D'; 1861id select_type table type possible_keys key key_len ref rows Extra 18621 SIMPLE t1 ALL NULL NULL NULL NULL 349 Using where 18631 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.id3 1 Using where 18641 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t2.id4 1 Using where 18651 SIMPLE t4 eq_ref PRIMARY PRIMARY 8 test.t1.id2 1 NULL 18661 SIMPLE t5 eq_ref PRIMARY PRIMARY 16 test.t1.id1,test.t1.id2 1 Using where 1867SELECT STRAIGHT_JOIN t1.id1, t1.num3, t3.text1, t3.id4, t2.id3, t4.dummy 1868FROM t1 JOIN t2 JOIN t3 JOIN t4 JOIN t5 1869WHERE t1.id1=t5.id1 AND t1.id2=t5.id2 and t4.id2=t1.id2 AND 1870t5.enum2='Active' AND t3.id4=t2.id4 AND t2.id3=t1.id3 AND t3.text1<'D'; 1871id1 num3 text1 id4 id3 dummy 1872228172702 134 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 2567095402 2667134182 0 1873228172702 14 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 2567095402 2667134182 0 1874228172702 15 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 2567095402 2667134182 0 1875228172702 3 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 2567095402 2667134182 0 1876228808822 1 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 1877228808822 1 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 1878228808822 1 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 1879228808822 10 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 1880228808822 13 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 1881228808822 13 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 1882228808822 14 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 1883228808822 17 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 1884228808822 18 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 1885228808822 19 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 1886228808822 26 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 1887228808822 28 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 1888228808822 3 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 1889228808822 3 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 1890228808822 3 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 1891228808822 3 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 1892228808822 4 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 1893228808822 4 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 1894228808822 4 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 1895228808822 50 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 1896228808822 6 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 1897228808822 60 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 1898228808822 61 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 1899228808822 62 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 1900228808822 84 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 1901228808822 89 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 1902228808822 9 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 1903set join_buffer_size=default; 1904DROP TABLE t1,t2,t3,t4,t5; 1905# 1906# Bug #46328: Use of aggregate function without GROUP BY clause 1907# returns many rows (vs. one ) 1908# 1909CREATE TABLE t1 ( 1910int_key int(11) NOT NULL, 1911KEY int_key (int_key) 1912); 1913INSERT INTO t1 VALUES 1914(0),(2),(2),(2),(3),(4),(5),(5),(6),(6),(8),(8),(9),(9); 1915CREATE TABLE t2 ( 1916int_key int(11) NOT NULL, 1917KEY int_key (int_key) 1918); 1919INSERT INTO t2 VALUES (2),(3); 1920 1921# The query shall return 1 record with a max value 9 and one of the 1922# int_key values inserted above (undefined which one). A changed 1923# execution plan may change the value in the second column 1924SELECT MAX(t1.int_key), t1.int_key 1925FROM t1 STRAIGHT_JOIN t2 1926ORDER BY t1.int_key; 1927MAX(t1.int_key) int_key 19289 0 1929 1930explain 1931SELECT MAX(t1.int_key), t1.int_key 1932FROM t1 STRAIGHT_JOIN t2 1933ORDER BY t1.int_key; 1934id select_type table type possible_keys key key_len ref rows Extra 19351 SIMPLE t1 index NULL int_key 4 NULL 14 Using index 19361 SIMPLE t2 index NULL int_key 4 NULL 2 Using index; Using join buffer (Block Nested Loop) 1937 1938DROP TABLE t1,t2; 1939# 1940# Bug #45019: join buffer contains two blob columns one of which is 1941# used in the key employed to access the joined table 1942# 1943CREATE TABLE t1 (c1 int, c2 int, key (c2)); 1944INSERT INTO t1 VALUES (1,1); 1945INSERT INTO t1 VALUES (2,2); 1946CREATE TABLE t2 (c1 text, c2 text); 1947INSERT INTO t2 VALUES('tt', 'uu'); 1948INSERT INTO t2 VALUES('zzzz', 'xxxxxxxxx'); 1949ANALYZE TABLE t1,t2; 1950SELECT t1.*, t2.*, LENGTH(t2.c1), LENGTH(t2.c2) FROM t1,t2 1951WHERE t1.c2=LENGTH(t2.c2) and t1.c1=LENGTH(t2.c1); 1952c1 c2 c1 c2 LENGTH(t2.c1) LENGTH(t2.c2) 19532 2 tt uu 2 2 1954DROP TABLE t1,t2; 1955# 1956# Regression test for 1957# Bug#46733 - NULL value not returned for aggregate on empty result 1958# set w/ semijoin on 1959CREATE TABLE t1 ( 1960i int(11) NOT NULL, 1961v varchar(1) DEFAULT NULL, 1962PRIMARY KEY (i) 1963); 1964INSERT INTO t1 VALUES (10,'a'),(11,'b'),(12,'c'),(13,'d'); 1965CREATE TABLE t2 ( 1966i int(11) NOT NULL, 1967v varchar(1) DEFAULT NULL, 1968PRIMARY KEY (i) 1969); 1970INSERT INTO t2 VALUES (1,'x'),(2,'y'); 1971 1972SELECT MAX(t1.i) 1973FROM t1 JOIN t2 ON t2.v 1974ORDER BY t2.v; 1975MAX(t1.i) 1976NULL 1977Warnings: 1978Warning 1292 Truncated incorrect INTEGER value: 'x' 1979Warning 1292 Truncated incorrect INTEGER value: 'y' 1980 1981EXPLAIN 1982SELECT MAX(t1.i) 1983FROM t1 JOIN t2 ON t2.v 1984ORDER BY t2.v; 1985id select_type table type possible_keys key key_len ref rows Extra 19861 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where 19871 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index; Using join buffer (Block Nested Loop) 1988 1989DROP TABLE t1,t2; 1990# 1991# Bug#51092: Linked join buffer gives wrong result 1992# for 3-way cross join 1993# 1994CREATE TABLE t1 (a INT, b INT); 1995INSERT INTO t1 VALUES (1,1),(2,2); 1996CREATE TABLE t2 (a INT, b INT); 1997INSERT INTO t2 VALUES (1,1),(2,2); 1998CREATE TABLE t3 (a INT, b INT); 1999INSERT INTO t3 VALUES (1,1),(2,2); 2000EXPLAIN SELECT t1.* FROM t1,t2,t3; 2001id select_type table type possible_keys key key_len ref rows Extra 20021 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL 20031 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using join buffer (Block Nested Loop) 20041 SIMPLE t3 ALL NULL NULL NULL NULL 2 Using join buffer (Block Nested Loop) 2005SELECT t1.* FROM t1,t2,t3; 2006a b 20071 1 20082 2 20091 1 20102 2 20111 1 20122 2 20131 1 20142 2 2015DROP TABLE t1,t2,t3; 2016# 2017# BUG#52394 Segfault in JOIN_CACHE::get_offset () at sql_select.h:445 2018# 2019CREATE TABLE C(a int); 2020INSERT INTO C VALUES(1),(2),(3),(4),(5); 2021CREATE TABLE D (a int(11), b varchar(1)); 2022INSERT INTO D VALUES (6,'r'),(27,'o'); 2023CREATE TABLE E (a int(11) primary key, b varchar(1)); 2024INSERT INTO E VALUES 2025(14,'d'),(15,'z'),(16,'e'),(17,'h'),(18,'b'),(19,'s'),(20,'e'),(21,'j'),(22,'e'),(23,'f'),(24,'v'),(25,'x'),(26,'m'),(27,'c'); 2026SELECT 1 FROM C,D,E WHERE D.a = E.a AND D.b = E.b; 20271 2028DROP TABLE C,D,E; 2029# 2030# BUG#52540 Crash in JOIN_CACHE::set_match_flag_if_none () at sql_join_cache.cc:1883 2031# 2032CREATE TABLE t1 (a int); 2033INSERT INTO t1 VALUES (2); 2034CREATE TABLE t2 (a varchar(10)); 2035INSERT INTO t2 VALUES ('f'),('x'); 2036CREATE TABLE t3 (pk int(11) PRIMARY KEY); 2037INSERT INTO t3 VALUES (2); 2038CREATE TABLE t4 (a varchar(10)); 2039EXPLAIN SELECT 1 2040FROM t2 LEFT JOIN 2041((t1 JOIN t3 ON t1.a = t3.pk) 2042LEFT JOIN t4 ON 1 ) 2043ON 1 ; 2044id select_type table type possible_keys key key_len ref rows Extra 20451 SIMPLE t2 ALL NULL NULL NULL NULL 2 NULL 20461 SIMPLE t1 ALL NULL NULL NULL NULL 1 Using where 20471 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index 20481 SIMPLE t4 ALL NULL NULL NULL NULL 0 Using where 2049SELECT 1 2050FROM t2 LEFT JOIN 2051((t1 JOIN t3 ON t1.a = t3.pk) 2052LEFT JOIN t4 ON 1 ) 2053ON 1 ; 20541 20551 20561 2057DROP TABLE t1,t2,t3,t4; 2058# 2059# Bug#51084: Batched key access crashes for SELECT with 2060# derived table and LEFT JOIN 2061# 2062CREATE TABLE t1 ( 2063carrier int, 2064id int PRIMARY KEY 2065); 2066INSERT INTO t1 VALUES (1,11),(1,12),(2,13); 2067CREATE TABLE t2 ( 2068scan_date int, 2069package_id int 2070); 2071INSERT INTO t2 VALUES (2008,21),(2008,22); 2072CREATE TABLE t3 ( 2073carrier int PRIMARY KEY, 2074id int 2075); 2076INSERT INTO t3 VALUES (1,31); 2077CREATE TABLE t4 ( 2078carrier_id int, 2079INDEX carrier_id(carrier_id) 2080); 2081INSERT INTO t4 VALUES (31),(32); 2082 2083SELECT COUNT(*) 2084FROM (t2 JOIN t1) LEFT JOIN (t3 JOIN t4 ON t3.id = t4.carrier_id) 2085ON t3.carrier = t1.carrier; 2086COUNT(*) 20876 2088 2089EXPLAIN 2090SELECT COUNT(*) 2091FROM (t2 JOIN t1) LEFT JOIN (t3 JOIN t4 ON t3.id = t4.carrier_id) 2092ON t3.carrier = t1.carrier; 2093id select_type table type possible_keys key key_len ref rows Extra 20941 SIMPLE t2 ALL NULL NULL NULL NULL 2 NULL 20951 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join buffer (Block Nested Loop) 20961 SIMPLE t3 ALL PRIMARY NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop) 20971 SIMPLE t4 index carrier_id carrier_id 5 NULL 2 Using where; Using index; Using join buffer (Block Nested Loop) 2098 2099DROP TABLE t1,t2,t3,t4; 2100# 2101# Bug#45267: Incomplete check caused wrong result. 2102# 2103CREATE TABLE t1 ( 2104`pk` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY 2105); 2106CREATE TABLE t3 ( 2107`pk` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY 2108); 2109INSERT INTO t3 VALUES 2110(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15), 2111(16),(17),(18),(19),(20); 2112CREATE TABLE t2 ( 2113`pk` int(11) NOT NULL AUTO_INCREMENT, 2114`int_nokey` int(11) NOT NULL, 2115`time_key` time NOT NULL, 2116PRIMARY KEY (`pk`), 2117KEY `time_key` (`time_key`) 2118); 2119INSERT INTO t2 VALUES (10,9,'22:36:46'),(11,0,'08:46:46'); 2120SELECT DISTINCT t1.`pk` 2121FROM t1 RIGHT JOIN t2 STRAIGHT_JOIN t3 ON t2.`int_nokey` ON t2.`time_key` 2122GROUP BY 1; 2123pk 2124NULL 2125DROP TABLE IF EXISTS t1, t2, t3; 2126# 2127# BUG#52636 6.0 allowing JOINs on NULL values w/ optimizer_join_cache_level = 5-8 2128# 2129CREATE TABLE t1 (b int); 2130INSERT INTO t1 VALUES (NULL),(3); 2131CREATE TABLE t2 (a int, b int, KEY (b)); 2132INSERT INTO t2 VALUES (100,NULL),(150,200); 2133EXPLAIN SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b = t1.b; 2134id select_type table type possible_keys key key_len ref rows Extra 21351 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL 21361 SIMPLE t2 ref b b 5 test.t1.b 2 NULL 2137SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b = t1.b; 2138a 2139NULL 2140NULL 2141delete from t1; 2142INSERT INTO t1 VALUES (NULL),(NULL); 2143EXPLAIN SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b = t1.b; 2144id select_type table type possible_keys key key_len ref rows Extra 21451 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL 21461 SIMPLE t2 ref b b 5 test.t1.b 2 NULL 2147SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b = t1.b; 2148a 2149NULL 2150NULL 2151DROP TABLE t1,t2; 2152CREATE TABLE t1 (b varchar(100)); 2153INSERT INTO t1 VALUES (NULL),("some varchar"); 2154CREATE TABLE t2 (a int, b varchar(100), KEY (b)); 2155INSERT INTO t2 VALUES (100,NULL),(150,"varchar"),(200,NULL),(250,"long long varchar"); 2156explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; 2157id select_type table type possible_keys key key_len ref rows Extra 21581 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL 21591 SIMPLE t2 ALL b NULL NULL NULL 4 Using where; Using join buffer (Block Nested Loop) 2160SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; 2161a 2162NULL 2163NULL 2164DROP TABLE t1,t2; 2165# 2166# BUG#54359 "Extra rows with join_cache_level=7,8 and two joins 2167# --and multi-column index" 2168# 2169CREATE TABLE t1 ( 2170`pk` int(11) NOT NULL, 2171`col_int_key` int(11) DEFAULT NULL, 2172`col_varchar_key` varchar(1) DEFAULT NULL, 2173`col_varchar_nokey` varchar(1) DEFAULT NULL, 2174KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)) 2175; 2176INSERT INTO t1 VALUES (4,9,'k','k'); 2177INSERT INTO t1 VALUES (12,5,'k','k'); 2178explain SELECT table2 .`col_int_key` FROM t1 table2, 2179t1 table3 force index (`col_varchar_key`) 2180where table3 .`pk` and table3 .`col_int_key` >= table2 .`pk` 2181and table3 .`col_varchar_key` = table2 .`col_varchar_nokey`; 2182id select_type table type possible_keys key key_len ref rows Extra 21831 SIMPLE table2 ALL NULL NULL NULL NULL 2 Using where 21841 SIMPLE table3 ref col_varchar_key col_varchar_key 4 test.table2.col_varchar_nokey 1 Using index condition; Using where 2185SELECT table2 .`col_int_key` FROM t1 table2, 2186t1 table3 force index (`col_varchar_key`) 2187where table3 .`pk` and table3 .`col_int_key` >= table2 .`pk` 2188and table3 .`col_varchar_key` = table2 .`col_varchar_nokey`; 2189col_int_key 21909 21919 2192drop table t1; 2193# 2194# BUG#54481 "GROUP BY loses effect with JOIN + ORDER BY + LIMIT 2195# and join_cache_level=5-8" 2196# 2197CREATE TABLE t1 ( 2198`col_int_key` int, 2199`col_datetime` datetime, 2200KEY `col_int_key` (`col_int_key`) 2201); 2202INSERT INTO t1 VALUES (2,'2003-02-11 21:19:41'); 2203INSERT INTO t1 VALUES (3,'2009-10-18 02:27:49'); 2204INSERT INTO t1 VALUES (0,'2000-09-26 07:45:57'); 2205CREATE TABLE t2 ( 2206`col_int` int, 2207`col_int_key` int, 2208KEY `col_int_key` (`col_int_key`) 2209); 2210INSERT INTO t2 VALUES (14,1); 2211INSERT INTO t2 VALUES (98,1); 2212explain SELECT t1.col_int_key, t1.col_datetime 2213FROM t1,t2 2214WHERE t2.col_int_key = 1 AND t2.col_int >= 3 2215GROUP BY t1.col_int_key 2216ORDER BY t1.col_int_key, t1.col_datetime 2217LIMIT 2; 2218id select_type table type possible_keys key key_len ref rows Extra 22191 SIMPLE t1 ALL col_int_key NULL NULL NULL 3 Using temporary; Using filesort 22201 SIMPLE t2 ref col_int_key col_int_key 5 const 1 Using where 2221SELECT t1.col_int_key, t1.col_datetime 2222FROM t1,t2 2223WHERE t2.col_int_key = 1 AND t2.col_int >= 3 2224GROUP BY t1.col_int_key 2225ORDER BY t1.col_int_key, t1.col_datetime 2226LIMIT 2; 2227col_int_key col_datetime 22280 2000-09-26 07:45:57 22292 2003-02-11 21:19:41 2230explain SELECT t1.col_int_key, t1.col_datetime 2231FROM t1 force index (col_int_key), t2 ignore index (col_int_key) 2232WHERE t2.col_int_key = 1 AND t2.col_int >= 3 2233GROUP BY t1.col_int_key 2234ORDER BY t1.col_int_key, t1.col_datetime 2235LIMIT 2; 2236id select_type table type possible_keys key key_len ref rows Extra 22371 SIMPLE t1 index col_int_key col_int_key 5 NULL 3 Using temporary; Using filesort 22381 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where 2239SELECT t1.col_int_key, t1.col_datetime 2240FROM t1 force index (col_int_key), t2 ignore index (col_int_key) 2241WHERE t2.col_int_key = 1 AND t2.col_int >= 3 2242GROUP BY t1.col_int_key 2243ORDER BY t1.col_int_key, t1.col_datetime 2244LIMIT 2; 2245col_int_key col_datetime 22460 2000-09-26 07:45:57 22472 2003-02-11 21:19:41 2248drop table t1,t2; 2249 2250# Bug#11766522 "59651: ASSERTION `TABLE_REF->HAS_RECORD' FAILED 2251# WITH JOIN_CACHE_LEVEL=3" 2252 2253CREATE TABLE t1 ( 2254b varchar(20) 2255) ; 2256INSERT INTO t1 VALUES ('1'),('1'); 2257CREATE TABLE t4 ( 2258col253 text 2259) ; 2260INSERT INTO t4 VALUES (''),('pf'); 2261CREATE TABLE t6 ( 2262col282 timestamp 2263) ; 2264INSERT INTO t6 VALUES ('2010-11-07 01:04:45'),('2010-12-13 01:36:32'); 2265CREATE TABLE t7 ( 2266col319 timestamp NOT NULL, 2267UNIQUE KEY idx263 (col319) 2268) ; 2269insert into t7 values("2000-01-01"),("2000-01-02"); 2270CREATE TABLE t3 ( 2271col582 char(230) CHARACTER SET utf8 DEFAULT NULL 2272) ; 2273INSERT INTO t3 VALUES ('cymej'),('spb'); 2274CREATE TABLE t5 ( 2275col712 time 2276) ; 2277insert into t5 values(0),(0); 2278CREATE TABLE t8 ( 2279col804 char(169), 2280col805 varchar(51) 2281) ; 2282INSERT INTO t8 VALUES ('tmqcb','pwk'); 2283CREATE TABLE t2 ( 2284col841 varchar(10) 2285) ; 2286INSERT INTO t2 VALUES (''),(''); 2287set join_buffer_size=1; 2288Warnings: 2289Warning 1292 Truncated incorrect join_buffer_size value: '1' 2290select @@join_buffer_size; 2291@@join_buffer_size 2292128 2293select count(*) from 2294(t1 join t2 join t3) 2295left join t4 on 1 2296left join t5 on 1 like t4.col253 2297left join t6 on t5.col712 is null 2298left join t7 on t1.b <=>t7.col319 2299left join t8 on t3.col582 <= 1; 2300count(*) 230132 2302drop table t1,t2,t3,t4,t5,t6,t7,t8; 2303# 2304# Bug#12616131 - JCL: NULL VS DATE + TWICE AS MANY ROWS 2305# RETURNED WHEN JCL>=7 2306# 2307CREATE TABLE t1 (t1a int, t1b int); 2308INSERT INTO t1 VALUES (99, NULL),(99, 3),(99,0); 2309CREATE TABLE t2 (t2a int, t2b int, KEY idx (t2b)); 2310INSERT INTO t2 VALUES (100,0),(150,200),(999, 0),(999, NULL); 2311 2312# t2b is NULL-able 2313 2314EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b; 2315id select_type table type possible_keys key key_len ref rows Extra 23161 SIMPLE t1 ALL NULL NULL NULL NULL 3 NULL 23171 SIMPLE t2 ref idx idx 5 test.t1.t1b 2 NULL 2318SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b; 2319t1a t1b t2a t2b 232099 NULL NULL NULL 232199 3 NULL NULL 232299 0 100 0 232399 0 999 0 2324 2325EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b; 2326id select_type table type possible_keys key key_len ref rows Extra 23271 SIMPLE t1 ALL NULL NULL NULL NULL 3 NULL 23281 SIMPLE t2 ref idx idx 5 test.t1.t1b 2 Using where 2329SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b; 2330t1a t1b t2a t2b 233199 NULL 999 NULL 233299 3 NULL NULL 233399 0 100 0 233499 0 999 0 2335 2336DROP TABLE t2; 2337CREATE TABLE t2 (t2a int, t2b int NOT NULL, KEY idx (t2b)); 2338INSERT INTO t2 VALUES (100,0),(150,200),(999, 0); 2339 2340# t2b is NOT NULL 2341 2342EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b; 2343id select_type table type possible_keys key key_len ref rows Extra 23441 SIMPLE t1 ALL NULL NULL NULL NULL 3 NULL 23451 SIMPLE t2 ref idx idx 4 test.t1.t1b 2 NULL 2346SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b; 2347t1a t1b t2a t2b 234899 NULL NULL NULL 234999 3 NULL NULL 235099 0 100 0 235199 0 999 0 2352 2353EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b; 2354id select_type table type possible_keys key key_len ref rows Extra 23551 SIMPLE t1 ALL NULL NULL NULL NULL 3 NULL 23561 SIMPLE t2 ref idx idx 4 test.t1.t1b 2 Using where 2357SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b; 2358t1a t1b t2a t2b 235999 NULL NULL NULL 236099 3 NULL NULL 236199 0 100 0 236299 0 999 0 2363 2364DROP TABLE t1,t2; 2365# 2366# BUG#12619399 - JCL: NO ROWS VS 3X NULL QUERY OUTPUT WHEN JCL>=5 2367# 2368CREATE TABLE t1 ( 2369c1 INTEGER NOT NULL, 2370c2_key INTEGER NOT NULL, 2371KEY col_int_key (c2_key) 2372) ENGINE=InnoDB; 2373INSERT INTO t1 VALUES (24,204); 2374CREATE TABLE t2 ( 2375pk INTEGER NOT NULL, 2376PRIMARY KEY (pk) 2377) ENGINE=InnoDB; 2378INSERT INTO t2 VALUES (10); 2379CREATE TABLE t3 ( 2380c1 INTEGER, 2381KEY k1 (c1) 2382) ENGINE=InnoDB; 2383INSERT INTO t3 VALUES (NULL), (NULL); 2384set @old_opt_switch=@@optimizer_switch; 2385 2386explain SELECT t3.c1 FROM t3 2387WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1) 2388XOR TRUE; 2389id select_type table type possible_keys key key_len ref rows Extra 23901 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index 23912 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key 23922 DEPENDENT SUBQUERY t2 ALL PRIMARY NULL NULL NULL 1 Range checked for each record (index map: 0x1) 2393explain SELECT t3.c1 FROM t3 2394WHERE t3.c1 = ANY (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1) 2395XOR TRUE; 2396id select_type table type possible_keys key key_len ref rows Extra 23971 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index 23982 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key 23992 DEPENDENT SUBQUERY t2 ALL PRIMARY NULL NULL NULL 1 Range checked for each record (index map: 0x1) 2400explain SELECT t3.c1 FROM t3 2401WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1) 2402XOR TRUE; 2403id select_type table type possible_keys key key_len ref rows Extra 24041 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index 24052 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key 24062 DEPENDENT SUBQUERY t2 ALL PRIMARY NULL NULL NULL 1 Range checked for each record (index map: 0x1) 2407explain SELECT t3.c1 FROM t3 2408WHERE t3.c1 NOT IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1); 2409id select_type table type possible_keys key key_len ref rows Extra 24101 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index 24112 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key 24122 DEPENDENT SUBQUERY t2 ALL PRIMARY NULL NULL NULL 1 Range checked for each record (index map: 0x1) 2413explain SELECT t3.c1 FROM t3 2414WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1); 2415id select_type table type possible_keys key key_len ref rows Extra 24161 SIMPLE t1 ALL col_int_key NULL NULL NULL 1 Start temporary 24171 SIMPLE t2 ALL PRIMARY NULL NULL NULL 1 Range checked for each record (index map: 0x1) 24181 SIMPLE t3 ref k1 k1 5 test.t1.c2_key 1 Using index; End temporary 2419SELECT t3.c1 FROM t3 2420WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1) 2421XOR TRUE; 2422c1 2423SELECT t3.c1 FROM t3 2424WHERE t3.c1 = ANY (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1) 2425XOR TRUE; 2426c1 2427SELECT t3.c1 FROM t3 2428WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1) 2429XOR TRUE; 2430c1 2431SELECT t3.c1 FROM t3 2432WHERE t3.c1 NOT IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1); 2433c1 2434SELECT t3.c1 FROM t3 2435WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1); 2436c1 2437 2438set @@optimizer_switch=@old_opt_switch; 2439DROP TABLE t1, t2, t3; 2440set @@join_buffer_size=default; 2441 2442# BUG#12586926 "EXTRA ROW WITH JOIN + GROUP BY + ORDER BY WITH 2443# JCL>=5 AND MRR ENABLED" 2444 2445CREATE TABLE t1 ( col_int_key int(11) NOT NULL, 2446col_varchar_key varchar(1) NOT NULL, 2447KEY col_int_key (col_int_key), 2448KEY col_varchar_key (col_varchar_key,col_int_key) 2449) ENGINE=innodb; 2450INSERT INTO t1 VALUES (0,'j'),(4,'b'),(4,'d'); 2451CREATE TABLE t2 ( 2452col_datetime_key datetime NOT NULL, 2453col_varchar_key varchar(1) NOT NULL, 2454KEY col_varchar_key (col_varchar_key) 2455) ENGINE=innodb; 2456INSERT INTO t2 VALUES ('2003-08-21 00:00:00','b'); 2457explain SELECT MIN(t2.col_datetime_key) AS field1, 2458t1.col_int_key AS field2 2459FROM t1 2460LEFT JOIN t2 force index (col_varchar_key) 2461ON t1.col_varchar_key = t2.col_varchar_key 2462GROUP BY field2 2463ORDER BY field1; 2464id select_type table type possible_keys key key_len ref rows Extra 24651 SIMPLE t1 index col_int_key,col_varchar_key col_int_key 4 NULL 3 Using temporary; Using filesort 24661 SIMPLE t2 ref col_varchar_key col_varchar_key 3 test.t1.col_varchar_key 1 NULL 2467SELECT MIN(t2.col_datetime_key) AS field1, 2468t1.col_int_key AS field2 2469FROM t1 2470LEFT JOIN t2 force index (col_varchar_key) 2471ON t1.col_varchar_key = t2.col_varchar_key 2472GROUP BY field2 2473ORDER BY field1; 2474field1 field2 2475NULL 0 24762003-08-21 00:00:00 4 2477DROP TABLE t1,t2; 2478 2479# BUG#12619510 "JCL: MORE ROWS AND DIFFERENT OUTPUT WITH JCL>=5" 2480 2481CREATE TABLE t1 ( 2482col_int_key int(11) NOT NULL, 2483col_datetime_key datetime NOT NULL, 2484col_varchar_nokey varchar(1) NOT NULL, 2485KEY col_int_key (col_int_key), 2486KEY col_datetime_key (col_datetime_key) 2487); 2488INSERT INTO t1 VALUES (7,'2004-06-06 04:22:12','v'); 2489INSERT INTO t1 VALUES (0,'2005-11-13 01:12:31','s'); 2490INSERT INTO t1 VALUES (9,'2002-05-04 01:50:00','l'); 2491INSERT INTO t1 VALUES (3,'2004-10-27 10:28:45','y'); 2492INSERT INTO t1 VALUES (4,'2006-07-22 05:24:23','c'); 2493INSERT INTO t1 VALUES (2,'2002-05-16 21:34:03','i'); 2494INSERT INTO t1 VALUES (5,'2008-04-17 10:45:30','h'); 2495INSERT INTO t1 VALUES (3,'2009-04-21 02:58:02','q'); 2496INSERT INTO t1 VALUES (1,'2008-01-11 11:01:51','a'); 2497INSERT INTO t1 VALUES (3,'1900-01-01 00:00:00','v'); 2498INSERT INTO t1 VALUES (6,'2007-05-17 18:24:57','u'); 2499INSERT INTO t1 VALUES (7,'2007-08-07 00:00:00','s'); 2500INSERT INTO t1 VALUES (5,'2001-08-28 00:00:00','y'); 2501INSERT INTO t1 VALUES (1,'2004-04-16 00:27:28','z'); 2502INSERT INTO t1 VALUES (204,'2005-05-03 07:06:22','h'); 2503INSERT INTO t1 VALUES (224,'2009-03-11 17:09:50','p'); 2504INSERT INTO t1 VALUES (9,'2007-12-08 01:54:28','e'); 2505INSERT INTO t1 VALUES (5,'2009-07-28 18:19:54','i'); 2506INSERT INTO t1 VALUES (0,'2008-06-08 00:00:00','y'); 2507INSERT INTO t1 VALUES (3,'2005-02-09 09:20:26','w'); 2508CREATE TABLE t2 ( 2509pk int(11) NOT NULL, 2510col_varchar_key varchar(1) NOT NULL, 2511PRIMARY KEY (pk) 2512); 2513INSERT INTO t2 VALUES 2514(1,'j'),(2,'v'),(3,'c'),(4,'m'),(5,'d'),(6,'d'),(7,'y'), 2515(8,'t'),(9,'d'),(10,'s'),(11,'r'),(12,'m'),(13,'b'),(14,'x'), 2516(15,'g'),(16,'p'),(17,'q'),(18,'w'),(19,'d'),(20,'e'); 2517explain SELECT t2.col_varchar_key AS field1 , COUNT(DISTINCT t1.col_varchar_nokey), t2.pk AS field4 2518FROM t1 2519RIGHT JOIN t2 ON t2.pk = t1.col_int_key 2520GROUP BY field1 , field4 2521ORDER BY t1.col_datetime_key ; 2522id select_type table type possible_keys key key_len ref rows Extra 25231 SIMPLE t2 ALL NULL NULL NULL NULL 20 Using temporary; Using filesort 25241 SIMPLE t1 ref col_int_key col_int_key 4 test.t2.pk 2 NULL 2525SELECT t2.col_varchar_key AS field1 , COUNT(DISTINCT t1.col_varchar_nokey), t2.pk AS field4 2526FROM t1 2527RIGHT JOIN t2 ON t2.pk = t1.col_int_key 2528GROUP BY field1 , field4 2529ORDER BY t1.col_datetime_key ; 2530field1 COUNT(DISTINCT t1.col_varchar_nokey) field4 2531b 0 13 2532c 4 3 2533d 0 19 2534d 1 6 2535d 2 9 2536d 3 5 2537e 0 20 2538g 0 15 2539j 2 1 2540m 0 12 2541m 1 4 2542p 0 16 2543q 0 17 2544r 0 11 2545s 0 10 2546t 0 8 2547v 1 2 2548w 0 18 2549x 0 14 2550y 2 7 2551DROP TABLE t1,t2; 2552 2553# BUG#12619868 "JCL: MORE ROWS OF OUTPUT WHEN JCL>=5" 2554 2555CREATE TABLE t1 (col_varchar_key varchar(1)); 2556CREATE TABLE t2 ( 2557pk int(11) NOT NULL, 2558col_int_nokey int(11) NOT NULL, 2559col_int_key int(11) NOT NULL, 2560PRIMARY KEY (pk), 2561KEY col_int_key (col_int_key) 2562); 2563INSERT INTO t2 VALUES (5,3,9); 2564INSERT INTO t2 VALUES (6,246,24); 2565INSERT INTO t2 VALUES (7,2,6); 2566INSERT INTO t2 VALUES (8,9,1); 2567INSERT INTO t2 VALUES (9,3,6); 2568INSERT INTO t2 VALUES (10,8,2); 2569INSERT INTO t2 VALUES (11,1,4); 2570INSERT INTO t2 VALUES (12,8,8); 2571INSERT INTO t2 VALUES (13,8,4); 2572INSERT INTO t2 VALUES (14,5,4); 2573INSERT INTO t2 VALUES (15,7,7); 2574INSERT INTO t2 VALUES (16,5,4); 2575INSERT INTO t2 VALUES (17,1,1); 2576INSERT INTO t2 VALUES (18,6,9); 2577INSERT INTO t2 VALUES (19,2,4); 2578INSERT INTO t2 VALUES (20,9,8); 2579explain SELECT t1.col_varchar_key AS field1, alias2.col_int_key AS field4 2580FROM t2 AS alias2 STRAIGHT_JOIN t2 AS alias3 ON alias3.pk = 2581alias2.col_int_nokey 2582left join t1 2583ON alias3.col_int_nokey 2584GROUP BY field1, field4 2585LIMIT 15; 2586id select_type table type possible_keys key key_len ref rows Extra 25871 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found 25881 SIMPLE alias2 index NULL col_int_key 4 NULL 15 NULL 25891 SIMPLE alias3 eq_ref PRIMARY PRIMARY 4 test.alias2.col_int_nokey 1 NULL 2590SELECT t1.col_varchar_key AS field1, alias2.col_int_key AS field4 2591FROM t2 AS alias2 STRAIGHT_JOIN t2 AS alias3 ON alias3.pk = 2592alias2.col_int_nokey 2593left join t1 2594ON alias3.col_int_nokey 2595GROUP BY field1, field4 2596LIMIT 15; 2597field1 field4 2598NULL 1 2599NULL 2 2600NULL 4 2601NULL 7 2602NULL 8 2603NULL 9 2604DROP TABLE t1,t2; 2605 2606# BUG#12722133 - JCL: JOIN QUERY GIVES DIFFERENT RESULTS AT 2607# JCL=6 ONLY [NULL VERSUS NULL+#INTS] 2608 2609CREATE TABLE t1 (pk INTEGER PRIMARY KEY, k INTEGER, i INTEGER, KEY k(k)); 2610CREATE TABLE t2 LIKE t1; 2611CREATE TABLE t3 LIKE t1; 2612CREATE TABLE t4 LIKE t1; 2613INSERT INTO t1 VALUES (6,NULL,6),(0,1,11); 2614INSERT INTO t2 VALUES (1,NULL,NULL),(4,7,NULL); 2615INSERT INTO t3 VALUES (2,3,0),(3,4,4); 2616INSERT INTO t4 VALUES (1,9,-1),(4,7,NULL); 2617EXPLAIN SELECT t2.pk as t2_pk, t4.pk as t4_pk, t4.k as t4_k, t4.i 2618as t4_i FROM t1 2619LEFT JOIN t2 ON t1.k = t2.pk 2620LEFT JOIN t3 ON t3.i 2621LEFT JOIN t4 ON t4.pk = t2.pk; 2622id select_type table type possible_keys key key_len ref rows Extra 26231 SIMPLE t1 index NULL k 5 NULL 2 Using index 26241 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.k 1 Using index 26251 SIMPLE t3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop) 26261 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t2.pk 1 NULL 2627SELECT t2.pk as t2_pk, t4.pk as t4_pk, t4.k as t4_k, t4.i 2628as t4_i FROM t1 2629LEFT JOIN t2 ON t1.k = t2.pk 2630LEFT JOIN t3 ON t3.i 2631LEFT JOIN t4 ON t4.pk = t2.pk; 2632t2_pk t4_pk t4_k t4_i 2633NULL NULL NULL NULL 26341 1 9 -1 2635DROP TABLE t1, t2, t3, t4; 2636 2637# BUG#12827509 - BNL/BKA: SELECT LEFT/RIGHT JOIN QUERY GIVES 2638# DIFFERENT OUTPUT ON BNL=OFF+BKA=ON 2639# (Duplicate of BUG#12722133) 2640 2641CREATE TABLE t1 ( 2642col_int INTEGER 2643); 2644INSERT INTO t1 VALUES (3), (7), (2), (8), (6); 2645CREATE TABLE t2 ( 2646pk INTEGER, 2647col_int INTEGER, 2648PRIMARY KEY (pk) 2649); 2650INSERT INTO t2 VALUES (1,5), (2,8), (6,3), (8,7), (9,9); 2651CREATE TABLE t3 ( 2652pk INTEGER, 2653col_int INTEGER, 2654PRIMARY KEY (pk) 2655); 2656INSERT INTO t3 VALUES (3,2), (4,3), (8,2); 2657CREATE TABLE t4 ( 2658pk INTEGER, 2659col_int INTEGER, 2660PRIMARY KEY (pk) 2661); 2662INSERT INTO t4 VALUES (2,3), (6,1), (8,2); 2663EXPLAIN SELECT t4.col_int 2664FROM t1 2665LEFT JOIN t2 ON t1.col_int = t2.col_int 2666LEFT JOIN t3 ON t2.pk = t3.pk 2667LEFT JOIN t4 ON t4.pk = t2.pk 2668WHERE t1.col_int OR t3.col_int; 2669id select_type table type possible_keys key key_len ref rows Extra 26701 SIMPLE t1 ALL NULL NULL NULL NULL 5 NULL 26711 SIMPLE t2 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (Block Nested Loop) 26721 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.pk 1 Using where 26731 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t2.pk 1 NULL 2674SELECT t4.col_int 2675FROM t1 2676LEFT JOIN t2 ON t1.col_int = t2.col_int 2677LEFT JOIN t3 ON t2.pk = t3.pk 2678LEFT JOIN t4 ON t4.pk = t2.pk 2679WHERE t1.col_int OR t3.col_int; 2680col_int 26813 26821 26832 2684NULL 2685NULL 2686DROP TABLE t1, t2, t3, t4; 2687# 2688# Bug#12997905: VALGRIND: SYSCALL PARAM PWRITE64(BUF) 2689# POINTS TO UNINITIALISED BYTE(S) 2690# 2691CREATE TABLE t1 ( 2692col1 varchar(10), 2693col2 varchar(1024) 2694) ENGINE=innodb; 2695INSERT INTO t1 VALUES ('a','a'); 2696CREATE TABLE t2 (i varchar(10)) ENGINE=innodb; 2697INSERT INTO t2 VALUES ('a'); 2698SELECT t1.col1 2699FROM t1 JOIN t2 ON t1.col1 = t2.i 2700GROUP BY t1.col2; 2701col1 2702a 2703DROP TABLE t1,t2; 2704# End of Bug#12997905 2705# 2706# Bug 13596330 - EXTRA ROW ON SELECT WITH NESTED IN CLAUSE + IS 2707# NULL WHEN SEMIJOIN + BNL IS ON 2708# 2709CREATE TABLE t1 ( 2710col_int_nokey int 2711); 2712INSERT INTO t1 VALUES(-1),(-1); 2713CREATE TABLE t2 ( 2714col_int_nokey int, 2715col_datetime_nokey datetime NOT NULL, 2716col_varchar_key varchar(1), 2717KEY col_varchar_key (col_varchar_key) 2718); 2719INSERT INTO t2 VALUES (9, '2002-08-25 20:35:06', 'e'), 2720(9, '2002-08-25 20:35:06', 'e'); 2721set @optimizer_switch_saved=@@session.optimizer_switch; 2722set @@session.optimizer_switch='semijoin=off'; 2723EXPLAIN SELECT PARENT1.col_varchar_key 2724FROM t2 AS PARENT1 LEFT JOIN t1 USING (col_int_nokey) 2725WHERE PARENT1.col_varchar_key IN 2726( SELECT col_varchar_key FROM t2 AS CHILD1 2727WHERE PARENT1.col_datetime_nokey IS NULL 2728AND t1.col_int_nokey IS NULL ) 2729; 2730id select_type table type possible_keys key key_len ref rows Extra 27311 PRIMARY PARENT1 ALL NULL NULL NULL NULL 2 NULL 27321 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop) 27332 DEPENDENT SUBQUERY CHILD1 index_subquery col_varchar_key col_varchar_key 4 func 2 Using index; Using where 2734SELECT PARENT1.col_varchar_key 2735FROM t2 AS PARENT1 LEFT JOIN t1 USING (col_int_nokey) 2736WHERE PARENT1.col_varchar_key IN 2737( SELECT col_varchar_key FROM t2 AS CHILD1 2738WHERE PARENT1.col_datetime_nokey IS NULL 2739AND t1.col_int_nokey IS NULL ) 2740; 2741col_varchar_key 2742set @@session.optimizer_switch=@optimizer_switch_saved; 2743DROP TABLE t1,t2; 2744set optimizer_switch = default; 2745