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