1set optimizer_switch='semijoin=on,materialization=on,firstmatch=on,loosescan=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=off'; 2drop table if exists t1,t2,t3,t4,t11; 3drop table if exists t1_1,t1_2,t9_1,t9_2,t1aa,t2aa; 4drop view if exists v1; 5CREATE TABLE t1 ( 6Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL, 7Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL 8); 9INSERT INTO t1 VALUES (9410,9412); 10select period from t1; 11period 129410 13select * from t1; 14Period Varor_period 159410 9412 16select t1.* from t1; 17Period Varor_period 189410 9412 19CREATE TABLE t2 ( 20auto int not null auto_increment, 21fld1 int(6) unsigned zerofill DEFAULT '000000' NOT NULL, 22companynr tinyint(2) unsigned zerofill DEFAULT '00' NOT NULL, 23fld3 char(30) DEFAULT '' NOT NULL, 24fld4 char(35) DEFAULT '' NOT NULL, 25fld5 char(35) DEFAULT '' NOT NULL, 26fld6 char(4) DEFAULT '' NOT NULL, 27UNIQUE fld1 (fld1), 28KEY fld3 (fld3), 29PRIMARY KEY (auto) 30); 31select t2.fld3 from t2 where companynr = 58 and fld3 like "%imaginable%"; 32fld3 33imaginable 34select fld3 from t2 where fld3 like "%cultivation" ; 35fld3 36cultivation 37select t2.fld3,companynr from t2 where companynr = 57+1 order by fld3; 38fld3 companynr 39concoct 58 40druggists 58 41engrossing 58 42Eurydice 58 43exclaimers 58 44ferociousness 58 45hopelessness 58 46Huey 58 47imaginable 58 48judges 58 49merging 58 50ostrich 58 51peering 58 52Phelps 58 53presumes 58 54Ruth 58 55sentences 58 56Shylock 58 57straggled 58 58synergy 58 59thanking 58 60tying 58 61unlocks 58 62select fld3,companynr from t2 where companynr = 58 order by fld3; 63fld3 companynr 64concoct 58 65druggists 58 66engrossing 58 67Eurydice 58 68exclaimers 58 69ferociousness 58 70hopelessness 58 71Huey 58 72imaginable 58 73judges 58 74merging 58 75ostrich 58 76peering 58 77Phelps 58 78presumes 58 79Ruth 58 80sentences 58 81Shylock 58 82straggled 58 83synergy 58 84thanking 58 85tying 58 86unlocks 58 87select fld3 from t2 order by fld3 desc limit 10; 88fld3 89youthfulness 90yelped 91Wotan 92workers 93Witt 94witchcraft 95Winsett 96Willy 97willed 98wildcats 99select fld3 from t2 order by fld3 desc limit 5; 100fld3 101youthfulness 102yelped 103Wotan 104workers 105Witt 106select fld3 from t2 order by fld3 desc limit 5,5; 107fld3 108witchcraft 109Winsett 110Willy 111willed 112wildcats 113select t2.fld3 from t2 where fld3 = 'honeysuckle'; 114fld3 115honeysuckle 116select t2.fld3 from t2 where fld3 LIKE 'honeysuckl_'; 117fld3 118honeysuckle 119select t2.fld3 from t2 where fld3 LIKE 'hon_ysuckl_'; 120fld3 121honeysuckle 122select t2.fld3 from t2 where fld3 LIKE 'honeysuckle%'; 123fld3 124honeysuckle 125select t2.fld3 from t2 where fld3 LIKE 'h%le'; 126fld3 127honeysuckle 128select t2.fld3 from t2 where fld3 LIKE 'honeysuckle_'; 129fld3 130select t2.fld3 from t2 where fld3 LIKE 'don_t_find_me_please%'; 131fld3 132explain select t2.fld3 from t2 where fld3 = 'honeysuckle'; 133id select_type table type possible_keys key key_len ref rows Extra 1341 SIMPLE t2 ref fld3 fld3 30 const 1 Using where; Using index 135explain select fld3 from t2 ignore index (fld3) where fld3 = 'honeysuckle'; 136id select_type table type possible_keys key key_len ref rows Extra 1371 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 138explain select fld3 from t2 use index (fld1) where fld3 = 'honeysuckle'; 139id select_type table type possible_keys key key_len ref rows Extra 1401 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 141explain select fld3 from t2 use index (fld3) where fld3 = 'honeysuckle'; 142id select_type table type possible_keys key key_len ref rows Extra 1431 SIMPLE t2 ref fld3 fld3 30 const 1 Using where; Using index 144explain select fld3 from t2 use index (fld1,fld3) where fld3 = 'honeysuckle'; 145id select_type table type possible_keys key key_len ref rows Extra 1461 SIMPLE t2 ref fld3 fld3 30 const 1 Using where; Using index 147explain select fld3 from t2 ignore index (fld3,not_used); 148ERROR 42000: Key 'not_used' doesn't exist in table 't2' 149explain select fld3 from t2 use index (not_used); 150ERROR 42000: Key 'not_used' doesn't exist in table 't2' 151select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3; 152fld3 153honeysuckle 154honoring 155explain select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3; 156id select_type table type possible_keys key key_len ref rows Extra 1571 SIMPLE t2 range fld3 fld3 30 NULL 2 Using where; Using index 158select fld1,fld3 from t2 where fld3="Colombo" or fld3 = "nondecreasing" order by fld3; 159fld1 fld3 160148504 Colombo 161068305 Colombo 162000000 nondecreasing 163select fld1,fld3 from t2 where companynr = 37 and fld3 = 'appendixes'; 164fld1 fld3 165232605 appendixes 1661232605 appendixes 1671232606 appendixes 1681232607 appendixes 1691232608 appendixes 1701232609 appendixes 171select fld1 from t2 where fld1=250501 or fld1="250502"; 172fld1 173250501 174250502 175explain select fld1 from t2 where fld1=250501 or fld1="250502"; 176id select_type table type possible_keys key key_len ref rows Extra 1771 SIMPLE t2 range fld1 fld1 4 NULL 2 Using where; Using index 178select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502; 179fld1 180250501 181250502 182250505 183250601 184explain select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502; 185id select_type table type possible_keys key key_len ref rows Extra 1861 SIMPLE t2 range fld1 fld1 4 NULL 4 Using where; Using index 187select fld1,fld3 from t2 where companynr = 37 and fld3 like 'f%'; 188fld1 fld3 189012001 flanking 190013602 foldout 191013606 fingerings 192018007 fanatic 193018017 featherweight 194018054 fetters 195018103 flint 196018104 flopping 197036002 funereal 198038017 fetched 199038205 firearm 200058004 Fenton 201088303 feminine 202186002 freakish 203188007 flurried 204188505 fitting 205198006 furthermore 206202301 Fitzpatrick 207208101 fiftieth 208208113 freest 209218008 finishers 210218022 feed 211218401 faithful 212226205 foothill 213226209 furnishings 214228306 forthcoming 215228311 fated 216231315 freezes 217232102 forgivably 218238007 filial 219238008 fixedly 220select fld3 from t2 where fld3 like "L%" and fld3 = "ok"; 221fld3 222select fld3 from t2 where (fld3 like "C%" and fld3 = "Chantilly"); 223fld3 224Chantilly 225select fld1,fld3 from t2 where fld1 like "25050%"; 226fld1 fld3 227250501 poisoning 228250502 Iraqis 229250503 heaving 230250504 population 231250505 bomb 232select fld1,fld3 from t2 where fld1 like "25050_"; 233fld1 fld3 234250501 poisoning 235250502 Iraqis 236250503 heaving 237250504 population 238250505 bomb 239select distinct companynr from t2; 240companynr 24100 24237 24336 24450 24558 24629 24740 24853 24965 25041 25134 25268 253select distinct companynr from t2 order by companynr; 254companynr 25500 25629 25734 25836 25937 26040 26141 26250 26353 26458 26565 26668 267select distinct companynr from t2 order by companynr desc; 268companynr 26968 27065 27158 27253 27350 27441 27540 27637 27736 27834 27929 28000 281select distinct t2.fld3,period from t2,t1 where companynr=37 and fld3 like "O%"; 282fld3 period 283obliterates 9410 284offload 9410 285opaquely 9410 286organizer 9410 287overestimating 9410 288overlay 9410 289select distinct fld3 from t2 where companynr = 34 order by fld3; 290fld3 291absentee 292accessed 293ahead 294alphabetic 295Asiaticizations 296attitude 297aye 298bankruptcies 299belays 300Blythe 301bomb 302boulevard 303bulldozes 304cannot 305caressing 306charcoal 307checksumming 308chess 309clubroom 310colorful 311cosy 312creator 313crying 314Darius 315diffusing 316duality 317Eiffel 318Epiphany 319Ernestine 320explorers 321exterminated 322famine 323forked 324Gershwins 325heaving 326Hodges 327Iraqis 328Italianization 329Lagos 330landslide 331libretto 332Majorca 333mastering 334narrowed 335occurred 336offerers 337Palestine 338Peruvianizes 339pharmaceutic 340poisoning 341population 342Pygmalion 343rats 344realest 345recording 346regimented 347retransmitting 348reviver 349rouses 350scars 351sicker 352sleepwalk 353stopped 354sugars 355translatable 356uncles 357unexpected 358uprisings 359versatility 360vest 361select distinct fld3 from t2 limit 10; 362fld3 363abates 364abiding 365Abraham 366abrogating 367absentee 368abut 369accessed 370accruing 371accumulating 372accuracies 373select distinct fld3 from t2 having fld3 like "A%" limit 10; 374fld3 375abates 376abiding 377Abraham 378abrogating 379absentee 380abut 381accessed 382accruing 383accumulating 384accuracies 385select distinct substring(fld3,1,3) from t2 where fld3 like "A%"; 386substring(fld3,1,3) 387aba 388abi 389Abr 390abs 391abu 392acc 393acq 394acu 395Ade 396adj 397Adl 398adm 399Ado 400ads 401adv 402aer 403aff 404afi 405afl 406afo 407agi 408ahe 409aim 410air 411Ald 412alg 413ali 414all 415alp 416alr 417ama 418ame 419amm 420ana 421and 422ane 423Ang 424ani 425Ann 426Ant 427api 428app 429aqu 430Ara 431arc 432Arm 433arr 434Art 435Asi 436ask 437asp 438ass 439ast 440att 441aud 442Aug 443aut 444ave 445avo 446awe 447aye 448Azt 449select distinct substring(fld3,1,3) as a from t2 having a like "A%" order by a limit 10; 450a 451aba 452abi 453Abr 454abs 455abu 456acc 457acq 458acu 459Ade 460adj 461select distinct substring(fld3,1,3) from t2 where fld3 like "A%" limit 10; 462substring(fld3,1,3) 463aba 464abi 465Abr 466abs 467abu 468acc 469acq 470acu 471Ade 472adj 473select distinct substring(fld3,1,3) as a from t2 having a like "A%" limit 10; 474a 475aba 476abi 477Abr 478abs 479abu 480acc 481acq 482acu 483Ade 484adj 485create table t3 ( 486period int not null, 487name char(32) not null, 488companynr int not null, 489price double(11,0), 490price2 double(11,0), 491key (period), 492key (name) 493); 494create temporary table tmp engine = myisam select * from t3; 495insert into t3 select * from tmp; 496insert into tmp select * from t3; 497insert into t3 select * from tmp; 498insert into tmp select * from t3; 499insert into t3 select * from tmp; 500insert into tmp select * from t3; 501insert into t3 select * from tmp; 502insert into tmp select * from t3; 503insert into t3 select * from tmp; 504insert into tmp select * from t3; 505insert into t3 select * from tmp; 506insert into tmp select * from t3; 507insert into t3 select * from tmp; 508insert into tmp select * from t3; 509insert into t3 select * from tmp; 510insert into tmp select * from t3; 511insert into t3 select * from tmp; 512alter table t3 add t2nr int not null auto_increment primary key first; 513drop table tmp; 514SET BIG_TABLES=1; 515select distinct concat(fld3," ",fld3) as namn from t2,t3 where t2.fld1=t3.t2nr order by namn limit 10; 516namn 517Abraham Abraham 518abrogating abrogating 519admonishing admonishing 520Adolph Adolph 521afield afield 522aging aging 523ammonium ammonium 524analyzable analyzable 525animals animals 526animized animized 527SET BIG_TABLES=0; 528select distinct concat(fld3," ",fld3) from t2,t3 where t2.fld1=t3.t2nr order by fld3 limit 10; 529concat(fld3," ",fld3) 530Abraham Abraham 531abrogating abrogating 532admonishing admonishing 533Adolph Adolph 534afield afield 535aging aging 536ammonium ammonium 537analyzable analyzable 538animals animals 539animized animized 540select distinct fld5 from t2 limit 10; 541fld5 542neat 543Steinberg 544jarring 545tinily 546balled 547persist 548attainments 549fanatic 550measures 551rightfulness 552select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10; 553fld3 count(*) 554affixed 1 555and 1 556annoyers 1 557Anthony 1 558assayed 1 559assurers 1 560attendants 1 561bedlam 1 562bedpost 1 563boasted 1 564SET BIG_TABLES=1; 565select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10; 566fld3 count(*) 567affixed 1 568and 1 569annoyers 1 570Anthony 1 571assayed 1 572assurers 1 573attendants 1 574bedlam 1 575bedpost 1 576boasted 1 577SET BIG_TABLES=0; 578select distinct fld3,repeat("a",length(fld3)),count(*) from t2 group by companynr,fld3 limit 100,10; 579fld3 repeat("a",length(fld3)) count(*) 580circus aaaaaa 1 581cited aaaaa 1 582Colombo aaaaaaa 1 583congresswoman aaaaaaaaaaaaa 1 584contrition aaaaaaaaaa 1 585corny aaaaa 1 586cultivation aaaaaaaaaaa 1 587definiteness aaaaaaaaaaaa 1 588demultiplex aaaaaaaaaaa 1 589disappointing aaaaaaaaaaaaa 1 590select distinct companynr,rtrim(space(512+companynr)) from t3 order by 1,2; 591companynr rtrim(space(512+companynr)) 59237 59378 594101 595154 596311 597447 598512 599select distinct fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by fld3; 600fld3 601explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by t3.t2nr,fld3; 602id select_type table type possible_keys key key_len ref rows Extra 6031 SIMPLE t2 ALL fld1 NULL NULL NULL 1199 Using where; Using temporary; Using filesort 6041 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.fld1 1 Using where; Using index 605explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period; 606id select_type table type possible_keys key key_len ref rows Extra 6071 SIMPLE t1 ALL period NULL NULL NULL 41810 Using temporary; Using filesort 6081 SIMPLE t3 ref period period 4 test.t1.period 4181 NULL 609explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10; 610id select_type table type possible_keys key key_len ref rows Extra 6111 SIMPLE t3 index period period 4 NULL 1 NULL 6121 SIMPLE t1 ref period period 4 test.t3.period 4181 NULL 613explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10; 614id select_type table type possible_keys key key_len ref rows Extra 6151 SIMPLE t1 index period period 4 NULL 1 NULL 6161 SIMPLE t3 ref period period 4 test.t1.period 4181 NULL 617select period from t1; 618period 6199410 620select period from t1 where period=1900; 621period 622select fld3,period from t1,t2 where fld1 = 011401 order by period; 623fld3 period 624breaking 9410 625select fld3,period from t2,t3 where t2.fld1 = 011401 and t2.fld1=t3.t2nr and t3.period=1001; 626fld3 period 627breaking 1001 628explain select fld3,period from t2,t3 where t2.fld1 = 011401 and t3.t2nr=t2.fld1 and 1001 = t3.period; 629id select_type table type possible_keys key key_len ref rows Extra 6301 SIMPLE t2 const fld1 fld1 4 const 1 NULL 6311 SIMPLE t3 const PRIMARY,period PRIMARY 4 const 1 NULL 632select fld3,period from t2,t1 where companynr*10 = 37*10; 633fld3 period 634breaking 9410 635Romans 9410 636intercepted 9410 637bewilderingly 9410 638astound 9410 639admonishing 9410 640sumac 9410 641flanking 9410 642combed 9410 643subjective 9410 644scatterbrain 9410 645Eulerian 9410 646Kane 9410 647overlay 9410 648perturb 9410 649goblins 9410 650annihilates 9410 651Wotan 9410 652snatching 9410 653concludes 9410 654laterally 9410 655yelped 9410 656grazing 9410 657Baird 9410 658celery 9410 659misunderstander 9410 660handgun 9410 661foldout 9410 662mystic 9410 663succumbed 9410 664Nabisco 9410 665fingerings 9410 666aging 9410 667afield 9410 668ammonium 9410 669boat 9410 670intelligibility 9410 671Augustine 9410 672teethe 9410 673dreaded 9410 674scholastics 9410 675audiology 9410 676wallet 9410 677parters 9410 678eschew 9410 679quitter 9410 680neat 9410 681Steinberg 9410 682jarring 9410 683tinily 9410 684balled 9410 685persist 9410 686attainments 9410 687fanatic 9410 688measures 9410 689rightfulness 9410 690capably 9410 691impulsive 9410 692starlet 9410 693terminators 9410 694untying 9410 695announces 9410 696featherweight 9410 697pessimist 9410 698daughter 9410 699decliner 9410 700lawgiver 9410 701stated 9410 702readable 9410 703attrition 9410 704cascade 9410 705motors 9410 706interrogate 9410 707pests 9410 708stairway 9410 709dopers 9410 710testicle 9410 711Parsifal 9410 712leavings 9410 713postulation 9410 714squeaking 9410 715contrasted 9410 716leftover 9410 717whiteners 9410 718erases 9410 719Punjab 9410 720Merritt 9410 721Quixotism 9410 722sweetish 9410 723dogging 9410 724scornfully 9410 725bellow 9410 726bills 9410 727cupboard 9410 728sureties 9410 729puddings 9410 730fetters 9410 731bivalves 9410 732incurring 9410 733Adolph 9410 734pithed 9410 735Miles 9410 736trimmings 9410 737tragedies 9410 738skulking 9410 739flint 9410 740flopping 9410 741relaxing 9410 742offload 9410 743suites 9410 744lists 9410 745animized 9410 746multilayer 9410 747standardizes 9410 748Judas 9410 749vacuuming 9410 750dentally 9410 751humanness 9410 752inch 9410 753Weissmuller 9410 754irresponsibly 9410 755luckily 9410 756culled 9410 757medical 9410 758bloodbath 9410 759subschema 9410 760animals 9410 761Micronesia 9410 762repetitions 9410 763Antares 9410 764ventilate 9410 765pityingly 9410 766interdependent 9410 767Graves 9410 768neonatal 9410 769chafe 9410 770honoring 9410 771realtor 9410 772elite 9410 773funereal 9410 774abrogating 9410 775sorters 9410 776Conley 9410 777lectured 9410 778Abraham 9410 779Hawaii 9410 780cage 9410 781hushes 9410 782Simla 9410 783reporters 9410 784Dutchman 9410 785descendants 9410 786groupings 9410 787dissociate 9410 788coexist 9410 789Beebe 9410 790Taoism 9410 791Connally 9410 792fetched 9410 793checkpoints 9410 794rusting 9410 795galling 9410 796obliterates 9410 797traitor 9410 798resumes 9410 799analyzable 9410 800terminator 9410 801gritty 9410 802firearm 9410 803minima 9410 804Selfridge 9410 805disable 9410 806witchcraft 9410 807betroth 9410 808Manhattanize 9410 809imprint 9410 810peeked 9410 811swelling 9410 812interrelationships 9410 813riser 9410 814Gandhian 9410 815peacock 9410 816bee 9410 817kanji 9410 818dental 9410 819scarf 9410 820chasm 9410 821insolence 9410 822syndicate 9410 823alike 9410 824imperial 9410 825convulsion 9410 826railway 9410 827validate 9410 828normalizes 9410 829comprehensive 9410 830chewing 9410 831denizen 9410 832schemer 9410 833chronicle 9410 834Kline 9410 835Anatole 9410 836partridges 9410 837brunch 9410 838recruited 9410 839dimensions 9410 840Chicana 9410 841announced 9410 842praised 9410 843employing 9410 844linear 9410 845quagmire 9410 846western 9410 847relishing 9410 848serving 9410 849scheduling 9410 850lore 9410 851eventful 9410 852arteriole 9410 853disentangle 9410 854cured 9410 855Fenton 9410 856avoidable 9410 857drains 9410 858detectably 9410 859husky 9410 860impelling 9410 861undoes 9410 862evened 9410 863squeezes 9410 864destroyer 9410 865rudeness 9410 866beaner 9410 867boorish 9410 868Everhart 9410 869encompass 9410 870mushrooms 9410 871Alison 9410 872externally 9410 873pellagra 9410 874cult 9410 875creek 9410 876Huffman 9410 877Majorca 9410 878governing 9410 879gadfly 9410 880reassigned 9410 881intentness 9410 882craziness 9410 883psychic 9410 884squabbled 9410 885burlesque 9410 886capped 9410 887extracted 9410 888DiMaggio 9410 889exclamation 9410 890subdirectory 9410 891Gothicism 9410 892feminine 9410 893metaphysically 9410 894sanding 9410 895Miltonism 9410 896freakish 9410 897index 9410 898straight 9410 899flurried 9410 900denotative 9410 901coming 9410 902commencements 9410 903gentleman 9410 904gifted 9410 905Shanghais 9410 906sportswriting 9410 907sloping 9410 908navies 9410 909leaflet 9410 910shooter 9410 911Joplin 9410 912babies 9410 913assails 9410 914admiring 9410 915swaying 9410 916Goldstine 9410 917fitting 9410 918Norwalk 9410 919analogy 9410 920deludes 9410 921cokes 9410 922Clayton 9410 923exhausts 9410 924causality 9410 925sating 9410 926icon 9410 927throttles 9410 928communicants 9410 929dehydrate 9410 930priceless 9410 931publicly 9410 932incidentals 9410 933commonplace 9410 934mumbles 9410 935furthermore 9410 936cautioned 9410 937parametrized 9410 938registration 9410 939sadly 9410 940positioning 9410 941babysitting 9410 942eternal 9410 943hoarder 9410 944congregates 9410 945rains 9410 946workers 9410 947sags 9410 948unplug 9410 949garage 9410 950boulder 9410 951specifics 9410 952Teresa 9410 953Winsett 9410 954convenient 9410 955buckboards 9410 956amenities 9410 957resplendent 9410 958sews 9410 959participated 9410 960Simon 9410 961certificates 9410 962Fitzpatrick 9410 963Evanston 9410 964misted 9410 965textures 9410 966save 9410 967count 9410 968rightful 9410 969chaperone 9410 970Lizzy 9410 971clenched 9410 972effortlessly 9410 973accessed 9410 974beaters 9410 975Hornblower 9410 976vests 9410 977indulgences 9410 978infallibly 9410 979unwilling 9410 980excrete 9410 981spools 9410 982crunches 9410 983overestimating 9410 984ineffective 9410 985humiliation 9410 986sophomore 9410 987star 9410 988rifles 9410 989dialysis 9410 990arriving 9410 991indulge 9410 992clockers 9410 993languages 9410 994Antarctica 9410 995percentage 9410 996ceiling 9410 997specification 9410 998regimented 9410 999ciphers 9410 1000pictures 9410 1001serpents 9410 1002allot 9410 1003realized 9410 1004mayoral 9410 1005opaquely 9410 1006hostess 9410 1007fiftieth 9410 1008incorrectly 9410 1009decomposition 9410 1010stranglings 9410 1011mixture 9410 1012electroencephalography 9410 1013similarities 9410 1014charges 9410 1015freest 9410 1016Greenberg 9410 1017tinting 9410 1018expelled 9410 1019warm 9410 1020smoothed 9410 1021deductions 9410 1022Romano 9410 1023bitterroot 9410 1024corset 9410 1025securing 9410 1026environing 9410 1027cute 9410 1028Crays 9410 1029heiress 9410 1030inform 9410 1031avenge 9410 1032universals 9410 1033Kinsey 9410 1034ravines 9410 1035bestseller 9410 1036equilibrium 9410 1037extents 9410 1038relatively 9410 1039pressure 9410 1040critiques 9410 1041befouled 9410 1042rightfully 9410 1043mechanizing 9410 1044Latinizes 9410 1045timesharing 9410 1046Aden 9410 1047embassies 9410 1048males 9410 1049shapelessly 9410 1050mastering 9410 1051Newtonian 9410 1052finishers 9410 1053abates 9410 1054teem 9410 1055kiting 9410 1056stodgy 9410 1057feed 9410 1058guitars 9410 1059airships 9410 1060store 9410 1061denounces 9410 1062Pyle 9410 1063Saxony 9410 1064serializations 9410 1065Peruvian 9410 1066taxonomically 9410 1067kingdom 9410 1068stint 9410 1069Sault 9410 1070faithful 9410 1071Ganymede 9410 1072tidiness 9410 1073gainful 9410 1074contrary 9410 1075Tipperary 9410 1076tropics 9410 1077theorizers 9410 1078renew 9410 1079already 9410 1080terminal 9410 1081Hegelian 9410 1082hypothesizer 9410 1083warningly 9410 1084journalizing 9410 1085nested 9410 1086Lars 9410 1087saplings 9410 1088foothill 9410 1089labeled 9410 1090imperiously 9410 1091reporters 9410 1092furnishings 9410 1093precipitable 9410 1094discounts 9410 1095excises 9410 1096Stalin 9410 1097despot 9410 1098ripeness 9410 1099Arabia 9410 1100unruly 9410 1101mournfulness 9410 1102boom 9410 1103slaughter 9410 1104Sabine 9410 1105handy 9410 1106rural 9410 1107organizer 9410 1108shipyard 9410 1109civics 9410 1110inaccuracy 9410 1111rules 9410 1112juveniles 9410 1113comprised 9410 1114investigations 9410 1115stabilizes 9410 1116seminaries 9410 1117Hunter 9410 1118sporty 9410 1119test 9410 1120weasels 9410 1121CERN 9410 1122tempering 9410 1123afore 9410 1124Galatean 9410 1125techniques 9410 1126error 9410 1127veranda 9410 1128severely 9410 1129Cassites 9410 1130forthcoming 9410 1131guides 9410 1132vanish 9410 1133lied 9410 1134sawtooth 9410 1135fated 9410 1136gradually 9410 1137widens 9410 1138preclude 9410 1139evenhandedly 9410 1140percentage 9410 1141disobedience 9410 1142humility 9410 1143gleaning 9410 1144petted 9410 1145bloater 9410 1146minion 9410 1147marginal 9410 1148apiary 9410 1149measures 9410 1150precaution 9410 1151repelled 9410 1152primary 9410 1153coverings 9410 1154Artemia 9410 1155navigate 9410 1156spatial 9410 1157Gurkha 9410 1158meanwhile 9410 1159Melinda 9410 1160Butterfield 9410 1161Aldrich 9410 1162previewing 9410 1163glut 9410 1164unaffected 9410 1165inmate 9410 1166mineral 9410 1167impending 9410 1168meditation 9410 1169ideas 9410 1170miniaturizes 9410 1171lewdly 9410 1172title 9410 1173youthfulness 9410 1174creak 9410 1175Chippewa 9410 1176clamored 9410 1177freezes 9410 1178forgivably 9410 1179reduce 9410 1180McGovern 9410 1181Nazis 9410 1182epistle 9410 1183socializes 9410 1184conceptions 9410 1185Kevin 9410 1186uncovering 9410 1187chews 9410 1188appendixes 9410 1189appendixes 9410 1190appendixes 9410 1191appendixes 9410 1192appendixes 9410 1193appendixes 9410 1194raining 9410 1195infest 9410 1196compartment 9410 1197minting 9410 1198ducks 9410 1199roped 9410 1200waltz 9410 1201Lillian 9410 1202repressions 9410 1203chillingly 9410 1204noncritical 9410 1205lithograph 9410 1206spongers 9410 1207parenthood 9410 1208posed 9410 1209instruments 9410 1210filial 9410 1211fixedly 9410 1212relives 9410 1213Pandora 9410 1214watering 9410 1215ungrateful 9410 1216secures 9410 1217poison 9410 1218dusted 9410 1219encompasses 9410 1220presentation 9410 1221Kantian 9410 1222select 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; 1223fld3 period price price2 1224admonishing 1002 28357832 8723648 1225analyzable 1002 28357832 8723648 1226annihilates 1001 5987435 234724 1227Antares 1002 28357832 8723648 1228astound 1001 5987435 234724 1229audiology 1001 5987435 234724 1230Augustine 1002 28357832 8723648 1231Baird 1002 28357832 8723648 1232bewilderingly 1001 5987435 234724 1233breaking 1001 5987435 234724 1234Conley 1001 5987435 234724 1235dentally 1002 28357832 8723648 1236dissociate 1002 28357832 8723648 1237elite 1001 5987435 234724 1238eschew 1001 5987435 234724 1239Eulerian 1001 5987435 234724 1240flanking 1001 5987435 234724 1241foldout 1002 28357832 8723648 1242funereal 1002 28357832 8723648 1243galling 1002 28357832 8723648 1244Graves 1001 5987435 234724 1245grazing 1001 5987435 234724 1246groupings 1001 5987435 234724 1247handgun 1001 5987435 234724 1248humility 1002 28357832 8723648 1249impulsive 1002 28357832 8723648 1250inch 1001 5987435 234724 1251intelligibility 1001 5987435 234724 1252jarring 1001 5987435 234724 1253lawgiver 1001 5987435 234724 1254lectured 1002 28357832 8723648 1255Merritt 1002 28357832 8723648 1256neonatal 1001 5987435 234724 1257offload 1002 28357832 8723648 1258parters 1002 28357832 8723648 1259pityingly 1002 28357832 8723648 1260puddings 1002 28357832 8723648 1261Punjab 1001 5987435 234724 1262quitter 1002 28357832 8723648 1263realtor 1001 5987435 234724 1264relaxing 1001 5987435 234724 1265repetitions 1001 5987435 234724 1266resumes 1001 5987435 234724 1267Romans 1002 28357832 8723648 1268rusting 1001 5987435 234724 1269scholastics 1001 5987435 234724 1270skulking 1002 28357832 8723648 1271stated 1002 28357832 8723648 1272suites 1002 28357832 8723648 1273sureties 1001 5987435 234724 1274testicle 1002 28357832 8723648 1275tinily 1002 28357832 8723648 1276tragedies 1001 5987435 234724 1277trimmings 1001 5987435 234724 1278vacuuming 1001 5987435 234724 1279ventilate 1001 5987435 234724 1280wallet 1001 5987435 234724 1281Weissmuller 1002 28357832 8723648 1282Wotan 1002 28357832 8723648 1283select 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; 1284fld1 fld3 period price price2 1285018201 relaxing 1001 5987435 234724 1286018601 vacuuming 1001 5987435 234724 1287018801 inch 1001 5987435 234724 1288018811 repetitions 1001 5987435 234724 1289create table t4 ( 1290companynr tinyint(2) unsigned zerofill NOT NULL default '00', 1291companyname char(30) NOT NULL default '', 1292PRIMARY KEY (companynr), 1293UNIQUE KEY companyname(companyname) 1294) ENGINE=MyISAM MAX_ROWS=50 PACK_KEYS=1 COMMENT='companynames'; 1295select STRAIGHT_JOIN t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr; 1296companynr companyname 129700 Unknown 129829 company 1 129934 company 2 130036 company 3 130137 company 4 130240 company 5 130341 company 6 130450 company 11 130553 company 7 130658 company 8 130765 company 9 130868 company 10 1309select SQL_SMALL_RESULT t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr; 1310companynr companyname 131100 Unknown 131229 company 1 131334 company 2 131436 company 3 131537 company 4 131640 company 5 131741 company 6 131850 company 11 131953 company 7 132058 company 8 132165 company 9 132268 company 10 1323select * from t1,t1 t12; 1324Period Varor_period Period Varor_period 13259410 9412 9410 9412 1326select t2.fld1,t22.fld1 from t2,t2 t22 where t2.fld1 >= 250501 and t2.fld1 <= 250505 and t22.fld1 >= 250501 and t22.fld1 <= 250505; 1327fld1 fld1 1328250501 250501 1329250502 250501 1330250503 250501 1331250504 250501 1332250505 250501 1333250501 250502 1334250502 250502 1335250503 250502 1336250504 250502 1337250505 250502 1338250501 250503 1339250502 250503 1340250503 250503 1341250504 250503 1342250505 250503 1343250501 250504 1344250502 250504 1345250503 250504 1346250504 250504 1347250505 250504 1348250501 250505 1349250502 250505 1350250503 250505 1351250504 250505 1352250505 250505 1353insert into t2 (fld1, companynr) values (999999,99); 1354select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null; 1355companynr companyname 135699 NULL 1357select count(*) from t2 left join t4 using (companynr) where t4.companynr is not null; 1358count(*) 13591199 1360explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null; 1361id select_type table type possible_keys key key_len ref rows Extra 13621 SIMPLE t2 ALL NULL NULL NULL NULL 1200 NULL 13631 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 Using where; Not exists 1364explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null; 1365id select_type table type possible_keys key key_len ref rows Extra 13661 SIMPLE t4 ALL NULL NULL NULL NULL 12 NULL 13671 SIMPLE t2 ALL NULL NULL NULL NULL 1200 Using where; Not exists; Using join buffer (Block Nested Loop) 1368select companynr,companyname from t2 left join t4 using (companynr) where companynr is null; 1369companynr companyname 1370select count(*) from t2 left join t4 using (companynr) where companynr is not null; 1371count(*) 13721200 1373explain select companynr,companyname from t2 left join t4 using (companynr) where companynr is null; 1374id select_type table type possible_keys key key_len ref rows Extra 13751 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 1376explain select companynr,companyname from t4 left join t2 using (companynr) where companynr is null; 1377id select_type table type possible_keys key key_len ref rows Extra 13781 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 1379delete from t2 where fld1=999999; 1380explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0; 1381id select_type table type possible_keys key key_len ref rows Extra 13821 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 13831 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 NULL 1384explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0; 1385id select_type table type possible_keys key key_len ref rows Extra 13861 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 13871 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 NULL 1388explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0; 1389id select_type table type possible_keys key key_len ref rows Extra 13901 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 13911 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 NULL 1392explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0; 1393id select_type table type possible_keys key key_len ref rows Extra 13941 SIMPLE t4 range PRIMARY PRIMARY 1 NULL 11 Using index condition; Using MRR 13951 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (Block Nested Loop) 1396explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0; 1397id select_type table type possible_keys key key_len ref rows Extra 13981 SIMPLE t4 range PRIMARY PRIMARY 1 NULL 12 Using index condition; Using MRR 13991 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (Block Nested Loop) 1400explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0; 1401id select_type table type possible_keys key key_len ref rows Extra 14021 SIMPLE t4 range PRIMARY PRIMARY 1 NULL 11 Using index condition; Using MRR 14031 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (Block Nested Loop) 1404explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null; 1405id select_type table type possible_keys key key_len ref rows Extra 14061 SIMPLE t4 ALL NULL NULL NULL NULL 12 NULL 14071 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (Block Nested Loop) 1408explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0 or t4.companynr > 0; 1409id select_type table type possible_keys key key_len ref rows Extra 14101 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 NULL 14111 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (Block Nested Loop) 1412explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0; 1413id select_type table type possible_keys key key_len ref rows Extra 14141 SIMPLE t4 ALL NULL NULL NULL NULL 12 NULL 14151 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (Block Nested Loop) 1416explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null; 1417id select_type table type possible_keys key key_len ref rows Extra 14181 SIMPLE t4 range PRIMARY PRIMARY 1 NULL 11 Using index condition; Using MRR 14191 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (Block Nested Loop) 1420explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0; 1421id select_type table type possible_keys key key_len ref rows Extra 14221 SIMPLE t4 range PRIMARY PRIMARY 1 NULL 12 Using index condition; Using MRR 14231 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (Block Nested Loop) 1424explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0; 1425id select_type table type possible_keys key key_len ref rows Extra 14261 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where 14271 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (Block Nested Loop) 1428select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; 1429companynr companynr 143037 36 143141 40 1432explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; 1433id select_type table type possible_keys key key_len ref rows Extra 14341 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using index; Using temporary 14351 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (Block Nested Loop) 1436select 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; 1437fld1 companynr fld3 period 1438038008 37 reporters 1008 1439038208 37 Selfridge 1008 1440select 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; 1441fld1 companynr fld3 period 1442038008 37 reporters 1008 1443038208 37 Selfridge 1008 1444select 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; 1445fld1 companynr fld3 period 1446038008 37 reporters 1008 1447038208 37 Selfridge 1008 1448select 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); 1449period 14509410 1451select 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))); 1452period 14539410 1454select 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; 1455fld1 1456250501 1457250502 1458250503 1459250505 1460select fld1 from t2 where fld1 in (250502,98005,98006,250503,250605,250606) and fld1 >=250502 and fld1 not in (250605,250606); 1461fld1 1462250502 1463250503 1464select fld1 from t2 where fld1 between 250502 and 250504; 1465fld1 1466250502 1467250503 1468250504 1469select fld3 from t2 where (((fld3 like "_%L%" ) or (fld3 like "%ok%")) and ( fld3 like "L%" or fld3 like "G%")) and fld3 like "L%" ; 1470fld3 1471label 1472labeled 1473labeled 1474landslide 1475laterally 1476leaflet 1477lewdly 1478Lillian 1479luckily 1480select count(*) from t1; 1481count(*) 14821 1483select companynr,count(*),sum(fld1) from t2 group by companynr; 1484companynr count(*) sum(fld1) 148500 82 10355753 148629 95 14473298 148734 70 17788966 148836 215 22786296 148937 588 83602098 149040 37 6618386 149141 52 12816335 149250 11 1595438 149353 4 793210 149458 23 2254293 149565 10 2284055 149668 12 3097288 1497select companynr,count(*) from t2 group by companynr order by companynr desc limit 5; 1498companynr count(*) 149968 12 150065 10 150158 23 150253 4 150350 11 1504select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>""; 1505count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1) 150670 absentee vest 17788966 254128.0857 3272.5939722090234 10709871.306938833 1507explain extended select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>""; 1508id select_type table type possible_keys key key_len ref rows filtered Extra 15091 SIMPLE t2 ALL NULL NULL NULL NULL 1199 100.00 Using where 1510Warnings: 1511Note 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` <> '')) 1512select companynr,count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 group by companynr limit 3; 1513companynr count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1) 151400 82 Anthony windmills 10355753 126289.6707 115550.97568479746 13352027981.708656 151529 95 abut wetness 14473298 152350.5053 8368.547956641249 70032594.90260443 151634 70 absentee vest 17788966 254128.0857 3272.5939722090234 10709871.306938833 1517select companynr,t2nr,count(price),sum(price),min(price),max(price),avg(price) from t3 where companynr = 37 group by companynr,t2nr limit 10; 1518companynr t2nr count(price) sum(price) min(price) max(price) avg(price) 151937 1 1 5987435 5987435 5987435 5987435.0000 152037 2 1 28357832 28357832 28357832 28357832.0000 152137 3 1 39654943 39654943 39654943 39654943.0000 152237 11 1 5987435 5987435 5987435 5987435.0000 152337 12 1 28357832 28357832 28357832 28357832.0000 152437 13 1 39654943 39654943 39654943 39654943.0000 152537 21 1 5987435 5987435 5987435 5987435.0000 152637 22 1 28357832 28357832 28357832 28357832.0000 152737 23 1 39654943 39654943 39654943 39654943.0000 152837 31 1 5987435 5987435 5987435 5987435.0000 1529select /*! 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; 1530companynr t2nr count(price) sum(price) min(price) max(price) avg(price) 153137 1 1 5987435 5987435 5987435 5987435.0000 153237 2 1 28357832 28357832 28357832 28357832.0000 153337 3 1 39654943 39654943 39654943 39654943.0000 153437 11 1 5987435 5987435 5987435 5987435.0000 153537 12 1 28357832 28357832 28357832 28357832.0000 153637 13 1 39654943 39654943 39654943 39654943.0000 153737 21 1 5987435 5987435 5987435 5987435.0000 153837 22 1 28357832 28357832 28357832 28357832.0000 153937 23 1 39654943 39654943 39654943 39654943.0000 154037 31 1 5987435 5987435 5987435 5987435.0000 1541select companynr,count(price),sum(price),min(price),max(price),avg(price) from t3 group by companynr ; 1542companynr count(price) sum(price) min(price) max(price) avg(price) 154337 12543 309394878010 5987435 39654943 24666736.6667 154478 8362 414611089292 726498 98439034 49582766.0000 1545101 4181 3489454238 834598 834598 834598.0000 1546154 4181 4112197254950 983543950 983543950 983543950.0000 1547311 4181 979599938 234298 234298 234298.0000 1548447 4181 9929180954 2374834 2374834 2374834.0000 1549512 4181 3288532102 786542 786542 786542.0000 1550select distinct mod(companynr,10) from t4 group by companynr; 1551mod(companynr,10) 15520 15539 15544 15556 15567 15571 15583 15598 15605 1561select distinct 1 from t4 group by companynr; 15621 15631 1564select count(distinct fld1) from t2; 1565count(distinct fld1) 15661199 1567select companynr,count(distinct fld1) from t2 group by companynr; 1568companynr count(distinct fld1) 156900 82 157029 95 157134 70 157236 215 157337 588 157440 37 157541 52 157650 11 157753 4 157858 23 157965 10 158068 12 1581select companynr,count(*) from t2 group by companynr; 1582companynr count(*) 158300 82 158429 95 158534 70 158636 215 158737 588 158840 37 158941 52 159050 11 159153 4 159258 23 159365 10 159468 12 1595select companynr,count(distinct concat(fld1,repeat(65,1000))) from t2 group by companynr; 1596companynr count(distinct concat(fld1,repeat(65,1000))) 159700 82 159829 95 159934 70 160036 215 160137 588 160240 37 160341 52 160450 11 160553 4 160658 23 160765 10 160868 12 1609select companynr,count(distinct concat(fld1,repeat(65,200))) from t2 group by companynr; 1610companynr count(distinct concat(fld1,repeat(65,200))) 161100 82 161229 95 161334 70 161436 215 161537 588 161640 37 161741 52 161850 11 161953 4 162058 23 162165 10 162268 12 1623select companynr,count(distinct floor(fld1/100)) from t2 group by companynr; 1624companynr count(distinct floor(fld1/100)) 162500 47 162629 35 162734 14 162836 69 162937 108 163040 16 163141 11 163250 9 163353 1 163458 1 163565 1 163668 1 1637select companynr,count(distinct concat(repeat(65,1000),floor(fld1/100))) from t2 group by companynr; 1638companynr count(distinct concat(repeat(65,1000),floor(fld1/100))) 163900 47 164029 35 164134 14 164236 69 164337 108 164440 16 164541 11 164650 9 164753 1 164858 1 164965 1 165068 1 1651select sum(fld1),fld3 from t2 where fld3="Romans" group by fld1 limit 10; 1652sum(fld1) fld3 165311402 Romans 1654select name,count(*) from t3 where name='cloakroom' group by name; 1655name count(*) 1656cloakroom 4181 1657select name,count(*) from t3 where name='cloakroom' and price>10 group by name; 1658name count(*) 1659cloakroom 4181 1660select count(*) from t3 where name='cloakroom' and price2=823742; 1661count(*) 16624181 1663select name,count(*) from t3 where name='cloakroom' and price2=823742 group by name; 1664name count(*) 1665cloakroom 4181 1666select name,count(*) from t3 where name >= "extramarital" and price <= 39654943 group by name; 1667name count(*) 1668extramarital 4181 1669gazer 4181 1670gems 4181 1671Iranizes 4181 1672spates 4181 1673tucked 4181 1674violinist 4181 1675select t2.fld3,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name; 1676fld3 count(*) 1677spates 4181 1678select companynr|0,companyname from t4 group by 1; 1679companynr|0 companyname 16800 Unknown 168129 company 1 168234 company 2 168336 company 3 168437 company 4 168540 company 5 168641 company 6 168750 company 11 168853 company 7 168958 company 8 169065 company 9 169168 company 10 1692select t2.companynr,companyname,count(*) from t2,t4 where t2.companynr=t4.companynr group by t2.companynr order by companyname; 1693companynr companyname count(*) 169429 company 1 95 169568 company 10 12 169650 company 11 11 169734 company 2 70 169836 company 3 215 169937 company 4 588 170040 company 5 37 170141 company 6 52 170253 company 7 4 170358 company 8 23 170465 company 9 10 170500 Unknown 82 1706select t2.fld1,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name; 1707fld1 count(*) 1708158402 4181 1709select sum(Period)/count(*) from t1; 1710sum(Period)/count(*) 17119410.0000 1712select 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; 1713companynr count sum diff func 171437 12543 309394878010 0.0000 464091 171578 8362 414611089292 0.0000 652236 1716101 4181 3489454238 0.0000 422281 1717154 4181 4112197254950 0.0000 643874 1718311 4181 979599938 0.0000 1300291 1719447 4181 9929180954 0.0000 1868907 1720512 4181 3288532102 0.0000 2140672 1721select companynr,sum(price)/count(price) as avg from t3 group by companynr having avg > 70000000 order by avg; 1722companynr avg 1723154 983543950.0000 1724select companynr,count(*) from t2 group by companynr order by 2 desc; 1725companynr count(*) 172637 588 172736 215 172829 95 172900 82 173034 70 173141 52 173240 37 173358 23 173468 12 173550 11 173665 10 173753 4 1738select companynr,count(*) from t2 where companynr > 40 group by companynr order by 2 desc; 1739companynr count(*) 174041 52 174158 23 174268 12 174350 11 174465 10 174553 4 1746select 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; 1747fld4 fld1 count(price) sum(price) min(price) max(price) avg(price) 1748teethe 000001 1 5987435 5987435 5987435 5987435.0000 1749dreaded 011401 1 5987435 5987435 5987435 5987435.0000 1750scholastics 011402 1 28357832 28357832 28357832 28357832.0000 1751audiology 011403 1 39654943 39654943 39654943 39654943.0000 1752wallet 011501 1 5987435 5987435 5987435 5987435.0000 1753parters 011701 1 5987435 5987435 5987435 5987435.0000 1754eschew 011702 1 28357832 28357832 28357832 28357832.0000 1755quitter 011703 1 39654943 39654943 39654943 39654943.0000 1756neat 012001 1 5987435 5987435 5987435 5987435.0000 1757Steinberg 012003 1 39654943 39654943 39654943 39654943.0000 1758balled 012301 1 5987435 5987435 5987435 5987435.0000 1759persist 012302 1 28357832 28357832 28357832 28357832.0000 1760attainments 012303 1 39654943 39654943 39654943 39654943.0000 1761capably 012501 1 5987435 5987435 5987435 5987435.0000 1762impulsive 012602 1 28357832 28357832 28357832 28357832.0000 1763starlet 012603 1 39654943 39654943 39654943 39654943.0000 1764featherweight 012701 1 5987435 5987435 5987435 5987435.0000 1765pessimist 012702 1 28357832 28357832 28357832 28357832.0000 1766daughter 012703 1 39654943 39654943 39654943 39654943.0000 1767lawgiver 013601 1 5987435 5987435 5987435 5987435.0000 1768stated 013602 1 28357832 28357832 28357832 28357832.0000 1769readable 013603 1 39654943 39654943 39654943 39654943.0000 1770testicle 013801 1 5987435 5987435 5987435 5987435.0000 1771Parsifal 013802 1 28357832 28357832 28357832 28357832.0000 1772leavings 013803 1 39654943 39654943 39654943 39654943.0000 1773squeaking 013901 1 5987435 5987435 5987435 5987435.0000 1774contrasted 016001 1 5987435 5987435 5987435 5987435.0000 1775leftover 016201 1 5987435 5987435 5987435 5987435.0000 1776whiteners 016202 1 28357832 28357832 28357832 28357832.0000 1777erases 016301 1 5987435 5987435 5987435 5987435.0000 1778Punjab 016302 1 28357832 28357832 28357832 28357832.0000 1779Merritt 016303 1 39654943 39654943 39654943 39654943.0000 1780sweetish 018001 1 5987435 5987435 5987435 5987435.0000 1781dogging 018002 1 28357832 28357832 28357832 28357832.0000 1782scornfully 018003 1 39654943 39654943 39654943 39654943.0000 1783fetters 018012 1 28357832 28357832 28357832 28357832.0000 1784bivalves 018013 1 39654943 39654943 39654943 39654943.0000 1785skulking 018021 1 5987435 5987435 5987435 5987435.0000 1786flint 018022 1 28357832 28357832 28357832 28357832.0000 1787flopping 018023 1 39654943 39654943 39654943 39654943.0000 1788Judas 018032 1 28357832 28357832 28357832 28357832.0000 1789vacuuming 018033 1 39654943 39654943 39654943 39654943.0000 1790medical 018041 1 5987435 5987435 5987435 5987435.0000 1791bloodbath 018042 1 28357832 28357832 28357832 28357832.0000 1792subschema 018043 1 39654943 39654943 39654943 39654943.0000 1793interdependent 018051 1 5987435 5987435 5987435 5987435.0000 1794Graves 018052 1 28357832 28357832 28357832 28357832.0000 1795neonatal 018053 1 39654943 39654943 39654943 39654943.0000 1796sorters 018061 1 5987435 5987435 5987435 5987435.0000 1797epistle 018062 1 28357832 28357832 28357832 28357832.0000 1798Conley 018101 1 5987435 5987435 5987435 5987435.0000 1799lectured 018102 1 28357832 28357832 28357832 28357832.0000 1800Abraham 018103 1 39654943 39654943 39654943 39654943.0000 1801cage 018201 1 5987435 5987435 5987435 5987435.0000 1802hushes 018202 1 28357832 28357832 28357832 28357832.0000 1803Simla 018402 1 28357832 28357832 28357832 28357832.0000 1804reporters 018403 1 39654943 39654943 39654943 39654943.0000 1805coexist 018601 1 5987435 5987435 5987435 5987435.0000 1806Beebe 018602 1 28357832 28357832 28357832 28357832.0000 1807Taoism 018603 1 39654943 39654943 39654943 39654943.0000 1808Connally 018801 1 5987435 5987435 5987435 5987435.0000 1809fetched 018802 1 28357832 28357832 28357832 28357832.0000 1810checkpoints 018803 1 39654943 39654943 39654943 39654943.0000 1811gritty 018811 1 5987435 5987435 5987435 5987435.0000 1812firearm 018812 1 28357832 28357832 28357832 28357832.0000 1813minima 019101 1 5987435 5987435 5987435 5987435.0000 1814Selfridge 019102 1 28357832 28357832 28357832 28357832.0000 1815disable 019103 1 39654943 39654943 39654943 39654943.0000 1816witchcraft 019201 1 5987435 5987435 5987435 5987435.0000 1817betroth 030501 1 5987435 5987435 5987435 5987435.0000 1818Manhattanize 030502 1 28357832 28357832 28357832 28357832.0000 1819imprint 030503 1 39654943 39654943 39654943 39654943.0000 1820swelling 031901 1 5987435 5987435 5987435 5987435.0000 1821interrelationships 036001 1 5987435 5987435 5987435 5987435.0000 1822riser 036002 1 28357832 28357832 28357832 28357832.0000 1823bee 038001 1 5987435 5987435 5987435 5987435.0000 1824kanji 038002 1 28357832 28357832 28357832 28357832.0000 1825dental 038003 1 39654943 39654943 39654943 39654943.0000 1826railway 038011 1 5987435 5987435 5987435 5987435.0000 1827validate 038012 1 28357832 28357832 28357832 28357832.0000 1828normalizes 038013 1 39654943 39654943 39654943 39654943.0000 1829Kline 038101 1 5987435 5987435 5987435 5987435.0000 1830Anatole 038102 1 28357832 28357832 28357832 28357832.0000 1831partridges 038103 1 39654943 39654943 39654943 39654943.0000 1832recruited 038201 1 5987435 5987435 5987435 5987435.0000 1833dimensions 038202 1 28357832 28357832 28357832 28357832.0000 1834Chicana 038203 1 39654943 39654943 39654943 39654943.0000 1835select t3.companynr,fld3,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 512 group by companynr,fld3; 1836companynr fld3 sum(price) 1837512 boat 786542 1838512 capably 786542 1839512 cupboard 786542 1840512 decliner 786542 1841512 descendants 786542 1842512 dopers 786542 1843512 erases 786542 1844512 Micronesia 786542 1845512 Miles 786542 1846512 skies 786542 1847select 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; 1848companynr count(*) min(fld3) max(fld3) sum(price) avg(price) 184900 1 Omaha Omaha 5987435 5987435.0000 185036 1 dubbed dubbed 28357832 28357832.0000 185137 83 Abraham Wotan 1908978016 22999735.1325 185250 2 scribbled tapestry 68012775 34006387.5000 1853select 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; 1854t3.companynr+0 t2nr fld3 sum(price) 185537 1 Omaha 5987435 185637 11401 breaking 5987435 185737 11402 Romans 28357832 185837 11403 intercepted 39654943 185937 11501 bewilderingly 5987435 186037 11701 astound 5987435 186137 11702 admonishing 28357832 186237 11703 sumac 39654943 186337 12001 flanking 5987435 186437 12003 combed 39654943 186537 12301 Eulerian 5987435 186637 12302 dubbed 28357832 186737 12303 Kane 39654943 186837 12501 annihilates 5987435 186937 12602 Wotan 28357832 187037 12603 snatching 39654943 187137 12701 grazing 5987435 187237 12702 Baird 28357832 187337 12703 celery 39654943 187437 13601 handgun 5987435 187537 13602 foldout 28357832 187637 13603 mystic 39654943 187737 13801 intelligibility 5987435 187837 13802 Augustine 28357832 187937 13803 teethe 39654943 188037 13901 scholastics 5987435 188137 16001 audiology 5987435 188237 16201 wallet 5987435 188337 16202 parters 28357832 188437 16301 eschew 5987435 188537 16302 quitter 28357832 188637 16303 neat 39654943 188737 18001 jarring 5987435 188837 18002 tinily 28357832 188937 18003 balled 39654943 189037 18012 impulsive 28357832 189137 18013 starlet 39654943 189237 18021 lawgiver 5987435 189337 18022 stated 28357832 189437 18023 readable 39654943 189537 18032 testicle 28357832 189637 18033 Parsifal 39654943 189737 18041 Punjab 5987435 189837 18042 Merritt 28357832 189937 18043 Quixotism 39654943 190037 18051 sureties 5987435 190137 18052 puddings 28357832 190237 18053 tapestry 39654943 190337 18061 trimmings 5987435 190437 18062 humility 28357832 190537 18101 tragedies 5987435 190637 18102 skulking 28357832 190737 18103 flint 39654943 190837 18201 relaxing 5987435 190937 18202 offload 28357832 191037 18402 suites 28357832 191137 18403 lists 39654943 191237 18601 vacuuming 5987435 191337 18602 dentally 28357832 191437 18603 humanness 39654943 191537 18801 inch 5987435 191637 18802 Weissmuller 28357832 191737 18803 irresponsibly 39654943 191837 18811 repetitions 5987435 191937 18812 Antares 28357832 192037 19101 ventilate 5987435 192137 19102 pityingly 28357832 192237 19103 interdependent 39654943 192337 19201 Graves 5987435 192437 30501 neonatal 5987435 192537 30502 scribbled 28357832 192637 30503 chafe 39654943 192737 31901 realtor 5987435 192837 36001 elite 5987435 192937 36002 funereal 28357832 193037 38001 Conley 5987435 193137 38002 lectured 28357832 193237 38003 Abraham 39654943 193337 38011 groupings 5987435 193437 38012 dissociate 28357832 193537 38013 coexist 39654943 193637 38101 rusting 5987435 193737 38102 galling 28357832 193837 38103 obliterates 39654943 193937 38201 resumes 5987435 194037 38202 analyzable 28357832 194137 38203 terminator 39654943 1942select 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; 1943sum(price) 1944234298 1945select 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; 1946fld1 sum(price) 1947038008 234298 1948explain select fld3 from t2 where 1>2 or 2>3; 1949id select_type table type possible_keys key key_len ref rows Extra 19501 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 1951explain select fld3 from t2 where fld1=fld1; 1952id select_type table type possible_keys key key_len ref rows Extra 19531 SIMPLE t2 ALL NULL NULL NULL NULL 1199 NULL 1954select companynr,fld1 from t2 HAVING fld1=250501 or fld1=250502; 1955companynr fld1 195634 250501 195734 250502 1958select companynr,fld1 from t2 WHERE fld1>=250501 HAVING fld1<=250502; 1959companynr fld1 196034 250501 196134 250502 1962select companynr,count(*) as count,sum(fld1) as sum from t2 group by companynr having count > 40 and sum/count >= 120000; 1963companynr count sum 196400 82 10355753 196529 95 14473298 196634 70 17788966 196737 588 83602098 196841 52 12816335 1969select companynr from t2 group by companynr having count(*) > 40 and sum(fld1)/count(*) >= 120000 ; 1970companynr 197100 197229 197334 197437 197541 1976select t2.companynr,companyname,count(*) from t2,t4 where t2.companynr=t4.companynr group by companyname having t2.companynr >= 40; 1977companynr companyname count(*) 197868 company 10 12 197950 company 11 11 198040 company 5 37 198141 company 6 52 198253 company 7 4 198358 company 8 23 198465 company 9 10 1985select count(*) from t2; 1986count(*) 19871199 1988select count(*) from t2 where fld1 < 098024; 1989count(*) 1990387 1991select min(fld1) from t2 where fld1>= 098024; 1992min(fld1) 199398024 1994select max(fld1) from t2 where fld1>= 098024; 1995max(fld1) 19961232609 1997select count(*) from t3 where price2=76234234; 1998count(*) 19994181 2000select count(*) from t3 where companynr=512 and price2=76234234; 2001count(*) 20024181 2003explain select min(fld1),max(fld1),count(*) from t2; 2004id select_type table type possible_keys key key_len ref rows Extra 20051 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2006select min(fld1),max(fld1),count(*) from t2; 2007min(fld1) max(fld1) count(*) 20080 1232609 1199 2009select min(t2nr),max(t2nr) from t3 where t2nr=2115 and price2=823742; 2010min(t2nr) max(t2nr) 20112115 2115 2012select count(*),min(t2nr),max(t2nr) from t3 where name='spates' and companynr=78; 2013count(*) min(t2nr) max(t2nr) 20144181 4 41804 2015select t2nr,count(*) from t3 where name='gems' group by t2nr limit 20; 2016t2nr count(*) 20179 1 201819 1 201929 1 202039 1 202149 1 202259 1 202369 1 202479 1 202589 1 202699 1 2027109 1 2028119 1 2029129 1 2030139 1 2031149 1 2032159 1 2033169 1 2034179 1 2035189 1 2036199 1 2037select max(t2nr) from t3 where price=983543950; 2038max(t2nr) 203941807 2040select t1.period from t3 = t1 limit 1; 2041period 20421001 2043select t1.period from t1 as t1 limit 1; 2044period 20459410 2046select t1.period as "Nuvarande period" from t1 as t1 limit 1; 2047Nuvarande period 20489410 2049select period as ok_period from t1 limit 1; 2050ok_period 20519410 2052select period as ok_period from t1 group by ok_period limit 1; 2053ok_period 20549410 2055select 1+1 as summa from t1 group by summa limit 1; 2056summa 20572 2058select period as "Nuvarande period" from t1 group by "Nuvarande period" limit 1; 2059Nuvarande period 20609410 2061show tables; 2062Tables_in_test 2063t1 2064t2 2065t3 2066t4 2067show tables from test like "s%"; 2068Tables_in_test (s%) 2069show tables from test like "t?"; 2070Tables_in_test (t?) 2071show full columns from t2; 2072Field Type Collation Null Key Default Extra Privileges Comment 2073auto int(11) NULL NO PRI NULL auto_increment # 2074fld1 int(6) unsigned zerofill NULL NO UNI 000000 # 2075companynr tinyint(2) unsigned zerofill NULL NO 00 # 2076fld3 char(30) latin1_swedish_ci NO MUL # 2077fld4 char(35) latin1_swedish_ci NO # 2078fld5 char(35) latin1_swedish_ci NO # 2079fld6 char(4) latin1_swedish_ci NO # 2080show full columns from t2 from test like 'f%'; 2081Field Type Collation Null Key Default Extra Privileges Comment 2082fld1 int(6) unsigned zerofill NULL NO UNI 000000 # 2083fld3 char(30) latin1_swedish_ci NO MUL # 2084fld4 char(35) latin1_swedish_ci NO # 2085fld5 char(35) latin1_swedish_ci NO # 2086fld6 char(4) latin1_swedish_ci NO # 2087show full columns from t2 from test like 's%'; 2088Field Type Collation Null Key Default Extra Privileges Comment 2089show keys from t2; 2090Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 2091t2 0 PRIMARY 1 auto A 1199 NULL NULL BTREE 2092t2 0 fld1 1 fld1 A 1199 NULL NULL BTREE 2093t2 1 fld3 1 fld3 A NULL NULL NULL BTREE 2094drop table t4, t3, t2, t1; 2095DO 1; 2096DO benchmark(100,1+1),1,1; 2097do default; 2098ERROR 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 2099do foobar; 2100ERROR 42S22: Unknown column 'foobar' in 'field list' 2101CREATE TABLE t1 ( 2102id mediumint(8) unsigned NOT NULL auto_increment, 2103pseudo varchar(35) NOT NULL default '', 2104PRIMARY KEY (id), 2105UNIQUE KEY pseudo (pseudo) 2106); 2107INSERT INTO t1 (pseudo) VALUES ('test'); 2108INSERT INTO t1 (pseudo) VALUES ('test1'); 2109SELECT 1 as rnd1 from t1 where rand() > 2; 2110rnd1 2111DROP TABLE t1; 2112CREATE 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; 2113INSERT 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); 2114CREATE 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; 2115INSERT INTO t2 VALUES (1,3,10,'2002-06-01 08:00:00',35),(1,3,1010,'2002-06-01 12:00:01',35); 2116SELECT 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; 2117gvid the_success the_fail the_size the_time 2118Warnings: 2119Warning 1292 Incorrect datetime value: 'wrong-date-value' for column 'sampletime' at row 1 2120Warning 1292 Incorrect datetime value: 'wrong-date-value' for column 'sampletime' at row 1 2121SELECT 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; 2122gvid the_success the_fail the_size the_time 2123DROP TABLE t1,t2; 2124create 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'); 2125INSERT INTO t1 VALUES (102935998719055004,'brade',1029359987,2,102935229116544068,102935229216544093); 2126select wss_type from t1 where wss_type ='102935229216544106'; 2127wss_type 2128select wss_type from t1 where wss_type ='102935229216544105'; 2129wss_type 2130select wss_type from t1 where wss_type ='102935229216544104'; 2131wss_type 2132select wss_type from t1 where wss_type ='102935229216544093'; 2133wss_type 2134102935229216544093 2135select wss_type from t1 where wss_type =102935229216544093; 2136wss_type 2137102935229216544093 2138drop table t1; 2139select 1+2,"aaaa",3.13*2.0 into @a,@b,@c; 2140select @a; 2141@a 21423 2143select @b; 2144@b 2145aaaa 2146select @c; 2147@c 21486.260 2149create table t1 (a int not null auto_increment primary key); 2150insert into t1 values (); 2151insert into t1 values (); 2152insert into t1 values (); 2153select * from (t1 as t2 left join t1 as t3 using (a)), t1; 2154a a 21551 1 21562 1 21573 1 21581 2 21592 2 21603 2 21611 3 21622 3 21633 3 2164select * from t1, (t1 as t2 left join t1 as t3 using (a)); 2165a a 21661 1 21672 1 21683 1 21691 2 21702 2 21713 2 21721 3 21732 3 21743 3 2175select * from (t1 as t2 left join t1 as t3 using (a)) straight_join t1; 2176a a 21771 1 21782 1 21793 1 21801 2 21812 2 21823 2 21831 3 21842 3 21853 3 2186select * from t1 straight_join (t1 as t2 left join t1 as t3 using (a)); 2187a a 21881 1 21892 1 21903 1 21911 2 21922 2 21933 2 21941 3 21952 3 21963 3 2197select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 on t1.a>1; 2198a a 21991 2 22002 2 22013 2 22021 3 22032 3 22043 3 2205select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1; 2206a a 22072 1 22083 1 22092 2 22103 2 22112 3 22123 3 2213select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 using ( a ); 2214a 22151 22162 22173 2218select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) using ( a ); 2219a 22201 22212 22223 2223select * from (t1 as t2 left join t1 as t3 using (a)) left outer join t1 on t1.a>1; 2224a a 22251 2 22261 3 22272 2 22282 3 22293 2 22303 3 2231select * from t1 left outer join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1; 2232a a 22331 NULL 22342 1 22352 2 22362 3 22373 1 22383 2 22393 3 2240select * from (t1 as t2 left join t1 as t3 using (a)) left join t1 using ( a ); 2241a 22421 22432 22443 2245select * from t1 left join (t1 as t2 left join t1 as t3 using (a)) using ( a ); 2246a 22471 22482 22493 2250select * from (t1 as t2 left join t1 as t3 using (a)) natural left join t1; 2251a 22521 22532 22543 2255select * from t1 natural left join (t1 as t2 left join t1 as t3 using (a)); 2256a 22571 22582 22593 2260select * from (t1 as t2 left join t1 as t3 using (a)) right join t1 on t1.a>1; 2261a a 2262NULL 1 22631 2 22642 2 22653 2 22661 3 22672 3 22683 3 2269select * from t1 right join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1; 2270a a 22712 1 22722 2 22732 3 22743 1 22753 2 22763 3 2277select * from (t1 as t2 left join t1 as t3 using (a)) right outer join t1 using ( a ); 2278a 22791 22802 22813 2282select * from t1 right outer join (t1 as t2 left join t1 as t3 using (a)) using ( a ); 2283a 22841 22852 22863 2287select * from (t1 as t2 left join t1 as t3 using (a)) natural right join t1; 2288a 22891 22902 22913 2292select * from t1 natural right join (t1 as t2 left join t1 as t3 using (a)); 2293a 22941 22952 22963 2297select * from t1 natural join (t1 as t2 left join t1 as t3 using (a)); 2298a 22991 23002 23013 2302select * from (t1 as t2 left join t1 as t3 using (a)) natural join t1; 2303a 23041 23052 23063 2307drop table t1; 2308CREATE 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; 2309INSERT INTO t1 VALUES ("1",8264,2506),("2",8299,2517),("3",8301,2518),("4",8302,2519),("5",8303,2520),("6",8304,2521),("7",8305,2522); 2310CREATE TABLE t2 ( id int(11) NOT NULL auto_increment, PRIMARY KEY (id)) ENGINE=MyISAM; 2311INSERT INTO t2 VALUES (2517), (2518), (2519), (2520), (2521), (2522); 2312select * from t1, t2 WHERE t1.t2_id = t2.id and t1.t2_id > 0 order by t1.id LIMIT 0, 5; 2313aa id t2_id id 23142 8299 2517 2517 23153 8301 2518 2518 23164 8302 2519 2519 23175 8303 2520 2520 23186 8304 2521 2521 2319drop table t1,t2; 2320create table t1 (id1 int NOT NULL); 2321create table t2 (id2 int NOT NULL); 2322create table t3 (id3 int NOT NULL); 2323create table t4 (id4 int NOT NULL, id44 int NOT NULL, KEY (id4)); 2324insert into t1 values (1); 2325insert into t1 values (2); 2326insert into t2 values (1); 2327insert into t4 values (1,1); 2328explain select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3 2329left join t4 on id3 = id4 where id2 = 1 or id4 = 1; 2330id select_type table type possible_keys key key_len ref rows Extra 23311 SIMPLE t3 system NULL NULL NULL NULL 0 const row not found 23321 SIMPLE t4 const id4 NULL NULL NULL 1 NULL 23331 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL 23341 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop) 2335select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3 2336left join t4 on id3 = id4 where id2 = 1 or id4 = 1; 2337id1 id2 id3 id4 id44 23381 1 NULL NULL NULL 2339drop table t1,t2,t3,t4; 2340create table t1(s varchar(10) not null); 2341create table t2(s varchar(10) not null primary key); 2342create table t3(s varchar(10) not null primary key); 2343insert into t1 values ('one\t'), ('two\t'); 2344insert into t2 values ('one\r'), ('two\t'); 2345insert into t3 values ('one '), ('two\t'); 2346select * from t1 where s = 'one'; 2347s 2348select * from t2 where s = 'one'; 2349s 2350select * from t3 where s = 'one'; 2351s 2352one 2353select * from t1,t2 where t1.s = t2.s; 2354s s 2355two two 2356select * from t2,t3 where t2.s = t3.s; 2357s s 2358two two 2359drop table t1, t2, t3; 2360create table t1 (a integer, b integer, index(a), index(b)); 2361create table t2 (c integer, d integer, index(c), index(d)); 2362insert into t1 values (1,2), (2,2), (3,2), (4,2); 2363insert into t2 values (1,3), (2,3), (3,4), (4,4); 2364explain select * from t1 left join t2 on a=c where d in (4); 2365id select_type table type possible_keys key key_len ref rows Extra 23661 SIMPLE t2 ref c,d d 5 const 2 NULL 23671 SIMPLE t1 ALL a NULL NULL NULL 4 Using where; Using join buffer (Block Nested Loop) 2368select * from t1 left join t2 on a=c where d in (4); 2369a b c d 23703 2 3 4 23714 2 4 4 2372explain select * from t1 left join t2 on a=c where d = 4; 2373id select_type table type possible_keys key key_len ref rows Extra 23741 SIMPLE t2 ref c,d d 5 const 2 NULL 23751 SIMPLE t1 ALL a NULL NULL NULL 4 Using where; Using join buffer (Block Nested Loop) 2376select * from t1 left join t2 on a=c where d = 4; 2377a b c d 23783 2 3 4 23794 2 4 4 2380drop table t1, t2; 2381CREATE TABLE t1 ( 2382i int(11) NOT NULL default '0', 2383c char(10) NOT NULL default '', 2384PRIMARY KEY (i), 2385UNIQUE KEY c (c) 2386) ENGINE=MyISAM; 2387INSERT INTO t1 VALUES (1,'a'); 2388INSERT INTO t1 VALUES (2,'b'); 2389INSERT INTO t1 VALUES (3,'c'); 2390EXPLAIN SELECT i FROM t1 WHERE i=1; 2391id select_type table type possible_keys key key_len ref rows Extra 23921 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index 2393DROP TABLE t1; 2394CREATE TABLE t1 ( a BLOB, INDEX (a(20)) ); 2395CREATE TABLE t2 ( a BLOB, INDEX (a(20)) ); 2396INSERT INTO t1 VALUES ('one'),('two'),('three'),('four'),('five'); 2397INSERT INTO t2 VALUES ('one'),('two'),('three'),('four'),('five'); 2398EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USE INDEX (a) ON t1.a=t2.a; 2399id select_type table type possible_keys key key_len ref rows Extra 24001 SIMPLE t1 ALL NULL NULL NULL NULL 5 NULL 24011 SIMPLE t2 ALL a NULL NULL NULL 5 Using where; Using join buffer (Block Nested Loop) 2402EXPLAIN SELECT * FROM t1 LEFT JOIN t2 FORCE INDEX (a) ON t1.a=t2.a; 2403id select_type table type possible_keys key key_len ref rows Extra 24041 SIMPLE t1 ALL NULL NULL NULL NULL 5 NULL 24051 SIMPLE t2 ref a a 23 test.t1.a 2 Using where 2406DROP TABLE t1, t2; 2407CREATE TABLE t1 ( city char(30) ); 2408INSERT INTO t1 VALUES ('London'); 2409INSERT INTO t1 VALUES ('Paris'); 2410SELECT * FROM t1 WHERE city='London'; 2411city 2412London 2413SELECT * FROM t1 WHERE city='london'; 2414city 2415London 2416EXPLAIN SELECT * FROM t1 WHERE city='London' AND city='london'; 2417id select_type table type possible_keys key key_len ref rows Extra 24181 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where 2419SELECT * FROM t1 WHERE city='London' AND city='london'; 2420city 2421London 2422EXPLAIN SELECT * FROM t1 WHERE city LIKE '%london%' AND city='London'; 2423id select_type table type possible_keys key key_len ref rows Extra 24241 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where 2425SELECT * FROM t1 WHERE city LIKE '%london%' AND city='London'; 2426city 2427London 2428DROP TABLE t1; 2429create table t1 (a int(11) unsigned, b int(11) unsigned); 2430insert into t1 values (1,0), (1,1), (18446744073709551615,0); 2431Warnings: 2432Warning 1264 Out of range value for column 'a' at row 3 2433select a-b from t1 order by 1; 2434a-b 24350 24361 24374294967295 2438select a-b , (a-b < 0) from t1 order by 1; 2439a-b (a-b < 0) 24400 0 24411 0 24424294967295 0 2443select a-b as d, (a-b >= 0), b from t1 group by b having d >= 0; 2444d (a-b >= 0) b 24451 1 0 24460 1 1 2447select cast((a - b) as unsigned) from t1 order by 1; 2448cast((a - b) as unsigned) 24490 24501 24514294967295 2452drop table t1; 2453create table t1 (a int(11)); 2454select all all * from t1; 2455a 2456select distinct distinct * from t1; 2457a 2458select all distinct * from t1; 2459ERROR HY000: Incorrect usage of ALL and DISTINCT 2460select distinct all * from t1; 2461ERROR HY000: Incorrect usage of ALL and DISTINCT 2462drop table t1; 2463CREATE TABLE t1 ( 2464kunde_intern_id int(10) unsigned NOT NULL default '0', 2465kunde_id int(10) unsigned NOT NULL default '0', 2466FK_firma_id int(10) unsigned NOT NULL default '0', 2467aktuell enum('Ja','Nein') NOT NULL default 'Ja', 2468vorname varchar(128) NOT NULL default '', 2469nachname varchar(128) NOT NULL default '', 2470geloescht enum('Ja','Nein') NOT NULL default 'Nein', 2471firma varchar(128) NOT NULL default '' 2472); 2473INSERT INTO t1 VALUES 2474(3964,3051,1,'Ja','Vorname1','1Nachname','Nein','Print Schau XXXX'), 2475(3965,3051111,1,'Ja','Vorname1111','1111Nachname','Nein','Print Schau XXXX'); 2476SELECT kunde_id ,FK_firma_id ,aktuell, vorname, nachname, geloescht FROM t1 2477WHERE 2478( 2479( 2480( '' != '' AND firma LIKE CONCAT('%', '', '%')) 2481OR 2482(vorname LIKE CONCAT('%', 'Vorname1', '%') AND 2483nachname LIKE CONCAT('%', '1Nachname', '%') AND 2484'Vorname1' != '' AND 'xxxx' != '') 2485) 2486AND 2487( 2488aktuell = 'Ja' AND geloescht = 'Nein' AND FK_firma_id = 2 2489) 2490) 2491; 2492kunde_id FK_firma_id aktuell vorname nachname geloescht 2493SELECT kunde_id ,FK_firma_id ,aktuell, vorname, nachname, 2494geloescht FROM t1 2495WHERE 2496( 2497( 2498aktuell = 'Ja' AND geloescht = 'Nein' AND FK_firma_id = 2 2499) 2500AND 2501( 2502( '' != '' AND firma LIKE CONCAT('%', '', '%') ) 2503OR 2504( vorname LIKE CONCAT('%', 'Vorname1', '%') AND 2505nachname LIKE CONCAT('%', '1Nachname', '%') AND 'Vorname1' != '' AND 2506'xxxx' != '') 2507) 2508) 2509; 2510kunde_id FK_firma_id aktuell vorname nachname geloescht 2511SELECT COUNT(*) FROM t1 WHERE 2512( 0 OR (vorname LIKE '%Vorname1%' AND nachname LIKE '%1Nachname%' AND 1)) 2513AND FK_firma_id = 2; 2514COUNT(*) 25150 2516drop table t1; 2517CREATE TABLE t1 (b BIGINT(20) UNSIGNED NOT NULL, PRIMARY KEY (b)); 2518INSERT INTO t1 VALUES (0x8000000000000000); 2519SELECT b FROM t1 WHERE b=0x8000000000000000; 2520b 25219223372036854775808 2522DROP TABLE t1; 2523CREATE TABLE `t1` ( `gid` int(11) default NULL, `uid` int(11) default NULL); 2524CREATE TABLE `t2` ( `ident` int(11) default NULL, `level` char(16) default NULL); 2525INSERT INTO `t2` VALUES (0,'READ'); 2526CREATE TABLE `t3` ( `id` int(11) default NULL, `name` char(16) default NULL); 2527INSERT INTO `t3` VALUES (1,'fs'); 2528select * from t3 left join t1 on t3.id = t1.uid, t2 where t2.ident in (0, t1.gid, t3.id, 0); 2529id name gid uid ident level 25301 fs NULL NULL 0 READ 2531drop table t1,t2,t3; 2532CREATE TABLE t1 ( 2533acct_id int(11) NOT NULL default '0', 2534profile_id smallint(6) default NULL, 2535UNIQUE KEY t1$acct_id (acct_id), 2536KEY t1$profile_id (profile_id) 2537); 2538INSERT INTO t1 VALUES (132,17),(133,18); 2539CREATE TABLE t2 ( 2540profile_id smallint(6) default NULL, 2541queue_id int(11) default NULL, 2542seq int(11) default NULL, 2543KEY t2$queue_id (queue_id) 2544); 2545INSERT INTO t2 VALUES (17,31,4),(17,30,3),(17,36,2),(17,37,1); 2546CREATE TABLE t3 ( 2547id int(11) NOT NULL default '0', 2548qtype int(11) default NULL, 2549seq int(11) default NULL, 2550warn_lvl int(11) default NULL, 2551crit_lvl int(11) default NULL, 2552rr1 tinyint(4) NOT NULL default '0', 2553rr2 int(11) default NULL, 2554default_queue tinyint(4) NOT NULL default '0', 2555KEY t3$qtype (qtype), 2556KEY t3$id (id) 2557); 2558INSERT INTO t3 VALUES (30,1,29,NULL,NULL,0,NULL,0),(31,1,28,NULL,NULL,0,NULL,0), 2559(36,1,34,NULL,NULL,0,NULL,0),(37,1,35,NULL,NULL,0,121,0); 2560SELECT COUNT(*) FROM t1 a STRAIGHT_JOIN t2 pq STRAIGHT_JOIN t3 q 2561WHERE 2562(pq.profile_id = a.profile_id) AND (a.acct_id = 132) AND 2563(pq.queue_id = q.id) AND (q.rr1 <> 1); 2564COUNT(*) 25654 2566drop table t1,t2,t3; 2567create table t1 (f1 int); 2568insert into t1 values (1),(NULL); 2569create table t2 (f2 int, f3 int, f4 int); 2570create index idx1 on t2 (f4); 2571insert into t2 values (1,2,3),(2,4,6); 2572select A.f2 from t1 left join t2 A on A.f2 = f1 where A.f3=(select min(f3) 2573from t2 C where A.f4 = C.f4) or A.f3 IS NULL; 2574f2 25751 2576NULL 2577drop table t1,t2; 2578create table t2 (a tinyint unsigned); 2579create index t2i on t2(a); 2580insert into t2 values (0), (254), (255); 2581explain select * from t2 where a > -1; 2582id select_type table type possible_keys key key_len ref rows Extra 25831 SIMPLE t2 index t2i t2i 2 NULL 3 Using where; Using index 2584select * from t2 where a > -1; 2585a 25860 2587254 2588255 2589drop table t2; 2590CREATE TABLE t1 (a int, b int, c int); 2591INSERT INTO t1 2592SELECT 50, 3, 3 FROM DUAL 2593WHERE NOT EXISTS 2594(SELECT * FROM t1 WHERE a = 50 AND b = 3); 2595SELECT * FROM t1; 2596a b c 259750 3 3 2598INSERT INTO t1 2599SELECT 50, 3, 3 FROM DUAL 2600WHERE NOT EXISTS 2601(SELECT * FROM t1 WHERE a = 50 AND b = 3); 2602select found_rows(); 2603found_rows() 26040 2605SELECT * FROM t1; 2606a b c 260750 3 3 2608select count(*) from t1; 2609count(*) 26101 2611select found_rows(); 2612found_rows() 26131 2614select count(*) from t1 limit 2,3; 2615count(*) 2616select found_rows(); 2617found_rows() 26180 2619select SQL_CALC_FOUND_ROWS count(*) from t1 limit 2,3; 2620count(*) 2621select found_rows(); 2622found_rows() 26231 2624DROP TABLE t1; 2625CREATE TABLE t1 (a INT, b INT); 2626(SELECT a, b AS c FROM t1) ORDER BY c+1; 2627a c 2628(SELECT a, b AS c FROM t1) ORDER BY b+1; 2629a c 2630SELECT a, b AS c FROM t1 ORDER BY c+1; 2631a c 2632SELECT a, b AS c FROM t1 ORDER BY b+1; 2633a c 2634drop table t1; 2635create table t1(f1 int, f2 int); 2636create table t2(f3 int); 2637select f1 from t1,t2 where f1=f2 and (f1,f2) = ((1,1)); 2638f1 2639select f1 from t1,t2 where f1=f2 and (f1,NULL) = ((1,1)); 2640f1 2641select f1 from t1,t2 where f1=f2 and (f1,f2) = ((1,NULL)); 2642f1 2643insert into t1 values(1,1),(2,null); 2644insert into t2 values(2); 2645select * from t1,t2 where f1=f3 and (f1,f2) = (2,null); 2646f1 f2 f3 2647select * from t1,t2 where f1=f3 and (f1,f2) <=> (2,null); 2648f1 f2 f3 26492 NULL 2 2650drop table t1,t2; 2651create table t1 (f1 int not null auto_increment primary key, f2 varchar(10)); 2652create table t11 like t1; 2653insert into t1 values(1,""),(2,""); 2654show table status like 't1%'; 2655Name 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 2656t1 MyISAM 10 Dynamic 2 20 X X X X X X X X latin1_swedish_ci NULL 2657t11 MyISAM 10 Dynamic 0 0 X X X X X X X X latin1_swedish_ci NULL 2658select 123 as a from t1 where f1 is null; 2659a 2660drop table t1,t11; 2661CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL, UNIQUE idx (a,b) ); 2662INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4); 2663CREATE TABLE t2 ( a INT NOT NULL, b INT NOT NULL, e INT ); 2664INSERT INTO t2 VALUES ( 1,10,1), (1,10,2), (1,11,1), (1,11,2), (1,2,1), (1,2,2),(1,2,3); 2665SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2 LEFT JOIN 2666t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY a, b, c; 2667a b c d 26681 2 1 1 26691 2 2 1 26701 2 3 1 26711 10 2 26721 11 2 2673SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2 LEFT JOIN 2674t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY t1.a, t1.b, c; 2675a b c d 26761 10 4 26771 2 1 1 26781 2 2 1 26791 2 3 1 2680SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2 LEFT JOIN 2681t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY t2.a, t2.b, c; 2682a b c d 26831 2 1 1 26841 2 2 1 26851 2 3 1 26861 10 2 26871 11 2 2688SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2,t1 2689WHERE t2.a = t1.a AND t2.b = t1.b GROUP BY a, b, c; 2690a b c d 26911 2 1 1 26921 2 2 1 26931 2 3 1 2694DROP TABLE IF EXISTS t1, t2; 2695create table t1 (f1 int primary key, f2 int); 2696create table t2 (f3 int, f4 int, primary key(f3,f4)); 2697insert into t1 values (1,1); 2698insert into t2 values (1,1),(1,2); 2699select distinct count(f2) >0 from t1 left join t2 on f1=f3 group by f1; 2700count(f2) >0 27011 2702drop table t1,t2; 2703create table t1 (f1 int,f2 int); 2704insert into t1 values(1,1); 2705create table t2 (f3 int, f4 int, primary key(f3,f4)); 2706insert into t2 values(1,1); 2707select * from t1 where f1 in (select f3 from t2 where (f3,f4)= (select f3,f4 from t2)); 2708f1 f2 27091 1 2710drop table t1,t2; 2711CREATE TABLE t1(a int, b int, c int, KEY b(b), KEY c(c)); 2712insert into t1 values (1,0,0),(2,0,0); 2713CREATE TABLE t2 (a int, b varchar(2), c varchar(2), PRIMARY KEY(a)); 2714insert into t2 values (1,'',''), (2,'',''); 2715CREATE TABLE t3 (a int, b int, PRIMARY KEY (a,b), KEY a (a), KEY b (b)); 2716insert into t3 values (1,1),(1,2); 2717explain select straight_join DISTINCT t2.a,t2.b, t1.c from t1, t3, t2 2718where (t1.c=t2.a or (t1.c=t3.a and t2.a=t3.b)) and t1.b=556476786 and 2719t2.b like '%%' order by t2.b limit 0,1; 2720id select_type table type possible_keys key key_len ref rows Extra 27211 SIMPLE t1 ref b,c b 5 const 1 Using temporary; Using filesort 27221 SIMPLE t3 index PRIMARY,a,b PRIMARY 8 NULL 2 Using index; Using join buffer (Block Nested Loop) 27231 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1) 2724DROP TABLE t1,t2,t3; 2725CREATE TABLE t1 (a int, INDEX idx(a)); 2726INSERT INTO t1 VALUES (2), (3), (1); 2727EXPLAIN SELECT * FROM t1 IGNORE INDEX (idx); 2728id select_type table type possible_keys key key_len ref rows Extra 27291 SIMPLE t1 ALL NULL NULL NULL NULL 3 NULL 2730EXPLAIN SELECT * FROM t1 IGNORE INDEX (a); 2731ERROR 42000: Key 'a' doesn't exist in table 't1' 2732EXPLAIN SELECT * FROM t1 FORCE INDEX (a); 2733ERROR 42000: Key 'a' doesn't exist in table 't1' 2734DROP TABLE t1; 2735CREATE TABLE t1 (a int, b int); 2736INSERT INTO t1 VALUES (1,1), (2,1), (4,10); 2737CREATE TABLE t2 (a int PRIMARY KEY, b int, KEY b (b)); 2738INSERT INTO t2 VALUES (1,NULL), (2,10); 2739ALTER TABLE t1 ENABLE KEYS; 2740EXPLAIN SELECT STRAIGHT_JOIN SQL_NO_CACHE COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; 2741id select_type table type possible_keys key key_len ref rows Extra 27421 SIMPLE t2 index b b 5 NULL 2 Using index 27431 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (Block Nested Loop) 2744SELECT STRAIGHT_JOIN SQL_NO_CACHE * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; 2745a b a b 27461 NULL 1 1 27471 NULL 2 1 27481 NULL 4 10 27492 10 4 10 2750EXPLAIN SELECT STRAIGHT_JOIN SQL_NO_CACHE COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; 2751id select_type table type possible_keys key key_len ref rows Extra 27521 SIMPLE t2 index b b 5 NULL 2 Using index 27531 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (Block Nested Loop) 2754SELECT STRAIGHT_JOIN SQL_NO_CACHE * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; 2755a b a b 27561 NULL 1 1 27571 NULL 2 1 27581 NULL 4 10 27592 10 4 10 2760DROP TABLE IF EXISTS t1,t2; 2761CREATE TABLE t1 (key1 float default NULL, UNIQUE KEY key1 (key1)); 2762CREATE TABLE t2 (key2 float default NULL, UNIQUE KEY key2 (key2)); 2763INSERT INTO t1 VALUES (0.3762),(0.3845),(0.6158),(0.7941); 2764INSERT INTO t2 VALUES (1.3762),(1.3845),(1.6158),(1.7941); 2765explain select max(key1) from t1 where key1 <= 0.6158; 2766id select_type table type possible_keys key key_len ref rows Extra 27671 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2768explain select max(key2) from t2 where key2 <= 1.6158; 2769id select_type table type possible_keys key key_len ref rows Extra 27701 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2771explain select min(key1) from t1 where key1 >= 0.3762; 2772id select_type table type possible_keys key key_len ref rows Extra 27731 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2774explain select min(key2) from t2 where key2 >= 1.3762; 2775id select_type table type possible_keys key key_len ref rows Extra 27761 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2777explain select max(key1), min(key2) from t1, t2 2778where key1 <= 0.6158 and key2 >= 1.3762; 2779id select_type table type possible_keys key key_len ref rows Extra 27801 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2781explain select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5; 2782id select_type table type possible_keys key key_len ref rows Extra 27831 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2784explain select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5; 2785id select_type table type possible_keys key key_len ref rows Extra 27861 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2787select max(key1) from t1 where key1 <= 0.6158; 2788max(key1) 27890.6158000230789185 2790select max(key2) from t2 where key2 <= 1.6158; 2791max(key2) 27921.6158000230789185 2793select min(key1) from t1 where key1 >= 0.3762; 2794min(key1) 27950.37619999051094055 2796select min(key2) from t2 where key2 >= 1.3762; 2797min(key2) 27981.3761999607086182 2799select max(key1), min(key2) from t1, t2 2800where key1 <= 0.6158 and key2 >= 1.3762; 2801max(key1) min(key2) 28020.6158000230789185 1.3761999607086182 2803select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5; 2804max(key1) 28050.6158000230789185 2806select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5; 2807min(key1) 28080.37619999051094055 2809DROP TABLE t1,t2; 2810CREATE TABLE t1 (i BIGINT UNSIGNED NOT NULL); 2811INSERT INTO t1 VALUES (10); 2812SELECT i='1e+01',i=1e+01, i in (1e+01,1e+01), i in ('1e+01','1e+01') FROM t1; 2813i='1e+01' i=1e+01 i in (1e+01,1e+01) i in ('1e+01','1e+01') 28141 1 1 1 2815DROP TABLE t1; 2816create table t1(a bigint unsigned, b bigint); 2817insert into t1 values (0xfffffffffffffffff, 0xfffffffffffffffff), 2818(0x10000000000000000, 0x10000000000000000), 2819(0x8fffffffffffffff, 0x8fffffffffffffff); 2820Warnings: 2821Warning 1264 Out of range value for column 'a' at row 1 2822Warning 1264 Out of range value for column 'b' at row 1 2823Warning 1264 Out of range value for column 'a' at row 2 2824Warning 1264 Out of range value for column 'b' at row 2 2825Warning 1264 Out of range value for column 'b' at row 3 2826select hex(a), hex(b) from t1; 2827hex(a) hex(b) 2828FFFFFFFFFFFFFFFF 7FFFFFFFFFFFFFFF 2829FFFFFFFFFFFFFFFF 7FFFFFFFFFFFFFFF 28308FFFFFFFFFFFFFFF 7FFFFFFFFFFFFFFF 2831drop table t1; 2832CREATE TABLE t1 (c0 int); 2833CREATE TABLE t2 (c0 int); 2834INSERT INTO t1 VALUES(@@connect_timeout); 2835INSERT INTO t2 VALUES(@@connect_timeout); 2836SELECT * FROM t1 JOIN t2 ON t1.c0 = t2.c0 WHERE (t1.c0 <=> @@connect_timeout); 2837c0 c0 2838X X 2839DROP TABLE t1, t2; 2840End of 4.1 tests 2841CREATE TABLE t1 ( 2842K2C4 varchar(4) character set latin1 collate latin1_bin NOT NULL default '', 2843K4N4 varchar(4) character set latin1 collate latin1_bin NOT NULL default '0000', 2844F2I4 int(11) NOT NULL default '0' 2845) ENGINE=MyISAM DEFAULT CHARSET=latin1; 2846INSERT INTO t1 VALUES 2847('W%RT', '0100', 1), 2848('W-RT', '0100', 1), 2849('WART', '0100', 1), 2850('WART', '0200', 1), 2851('WERT', '0100', 2), 2852('WORT','0200', 2), 2853('WT', '0100', 2), 2854('W_RT', '0100', 2), 2855('WaRT', '0100', 3), 2856('WART', '0300', 3), 2857('WRT' , '0400', 3), 2858('WURM', '0500', 3), 2859('W%T', '0600', 4), 2860('WA%T', '0700', 4), 2861('WA_T', '0800', 4); 2862SELECT K2C4, K4N4, F2I4 FROM t1 2863WHERE K2C4 = 'WART' AND 2864(F2I4 = 2 AND K2C4 = 'WART' OR (F2I4 = 2 OR K4N4 = '0200')); 2865K2C4 K4N4 F2I4 2866WART 0200 1 2867SELECT K2C4, K4N4, F2I4 FROM t1 2868WHERE K2C4 = 'WART' AND (K2C4 = 'WART' OR K4N4 = '0200'); 2869K2C4 K4N4 F2I4 2870WART 0100 1 2871WART 0200 1 2872WART 0300 3 2873DROP TABLE t1; 2874create table t1 (a int, b int); 2875create table t2 like t1; 2876select t1.a from (t1 inner join t2 on t1.a=t2.a) where t2.a=1; 2877a 2878select t1.a from ((t1 inner join t2 on t1.a=t2.a)) where t2.a=1; 2879a 2880select 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; 2881a a a 2882drop table t1,t2; 2883create table t1 (s1 varchar(5)); 2884insert into t1 values ('Wall'); 2885select min(s1) from t1 group by s1 with rollup; 2886min(s1) 2887Wall 2888Wall 2889drop table t1; 2890create table t1 (s1 int) engine=myisam; 2891insert into t1 values (0); 2892select avg(distinct s1) from t1 group by s1 with rollup; 2893avg(distinct s1) 28940.0000 28950.0000 2896drop table t1; 2897create table t1 (s1 int); 2898insert into t1 values (null),(1); 2899select avg(s1) as x from t1 group by s1 with rollup; 2900x 2901NULL 29021.0000 29031.0000 2904select distinct avg(s1) as x from t1 group by s1 with rollup; 2905ERROR HY000: Incorrect usage of WITH ROLLUP and DISTINCT 2906drop table t1; 2907CREATE TABLE t1 (a int); 2908CREATE TABLE t2 (a int); 2909INSERT INTO t1 VALUES (1), (2), (3), (4), (5); 2910INSERT INTO t2 VALUES (2), (4), (6); 2911SELECT t1.a FROM t1 STRAIGHT_JOIN t2 ON t1.a=t2.a; 2912a 29132 29144 2915EXPLAIN SELECT t1.a FROM t1 STRAIGHT_JOIN t2 ON t1.a=t2.a; 2916id select_type table type possible_keys key key_len ref rows Extra 29171 SIMPLE t1 ALL NULL NULL NULL NULL 5 NULL 29181 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (Block Nested Loop) 2919EXPLAIN SELECT t1.a FROM t1 INNER JOIN t2 ON t1.a=t2.a; 2920id select_type table type possible_keys key key_len ref rows Extra 29211 SIMPLE t2 ALL NULL NULL NULL NULL 3 NULL 29221 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (Block Nested Loop) 2923DROP TABLE t1,t2; 2924select x'10' + 0, X'10' + 0, b'10' + 0, B'10' + 0; 2925x'10' + 0 X'10' + 0 b'10' + 0 B'10' + 0 292616 16 2 2 2927create table t1 (f1 varchar(6) default NULL, f2 int(6) primary key not null); 2928create table t2 (f3 varchar(5) not null, f4 varchar(5) not null, UNIQUE KEY UKEY (f3,f4)); 2929insert into t1 values (" 2", 2); 2930insert into t2 values (" 2", " one "),(" 2", " two "); 2931select * from t1 left join t2 on f1 = f3; 2932f1 f2 f3 f4 2933 2 2 2 one 2934 2 2 2 two 2935drop table t1,t2; 2936create table t1 (empnum smallint, grp int); 2937create table t2 (empnum int, name char(5)); 2938insert into t1 values(1,1); 2939insert into t2 values(1,'bob'); 2940create view v1 as select * from t2 inner join t1 using (empnum); 2941select * from v1; 2942empnum name grp 29431 bob 1 2944drop table t1,t2; 2945drop view v1; 2946create table t1 (pk int primary key, b int); 2947create table t2 (pk int primary key, c int); 2948select pk from t1 inner join t2 using (pk); 2949pk 2950drop table t1,t2; 2951create table t1 (s1 int, s2 char(5), s3 decimal(10)); 2952create view v1 as select s1, s2, 'x' as s3 from t1; 2953select * from t1 natural join v1; 2954s1 s2 s3 2955insert into t1 values (1,'x',5); 2956select * from t1 natural join v1; 2957s1 s2 s3 2958Warnings: 2959Warning 1292 Truncated incorrect DOUBLE value: 'x' 2960drop table t1; 2961drop view v1; 2962create table t1(a1 int); 2963create table t2(a2 int); 2964insert into t1 values(1),(2); 2965insert into t2 values(1),(2); 2966create view v2 (c) as select a1 from t1; 2967select * from t1 natural left join t2; 2968a1 a2 29691 1 29701 2 29712 1 29722 2 2973select * from t1 natural right join t2; 2974a2 a1 29751 1 29761 2 29772 1 29782 2 2979select * from v2 natural left join t2; 2980c a2 29811 1 29821 2 29832 1 29842 2 2985select * from v2 natural right join t2; 2986a2 c 29871 1 29881 2 29892 1 29902 2 2991drop table t1, t2; 2992drop view v2; 2993create table t1 (a int(10), t1_val int(10)); 2994create table t2 (b int(10), t2_val int(10)); 2995create table t3 (a int(10), b int(10)); 2996insert into t1 values (1,1),(2,2); 2997insert into t2 values (1,1),(2,2),(3,3); 2998insert into t3 values (1,1),(2,1),(3,1),(4,1); 2999select * from t1 natural join t2 natural join t3; 3000a b t1_val t2_val 30011 1 1 1 30022 1 2 1 3003select * from t1 natural join t3 natural join t2; 3004b a t1_val t2_val 30051 1 1 1 30061 2 2 1 3007drop table t1, t2, t3; 3008DO IFNULL(NULL, NULL); 3009SELECT CAST(IFNULL(NULL, NULL) AS DECIMAL); 3010CAST(IFNULL(NULL, NULL) AS DECIMAL) 3011NULL 3012SELECT ABS(IFNULL(NULL, NULL)); 3013ABS(IFNULL(NULL, NULL)) 3014NULL 3015SELECT IFNULL(NULL, NULL); 3016IFNULL(NULL, NULL) 3017NULL 3018SET @OLD_SQL_MODE12595=@@SQL_MODE, @@SQL_MODE=''; 3019SHOW LOCAL VARIABLES LIKE 'SQL_MODE'; 3020Variable_name Value 3021sql_mode 3022CREATE TABLE BUG_12595(a varchar(100)); 3023INSERT INTO BUG_12595 VALUES ('hakan%'), ('hakank'), ("ha%an"); 3024SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\%'; 3025a 3026hakan% 3027SELECT * FROM BUG_12595 WHERE a LIKE 'hakan*%' ESCAPE '*'; 3028a 3029hakan% 3030SELECT * FROM BUG_12595 WHERE a LIKE 'hakan**%' ESCAPE '**'; 3031ERROR HY000: Incorrect arguments to ESCAPE 3032SELECT * FROM BUG_12595 WHERE a LIKE 'hakan%' ESCAPE ''; 3033a 3034hakan% 3035hakank 3036SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\%' ESCAPE ''; 3037a 3038SELECT * FROM BUG_12595 WHERE a LIKE 'ha\%an' ESCAPE 0x5c; 3039a 3040ha%an 3041SELECT * FROM BUG_12595 WHERE a LIKE 'ha%%an' ESCAPE '%'; 3042a 3043ha%an 3044SELECT * FROM BUG_12595 WHERE a LIKE 'ha\%an' ESCAPE '\\'; 3045a 3046ha%an 3047SELECT * FROM BUG_12595 WHERE a LIKE 'ha|%an' ESCAPE '|'; 3048a 3049ha%an 3050SET @@SQL_MODE='NO_BACKSLASH_ESCAPES'; 3051SHOW LOCAL VARIABLES LIKE 'SQL_MODE'; 3052Variable_name Value 3053sql_mode NO_BACKSLASH_ESCAPES 3054SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\%'; 3055a 3056SELECT * FROM BUG_12595 WHERE a LIKE 'hakan*%' ESCAPE '*'; 3057a 3058hakan% 3059SELECT * FROM BUG_12595 WHERE a LIKE 'hakan**%' ESCAPE '**'; 3060ERROR HY000: Incorrect arguments to ESCAPE 3061SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\%' ESCAPE '\\'; 3062ERROR HY000: Incorrect arguments to ESCAPE 3063SELECT * FROM BUG_12595 WHERE a LIKE 'hakan%' ESCAPE ''; 3064ERROR HY000: Incorrect arguments to ESCAPE 3065SELECT * FROM BUG_12595 WHERE a LIKE 'ha\%an' ESCAPE 0x5c; 3066a 3067ha%an 3068SELECT * FROM BUG_12595 WHERE a LIKE 'ha|%an' ESCAPE '|'; 3069a 3070ha%an 3071SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\n%' ESCAPE '\n'; 3072ERROR HY000: Incorrect arguments to ESCAPE 3073SET @@SQL_MODE=@OLD_SQL_MODE12595; 3074DROP TABLE BUG_12595; 3075create table t1 (a char(1)); 3076create table t2 (a char(1)); 3077insert into t1 values ('a'),('b'),('c'); 3078insert into t2 values ('b'),('c'),('d'); 3079select a from t1 natural join t2; 3080a 3081b 3082c 3083select * from t1 natural join t2 where a = 'b'; 3084a 3085b 3086drop table t1, t2; 3087CREATE TABLE t1 (`id` TINYINT); 3088CREATE TABLE t2 (`id` TINYINT); 3089CREATE TABLE t3 (`id` TINYINT); 3090INSERT INTO t1 VALUES (1),(2),(3); 3091INSERT INTO t2 VALUES (2); 3092INSERT INTO t3 VALUES (3); 3093SELECT t1.id,t3.id FROM t1 JOIN t2 ON (t2.id=t1.id) LEFT JOIN t3 USING (id); 3094ERROR 23000: Column 'id' in from clause is ambiguous 3095SELECT t1.id,t3.id FROM t1 JOIN t2 ON (t2.notacolumn=t1.id) LEFT JOIN t3 USING (id); 3096ERROR 23000: Column 'id' in from clause is ambiguous 3097SELECT id,t3.id FROM t1 JOIN t2 ON (t2.id=t1.id) LEFT JOIN t3 USING (id); 3098ERROR 23000: Column 'id' in from clause is ambiguous 3099SELECT id,t3.id FROM (t1 JOIN t2 ON (t2.id=t1.id)) LEFT JOIN t3 USING (id); 3100ERROR 23000: Column 'id' in from clause is ambiguous 3101drop table t1, t2, t3; 3102create table t1 (a int(10),b int(10)); 3103create table t2 (a int(10),b int(10)); 3104insert into t1 values (1,10),(2,20),(3,30); 3105insert into t2 values (1,10); 3106select * from t1 inner join t2 using (A); 3107a b b 31081 10 10 3109select * from t1 inner join t2 using (a); 3110a b b 31111 10 10 3112drop table t1, t2; 3113create table t1 (a int, c int); 3114create table t2 (b int); 3115create table t3 (b int, a int); 3116create table t4 (c int); 3117insert into t1 values (1,1); 3118insert into t2 values (1); 3119insert into t3 values (1,1); 3120insert into t4 values (1); 3121select * from t1 join t2 join t3 on (t2.b = t3.b and t1.a = t3.a); 3122a c b b a 31231 1 1 1 1 3124select * from t1, t2 join t3 on (t2.b = t3.b and t1.a = t3.a); 3125ERROR 42S22: Unknown column 't1.a' in 'on clause' 3126select * from t1 join t2 join t3 join t4 on (t1.a = t4.c and t2.b = t4.c); 3127a c b b a c 31281 1 1 1 1 1 3129select * from t1 join t2 join t4 using (c); 3130c a b 31311 1 1 3132drop table t1, t2, t3, t4; 3133create table t1(x int, y int); 3134create table t2(x int, y int); 3135create table t3(x int, primary key(x)); 3136insert into t1 values (1, 1), (2, 1), (3, 1), (4, 3), (5, 6), (6, 6); 3137insert into t2 values (1, 1), (2, 1), (3, 3), (4, 6), (5, 6); 3138insert into t3 values (1), (2), (3), (4), (5); 3139select t1.x, t3.x from t1, t2, t3 where t1.x = t2.x and t3.x >= t1.y and t3.x <= t2.y; 3140x x 31411 1 31422 1 31433 1 31443 2 31453 3 31464 3 31474 4 31484 5 3149drop table t1,t2,t3; 3150create table t1 (id char(16) not null default '', primary key (id)); 3151insert into t1 values ('100'),('101'),('102'); 3152create table t2 (id char(16) default null); 3153insert into t2 values (1); 3154create view v1 as select t1.id from t1; 3155create view v2 as select t2.id from t2; 3156create view v3 as select (t1.id+2) as id from t1 natural left join t2; 3157select t1.id from t1 left join v2 using (id); 3158id 3159100 3160101 3161102 3162select t1.id from v2 right join t1 using (id); 3163id 3164100 3165101 3166102 3167select t1.id from t1 left join v3 using (id); 3168id 3169100 3170101 3171102 3172select * from t1 left join v2 using (id); 3173id 3174100 3175101 3176102 3177select * from v2 right join t1 using (id); 3178id 3179100 3180101 3181102 3182select * from t1 left join v3 using (id); 3183id 3184100 3185101 3186102 3187select v1.id from v1 left join v2 using (id); 3188id 3189100 3190101 3191102 3192select v1.id from v2 right join v1 using (id); 3193id 3194100 3195101 3196102 3197select v1.id from v1 left join v3 using (id); 3198id 3199100 3200101 3201102 3202select * from v1 left join v2 using (id); 3203id 3204100 3205101 3206102 3207select * from v2 right join v1 using (id); 3208id 3209100 3210101 3211102 3212select * from v1 left join v3 using (id); 3213id 3214100 3215101 3216102 3217drop table t1, t2; 3218drop view v1, v2, v3; 3219create table t1 (id int(11) not null default '0'); 3220insert into t1 values (123),(191),(192); 3221create table t2 (id char(16) character set utf8 not null); 3222insert into t2 values ('58013'),('58014'),('58015'),('58016'); 3223create table t3 (a_id int(11) not null, b_id char(16) character set utf8); 3224insert into t3 values (123,null),(123,null),(123,null),(123,null),(123,null),(123,'58013'); 3225select count(*) 3226from t1 inner join (t3 left join t2 on t2.id = t3.b_id) on t1.id = t3.a_id; 3227count(*) 32286 3229select count(*) 3230from t1 inner join (t2 right join t3 on t2.id = t3.b_id) on t1.id = t3.a_id; 3231count(*) 32326 3233drop table t1,t2,t3; 3234create table t1 (a int); 3235create table t2 (b int); 3236create table t3 (c int); 3237select * from t1 join t2 join t3 on (t1.a=t3.c); 3238a b c 3239select * from t1 join t2 left join t3 on (t1.a=t3.c); 3240a b c 3241select * from t1 join t2 right join t3 on (t1.a=t3.c); 3242a b c 3243select * from t1 join t2 straight_join t3 on (t1.a=t3.c); 3244a b c 3245drop table t1, t2 ,t3; 3246create table t1(f1 int, f2 date); 3247insert into t1 values(1,'2005-01-01'),(2,'2005-09-01'),(3,'2005-09-30'), 3248(4,'2005-10-01'),(5,'2005-12-30'); 3249select * from t1 where f2 >= 0 order by f2; 3250f1 f2 32511 2005-01-01 32522 2005-09-01 32533 2005-09-30 32544 2005-10-01 32555 2005-12-30 3256select * from t1 where f2 >= '0000-00-00' order by f2; 3257f1 f2 32581 2005-01-01 32592 2005-09-01 32603 2005-09-30 32614 2005-10-01 32625 2005-12-30 3263select * from t1 where f2 >= '2005-09-31' order by f2; 3264f1 f2 32654 2005-10-01 32665 2005-12-30 3267select * from t1 where f2 >= '2005-09-3a' order by f2; 3268f1 f2 32693 2005-09-30 32704 2005-10-01 32715 2005-12-30 3272Warnings: 3273Warning 1292 Incorrect date value: '2005-09-3a' for column 'f2' at row 1 3274select * from t1 where f2 <= '2005-09-31' order by f2; 3275f1 f2 32761 2005-01-01 32772 2005-09-01 32783 2005-09-30 3279select * from t1 where f2 <= '2005-09-3a' order by f2; 3280f1 f2 32811 2005-01-01 32822 2005-09-01 3283Warnings: 3284Warning 1292 Incorrect date value: '2005-09-3a' for column 'f2' at row 1 3285drop table t1; 3286create table t1 (f1 int, f2 int); 3287insert into t1 values (1, 30), (2, 20), (3, 10); 3288create algorithm=merge view v1 as select f1, f2 from t1; 3289create algorithm=merge view v2 (f2, f1) as select f1, f2 from t1; 3290create algorithm=merge view v3 as select t1.f1 as f2, t1.f2 as f1 from t1; 3291select t1.f1 as x1, f1 from t1 order by t1.f1; 3292x1 f1 32931 1 32942 2 32953 3 3296select v1.f1 as x1, f1 from v1 order by v1.f1; 3297x1 f1 32981 1 32992 2 33003 3 3301select v2.f1 as x1, f1 from v2 order by v2.f1; 3302x1 f1 330310 10 330420 20 330530 30 3306select v3.f1 as x1, f1 from v3 order by v3.f1; 3307x1 f1 330810 10 330920 20 331030 30 3311select f1, f2, v1.f1 as x1 from v1 order by v1.f1; 3312f1 f2 x1 33131 30 1 33142 20 2 33153 10 3 3316select f1, f2, v2.f1 as x1 from v2 order by v2.f1; 3317f1 f2 x1 331810 3 10 331920 2 20 332030 1 30 3321select f1, f2, v3.f1 as x1 from v3 order by v3.f1; 3322f1 f2 x1 332310 3 10 332420 2 20 332530 1 30 3326drop table t1; 3327drop view v1, v2, v3; 3328CREATE TABLE t1(key_a int4 NOT NULL, optimus varchar(32), PRIMARY KEY(key_a)); 3329CREATE TABLE t2(key_a int4 NOT NULL, prime varchar(32), PRIMARY KEY(key_a)); 3330CREATE table t3(key_a int4 NOT NULL, key_b int4 NOT NULL, foo varchar(32), 3331PRIMARY KEY(key_a,key_b)); 3332INSERT INTO t1 VALUES (0,''); 3333INSERT INTO t1 VALUES (1,'i'); 3334INSERT INTO t1 VALUES (2,'j'); 3335INSERT INTO t1 VALUES (3,'k'); 3336INSERT INTO t2 VALUES (1,'r'); 3337INSERT INTO t2 VALUES (2,'s'); 3338INSERT INTO t2 VALUES (3,'t'); 3339INSERT INTO t3 VALUES (1,5,'x'); 3340INSERT INTO t3 VALUES (1,6,'y'); 3341INSERT INTO t3 VALUES (2,5,'xx'); 3342INSERT INTO t3 VALUES (2,6,'yy'); 3343INSERT INTO t3 VALUES (2,7,'zz'); 3344INSERT INTO t3 VALUES (3,5,'xxx'); 3345SELECT t2.key_a,foo 3346FROM t1 INNER JOIN t2 ON t1.key_a = t2.key_a 3347INNER JOIN t3 ON t1.key_a = t3.key_a 3348WHERE t2.key_a=2 and key_b=5; 3349key_a foo 33502 xx 3351EXPLAIN SELECT t2.key_a,foo 3352FROM t1 INNER JOIN t2 ON t1.key_a = t2.key_a 3353INNER JOIN t3 ON t1.key_a = t3.key_a 3354WHERE t2.key_a=2 and key_b=5; 3355id select_type table type possible_keys key key_len ref rows Extra 33561 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index 33571 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 Using index 33581 SIMPLE t3 const PRIMARY PRIMARY 8 const,const 1 NULL 3359SELECT t2.key_a,foo 3360FROM t1 INNER JOIN t2 ON t2.key_a = t1.key_a 3361INNER JOIN t3 ON t1.key_a = t3.key_a 3362WHERE t2.key_a=2 and key_b=5; 3363key_a foo 33642 xx 3365EXPLAIN SELECT t2.key_a,foo 3366FROM t1 INNER JOIN t2 ON t2.key_a = t1.key_a 3367INNER JOIN t3 ON t1.key_a = t3.key_a 3368WHERE t2.key_a=2 and key_b=5; 3369id select_type table type possible_keys key key_len ref rows Extra 33701 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index 33711 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 Using index 33721 SIMPLE t3 const PRIMARY PRIMARY 8 const,const 1 NULL 3373DROP TABLE t1,t2,t3; 3374create table t1 (f1 int); 3375insert into t1 values(1),(2); 3376create table t2 (f2 int, f3 int, key(f2)); 3377insert into t2 values(1,1),(2,2); 3378create table t3 (f4 int not null); 3379insert into t3 values (2),(2),(2); 3380select f1,(select count(*) from t2,t3 where f2=f1 and f3=f4) as count from t1; 3381f1 count 33821 0 33832 3 3384drop table t1,t2,t3; 3385create table t1 (f1 int unique); 3386create table t2 (f2 int unique); 3387create table t3 (f3 int unique); 3388insert into t1 values(1),(2); 3389insert into t2 values(1),(2); 3390insert into t3 values(1),(NULL); 3391select * from t3 where f3 is null; 3392f3 3393NULL 3394select t2.f2 from t1 left join t2 on f1=f2 join t3 on f1=f3 where f1=1; 3395f2 33961 3397drop table t1,t2,t3; 3398create table t1(f1 char, f2 char not null); 3399insert into t1 values(null,'a'); 3400create table t2 (f2 char not null); 3401insert into t2 values('b'); 3402select * from t1 left join t2 on f1=t2.f2 where t1.f2='a'; 3403f1 f2 f2 3404NULL a NULL 3405drop table t1,t2; 3406select * from (select * left join t on f1=f2) tt; 3407ERROR 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 3408CREATE TABLE t1 (sku int PRIMARY KEY, pr int); 3409CREATE TABLE t2 (sku int PRIMARY KEY, sppr int, name varchar(255)); 3410INSERT INTO t1 VALUES 3411(10, 10), (20, 10), (30, 20), (40, 30), (50, 10), (60, 10); 3412INSERT INTO t2 VALUES 3413(10, 10, 'aaa'), (20, 10, 'bbb'), (30, 10, 'ccc'), (40, 20, 'ddd'), 3414(50, 10, 'eee'), (60, 20, 'fff'), (70, 20, 'ggg'), (80, 30, 'hhh'); 3415SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr 3416FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku); 3417sku sppr name sku pr 341820 10 bbb 10 10 341920 10 bbb 20 10 3420EXPLAIN 3421SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr 3422FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku); 3423id select_type table type possible_keys key key_len ref rows Extra 34241 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 NULL 34251 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using MRR 3426DROP TABLE t1,t2; 3427SET SQL_MODE='NO_UNSIGNED_SUBTRACTION'; 3428CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL); 3429INSERT t1 SET i = 0; 3430UPDATE t1 SET i = -1; 3431Warnings: 3432Warning 1264 Out of range value for column 'i' at row 1 3433SELECT * FROM t1; 3434i 34350 3436UPDATE t1 SET i = CAST(i - 1 AS SIGNED); 3437Warnings: 3438Warning 1264 Out of range value for column 'i' at row 1 3439SELECT * FROM t1; 3440i 34410 3442UPDATE t1 SET i = i - 1; 3443Warnings: 3444Warning 1264 Out of range value for column 'i' at row 1 3445SELECT * FROM t1; 3446i 34470 3448DROP TABLE t1; 3449SET SQL_MODE=default; 3450create table t1 (a int); 3451insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 3452create table t2 (a int, b int, c int, e int, primary key(a,b,c)); 3453insert into t2 select A.a, B.a, C.a, C.a from t1 A, t1 B, t1 C; 3454analyze table t2; 3455Table Op Msg_type Msg_text 3456test.t2 analyze status OK 3457select 'In next EXPLAIN, B.rows must be exactly 10:' Z; 3458Z 3459In next EXPLAIN, B.rows must be exactly 10: 3460explain select * from t2 A, t2 B where A.a=5 and A.b=5 and A.C<5 3461and B.a=5 and B.b=A.e and (B.b =1 or B.b = 3 or B.b=5); 3462id select_type table type possible_keys key key_len ref rows Extra 34631 SIMPLE A range PRIMARY PRIMARY 12 NULL 4 Using index condition; Using where; Using MRR 34641 SIMPLE B ref PRIMARY PRIMARY 8 const,test.A.e 10 NULL 3465drop table t1, t2; 3466CREATE TABLE t1 (a int PRIMARY KEY, b int, INDEX(b)); 3467INSERT INTO t1 VALUES (1, 3), (9,4), (7,5), (4,5), (6,2), 3468(3,1), (5,1), (8,9), (2,2), (0,9); 3469CREATE TABLE t2 (c int, d int, f int, INDEX(c,f)); 3470INSERT INTO t2 VALUES 3471(1,0,0), (1,0,1), (2,0,0), (2,0,1), (3,0,0), (4,0,1), 3472(5,0,0), (5,0,1), (6,0,0), (0,0,1), (7,0,0), (7,0,1), 3473(0,0,0), (0,0,1), (8,0,0), (8,0,1), (9,0,0), (9,0,1); 3474EXPLAIN 3475SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6; 3476id select_type table type possible_keys key key_len ref rows Extra 34771 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using index condition; Using MRR 34781 SIMPLE t2 ref c c 5 test.t1.a 2 NULL 3479EXPLAIN 3480SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6 AND a > 0; 3481id select_type table type possible_keys key key_len ref rows Extra 34821 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using index condition; Using where; Using MRR 34831 SIMPLE t2 ref c c 5 test.t1.a 2 NULL 3484DROP TABLE t1, t2; 3485create table t1 ( 3486a int unsigned not null auto_increment primary key, 3487b bit not null, 3488c bit not null 3489); 3490create table t2 ( 3491a int unsigned not null auto_increment primary key, 3492b bit not null, 3493c int unsigned not null, 3494d varchar(50) 3495); 3496insert into t1 (b,c) values (0,1), (0,1); 3497insert into t2 (b,c) values (0,1); 3498select t1.a, t1.b + 0, t1.c + 0, t2.a, t2.b + 0, t2.c, t2.d 3499from t1 left outer join t2 on t1.a = t2.c and t2.b <> 1 3500where t1.b <> 1 order by t1.a; 3501a t1.b + 0 t1.c + 0 a t2.b + 0 c d 35021 0 1 1 0 1 NULL 35032 0 1 NULL NULL NULL NULL 3504drop table t1,t2; 3505SELECT 0.9888889889 * 1.011111411911; 35060.9888889889 * 1.011111411911 35070.9998769417899202067879 3508prepare stmt from 'select 1 as " a "'; 3509Warnings: 3510Warning 1466 Leading spaces are removed from name ' a ' 3511execute stmt; 3512a 35131 3514CREATE TABLE t1 (a int NOT NULL PRIMARY KEY, b int NOT NULL); 3515INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4); 3516CREATE TABLE t2 (c int NOT NULL, INDEX idx(c)); 3517INSERT INTO t2 VALUES 3518(1), (1), (1), (1), (1), (1), (1), (1), 3519(2), (2), (2), (2), 3520(3), (3), 3521(4); 3522EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=1; 3523id select_type table type possible_keys key key_len ref rows Extra 35241 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 NULL 35251 SIMPLE t2 ref idx idx 4 const 7 Using index 3526EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=4; 3527id select_type table type possible_keys key key_len ref rows Extra 35281 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 NULL 35291 SIMPLE t2 ref idx idx 4 const 1 Using index 3530DROP TABLE t1, t2; 3531CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, a int); 3532INSERT INTO t1 VALUES (1,2), (2,NULL), (3,2); 3533CREATE TABLE t2 (b int, c INT, INDEX idx1(b)); 3534INSERT INTO t2 VALUES (2,1), (3,2); 3535CREATE TABLE t3 (d int, e int, INDEX idx1(d)); 3536INSERT INTO t3 VALUES (2,10), (2,20), (1,30), (2,40), (2,50); 3537EXPLAIN 3538SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id 3539WHERE t1.id=2; 3540id select_type table type possible_keys key key_len ref rows Extra 35411 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 NULL 35421 SIMPLE t2 const idx1 NULL NULL NULL 1 NULL 35431 SIMPLE t3 ref idx1 idx1 5 const 3 NULL 3544SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id 3545WHERE t1.id=2; 3546id a b c d e 35472 NULL NULL NULL 2 10 35482 NULL NULL NULL 2 20 35492 NULL NULL NULL 2 40 35502 NULL NULL NULL 2 50 3551DROP TABLE t1,t2,t3; 3552create table t1 (c1 varchar(1), c2 int, c3 int, c4 int, c5 int, c6 int, 3553c7 int, c8 int, c9 int, fulltext key (`c1`)); 3554select distinct match (`c1`) against ('z') , c2, c3, c4,c5, c6,c7, c8 3555from t1 where c9=1 order by c2, c2; 3556match (`c1`) against ('z') c2 c3 c4 c5 c6 c7 c8 3557drop table t1; 3558CREATE TABLE t1 (pk varchar(10) PRIMARY KEY, fk varchar(16)); 3559CREATE TABLE t2 (pk varchar(16) PRIMARY KEY, fk varchar(10)); 3560INSERT INTO t1 VALUES 3561('d','dddd'), ('i','iii'), ('a','aa'), ('b','bb'), ('g','gg'), 3562('e','eee'), ('c','cccc'), ('h','hhh'), ('j','jjj'), ('f','fff'); 3563INSERT INTO t2 VALUES 3564('jjj', 'j'), ('cc','c'), ('ccc','c'), ('aaa', 'a'), ('jjjj','j'), 3565('hhh','h'), ('gg','g'), ('fff','f'), ('ee','e'), ('ffff','f'), 3566('bbb','b'), ('ff','f'), ('cccc','c'), ('dddd','d'), ('jj','j'), 3567('aaaa','a'), ('bb','b'), ('eeee','e'), ('aa','a'), ('hh','h'); 3568EXPLAIN SELECT t2.* 3569FROM t1 JOIN t2 ON t2.fk=t1.pk 3570WHERE t2.fk < 'c' AND t2.pk=t1.fk; 3571id select_type table type possible_keys key key_len ref rows Extra 35721 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 3 Using index condition; Using where; Using MRR 35731 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where 3574EXPLAIN SELECT t2.* 3575FROM t1 JOIN t2 ON t2.fk=t1.pk 3576WHERE t2.fk BETWEEN 'a' AND 'b' AND t2.pk=t1.fk; 3577id select_type table type possible_keys key key_len ref rows Extra 35781 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using where; Using MRR 35791 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where 3580EXPLAIN SELECT t2.* 3581FROM t1 JOIN t2 ON t2.fk=t1.pk 3582WHERE t2.fk IN ('a','b') AND t2.pk=t1.fk; 3583id select_type table type possible_keys key key_len ref rows Extra 35841 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using where; Using MRR 35851 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where 3586DROP TABLE t1,t2; 3587CREATE TABLE t1 (a int, b varchar(20) NOT NULL, PRIMARY KEY(a)); 3588CREATE TABLE t2 (a int, b varchar(20) NOT NULL, 3589PRIMARY KEY (a), UNIQUE KEY (b)); 3590INSERT INTO t1 VALUES (1,'a'),(2,'b'),(3,'c'); 3591INSERT INTO t2 VALUES (1,'a'),(2,'b'),(3,'c'); 3592EXPLAIN SELECT t1.a FROM t1 LEFT JOIN t2 ON t2.b=t1.b WHERE t1.a=3; 3593id select_type table type possible_keys key key_len ref rows Extra 35941 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 NULL 35951 SIMPLE t2 const b b 22 const 1 Using index 3596DROP TABLE t1,t2; 3597CREATE TABLE t1(id int PRIMARY KEY, b int, e int); 3598CREATE TABLE t2(i int, a int, INDEX si(i), INDEX ai(a)); 3599CREATE TABLE t3(a int PRIMARY KEY, c char(4), INDEX ci(c)); 3600INSERT INTO t1 VALUES 3601(1,10,19), (2,20,22), (4,41,42), (9,93,95), (7, 77,79), 3602(6,63,67), (5,55,58), (3,38,39), (8,81,89); 3603INSERT INTO t2 VALUES 3604(21,210), (41,410), (82,820), (83,830), (84,840), 3605(65,650), (51,510), (37,370), (94,940), (76,760), 3606(22,220), (33,330), (40,400), (95,950), (38,380), 3607(67,670), (88,880), (57,570), (96,960), (97,970); 3608INSERT INTO t3 VALUES 3609(210,'bb'), (950,'ii'), (400,'ab'), (500,'ee'), (220,'gg'), 3610(440,'gg'), (310,'eg'), (380,'ee'), (840,'bb'), (830,'ff'), 3611(230,'aa'), (960,'ii'), (410,'aa'), (510,'ee'), (290,'bb'), 3612(450,'gg'), (320,'dd'), (390,'hh'), (850,'jj'), (860,'ff'); 3613EXPLAIN 3614SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3 3615WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND 3616t3.a=t2.a AND t3.c IN ('bb','ee'); 3617id select_type table type possible_keys key key_len ref rows Extra 36181 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 NULL 36191 SIMPLE t2 range si si 5 NULL 4 Using index condition; Using where; Using MRR 36201 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where 3621EXPLAIN 3622SELECT t3.a FROM t1,t2,t3 3623WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND 3624t3.a=t2.a AND t3.c IN ('bb','ee') ; 3625id select_type table type possible_keys key key_len ref rows Extra 36261 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 NULL 36271 SIMPLE t2 range si,ai si 5 NULL 4 Using index condition; Using where; Using MRR 36281 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where 3629EXPLAIN 3630SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3 3631WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND 3632t3.c IN ('bb','ee'); 3633id select_type table type possible_keys key key_len ref rows Extra 36341 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 NULL 36351 SIMPLE t2 range si si 5 NULL 2 Using index condition; Using where; Using MRR 36361 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where 3637EXPLAIN 3638SELECT t3.a FROM t1,t2,t3 3639WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND 3640t3.c IN ('bb','ee'); 3641id select_type table type possible_keys key key_len ref rows Extra 36421 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 NULL 36431 SIMPLE t2 range si,ai si 5 NULL 2 Using index condition; Using where; Using MRR 36441 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where 3645DROP TABLE t1,t2,t3; 3646CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int); 3647CREATE TABLE t2 ( f11 int PRIMARY KEY ); 3648INSERT INTO t1 VALUES (1,1,1,0,0,0,0),(2,1,1,3,8,1,0),(3,1,1,4,12,1,0); 3649INSERT INTO t2 VALUES (62); 3650SELECT * FROM t1 LEFT JOIN t2 ON f11 = t1.checked_out GROUP BY f1 ORDER BY f2, f3, f4, f5 LIMIT 0, 1; 3651f1 f2 f3 f4 f5 f6 checked_out f11 36521 1 1 0 0 0 0 NULL 3653DROP TABLE t1, t2; 3654DROP TABLE IF EXISTS t1; 3655CREATE TABLE t1(a int); 3656INSERT into t1 values (1), (2), (3); 3657SELECT * FROM t1 LIMIT 2, -1; 3658ERROR 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 3659DROP TABLE t1; 3660CREATE TABLE t1 ( 3661ID_with_null int NULL, 3662ID_better int NOT NULL, 3663INDEX idx1 (ID_with_null), 3664INDEX idx2 (ID_better) 3665); 3666INSERT INTO t1 VALUES (1,1), (2,1), (null,3), (null,3), (null,3), (null,3); 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; 3671INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; 3672SELECT COUNT(*) FROM t1 WHERE ID_with_null IS NULL; 3673COUNT(*) 3674128 3675SELECT COUNT(*) FROM t1 WHERE ID_better=1; 3676COUNT(*) 36772 3678EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL; 3679id select_type table type possible_keys key key_len ref rows Extra 36801 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where 3681DROP INDEX idx1 ON t1; 3682CREATE UNIQUE INDEX idx1 ON t1(ID_with_null); 3683EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL; 3684id select_type table type possible_keys key key_len ref rows Extra 36851 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where 3686DROP TABLE t1; 3687CREATE TABLE t1 ( 3688ID1_with_null int NULL, 3689ID2_with_null int NULL, 3690ID_better int NOT NULL, 3691INDEX idx1 (ID1_with_null, ID2_with_null), 3692INDEX idx2 (ID_better) 3693); 3694INSERT INTO t1 VALUES (1,1,1), (2,2,1), (3,null,3), (null,3,3), (null,null,3), 3695(3,null,3), (null,3,3), (null,null,3), (3,null,3), (null,3,3), (null,null,3); 3696INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; 3697INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; 3698INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; 3699INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; 3700INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; 3701INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; 3702SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null=3; 3703COUNT(*) 370424 3705SELECT COUNT(*) FROM t1 WHERE ID1_with_null=3 AND ID2_with_null IS NULL; 3706COUNT(*) 370724 3708SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null IS NULL; 3709COUNT(*) 3710192 3711SELECT COUNT(*) FROM t1 WHERE ID_better=1; 3712COUNT(*) 37132 3714EXPLAIN SELECT * FROM t1 3715WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ; 3716id select_type table type possible_keys key key_len ref rows Extra 37171 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where 3718EXPLAIN SELECT * FROM t1 3719WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null=3 IS NULL ; 3720id select_type table type possible_keys key key_len ref rows Extra 37211 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where 3722EXPLAIN SELECT * FROM t1 3723WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL; 3724id select_type table type possible_keys key key_len ref rows Extra 37251 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where 3726DROP INDEX idx1 ON t1; 3727CREATE UNIQUE INDEX idx1 ON t1(ID1_with_null,ID2_with_null); 3728EXPLAIN SELECT * FROM t1 3729WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ; 3730id select_type table type possible_keys key key_len ref rows Extra 37311 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where 3732EXPLAIN SELECT * FROM t1 3733WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null IS NULL ; 3734id select_type table type possible_keys key key_len ref rows Extra 37351 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where 3736EXPLAIN SELECT * FROM t1 3737WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL; 3738id select_type table type possible_keys key key_len ref rows Extra 37391 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where 3740EXPLAIN SELECT * FROM t1 3741WHERE ID_better=1 AND ID1_with_null IS NULL AND 3742(ID2_with_null=1 OR ID2_with_null=2); 3743id select_type table type possible_keys key key_len ref rows Extra 37441 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where 3745DROP TABLE t1; 3746CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts)); 3747INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00"); 3748ANALYZE TABLE t1; 3749Table Op Msg_type Msg_text 3750test.t1 analyze status OK 3751CREATE TABLE t2 (a INT, dt1 DATETIME, dt2 DATETIME, PRIMARY KEY (a)); 3752INSERT INTO t2 VALUES (30, "2006-01-01 00:00:00", "2999-12-31 00:00:00"); 3753INSERT INTO t2 SELECT a+1,dt1,dt2 FROM t2; 3754ANALYZE TABLE t2; 3755Table Op Msg_type Msg_text 3756test.t2 analyze status OK 3757EXPLAIN 3758SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30 3759AND t1.ts BETWEEN t2.dt1 AND t2.dt2 3760AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31"; 3761id select_type table type possible_keys key key_len ref rows Extra 37621 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 NULL 37631 SIMPLE t1 range ts ts 4 NULL 1 Using index condition; Using where; Using MRR 3764Warnings: 3765Warning 1292 Incorrect datetime value: '2999-12-31 00:00:00' for column 'ts' at row 1 3766SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30 3767AND t1.ts BETWEEN t2.dt1 AND t2.dt2 3768AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31"; 3769a ts a dt1 dt2 377030 2006-01-03 23:00:00 30 2006-01-01 00:00:00 2999-12-31 00:00:00 3771Warnings: 3772Warning 1292 Incorrect datetime value: '2999-12-31 00:00:00' for column 'ts' at row 1 3773DROP TABLE t1,t2; 3774create table t1 (a bigint unsigned); 3775insert into t1 values 3776(if(1, 9223372036854775808, 1)), 3777(case when 1 then 9223372036854775808 else 1 end), 3778(coalesce(9223372036854775808, 1)); 3779select * from t1; 3780a 37819223372036854775808 37829223372036854775808 37839223372036854775808 3784drop table t1; 3785create table t1 select 3786if(1, 9223372036854775808, 1) i, 3787case when 1 then 9223372036854775808 else 1 end c, 3788coalesce(9223372036854775808, 1) co; 3789show create table t1; 3790Table Create Table 3791t1 CREATE TABLE `t1` ( 3792 `i` decimal(19,0) NOT NULL DEFAULT '0', 3793 `c` decimal(19,0) NOT NULL DEFAULT '0', 3794 `co` decimal(19,0) NOT NULL DEFAULT '0' 3795) ENGINE=MyISAM DEFAULT CHARSET=latin1 3796drop table t1; 3797select 3798if(1, cast(1111111111111111111 as unsigned), 1) i, 3799case when 1 then cast(1111111111111111111 as unsigned) else 1 end c, 3800coalesce(cast(1111111111111111111 as unsigned), 1) co; 3801i c co 38021111111111111111111 1111111111111111111 1111111111111111111 3803CREATE TABLE t1 (name varchar(255)); 3804CREATE TABLE t2 (name varchar(255), n int, KEY (name(3))); 3805INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa '), ('aa'); 3806INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc ',3); 3807INSERT INTO t2 VALUES (concat('cc ', 0x06), 4); 3808INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7); 3809SELECT * FROM t2; 3810name n 3811bb 1 3812aa 2 3813cc 3 3814cc 4 3815cc 5 3816bb 6 3817cc 7 3818SELECT * FROM t2 ORDER BY name; 3819name n 3820aa 2 3821bb 1 3822bb 6 3823cc 4 3824cc 3 3825cc 5 3826cc 7 3827SELECT name, LENGTH(name), n FROM t2 ORDER BY name; 3828name LENGTH(name) n 3829aa 2 2 3830bb 2 1 3831bb 3 6 3832cc 4 4 3833cc 5 3 3834cc 2 5 3835cc 3 7 3836EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; 3837id select_type table type possible_keys key key_len ref rows Extra 38381 SIMPLE t2 ref name name 6 const 3 Using where 3839SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; 3840name LENGTH(name) n 3841cc 5 3 3842cc 2 5 3843cc 3 7 3844EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; 3845id select_type table type possible_keys key key_len ref rows Extra 38461 SIMPLE t2 range name name 6 NULL 3 Using where 3847SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; 3848name LENGTH(name) n 3849cc 5 3 3850cc 4 4 3851cc 2 5 3852cc 3 7 3853EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; 3854id select_type table type possible_keys key key_len ref rows Extra 38551 SIMPLE t2 range name name 6 NULL 3 Using where; Using filesort 3856SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; 3857name LENGTH(name) n 3858cc 4 4 3859cc 5 3 3860cc 2 5 3861cc 3 7 3862EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; 3863id select_type table type possible_keys key key_len ref rows Extra 38641 SIMPLE t1 ALL NULL NULL NULL NULL 5 NULL 38651 SIMPLE t2 ALL name NULL NULL NULL 7 Using where; Using join buffer (Block Nested Loop) 3866SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; 3867name name n 3868bb bb 1 3869aa aa 2 3870aa aa 2 3871cc cc 3 3872cc cc 5 3873bb bb 6 3874cc cc 7 3875ccc NULL NULL 3876DROP TABLE t1,t2; 3877CREATE TABLE t1 (name text); 3878CREATE TABLE t2 (name text, n int, KEY (name(3))); 3879INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa '), ('aa'); 3880INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc ',3); 3881INSERT INTO t2 VALUES (concat('cc ', 0x06), 4); 3882INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7); 3883SELECT * FROM t2; 3884name n 3885bb 1 3886aa 2 3887cc 3 3888cc 4 3889cc 5 3890bb 6 3891cc 7 3892SELECT * FROM t2 ORDER BY name; 3893name n 3894aa 2 3895bb 1 3896bb 6 3897cc 4 3898cc 3 3899cc 5 3900cc 7 3901SELECT name, LENGTH(name), n FROM t2 ORDER BY name; 3902name LENGTH(name) n 3903aa 2 2 3904bb 2 1 3905bb 3 6 3906cc 4 4 3907cc 5 3 3908cc 2 5 3909cc 3 7 3910EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; 3911id select_type table type possible_keys key key_len ref rows Extra 39121 SIMPLE t2 ref name name 6 const 3 Using where 3913SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; 3914name LENGTH(name) n 3915cc 5 3 3916cc 2 5 3917cc 3 7 3918EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; 3919id select_type table type possible_keys key key_len ref rows Extra 39201 SIMPLE t2 range name name 6 NULL 3 Using where 3921SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; 3922name LENGTH(name) n 3923cc 5 3 3924cc 4 4 3925cc 2 5 3926cc 3 7 3927EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; 3928id select_type table type possible_keys key key_len ref rows Extra 39291 SIMPLE t2 range name name 6 NULL 3 Using where; Using filesort 3930SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; 3931name LENGTH(name) n 3932cc 4 4 3933cc 5 3 3934cc 2 5 3935cc 3 7 3936EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; 3937id select_type table type possible_keys key key_len ref rows Extra 39381 SIMPLE t1 ALL NULL NULL NULL NULL 5 NULL 39391 SIMPLE t2 ALL name NULL NULL NULL 7 Using where; Using join buffer (Block Nested Loop) 3940SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; 3941name name n 3942bb bb 1 3943aa aa 2 3944aa aa 2 3945cc cc 3 3946cc cc 5 3947bb bb 6 3948cc cc 7 3949ccc NULL NULL 3950DROP TABLE t1,t2; 3951CREATE TABLE t1 ( 3952access_id int NOT NULL default '0', 3953name varchar(20) default NULL, 3954rank int NOT NULL default '0', 3955KEY idx (access_id) 3956); 3957CREATE TABLE t2 ( 3958faq_group_id int NOT NULL default '0', 3959faq_id int NOT NULL default '0', 3960access_id int default NULL, 3961UNIQUE KEY idx1 (faq_id), 3962KEY idx2 (faq_group_id,faq_id) 3963); 3964INSERT INTO t1 VALUES 3965(1,'Everyone',2),(2,'Help',3),(3,'Technical Support',1),(4,'Chat User',4); 3966INSERT INTO t2 VALUES 3967(261,265,1),(490,494,1); 3968SELECT t2.faq_id 3969FROM t1 INNER JOIN t2 IGNORE INDEX (idx1) 3970ON (t1.access_id = t2.access_id) 3971LEFT JOIN t2 t 3972ON (t.faq_group_id = t2.faq_group_id AND 3973find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4')) 3974WHERE 3975t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265); 3976faq_id 3977265 3978SELECT t2.faq_id 3979FROM t1 INNER JOIN t2 3980ON (t1.access_id = t2.access_id) 3981LEFT JOIN t2 t 3982ON (t.faq_group_id = t2.faq_group_id AND 3983find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4')) 3984WHERE 3985t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265); 3986faq_id 3987265 3988DROP TABLE t1,t2; 3989CREATE TABLE t1 (a INT, b INT, KEY inx (b,a)); 3990INSERT INTO t1 VALUES (1,1), (1,2), (1,3), (1,4), (1,5), (1, 6), (1,7); 3991EXPLAIN SELECT COUNT(*) FROM t1 f1 INNER JOIN t1 f2 3992ON ( f1.b=f2.b AND f1.a<f2.a ) 3993WHERE 1 AND f1.b NOT IN (100,2232,3343,51111); 3994id select_type table type possible_keys key key_len ref rows Extra 39951 SIMPLE f1 index inx inx 10 NULL 7 Using where; Using index 39961 SIMPLE f2 ref inx inx 5 test.f1.b 1 Using where; Using index 3997DROP TABLE t1; 3998CREATE TABLE t1 (c1 INT, c2 INT); 3999INSERT INTO t1 VALUES (1,11), (2,22), (2,22); 4000EXPLAIN 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; 4001id select_type table type possible_keys key key_len ref rows Extra 40021 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where 40032 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 40043 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 40054 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 40065 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 40076 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 40087 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 40098 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 40109 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 401110 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 401211 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 401312 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 401413 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 401514 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 401615 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 401716 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 401817 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 401918 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 402019 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 402120 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 402221 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 402322 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 402423 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 402524 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 402625 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 402726 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 402827 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 402928 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 403029 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 403130 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 4032EXPLAIN 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; 4033ERROR HY000: Too high level of nesting for select 4034DROP TABLE t1; 4035CREATE TABLE t1 ( 4036c1 int(11) NOT NULL AUTO_INCREMENT, 4037c2 varchar(1000) DEFAULT NULL, 4038c3 bigint(20) DEFAULT NULL, 4039c4 bigint(20) DEFAULT NULL, 4040PRIMARY KEY (c1) 4041); 4042EXPLAIN EXTENDED 4043SELECT join_2.c1 4044FROM 4045t1 AS join_0, 4046t1 AS join_1, 4047t1 AS join_2, 4048t1 AS join_3, 4049t1 AS join_4, 4050t1 AS join_5, 4051t1 AS join_6, 4052t1 AS join_7 4053WHERE 4054join_0.c1=join_1.c1 AND 4055join_1.c1=join_2.c1 AND 4056join_2.c1=join_3.c1 AND 4057join_3.c1=join_4.c1 AND 4058join_4.c1=join_5.c1 AND 4059join_5.c1=join_6.c1 AND 4060join_6.c1=join_7.c1 4061OR 4062join_0.c2 < '?' AND 4063join_1.c2 < '?' AND 4064join_2.c2 > '?' AND 4065join_2.c2 < '!' AND 4066join_3.c2 > '?' AND 4067join_4.c2 = '?' AND 4068join_5.c2 <> '?' AND 4069join_6.c2 <> '?' AND 4070join_7.c2 >= '?' AND 4071join_0.c1=join_1.c1 AND 4072join_1.c1=join_2.c1 AND 4073join_2.c1=join_3.c1 AND 4074join_3.c1=join_4.c1 AND 4075join_4.c1=join_5.c1 AND 4076join_5.c1=join_6.c1 AND 4077join_6.c1=join_7.c1 4078GROUP BY 4079join_3.c1, 4080join_2.c1, 4081join_7.c1, 4082join_1.c1, 4083join_0.c1; 4084id select_type table type possible_keys key key_len ref rows filtered Extra 40851 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 4086Warnings: 4087Note 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 4088SHOW WARNINGS; 4089Level Code Message 4090Note 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 4091DROP TABLE t1; 4092SELECT 1 AS ` `; 4093 40941 4095Warnings: 4096Warning 1474 Name ' ' has become '' 4097SELECT 1 AS ` `; 4098 40991 4100Warnings: 4101Warning 1474 Name ' ' has become '' 4102SELECT 1 AS ` x`; 4103x 41041 4105Warnings: 4106Warning 1466 Leading spaces are removed from name ' x' 4107CREATE VIEW v1 AS SELECT 1 AS ``; 4108ERROR 42000: Incorrect column name '' 4109CREATE VIEW v1 AS SELECT 1 AS ` `; 4110ERROR 42000: Incorrect column name ' ' 4111CREATE VIEW v1 AS SELECT 1 AS ` `; 4112ERROR 42000: Incorrect column name ' ' 4113CREATE VIEW v1 AS SELECT (SELECT 1 AS ` `); 4114ERROR 42000: Incorrect column name ' ' 4115CREATE VIEW v1 AS SELECT 1 AS ` x`; 4116Warnings: 4117Warning 1466 Leading spaces are removed from name ' x' 4118SELECT `x` FROM v1; 4119x 41201 4121ALTER VIEW v1 AS SELECT 1 AS ` `; 4122ERROR 42000: Incorrect column name ' ' 4123DROP VIEW v1; 4124select str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT' 4125 and '2007/10/20 00:00:00 GMT'; 4126str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT' 4127 and '2007/10/20 00:00:00 GMT' 41281 4129Warnings: 4130Warning 1292 Truncated incorrect datetime value: '2007/10/01 00:00:00 GMT' 4131Warning 1292 Truncated incorrect datetime value: '2007/10/20 00:00:00 GMT' 4132select str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6'; 4133str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6' 41341 4135Warnings: 4136Warning 1292 Truncated incorrect date value: '2007/10/01 00:00:00 GMT-6' 4137select str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6'; 4138str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6' 41391 4140Warnings: 4141Warning 1292 Truncated incorrect date value: '2007/10/2000:00:00 GMT-6' 4142select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6'; 4143str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6' 41441 4145Warnings: 4146Warning 1292 Truncated incorrect date value: '2007-10-1 00:00:00 GMT-6' 4147select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6'; 4148str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6' 41491 4150Warnings: 4151Warning 1292 Truncated incorrect date value: '2007-10-01 x00:00:00 GMT-6' 4152select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6'; 4153str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6' 41541 4155Warnings: 4156Warning 1292 Truncated incorrect datetime value: '2007-10-01 00:00:00 GMT-6' 4157select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6'; 4158str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6' 41591 4160Warnings: 4161Warning 1292 Truncated incorrect datetime value: '2007-10-01 00:x00:00 GMT-6' 4162select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6'; 4163str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6' 41641 4165Warnings: 4166Warning 1292 Truncated incorrect datetime value: '2007-10-01 x12:34:56 GMT-6' 4167select str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6'; 4168str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6' 41691 4170Warnings: 4171Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34x:56 GMT-6' 4172select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6'; 4173str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6' 41740 4175Warnings: 4176Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34x:56 GMT-6' 4177select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34:56'; 4178str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34:56' 41791 4180select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 12:00:00'; 4181str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 12:00:00' 41820 4183select str_to_date('2007-10-01 12','%Y-%m-%d %H') = '2007-10-01 12:00:00'; 4184str_to_date('2007-10-01 12','%Y-%m-%d %H') = '2007-10-01 12:00:00' 41851 4186select str_to_date('2007-10-01 12:34','%Y-%m-%d %H') = '2007-10-01 12:00:00'; 4187str_to_date('2007-10-01 12:34','%Y-%m-%d %H') = '2007-10-01 12:00:00' 41881 4189Warnings: 4190Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34' 4191select str_to_date('2007-02-30 12:34','%Y-%m-%d %H:%i') = '2007-02-30 12:34'; 4192str_to_date('2007-02-30 12:34','%Y-%m-%d %H:%i') = '2007-02-30 12:34' 41931 4194select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'; 4195str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34' 41961 4197select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00' 4198 and '2007/10/20 00:00:00'; 4199str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00' 4200 and '2007/10/20 00:00:00' 42011 4202set SQL_MODE=TRADITIONAL; 4203select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'; 4204str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34' 4205NULL 4206Warnings: 4207Warning 1292 Truncated incorrect datetime value: '2007-10-00 12:34' 4208Warning 1411 Incorrect datetime value: '2007-10-00 12:34' for function str_to_date 4209select str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'; 4210str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34' 42110 4212Warnings: 4213Warning 1292 Truncated incorrect datetime value: '2007-10-00 12:34' 4214select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34'; 4215str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34' 4216NULL 4217Warnings: 4218Warning 1411 Incorrect datetime value: '2007-10-00 12:34' for function str_to_date 4219select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01' 4220 and '2007/10/20'; 4221str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01' 4222 and '2007/10/20' 4223NULL 4224Warnings: 4225Warning 1411 Incorrect datetime value: '2007-10-00' for function str_to_date 4226set SQL_MODE=DEFAULT; 4227select str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20'; 4228str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20' 42291 4230Warnings: 4231Warning 1292 Truncated incorrect datetime value: '' 4232select str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20'; 4233str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20' 42340 4235select str_to_date('','%Y-%m-%d %H:%i') = '2007-10-01 12:34'; 4236str_to_date('','%Y-%m-%d %H:%i') = '2007-10-01 12:34' 42370 4238select str_to_date(NULL,'%Y-%m-%d %H:%i') = '2007-10-01 12:34'; 4239str_to_date(NULL,'%Y-%m-%d %H:%i') = '2007-10-01 12:34' 4240NULL 4241select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = ''; 4242str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '' 42430 4244Warnings: 4245Warning 1292 Truncated incorrect datetime value: '' 4246select str_to_date('1','%Y-%m-%d') = '1'; 4247str_to_date('1','%Y-%m-%d') = '1' 42480 4249Warnings: 4250Warning 1292 Truncated incorrect date value: '1' 4251select str_to_date('1','%Y-%m-%d') = '1'; 4252str_to_date('1','%Y-%m-%d') = '1' 42530 4254Warnings: 4255Warning 1292 Truncated incorrect date value: '1' 4256select str_to_date('','%Y-%m-%d') = ''; 4257str_to_date('','%Y-%m-%d') = '' 42580 4259Warnings: 4260Warning 1292 Truncated incorrect date value: '' 4261select str_to_date('1000-01-01','%Y-%m-%d') between '0000-00-00' and NULL; 4262str_to_date('1000-01-01','%Y-%m-%d') between '0000-00-00' and NULL 42630 4264select str_to_date('1000-01-01','%Y-%m-%d') between NULL and '2000-00-00'; 4265str_to_date('1000-01-01','%Y-%m-%d') between NULL and '2000-00-00' 42660 4267select str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL; 4268str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL 42690 4270CREATE TABLE t1 (c11 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY); 4271CREATE TABLE t2 (c21 INT UNSIGNED NOT NULL, 4272c22 INT DEFAULT NULL, 4273KEY(c21, c22)); 4274CREATE TABLE t3 (c31 INT UNSIGNED NOT NULL DEFAULT 0, 4275c32 INT DEFAULT NULL, 4276c33 INT NOT NULL, 4277c34 INT UNSIGNED DEFAULT 0, 4278KEY (c33, c34, c32)); 4279INSERT INTO t1 values (),(),(),(),(); 4280INSERT INTO t2 SELECT a.c11, b.c11 FROM t1 a, t1 b; 4281INSERT INTO t3 VALUES (1, 1, 1, 0), 4282(2, 2, 0, 0), 4283(3, 3, 1, 0), 4284(4, 4, 0, 0), 4285(5, 5, 1, 0); 4286SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND 4287t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND 4288t3.c33 = 1 AND t2.c22 in (1, 3) 4289ORDER BY c32; 4290c32 42911 42921 42933 42943 42955 42965 4297SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND 4298t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND 4299t3.c33 = 1 AND t2.c22 in (1, 3) 4300ORDER BY c32 DESC; 4301c32 43025 43035 43043 43053 43061 43071 4308DROP TABLE t1, t2, t3; 4309 4310# 4311# Bug#30736: Row Size Too Large Error Creating a Table and 4312# Inserting Data. 4313# 4314DROP TABLE IF EXISTS t1; 4315DROP TABLE IF EXISTS t2; 4316 4317CREATE TABLE t1( 4318c1 DECIMAL(10, 2), 4319c2 FLOAT); 4320 4321INSERT INTO t1 VALUES (0, 1), (2, 3), (4, 5); 4322 4323CREATE TABLE t2( 4324c3 DECIMAL(10, 2)) 4325SELECT 4326c1 * c2 AS c3 4327FROM t1; 4328 4329SELECT * FROM t1; 4330c1 c2 43310.00 1 43322.00 3 43334.00 5 4334 4335SELECT * FROM t2; 4336c3 43370.00 43386.00 433920.00 4340 4341DROP TABLE t1; 4342DROP TABLE t2; 4343 4344CREATE TABLE t1 (c1 BIGINT NOT NULL); 4345INSERT INTO t1 (c1) VALUES (1); 4346SELECT * FROM t1 WHERE c1 > NULL + 1; 4347c1 4348DROP TABLE t1; 4349 4350CREATE TABLE t1 (a VARCHAR(10) NOT NULL PRIMARY KEY); 4351INSERT INTO t1 (a) VALUES ('foo0'), ('bar0'), ('baz0'); 4352SELECT * FROM t1 WHERE a IN (CONCAT('foo', 0), 'bar'); 4353a 4354foo0 4355DROP TABLE t1; 4356CREATE TABLE t1 (a INT, b INT); 4357CREATE TABLE t2 (a INT, c INT, KEY(a)); 4358INSERT INTO t1 VALUES (1, 1), (2, 2); 4359INSERT INTO t2 VALUES (1, 1), (1, 2), (1, 3), (1, 4), (1, 5), 4360(2, 1), (2, 2), (2, 3), (2, 4), (2, 5), 4361(3, 1), (3, 2), (3, 3), (3, 4), (3, 5), 4362(4, 1), (4, 2), (4, 3), (4, 4), (4, 5); 4363FLUSH STATUS; 4364SELECT DISTINCT b FROM t1 LEFT JOIN t2 USING(a) WHERE c <= 3; 4365b 43661 43672 4368SHOW STATUS LIKE 'Handler_read%'; 4369Variable_name Value 4370Handler_read_first 0 4371Handler_read_key 2 4372Handler_read_last 0 4373Handler_read_next 0 4374Handler_read_prev 0 4375Handler_read_rnd 0 4376Handler_read_rnd_next 6 4377DROP TABLE t1, t2; 4378CREATE TABLE t1 (f1 bigint(20) NOT NULL default '0', 4379f2 int(11) NOT NULL default '0', 4380f3 bigint(20) NOT NULL default '0', 4381f4 varchar(255) NOT NULL default '', 4382PRIMARY KEY (f1), 4383KEY key1 (f4), 4384KEY key2 (f2)); 4385CREATE TABLE t2 (f1 int(11) NOT NULL default '0', 4386f2 enum('A1','A2','A3') NOT NULL default 'A1', 4387f3 int(11) NOT NULL default '0', 4388PRIMARY KEY (f1), 4389KEY key1 (f3)); 4390CREATE TABLE t3 (f1 bigint(20) NOT NULL default '0', 4391f2 datetime NOT NULL default '1980-01-01 00:00:00', 4392PRIMARY KEY (f1)); 4393insert into t1 values (1, 1, 1, 'abc'); 4394insert into t1 values (2, 1, 2, 'def'); 4395insert into t1 values (3, 1, 2, 'def'); 4396insert into t2 values (1, 'A1', 1); 4397insert into t3 values (1, '1980-01-01'); 4398SELECT a.f3, cr.f4, count(*) count 4399FROM t2 a 4400STRAIGHT_JOIN t1 cr ON cr.f2 = a.f1 4401LEFT JOIN 4402(t1 cr2 4403JOIN t3 ae2 ON cr2.f3 = ae2.f1 4404) ON a.f1 = cr2.f2 AND ae2.f2 < now() - INTERVAL 7 DAY AND 4405cr.f4 = cr2.f4 4406GROUP BY a.f3, cr.f4; 4407f3 f4 count 44081 abc 1 44091 def 2 4410drop table t1, t2, t3; 4411CREATE TABLE t1 (a INT KEY, b INT); 4412INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4); 4413EXPLAIN EXTENDED SELECT a, b FROM t1 WHERE a > 1 AND a = b LIMIT 2; 4414id select_type table type possible_keys key key_len ref rows filtered Extra 44151 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using index condition; Using where; Using MRR 4416Warnings: 4417Note 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 4418EXPLAIN EXTENDED SELECT a, b FROM t1 WHERE a > 1 AND b = a LIMIT 2; 4419id select_type table type possible_keys key key_len ref rows filtered Extra 44201 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using index condition; Using where; Using MRR 4421Warnings: 4422Note 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 4423DROP TABLE t1; 4424# 4425# Bug#47019: Assertion failed: 0, file .\rt_mbr.c, line 138 when 4426# forcing a spatial index 4427# 4428CREATE TABLE t1(a LINESTRING NOT NULL, SPATIAL KEY(a)); 4429INSERT INTO t1 VALUES 4430(GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1)')), 4431(GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1)')); 4432EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2; 4433id select_type table type possible_keys key key_len ref rows Extra 44341 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL 44351 SIMPLE t2 ALL a NULL NULL NULL 2 Range checked for each record (index map: 0x1) 4436SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2; 44371 44381 44391 44401 44411 4442EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a); 4443id select_type table type possible_keys key key_len ref rows Extra 44441 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL 44451 SIMPLE t2 ALL a NULL NULL NULL 2 Range checked for each record (index map: 0x1) 4446SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a); 44471 44481 44491 44501 44511 4452DROP TABLE t1; 4453# 4454# Bug #48291 : crash with row() operator,select into @var, and 4455# subquery returning multiple rows 4456# 4457CREATE TABLE t1(a INT); 4458INSERT INTO t1 VALUES (2),(3); 4459# Should not crash 4460SELECT 1 FROM t1 WHERE a <> 1 AND NOT 4461ROW(1,a) <=> ROW(1,(SELECT 1 FROM t1)) 4462INTO @var0; 4463ERROR 21000: Subquery returns more than 1 row 4464DROP TABLE t1; 4465# 4466# Bug #48458: simple query tries to allocate enormous amount of 4467# memory 4468# 4469CREATE TABLE t1(a INT NOT NULL, b YEAR); 4470INSERT INTO t1 VALUES (); 4471Warnings: 4472Warning 1364 Field 'a' doesn't have a default value 4473CREATE TABLE t2(c INT); 4474# Should not err out because of out-of-memory 4475SELECT 1 FROM t2 JOIN t1 ON 1=1 4476WHERE a != '1' AND NOT a >= b OR NOT ROW(b,a )<> ROW(a,a); 44771 4478DROP TABLE t1,t2; 4479# 4480# Bug #49199: Optimizer handles incorrectly: 4481# field='const1' AND field='const2' in some cases 4482 4483CREATE TABLE t1(a DATETIME NOT NULL); 4484INSERT INTO t1 VALUES('2001-01-01'); 4485SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; 4486a 44872001-01-01 00:00:00 4488EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; 4489id select_type table type possible_keys key key_len ref rows filtered Extra 44901 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 NULL 4491Warnings: 4492Note 1003 /* select#1 */ select '2001-01-01 00:00:00' AS `a` from dual where 1 4493DROP TABLE t1; 4494CREATE TABLE t1(a DATE NOT NULL); 4495INSERT INTO t1 VALUES('2001-01-01'); 4496SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; 4497a 44982001-01-01 4499EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; 4500id select_type table type possible_keys key key_len ref rows filtered Extra 45011 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 NULL 4502Warnings: 4503Note 1003 /* select#1 */ select '2001-01-01' AS `a` from dual where 1 4504DROP TABLE t1; 4505CREATE TABLE t1(a TIMESTAMP NOT NULL NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); 4506INSERT INTO t1 VALUES('2001-01-01'); 4507SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; 4508a 45092001-01-01 00:00:00 4510EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; 4511id select_type table type possible_keys key key_len ref rows filtered Extra 45121 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 NULL 4513Warnings: 4514Note 1003 /* select#1 */ select '2001-01-01 00:00:00' AS `a` from dual where 1 4515DROP TABLE t1; 4516CREATE TABLE t1(a DATETIME NOT NULL, b DATE NOT NULL); 4517INSERT INTO t1 VALUES('2001-01-01', '2001-01-01'); 4518SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00'; 4519a b 45202001-01-01 00:00:00 2001-01-01 4521EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00'; 4522id select_type table type possible_keys key key_len ref rows filtered Extra 45231 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 NULL 4524Warnings: 4525Note 1003 /* select#1 */ select '2001-01-01 00:00:00' AS `a`,'2001-01-01' AS `b` from dual where 1 4526DROP TABLE t1; 4527CREATE TABLE t1(a DATETIME NOT NULL, b VARCHAR(20) NOT NULL); 4528INSERT INTO t1 VALUES('2001-01-01', '2001-01-01'); 4529SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00'; 4530a b 4531EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00'; 4532id select_type table type possible_keys key key_len ref rows filtered Extra 45331 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 4534Warnings: 4535Note 1003 /* select#1 */ select '2001-01-01 00:00:00' AS `a`,'2001-01-01' AS `b` from dual where 0 4536SELECT * FROM t1 WHERE a='2001-01-01 00:00:00' AND a=b AND b='2001-01-01'; 4537a b 45382001-01-01 00:00:00 2001-01-01 4539EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01 00:00:00' AND a=b AND b='2001-01-01'; 4540id select_type table type possible_keys key key_len ref rows filtered Extra 45411 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 NULL 4542Warnings: 4543Note 1003 /* select#1 */ select '2001-01-01 00:00:00' AS `a`,'2001-01-01' AS `b` from dual where 1 4544DROP TABLE t1; 4545CREATE TABLE t1(a DATETIME NOT NULL, b DATE NOT NULL); 4546INSERT INTO t1 VALUES('2001-01-01', '2001-01-01'); 4547SELECT x.a, y.a, z.a FROM t1 x 4548JOIN t1 y ON x.a=y.a 4549JOIN t1 z ON y.a=z.a 4550WHERE x.a='2001-01-01' AND z.a='2001-01-01 00:00:00'; 4551a a a 45522001-01-01 00:00:00 2001-01-01 00:00:00 2001-01-01 00:00:00 4553EXPLAIN EXTENDED SELECT x.a, y.a, z.a FROM t1 x 4554JOIN t1 y ON x.a=y.a 4555JOIN t1 z ON y.a=z.a 4556WHERE x.a='2001-01-01' AND z.a='2001-01-01 00:00:00'; 4557id select_type table type possible_keys key key_len ref rows filtered Extra 45581 SIMPLE x system NULL NULL NULL NULL 1 100.00 NULL 45591 SIMPLE y system NULL NULL NULL NULL 1 100.00 NULL 45601 SIMPLE z system NULL NULL NULL NULL 1 100.00 NULL 4561Warnings: 4562Note 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 4563DROP TABLE t1; 4564# 4565# Bug #49897: crash in ptr_compare when char(0) NOT NULL 4566# column is used for ORDER BY 4567# 4568SET @old_sort_buffer_size= @@session.sort_buffer_size; 4569SET @@sort_buffer_size= 40000; 4570CREATE TABLE t1(a CHAR(0) NOT NULL); 4571INSERT INTO t1 VALUES (0), (0), (0); 4572INSERT INTO t1 SELECT t11.a FROM t1 t11, t1 t12; 4573INSERT INTO t1 SELECT t11.a FROM t1 t11, t1 t12; 4574INSERT INTO t1 SELECT t11.a FROM t1 t11, t1 t12; 4575EXPLAIN SELECT a FROM t1 ORDER BY a; 4576id select_type table type possible_keys key key_len ref rows Extra 45771 SIMPLE t1 ALL NULL NULL NULL NULL 24492 NULL 4578SELECT a FROM t1 ORDER BY a; 4579DROP TABLE t1; 4580CREATE TABLE t1(a CHAR(0) NOT NULL, b CHAR(0) NOT NULL, c int); 4581INSERT INTO t1 VALUES (0, 0, 0), (0, 0, 2), (0, 0, 1); 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; 4584INSERT INTO t1 SELECT t11.a, t11.b, t11.c FROM t1 t11, t1 t12; 4585EXPLAIN SELECT a FROM t1 ORDER BY a LIMIT 5; 4586id select_type table type possible_keys key key_len ref rows Extra 45871 SIMPLE t1 ALL NULL NULL NULL NULL 24492 NULL 4588SELECT a FROM t1 ORDER BY a LIMIT 5; 4589a 4590 4591 4592 4593 4594 4595EXPLAIN SELECT * FROM t1 ORDER BY a, b LIMIT 5; 4596id select_type table type possible_keys key key_len ref rows Extra 45971 SIMPLE t1 ALL NULL NULL NULL NULL 24492 NULL 4598SELECT * FROM t1 ORDER BY a, b LIMIT 5; 4599a b c 4600 0 4601 2 4602 1 4603 0 4604 2 4605EXPLAIN SELECT * FROM t1 ORDER BY a, b, c LIMIT 5; 4606id select_type table type possible_keys key key_len ref rows Extra 46071 SIMPLE t1 ALL NULL NULL NULL NULL 24492 Using filesort 4608SELECT * FROM t1 ORDER BY a, b, c LIMIT 5; 4609a b c 4610 0 4611 0 4612 0 4613 0 4614 0 4615EXPLAIN SELECT * FROM t1 ORDER BY c, a LIMIT 5; 4616id select_type table type possible_keys key key_len ref rows Extra 46171 SIMPLE t1 ALL NULL NULL NULL NULL 24492 Using filesort 4618SELECT * FROM t1 ORDER BY c, a LIMIT 5; 4619a b c 4620 0 4621 0 4622 0 4623 0 4624 0 4625SET @@sort_buffer_size= @old_sort_buffer_size; 4626DROP TABLE t1; 4627End of 5.0 tests 4628create table t1(a INT, KEY (a)); 4629INSERT INTO t1 VALUES (1),(2),(3),(4),(5); 4630SELECT a FROM t1 ORDER BY a LIMIT 2; 4631a 46321 46332 4634SELECT a FROM t1 ORDER BY a LIMIT 2,4294967296; 4635a 46363 46374 46385 4639SELECT a FROM t1 ORDER BY a LIMIT 2,4294967297; 4640a 46413 46424 46435 4644DROP TABLE t1; 4645CREATE TABLE A (date_key date); 4646CREATE TABLE C ( 4647pk int, 4648int_nokey int, 4649int_key int, 4650date_key date NOT NULL, 4651date_nokey date, 4652varchar_key varchar(1) 4653); 4654INSERT INTO C VALUES 4655(1,1,1,'0000-00-00',NULL,NULL), 4656(1,1,1,'0000-00-00',NULL,NULL); 4657SELECT 1 FROM C WHERE pk > ANY (SELECT 1 FROM C); 46581 4659SELECT COUNT(DISTINCT 1) FROM C 4660WHERE date_key = (SELECT 1 FROM A WHERE C.date_key IS NULL) GROUP BY pk; 4661COUNT(DISTINCT 1) 4662SELECT date_nokey FROM C 4663WHERE int_key IN (SELECT 1 FROM A) 4664HAVING date_nokey = '10:41:7' 4665ORDER BY date_key; 4666date_nokey 4667Warnings: 4668Warning 1292 Incorrect date value: '10:41:7' for column 'date_nokey' at row 1 4669DROP TABLE A,C; 4670CREATE TABLE t1 (a INT NOT NULL, b INT); 4671INSERT INTO t1 VALUES (1, 1); 4672EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND a=a) OR b > 2; 4673id select_type table type possible_keys key key_len ref rows filtered Extra 46741 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 NULL 4675Warnings: 4676Note 1003 /* select#1 */ select '1' AS `a`,'1' AS `b` from dual where 1 4677SELECT * FROM t1 WHERE (a=a AND a=a) OR b > 2; 4678a b 46791 1 4680DROP TABLE t1; 4681CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, c INT NOT NULL); 4682EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND b=b AND c=c) OR b > 20; 4683id select_type table type possible_keys key key_len ref rows filtered Extra 46841 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 const row not found 4685Warnings: 4686Note 1003 /* select#1 */ select NULL AS `a`,NULL AS `b`,NULL AS `c` from `test`.`t1` where 1 4687EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND a=a AND b=b) OR b > 20; 4688id select_type table type possible_keys key key_len ref rows filtered Extra 46891 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 const row not found 4690Warnings: 4691Note 1003 /* select#1 */ select NULL AS `a`,NULL AS `b`,NULL AS `c` from `test`.`t1` where 1 4692EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND b=b AND a=a) OR b > 20; 4693id select_type table type possible_keys key key_len ref rows filtered Extra 46941 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 const row not found 4695Warnings: 4696Note 1003 /* select#1 */ select NULL AS `a`,NULL AS `b`,NULL AS `c` from `test`.`t1` where 1 4697DROP TABLE t1; 4698# 4699# Bug#45266: Uninitialized variable lead to an empty result. 4700# 4701drop table if exists A,AA,B,BB; 4702CREATE TABLE `A` ( 4703`pk` int(11) NOT NULL AUTO_INCREMENT, 4704`date_key` date NOT NULL, 4705`date_nokey` date NOT NULL, 4706`datetime_key` datetime NOT NULL, 4707`int_nokey` int(11) NOT NULL, 4708`time_key` time NOT NULL, 4709`time_nokey` time NOT NULL, 4710PRIMARY KEY (`pk`), 4711KEY `date_key` (`date_key`), 4712KEY `time_key` (`time_key`), 4713KEY `datetime_key` (`datetime_key`) 4714); 4715CREATE TABLE `AA` ( 4716`pk` int(11) NOT NULL AUTO_INCREMENT, 4717`int_nokey` int(11) NOT NULL, 4718`time_key` time NOT NULL, 4719KEY `time_key` (`time_key`), 4720PRIMARY KEY (`pk`) 4721); 4722CREATE TABLE `B` ( 4723`date_nokey` date NOT NULL, 4724`date_key` date NOT NULL, 4725`time_key` time NOT NULL, 4726`datetime_nokey` datetime NOT NULL, 4727`varchar_key` varchar(1) NOT NULL, 4728KEY `date_key` (`date_key`), 4729KEY `time_key` (`time_key`), 4730KEY `varchar_key` (`varchar_key`) 4731); 4732INSERT 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'); 4733CREATE TABLE `BB` ( 4734`pk` int(11) NOT NULL AUTO_INCREMENT, 4735`int_nokey` int(11) NOT NULL, 4736`date_key` date NOT NULL, 4737`varchar_nokey` varchar(1) NOT NULL, 4738`date_nokey` date NOT NULL, 4739PRIMARY KEY (`pk`), 4740KEY `date_key` (`date_key`) 4741); 4742INSERT INTO `BB` VALUES (10,8,'0000-00-00','i','0000-00-00'),(11,0,'2005-08-18','','2005-08-18'); 4743SELECT table1 . `pk` AS field1 4744FROM 4745(BB AS table1 INNER JOIN 4746(AA AS table2 STRAIGHT_JOIN A AS table3 4747ON ( table3 . `date_key` = table2 . `pk` )) 4748ON ( table3 . `datetime_key` = table2 . `int_nokey` )) 4749WHERE ( table3 . `date_key` <= 4 AND table2 . `pk` = table1 . `varchar_nokey`) 4750GROUP BY field1 ; 4751field1 4752SELECT table3 .`date_key` field1 4753FROM 4754B table1 LEFT JOIN B table3 JOIN 4755(BB table6 JOIN A table7 ON table6 .`varchar_nokey`) 4756ON table6 .`int_nokey` ON table6 .`date_key` 4757 WHERE NOT ( table1 .`varchar_key` AND table7 .`pk`) GROUP BY field1; 4758field1 4759NULL 4760SELECT table4 . `time_nokey` AS field1 FROM 4761(AA AS table1 CROSS JOIN 4762(AA AS table2 STRAIGHT_JOIN 4763(B AS table3 STRAIGHT_JOIN A AS table4 4764ON ( table4 . `date_key` = table3 . `time_key` )) 4765ON ( table4 . `pk` = table3 . `date_nokey` )) 4766ON ( table4 . `time_key` = table3 . `datetime_nokey` )) 4767WHERE ( table4 . `time_key` < table1 . `time_key` AND 4768table1 . `int_nokey` != 'f') 4769GROUP BY field1 ORDER BY field1 , field1; 4770field1 4771SELECT table1 .`time_key` field2 FROM B table1 LEFT JOIN BB JOIN A table5 ON table5 .`date_nokey` ON table5 .`int_nokey` GROUP BY field2; 4772field2 477300:05:48 477415:13:38 4775drop table A,AA,B,BB; 4776#end of test for bug#45266 4777# 4778# Bug#33546: Slowdown on re-evaluation of constant expressions. 4779# 4780CREATE TABLE t1 (a INT); 4781INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); 4782CREATE TABLE t2 (b INT); 4783INSERT INTO t2 VALUES (2); 4784SELECT * FROM t1 WHERE a = 1 + 1; 4785a 47862 4787EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = 1 + 1; 4788id select_type table type possible_keys key key_len ref rows filtered Extra 47891 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where 4790Warnings: 4791Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = <cache>((1 + 1))) 4792SELECT * FROM t1 HAVING a = 1 + 1; 4793a 47942 4795EXPLAIN EXTENDED SELECT * FROM t1 HAVING a = 1 + 1; 4796id select_type table type possible_keys key key_len ref rows filtered Extra 47971 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 NULL 4798Warnings: 4799Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` having (`test`.`t1`.`a` = <cache>((1 + 1))) 4800SELECT * FROM t1, t2 WHERE a = b + (1 + 1); 4801a b 48024 2 4803EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE a = b + (1 + 1); 4804id select_type table type possible_keys key key_len ref rows filtered Extra 48051 SIMPLE t2 system NULL NULL NULL NULL 1 100.00 NULL 48061 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where 4807Warnings: 4808Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,'2' AS `b` from `test`.`t1` where (`test`.`t1`.`a` = <cache>(('2' + (1 + 1)))) 4809SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1; 4810b a 48112 3 4812EXPLAIN EXTENDED SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1; 4813id select_type table type possible_keys key key_len ref rows filtered Extra 48141 SIMPLE t2 system NULL NULL NULL NULL 1 100.00 NULL 48151 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where 4816Warnings: 4817Note 1003 /* select#1 */ select '2' AS `b`,`test`.`t1`.`a` AS `a` from `test`.`t1` where 1 4818EXPLAIN EXTENDED SELECT * FROM t1 WHERE a > UNIX_TIMESTAMP('2009-03-10 00:00:00'); 4819id select_type table type possible_keys key key_len ref rows filtered Extra 48201 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where 4821Warnings: 4822Note 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'))) 4823CREATE FUNCTION f1() RETURNS INT DETERMINISTIC 4824BEGIN 4825SET @cnt := @cnt + 1; 4826RETURN 1; 4827END;| 4828SET @cnt := 0; 4829SELECT * FROM t1 WHERE a = f1(); 4830a 48311 4832SELECT @cnt; 4833@cnt 48341 4835EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = f1(); 4836id select_type table type possible_keys key key_len ref rows filtered Extra 48371 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where 4838Warnings: 4839Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = <cache>(`f1`())) 4840DROP TABLE t1, t2; 4841DROP FUNCTION f1; 4842# End of bug#33546 4843# 4844# BUG#48052: Valgrind warning - uninitialized value in init_read_record() 4845# 4846# Disable Index condition pushdown 4847SELECT @old_optimizer_switch:=@@optimizer_switch; 4848@old_optimizer_switch:=@@optimizer_switch 4849# 4850CREATE TABLE t1 ( 4851pk int(11) NOT NULL, 4852i int(11) DEFAULT NULL, 4853v varchar(1) DEFAULT NULL, 4854PRIMARY KEY (pk) 4855); 4856INSERT INTO t1 VALUES (2,7,'m'); 4857INSERT INTO t1 VALUES (3,9,'m'); 4858SELECT v 4859FROM t1 4860WHERE NOT pk > 0 4861HAVING v <= 't' 4862ORDER BY pk; 4863v 4864# Restore old value for Index condition pushdown 4865SET SESSION optimizer_switch=@old_optimizer_switch; 4866DROP TABLE t1; 4867# 4868# Bug#49489 Uninitialized cache led to a wrong result. 4869# 4870CREATE TABLE t1(c1 DOUBLE(5,4)); 4871INSERT INTO t1 VALUES (9.1234); 4872SELECT * FROM t1 WHERE c1 < 9.12345; 4873c1 48749.1234 4875DROP TABLE t1; 4876# End of test for bug#49489. 4877# 4878# Bug #49517: Inconsistent behavior while using 4879# NULLable BIGINT and INT columns in comparison 4880# 4881CREATE TABLE t1(a BIGINT UNSIGNED NOT NULL, b BIGINT NULL, c INT NULL); 4882INSERT INTO t1 VALUES(105, NULL, NULL); 4883SELECT * FROM t1 WHERE b < 102; 4884a b c 4885SELECT * FROM t1 WHERE c < 102; 4886a b c 4887SELECT * FROM t1 WHERE 102 < b; 4888a b c 4889SELECT * FROM t1 WHERE 102 < c; 4890a b c 4891DROP TABLE t1; 4892# 4893# Bug #54459: Assertion failed: param.sort_length, 4894# file .\filesort.cc, line 149 (part II) 4895# 4896CREATE TABLE t1(a ENUM('') NOT NULL); 4897INSERT INTO t1 VALUES (), (), (); 4898EXPLAIN SELECT 1 FROM t1 ORDER BY a COLLATE latin1_german2_ci; 4899id select_type table type possible_keys key key_len ref rows Extra 49001 SIMPLE t1 ALL NULL NULL NULL NULL 3 NULL 4901SELECT 1 FROM t1 ORDER BY a COLLATE latin1_german2_ci; 49021 49031 49041 49051 4906DROP TABLE t1; 4907# 4908# Bug #58422: Incorrect result when OUTER JOIN'ing 4909# with an empty table 4910# 4911CREATE TABLE t_empty(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM; 4912CREATE TABLE t1(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM; 4913INSERT INTO t1 VALUES (1,1), (2,2), (3,3); 4914CREATE TABLE t2(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM; 4915INSERT INTO t2 VALUES (1,1), (2,2), (3,3); 4916EXPLAIN 4917SELECT * 4918FROM 4919t1 4920LEFT OUTER JOIN 4921(t2 INNER JOIN t_empty ON TRUE) 4922ON t1.pk=t2.pk 4923WHERE t2.pk <> 2; 4924id select_type table type possible_keys key key_len ref rows Extra 49251 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 4926SELECT * 4927FROM 4928t1 4929LEFT OUTER JOIN 4930(t2 INNER JOIN t_empty ON TRUE) 4931ON t1.pk=t2.pk 4932WHERE t2.pk <> 2; 4933pk i pk i pk i 4934EXPLAIN 4935SELECT * 4936FROM 4937t1 4938LEFT OUTER JOIN 4939(t2 CROSS JOIN t_empty) 4940ON t1.pk=t2.pk 4941WHERE t2.pk <> 2; 4942id select_type table type possible_keys key key_len ref rows Extra 49431 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 4944SELECT * 4945FROM 4946t1 4947LEFT OUTER JOIN 4948(t2 CROSS JOIN t_empty) 4949ON t1.pk=t2.pk 4950WHERE t2.pk <> 2; 4951pk i pk i pk i 4952EXPLAIN 4953SELECT * 4954FROM 4955t1 4956LEFT OUTER JOIN 4957(t2 INNER JOIN t_empty ON t_empty.i=t2.i) 4958ON t1.pk=t2.pk 4959WHERE t2.pk <> 2; 4960id select_type table type possible_keys key key_len ref rows Extra 49611 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 4962SELECT * 4963FROM 4964t1 4965LEFT OUTER JOIN 4966(t2 INNER JOIN t_empty ON t_empty.i=t2.i) 4967ON t1.pk=t2.pk 4968WHERE t2.pk <> 2; 4969pk i pk i pk i 4970DROP TABLE t1,t2,t_empty; 4971End of 5.1 tests 4972# 4973# Bug#45227: Lost HAVING clause led to a wrong result. 4974# 4975CREATE TABLE `CC` ( 4976`int_nokey` int(11) NOT NULL, 4977`int_key` int(11) NOT NULL, 4978`varchar_key` varchar(1) NOT NULL, 4979`varchar_nokey` varchar(1) NOT NULL, 4980KEY `int_key` (`int_key`), 4981KEY `varchar_key` (`varchar_key`) 4982); 4983INSERT INTO `CC` VALUES 4984(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' 4985,'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'), 4986(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' 4987,'x'); 4988EXPLAIN SELECT `varchar_nokey` G1 FROM CC WHERE `int_nokey` AND `int_key` <= 4 4989HAVING G1 ORDER BY `varchar_key` LIMIT 6 ; 4990id select_type table type possible_keys key key_len ref rows Extra 49911 SIMPLE CC range int_key int_key 4 NULL 10 Using index condition; Using where; Using MRR; Using filesort 4992SELECT `varchar_nokey` G1 FROM CC WHERE `int_nokey` AND `int_key` <= 4 4993HAVING G1 ORDER BY `varchar_key` LIMIT 6 ; 4994G1 4995Warnings: 4996Warning 1292 Truncated incorrect DOUBLE value: 'j' 4997Warning 1292 Truncated incorrect DOUBLE value: 'z' 4998Warning 1292 Truncated incorrect DOUBLE value: 'a' 4999Warning 1292 Truncated incorrect DOUBLE value: 'q' 5000Warning 1292 Truncated incorrect DOUBLE value: 'm' 5001DROP TABLE CC; 5002# End of test#45227 5003# 5004# Bug#54515: Crash in opt_range.cc::get_best_group_min_max on 5005# SELECT from VIEW with GROUP BY 5006# 5007CREATE TABLE t1 ( 5008col_int_key int DEFAULT NULL, 5009KEY int_key (col_int_key) 5010) ; 5011INSERT INTO t1 VALUES (1),(2); 5012CREATE VIEW view_t1 AS 5013SELECT t1.col_int_key AS col_int_key 5014FROM t1; 5015SELECT col_int_key FROM view_t1 GROUP BY col_int_key; 5016col_int_key 50171 50182 5019DROP VIEW view_t1; 5020DROP TABLE t1; 5021# End of test BUG#54515 5022# 5023# Bug #57203 Assertion `field_length <= 255' failed. 5024# 5025SELECT coalesce((avg(distinct (geomfromtext("point(25379 -22010)"))))) 5026UNION ALL 5027SELECT coalesce((avg(distinct (geomfromtext("point(25379 -22010)"))))) 5028AS foo 5029; 5030coalesce((avg(distinct (geomfromtext("point(25379 -22010)"))))) 50310.0000 50320.0000 5033CREATE table t1(a text); 5034INSERT INTO t1 VALUES (''), (''); 5035SELECT avg(distinct(t1.a)) FROM t1, t1 t2 5036GROUP BY t2.a ORDER BY t1.a; 5037avg(distinct(t1.a)) 50380 5039DROP TABLE t1; 5040# End of test BUG#57203 5041# 5042# Bug#63020: Function "format"'s 'locale' argument is not considered 5043# when creating a "view' 5044# 5045CREATE TABLE t1 (f1 DECIMAL(10,2)); 5046INSERT INTO t1 VALUES (11.67),(17865.3),(12345678.92); 5047CREATE VIEW view_t1 AS SELECT FORMAT(f1,1,'sk_SK') AS f1 FROM t1; 5048SHOW CREATE VIEW view_t1; 5049View Create View character_set_client collation_connection 5050view_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 5051SELECT * FROM view_t1; 5052f1 505311,7 505417 865,3 505512 345 678,9 5056DROP TABLE t1; 5057DROP VIEW view_t1; 5058# End of test BUG#63020 5059# 5060# Bug #13571700 TINYBLOB NOT NULL, CRASH IN PROTOCOL::NET_STORE_DATA 5061# 5062CREATE TABLE t1 (a TINYBLOB NOT NULL); 5063SELECT a, COUNT(*) FROM t1 WHERE 0; 5064a COUNT(*) 5065NULL 0 5066DROP TABLE t1; 5067# End of test BUG#13571700 5068set optimizer_switch=default; 5069