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 partitions type possible_keys key key_len ref rows filtered Extra 421 SIMPLE country NULL ALL NULL NULL NULL NULL 239 11.11 Using where 431 SIMPLE city NULL ALL NULL NULL NULL NULL 4079 3.33 Using where; Using join buffer (Block Nested Loop) 44Warnings: 45Note 1003 /* select#1 */ select `world`.`city`.`Name` AS `Name`,`world`.`country`.`Name` AS `Name` from `world`.`city` join `world`.`country` where ((`world`.`city`.`Country` = `world`.`country`.`Code`) and (`world`.`country`.`Name` like 'L%') and (`world`.`city`.`Population` > 100000)) 46SELECT city.Name, country.Name FROM city,country 47WHERE city.Country=country.Code AND 48country.Name LIKE 'L%' AND city.Population > 100000; 49Name Name 50?iauliai Lithuania 51Beirut Lebanon 52Bengasi Libyan Arab Jamahiriya 53Daugavpils Latvia 54Kaunas Lithuania 55Klaipeda Lithuania 56Maseru Lesotho 57Misrata Libyan Arab Jamahiriya 58Monrovia Liberia 59Panevezys Lithuania 60Riga Latvia 61Tripoli Lebanon 62Tripoli Libyan Arab Jamahiriya 63Vientiane Laos 64Vilnius Lithuania 65EXPLAIN 66SELECT city.Name, country.Name, countrylanguage.Language 67FROM city,country,countrylanguage 68WHERE city.Country=country.Code AND 69countrylanguage.Country=country.Code AND 70city.Name LIKE 'L%' AND country.Population > 3000000 AND 71countrylanguage.Percentage > 50; 72id select_type table partitions type possible_keys key key_len ref rows filtered Extra 731 SIMPLE country NULL ALL NULL NULL NULL NULL 239 33.33 Using where 741 SIMPLE countrylanguage NULL ALL NULL NULL NULL NULL 984 3.33 Using where; Using join buffer (Block Nested Loop) 751 SIMPLE city NULL ALL NULL NULL NULL NULL 4079 1.11 Using where; Using join buffer (Block Nested Loop) 76Warnings: 77Note 1003 /* select#1 */ select `world`.`city`.`Name` AS `Name`,`world`.`country`.`Name` AS `Name`,`world`.`countrylanguage`.`Language` AS `Language` from `world`.`city` join `world`.`country` join `world`.`countrylanguage` where ((`world`.`countrylanguage`.`Country` = `world`.`country`.`Code`) and (`world`.`city`.`Country` = `world`.`country`.`Code`) and (`world`.`city`.`Name` like 'L%') and (`world`.`country`.`Population` > 3000000) and (`world`.`countrylanguage`.`Percentage` > 50)) 78SELECT city.Name, country.Name, countrylanguage.Language 79FROM city,country,countrylanguage 80WHERE city.Country=country.Code AND 81countrylanguage.Country=country.Code AND 82city.Name LIKE 'L%' AND country.Population > 3000000 AND 83countrylanguage.Percentage > 50; 84Name Name Language 85La Ceiba Honduras Spanish 86La Habana Cuba Spanish 87La Matanza Argentina Spanish 88La Paz Bolivia Spanish 89La Paz Mexico Spanish 90La Paz Mexico Spanish 91La Plata Argentina Spanish 92La Rioja Argentina Spanish 93La Romana Dominican Republic Spanish 94La Serena Chile Spanish 95La Spezia Italy Italian 96Lafayette United States English 97Lages Brazil Portuguese 98Lagos de Moreno Mexico Spanish 99Lahti Finland Finnish 100Laiwu China Chinese 101Laiyang China Chinese 102Laizhou China Chinese 103Lakewood United States English 104Lalitapur Nepal Nepali 105Lambaré Paraguay Spanish 106Lancaster United States English 107Langfang China Chinese 108Lansing United States English 109Lanzhou China Chinese 110Lanús Argentina Spanish 111Laohekou China Chinese 112Laredo United States English 113Larisa Greece Greek 114Las Heras Argentina Spanish 115Las Margaritas Mexico Spanish 116Las Palmas de Gran Canaria Spain Spanish 117Las Vegas United States English 118Lashio (Lasho) Myanmar Burmese 119Latakia Syria Arabic 120Latina Italy Italian 121Lauro de Freitas Brazil Portuguese 122Lausanne Switzerland German 123Laval Canada English 124Le Havre France French 125Le Mans France French 126Le-Cap-Haïtien Haiti Haiti Creole 127Lecce Italy Italian 128Leeds United Kingdom English 129Leganés Spain Spanish 130Legnica Poland Polish 131Leicester United Kingdom English 132Leiden Netherlands Dutch 133Leipzig Germany German 134Leiyang China Chinese 135Lengshuijiang China Chinese 136Leninsk-Kuznetski Russian Federation Russian 137Lerdo Mexico Spanish 138Lerma Mexico Spanish 139Leshan China Chinese 140Leverkusen Germany German 141Lexington-Fayette United States English 142León Mexico Spanish 143León Nicaragua Spanish 144León Spain Spanish 145Lhasa China Chinese 146Liangcheng China Chinese 147Lianyuan China Chinese 148Lianyungang China Chinese 149Liaocheng China Chinese 150Liaoyang China Chinese 151Liaoyuan China Chinese 152Liberec Czech Republic Czech 153Lida Belarus Belorussian 154Liling China Chinese 155Lille France French 156Lilongwe Malawi Chichewa 157Lima Peru Spanish 158Limeira Brazil Portuguese 159Limoges France French 160Linchuan China Chinese 161Lincoln United States English 162Linfen China Chinese 163Linhai China Chinese 164Linhares Brazil Portuguese 165Linhe China Chinese 166Linköping Sweden Swedish 167Linqing China Chinese 168Linyi China Chinese 169Linz Austria German 170Lipetsk Russian Federation Russian 171Lisboa Portugal Portuguese 172Little Rock United States English 173Liupanshui China Chinese 174Liuzhou China Chinese 175Liu´an China Chinese 176Liverpool United Kingdom English 177Livonia United States English 178Livorno Italy Italian 179Liyang China Chinese 180Liège Belgium Dutch 181Ljubertsy Russian Federation Russian 182Lleida (Lérida) Spain Spanish 183Logroño Spain Spanish 184Loja Ecuador Spanish 185Lomas de Zamora Argentina Spanish 186London Canada English 187London United Kingdom English 188Londrina Brazil Portuguese 189Long Beach United States English 190Long Xuyen Vietnam Vietnamese 191Longjing China Chinese 192Longkou China Chinese 193Longueuil Canada English 194Longyan China Chinese 195Los Angeles Chile Spanish 196Los Angeles United States English 197Los Cabos Mexico Spanish 198Los Teques Venezuela Spanish 199Loudi China Chinese 200Louisville United States English 201Lowell United States English 202Lower Hutt New Zealand English 203Lubbock United States English 204Lublin Poland Polish 205Luchou Taiwan Min 206Ludwigshafen am Rhein Germany German 207Lugansk Ukraine Ukrainian 208Lund Sweden Swedish 209Lungtan Taiwan Min 210Luohe China Chinese 211Luoyang China Chinese 212Luton United Kingdom English 213Lutsk Ukraine Ukrainian 214Luxor Egypt Arabic 215Luzhou China Chinese 216Luziânia Brazil Portuguese 217Lviv Ukraine Ukrainian 218Lyon France French 219Lysyt?ansk Ukraine Ukrainian 220L´Hospitalet de Llobregat Spain Spanish 221Lázaro Cárdenas Mexico Spanish 222Lódz Poland Polish 223Lübeck Germany German 224Lünen Germany German 225set join_buffer_size=256; 226show variables like 'join_buffer_size'; 227Variable_name Value 228join_buffer_size 256 229EXPLAIN 230SELECT city.Name, country.Name FROM city,country 231WHERE city.Country=country.Code AND 232country.Name LIKE 'L%' AND city.Population > 100000; 233id select_type table partitions type possible_keys key key_len ref rows filtered Extra 2341 SIMPLE country NULL ALL NULL NULL NULL NULL 239 11.11 Using where 2351 SIMPLE city NULL ALL NULL NULL NULL NULL 4079 3.33 Using where; Using join buffer (Block Nested Loop) 236Warnings: 237Note 1003 /* select#1 */ select `world`.`city`.`Name` AS `Name`,`world`.`country`.`Name` AS `Name` from `world`.`city` join `world`.`country` where ((`world`.`city`.`Country` = `world`.`country`.`Code`) and (`world`.`country`.`Name` like 'L%') and (`world`.`city`.`Population` > 100000)) 238SELECT city.Name, country.Name FROM city,country 239WHERE city.Country=country.Code AND 240country.Name LIKE 'L%' AND city.Population > 100000; 241Name Name 242?iauliai Lithuania 243Beirut Lebanon 244Bengasi Libyan Arab Jamahiriya 245Daugavpils Latvia 246Kaunas Lithuania 247Klaipeda Lithuania 248Maseru Lesotho 249Misrata Libyan Arab Jamahiriya 250Monrovia Liberia 251Panevezys Lithuania 252Riga Latvia 253Tripoli Lebanon 254Tripoli Libyan Arab Jamahiriya 255Vientiane Laos 256Vilnius Lithuania 257EXPLAIN 258SELECT city.Name, country.Name, countrylanguage.Language 259FROM city,country,countrylanguage 260WHERE city.Country=country.Code AND 261countrylanguage.Country=country.Code AND 262city.Name LIKE 'L%' AND country.Population > 3000000 AND 263countrylanguage.Percentage > 50; 264id select_type table partitions type possible_keys key key_len ref rows filtered Extra 2651 SIMPLE country NULL ALL NULL NULL NULL NULL 239 33.33 Using where 2661 SIMPLE countrylanguage NULL ALL NULL NULL NULL NULL 984 3.33 Using where; Using join buffer (Block Nested Loop) 2671 SIMPLE city NULL ALL NULL NULL NULL NULL 4079 1.11 Using where; Using join buffer (Block Nested Loop) 268Warnings: 269Note 1003 /* select#1 */ select `world`.`city`.`Name` AS `Name`,`world`.`country`.`Name` AS `Name`,`world`.`countrylanguage`.`Language` AS `Language` from `world`.`city` join `world`.`country` join `world`.`countrylanguage` where ((`world`.`countrylanguage`.`Country` = `world`.`country`.`Code`) and (`world`.`city`.`Country` = `world`.`country`.`Code`) and (`world`.`city`.`Name` like 'L%') and (`world`.`country`.`Population` > 3000000) and (`world`.`countrylanguage`.`Percentage` > 50)) 270SELECT city.Name, country.Name, countrylanguage.Language 271FROM city,country,countrylanguage 272WHERE city.Country=country.Code AND 273countrylanguage.Country=country.Code AND 274city.Name LIKE 'L%' AND country.Population > 3000000 AND 275countrylanguage.Percentage > 50; 276Name Name Language 277La Ceiba Honduras Spanish 278La Habana Cuba Spanish 279La Matanza Argentina Spanish 280La Paz Bolivia Spanish 281La Paz Mexico Spanish 282La Paz Mexico Spanish 283La Plata Argentina Spanish 284La Rioja Argentina Spanish 285La Romana Dominican Republic Spanish 286La Serena Chile Spanish 287La Spezia Italy Italian 288Lafayette United States English 289Lages Brazil Portuguese 290Lagos de Moreno Mexico Spanish 291Lahti Finland Finnish 292Laiwu China Chinese 293Laiyang China Chinese 294Laizhou China Chinese 295Lakewood United States English 296Lalitapur Nepal Nepali 297Lambaré Paraguay Spanish 298Lancaster United States English 299Langfang China Chinese 300Lansing United States English 301Lanzhou China Chinese 302Lanús Argentina Spanish 303Laohekou China Chinese 304Laredo United States English 305Larisa Greece Greek 306Las Heras Argentina Spanish 307Las Margaritas Mexico Spanish 308Las Palmas de Gran Canaria Spain Spanish 309Las Vegas United States English 310Lashio (Lasho) Myanmar Burmese 311Latakia Syria Arabic 312Latina Italy Italian 313Lauro de Freitas Brazil Portuguese 314Lausanne Switzerland German 315Laval Canada English 316Le Havre France French 317Le Mans France French 318Le-Cap-Haïtien Haiti Haiti Creole 319Lecce Italy Italian 320Leeds United Kingdom English 321Leganés Spain Spanish 322Legnica Poland Polish 323Leicester United Kingdom English 324Leiden Netherlands Dutch 325Leipzig Germany German 326Leiyang China Chinese 327Lengshuijiang China Chinese 328Leninsk-Kuznetski Russian Federation Russian 329Lerdo Mexico Spanish 330Lerma Mexico Spanish 331Leshan China Chinese 332Leverkusen Germany German 333Lexington-Fayette United States English 334León Mexico Spanish 335León Nicaragua Spanish 336León Spain Spanish 337Lhasa China Chinese 338Liangcheng China Chinese 339Lianyuan China Chinese 340Lianyungang China Chinese 341Liaocheng China Chinese 342Liaoyang China Chinese 343Liaoyuan China Chinese 344Liberec Czech Republic Czech 345Lida Belarus Belorussian 346Liling China Chinese 347Lille France French 348Lilongwe Malawi Chichewa 349Lima Peru Spanish 350Limeira Brazil Portuguese 351Limoges France French 352Linchuan China Chinese 353Lincoln United States English 354Linfen China Chinese 355Linhai China Chinese 356Linhares Brazil Portuguese 357Linhe China Chinese 358Linköping Sweden Swedish 359Linqing China Chinese 360Linyi China Chinese 361Linz Austria German 362Lipetsk Russian Federation Russian 363Lisboa Portugal Portuguese 364Little Rock United States English 365Liupanshui China Chinese 366Liuzhou China Chinese 367Liu´an China Chinese 368Liverpool United Kingdom English 369Livonia United States English 370Livorno Italy Italian 371Liyang China Chinese 372Liège Belgium Dutch 373Ljubertsy Russian Federation Russian 374Lleida (Lérida) Spain Spanish 375Logroño Spain Spanish 376Loja Ecuador Spanish 377Lomas de Zamora Argentina Spanish 378London Canada English 379London United Kingdom English 380Londrina Brazil Portuguese 381Long Beach United States English 382Long Xuyen Vietnam Vietnamese 383Longjing China Chinese 384Longkou China Chinese 385Longueuil Canada English 386Longyan China Chinese 387Los Angeles Chile Spanish 388Los Angeles United States English 389Los Cabos Mexico Spanish 390Los Teques Venezuela Spanish 391Loudi China Chinese 392Louisville United States English 393Lowell United States English 394Lower Hutt New Zealand English 395Lubbock United States English 396Lublin Poland Polish 397Luchou Taiwan Min 398Ludwigshafen am Rhein Germany German 399Lugansk Ukraine Ukrainian 400Lund Sweden Swedish 401Lungtan Taiwan Min 402Luohe China Chinese 403Luoyang China Chinese 404Luton United Kingdom English 405Lutsk Ukraine Ukrainian 406Luxor Egypt Arabic 407Luzhou China Chinese 408Luziânia Brazil Portuguese 409Lviv Ukraine Ukrainian 410Lyon France French 411Lysyt?ansk Ukraine Ukrainian 412L´Hospitalet de Llobregat Spain Spanish 413Lázaro Cárdenas Mexico Spanish 414Lódz Poland Polish 415Lübeck Germany German 416Lünen Germany German 417set join_buffer_size=default; 418show variables like 'join_buffer_size'; 419Variable_name Value 420join_buffer_size 262144 421DROP DATABASE world; 422CREATE DATABASE world; 423use world; 424CREATE TABLE country ( 425Code char(3) NOT NULL default '', 426Name char(52) NOT NULL default '', 427SurfaceArea float(10,2) NOT NULL default '0.00', 428Population int(11) NOT NULL default '0', 429Capital int(11) default NULL, 430PRIMARY KEY (Code), 431UNIQUE INDEX (Name) 432); 433CREATE TABLE city ( 434ID int(11) NOT NULL auto_increment, 435Name char(35) NOT NULL default '', 436Country char(3) NOT NULL default '', 437Population int(11) NOT NULL default '0', 438PRIMARY KEY (ID), 439INDEX (Population), 440INDEX (Country) 441); 442CREATE TABLE countrylanguage ( 443Country char(3) NOT NULL default '', 444Language char(30) NOT NULL default '', 445Percentage float(3,1) NOT NULL default '0.0', 446PRIMARY KEY (Country, Language), 447INDEX (Percentage) 448); 449show variables like 'join_buffer_size'; 450Variable_name Value 451join_buffer_size 262144 452EXPLAIN 453SELECT city.Name, country.Name FROM city,country 454WHERE city.country=country.Code AND 455country.Name LIKE 'L%' AND city.Population > 100000; 456id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4571 SIMPLE country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Using MRR 4581 SIMPLE city NULL ref Population,Country Country 3 world.country.Code 18 87.57 Using where; Using join buffer (Batched Key Access) 459Warnings: 460Note 1003 /* select#1 */ select `world`.`city`.`Name` AS `Name`,`world`.`country`.`Name` AS `Name` from `world`.`city` join `world`.`country` where ((`world`.`city`.`Country` = `world`.`country`.`Code`) and (`world`.`country`.`Name` like 'L%') and (`world`.`city`.`Population` > 100000)) 461SELECT city.Name, country.Name FROM city,country 462WHERE city.country=country.Code AND 463country.Name LIKE 'L%' AND city.Population > 100000; 464Name Name 465?iauliai Lithuania 466Beirut Lebanon 467Bengasi Libyan Arab Jamahiriya 468Daugavpils Latvia 469Kaunas Lithuania 470Klaipeda Lithuania 471Maseru Lesotho 472Misrata Libyan Arab Jamahiriya 473Monrovia Liberia 474Panevezys Lithuania 475Riga Latvia 476Tripoli Lebanon 477Tripoli Libyan Arab Jamahiriya 478Vientiane Laos 479Vilnius Lithuania 480EXPLAIN 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; 487id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4881 SIMPLE countrylanguage NULL range PRIMARY,Percentage Percentage 4 NULL # 100.00 Using index condition; Using MRR 4891 SIMPLE country NULL eq_ref PRIMARY PRIMARY 3 world.countrylanguage.Country 1 33.33 Using where; Using join buffer (Batched Key Access) 4901 SIMPLE city NULL ref Country Country 3 world.countrylanguage.Country 18 11.11 Using where; Using join buffer (Batched Key Access) 491Warnings: 492Note 1003 /* select#1 */ select `world`.`city`.`Name` AS `Name`,`world`.`country`.`Name` AS `Name`,`world`.`countrylanguage`.`Language` AS `Language` from `world`.`city` join `world`.`country` join `world`.`countrylanguage` where ((`world`.`country`.`Code` = `world`.`countrylanguage`.`Country`) and (`world`.`city`.`Country` = `world`.`countrylanguage`.`Country`) and (`world`.`city`.`Name` like 'L%') and (`world`.`country`.`Population` > 3000000) and (`world`.`countrylanguage`.`Percentage` > 50)) 493SELECT city.Name, country.Name, countrylanguage.Language 494FROM city,country,countrylanguage 495WHERE city.country=country.Code AND 496countrylanguage.country=country.Code AND 497city.Name LIKE 'L%' AND country.Population > 3000000 AND 498countrylanguage.Percentage > 50; 499Name Name Language 500La Ceiba Honduras Spanish 501La Habana Cuba Spanish 502La Matanza Argentina Spanish 503La Paz Bolivia Spanish 504La Paz Mexico Spanish 505La Paz Mexico Spanish 506La Plata Argentina Spanish 507La Rioja Argentina Spanish 508La Romana Dominican Republic Spanish 509La Serena Chile Spanish 510La Spezia Italy Italian 511Lafayette United States English 512Lages Brazil Portuguese 513Lagos de Moreno Mexico Spanish 514Lahti Finland Finnish 515Laiwu China Chinese 516Laiyang China Chinese 517Laizhou China Chinese 518Lakewood United States English 519Lalitapur Nepal Nepali 520Lambaré Paraguay Spanish 521Lancaster United States English 522Langfang China Chinese 523Lansing United States English 524Lanzhou China Chinese 525Lanús Argentina Spanish 526Laohekou China Chinese 527Laredo United States English 528Larisa Greece Greek 529Las Heras Argentina Spanish 530Las Margaritas Mexico Spanish 531Las Palmas de Gran Canaria Spain Spanish 532Las Vegas United States English 533Lashio (Lasho) Myanmar Burmese 534Latakia Syria Arabic 535Latina Italy Italian 536Lauro de Freitas Brazil Portuguese 537Lausanne Switzerland German 538Laval Canada English 539Le Havre France French 540Le Mans France French 541Le-Cap-Haïtien Haiti Haiti Creole 542Lecce Italy Italian 543Leeds United Kingdom English 544Leganés Spain Spanish 545Legnica Poland Polish 546Leicester United Kingdom English 547Leiden Netherlands Dutch 548Leipzig Germany German 549Leiyang China Chinese 550Lengshuijiang China Chinese 551Leninsk-Kuznetski Russian Federation Russian 552Lerdo Mexico Spanish 553Lerma Mexico Spanish 554Leshan China Chinese 555Leverkusen Germany German 556Lexington-Fayette United States English 557León Mexico Spanish 558León Nicaragua Spanish 559León Spain Spanish 560Lhasa China Chinese 561Liangcheng China Chinese 562Lianyuan China Chinese 563Lianyungang China Chinese 564Liaocheng China Chinese 565Liaoyang China Chinese 566Liaoyuan China Chinese 567Liberec Czech Republic Czech 568Lida Belarus Belorussian 569Liling China Chinese 570Lille France French 571Lilongwe Malawi Chichewa 572Lima Peru Spanish 573Limeira Brazil Portuguese 574Limoges France French 575Linchuan China Chinese 576Lincoln United States English 577Linfen China Chinese 578Linhai China Chinese 579Linhares Brazil Portuguese 580Linhe China Chinese 581Linköping Sweden Swedish 582Linqing China Chinese 583Linyi China Chinese 584Linz Austria German 585Lipetsk Russian Federation Russian 586Lisboa Portugal Portuguese 587Little Rock United States English 588Liupanshui China Chinese 589Liuzhou China Chinese 590Liu´an China Chinese 591Liverpool United Kingdom English 592Livonia United States English 593Livorno Italy Italian 594Liyang China Chinese 595Liège Belgium Dutch 596Ljubertsy Russian Federation Russian 597Lleida (Lérida) Spain Spanish 598Logroño Spain Spanish 599Loja Ecuador Spanish 600Lomas de Zamora Argentina Spanish 601London Canada English 602London United Kingdom English 603Londrina Brazil Portuguese 604Long Beach United States English 605Long Xuyen Vietnam Vietnamese 606Longjing China Chinese 607Longkou China Chinese 608Longueuil Canada English 609Longyan China Chinese 610Los Angeles Chile Spanish 611Los Angeles United States English 612Los Cabos Mexico Spanish 613Los Teques Venezuela Spanish 614Loudi China Chinese 615Louisville United States English 616Lowell United States English 617Lower Hutt New Zealand English 618Lubbock United States English 619Lublin Poland Polish 620Luchou Taiwan Min 621Ludwigshafen am Rhein Germany German 622Lugansk Ukraine Ukrainian 623Lund Sweden Swedish 624Lungtan Taiwan Min 625Luohe China Chinese 626Luoyang China Chinese 627Luton United Kingdom English 628Lutsk Ukraine Ukrainian 629Luxor Egypt Arabic 630Luzhou China Chinese 631Luziânia Brazil Portuguese 632Lviv Ukraine Ukrainian 633Lyon France French 634Lysyt?ansk Ukraine Ukrainian 635L´Hospitalet de Llobregat Spain Spanish 636Lázaro Cárdenas Mexico Spanish 637Lódz Poland Polish 638Lübeck Germany German 639Lünen Germany German 640EXPLAIN 641SELECT Name FROM city 642WHERE city.country IN (SELECT Code FROM country WHERE country.Name LIKE 'L%') AND 643city.Population > 100000; 644id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6451 SIMPLE country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Using MRR 6461 SIMPLE city NULL ref Population,Country Country 3 world.country.Code 18 87.57 Using where; Using join buffer (Batched Key Access) 647Warnings: 648Note 1003 /* select#1 */ select `world`.`city`.`Name` AS `Name` from `world`.`country` join `world`.`city` where ((`world`.`city`.`Country` = `world`.`country`.`Code`) and (`world`.`city`.`Population` > 100000) and (`world`.`country`.`Name` like 'L%')) 649SELECT Name FROM city 650WHERE city.country IN (SELECT Code FROM country WHERE country.Name LIKE 'L%') AND 651city.Population > 100000; 652Name 653?iauliai 654Beirut 655Bengasi 656Daugavpils 657Kaunas 658Klaipeda 659Maseru 660Misrata 661Monrovia 662Panevezys 663Riga 664Tripoli 665Tripoli 666Vientiane 667Vilnius 668EXPLAIN 669SELECT country.Name, IF(ISNULL(countrylanguage.country), NULL, countrylanguage.Percentage) 670FROM country LEFT JOIN countrylanguage ON 671(countrylanguage.country=country.Code AND Language='English') 672WHERE 673country.Population > 10000000; 674id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6751 SIMPLE country NULL ALL NULL NULL NULL NULL 239 33.33 Using where 6761 SIMPLE countrylanguage NULL eq_ref PRIMARY PRIMARY 33 world.country.Code,const 1 100.00 Using join buffer (Batched Key Access) 677Warnings: 678Note 1003 /* select#1 */ select `world`.`country`.`Name` AS `Name`,if(isnull(`world`.`countrylanguage`.`Country`),NULL,`world`.`countrylanguage`.`Percentage`) AS `IF(ISNULL(countrylanguage.country), NULL, countrylanguage.Percentage)` from `world`.`country` left join `world`.`countrylanguage` on(((`world`.`countrylanguage`.`Language` = 'English') and (`world`.`countrylanguage`.`Country` = `world`.`country`.`Code`))) where (`world`.`country`.`Population` > 10000000) 679SELECT country.Name, IF(ISNULL(countrylanguage.country), NULL, countrylanguage.Percentage) 680FROM country LEFT JOIN countrylanguage ON 681(countrylanguage.country=country.Code AND Language='English') 682WHERE 683country.Population > 10000000; 684Name IF(ISNULL(countrylanguage.country), NULL, countrylanguage.Percentage) 685Afghanistan NULL 686Algeria NULL 687Angola NULL 688Argentina NULL 689Australia 81.2 690Bangladesh NULL 691Belarus NULL 692Belgium NULL 693Brazil NULL 694Burkina Faso NULL 695Cambodia NULL 696Cameroon NULL 697Canada 60.4 698Chile NULL 699China NULL 700Colombia NULL 701Congo, The Democratic Republic of the NULL 702Cuba NULL 703Czech Republic NULL 704Côte d?Ivoire NULL 705Ecuador NULL 706Egypt NULL 707Ethiopia NULL 708France NULL 709Germany NULL 710Ghana NULL 711Greece NULL 712Guatemala NULL 713Hungary NULL 714India NULL 715Indonesia NULL 716Iran NULL 717Iraq NULL 718Italy NULL 719Japan 0.1 720Kazakstan NULL 721Kenya NULL 722Madagascar NULL 723Malawi NULL 724Malaysia 1.6 725Mali NULL 726Mexico NULL 727Morocco NULL 728Mozambique NULL 729Myanmar NULL 730Nepal NULL 731Netherlands NULL 732Niger NULL 733Nigeria NULL 734North Korea NULL 735Pakistan NULL 736Peru NULL 737Philippines NULL 738Poland NULL 739Romania NULL 740Russian Federation NULL 741Saudi Arabia NULL 742Somalia NULL 743South Africa 8.5 744South Korea NULL 745Spain NULL 746Sri Lanka NULL 747Sudan NULL 748Syria NULL 749Taiwan NULL 750Tanzania NULL 751Thailand NULL 752Turkey NULL 753Uganda NULL 754Ukraine NULL 755United Kingdom 97.3 756United States 86.2 757Uzbekistan NULL 758Venezuela NULL 759Vietnam NULL 760Yemen NULL 761Yugoslavia NULL 762Zimbabwe 2.2 763set join_buffer_size=256; 764show variables like 'join_buffer_size'; 765Variable_name Value 766join_buffer_size 256 767EXPLAIN 768SELECT city.Name, country.Name FROM city,country 769WHERE city.country=country.Code AND 770country.Name LIKE 'L%' AND city.Population > 100000; 771id select_type table partitions type possible_keys key key_len ref rows filtered Extra 7721 SIMPLE country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Using MRR 7731 SIMPLE city NULL ref Population,Country Country 3 world.country.Code 18 87.57 Using where; Using join buffer (Batched Key Access) 774Warnings: 775Note 1003 /* select#1 */ select `world`.`city`.`Name` AS `Name`,`world`.`country`.`Name` AS `Name` from `world`.`city` join `world`.`country` where ((`world`.`city`.`Country` = `world`.`country`.`Code`) and (`world`.`country`.`Name` like 'L%') and (`world`.`city`.`Population` > 100000)) 776SELECT city.Name, country.Name FROM city,country 777WHERE city.country=country.Code AND 778country.Name LIKE 'L%' AND city.Population > 100000; 779Name Name 780?iauliai Lithuania 781Beirut Lebanon 782Bengasi Libyan Arab Jamahiriya 783Daugavpils Latvia 784Kaunas Lithuania 785Klaipeda Lithuania 786Maseru Lesotho 787Misrata Libyan Arab Jamahiriya 788Monrovia Liberia 789Panevezys Lithuania 790Riga Latvia 791Tripoli Lebanon 792Tripoli Libyan Arab Jamahiriya 793Vientiane Laos 794Vilnius Lithuania 795EXPLAIN 796SELECT city.Name, country.Name, countrylanguage.Language 797FROM city,country,countrylanguage 798WHERE city.country=country.Code AND 799countrylanguage.country=country.Code AND 800city.Name LIKE 'L%' AND country.Population > 3000000 AND 801countrylanguage.Percentage > 50; 802id select_type table partitions type possible_keys key key_len ref rows filtered Extra 8031 SIMPLE countrylanguage NULL range PRIMARY,Percentage Percentage 4 NULL # 100.00 Using index condition; Using MRR 8041 SIMPLE country NULL eq_ref PRIMARY PRIMARY 3 world.countrylanguage.Country 1 33.33 Using where; Using join buffer (Batched Key Access) 8051 SIMPLE city NULL ref Country Country 3 world.countrylanguage.Country 18 11.11 Using where; Using join buffer (Batched Key Access) 806Warnings: 807Note 1003 /* select#1 */ select `world`.`city`.`Name` AS `Name`,`world`.`country`.`Name` AS `Name`,`world`.`countrylanguage`.`Language` AS `Language` from `world`.`city` join `world`.`country` join `world`.`countrylanguage` where ((`world`.`country`.`Code` = `world`.`countrylanguage`.`Country`) and (`world`.`city`.`Country` = `world`.`countrylanguage`.`Country`) and (`world`.`city`.`Name` like 'L%') and (`world`.`country`.`Population` > 3000000) and (`world`.`countrylanguage`.`Percentage` > 50)) 808SELECT city.Name, country.Name, countrylanguage.Language 809FROM city,country,countrylanguage 810WHERE city.country=country.Code AND 811countrylanguage.country=country.Code AND 812city.Name LIKE 'L%' AND country.Population > 3000000 AND 813countrylanguage.Percentage > 50; 814Name Name Language 815La Ceiba Honduras Spanish 816La Habana Cuba Spanish 817La Matanza Argentina Spanish 818La Paz Bolivia Spanish 819La Paz Mexico Spanish 820La Paz Mexico Spanish 821La Plata Argentina Spanish 822La Rioja Argentina Spanish 823La Romana Dominican Republic Spanish 824La Serena Chile Spanish 825La Spezia Italy Italian 826Lafayette United States English 827Lages Brazil Portuguese 828Lagos de Moreno Mexico Spanish 829Lahti Finland Finnish 830Laiwu China Chinese 831Laiyang China Chinese 832Laizhou China Chinese 833Lakewood United States English 834Lalitapur Nepal Nepali 835Lambaré Paraguay Spanish 836Lancaster United States English 837Langfang China Chinese 838Lansing United States English 839Lanzhou China Chinese 840Lanús Argentina Spanish 841Laohekou China Chinese 842Laredo United States English 843Larisa Greece Greek 844Las Heras Argentina Spanish 845Las Margaritas Mexico Spanish 846Las Palmas de Gran Canaria Spain Spanish 847Las Vegas United States English 848Lashio (Lasho) Myanmar Burmese 849Latakia Syria Arabic 850Latina Italy Italian 851Lauro de Freitas Brazil Portuguese 852Lausanne Switzerland German 853Laval Canada English 854Le Havre France French 855Le Mans France French 856Le-Cap-Haïtien Haiti Haiti Creole 857Lecce Italy Italian 858Leeds United Kingdom English 859Leganés Spain Spanish 860Legnica Poland Polish 861Leicester United Kingdom English 862Leiden Netherlands Dutch 863Leipzig Germany German 864Leiyang China Chinese 865Lengshuijiang China Chinese 866Leninsk-Kuznetski Russian Federation Russian 867Lerdo Mexico Spanish 868Lerma Mexico Spanish 869Leshan China Chinese 870Leverkusen Germany German 871Lexington-Fayette United States English 872León Mexico Spanish 873León Nicaragua Spanish 874León Spain Spanish 875Lhasa China Chinese 876Liangcheng China Chinese 877Lianyuan China Chinese 878Lianyungang China Chinese 879Liaocheng China Chinese 880Liaoyang China Chinese 881Liaoyuan China Chinese 882Liberec Czech Republic Czech 883Lida Belarus Belorussian 884Liling China Chinese 885Lille France French 886Lilongwe Malawi Chichewa 887Lima Peru Spanish 888Limeira Brazil Portuguese 889Limoges France French 890Linchuan China Chinese 891Lincoln United States English 892Linfen China Chinese 893Linhai China Chinese 894Linhares Brazil Portuguese 895Linhe China Chinese 896Linköping Sweden Swedish 897Linqing China Chinese 898Linyi China Chinese 899Linz Austria German 900Lipetsk Russian Federation Russian 901Lisboa Portugal Portuguese 902Little Rock United States English 903Liupanshui China Chinese 904Liuzhou China Chinese 905Liu´an China Chinese 906Liverpool United Kingdom English 907Livonia United States English 908Livorno Italy Italian 909Liyang China Chinese 910Liège Belgium Dutch 911Ljubertsy Russian Federation Russian 912Lleida (Lérida) Spain Spanish 913Logroño Spain Spanish 914Loja Ecuador Spanish 915Lomas de Zamora Argentina Spanish 916London Canada English 917London United Kingdom English 918Londrina Brazil Portuguese 919Long Beach United States English 920Long Xuyen Vietnam Vietnamese 921Longjing China Chinese 922Longkou China Chinese 923Longueuil Canada English 924Longyan China Chinese 925Los Angeles Chile Spanish 926Los Angeles United States English 927Los Cabos Mexico Spanish 928Los Teques Venezuela Spanish 929Loudi China Chinese 930Louisville United States English 931Lowell United States English 932Lower Hutt New Zealand English 933Lubbock United States English 934Lublin Poland Polish 935Luchou Taiwan Min 936Ludwigshafen am Rhein Germany German 937Lugansk Ukraine Ukrainian 938Lund Sweden Swedish 939Lungtan Taiwan Min 940Luohe China Chinese 941Luoyang China Chinese 942Luton United Kingdom English 943Lutsk Ukraine Ukrainian 944Luxor Egypt Arabic 945Luzhou China Chinese 946Luziânia Brazil Portuguese 947Lviv Ukraine Ukrainian 948Lyon France French 949Lysyt?ansk Ukraine Ukrainian 950L´Hospitalet de Llobregat Spain Spanish 951Lázaro Cárdenas Mexico Spanish 952Lódz Poland Polish 953Lübeck Germany German 954Lünen Germany German 955EXPLAIN 956SELECT Name FROM city 957WHERE city.country IN (SELECT Code FROM country WHERE country.Name LIKE 'L%') AND 958city.Population > 100000; 959id select_type table partitions type possible_keys key key_len ref rows filtered Extra 9601 SIMPLE country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Using MRR 9611 SIMPLE city NULL ref Population,Country Country 3 world.country.Code 18 87.57 Using where; Using join buffer (Batched Key Access) 962Warnings: 963Note 1003 /* select#1 */ select `world`.`city`.`Name` AS `Name` from `world`.`country` join `world`.`city` where ((`world`.`city`.`Country` = `world`.`country`.`Code`) and (`world`.`city`.`Population` > 100000) and (`world`.`country`.`Name` like 'L%')) 964SELECT Name FROM city 965WHERE city.country IN (SELECT Code FROM country WHERE country.Name LIKE 'L%') AND 966city.Population > 100000; 967Name 968?iauliai 969Beirut 970Bengasi 971Daugavpils 972Kaunas 973Klaipeda 974Maseru 975Misrata 976Monrovia 977Panevezys 978Riga 979Tripoli 980Tripoli 981Vientiane 982Vilnius 983set join_buffer_size=default; 984show variables like 'join_buffer_size'; 985Variable_name Value 986join_buffer_size 262144 987SELECT city.Name, country.Name FROM city,country 988WHERE city.country=country.Code AND city.Population > 3000000; 989Name Name 990Alexandria Egypt 991Ankara Turkey 992Baghdad Iraq 993Bangkok Thailand 994Berlin Germany 995Cairo Egypt 996Calcutta [Kolkata] India 997Chengdu China 998Chennai (Madras) India 999Chongqing China 1000Ciudad de México Mexico 1001Delhi India 1002Dhaka Bangladesh 1003Harbin China 1004Ho Chi Minh City Vietnam 1005Istanbul Turkey 1006Jakarta Indonesia 1007Jokohama [Yokohama] Japan 1008Kanton [Guangzhou] China 1009Karachi Pakistan 1010Kinshasa Congo, The Democratic Republic of the 1011Lahore Pakistan 1012Lima Peru 1013London United Kingdom 1014Los Angeles United States 1015Moscow Russian Federation 1016Mumbai (Bombay) India 1017New York United States 1018Peking China 1019Pusan South Korea 1020Rangoon (Yangon) Myanmar 1021Rio de Janeiro Brazil 1022Riyadh Saudi Arabia 1023Santafé de Bogotá Colombia 1024Santiago de Chile Chile 1025Seoul South Korea 1026Shanghai China 1027Shenyang China 1028Singapore Singapore 1029St Petersburg Russian Federation 1030Sydney Australia 1031São Paulo Brazil 1032Teheran Iran 1033Tianjin China 1034Tokyo Japan 1035Wuhan China 1036set join_buffer_size=256; 1037EXPLAIN 1038SELECT city.Name, country.Name FROM city,country 1039WHERE city.country=country.Code AND city.Population > 3000000; 1040id select_type table partitions type possible_keys key key_len ref rows filtered Extra 10411 SIMPLE city NULL range Population,Country Population 4 NULL # 100.00 Using index condition; Using MRR 10421 SIMPLE country NULL eq_ref PRIMARY PRIMARY 3 world.city.Country # 100.00 Using join buffer (Batched Key Access) 1043Warnings: 1044Note 1003 /* select#1 */ select `world`.`city`.`Name` AS `Name`,`world`.`country`.`Name` AS `Name` from `world`.`city` join `world`.`country` where ((`world`.`country`.`Code` = `world`.`city`.`Country`) and (`world`.`city`.`Population` > 3000000)) 1045SELECT city.Name, country.Name FROM city,country 1046WHERE city.country=country.Code AND city.Population > 3000000; 1047Name Name 1048Alexandria Egypt 1049Ankara Turkey 1050Baghdad Iraq 1051Bangkok Thailand 1052Berlin Germany 1053Cairo Egypt 1054Calcutta [Kolkata] India 1055Chengdu China 1056Chennai (Madras) India 1057Chongqing China 1058Ciudad de México Mexico 1059Delhi India 1060Dhaka Bangladesh 1061Harbin China 1062Ho Chi Minh City Vietnam 1063Istanbul Turkey 1064Jakarta Indonesia 1065Jokohama [Yokohama] Japan 1066Kanton [Guangzhou] China 1067Karachi Pakistan 1068Kinshasa Congo, The Democratic Republic of the 1069Lahore Pakistan 1070Lima Peru 1071London United Kingdom 1072Los Angeles United States 1073Moscow Russian Federation 1074Mumbai (Bombay) India 1075New York United States 1076Peking China 1077Pusan South Korea 1078Rangoon (Yangon) Myanmar 1079Rio de Janeiro Brazil 1080Riyadh Saudi Arabia 1081Santafé de Bogotá Colombia 1082Santiago de Chile Chile 1083Seoul South Korea 1084Shanghai China 1085Shenyang China 1086Singapore Singapore 1087St Petersburg Russian Federation 1088Sydney Australia 1089São Paulo Brazil 1090Teheran Iran 1091Tianjin China 1092Tokyo Japan 1093Wuhan China 1094set join_buffer_size=default; 1095ALTER TABLE country MODIFY Name varchar(52) NOT NULL default ''; 1096SELECT city.Name, country.Name FROM city,country 1097WHERE city.country=country.Code AND 1098country.Name LIKE 'L%' AND city.Population > 100000; 1099Name Name 1100?iauliai Lithuania 1101Beirut Lebanon 1102Bengasi Libyan Arab Jamahiriya 1103Daugavpils Latvia 1104Kaunas Lithuania 1105Klaipeda Lithuania 1106Maseru Lesotho 1107Misrata Libyan Arab Jamahiriya 1108Monrovia Liberia 1109Panevezys Lithuania 1110Riga Latvia 1111Tripoli Lebanon 1112Tripoli Libyan Arab Jamahiriya 1113Vientiane Laos 1114Vilnius Lithuania 1115ALTER TABLE country MODIFY Name varchar(300) NOT NULL default ''; 1116SELECT city.Name, country.Name FROM city,country 1117WHERE city.country=country.Code AND 1118country.Name LIKE 'L%' AND city.Population > 100000; 1119Name Name 1120?iauliai Lithuania 1121Beirut Lebanon 1122Bengasi Libyan Arab Jamahiriya 1123Daugavpils Latvia 1124Kaunas Lithuania 1125Klaipeda Lithuania 1126Maseru Lesotho 1127Misrata Libyan Arab Jamahiriya 1128Monrovia Liberia 1129Panevezys Lithuania 1130Riga Latvia 1131Tripoli Lebanon 1132Tripoli Libyan Arab Jamahiriya 1133Vientiane Laos 1134Vilnius Lithuania 1135ALTER TABLE country ADD COLUMN PopulationBar text; 1136UPDATE country 1137SET PopulationBar=REPEAT('x', CAST(Population/100000 AS unsigned int)); 1138SELECT city.Name, country.Name, country.PopulationBar FROM city,country 1139WHERE city.country=country.Code AND 1140country.Name LIKE 'L%' AND city.Population > 100000; 1141Name Name PopulationBar 1142?iauliai Lithuania xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1143Beirut Lebanon xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1144Bengasi Libyan Arab Jamahiriya xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1145Daugavpils Latvia xxxxxxxxxxxxxxxxxxxxxxxx 1146Kaunas Lithuania xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1147Klaipeda Lithuania xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1148Maseru Lesotho xxxxxxxxxxxxxxxxxxxxxx 1149Misrata Libyan Arab Jamahiriya xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1150Monrovia Liberia xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1151Panevezys Lithuania xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1152Riga Latvia xxxxxxxxxxxxxxxxxxxxxxxx 1153Tripoli Lebanon xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1154Tripoli Libyan Arab Jamahiriya xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1155Vientiane Laos xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1156Vilnius Lithuania xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1157set join_buffer_size=256; 1158SELECT city.Name, country.Name, country.PopulationBar FROM city,country 1159WHERE city.country=country.Code AND 1160country.Name LIKE 'L%' AND city.Population > 100000; 1161Name Name PopulationBar 1162?iauliai Lithuania xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1163Beirut Lebanon xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1164Bengasi Libyan Arab Jamahiriya xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1165Daugavpils Latvia xxxxxxxxxxxxxxxxxxxxxxxx 1166Kaunas Lithuania xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1167Klaipeda Lithuania xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1168Maseru Lesotho xxxxxxxxxxxxxxxxxxxxxx 1169Misrata Libyan Arab Jamahiriya xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1170Monrovia Liberia xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1171Panevezys Lithuania xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1172Riga Latvia xxxxxxxxxxxxxxxxxxxxxxxx 1173Tripoli Lebanon xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1174Tripoli Libyan Arab Jamahiriya xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1175Vientiane Laos xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1176Vilnius Lithuania xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1177set join_buffer_size=default; 1178DROP DATABASE world; 1179use test; 1180CREATE TABLE t1( 1181affiliatetometaid int NOT NULL default '0', 1182uniquekey int NOT NULL default '0', 1183metaid int NOT NULL default '0', 1184affiliateid int NOT NULL default '0', 1185xml text, 1186isactive char(1) NOT NULL default 'Y', 1187PRIMARY KEY (affiliatetometaid) 1188); 1189CREATE UNIQUE INDEX t1_uniquekey ON t1(uniquekey); 1190CREATE INDEX t1_affiliateid ON t1(affiliateid); 1191CREATE INDEX t1_metaid on t1 (metaid); 1192INSERT INTO t1 VALUES 1193(1616, 1571693233, 1391, 2, NULL, 'Y'), (1943, 1993216749, 1726, 2, NULL, 'Y'); 1194CREATE TABLE t2( 1195metaid int NOT NULL default '0', 1196name varchar(80) NOT NULL default '', 1197dateadded timestamp NOT NULL , 1198xml text, 1199status int default NULL, 1200origin int default NULL, 1201gid int NOT NULL default '1', 1202formattypeid int default NULL, 1203PRIMARY KEY (metaid) 1204); 1205CREATE INDEX t2_status ON t2(status); 1206CREATE INDEX t2_gid ON t2(gid); 1207CREATE INDEX t2_formattypeid ON t2(formattypeid); 1208INSERT INTO t2 VALUES 1209(1391, "I Just Died", "2003-10-02 10:07:37", "", 1, NULL, 3, NULL), 1210(1726, "Me, Myself & I", "2003-12-05 11:24:36", " ", 1, NULL, 3, NULL); 1211CREATE TABLE t3( 1212mediaid int NOT NULL , 1213metaid int NOT NULL default '0', 1214formatid int NOT NULL default '0', 1215status int default NULL, 1216path varchar(100) NOT NULL default '', 1217datemodified timestamp NOT NULL , 1218resourcetype int NOT NULL default '1', 1219parameters text, 1220signature int default NULL, 1221quality int NOT NULL default '255', 1222PRIMARY KEY (mediaid) 1223); 1224CREATE INDEX t3_metaid ON t3(metaid); 1225CREATE INDEX t3_formatid ON t3(formatid); 1226CREATE INDEX t3_status ON t3(status); 1227CREATE INDEX t3_metaidformatid ON t3(metaid,formatid); 1228CREATE INDEX t3_signature ON t3(signature); 1229CREATE INDEX t3_quality ON t3(quality); 1230INSERT INTO t3 VALUES 1231(6, 4, 8, 0, "010101_anastacia_spmidi.mid", "2004-03-16 13:40:00", 1, NULL, NULL, 255), 1232(3343, 3, 8, 1, "010102_4VN4bsPwnxRQUJW5Zp1RhG2IL9vvl_8.mid", "2004-03-16 13:40:00", 1, NULL, NULL, 255); 1233CREATE TABLE t4( 1234formatid int NOT NULL , 1235name varchar(60) NOT NULL default '', 1236formatclassid int NOT NULL default '0', 1237mime varchar(60) default NULL, 1238extension varchar(10) default NULL, 1239priority int NOT NULL default '0', 1240canaddtocapability char(1) NOT NULL default 'Y', 1241PRIMARY KEY (formatid) 1242); 1243CREATE INDEX t4_formatclassid ON t4(formatclassid); 1244CREATE INDEX t4_formats_idx ON t4(canaddtocapability); 1245INSERT INTO t4 VALUES 1246(19, "XHTML", 11, "text/html", "xhtml", 10, 'Y'), 1247(54, "AMR (wide band)", 13, "audio/amr-wb", "awb", 0, 'Y'); 1248CREATE TABLE t5( 1249formatclassid int NOT NULL , 1250name varchar(60) NOT NULL default '', 1251priority int NOT NULL default '0', 1252formattypeid int NOT NULL default '0', 1253PRIMARY KEY (formatclassid) 1254); 1255CREATE INDEX t5_formattypeid on t5(formattypeid); 1256INSERT INTO t5 VALUES 1257(11, "Info", 0, 4), (13, "Digital Audio", 0, 2); 1258CREATE TABLE t6( 1259formattypeid int NOT NULL , 1260name varchar(60) NOT NULL default '', 1261priority int default NULL, 1262PRIMARY KEY (formattypeid) 1263); 1264INSERT INTO t6 VALUES 1265(2, "Ringtones", 0); 1266CREATE TABLE t7( 1267metaid int NOT NULL default '0', 1268artistid int NOT NULL default '0', 1269PRIMARY KEY (metaid,artistid) 1270); 1271INSERT INTO t7 VALUES 1272(4, 5), (3, 4); 1273CREATE TABLE t8( 1274artistid int NOT NULL , 1275name varchar(80) NOT NULL default '', 1276PRIMARY KEY (artistid) 1277); 1278INSERT INTO t8 VALUES 1279(5, "Anastacia"), (4, "John Mayer"); 1280CREATE TABLE t9( 1281subgenreid int NOT NULL default '0', 1282metaid int NOT NULL default '0', 1283PRIMARY KEY (subgenreid,metaid) 1284) ; 1285CREATE INDEX t9_subgenreid ON t9(subgenreid); 1286CREATE INDEX t9_metaid ON t9(metaid); 1287INSERT INTO t9 VALUES 1288(138, 4), (31, 3); 1289CREATE TABLE t10( 1290subgenreid int NOT NULL , 1291genreid int NOT NULL default '0', 1292name varchar(80) NOT NULL default '', 1293PRIMARY KEY (subgenreid) 1294) ; 1295CREATE INDEX t10_genreid ON t10(genreid); 1296INSERT INTO t10 VALUES 1297(138, 19, ''), (31, 3, ''); 1298CREATE TABLE t11( 1299genreid int NOT NULL default '0', 1300name char(80) NOT NULL default '', 1301priority int NOT NULL default '0', 1302masterclip char(1) default NULL, 1303PRIMARY KEY (genreid) 1304) ; 1305CREATE INDEX t11_masterclip ON t11( masterclip); 1306INSERT INTO t11 VALUES 1307(19, "Pop & Dance", 95, 'Y'), (3, "Rock & Alternative", 100, 'Y'); 1308EXPLAIN 1309SELECT t1.uniquekey, t1.xml AS affiliateXml, 1310t8.name AS artistName, t8.artistid, 1311t11.name AS genreName, t11.genreid, t11.priority AS genrePriority, 1312t10.subgenreid, t10.name AS subgenreName, 1313t2.name AS metaName, t2.metaid, t2.xml AS metaXml, 1314t4.priority + t5.priority + t6.priority AS overallPriority, 1315t3.path AS path, t3.mediaid, 1316t4.formatid, t4.name AS formatName, 1317t5.formatclassid, t5.name AS formatclassName, 1318t6.formattypeid, t6.name AS formattypeName 1319FROM t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11 1320WHERE t7.metaid = t2.metaid AND t7.artistid = t8.artistid AND 1321t9.metaid = t2.metaid AND t9.subgenreid = t10.subgenreid AND 1322t10.genreid = t11.genreid AND t3.metaid = t2.metaid AND 1323t3.formatid = t4.formatid AND t4.formatclassid = t5.formatclassid AND 1324t4.canaddtocapability = 'Y' AND t5.formattypeid = t6.formattypeid AND 1325t6.formattypeid IN (2) AND (t3.formatid IN (31, 8, 76)) AND 1326t1.metaid = t2.metaid AND t1.affiliateid = '2'; 1327id select_type table partitions type possible_keys key key_len ref rows filtered Extra 13281 SIMPLE t6 NULL system PRIMARY NULL NULL NULL 1 100.00 NULL 13291 SIMPLE t1 NULL ref t1_affiliateid,t1_metaid t1_affiliateid 4 const 1 100.00 NULL 13301 SIMPLE t2 NULL eq_ref PRIMARY PRIMARY 4 test.t1.metaid 1 100.00 Using join buffer (Batched Key Access) 13311 SIMPLE t7 NULL ref PRIMARY PRIMARY 4 test.t1.metaid 1 100.00 Using index 13321 SIMPLE t8 NULL eq_ref PRIMARY PRIMARY 4 test.t7.artistid 1 100.00 Using join buffer (Batched Key Access) 13331 SIMPLE t4 NULL ref PRIMARY,t4_formatclassid,t4_formats_idx t4_formats_idx 1 const 1 100.00 Using where; Using join buffer (Batched Key Access) 13341 SIMPLE t5 NULL eq_ref PRIMARY,t5_formattypeid PRIMARY 4 test.t4.formatclassid 1 50.00 Using where; Using join buffer (Batched Key Access) 13351 SIMPLE t9 NULL ref PRIMARY,t9_subgenreid,t9_metaid t9_metaid 4 test.t1.metaid 2 100.00 Using join buffer (Batched Key Access) 13361 SIMPLE t10 NULL eq_ref PRIMARY,t10_genreid PRIMARY 4 test.t9.subgenreid 1 100.00 Using join buffer (Batched Key Access) 13371 SIMPLE t11 NULL eq_ref PRIMARY PRIMARY 4 test.t10.genreid 1 100.00 Using join buffer (Batched Key Access) 13381 SIMPLE t3 NULL ref t3_metaid,t3_formatid,t3_metaidformatid t3_metaid 4 test.t1.metaid 2 100.00 Using where; Using join buffer (Batched Key Access) 1339Warnings: 1340Note 1003 /* select#1 */ select `test`.`t1`.`uniquekey` AS `uniquekey`,`test`.`t1`.`xml` AS `affiliateXml`,`test`.`t8`.`name` AS `artistName`,`test`.`t8`.`artistid` AS `artistid`,`test`.`t11`.`name` AS `genreName`,`test`.`t11`.`genreid` AS `genreid`,`test`.`t11`.`priority` AS `genrePriority`,`test`.`t10`.`subgenreid` AS `subgenreid`,`test`.`t10`.`name` AS `subgenreName`,`test`.`t2`.`name` AS `metaName`,`test`.`t2`.`metaid` AS `metaid`,`test`.`t2`.`xml` AS `metaXml`,((`test`.`t4`.`priority` + `test`.`t5`.`priority`) + '0') AS `overallPriority`,`test`.`t3`.`path` AS `path`,`test`.`t3`.`mediaid` AS `mediaid`,`test`.`t4`.`formatid` AS `formatid`,`test`.`t4`.`name` AS `formatName`,`test`.`t5`.`formatclassid` AS `formatclassid`,`test`.`t5`.`name` AS `formatclassName`,'2' AS `formattypeid`,'Ringtones' AS `formattypeName` from `test`.`t1` join `test`.`t2` join `test`.`t3` join `test`.`t4` join `test`.`t5` join `test`.`t7` join `test`.`t8` join `test`.`t9` join `test`.`t10` join `test`.`t11` where ((`test`.`t5`.`formattypeid` = 2) and (`test`.`t4`.`canaddtocapability` = 'Y') and (`test`.`t5`.`formatclassid` = `test`.`t4`.`formatclassid`) and (`test`.`t3`.`formatid` = `test`.`t4`.`formatid`) and (`test`.`t11`.`genreid` = `test`.`t10`.`genreid`) and (`test`.`t10`.`subgenreid` = `test`.`t9`.`subgenreid`) and (`test`.`t8`.`artistid` = `test`.`t7`.`artistid`) and (`test`.`t2`.`metaid` = `test`.`t1`.`metaid`) and (`test`.`t7`.`metaid` = `test`.`t1`.`metaid`) and (`test`.`t9`.`metaid` = `test`.`t1`.`metaid`) and (`test`.`t3`.`metaid` = `test`.`t1`.`metaid`) and (`test`.`t4`.`formatid` in (31,8,76)) and (`test`.`t1`.`affiliateid` = '2')) 1341SELECT t1.uniquekey, t1.xml AS affiliateXml, 1342t8.name AS artistName, t8.artistid, 1343t11.name AS genreName, t11.genreid, t11.priority AS genrePriority, 1344t10.subgenreid, t10.name AS subgenreName, 1345t2.name AS metaName, t2.metaid, t2.xml AS metaXml, 1346t4.priority + t5.priority + t6.priority AS overallPriority, 1347t3.path AS path, t3.mediaid, 1348t4.formatid, t4.name AS formatName, 1349t5.formatclassid, t5.name AS formatclassName, 1350t6.formattypeid, t6.name AS formattypeName 1351FROM t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11 1352WHERE t7.metaid = t2.metaid AND t7.artistid = t8.artistid AND 1353t9.metaid = t2.metaid AND t9.subgenreid = t10.subgenreid AND 1354t10.genreid = t11.genreid AND t3.metaid = t2.metaid AND 1355t3.formatid = t4.formatid AND t4.formatclassid = t5.formatclassid AND 1356t4.canaddtocapability = 'Y' AND t5.formattypeid = t6.formattypeid AND 1357t6.formattypeid IN (2) AND (t3.formatid IN (31, 8, 76)) AND 1358t1.metaid = t2.metaid AND t1.affiliateid = '2'; 1359uniquekey affiliateXml artistName artistid genreName genreid genrePriority subgenreid subgenreName metaName metaid metaXml overallPriority path mediaid formatid formatName formatclassid formatclassName formattypeid formattypeName 1360DROP TABLE t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11; 1361CREATE TABLE t1 (a1 int, filler1 char(64) default ' ' ); 1362CREATE TABLE t2 ( 1363a2 int, b2 int, filler2 char(64) default ' ', 1364PRIMARY KEY idx(a2,b2,filler2) 1365) ; 1366CREATE TABLE t3 (b3 int, c3 int, INDEX idx(b3)); 1367INSERT INTO t1(a1) VALUES 1368(4), (7), (1), (9), (8), (5), (3), (6), (2); 1369INSERT INTO t2(a2,b2) VALUES 1370(1,30), (3,40), (2,61), (6,73), (8,92), (9,27), (4,18), (5,84), (7,56), 1371(4,14), (6,76), (8,98), (7,55), (1,39), (2,68), (3,45), (9,21), (5,81), 1372(5,88), (2,65), (6,74), (9,23), (1,37), (3,44), (4,17), (8,99), (7,51), 1373(9,28), (7,52), (1,33), (4,13), (5,87), (3,43), (8,91), (2,62), (6,79), 1374(3,49), (8,93), (7,34), (5,82), (6,78), (2,63), (1,32), (9,22), (4,11); 1375INSERT INTO t3 VALUES 1376(30,302), (92,923), (18,187), (45,459), (30,309), 1377(39,393), (68,685), (45,458), (21,210), (81,817), 1378(40,405), (61,618), (73,738), (92,929), (27,275), 1379(18,188), (84,846), (56,564), (14,144), (76,763), 1380(98,982), (55,551), (17,174), (99,998), (51,513), 1381(28,282), (52,527), (33,336), (13,138), (87,878), 1382(43,431), (91,916), (62,624), (79,797), (49,494), 1383(93,933), (34,347), (82,829), (78,780), (63,634), 1384(32,329), (22,228), (11,114), (74,749), (23,236); 1385EXPLAIN 1386SELECT a1<>a2, a1, a2, b2, b3, c3, 1387SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2 1388FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7; 1389id select_type table partitions type possible_keys key key_len ref rows filtered Extra 13901 SIMPLE t1 NULL ALL NULL NULL NULL NULL 9 100.00 Using where 13911 SIMPLE t2 NULL ref PRIMARY PRIMARY 4 test.t1.a1 1 100.00 Using index 13921 SIMPLE t3 NULL ref idx idx 5 test.t2.b2 5 100.00 Using where; Using join buffer (Batched Key Access) 1393Warnings: 1394Note 1003 /* select#1 */ select (`test`.`t1`.`a1` <> `test`.`t2`.`a2`) AS `a1<>a2`,`test`.`t1`.`a1` AS `a1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2`,`test`.`t3`.`b3` AS `b3`,`test`.`t3`.`c3` AS `c3`,substr(`test`.`t1`.`filler1`,1,1) AS `s1`,substr(`test`.`t2`.`filler2`,1,1) AS `s2` from `test`.`t1` join `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`b3` = `test`.`t2`.`b2`) and (`test`.`t2`.`a2` = `test`.`t1`.`a1`) and ((`test`.`t3`.`c3` % 10) > 7)) 1395SELECT a1<>a2, a1, a2, b2, b3, c3, 1396SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2 1397FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7; 1398a1<>a2 a1 a2 b2 b3 c3 s1 s2 13990 1 1 30 30 309 14000 1 1 32 32 329 14010 2 2 61 61 618 14020 3 3 45 45 458 14030 3 3 45 45 459 14040 4 4 13 13 138 14050 4 4 18 18 188 14060 5 5 82 82 829 14070 5 5 87 87 878 14080 6 6 73 73 738 14090 6 6 74 74 749 14100 8 8 92 92 929 14110 8 8 99 99 998 14120 9 9 22 22 228 1413set join_buffer_size=512; 1414EXPLAIN 1415SELECT a1<>a2, a1, a2, b2, b3, c3, 1416SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2 1417FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7; 1418id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14191 SIMPLE t1 NULL ALL NULL NULL NULL NULL 9 100.00 Using where 14201 SIMPLE t2 NULL ref PRIMARY PRIMARY 4 test.t1.a1 1 100.00 Using index 14211 SIMPLE t3 NULL ref idx idx 5 test.t2.b2 5 100.00 Using where; Using join buffer (Batched Key Access) 1422Warnings: 1423Note 1003 /* select#1 */ select (`test`.`t1`.`a1` <> `test`.`t2`.`a2`) AS `a1<>a2`,`test`.`t1`.`a1` AS `a1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2`,`test`.`t3`.`b3` AS `b3`,`test`.`t3`.`c3` AS `c3`,substr(`test`.`t1`.`filler1`,1,1) AS `s1`,substr(`test`.`t2`.`filler2`,1,1) AS `s2` from `test`.`t1` join `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`b3` = `test`.`t2`.`b2`) and (`test`.`t2`.`a2` = `test`.`t1`.`a1`) and ((`test`.`t3`.`c3` % 10) > 7)) 1424SELECT a1<>a2, a1, a2, b2, b3, c3, 1425SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2 1426FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7; 1427a1<>a2 a1 a2 b2 b3 c3 s1 s2 14280 1 1 30 30 309 14290 1 1 32 32 329 14300 2 2 61 61 618 14310 3 3 45 45 458 14320 3 3 45 45 459 14330 4 4 13 13 138 14340 4 4 18 18 188 14350 5 5 82 82 829 14360 5 5 87 87 878 14370 6 6 73 73 738 14380 6 6 74 74 749 14390 8 8 92 92 929 14400 8 8 99 99 998 14410 9 9 22 22 228 1442DROP TABLE t1,t2,t3; 1443CREATE TABLE t1 (a int, b int, INDEX idx(b)); 1444CREATE TABLE t2 (a int, b int, INDEX idx(a)); 1445INSERT INTO t1 VALUES (5,30), (3,20), (7,40), (2,10), (8,30), (1,10), (4,20); 1446INSERT INTO t2 VALUES (7,10), (1,20), (2,20), (8,20), (8,10), (1,20); 1447INSERT INTO t2 VALUES (1,10), (4,20), (3,20), (7,20), (7,10), (1,20); 1448set join_buffer_size=32; 1449Warnings: 1450Warning 1292 Truncated incorrect join_buffer_size value: '32' 1451EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b >= 30; 1452id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14531 SIMPLE t1 NULL range idx idx 5 NULL 4 100.00 Using index condition; Using where; Using MRR 14541 SIMPLE t2 NULL ref idx idx 5 test.t1.a 2 100.00 Using join buffer (Batched Key Access) 1455Warnings: 1456Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t1`.`b` >= 30)) 1457SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b >= 30; 1458a b a b 14597 40 7 10 14607 40 7 10 14617 40 7 20 14628 30 8 10 14638 30 8 20 1464DROP TABLE t1,t2; 1465 1466BUG#40136: Group by is ignored when join buffer is used for an outer join 1467 1468create table t1(a int PRIMARY KEY, b int); 1469insert into t1 values 1470(5, 10), (2, 70), (7, 80), (6, 20), (1, 50), (9, 40), (8, 30), (3, 60); 1471create table t2 (p int, a int, INDEX i_a(a)); 1472insert into t2 values 1473(103, 7), (109, 3), (102, 3), (108, 1), (106, 3), 1474(107, 7), (105, 1), (101, 3), (100, 7), (110, 1); 1475explain 1476select t1.a, count(t2.p) as count 1477from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a; 1478id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14791 SIMPLE t1 NULL index PRIMARY PRIMARY 4 NULL 8 100.00 Using index; Using temporary; Using filesort 14801 SIMPLE t2 NULL ALL i_a NULL NULL NULL 10 100.00 Using where; Using join buffer (Block Nested Loop) 1481Warnings: 1482Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,count(`test`.`t2`.`p`) AS `count` from `test`.`t1` left join `test`.`t2` on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and ((`test`.`t2`.`p` % 2) = 1))) where 1 group by `test`.`t1`.`a` 1483select t1.a, count(t2.p) as count 1484from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a; 1485a count 14861 1 14872 0 14883 2 14895 0 14906 0 14917 2 14928 0 14939 0 1494drop table t1, t2; 1495# 1496# Bug #40134: outer join with not exists optimization and join buffer 1497# 1498set join_buffer_size=default; 1499CREATE TABLE t1 (a int NOT NULL); 1500INSERT INTO t1 VALUES (2), (4), (3), (5), (1); 1501CREATE TABLE t2 (a int NOT NULL, b int NOT NULL, INDEX i_a(a)); 1502INSERT INTO t2 VALUES (4,10), (2,10), (2,30), (2,20), (4,20); 1503EXPLAIN 1504SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL; 1505id select_type table partitions type possible_keys key key_len ref rows filtered Extra 15061 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 15071 SIMPLE t2 NULL ALL i_a NULL NULL NULL 5 20.00 Using where; Not exists; Using join buffer (Block Nested Loop) 1508Warnings: 1509Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`a` = `test`.`t1`.`a`)) where isnull(`test`.`t2`.`b`) 1510SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL; 1511a a b 15123 NULL NULL 15135 NULL NULL 15141 NULL NULL 1515DROP TABLE t1, t2; 1516# 1517# BUG#40268: Nested outer join with not null-rejecting where condition 1518# over an inner table which is not the last in the nest 1519# 1520CREATE TABLE t2 (a int, b int, c int); 1521CREATE TABLE t3 (a int, b int, c int); 1522CREATE TABLE t4 (a int, b int, c int); 1523INSERT INTO t2 VALUES (3,3,0), (4,2,0), (5,3,0); 1524INSERT INTO t3 VALUES (1,2,0), (2,2,0); 1525INSERT INTO t4 VALUES (3,2,0), (4,2,0); 1526SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b 1527FROM t2 LEFT JOIN (t3, t4) ON t2.b=t4.b 1528WHERE t3.a+2<t2.a OR t3.c IS NULL; 1529a b a b a b 15303 3 NULL NULL NULL NULL 15314 2 1 2 3 2 15324 2 1 2 4 2 15335 3 NULL NULL NULL NULL 1534DROP TABLE t2, t3, t4; 1535# 1536# Bug #40192: outer join with where clause when using BNL 1537# 1538create table t1 (a int, b int); 1539insert into t1 values (2, 20), (3, 30), (1, 10); 1540create table t2 (a int, c int); 1541insert into t2 values (1, 101), (3, 102), (1, 100); 1542select * from t1 left join t2 on t1.a=t2.a; 1543a b a c 15441 10 1 100 15451 10 1 101 15462 20 NULL NULL 15473 30 3 102 1548explain select * from t1 left join t2 on t1.a=t2.a where t2.c=102 or t2.c is null; 1549id select_type table partitions type possible_keys key key_len ref rows filtered Extra 15501 SIMPLE t1 NULL ALL NULL NULL NULL NULL 3 100.00 NULL 15511 SIMPLE t2 NULL ALL NULL NULL NULL NULL 3 55.56 Using where; Using join buffer (Block Nested Loop) 1552Warnings: 1553Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`c` AS `c` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`a` = `test`.`t1`.`a`)) where ((`test`.`t2`.`c` = 102) or isnull(`test`.`t2`.`c`)) 1554select * from t1 left join t2 on t1.a=t2.a where t2.c=102 or t2.c is null; 1555a b a c 15562 20 NULL NULL 15573 30 3 102 1558drop table t1, t2; 1559# 1560# Bug #40317: outer join with with constant on expression equal to FALSE 1561# 1562create table t1 (a int); 1563insert into t1 values (30), (40), (20); 1564create table t2 (b int); 1565insert into t2 values (200), (100); 1566select * from t1 left join t2 on (1=0); 1567a b 156830 NULL 156940 NULL 157020 NULL 1571explain select * from t1 left join t2 on (1=0) where a=40; 1572id select_type table partitions type possible_keys key key_len ref rows filtered Extra 15731 SIMPLE t1 NULL ALL NULL NULL NULL NULL 3 33.33 Using where 15741 SIMPLE t2 NULL ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop) 1575Warnings: 1576Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` left join `test`.`t2` on((1 = 0)) where (`test`.`t1`.`a` = 40) 1577select * from t1 left join t2 on (1=0) where a=40; 1578a b 157940 NULL 1580drop table t1, t2; 1581# 1582# Bug #41204: small buffer with big rec_per_key for ref access 1583# 1584CREATE TABLE t1 (a int); 1585INSERT INTO t1 VALUES (0); 1586INSERT INTO t1(a) SELECT a FROM t1; 1587INSERT INTO t1(a) SELECT a FROM t1; 1588INSERT INTO t1(a) SELECT a FROM t1; 1589INSERT INTO t1(a) SELECT a FROM t1; 1590INSERT INTO t1(a) SELECT a FROM t1; 1591INSERT INTO t1(a) SELECT a FROM t1; 1592INSERT INTO t1(a) SELECT a FROM t1; 1593INSERT INTO t1(a) SELECT a FROM t1; 1594INSERT INTO t1(a) SELECT a FROM t1; 1595INSERT INTO t1(a) SELECT a FROM t1; 1596INSERT INTO t1(a) SELECT a FROM t1; 1597INSERT INTO t1 VALUES (20000), (10000); 1598CREATE TABLE t2 (pk int AUTO_INCREMENT PRIMARY KEY, b int, c int, INDEX idx(b)); 1599INSERT INTO t2(b,c) VALUES (10000, 3), (20000, 7), (20000, 1), (10000, 9), (20000, 5); 1600INSERT INTO t2(b,c) SELECT b,c FROM t2; 1601INSERT INTO t2(b,c) SELECT b,c FROM t2; 1602INSERT INTO t2(b,c) SELECT b,c FROM t2; 1603INSERT INTO t2(b,c) SELECT b,c FROM t2; 1604INSERT INTO t2(b,c) SELECT b,c FROM t2; 1605INSERT INTO t2(b,c) SELECT b,c FROM t2; 1606INSERT INTO t2(b,c) SELECT b,c FROM t2; 1607INSERT INTO t2(b,c) SELECT b,c FROM t2; 1608ANALYZE TABLE t1,t2; 1609set join_buffer_size=1024; 1610EXPLAIN SELECT AVG(c) FROM t1,t2 WHERE t1.a=t2.b; 1611id select_type table partitions type possible_keys key key_len ref rows filtered Extra 16121 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2050 100.00 Using where 16131 SIMPLE t2 NULL ref idx idx 5 test.t1.a 640 100.00 Using join buffer (Batched Key Access) 1614Warnings: 1615Note 1003 /* select#1 */ select avg(`test`.`t2`.`c`) AS `AVG(c)` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`b` = `test`.`t1`.`a`) 1616SELECT AVG(c) FROM t1,t2 WHERE t1.a=t2.b; 1617AVG(c) 16185.0000 1619set join_buffer_size=default; 1620DROP TABLE t1, t2; 1621# 1622# Bug #41894: big join buffer of level 7 used to join records 1623# with null values in place of varchar strings 1624# 1625CREATE TABLE t1 (a int NOT NULL AUTO_INCREMENT PRIMARY KEY, 1626b varchar(127) DEFAULT NULL); 1627INSERT INTO t1(a) VALUES (1); 1628INSERT INTO t1(b) SELECT b FROM t1; 1629INSERT INTO t1(b) SELECT b FROM t1; 1630INSERT INTO t1(b) SELECT b FROM t1; 1631INSERT INTO t1(b) SELECT b FROM t1; 1632INSERT INTO t1(b) SELECT b FROM t1; 1633INSERT INTO t1(b) SELECT b FROM t1; 1634INSERT INTO t1(b) SELECT b FROM t1; 1635INSERT INTO t1(b) SELECT b FROM t1; 1636INSERT INTO t1(b) SELECT b FROM t1; 1637INSERT INTO t1(b) SELECT b FROM t1; 1638INSERT INTO t1(b) SELECT b FROM t1; 1639INSERT INTO t1(b) SELECT b FROM t1; 1640INSERT INTO t1(b) SELECT b FROM t1; 1641INSERT INTO t1(b) SELECT b FROM t1; 1642CREATE TABLE t2 (a int NOT NULL PRIMARY KEY, b varchar(127) DEFAULT NULL); 1643INSERT INTO t2 SELECT * FROM t1; 1644CREATE TABLE t3 (a int NOT NULL PRIMARY KEY, b varchar(127) DEFAULT NULL); 1645INSERT INTO t3 SELECT * FROM t1; 1646set join_buffer_size=1024*1024; 1647EXPLAIN 1648SELECT COUNT(*) FROM t1,t2,t3 1649WHERE t1.a=t2.a AND t2.a=t3.a AND 1650t1.b IS NULL AND t2.b IS NULL AND t3.b IS NULL; 1651id select_type table partitions type possible_keys key key_len ref rows filtered Extra 16521 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 16384 10.00 Using where 16531 SIMPLE t2 NULL eq_ref PRIMARY PRIMARY 4 test.t1.a 1 10.00 Using where; Using join buffer (Batched Key Access) 16541 SIMPLE t3 NULL eq_ref PRIMARY PRIMARY 4 test.t1.a 1 10.00 Using where; Using join buffer (Batched Key Access) 1655Warnings: 1656Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` join `test`.`t2` join `test`.`t3` where ((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`) and isnull(`test`.`t1`.`b`) and isnull(`test`.`t2`.`b`) and isnull(`test`.`t3`.`b`)) 1657SELECT COUNT(*) FROM t1,t2,t3 1658WHERE t1.a=t2.a AND t2.a=t3.a AND 1659t1.b IS NULL AND t2.b IS NULL AND t3.b IS NULL; 1660COUNT(*) 166116384 1662set join_buffer_size=default; 1663DROP TABLE t1,t2,t3; 1664# 1665# Bug #42020: join buffer is used for outer join with fields of 1666# several outer tables in join buffer 1667# 1668CREATE TABLE t1 ( 1669a bigint NOT NULL, 1670PRIMARY KEY (a) 1671); 1672INSERT INTO t1 VALUES 1673(2), (1); 1674CREATE TABLE t2 ( 1675a bigint NOT NULL, 1676b bigint NOT NULL, 1677PRIMARY KEY (a,b) 1678); 1679INSERT INTO t2 VALUES 1680(2,30), (2,40), (2,50), (2,60), (2,70), (2,80), 1681(1,10), (1, 20), (1,30), (1,40), (1,50); 1682CREATE TABLE t3 ( 1683pk bigint NOT NULL AUTO_INCREMENT, 1684a bigint NOT NULL, 1685b bigint NOT NULL, 1686val bigint DEFAULT '0', 1687PRIMARY KEY (pk), 1688KEY idx (a,b) 1689); 1690INSERT INTO t3(a,b) VALUES 1691(2,30), (2,40), (2,50), (2,60), (2,70), (2,80), 1692(4,30), (4,40), (4,50), (4,60), (4,70), (4,80), 1693(5,30), (5,40), (5,50), (5,60), (5,70), (5,80), 1694(7,30), (7,40), (7,50), (7,60), (7,70), (7,80); 1695SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val 1696FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) 1697WHERE t1.a=t2.a; 1698a a a b b val 16991 1 NULL 10 NULL NULL 17001 1 NULL 20 NULL NULL 17011 1 NULL 30 NULL NULL 17021 1 NULL 40 NULL NULL 17031 1 NULL 50 NULL NULL 17042 2 2 30 30 0 17052 2 2 40 40 0 17062 2 2 50 50 0 17072 2 2 60 60 0 17082 2 2 70 70 0 17092 2 2 80 80 0 1710set join_buffer_size=256; 1711EXPLAIN 1712SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val 1713FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) 1714WHERE t1.a=t2.a; 1715id select_type table partitions type possible_keys key key_len ref rows filtered Extra 17161 SIMPLE t1 NULL index PRIMARY PRIMARY 8 NULL 2 100.00 Using index 17171 SIMPLE t2 NULL ref PRIMARY PRIMARY 8 test.t1.a 1 100.00 Using index 17181 SIMPLE t3 NULL ref idx idx 16 test.t1.a,test.t2.b 2 100.00 Using join buffer (Batched Key Access) 1719Warnings: 1720Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t3`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`val` AS `val` from `test`.`t1` join `test`.`t2` left join `test`.`t3` on(((`test`.`t3`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = `test`.`t1`.`a`))) where (`test`.`t2`.`a` = `test`.`t1`.`a`) 1721SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val 1722FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) 1723WHERE t1.a=t2.a; 1724a a a b b val 17251 1 NULL 10 NULL NULL 17261 1 NULL 20 NULL NULL 17271 1 NULL 30 NULL NULL 17281 1 NULL 40 NULL NULL 17291 1 NULL 50 NULL NULL 17302 2 2 30 30 0 17312 2 2 40 40 0 17322 2 2 50 50 0 17332 2 2 60 60 0 17342 2 2 70 70 0 17352 2 2 80 80 0 1736DROP INDEX idx ON t3; 1737EXPLAIN 1738SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val 1739FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) 1740WHERE t1.a=t2.a; 1741id select_type table partitions type possible_keys key key_len ref rows filtered Extra 17421 SIMPLE t1 NULL index PRIMARY PRIMARY 8 NULL 2 100.00 Using index 17431 SIMPLE t2 NULL ref PRIMARY PRIMARY 8 test.t1.a 1 100.00 Using index 17441 SIMPLE t3 NULL ALL NULL NULL NULL NULL 24 100.00 Using where; Using join buffer (Block Nested Loop) 1745Warnings: 1746Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t3`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`val` AS `val` from `test`.`t1` join `test`.`t2` left join `test`.`t3` on(((`test`.`t3`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = `test`.`t1`.`a`))) where (`test`.`t2`.`a` = `test`.`t1`.`a`) 1747SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val 1748FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) 1749WHERE t1.a=t2.a; 1750a a a b b val 17511 1 NULL 10 NULL NULL 17521 1 NULL 20 NULL NULL 17531 1 NULL 30 NULL NULL 17541 1 NULL 40 NULL NULL 17551 1 NULL 50 NULL NULL 17562 2 2 30 30 0 17572 2 2 40 40 0 17582 2 2 50 50 0 17592 2 2 60 60 0 17602 2 2 70 70 0 17612 2 2 80 80 0 1762set join_buffer_size=default; 1763DROP TABLE t1,t2,t3; 1764create table t1(f1 int, f2 int); 1765insert into t1 values (1,1),(2,2),(3,3); 1766create table t2(f1 int not null, f2 int not null, f3 char(200), key(f1,f2)); 1767insert into t2 values (1,1, 'qwerty'),(1,2, 'qwerty'),(1,3, 'qwerty'); 1768insert into t2 values (2,1, 'qwerty'),(2,2, 'qwerty'),(2,3, 'qwerty'), 1769(2,4, 'qwerty'),(2,5, 'qwerty'); 1770insert into t2 values (3,1, 'qwerty'),(3,4, 'qwerty'); 1771insert into t2 values (4,1, 'qwerty'),(4,2, 'qwerty'),(4,3, 'qwerty'), 1772(4,4, 'qwerty'); 1773insert into t2 values (1,1, 'qwerty'),(1,2, 'qwerty'),(1,3, 'qwerty'); 1774insert into t2 values (2,1, 'qwerty'),(2,2, 'qwerty'),(2,3, 'qwerty'), 1775(2,4, 'qwerty'),(2,5, 'qwerty'); 1776insert into t2 values (3,1, 'qwerty'),(3,4, 'qwerty'); 1777insert into t2 values (4,1, 'qwerty'),(4,2, 'qwerty'),(4,3, 'qwerty'), 1778(4,4, 'qwerty'); 1779select t2.f1, t2.f2, t2.f3 from t1,t2 1780where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1; 1781f1 f2 f3 17821 1 qwerty 17831 1 qwerty 17842 2 qwerty 17852 2 qwerty 1786explain select t2.f1, t2.f2, t2.f3 from t1,t2 1787where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2; 1788id select_type table partitions type possible_keys key key_len ref rows filtered Extra 17891 SIMPLE t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 17901 SIMPLE t2 NULL ref f1 f1 4 test.t1.f1 3 11.11 Using index condition; Using join buffer (Batched Key Access) 1791Warnings: 1792Note 1003 /* select#1 */ select `test`.`t2`.`f1` AS `f1`,`test`.`t2`.`f2` AS `f2`,`test`.`t2`.`f3` AS `f3` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`f1` = `test`.`t1`.`f1`) and (`test`.`t2`.`f2` between `test`.`t1`.`f1` and `test`.`t2`.`f2`)) 1793drop table t1,t2; 1794# 1795# Bug #42955: join with GROUP BY/ORDER BY and when BKA is enabled 1796# 1797create table t1 (d int, id1 int, index idx1 (d, id1)); 1798insert into t1 values 1799(3, 20), (2, 40), (3, 10), (1, 10), (3, 20), (1, 40), (2, 30), (3, 30); 1800create table t2 (id1 int, id2 int, index idx2 (id1)); 1801insert into t2 values 1802(20, 100), (30, 400), (20, 400), (30, 200), (10, 300), (10, 200), (40, 100), 1803(40, 200), (30, 300), (10, 400), (20, 200), (20, 300); 1804explain 1805select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1 1806where t1.d=3 group by t1.id1; 1807id select_type table partitions type possible_keys key key_len ref rows filtered Extra 18081 SIMPLE t1 NULL ref idx1 idx1 5 const 4 100.00 Using where; Using index; Using temporary; Using filesort 18091 SIMPLE t2 NULL ref idx2 idx2 5 test.t1.id1 2 100.00 Using join buffer (Batched Key Access) 1810Warnings: 1811Note 1003 /* select#1 */ select `test`.`t1`.`id1` AS `id1`,sum(`test`.`t2`.`id2`) AS `sum(t2.id2)` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`id1` = `test`.`t1`.`id1`) and (`test`.`t1`.`d` = 3)) group by `test`.`t1`.`id1` 1812select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1 1813where t1.d=3 group by t1.id1; 1814id1 sum(t2.id2) 181510 900 181620 2000 181730 900 1818explain 1819select t1.id1 from t1 join t2 on t1.id1=t2.id1 1820where t1.d=3 and t2.id2 > 200 order by t1.id1; 1821id select_type table partitions type possible_keys key key_len ref rows filtered Extra 18221 SIMPLE t1 NULL ref idx1 idx1 5 const 4 100.00 Using index; Using temporary; Using filesort 18231 SIMPLE t2 NULL ALL idx2 NULL NULL NULL 12 8.33 Using where; Using join buffer (Block Nested Loop) 1824Warnings: 1825Note 1003 /* select#1 */ select `test`.`t1`.`id1` AS `id1` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`id1` = `test`.`t1`.`id1`) and (`test`.`t1`.`d` = 3) and (`test`.`t2`.`id2` > 200)) order by `test`.`t1`.`id1` 1826select t1.id1 from t1 join t2 on t1.id1=t2.id1 1827where t1.d=3 and t2.id2 > 200 order by t1.id1; 1828id1 182910 183010 183120 183220 183320 183420 183530 183630 1837drop table t1,t2; 1838# 1839# Bug #44019: star-like multi-join query executed optimizer_join_cache_level=6 1840# 1841create table t1 (a int, b int, c int, d int); 1842create table t2 (b int, e varchar(16), index idx(b)); 1843create table t3 (d int, f varchar(16), index idx(d)); 1844create table t4 (c int, g varchar(16), index idx(c)); 1845insert into t1 values 1846(5, 50, 500, 5000), (3, 30, 300, 3000), (9, 90, 900, 9000), 1847(2, 20, 200, 2000), (4, 40, 400, 4000), (8, 80, 800, 800), 1848(7, 70, 700, 7000); 1849insert into t2 values 1850(30, 'bbb'), (10, 'b'), (70, 'bbbbbbb'), (60, 'bbbbbb'), 1851(31, 'bbb'), (11, 'b'), (71, 'bbbbbbb'), (61, 'bbbbbb'), 1852(32, 'bbb'), (12, 'b'), (72, 'bbbbbbb'), (62, 'bbbbbb'); 1853insert into t3 values 1854(4000, 'dddd'), (3000, 'ddd'), (1000, 'd'), (8000, 'dddddddd'), 1855(4001, 'dddd'), (3001, 'ddd'), (1001, 'd'), (8001, 'dddddddd'), 1856(4002, 'dddd'), (3002, 'ddd'), (1002, 'd'), (8002, 'dddddddd'); 1857insert into t4 values 1858(200, 'cc'), (600, 'cccccc'), (300, 'ccc'), (500, 'ccccc'), 1859(201, 'cc'), (601, 'cccccc'), (301, 'ccc'), (501, 'ccccc'), 1860(202, 'cc'), (602, 'cccccc'), (302, 'ccc'), (502, 'ccccc'); 1861analyze table t2,t3,t4; 1862explain 1863select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4 1864where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c; 1865id select_type table partitions type possible_keys key key_len ref rows filtered Extra 18661 SIMPLE t1 NULL ALL NULL NULL NULL NULL 7 100.00 Using where 18671 SIMPLE t2 NULL ref idx idx 5 test.t1.b 1 100.00 Using join buffer (Batched Key Access) 18681 SIMPLE t3 NULL ref idx idx 5 test.t1.d 1 100.00 Using join buffer (Batched Key Access) 18691 SIMPLE t4 NULL ref idx idx 5 test.t1.c 1 100.00 Using join buffer (Batched Key Access) 1870Warnings: 1871Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d`,`test`.`t2`.`e` AS `e`,`test`.`t3`.`f` AS `f`,`test`.`t4`.`g` AS `g` from `test`.`t1` join `test`.`t2` join `test`.`t3` join `test`.`t4` where ((`test`.`t4`.`c` = `test`.`t1`.`c`) and (`test`.`t3`.`d` = `test`.`t1`.`d`) and (`test`.`t2`.`b` = `test`.`t1`.`b`)) 1872select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4 1873where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c; 1874a b c d e f g 18753 30 300 3000 bbb ddd ccc 1876drop table t1,t2,t3,t4; 1877# 1878# Bug #44250: Corruption of linked join buffers when using BKA 1879# 1880CREATE TABLE t1 ( 1881id1 bigint(20) DEFAULT NULL, 1882id2 bigint(20) DEFAULT NULL, 1883id3 bigint(20) DEFAULT NULL, 1884num1 bigint(20) DEFAULT NULL, 1885num2 int(11) DEFAULT NULL, 1886num3 bigint(20) DEFAULT NULL 1887); 1888CREATE TABLE t2 ( 1889id3 bigint(20) NOT NULL DEFAULT '0', 1890id4 bigint(20) DEFAULT NULL, 1891enum1 enum('Enabled','Disabled','Paused') DEFAULT NULL, 1892PRIMARY KEY (id3) 1893); 1894CREATE TABLE t3 ( 1895id4 bigint(20) NOT NULL DEFAULT '0', 1896text1 text, 1897PRIMARY KEY (id4) 1898); 1899CREATE TABLE t4 ( 1900id2 bigint(20) NOT NULL DEFAULT '0', 1901dummy int(11) DEFAULT '0', 1902PRIMARY KEY (id2) 1903); 1904CREATE TABLE t5 ( 1905id1 bigint(20) NOT NULL DEFAULT '0', 1906id2 bigint(20) NOT NULL DEFAULT '0', 1907enum2 enum('Active','Deleted','Paused') DEFAULT NULL, 1908PRIMARY KEY (id1,id2) 1909); 1910set join_buffer_size=2048; 1911EXPLAIN 1912SELECT STRAIGHT_JOIN t1.id1, t1.num3, t3.text1, t3.id4, t2.id3, t4.dummy 1913FROM t1 JOIN t2 JOIN t3 JOIN t4 JOIN t5 1914WHERE t1.id1=t5.id1 AND t1.id2=t5.id2 and t4.id2=t1.id2 AND 1915t5.enum2='Active' AND t3.id4=t2.id4 AND t2.id3=t1.id3 AND t3.text1<'D'; 1916id select_type table partitions type possible_keys key key_len ref rows filtered Extra 19171 SIMPLE t1 NULL ALL NULL NULL NULL NULL 349 100.00 Using where 19181 SIMPLE t2 NULL eq_ref PRIMARY PRIMARY 8 test.t1.id3 1 100.00 Using join buffer (Batched Key Access) 19191 SIMPLE t3 NULL ALL PRIMARY NULL NULL NULL 13 7.69 Using where; Using join buffer (Block Nested Loop) 19201 SIMPLE t4 NULL eq_ref PRIMARY PRIMARY 8 test.t1.id2 1 100.00 Using join buffer (Batched Key Access) 19211 SIMPLE t5 NULL eq_ref PRIMARY PRIMARY 16 test.t1.id1,test.t1.id2 1 33.33 Using where; Using join buffer (Batched Key Access) 1922Warnings: 1923Note 1003 /* select#1 */ select straight_join `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`num3` AS `num3`,`test`.`t3`.`text1` AS `text1`,`test`.`t3`.`id4` AS `id4`,`test`.`t2`.`id3` AS `id3`,`test`.`t4`.`dummy` AS `dummy` from `test`.`t1` join `test`.`t2` join `test`.`t3` join `test`.`t4` join `test`.`t5` where ((`test`.`t2`.`id3` = `test`.`t1`.`id3`) and (`test`.`t3`.`id4` = `test`.`t2`.`id4`) and (`test`.`t5`.`enum2` = 'Active') and (`test`.`t4`.`id2` = `test`.`t1`.`id2`) and (`test`.`t5`.`id2` = `test`.`t1`.`id2`) and (`test`.`t5`.`id1` = `test`.`t1`.`id1`) and (`test`.`t3`.`text1` < 'D')) 1924SELECT STRAIGHT_JOIN t1.id1, t1.num3, t3.text1, t3.id4, t2.id3, t4.dummy 1925FROM t1 JOIN t2 JOIN t3 JOIN t4 JOIN t5 1926WHERE t1.id1=t5.id1 AND t1.id2=t5.id2 and t4.id2=t1.id2 AND 1927t5.enum2='Active' AND t3.id4=t2.id4 AND t2.id3=t1.id3 AND t3.text1<'D'; 1928id1 num3 text1 id4 id3 dummy 1929228172702 134 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 2567095402 2667134182 0 1930228172702 14 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 2567095402 2667134182 0 1931228172702 15 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 2567095402 2667134182 0 1932228172702 3 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 2567095402 2667134182 0 1933228808822 1 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 1934228808822 1 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 1935228808822 1 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 1936228808822 10 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 1937228808822 13 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 1938228808822 13 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 1939228808822 14 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 1940228808822 17 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 1941228808822 18 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 1942228808822 19 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 1943228808822 26 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 1944228808822 28 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 1945228808822 3 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 1946228808822 3 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 1947228808822 3 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 1948228808822 3 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 1949228808822 4 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 1950228808822 4 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 1951228808822 4 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 1952228808822 50 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 1953228808822 6 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 1954228808822 60 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 1955228808822 61 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 1956228808822 62 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 1957228808822 84 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 1958228808822 89 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 1959228808822 9 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 1960set join_buffer_size=default; 1961DROP TABLE t1,t2,t3,t4,t5; 1962# 1963# Bug #46328: Use of aggregate function without GROUP BY clause 1964# returns many rows (vs. one ) 1965# 1966CREATE TABLE t1 ( 1967int_key int(11) NOT NULL, 1968KEY int_key (int_key) 1969); 1970INSERT INTO t1 VALUES 1971(0),(2),(2),(2),(3),(4),(5),(5),(6),(6),(8),(8),(9),(9); 1972CREATE TABLE t2 ( 1973int_key int(11) NOT NULL, 1974KEY int_key (int_key) 1975); 1976INSERT INTO t2 VALUES (2),(3); 1977 1978# The query shall return 1 record with a max value 9 and one of the 1979# int_key values inserted above (undefined which one). A changed 1980# execution plan may change the value in the second column 1981SELECT MAX(t1.int_key), t1.int_key 1982FROM t1 STRAIGHT_JOIN t2 1983ORDER BY t1.int_key; 1984MAX(t1.int_key) int_key 19859 0 1986 1987explain 1988SELECT MAX(t1.int_key), t1.int_key 1989FROM t1 STRAIGHT_JOIN t2 1990ORDER BY t1.int_key; 1991id select_type table partitions type possible_keys key key_len ref rows filtered Extra 19921 SIMPLE t1 NULL index NULL int_key 4 NULL 14 100.00 Using index 19931 SIMPLE t2 NULL index NULL int_key 4 NULL 2 100.00 Using index; Using join buffer (Block Nested Loop) 1994Warnings: 1995Note 1003 /* select#1 */ select max(`test`.`t1`.`int_key`) AS `MAX(t1.int_key)`,`test`.`t1`.`int_key` AS `int_key` from `test`.`t1` straight_join `test`.`t2` 1996 1997DROP TABLE t1,t2; 1998# 1999# Bug #45019: join buffer contains two blob columns one of which is 2000# used in the key employed to access the joined table 2001# 2002CREATE TABLE t1 (c1 int, c2 int, key (c2)); 2003INSERT INTO t1 VALUES (1,1); 2004INSERT INTO t1 VALUES (2,2); 2005CREATE TABLE t2 (c1 text, c2 text); 2006INSERT INTO t2 VALUES('tt', 'uu'); 2007INSERT INTO t2 VALUES('zzzz', 'xxxxxxxxx'); 2008ANALYZE TABLE t1,t2; 2009SELECT t1.*, t2.*, LENGTH(t2.c1), LENGTH(t2.c2) FROM t1,t2 2010WHERE t1.c2=LENGTH(t2.c2) and t1.c1=LENGTH(t2.c1); 2011c1 c2 c1 c2 LENGTH(t2.c1) LENGTH(t2.c2) 20122 2 tt uu 2 2 2013DROP TABLE t1,t2; 2014# 2015# Regression test for 2016# Bug#46733 - NULL value not returned for aggregate on empty result 2017# set w/ semijoin on 2018CREATE TABLE t1 ( 2019i int(11) NOT NULL, 2020v varchar(1) DEFAULT NULL, 2021PRIMARY KEY (i) 2022); 2023INSERT INTO t1 VALUES (10,'a'),(11,'b'),(12,'c'),(13,'d'); 2024CREATE TABLE t2 ( 2025i int(11) NOT NULL, 2026v varchar(1) DEFAULT NULL, 2027PRIMARY KEY (i) 2028); 2029INSERT INTO t2 VALUES (1,'x'),(2,'y'); 2030 2031SELECT MAX(t1.i) 2032FROM t1 JOIN t2 ON t2.v 2033ORDER BY t2.v; 2034MAX(t1.i) 2035NULL 2036Warnings: 2037Warning 1292 Truncated incorrect INTEGER value: 'x' 2038Warning 1292 Truncated incorrect INTEGER value: 'y' 2039 2040EXPLAIN 2041SELECT MAX(t1.i) 2042FROM t1 JOIN t2 ON t2.v 2043ORDER BY t2.v; 2044id select_type table partitions type possible_keys key key_len ref rows filtered Extra 20451 SIMPLE t2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where 20461 SIMPLE t1 NULL index NULL PRIMARY 4 NULL 4 100.00 Using index; Using join buffer (Block Nested Loop) 2047Warnings: 2048Note 1003 /* select#1 */ select max(`test`.`t1`.`i`) AS `MAX(t1.i)` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`v` 2049 2050DROP TABLE t1,t2; 2051# 2052# Bug#51092: Linked join buffer gives wrong result 2053# for 3-way cross join 2054# 2055CREATE TABLE t1 (a INT, b INT); 2056INSERT INTO t1 VALUES (1,1),(2,2); 2057CREATE TABLE t2 (a INT, b INT); 2058INSERT INTO t2 VALUES (1,1),(2,2); 2059CREATE TABLE t3 (a INT, b INT); 2060INSERT INTO t3 VALUES (1,1),(2,2); 2061EXPLAIN SELECT t1.* FROM t1,t2,t3; 2062id select_type table partitions type possible_keys key key_len ref rows filtered Extra 20631 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 20641 SIMPLE t2 NULL ALL NULL NULL NULL NULL 2 100.00 Using join buffer (Block Nested Loop) 20651 SIMPLE t3 NULL ALL NULL NULL NULL NULL 2 100.00 Using join buffer (Block Nested Loop) 2066Warnings: 2067Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` join `test`.`t3` 2068SELECT t1.* FROM t1,t2,t3; 2069a b 20701 1 20712 2 20721 1 20732 2 20741 1 20752 2 20761 1 20772 2 2078DROP TABLE t1,t2,t3; 2079# 2080# BUG#52394 Segfault in JOIN_CACHE::get_offset () at sql_select.h:445 2081# 2082CREATE TABLE C(a int); 2083INSERT INTO C VALUES(1),(2),(3),(4),(5); 2084CREATE TABLE D (a int(11), b varchar(1)); 2085INSERT INTO D VALUES (6,'r'),(27,'o'); 2086CREATE TABLE E (a int(11) primary key, b varchar(1)); 2087INSERT INTO E VALUES 2088(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'); 2089SELECT 1 FROM C,D,E WHERE D.a = E.a AND D.b = E.b; 20901 2091DROP TABLE C,D,E; 2092# 2093# BUG#52540 Crash in JOIN_CACHE::set_match_flag_if_none () at sql_join_cache.cc:1883 2094# 2095CREATE TABLE t1 (a int); 2096INSERT INTO t1 VALUES (2); 2097CREATE TABLE t2 (a varchar(10)); 2098INSERT INTO t2 VALUES ('f'),('x'); 2099CREATE TABLE t3 (pk int(11) PRIMARY KEY); 2100INSERT INTO t3 VALUES (2); 2101CREATE TABLE t4 (a varchar(10)); 2102EXPLAIN SELECT 1 2103FROM t2 LEFT JOIN 2104((t1 JOIN t3 ON t1.a = t3.pk) 2105LEFT JOIN t4 ON 1 ) 2106ON 1 ; 2107id select_type table partitions type possible_keys key key_len ref rows filtered Extra 21081 SIMPLE t2 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 21091 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where 21101 SIMPLE t3 NULL eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 Using index 21111 SIMPLE t4 NULL ALL NULL NULL NULL NULL 0 0.00 Using where 2112Warnings: 2113Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t2` left join (`test`.`t1` join `test`.`t3` left join `test`.`t4` on(1)) on(((`test`.`t3`.`pk` = `test`.`t1`.`a`) and 1)) where 1 2114SELECT 1 2115FROM t2 LEFT JOIN 2116((t1 JOIN t3 ON t1.a = t3.pk) 2117LEFT JOIN t4 ON 1 ) 2118ON 1 ; 21191 21201 21211 2122DROP TABLE t1,t2,t3,t4; 2123# 2124# Bug#51084: Batched key access crashes for SELECT with 2125# derived table and LEFT JOIN 2126# 2127CREATE TABLE t1 ( 2128carrier int, 2129id int PRIMARY KEY 2130); 2131INSERT INTO t1 VALUES (1,11),(1,12),(2,13); 2132CREATE TABLE t2 ( 2133scan_date int, 2134package_id int 2135); 2136INSERT INTO t2 VALUES (2008,21),(2008,22); 2137CREATE TABLE t3 ( 2138carrier int PRIMARY KEY, 2139id int 2140); 2141INSERT INTO t3 VALUES (1,31); 2142CREATE TABLE t4 ( 2143carrier_id int, 2144INDEX carrier_id(carrier_id) 2145); 2146INSERT INTO t4 VALUES (31),(32); 2147 2148SELECT COUNT(*) 2149FROM (t2 JOIN t1) LEFT JOIN (t3 JOIN t4 ON t3.id = t4.carrier_id) 2150ON t3.carrier = t1.carrier; 2151COUNT(*) 21526 2153 2154EXPLAIN 2155SELECT COUNT(*) 2156FROM (t2 JOIN t1) LEFT JOIN (t3 JOIN t4 ON t3.id = t4.carrier_id) 2157ON t3.carrier = t1.carrier; 2158id select_type table partitions type possible_keys key key_len ref rows filtered Extra 21591 SIMPLE t2 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 21601 SIMPLE t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using join buffer (Block Nested Loop) 21611 SIMPLE t3 NULL ALL PRIMARY NULL NULL NULL 1 100.00 Using where; Using join buffer (Block Nested Loop) 21621 SIMPLE t4 NULL index carrier_id carrier_id 5 NULL 2 100.00 Using where; Using index; Using join buffer (Block Nested Loop) 2163Warnings: 2164Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t2` join `test`.`t1` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`carrier_id` = `test`.`t3`.`id`) and (`test`.`t3`.`carrier` = `test`.`t1`.`carrier`))) where 1 2165 2166DROP TABLE t1,t2,t3,t4; 2167# 2168# Bug#45267: Incomplete check caused wrong result. 2169# 2170CREATE TABLE t1 ( 2171`pk` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY 2172); 2173CREATE TABLE t3 ( 2174`pk` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY 2175); 2176INSERT INTO t3 VALUES 2177(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15), 2178(16),(17),(18),(19),(20); 2179CREATE TABLE t2 ( 2180`pk` int(11) NOT NULL AUTO_INCREMENT, 2181`int_nokey` int(11) NOT NULL, 2182`time_key` time NOT NULL, 2183PRIMARY KEY (`pk`), 2184KEY `time_key` (`time_key`) 2185); 2186INSERT INTO t2 VALUES (10,9,'22:36:46'),(11,0,'08:46:46'); 2187SELECT DISTINCT t1.`pk` 2188FROM t1 RIGHT JOIN t2 STRAIGHT_JOIN t3 ON t2.`int_nokey` ON t2.`time_key` 2189GROUP BY 1; 2190pk 2191NULL 2192DROP TABLE IF EXISTS t1, t2, t3; 2193# 2194# BUG#52636 6.0 allowing JOINs on NULL values w/ optimizer_join_cache_level = 5-8 2195# 2196CREATE TABLE t1 (b int); 2197INSERT INTO t1 VALUES (NULL),(3); 2198CREATE TABLE t2 (a int, b int, KEY (b)); 2199INSERT INTO t2 VALUES (100,NULL),(150,200); 2200EXPLAIN SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b = t1.b; 2201id select_type table partitions type possible_keys key key_len ref rows filtered Extra 22021 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 22031 SIMPLE t2 NULL ref b b 5 test.t1.b 2 100.00 Using join buffer (Batched Key Access) 2204Warnings: 2205Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t1` left join `test`.`t2` FORCE INDEX (`b`) on((`test`.`t2`.`b` = `test`.`t1`.`b`)) where 1 2206SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b = t1.b; 2207a 2208NULL 2209NULL 2210delete from t1; 2211INSERT INTO t1 VALUES (NULL),(NULL); 2212EXPLAIN SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b = t1.b; 2213id select_type table partitions type possible_keys key key_len ref rows filtered Extra 22141 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 22151 SIMPLE t2 NULL ref b b 5 test.t1.b 2 100.00 Using join buffer (Batched Key Access) 2216Warnings: 2217Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t1` left join `test`.`t2` FORCE INDEX (`b`) on((`test`.`t2`.`b` = `test`.`t1`.`b`)) where 1 2218SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b = t1.b; 2219a 2220NULL 2221NULL 2222DROP TABLE t1,t2; 2223CREATE TABLE t1 (b varchar(100)); 2224INSERT INTO t1 VALUES (NULL),("some varchar"); 2225CREATE TABLE t2 (a int, b varchar(100), KEY (b)); 2226INSERT INTO t2 VALUES (100,NULL),(150,"varchar"),(200,NULL),(250,"long long varchar"); 2227explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; 2228id select_type table partitions type possible_keys key key_len ref rows filtered Extra 22291 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 22301 SIMPLE t2 NULL ALL b NULL NULL NULL 4 100.00 Using where; Using join buffer (Block Nested Loop) 2231Warnings: 2232Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`b` = `test`.`t1`.`b`)) where 1 2233SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; 2234a 2235NULL 2236NULL 2237DROP TABLE t1,t2; 2238# 2239# BUG#54359 "Extra rows with join_cache_level=7,8 and two joins 2240# --and multi-column index" 2241# 2242CREATE TABLE t1 ( 2243`pk` int(11) NOT NULL, 2244`col_int_key` int(11) DEFAULT NULL, 2245`col_varchar_key` varchar(1) DEFAULT NULL, 2246`col_varchar_nokey` varchar(1) DEFAULT NULL, 2247KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)) 2248; 2249INSERT INTO t1 VALUES (4,9,'k','k'); 2250INSERT INTO t1 VALUES (12,5,'k','k'); 2251explain SELECT table2 .`col_int_key` FROM t1 table2, 2252t1 table3 force index (`col_varchar_key`) 2253where table3 .`pk` and table3 .`col_int_key` >= table2 .`pk` 2254and table3 .`col_varchar_key` = table2 .`col_varchar_nokey`; 2255id select_type table partitions type possible_keys key key_len ref rows filtered Extra 22561 SIMPLE table3 NULL ALL col_varchar_key NULL NULL NULL 2 50.00 Using where 22571 SIMPLE table2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where; Using join buffer (Block Nested Loop) 2258Warnings: 2259Note 1003 /* select#1 */ select `test`.`table2`.`col_int_key` AS `col_int_key` from `test`.`t1` `table2` join `test`.`t1` `table3` FORCE INDEX (`col_varchar_key`) where ((`test`.`table2`.`col_varchar_nokey` = `test`.`table3`.`col_varchar_key`) and `test`.`table3`.`pk` and (`test`.`table3`.`col_int_key` >= `test`.`table2`.`pk`)) 2260SELECT table2 .`col_int_key` FROM t1 table2, 2261t1 table3 force index (`col_varchar_key`) 2262where table3 .`pk` and table3 .`col_int_key` >= table2 .`pk` 2263and table3 .`col_varchar_key` = table2 .`col_varchar_nokey`; 2264col_int_key 22659 22669 2267drop table t1; 2268# 2269# BUG#54481 "GROUP BY loses effect with JOIN + ORDER BY + LIMIT 2270# and join_cache_level=5-8" 2271# 2272CREATE TABLE t1 ( 2273`col_int_key` int, 2274`col_datetime` datetime, 2275KEY `col_int_key` (`col_int_key`) 2276); 2277INSERT INTO t1 VALUES (2,'2003-02-11 21:19:41'); 2278INSERT INTO t1 VALUES (3,'2009-10-18 02:27:49'); 2279INSERT INTO t1 VALUES (0,'2000-09-26 07:45:57'); 2280CREATE TABLE t2 ( 2281`col_int` int, 2282`col_int_key` int, 2283KEY `col_int_key` (`col_int_key`) 2284); 2285INSERT INTO t2 VALUES (14,1); 2286INSERT INTO t2 VALUES (98,1); 2287explain SELECT t1.col_int_key, t1.col_datetime 2288FROM t1,t2 2289WHERE t2.col_int_key = 1 AND t2.col_int >= 3 2290GROUP BY t1.col_int_key 2291ORDER BY t1.col_int_key, t1.col_datetime 2292LIMIT 2; 2293id select_type table partitions type possible_keys key key_len ref rows filtered Extra 22941 SIMPLE t2 NULL ref col_int_key col_int_key 5 const 1 50.00 Using where; Using temporary; Using filesort 22951 SIMPLE t1 NULL ALL col_int_key NULL NULL NULL 3 100.00 Using join buffer (Block Nested Loop) 2296Warnings: 2297Note 1003 /* select#1 */ select `test`.`t1`.`col_int_key` AS `col_int_key`,`test`.`t1`.`col_datetime` AS `col_datetime` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`col_int_key` = 1) and (`test`.`t2`.`col_int` >= 3)) group by `test`.`t1`.`col_int_key` order by `test`.`t1`.`col_int_key`,`test`.`t1`.`col_datetime` limit 2 2298SELECT t1.col_int_key, t1.col_datetime 2299FROM t1,t2 2300WHERE t2.col_int_key = 1 AND t2.col_int >= 3 2301GROUP BY t1.col_int_key 2302ORDER BY t1.col_int_key, t1.col_datetime 2303LIMIT 2; 2304col_int_key col_datetime 23050 2000-09-26 07:45:57 23062 2003-02-11 21:19:41 2307explain SELECT t1.col_int_key, t1.col_datetime 2308FROM t1 force index (col_int_key), t2 ignore index (col_int_key) 2309WHERE t2.col_int_key = 1 AND t2.col_int >= 3 2310GROUP BY t1.col_int_key 2311ORDER BY t1.col_int_key, t1.col_datetime 2312LIMIT 2; 2313id select_type table partitions type possible_keys key key_len ref rows filtered Extra 23141 SIMPLE t1 NULL index col_int_key col_int_key 5 NULL 3 100.00 Using temporary; Using filesort 23151 SIMPLE t2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where 2316Warnings: 2317Note 1003 /* select#1 */ select `test`.`t1`.`col_int_key` AS `col_int_key`,`test`.`t1`.`col_datetime` AS `col_datetime` from `test`.`t1` FORCE INDEX (`col_int_key`) join `test`.`t2` IGNORE INDEX (`col_int_key`) where ((`test`.`t2`.`col_int_key` = 1) and (`test`.`t2`.`col_int` >= 3)) group by `test`.`t1`.`col_int_key` order by `test`.`t1`.`col_int_key`,`test`.`t1`.`col_datetime` limit 2 2318SELECT t1.col_int_key, t1.col_datetime 2319FROM t1 force index (col_int_key), t2 ignore index (col_int_key) 2320WHERE t2.col_int_key = 1 AND t2.col_int >= 3 2321GROUP BY t1.col_int_key 2322ORDER BY t1.col_int_key, t1.col_datetime 2323LIMIT 2; 2324col_int_key col_datetime 23250 2000-09-26 07:45:57 23262 2003-02-11 21:19:41 2327drop table t1,t2; 2328 2329# Bug#11766522 "59651: ASSERTION `TABLE_REF->HAS_RECORD' FAILED 2330# WITH JOIN_CACHE_LEVEL=3" 2331 2332CREATE TABLE t1 ( 2333b varchar(20) 2334) ; 2335INSERT INTO t1 VALUES ('1'),('1'); 2336CREATE TABLE t4 ( 2337col253 text 2338) ; 2339INSERT INTO t4 VALUES (''),('pf'); 2340CREATE TABLE t6 ( 2341col282 timestamp 2342) ; 2343INSERT INTO t6 VALUES ('2010-11-07 01:04:45'),('2010-12-13 01:36:32'); 2344CREATE TABLE t7 ( 2345col319 timestamp NOT NULL, 2346UNIQUE KEY idx263 (col319) 2347) ; 2348insert into t7 values("2000-01-01"),("2000-01-02"); 2349CREATE TABLE t3 ( 2350col582 char(230) CHARACTER SET utf8 DEFAULT NULL 2351) ; 2352INSERT INTO t3 VALUES ('cymej'),('spb'); 2353CREATE TABLE t5 ( 2354col712 time 2355) ; 2356insert into t5 values(0),(0); 2357CREATE TABLE t8 ( 2358col804 char(169), 2359col805 varchar(51) 2360) ; 2361INSERT INTO t8 VALUES ('tmqcb','pwk'); 2362CREATE TABLE t2 ( 2363col841 varchar(10) 2364) ; 2365INSERT INTO t2 VALUES (''),(''); 2366set join_buffer_size=1; 2367Warnings: 2368Warning 1292 Truncated incorrect join_buffer_size value: '1' 2369select @@join_buffer_size; 2370@@join_buffer_size 2371128 2372select count(*) from 2373(t1 join t2 join t3) 2374left join t4 on 1 2375left join t5 on 1 like t4.col253 2376left join t6 on t5.col712 is null 2377left join t7 on t1.b <=>t7.col319 2378left join t8 on t3.col582 <= 1; 2379count(*) 238032 2381drop table t1,t2,t3,t4,t5,t6,t7,t8; 2382# 2383# Bug#12616131 - JCL: NULL VS DATE + TWICE AS MANY ROWS 2384# RETURNED WHEN JCL>=7 2385# 2386CREATE TABLE t1 (t1a int, t1b int); 2387INSERT INTO t1 VALUES (99, NULL),(99, 3),(99,0); 2388CREATE TABLE t2 (t2a int, t2b int, KEY idx (t2b)); 2389INSERT INTO t2 VALUES (100,0),(150,200),(999, 0),(999, NULL); 2390 2391# t2b is NULL-able 2392 2393EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b; 2394id select_type table partitions type possible_keys key key_len ref rows filtered Extra 23951 SIMPLE t1 NULL ALL NULL NULL NULL NULL 3 100.00 NULL 23961 SIMPLE t2 NULL ref idx idx 5 test.t1.t1b 2 100.00 Using join buffer (Batched Key Access) 2397Warnings: 2398Note 1003 /* select#1 */ select `test`.`t1`.`t1a` AS `t1a`,`test`.`t1`.`t1b` AS `t1b`,`test`.`t2`.`t2a` AS `t2a`,`test`.`t2`.`t2b` AS `t2b` from `test`.`t1` left join `test`.`t2` FORCE INDEX (`idx`) on((`test`.`t2`.`t2b` = `test`.`t1`.`t1b`)) where 1 2399SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b; 2400t1a t1b t2a t2b 240199 0 100 0 240299 0 999 0 240399 NULL NULL NULL 240499 3 NULL NULL 2405 2406EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b; 2407id select_type table partitions type possible_keys key key_len ref rows filtered Extra 24081 SIMPLE t1 NULL ALL NULL NULL NULL NULL 3 100.00 NULL 24091 SIMPLE t2 NULL ref idx idx 5 test.t1.t1b 2 100.00 Using where; Using join buffer (Batched Key Access) 2410Warnings: 2411Note 1003 /* select#1 */ select `test`.`t1`.`t1a` AS `t1a`,`test`.`t1`.`t1b` AS `t1b`,`test`.`t2`.`t2a` AS `t2a`,`test`.`t2`.`t2b` AS `t2b` from `test`.`t1` left join `test`.`t2` FORCE INDEX (`idx`) on((`test`.`t2`.`t2b` <=> `test`.`t1`.`t1b`)) where 1 2412SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b; 2413t1a t1b t2a t2b 241499 0 100 0 241599 0 999 0 241699 NULL 999 NULL 241799 3 NULL NULL 2418 2419DROP TABLE t2; 2420CREATE TABLE t2 (t2a int, t2b int NOT NULL, KEY idx (t2b)); 2421INSERT INTO t2 VALUES (100,0),(150,200),(999, 0); 2422 2423# t2b is NOT NULL 2424 2425EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b; 2426id select_type table partitions type possible_keys key key_len ref rows filtered Extra 24271 SIMPLE t1 NULL ALL NULL NULL NULL NULL 3 100.00 NULL 24281 SIMPLE t2 NULL ref idx idx 4 test.t1.t1b 2 100.00 Using join buffer (Batched Key Access) 2429Warnings: 2430Note 1003 /* select#1 */ select `test`.`t1`.`t1a` AS `t1a`,`test`.`t1`.`t1b` AS `t1b`,`test`.`t2`.`t2a` AS `t2a`,`test`.`t2`.`t2b` AS `t2b` from `test`.`t1` left join `test`.`t2` FORCE INDEX (`idx`) on((`test`.`t2`.`t2b` = `test`.`t1`.`t1b`)) where 1 2431SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b; 2432t1a t1b t2a t2b 243399 0 100 0 243499 0 999 0 243599 NULL NULL NULL 243699 3 NULL NULL 2437 2438EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b; 2439id select_type table partitions type possible_keys key key_len ref rows filtered Extra 24401 SIMPLE t1 NULL ALL NULL NULL NULL NULL 3 100.00 NULL 24411 SIMPLE t2 NULL ref idx idx 4 test.t1.t1b 2 100.00 Using where; Using join buffer (Batched Key Access) 2442Warnings: 2443Note 1003 /* select#1 */ select `test`.`t1`.`t1a` AS `t1a`,`test`.`t1`.`t1b` AS `t1b`,`test`.`t2`.`t2a` AS `t2a`,`test`.`t2`.`t2b` AS `t2b` from `test`.`t1` left join `test`.`t2` FORCE INDEX (`idx`) on((`test`.`t2`.`t2b` <=> `test`.`t1`.`t1b`)) where 1 2444SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b; 2445t1a t1b t2a t2b 244699 0 100 0 244799 0 999 0 244899 NULL NULL NULL 244999 3 NULL NULL 2450 2451DROP TABLE t1,t2; 2452# 2453# BUG#12619399 - JCL: NO ROWS VS 3X NULL QUERY OUTPUT WHEN JCL>=5 2454# 2455CREATE TABLE t1 ( 2456c1 INTEGER NOT NULL, 2457c2_key INTEGER NOT NULL, 2458KEY col_int_key (c2_key) 2459) ENGINE=InnoDB; 2460INSERT INTO t1 VALUES (24,204); 2461CREATE TABLE t2 ( 2462pk INTEGER NOT NULL, 2463PRIMARY KEY (pk) 2464) ENGINE=InnoDB; 2465INSERT INTO t2 VALUES (10); 2466CREATE TABLE t3 ( 2467c1 INTEGER, 2468KEY k1 (c1) 2469) ENGINE=InnoDB; 2470INSERT INTO t3 VALUES (NULL), (NULL); 2471set @old_opt_switch=@@optimizer_switch; 2472 2473explain SELECT t3.c1 FROM t3 2474WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1) 2475XOR TRUE; 2476id select_type table partitions type possible_keys key key_len ref rows filtered Extra 24771 PRIMARY t3 NULL index NULL k1 5 NULL 2 100.00 Using where; Using index 24782 DEPENDENT SUBQUERY t1 NULL ref col_int_key col_int_key 4 func 1 100.00 Using where; Full scan on NULL key 24792 DEPENDENT SUBQUERY t2 NULL ALL PRIMARY NULL NULL NULL 1 100.00 Range checked for each record (index map: 0x1) 2480Warnings: 2481Note 1003 /* select#1 */ select `test`.`t3`.`c1` AS `c1` from `test`.`t3` where (<in_optimizer>(`test`.`t3`.`c1`,<exists>(/* select#2 */ select 1 from `test`.`t2` join `test`.`t1` where (<if>(outer_field_is_not_null, (<cache>(`test`.`t3`.`c1`) = `test`.`t1`.`c2_key`), true) and (`test`.`t2`.`pk` < `test`.`t1`.`c1`)))) xor TRUE) 2482explain SELECT t3.c1 FROM t3 2483WHERE t3.c1 = ANY (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1) 2484XOR TRUE; 2485id select_type table partitions type possible_keys key key_len ref rows filtered Extra 24861 PRIMARY t3 NULL index NULL k1 5 NULL 2 100.00 Using where; Using index 24872 DEPENDENT SUBQUERY t1 NULL ref col_int_key col_int_key 4 func 1 100.00 Using where; Full scan on NULL key 24882 DEPENDENT SUBQUERY t2 NULL ALL PRIMARY NULL NULL NULL 1 100.00 Range checked for each record (index map: 0x1) 2489Warnings: 2490Note 1003 /* select#1 */ select `test`.`t3`.`c1` AS `c1` from `test`.`t3` where (<in_optimizer>(`test`.`t3`.`c1`,<exists>(/* select#2 */ select 1 from `test`.`t2` join `test`.`t1` where (<if>(outer_field_is_not_null, (<cache>(`test`.`t3`.`c1`) = `test`.`t1`.`c2_key`), true) and (`test`.`t2`.`pk` < `test`.`t1`.`c1`)))) xor TRUE) 2491explain SELECT t3.c1 FROM t3 2492WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1) 2493XOR TRUE; 2494id select_type table partitions type possible_keys key key_len ref rows filtered Extra 24951 PRIMARY t3 NULL index NULL k1 5 NULL 2 100.00 Using where; Using index 24962 DEPENDENT SUBQUERY t1 NULL ref col_int_key col_int_key 4 func 1 100.00 Using where; Full scan on NULL key 24972 DEPENDENT SUBQUERY t2 NULL ALL PRIMARY NULL NULL NULL 1 100.00 Range checked for each record (index map: 0x1) 2498Warnings: 2499Note 1003 /* select#1 */ select `test`.`t3`.`c1` AS `c1` from `test`.`t3` where (<in_optimizer>(`test`.`t3`.`c1`,<exists>(/* select#2 */ select 1 from `test`.`t2` join `test`.`t1` where (<if>(outer_field_is_not_null, (<cache>(`test`.`t3`.`c1`) = `test`.`t1`.`c2_key`), true) and (`test`.`t2`.`pk` < `test`.`t1`.`c1`)))) xor TRUE) 2500explain SELECT t3.c1 FROM t3 2501WHERE t3.c1 NOT IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1); 2502id select_type table partitions type possible_keys key key_len ref rows filtered Extra 25031 PRIMARY t3 NULL index NULL k1 5 NULL 2 100.00 Using where; Using index 25042 DEPENDENT SUBQUERY t1 NULL ref col_int_key col_int_key 4 func 1 100.00 Using where; Full scan on NULL key 25052 DEPENDENT SUBQUERY t2 NULL ALL PRIMARY NULL NULL NULL 1 100.00 Range checked for each record (index map: 0x1) 2506Warnings: 2507Note 1003 /* select#1 */ select `test`.`t3`.`c1` AS `c1` from `test`.`t3` where (not(<in_optimizer>(`test`.`t3`.`c1`,<exists>(/* select#2 */ select 1 from `test`.`t2` join `test`.`t1` where (<if>(outer_field_is_not_null, (<cache>(`test`.`t3`.`c1`) = `test`.`t1`.`c2_key`), true) and (`test`.`t2`.`pk` < `test`.`t1`.`c1`)))))) 2508explain SELECT t3.c1 FROM t3 2509WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1); 2510id select_type table partitions type possible_keys key key_len ref rows filtered Extra 25111 SIMPLE t1 NULL ALL col_int_key NULL NULL NULL 1 100.00 Start temporary 25121 SIMPLE t2 NULL ALL PRIMARY NULL NULL NULL 1 100.00 Range checked for each record (index map: 0x1) 25131 SIMPLE t3 NULL ref k1 k1 5 test.t1.c2_key 1 100.00 Using index; End temporary 2514Warnings: 2515Note 1003 /* select#1 */ select `test`.`t3`.`c1` AS `c1` from `test`.`t3` semi join (`test`.`t2` join `test`.`t1`) where ((`test`.`t3`.`c1` = `test`.`t1`.`c2_key`) and (`test`.`t2`.`pk` < `test`.`t1`.`c1`)) 2516SELECT t3.c1 FROM t3 2517WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1) 2518XOR TRUE; 2519c1 2520SELECT t3.c1 FROM t3 2521WHERE t3.c1 = ANY (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1) 2522XOR TRUE; 2523c1 2524SELECT t3.c1 FROM t3 2525WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1) 2526XOR TRUE; 2527c1 2528SELECT t3.c1 FROM t3 2529WHERE t3.c1 NOT IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1); 2530c1 2531SELECT t3.c1 FROM t3 2532WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1); 2533c1 2534 2535set @@optimizer_switch=@old_opt_switch; 2536DROP TABLE t1, t2, t3; 2537set @@join_buffer_size=default; 2538 2539# BUG#12586926 "EXTRA ROW WITH JOIN + GROUP BY + ORDER BY WITH 2540# JCL>=5 AND MRR ENABLED" 2541 2542CREATE TABLE t1 ( col_int_key int(11) NOT NULL, 2543col_varchar_key varchar(1) NOT NULL, 2544KEY col_int_key (col_int_key), 2545KEY col_varchar_key (col_varchar_key,col_int_key) 2546) ENGINE=innodb; 2547INSERT INTO t1 VALUES (0,'j'),(4,'b'),(4,'d'); 2548CREATE TABLE t2 ( 2549col_datetime_key datetime NOT NULL, 2550col_varchar_key varchar(1) NOT NULL, 2551KEY col_varchar_key (col_varchar_key) 2552) ENGINE=innodb; 2553INSERT INTO t2 VALUES ('2003-08-21 00:00:00','b'); 2554explain SELECT MIN(t2.col_datetime_key) AS field1, 2555t1.col_int_key AS field2 2556FROM t1 2557LEFT JOIN t2 force index (col_varchar_key) 2558ON t1.col_varchar_key = t2.col_varchar_key 2559GROUP BY field2 2560ORDER BY field1; 2561id select_type table partitions type possible_keys key key_len ref rows filtered Extra 25621 SIMPLE t1 NULL index col_int_key,col_varchar_key col_varchar_key 7 NULL 3 100.00 Using index; Using temporary; Using filesort 25631 SIMPLE t2 NULL ref col_varchar_key col_varchar_key 3 test.t1.col_varchar_key 1 100.00 Using join buffer (Batched Key Access) 2564Warnings: 2565Note 1003 /* select#1 */ select min(`test`.`t2`.`col_datetime_key`) AS `field1`,`test`.`t1`.`col_int_key` AS `field2` from `test`.`t1` left join `test`.`t2` FORCE INDEX (`col_varchar_key`) on((`test`.`t2`.`col_varchar_key` = `test`.`t1`.`col_varchar_key`)) where 1 group by `field2` order by `field1` 2566SELECT MIN(t2.col_datetime_key) AS field1, 2567t1.col_int_key AS field2 2568FROM t1 2569LEFT JOIN t2 force index (col_varchar_key) 2570ON t1.col_varchar_key = t2.col_varchar_key 2571GROUP BY field2 2572ORDER BY field1; 2573field1 field2 2574NULL 0 25752003-08-21 00:00:00 4 2576DROP TABLE t1,t2; 2577 2578# BUG#12619510 "JCL: MORE ROWS AND DIFFERENT OUTPUT WITH JCL>=5" 2579 2580CREATE TABLE t1 ( 2581col_int_key int(11) NOT NULL, 2582col_datetime_key datetime NOT NULL, 2583col_varchar_nokey varchar(1) NOT NULL, 2584KEY col_int_key (col_int_key), 2585KEY col_datetime_key (col_datetime_key) 2586); 2587INSERT INTO t1 VALUES (7,'2004-06-06 04:22:12','v'); 2588INSERT INTO t1 VALUES (0,'2005-11-13 01:12:31','s'); 2589INSERT INTO t1 VALUES (9,'2002-05-04 01:50:00','l'); 2590INSERT INTO t1 VALUES (3,'2004-10-27 10:28:45','y'); 2591INSERT INTO t1 VALUES (4,'2006-07-22 05:24:23','c'); 2592INSERT INTO t1 VALUES (2,'2002-05-16 21:34:03','i'); 2593INSERT INTO t1 VALUES (5,'2008-04-17 10:45:30','h'); 2594INSERT INTO t1 VALUES (3,'2009-04-21 02:58:02','q'); 2595INSERT INTO t1 VALUES (1,'2008-01-11 11:01:51','a'); 2596INSERT INTO t1 VALUES (3,'1900-01-01 00:00:00','v'); 2597INSERT INTO t1 VALUES (6,'2007-05-17 18:24:57','u'); 2598INSERT INTO t1 VALUES (7,'2007-08-07 00:00:00','s'); 2599INSERT INTO t1 VALUES (5,'2001-08-28 00:00:00','y'); 2600INSERT INTO t1 VALUES (1,'2004-04-16 00:27:28','z'); 2601INSERT INTO t1 VALUES (204,'2005-05-03 07:06:22','h'); 2602INSERT INTO t1 VALUES (224,'2009-03-11 17:09:50','p'); 2603INSERT INTO t1 VALUES (9,'2007-12-08 01:54:28','e'); 2604INSERT INTO t1 VALUES (5,'2009-07-28 18:19:54','i'); 2605INSERT INTO t1 VALUES (0,'2008-06-08 00:00:00','y'); 2606INSERT INTO t1 VALUES (3,'2005-02-09 09:20:26','w'); 2607CREATE TABLE t2 ( 2608pk int(11) NOT NULL, 2609col_varchar_key varchar(1) NOT NULL, 2610PRIMARY KEY (pk) 2611); 2612INSERT INTO t2 VALUES 2613(1,'j'),(2,'v'),(3,'c'),(4,'m'),(5,'d'),(6,'d'),(7,'y'), 2614(8,'t'),(9,'d'),(10,'s'),(11,'r'),(12,'m'),(13,'b'),(14,'x'), 2615(15,'g'),(16,'p'),(17,'q'),(18,'w'),(19,'d'),(20,'e'); 2616explain SELECT t2.col_varchar_key AS field1 , COUNT(DISTINCT t1.col_varchar_nokey), t2.pk AS field4 2617FROM t1 2618RIGHT JOIN t2 ON t2.pk = t1.col_int_key 2619GROUP BY field1 , field4 2620ORDER BY t1.col_datetime_key ; 2621id select_type table partitions type possible_keys key key_len ref rows filtered Extra 26221 SIMPLE t2 NULL ALL NULL NULL NULL NULL 20 100.00 Using temporary; Using filesort 26231 SIMPLE t1 NULL ref col_int_key col_int_key 4 test.t2.pk 2 100.00 Using join buffer (Batched Key Access) 2624Warnings: 2625Note 1003 /* select#1 */ select `test`.`t2`.`col_varchar_key` AS `field1`,count(distinct `test`.`t1`.`col_varchar_nokey`) AS `COUNT(DISTINCT t1.col_varchar_nokey)`,`test`.`t2`.`pk` AS `field4` from `test`.`t2` left join `test`.`t1` on((`test`.`t1`.`col_int_key` = `test`.`t2`.`pk`)) where 1 group by `field1`,`field4` order by `test`.`t1`.`col_datetime_key` 2626SELECT t2.col_varchar_key AS field1 , COUNT(DISTINCT t1.col_varchar_nokey), t2.pk AS field4 2627FROM t1 2628RIGHT JOIN t2 ON t2.pk = t1.col_int_key 2629GROUP BY field1 , field4 2630ORDER BY t1.col_datetime_key ; 2631field1 COUNT(DISTINCT t1.col_varchar_nokey) field4 2632b 0 13 2633c 4 3 2634d 0 19 2635d 1 6 2636d 2 9 2637d 3 5 2638e 0 20 2639g 0 15 2640j 2 1 2641m 0 12 2642m 1 4 2643p 0 16 2644q 0 17 2645r 0 11 2646s 0 10 2647t 0 8 2648v 1 2 2649w 0 18 2650x 0 14 2651y 2 7 2652DROP TABLE t1,t2; 2653 2654# BUG#12619868 "JCL: MORE ROWS OF OUTPUT WHEN JCL>=5" 2655 2656CREATE TABLE t1 (col_varchar_key varchar(1)); 2657CREATE TABLE t2 ( 2658pk int(11) NOT NULL, 2659col_int_nokey int(11) NOT NULL, 2660col_int_key int(11) NOT NULL, 2661PRIMARY KEY (pk), 2662KEY col_int_key (col_int_key) 2663); 2664INSERT INTO t2 VALUES (5,3,9); 2665INSERT INTO t2 VALUES (6,246,24); 2666INSERT INTO t2 VALUES (7,2,6); 2667INSERT INTO t2 VALUES (8,9,1); 2668INSERT INTO t2 VALUES (9,3,6); 2669INSERT INTO t2 VALUES (10,8,2); 2670INSERT INTO t2 VALUES (11,1,4); 2671INSERT INTO t2 VALUES (12,8,8); 2672INSERT INTO t2 VALUES (13,8,4); 2673INSERT INTO t2 VALUES (14,5,4); 2674INSERT INTO t2 VALUES (15,7,7); 2675INSERT INTO t2 VALUES (16,5,4); 2676INSERT INTO t2 VALUES (17,1,1); 2677INSERT INTO t2 VALUES (18,6,9); 2678INSERT INTO t2 VALUES (19,2,4); 2679INSERT INTO t2 VALUES (20,9,8); 2680explain SELECT t1.col_varchar_key AS field1, alias2.col_int_key AS field4 2681FROM t2 AS alias2 STRAIGHT_JOIN t2 AS alias3 ON alias3.pk = 2682alias2.col_int_nokey 2683left join t1 2684ON alias3.col_int_nokey 2685GROUP BY field1, field4 2686LIMIT 15; 2687id select_type table partitions type possible_keys key key_len ref rows filtered Extra 26881 SIMPLE t1 NULL system NULL NULL NULL NULL 0 0.00 const row not found 26891 SIMPLE alias2 NULL ALL NULL NULL NULL NULL 16 100.00 Using temporary; Using filesort 26901 SIMPLE alias3 NULL eq_ref PRIMARY PRIMARY 4 test.alias2.col_int_nokey 1 100.00 Using join buffer (Batched Key Access) 2691Warnings: 2692Note 1003 /* select#1 */ select NULL AS `field1`,`test`.`alias2`.`col_int_key` AS `field4` from `test`.`t2` `alias2` straight_join `test`.`t2` `alias3` where (`test`.`alias3`.`pk` = `test`.`alias2`.`col_int_nokey`) group by `field1`,`field4` limit 15 2693SELECT t1.col_varchar_key AS field1, alias2.col_int_key AS field4 2694FROM t2 AS alias2 STRAIGHT_JOIN t2 AS alias3 ON alias3.pk = 2695alias2.col_int_nokey 2696left join t1 2697ON alias3.col_int_nokey 2698GROUP BY field1, field4 2699LIMIT 15; 2700field1 field4 2701NULL 1 2702NULL 2 2703NULL 4 2704NULL 7 2705NULL 8 2706NULL 9 2707DROP TABLE t1,t2; 2708 2709# BUG#12722133 - JCL: JOIN QUERY GIVES DIFFERENT RESULTS AT 2710# JCL=6 ONLY [NULL VERSUS NULL+#INTS] 2711 2712CREATE TABLE t1 (pk INTEGER PRIMARY KEY, k INTEGER, i INTEGER, KEY k(k)); 2713CREATE TABLE t2 LIKE t1; 2714CREATE TABLE t3 LIKE t1; 2715CREATE TABLE t4 LIKE t1; 2716INSERT INTO t1 VALUES (6,NULL,6),(0,1,11); 2717INSERT INTO t2 VALUES (1,NULL,NULL),(4,7,NULL); 2718INSERT INTO t3 VALUES (2,3,0),(3,4,4); 2719INSERT INTO t4 VALUES (1,9,-1),(4,7,NULL); 2720EXPLAIN SELECT t2.pk as t2_pk, t4.pk as t4_pk, t4.k as t4_k, t4.i 2721as t4_i FROM t1 2722LEFT JOIN t2 ON t1.k = t2.pk 2723LEFT JOIN t3 ON t3.i 2724LEFT JOIN t4 ON t4.pk = t2.pk; 2725id select_type table partitions type possible_keys key key_len ref rows filtered Extra 27261 SIMPLE t1 NULL index NULL k 5 NULL 2 100.00 Using index 27271 SIMPLE t2 NULL eq_ref PRIMARY PRIMARY 4 test.t1.k 1 100.00 Using index 27281 SIMPLE t3 NULL ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop) 27291 SIMPLE t4 NULL eq_ref PRIMARY PRIMARY 4 test.t2.pk 1 100.00 Using join buffer (Batched Key Access) 2730Warnings: 2731Note 1003 /* select#1 */ select `test`.`t2`.`pk` AS `t2_pk`,`test`.`t4`.`pk` AS `t4_pk`,`test`.`t4`.`k` AS `t4_k`,`test`.`t4`.`i` AS `t4_i` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`pk` = `test`.`t1`.`k`)) left join `test`.`t3` on(`test`.`t3`.`i`) left join `test`.`t4` on((`test`.`t4`.`pk` = `test`.`t2`.`pk`)) where 1 2732SELECT t2.pk as t2_pk, t4.pk as t4_pk, t4.k as t4_k, t4.i 2733as t4_i FROM t1 2734LEFT JOIN t2 ON t1.k = t2.pk 2735LEFT JOIN t3 ON t3.i 2736LEFT JOIN t4 ON t4.pk = t2.pk; 2737t2_pk t4_pk t4_k t4_i 27381 1 9 -1 2739NULL NULL NULL NULL 2740DROP TABLE t1, t2, t3, t4; 2741 2742# BUG#12827509 - BNL/BKA: SELECT LEFT/RIGHT JOIN QUERY GIVES 2743# DIFFERENT OUTPUT ON BNL=OFF+BKA=ON 2744# (Duplicate of BUG#12722133) 2745 2746CREATE TABLE t1 ( 2747col_int INTEGER 2748); 2749INSERT INTO t1 VALUES (3), (7), (2), (8), (6); 2750CREATE TABLE t2 ( 2751pk INTEGER, 2752col_int INTEGER, 2753PRIMARY KEY (pk) 2754); 2755INSERT INTO t2 VALUES (1,5), (2,8), (6,3), (8,7), (9,9); 2756CREATE TABLE t3 ( 2757pk INTEGER, 2758col_int INTEGER, 2759PRIMARY KEY (pk) 2760); 2761INSERT INTO t3 VALUES (3,2), (4,3), (8,2); 2762CREATE TABLE t4 ( 2763pk INTEGER, 2764col_int INTEGER, 2765PRIMARY KEY (pk) 2766); 2767INSERT INTO t4 VALUES (2,3), (6,1), (8,2); 2768EXPLAIN SELECT t4.col_int 2769FROM t1 2770LEFT JOIN t2 ON t1.col_int = t2.col_int 2771LEFT JOIN t3 ON t2.pk = t3.pk 2772LEFT JOIN t4 ON t4.pk = t2.pk 2773WHERE t1.col_int OR t3.col_int; 2774id select_type table partitions type possible_keys key key_len ref rows filtered Extra 27751 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 NULL 27761 SIMPLE t2 NULL ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (Block Nested Loop) 27771 SIMPLE t3 NULL eq_ref PRIMARY PRIMARY 4 test.t2.pk 1 100.00 Using where; Using join buffer (Batched Key Access) 27781 SIMPLE t4 NULL eq_ref PRIMARY PRIMARY 4 test.t2.pk 1 100.00 Using join buffer (Batched Key Access) 2779Warnings: 2780Note 1003 /* select#1 */ select `test`.`t4`.`col_int` AS `col_int` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`col_int` = `test`.`t1`.`col_int`)) left join `test`.`t3` on((`test`.`t3`.`pk` = `test`.`t2`.`pk`)) left join `test`.`t4` on((`test`.`t4`.`pk` = `test`.`t2`.`pk`)) where (`test`.`t1`.`col_int` or `test`.`t3`.`col_int`) 2781SELECT t4.col_int 2782FROM t1 2783LEFT JOIN t2 ON t1.col_int = t2.col_int 2784LEFT JOIN t3 ON t2.pk = t3.pk 2785LEFT JOIN t4 ON t4.pk = t2.pk 2786WHERE t1.col_int OR t3.col_int; 2787col_int 27883 27891 27902 2791NULL 2792NULL 2793DROP TABLE t1, t2, t3, t4; 2794# 2795# Bug#12997905: VALGRIND: SYSCALL PARAM PWRITE64(BUF) 2796# POINTS TO UNINITIALISED BYTE(S) 2797# 2798CREATE TABLE t1 ( 2799col1 varchar(10), 2800col2 varchar(1024) 2801) ENGINE=innodb; 2802INSERT INTO t1 VALUES ('a','a'); 2803CREATE TABLE t2 (i varchar(10)) ENGINE=innodb; 2804INSERT INTO t2 VALUES ('a'); 2805SELECT t1.col1 2806FROM t1 JOIN t2 ON t1.col1 = t2.i 2807GROUP BY t1.col2; 2808col1 2809a 2810DROP TABLE t1,t2; 2811# End of Bug#12997905 2812# 2813# Bug 13596330 - EXTRA ROW ON SELECT WITH NESTED IN CLAUSE + IS 2814# NULL WHEN SEMIJOIN + BNL IS ON 2815# 2816CREATE TABLE t1 ( 2817col_int_nokey int 2818); 2819INSERT INTO t1 VALUES(-1),(-1); 2820CREATE TABLE t2 ( 2821col_int_nokey int, 2822col_datetime_nokey datetime NOT NULL, 2823col_varchar_key varchar(1), 2824KEY col_varchar_key (col_varchar_key) 2825); 2826INSERT INTO t2 VALUES (9, '2002-08-25 20:35:06', 'e'), 2827(9, '2002-08-25 20:35:06', 'e'); 2828set @optimizer_switch_saved=@@session.optimizer_switch; 2829set @@session.optimizer_switch='semijoin=off'; 2830EXPLAIN SELECT parent1.col_varchar_key 2831FROM t2 AS parent1 LEFT JOIN t1 USING (col_int_nokey) 2832WHERE parent1.col_varchar_key IN 2833( SELECT col_varchar_key FROM t2 AS child1 2834WHERE parent1.col_datetime_nokey IS NULL 2835AND t1.col_int_nokey IS NULL ) 2836; 2837id select_type table partitions type possible_keys key key_len ref rows filtered Extra 28381 PRIMARY parent1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 28391 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop) 28402 DEPENDENT SUBQUERY child1 NULL index_subquery col_varchar_key col_varchar_key 4 func 2 100.00 Using where; Using index 2841Warnings: 2842Note 1276 Field or reference 'test.parent1.col_datetime_nokey' of SELECT #2 was resolved in SELECT #1 2843Note 1276 Field or reference 'test.t1.col_int_nokey' of SELECT #2 was resolved in SELECT #1 2844Note 1003 /* select#1 */ select `test`.`parent1`.`col_varchar_key` AS `col_varchar_key` from `test`.`t2` `parent1` left join `test`.`t1` on((`test`.`t1`.`col_int_nokey` = `test`.`parent1`.`col_int_nokey`)) where <in_optimizer>(`test`.`parent1`.`col_varchar_key`,<exists>(<index_lookup>(<cache>(`test`.`parent1`.`col_varchar_key`) in t2 on col_varchar_key where ((`test`.`parent1`.`col_datetime_nokey` = '0000-00-00 00:00:00') and isnull(`test`.`t1`.`col_int_nokey`) and (<cache>(`test`.`parent1`.`col_varchar_key`) = `test`.`child1`.`col_varchar_key`))))) 2845SELECT parent1.col_varchar_key 2846FROM t2 AS parent1 LEFT JOIN t1 USING (col_int_nokey) 2847WHERE parent1.col_varchar_key IN 2848( SELECT col_varchar_key FROM t2 AS child1 2849WHERE parent1.col_datetime_nokey IS NULL 2850AND t1.col_int_nokey IS NULL ) 2851; 2852col_varchar_key 2853set @@session.optimizer_switch=@optimizer_switch_saved; 2854DROP TABLE t1,t2; 2855set optimizer_switch = default; 2856