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