1drop table if exists t1,t2,t3,t4,t11; 2drop table if exists t1_1,t1_2,t9_1,t9_2,t1aa,t2aa; 3drop view if exists v1; 4CREATE TABLE t1 ( 5Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL, 6Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL 7); 8INSERT INTO t1 VALUES (9410,9412); 9select period from t1; 10period 119410 12select * from t1; 13Period Varor_period 149410 9412 15select t1.* from t1; 16Period Varor_period 179410 9412 18CREATE TABLE t2 ( 19auto int not null auto_increment, 20fld1 int(6) unsigned zerofill DEFAULT '000000' NOT NULL, 21companynr tinyint(2) unsigned zerofill DEFAULT '00' NOT NULL, 22fld3 char(30) DEFAULT '' NOT NULL, 23fld4 char(35) DEFAULT '' NOT NULL, 24fld5 char(35) DEFAULT '' NOT NULL, 25fld6 char(4) DEFAULT '' NOT NULL, 26UNIQUE fld1 (fld1), 27KEY fld3 (fld3), 28PRIMARY KEY (auto) 29); 30select t2.fld3 from t2 where companynr = 58 and fld3 like "%imaginable%"; 31fld3 32imaginable 33select fld3 from t2 where fld3 like "%cultivation" ; 34fld3 35cultivation 36select t2.fld3,companynr from t2 where companynr = 57+1 order by fld3; 37fld3 companynr 38concoct 58 39druggists 58 40engrossing 58 41Eurydice 58 42exclaimers 58 43ferociousness 58 44hopelessness 58 45Huey 58 46imaginable 58 47judges 58 48merging 58 49ostrich 58 50peering 58 51Phelps 58 52presumes 58 53Ruth 58 54sentences 58 55Shylock 58 56straggled 58 57synergy 58 58thanking 58 59tying 58 60unlocks 58 61select fld3,companynr from t2 where companynr = 58 order by fld3; 62fld3 companynr 63concoct 58 64druggists 58 65engrossing 58 66Eurydice 58 67exclaimers 58 68ferociousness 58 69hopelessness 58 70Huey 58 71imaginable 58 72judges 58 73merging 58 74ostrich 58 75peering 58 76Phelps 58 77presumes 58 78Ruth 58 79sentences 58 80Shylock 58 81straggled 58 82synergy 58 83thanking 58 84tying 58 85unlocks 58 86select fld3 from t2 order by fld3 desc limit 10; 87fld3 88youthfulness 89yelped 90Wotan 91workers 92Witt 93witchcraft 94Winsett 95Willy 96willed 97wildcats 98select fld3 from t2 order by fld3 desc limit 5; 99fld3 100youthfulness 101yelped 102Wotan 103workers 104Witt 105select fld3 from t2 order by fld3 desc limit 5,5; 106fld3 107witchcraft 108Winsett 109Willy 110willed 111wildcats 112select t2.fld3 from t2 where fld3 = 'honeysuckle'; 113fld3 114honeysuckle 115select t2.fld3 from t2 where fld3 LIKE 'honeysuckl_'; 116fld3 117honeysuckle 118select t2.fld3 from t2 where fld3 LIKE 'hon_ysuckl_'; 119fld3 120honeysuckle 121select t2.fld3 from t2 where fld3 LIKE 'honeysuckle%'; 122fld3 123honeysuckle 124select t2.fld3 from t2 where fld3 LIKE 'h%le'; 125fld3 126honeysuckle 127select t2.fld3 from t2 where fld3 LIKE 'honeysuckle_'; 128fld3 129select t2.fld3 from t2 where fld3 LIKE 'don_t_find_me_please%'; 130fld3 131explain select t2.fld3 from t2 where fld3 = 'honeysuckle'; 132id select_type table type possible_keys key key_len ref rows Extra 1331 SIMPLE t2 ref fld3 fld3 30 const 1 Using where; Using index 134explain select fld3 from t2 ignore index (fld3) where fld3 = 'honeysuckle'; 135id select_type table type possible_keys key key_len ref rows Extra 1361 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 137explain select fld3 from t2 use index (fld1) where fld3 = 'honeysuckle'; 138id select_type table type possible_keys key key_len ref rows Extra 1391 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 140explain select fld3 from t2 use index (fld3) where fld3 = 'honeysuckle'; 141id select_type table type possible_keys key key_len ref rows Extra 1421 SIMPLE t2 ref fld3 fld3 30 const 1 Using where; Using index 143explain select fld3 from t2 use index (fld1,fld3) where fld3 = 'honeysuckle'; 144id select_type table type possible_keys key key_len ref rows Extra 1451 SIMPLE t2 ref fld3 fld3 30 const 1 Using where; Using index 146explain select fld3 from t2 ignore index (fld3,not_used); 147ERROR 42000: Key 'not_used' doesn't exist in table 't2' 148explain select fld3 from t2 use index (not_used); 149ERROR 42000: Key 'not_used' doesn't exist in table 't2' 150select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3; 151fld3 152honeysuckle 153honoring 154explain select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3; 155id select_type table type possible_keys key key_len ref rows Extra 1561 SIMPLE t2 range fld3 fld3 30 NULL 2 Using where; Using index 157select fld1,fld3 from t2 where fld3="Colombo" or fld3 = "nondecreasing" order by fld3; 158fld1 fld3 159148504 Colombo 160068305 Colombo 161000000 nondecreasing 162select fld1,fld3 from t2 where companynr = 37 and fld3 = 'appendixes'; 163fld1 fld3 164232605 appendixes 1651232605 appendixes 1661232606 appendixes 1671232607 appendixes 1681232608 appendixes 1691232609 appendixes 170select fld1 from t2 where fld1=250501 or fld1="250502"; 171fld1 172250501 173250502 174explain select fld1 from t2 where fld1=250501 or fld1="250502"; 175id select_type table type possible_keys key key_len ref rows Extra 1761 SIMPLE t2 range fld1 fld1 4 NULL 2 Using where; Using index 177select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502; 178fld1 179250501 180250502 181250505 182250601 183explain select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502; 184id select_type table type possible_keys key key_len ref rows Extra 1851 SIMPLE t2 range fld1 fld1 4 NULL 4 Using where; Using index 186select fld1,fld3 from t2 where companynr = 37 and fld3 like 'f%'; 187fld1 fld3 188012001 flanking 189013602 foldout 190013606 fingerings 191018007 fanatic 192018017 featherweight 193018054 fetters 194018103 flint 195018104 flopping 196036002 funereal 197038017 fetched 198038205 firearm 199058004 Fenton 200088303 feminine 201186002 freakish 202188007 flurried 203188505 fitting 204198006 furthermore 205202301 Fitzpatrick 206208101 fiftieth 207208113 freest 208218008 finishers 209218022 feed 210218401 faithful 211226205 foothill 212226209 furnishings 213228306 forthcoming 214228311 fated 215231315 freezes 216232102 forgivably 217238007 filial 218238008 fixedly 219select fld3 from t2 where fld3 like "L%" and fld3 = "ok"; 220fld3 221select fld3 from t2 where (fld3 like "C%" and fld3 = "Chantilly"); 222fld3 223Chantilly 224select fld1,fld3 from t2 where fld1 like "25050%"; 225fld1 fld3 226250501 poisoning 227250502 Iraqis 228250503 heaving 229250504 population 230250505 bomb 231select fld1,fld3 from t2 where fld1 like "25050_"; 232fld1 fld3 233250501 poisoning 234250502 Iraqis 235250503 heaving 236250504 population 237250505 bomb 238select distinct companynr from t2; 239companynr 24000 24137 24236 24350 24458 24529 24640 24753 24865 24941 25034 25168 252select distinct companynr from t2 order by companynr; 253companynr 25400 25529 25634 25736 25837 25940 26041 26150 26253 26358 26465 26568 266select distinct companynr from t2 order by companynr desc; 267companynr 26868 26965 27058 27153 27250 27341 27440 27537 27636 27734 27829 27900 280select distinct t2.fld3,period from t2,t1 where companynr=37 and fld3 like "O%"; 281fld3 period 282obliterates 9410 283offload 9410 284opaquely 9410 285organizer 9410 286overestimating 9410 287overlay 9410 288select distinct fld3 from t2 where companynr = 34 order by fld3; 289fld3 290absentee 291accessed 292ahead 293alphabetic 294Asiaticizations 295attitude 296aye 297bankruptcies 298belays 299Blythe 300bomb 301boulevard 302bulldozes 303cannot 304caressing 305charcoal 306checksumming 307chess 308clubroom 309colorful 310cosy 311creator 312crying 313Darius 314diffusing 315duality 316Eiffel 317Epiphany 318Ernestine 319explorers 320exterminated 321famine 322forked 323Gershwins 324heaving 325Hodges 326Iraqis 327Italianization 328Lagos 329landslide 330libretto 331Majorca 332mastering 333narrowed 334occurred 335offerers 336Palestine 337Peruvianizes 338pharmaceutic 339poisoning 340population 341Pygmalion 342rats 343realest 344recording 345regimented 346retransmitting 347reviver 348rouses 349scars 350sicker 351sleepwalk 352stopped 353sugars 354translatable 355uncles 356unexpected 357uprisings 358versatility 359vest 360select distinct fld3 from t2 limit 10; 361fld3 362abates 363abiding 364Abraham 365abrogating 366absentee 367abut 368accessed 369accruing 370accumulating 371accuracies 372select distinct fld3 from t2 having fld3 like "A%" limit 10; 373fld3 374abates 375abiding 376Abraham 377abrogating 378absentee 379abut 380accessed 381accruing 382accumulating 383accuracies 384select distinct substring(fld3,1,3) from t2 where fld3 like "A%"; 385substring(fld3,1,3) 386aba 387abi 388Abr 389abs 390abu 391acc 392acq 393acu 394Ade 395adj 396Adl 397adm 398Ado 399ads 400adv 401aer 402aff 403afi 404afl 405afo 406agi 407ahe 408aim 409air 410Ald 411alg 412ali 413all 414alp 415alr 416ama 417ame 418amm 419ana 420and 421ane 422Ang 423ani 424Ann 425Ant 426api 427app 428aqu 429Ara 430arc 431Arm 432arr 433Art 434Asi 435ask 436asp 437ass 438ast 439att 440aud 441Aug 442aut 443ave 444avo 445awe 446aye 447Azt 448select distinct substring(fld3,1,3) as a from t2 having a like "A%" order by a limit 10; 449a 450aba 451abi 452Abr 453abs 454abu 455acc 456acq 457acu 458Ade 459adj 460select distinct substring(fld3,1,3) from t2 where fld3 like "A%" limit 10; 461substring(fld3,1,3) 462aba 463abi 464Abr 465abs 466abu 467acc 468acq 469acu 470Ade 471adj 472select distinct substring(fld3,1,3) as a from t2 having a like "A%" limit 10; 473a 474aba 475abi 476Abr 477abs 478abu 479acc 480acq 481acu 482Ade 483adj 484create table t3 ( 485period int not null, 486name char(32) not null, 487companynr int not null, 488price double(11,0), 489price2 double(11,0), 490key (period), 491key (name) 492); 493create temporary table tmp engine = myisam select * from t3; 494insert into t3 select * from tmp; 495insert into tmp select * from t3; 496insert into t3 select * from tmp; 497insert into tmp select * from t3; 498insert into t3 select * from tmp; 499insert into tmp select * from t3; 500insert into t3 select * from tmp; 501insert into tmp select * from t3; 502insert into t3 select * from tmp; 503insert into tmp select * from t3; 504insert into t3 select * from tmp; 505insert into tmp select * from t3; 506insert into t3 select * from tmp; 507insert into tmp select * from t3; 508insert into t3 select * from tmp; 509insert into tmp select * from t3; 510insert into t3 select * from tmp; 511alter table t3 add t2nr int not null auto_increment primary key first; 512drop table tmp; 513SET BIG_TABLES=1; 514select distinct concat(fld3," ",fld3) as namn from t2,t3 where t2.fld1=t3.t2nr order by namn limit 10; 515namn 516Abraham Abraham 517abrogating abrogating 518admonishing admonishing 519Adolph Adolph 520afield afield 521aging aging 522ammonium ammonium 523analyzable analyzable 524animals animals 525animized animized 526SET BIG_TABLES=0; 527select distinct concat(fld3," ",fld3) from t2,t3 where t2.fld1=t3.t2nr order by fld3 limit 10; 528concat(fld3," ",fld3) 529Abraham Abraham 530abrogating abrogating 531admonishing admonishing 532Adolph Adolph 533afield afield 534aging aging 535ammonium ammonium 536analyzable analyzable 537animals animals 538animized animized 539select distinct fld5 from t2 limit 10; 540fld5 541neat 542Steinberg 543jarring 544tinily 545balled 546persist 547attainments 548fanatic 549measures 550rightfulness 551select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10; 552fld3 count(*) 553affixed 1 554and 1 555annoyers 1 556Anthony 1 557assayed 1 558assurers 1 559attendants 1 560bedlam 1 561bedpost 1 562boasted 1 563SET BIG_TABLES=1; 564select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10; 565fld3 count(*) 566affixed 1 567and 1 568annoyers 1 569Anthony 1 570assayed 1 571assurers 1 572attendants 1 573bedlam 1 574bedpost 1 575boasted 1 576SET BIG_TABLES=0; 577select distinct fld3,repeat("a",length(fld3)),count(*) from t2 group by companynr,fld3 limit 100,10; 578fld3 repeat("a",length(fld3)) count(*) 579circus aaaaaa 1 580cited aaaaa 1 581Colombo aaaaaaa 1 582congresswoman aaaaaaaaaaaaa 1 583contrition aaaaaaaaaa 1 584corny aaaaa 1 585cultivation aaaaaaaaaaa 1 586definiteness aaaaaaaaaaaa 1 587demultiplex aaaaaaaaaaa 1 588disappointing aaaaaaaaaaaaa 1 589select distinct companynr,rtrim(space(512+companynr)) from t3 order by 1,2; 590companynr rtrim(space(512+companynr)) 59137 59278 593101 594154 595311 596447 597512 598select distinct fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by fld3; 599fld3 600explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by t3.t2nr,fld3; 601id select_type table type possible_keys key key_len ref rows Extra 6021 SIMPLE t2 ALL fld1 NULL NULL NULL 1199 Using where; Using temporary; Using filesort 6031 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.fld1 1 Using where; Using index 604explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period; 605id select_type table type possible_keys key key_len ref rows Extra 6061 SIMPLE t1 ALL period NULL NULL NULL 41810 Using temporary; Using filesort 6071 SIMPLE t3 ref period period 4 test.t1.period 4181 NULL 608explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10; 609id select_type table type possible_keys key key_len ref rows Extra 6101 SIMPLE t3 index period period 4 NULL 1 NULL 6111 SIMPLE t1 ref period period 4 test.t3.period 4181 NULL 612explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10; 613id select_type table type possible_keys key key_len ref rows Extra 6141 SIMPLE t1 index period period 4 NULL 1 NULL 6151 SIMPLE t3 ref period period 4 test.t1.period 4181 NULL 616select period from t1; 617period 6189410 619select period from t1 where period=1900; 620period 621select fld3,period from t1,t2 where fld1 = 011401 order by period; 622fld3 period 623breaking 9410 624select fld3,period from t2,t3 where t2.fld1 = 011401 and t2.fld1=t3.t2nr and t3.period=1001; 625fld3 period 626breaking 1001 627explain select fld3,period from t2,t3 where t2.fld1 = 011401 and t3.t2nr=t2.fld1 and 1001 = t3.period; 628id select_type table type possible_keys key key_len ref rows Extra 6291 SIMPLE t2 const fld1 fld1 4 const 1 NULL 6301 SIMPLE t3 const PRIMARY,period PRIMARY 4 const 1 NULL 631select fld3,period from t2,t1 where companynr*10 = 37*10; 632fld3 period 633breaking 9410 634Romans 9410 635intercepted 9410 636bewilderingly 9410 637astound 9410 638admonishing 9410 639sumac 9410 640flanking 9410 641combed 9410 642subjective 9410 643scatterbrain 9410 644Eulerian 9410 645Kane 9410 646overlay 9410 647perturb 9410 648goblins 9410 649annihilates 9410 650Wotan 9410 651snatching 9410 652concludes 9410 653laterally 9410 654yelped 9410 655grazing 9410 656Baird 9410 657celery 9410 658misunderstander 9410 659handgun 9410 660foldout 9410 661mystic 9410 662succumbed 9410 663Nabisco 9410 664fingerings 9410 665aging 9410 666afield 9410 667ammonium 9410 668boat 9410 669intelligibility 9410 670Augustine 9410 671teethe 9410 672dreaded 9410 673scholastics 9410 674audiology 9410 675wallet 9410 676parters 9410 677eschew 9410 678quitter 9410 679neat 9410 680Steinberg 9410 681jarring 9410 682tinily 9410 683balled 9410 684persist 9410 685attainments 9410 686fanatic 9410 687measures 9410 688rightfulness 9410 689capably 9410 690impulsive 9410 691starlet 9410 692terminators 9410 693untying 9410 694announces 9410 695featherweight 9410 696pessimist 9410 697daughter 9410 698decliner 9410 699lawgiver 9410 700stated 9410 701readable 9410 702attrition 9410 703cascade 9410 704motors 9410 705interrogate 9410 706pests 9410 707stairway 9410 708dopers 9410 709testicle 9410 710Parsifal 9410 711leavings 9410 712postulation 9410 713squeaking 9410 714contrasted 9410 715leftover 9410 716whiteners 9410 717erases 9410 718Punjab 9410 719Merritt 9410 720Quixotism 9410 721sweetish 9410 722dogging 9410 723scornfully 9410 724bellow 9410 725bills 9410 726cupboard 9410 727sureties 9410 728puddings 9410 729fetters 9410 730bivalves 9410 731incurring 9410 732Adolph 9410 733pithed 9410 734Miles 9410 735trimmings 9410 736tragedies 9410 737skulking 9410 738flint 9410 739flopping 9410 740relaxing 9410 741offload 9410 742suites 9410 743lists 9410 744animized 9410 745multilayer 9410 746standardizes 9410 747Judas 9410 748vacuuming 9410 749dentally 9410 750humanness 9410 751inch 9410 752Weissmuller 9410 753irresponsibly 9410 754luckily 9410 755culled 9410 756medical 9410 757bloodbath 9410 758subschema 9410 759animals 9410 760Micronesia 9410 761repetitions 9410 762Antares 9410 763ventilate 9410 764pityingly 9410 765interdependent 9410 766Graves 9410 767neonatal 9410 768chafe 9410 769honoring 9410 770realtor 9410 771elite 9410 772funereal 9410 773abrogating 9410 774sorters 9410 775Conley 9410 776lectured 9410 777Abraham 9410 778Hawaii 9410 779cage 9410 780hushes 9410 781Simla 9410 782reporters 9410 783Dutchman 9410 784descendants 9410 785groupings 9410 786dissociate 9410 787coexist 9410 788Beebe 9410 789Taoism 9410 790Connally 9410 791fetched 9410 792checkpoints 9410 793rusting 9410 794galling 9410 795obliterates 9410 796traitor 9410 797resumes 9410 798analyzable 9410 799terminator 9410 800gritty 9410 801firearm 9410 802minima 9410 803Selfridge 9410 804disable 9410 805witchcraft 9410 806betroth 9410 807Manhattanize 9410 808imprint 9410 809peeked 9410 810swelling 9410 811interrelationships 9410 812riser 9410 813Gandhian 9410 814peacock 9410 815bee 9410 816kanji 9410 817dental 9410 818scarf 9410 819chasm 9410 820insolence 9410 821syndicate 9410 822alike 9410 823imperial 9410 824convulsion 9410 825railway 9410 826validate 9410 827normalizes 9410 828comprehensive 9410 829chewing 9410 830denizen 9410 831schemer 9410 832chronicle 9410 833Kline 9410 834Anatole 9410 835partridges 9410 836brunch 9410 837recruited 9410 838dimensions 9410 839Chicana 9410 840announced 9410 841praised 9410 842employing 9410 843linear 9410 844quagmire 9410 845western 9410 846relishing 9410 847serving 9410 848scheduling 9410 849lore 9410 850eventful 9410 851arteriole 9410 852disentangle 9410 853cured 9410 854Fenton 9410 855avoidable 9410 856drains 9410 857detectably 9410 858husky 9410 859impelling 9410 860undoes 9410 861evened 9410 862squeezes 9410 863destroyer 9410 864rudeness 9410 865beaner 9410 866boorish 9410 867Everhart 9410 868encompass 9410 869mushrooms 9410 870Alison 9410 871externally 9410 872pellagra 9410 873cult 9410 874creek 9410 875Huffman 9410 876Majorca 9410 877governing 9410 878gadfly 9410 879reassigned 9410 880intentness 9410 881craziness 9410 882psychic 9410 883squabbled 9410 884burlesque 9410 885capped 9410 886extracted 9410 887DiMaggio 9410 888exclamation 9410 889subdirectory 9410 890Gothicism 9410 891feminine 9410 892metaphysically 9410 893sanding 9410 894Miltonism 9410 895freakish 9410 896index 9410 897straight 9410 898flurried 9410 899denotative 9410 900coming 9410 901commencements 9410 902gentleman 9410 903gifted 9410 904Shanghais 9410 905sportswriting 9410 906sloping 9410 907navies 9410 908leaflet 9410 909shooter 9410 910Joplin 9410 911babies 9410 912assails 9410 913admiring 9410 914swaying 9410 915Goldstine 9410 916fitting 9410 917Norwalk 9410 918analogy 9410 919deludes 9410 920cokes 9410 921Clayton 9410 922exhausts 9410 923causality 9410 924sating 9410 925icon 9410 926throttles 9410 927communicants 9410 928dehydrate 9410 929priceless 9410 930publicly 9410 931incidentals 9410 932commonplace 9410 933mumbles 9410 934furthermore 9410 935cautioned 9410 936parametrized 9410 937registration 9410 938sadly 9410 939positioning 9410 940babysitting 9410 941eternal 9410 942hoarder 9410 943congregates 9410 944rains 9410 945workers 9410 946sags 9410 947unplug 9410 948garage 9410 949boulder 9410 950specifics 9410 951Teresa 9410 952Winsett 9410 953convenient 9410 954buckboards 9410 955amenities 9410 956resplendent 9410 957sews 9410 958participated 9410 959Simon 9410 960certificates 9410 961Fitzpatrick 9410 962Evanston 9410 963misted 9410 964textures 9410 965save 9410 966count 9410 967rightful 9410 968chaperone 9410 969Lizzy 9410 970clenched 9410 971effortlessly 9410 972accessed 9410 973beaters 9410 974Hornblower 9410 975vests 9410 976indulgences 9410 977infallibly 9410 978unwilling 9410 979excrete 9410 980spools 9410 981crunches 9410 982overestimating 9410 983ineffective 9410 984humiliation 9410 985sophomore 9410 986star 9410 987rifles 9410 988dialysis 9410 989arriving 9410 990indulge 9410 991clockers 9410 992languages 9410 993Antarctica 9410 994percentage 9410 995ceiling 9410 996specification 9410 997regimented 9410 998ciphers 9410 999pictures 9410 1000serpents 9410 1001allot 9410 1002realized 9410 1003mayoral 9410 1004opaquely 9410 1005hostess 9410 1006fiftieth 9410 1007incorrectly 9410 1008decomposition 9410 1009stranglings 9410 1010mixture 9410 1011electroencephalography 9410 1012similarities 9410 1013charges 9410 1014freest 9410 1015Greenberg 9410 1016tinting 9410 1017expelled 9410 1018warm 9410 1019smoothed 9410 1020deductions 9410 1021Romano 9410 1022bitterroot 9410 1023corset 9410 1024securing 9410 1025environing 9410 1026cute 9410 1027Crays 9410 1028heiress 9410 1029inform 9410 1030avenge 9410 1031universals 9410 1032Kinsey 9410 1033ravines 9410 1034bestseller 9410 1035equilibrium 9410 1036extents 9410 1037relatively 9410 1038pressure 9410 1039critiques 9410 1040befouled 9410 1041rightfully 9410 1042mechanizing 9410 1043Latinizes 9410 1044timesharing 9410 1045Aden 9410 1046embassies 9410 1047males 9410 1048shapelessly 9410 1049mastering 9410 1050Newtonian 9410 1051finishers 9410 1052abates 9410 1053teem 9410 1054kiting 9410 1055stodgy 9410 1056feed 9410 1057guitars 9410 1058airships 9410 1059store 9410 1060denounces 9410 1061Pyle 9410 1062Saxony 9410 1063serializations 9410 1064Peruvian 9410 1065taxonomically 9410 1066kingdom 9410 1067stint 9410 1068Sault 9410 1069faithful 9410 1070Ganymede 9410 1071tidiness 9410 1072gainful 9410 1073contrary 9410 1074Tipperary 9410 1075tropics 9410 1076theorizers 9410 1077renew 9410 1078already 9410 1079terminal 9410 1080Hegelian 9410 1081hypothesizer 9410 1082warningly 9410 1083journalizing 9410 1084nested 9410 1085Lars 9410 1086saplings 9410 1087foothill 9410 1088labeled 9410 1089imperiously 9410 1090reporters 9410 1091furnishings 9410 1092precipitable 9410 1093discounts 9410 1094excises 9410 1095Stalin 9410 1096despot 9410 1097ripeness 9410 1098Arabia 9410 1099unruly 9410 1100mournfulness 9410 1101boom 9410 1102slaughter 9410 1103Sabine 9410 1104handy 9410 1105rural 9410 1106organizer 9410 1107shipyard 9410 1108civics 9410 1109inaccuracy 9410 1110rules 9410 1111juveniles 9410 1112comprised 9410 1113investigations 9410 1114stabilizes 9410 1115seminaries 9410 1116Hunter 9410 1117sporty 9410 1118test 9410 1119weasels 9410 1120CERN 9410 1121tempering 9410 1122afore 9410 1123Galatean 9410 1124techniques 9410 1125error 9410 1126veranda 9410 1127severely 9410 1128Cassites 9410 1129forthcoming 9410 1130guides 9410 1131vanish 9410 1132lied 9410 1133sawtooth 9410 1134fated 9410 1135gradually 9410 1136widens 9410 1137preclude 9410 1138evenhandedly 9410 1139percentage 9410 1140disobedience 9410 1141humility 9410 1142gleaning 9410 1143petted 9410 1144bloater 9410 1145minion 9410 1146marginal 9410 1147apiary 9410 1148measures 9410 1149precaution 9410 1150repelled 9410 1151primary 9410 1152coverings 9410 1153Artemia 9410 1154navigate 9410 1155spatial 9410 1156Gurkha 9410 1157meanwhile 9410 1158Melinda 9410 1159Butterfield 9410 1160Aldrich 9410 1161previewing 9410 1162glut 9410 1163unaffected 9410 1164inmate 9410 1165mineral 9410 1166impending 9410 1167meditation 9410 1168ideas 9410 1169miniaturizes 9410 1170lewdly 9410 1171title 9410 1172youthfulness 9410 1173creak 9410 1174Chippewa 9410 1175clamored 9410 1176freezes 9410 1177forgivably 9410 1178reduce 9410 1179McGovern 9410 1180Nazis 9410 1181epistle 9410 1182socializes 9410 1183conceptions 9410 1184Kevin 9410 1185uncovering 9410 1186chews 9410 1187appendixes 9410 1188appendixes 9410 1189appendixes 9410 1190appendixes 9410 1191appendixes 9410 1192appendixes 9410 1193raining 9410 1194infest 9410 1195compartment 9410 1196minting 9410 1197ducks 9410 1198roped 9410 1199waltz 9410 1200Lillian 9410 1201repressions 9410 1202chillingly 9410 1203noncritical 9410 1204lithograph 9410 1205spongers 9410 1206parenthood 9410 1207posed 9410 1208instruments 9410 1209filial 9410 1210fixedly 9410 1211relives 9410 1212Pandora 9410 1213watering 9410 1214ungrateful 9410 1215secures 9410 1216poison 9410 1217dusted 9410 1218encompasses 9410 1219presentation 9410 1220Kantian 9410 1221select fld3,period,price,price2 from t2,t3 where t2.fld1=t3.t2nr and period >= 1001 and period <= 1002 and t2.companynr = 37 order by fld3,period, price; 1222fld3 period price price2 1223admonishing 1002 28357832 8723648 1224analyzable 1002 28357832 8723648 1225annihilates 1001 5987435 234724 1226Antares 1002 28357832 8723648 1227astound 1001 5987435 234724 1228audiology 1001 5987435 234724 1229Augustine 1002 28357832 8723648 1230Baird 1002 28357832 8723648 1231bewilderingly 1001 5987435 234724 1232breaking 1001 5987435 234724 1233Conley 1001 5987435 234724 1234dentally 1002 28357832 8723648 1235dissociate 1002 28357832 8723648 1236elite 1001 5987435 234724 1237eschew 1001 5987435 234724 1238Eulerian 1001 5987435 234724 1239flanking 1001 5987435 234724 1240foldout 1002 28357832 8723648 1241funereal 1002 28357832 8723648 1242galling 1002 28357832 8723648 1243Graves 1001 5987435 234724 1244grazing 1001 5987435 234724 1245groupings 1001 5987435 234724 1246handgun 1001 5987435 234724 1247humility 1002 28357832 8723648 1248impulsive 1002 28357832 8723648 1249inch 1001 5987435 234724 1250intelligibility 1001 5987435 234724 1251jarring 1001 5987435 234724 1252lawgiver 1001 5987435 234724 1253lectured 1002 28357832 8723648 1254Merritt 1002 28357832 8723648 1255neonatal 1001 5987435 234724 1256offload 1002 28357832 8723648 1257parters 1002 28357832 8723648 1258pityingly 1002 28357832 8723648 1259puddings 1002 28357832 8723648 1260Punjab 1001 5987435 234724 1261quitter 1002 28357832 8723648 1262realtor 1001 5987435 234724 1263relaxing 1001 5987435 234724 1264repetitions 1001 5987435 234724 1265resumes 1001 5987435 234724 1266Romans 1002 28357832 8723648 1267rusting 1001 5987435 234724 1268scholastics 1001 5987435 234724 1269skulking 1002 28357832 8723648 1270stated 1002 28357832 8723648 1271suites 1002 28357832 8723648 1272sureties 1001 5987435 234724 1273testicle 1002 28357832 8723648 1274tinily 1002 28357832 8723648 1275tragedies 1001 5987435 234724 1276trimmings 1001 5987435 234724 1277vacuuming 1001 5987435 234724 1278ventilate 1001 5987435 234724 1279wallet 1001 5987435 234724 1280Weissmuller 1002 28357832 8723648 1281Wotan 1002 28357832 8723648 1282select t2.fld1,fld3,period,price,price2 from t2,t3 where t2.fld1>= 18201 and t2.fld1 <= 18811 and t2.fld1=t3.t2nr and period = 1001 and t2.companynr = 37; 1283fld1 fld3 period price price2 1284018201 relaxing 1001 5987435 234724 1285018601 vacuuming 1001 5987435 234724 1286018801 inch 1001 5987435 234724 1287018811 repetitions 1001 5987435 234724 1288create table t4 ( 1289companynr tinyint(2) unsigned zerofill NOT NULL default '00', 1290companyname char(30) NOT NULL default '', 1291PRIMARY KEY (companynr), 1292UNIQUE KEY companyname(companyname) 1293) ENGINE=MyISAM MAX_ROWS=50 PACK_KEYS=1 COMMENT='companynames'; 1294select STRAIGHT_JOIN t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr; 1295companynr companyname 129600 Unknown 129729 company 1 129834 company 2 129936 company 3 130037 company 4 130140 company 5 130241 company 6 130350 company 11 130453 company 7 130558 company 8 130665 company 9 130768 company 10 1308select SQL_SMALL_RESULT t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr; 1309companynr companyname 131000 Unknown 131129 company 1 131234 company 2 131336 company 3 131437 company 4 131540 company 5 131641 company 6 131750 company 11 131853 company 7 131958 company 8 132065 company 9 132168 company 10 1322select * from t1,t1 t12; 1323Period Varor_period Period Varor_period 13249410 9412 9410 9412 1325select t2.fld1,t22.fld1 from t2,t2 t22 where t2.fld1 >= 250501 and t2.fld1 <= 250505 and t22.fld1 >= 250501 and t22.fld1 <= 250505; 1326fld1 fld1 1327250501 250501 1328250502 250501 1329250503 250501 1330250504 250501 1331250505 250501 1332250501 250502 1333250502 250502 1334250503 250502 1335250504 250502 1336250505 250502 1337250501 250503 1338250502 250503 1339250503 250503 1340250504 250503 1341250505 250503 1342250501 250504 1343250502 250504 1344250503 250504 1345250504 250504 1346250505 250504 1347250501 250505 1348250502 250505 1349250503 250505 1350250504 250505 1351250505 250505 1352insert into t2 (fld1, companynr) values (999999,99); 1353select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null; 1354companynr companyname 135599 NULL 1356select count(*) from t2 left join t4 using (companynr) where t4.companynr is not null; 1357count(*) 13581199 1359explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null; 1360id select_type table type possible_keys key key_len ref rows Extra 13611 SIMPLE t2 ALL NULL NULL NULL NULL 1200 NULL 13621 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 Using where; Not exists 1363explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null; 1364id select_type table type possible_keys key key_len ref rows Extra 13651 SIMPLE t4 ALL NULL NULL NULL NULL 12 NULL 13661 SIMPLE t2 ALL NULL NULL NULL NULL 1200 Using where; Not exists; Using join buffer (Block Nested Loop) 1367select companynr,companyname from t2 left join t4 using (companynr) where companynr is null; 1368companynr companyname 1369select count(*) from t2 left join t4 using (companynr) where companynr is not null; 1370count(*) 13711200 1372explain select companynr,companyname from t2 left join t4 using (companynr) where companynr is null; 1373id select_type table type possible_keys key key_len ref rows Extra 13741 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 1375explain select companynr,companyname from t4 left join t2 using (companynr) where companynr is null; 1376id select_type table type possible_keys key key_len ref rows Extra 13771 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 1378delete from t2 where fld1=999999; 1379explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0; 1380id select_type table type possible_keys key key_len ref rows Extra 13811 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 13821 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 NULL 1383explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0; 1384id select_type table type possible_keys key key_len ref rows Extra 13851 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 13861 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 NULL 1387explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0; 1388id select_type table type possible_keys key key_len ref rows Extra 13891 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 13901 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 NULL 1391explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0; 1392id select_type table type possible_keys key key_len ref rows Extra 13931 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where 13941 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (Block Nested Loop) 1395explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0; 1396id select_type table type possible_keys key key_len ref rows Extra 13971 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where 13981 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (Block Nested Loop) 1399explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0; 1400id select_type table type possible_keys key key_len ref rows Extra 14011 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where 14021 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (Block Nested Loop) 1403explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null; 1404id select_type table type possible_keys key key_len ref rows Extra 14051 SIMPLE t4 ALL NULL NULL NULL NULL 12 NULL 14061 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (Block Nested Loop) 1407explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0 or t4.companynr > 0; 1408id select_type table type possible_keys key key_len ref rows Extra 14091 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 NULL 14101 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (Block Nested Loop) 1411explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0; 1412id select_type table type possible_keys key key_len ref rows Extra 14131 SIMPLE t4 ALL NULL NULL NULL NULL 12 NULL 14141 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (Block Nested Loop) 1415explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null; 1416id select_type table type possible_keys key key_len ref rows Extra 14171 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where 14181 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (Block Nested Loop) 1419explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0; 1420id select_type table type possible_keys key key_len ref rows Extra 14211 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where 14221 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (Block Nested Loop) 1423explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0; 1424id select_type table type possible_keys key key_len ref rows Extra 14251 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where 14261 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (Block Nested Loop) 1427select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; 1428companynr companynr 142937 36 143041 40 1431explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; 1432id select_type table type possible_keys key key_len ref rows Extra 14331 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using index; Using temporary 14341 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (Block Nested Loop) 1435select t2.fld1,t2.companynr,fld3,period from t3,t2 where t2.fld1 = 38208 and t2.fld1=t3.t2nr and period = 1008 or t2.fld1 = 38008 and t2.fld1 =t3.t2nr and period = 1008; 1436fld1 companynr fld3 period 1437038008 37 reporters 1008 1438038208 37 Selfridge 1008 1439select t2.fld1,t2.companynr,fld3,period from t3,t2 where (t2.fld1 = 38208 or t2.fld1 = 38008) and t2.fld1=t3.t2nr and period>=1008 and period<=1009; 1440fld1 companynr fld3 period 1441038008 37 reporters 1008 1442038208 37 Selfridge 1008 1443select t2.fld1,t2.companynr,fld3,period from t3,t2 where (t3.t2nr = 38208 or t3.t2nr = 38008) and t2.fld1=t3.t2nr and period>=1008 and period<=1009; 1444fld1 companynr fld3 period 1445038008 37 reporters 1008 1446038208 37 Selfridge 1008 1447select period from t1 where (((period > 0) or period < 10000 or (period = 1900)) and (period=1900 and period <= 1901) or (period=1903 and (period=1903)) and period>=1902) or ((period=1904 or period=1905) or (period=1906 or period>1907)) or (period=1908 and period = 1909); 1448period 14499410 1450select period from t1 where ((period > 0 and period < 1) or (((period > 0 and period < 100) and (period > 10)) or (period > 10)) or (period > 0 and (period > 5 or period > 6))); 1451period 14529410 1453select a.fld1 from t2 as a,t2 b where ((a.fld1 = 250501 and a.fld1=b.fld1) or a.fld1=250502 or a.fld1=250503 or (a.fld1=250505 and a.fld1<=b.fld1 and b.fld1>=a.fld1)) and a.fld1=b.fld1; 1454fld1 1455250501 1456250502 1457250503 1458250505 1459select fld1 from t2 where fld1 in (250502,98005,98006,250503,250605,250606) and fld1 >=250502 and fld1 not in (250605,250606); 1460fld1 1461250502 1462250503 1463select fld1 from t2 where fld1 between 250502 and 250504; 1464fld1 1465250502 1466250503 1467250504 1468select fld3 from t2 where (((fld3 like "_%L%" ) or (fld3 like "%ok%")) and ( fld3 like "L%" or fld3 like "G%")) and fld3 like "L%" ; 1469fld3 1470label 1471labeled 1472labeled 1473landslide 1474laterally 1475leaflet 1476lewdly 1477Lillian 1478luckily 1479select count(*) from t1; 1480count(*) 14811 1482select companynr,count(*),sum(fld1) from t2 group by companynr; 1483companynr count(*) sum(fld1) 148400 82 10355753 148529 95 14473298 148634 70 17788966 148736 215 22786296 148837 588 83602098 148940 37 6618386 149041 52 12816335 149150 11 1595438 149253 4 793210 149358 23 2254293 149465 10 2284055 149568 12 3097288 1496select companynr,count(*) from t2 group by companynr order by companynr desc limit 5; 1497companynr count(*) 149868 12 149965 10 150058 23 150153 4 150250 11 1503select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>""; 1504count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1) 150570 absentee vest 17788966 254128.0857 3272.5939722090234 10709871.306938833 1506explain extended select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>""; 1507id select_type table type possible_keys key key_len ref rows filtered Extra 15081 SIMPLE t2 ALL NULL NULL NULL NULL 1199 100.00 Using where 1509Warnings: 1510Note 1003 /* select#1 */ select count(0) AS `count(*)`,min(`test`.`t2`.`fld4`) AS `min(fld4)`,max(`test`.`t2`.`fld4`) AS `max(fld4)`,sum(`test`.`t2`.`fld1`) AS `sum(fld1)`,avg(`test`.`t2`.`fld1`) AS `avg(fld1)`,std(`test`.`t2`.`fld1`) AS `std(fld1)`,variance(`test`.`t2`.`fld1`) AS `variance(fld1)` from `test`.`t2` where ((`test`.`t2`.`companynr` = 34) and (`test`.`t2`.`fld4` <> '')) 1511select companynr,count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 group by companynr limit 3; 1512companynr count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1) 151300 82 Anthony windmills 10355753 126289.6707 115550.97568479746 13352027981.708656 151429 95 abut wetness 14473298 152350.5053 8368.547956641249 70032594.90260443 151534 70 absentee vest 17788966 254128.0857 3272.5939722090234 10709871.306938833 1516select companynr,t2nr,count(price),sum(price),min(price),max(price),avg(price) from t3 where companynr = 37 group by companynr,t2nr limit 10; 1517companynr t2nr count(price) sum(price) min(price) max(price) avg(price) 151837 1 1 5987435 5987435 5987435 5987435.0000 151937 2 1 28357832 28357832 28357832 28357832.0000 152037 3 1 39654943 39654943 39654943 39654943.0000 152137 11 1 5987435 5987435 5987435 5987435.0000 152237 12 1 28357832 28357832 28357832 28357832.0000 152337 13 1 39654943 39654943 39654943 39654943.0000 152437 21 1 5987435 5987435 5987435 5987435.0000 152537 22 1 28357832 28357832 28357832 28357832.0000 152637 23 1 39654943 39654943 39654943 39654943.0000 152737 31 1 5987435 5987435 5987435 5987435.0000 1528select /*! SQL_SMALL_RESULT */ companynr,t2nr,count(price),sum(price),min(price),max(price),avg(price) from t3 where companynr = 37 group by companynr,t2nr limit 10; 1529companynr t2nr count(price) sum(price) min(price) max(price) avg(price) 153037 1 1 5987435 5987435 5987435 5987435.0000 153137 2 1 28357832 28357832 28357832 28357832.0000 153237 3 1 39654943 39654943 39654943 39654943.0000 153337 11 1 5987435 5987435 5987435 5987435.0000 153437 12 1 28357832 28357832 28357832 28357832.0000 153537 13 1 39654943 39654943 39654943 39654943.0000 153637 21 1 5987435 5987435 5987435 5987435.0000 153737 22 1 28357832 28357832 28357832 28357832.0000 153837 23 1 39654943 39654943 39654943 39654943.0000 153937 31 1 5987435 5987435 5987435 5987435.0000 1540select companynr,count(price),sum(price),min(price),max(price),avg(price) from t3 group by companynr ; 1541companynr count(price) sum(price) min(price) max(price) avg(price) 154237 12543 309394878010 5987435 39654943 24666736.6667 154378 8362 414611089292 726498 98439034 49582766.0000 1544101 4181 3489454238 834598 834598 834598.0000 1545154 4181 4112197254950 983543950 983543950 983543950.0000 1546311 4181 979599938 234298 234298 234298.0000 1547447 4181 9929180954 2374834 2374834 2374834.0000 1548512 4181 3288532102 786542 786542 786542.0000 1549select distinct mod(companynr,10) from t4 group by companynr; 1550mod(companynr,10) 15510 15529 15534 15546 15557 15561 15573 15588 15595 1560select distinct 1 from t4 group by companynr; 15611 15621 1563select count(distinct fld1) from t2; 1564count(distinct fld1) 15651199 1566select companynr,count(distinct fld1) from t2 group by companynr; 1567companynr count(distinct fld1) 156800 82 156929 95 157034 70 157136 215 157237 588 157340 37 157441 52 157550 11 157653 4 157758 23 157865 10 157968 12 1580select companynr,count(*) from t2 group by companynr; 1581companynr count(*) 158200 82 158329 95 158434 70 158536 215 158637 588 158740 37 158841 52 158950 11 159053 4 159158 23 159265 10 159368 12 1594select companynr,count(distinct concat(fld1,repeat(65,1000))) from t2 group by companynr; 1595companynr count(distinct concat(fld1,repeat(65,1000))) 159600 82 159729 95 159834 70 159936 215 160037 588 160140 37 160241 52 160350 11 160453 4 160558 23 160665 10 160768 12 1608select companynr,count(distinct concat(fld1,repeat(65,200))) from t2 group by companynr; 1609companynr count(distinct concat(fld1,repeat(65,200))) 161000 82 161129 95 161234 70 161336 215 161437 588 161540 37 161641 52 161750 11 161853 4 161958 23 162065 10 162168 12 1622select companynr,count(distinct floor(fld1/100)) from t2 group by companynr; 1623companynr count(distinct floor(fld1/100)) 162400 47 162529 35 162634 14 162736 69 162837 108 162940 16 163041 11 163150 9 163253 1 163358 1 163465 1 163568 1 1636select companynr,count(distinct concat(repeat(65,1000),floor(fld1/100))) from t2 group by companynr; 1637companynr count(distinct concat(repeat(65,1000),floor(fld1/100))) 163800 47 163929 35 164034 14 164136 69 164237 108 164340 16 164441 11 164550 9 164653 1 164758 1 164865 1 164968 1 1650select sum(fld1),fld3 from t2 where fld3="Romans" group by fld1 limit 10; 1651sum(fld1) fld3 165211402 Romans 1653select name,count(*) from t3 where name='cloakroom' group by name; 1654name count(*) 1655cloakroom 4181 1656select name,count(*) from t3 where name='cloakroom' and price>10 group by name; 1657name count(*) 1658cloakroom 4181 1659select count(*) from t3 where name='cloakroom' and price2=823742; 1660count(*) 16614181 1662select name,count(*) from t3 where name='cloakroom' and price2=823742 group by name; 1663name count(*) 1664cloakroom 4181 1665select name,count(*) from t3 where name >= "extramarital" and price <= 39654943 group by name; 1666name count(*) 1667extramarital 4181 1668gazer 4181 1669gems 4181 1670Iranizes 4181 1671spates 4181 1672tucked 4181 1673violinist 4181 1674select t2.fld3,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name; 1675fld3 count(*) 1676spates 4181 1677select companynr|0,companyname from t4 group by 1; 1678companynr|0 companyname 16790 Unknown 168029 company 1 168134 company 2 168236 company 3 168337 company 4 168440 company 5 168541 company 6 168650 company 11 168753 company 7 168858 company 8 168965 company 9 169068 company 10 1691select t2.companynr,companyname,count(*) from t2,t4 where t2.companynr=t4.companynr group by t2.companynr order by companyname; 1692companynr companyname count(*) 169329 company 1 95 169468 company 10 12 169550 company 11 11 169634 company 2 70 169736 company 3 215 169837 company 4 588 169940 company 5 37 170041 company 6 52 170153 company 7 4 170258 company 8 23 170365 company 9 10 170400 Unknown 82 1705select t2.fld1,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name; 1706fld1 count(*) 1707158402 4181 1708select sum(Period)/count(*) from t1; 1709sum(Period)/count(*) 17109410.0000 1711select companynr,count(price) as "count",sum(price) as "sum" ,abs(sum(price)/count(price)-avg(price)) as "diff",(0+count(price))*companynr as func from t3 group by companynr; 1712companynr count sum diff func 171337 12543 309394878010 0.0000 464091 171478 8362 414611089292 0.0000 652236 1715101 4181 3489454238 0.0000 422281 1716154 4181 4112197254950 0.0000 643874 1717311 4181 979599938 0.0000 1300291 1718447 4181 9929180954 0.0000 1868907 1719512 4181 3288532102 0.0000 2140672 1720select companynr,sum(price)/count(price) as avg from t3 group by companynr having avg > 70000000 order by avg; 1721companynr avg 1722154 983543950.0000 1723select companynr,count(*) from t2 group by companynr order by 2 desc; 1724companynr count(*) 172537 588 172636 215 172729 95 172800 82 172934 70 173041 52 173140 37 173258 23 173368 12 173450 11 173565 10 173653 4 1737select companynr,count(*) from t2 where companynr > 40 group by companynr order by 2 desc; 1738companynr count(*) 173941 52 174058 23 174168 12 174250 11 174365 10 174453 4 1745select t2.fld4,t2.fld1,count(price),sum(price),min(price),max(price),avg(price) from t3,t2 where t3.companynr = 37 and t2.fld1 = t3.t2nr group by fld1,t2.fld4; 1746fld4 fld1 count(price) sum(price) min(price) max(price) avg(price) 1747teethe 000001 1 5987435 5987435 5987435 5987435.0000 1748dreaded 011401 1 5987435 5987435 5987435 5987435.0000 1749scholastics 011402 1 28357832 28357832 28357832 28357832.0000 1750audiology 011403 1 39654943 39654943 39654943 39654943.0000 1751wallet 011501 1 5987435 5987435 5987435 5987435.0000 1752parters 011701 1 5987435 5987435 5987435 5987435.0000 1753eschew 011702 1 28357832 28357832 28357832 28357832.0000 1754quitter 011703 1 39654943 39654943 39654943 39654943.0000 1755neat 012001 1 5987435 5987435 5987435 5987435.0000 1756Steinberg 012003 1 39654943 39654943 39654943 39654943.0000 1757balled 012301 1 5987435 5987435 5987435 5987435.0000 1758persist 012302 1 28357832 28357832 28357832 28357832.0000 1759attainments 012303 1 39654943 39654943 39654943 39654943.0000 1760capably 012501 1 5987435 5987435 5987435 5987435.0000 1761impulsive 012602 1 28357832 28357832 28357832 28357832.0000 1762starlet 012603 1 39654943 39654943 39654943 39654943.0000 1763featherweight 012701 1 5987435 5987435 5987435 5987435.0000 1764pessimist 012702 1 28357832 28357832 28357832 28357832.0000 1765daughter 012703 1 39654943 39654943 39654943 39654943.0000 1766lawgiver 013601 1 5987435 5987435 5987435 5987435.0000 1767stated 013602 1 28357832 28357832 28357832 28357832.0000 1768readable 013603 1 39654943 39654943 39654943 39654943.0000 1769testicle 013801 1 5987435 5987435 5987435 5987435.0000 1770Parsifal 013802 1 28357832 28357832 28357832 28357832.0000 1771leavings 013803 1 39654943 39654943 39654943 39654943.0000 1772squeaking 013901 1 5987435 5987435 5987435 5987435.0000 1773contrasted 016001 1 5987435 5987435 5987435 5987435.0000 1774leftover 016201 1 5987435 5987435 5987435 5987435.0000 1775whiteners 016202 1 28357832 28357832 28357832 28357832.0000 1776erases 016301 1 5987435 5987435 5987435 5987435.0000 1777Punjab 016302 1 28357832 28357832 28357832 28357832.0000 1778Merritt 016303 1 39654943 39654943 39654943 39654943.0000 1779sweetish 018001 1 5987435 5987435 5987435 5987435.0000 1780dogging 018002 1 28357832 28357832 28357832 28357832.0000 1781scornfully 018003 1 39654943 39654943 39654943 39654943.0000 1782fetters 018012 1 28357832 28357832 28357832 28357832.0000 1783bivalves 018013 1 39654943 39654943 39654943 39654943.0000 1784skulking 018021 1 5987435 5987435 5987435 5987435.0000 1785flint 018022 1 28357832 28357832 28357832 28357832.0000 1786flopping 018023 1 39654943 39654943 39654943 39654943.0000 1787Judas 018032 1 28357832 28357832 28357832 28357832.0000 1788vacuuming 018033 1 39654943 39654943 39654943 39654943.0000 1789medical 018041 1 5987435 5987435 5987435 5987435.0000 1790bloodbath 018042 1 28357832 28357832 28357832 28357832.0000 1791subschema 018043 1 39654943 39654943 39654943 39654943.0000 1792interdependent 018051 1 5987435 5987435 5987435 5987435.0000 1793Graves 018052 1 28357832 28357832 28357832 28357832.0000 1794neonatal 018053 1 39654943 39654943 39654943 39654943.0000 1795sorters 018061 1 5987435 5987435 5987435 5987435.0000 1796epistle 018062 1 28357832 28357832 28357832 28357832.0000 1797Conley 018101 1 5987435 5987435 5987435 5987435.0000 1798lectured 018102 1 28357832 28357832 28357832 28357832.0000 1799Abraham 018103 1 39654943 39654943 39654943 39654943.0000 1800cage 018201 1 5987435 5987435 5987435 5987435.0000 1801hushes 018202 1 28357832 28357832 28357832 28357832.0000 1802Simla 018402 1 28357832 28357832 28357832 28357832.0000 1803reporters 018403 1 39654943 39654943 39654943 39654943.0000 1804coexist 018601 1 5987435 5987435 5987435 5987435.0000 1805Beebe 018602 1 28357832 28357832 28357832 28357832.0000 1806Taoism 018603 1 39654943 39654943 39654943 39654943.0000 1807Connally 018801 1 5987435 5987435 5987435 5987435.0000 1808fetched 018802 1 28357832 28357832 28357832 28357832.0000 1809checkpoints 018803 1 39654943 39654943 39654943 39654943.0000 1810gritty 018811 1 5987435 5987435 5987435 5987435.0000 1811firearm 018812 1 28357832 28357832 28357832 28357832.0000 1812minima 019101 1 5987435 5987435 5987435 5987435.0000 1813Selfridge 019102 1 28357832 28357832 28357832 28357832.0000 1814disable 019103 1 39654943 39654943 39654943 39654943.0000 1815witchcraft 019201 1 5987435 5987435 5987435 5987435.0000 1816betroth 030501 1 5987435 5987435 5987435 5987435.0000 1817Manhattanize 030502 1 28357832 28357832 28357832 28357832.0000 1818imprint 030503 1 39654943 39654943 39654943 39654943.0000 1819swelling 031901 1 5987435 5987435 5987435 5987435.0000 1820interrelationships 036001 1 5987435 5987435 5987435 5987435.0000 1821riser 036002 1 28357832 28357832 28357832 28357832.0000 1822bee 038001 1 5987435 5987435 5987435 5987435.0000 1823kanji 038002 1 28357832 28357832 28357832 28357832.0000 1824dental 038003 1 39654943 39654943 39654943 39654943.0000 1825railway 038011 1 5987435 5987435 5987435 5987435.0000 1826validate 038012 1 28357832 28357832 28357832 28357832.0000 1827normalizes 038013 1 39654943 39654943 39654943 39654943.0000 1828Kline 038101 1 5987435 5987435 5987435 5987435.0000 1829Anatole 038102 1 28357832 28357832 28357832 28357832.0000 1830partridges 038103 1 39654943 39654943 39654943 39654943.0000 1831recruited 038201 1 5987435 5987435 5987435 5987435.0000 1832dimensions 038202 1 28357832 28357832 28357832 28357832.0000 1833Chicana 038203 1 39654943 39654943 39654943 39654943.0000 1834select t3.companynr,fld3,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 512 group by companynr,fld3; 1835companynr fld3 sum(price) 1836512 boat 786542 1837512 capably 786542 1838512 cupboard 786542 1839512 decliner 786542 1840512 descendants 786542 1841512 dopers 786542 1842512 erases 786542 1843512 Micronesia 786542 1844512 Miles 786542 1845512 skies 786542 1846select t2.companynr,count(*),min(fld3),max(fld3),sum(price),avg(price) from t2,t3 where t3.companynr >= 30 and t3.companynr <= 58 and t3.t2nr = t2.fld1 and 1+1=2 group by t2.companynr; 1847companynr count(*) min(fld3) max(fld3) sum(price) avg(price) 184800 1 Omaha Omaha 5987435 5987435.0000 184936 1 dubbed dubbed 28357832 28357832.0000 185037 83 Abraham Wotan 1908978016 22999735.1325 185150 2 scribbled tapestry 68012775 34006387.5000 1852select t3.companynr+0,t3.t2nr,fld3,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 37 group by 1,t3.t2nr,fld3,fld3,fld3,fld3,fld3 order by fld1; 1853t3.companynr+0 t2nr fld3 sum(price) 185437 1 Omaha 5987435 185537 11401 breaking 5987435 185637 11402 Romans 28357832 185737 11403 intercepted 39654943 185837 11501 bewilderingly 5987435 185937 11701 astound 5987435 186037 11702 admonishing 28357832 186137 11703 sumac 39654943 186237 12001 flanking 5987435 186337 12003 combed 39654943 186437 12301 Eulerian 5987435 186537 12302 dubbed 28357832 186637 12303 Kane 39654943 186737 12501 annihilates 5987435 186837 12602 Wotan 28357832 186937 12603 snatching 39654943 187037 12701 grazing 5987435 187137 12702 Baird 28357832 187237 12703 celery 39654943 187337 13601 handgun 5987435 187437 13602 foldout 28357832 187537 13603 mystic 39654943 187637 13801 intelligibility 5987435 187737 13802 Augustine 28357832 187837 13803 teethe 39654943 187937 13901 scholastics 5987435 188037 16001 audiology 5987435 188137 16201 wallet 5987435 188237 16202 parters 28357832 188337 16301 eschew 5987435 188437 16302 quitter 28357832 188537 16303 neat 39654943 188637 18001 jarring 5987435 188737 18002 tinily 28357832 188837 18003 balled 39654943 188937 18012 impulsive 28357832 189037 18013 starlet 39654943 189137 18021 lawgiver 5987435 189237 18022 stated 28357832 189337 18023 readable 39654943 189437 18032 testicle 28357832 189537 18033 Parsifal 39654943 189637 18041 Punjab 5987435 189737 18042 Merritt 28357832 189837 18043 Quixotism 39654943 189937 18051 sureties 5987435 190037 18052 puddings 28357832 190137 18053 tapestry 39654943 190237 18061 trimmings 5987435 190337 18062 humility 28357832 190437 18101 tragedies 5987435 190537 18102 skulking 28357832 190637 18103 flint 39654943 190737 18201 relaxing 5987435 190837 18202 offload 28357832 190937 18402 suites 28357832 191037 18403 lists 39654943 191137 18601 vacuuming 5987435 191237 18602 dentally 28357832 191337 18603 humanness 39654943 191437 18801 inch 5987435 191537 18802 Weissmuller 28357832 191637 18803 irresponsibly 39654943 191737 18811 repetitions 5987435 191837 18812 Antares 28357832 191937 19101 ventilate 5987435 192037 19102 pityingly 28357832 192137 19103 interdependent 39654943 192237 19201 Graves 5987435 192337 30501 neonatal 5987435 192437 30502 scribbled 28357832 192537 30503 chafe 39654943 192637 31901 realtor 5987435 192737 36001 elite 5987435 192837 36002 funereal 28357832 192937 38001 Conley 5987435 193037 38002 lectured 28357832 193137 38003 Abraham 39654943 193237 38011 groupings 5987435 193337 38012 dissociate 28357832 193437 38013 coexist 39654943 193537 38101 rusting 5987435 193637 38102 galling 28357832 193737 38103 obliterates 39654943 193837 38201 resumes 5987435 193937 38202 analyzable 28357832 194037 38203 terminator 39654943 1941select sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 512 and t3.t2nr = 38008 and t2.fld1 = 38008 or t2.fld1= t3.t2nr and t3.t2nr = 38008 and t2.fld1 = 38008; 1942sum(price) 1943234298 1944select t2.fld1,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 512 and t3.t2nr = 38008 and t2.fld1 = 38008 or t2.fld1 = t3.t2nr and t3.t2nr = 38008 and t2.fld1 = 38008 or t3.t2nr = t2.fld1 and t2.fld1 = 38008 group by t2.fld1; 1945fld1 sum(price) 1946038008 234298 1947explain select fld3 from t2 where 1>2 or 2>3; 1948id select_type table type possible_keys key key_len ref rows Extra 19491 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 1950explain select fld3 from t2 where fld1=fld1; 1951id select_type table type possible_keys key key_len ref rows Extra 19521 SIMPLE t2 ALL NULL NULL NULL NULL 1199 NULL 1953select companynr,fld1 from t2 HAVING fld1=250501 or fld1=250502; 1954companynr fld1 195534 250501 195634 250502 1957select companynr,fld1 from t2 WHERE fld1>=250501 HAVING fld1<=250502; 1958companynr fld1 195934 250501 196034 250502 1961select companynr,count(*) as count,sum(fld1) as sum from t2 group by companynr having count > 40 and sum/count >= 120000; 1962companynr count sum 196300 82 10355753 196429 95 14473298 196534 70 17788966 196637 588 83602098 196741 52 12816335 1968select companynr from t2 group by companynr having count(*) > 40 and sum(fld1)/count(*) >= 120000 ; 1969companynr 197000 197129 197234 197337 197441 1975select t2.companynr,companyname,count(*) from t2,t4 where t2.companynr=t4.companynr group by companyname having t2.companynr >= 40; 1976companynr companyname count(*) 197768 company 10 12 197850 company 11 11 197940 company 5 37 198041 company 6 52 198153 company 7 4 198258 company 8 23 198365 company 9 10 1984select count(*) from t2; 1985count(*) 19861199 1987select count(*) from t2 where fld1 < 098024; 1988count(*) 1989387 1990select min(fld1) from t2 where fld1>= 098024; 1991min(fld1) 199298024 1993select max(fld1) from t2 where fld1>= 098024; 1994max(fld1) 19951232609 1996select count(*) from t3 where price2=76234234; 1997count(*) 19984181 1999select count(*) from t3 where companynr=512 and price2=76234234; 2000count(*) 20014181 2002explain select min(fld1),max(fld1),count(*) from t2; 2003id select_type table type possible_keys key key_len ref rows Extra 20041 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2005select min(fld1),max(fld1),count(*) from t2; 2006min(fld1) max(fld1) count(*) 20070 1232609 1199 2008select min(t2nr),max(t2nr) from t3 where t2nr=2115 and price2=823742; 2009min(t2nr) max(t2nr) 20102115 2115 2011select count(*),min(t2nr),max(t2nr) from t3 where name='spates' and companynr=78; 2012count(*) min(t2nr) max(t2nr) 20134181 4 41804 2014select t2nr,count(*) from t3 where name='gems' group by t2nr limit 20; 2015t2nr count(*) 20169 1 201719 1 201829 1 201939 1 202049 1 202159 1 202269 1 202379 1 202489 1 202599 1 2026109 1 2027119 1 2028129 1 2029139 1 2030149 1 2031159 1 2032169 1 2033179 1 2034189 1 2035199 1 2036select max(t2nr) from t3 where price=983543950; 2037max(t2nr) 203841807 2039select t1.period from t3 = t1 limit 1; 2040period 20411001 2042select t1.period from t1 as t1 limit 1; 2043period 20449410 2045select t1.period as "Nuvarande period" from t1 as t1 limit 1; 2046Nuvarande period 20479410 2048select period as ok_period from t1 limit 1; 2049ok_period 20509410 2051select period as ok_period from t1 group by ok_period limit 1; 2052ok_period 20539410 2054select 1+1 as summa from t1 group by summa limit 1; 2055summa 20562 2057select period as "Nuvarande period" from t1 group by "Nuvarande period" limit 1; 2058Nuvarande period 20599410 2060show tables; 2061Tables_in_test 2062t1 2063t2 2064t3 2065t4 2066show tables from test like "s%"; 2067Tables_in_test (s%) 2068show tables from test like "t?"; 2069Tables_in_test (t?) 2070show full columns from t2; 2071Field Type Collation Null Key Default Extra Privileges Comment 2072auto int(11) NULL NO PRI NULL auto_increment # 2073fld1 int(6) unsigned zerofill NULL NO UNI 000000 # 2074companynr tinyint(2) unsigned zerofill NULL NO 00 # 2075fld3 char(30) latin1_swedish_ci NO MUL # 2076fld4 char(35) latin1_swedish_ci NO # 2077fld5 char(35) latin1_swedish_ci NO # 2078fld6 char(4) latin1_swedish_ci NO # 2079show full columns from t2 from test like 'f%'; 2080Field Type Collation Null Key Default Extra Privileges Comment 2081fld1 int(6) unsigned zerofill NULL NO UNI 000000 # 2082fld3 char(30) latin1_swedish_ci NO MUL # 2083fld4 char(35) latin1_swedish_ci NO # 2084fld5 char(35) latin1_swedish_ci NO # 2085fld6 char(4) latin1_swedish_ci NO # 2086show full columns from t2 from test like 's%'; 2087Field Type Collation Null Key Default Extra Privileges Comment 2088show keys from t2; 2089Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 2090t2 0 PRIMARY 1 auto A 1199 NULL NULL BTREE 2091t2 0 fld1 1 fld1 A 1199 NULL NULL BTREE 2092t2 1 fld3 1 fld3 A NULL NULL NULL BTREE 2093drop table t4, t3, t2, t1; 2094DO 1; 2095DO benchmark(100,1+1),1,1; 2096do default; 2097ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 2098do foobar; 2099ERROR 42S22: Unknown column 'foobar' in 'field list' 2100CREATE TABLE t1 ( 2101id mediumint(8) unsigned NOT NULL auto_increment, 2102pseudo varchar(35) NOT NULL default '', 2103PRIMARY KEY (id), 2104UNIQUE KEY pseudo (pseudo) 2105); 2106INSERT INTO t1 (pseudo) VALUES ('test'); 2107INSERT INTO t1 (pseudo) VALUES ('test1'); 2108SELECT 1 as rnd1 from t1 where rand() > 2; 2109rnd1 2110DROP TABLE t1; 2111CREATE TABLE t1 (gvid int(10) unsigned default NULL, hmid int(10) unsigned default NULL, volid int(10) unsigned default NULL, mmid int(10) unsigned default NULL, hdid int(10) unsigned default NULL, fsid int(10) unsigned default NULL, ctid int(10) unsigned default NULL, dtid int(10) unsigned default NULL, cost int(10) unsigned default NULL, performance int(10) unsigned default NULL, serialnumber bigint(20) unsigned default NULL, monitored tinyint(3) unsigned default '1', removed tinyint(3) unsigned default '0', target tinyint(3) unsigned default '0', dt_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, name varchar(255) binary default NULL, description varchar(255) default NULL, UNIQUE KEY hmid (hmid,volid)) ENGINE=MyISAM; 2112INSERT INTO t1 VALUES (200001,2,1,1,100,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\E$',''),(200002,2,2,1,101,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\C$',''),(200003,1,3,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,0,1,20020425060427,'c:',NULL); 2113CREATE TABLE t2 ( hmid int(10) unsigned default NULL, volid int(10) unsigned default NULL, sampletid smallint(5) unsigned default NULL, sampletime datetime default NULL, samplevalue bigint(20) unsigned default NULL, KEY idx1 (hmid,volid,sampletid,sampletime)) ENGINE=MyISAM; 2114INSERT INTO t2 VALUES (1,3,10,'2002-06-01 08:00:00',35),(1,3,1010,'2002-06-01 12:00:01',35); 2115SELECT a.gvid, (SUM(CASE b.sampletid WHEN 140 THEN b.samplevalue ELSE 0 END)) as the_success,(SUM(CASE b.sampletid WHEN 141 THEN b.samplevalue ELSE 0 END)) as the_fail,(SUM(CASE b.sampletid WHEN 142 THEN b.samplevalue ELSE 0 END)) as the_size,(SUM(CASE b.sampletid WHEN 143 THEN b.samplevalue ELSE 0 END)) as the_time FROM t1 a, t2 b WHERE a.hmid = b.hmid AND a.volid = b.volid AND b.sampletime >= 'wrong-date-value' AND b.sampletime < 'wrong-date-value' AND b.sampletid IN (140, 141, 142, 143) GROUP BY a.gvid; 2116gvid the_success the_fail the_size the_time 2117Warnings: 2118Warning 1292 Incorrect datetime value: 'wrong-date-value' for column 'sampletime' at row 1 2119Warning 1292 Incorrect datetime value: 'wrong-date-value' for column 'sampletime' at row 1 2120SELECT a.gvid, (SUM(CASE b.sampletid WHEN 140 THEN b.samplevalue ELSE 0 END)) as the_success,(SUM(CASE b.sampletid WHEN 141 THEN b.samplevalue ELSE 0 END)) as the_fail,(SUM(CASE b.sampletid WHEN 142 THEN b.samplevalue ELSE 0 END)) as the_size,(SUM(CASE b.sampletid WHEN 143 THEN b.samplevalue ELSE 0 END)) as the_time FROM t1 a, t2 b WHERE a.hmid = b.hmid AND a.volid = b.volid AND b.sampletime >= NULL AND b.sampletime < NULL AND b.sampletid IN (140, 141, 142, 143) GROUP BY a.gvid; 2121gvid the_success the_fail the_size the_time 2122DROP TABLE t1,t2; 2123create table t1 ( A_Id bigint(20) NOT NULL default '0', A_UpdateBy char(10) NOT NULL default '', A_UpdateDate bigint(20) NOT NULL default '0', A_UpdateSerial int(11) NOT NULL default '0', other_types bigint(20) NOT NULL default '0', wss_type bigint(20) NOT NULL default '0'); 2124INSERT INTO t1 VALUES (102935998719055004,'brade',1029359987,2,102935229116544068,102935229216544093); 2125select wss_type from t1 where wss_type ='102935229216544106'; 2126wss_type 2127select wss_type from t1 where wss_type ='102935229216544105'; 2128wss_type 2129select wss_type from t1 where wss_type ='102935229216544104'; 2130wss_type 2131select wss_type from t1 where wss_type ='102935229216544093'; 2132wss_type 2133102935229216544093 2134select wss_type from t1 where wss_type =102935229216544093; 2135wss_type 2136102935229216544093 2137drop table t1; 2138select 1+2,"aaaa",3.13*2.0 into @a,@b,@c; 2139select @a; 2140@a 21413 2142select @b; 2143@b 2144aaaa 2145select @c; 2146@c 21476.260 2148create table t1 (a int not null auto_increment primary key); 2149insert into t1 values (); 2150insert into t1 values (); 2151insert into t1 values (); 2152select * from (t1 as t2 left join t1 as t3 using (a)), t1; 2153a a 21541 1 21552 1 21563 1 21571 2 21582 2 21593 2 21601 3 21612 3 21623 3 2163select * from t1, (t1 as t2 left join t1 as t3 using (a)); 2164a a 21651 1 21662 1 21673 1 21681 2 21692 2 21703 2 21711 3 21722 3 21733 3 2174select * from (t1 as t2 left join t1 as t3 using (a)) straight_join t1; 2175a a 21761 1 21772 1 21783 1 21791 2 21802 2 21813 2 21821 3 21832 3 21843 3 2185select * from t1 straight_join (t1 as t2 left join t1 as t3 using (a)); 2186a a 21871 1 21882 1 21893 1 21901 2 21912 2 21923 2 21931 3 21942 3 21953 3 2196select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 on t1.a>1; 2197a a 21981 2 21992 2 22003 2 22011 3 22022 3 22033 3 2204select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1; 2205a a 22062 1 22073 1 22082 2 22093 2 22102 3 22113 3 2212select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 using ( a ); 2213a 22141 22152 22163 2217select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) using ( a ); 2218a 22191 22202 22213 2222select * from (t1 as t2 left join t1 as t3 using (a)) left outer join t1 on t1.a>1; 2223a a 22241 2 22251 3 22262 2 22272 3 22283 2 22293 3 2230select * from t1 left outer join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1; 2231a a 22321 NULL 22332 1 22342 2 22352 3 22363 1 22373 2 22383 3 2239select * from (t1 as t2 left join t1 as t3 using (a)) left join t1 using ( a ); 2240a 22411 22422 22433 2244select * from t1 left join (t1 as t2 left join t1 as t3 using (a)) using ( a ); 2245a 22461 22472 22483 2249select * from (t1 as t2 left join t1 as t3 using (a)) natural left join t1; 2250a 22511 22522 22533 2254select * from t1 natural left join (t1 as t2 left join t1 as t3 using (a)); 2255a 22561 22572 22583 2259select * from (t1 as t2 left join t1 as t3 using (a)) right join t1 on t1.a>1; 2260a a 2261NULL 1 22621 2 22632 2 22643 2 22651 3 22662 3 22673 3 2268select * from t1 right join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1; 2269a a 22702 1 22712 2 22722 3 22733 1 22743 2 22753 3 2276select * from (t1 as t2 left join t1 as t3 using (a)) right outer join t1 using ( a ); 2277a 22781 22792 22803 2281select * from t1 right outer join (t1 as t2 left join t1 as t3 using (a)) using ( a ); 2282a 22831 22842 22853 2286select * from (t1 as t2 left join t1 as t3 using (a)) natural right join t1; 2287a 22881 22892 22903 2291select * from t1 natural right join (t1 as t2 left join t1 as t3 using (a)); 2292a 22931 22942 22953 2296select * from t1 natural join (t1 as t2 left join t1 as t3 using (a)); 2297a 22981 22992 23003 2301select * from (t1 as t2 left join t1 as t3 using (a)) natural join t1; 2302a 23031 23042 23053 2306drop table t1; 2307CREATE TABLE t1 ( aa char(2), id int(11) NOT NULL auto_increment, t2_id int(11) NOT NULL default '0', PRIMARY KEY (id), KEY replace_id (t2_id)) ENGINE=MyISAM; 2308INSERT INTO t1 VALUES ("1",8264,2506),("2",8299,2517),("3",8301,2518),("4",8302,2519),("5",8303,2520),("6",8304,2521),("7",8305,2522); 2309CREATE TABLE t2 ( id int(11) NOT NULL auto_increment, PRIMARY KEY (id)) ENGINE=MyISAM; 2310INSERT INTO t2 VALUES (2517), (2518), (2519), (2520), (2521), (2522); 2311select * from t1, t2 WHERE t1.t2_id = t2.id and t1.t2_id > 0 order by t1.id LIMIT 0, 5; 2312aa id t2_id id 23132 8299 2517 2517 23143 8301 2518 2518 23154 8302 2519 2519 23165 8303 2520 2520 23176 8304 2521 2521 2318drop table t1,t2; 2319create table t1 (id1 int NOT NULL); 2320create table t2 (id2 int NOT NULL); 2321create table t3 (id3 int NOT NULL); 2322create table t4 (id4 int NOT NULL, id44 int NOT NULL, KEY (id4)); 2323insert into t1 values (1); 2324insert into t1 values (2); 2325insert into t2 values (1); 2326insert into t4 values (1,1); 2327explain select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3 2328left join t4 on id3 = id4 where id2 = 1 or id4 = 1; 2329id select_type table type possible_keys key key_len ref rows Extra 23301 SIMPLE t3 system NULL NULL NULL NULL 0 const row not found 23311 SIMPLE t4 const id4 NULL NULL NULL 1 NULL 23321 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL 23331 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop) 2334select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3 2335left join t4 on id3 = id4 where id2 = 1 or id4 = 1; 2336id1 id2 id3 id4 id44 23371 1 NULL NULL NULL 2338drop table t1,t2,t3,t4; 2339create table t1(s varchar(10) not null); 2340create table t2(s varchar(10) not null primary key); 2341create table t3(s varchar(10) not null primary key); 2342insert into t1 values ('one\t'), ('two\t'); 2343insert into t2 values ('one\r'), ('two\t'); 2344insert into t3 values ('one '), ('two\t'); 2345select * from t1 where s = 'one'; 2346s 2347select * from t2 where s = 'one'; 2348s 2349select * from t3 where s = 'one'; 2350s 2351one 2352select * from t1,t2 where t1.s = t2.s; 2353s s 2354two two 2355select * from t2,t3 where t2.s = t3.s; 2356s s 2357two two 2358drop table t1, t2, t3; 2359create table t1 (a integer, b integer, index(a), index(b)); 2360create table t2 (c integer, d integer, index(c), index(d)); 2361insert into t1 values (1,2), (2,2), (3,2), (4,2); 2362insert into t2 values (1,3), (2,3), (3,4), (4,4); 2363explain select * from t1 left join t2 on a=c where d in (4); 2364id select_type table type possible_keys key key_len ref rows Extra 23651 SIMPLE t2 ref c,d d 5 const 2 NULL 23661 SIMPLE t1 ALL a NULL NULL NULL 4 Using where; Using join buffer (Block Nested Loop) 2367select * from t1 left join t2 on a=c where d in (4); 2368a b c d 23693 2 3 4 23704 2 4 4 2371explain select * from t1 left join t2 on a=c where d = 4; 2372id select_type table type possible_keys key key_len ref rows Extra 23731 SIMPLE t2 ref c,d d 5 const 2 NULL 23741 SIMPLE t1 ALL a NULL NULL NULL 4 Using where; Using join buffer (Block Nested Loop) 2375select * from t1 left join t2 on a=c where d = 4; 2376a b c d 23773 2 3 4 23784 2 4 4 2379drop table t1, t2; 2380CREATE TABLE t1 ( 2381i int(11) NOT NULL default '0', 2382c char(10) NOT NULL default '', 2383PRIMARY KEY (i), 2384UNIQUE KEY c (c) 2385) ENGINE=MyISAM; 2386INSERT INTO t1 VALUES (1,'a'); 2387INSERT INTO t1 VALUES (2,'b'); 2388INSERT INTO t1 VALUES (3,'c'); 2389EXPLAIN SELECT i FROM t1 WHERE i=1; 2390id select_type table type possible_keys key key_len ref rows Extra 23911 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index 2392DROP TABLE t1; 2393CREATE TABLE t1 ( a BLOB, INDEX (a(20)) ); 2394CREATE TABLE t2 ( a BLOB, INDEX (a(20)) ); 2395INSERT INTO t1 VALUES ('one'),('two'),('three'),('four'),('five'); 2396INSERT INTO t2 VALUES ('one'),('two'),('three'),('four'),('five'); 2397EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USE INDEX (a) ON t1.a=t2.a; 2398id select_type table type possible_keys key key_len ref rows Extra 23991 SIMPLE t1 ALL NULL NULL NULL NULL 5 NULL 24001 SIMPLE t2 ALL a NULL NULL NULL 5 Using where; Using join buffer (Block Nested Loop) 2401EXPLAIN SELECT * FROM t1 LEFT JOIN t2 FORCE INDEX (a) ON t1.a=t2.a; 2402id select_type table type possible_keys key key_len ref rows Extra 24031 SIMPLE t1 ALL NULL NULL NULL NULL 5 NULL 24041 SIMPLE t2 ref a a 23 test.t1.a 2 Using where 2405DROP TABLE t1, t2; 2406CREATE TABLE t1 ( city char(30) ); 2407INSERT INTO t1 VALUES ('London'); 2408INSERT INTO t1 VALUES ('Paris'); 2409SELECT * FROM t1 WHERE city='London'; 2410city 2411London 2412SELECT * FROM t1 WHERE city='london'; 2413city 2414London 2415EXPLAIN SELECT * FROM t1 WHERE city='London' AND city='london'; 2416id select_type table type possible_keys key key_len ref rows Extra 24171 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where 2418SELECT * FROM t1 WHERE city='London' AND city='london'; 2419city 2420London 2421EXPLAIN SELECT * FROM t1 WHERE city LIKE '%london%' AND city='London'; 2422id select_type table type possible_keys key key_len ref rows Extra 24231 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where 2424SELECT * FROM t1 WHERE city LIKE '%london%' AND city='London'; 2425city 2426London 2427DROP TABLE t1; 2428create table t1 (a int(11) unsigned, b int(11) unsigned); 2429insert into t1 values (1,0), (1,1), (18446744073709551615,0); 2430Warnings: 2431Warning 1264 Out of range value for column 'a' at row 3 2432select a-b from t1 order by 1; 2433a-b 24340 24351 24364294967295 2437select a-b , (a-b < 0) from t1 order by 1; 2438a-b (a-b < 0) 24390 0 24401 0 24414294967295 0 2442select a-b as d, (a-b >= 0), b from t1 group by b having d >= 0; 2443d (a-b >= 0) b 24441 1 0 24450 1 1 2446select cast((a - b) as unsigned) from t1 order by 1; 2447cast((a - b) as unsigned) 24480 24491 24504294967295 2451drop table t1; 2452create table t1 (a int(11)); 2453select all all * from t1; 2454a 2455select distinct distinct * from t1; 2456a 2457select all distinct * from t1; 2458ERROR HY000: Incorrect usage of ALL and DISTINCT 2459select distinct all * from t1; 2460ERROR HY000: Incorrect usage of ALL and DISTINCT 2461drop table t1; 2462CREATE TABLE t1 ( 2463kunde_intern_id int(10) unsigned NOT NULL default '0', 2464kunde_id int(10) unsigned NOT NULL default '0', 2465FK_firma_id int(10) unsigned NOT NULL default '0', 2466aktuell enum('Ja','Nein') NOT NULL default 'Ja', 2467vorname varchar(128) NOT NULL default '', 2468nachname varchar(128) NOT NULL default '', 2469geloescht enum('Ja','Nein') NOT NULL default 'Nein', 2470firma varchar(128) NOT NULL default '' 2471); 2472INSERT INTO t1 VALUES 2473(3964,3051,1,'Ja','Vorname1','1Nachname','Nein','Print Schau XXXX'), 2474(3965,3051111,1,'Ja','Vorname1111','1111Nachname','Nein','Print Schau XXXX'); 2475SELECT kunde_id ,FK_firma_id ,aktuell, vorname, nachname, geloescht FROM t1 2476WHERE 2477( 2478( 2479( '' != '' AND firma LIKE CONCAT('%', '', '%')) 2480OR 2481(vorname LIKE CONCAT('%', 'Vorname1', '%') AND 2482nachname LIKE CONCAT('%', '1Nachname', '%') AND 2483'Vorname1' != '' AND 'xxxx' != '') 2484) 2485AND 2486( 2487aktuell = 'Ja' AND geloescht = 'Nein' AND FK_firma_id = 2 2488) 2489) 2490; 2491kunde_id FK_firma_id aktuell vorname nachname geloescht 2492SELECT kunde_id ,FK_firma_id ,aktuell, vorname, nachname, 2493geloescht FROM t1 2494WHERE 2495( 2496( 2497aktuell = 'Ja' AND geloescht = 'Nein' AND FK_firma_id = 2 2498) 2499AND 2500( 2501( '' != '' AND firma LIKE CONCAT('%', '', '%') ) 2502OR 2503( vorname LIKE CONCAT('%', 'Vorname1', '%') AND 2504nachname LIKE CONCAT('%', '1Nachname', '%') AND 'Vorname1' != '' AND 2505'xxxx' != '') 2506) 2507) 2508; 2509kunde_id FK_firma_id aktuell vorname nachname geloescht 2510SELECT COUNT(*) FROM t1 WHERE 2511( 0 OR (vorname LIKE '%Vorname1%' AND nachname LIKE '%1Nachname%' AND 1)) 2512AND FK_firma_id = 2; 2513COUNT(*) 25140 2515drop table t1; 2516CREATE TABLE t1 (b BIGINT(20) UNSIGNED NOT NULL, PRIMARY KEY (b)); 2517INSERT INTO t1 VALUES (0x8000000000000000); 2518SELECT b FROM t1 WHERE b=0x8000000000000000; 2519b 25209223372036854775808 2521DROP TABLE t1; 2522CREATE TABLE `t1` ( `gid` int(11) default NULL, `uid` int(11) default NULL); 2523CREATE TABLE `t2` ( `ident` int(11) default NULL, `level` char(16) default NULL); 2524INSERT INTO `t2` VALUES (0,'READ'); 2525CREATE TABLE `t3` ( `id` int(11) default NULL, `name` char(16) default NULL); 2526INSERT INTO `t3` VALUES (1,'fs'); 2527select * from t3 left join t1 on t3.id = t1.uid, t2 where t2.ident in (0, t1.gid, t3.id, 0); 2528id name gid uid ident level 25291 fs NULL NULL 0 READ 2530drop table t1,t2,t3; 2531CREATE TABLE t1 ( 2532acct_id int(11) NOT NULL default '0', 2533profile_id smallint(6) default NULL, 2534UNIQUE KEY t1$acct_id (acct_id), 2535KEY t1$profile_id (profile_id) 2536); 2537INSERT INTO t1 VALUES (132,17),(133,18); 2538CREATE TABLE t2 ( 2539profile_id smallint(6) default NULL, 2540queue_id int(11) default NULL, 2541seq int(11) default NULL, 2542KEY t2$queue_id (queue_id) 2543); 2544INSERT INTO t2 VALUES (17,31,4),(17,30,3),(17,36,2),(17,37,1); 2545CREATE TABLE t3 ( 2546id int(11) NOT NULL default '0', 2547qtype int(11) default NULL, 2548seq int(11) default NULL, 2549warn_lvl int(11) default NULL, 2550crit_lvl int(11) default NULL, 2551rr1 tinyint(4) NOT NULL default '0', 2552rr2 int(11) default NULL, 2553default_queue tinyint(4) NOT NULL default '0', 2554KEY t3$qtype (qtype), 2555KEY t3$id (id) 2556); 2557INSERT INTO t3 VALUES (30,1,29,NULL,NULL,0,NULL,0),(31,1,28,NULL,NULL,0,NULL,0), 2558(36,1,34,NULL,NULL,0,NULL,0),(37,1,35,NULL,NULL,0,121,0); 2559SELECT COUNT(*) FROM t1 a STRAIGHT_JOIN t2 pq STRAIGHT_JOIN t3 q 2560WHERE 2561(pq.profile_id = a.profile_id) AND (a.acct_id = 132) AND 2562(pq.queue_id = q.id) AND (q.rr1 <> 1); 2563COUNT(*) 25644 2565drop table t1,t2,t3; 2566create table t1 (f1 int); 2567insert into t1 values (1),(NULL); 2568create table t2 (f2 int, f3 int, f4 int); 2569create index idx1 on t2 (f4); 2570insert into t2 values (1,2,3),(2,4,6); 2571select A.f2 from t1 left join t2 A on A.f2 = f1 where A.f3=(select min(f3) 2572from t2 C where A.f4 = C.f4) or A.f3 IS NULL; 2573f2 25741 2575NULL 2576drop table t1,t2; 2577create table t2 (a tinyint unsigned); 2578create index t2i on t2(a); 2579insert into t2 values (0), (254), (255); 2580explain select * from t2 where a > -1; 2581id select_type table type possible_keys key key_len ref rows Extra 25821 SIMPLE t2 index t2i t2i 2 NULL 3 Using where; Using index 2583select * from t2 where a > -1; 2584a 25850 2586254 2587255 2588drop table t2; 2589CREATE TABLE t1 (a int, b int, c int); 2590INSERT INTO t1 2591SELECT 50, 3, 3 FROM DUAL 2592WHERE NOT EXISTS 2593(SELECT * FROM t1 WHERE a = 50 AND b = 3); 2594SELECT * FROM t1; 2595a b c 259650 3 3 2597INSERT INTO t1 2598SELECT 50, 3, 3 FROM DUAL 2599WHERE NOT EXISTS 2600(SELECT * FROM t1 WHERE a = 50 AND b = 3); 2601select found_rows(); 2602found_rows() 26030 2604SELECT * FROM t1; 2605a b c 260650 3 3 2607select count(*) from t1; 2608count(*) 26091 2610select found_rows(); 2611found_rows() 26121 2613select count(*) from t1 limit 2,3; 2614count(*) 2615select found_rows(); 2616found_rows() 26170 2618select SQL_CALC_FOUND_ROWS count(*) from t1 limit 2,3; 2619count(*) 2620select found_rows(); 2621found_rows() 26221 2623DROP TABLE t1; 2624CREATE TABLE t1 (a INT, b INT); 2625(SELECT a, b AS c FROM t1) ORDER BY c+1; 2626a c 2627(SELECT a, b AS c FROM t1) ORDER BY b+1; 2628a c 2629SELECT a, b AS c FROM t1 ORDER BY c+1; 2630a c 2631SELECT a, b AS c FROM t1 ORDER BY b+1; 2632a c 2633drop table t1; 2634create table t1(f1 int, f2 int); 2635create table t2(f3 int); 2636select f1 from t1,t2 where f1=f2 and (f1,f2) = ((1,1)); 2637f1 2638select f1 from t1,t2 where f1=f2 and (f1,NULL) = ((1,1)); 2639f1 2640select f1 from t1,t2 where f1=f2 and (f1,f2) = ((1,NULL)); 2641f1 2642insert into t1 values(1,1),(2,null); 2643insert into t2 values(2); 2644select * from t1,t2 where f1=f3 and (f1,f2) = (2,null); 2645f1 f2 f3 2646select * from t1,t2 where f1=f3 and (f1,f2) <=> (2,null); 2647f1 f2 f3 26482 NULL 2 2649drop table t1,t2; 2650create table t1 (f1 int not null auto_increment primary key, f2 varchar(10)); 2651create table t11 like t1; 2652insert into t1 values(1,""),(2,""); 2653show table status like 't1%'; 2654Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment 2655t1 MyISAM 10 Dynamic 2 20 X X X X X X X X latin1_swedish_ci NULL 2656t11 MyISAM 10 Dynamic 0 0 X X X X X X X X latin1_swedish_ci NULL 2657select 123 as a from t1 where f1 is null; 2658a 2659drop table t1,t11; 2660CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL, UNIQUE idx (a,b) ); 2661INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4); 2662CREATE TABLE t2 ( a INT NOT NULL, b INT NOT NULL, e INT ); 2663INSERT INTO t2 VALUES ( 1,10,1), (1,10,2), (1,11,1), (1,11,2), (1,2,1), (1,2,2),(1,2,3); 2664SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2 LEFT JOIN 2665t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY a, b, c; 2666a b c d 26671 2 1 1 26681 2 2 1 26691 2 3 1 26701 10 2 26711 11 2 2672SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2 LEFT JOIN 2673t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY t1.a, t1.b, c; 2674a b c d 26751 10 4 26761 2 1 1 26771 2 2 1 26781 2 3 1 2679SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2 LEFT JOIN 2680t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY t2.a, t2.b, c; 2681a b c d 26821 2 1 1 26831 2 2 1 26841 2 3 1 26851 10 2 26861 11 2 2687SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2,t1 2688WHERE t2.a = t1.a AND t2.b = t1.b GROUP BY a, b, c; 2689a b c d 26901 2 1 1 26911 2 2 1 26921 2 3 1 2693DROP TABLE IF EXISTS t1, t2; 2694create table t1 (f1 int primary key, f2 int); 2695create table t2 (f3 int, f4 int, primary key(f3,f4)); 2696insert into t1 values (1,1); 2697insert into t2 values (1,1),(1,2); 2698select distinct count(f2) >0 from t1 left join t2 on f1=f3 group by f1; 2699count(f2) >0 27001 2701drop table t1,t2; 2702create table t1 (f1 int,f2 int); 2703insert into t1 values(1,1); 2704create table t2 (f3 int, f4 int, primary key(f3,f4)); 2705insert into t2 values(1,1); 2706select * from t1 where f1 in (select f3 from t2 where (f3,f4)= (select f3,f4 from t2)); 2707f1 f2 27081 1 2709drop table t1,t2; 2710CREATE TABLE t1(a int, b int, c int, KEY b(b), KEY c(c)); 2711insert into t1 values (1,0,0),(2,0,0); 2712CREATE TABLE t2 (a int, b varchar(2), c varchar(2), PRIMARY KEY(a)); 2713insert into t2 values (1,'',''), (2,'',''); 2714CREATE TABLE t3 (a int, b int, PRIMARY KEY (a,b), KEY a (a), KEY b (b)); 2715insert into t3 values (1,1),(1,2); 2716explain select straight_join DISTINCT t2.a,t2.b, t1.c from t1, t3, t2 2717where (t1.c=t2.a or (t1.c=t3.a and t2.a=t3.b)) and t1.b=556476786 and 2718t2.b like '%%' order by t2.b limit 0,1; 2719id select_type table type possible_keys key key_len ref rows Extra 27201 SIMPLE t1 ref b,c b 5 const 1 Using temporary; Using filesort 27211 SIMPLE t3 index PRIMARY,a,b PRIMARY 8 NULL 2 Using index; Using join buffer (Block Nested Loop) 27221 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1) 2723DROP TABLE t1,t2,t3; 2724CREATE TABLE t1 (a int, INDEX idx(a)); 2725INSERT INTO t1 VALUES (2), (3), (1); 2726EXPLAIN SELECT * FROM t1 IGNORE INDEX (idx); 2727id select_type table type possible_keys key key_len ref rows Extra 27281 SIMPLE t1 ALL NULL NULL NULL NULL 3 NULL 2729EXPLAIN SELECT * FROM t1 IGNORE INDEX (a); 2730ERROR 42000: Key 'a' doesn't exist in table 't1' 2731EXPLAIN SELECT * FROM t1 FORCE INDEX (a); 2732ERROR 42000: Key 'a' doesn't exist in table 't1' 2733DROP TABLE t1; 2734CREATE TABLE t1 (a int, b int); 2735INSERT INTO t1 VALUES (1,1), (2,1), (4,10); 2736CREATE TABLE t2 (a int PRIMARY KEY, b int, KEY b (b)); 2737INSERT INTO t2 VALUES (1,NULL), (2,10); 2738ALTER TABLE t1 ENABLE KEYS; 2739EXPLAIN SELECT STRAIGHT_JOIN SQL_NO_CACHE COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; 2740id select_type table type possible_keys key key_len ref rows Extra 27411 SIMPLE t2 index b b 5 NULL 2 Using index 27421 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (Block Nested Loop) 2743SELECT STRAIGHT_JOIN SQL_NO_CACHE * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; 2744a b a b 27451 NULL 1 1 27461 NULL 2 1 27471 NULL 4 10 27482 10 4 10 2749EXPLAIN SELECT STRAIGHT_JOIN SQL_NO_CACHE COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; 2750id select_type table type possible_keys key key_len ref rows Extra 27511 SIMPLE t2 index b b 5 NULL 2 Using index 27521 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (Block Nested Loop) 2753SELECT STRAIGHT_JOIN SQL_NO_CACHE * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; 2754a b a b 27551 NULL 1 1 27561 NULL 2 1 27571 NULL 4 10 27582 10 4 10 2759DROP TABLE IF EXISTS t1,t2; 2760CREATE TABLE t1 (key1 float default NULL, UNIQUE KEY key1 (key1)); 2761CREATE TABLE t2 (key2 float default NULL, UNIQUE KEY key2 (key2)); 2762INSERT INTO t1 VALUES (0.3762),(0.3845),(0.6158),(0.7941); 2763INSERT INTO t2 VALUES (1.3762),(1.3845),(1.6158),(1.7941); 2764explain select max(key1) from t1 where key1 <= 0.6158; 2765id select_type table type possible_keys key key_len ref rows Extra 27661 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2767explain select max(key2) from t2 where key2 <= 1.6158; 2768id select_type table type possible_keys key key_len ref rows Extra 27691 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2770explain select min(key1) from t1 where key1 >= 0.3762; 2771id select_type table type possible_keys key key_len ref rows Extra 27721 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2773explain select min(key2) from t2 where key2 >= 1.3762; 2774id select_type table type possible_keys key key_len ref rows Extra 27751 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2776explain select max(key1), min(key2) from t1, t2 2777where key1 <= 0.6158 and key2 >= 1.3762; 2778id select_type table type possible_keys key key_len ref rows Extra 27791 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2780explain select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5; 2781id select_type table type possible_keys key key_len ref rows Extra 27821 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2783explain select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5; 2784id select_type table type possible_keys key key_len ref rows Extra 27851 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2786select max(key1) from t1 where key1 <= 0.6158; 2787max(key1) 27880.6158000230789185 2789select max(key2) from t2 where key2 <= 1.6158; 2790max(key2) 27911.6158000230789185 2792select min(key1) from t1 where key1 >= 0.3762; 2793min(key1) 27940.37619999051094055 2795select min(key2) from t2 where key2 >= 1.3762; 2796min(key2) 27971.3761999607086182 2798select max(key1), min(key2) from t1, t2 2799where key1 <= 0.6158 and key2 >= 1.3762; 2800max(key1) min(key2) 28010.6158000230789185 1.3761999607086182 2802select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5; 2803max(key1) 28040.6158000230789185 2805select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5; 2806min(key1) 28070.37619999051094055 2808DROP TABLE t1,t2; 2809CREATE TABLE t1 (i BIGINT UNSIGNED NOT NULL); 2810INSERT INTO t1 VALUES (10); 2811SELECT i='1e+01',i=1e+01, i in (1e+01,1e+01), i in ('1e+01','1e+01') FROM t1; 2812i='1e+01' i=1e+01 i in (1e+01,1e+01) i in ('1e+01','1e+01') 28131 1 1 1 2814DROP TABLE t1; 2815create table t1(a bigint unsigned, b bigint); 2816insert into t1 values (0xfffffffffffffffff, 0xfffffffffffffffff), 2817(0x10000000000000000, 0x10000000000000000), 2818(0x8fffffffffffffff, 0x8fffffffffffffff); 2819Warnings: 2820Warning 1264 Out of range value for column 'a' at row 1 2821Warning 1264 Out of range value for column 'b' at row 1 2822Warning 1264 Out of range value for column 'a' at row 2 2823Warning 1264 Out of range value for column 'b' at row 2 2824Warning 1264 Out of range value for column 'b' at row 3 2825select hex(a), hex(b) from t1; 2826hex(a) hex(b) 2827FFFFFFFFFFFFFFFF 7FFFFFFFFFFFFFFF 2828FFFFFFFFFFFFFFFF 7FFFFFFFFFFFFFFF 28298FFFFFFFFFFFFFFF 7FFFFFFFFFFFFFFF 2830drop table t1; 2831CREATE TABLE t1 (c0 int); 2832CREATE TABLE t2 (c0 int); 2833INSERT INTO t1 VALUES(@@connect_timeout); 2834INSERT INTO t2 VALUES(@@connect_timeout); 2835SELECT * FROM t1 JOIN t2 ON t1.c0 = t2.c0 WHERE (t1.c0 <=> @@connect_timeout); 2836c0 c0 2837X X 2838DROP TABLE t1, t2; 2839End of 4.1 tests 2840CREATE TABLE t1 ( 2841K2C4 varchar(4) character set latin1 collate latin1_bin NOT NULL default '', 2842K4N4 varchar(4) character set latin1 collate latin1_bin NOT NULL default '0000', 2843F2I4 int(11) NOT NULL default '0' 2844) ENGINE=MyISAM DEFAULT CHARSET=latin1; 2845INSERT INTO t1 VALUES 2846('W%RT', '0100', 1), 2847('W-RT', '0100', 1), 2848('WART', '0100', 1), 2849('WART', '0200', 1), 2850('WERT', '0100', 2), 2851('WORT','0200', 2), 2852('WT', '0100', 2), 2853('W_RT', '0100', 2), 2854('WaRT', '0100', 3), 2855('WART', '0300', 3), 2856('WRT' , '0400', 3), 2857('WURM', '0500', 3), 2858('W%T', '0600', 4), 2859('WA%T', '0700', 4), 2860('WA_T', '0800', 4); 2861SELECT K2C4, K4N4, F2I4 FROM t1 2862WHERE K2C4 = 'WART' AND 2863(F2I4 = 2 AND K2C4 = 'WART' OR (F2I4 = 2 OR K4N4 = '0200')); 2864K2C4 K4N4 F2I4 2865WART 0200 1 2866SELECT K2C4, K4N4, F2I4 FROM t1 2867WHERE K2C4 = 'WART' AND (K2C4 = 'WART' OR K4N4 = '0200'); 2868K2C4 K4N4 F2I4 2869WART 0100 1 2870WART 0200 1 2871WART 0300 3 2872DROP TABLE t1; 2873create table t1 (a int, b int); 2874create table t2 like t1; 2875select t1.a from (t1 inner join t2 on t1.a=t2.a) where t2.a=1; 2876a 2877select t1.a from ((t1 inner join t2 on t1.a=t2.a)) where t2.a=1; 2878a 2879select x.a, y.a, z.a from ( (t1 x inner join t2 y on x.a=y.a) inner join t2 z on y.a=z.a) WHERE x.a=1; 2880a a a 2881drop table t1,t2; 2882create table t1 (s1 varchar(5)); 2883insert into t1 values ('Wall'); 2884select min(s1) from t1 group by s1 with rollup; 2885min(s1) 2886Wall 2887Wall 2888drop table t1; 2889create table t1 (s1 int) engine=myisam; 2890insert into t1 values (0); 2891select avg(distinct s1) from t1 group by s1 with rollup; 2892avg(distinct s1) 28930.0000 28940.0000 2895drop table t1; 2896create table t1 (s1 int); 2897insert into t1 values (null),(1); 2898select avg(s1) as x from t1 group by s1 with rollup; 2899x 2900NULL 29011.0000 29021.0000 2903select distinct avg(s1) as x from t1 group by s1 with rollup; 2904ERROR HY000: Incorrect usage of WITH ROLLUP and DISTINCT 2905drop table t1; 2906CREATE TABLE t1 (a int); 2907CREATE TABLE t2 (a int); 2908INSERT INTO t1 VALUES (1), (2), (3), (4), (5); 2909INSERT INTO t2 VALUES (2), (4), (6); 2910SELECT t1.a FROM t1 STRAIGHT_JOIN t2 ON t1.a=t2.a; 2911a 29122 29134 2914EXPLAIN SELECT t1.a FROM t1 STRAIGHT_JOIN t2 ON t1.a=t2.a; 2915id select_type table type possible_keys key key_len ref rows Extra 29161 SIMPLE t1 ALL NULL NULL NULL NULL 5 NULL 29171 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (Block Nested Loop) 2918EXPLAIN SELECT t1.a FROM t1 INNER JOIN t2 ON t1.a=t2.a; 2919id select_type table type possible_keys key key_len ref rows Extra 29201 SIMPLE t2 ALL NULL NULL NULL NULL 3 NULL 29211 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (Block Nested Loop) 2922DROP TABLE t1,t2; 2923select x'10' + 0, X'10' + 0, b'10' + 0, B'10' + 0; 2924x'10' + 0 X'10' + 0 b'10' + 0 B'10' + 0 292516 16 2 2 2926create table t1 (f1 varchar(6) default NULL, f2 int(6) primary key not null); 2927create table t2 (f3 varchar(5) not null, f4 varchar(5) not null, UNIQUE KEY UKEY (f3,f4)); 2928insert into t1 values (" 2", 2); 2929insert into t2 values (" 2", " one "),(" 2", " two "); 2930select * from t1 left join t2 on f1 = f3; 2931f1 f2 f3 f4 2932 2 2 2 one 2933 2 2 2 two 2934drop table t1,t2; 2935create table t1 (empnum smallint, grp int); 2936create table t2 (empnum int, name char(5)); 2937insert into t1 values(1,1); 2938insert into t2 values(1,'bob'); 2939create view v1 as select * from t2 inner join t1 using (empnum); 2940select * from v1; 2941empnum name grp 29421 bob 1 2943drop table t1,t2; 2944drop view v1; 2945create table t1 (pk int primary key, b int); 2946create table t2 (pk int primary key, c int); 2947select pk from t1 inner join t2 using (pk); 2948pk 2949drop table t1,t2; 2950create table t1 (s1 int, s2 char(5), s3 decimal(10)); 2951create view v1 as select s1, s2, 'x' as s3 from t1; 2952select * from t1 natural join v1; 2953s1 s2 s3 2954insert into t1 values (1,'x',5); 2955select * from t1 natural join v1; 2956s1 s2 s3 2957Warnings: 2958Warning 1292 Truncated incorrect DOUBLE value: 'x' 2959drop table t1; 2960drop view v1; 2961create table t1(a1 int); 2962create table t2(a2 int); 2963insert into t1 values(1),(2); 2964insert into t2 values(1),(2); 2965create view v2 (c) as select a1 from t1; 2966select * from t1 natural left join t2; 2967a1 a2 29681 1 29691 2 29702 1 29712 2 2972select * from t1 natural right join t2; 2973a2 a1 29741 1 29751 2 29762 1 29772 2 2978select * from v2 natural left join t2; 2979c a2 29801 1 29811 2 29822 1 29832 2 2984select * from v2 natural right join t2; 2985a2 c 29861 1 29871 2 29882 1 29892 2 2990drop table t1, t2; 2991drop view v2; 2992create table t1 (a int(10), t1_val int(10)); 2993create table t2 (b int(10), t2_val int(10)); 2994create table t3 (a int(10), b int(10)); 2995insert into t1 values (1,1),(2,2); 2996insert into t2 values (1,1),(2,2),(3,3); 2997insert into t3 values (1,1),(2,1),(3,1),(4,1); 2998select * from t1 natural join t2 natural join t3; 2999a b t1_val t2_val 30001 1 1 1 30012 1 2 1 3002select * from t1 natural join t3 natural join t2; 3003b a t1_val t2_val 30041 1 1 1 30051 2 2 1 3006drop table t1, t2, t3; 3007DO IFNULL(NULL, NULL); 3008SELECT CAST(IFNULL(NULL, NULL) AS DECIMAL); 3009CAST(IFNULL(NULL, NULL) AS DECIMAL) 3010NULL 3011SELECT ABS(IFNULL(NULL, NULL)); 3012ABS(IFNULL(NULL, NULL)) 3013NULL 3014SELECT IFNULL(NULL, NULL); 3015IFNULL(NULL, NULL) 3016NULL 3017SET @OLD_SQL_MODE12595=@@SQL_MODE, @@SQL_MODE=''; 3018SHOW LOCAL VARIABLES LIKE 'SQL_MODE'; 3019Variable_name Value 3020sql_mode 3021CREATE TABLE BUG_12595(a varchar(100)); 3022INSERT INTO BUG_12595 VALUES ('hakan%'), ('hakank'), ("ha%an"); 3023SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\%'; 3024a 3025hakan% 3026SELECT * FROM BUG_12595 WHERE a LIKE 'hakan*%' ESCAPE '*'; 3027a 3028hakan% 3029SELECT * FROM BUG_12595 WHERE a LIKE 'hakan**%' ESCAPE '**'; 3030ERROR HY000: Incorrect arguments to ESCAPE 3031SELECT * FROM BUG_12595 WHERE a LIKE 'hakan%' ESCAPE ''; 3032a 3033hakan% 3034hakank 3035SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\%' ESCAPE ''; 3036a 3037SELECT * FROM BUG_12595 WHERE a LIKE 'ha\%an' ESCAPE 0x5c; 3038a 3039ha%an 3040SELECT * FROM BUG_12595 WHERE a LIKE 'ha%%an' ESCAPE '%'; 3041a 3042ha%an 3043SELECT * FROM BUG_12595 WHERE a LIKE 'ha\%an' ESCAPE '\\'; 3044a 3045ha%an 3046SELECT * FROM BUG_12595 WHERE a LIKE 'ha|%an' ESCAPE '|'; 3047a 3048ha%an 3049SET @@SQL_MODE='NO_BACKSLASH_ESCAPES'; 3050SHOW LOCAL VARIABLES LIKE 'SQL_MODE'; 3051Variable_name Value 3052sql_mode NO_BACKSLASH_ESCAPES 3053SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\%'; 3054a 3055SELECT * FROM BUG_12595 WHERE a LIKE 'hakan*%' ESCAPE '*'; 3056a 3057hakan% 3058SELECT * FROM BUG_12595 WHERE a LIKE 'hakan**%' ESCAPE '**'; 3059ERROR HY000: Incorrect arguments to ESCAPE 3060SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\%' ESCAPE '\\'; 3061ERROR HY000: Incorrect arguments to ESCAPE 3062SELECT * FROM BUG_12595 WHERE a LIKE 'hakan%' ESCAPE ''; 3063ERROR HY000: Incorrect arguments to ESCAPE 3064SELECT * FROM BUG_12595 WHERE a LIKE 'ha\%an' ESCAPE 0x5c; 3065a 3066ha%an 3067SELECT * FROM BUG_12595 WHERE a LIKE 'ha|%an' ESCAPE '|'; 3068a 3069ha%an 3070SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\n%' ESCAPE '\n'; 3071ERROR HY000: Incorrect arguments to ESCAPE 3072SET @@SQL_MODE=@OLD_SQL_MODE12595; 3073DROP TABLE BUG_12595; 3074create table t1 (a char(1)); 3075create table t2 (a char(1)); 3076insert into t1 values ('a'),('b'),('c'); 3077insert into t2 values ('b'),('c'),('d'); 3078select a from t1 natural join t2; 3079a 3080b 3081c 3082select * from t1 natural join t2 where a = 'b'; 3083a 3084b 3085drop table t1, t2; 3086CREATE TABLE t1 (`id` TINYINT); 3087CREATE TABLE t2 (`id` TINYINT); 3088CREATE TABLE t3 (`id` TINYINT); 3089INSERT INTO t1 VALUES (1),(2),(3); 3090INSERT INTO t2 VALUES (2); 3091INSERT INTO t3 VALUES (3); 3092SELECT t1.id,t3.id FROM t1 JOIN t2 ON (t2.id=t1.id) LEFT JOIN t3 USING (id); 3093ERROR 23000: Column 'id' in from clause is ambiguous 3094SELECT t1.id,t3.id FROM t1 JOIN t2 ON (t2.notacolumn=t1.id) LEFT JOIN t3 USING (id); 3095ERROR 23000: Column 'id' in from clause is ambiguous 3096SELECT id,t3.id FROM t1 JOIN t2 ON (t2.id=t1.id) LEFT JOIN t3 USING (id); 3097ERROR 23000: Column 'id' in from clause is ambiguous 3098SELECT id,t3.id FROM (t1 JOIN t2 ON (t2.id=t1.id)) LEFT JOIN t3 USING (id); 3099ERROR 23000: Column 'id' in from clause is ambiguous 3100drop table t1, t2, t3; 3101create table t1 (a int(10),b int(10)); 3102create table t2 (a int(10),b int(10)); 3103insert into t1 values (1,10),(2,20),(3,30); 3104insert into t2 values (1,10); 3105select * from t1 inner join t2 using (A); 3106a b b 31071 10 10 3108select * from t1 inner join t2 using (a); 3109a b b 31101 10 10 3111drop table t1, t2; 3112create table t1 (a int, c int); 3113create table t2 (b int); 3114create table t3 (b int, a int); 3115create table t4 (c int); 3116insert into t1 values (1,1); 3117insert into t2 values (1); 3118insert into t3 values (1,1); 3119insert into t4 values (1); 3120select * from t1 join t2 join t3 on (t2.b = t3.b and t1.a = t3.a); 3121a c b b a 31221 1 1 1 1 3123select * from t1, t2 join t3 on (t2.b = t3.b and t1.a = t3.a); 3124ERROR 42S22: Unknown column 't1.a' in 'on clause' 3125select * from t1 join t2 join t3 join t4 on (t1.a = t4.c and t2.b = t4.c); 3126a c b b a c 31271 1 1 1 1 1 3128select * from t1 join t2 join t4 using (c); 3129c a b 31301 1 1 3131drop table t1, t2, t3, t4; 3132create table t1(x int, y int); 3133create table t2(x int, y int); 3134create table t3(x int, primary key(x)); 3135insert into t1 values (1, 1), (2, 1), (3, 1), (4, 3), (5, 6), (6, 6); 3136insert into t2 values (1, 1), (2, 1), (3, 3), (4, 6), (5, 6); 3137insert into t3 values (1), (2), (3), (4), (5); 3138select t1.x, t3.x from t1, t2, t3 where t1.x = t2.x and t3.x >= t1.y and t3.x <= t2.y; 3139x x 31401 1 31412 1 31423 1 31433 2 31443 3 31454 3 31464 4 31474 5 3148drop table t1,t2,t3; 3149create table t1 (id char(16) not null default '', primary key (id)); 3150insert into t1 values ('100'),('101'),('102'); 3151create table t2 (id char(16) default null); 3152insert into t2 values (1); 3153create view v1 as select t1.id from t1; 3154create view v2 as select t2.id from t2; 3155create view v3 as select (t1.id+2) as id from t1 natural left join t2; 3156select t1.id from t1 left join v2 using (id); 3157id 3158100 3159101 3160102 3161select t1.id from v2 right join t1 using (id); 3162id 3163100 3164101 3165102 3166select t1.id from t1 left join v3 using (id); 3167id 3168100 3169101 3170102 3171select * from t1 left join v2 using (id); 3172id 3173100 3174101 3175102 3176select * from v2 right join t1 using (id); 3177id 3178100 3179101 3180102 3181select * from t1 left join v3 using (id); 3182id 3183100 3184101 3185102 3186select v1.id from v1 left join v2 using (id); 3187id 3188100 3189101 3190102 3191select v1.id from v2 right join v1 using (id); 3192id 3193100 3194101 3195102 3196select v1.id from v1 left join v3 using (id); 3197id 3198100 3199101 3200102 3201select * from v1 left join v2 using (id); 3202id 3203100 3204101 3205102 3206select * from v2 right join v1 using (id); 3207id 3208100 3209101 3210102 3211select * from v1 left join v3 using (id); 3212id 3213100 3214101 3215102 3216drop table t1, t2; 3217drop view v1, v2, v3; 3218create table t1 (id int(11) not null default '0'); 3219insert into t1 values (123),(191),(192); 3220create table t2 (id char(16) character set utf8 not null); 3221insert into t2 values ('58013'),('58014'),('58015'),('58016'); 3222create table t3 (a_id int(11) not null, b_id char(16) character set utf8); 3223insert into t3 values (123,null),(123,null),(123,null),(123,null),(123,null),(123,'58013'); 3224select count(*) 3225from t1 inner join (t3 left join t2 on t2.id = t3.b_id) on t1.id = t3.a_id; 3226count(*) 32276 3228select count(*) 3229from t1 inner join (t2 right join t3 on t2.id = t3.b_id) on t1.id = t3.a_id; 3230count(*) 32316 3232drop table t1,t2,t3; 3233create table t1 (a int); 3234create table t2 (b int); 3235create table t3 (c int); 3236select * from t1 join t2 join t3 on (t1.a=t3.c); 3237a b c 3238select * from t1 join t2 left join t3 on (t1.a=t3.c); 3239a b c 3240select * from t1 join t2 right join t3 on (t1.a=t3.c); 3241a b c 3242select * from t1 join t2 straight_join t3 on (t1.a=t3.c); 3243a b c 3244drop table t1, t2 ,t3; 3245create table t1(f1 int, f2 date); 3246insert into t1 values(1,'2005-01-01'),(2,'2005-09-01'),(3,'2005-09-30'), 3247(4,'2005-10-01'),(5,'2005-12-30'); 3248select * from t1 where f2 >= 0 order by f2; 3249f1 f2 32501 2005-01-01 32512 2005-09-01 32523 2005-09-30 32534 2005-10-01 32545 2005-12-30 3255select * from t1 where f2 >= '0000-00-00' order by f2; 3256f1 f2 32571 2005-01-01 32582 2005-09-01 32593 2005-09-30 32604 2005-10-01 32615 2005-12-30 3262select * from t1 where f2 >= '2005-09-31' order by f2; 3263f1 f2 32644 2005-10-01 32655 2005-12-30 3266select * from t1 where f2 >= '2005-09-3a' order by f2; 3267f1 f2 32683 2005-09-30 32694 2005-10-01 32705 2005-12-30 3271Warnings: 3272Warning 1292 Incorrect date value: '2005-09-3a' for column 'f2' at row 1 3273select * from t1 where f2 <= '2005-09-31' order by f2; 3274f1 f2 32751 2005-01-01 32762 2005-09-01 32773 2005-09-30 3278select * from t1 where f2 <= '2005-09-3a' order by f2; 3279f1 f2 32801 2005-01-01 32812 2005-09-01 3282Warnings: 3283Warning 1292 Incorrect date value: '2005-09-3a' for column 'f2' at row 1 3284drop table t1; 3285create table t1 (f1 int, f2 int); 3286insert into t1 values (1, 30), (2, 20), (3, 10); 3287create algorithm=merge view v1 as select f1, f2 from t1; 3288create algorithm=merge view v2 (f2, f1) as select f1, f2 from t1; 3289create algorithm=merge view v3 as select t1.f1 as f2, t1.f2 as f1 from t1; 3290select t1.f1 as x1, f1 from t1 order by t1.f1; 3291x1 f1 32921 1 32932 2 32943 3 3295select v1.f1 as x1, f1 from v1 order by v1.f1; 3296x1 f1 32971 1 32982 2 32993 3 3300select v2.f1 as x1, f1 from v2 order by v2.f1; 3301x1 f1 330210 10 330320 20 330430 30 3305select v3.f1 as x1, f1 from v3 order by v3.f1; 3306x1 f1 330710 10 330820 20 330930 30 3310select f1, f2, v1.f1 as x1 from v1 order by v1.f1; 3311f1 f2 x1 33121 30 1 33132 20 2 33143 10 3 3315select f1, f2, v2.f1 as x1 from v2 order by v2.f1; 3316f1 f2 x1 331710 3 10 331820 2 20 331930 1 30 3320select f1, f2, v3.f1 as x1 from v3 order by v3.f1; 3321f1 f2 x1 332210 3 10 332320 2 20 332430 1 30 3325drop table t1; 3326drop view v1, v2, v3; 3327CREATE TABLE t1(key_a int4 NOT NULL, optimus varchar(32), PRIMARY KEY(key_a)); 3328CREATE TABLE t2(key_a int4 NOT NULL, prime varchar(32), PRIMARY KEY(key_a)); 3329CREATE table t3(key_a int4 NOT NULL, key_b int4 NOT NULL, foo varchar(32), 3330PRIMARY KEY(key_a,key_b)); 3331INSERT INTO t1 VALUES (0,''); 3332INSERT INTO t1 VALUES (1,'i'); 3333INSERT INTO t1 VALUES (2,'j'); 3334INSERT INTO t1 VALUES (3,'k'); 3335INSERT INTO t2 VALUES (1,'r'); 3336INSERT INTO t2 VALUES (2,'s'); 3337INSERT INTO t2 VALUES (3,'t'); 3338INSERT INTO t3 VALUES (1,5,'x'); 3339INSERT INTO t3 VALUES (1,6,'y'); 3340INSERT INTO t3 VALUES (2,5,'xx'); 3341INSERT INTO t3 VALUES (2,6,'yy'); 3342INSERT INTO t3 VALUES (2,7,'zz'); 3343INSERT INTO t3 VALUES (3,5,'xxx'); 3344SELECT t2.key_a,foo 3345FROM t1 INNER JOIN t2 ON t1.key_a = t2.key_a 3346INNER JOIN t3 ON t1.key_a = t3.key_a 3347WHERE t2.key_a=2 and key_b=5; 3348key_a foo 33492 xx 3350EXPLAIN SELECT t2.key_a,foo 3351FROM t1 INNER JOIN t2 ON t1.key_a = t2.key_a 3352INNER JOIN t3 ON t1.key_a = t3.key_a 3353WHERE t2.key_a=2 and key_b=5; 3354id select_type table type possible_keys key key_len ref rows Extra 33551 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index 33561 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 Using index 33571 SIMPLE t3 const PRIMARY PRIMARY 8 const,const 1 NULL 3358SELECT t2.key_a,foo 3359FROM t1 INNER JOIN t2 ON t2.key_a = t1.key_a 3360INNER JOIN t3 ON t1.key_a = t3.key_a 3361WHERE t2.key_a=2 and key_b=5; 3362key_a foo 33632 xx 3364EXPLAIN SELECT t2.key_a,foo 3365FROM t1 INNER JOIN t2 ON t2.key_a = t1.key_a 3366INNER JOIN t3 ON t1.key_a = t3.key_a 3367WHERE t2.key_a=2 and key_b=5; 3368id select_type table type possible_keys key key_len ref rows Extra 33691 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index 33701 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 Using index 33711 SIMPLE t3 const PRIMARY PRIMARY 8 const,const 1 NULL 3372DROP TABLE t1,t2,t3; 3373create table t1 (f1 int); 3374insert into t1 values(1),(2); 3375create table t2 (f2 int, f3 int, key(f2)); 3376insert into t2 values(1,1),(2,2); 3377create table t3 (f4 int not null); 3378insert into t3 values (2),(2),(2); 3379select f1,(select count(*) from t2,t3 where f2=f1 and f3=f4) as count from t1; 3380f1 count 33811 0 33822 3 3383drop table t1,t2,t3; 3384create table t1 (f1 int unique); 3385create table t2 (f2 int unique); 3386create table t3 (f3 int unique); 3387insert into t1 values(1),(2); 3388insert into t2 values(1),(2); 3389insert into t3 values(1),(NULL); 3390select * from t3 where f3 is null; 3391f3 3392NULL 3393select t2.f2 from t1 left join t2 on f1=f2 join t3 on f1=f3 where f1=1; 3394f2 33951 3396drop table t1,t2,t3; 3397create table t1(f1 char, f2 char not null); 3398insert into t1 values(null,'a'); 3399create table t2 (f2 char not null); 3400insert into t2 values('b'); 3401select * from t1 left join t2 on f1=t2.f2 where t1.f2='a'; 3402f1 f2 f2 3403NULL a NULL 3404drop table t1,t2; 3405select * from (select * left join t on f1=f2) tt; 3406ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'on f1=f2) tt' at line 1 3407CREATE TABLE t1 (sku int PRIMARY KEY, pr int); 3408CREATE TABLE t2 (sku int PRIMARY KEY, sppr int, name varchar(255)); 3409INSERT INTO t1 VALUES 3410(10, 10), (20, 10), (30, 20), (40, 30), (50, 10), (60, 10); 3411INSERT INTO t2 VALUES 3412(10, 10, 'aaa'), (20, 10, 'bbb'), (30, 10, 'ccc'), (40, 20, 'ddd'), 3413(50, 10, 'eee'), (60, 20, 'fff'), (70, 20, 'ggg'), (80, 30, 'hhh'); 3414SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr 3415FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku); 3416sku sppr name sku pr 341720 10 bbb 10 10 341820 10 bbb 20 10 3419EXPLAIN 3420SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr 3421FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku); 3422id select_type table type possible_keys key key_len ref rows Extra 34231 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 NULL 34241 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where 3425DROP TABLE t1,t2; 3426SET SQL_MODE='NO_UNSIGNED_SUBTRACTION'; 3427CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL); 3428INSERT t1 SET i = 0; 3429UPDATE t1 SET i = -1; 3430Warnings: 3431Warning 1264 Out of range value for column 'i' at row 1 3432SELECT * FROM t1; 3433i 34340 3435UPDATE t1 SET i = CAST(i - 1 AS SIGNED); 3436Warnings: 3437Warning 1264 Out of range value for column 'i' at row 1 3438SELECT * FROM t1; 3439i 34400 3441UPDATE t1 SET i = i - 1; 3442Warnings: 3443Warning 1264 Out of range value for column 'i' at row 1 3444SELECT * FROM t1; 3445i 34460 3447DROP TABLE t1; 3448SET SQL_MODE=default; 3449create table t1 (a int); 3450insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 3451create table t2 (a int, b int, c int, e int, primary key(a,b,c)); 3452insert into t2 select A.a, B.a, C.a, C.a from t1 A, t1 B, t1 C; 3453analyze table t2; 3454Table Op Msg_type Msg_text 3455test.t2 analyze status OK 3456select 'In next EXPLAIN, B.rows must be exactly 10:' Z; 3457Z 3458In next EXPLAIN, B.rows must be exactly 10: 3459explain select * from t2 A, t2 B where A.a=5 and A.b=5 and A.C<5 3460and B.a=5 and B.b=A.e and (B.b =1 or B.b = 3 or B.b=5); 3461id select_type table type possible_keys key key_len ref rows Extra 34621 SIMPLE A range PRIMARY PRIMARY 12 NULL 4 Using where 34631 SIMPLE B ref PRIMARY PRIMARY 8 const,test.A.e 10 NULL 3464drop table t1, t2; 3465CREATE TABLE t1 (a int PRIMARY KEY, b int, INDEX(b)); 3466INSERT INTO t1 VALUES (1, 3), (9,4), (7,5), (4,5), (6,2), 3467(3,1), (5,1), (8,9), (2,2), (0,9); 3468CREATE TABLE t2 (c int, d int, f int, INDEX(c,f)); 3469INSERT INTO t2 VALUES 3470(1,0,0), (1,0,1), (2,0,0), (2,0,1), (3,0,0), (4,0,1), 3471(5,0,0), (5,0,1), (6,0,0), (0,0,1), (7,0,0), (7,0,1), 3472(0,0,0), (0,0,1), (8,0,0), (8,0,1), (9,0,0), (9,0,1); 3473EXPLAIN 3474SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6; 3475id select_type table type possible_keys key key_len ref rows Extra 34761 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using where 34771 SIMPLE t2 ref c c 5 test.t1.a 2 NULL 3478EXPLAIN 3479SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6 AND a > 0; 3480id select_type table type possible_keys key key_len ref rows Extra 34811 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using where 34821 SIMPLE t2 ref c c 5 test.t1.a 2 NULL 3483DROP TABLE t1, t2; 3484create table t1 ( 3485a int unsigned not null auto_increment primary key, 3486b bit not null, 3487c bit not null 3488); 3489create table t2 ( 3490a int unsigned not null auto_increment primary key, 3491b bit not null, 3492c int unsigned not null, 3493d varchar(50) 3494); 3495insert into t1 (b,c) values (0,1), (0,1); 3496insert into t2 (b,c) values (0,1); 3497select t1.a, t1.b + 0, t1.c + 0, t2.a, t2.b + 0, t2.c, t2.d 3498from t1 left outer join t2 on t1.a = t2.c and t2.b <> 1 3499where t1.b <> 1 order by t1.a; 3500a t1.b + 0 t1.c + 0 a t2.b + 0 c d 35011 0 1 1 0 1 NULL 35022 0 1 NULL NULL NULL NULL 3503drop table t1,t2; 3504SELECT 0.9888889889 * 1.011111411911; 35050.9888889889 * 1.011111411911 35060.9998769417899202067879 3507prepare stmt from 'select 1 as " a "'; 3508Warnings: 3509Warning 1466 Leading spaces are removed from name ' a ' 3510execute stmt; 3511a 35121 3513CREATE TABLE t1 (a int NOT NULL PRIMARY KEY, b int NOT NULL); 3514INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4); 3515CREATE TABLE t2 (c int NOT NULL, INDEX idx(c)); 3516INSERT INTO t2 VALUES 3517(1), (1), (1), (1), (1), (1), (1), (1), 3518(2), (2), (2), (2), 3519(3), (3), 3520(4); 3521EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=1; 3522id select_type table type possible_keys key key_len ref rows Extra 35231 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 NULL 35241 SIMPLE t2 ref idx idx 4 const 7 Using index 3525EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=4; 3526id select_type table type possible_keys key key_len ref rows Extra 35271 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 NULL 35281 SIMPLE t2 ref idx idx 4 const 1 Using index 3529DROP TABLE t1, t2; 3530CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, a int); 3531INSERT INTO t1 VALUES (1,2), (2,NULL), (3,2); 3532CREATE TABLE t2 (b int, c INT, INDEX idx1(b)); 3533INSERT INTO t2 VALUES (2,1), (3,2); 3534CREATE TABLE t3 (d int, e int, INDEX idx1(d)); 3535INSERT INTO t3 VALUES (2,10), (2,20), (1,30), (2,40), (2,50); 3536EXPLAIN 3537SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id 3538WHERE t1.id=2; 3539id select_type table type possible_keys key key_len ref rows Extra 35401 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 NULL 35411 SIMPLE t2 const idx1 NULL NULL NULL 1 NULL 35421 SIMPLE t3 ref idx1 idx1 5 const 3 NULL 3543SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id 3544WHERE t1.id=2; 3545id a b c d e 35462 NULL NULL NULL 2 10 35472 NULL NULL NULL 2 20 35482 NULL NULL NULL 2 40 35492 NULL NULL NULL 2 50 3550DROP TABLE t1,t2,t3; 3551create table t1 (c1 varchar(1), c2 int, c3 int, c4 int, c5 int, c6 int, 3552c7 int, c8 int, c9 int, fulltext key (`c1`)); 3553select distinct match (`c1`) against ('z') , c2, c3, c4,c5, c6,c7, c8 3554from t1 where c9=1 order by c2, c2; 3555match (`c1`) against ('z') c2 c3 c4 c5 c6 c7 c8 3556drop table t1; 3557CREATE TABLE t1 (pk varchar(10) PRIMARY KEY, fk varchar(16)); 3558CREATE TABLE t2 (pk varchar(16) PRIMARY KEY, fk varchar(10)); 3559INSERT INTO t1 VALUES 3560('d','dddd'), ('i','iii'), ('a','aa'), ('b','bb'), ('g','gg'), 3561('e','eee'), ('c','cccc'), ('h','hhh'), ('j','jjj'), ('f','fff'); 3562INSERT INTO t2 VALUES 3563('jjj', 'j'), ('cc','c'), ('ccc','c'), ('aaa', 'a'), ('jjjj','j'), 3564('hhh','h'), ('gg','g'), ('fff','f'), ('ee','e'), ('ffff','f'), 3565('bbb','b'), ('ff','f'), ('cccc','c'), ('dddd','d'), ('jj','j'), 3566('aaaa','a'), ('bb','b'), ('eeee','e'), ('aa','a'), ('hh','h'); 3567EXPLAIN SELECT t2.* 3568FROM t1 JOIN t2 ON t2.fk=t1.pk 3569WHERE t2.fk < 'c' AND t2.pk=t1.fk; 3570id select_type table type possible_keys key key_len ref rows Extra 35711 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 3 Using where 35721 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where 3573EXPLAIN SELECT t2.* 3574FROM t1 JOIN t2 ON t2.fk=t1.pk 3575WHERE t2.fk BETWEEN 'a' AND 'b' AND t2.pk=t1.fk; 3576id select_type table type possible_keys key key_len ref rows Extra 35771 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using where 35781 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where 3579EXPLAIN SELECT t2.* 3580FROM t1 JOIN t2 ON t2.fk=t1.pk 3581WHERE t2.fk IN ('a','b') AND t2.pk=t1.fk; 3582id select_type table type possible_keys key key_len ref rows Extra 35831 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using where 35841 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where 3585DROP TABLE t1,t2; 3586CREATE TABLE t1 (a int, b varchar(20) NOT NULL, PRIMARY KEY(a)); 3587CREATE TABLE t2 (a int, b varchar(20) NOT NULL, 3588PRIMARY KEY (a), UNIQUE KEY (b)); 3589INSERT INTO t1 VALUES (1,'a'),(2,'b'),(3,'c'); 3590INSERT INTO t2 VALUES (1,'a'),(2,'b'),(3,'c'); 3591EXPLAIN SELECT t1.a FROM t1 LEFT JOIN t2 ON t2.b=t1.b WHERE t1.a=3; 3592id select_type table type possible_keys key key_len ref rows Extra 35931 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 NULL 35941 SIMPLE t2 const b b 22 const 1 Using index 3595DROP TABLE t1,t2; 3596CREATE TABLE t1(id int PRIMARY KEY, b int, e int); 3597CREATE TABLE t2(i int, a int, INDEX si(i), INDEX ai(a)); 3598CREATE TABLE t3(a int PRIMARY KEY, c char(4), INDEX ci(c)); 3599INSERT INTO t1 VALUES 3600(1,10,19), (2,20,22), (4,41,42), (9,93,95), (7, 77,79), 3601(6,63,67), (5,55,58), (3,38,39), (8,81,89); 3602INSERT INTO t2 VALUES 3603(21,210), (41,410), (82,820), (83,830), (84,840), 3604(65,650), (51,510), (37,370), (94,940), (76,760), 3605(22,220), (33,330), (40,400), (95,950), (38,380), 3606(67,670), (88,880), (57,570), (96,960), (97,970); 3607INSERT INTO t3 VALUES 3608(210,'bb'), (950,'ii'), (400,'ab'), (500,'ee'), (220,'gg'), 3609(440,'gg'), (310,'eg'), (380,'ee'), (840,'bb'), (830,'ff'), 3610(230,'aa'), (960,'ii'), (410,'aa'), (510,'ee'), (290,'bb'), 3611(450,'gg'), (320,'dd'), (390,'hh'), (850,'jj'), (860,'ff'); 3612EXPLAIN 3613SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3 3614WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND 3615t3.a=t2.a AND t3.c IN ('bb','ee'); 3616id select_type table type possible_keys key key_len ref rows Extra 36171 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 NULL 36181 SIMPLE t2 range si si 5 NULL 4 Using where 36191 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where 3620EXPLAIN 3621SELECT t3.a FROM t1,t2,t3 3622WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND 3623t3.a=t2.a AND t3.c IN ('bb','ee') ; 3624id select_type table type possible_keys key key_len ref rows Extra 36251 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 NULL 36261 SIMPLE t2 range si,ai si 5 NULL 4 Using where 36271 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where 3628EXPLAIN 3629SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3 3630WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND 3631t3.c IN ('bb','ee'); 3632id select_type table type possible_keys key key_len ref rows Extra 36331 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 NULL 36341 SIMPLE t2 range si si 5 NULL 2 Using where 36351 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where 3636EXPLAIN 3637SELECT t3.a FROM t1,t2,t3 3638WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND 3639t3.c IN ('bb','ee'); 3640id select_type table type possible_keys key key_len ref rows Extra 36411 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 NULL 36421 SIMPLE t2 range si,ai si 5 NULL 2 Using where 36431 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where 3644DROP TABLE t1,t2,t3; 3645CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int); 3646CREATE TABLE t2 ( f11 int PRIMARY KEY ); 3647INSERT INTO t1 VALUES (1,1,1,0,0,0,0),(2,1,1,3,8,1,0),(3,1,1,4,12,1,0); 3648INSERT INTO t2 VALUES (62); 3649SELECT * FROM t1 LEFT JOIN t2 ON f11 = t1.checked_out GROUP BY f1 ORDER BY f2, f3, f4, f5 LIMIT 0, 1; 3650f1 f2 f3 f4 f5 f6 checked_out f11 36511 1 1 0 0 0 0 NULL 3652DROP TABLE t1, t2; 3653DROP TABLE IF EXISTS t1; 3654CREATE TABLE t1(a int); 3655INSERT into t1 values (1), (2), (3); 3656SELECT * FROM t1 LIMIT 2, -1; 3657ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1' at line 1 3658DROP TABLE t1; 3659CREATE TABLE t1 ( 3660ID_with_null int NULL, 3661ID_better int NOT NULL, 3662INDEX idx1 (ID_with_null), 3663INDEX idx2 (ID_better) 3664); 3665INSERT INTO t1 VALUES (1,1), (2,1), (null,3), (null,3), (null,3), (null,3); 3666INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; 3667INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; 3668INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; 3669INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; 3670INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; 3671SELECT COUNT(*) FROM t1 WHERE ID_with_null IS NULL; 3672COUNT(*) 3673128 3674SELECT COUNT(*) FROM t1 WHERE ID_better=1; 3675COUNT(*) 36762 3677EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL; 3678id select_type table type possible_keys key key_len ref rows Extra 36791 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where 3680DROP INDEX idx1 ON t1; 3681CREATE UNIQUE INDEX idx1 ON t1(ID_with_null); 3682EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL; 3683id select_type table type possible_keys key key_len ref rows Extra 36841 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where 3685DROP TABLE t1; 3686CREATE TABLE t1 ( 3687ID1_with_null int NULL, 3688ID2_with_null int NULL, 3689ID_better int NOT NULL, 3690INDEX idx1 (ID1_with_null, ID2_with_null), 3691INDEX idx2 (ID_better) 3692); 3693INSERT INTO t1 VALUES (1,1,1), (2,2,1), (3,null,3), (null,3,3), (null,null,3), 3694(3,null,3), (null,3,3), (null,null,3), (3,null,3), (null,3,3), (null,null,3); 3695INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; 3696INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; 3697INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; 3698INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; 3699INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; 3700INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; 3701SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null=3; 3702COUNT(*) 370324 3704SELECT COUNT(*) FROM t1 WHERE ID1_with_null=3 AND ID2_with_null IS NULL; 3705COUNT(*) 370624 3707SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null IS NULL; 3708COUNT(*) 3709192 3710SELECT COUNT(*) FROM t1 WHERE ID_better=1; 3711COUNT(*) 37122 3713EXPLAIN SELECT * FROM t1 3714WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ; 3715id select_type table type possible_keys key key_len ref rows Extra 37161 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where 3717EXPLAIN SELECT * FROM t1 3718WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null=3 IS NULL ; 3719id select_type table type possible_keys key key_len ref rows Extra 37201 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where 3721EXPLAIN SELECT * FROM t1 3722WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL; 3723id select_type table type possible_keys key key_len ref rows Extra 37241 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where 3725DROP INDEX idx1 ON t1; 3726CREATE UNIQUE INDEX idx1 ON t1(ID1_with_null,ID2_with_null); 3727EXPLAIN SELECT * FROM t1 3728WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ; 3729id select_type table type possible_keys key key_len ref rows Extra 37301 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where 3731EXPLAIN SELECT * FROM t1 3732WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null IS NULL ; 3733id select_type table type possible_keys key key_len ref rows Extra 37341 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where 3735EXPLAIN SELECT * FROM t1 3736WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL; 3737id select_type table type possible_keys key key_len ref rows Extra 37381 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where 3739EXPLAIN SELECT * FROM t1 3740WHERE ID_better=1 AND ID1_with_null IS NULL AND 3741(ID2_with_null=1 OR ID2_with_null=2); 3742id select_type table type possible_keys key key_len ref rows Extra 37431 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where 3744DROP TABLE t1; 3745CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts)); 3746INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00"); 3747ANALYZE TABLE t1; 3748Table Op Msg_type Msg_text 3749test.t1 analyze status OK 3750CREATE TABLE t2 (a INT, dt1 DATETIME, dt2 DATETIME, PRIMARY KEY (a)); 3751INSERT INTO t2 VALUES (30, "2006-01-01 00:00:00", "2999-12-31 00:00:00"); 3752INSERT INTO t2 SELECT a+1,dt1,dt2 FROM t2; 3753ANALYZE TABLE t2; 3754Table Op Msg_type Msg_text 3755test.t2 analyze status OK 3756EXPLAIN 3757SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30 3758AND t1.ts BETWEEN t2.dt1 AND t2.dt2 3759AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31"; 3760id select_type table type possible_keys key key_len ref rows Extra 37611 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 NULL 37621 SIMPLE t1 range ts ts 4 NULL 1 Using where 3763Warnings: 3764Warning 1292 Incorrect datetime value: '2999-12-31 00:00:00' for column 'ts' at row 1 3765SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30 3766AND t1.ts BETWEEN t2.dt1 AND t2.dt2 3767AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31"; 3768a ts a dt1 dt2 376930 2006-01-03 23:00:00 30 2006-01-01 00:00:00 2999-12-31 00:00:00 3770Warnings: 3771Warning 1292 Incorrect datetime value: '2999-12-31 00:00:00' for column 'ts' at row 1 3772DROP TABLE t1,t2; 3773create table t1 (a bigint unsigned); 3774insert into t1 values 3775(if(1, 9223372036854775808, 1)), 3776(case when 1 then 9223372036854775808 else 1 end), 3777(coalesce(9223372036854775808, 1)); 3778select * from t1; 3779a 37809223372036854775808 37819223372036854775808 37829223372036854775808 3783drop table t1; 3784create table t1 select 3785if(1, 9223372036854775808, 1) i, 3786case when 1 then 9223372036854775808 else 1 end c, 3787coalesce(9223372036854775808, 1) co; 3788show create table t1; 3789Table Create Table 3790t1 CREATE TABLE `t1` ( 3791 `i` decimal(19,0) NOT NULL DEFAULT '0', 3792 `c` decimal(19,0) NOT NULL DEFAULT '0', 3793 `co` decimal(19,0) NOT NULL DEFAULT '0' 3794) ENGINE=MyISAM DEFAULT CHARSET=latin1 3795drop table t1; 3796select 3797if(1, cast(1111111111111111111 as unsigned), 1) i, 3798case when 1 then cast(1111111111111111111 as unsigned) else 1 end c, 3799coalesce(cast(1111111111111111111 as unsigned), 1) co; 3800i c co 38011111111111111111111 1111111111111111111 1111111111111111111 3802CREATE TABLE t1 (name varchar(255)); 3803CREATE TABLE t2 (name varchar(255), n int, KEY (name(3))); 3804INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa '), ('aa'); 3805INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc ',3); 3806INSERT INTO t2 VALUES (concat('cc ', 0x06), 4); 3807INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7); 3808SELECT * FROM t2; 3809name n 3810bb 1 3811aa 2 3812cc 3 3813cc 4 3814cc 5 3815bb 6 3816cc 7 3817SELECT * FROM t2 ORDER BY name; 3818name n 3819aa 2 3820bb 1 3821bb 6 3822cc 4 3823cc 3 3824cc 5 3825cc 7 3826SELECT name, LENGTH(name), n FROM t2 ORDER BY name; 3827name LENGTH(name) n 3828aa 2 2 3829bb 2 1 3830bb 3 6 3831cc 4 4 3832cc 5 3 3833cc 2 5 3834cc 3 7 3835EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; 3836id select_type table type possible_keys key key_len ref rows Extra 38371 SIMPLE t2 ref name name 6 const 3 Using where 3838SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; 3839name LENGTH(name) n 3840cc 5 3 3841cc 2 5 3842cc 3 7 3843EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; 3844id select_type table type possible_keys key key_len ref rows Extra 38451 SIMPLE t2 range name name 6 NULL 3 Using where 3846SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; 3847name LENGTH(name) n 3848cc 5 3 3849cc 4 4 3850cc 2 5 3851cc 3 7 3852EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; 3853id select_type table type possible_keys key key_len ref rows Extra 38541 SIMPLE t2 range name name 6 NULL 3 Using where; Using filesort 3855SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; 3856name LENGTH(name) n 3857cc 4 4 3858cc 5 3 3859cc 2 5 3860cc 3 7 3861EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; 3862id select_type table type possible_keys key key_len ref rows Extra 38631 SIMPLE t1 ALL NULL NULL NULL NULL 5 NULL 38641 SIMPLE t2 ALL name NULL NULL NULL 7 Using where; Using join buffer (Block Nested Loop) 3865SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; 3866name name n 3867bb bb 1 3868aa aa 2 3869aa aa 2 3870cc cc 3 3871cc cc 5 3872bb bb 6 3873cc cc 7 3874ccc NULL NULL 3875DROP TABLE t1,t2; 3876CREATE TABLE t1 (name text); 3877CREATE TABLE t2 (name text, n int, KEY (name(3))); 3878INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa '), ('aa'); 3879INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc ',3); 3880INSERT INTO t2 VALUES (concat('cc ', 0x06), 4); 3881INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7); 3882SELECT * FROM t2; 3883name n 3884bb 1 3885aa 2 3886cc 3 3887cc 4 3888cc 5 3889bb 6 3890cc 7 3891SELECT * FROM t2 ORDER BY name; 3892name n 3893aa 2 3894bb 1 3895bb 6 3896cc 4 3897cc 3 3898cc 5 3899cc 7 3900SELECT name, LENGTH(name), n FROM t2 ORDER BY name; 3901name LENGTH(name) n 3902aa 2 2 3903bb 2 1 3904bb 3 6 3905cc 4 4 3906cc 5 3 3907cc 2 5 3908cc 3 7 3909EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; 3910id select_type table type possible_keys key key_len ref rows Extra 39111 SIMPLE t2 ref name name 6 const 3 Using where 3912SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; 3913name LENGTH(name) n 3914cc 5 3 3915cc 2 5 3916cc 3 7 3917EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; 3918id select_type table type possible_keys key key_len ref rows Extra 39191 SIMPLE t2 range name name 6 NULL 3 Using where 3920SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; 3921name LENGTH(name) n 3922cc 5 3 3923cc 4 4 3924cc 2 5 3925cc 3 7 3926EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; 3927id select_type table type possible_keys key key_len ref rows Extra 39281 SIMPLE t2 range name name 6 NULL 3 Using where; Using filesort 3929SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; 3930name LENGTH(name) n 3931cc 4 4 3932cc 5 3 3933cc 2 5 3934cc 3 7 3935EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; 3936id select_type table type possible_keys key key_len ref rows Extra 39371 SIMPLE t1 ALL NULL NULL NULL NULL 5 NULL 39381 SIMPLE t2 ALL name NULL NULL NULL 7 Using where; Using join buffer (Block Nested Loop) 3939SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; 3940name name n 3941bb bb 1 3942aa aa 2 3943aa aa 2 3944cc cc 3 3945cc cc 5 3946bb bb 6 3947cc cc 7 3948ccc NULL NULL 3949DROP TABLE t1,t2; 3950CREATE TABLE t1 ( 3951access_id int NOT NULL default '0', 3952name varchar(20) default NULL, 3953rank int NOT NULL default '0', 3954KEY idx (access_id) 3955); 3956CREATE TABLE t2 ( 3957faq_group_id int NOT NULL default '0', 3958faq_id int NOT NULL default '0', 3959access_id int default NULL, 3960UNIQUE KEY idx1 (faq_id), 3961KEY idx2 (faq_group_id,faq_id) 3962); 3963INSERT INTO t1 VALUES 3964(1,'Everyone',2),(2,'Help',3),(3,'Technical Support',1),(4,'Chat User',4); 3965INSERT INTO t2 VALUES 3966(261,265,1),(490,494,1); 3967SELECT t2.faq_id 3968FROM t1 INNER JOIN t2 IGNORE INDEX (idx1) 3969ON (t1.access_id = t2.access_id) 3970LEFT JOIN t2 t 3971ON (t.faq_group_id = t2.faq_group_id AND 3972find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4')) 3973WHERE 3974t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265); 3975faq_id 3976265 3977SELECT t2.faq_id 3978FROM t1 INNER JOIN t2 3979ON (t1.access_id = t2.access_id) 3980LEFT JOIN t2 t 3981ON (t.faq_group_id = t2.faq_group_id AND 3982find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4')) 3983WHERE 3984t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265); 3985faq_id 3986265 3987DROP TABLE t1,t2; 3988CREATE TABLE t1 (a INT, b INT, KEY inx (b,a)); 3989INSERT INTO t1 VALUES (1,1), (1,2), (1,3), (1,4), (1,5), (1, 6), (1,7); 3990EXPLAIN SELECT COUNT(*) FROM t1 f1 INNER JOIN t1 f2 3991ON ( f1.b=f2.b AND f1.a<f2.a ) 3992WHERE 1 AND f1.b NOT IN (100,2232,3343,51111); 3993id select_type table type possible_keys key key_len ref rows Extra 39941 SIMPLE f1 index inx inx 10 NULL 7 Using where; Using index 39951 SIMPLE f2 ref inx inx 5 test.f1.b 1 Using where; Using index 3996DROP TABLE t1; 3997CREATE TABLE t1 (c1 INT, c2 INT); 3998INSERT INTO t1 VALUES (1,11), (2,22), (2,22); 3999EXPLAIN SELECT c1 FROM t1 WHERE (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT COUNT(c2)))))))))))))))))))))))))))))) > 0; 4000id select_type table type possible_keys key key_len ref rows Extra 40011 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where 40022 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 40033 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 40044 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 40055 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 40066 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 40077 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 40088 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 40099 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 401010 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 401111 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 401212 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 401313 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 401414 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 401515 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 401616 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 401717 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 401818 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 401919 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 402020 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 402121 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 402222 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 402323 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 402424 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 402525 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 402626 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 402727 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 402828 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 402929 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 403030 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 4031EXPLAIN SELECT c1 FROM t1 WHERE (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT (SELECT COUNT(c2))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))) > 0; 4032ERROR HY000: Too high level of nesting for select 4033DROP TABLE t1; 4034CREATE TABLE t1 ( 4035c1 int(11) NOT NULL AUTO_INCREMENT, 4036c2 varchar(1000) DEFAULT NULL, 4037c3 bigint(20) DEFAULT NULL, 4038c4 bigint(20) DEFAULT NULL, 4039PRIMARY KEY (c1) 4040); 4041EXPLAIN EXTENDED 4042SELECT join_2.c1 4043FROM 4044t1 AS join_0, 4045t1 AS join_1, 4046t1 AS join_2, 4047t1 AS join_3, 4048t1 AS join_4, 4049t1 AS join_5, 4050t1 AS join_6, 4051t1 AS join_7 4052WHERE 4053join_0.c1=join_1.c1 AND 4054join_1.c1=join_2.c1 AND 4055join_2.c1=join_3.c1 AND 4056join_3.c1=join_4.c1 AND 4057join_4.c1=join_5.c1 AND 4058join_5.c1=join_6.c1 AND 4059join_6.c1=join_7.c1 4060OR 4061join_0.c2 < '?' AND 4062join_1.c2 < '?' AND 4063join_2.c2 > '?' AND 4064join_2.c2 < '!' AND 4065join_3.c2 > '?' AND 4066join_4.c2 = '?' AND 4067join_5.c2 <> '?' AND 4068join_6.c2 <> '?' AND 4069join_7.c2 >= '?' AND 4070join_0.c1=join_1.c1 AND 4071join_1.c1=join_2.c1 AND 4072join_2.c1=join_3.c1 AND 4073join_3.c1=join_4.c1 AND 4074join_4.c1=join_5.c1 AND 4075join_5.c1=join_6.c1 AND 4076join_6.c1=join_7.c1 4077GROUP BY 4078join_3.c1, 4079join_2.c1, 4080join_7.c1, 4081join_1.c1, 4082join_0.c1; 4083id select_type table type possible_keys key key_len ref rows filtered Extra 40841 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 4085Warnings: 4086Note 1003 /* select#1 */ select NULL AS `c1` from `test`.`t1` `join_0` join `test`.`t1` `join_1` join `test`.`t1` `join_2` join `test`.`t1` `join_3` join `test`.`t1` `join_4` join `test`.`t1` `join_5` join `test`.`t1` `join_6` join `test`.`t1` `join_7` where 0 group by NULL,NULL,NULL,NULL,NULL 4087SHOW WARNINGS; 4088Level Code Message 4089Note 1003 /* select#1 */ select NULL AS `c1` from `test`.`t1` `join_0` join `test`.`t1` `join_1` join `test`.`t1` `join_2` join `test`.`t1` `join_3` join `test`.`t1` `join_4` join `test`.`t1` `join_5` join `test`.`t1` `join_6` join `test`.`t1` `join_7` where 0 group by NULL,NULL,NULL,NULL,NULL 4090DROP TABLE t1; 4091SELECT 1 AS ` `; 4092 40931 4094Warnings: 4095Warning 1474 Name ' ' has become '' 4096SELECT 1 AS ` `; 4097 40981 4099Warnings: 4100Warning 1474 Name ' ' has become '' 4101SELECT 1 AS ` x`; 4102x 41031 4104Warnings: 4105Warning 1466 Leading spaces are removed from name ' x' 4106CREATE VIEW v1 AS SELECT 1 AS ``; 4107ERROR 42000: Incorrect column name '' 4108CREATE VIEW v1 AS SELECT 1 AS ` `; 4109ERROR 42000: Incorrect column name ' ' 4110CREATE VIEW v1 AS SELECT 1 AS ` `; 4111ERROR 42000: Incorrect column name ' ' 4112CREATE VIEW v1 AS SELECT (SELECT 1 AS ` `); 4113ERROR 42000: Incorrect column name ' ' 4114CREATE VIEW v1 AS SELECT 1 AS ` x`; 4115Warnings: 4116Warning 1466 Leading spaces are removed from name ' x' 4117SELECT `x` FROM v1; 4118x 41191 4120ALTER VIEW v1 AS SELECT 1 AS ` `; 4121ERROR 42000: Incorrect column name ' ' 4122DROP VIEW v1; 4123select str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT' 4124 and '2007/10/20 00:00:00 GMT'; 4125str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT' 4126 and '2007/10/20 00:00:00 GMT' 41271 4128Warnings: 4129Warning 1292 Truncated incorrect datetime value: '2007/10/01 00:00:00 GMT' 4130Warning 1292 Truncated incorrect datetime value: '2007/10/20 00:00:00 GMT' 4131select str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6'; 4132str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6' 41331 4134Warnings: 4135Warning 1292 Truncated incorrect date value: '2007/10/01 00:00:00 GMT-6' 4136select str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6'; 4137str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6' 41381 4139Warnings: 4140Warning 1292 Truncated incorrect date value: '2007/10/2000:00:00 GMT-6' 4141select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6'; 4142str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6' 41431 4144Warnings: 4145Warning 1292 Truncated incorrect date value: '2007-10-1 00:00:00 GMT-6' 4146select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6'; 4147str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6' 41481 4149Warnings: 4150Warning 1292 Truncated incorrect date value: '2007-10-01 x00:00:00 GMT-6' 4151select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6'; 4152str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6' 41531 4154Warnings: 4155Warning 1292 Truncated incorrect datetime value: '2007-10-01 00:00:00 GMT-6' 4156select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6'; 4157str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6' 41581 4159Warnings: 4160Warning 1292 Truncated incorrect datetime value: '2007-10-01 00:x00:00 GMT-6' 4161select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6'; 4162str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6' 41631 4164Warnings: 4165Warning 1292 Truncated incorrect datetime value: '2007-10-01 x12:34:56 GMT-6' 4166select str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6'; 4167str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6' 41681 4169Warnings: 4170Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34x:56 GMT-6' 4171select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6'; 4172str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6' 41730 4174Warnings: 4175Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34x:56 GMT-6' 4176select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34:56'; 4177str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34:56' 41781 4179select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 12:00:00'; 4180str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 12:00:00' 41810 4182select str_to_date('2007-10-01 12','%Y-%m-%d %H') = '2007-10-01 12:00:00'; 4183str_to_date('2007-10-01 12','%Y-%m-%d %H') = '2007-10-01 12:00:00' 41841 4185select str_to_date('2007-10-01 12:34','%Y-%m-%d %H') = '2007-10-01 12:00:00'; 4186str_to_date('2007-10-01 12:34','%Y-%m-%d %H') = '2007-10-01 12:00:00' 41871 4188Warnings: 4189Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34' 4190select str_to_date('2007-02-30 12:34','%Y-%m-%d %H:%i') = '2007-02-30 12:34'; 4191str_to_date('2007-02-30 12:34','%Y-%m-%d %H:%i') = '2007-02-30 12:34' 41921 4193select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'; 4194str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34' 41951 4196select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00' 4197 and '2007/10/20 00:00:00'; 4198str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00' 4199 and '2007/10/20 00:00:00' 42001 4201set SQL_MODE=TRADITIONAL; 4202select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'; 4203str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34' 4204NULL 4205Warnings: 4206Warning 1292 Truncated incorrect datetime value: '2007-10-00 12:34' 4207Warning 1411 Incorrect datetime value: '2007-10-00 12:34' for function str_to_date 4208select str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'; 4209str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34' 42100 4211Warnings: 4212Warning 1292 Truncated incorrect datetime value: '2007-10-00 12:34' 4213select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34'; 4214str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34' 4215NULL 4216Warnings: 4217Warning 1411 Incorrect datetime value: '2007-10-00 12:34' for function str_to_date 4218select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01' 4219 and '2007/10/20'; 4220str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01' 4221 and '2007/10/20' 4222NULL 4223Warnings: 4224Warning 1411 Incorrect datetime value: '2007-10-00' for function str_to_date 4225set SQL_MODE=DEFAULT; 4226select str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20'; 4227str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20' 42281 4229Warnings: 4230Warning 1292 Truncated incorrect datetime value: '' 4231select str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20'; 4232str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20' 42330 4234select str_to_date('','%Y-%m-%d %H:%i') = '2007-10-01 12:34'; 4235str_to_date('','%Y-%m-%d %H:%i') = '2007-10-01 12:34' 42360 4237select str_to_date(NULL,'%Y-%m-%d %H:%i') = '2007-10-01 12:34'; 4238str_to_date(NULL,'%Y-%m-%d %H:%i') = '2007-10-01 12:34' 4239NULL 4240select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = ''; 4241str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '' 42420 4243Warnings: 4244Warning 1292 Truncated incorrect datetime value: '' 4245select str_to_date('1','%Y-%m-%d') = '1'; 4246str_to_date('1','%Y-%m-%d') = '1' 42470 4248Warnings: 4249Warning 1292 Truncated incorrect date value: '1' 4250select str_to_date('1','%Y-%m-%d') = '1'; 4251str_to_date('1','%Y-%m-%d') = '1' 42520 4253Warnings: 4254Warning 1292 Truncated incorrect date value: '1' 4255select str_to_date('','%Y-%m-%d') = ''; 4256str_to_date('','%Y-%m-%d') = '' 42570 4258Warnings: 4259Warning 1292 Truncated incorrect date value: '' 4260select str_to_date('1000-01-01','%Y-%m-%d') between '0000-00-00' and NULL; 4261str_to_date('1000-01-01','%Y-%m-%d') between '0000-00-00' and NULL 42620 4263select str_to_date('1000-01-01','%Y-%m-%d') between NULL and '2000-00-00'; 4264str_to_date('1000-01-01','%Y-%m-%d') between NULL and '2000-00-00' 42650 4266select str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL; 4267str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL 42680 4269CREATE TABLE t1 (c11 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY); 4270CREATE TABLE t2 (c21 INT UNSIGNED NOT NULL, 4271c22 INT DEFAULT NULL, 4272KEY(c21, c22)); 4273CREATE TABLE t3 (c31 INT UNSIGNED NOT NULL DEFAULT 0, 4274c32 INT DEFAULT NULL, 4275c33 INT NOT NULL, 4276c34 INT UNSIGNED DEFAULT 0, 4277KEY (c33, c34, c32)); 4278INSERT INTO t1 values (),(),(),(),(); 4279INSERT INTO t2 SELECT a.c11, b.c11 FROM t1 a, t1 b; 4280INSERT INTO t3 VALUES (1, 1, 1, 0), 4281(2, 2, 0, 0), 4282(3, 3, 1, 0), 4283(4, 4, 0, 0), 4284(5, 5, 1, 0); 4285SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND 4286t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND 4287t3.c33 = 1 AND t2.c22 in (1, 3) 4288ORDER BY c32; 4289c32 42901 42911 42923 42933 42945 42955 4296SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND 4297t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND 4298t3.c33 = 1 AND t2.c22 in (1, 3) 4299ORDER BY c32 DESC; 4300c32 43015 43025 43033 43043 43051 43061 4307DROP TABLE t1, t2, t3; 4308 4309# 4310# Bug#30736: Row Size Too Large Error Creating a Table and 4311# Inserting Data. 4312# 4313DROP TABLE IF EXISTS t1; 4314DROP TABLE IF EXISTS t2; 4315 4316CREATE TABLE t1( 4317c1 DECIMAL(10, 2), 4318c2 FLOAT); 4319 4320INSERT INTO t1 VALUES (0, 1), (2, 3), (4, 5); 4321 4322CREATE TABLE t2( 4323c3 DECIMAL(10, 2)) 4324SELECT 4325c1 * c2 AS c3 4326FROM t1; 4327 4328SELECT * FROM t1; 4329c1 c2 43300.00 1 43312.00 3 43324.00 5 4333 4334SELECT * FROM t2; 4335c3 43360.00 43376.00 433820.00 4339 4340DROP TABLE t1; 4341DROP TABLE t2; 4342 4343CREATE TABLE t1 (c1 BIGINT NOT NULL); 4344INSERT INTO t1 (c1) VALUES (1); 4345SELECT * FROM t1 WHERE c1 > NULL + 1; 4346c1 4347DROP TABLE t1; 4348 4349CREATE TABLE t1 (a VARCHAR(10) NOT NULL PRIMARY KEY); 4350INSERT INTO t1 (a) VALUES ('foo0'), ('bar0'), ('baz0'); 4351SELECT * FROM t1 WHERE a IN (CONCAT('foo', 0), 'bar'); 4352a 4353foo0 4354DROP TABLE t1; 4355CREATE TABLE t1 (a INT, b INT); 4356CREATE TABLE t2 (a INT, c INT, KEY(a)); 4357INSERT INTO t1 VALUES (1, 1), (2, 2); 4358INSERT INTO t2 VALUES (1, 1), (1, 2), (1, 3), (1, 4), (1, 5), 4359(2, 1), (2, 2), (2, 3), (2, 4), (2, 5), 4360(3, 1), (3, 2), (3, 3), (3, 4), (3, 5), 4361(4, 1), (4, 2), (4, 3), (4, 4), (4, 5); 4362FLUSH STATUS; 4363SELECT DISTINCT b FROM t1 LEFT JOIN t2 USING(a) WHERE c <= 3; 4364b 43651 43662 4367SHOW STATUS LIKE 'Handler_read%'; 4368Variable_name Value 4369Handler_read_first 0 4370Handler_read_key 2 4371Handler_read_last 0 4372Handler_read_next 0 4373Handler_read_prev 0 4374Handler_read_rnd 0 4375Handler_read_rnd_next 6 4376DROP TABLE t1, t2; 4377CREATE TABLE t1 (f1 bigint(20) NOT NULL default '0', 4378f2 int(11) NOT NULL default '0', 4379f3 bigint(20) NOT NULL default '0', 4380f4 varchar(255) NOT NULL default '', 4381PRIMARY KEY (f1), 4382KEY key1 (f4), 4383KEY key2 (f2)); 4384CREATE TABLE t2 (f1 int(11) NOT NULL default '0', 4385f2 enum('A1','A2','A3') NOT NULL default 'A1', 4386f3 int(11) NOT NULL default '0', 4387PRIMARY KEY (f1), 4388KEY key1 (f3)); 4389CREATE TABLE t3 (f1 bigint(20) NOT NULL default '0', 4390f2 datetime NOT NULL default '1980-01-01 00:00:00', 4391PRIMARY KEY (f1)); 4392insert into t1 values (1, 1, 1, 'abc'); 4393insert into t1 values (2, 1, 2, 'def'); 4394insert into t1 values (3, 1, 2, 'def'); 4395insert into t2 values (1, 'A1', 1); 4396insert into t3 values (1, '1980-01-01'); 4397SELECT a.f3, cr.f4, count(*) count 4398FROM t2 a 4399STRAIGHT_JOIN t1 cr ON cr.f2 = a.f1 4400LEFT JOIN 4401(t1 cr2 4402JOIN t3 ae2 ON cr2.f3 = ae2.f1 4403) ON a.f1 = cr2.f2 AND ae2.f2 < now() - INTERVAL 7 DAY AND 4404cr.f4 = cr2.f4 4405GROUP BY a.f3, cr.f4; 4406f3 f4 count 44071 abc 1 44081 def 2 4409drop table t1, t2, t3; 4410CREATE TABLE t1 (a INT KEY, b INT); 4411INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4); 4412EXPLAIN EXTENDED SELECT a, b FROM t1 WHERE a > 1 AND a = b LIMIT 2; 4413id select_type table type possible_keys key key_len ref rows filtered Extra 44141 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using where 4415Warnings: 4416Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`b` = `test`.`t1`.`a`) and (`test`.`t1`.`a` > 1)) limit 2 4417EXPLAIN EXTENDED SELECT a, b FROM t1 WHERE a > 1 AND b = a LIMIT 2; 4418id select_type table type possible_keys key key_len ref rows filtered Extra 44191 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using where 4420Warnings: 4421Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` > 1)) limit 2 4422DROP TABLE t1; 4423# 4424# Bug#47019: Assertion failed: 0, file .\rt_mbr.c, line 138 when 4425# forcing a spatial index 4426# 4427CREATE TABLE t1(a LINESTRING NOT NULL, SPATIAL KEY(a)); 4428INSERT INTO t1 VALUES 4429(GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1)')), 4430(GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1)')); 4431EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2; 4432id select_type table type possible_keys key key_len ref rows Extra 44331 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL 44341 SIMPLE t2 ALL a NULL NULL NULL 2 Range checked for each record (index map: 0x1) 4435SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2; 44361 44371 44381 44391 44401 4441EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a); 4442id select_type table type possible_keys key key_len ref rows Extra 44431 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL 44441 SIMPLE t2 ALL a NULL NULL NULL 2 Range checked for each record (index map: 0x1) 4445SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a); 44461 44471 44481 44491 44501 4451DROP TABLE t1; 4452# 4453# Bug #48291 : crash with row() operator,select into @var, and 4454# subquery returning multiple rows 4455# 4456CREATE TABLE t1(a INT); 4457INSERT INTO t1 VALUES (2),(3); 4458# Should not crash 4459SELECT 1 FROM t1 WHERE a <> 1 AND NOT 4460ROW(1,a) <=> ROW(1,(SELECT 1 FROM t1)) 4461INTO @var0; 4462ERROR 21000: Subquery returns more than 1 row 4463DROP TABLE t1; 4464# 4465# Bug #48458: simple query tries to allocate enormous amount of 4466# memory 4467# 4468CREATE TABLE t1(a INT NOT NULL, b YEAR); 4469INSERT INTO t1 VALUES (); 4470Warnings: 4471Warning 1364 Field 'a' doesn't have a default value 4472CREATE TABLE t2(c INT); 4473# Should not err out because of out-of-memory 4474SELECT 1 FROM t2 JOIN t1 ON 1=1 4475WHERE a != '1' AND NOT a >= b OR NOT ROW(b,a )<> ROW(a,a); 44761 4477DROP TABLE t1,t2; 4478# 4479# Bug #49199: Optimizer handles incorrectly: 4480# field='const1' AND field='const2' in some cases 4481 4482CREATE TABLE t1(a DATETIME NOT NULL); 4483INSERT INTO t1 VALUES('2001-01-01'); 4484SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; 4485a 44862001-01-01 00:00:00 4487EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; 4488id select_type table type possible_keys key key_len ref rows filtered Extra 44891 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 NULL 4490Warnings: 4491Note 1003 /* select#1 */ select '2001-01-01 00:00:00' AS `a` from dual where 1 4492DROP TABLE t1; 4493CREATE TABLE t1(a DATE NOT NULL); 4494INSERT INTO t1 VALUES('2001-01-01'); 4495SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; 4496a 44972001-01-01 4498EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; 4499id select_type table type possible_keys key key_len ref rows filtered Extra 45001 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 NULL 4501Warnings: 4502Note 1003 /* select#1 */ select '2001-01-01' AS `a` from dual where 1 4503DROP TABLE t1; 4504CREATE TABLE t1(a TIMESTAMP NOT NULL NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); 4505INSERT INTO t1 VALUES('2001-01-01'); 4506SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; 4507a 45082001-01-01 00:00:00 4509EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; 4510id select_type table type possible_keys key key_len ref rows filtered Extra 45111 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 NULL 4512Warnings: 4513Note 1003 /* select#1 */ select '2001-01-01 00:00:00' AS `a` from dual where 1 4514DROP TABLE t1; 4515CREATE TABLE t1(a DATETIME NOT NULL, b DATE NOT NULL); 4516INSERT INTO t1 VALUES('2001-01-01', '2001-01-01'); 4517SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00'; 4518a b 45192001-01-01 00:00:00 2001-01-01 4520EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00'; 4521id select_type table type possible_keys key key_len ref rows filtered Extra 45221 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 NULL 4523Warnings: 4524Note 1003 /* select#1 */ select '2001-01-01 00:00:00' AS `a`,'2001-01-01' AS `b` from dual where 1 4525DROP TABLE t1; 4526CREATE TABLE t1(a DATETIME NOT NULL, b VARCHAR(20) NOT NULL); 4527INSERT INTO t1 VALUES('2001-01-01', '2001-01-01'); 4528SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00'; 4529a b 4530EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00'; 4531id select_type table type possible_keys key key_len ref rows filtered Extra 45321 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 4533Warnings: 4534Note 1003 /* select#1 */ select '2001-01-01 00:00:00' AS `a`,'2001-01-01' AS `b` from dual where 0 4535SELECT * FROM t1 WHERE a='2001-01-01 00:00:00' AND a=b AND b='2001-01-01'; 4536a b 45372001-01-01 00:00:00 2001-01-01 4538EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01 00:00:00' AND a=b AND b='2001-01-01'; 4539id select_type table type possible_keys key key_len ref rows filtered Extra 45401 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 NULL 4541Warnings: 4542Note 1003 /* select#1 */ select '2001-01-01 00:00:00' AS `a`,'2001-01-01' AS `b` from dual where 1 4543DROP TABLE t1; 4544CREATE TABLE t1(a DATETIME NOT NULL, b DATE NOT NULL); 4545INSERT INTO t1 VALUES('2001-01-01', '2001-01-01'); 4546SELECT x.a, y.a, z.a FROM t1 x 4547JOIN t1 y ON x.a=y.a 4548JOIN t1 z ON y.a=z.a 4549WHERE x.a='2001-01-01' AND z.a='2001-01-01 00:00:00'; 4550a a a 45512001-01-01 00:00:00 2001-01-01 00:00:00 2001-01-01 00:00:00 4552EXPLAIN EXTENDED SELECT x.a, y.a, z.a FROM t1 x 4553JOIN t1 y ON x.a=y.a 4554JOIN t1 z ON y.a=z.a 4555WHERE x.a='2001-01-01' AND z.a='2001-01-01 00:00:00'; 4556id select_type table type possible_keys key key_len ref rows filtered Extra 45571 SIMPLE x system NULL NULL NULL NULL 1 100.00 NULL 45581 SIMPLE y system NULL NULL NULL NULL 1 100.00 NULL 45591 SIMPLE z system NULL NULL NULL NULL 1 100.00 NULL 4560Warnings: 4561Note 1003 /* select#1 */ select '2001-01-01 00:00:00' AS `a`,'2001-01-01 00:00:00' AS `a`,'2001-01-01 00:00:00' AS `a` from dual where 1 4562DROP TABLE t1; 4563# 4564# Bug #49897: crash in ptr_compare when char(0) NOT NULL 4565# column is used for ORDER BY 4566# 4567SET @old_sort_buffer_size= @@session.sort_buffer_size; 4568SET @@sort_buffer_size= 40000; 4569CREATE TABLE t1(a CHAR(0) NOT NULL); 4570INSERT INTO t1 VALUES (0), (0), (0); 4571INSERT INTO t1 SELECT t11.a FROM t1 t11, t1 t12; 4572INSERT INTO t1 SELECT t11.a FROM t1 t11, t1 t12; 4573INSERT INTO t1 SELECT t11.a FROM t1 t11, t1 t12; 4574EXPLAIN SELECT a FROM t1 ORDER BY a; 4575id select_type table type possible_keys key key_len ref rows Extra 45761 SIMPLE t1 ALL NULL NULL NULL NULL 24492 NULL 4577SELECT a FROM t1 ORDER BY a; 4578DROP TABLE t1; 4579CREATE TABLE t1(a CHAR(0) NOT NULL, b CHAR(0) NOT NULL, c int); 4580INSERT INTO t1 VALUES (0, 0, 0), (0, 0, 2), (0, 0, 1); 4581INSERT INTO t1 SELECT t11.a, t11.b, t11.c FROM t1 t11, t1 t12; 4582INSERT INTO t1 SELECT t11.a, t11.b, t11.c FROM t1 t11, t1 t12; 4583INSERT INTO t1 SELECT t11.a, t11.b, t11.c FROM t1 t11, t1 t12; 4584EXPLAIN SELECT a FROM t1 ORDER BY a LIMIT 5; 4585id select_type table type possible_keys key key_len ref rows Extra 45861 SIMPLE t1 ALL NULL NULL NULL NULL 24492 NULL 4587SELECT a FROM t1 ORDER BY a LIMIT 5; 4588a 4589 4590 4591 4592 4593 4594EXPLAIN SELECT * FROM t1 ORDER BY a, b LIMIT 5; 4595id select_type table type possible_keys key key_len ref rows Extra 45961 SIMPLE t1 ALL NULL NULL NULL NULL 24492 NULL 4597SELECT * FROM t1 ORDER BY a, b LIMIT 5; 4598a b c 4599 0 4600 2 4601 1 4602 0 4603 2 4604EXPLAIN SELECT * FROM t1 ORDER BY a, b, c LIMIT 5; 4605id select_type table type possible_keys key key_len ref rows Extra 46061 SIMPLE t1 ALL NULL NULL NULL NULL 24492 Using filesort 4607SELECT * FROM t1 ORDER BY a, b, c LIMIT 5; 4608a b c 4609 0 4610 0 4611 0 4612 0 4613 0 4614EXPLAIN SELECT * FROM t1 ORDER BY c, a LIMIT 5; 4615id select_type table type possible_keys key key_len ref rows Extra 46161 SIMPLE t1 ALL NULL NULL NULL NULL 24492 Using filesort 4617SELECT * FROM t1 ORDER BY c, a LIMIT 5; 4618a b c 4619 0 4620 0 4621 0 4622 0 4623 0 4624SET @@sort_buffer_size= @old_sort_buffer_size; 4625DROP TABLE t1; 4626End of 5.0 tests 4627create table t1(a INT, KEY (a)); 4628INSERT INTO t1 VALUES (1),(2),(3),(4),(5); 4629SELECT a FROM t1 ORDER BY a LIMIT 2; 4630a 46311 46322 4633SELECT a FROM t1 ORDER BY a LIMIT 2,4294967296; 4634a 46353 46364 46375 4638SELECT a FROM t1 ORDER BY a LIMIT 2,4294967297; 4639a 46403 46414 46425 4643DROP TABLE t1; 4644CREATE TABLE A (date_key date); 4645CREATE TABLE C ( 4646pk int, 4647int_nokey int, 4648int_key int, 4649date_key date NOT NULL, 4650date_nokey date, 4651varchar_key varchar(1) 4652); 4653INSERT INTO C VALUES 4654(1,1,1,'0000-00-00',NULL,NULL), 4655(1,1,1,'0000-00-00',NULL,NULL); 4656SELECT 1 FROM C WHERE pk > ANY (SELECT 1 FROM C); 46571 4658SELECT COUNT(DISTINCT 1) FROM C 4659WHERE date_key = (SELECT 1 FROM A WHERE C.date_key IS NULL) GROUP BY pk; 4660COUNT(DISTINCT 1) 4661SELECT date_nokey FROM C 4662WHERE int_key IN (SELECT 1 FROM A) 4663HAVING date_nokey = '10:41:7' 4664ORDER BY date_key; 4665date_nokey 4666Warnings: 4667Warning 1292 Incorrect date value: '10:41:7' for column 'date_nokey' at row 1 4668DROP TABLE A,C; 4669CREATE TABLE t1 (a INT NOT NULL, b INT); 4670INSERT INTO t1 VALUES (1, 1); 4671EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND a=a) OR b > 2; 4672id select_type table type possible_keys key key_len ref rows filtered Extra 46731 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 NULL 4674Warnings: 4675Note 1003 /* select#1 */ select '1' AS `a`,'1' AS `b` from dual where 1 4676SELECT * FROM t1 WHERE (a=a AND a=a) OR b > 2; 4677a b 46781 1 4679DROP TABLE t1; 4680CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, c INT NOT NULL); 4681EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND b=b AND c=c) OR b > 20; 4682id select_type table type possible_keys key key_len ref rows filtered Extra 46831 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 const row not found 4684Warnings: 4685Note 1003 /* select#1 */ select NULL AS `a`,NULL AS `b`,NULL AS `c` from `test`.`t1` where 1 4686EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND a=a AND b=b) OR b > 20; 4687id select_type table type possible_keys key key_len ref rows filtered Extra 46881 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 const row not found 4689Warnings: 4690Note 1003 /* select#1 */ select NULL AS `a`,NULL AS `b`,NULL AS `c` from `test`.`t1` where 1 4691EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND b=b AND a=a) OR b > 20; 4692id select_type table type possible_keys key key_len ref rows filtered Extra 46931 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 const row not found 4694Warnings: 4695Note 1003 /* select#1 */ select NULL AS `a`,NULL AS `b`,NULL AS `c` from `test`.`t1` where 1 4696DROP TABLE t1; 4697# 4698# Bug#45266: Uninitialized variable lead to an empty result. 4699# 4700drop table if exists A,AA,B,BB; 4701CREATE TABLE `A` ( 4702`pk` int(11) NOT NULL AUTO_INCREMENT, 4703`date_key` date NOT NULL, 4704`date_nokey` date NOT NULL, 4705`datetime_key` datetime NOT NULL, 4706`int_nokey` int(11) NOT NULL, 4707`time_key` time NOT NULL, 4708`time_nokey` time NOT NULL, 4709PRIMARY KEY (`pk`), 4710KEY `date_key` (`date_key`), 4711KEY `time_key` (`time_key`), 4712KEY `datetime_key` (`datetime_key`) 4713); 4714CREATE TABLE `AA` ( 4715`pk` int(11) NOT NULL AUTO_INCREMENT, 4716`int_nokey` int(11) NOT NULL, 4717`time_key` time NOT NULL, 4718KEY `time_key` (`time_key`), 4719PRIMARY KEY (`pk`) 4720); 4721CREATE TABLE `B` ( 4722`date_nokey` date NOT NULL, 4723`date_key` date NOT NULL, 4724`time_key` time NOT NULL, 4725`datetime_nokey` datetime NOT NULL, 4726`varchar_key` varchar(1) NOT NULL, 4727KEY `date_key` (`date_key`), 4728KEY `time_key` (`time_key`), 4729KEY `varchar_key` (`varchar_key`) 4730); 4731INSERT INTO `B` VALUES ('2003-07-28','2003-07-28','15:13:38','0000-00-00 00:00:00','f'),('0000-00-00','0000-00-00','00:05:48','2004-07-02 14:34:13','x'); 4732CREATE TABLE `BB` ( 4733`pk` int(11) NOT NULL AUTO_INCREMENT, 4734`int_nokey` int(11) NOT NULL, 4735`date_key` date NOT NULL, 4736`varchar_nokey` varchar(1) NOT NULL, 4737`date_nokey` date NOT NULL, 4738PRIMARY KEY (`pk`), 4739KEY `date_key` (`date_key`) 4740); 4741INSERT INTO `BB` VALUES (10,8,'0000-00-00','i','0000-00-00'),(11,0,'2005-08-18','','2005-08-18'); 4742SELECT table1 . `pk` AS field1 4743FROM 4744(BB AS table1 INNER JOIN 4745(AA AS table2 STRAIGHT_JOIN A AS table3 4746ON ( table3 . `date_key` = table2 . `pk` )) 4747ON ( table3 . `datetime_key` = table2 . `int_nokey` )) 4748WHERE ( table3 . `date_key` <= 4 AND table2 . `pk` = table1 . `varchar_nokey`) 4749GROUP BY field1 ; 4750field1 4751SELECT table3 .`date_key` field1 4752FROM 4753B table1 LEFT JOIN B table3 JOIN 4754(BB table6 JOIN A table7 ON table6 .`varchar_nokey`) 4755ON table6 .`int_nokey` ON table6 .`date_key` 4756 WHERE NOT ( table1 .`varchar_key` AND table7 .`pk`) GROUP BY field1; 4757field1 4758NULL 4759SELECT table4 . `time_nokey` AS field1 FROM 4760(AA AS table1 CROSS JOIN 4761(AA AS table2 STRAIGHT_JOIN 4762(B AS table3 STRAIGHT_JOIN A AS table4 4763ON ( table4 . `date_key` = table3 . `time_key` )) 4764ON ( table4 . `pk` = table3 . `date_nokey` )) 4765ON ( table4 . `time_key` = table3 . `datetime_nokey` )) 4766WHERE ( table4 . `time_key` < table1 . `time_key` AND 4767table1 . `int_nokey` != 'f') 4768GROUP BY field1 ORDER BY field1 , field1; 4769field1 4770SELECT table1 .`time_key` field2 FROM B table1 LEFT JOIN BB JOIN A table5 ON table5 .`date_nokey` ON table5 .`int_nokey` GROUP BY field2; 4771field2 477200:05:48 477315:13:38 4774drop table A,AA,B,BB; 4775#end of test for bug#45266 4776# 4777# Bug#33546: Slowdown on re-evaluation of constant expressions. 4778# 4779CREATE TABLE t1 (a INT); 4780INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); 4781CREATE TABLE t2 (b INT); 4782INSERT INTO t2 VALUES (2); 4783SELECT * FROM t1 WHERE a = 1 + 1; 4784a 47852 4786EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = 1 + 1; 4787id select_type table type possible_keys key key_len ref rows filtered Extra 47881 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where 4789Warnings: 4790Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = <cache>((1 + 1))) 4791SELECT * FROM t1 HAVING a = 1 + 1; 4792a 47932 4794EXPLAIN EXTENDED SELECT * FROM t1 HAVING a = 1 + 1; 4795id select_type table type possible_keys key key_len ref rows filtered Extra 47961 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 NULL 4797Warnings: 4798Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` having (`test`.`t1`.`a` = <cache>((1 + 1))) 4799SELECT * FROM t1, t2 WHERE a = b + (1 + 1); 4800a b 48014 2 4802EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE a = b + (1 + 1); 4803id select_type table type possible_keys key key_len ref rows filtered Extra 48041 SIMPLE t2 system NULL NULL NULL NULL 1 100.00 NULL 48051 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where 4806Warnings: 4807Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,'2' AS `b` from `test`.`t1` where (`test`.`t1`.`a` = <cache>(('2' + (1 + 1)))) 4808SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1; 4809b a 48102 3 4811EXPLAIN EXTENDED SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1; 4812id select_type table type possible_keys key key_len ref rows filtered Extra 48131 SIMPLE t2 system NULL NULL NULL NULL 1 100.00 NULL 48141 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where 4815Warnings: 4816Note 1003 /* select#1 */ select '2' AS `b`,`test`.`t1`.`a` AS `a` from `test`.`t1` where 1 4817EXPLAIN EXTENDED SELECT * FROM t1 WHERE a > UNIX_TIMESTAMP('2009-03-10 00:00:00'); 4818id select_type table type possible_keys key key_len ref rows filtered Extra 48191 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where 4820Warnings: 4821Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > <cache>(unix_timestamp('2009-03-10 00:00:00'))) 4822CREATE FUNCTION f1() RETURNS INT DETERMINISTIC 4823BEGIN 4824SET @cnt := @cnt + 1; 4825RETURN 1; 4826END;| 4827SET @cnt := 0; 4828SELECT * FROM t1 WHERE a = f1(); 4829a 48301 4831SELECT @cnt; 4832@cnt 48331 4834EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = f1(); 4835id select_type table type possible_keys key key_len ref rows filtered Extra 48361 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where 4837Warnings: 4838Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = <cache>(`f1`())) 4839DROP TABLE t1, t2; 4840DROP FUNCTION f1; 4841# End of bug#33546 4842# 4843# BUG#48052: Valgrind warning - uninitialized value in init_read_record() 4844# 4845# Disable Index condition pushdown 4846SELECT @old_optimizer_switch:=@@optimizer_switch; 4847@old_optimizer_switch:=@@optimizer_switch 4848# 4849CREATE TABLE t1 ( 4850pk int(11) NOT NULL, 4851i int(11) DEFAULT NULL, 4852v varchar(1) DEFAULT NULL, 4853PRIMARY KEY (pk) 4854); 4855INSERT INTO t1 VALUES (2,7,'m'); 4856INSERT INTO t1 VALUES (3,9,'m'); 4857SELECT v 4858FROM t1 4859WHERE NOT pk > 0 4860HAVING v <= 't' 4861ORDER BY pk; 4862v 4863# Restore old value for Index condition pushdown 4864SET SESSION optimizer_switch=@old_optimizer_switch; 4865DROP TABLE t1; 4866# 4867# Bug#49489 Uninitialized cache led to a wrong result. 4868# 4869CREATE TABLE t1(c1 DOUBLE(5,4)); 4870INSERT INTO t1 VALUES (9.1234); 4871SELECT * FROM t1 WHERE c1 < 9.12345; 4872c1 48739.1234 4874DROP TABLE t1; 4875# End of test for bug#49489. 4876# 4877# Bug #49517: Inconsistent behavior while using 4878# NULLable BIGINT and INT columns in comparison 4879# 4880CREATE TABLE t1(a BIGINT UNSIGNED NOT NULL, b BIGINT NULL, c INT NULL); 4881INSERT INTO t1 VALUES(105, NULL, NULL); 4882SELECT * FROM t1 WHERE b < 102; 4883a b c 4884SELECT * FROM t1 WHERE c < 102; 4885a b c 4886SELECT * FROM t1 WHERE 102 < b; 4887a b c 4888SELECT * FROM t1 WHERE 102 < c; 4889a b c 4890DROP TABLE t1; 4891# 4892# Bug #54459: Assertion failed: param.sort_length, 4893# file .\filesort.cc, line 149 (part II) 4894# 4895CREATE TABLE t1(a ENUM('') NOT NULL); 4896INSERT INTO t1 VALUES (), (), (); 4897EXPLAIN SELECT 1 FROM t1 ORDER BY a COLLATE latin1_german2_ci; 4898id select_type table type possible_keys key key_len ref rows Extra 48991 SIMPLE t1 ALL NULL NULL NULL NULL 3 NULL 4900SELECT 1 FROM t1 ORDER BY a COLLATE latin1_german2_ci; 49011 49021 49031 49041 4905DROP TABLE t1; 4906# 4907# Bug #58422: Incorrect result when OUTER JOIN'ing 4908# with an empty table 4909# 4910CREATE TABLE t_empty(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM; 4911CREATE TABLE t1(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM; 4912INSERT INTO t1 VALUES (1,1), (2,2), (3,3); 4913CREATE TABLE t2(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM; 4914INSERT INTO t2 VALUES (1,1), (2,2), (3,3); 4915EXPLAIN 4916SELECT * 4917FROM 4918t1 4919LEFT OUTER JOIN 4920(t2 INNER JOIN t_empty ON TRUE) 4921ON t1.pk=t2.pk 4922WHERE t2.pk <> 2; 4923id select_type table type possible_keys key key_len ref rows Extra 49241 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 4925SELECT * 4926FROM 4927t1 4928LEFT OUTER JOIN 4929(t2 INNER JOIN t_empty ON TRUE) 4930ON t1.pk=t2.pk 4931WHERE t2.pk <> 2; 4932pk i pk i pk i 4933EXPLAIN 4934SELECT * 4935FROM 4936t1 4937LEFT OUTER JOIN 4938(t2 CROSS JOIN t_empty) 4939ON t1.pk=t2.pk 4940WHERE t2.pk <> 2; 4941id select_type table type possible_keys key key_len ref rows Extra 49421 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 4943SELECT * 4944FROM 4945t1 4946LEFT OUTER JOIN 4947(t2 CROSS JOIN t_empty) 4948ON t1.pk=t2.pk 4949WHERE t2.pk <> 2; 4950pk i pk i pk i 4951EXPLAIN 4952SELECT * 4953FROM 4954t1 4955LEFT OUTER JOIN 4956(t2 INNER JOIN t_empty ON t_empty.i=t2.i) 4957ON t1.pk=t2.pk 4958WHERE t2.pk <> 2; 4959id select_type table type possible_keys key key_len ref rows Extra 49601 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 4961SELECT * 4962FROM 4963t1 4964LEFT OUTER JOIN 4965(t2 INNER JOIN t_empty ON t_empty.i=t2.i) 4966ON t1.pk=t2.pk 4967WHERE t2.pk <> 2; 4968pk i pk i pk i 4969DROP TABLE t1,t2,t_empty; 4970End of 5.1 tests 4971# 4972# Bug#45227: Lost HAVING clause led to a wrong result. 4973# 4974CREATE TABLE `CC` ( 4975`int_nokey` int(11) NOT NULL, 4976`int_key` int(11) NOT NULL, 4977`varchar_key` varchar(1) NOT NULL, 4978`varchar_nokey` varchar(1) NOT NULL, 4979KEY `int_key` (`int_key`), 4980KEY `varchar_key` (`varchar_key`) 4981); 4982INSERT INTO `CC` VALUES 4983(0,8,'q','q'),(5,8,'m','m'),(7,3,'j','j'),(1,2,'z','z'),(8,2,'a','a'),(2,6,'',''),(1,8,'e' 4984,'e'),(8,9,'t','t'),(5,2,'q','q'),(4,6,'b','b'),(5,5,'w','w'),(3,2,'m','m'),(0,4,'x','x'), 4985(8,9,'',''),(0,6,'w','w'),(4,5,'x','x'),(0,0,'e','e'),(0,0,'e','e'),(2,8,'p','p'),(0,0,'x' 4986,'x'); 4987EXPLAIN SELECT `varchar_nokey` G1 FROM CC WHERE `int_nokey` AND `int_key` <= 4 4988HAVING G1 ORDER BY `varchar_key` LIMIT 6 ; 4989id select_type table type possible_keys key key_len ref rows Extra 49901 SIMPLE CC ALL int_key NULL NULL NULL 20 Using where; Using filesort 4991SELECT `varchar_nokey` G1 FROM CC WHERE `int_nokey` AND `int_key` <= 4 4992HAVING G1 ORDER BY `varchar_key` LIMIT 6 ; 4993G1 4994Warnings: 4995Warning 1292 Truncated incorrect DOUBLE value: 'j' 4996Warning 1292 Truncated incorrect DOUBLE value: 'z' 4997Warning 1292 Truncated incorrect DOUBLE value: 'a' 4998Warning 1292 Truncated incorrect DOUBLE value: 'q' 4999Warning 1292 Truncated incorrect DOUBLE value: 'm' 5000DROP TABLE CC; 5001# End of test#45227 5002# 5003# Bug#54515: Crash in opt_range.cc::get_best_group_min_max on 5004# SELECT from VIEW with GROUP BY 5005# 5006CREATE TABLE t1 ( 5007col_int_key int DEFAULT NULL, 5008KEY int_key (col_int_key) 5009) ; 5010INSERT INTO t1 VALUES (1),(2); 5011CREATE VIEW view_t1 AS 5012SELECT t1.col_int_key AS col_int_key 5013FROM t1; 5014SELECT col_int_key FROM view_t1 GROUP BY col_int_key; 5015col_int_key 50161 50172 5018DROP VIEW view_t1; 5019DROP TABLE t1; 5020# End of test BUG#54515 5021# 5022# Bug #57203 Assertion `field_length <= 255' failed. 5023# 5024SELECT coalesce((avg(distinct (geomfromtext("point(25379 -22010)"))))) 5025UNION ALL 5026SELECT coalesce((avg(distinct (geomfromtext("point(25379 -22010)"))))) 5027AS foo 5028; 5029coalesce((avg(distinct (geomfromtext("point(25379 -22010)"))))) 50300.0000 50310.0000 5032CREATE table t1(a text); 5033INSERT INTO t1 VALUES (''), (''); 5034SELECT avg(distinct(t1.a)) FROM t1, t1 t2 5035GROUP BY t2.a ORDER BY t1.a; 5036avg(distinct(t1.a)) 50370 5038DROP TABLE t1; 5039# End of test BUG#57203 5040# 5041# Bug#63020: Function "format"'s 'locale' argument is not considered 5042# when creating a "view' 5043# 5044CREATE TABLE t1 (f1 DECIMAL(10,2)); 5045INSERT INTO t1 VALUES (11.67),(17865.3),(12345678.92); 5046CREATE VIEW view_t1 AS SELECT FORMAT(f1,1,'sk_SK') AS f1 FROM t1; 5047SHOW CREATE VIEW view_t1; 5048View Create View character_set_client collation_connection 5049view_t1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_t1` AS select format(`t1`.`f1`,1,'sk_SK') AS `f1` from `t1` latin1 latin1_swedish_ci 5050SELECT * FROM view_t1; 5051f1 505211,7 505317 865,3 505412 345 678,9 5055DROP TABLE t1; 5056DROP VIEW view_t1; 5057# End of test BUG#63020 5058# 5059# Bug #13571700 TINYBLOB NOT NULL, CRASH IN PROTOCOL::NET_STORE_DATA 5060# 5061CREATE TABLE t1 (a TINYBLOB NOT NULL); 5062SELECT a, COUNT(*) FROM t1 WHERE 0; 5063a COUNT(*) 5064NULL 0 5065DROP TABLE t1; 5066# End of test BUG#13571700 5067set optimizer_switch=default; 5068