1set optimizer_switch='batched_key_access=on,mrr_cost_based=off'; 2set optimizer_switch='index_condition_pushdown=on,mrr=on,mrr_cost_based=off'; 3drop table if exists t1,t2,t3,t4,t11; 4drop table if exists t1_1,t1_2,t9_1,t9_2,t1aa,t2aa; 5drop view if exists v1; 6CREATE TABLE t1 ( 7Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL, 8Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL 9); 10INSERT INTO t1 VALUES (9410,9412); 11select period from t1; 12period 139410 14select * from t1; 15Period Varor_period 169410 9412 17select t1.* from t1; 18Period Varor_period 199410 9412 20CREATE TABLE t2 ( 21auto int not null auto_increment, 22fld1 int(6) unsigned zerofill DEFAULT '000000' NOT NULL, 23companynr tinyint(2) unsigned zerofill DEFAULT '00' NOT NULL, 24fld3 char(30) DEFAULT '' NOT NULL, 25fld4 char(35) DEFAULT '' NOT NULL, 26fld5 char(35) DEFAULT '' NOT NULL, 27fld6 char(4) DEFAULT '' NOT NULL, 28UNIQUE fld1 (fld1), 29KEY fld3 (fld3), 30PRIMARY KEY (auto) 31); 32select t2.fld3 from t2 where companynr = 58 and fld3 like "%imaginable%"; 33fld3 34imaginable 35select fld3 from t2 where fld3 like "%cultivation" ; 36fld3 37cultivation 38select t2.fld3,companynr from t2 where companynr = 57+1 order by fld3; 39fld3 companynr 40concoct 58 41druggists 58 42engrossing 58 43Eurydice 58 44exclaimers 58 45ferociousness 58 46hopelessness 58 47Huey 58 48imaginable 58 49judges 58 50merging 58 51ostrich 58 52peering 58 53Phelps 58 54presumes 58 55Ruth 58 56sentences 58 57Shylock 58 58straggled 58 59synergy 58 60thanking 58 61tying 58 62unlocks 58 63select fld3,companynr from t2 where companynr = 58 order by fld3; 64fld3 companynr 65concoct 58 66druggists 58 67engrossing 58 68Eurydice 58 69exclaimers 58 70ferociousness 58 71hopelessness 58 72Huey 58 73imaginable 58 74judges 58 75merging 58 76ostrich 58 77peering 58 78Phelps 58 79presumes 58 80Ruth 58 81sentences 58 82Shylock 58 83straggled 58 84synergy 58 85thanking 58 86tying 58 87unlocks 58 88select fld3 from t2 order by fld3 desc limit 10; 89fld3 90youthfulness 91yelped 92Wotan 93workers 94Witt 95witchcraft 96Winsett 97Willy 98willed 99wildcats 100select fld3 from t2 order by fld3 desc limit 5; 101fld3 102youthfulness 103yelped 104Wotan 105workers 106Witt 107select fld3 from t2 order by fld3 desc limit 5,5; 108fld3 109witchcraft 110Winsett 111Willy 112willed 113wildcats 114select t2.fld3 from t2 where fld3 = 'honeysuckle'; 115fld3 116honeysuckle 117select t2.fld3 from t2 where fld3 LIKE 'honeysuckl_'; 118fld3 119honeysuckle 120select t2.fld3 from t2 where fld3 LIKE 'hon_ysuckl_'; 121fld3 122honeysuckle 123select t2.fld3 from t2 where fld3 LIKE 'honeysuckle%'; 124fld3 125honeysuckle 126select t2.fld3 from t2 where fld3 LIKE 'h%le'; 127fld3 128honeysuckle 129select t2.fld3 from t2 where fld3 LIKE 'honeysuckle_'; 130fld3 131select t2.fld3 from t2 where fld3 LIKE 'don_t_find_me_please%'; 132fld3 133explain select t2.fld3 from t2 where fld3 = 'honeysuckle'; 134id select_type table type possible_keys key key_len ref rows Extra 1351 SIMPLE t2 ref fld3 fld3 30 const 1 Using where; Using index 136explain select fld3 from t2 ignore index (fld3) where fld3 = 'honeysuckle'; 137id select_type table type possible_keys key key_len ref rows Extra 1381 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 139explain select fld3 from t2 use index (fld1) where fld3 = 'honeysuckle'; 140id select_type table type possible_keys key key_len ref rows Extra 1411 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 142explain select fld3 from t2 use index (fld3) where fld3 = 'honeysuckle'; 143id select_type table type possible_keys key key_len ref rows Extra 1441 SIMPLE t2 ref fld3 fld3 30 const 1 Using where; Using index 145explain select fld3 from t2 use index (fld1,fld3) where fld3 = 'honeysuckle'; 146id select_type table type possible_keys key key_len ref rows Extra 1471 SIMPLE t2 ref fld3 fld3 30 const 1 Using where; Using index 148explain select fld3 from t2 ignore index (fld3,not_used); 149ERROR 42000: Key 'not_used' doesn't exist in table 't2' 150explain select fld3 from t2 use index (not_used); 151ERROR 42000: Key 'not_used' doesn't exist in table 't2' 152select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3; 153fld3 154honeysuckle 155honoring 156explain select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3; 157id select_type table type possible_keys key key_len ref rows Extra 1581 SIMPLE t2 range fld3 fld3 30 NULL 2 Using where; Using index 159select fld1,fld3 from t2 where fld3="Colombo" or fld3 = "nondecreasing" order by fld3; 160fld1 fld3 161148504 Colombo 162068305 Colombo 163000000 nondecreasing 164select fld1,fld3 from t2 where companynr = 37 and fld3 = 'appendixes'; 165fld1 fld3 166232605 appendixes 1671232605 appendixes 1681232606 appendixes 1691232607 appendixes 1701232608 appendixes 1711232609 appendixes 172select fld1 from t2 where fld1=250501 or fld1="250502"; 173fld1 174250501 175250502 176explain select fld1 from t2 where fld1=250501 or fld1="250502"; 177id select_type table type possible_keys key key_len ref rows Extra 1781 SIMPLE t2 range fld1 fld1 4 NULL 2 Using where; Using index 179select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502; 180fld1 181250501 182250502 183250505 184250601 185explain select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502; 186id select_type table type possible_keys key key_len ref rows Extra 1871 SIMPLE t2 range fld1 fld1 4 NULL 4 Using where; Using index 188select fld1,fld3 from t2 where companynr = 37 and fld3 like 'f%'; 189fld1 fld3 190012001 flanking 191013602 foldout 192013606 fingerings 193018007 fanatic 194018017 featherweight 195018054 fetters 196018103 flint 197018104 flopping 198036002 funereal 199038017 fetched 200038205 firearm 201058004 Fenton 202088303 feminine 203186002 freakish 204188007 flurried 205188505 fitting 206198006 furthermore 207202301 Fitzpatrick 208208101 fiftieth 209208113 freest 210218008 finishers 211218022 feed 212218401 faithful 213226205 foothill 214226209 furnishings 215228306 forthcoming 216228311 fated 217231315 freezes 218232102 forgivably 219238007 filial 220238008 fixedly 221select fld3 from t2 where fld3 like "L%" and fld3 = "ok"; 222fld3 223select fld3 from t2 where (fld3 like "C%" and fld3 = "Chantilly"); 224fld3 225Chantilly 226select fld1,fld3 from t2 where fld1 like "25050%"; 227fld1 fld3 228250501 poisoning 229250502 Iraqis 230250503 heaving 231250504 population 232250505 bomb 233select fld1,fld3 from t2 where fld1 like "25050_"; 234fld1 fld3 235250501 poisoning 236250502 Iraqis 237250503 heaving 238250504 population 239250505 bomb 240select distinct companynr from t2; 241companynr 24200 24337 24436 24550 24658 24729 24840 24953 25065 25141 25234 25368 254select distinct companynr from t2 order by companynr; 255companynr 25600 25729 25834 25936 26037 26140 26241 26350 26453 26558 26665 26768 268select distinct companynr from t2 order by companynr desc; 269companynr 27068 27165 27258 27353 27450 27541 27640 27737 27836 27934 28029 28100 282select distinct t2.fld3,period from t2,t1 where companynr=37 and fld3 like "O%"; 283fld3 period 284obliterates 9410 285offload 9410 286opaquely 9410 287organizer 9410 288overestimating 9410 289overlay 9410 290select distinct fld3 from t2 where companynr = 34 order by fld3; 291fld3 292absentee 293accessed 294ahead 295alphabetic 296Asiaticizations 297attitude 298aye 299bankruptcies 300belays 301Blythe 302bomb 303boulevard 304bulldozes 305cannot 306caressing 307charcoal 308checksumming 309chess 310clubroom 311colorful 312cosy 313creator 314crying 315Darius 316diffusing 317duality 318Eiffel 319Epiphany 320Ernestine 321explorers 322exterminated 323famine 324forked 325Gershwins 326heaving 327Hodges 328Iraqis 329Italianization 330Lagos 331landslide 332libretto 333Majorca 334mastering 335narrowed 336occurred 337offerers 338Palestine 339Peruvianizes 340pharmaceutic 341poisoning 342population 343Pygmalion 344rats 345realest 346recording 347regimented 348retransmitting 349reviver 350rouses 351scars 352sicker 353sleepwalk 354stopped 355sugars 356translatable 357uncles 358unexpected 359uprisings 360versatility 361vest 362select distinct fld3 from t2 limit 10; 363fld3 364abates 365abiding 366Abraham 367abrogating 368absentee 369abut 370accessed 371accruing 372accumulating 373accuracies 374select distinct fld3 from t2 having fld3 like "A%" limit 10; 375fld3 376abates 377abiding 378Abraham 379abrogating 380absentee 381abut 382accessed 383accruing 384accumulating 385accuracies 386select distinct substring(fld3,1,3) from t2 where fld3 like "A%"; 387substring(fld3,1,3) 388aba 389abi 390Abr 391abs 392abu 393acc 394acq 395acu 396Ade 397adj 398Adl 399adm 400Ado 401ads 402adv 403aer 404aff 405afi 406afl 407afo 408agi 409ahe 410aim 411air 412Ald 413alg 414ali 415all 416alp 417alr 418ama 419ame 420amm 421ana 422and 423ane 424Ang 425ani 426Ann 427Ant 428api 429app 430aqu 431Ara 432arc 433Arm 434arr 435Art 436Asi 437ask 438asp 439ass 440ast 441att 442aud 443Aug 444aut 445ave 446avo 447awe 448aye 449Azt 450select distinct substring(fld3,1,3) as a from t2 having a like "A%" order by a limit 10; 451a 452aba 453abi 454Abr 455abs 456abu 457acc 458acq 459acu 460Ade 461adj 462select distinct substring(fld3,1,3) from t2 where fld3 like "A%" limit 10; 463substring(fld3,1,3) 464aba 465abi 466Abr 467abs 468abu 469acc 470acq 471acu 472Ade 473adj 474select distinct substring(fld3,1,3) as a from t2 having a like "A%" limit 10; 475a 476aba 477abi 478Abr 479abs 480abu 481acc 482acq 483acu 484Ade 485adj 486create table t3 ( 487period int not null, 488name char(32) not null, 489companynr int not null, 490price double(11,0), 491price2 double(11,0), 492key (period), 493key (name) 494); 495create temporary table tmp engine = myisam select * from t3; 496insert into t3 select * from tmp; 497insert into tmp select * from t3; 498insert into t3 select * from tmp; 499insert into tmp select * from t3; 500insert into t3 select * from tmp; 501insert into tmp select * from t3; 502insert into t3 select * from tmp; 503insert into tmp select * from t3; 504insert into t3 select * from tmp; 505insert into tmp select * from t3; 506insert into t3 select * from tmp; 507insert into tmp select * from t3; 508insert into t3 select * from tmp; 509insert into tmp select * from t3; 510insert into t3 select * from tmp; 511insert into tmp select * from t3; 512insert into t3 select * from tmp; 513alter table t3 add t2nr int not null auto_increment primary key first; 514drop table tmp; 515SET BIG_TABLES=1; 516select distinct concat(fld3," ",fld3) as namn from t2,t3 where t2.fld1=t3.t2nr order by namn limit 10; 517namn 518Abraham Abraham 519abrogating abrogating 520admonishing admonishing 521Adolph Adolph 522afield afield 523aging aging 524ammonium ammonium 525analyzable analyzable 526animals animals 527animized animized 528SET BIG_TABLES=0; 529select distinct concat(fld3," ",fld3) from t2,t3 where t2.fld1=t3.t2nr order by fld3 limit 10; 530concat(fld3," ",fld3) 531Abraham Abraham 532abrogating abrogating 533admonishing admonishing 534Adolph Adolph 535afield afield 536aging aging 537ammonium ammonium 538analyzable analyzable 539animals animals 540animized animized 541select distinct fld5 from t2 limit 10; 542fld5 543neat 544Steinberg 545jarring 546tinily 547balled 548persist 549attainments 550fanatic 551measures 552rightfulness 553select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10; 554fld3 count(*) 555affixed 1 556and 1 557annoyers 1 558Anthony 1 559assayed 1 560assurers 1 561attendants 1 562bedlam 1 563bedpost 1 564boasted 1 565SET BIG_TABLES=1; 566select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10; 567fld3 count(*) 568affixed 1 569and 1 570annoyers 1 571Anthony 1 572assayed 1 573assurers 1 574attendants 1 575bedlam 1 576bedpost 1 577boasted 1 578SET BIG_TABLES=0; 579select distinct fld3,repeat("a",length(fld3)),count(*) from t2 group by companynr,fld3 limit 100,10; 580fld3 repeat("a",length(fld3)) count(*) 581circus aaaaaa 1 582cited aaaaa 1 583Colombo aaaaaaa 1 584congresswoman aaaaaaaaaaaaa 1 585contrition aaaaaaaaaa 1 586corny aaaaa 1 587cultivation aaaaaaaaaaa 1 588definiteness aaaaaaaaaaaa 1 589demultiplex aaaaaaaaaaa 1 590disappointing aaaaaaaaaaaaa 1 591select distinct companynr,rtrim(space(512+companynr)) from t3 order by 1,2; 592companynr rtrim(space(512+companynr)) 59337 59478 595101 596154 597311 598447 599512 600select distinct fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by fld3; 601fld3 602explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by t3.t2nr,fld3; 603id select_type table type possible_keys key key_len ref rows Extra 6041 SIMPLE t2 ALL fld1 NULL NULL NULL 1199 Using where; Using temporary; Using filesort 6051 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.fld1 1 Using where; Using index 606explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period; 607id select_type table type possible_keys key key_len ref rows Extra 6081 SIMPLE t1 ALL period NULL NULL NULL 41810 Using temporary; Using filesort 6091 SIMPLE t3 ref period period 4 test.t1.period 4181 Using join buffer (Batched Key Access) 610explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10; 611id select_type table type possible_keys key key_len ref rows Extra 6121 SIMPLE t3 ALL period NULL NULL NULL 41810 Using temporary; Using filesort 6131 SIMPLE t1 ref period period 4 test.t3.period 4181 Using join buffer (Batched Key Access) 614explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10; 615id select_type table type possible_keys key key_len ref rows Extra 6161 SIMPLE t1 ALL period NULL NULL NULL 41810 Using temporary; Using filesort 6171 SIMPLE t3 ref period period 4 test.t1.period 4181 Using join buffer (Batched Key Access) 618select period from t1; 619period 6209410 621select period from t1 where period=1900; 622period 623select fld3,period from t1,t2 where fld1 = 011401 order by period; 624fld3 period 625breaking 9410 626select fld3,period from t2,t3 where t2.fld1 = 011401 and t2.fld1=t3.t2nr and t3.period=1001; 627fld3 period 628breaking 1001 629explain select fld3,period from t2,t3 where t2.fld1 = 011401 and t3.t2nr=t2.fld1 and 1001 = t3.period; 630id select_type table type possible_keys key key_len ref rows Extra 6311 SIMPLE t2 const fld1 fld1 4 const 1 NULL 6321 SIMPLE t3 const PRIMARY,period PRIMARY 4 const 1 NULL 633select fld3,period from t2,t1 where companynr*10 = 37*10; 634fld3 period 635breaking 9410 636Romans 9410 637intercepted 9410 638bewilderingly 9410 639astound 9410 640admonishing 9410 641sumac 9410 642flanking 9410 643combed 9410 644subjective 9410 645scatterbrain 9410 646Eulerian 9410 647Kane 9410 648overlay 9410 649perturb 9410 650goblins 9410 651annihilates 9410 652Wotan 9410 653snatching 9410 654concludes 9410 655laterally 9410 656yelped 9410 657grazing 9410 658Baird 9410 659celery 9410 660misunderstander 9410 661handgun 9410 662foldout 9410 663mystic 9410 664succumbed 9410 665Nabisco 9410 666fingerings 9410 667aging 9410 668afield 9410 669ammonium 9410 670boat 9410 671intelligibility 9410 672Augustine 9410 673teethe 9410 674dreaded 9410 675scholastics 9410 676audiology 9410 677wallet 9410 678parters 9410 679eschew 9410 680quitter 9410 681neat 9410 682Steinberg 9410 683jarring 9410 684tinily 9410 685balled 9410 686persist 9410 687attainments 9410 688fanatic 9410 689measures 9410 690rightfulness 9410 691capably 9410 692impulsive 9410 693starlet 9410 694terminators 9410 695untying 9410 696announces 9410 697featherweight 9410 698pessimist 9410 699daughter 9410 700decliner 9410 701lawgiver 9410 702stated 9410 703readable 9410 704attrition 9410 705cascade 9410 706motors 9410 707interrogate 9410 708pests 9410 709stairway 9410 710dopers 9410 711testicle 9410 712Parsifal 9410 713leavings 9410 714postulation 9410 715squeaking 9410 716contrasted 9410 717leftover 9410 718whiteners 9410 719erases 9410 720Punjab 9410 721Merritt 9410 722Quixotism 9410 723sweetish 9410 724dogging 9410 725scornfully 9410 726bellow 9410 727bills 9410 728cupboard 9410 729sureties 9410 730puddings 9410 731fetters 9410 732bivalves 9410 733incurring 9410 734Adolph 9410 735pithed 9410 736Miles 9410 737trimmings 9410 738tragedies 9410 739skulking 9410 740flint 9410 741flopping 9410 742relaxing 9410 743offload 9410 744suites 9410 745lists 9410 746animized 9410 747multilayer 9410 748standardizes 9410 749Judas 9410 750vacuuming 9410 751dentally 9410 752humanness 9410 753inch 9410 754Weissmuller 9410 755irresponsibly 9410 756luckily 9410 757culled 9410 758medical 9410 759bloodbath 9410 760subschema 9410 761animals 9410 762Micronesia 9410 763repetitions 9410 764Antares 9410 765ventilate 9410 766pityingly 9410 767interdependent 9410 768Graves 9410 769neonatal 9410 770chafe 9410 771honoring 9410 772realtor 9410 773elite 9410 774funereal 9410 775abrogating 9410 776sorters 9410 777Conley 9410 778lectured 9410 779Abraham 9410 780Hawaii 9410 781cage 9410 782hushes 9410 783Simla 9410 784reporters 9410 785Dutchman 9410 786descendants 9410 787groupings 9410 788dissociate 9410 789coexist 9410 790Beebe 9410 791Taoism 9410 792Connally 9410 793fetched 9410 794checkpoints 9410 795rusting 9410 796galling 9410 797obliterates 9410 798traitor 9410 799resumes 9410 800analyzable 9410 801terminator 9410 802gritty 9410 803firearm 9410 804minima 9410 805Selfridge 9410 806disable 9410 807witchcraft 9410 808betroth 9410 809Manhattanize 9410 810imprint 9410 811peeked 9410 812swelling 9410 813interrelationships 9410 814riser 9410 815Gandhian 9410 816peacock 9410 817bee 9410 818kanji 9410 819dental 9410 820scarf 9410 821chasm 9410 822insolence 9410 823syndicate 9410 824alike 9410 825imperial 9410 826convulsion 9410 827railway 9410 828validate 9410 829normalizes 9410 830comprehensive 9410 831chewing 9410 832denizen 9410 833schemer 9410 834chronicle 9410 835Kline 9410 836Anatole 9410 837partridges 9410 838brunch 9410 839recruited 9410 840dimensions 9410 841Chicana 9410 842announced 9410 843praised 9410 844employing 9410 845linear 9410 846quagmire 9410 847western 9410 848relishing 9410 849serving 9410 850scheduling 9410 851lore 9410 852eventful 9410 853arteriole 9410 854disentangle 9410 855cured 9410 856Fenton 9410 857avoidable 9410 858drains 9410 859detectably 9410 860husky 9410 861impelling 9410 862undoes 9410 863evened 9410 864squeezes 9410 865destroyer 9410 866rudeness 9410 867beaner 9410 868boorish 9410 869Everhart 9410 870encompass 9410 871mushrooms 9410 872Alison 9410 873externally 9410 874pellagra 9410 875cult 9410 876creek 9410 877Huffman 9410 878Majorca 9410 879governing 9410 880gadfly 9410 881reassigned 9410 882intentness 9410 883craziness 9410 884psychic 9410 885squabbled 9410 886burlesque 9410 887capped 9410 888extracted 9410 889DiMaggio 9410 890exclamation 9410 891subdirectory 9410 892Gothicism 9410 893feminine 9410 894metaphysically 9410 895sanding 9410 896Miltonism 9410 897freakish 9410 898index 9410 899straight 9410 900flurried 9410 901denotative 9410 902coming 9410 903commencements 9410 904gentleman 9410 905gifted 9410 906Shanghais 9410 907sportswriting 9410 908sloping 9410 909navies 9410 910leaflet 9410 911shooter 9410 912Joplin 9410 913babies 9410 914assails 9410 915admiring 9410 916swaying 9410 917Goldstine 9410 918fitting 9410 919Norwalk 9410 920analogy 9410 921deludes 9410 922cokes 9410 923Clayton 9410 924exhausts 9410 925causality 9410 926sating 9410 927icon 9410 928throttles 9410 929communicants 9410 930dehydrate 9410 931priceless 9410 932publicly 9410 933incidentals 9410 934commonplace 9410 935mumbles 9410 936furthermore 9410 937cautioned 9410 938parametrized 9410 939registration 9410 940sadly 9410 941positioning 9410 942babysitting 9410 943eternal 9410 944hoarder 9410 945congregates 9410 946rains 9410 947workers 9410 948sags 9410 949unplug 9410 950garage 9410 951boulder 9410 952specifics 9410 953Teresa 9410 954Winsett 9410 955convenient 9410 956buckboards 9410 957amenities 9410 958resplendent 9410 959sews 9410 960participated 9410 961Simon 9410 962certificates 9410 963Fitzpatrick 9410 964Evanston 9410 965misted 9410 966textures 9410 967save 9410 968count 9410 969rightful 9410 970chaperone 9410 971Lizzy 9410 972clenched 9410 973effortlessly 9410 974accessed 9410 975beaters 9410 976Hornblower 9410 977vests 9410 978indulgences 9410 979infallibly 9410 980unwilling 9410 981excrete 9410 982spools 9410 983crunches 9410 984overestimating 9410 985ineffective 9410 986humiliation 9410 987sophomore 9410 988star 9410 989rifles 9410 990dialysis 9410 991arriving 9410 992indulge 9410 993clockers 9410 994languages 9410 995Antarctica 9410 996percentage 9410 997ceiling 9410 998specification 9410 999regimented 9410 1000ciphers 9410 1001pictures 9410 1002serpents 9410 1003allot 9410 1004realized 9410 1005mayoral 9410 1006opaquely 9410 1007hostess 9410 1008fiftieth 9410 1009incorrectly 9410 1010decomposition 9410 1011stranglings 9410 1012mixture 9410 1013electroencephalography 9410 1014similarities 9410 1015charges 9410 1016freest 9410 1017Greenberg 9410 1018tinting 9410 1019expelled 9410 1020warm 9410 1021smoothed 9410 1022deductions 9410 1023Romano 9410 1024bitterroot 9410 1025corset 9410 1026securing 9410 1027environing 9410 1028cute 9410 1029Crays 9410 1030heiress 9410 1031inform 9410 1032avenge 9410 1033universals 9410 1034Kinsey 9410 1035ravines 9410 1036bestseller 9410 1037equilibrium 9410 1038extents 9410 1039relatively 9410 1040pressure 9410 1041critiques 9410 1042befouled 9410 1043rightfully 9410 1044mechanizing 9410 1045Latinizes 9410 1046timesharing 9410 1047Aden 9410 1048embassies 9410 1049males 9410 1050shapelessly 9410 1051mastering 9410 1052Newtonian 9410 1053finishers 9410 1054abates 9410 1055teem 9410 1056kiting 9410 1057stodgy 9410 1058feed 9410 1059guitars 9410 1060airships 9410 1061store 9410 1062denounces 9410 1063Pyle 9410 1064Saxony 9410 1065serializations 9410 1066Peruvian 9410 1067taxonomically 9410 1068kingdom 9410 1069stint 9410 1070Sault 9410 1071faithful 9410 1072Ganymede 9410 1073tidiness 9410 1074gainful 9410 1075contrary 9410 1076Tipperary 9410 1077tropics 9410 1078theorizers 9410 1079renew 9410 1080already 9410 1081terminal 9410 1082Hegelian 9410 1083hypothesizer 9410 1084warningly 9410 1085journalizing 9410 1086nested 9410 1087Lars 9410 1088saplings 9410 1089foothill 9410 1090labeled 9410 1091imperiously 9410 1092reporters 9410 1093furnishings 9410 1094precipitable 9410 1095discounts 9410 1096excises 9410 1097Stalin 9410 1098despot 9410 1099ripeness 9410 1100Arabia 9410 1101unruly 9410 1102mournfulness 9410 1103boom 9410 1104slaughter 9410 1105Sabine 9410 1106handy 9410 1107rural 9410 1108organizer 9410 1109shipyard 9410 1110civics 9410 1111inaccuracy 9410 1112rules 9410 1113juveniles 9410 1114comprised 9410 1115investigations 9410 1116stabilizes 9410 1117seminaries 9410 1118Hunter 9410 1119sporty 9410 1120test 9410 1121weasels 9410 1122CERN 9410 1123tempering 9410 1124afore 9410 1125Galatean 9410 1126techniques 9410 1127error 9410 1128veranda 9410 1129severely 9410 1130Cassites 9410 1131forthcoming 9410 1132guides 9410 1133vanish 9410 1134lied 9410 1135sawtooth 9410 1136fated 9410 1137gradually 9410 1138widens 9410 1139preclude 9410 1140evenhandedly 9410 1141percentage 9410 1142disobedience 9410 1143humility 9410 1144gleaning 9410 1145petted 9410 1146bloater 9410 1147minion 9410 1148marginal 9410 1149apiary 9410 1150measures 9410 1151precaution 9410 1152repelled 9410 1153primary 9410 1154coverings 9410 1155Artemia 9410 1156navigate 9410 1157spatial 9410 1158Gurkha 9410 1159meanwhile 9410 1160Melinda 9410 1161Butterfield 9410 1162Aldrich 9410 1163previewing 9410 1164glut 9410 1165unaffected 9410 1166inmate 9410 1167mineral 9410 1168impending 9410 1169meditation 9410 1170ideas 9410 1171miniaturizes 9410 1172lewdly 9410 1173title 9410 1174youthfulness 9410 1175creak 9410 1176Chippewa 9410 1177clamored 9410 1178freezes 9410 1179forgivably 9410 1180reduce 9410 1181McGovern 9410 1182Nazis 9410 1183epistle 9410 1184socializes 9410 1185conceptions 9410 1186Kevin 9410 1187uncovering 9410 1188chews 9410 1189appendixes 9410 1190appendixes 9410 1191appendixes 9410 1192appendixes 9410 1193appendixes 9410 1194appendixes 9410 1195raining 9410 1196infest 9410 1197compartment 9410 1198minting 9410 1199ducks 9410 1200roped 9410 1201waltz 9410 1202Lillian 9410 1203repressions 9410 1204chillingly 9410 1205noncritical 9410 1206lithograph 9410 1207spongers 9410 1208parenthood 9410 1209posed 9410 1210instruments 9410 1211filial 9410 1212fixedly 9410 1213relives 9410 1214Pandora 9410 1215watering 9410 1216ungrateful 9410 1217secures 9410 1218poison 9410 1219dusted 9410 1220encompasses 9410 1221presentation 9410 1222Kantian 9410 1223select 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; 1224fld3 period price price2 1225admonishing 1002 28357832 8723648 1226analyzable 1002 28357832 8723648 1227annihilates 1001 5987435 234724 1228Antares 1002 28357832 8723648 1229astound 1001 5987435 234724 1230audiology 1001 5987435 234724 1231Augustine 1002 28357832 8723648 1232Baird 1002 28357832 8723648 1233bewilderingly 1001 5987435 234724 1234breaking 1001 5987435 234724 1235Conley 1001 5987435 234724 1236dentally 1002 28357832 8723648 1237dissociate 1002 28357832 8723648 1238elite 1001 5987435 234724 1239eschew 1001 5987435 234724 1240Eulerian 1001 5987435 234724 1241flanking 1001 5987435 234724 1242foldout 1002 28357832 8723648 1243funereal 1002 28357832 8723648 1244galling 1002 28357832 8723648 1245Graves 1001 5987435 234724 1246grazing 1001 5987435 234724 1247groupings 1001 5987435 234724 1248handgun 1001 5987435 234724 1249humility 1002 28357832 8723648 1250impulsive 1002 28357832 8723648 1251inch 1001 5987435 234724 1252intelligibility 1001 5987435 234724 1253jarring 1001 5987435 234724 1254lawgiver 1001 5987435 234724 1255lectured 1002 28357832 8723648 1256Merritt 1002 28357832 8723648 1257neonatal 1001 5987435 234724 1258offload 1002 28357832 8723648 1259parters 1002 28357832 8723648 1260pityingly 1002 28357832 8723648 1261puddings 1002 28357832 8723648 1262Punjab 1001 5987435 234724 1263quitter 1002 28357832 8723648 1264realtor 1001 5987435 234724 1265relaxing 1001 5987435 234724 1266repetitions 1001 5987435 234724 1267resumes 1001 5987435 234724 1268Romans 1002 28357832 8723648 1269rusting 1001 5987435 234724 1270scholastics 1001 5987435 234724 1271skulking 1002 28357832 8723648 1272stated 1002 28357832 8723648 1273suites 1002 28357832 8723648 1274sureties 1001 5987435 234724 1275testicle 1002 28357832 8723648 1276tinily 1002 28357832 8723648 1277tragedies 1001 5987435 234724 1278trimmings 1001 5987435 234724 1279vacuuming 1001 5987435 234724 1280ventilate 1001 5987435 234724 1281wallet 1001 5987435 234724 1282Weissmuller 1002 28357832 8723648 1283Wotan 1002 28357832 8723648 1284select 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; 1285fld1 fld3 period price price2 1286018201 relaxing 1001 5987435 234724 1287018601 vacuuming 1001 5987435 234724 1288018801 inch 1001 5987435 234724 1289018811 repetitions 1001 5987435 234724 1290create table t4 ( 1291companynr tinyint(2) unsigned zerofill NOT NULL default '00', 1292companyname char(30) NOT NULL default '', 1293PRIMARY KEY (companynr), 1294UNIQUE KEY companyname(companyname) 1295) ENGINE=MyISAM MAX_ROWS=50 PACK_KEYS=1 COMMENT='companynames'; 1296select STRAIGHT_JOIN t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr; 1297companynr companyname 129800 Unknown 129929 company 1 130034 company 2 130136 company 3 130237 company 4 130340 company 5 130441 company 6 130550 company 11 130653 company 7 130758 company 8 130865 company 9 130968 company 10 1310select SQL_SMALL_RESULT t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr; 1311companynr companyname 131200 Unknown 131329 company 1 131434 company 2 131536 company 3 131637 company 4 131740 company 5 131841 company 6 131950 company 11 132053 company 7 132158 company 8 132265 company 9 132368 company 10 1324select * from t1,t1 t12; 1325Period Varor_period Period Varor_period 13269410 9412 9410 9412 1327select t2.fld1,t22.fld1 from t2,t2 t22 where t2.fld1 >= 250501 and t2.fld1 <= 250505 and t22.fld1 >= 250501 and t22.fld1 <= 250505; 1328fld1 fld1 1329250501 250501 1330250502 250501 1331250503 250501 1332250504 250501 1333250505 250501 1334250501 250502 1335250502 250502 1336250503 250502 1337250504 250502 1338250505 250502 1339250501 250503 1340250502 250503 1341250503 250503 1342250504 250503 1343250505 250503 1344250501 250504 1345250502 250504 1346250503 250504 1347250504 250504 1348250505 250504 1349250501 250505 1350250502 250505 1351250503 250505 1352250504 250505 1353250505 250505 1354insert into t2 (fld1, companynr) values (999999,99); 1355select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null; 1356companynr companyname 135799 NULL 1358select count(*) from t2 left join t4 using (companynr) where t4.companynr is not null; 1359count(*) 13601199 1361explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null; 1362id select_type table type possible_keys key key_len ref rows Extra 13631 SIMPLE t2 ALL NULL NULL NULL NULL 1200 NULL 13641 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 Using where; Not exists; Using join buffer (Batched Key Access) 1365explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null; 1366id select_type table type possible_keys key key_len ref rows Extra 13671 SIMPLE t4 ALL NULL NULL NULL NULL 12 NULL 13681 SIMPLE t2 ALL NULL NULL NULL NULL 1200 Using where; Not exists; Using join buffer (Block Nested Loop) 1369select companynr,companyname from t2 left join t4 using (companynr) where companynr is null; 1370companynr companyname 1371select count(*) from t2 left join t4 using (companynr) where companynr is not null; 1372count(*) 13731200 1374explain select companynr,companyname from t2 left join t4 using (companynr) where companynr is null; 1375id select_type table type possible_keys key key_len ref rows Extra 13761 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 1377explain select companynr,companyname from t4 left join t2 using (companynr) where companynr is null; 1378id select_type table type possible_keys key key_len ref rows Extra 13791 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 1380delete from t2 where fld1=999999; 1381explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0; 1382id select_type table type possible_keys key key_len ref rows Extra 13831 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 13841 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 Using join buffer (Batched Key Access) 1385explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0; 1386id select_type table type possible_keys key key_len ref rows Extra 13871 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 13881 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 Using join buffer (Batched Key Access) 1389explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0; 1390id select_type table type possible_keys key key_len ref rows Extra 13911 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 13921 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 Using join buffer (Batched Key Access) 1393explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0; 1394id select_type table type possible_keys key key_len ref rows Extra 13951 SIMPLE t4 range PRIMARY PRIMARY 1 NULL 11 Using index condition; Using MRR 13961 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (Block Nested Loop) 1397explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0; 1398id select_type table type possible_keys key key_len ref rows Extra 13991 SIMPLE t4 range PRIMARY PRIMARY 1 NULL 12 Using index condition; Using MRR 14001 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (Block Nested Loop) 1401explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0; 1402id select_type table type possible_keys key key_len ref rows Extra 14031 SIMPLE t4 range PRIMARY PRIMARY 1 NULL 11 Using index condition; Using MRR 14041 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (Block Nested Loop) 1405explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null; 1406id select_type table type possible_keys key key_len ref rows Extra 14071 SIMPLE t4 ALL NULL NULL NULL NULL 12 NULL 14081 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (Block Nested Loop) 1409explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0 or t4.companynr > 0; 1410id select_type table type possible_keys key key_len ref rows Extra 14111 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 NULL 14121 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (Block Nested Loop) 1413explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0; 1414id select_type table type possible_keys key key_len ref rows Extra 14151 SIMPLE t4 ALL NULL NULL NULL NULL 12 NULL 14161 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (Block Nested Loop) 1417explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null; 1418id select_type table type possible_keys key key_len ref rows Extra 14191 SIMPLE t4 range PRIMARY PRIMARY 1 NULL 11 Using index condition; Using MRR 14201 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (Block Nested Loop) 1421explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0; 1422id select_type table type possible_keys key key_len ref rows Extra 14231 SIMPLE t4 range PRIMARY PRIMARY 1 NULL 12 Using index condition; Using MRR 14241 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (Block Nested Loop) 1425explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0; 1426id select_type table type possible_keys key key_len ref rows Extra 14271 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where 14281 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (Block Nested Loop) 1429select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; 1430companynr companynr 143137 36 143241 40 1433explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; 1434id select_type table type possible_keys key key_len ref rows Extra 14351 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using index; Using temporary 14361 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (Block Nested Loop) 1437select 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; 1438fld1 companynr fld3 period 1439038008 37 reporters 1008 1440038208 37 Selfridge 1008 1441select 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; 1442fld1 companynr fld3 period 1443038008 37 reporters 1008 1444038208 37 Selfridge 1008 1445select 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; 1446fld1 companynr fld3 period 1447038008 37 reporters 1008 1448038208 37 Selfridge 1008 1449select 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); 1450period 14519410 1452select 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))); 1453period 14549410 1455select 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; 1456fld1 1457250501 1458250502 1459250503 1460250505 1461select fld1 from t2 where fld1 in (250502,98005,98006,250503,250605,250606) and fld1 >=250502 and fld1 not in (250605,250606); 1462fld1 1463250502 1464250503 1465select fld1 from t2 where fld1 between 250502 and 250504; 1466fld1 1467250502 1468250503 1469250504 1470select fld3 from t2 where (((fld3 like "_%L%" ) or (fld3 like "%ok%")) and ( fld3 like "L%" or fld3 like "G%")) and fld3 like "L%" ; 1471fld3 1472label 1473labeled 1474labeled 1475landslide 1476laterally 1477leaflet 1478lewdly 1479Lillian 1480luckily 1481select count(*) from t1; 1482count(*) 14831 1484select companynr,count(*),sum(fld1) from t2 group by companynr; 1485companynr count(*) sum(fld1) 148600 82 10355753 148729 95 14473298 148834 70 17788966 148936 215 22786296 149037 588 83602098 149140 37 6618386 149241 52 12816335 149350 11 1595438 149453 4 793210 149558 23 2254293 149665 10 2284055 149768 12 3097288 1498select companynr,count(*) from t2 group by companynr order by companynr desc limit 5; 1499companynr count(*) 150068 12 150165 10 150258 23 150353 4 150450 11 1505select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>""; 1506count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1) 150770 absentee vest 17788966 254128.0857 3272.5939722090234 10709871.306938833 1508explain extended select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>""; 1509id select_type table type possible_keys key key_len ref rows filtered Extra 15101 SIMPLE t2 ALL NULL NULL NULL NULL 1199 100.00 Using where 1511Warnings: 1512Note 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` <> '')) 1513select companynr,count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 group by companynr limit 3; 1514companynr count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1) 151500 82 Anthony windmills 10355753 126289.6707 115550.97568479746 13352027981.708656 151629 95 abut wetness 14473298 152350.5053 8368.547956641249 70032594.90260443 151734 70 absentee vest 17788966 254128.0857 3272.5939722090234 10709871.306938833 1518select companynr,t2nr,count(price),sum(price),min(price),max(price),avg(price) from t3 where companynr = 37 group by companynr,t2nr limit 10; 1519companynr t2nr count(price) sum(price) min(price) max(price) avg(price) 152037 1 1 5987435 5987435 5987435 5987435.0000 152137 2 1 28357832 28357832 28357832 28357832.0000 152237 3 1 39654943 39654943 39654943 39654943.0000 152337 11 1 5987435 5987435 5987435 5987435.0000 152437 12 1 28357832 28357832 28357832 28357832.0000 152537 13 1 39654943 39654943 39654943 39654943.0000 152637 21 1 5987435 5987435 5987435 5987435.0000 152737 22 1 28357832 28357832 28357832 28357832.0000 152837 23 1 39654943 39654943 39654943 39654943.0000 152937 31 1 5987435 5987435 5987435 5987435.0000 1530select /*! 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; 1531companynr t2nr count(price) sum(price) min(price) max(price) avg(price) 153237 1 1 5987435 5987435 5987435 5987435.0000 153337 2 1 28357832 28357832 28357832 28357832.0000 153437 3 1 39654943 39654943 39654943 39654943.0000 153537 11 1 5987435 5987435 5987435 5987435.0000 153637 12 1 28357832 28357832 28357832 28357832.0000 153737 13 1 39654943 39654943 39654943 39654943.0000 153837 21 1 5987435 5987435 5987435 5987435.0000 153937 22 1 28357832 28357832 28357832 28357832.0000 154037 23 1 39654943 39654943 39654943 39654943.0000 154137 31 1 5987435 5987435 5987435 5987435.0000 1542select companynr,count(price),sum(price),min(price),max(price),avg(price) from t3 group by companynr ; 1543companynr count(price) sum(price) min(price) max(price) avg(price) 154437 12543 309394878010 5987435 39654943 24666736.6667 154578 8362 414611089292 726498 98439034 49582766.0000 1546101 4181 3489454238 834598 834598 834598.0000 1547154 4181 4112197254950 983543950 983543950 983543950.0000 1548311 4181 979599938 234298 234298 234298.0000 1549447 4181 9929180954 2374834 2374834 2374834.0000 1550512 4181 3288532102 786542 786542 786542.0000 1551select distinct mod(companynr,10) from t4 group by companynr; 1552mod(companynr,10) 15530 15549 15554 15566 15577 15581 15593 15608 15615 1562select distinct 1 from t4 group by companynr; 15631 15641 1565select count(distinct fld1) from t2; 1566count(distinct fld1) 15671199 1568select companynr,count(distinct fld1) from t2 group by companynr; 1569companynr count(distinct fld1) 157000 82 157129 95 157234 70 157336 215 157437 588 157540 37 157641 52 157750 11 157853 4 157958 23 158065 10 158168 12 1582select companynr,count(*) from t2 group by companynr; 1583companynr count(*) 158400 82 158529 95 158634 70 158736 215 158837 588 158940 37 159041 52 159150 11 159253 4 159358 23 159465 10 159568 12 1596select companynr,count(distinct concat(fld1,repeat(65,1000))) from t2 group by companynr; 1597companynr count(distinct concat(fld1,repeat(65,1000))) 159800 82 159929 95 160034 70 160136 215 160237 588 160340 37 160441 52 160550 11 160653 4 160758 23 160865 10 160968 12 1610select companynr,count(distinct concat(fld1,repeat(65,200))) from t2 group by companynr; 1611companynr count(distinct concat(fld1,repeat(65,200))) 161200 82 161329 95 161434 70 161536 215 161637 588 161740 37 161841 52 161950 11 162053 4 162158 23 162265 10 162368 12 1624select companynr,count(distinct floor(fld1/100)) from t2 group by companynr; 1625companynr count(distinct floor(fld1/100)) 162600 47 162729 35 162834 14 162936 69 163037 108 163140 16 163241 11 163350 9 163453 1 163558 1 163665 1 163768 1 1638select companynr,count(distinct concat(repeat(65,1000),floor(fld1/100))) from t2 group by companynr; 1639companynr count(distinct concat(repeat(65,1000),floor(fld1/100))) 164000 47 164129 35 164234 14 164336 69 164437 108 164540 16 164641 11 164750 9 164853 1 164958 1 165065 1 165168 1 1652select sum(fld1),fld3 from t2 where fld3="Romans" group by fld1 limit 10; 1653sum(fld1) fld3 165411402 Romans 1655select name,count(*) from t3 where name='cloakroom' group by name; 1656name count(*) 1657cloakroom 4181 1658select name,count(*) from t3 where name='cloakroom' and price>10 group by name; 1659name count(*) 1660cloakroom 4181 1661select count(*) from t3 where name='cloakroom' and price2=823742; 1662count(*) 16634181 1664select name,count(*) from t3 where name='cloakroom' and price2=823742 group by name; 1665name count(*) 1666cloakroom 4181 1667select name,count(*) from t3 where name >= "extramarital" and price <= 39654943 group by name; 1668name count(*) 1669extramarital 4181 1670gazer 4181 1671gems 4181 1672Iranizes 4181 1673spates 4181 1674tucked 4181 1675violinist 4181 1676select t2.fld3,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name; 1677fld3 count(*) 1678spates 4181 1679select companynr|0,companyname from t4 group by 1; 1680companynr|0 companyname 16810 Unknown 168229 company 1 168334 company 2 168436 company 3 168537 company 4 168640 company 5 168741 company 6 168850 company 11 168953 company 7 169058 company 8 169165 company 9 169268 company 10 1693select t2.companynr,companyname,count(*) from t2,t4 where t2.companynr=t4.companynr group by t2.companynr order by companyname; 1694companynr companyname count(*) 169529 company 1 95 169668 company 10 12 169750 company 11 11 169834 company 2 70 169936 company 3 215 170037 company 4 588 170140 company 5 37 170241 company 6 52 170353 company 7 4 170458 company 8 23 170565 company 9 10 170600 Unknown 82 1707select t2.fld1,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name; 1708fld1 count(*) 1709158402 4181 1710select sum(Period)/count(*) from t1; 1711sum(Period)/count(*) 17129410.0000 1713select 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; 1714companynr count sum diff func 171537 12543 309394878010 0.0000 464091 171678 8362 414611089292 0.0000 652236 1717101 4181 3489454238 0.0000 422281 1718154 4181 4112197254950 0.0000 643874 1719311 4181 979599938 0.0000 1300291 1720447 4181 9929180954 0.0000 1868907 1721512 4181 3288532102 0.0000 2140672 1722select companynr,sum(price)/count(price) as avg from t3 group by companynr having avg > 70000000 order by avg; 1723companynr avg 1724154 983543950.0000 1725select companynr,count(*) from t2 group by companynr order by 2 desc; 1726companynr count(*) 172737 588 172836 215 172929 95 173000 82 173134 70 173241 52 173340 37 173458 23 173568 12 173650 11 173765 10 173853 4 1739select companynr,count(*) from t2 where companynr > 40 group by companynr order by 2 desc; 1740companynr count(*) 174141 52 174258 23 174368 12 174450 11 174565 10 174653 4 1747select 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; 1748fld4 fld1 count(price) sum(price) min(price) max(price) avg(price) 1749teethe 000001 1 5987435 5987435 5987435 5987435.0000 1750dreaded 011401 1 5987435 5987435 5987435 5987435.0000 1751scholastics 011402 1 28357832 28357832 28357832 28357832.0000 1752audiology 011403 1 39654943 39654943 39654943 39654943.0000 1753wallet 011501 1 5987435 5987435 5987435 5987435.0000 1754parters 011701 1 5987435 5987435 5987435 5987435.0000 1755eschew 011702 1 28357832 28357832 28357832 28357832.0000 1756quitter 011703 1 39654943 39654943 39654943 39654943.0000 1757neat 012001 1 5987435 5987435 5987435 5987435.0000 1758Steinberg 012003 1 39654943 39654943 39654943 39654943.0000 1759balled 012301 1 5987435 5987435 5987435 5987435.0000 1760persist 012302 1 28357832 28357832 28357832 28357832.0000 1761attainments 012303 1 39654943 39654943 39654943 39654943.0000 1762capably 012501 1 5987435 5987435 5987435 5987435.0000 1763impulsive 012602 1 28357832 28357832 28357832 28357832.0000 1764starlet 012603 1 39654943 39654943 39654943 39654943.0000 1765featherweight 012701 1 5987435 5987435 5987435 5987435.0000 1766pessimist 012702 1 28357832 28357832 28357832 28357832.0000 1767daughter 012703 1 39654943 39654943 39654943 39654943.0000 1768lawgiver 013601 1 5987435 5987435 5987435 5987435.0000 1769stated 013602 1 28357832 28357832 28357832 28357832.0000 1770readable 013603 1 39654943 39654943 39654943 39654943.0000 1771testicle 013801 1 5987435 5987435 5987435 5987435.0000 1772Parsifal 013802 1 28357832 28357832 28357832 28357832.0000 1773leavings 013803 1 39654943 39654943 39654943 39654943.0000 1774squeaking 013901 1 5987435 5987435 5987435 5987435.0000 1775contrasted 016001 1 5987435 5987435 5987435 5987435.0000 1776leftover 016201 1 5987435 5987435 5987435 5987435.0000 1777whiteners 016202 1 28357832 28357832 28357832 28357832.0000 1778erases 016301 1 5987435 5987435 5987435 5987435.0000 1779Punjab 016302 1 28357832 28357832 28357832 28357832.0000 1780Merritt 016303 1 39654943 39654943 39654943 39654943.0000 1781sweetish 018001 1 5987435 5987435 5987435 5987435.0000 1782dogging 018002 1 28357832 28357832 28357832 28357832.0000 1783scornfully 018003 1 39654943 39654943 39654943 39654943.0000 1784fetters 018012 1 28357832 28357832 28357832 28357832.0000 1785bivalves 018013 1 39654943 39654943 39654943 39654943.0000 1786skulking 018021 1 5987435 5987435 5987435 5987435.0000 1787flint 018022 1 28357832 28357832 28357832 28357832.0000 1788flopping 018023 1 39654943 39654943 39654943 39654943.0000 1789Judas 018032 1 28357832 28357832 28357832 28357832.0000 1790vacuuming 018033 1 39654943 39654943 39654943 39654943.0000 1791medical 018041 1 5987435 5987435 5987435 5987435.0000 1792bloodbath 018042 1 28357832 28357832 28357832 28357832.0000 1793subschema 018043 1 39654943 39654943 39654943 39654943.0000 1794interdependent 018051 1 5987435 5987435 5987435 5987435.0000 1795Graves 018052 1 28357832 28357832 28357832 28357832.0000 1796neonatal 018053 1 39654943 39654943 39654943 39654943.0000 1797sorters 018061 1 5987435 5987435 5987435 5987435.0000 1798epistle 018062 1 28357832 28357832 28357832 28357832.0000 1799Conley 018101 1 5987435 5987435 5987435 5987435.0000 1800lectured 018102 1 28357832 28357832 28357832 28357832.0000 1801Abraham 018103 1 39654943 39654943 39654943 39654943.0000 1802cage 018201 1 5987435 5987435 5987435 5987435.0000 1803hushes 018202 1 28357832 28357832 28357832 28357832.0000 1804Simla 018402 1 28357832 28357832 28357832 28357832.0000 1805reporters 018403 1 39654943 39654943 39654943 39654943.0000 1806coexist 018601 1 5987435 5987435 5987435 5987435.0000 1807Beebe 018602 1 28357832 28357832 28357832 28357832.0000 1808Taoism 018603 1 39654943 39654943 39654943 39654943.0000 1809Connally 018801 1 5987435 5987435 5987435 5987435.0000 1810fetched 018802 1 28357832 28357832 28357832 28357832.0000 1811checkpoints 018803 1 39654943 39654943 39654943 39654943.0000 1812gritty 018811 1 5987435 5987435 5987435 5987435.0000 1813firearm 018812 1 28357832 28357832 28357832 28357832.0000 1814minima 019101 1 5987435 5987435 5987435 5987435.0000 1815Selfridge 019102 1 28357832 28357832 28357832 28357832.0000 1816disable 019103 1 39654943 39654943 39654943 39654943.0000 1817witchcraft 019201 1 5987435 5987435 5987435 5987435.0000 1818betroth 030501 1 5987435 5987435 5987435 5987435.0000 1819Manhattanize 030502 1 28357832 28357832 28357832 28357832.0000 1820imprint 030503 1 39654943 39654943 39654943 39654943.0000 1821swelling 031901 1 5987435 5987435 5987435 5987435.0000 1822interrelationships 036001 1 5987435 5987435 5987435 5987435.0000 1823riser 036002 1 28357832 28357832 28357832 28357832.0000 1824bee 038001 1 5987435 5987435 5987435 5987435.0000 1825kanji 038002 1 28357832 28357832 28357832 28357832.0000 1826dental 038003 1 39654943 39654943 39654943 39654943.0000 1827railway 038011 1 5987435 5987435 5987435 5987435.0000 1828validate 038012 1 28357832 28357832 28357832 28357832.0000 1829normalizes 038013 1 39654943 39654943 39654943 39654943.0000 1830Kline 038101 1 5987435 5987435 5987435 5987435.0000 1831Anatole 038102 1 28357832 28357832 28357832 28357832.0000 1832partridges 038103 1 39654943 39654943 39654943 39654943.0000 1833recruited 038201 1 5987435 5987435 5987435 5987435.0000 1834dimensions 038202 1 28357832 28357832 28357832 28357832.0000 1835Chicana 038203 1 39654943 39654943 39654943 39654943.0000 1836select t3.companynr,fld3,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 512 group by companynr,fld3; 1837companynr fld3 sum(price) 1838512 boat 786542 1839512 capably 786542 1840512 cupboard 786542 1841512 decliner 786542 1842512 descendants 786542 1843512 dopers 786542 1844512 erases 786542 1845512 Micronesia 786542 1846512 Miles 786542 1847512 skies 786542 1848select 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; 1849companynr count(*) min(fld3) max(fld3) sum(price) avg(price) 185000 1 Omaha Omaha 5987435 5987435.0000 185136 1 dubbed dubbed 28357832 28357832.0000 185237 83 Abraham Wotan 1908978016 22999735.1325 185350 2 scribbled tapestry 68012775 34006387.5000 1854select 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; 1855t3.companynr+0 t2nr fld3 sum(price) 185637 1 Omaha 5987435 185737 11401 breaking 5987435 185837 11402 Romans 28357832 185937 11403 intercepted 39654943 186037 11501 bewilderingly 5987435 186137 11701 astound 5987435 186237 11702 admonishing 28357832 186337 11703 sumac 39654943 186437 12001 flanking 5987435 186537 12003 combed 39654943 186637 12301 Eulerian 5987435 186737 12302 dubbed 28357832 186837 12303 Kane 39654943 186937 12501 annihilates 5987435 187037 12602 Wotan 28357832 187137 12603 snatching 39654943 187237 12701 grazing 5987435 187337 12702 Baird 28357832 187437 12703 celery 39654943 187537 13601 handgun 5987435 187637 13602 foldout 28357832 187737 13603 mystic 39654943 187837 13801 intelligibility 5987435 187937 13802 Augustine 28357832 188037 13803 teethe 39654943 188137 13901 scholastics 5987435 188237 16001 audiology 5987435 188337 16201 wallet 5987435 188437 16202 parters 28357832 188537 16301 eschew 5987435 188637 16302 quitter 28357832 188737 16303 neat 39654943 188837 18001 jarring 5987435 188937 18002 tinily 28357832 189037 18003 balled 39654943 189137 18012 impulsive 28357832 189237 18013 starlet 39654943 189337 18021 lawgiver 5987435 189437 18022 stated 28357832 189537 18023 readable 39654943 189637 18032 testicle 28357832 189737 18033 Parsifal 39654943 189837 18041 Punjab 5987435 189937 18042 Merritt 28357832 190037 18043 Quixotism 39654943 190137 18051 sureties 5987435 190237 18052 puddings 28357832 190337 18053 tapestry 39654943 190437 18061 trimmings 5987435 190537 18062 humility 28357832 190637 18101 tragedies 5987435 190737 18102 skulking 28357832 190837 18103 flint 39654943 190937 18201 relaxing 5987435 191037 18202 offload 28357832 191137 18402 suites 28357832 191237 18403 lists 39654943 191337 18601 vacuuming 5987435 191437 18602 dentally 28357832 191537 18603 humanness 39654943 191637 18801 inch 5987435 191737 18802 Weissmuller 28357832 191837 18803 irresponsibly 39654943 191937 18811 repetitions 5987435 192037 18812 Antares 28357832 192137 19101 ventilate 5987435 192237 19102 pityingly 28357832 192337 19103 interdependent 39654943 192437 19201 Graves 5987435 192537 30501 neonatal 5987435 192637 30502 scribbled 28357832 192737 30503 chafe 39654943 192837 31901 realtor 5987435 192937 36001 elite 5987435 193037 36002 funereal 28357832 193137 38001 Conley 5987435 193237 38002 lectured 28357832 193337 38003 Abraham 39654943 193437 38011 groupings 5987435 193537 38012 dissociate 28357832 193637 38013 coexist 39654943 193737 38101 rusting 5987435 193837 38102 galling 28357832 193937 38103 obliterates 39654943 194037 38201 resumes 5987435 194137 38202 analyzable 28357832 194237 38203 terminator 39654943 1943select 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; 1944sum(price) 1945234298 1946select 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; 1947fld1 sum(price) 1948038008 234298 1949explain select fld3 from t2 where 1>2 or 2>3; 1950id select_type table type possible_keys key key_len ref rows Extra 19511 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 1952explain select fld3 from t2 where fld1=fld1; 1953id select_type table type possible_keys key key_len ref rows Extra 19541 SIMPLE t2 ALL NULL NULL NULL NULL 1199 NULL 1955select companynr,fld1 from t2 HAVING fld1=250501 or fld1=250502; 1956companynr fld1 195734 250501 195834 250502 1959select companynr,fld1 from t2 WHERE fld1>=250501 HAVING fld1<=250502; 1960companynr fld1 196134 250501 196234 250502 1963select companynr,count(*) as count,sum(fld1) as sum from t2 group by companynr having count > 40 and sum/count >= 120000; 1964companynr count sum 196500 82 10355753 196629 95 14473298 196734 70 17788966 196837 588 83602098 196941 52 12816335 1970select companynr from t2 group by companynr having count(*) > 40 and sum(fld1)/count(*) >= 120000 ; 1971companynr 197200 197329 197434 197537 197641 1977select t2.companynr,companyname,count(*) from t2,t4 where t2.companynr=t4.companynr group by companyname having t2.companynr >= 40; 1978companynr companyname count(*) 197968 company 10 12 198050 company 11 11 198140 company 5 37 198241 company 6 52 198353 company 7 4 198458 company 8 23 198565 company 9 10 1986select count(*) from t2; 1987count(*) 19881199 1989select count(*) from t2 where fld1 < 098024; 1990count(*) 1991387 1992select min(fld1) from t2 where fld1>= 098024; 1993min(fld1) 199498024 1995select max(fld1) from t2 where fld1>= 098024; 1996max(fld1) 19971232609 1998select count(*) from t3 where price2=76234234; 1999count(*) 20004181 2001select count(*) from t3 where companynr=512 and price2=76234234; 2002count(*) 20034181 2004explain select min(fld1),max(fld1),count(*) from t2; 2005id select_type table type possible_keys key key_len ref rows Extra 20061 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2007select min(fld1),max(fld1),count(*) from t2; 2008min(fld1) max(fld1) count(*) 20090 1232609 1199 2010select min(t2nr),max(t2nr) from t3 where t2nr=2115 and price2=823742; 2011min(t2nr) max(t2nr) 20122115 2115 2013select count(*),min(t2nr),max(t2nr) from t3 where name='spates' and companynr=78; 2014count(*) min(t2nr) max(t2nr) 20154181 4 41804 2016select t2nr,count(*) from t3 where name='gems' group by t2nr limit 20; 2017t2nr count(*) 20189 1 201919 1 202029 1 202139 1 202249 1 202359 1 202469 1 202579 1 202689 1 202799 1 2028109 1 2029119 1 2030129 1 2031139 1 2032149 1 2033159 1 2034169 1 2035179 1 2036189 1 2037199 1 2038select max(t2nr) from t3 where price=983543950; 2039max(t2nr) 204041807 2041select t1.period from t3 = t1 limit 1; 2042period 20431001 2044select t1.period from t1 as t1 limit 1; 2045period 20469410 2047select t1.period as "Nuvarande period" from t1 as t1 limit 1; 2048Nuvarande period 20499410 2050select period as ok_period from t1 limit 1; 2051ok_period 20529410 2053select period as ok_period from t1 group by ok_period limit 1; 2054ok_period 20559410 2056select 1+1 as summa from t1 group by summa limit 1; 2057summa 20582 2059select period as "Nuvarande period" from t1 group by "Nuvarande period" limit 1; 2060Nuvarande period 20619410 2062show tables; 2063Tables_in_test 2064t1 2065t2 2066t3 2067t4 2068show tables from test like "s%"; 2069Tables_in_test (s%) 2070show tables from test like "t?"; 2071Tables_in_test (t?) 2072show full columns from t2; 2073Field Type Collation Null Key Default Extra Privileges Comment 2074auto int(11) NULL NO PRI NULL auto_increment # 2075fld1 int(6) unsigned zerofill NULL NO UNI 000000 # 2076companynr tinyint(2) unsigned zerofill NULL NO 00 # 2077fld3 char(30) latin1_swedish_ci NO MUL # 2078fld4 char(35) latin1_swedish_ci NO # 2079fld5 char(35) latin1_swedish_ci NO # 2080fld6 char(4) latin1_swedish_ci NO # 2081show full columns from t2 from test like 'f%'; 2082Field Type Collation Null Key Default Extra Privileges Comment 2083fld1 int(6) unsigned zerofill NULL NO UNI 000000 # 2084fld3 char(30) latin1_swedish_ci NO MUL # 2085fld4 char(35) latin1_swedish_ci NO # 2086fld5 char(35) latin1_swedish_ci NO # 2087fld6 char(4) latin1_swedish_ci NO # 2088show full columns from t2 from test like 's%'; 2089Field Type Collation Null Key Default Extra Privileges Comment 2090show keys from t2; 2091Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 2092t2 0 PRIMARY 1 auto A 1199 NULL NULL BTREE 2093t2 0 fld1 1 fld1 A 1199 NULL NULL BTREE 2094t2 1 fld3 1 fld3 A NULL NULL NULL BTREE 2095drop table t4, t3, t2, t1; 2096DO 1; 2097DO benchmark(100,1+1),1,1; 2098do default; 2099ERROR 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 2100do foobar; 2101ERROR 42S22: Unknown column 'foobar' in 'field list' 2102CREATE TABLE t1 ( 2103id mediumint(8) unsigned NOT NULL auto_increment, 2104pseudo varchar(35) NOT NULL default '', 2105PRIMARY KEY (id), 2106UNIQUE KEY pseudo (pseudo) 2107); 2108INSERT INTO t1 (pseudo) VALUES ('test'); 2109INSERT INTO t1 (pseudo) VALUES ('test1'); 2110SELECT 1 as rnd1 from t1 where rand() > 2; 2111rnd1 2112DROP TABLE t1; 2113CREATE 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; 2114INSERT 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); 2115CREATE 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; 2116INSERT INTO t2 VALUES (1,3,10,'2002-06-01 08:00:00',35),(1,3,1010,'2002-06-01 12:00:01',35); 2117SELECT 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; 2118gvid the_success the_fail the_size the_time 2119Warnings: 2120Warning 1292 Incorrect datetime value: 'wrong-date-value' for column 'sampletime' at row 1 2121Warning 1292 Incorrect datetime value: 'wrong-date-value' for column 'sampletime' at row 1 2122SELECT 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; 2123gvid the_success the_fail the_size the_time 2124DROP TABLE t1,t2; 2125create 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'); 2126INSERT INTO t1 VALUES (102935998719055004,'brade',1029359987,2,102935229116544068,102935229216544093); 2127select wss_type from t1 where wss_type ='102935229216544106'; 2128wss_type 2129select wss_type from t1 where wss_type ='102935229216544105'; 2130wss_type 2131select wss_type from t1 where wss_type ='102935229216544104'; 2132wss_type 2133select wss_type from t1 where wss_type ='102935229216544093'; 2134wss_type 2135102935229216544093 2136select wss_type from t1 where wss_type =102935229216544093; 2137wss_type 2138102935229216544093 2139drop table t1; 2140select 1+2,"aaaa",3.13*2.0 into @a,@b,@c; 2141select @a; 2142@a 21433 2144select @b; 2145@b 2146aaaa 2147select @c; 2148@c 21496.260 2150create table t1 (a int not null auto_increment primary key); 2151insert into t1 values (); 2152insert into t1 values (); 2153insert into t1 values (); 2154select * from (t1 as t2 left join t1 as t3 using (a)), t1; 2155a a 21561 1 21572 1 21583 1 21591 2 21602 2 21613 2 21621 3 21632 3 21643 3 2165select * from t1, (t1 as t2 left join t1 as t3 using (a)); 2166a a 21671 1 21682 1 21693 1 21701 2 21712 2 21723 2 21731 3 21742 3 21753 3 2176select * from (t1 as t2 left join t1 as t3 using (a)) straight_join t1; 2177a a 21781 1 21792 1 21803 1 21811 2 21822 2 21833 2 21841 3 21852 3 21863 3 2187select * from t1 straight_join (t1 as t2 left join t1 as t3 using (a)); 2188a a 21891 1 21902 1 21913 1 21921 2 21932 2 21943 2 21951 3 21962 3 21973 3 2198select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 on t1.a>1; 2199a a 22001 2 22012 2 22023 2 22031 3 22042 3 22053 3 2206select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1; 2207a a 22082 1 22093 1 22102 2 22113 2 22122 3 22133 3 2214select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 using ( a ); 2215a 22161 22172 22183 2219select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) using ( a ); 2220a 22211 22222 22233 2224select * from (t1 as t2 left join t1 as t3 using (a)) left outer join t1 on t1.a>1; 2225a a 22261 2 22271 3 22282 2 22292 3 22303 2 22313 3 2232select * from t1 left outer join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1; 2233a a 22341 NULL 22352 1 22362 2 22372 3 22383 1 22393 2 22403 3 2241select * from (t1 as t2 left join t1 as t3 using (a)) left join t1 using ( a ); 2242a 22431 22442 22453 2246select * from t1 left join (t1 as t2 left join t1 as t3 using (a)) using ( a ); 2247a 22481 22492 22503 2251select * from (t1 as t2 left join t1 as t3 using (a)) natural left join t1; 2252a 22531 22542 22553 2256select * from t1 natural left join (t1 as t2 left join t1 as t3 using (a)); 2257a 22581 22592 22603 2261select * from (t1 as t2 left join t1 as t3 using (a)) right join t1 on t1.a>1; 2262a a 2263NULL 1 22641 2 22652 2 22663 2 22671 3 22682 3 22693 3 2270select * from t1 right join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1; 2271a a 22722 1 22732 2 22742 3 22753 1 22763 2 22773 3 2278select * from (t1 as t2 left join t1 as t3 using (a)) right outer join t1 using ( a ); 2279a 22801 22812 22823 2283select * from t1 right outer join (t1 as t2 left join t1 as t3 using (a)) using ( a ); 2284a 22851 22862 22873 2288select * from (t1 as t2 left join t1 as t3 using (a)) natural right join t1; 2289a 22901 22912 22923 2293select * from t1 natural right join (t1 as t2 left join t1 as t3 using (a)); 2294a 22951 22962 22973 2298select * from t1 natural join (t1 as t2 left join t1 as t3 using (a)); 2299a 23001 23012 23023 2303select * from (t1 as t2 left join t1 as t3 using (a)) natural join t1; 2304a 23051 23062 23073 2308drop table t1; 2309CREATE 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; 2310INSERT INTO t1 VALUES ("1",8264,2506),("2",8299,2517),("3",8301,2518),("4",8302,2519),("5",8303,2520),("6",8304,2521),("7",8305,2522); 2311CREATE TABLE t2 ( id int(11) NOT NULL auto_increment, PRIMARY KEY (id)) ENGINE=MyISAM; 2312INSERT INTO t2 VALUES (2517), (2518), (2519), (2520), (2521), (2522); 2313select * from t1, t2 WHERE t1.t2_id = t2.id and t1.t2_id > 0 order by t1.id LIMIT 0, 5; 2314aa id t2_id id 23152 8299 2517 2517 23163 8301 2518 2518 23174 8302 2519 2519 23185 8303 2520 2520 23196 8304 2521 2521 2320drop table t1,t2; 2321create table t1 (id1 int NOT NULL); 2322create table t2 (id2 int NOT NULL); 2323create table t3 (id3 int NOT NULL); 2324create table t4 (id4 int NOT NULL, id44 int NOT NULL, KEY (id4)); 2325insert into t1 values (1); 2326insert into t1 values (2); 2327insert into t2 values (1); 2328insert into t4 values (1,1); 2329explain select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3 2330left join t4 on id3 = id4 where id2 = 1 or id4 = 1; 2331id select_type table type possible_keys key key_len ref rows Extra 23321 SIMPLE t3 system NULL NULL NULL NULL 0 const row not found 23331 SIMPLE t4 const id4 NULL NULL NULL 1 NULL 23341 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL 23351 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop) 2336select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3 2337left join t4 on id3 = id4 where id2 = 1 or id4 = 1; 2338id1 id2 id3 id4 id44 23391 1 NULL NULL NULL 2340drop table t1,t2,t3,t4; 2341create table t1(s varchar(10) not null); 2342create table t2(s varchar(10) not null primary key); 2343create table t3(s varchar(10) not null primary key); 2344insert into t1 values ('one\t'), ('two\t'); 2345insert into t2 values ('one\r'), ('two\t'); 2346insert into t3 values ('one '), ('two\t'); 2347select * from t1 where s = 'one'; 2348s 2349select * from t2 where s = 'one'; 2350s 2351select * from t3 where s = 'one'; 2352s 2353one 2354select * from t1,t2 where t1.s = t2.s; 2355s s 2356two two 2357select * from t2,t3 where t2.s = t3.s; 2358s s 2359two two 2360drop table t1, t2, t3; 2361create table t1 (a integer, b integer, index(a), index(b)); 2362create table t2 (c integer, d integer, index(c), index(d)); 2363insert into t1 values (1,2), (2,2), (3,2), (4,2); 2364insert into t2 values (1,3), (2,3), (3,4), (4,4); 2365explain select * from t1 left join t2 on a=c where d in (4); 2366id select_type table type possible_keys key key_len ref rows Extra 23671 SIMPLE t2 ref c,d d 5 const 2 NULL 23681 SIMPLE t1 ALL a NULL NULL NULL 4 Using where; Using join buffer (Block Nested Loop) 2369select * from t1 left join t2 on a=c where d in (4); 2370a b c d 23713 2 3 4 23724 2 4 4 2373explain select * from t1 left join t2 on a=c where d = 4; 2374id select_type table type possible_keys key key_len ref rows Extra 23751 SIMPLE t2 ref c,d d 5 const 2 NULL 23761 SIMPLE t1 ALL a NULL NULL NULL 4 Using where; Using join buffer (Block Nested Loop) 2377select * from t1 left join t2 on a=c where d = 4; 2378a b c d 23793 2 3 4 23804 2 4 4 2381drop table t1, t2; 2382CREATE TABLE t1 ( 2383i int(11) NOT NULL default '0', 2384c char(10) NOT NULL default '', 2385PRIMARY KEY (i), 2386UNIQUE KEY c (c) 2387) ENGINE=MyISAM; 2388INSERT INTO t1 VALUES (1,'a'); 2389INSERT INTO t1 VALUES (2,'b'); 2390INSERT INTO t1 VALUES (3,'c'); 2391EXPLAIN SELECT i FROM t1 WHERE i=1; 2392id select_type table type possible_keys key key_len ref rows Extra 23931 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index 2394DROP TABLE t1; 2395CREATE TABLE t1 ( a BLOB, INDEX (a(20)) ); 2396CREATE TABLE t2 ( a BLOB, INDEX (a(20)) ); 2397INSERT INTO t1 VALUES ('one'),('two'),('three'),('four'),('five'); 2398INSERT INTO t2 VALUES ('one'),('two'),('three'),('four'),('five'); 2399EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USE INDEX (a) ON t1.a=t2.a; 2400id select_type table type possible_keys key key_len ref rows Extra 24011 SIMPLE t1 ALL NULL NULL NULL NULL 5 NULL 24021 SIMPLE t2 ALL a NULL NULL NULL 5 Using where; Using join buffer (Block Nested Loop) 2403EXPLAIN SELECT * FROM t1 LEFT JOIN t2 FORCE INDEX (a) ON t1.a=t2.a; 2404id select_type table type possible_keys key key_len ref rows Extra 24051 SIMPLE t1 ALL NULL NULL NULL NULL 5 NULL 24061 SIMPLE t2 ref a a 23 test.t1.a 2 Using where 2407DROP TABLE t1, t2; 2408CREATE TABLE t1 ( city char(30) ); 2409INSERT INTO t1 VALUES ('London'); 2410INSERT INTO t1 VALUES ('Paris'); 2411SELECT * FROM t1 WHERE city='London'; 2412city 2413London 2414SELECT * FROM t1 WHERE city='london'; 2415city 2416London 2417EXPLAIN SELECT * FROM t1 WHERE city='London' AND city='london'; 2418id select_type table type possible_keys key key_len ref rows Extra 24191 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where 2420SELECT * FROM t1 WHERE city='London' AND city='london'; 2421city 2422London 2423EXPLAIN SELECT * FROM t1 WHERE city LIKE '%london%' AND city='London'; 2424id select_type table type possible_keys key key_len ref rows Extra 24251 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where 2426SELECT * FROM t1 WHERE city LIKE '%london%' AND city='London'; 2427city 2428London 2429DROP TABLE t1; 2430create table t1 (a int(11) unsigned, b int(11) unsigned); 2431insert into t1 values (1,0), (1,1), (18446744073709551615,0); 2432Warnings: 2433Warning 1264 Out of range value for column 'a' at row 3 2434select a-b from t1 order by 1; 2435a-b 24360 24371 24384294967295 2439select a-b , (a-b < 0) from t1 order by 1; 2440a-b (a-b < 0) 24410 0 24421 0 24434294967295 0 2444select a-b as d, (a-b >= 0), b from t1 group by b having d >= 0; 2445d (a-b >= 0) b 24461 1 0 24470 1 1 2448select cast((a - b) as unsigned) from t1 order by 1; 2449cast((a - b) as unsigned) 24500 24511 24524294967295 2453drop table t1; 2454create table t1 (a int(11)); 2455select all all * from t1; 2456a 2457select distinct distinct * from t1; 2458a 2459select all distinct * from t1; 2460ERROR HY000: Incorrect usage of ALL and DISTINCT 2461select distinct all * from t1; 2462ERROR HY000: Incorrect usage of ALL and DISTINCT 2463drop table t1; 2464CREATE TABLE t1 ( 2465kunde_intern_id int(10) unsigned NOT NULL default '0', 2466kunde_id int(10) unsigned NOT NULL default '0', 2467FK_firma_id int(10) unsigned NOT NULL default '0', 2468aktuell enum('Ja','Nein') NOT NULL default 'Ja', 2469vorname varchar(128) NOT NULL default '', 2470nachname varchar(128) NOT NULL default '', 2471geloescht enum('Ja','Nein') NOT NULL default 'Nein', 2472firma varchar(128) NOT NULL default '' 2473); 2474INSERT INTO t1 VALUES 2475(3964,3051,1,'Ja','Vorname1','1Nachname','Nein','Print Schau XXXX'), 2476(3965,3051111,1,'Ja','Vorname1111','1111Nachname','Nein','Print Schau XXXX'); 2477SELECT kunde_id ,FK_firma_id ,aktuell, vorname, nachname, geloescht FROM t1 2478WHERE 2479( 2480( 2481( '' != '' AND firma LIKE CONCAT('%', '', '%')) 2482OR 2483(vorname LIKE CONCAT('%', 'Vorname1', '%') AND 2484nachname LIKE CONCAT('%', '1Nachname', '%') AND 2485'Vorname1' != '' AND 'xxxx' != '') 2486) 2487AND 2488( 2489aktuell = 'Ja' AND geloescht = 'Nein' AND FK_firma_id = 2 2490) 2491) 2492; 2493kunde_id FK_firma_id aktuell vorname nachname geloescht 2494SELECT kunde_id ,FK_firma_id ,aktuell, vorname, nachname, 2495geloescht FROM t1 2496WHERE 2497( 2498( 2499aktuell = 'Ja' AND geloescht = 'Nein' AND FK_firma_id = 2 2500) 2501AND 2502( 2503( '' != '' AND firma LIKE CONCAT('%', '', '%') ) 2504OR 2505( vorname LIKE CONCAT('%', 'Vorname1', '%') AND 2506nachname LIKE CONCAT('%', '1Nachname', '%') AND 'Vorname1' != '' AND 2507'xxxx' != '') 2508) 2509) 2510; 2511kunde_id FK_firma_id aktuell vorname nachname geloescht 2512SELECT COUNT(*) FROM t1 WHERE 2513( 0 OR (vorname LIKE '%Vorname1%' AND nachname LIKE '%1Nachname%' AND 1)) 2514AND FK_firma_id = 2; 2515COUNT(*) 25160 2517drop table t1; 2518CREATE TABLE t1 (b BIGINT(20) UNSIGNED NOT NULL, PRIMARY KEY (b)); 2519INSERT INTO t1 VALUES (0x8000000000000000); 2520SELECT b FROM t1 WHERE b=0x8000000000000000; 2521b 25229223372036854775808 2523DROP TABLE t1; 2524CREATE TABLE `t1` ( `gid` int(11) default NULL, `uid` int(11) default NULL); 2525CREATE TABLE `t2` ( `ident` int(11) default NULL, `level` char(16) default NULL); 2526INSERT INTO `t2` VALUES (0,'READ'); 2527CREATE TABLE `t3` ( `id` int(11) default NULL, `name` char(16) default NULL); 2528INSERT INTO `t3` VALUES (1,'fs'); 2529select * from t3 left join t1 on t3.id = t1.uid, t2 where t2.ident in (0, t1.gid, t3.id, 0); 2530id name gid uid ident level 25311 fs NULL NULL 0 READ 2532drop table t1,t2,t3; 2533CREATE TABLE t1 ( 2534acct_id int(11) NOT NULL default '0', 2535profile_id smallint(6) default NULL, 2536UNIQUE KEY t1$acct_id (acct_id), 2537KEY t1$profile_id (profile_id) 2538); 2539INSERT INTO t1 VALUES (132,17),(133,18); 2540CREATE TABLE t2 ( 2541profile_id smallint(6) default NULL, 2542queue_id int(11) default NULL, 2543seq int(11) default NULL, 2544KEY t2$queue_id (queue_id) 2545); 2546INSERT INTO t2 VALUES (17,31,4),(17,30,3),(17,36,2),(17,37,1); 2547CREATE TABLE t3 ( 2548id int(11) NOT NULL default '0', 2549qtype int(11) default NULL, 2550seq int(11) default NULL, 2551warn_lvl int(11) default NULL, 2552crit_lvl int(11) default NULL, 2553rr1 tinyint(4) NOT NULL default '0', 2554rr2 int(11) default NULL, 2555default_queue tinyint(4) NOT NULL default '0', 2556KEY t3$qtype (qtype), 2557KEY t3$id (id) 2558); 2559INSERT INTO t3 VALUES (30,1,29,NULL,NULL,0,NULL,0),(31,1,28,NULL,NULL,0,NULL,0), 2560(36,1,34,NULL,NULL,0,NULL,0),(37,1,35,NULL,NULL,0,121,0); 2561SELECT COUNT(*) FROM t1 a STRAIGHT_JOIN t2 pq STRAIGHT_JOIN t3 q 2562WHERE 2563(pq.profile_id = a.profile_id) AND (a.acct_id = 132) AND 2564(pq.queue_id = q.id) AND (q.rr1 <> 1); 2565COUNT(*) 25664 2567drop table t1,t2,t3; 2568create table t1 (f1 int); 2569insert into t1 values (1),(NULL); 2570create table t2 (f2 int, f3 int, f4 int); 2571create index idx1 on t2 (f4); 2572insert into t2 values (1,2,3),(2,4,6); 2573select A.f2 from t1 left join t2 A on A.f2 = f1 where A.f3=(select min(f3) 2574from t2 C where A.f4 = C.f4) or A.f3 IS NULL; 2575f2 25761 2577NULL 2578drop table t1,t2; 2579create table t2 (a tinyint unsigned); 2580create index t2i on t2(a); 2581insert into t2 values (0), (254), (255); 2582explain select * from t2 where a > -1; 2583id select_type table type possible_keys key key_len ref rows Extra 25841 SIMPLE t2 index t2i t2i 2 NULL 3 Using where; Using index 2585select * from t2 where a > -1; 2586a 25870 2588254 2589255 2590drop table t2; 2591CREATE TABLE t1 (a int, b int, c int); 2592INSERT INTO t1 2593SELECT 50, 3, 3 FROM DUAL 2594WHERE NOT EXISTS 2595(SELECT * FROM t1 WHERE a = 50 AND b = 3); 2596SELECT * FROM t1; 2597a b c 259850 3 3 2599INSERT INTO t1 2600SELECT 50, 3, 3 FROM DUAL 2601WHERE NOT EXISTS 2602(SELECT * FROM t1 WHERE a = 50 AND b = 3); 2603select found_rows(); 2604found_rows() 26050 2606SELECT * FROM t1; 2607a b c 260850 3 3 2609select count(*) from t1; 2610count(*) 26111 2612select found_rows(); 2613found_rows() 26141 2615select count(*) from t1 limit 2,3; 2616count(*) 2617select found_rows(); 2618found_rows() 26190 2620select SQL_CALC_FOUND_ROWS count(*) from t1 limit 2,3; 2621count(*) 2622select found_rows(); 2623found_rows() 26241 2625DROP TABLE t1; 2626CREATE TABLE t1 (a INT, b INT); 2627(SELECT a, b AS c FROM t1) ORDER BY c+1; 2628a c 2629(SELECT a, b AS c FROM t1) ORDER BY b+1; 2630a c 2631SELECT a, b AS c FROM t1 ORDER BY c+1; 2632a c 2633SELECT a, b AS c FROM t1 ORDER BY b+1; 2634a c 2635drop table t1; 2636create table t1(f1 int, f2 int); 2637create table t2(f3 int); 2638select f1 from t1,t2 where f1=f2 and (f1,f2) = ((1,1)); 2639f1 2640select f1 from t1,t2 where f1=f2 and (f1,NULL) = ((1,1)); 2641f1 2642select f1 from t1,t2 where f1=f2 and (f1,f2) = ((1,NULL)); 2643f1 2644insert into t1 values(1,1),(2,null); 2645insert into t2 values(2); 2646select * from t1,t2 where f1=f3 and (f1,f2) = (2,null); 2647f1 f2 f3 2648select * from t1,t2 where f1=f3 and (f1,f2) <=> (2,null); 2649f1 f2 f3 26502 NULL 2 2651drop table t1,t2; 2652create table t1 (f1 int not null auto_increment primary key, f2 varchar(10)); 2653create table t11 like t1; 2654insert into t1 values(1,""),(2,""); 2655show table status like 't1%'; 2656Name 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 2657t1 MyISAM 10 Dynamic 2 20 X X X X X X X X latin1_swedish_ci NULL 2658t11 MyISAM 10 Dynamic 0 0 X X X X X X X X latin1_swedish_ci NULL 2659select 123 as a from t1 where f1 is null; 2660a 2661drop table t1,t11; 2662CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL, UNIQUE idx (a,b) ); 2663INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4); 2664CREATE TABLE t2 ( a INT NOT NULL, b INT NOT NULL, e INT ); 2665INSERT INTO t2 VALUES ( 1,10,1), (1,10,2), (1,11,1), (1,11,2), (1,2,1), (1,2,2),(1,2,3); 2666SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2 LEFT JOIN 2667t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY a, b, c; 2668a b c d 26691 2 1 1 26701 2 2 1 26711 2 3 1 26721 10 2 26731 11 2 2674SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2 LEFT JOIN 2675t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY t1.a, t1.b, c; 2676a b c d 26771 10 4 26781 2 1 1 26791 2 2 1 26801 2 3 1 2681SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2 LEFT JOIN 2682t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY t2.a, t2.b, c; 2683a b c d 26841 2 1 1 26851 2 2 1 26861 2 3 1 26871 10 2 26881 11 2 2689SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2,t1 2690WHERE t2.a = t1.a AND t2.b = t1.b GROUP BY a, b, c; 2691a b c d 26921 2 1 1 26931 2 2 1 26941 2 3 1 2695DROP TABLE IF EXISTS t1, t2; 2696create table t1 (f1 int primary key, f2 int); 2697create table t2 (f3 int, f4 int, primary key(f3,f4)); 2698insert into t1 values (1,1); 2699insert into t2 values (1,1),(1,2); 2700select distinct count(f2) >0 from t1 left join t2 on f1=f3 group by f1; 2701count(f2) >0 27021 2703drop table t1,t2; 2704create table t1 (f1 int,f2 int); 2705insert into t1 values(1,1); 2706create table t2 (f3 int, f4 int, primary key(f3,f4)); 2707insert into t2 values(1,1); 2708select * from t1 where f1 in (select f3 from t2 where (f3,f4)= (select f3,f4 from t2)); 2709f1 f2 27101 1 2711drop table t1,t2; 2712CREATE TABLE t1(a int, b int, c int, KEY b(b), KEY c(c)); 2713insert into t1 values (1,0,0),(2,0,0); 2714CREATE TABLE t2 (a int, b varchar(2), c varchar(2), PRIMARY KEY(a)); 2715insert into t2 values (1,'',''), (2,'',''); 2716CREATE TABLE t3 (a int, b int, PRIMARY KEY (a,b), KEY a (a), KEY b (b)); 2717insert into t3 values (1,1),(1,2); 2718explain select straight_join DISTINCT t2.a,t2.b, t1.c from t1, t3, t2 2719where (t1.c=t2.a or (t1.c=t3.a and t2.a=t3.b)) and t1.b=556476786 and 2720t2.b like '%%' order by t2.b limit 0,1; 2721id select_type table type possible_keys key key_len ref rows Extra 27221 SIMPLE t1 ref b,c b 5 const 1 Using temporary; Using filesort 27231 SIMPLE t3 index PRIMARY,a,b PRIMARY 8 NULL 2 Using index; Using join buffer (Block Nested Loop) 27241 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1) 2725DROP TABLE t1,t2,t3; 2726CREATE TABLE t1 (a int, INDEX idx(a)); 2727INSERT INTO t1 VALUES (2), (3), (1); 2728EXPLAIN SELECT * FROM t1 IGNORE INDEX (idx); 2729id select_type table type possible_keys key key_len ref rows Extra 27301 SIMPLE t1 ALL NULL NULL NULL NULL 3 NULL 2731EXPLAIN SELECT * FROM t1 IGNORE INDEX (a); 2732ERROR 42000: Key 'a' doesn't exist in table 't1' 2733EXPLAIN SELECT * FROM t1 FORCE INDEX (a); 2734ERROR 42000: Key 'a' doesn't exist in table 't1' 2735DROP TABLE t1; 2736CREATE TABLE t1 (a int, b int); 2737INSERT INTO t1 VALUES (1,1), (2,1), (4,10); 2738CREATE TABLE t2 (a int PRIMARY KEY, b int, KEY b (b)); 2739INSERT INTO t2 VALUES (1,NULL), (2,10); 2740ALTER TABLE t1 ENABLE KEYS; 2741EXPLAIN SELECT STRAIGHT_JOIN SQL_NO_CACHE COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; 2742id select_type table type possible_keys key key_len ref rows Extra 27431 SIMPLE t2 index b b 5 NULL 2 Using index 27441 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (Block Nested Loop) 2745SELECT STRAIGHT_JOIN SQL_NO_CACHE * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; 2746a b a b 27471 NULL 1 1 27481 NULL 2 1 27491 NULL 4 10 27502 10 4 10 2751EXPLAIN SELECT STRAIGHT_JOIN SQL_NO_CACHE COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; 2752id select_type table type possible_keys key key_len ref rows Extra 27531 SIMPLE t2 index b b 5 NULL 2 Using index 27541 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (Block Nested Loop) 2755SELECT STRAIGHT_JOIN SQL_NO_CACHE * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; 2756a b a b 27571 NULL 1 1 27581 NULL 2 1 27591 NULL 4 10 27602 10 4 10 2761DROP TABLE IF EXISTS t1,t2; 2762CREATE TABLE t1 (key1 float default NULL, UNIQUE KEY key1 (key1)); 2763CREATE TABLE t2 (key2 float default NULL, UNIQUE KEY key2 (key2)); 2764INSERT INTO t1 VALUES (0.3762),(0.3845),(0.6158),(0.7941); 2765INSERT INTO t2 VALUES (1.3762),(1.3845),(1.6158),(1.7941); 2766explain select max(key1) from t1 where key1 <= 0.6158; 2767id select_type table type possible_keys key key_len ref rows Extra 27681 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2769explain select max(key2) from t2 where key2 <= 1.6158; 2770id select_type table type possible_keys key key_len ref rows Extra 27711 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2772explain select min(key1) from t1 where key1 >= 0.3762; 2773id select_type table type possible_keys key key_len ref rows Extra 27741 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2775explain select min(key2) from t2 where key2 >= 1.3762; 2776id select_type table type possible_keys key key_len ref rows Extra 27771 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2778explain select max(key1), min(key2) from t1, t2 2779where key1 <= 0.6158 and key2 >= 1.3762; 2780id select_type table type possible_keys key key_len ref rows Extra 27811 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2782explain select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5; 2783id select_type table type possible_keys key key_len ref rows Extra 27841 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2785explain select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5; 2786id select_type table type possible_keys key key_len ref rows Extra 27871 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2788select max(key1) from t1 where key1 <= 0.6158; 2789max(key1) 27900.6158000230789185 2791select max(key2) from t2 where key2 <= 1.6158; 2792max(key2) 27931.6158000230789185 2794select min(key1) from t1 where key1 >= 0.3762; 2795min(key1) 27960.37619999051094055 2797select min(key2) from t2 where key2 >= 1.3762; 2798min(key2) 27991.3761999607086182 2800select max(key1), min(key2) from t1, t2 2801where key1 <= 0.6158 and key2 >= 1.3762; 2802max(key1) min(key2) 28030.6158000230789185 1.3761999607086182 2804select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5; 2805max(key1) 28060.6158000230789185 2807select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5; 2808min(key1) 28090.37619999051094055 2810DROP TABLE t1,t2; 2811CREATE TABLE t1 (i BIGINT UNSIGNED NOT NULL); 2812INSERT INTO t1 VALUES (10); 2813SELECT i='1e+01',i=1e+01, i in (1e+01,1e+01), i in ('1e+01','1e+01') FROM t1; 2814i='1e+01' i=1e+01 i in (1e+01,1e+01) i in ('1e+01','1e+01') 28151 1 1 1 2816DROP TABLE t1; 2817create table t1(a bigint unsigned, b bigint); 2818insert into t1 values (0xfffffffffffffffff, 0xfffffffffffffffff), 2819(0x10000000000000000, 0x10000000000000000), 2820(0x8fffffffffffffff, 0x8fffffffffffffff); 2821Warnings: 2822Warning 1264 Out of range value for column 'a' at row 1 2823Warning 1264 Out of range value for column 'b' at row 1 2824Warning 1264 Out of range value for column 'a' at row 2 2825Warning 1264 Out of range value for column 'b' at row 2 2826Warning 1264 Out of range value for column 'b' at row 3 2827select hex(a), hex(b) from t1; 2828hex(a) hex(b) 2829FFFFFFFFFFFFFFFF 7FFFFFFFFFFFFFFF 2830FFFFFFFFFFFFFFFF 7FFFFFFFFFFFFFFF 28318FFFFFFFFFFFFFFF 7FFFFFFFFFFFFFFF 2832drop table t1; 2833CREATE TABLE t1 (c0 int); 2834CREATE TABLE t2 (c0 int); 2835INSERT INTO t1 VALUES(@@connect_timeout); 2836INSERT INTO t2 VALUES(@@connect_timeout); 2837SELECT * FROM t1 JOIN t2 ON t1.c0 = t2.c0 WHERE (t1.c0 <=> @@connect_timeout); 2838c0 c0 2839X X 2840DROP TABLE t1, t2; 2841End of 4.1 tests 2842CREATE TABLE t1 ( 2843K2C4 varchar(4) character set latin1 collate latin1_bin NOT NULL default '', 2844K4N4 varchar(4) character set latin1 collate latin1_bin NOT NULL default '0000', 2845F2I4 int(11) NOT NULL default '0' 2846) ENGINE=MyISAM DEFAULT CHARSET=latin1; 2847INSERT INTO t1 VALUES 2848('W%RT', '0100', 1), 2849('W-RT', '0100', 1), 2850('WART', '0100', 1), 2851('WART', '0200', 1), 2852('WERT', '0100', 2), 2853('WORT','0200', 2), 2854('WT', '0100', 2), 2855('W_RT', '0100', 2), 2856('WaRT', '0100', 3), 2857('WART', '0300', 3), 2858('WRT' , '0400', 3), 2859('WURM', '0500', 3), 2860('W%T', '0600', 4), 2861('WA%T', '0700', 4), 2862('WA_T', '0800', 4); 2863SELECT K2C4, K4N4, F2I4 FROM t1 2864WHERE K2C4 = 'WART' AND 2865(F2I4 = 2 AND K2C4 = 'WART' OR (F2I4 = 2 OR K4N4 = '0200')); 2866K2C4 K4N4 F2I4 2867WART 0200 1 2868SELECT K2C4, K4N4, F2I4 FROM t1 2869WHERE K2C4 = 'WART' AND (K2C4 = 'WART' OR K4N4 = '0200'); 2870K2C4 K4N4 F2I4 2871WART 0100 1 2872WART 0200 1 2873WART 0300 3 2874DROP TABLE t1; 2875create table t1 (a int, b int); 2876create table t2 like t1; 2877select t1.a from (t1 inner join t2 on t1.a=t2.a) where t2.a=1; 2878a 2879select t1.a from ((t1 inner join t2 on t1.a=t2.a)) where t2.a=1; 2880a 2881select 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; 2882a a a 2883drop table t1,t2; 2884create table t1 (s1 varchar(5)); 2885insert into t1 values ('Wall'); 2886select min(s1) from t1 group by s1 with rollup; 2887min(s1) 2888Wall 2889Wall 2890drop table t1; 2891create table t1 (s1 int) engine=myisam; 2892insert into t1 values (0); 2893select avg(distinct s1) from t1 group by s1 with rollup; 2894avg(distinct s1) 28950.0000 28960.0000 2897drop table t1; 2898create table t1 (s1 int); 2899insert into t1 values (null),(1); 2900select avg(s1) as x from t1 group by s1 with rollup; 2901x 2902NULL 29031.0000 29041.0000 2905select distinct avg(s1) as x from t1 group by s1 with rollup; 2906ERROR HY000: Incorrect usage of WITH ROLLUP and DISTINCT 2907drop table t1; 2908CREATE TABLE t1 (a int); 2909CREATE TABLE t2 (a int); 2910INSERT INTO t1 VALUES (1), (2), (3), (4), (5); 2911INSERT INTO t2 VALUES (2), (4), (6); 2912SELECT t1.a FROM t1 STRAIGHT_JOIN t2 ON t1.a=t2.a; 2913a 29142 29154 2916EXPLAIN SELECT t1.a FROM t1 STRAIGHT_JOIN t2 ON t1.a=t2.a; 2917id select_type table type possible_keys key key_len ref rows Extra 29181 SIMPLE t1 ALL NULL NULL NULL NULL 5 NULL 29191 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (Block Nested Loop) 2920EXPLAIN SELECT t1.a FROM t1 INNER JOIN t2 ON t1.a=t2.a; 2921id select_type table type possible_keys key key_len ref rows Extra 29221 SIMPLE t2 ALL NULL NULL NULL NULL 3 NULL 29231 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (Block Nested Loop) 2924DROP TABLE t1,t2; 2925select x'10' + 0, X'10' + 0, b'10' + 0, B'10' + 0; 2926x'10' + 0 X'10' + 0 b'10' + 0 B'10' + 0 292716 16 2 2 2928create table t1 (f1 varchar(6) default NULL, f2 int(6) primary key not null); 2929create table t2 (f3 varchar(5) not null, f4 varchar(5) not null, UNIQUE KEY UKEY (f3,f4)); 2930insert into t1 values (" 2", 2); 2931insert into t2 values (" 2", " one "),(" 2", " two "); 2932select * from t1 left join t2 on f1 = f3; 2933f1 f2 f3 f4 2934 2 2 2 one 2935 2 2 2 two 2936drop table t1,t2; 2937create table t1 (empnum smallint, grp int); 2938create table t2 (empnum int, name char(5)); 2939insert into t1 values(1,1); 2940insert into t2 values(1,'bob'); 2941create view v1 as select * from t2 inner join t1 using (empnum); 2942select * from v1; 2943empnum name grp 29441 bob 1 2945drop table t1,t2; 2946drop view v1; 2947create table t1 (pk int primary key, b int); 2948create table t2 (pk int primary key, c int); 2949select pk from t1 inner join t2 using (pk); 2950pk 2951drop table t1,t2; 2952create table t1 (s1 int, s2 char(5), s3 decimal(10)); 2953create view v1 as select s1, s2, 'x' as s3 from t1; 2954select * from t1 natural join v1; 2955s1 s2 s3 2956insert into t1 values (1,'x',5); 2957select * from t1 natural join v1; 2958s1 s2 s3 2959Warnings: 2960Warning 1292 Truncated incorrect DOUBLE value: 'x' 2961drop table t1; 2962drop view v1; 2963create table t1(a1 int); 2964create table t2(a2 int); 2965insert into t1 values(1),(2); 2966insert into t2 values(1),(2); 2967create view v2 (c) as select a1 from t1; 2968select * from t1 natural left join t2; 2969a1 a2 29701 1 29711 2 29722 1 29732 2 2974select * from t1 natural right join t2; 2975a2 a1 29761 1 29771 2 29782 1 29792 2 2980select * from v2 natural left join t2; 2981c a2 29821 1 29831 2 29842 1 29852 2 2986select * from v2 natural right join t2; 2987a2 c 29881 1 29891 2 29902 1 29912 2 2992drop table t1, t2; 2993drop view v2; 2994create table t1 (a int(10), t1_val int(10)); 2995create table t2 (b int(10), t2_val int(10)); 2996create table t3 (a int(10), b int(10)); 2997insert into t1 values (1,1),(2,2); 2998insert into t2 values (1,1),(2,2),(3,3); 2999insert into t3 values (1,1),(2,1),(3,1),(4,1); 3000select * from t1 natural join t2 natural join t3; 3001a b t1_val t2_val 30021 1 1 1 30032 1 2 1 3004select * from t1 natural join t3 natural join t2; 3005b a t1_val t2_val 30061 1 1 1 30071 2 2 1 3008drop table t1, t2, t3; 3009DO IFNULL(NULL, NULL); 3010SELECT CAST(IFNULL(NULL, NULL) AS DECIMAL); 3011CAST(IFNULL(NULL, NULL) AS DECIMAL) 3012NULL 3013SELECT ABS(IFNULL(NULL, NULL)); 3014ABS(IFNULL(NULL, NULL)) 3015NULL 3016SELECT IFNULL(NULL, NULL); 3017IFNULL(NULL, NULL) 3018NULL 3019SET @OLD_SQL_MODE12595=@@SQL_MODE, @@SQL_MODE=''; 3020SHOW LOCAL VARIABLES LIKE 'SQL_MODE'; 3021Variable_name Value 3022sql_mode 3023CREATE TABLE BUG_12595(a varchar(100)); 3024INSERT INTO BUG_12595 VALUES ('hakan%'), ('hakank'), ("ha%an"); 3025SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\%'; 3026a 3027hakan% 3028SELECT * FROM BUG_12595 WHERE a LIKE 'hakan*%' ESCAPE '*'; 3029a 3030hakan% 3031SELECT * FROM BUG_12595 WHERE a LIKE 'hakan**%' ESCAPE '**'; 3032ERROR HY000: Incorrect arguments to ESCAPE 3033SELECT * FROM BUG_12595 WHERE a LIKE 'hakan%' ESCAPE ''; 3034a 3035hakan% 3036hakank 3037SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\%' ESCAPE ''; 3038a 3039SELECT * FROM BUG_12595 WHERE a LIKE 'ha\%an' ESCAPE 0x5c; 3040a 3041ha%an 3042SELECT * FROM BUG_12595 WHERE a LIKE 'ha%%an' ESCAPE '%'; 3043a 3044ha%an 3045SELECT * FROM BUG_12595 WHERE a LIKE 'ha\%an' ESCAPE '\\'; 3046a 3047ha%an 3048SELECT * FROM BUG_12595 WHERE a LIKE 'ha|%an' ESCAPE '|'; 3049a 3050ha%an 3051SET @@SQL_MODE='NO_BACKSLASH_ESCAPES'; 3052SHOW LOCAL VARIABLES LIKE 'SQL_MODE'; 3053Variable_name Value 3054sql_mode NO_BACKSLASH_ESCAPES 3055SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\%'; 3056a 3057SELECT * FROM BUG_12595 WHERE a LIKE 'hakan*%' ESCAPE '*'; 3058a 3059hakan% 3060SELECT * FROM BUG_12595 WHERE a LIKE 'hakan**%' ESCAPE '**'; 3061ERROR HY000: Incorrect arguments to ESCAPE 3062SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\%' ESCAPE '\\'; 3063ERROR HY000: Incorrect arguments to ESCAPE 3064SELECT * FROM BUG_12595 WHERE a LIKE 'hakan%' ESCAPE ''; 3065ERROR HY000: Incorrect arguments to ESCAPE 3066SELECT * FROM BUG_12595 WHERE a LIKE 'ha\%an' ESCAPE 0x5c; 3067a 3068ha%an 3069SELECT * FROM BUG_12595 WHERE a LIKE 'ha|%an' ESCAPE '|'; 3070a 3071ha%an 3072SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\n%' ESCAPE '\n'; 3073ERROR HY000: Incorrect arguments to ESCAPE 3074SET @@SQL_MODE=@OLD_SQL_MODE12595; 3075DROP TABLE BUG_12595; 3076create table t1 (a char(1)); 3077create table t2 (a char(1)); 3078insert into t1 values ('a'),('b'),('c'); 3079insert into t2 values ('b'),('c'),('d'); 3080select a from t1 natural join t2; 3081a 3082b 3083c 3084select * from t1 natural join t2 where a = 'b'; 3085a 3086b 3087drop table t1, t2; 3088CREATE TABLE t1 (`id` TINYINT); 3089CREATE TABLE t2 (`id` TINYINT); 3090CREATE TABLE t3 (`id` TINYINT); 3091INSERT INTO t1 VALUES (1),(2),(3); 3092INSERT INTO t2 VALUES (2); 3093INSERT INTO t3 VALUES (3); 3094SELECT t1.id,t3.id FROM t1 JOIN t2 ON (t2.id=t1.id) LEFT JOIN t3 USING (id); 3095ERROR 23000: Column 'id' in from clause is ambiguous 3096SELECT t1.id,t3.id FROM t1 JOIN t2 ON (t2.notacolumn=t1.id) LEFT JOIN t3 USING (id); 3097ERROR 23000: Column 'id' in from clause is ambiguous 3098SELECT id,t3.id FROM t1 JOIN t2 ON (t2.id=t1.id) LEFT JOIN t3 USING (id); 3099ERROR 23000: Column 'id' in from clause is ambiguous 3100SELECT id,t3.id FROM (t1 JOIN t2 ON (t2.id=t1.id)) LEFT JOIN t3 USING (id); 3101ERROR 23000: Column 'id' in from clause is ambiguous 3102drop table t1, t2, t3; 3103create table t1 (a int(10),b int(10)); 3104create table t2 (a int(10),b int(10)); 3105insert into t1 values (1,10),(2,20),(3,30); 3106insert into t2 values (1,10); 3107select * from t1 inner join t2 using (A); 3108a b b 31091 10 10 3110select * from t1 inner join t2 using (a); 3111a b b 31121 10 10 3113drop table t1, t2; 3114create table t1 (a int, c int); 3115create table t2 (b int); 3116create table t3 (b int, a int); 3117create table t4 (c int); 3118insert into t1 values (1,1); 3119insert into t2 values (1); 3120insert into t3 values (1,1); 3121insert into t4 values (1); 3122select * from t1 join t2 join t3 on (t2.b = t3.b and t1.a = t3.a); 3123a c b b a 31241 1 1 1 1 3125select * from t1, t2 join t3 on (t2.b = t3.b and t1.a = t3.a); 3126ERROR 42S22: Unknown column 't1.a' in 'on clause' 3127select * from t1 join t2 join t3 join t4 on (t1.a = t4.c and t2.b = t4.c); 3128a c b b a c 31291 1 1 1 1 1 3130select * from t1 join t2 join t4 using (c); 3131c a b 31321 1 1 3133drop table t1, t2, t3, t4; 3134create table t1(x int, y int); 3135create table t2(x int, y int); 3136create table t3(x int, primary key(x)); 3137insert into t1 values (1, 1), (2, 1), (3, 1), (4, 3), (5, 6), (6, 6); 3138insert into t2 values (1, 1), (2, 1), (3, 3), (4, 6), (5, 6); 3139insert into t3 values (1), (2), (3), (4), (5); 3140select t1.x, t3.x from t1, t2, t3 where t1.x = t2.x and t3.x >= t1.y and t3.x <= t2.y; 3141x x 31421 1 31432 1 31443 1 31453 2 31463 3 31474 3 31484 4 31494 5 3150drop table t1,t2,t3; 3151create table t1 (id char(16) not null default '', primary key (id)); 3152insert into t1 values ('100'),('101'),('102'); 3153create table t2 (id char(16) default null); 3154insert into t2 values (1); 3155create view v1 as select t1.id from t1; 3156create view v2 as select t2.id from t2; 3157create view v3 as select (t1.id+2) as id from t1 natural left join t2; 3158select t1.id from t1 left join v2 using (id); 3159id 3160100 3161101 3162102 3163select t1.id from v2 right join t1 using (id); 3164id 3165100 3166101 3167102 3168select t1.id from t1 left join v3 using (id); 3169id 3170100 3171101 3172102 3173select * from t1 left join v2 using (id); 3174id 3175100 3176101 3177102 3178select * from v2 right join t1 using (id); 3179id 3180100 3181101 3182102 3183select * from t1 left join v3 using (id); 3184id 3185100 3186101 3187102 3188select v1.id from v1 left join v2 using (id); 3189id 3190100 3191101 3192102 3193select v1.id from v2 right join v1 using (id); 3194id 3195100 3196101 3197102 3198select v1.id from v1 left join v3 using (id); 3199id 3200100 3201101 3202102 3203select * from v1 left join v2 using (id); 3204id 3205100 3206101 3207102 3208select * from v2 right join v1 using (id); 3209id 3210100 3211101 3212102 3213select * from v1 left join v3 using (id); 3214id 3215100 3216101 3217102 3218drop table t1, t2; 3219drop view v1, v2, v3; 3220create table t1 (id int(11) not null default '0'); 3221insert into t1 values (123),(191),(192); 3222create table t2 (id char(16) character set utf8 not null); 3223insert into t2 values ('58013'),('58014'),('58015'),('58016'); 3224create table t3 (a_id int(11) not null, b_id char(16) character set utf8); 3225insert into t3 values (123,null),(123,null),(123,null),(123,null),(123,null),(123,'58013'); 3226select count(*) 3227from t1 inner join (t3 left join t2 on t2.id = t3.b_id) on t1.id = t3.a_id; 3228count(*) 32296 3230select count(*) 3231from t1 inner join (t2 right join t3 on t2.id = t3.b_id) on t1.id = t3.a_id; 3232count(*) 32336 3234drop table t1,t2,t3; 3235create table t1 (a int); 3236create table t2 (b int); 3237create table t3 (c int); 3238select * from t1 join t2 join t3 on (t1.a=t3.c); 3239a b c 3240select * from t1 join t2 left join t3 on (t1.a=t3.c); 3241a b c 3242select * from t1 join t2 right join t3 on (t1.a=t3.c); 3243a b c 3244select * from t1 join t2 straight_join t3 on (t1.a=t3.c); 3245a b c 3246drop table t1, t2 ,t3; 3247create table t1(f1 int, f2 date); 3248insert into t1 values(1,'2005-01-01'),(2,'2005-09-01'),(3,'2005-09-30'), 3249(4,'2005-10-01'),(5,'2005-12-30'); 3250select * from t1 where f2 >= 0 order by f2; 3251f1 f2 32521 2005-01-01 32532 2005-09-01 32543 2005-09-30 32554 2005-10-01 32565 2005-12-30 3257select * from t1 where f2 >= '0000-00-00' order by f2; 3258f1 f2 32591 2005-01-01 32602 2005-09-01 32613 2005-09-30 32624 2005-10-01 32635 2005-12-30 3264select * from t1 where f2 >= '2005-09-31' order by f2; 3265f1 f2 32664 2005-10-01 32675 2005-12-30 3268select * from t1 where f2 >= '2005-09-3a' order by f2; 3269f1 f2 32703 2005-09-30 32714 2005-10-01 32725 2005-12-30 3273Warnings: 3274Warning 1292 Incorrect date value: '2005-09-3a' for column 'f2' at row 1 3275select * from t1 where f2 <= '2005-09-31' order by f2; 3276f1 f2 32771 2005-01-01 32782 2005-09-01 32793 2005-09-30 3280select * from t1 where f2 <= '2005-09-3a' order by f2; 3281f1 f2 32821 2005-01-01 32832 2005-09-01 3284Warnings: 3285Warning 1292 Incorrect date value: '2005-09-3a' for column 'f2' at row 1 3286drop table t1; 3287create table t1 (f1 int, f2 int); 3288insert into t1 values (1, 30), (2, 20), (3, 10); 3289create algorithm=merge view v1 as select f1, f2 from t1; 3290create algorithm=merge view v2 (f2, f1) as select f1, f2 from t1; 3291create algorithm=merge view v3 as select t1.f1 as f2, t1.f2 as f1 from t1; 3292select t1.f1 as x1, f1 from t1 order by t1.f1; 3293x1 f1 32941 1 32952 2 32963 3 3297select v1.f1 as x1, f1 from v1 order by v1.f1; 3298x1 f1 32991 1 33002 2 33013 3 3302select v2.f1 as x1, f1 from v2 order by v2.f1; 3303x1 f1 330410 10 330520 20 330630 30 3307select v3.f1 as x1, f1 from v3 order by v3.f1; 3308x1 f1 330910 10 331020 20 331130 30 3312select f1, f2, v1.f1 as x1 from v1 order by v1.f1; 3313f1 f2 x1 33141 30 1 33152 20 2 33163 10 3 3317select f1, f2, v2.f1 as x1 from v2 order by v2.f1; 3318f1 f2 x1 331910 3 10 332020 2 20 332130 1 30 3322select f1, f2, v3.f1 as x1 from v3 order by v3.f1; 3323f1 f2 x1 332410 3 10 332520 2 20 332630 1 30 3327drop table t1; 3328drop view v1, v2, v3; 3329CREATE TABLE t1(key_a int4 NOT NULL, optimus varchar(32), PRIMARY KEY(key_a)); 3330CREATE TABLE t2(key_a int4 NOT NULL, prime varchar(32), PRIMARY KEY(key_a)); 3331CREATE table t3(key_a int4 NOT NULL, key_b int4 NOT NULL, foo varchar(32), 3332PRIMARY KEY(key_a,key_b)); 3333INSERT INTO t1 VALUES (0,''); 3334INSERT INTO t1 VALUES (1,'i'); 3335INSERT INTO t1 VALUES (2,'j'); 3336INSERT INTO t1 VALUES (3,'k'); 3337INSERT INTO t2 VALUES (1,'r'); 3338INSERT INTO t2 VALUES (2,'s'); 3339INSERT INTO t2 VALUES (3,'t'); 3340INSERT INTO t3 VALUES (1,5,'x'); 3341INSERT INTO t3 VALUES (1,6,'y'); 3342INSERT INTO t3 VALUES (2,5,'xx'); 3343INSERT INTO t3 VALUES (2,6,'yy'); 3344INSERT INTO t3 VALUES (2,7,'zz'); 3345INSERT INTO t3 VALUES (3,5,'xxx'); 3346SELECT t2.key_a,foo 3347FROM t1 INNER JOIN t2 ON t1.key_a = t2.key_a 3348INNER JOIN t3 ON t1.key_a = t3.key_a 3349WHERE t2.key_a=2 and key_b=5; 3350key_a foo 33512 xx 3352EXPLAIN SELECT t2.key_a,foo 3353FROM t1 INNER JOIN t2 ON t1.key_a = t2.key_a 3354INNER JOIN t3 ON t1.key_a = t3.key_a 3355WHERE t2.key_a=2 and key_b=5; 3356id select_type table type possible_keys key key_len ref rows Extra 33571 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index 33581 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 Using index 33591 SIMPLE t3 const PRIMARY PRIMARY 8 const,const 1 NULL 3360SELECT t2.key_a,foo 3361FROM t1 INNER JOIN t2 ON t2.key_a = t1.key_a 3362INNER JOIN t3 ON t1.key_a = t3.key_a 3363WHERE t2.key_a=2 and key_b=5; 3364key_a foo 33652 xx 3366EXPLAIN SELECT t2.key_a,foo 3367FROM t1 INNER JOIN t2 ON t2.key_a = t1.key_a 3368INNER JOIN t3 ON t1.key_a = t3.key_a 3369WHERE t2.key_a=2 and key_b=5; 3370id select_type table type possible_keys key key_len ref rows Extra 33711 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index 33721 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 Using index 33731 SIMPLE t3 const PRIMARY PRIMARY 8 const,const 1 NULL 3374DROP TABLE t1,t2,t3; 3375create table t1 (f1 int); 3376insert into t1 values(1),(2); 3377create table t2 (f2 int, f3 int, key(f2)); 3378insert into t2 values(1,1),(2,2); 3379create table t3 (f4 int not null); 3380insert into t3 values (2),(2),(2); 3381select f1,(select count(*) from t2,t3 where f2=f1 and f3=f4) as count from t1; 3382f1 count 33831 0 33842 3 3385drop table t1,t2,t3; 3386create table t1 (f1 int unique); 3387create table t2 (f2 int unique); 3388create table t3 (f3 int unique); 3389insert into t1 values(1),(2); 3390insert into t2 values(1),(2); 3391insert into t3 values(1),(NULL); 3392select * from t3 where f3 is null; 3393f3 3394NULL 3395select t2.f2 from t1 left join t2 on f1=f2 join t3 on f1=f3 where f1=1; 3396f2 33971 3398drop table t1,t2,t3; 3399create table t1(f1 char, f2 char not null); 3400insert into t1 values(null,'a'); 3401create table t2 (f2 char not null); 3402insert into t2 values('b'); 3403select * from t1 left join t2 on f1=t2.f2 where t1.f2='a'; 3404f1 f2 f2 3405NULL a NULL 3406drop table t1,t2; 3407select * from (select * left join t on f1=f2) tt; 3408ERROR 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 3409CREATE TABLE t1 (sku int PRIMARY KEY, pr int); 3410CREATE TABLE t2 (sku int PRIMARY KEY, sppr int, name varchar(255)); 3411INSERT INTO t1 VALUES 3412(10, 10), (20, 10), (30, 20), (40, 30), (50, 10), (60, 10); 3413INSERT INTO t2 VALUES 3414(10, 10, 'aaa'), (20, 10, 'bbb'), (30, 10, 'ccc'), (40, 20, 'ddd'), 3415(50, 10, 'eee'), (60, 20, 'fff'), (70, 20, 'ggg'), (80, 30, 'hhh'); 3416SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr 3417FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku); 3418sku sppr name sku pr 341920 10 bbb 10 10 342020 10 bbb 20 10 3421EXPLAIN 3422SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr 3423FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku); 3424id select_type table type possible_keys key key_len ref rows Extra 34251 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 NULL 34261 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using MRR 3427DROP TABLE t1,t2; 3428SET SQL_MODE='NO_UNSIGNED_SUBTRACTION'; 3429CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL); 3430INSERT t1 SET i = 0; 3431UPDATE t1 SET i = -1; 3432Warnings: 3433Warning 1264 Out of range value for column 'i' at row 1 3434SELECT * FROM t1; 3435i 34360 3437UPDATE t1 SET i = CAST(i - 1 AS SIGNED); 3438Warnings: 3439Warning 1264 Out of range value for column 'i' at row 1 3440SELECT * FROM t1; 3441i 34420 3443UPDATE t1 SET i = i - 1; 3444Warnings: 3445Warning 1264 Out of range value for column 'i' at row 1 3446SELECT * FROM t1; 3447i 34480 3449DROP TABLE t1; 3450SET SQL_MODE=default; 3451create table t1 (a int); 3452insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 3453create table t2 (a int, b int, c int, e int, primary key(a,b,c)); 3454insert into t2 select A.a, B.a, C.a, C.a from t1 A, t1 B, t1 C; 3455analyze table t2; 3456Table Op Msg_type Msg_text 3457test.t2 analyze status OK 3458select 'In next EXPLAIN, B.rows must be exactly 10:' Z; 3459Z 3460In next EXPLAIN, B.rows must be exactly 10: 3461explain select * from t2 A, t2 B where A.a=5 and A.b=5 and A.C<5 3462and B.a=5 and B.b=A.e and (B.b =1 or B.b = 3 or B.b=5); 3463id select_type table type possible_keys key key_len ref rows Extra 34641 SIMPLE A range PRIMARY PRIMARY 12 NULL 4 Using index condition; Using where; Using MRR 34651 SIMPLE B ref PRIMARY PRIMARY 8 const,test.A.e 10 Using join buffer (Batched Key Access) 3466drop table t1, t2; 3467CREATE TABLE t1 (a int PRIMARY KEY, b int, INDEX(b)); 3468INSERT INTO t1 VALUES (1, 3), (9,4), (7,5), (4,5), (6,2), 3469(3,1), (5,1), (8,9), (2,2), (0,9); 3470CREATE TABLE t2 (c int, d int, f int, INDEX(c,f)); 3471INSERT INTO t2 VALUES 3472(1,0,0), (1,0,1), (2,0,0), (2,0,1), (3,0,0), (4,0,1), 3473(5,0,0), (5,0,1), (6,0,0), (0,0,1), (7,0,0), (7,0,1), 3474(0,0,0), (0,0,1), (8,0,0), (8,0,1), (9,0,0), (9,0,1); 3475EXPLAIN 3476SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6; 3477id select_type table type possible_keys key key_len ref rows Extra 34781 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using index condition; Using MRR 34791 SIMPLE t2 ref c c 5 test.t1.a 2 Using join buffer (Batched Key Access) 3480EXPLAIN 3481SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6 AND a > 0; 3482id select_type table type possible_keys key key_len ref rows Extra 34831 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using index condition; Using where; Using MRR 34841 SIMPLE t2 ref c c 5 test.t1.a 2 Using join buffer (Batched Key Access) 3485DROP TABLE t1, t2; 3486create table t1 ( 3487a int unsigned not null auto_increment primary key, 3488b bit not null, 3489c bit not null 3490); 3491create table t2 ( 3492a int unsigned not null auto_increment primary key, 3493b bit not null, 3494c int unsigned not null, 3495d varchar(50) 3496); 3497insert into t1 (b,c) values (0,1), (0,1); 3498insert into t2 (b,c) values (0,1); 3499select t1.a, t1.b + 0, t1.c + 0, t2.a, t2.b + 0, t2.c, t2.d 3500from t1 left outer join t2 on t1.a = t2.c and t2.b <> 1 3501where t1.b <> 1 order by t1.a; 3502a t1.b + 0 t1.c + 0 a t2.b + 0 c d 35031 0 1 1 0 1 NULL 35042 0 1 NULL NULL NULL NULL 3505drop table t1,t2; 3506SELECT 0.9888889889 * 1.011111411911; 35070.9888889889 * 1.011111411911 35080.9998769417899202067879 3509prepare stmt from 'select 1 as " a "'; 3510Warnings: 3511Warning 1466 Leading spaces are removed from name ' a ' 3512execute stmt; 3513a 35141 3515CREATE TABLE t1 (a int NOT NULL PRIMARY KEY, b int NOT NULL); 3516INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4); 3517CREATE TABLE t2 (c int NOT NULL, INDEX idx(c)); 3518INSERT INTO t2 VALUES 3519(1), (1), (1), (1), (1), (1), (1), (1), 3520(2), (2), (2), (2), 3521(3), (3), 3522(4); 3523EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=1; 3524id select_type table type possible_keys key key_len ref rows Extra 35251 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 NULL 35261 SIMPLE t2 ref idx idx 4 const 7 Using index 3527EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=4; 3528id select_type table type possible_keys key key_len ref rows Extra 35291 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 NULL 35301 SIMPLE t2 ref idx idx 4 const 1 Using index 3531DROP TABLE t1, t2; 3532CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, a int); 3533INSERT INTO t1 VALUES (1,2), (2,NULL), (3,2); 3534CREATE TABLE t2 (b int, c INT, INDEX idx1(b)); 3535INSERT INTO t2 VALUES (2,1), (3,2); 3536CREATE TABLE t3 (d int, e int, INDEX idx1(d)); 3537INSERT INTO t3 VALUES (2,10), (2,20), (1,30), (2,40), (2,50); 3538EXPLAIN 3539SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id 3540WHERE t1.id=2; 3541id select_type table type possible_keys key key_len ref rows Extra 35421 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 NULL 35431 SIMPLE t2 const idx1 NULL NULL NULL 1 NULL 35441 SIMPLE t3 ref idx1 idx1 5 const 3 NULL 3545SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id 3546WHERE t1.id=2; 3547id a b c d e 35482 NULL NULL NULL 2 10 35492 NULL NULL NULL 2 20 35502 NULL NULL NULL 2 40 35512 NULL NULL NULL 2 50 3552DROP TABLE t1,t2,t3; 3553create table t1 (c1 varchar(1), c2 int, c3 int, c4 int, c5 int, c6 int, 3554c7 int, c8 int, c9 int, fulltext key (`c1`)); 3555select distinct match (`c1`) against ('z') , c2, c3, c4,c5, c6,c7, c8 3556from t1 where c9=1 order by c2, c2; 3557match (`c1`) against ('z') c2 c3 c4 c5 c6 c7 c8 3558drop table t1; 3559CREATE TABLE t1 (pk varchar(10) PRIMARY KEY, fk varchar(16)); 3560CREATE TABLE t2 (pk varchar(16) PRIMARY KEY, fk varchar(10)); 3561INSERT INTO t1 VALUES 3562('d','dddd'), ('i','iii'), ('a','aa'), ('b','bb'), ('g','gg'), 3563('e','eee'), ('c','cccc'), ('h','hhh'), ('j','jjj'), ('f','fff'); 3564INSERT INTO t2 VALUES 3565('jjj', 'j'), ('cc','c'), ('ccc','c'), ('aaa', 'a'), ('jjjj','j'), 3566('hhh','h'), ('gg','g'), ('fff','f'), ('ee','e'), ('ffff','f'), 3567('bbb','b'), ('ff','f'), ('cccc','c'), ('dddd','d'), ('jj','j'), 3568('aaaa','a'), ('bb','b'), ('eeee','e'), ('aa','a'), ('hh','h'); 3569EXPLAIN SELECT t2.* 3570FROM t1 JOIN t2 ON t2.fk=t1.pk 3571WHERE t2.fk < 'c' AND t2.pk=t1.fk; 3572id select_type table type possible_keys key key_len ref rows Extra 35731 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 3 Using index condition; Using where; Using MRR 35741 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where; Using join buffer (Batched Key Access) 3575EXPLAIN SELECT t2.* 3576FROM t1 JOIN t2 ON t2.fk=t1.pk 3577WHERE t2.fk BETWEEN 'a' AND 'b' AND t2.pk=t1.fk; 3578id select_type table type possible_keys key key_len ref rows Extra 35791 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using where; Using MRR 35801 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where; Using join buffer (Batched Key Access) 3581EXPLAIN SELECT t2.* 3582FROM t1 JOIN t2 ON t2.fk=t1.pk 3583WHERE t2.fk IN ('a','b') AND t2.pk=t1.fk; 3584id select_type table type possible_keys key key_len ref rows Extra 35851 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using where; Using MRR 35861 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where; Using join buffer (Batched Key Access) 3587DROP TABLE t1,t2; 3588CREATE TABLE t1 (a int, b varchar(20) NOT NULL, PRIMARY KEY(a)); 3589CREATE TABLE t2 (a int, b varchar(20) NOT NULL, 3590PRIMARY KEY (a), UNIQUE KEY (b)); 3591INSERT INTO t1 VALUES (1,'a'),(2,'b'),(3,'c'); 3592INSERT INTO t2 VALUES (1,'a'),(2,'b'),(3,'c'); 3593EXPLAIN SELECT t1.a FROM t1 LEFT JOIN t2 ON t2.b=t1.b WHERE t1.a=3; 3594id select_type table type possible_keys key key_len ref rows Extra 35951 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 NULL 35961 SIMPLE t2 const b b 22 const 1 Using index 3597DROP TABLE t1,t2; 3598CREATE TABLE t1(id int PRIMARY KEY, b int, e int); 3599CREATE TABLE t2(i int, a int, INDEX si(i), INDEX ai(a)); 3600CREATE TABLE t3(a int PRIMARY KEY, c char(4), INDEX ci(c)); 3601INSERT INTO t1 VALUES 3602(1,10,19), (2,20,22), (4,41,42), (9,93,95), (7, 77,79), 3603(6,63,67), (5,55,58), (3,38,39), (8,81,89); 3604INSERT INTO t2 VALUES 3605(21,210), (41,410), (82,820), (83,830), (84,840), 3606(65,650), (51,510), (37,370), (94,940), (76,760), 3607(22,220), (33,330), (40,400), (95,950), (38,380), 3608(67,670), (88,880), (57,570), (96,960), (97,970); 3609INSERT INTO t3 VALUES 3610(210,'bb'), (950,'ii'), (400,'ab'), (500,'ee'), (220,'gg'), 3611(440,'gg'), (310,'eg'), (380,'ee'), (840,'bb'), (830,'ff'), 3612(230,'aa'), (960,'ii'), (410,'aa'), (510,'ee'), (290,'bb'), 3613(450,'gg'), (320,'dd'), (390,'hh'), (850,'jj'), (860,'ff'); 3614EXPLAIN 3615SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3 3616WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND 3617t3.a=t2.a AND t3.c IN ('bb','ee'); 3618id select_type table type possible_keys key key_len ref rows Extra 36191 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 NULL 36201 SIMPLE t2 range si si 5 NULL 4 Using index condition; Using where; Using MRR 36211 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where; Using join buffer (Batched Key Access) 3622EXPLAIN 3623SELECT t3.a FROM t1,t2,t3 3624WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND 3625t3.a=t2.a AND t3.c IN ('bb','ee') ; 3626id select_type table type possible_keys key key_len ref rows Extra 36271 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 NULL 36281 SIMPLE t2 range si,ai si 5 NULL 4 Using index condition; Using where; Using MRR 36291 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where; Using join buffer (Batched Key Access) 3630EXPLAIN 3631SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3 3632WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND 3633t3.c IN ('bb','ee'); 3634id select_type table type possible_keys key key_len ref rows Extra 36351 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 NULL 36361 SIMPLE t2 range si si 5 NULL 2 Using index condition; Using where; Using MRR 36371 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where; Using join buffer (Batched Key Access) 3638EXPLAIN 3639SELECT t3.a FROM t1,t2,t3 3640WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND 3641t3.c IN ('bb','ee'); 3642id select_type table type possible_keys key key_len ref rows Extra 36431 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 NULL 36441 SIMPLE t2 range si,ai si 5 NULL 2 Using index condition; Using where; Using MRR 36451 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where; Using join buffer (Batched Key Access) 3646DROP TABLE t1,t2,t3; 3647CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int); 3648CREATE TABLE t2 ( f11 int PRIMARY KEY ); 3649INSERT INTO t1 VALUES (1,1,1,0,0,0,0),(2,1,1,3,8,1,0),(3,1,1,4,12,1,0); 3650INSERT INTO t2 VALUES (62); 3651SELECT * FROM t1 LEFT JOIN t2 ON f11 = t1.checked_out GROUP BY f1 ORDER BY f2, f3, f4, f5 LIMIT 0, 1; 3652f1 f2 f3 f4 f5 f6 checked_out f11 36531 1 1 0 0 0 0 NULL 3654DROP TABLE t1, t2; 3655DROP TABLE IF EXISTS t1; 3656CREATE TABLE t1(a int); 3657INSERT into t1 values (1), (2), (3); 3658SELECT * FROM t1 LIMIT 2, -1; 3659ERROR 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 3660DROP TABLE t1; 3661CREATE TABLE t1 ( 3662ID_with_null int NULL, 3663ID_better int NOT NULL, 3664INDEX idx1 (ID_with_null), 3665INDEX idx2 (ID_better) 3666); 3667INSERT INTO t1 VALUES (1,1), (2,1), (null,3), (null,3), (null,3), (null,3); 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; 3672INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; 3673SELECT COUNT(*) FROM t1 WHERE ID_with_null IS NULL; 3674COUNT(*) 3675128 3676SELECT COUNT(*) FROM t1 WHERE ID_better=1; 3677COUNT(*) 36782 3679EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL; 3680id select_type table type possible_keys key key_len ref rows Extra 36811 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where 3682DROP INDEX idx1 ON t1; 3683CREATE UNIQUE INDEX idx1 ON t1(ID_with_null); 3684EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL; 3685id select_type table type possible_keys key key_len ref rows Extra 36861 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where 3687DROP TABLE t1; 3688CREATE TABLE t1 ( 3689ID1_with_null int NULL, 3690ID2_with_null int NULL, 3691ID_better int NOT NULL, 3692INDEX idx1 (ID1_with_null, ID2_with_null), 3693INDEX idx2 (ID_better) 3694); 3695INSERT INTO t1 VALUES (1,1,1), (2,2,1), (3,null,3), (null,3,3), (null,null,3), 3696(3,null,3), (null,3,3), (null,null,3), (3,null,3), (null,3,3), (null,null,3); 3697INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; 3698INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; 3699INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; 3700INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; 3701INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; 3702INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; 3703SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null=3; 3704COUNT(*) 370524 3706SELECT COUNT(*) FROM t1 WHERE ID1_with_null=3 AND ID2_with_null IS NULL; 3707COUNT(*) 370824 3709SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null IS NULL; 3710COUNT(*) 3711192 3712SELECT COUNT(*) FROM t1 WHERE ID_better=1; 3713COUNT(*) 37142 3715EXPLAIN SELECT * FROM t1 3716WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ; 3717id select_type table type possible_keys key key_len ref rows Extra 37181 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where 3719EXPLAIN SELECT * FROM t1 3720WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null=3 IS NULL ; 3721id select_type table type possible_keys key key_len ref rows Extra 37221 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where 3723EXPLAIN SELECT * FROM t1 3724WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL; 3725id select_type table type possible_keys key key_len ref rows Extra 37261 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where 3727DROP INDEX idx1 ON t1; 3728CREATE UNIQUE INDEX idx1 ON t1(ID1_with_null,ID2_with_null); 3729EXPLAIN SELECT * FROM t1 3730WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ; 3731id select_type table type possible_keys key key_len ref rows Extra 37321 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where 3733EXPLAIN SELECT * FROM t1 3734WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null IS NULL ; 3735id select_type table type possible_keys key key_len ref rows Extra 37361 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where 3737EXPLAIN SELECT * FROM t1 3738WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL; 3739id select_type table type possible_keys key key_len ref rows Extra 37401 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where 3741EXPLAIN SELECT * FROM t1 3742WHERE ID_better=1 AND ID1_with_null IS NULL AND 3743(ID2_with_null=1 OR ID2_with_null=2); 3744id select_type table type possible_keys key key_len ref rows Extra 37451 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where 3746DROP TABLE t1; 3747CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts)); 3748INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00"); 3749ANALYZE TABLE t1; 3750Table Op Msg_type Msg_text 3751test.t1 analyze status OK 3752CREATE TABLE t2 (a INT, dt1 DATETIME, dt2 DATETIME, PRIMARY KEY (a)); 3753INSERT INTO t2 VALUES (30, "2006-01-01 00:00:00", "2999-12-31 00:00:00"); 3754INSERT INTO t2 SELECT a+1,dt1,dt2 FROM t2; 3755ANALYZE TABLE t2; 3756Table Op Msg_type Msg_text 3757test.t2 analyze status OK 3758EXPLAIN 3759SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30 3760AND t1.ts BETWEEN t2.dt1 AND t2.dt2 3761AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31"; 3762id select_type table type possible_keys key key_len ref rows Extra 37631 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 NULL 37641 SIMPLE t1 range ts ts 4 NULL 1 Using index condition; Using where; Using MRR 3765Warnings: 3766Warning 1292 Incorrect datetime value: '2999-12-31 00:00:00' for column 'ts' at row 1 3767SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30 3768AND t1.ts BETWEEN t2.dt1 AND t2.dt2 3769AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31"; 3770a ts a dt1 dt2 377130 2006-01-03 23:00:00 30 2006-01-01 00:00:00 2999-12-31 00:00:00 3772Warnings: 3773Warning 1292 Incorrect datetime value: '2999-12-31 00:00:00' for column 'ts' at row 1 3774DROP TABLE t1,t2; 3775create table t1 (a bigint unsigned); 3776insert into t1 values 3777(if(1, 9223372036854775808, 1)), 3778(case when 1 then 9223372036854775808 else 1 end), 3779(coalesce(9223372036854775808, 1)); 3780select * from t1; 3781a 37829223372036854775808 37839223372036854775808 37849223372036854775808 3785drop table t1; 3786create table t1 select 3787if(1, 9223372036854775808, 1) i, 3788case when 1 then 9223372036854775808 else 1 end c, 3789coalesce(9223372036854775808, 1) co; 3790show create table t1; 3791Table Create Table 3792t1 CREATE TABLE `t1` ( 3793 `i` decimal(19,0) NOT NULL DEFAULT '0', 3794 `c` decimal(19,0) NOT NULL DEFAULT '0', 3795 `co` decimal(19,0) NOT NULL DEFAULT '0' 3796) ENGINE=MyISAM DEFAULT CHARSET=latin1 3797drop table t1; 3798select 3799if(1, cast(1111111111111111111 as unsigned), 1) i, 3800case when 1 then cast(1111111111111111111 as unsigned) else 1 end c, 3801coalesce(cast(1111111111111111111 as unsigned), 1) co; 3802i c co 38031111111111111111111 1111111111111111111 1111111111111111111 3804CREATE TABLE t1 (name varchar(255)); 3805CREATE TABLE t2 (name varchar(255), n int, KEY (name(3))); 3806INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa '), ('aa'); 3807INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc ',3); 3808INSERT INTO t2 VALUES (concat('cc ', 0x06), 4); 3809INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7); 3810SELECT * FROM t2; 3811name n 3812bb 1 3813aa 2 3814cc 3 3815cc 4 3816cc 5 3817bb 6 3818cc 7 3819SELECT * FROM t2 ORDER BY name; 3820name n 3821aa 2 3822bb 1 3823bb 6 3824cc 4 3825cc 3 3826cc 5 3827cc 7 3828SELECT name, LENGTH(name), n FROM t2 ORDER BY name; 3829name LENGTH(name) n 3830aa 2 2 3831bb 2 1 3832bb 3 6 3833cc 4 4 3834cc 5 3 3835cc 2 5 3836cc 3 7 3837EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; 3838id select_type table type possible_keys key key_len ref rows Extra 38391 SIMPLE t2 ref name name 6 const 3 Using where 3840SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; 3841name LENGTH(name) n 3842cc 5 3 3843cc 2 5 3844cc 3 7 3845EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; 3846id select_type table type possible_keys key key_len ref rows Extra 38471 SIMPLE t2 range name name 6 NULL 3 Using where 3848SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; 3849name LENGTH(name) n 3850cc 5 3 3851cc 4 4 3852cc 2 5 3853cc 3 7 3854EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; 3855id select_type table type possible_keys key key_len ref rows Extra 38561 SIMPLE t2 range name name 6 NULL 3 Using where; Using filesort 3857SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; 3858name LENGTH(name) n 3859cc 4 4 3860cc 5 3 3861cc 2 5 3862cc 3 7 3863EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; 3864id select_type table type possible_keys key key_len ref rows Extra 38651 SIMPLE t1 ALL NULL NULL NULL NULL 5 NULL 38661 SIMPLE t2 ALL name NULL NULL NULL 7 Using where; Using join buffer (Block Nested Loop) 3867SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; 3868name name n 3869bb bb 1 3870aa aa 2 3871aa aa 2 3872cc cc 3 3873cc cc 5 3874bb bb 6 3875cc cc 7 3876ccc NULL NULL 3877DROP TABLE t1,t2; 3878CREATE TABLE t1 (name text); 3879CREATE TABLE t2 (name text, n int, KEY (name(3))); 3880INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa '), ('aa'); 3881INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc ',3); 3882INSERT INTO t2 VALUES (concat('cc ', 0x06), 4); 3883INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7); 3884SELECT * FROM t2; 3885name n 3886bb 1 3887aa 2 3888cc 3 3889cc 4 3890cc 5 3891bb 6 3892cc 7 3893SELECT * FROM t2 ORDER BY name; 3894name n 3895aa 2 3896bb 1 3897bb 6 3898cc 4 3899cc 3 3900cc 5 3901cc 7 3902SELECT name, LENGTH(name), n FROM t2 ORDER BY name; 3903name LENGTH(name) n 3904aa 2 2 3905bb 2 1 3906bb 3 6 3907cc 4 4 3908cc 5 3 3909cc 2 5 3910cc 3 7 3911EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; 3912id select_type table type possible_keys key key_len ref rows Extra 39131 SIMPLE t2 ref name name 6 const 3 Using where 3914SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; 3915name LENGTH(name) n 3916cc 5 3 3917cc 2 5 3918cc 3 7 3919EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; 3920id select_type table type possible_keys key key_len ref rows Extra 39211 SIMPLE t2 range name name 6 NULL 3 Using where 3922SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; 3923name LENGTH(name) n 3924cc 5 3 3925cc 4 4 3926cc 2 5 3927cc 3 7 3928EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; 3929id select_type table type possible_keys key key_len ref rows Extra 39301 SIMPLE t2 range name name 6 NULL 3 Using where; Using filesort 3931SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; 3932name LENGTH(name) n 3933cc 4 4 3934cc 5 3 3935cc 2 5 3936cc 3 7 3937EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; 3938id select_type table type possible_keys key key_len ref rows Extra 39391 SIMPLE t1 ALL NULL NULL NULL NULL 5 NULL 39401 SIMPLE t2 ALL name NULL NULL NULL 7 Using where; Using join buffer (Block Nested Loop) 3941SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; 3942name name n 3943bb bb 1 3944aa aa 2 3945aa aa 2 3946cc cc 3 3947cc cc 5 3948bb bb 6 3949cc cc 7 3950ccc NULL NULL 3951DROP TABLE t1,t2; 3952CREATE TABLE t1 ( 3953access_id int NOT NULL default '0', 3954name varchar(20) default NULL, 3955rank int NOT NULL default '0', 3956KEY idx (access_id) 3957); 3958CREATE TABLE t2 ( 3959faq_group_id int NOT NULL default '0', 3960faq_id int NOT NULL default '0', 3961access_id int default NULL, 3962UNIQUE KEY idx1 (faq_id), 3963KEY idx2 (faq_group_id,faq_id) 3964); 3965INSERT INTO t1 VALUES 3966(1,'Everyone',2),(2,'Help',3),(3,'Technical Support',1),(4,'Chat User',4); 3967INSERT INTO t2 VALUES 3968(261,265,1),(490,494,1); 3969SELECT t2.faq_id 3970FROM t1 INNER JOIN t2 IGNORE INDEX (idx1) 3971ON (t1.access_id = t2.access_id) 3972LEFT JOIN t2 t 3973ON (t.faq_group_id = t2.faq_group_id AND 3974find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4')) 3975WHERE 3976t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265); 3977faq_id 3978265 3979SELECT t2.faq_id 3980FROM t1 INNER JOIN t2 3981ON (t1.access_id = t2.access_id) 3982LEFT JOIN t2 t 3983ON (t.faq_group_id = t2.faq_group_id AND 3984find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4')) 3985WHERE 3986t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265); 3987faq_id 3988265 3989DROP TABLE t1,t2; 3990CREATE TABLE t1 (a INT, b INT, KEY inx (b,a)); 3991INSERT INTO t1 VALUES (1,1), (1,2), (1,3), (1,4), (1,5), (1, 6), (1,7); 3992EXPLAIN SELECT COUNT(*) FROM t1 f1 INNER JOIN t1 f2 3993ON ( f1.b=f2.b AND f1.a<f2.a ) 3994WHERE 1 AND f1.b NOT IN (100,2232,3343,51111); 3995id select_type table type possible_keys key key_len ref rows Extra 39961 SIMPLE f1 index inx inx 10 NULL 7 Using where; Using index 39971 SIMPLE f2 ref inx inx 5 test.f1.b 1 Using where; Using index 3998DROP TABLE t1; 3999CREATE TABLE t1 (c1 INT, c2 INT); 4000INSERT INTO t1 VALUES (1,11), (2,22), (2,22); 4001EXPLAIN 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; 4002id select_type table type possible_keys key key_len ref rows Extra 40031 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where 40042 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 40053 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 40064 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 40075 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 40086 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 40097 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 40108 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 40119 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 401210 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 401311 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 401412 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 401513 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 401614 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 401715 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 401816 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 401917 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 402018 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 402119 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 402220 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 402321 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 402422 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 402523 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 402624 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 402725 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 402826 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 402927 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 403028 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 403129 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 403230 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 4033EXPLAIN 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; 4034ERROR HY000: Too high level of nesting for select 4035DROP TABLE t1; 4036CREATE TABLE t1 ( 4037c1 int(11) NOT NULL AUTO_INCREMENT, 4038c2 varchar(1000) DEFAULT NULL, 4039c3 bigint(20) DEFAULT NULL, 4040c4 bigint(20) DEFAULT NULL, 4041PRIMARY KEY (c1) 4042); 4043EXPLAIN EXTENDED 4044SELECT join_2.c1 4045FROM 4046t1 AS join_0, 4047t1 AS join_1, 4048t1 AS join_2, 4049t1 AS join_3, 4050t1 AS join_4, 4051t1 AS join_5, 4052t1 AS join_6, 4053t1 AS join_7 4054WHERE 4055join_0.c1=join_1.c1 AND 4056join_1.c1=join_2.c1 AND 4057join_2.c1=join_3.c1 AND 4058join_3.c1=join_4.c1 AND 4059join_4.c1=join_5.c1 AND 4060join_5.c1=join_6.c1 AND 4061join_6.c1=join_7.c1 4062OR 4063join_0.c2 < '?' AND 4064join_1.c2 < '?' AND 4065join_2.c2 > '?' AND 4066join_2.c2 < '!' AND 4067join_3.c2 > '?' AND 4068join_4.c2 = '?' AND 4069join_5.c2 <> '?' AND 4070join_6.c2 <> '?' AND 4071join_7.c2 >= '?' AND 4072join_0.c1=join_1.c1 AND 4073join_1.c1=join_2.c1 AND 4074join_2.c1=join_3.c1 AND 4075join_3.c1=join_4.c1 AND 4076join_4.c1=join_5.c1 AND 4077join_5.c1=join_6.c1 AND 4078join_6.c1=join_7.c1 4079GROUP BY 4080join_3.c1, 4081join_2.c1, 4082join_7.c1, 4083join_1.c1, 4084join_0.c1; 4085id select_type table type possible_keys key key_len ref rows filtered Extra 40861 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 4087Warnings: 4088Note 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 4089SHOW WARNINGS; 4090Level Code Message 4091Note 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 4092DROP TABLE t1; 4093SELECT 1 AS ` `; 4094 40951 4096Warnings: 4097Warning 1474 Name ' ' has become '' 4098SELECT 1 AS ` `; 4099 41001 4101Warnings: 4102Warning 1474 Name ' ' has become '' 4103SELECT 1 AS ` x`; 4104x 41051 4106Warnings: 4107Warning 1466 Leading spaces are removed from name ' x' 4108CREATE VIEW v1 AS SELECT 1 AS ``; 4109ERROR 42000: Incorrect column name '' 4110CREATE VIEW v1 AS SELECT 1 AS ` `; 4111ERROR 42000: Incorrect column name ' ' 4112CREATE VIEW v1 AS SELECT 1 AS ` `; 4113ERROR 42000: Incorrect column name ' ' 4114CREATE VIEW v1 AS SELECT (SELECT 1 AS ` `); 4115ERROR 42000: Incorrect column name ' ' 4116CREATE VIEW v1 AS SELECT 1 AS ` x`; 4117Warnings: 4118Warning 1466 Leading spaces are removed from name ' x' 4119SELECT `x` FROM v1; 4120x 41211 4122ALTER VIEW v1 AS SELECT 1 AS ` `; 4123ERROR 42000: Incorrect column name ' ' 4124DROP VIEW v1; 4125select str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT' 4126 and '2007/10/20 00:00:00 GMT'; 4127str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT' 4128 and '2007/10/20 00:00:00 GMT' 41291 4130Warnings: 4131Warning 1292 Truncated incorrect datetime value: '2007/10/01 00:00:00 GMT' 4132Warning 1292 Truncated incorrect datetime value: '2007/10/20 00:00:00 GMT' 4133select str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6'; 4134str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6' 41351 4136Warnings: 4137Warning 1292 Truncated incorrect date value: '2007/10/01 00:00:00 GMT-6' 4138select str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6'; 4139str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6' 41401 4141Warnings: 4142Warning 1292 Truncated incorrect date value: '2007/10/2000:00:00 GMT-6' 4143select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6'; 4144str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6' 41451 4146Warnings: 4147Warning 1292 Truncated incorrect date value: '2007-10-1 00:00:00 GMT-6' 4148select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6'; 4149str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6' 41501 4151Warnings: 4152Warning 1292 Truncated incorrect date value: '2007-10-01 x00:00:00 GMT-6' 4153select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6'; 4154str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6' 41551 4156Warnings: 4157Warning 1292 Truncated incorrect datetime value: '2007-10-01 00:00:00 GMT-6' 4158select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6'; 4159str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6' 41601 4161Warnings: 4162Warning 1292 Truncated incorrect datetime value: '2007-10-01 00:x00:00 GMT-6' 4163select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6'; 4164str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6' 41651 4166Warnings: 4167Warning 1292 Truncated incorrect datetime value: '2007-10-01 x12:34:56 GMT-6' 4168select str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6'; 4169str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6' 41701 4171Warnings: 4172Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34x:56 GMT-6' 4173select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6'; 4174str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6' 41750 4176Warnings: 4177Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34x:56 GMT-6' 4178select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34:56'; 4179str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34:56' 41801 4181select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 12:00:00'; 4182str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 12:00:00' 41830 4184select str_to_date('2007-10-01 12','%Y-%m-%d %H') = '2007-10-01 12:00:00'; 4185str_to_date('2007-10-01 12','%Y-%m-%d %H') = '2007-10-01 12:00:00' 41861 4187select str_to_date('2007-10-01 12:34','%Y-%m-%d %H') = '2007-10-01 12:00:00'; 4188str_to_date('2007-10-01 12:34','%Y-%m-%d %H') = '2007-10-01 12:00:00' 41891 4190Warnings: 4191Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34' 4192select str_to_date('2007-02-30 12:34','%Y-%m-%d %H:%i') = '2007-02-30 12:34'; 4193str_to_date('2007-02-30 12:34','%Y-%m-%d %H:%i') = '2007-02-30 12:34' 41941 4195select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'; 4196str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34' 41971 4198select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00' 4199 and '2007/10/20 00:00:00'; 4200str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00' 4201 and '2007/10/20 00:00:00' 42021 4203set SQL_MODE=TRADITIONAL; 4204select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'; 4205str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34' 4206NULL 4207Warnings: 4208Warning 1292 Truncated incorrect datetime value: '2007-10-00 12:34' 4209Warning 1411 Incorrect datetime value: '2007-10-00 12:34' for function str_to_date 4210select str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'; 4211str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34' 42120 4213Warnings: 4214Warning 1292 Truncated incorrect datetime value: '2007-10-00 12:34' 4215select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34'; 4216str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34' 4217NULL 4218Warnings: 4219Warning 1411 Incorrect datetime value: '2007-10-00 12:34' for function str_to_date 4220select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01' 4221 and '2007/10/20'; 4222str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01' 4223 and '2007/10/20' 4224NULL 4225Warnings: 4226Warning 1411 Incorrect datetime value: '2007-10-00' for function str_to_date 4227set SQL_MODE=DEFAULT; 4228select str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20'; 4229str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20' 42301 4231Warnings: 4232Warning 1292 Truncated incorrect datetime value: '' 4233select str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20'; 4234str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20' 42350 4236select str_to_date('','%Y-%m-%d %H:%i') = '2007-10-01 12:34'; 4237str_to_date('','%Y-%m-%d %H:%i') = '2007-10-01 12:34' 42380 4239select str_to_date(NULL,'%Y-%m-%d %H:%i') = '2007-10-01 12:34'; 4240str_to_date(NULL,'%Y-%m-%d %H:%i') = '2007-10-01 12:34' 4241NULL 4242select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = ''; 4243str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '' 42440 4245Warnings: 4246Warning 1292 Truncated incorrect datetime value: '' 4247select str_to_date('1','%Y-%m-%d') = '1'; 4248str_to_date('1','%Y-%m-%d') = '1' 42490 4250Warnings: 4251Warning 1292 Truncated incorrect date value: '1' 4252select str_to_date('1','%Y-%m-%d') = '1'; 4253str_to_date('1','%Y-%m-%d') = '1' 42540 4255Warnings: 4256Warning 1292 Truncated incorrect date value: '1' 4257select str_to_date('','%Y-%m-%d') = ''; 4258str_to_date('','%Y-%m-%d') = '' 42590 4260Warnings: 4261Warning 1292 Truncated incorrect date value: '' 4262select str_to_date('1000-01-01','%Y-%m-%d') between '0000-00-00' and NULL; 4263str_to_date('1000-01-01','%Y-%m-%d') between '0000-00-00' and NULL 42640 4265select str_to_date('1000-01-01','%Y-%m-%d') between NULL and '2000-00-00'; 4266str_to_date('1000-01-01','%Y-%m-%d') between NULL and '2000-00-00' 42670 4268select str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL; 4269str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL 42700 4271CREATE TABLE t1 (c11 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY); 4272CREATE TABLE t2 (c21 INT UNSIGNED NOT NULL, 4273c22 INT DEFAULT NULL, 4274KEY(c21, c22)); 4275CREATE TABLE t3 (c31 INT UNSIGNED NOT NULL DEFAULT 0, 4276c32 INT DEFAULT NULL, 4277c33 INT NOT NULL, 4278c34 INT UNSIGNED DEFAULT 0, 4279KEY (c33, c34, c32)); 4280INSERT INTO t1 values (),(),(),(),(); 4281INSERT INTO t2 SELECT a.c11, b.c11 FROM t1 a, t1 b; 4282INSERT INTO t3 VALUES (1, 1, 1, 0), 4283(2, 2, 0, 0), 4284(3, 3, 1, 0), 4285(4, 4, 0, 0), 4286(5, 5, 1, 0); 4287SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND 4288t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND 4289t3.c33 = 1 AND t2.c22 in (1, 3) 4290ORDER BY c32; 4291c32 42921 42931 42943 42953 42965 42975 4298SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND 4299t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND 4300t3.c33 = 1 AND t2.c22 in (1, 3) 4301ORDER BY c32 DESC; 4302c32 43035 43045 43053 43063 43071 43081 4309DROP TABLE t1, t2, t3; 4310 4311# 4312# Bug#30736: Row Size Too Large Error Creating a Table and 4313# Inserting Data. 4314# 4315DROP TABLE IF EXISTS t1; 4316DROP TABLE IF EXISTS t2; 4317 4318CREATE TABLE t1( 4319c1 DECIMAL(10, 2), 4320c2 FLOAT); 4321 4322INSERT INTO t1 VALUES (0, 1), (2, 3), (4, 5); 4323 4324CREATE TABLE t2( 4325c3 DECIMAL(10, 2)) 4326SELECT 4327c1 * c2 AS c3 4328FROM t1; 4329 4330SELECT * FROM t1; 4331c1 c2 43320.00 1 43332.00 3 43344.00 5 4335 4336SELECT * FROM t2; 4337c3 43380.00 43396.00 434020.00 4341 4342DROP TABLE t1; 4343DROP TABLE t2; 4344 4345CREATE TABLE t1 (c1 BIGINT NOT NULL); 4346INSERT INTO t1 (c1) VALUES (1); 4347SELECT * FROM t1 WHERE c1 > NULL + 1; 4348c1 4349DROP TABLE t1; 4350 4351CREATE TABLE t1 (a VARCHAR(10) NOT NULL PRIMARY KEY); 4352INSERT INTO t1 (a) VALUES ('foo0'), ('bar0'), ('baz0'); 4353SELECT * FROM t1 WHERE a IN (CONCAT('foo', 0), 'bar'); 4354a 4355foo0 4356DROP TABLE t1; 4357CREATE TABLE t1 (a INT, b INT); 4358CREATE TABLE t2 (a INT, c INT, KEY(a)); 4359INSERT INTO t1 VALUES (1, 1), (2, 2); 4360INSERT INTO t2 VALUES (1, 1), (1, 2), (1, 3), (1, 4), (1, 5), 4361(2, 1), (2, 2), (2, 3), (2, 4), (2, 5), 4362(3, 1), (3, 2), (3, 3), (3, 4), (3, 5), 4363(4, 1), (4, 2), (4, 3), (4, 4), (4, 5); 4364FLUSH STATUS; 4365SELECT DISTINCT b FROM t1 LEFT JOIN t2 USING(a) WHERE c <= 3; 4366b 43671 43682 4369SHOW STATUS LIKE 'Handler_read%'; 4370Variable_name Value 4371Handler_read_first 0 4372Handler_read_key 2 4373Handler_read_last 0 4374Handler_read_next 10 4375Handler_read_prev 0 4376Handler_read_rnd 10 4377Handler_read_rnd_next 7 4378DROP TABLE t1, t2; 4379CREATE TABLE t1 (f1 bigint(20) NOT NULL default '0', 4380f2 int(11) NOT NULL default '0', 4381f3 bigint(20) NOT NULL default '0', 4382f4 varchar(255) NOT NULL default '', 4383PRIMARY KEY (f1), 4384KEY key1 (f4), 4385KEY key2 (f2)); 4386CREATE TABLE t2 (f1 int(11) NOT NULL default '0', 4387f2 enum('A1','A2','A3') NOT NULL default 'A1', 4388f3 int(11) NOT NULL default '0', 4389PRIMARY KEY (f1), 4390KEY key1 (f3)); 4391CREATE TABLE t3 (f1 bigint(20) NOT NULL default '0', 4392f2 datetime NOT NULL default '1980-01-01 00:00:00', 4393PRIMARY KEY (f1)); 4394insert into t1 values (1, 1, 1, 'abc'); 4395insert into t1 values (2, 1, 2, 'def'); 4396insert into t1 values (3, 1, 2, 'def'); 4397insert into t2 values (1, 'A1', 1); 4398insert into t3 values (1, '1980-01-01'); 4399SELECT a.f3, cr.f4, count(*) count 4400FROM t2 a 4401STRAIGHT_JOIN t1 cr ON cr.f2 = a.f1 4402LEFT JOIN 4403(t1 cr2 4404JOIN t3 ae2 ON cr2.f3 = ae2.f1 4405) ON a.f1 = cr2.f2 AND ae2.f2 < now() - INTERVAL 7 DAY AND 4406cr.f4 = cr2.f4 4407GROUP BY a.f3, cr.f4; 4408f3 f4 count 44091 abc 1 44101 def 2 4411drop table t1, t2, t3; 4412CREATE TABLE t1 (a INT KEY, b INT); 4413INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4); 4414EXPLAIN EXTENDED SELECT a, b FROM t1 WHERE a > 1 AND a = b LIMIT 2; 4415id select_type table type possible_keys key key_len ref rows filtered Extra 44161 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using index condition; Using where; Using MRR 4417Warnings: 4418Note 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 4419EXPLAIN EXTENDED SELECT a, b FROM t1 WHERE a > 1 AND b = a LIMIT 2; 4420id select_type table type possible_keys key key_len ref rows filtered Extra 44211 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using index condition; Using where; Using MRR 4422Warnings: 4423Note 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 4424DROP TABLE t1; 4425# 4426# Bug#47019: Assertion failed: 0, file .\rt_mbr.c, line 138 when 4427# forcing a spatial index 4428# 4429CREATE TABLE t1(a LINESTRING NOT NULL, SPATIAL KEY(a)); 4430INSERT INTO t1 VALUES 4431(GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1)')), 4432(GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1)')); 4433EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2; 4434id select_type table type possible_keys key key_len ref rows Extra 44351 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL 44361 SIMPLE t2 ALL a NULL NULL NULL 2 Range checked for each record (index map: 0x1) 4437SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2; 44381 44391 44401 44411 44421 4443EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a); 4444id select_type table type possible_keys key key_len ref rows Extra 44451 SIMPLE t1 ALL NULL NULL NULL NULL 2 NULL 44461 SIMPLE t2 ALL a NULL NULL NULL 2 Range checked for each record (index map: 0x1) 4447SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a); 44481 44491 44501 44511 44521 4453DROP TABLE t1; 4454# 4455# Bug #48291 : crash with row() operator,select into @var, and 4456# subquery returning multiple rows 4457# 4458CREATE TABLE t1(a INT); 4459INSERT INTO t1 VALUES (2),(3); 4460# Should not crash 4461SELECT 1 FROM t1 WHERE a <> 1 AND NOT 4462ROW(1,a) <=> ROW(1,(SELECT 1 FROM t1)) 4463INTO @var0; 4464ERROR 21000: Subquery returns more than 1 row 4465DROP TABLE t1; 4466# 4467# Bug #48458: simple query tries to allocate enormous amount of 4468# memory 4469# 4470CREATE TABLE t1(a INT NOT NULL, b YEAR); 4471INSERT INTO t1 VALUES (); 4472Warnings: 4473Warning 1364 Field 'a' doesn't have a default value 4474CREATE TABLE t2(c INT); 4475# Should not err out because of out-of-memory 4476SELECT 1 FROM t2 JOIN t1 ON 1=1 4477WHERE a != '1' AND NOT a >= b OR NOT ROW(b,a )<> ROW(a,a); 44781 4479DROP TABLE t1,t2; 4480# 4481# Bug #49199: Optimizer handles incorrectly: 4482# field='const1' AND field='const2' in some cases 4483 4484CREATE TABLE t1(a DATETIME NOT NULL); 4485INSERT INTO t1 VALUES('2001-01-01'); 4486SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; 4487a 44882001-01-01 00:00:00 4489EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; 4490id select_type table type possible_keys key key_len ref rows filtered Extra 44911 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 NULL 4492Warnings: 4493Note 1003 /* select#1 */ select '2001-01-01 00:00:00' AS `a` from dual where 1 4494DROP TABLE t1; 4495CREATE TABLE t1(a DATE NOT NULL); 4496INSERT INTO t1 VALUES('2001-01-01'); 4497SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; 4498a 44992001-01-01 4500EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; 4501id select_type table type possible_keys key key_len ref rows filtered Extra 45021 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 NULL 4503Warnings: 4504Note 1003 /* select#1 */ select '2001-01-01' AS `a` from dual where 1 4505DROP TABLE t1; 4506CREATE TABLE t1(a TIMESTAMP NOT NULL NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); 4507INSERT INTO t1 VALUES('2001-01-01'); 4508SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; 4509a 45102001-01-01 00:00:00 4511EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; 4512id select_type table type possible_keys key key_len ref rows filtered Extra 45131 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 NULL 4514Warnings: 4515Note 1003 /* select#1 */ select '2001-01-01 00:00:00' AS `a` from dual where 1 4516DROP TABLE t1; 4517CREATE TABLE t1(a DATETIME NOT NULL, b DATE NOT NULL); 4518INSERT INTO t1 VALUES('2001-01-01', '2001-01-01'); 4519SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00'; 4520a b 45212001-01-01 00:00:00 2001-01-01 4522EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00'; 4523id select_type table type possible_keys key key_len ref rows filtered Extra 45241 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 NULL 4525Warnings: 4526Note 1003 /* select#1 */ select '2001-01-01 00:00:00' AS `a`,'2001-01-01' AS `b` from dual where 1 4527DROP TABLE t1; 4528CREATE TABLE t1(a DATETIME NOT NULL, b VARCHAR(20) NOT NULL); 4529INSERT INTO t1 VALUES('2001-01-01', '2001-01-01'); 4530SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00'; 4531a b 4532EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00'; 4533id select_type table type possible_keys key key_len ref rows filtered Extra 45341 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 4535Warnings: 4536Note 1003 /* select#1 */ select '2001-01-01 00:00:00' AS `a`,'2001-01-01' AS `b` from dual where 0 4537SELECT * FROM t1 WHERE a='2001-01-01 00:00:00' AND a=b AND b='2001-01-01'; 4538a b 45392001-01-01 00:00:00 2001-01-01 4540EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01 00:00:00' AND a=b AND b='2001-01-01'; 4541id select_type table type possible_keys key key_len ref rows filtered Extra 45421 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 NULL 4543Warnings: 4544Note 1003 /* select#1 */ select '2001-01-01 00:00:00' AS `a`,'2001-01-01' AS `b` from dual where 1 4545DROP TABLE t1; 4546CREATE TABLE t1(a DATETIME NOT NULL, b DATE NOT NULL); 4547INSERT INTO t1 VALUES('2001-01-01', '2001-01-01'); 4548SELECT x.a, y.a, z.a FROM t1 x 4549JOIN t1 y ON x.a=y.a 4550JOIN t1 z ON y.a=z.a 4551WHERE x.a='2001-01-01' AND z.a='2001-01-01 00:00:00'; 4552a a a 45532001-01-01 00:00:00 2001-01-01 00:00:00 2001-01-01 00:00:00 4554EXPLAIN EXTENDED SELECT x.a, y.a, z.a FROM t1 x 4555JOIN t1 y ON x.a=y.a 4556JOIN t1 z ON y.a=z.a 4557WHERE x.a='2001-01-01' AND z.a='2001-01-01 00:00:00'; 4558id select_type table type possible_keys key key_len ref rows filtered Extra 45591 SIMPLE x system NULL NULL NULL NULL 1 100.00 NULL 45601 SIMPLE y system NULL NULL NULL NULL 1 100.00 NULL 45611 SIMPLE z system NULL NULL NULL NULL 1 100.00 NULL 4562Warnings: 4563Note 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 4564DROP TABLE t1; 4565# 4566# Bug #49897: crash in ptr_compare when char(0) NOT NULL 4567# column is used for ORDER BY 4568# 4569SET @old_sort_buffer_size= @@session.sort_buffer_size; 4570SET @@sort_buffer_size= 40000; 4571CREATE TABLE t1(a CHAR(0) NOT NULL); 4572INSERT INTO t1 VALUES (0), (0), (0); 4573INSERT INTO t1 SELECT t11.a FROM t1 t11, t1 t12; 4574INSERT INTO t1 SELECT t11.a FROM t1 t11, t1 t12; 4575INSERT INTO t1 SELECT t11.a FROM t1 t11, t1 t12; 4576EXPLAIN SELECT a FROM t1 ORDER BY a; 4577id select_type table type possible_keys key key_len ref rows Extra 45781 SIMPLE t1 ALL NULL NULL NULL NULL 24492 NULL 4579SELECT a FROM t1 ORDER BY a; 4580DROP TABLE t1; 4581CREATE TABLE t1(a CHAR(0) NOT NULL, b CHAR(0) NOT NULL, c int); 4582INSERT INTO t1 VALUES (0, 0, 0), (0, 0, 2), (0, 0, 1); 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; 4585INSERT INTO t1 SELECT t11.a, t11.b, t11.c FROM t1 t11, t1 t12; 4586EXPLAIN SELECT a FROM t1 ORDER BY a LIMIT 5; 4587id select_type table type possible_keys key key_len ref rows Extra 45881 SIMPLE t1 ALL NULL NULL NULL NULL 24492 NULL 4589SELECT a FROM t1 ORDER BY a LIMIT 5; 4590a 4591 4592 4593 4594 4595 4596EXPLAIN SELECT * FROM t1 ORDER BY a, b LIMIT 5; 4597id select_type table type possible_keys key key_len ref rows Extra 45981 SIMPLE t1 ALL NULL NULL NULL NULL 24492 NULL 4599SELECT * FROM t1 ORDER BY a, b LIMIT 5; 4600a b c 4601 0 4602 2 4603 1 4604 0 4605 2 4606EXPLAIN SELECT * FROM t1 ORDER BY a, b, c LIMIT 5; 4607id select_type table type possible_keys key key_len ref rows Extra 46081 SIMPLE t1 ALL NULL NULL NULL NULL 24492 Using filesort 4609SELECT * FROM t1 ORDER BY a, b, c LIMIT 5; 4610a b c 4611 0 4612 0 4613 0 4614 0 4615 0 4616EXPLAIN SELECT * FROM t1 ORDER BY c, a LIMIT 5; 4617id select_type table type possible_keys key key_len ref rows Extra 46181 SIMPLE t1 ALL NULL NULL NULL NULL 24492 Using filesort 4619SELECT * FROM t1 ORDER BY c, a LIMIT 5; 4620a b c 4621 0 4622 0 4623 0 4624 0 4625 0 4626SET @@sort_buffer_size= @old_sort_buffer_size; 4627DROP TABLE t1; 4628End of 5.0 tests 4629create table t1(a INT, KEY (a)); 4630INSERT INTO t1 VALUES (1),(2),(3),(4),(5); 4631SELECT a FROM t1 ORDER BY a LIMIT 2; 4632a 46331 46342 4635SELECT a FROM t1 ORDER BY a LIMIT 2,4294967296; 4636a 46373 46384 46395 4640SELECT a FROM t1 ORDER BY a LIMIT 2,4294967297; 4641a 46423 46434 46445 4645DROP TABLE t1; 4646CREATE TABLE A (date_key date); 4647CREATE TABLE C ( 4648pk int, 4649int_nokey int, 4650int_key int, 4651date_key date NOT NULL, 4652date_nokey date, 4653varchar_key varchar(1) 4654); 4655INSERT INTO C VALUES 4656(1,1,1,'0000-00-00',NULL,NULL), 4657(1,1,1,'0000-00-00',NULL,NULL); 4658SELECT 1 FROM C WHERE pk > ANY (SELECT 1 FROM C); 46591 4660SELECT COUNT(DISTINCT 1) FROM C 4661WHERE date_key = (SELECT 1 FROM A WHERE C.date_key IS NULL) GROUP BY pk; 4662COUNT(DISTINCT 1) 4663SELECT date_nokey FROM C 4664WHERE int_key IN (SELECT 1 FROM A) 4665HAVING date_nokey = '10:41:7' 4666ORDER BY date_key; 4667date_nokey 4668Warnings: 4669Warning 1292 Incorrect date value: '10:41:7' for column 'date_nokey' at row 1 4670DROP TABLE A,C; 4671CREATE TABLE t1 (a INT NOT NULL, b INT); 4672INSERT INTO t1 VALUES (1, 1); 4673EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND a=a) OR b > 2; 4674id select_type table type possible_keys key key_len ref rows filtered Extra 46751 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 NULL 4676Warnings: 4677Note 1003 /* select#1 */ select '1' AS `a`,'1' AS `b` from dual where 1 4678SELECT * FROM t1 WHERE (a=a AND a=a) OR b > 2; 4679a b 46801 1 4681DROP TABLE t1; 4682CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, c INT NOT NULL); 4683EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND b=b AND c=c) OR b > 20; 4684id select_type table type possible_keys key key_len ref rows filtered Extra 46851 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 const row not found 4686Warnings: 4687Note 1003 /* select#1 */ select NULL AS `a`,NULL AS `b`,NULL AS `c` from `test`.`t1` where 1 4688EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND a=a AND b=b) OR b > 20; 4689id select_type table type possible_keys key key_len ref rows filtered Extra 46901 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 const row not found 4691Warnings: 4692Note 1003 /* select#1 */ select NULL AS `a`,NULL AS `b`,NULL AS `c` from `test`.`t1` where 1 4693EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND b=b AND a=a) OR b > 20; 4694id select_type table type possible_keys key key_len ref rows filtered Extra 46951 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 const row not found 4696Warnings: 4697Note 1003 /* select#1 */ select NULL AS `a`,NULL AS `b`,NULL AS `c` from `test`.`t1` where 1 4698DROP TABLE t1; 4699# 4700# Bug#45266: Uninitialized variable lead to an empty result. 4701# 4702drop table if exists A,AA,B,BB; 4703CREATE TABLE `A` ( 4704`pk` int(11) NOT NULL AUTO_INCREMENT, 4705`date_key` date NOT NULL, 4706`date_nokey` date NOT NULL, 4707`datetime_key` datetime NOT NULL, 4708`int_nokey` int(11) NOT NULL, 4709`time_key` time NOT NULL, 4710`time_nokey` time NOT NULL, 4711PRIMARY KEY (`pk`), 4712KEY `date_key` (`date_key`), 4713KEY `time_key` (`time_key`), 4714KEY `datetime_key` (`datetime_key`) 4715); 4716CREATE TABLE `AA` ( 4717`pk` int(11) NOT NULL AUTO_INCREMENT, 4718`int_nokey` int(11) NOT NULL, 4719`time_key` time NOT NULL, 4720KEY `time_key` (`time_key`), 4721PRIMARY KEY (`pk`) 4722); 4723CREATE TABLE `B` ( 4724`date_nokey` date NOT NULL, 4725`date_key` date NOT NULL, 4726`time_key` time NOT NULL, 4727`datetime_nokey` datetime NOT NULL, 4728`varchar_key` varchar(1) NOT NULL, 4729KEY `date_key` (`date_key`), 4730KEY `time_key` (`time_key`), 4731KEY `varchar_key` (`varchar_key`) 4732); 4733INSERT 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'); 4734CREATE TABLE `BB` ( 4735`pk` int(11) NOT NULL AUTO_INCREMENT, 4736`int_nokey` int(11) NOT NULL, 4737`date_key` date NOT NULL, 4738`varchar_nokey` varchar(1) NOT NULL, 4739`date_nokey` date NOT NULL, 4740PRIMARY KEY (`pk`), 4741KEY `date_key` (`date_key`) 4742); 4743INSERT INTO `BB` VALUES (10,8,'0000-00-00','i','0000-00-00'),(11,0,'2005-08-18','','2005-08-18'); 4744SELECT table1 . `pk` AS field1 4745FROM 4746(BB AS table1 INNER JOIN 4747(AA AS table2 STRAIGHT_JOIN A AS table3 4748ON ( table3 . `date_key` = table2 . `pk` )) 4749ON ( table3 . `datetime_key` = table2 . `int_nokey` )) 4750WHERE ( table3 . `date_key` <= 4 AND table2 . `pk` = table1 . `varchar_nokey`) 4751GROUP BY field1 ; 4752field1 4753SELECT table3 .`date_key` field1 4754FROM 4755B table1 LEFT JOIN B table3 JOIN 4756(BB table6 JOIN A table7 ON table6 .`varchar_nokey`) 4757ON table6 .`int_nokey` ON table6 .`date_key` 4758 WHERE NOT ( table1 .`varchar_key` AND table7 .`pk`) GROUP BY field1; 4759field1 4760NULL 4761SELECT table4 . `time_nokey` AS field1 FROM 4762(AA AS table1 CROSS JOIN 4763(AA AS table2 STRAIGHT_JOIN 4764(B AS table3 STRAIGHT_JOIN A AS table4 4765ON ( table4 . `date_key` = table3 . `time_key` )) 4766ON ( table4 . `pk` = table3 . `date_nokey` )) 4767ON ( table4 . `time_key` = table3 . `datetime_nokey` )) 4768WHERE ( table4 . `time_key` < table1 . `time_key` AND 4769table1 . `int_nokey` != 'f') 4770GROUP BY field1 ORDER BY field1 , field1; 4771field1 4772SELECT table1 .`time_key` field2 FROM B table1 LEFT JOIN BB JOIN A table5 ON table5 .`date_nokey` ON table5 .`int_nokey` GROUP BY field2; 4773field2 477400:05:48 477515:13:38 4776drop table A,AA,B,BB; 4777#end of test for bug#45266 4778# 4779# Bug#33546: Slowdown on re-evaluation of constant expressions. 4780# 4781CREATE TABLE t1 (a INT); 4782INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); 4783CREATE TABLE t2 (b INT); 4784INSERT INTO t2 VALUES (2); 4785SELECT * FROM t1 WHERE a = 1 + 1; 4786a 47872 4788EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = 1 + 1; 4789id select_type table type possible_keys key key_len ref rows filtered Extra 47901 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where 4791Warnings: 4792Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = <cache>((1 + 1))) 4793SELECT * FROM t1 HAVING a = 1 + 1; 4794a 47952 4796EXPLAIN EXTENDED SELECT * FROM t1 HAVING a = 1 + 1; 4797id select_type table type possible_keys key key_len ref rows filtered Extra 47981 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 NULL 4799Warnings: 4800Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` having (`test`.`t1`.`a` = <cache>((1 + 1))) 4801SELECT * FROM t1, t2 WHERE a = b + (1 + 1); 4802a b 48034 2 4804EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE a = b + (1 + 1); 4805id select_type table type possible_keys key key_len ref rows filtered Extra 48061 SIMPLE t2 system NULL NULL NULL NULL 1 100.00 NULL 48071 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where 4808Warnings: 4809Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,'2' AS `b` from `test`.`t1` where (`test`.`t1`.`a` = <cache>(('2' + (1 + 1)))) 4810SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1; 4811b a 48122 3 4813EXPLAIN EXTENDED SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1; 4814id select_type table type possible_keys key key_len ref rows filtered Extra 48151 SIMPLE t2 system NULL NULL NULL NULL 1 100.00 NULL 48161 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where 4817Warnings: 4818Note 1003 /* select#1 */ select '2' AS `b`,`test`.`t1`.`a` AS `a` from `test`.`t1` where 1 4819EXPLAIN EXTENDED SELECT * FROM t1 WHERE a > UNIX_TIMESTAMP('2009-03-10 00:00:00'); 4820id select_type table type possible_keys key key_len ref rows filtered Extra 48211 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where 4822Warnings: 4823Note 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'))) 4824CREATE FUNCTION f1() RETURNS INT DETERMINISTIC 4825BEGIN 4826SET @cnt := @cnt + 1; 4827RETURN 1; 4828END;| 4829SET @cnt := 0; 4830SELECT * FROM t1 WHERE a = f1(); 4831a 48321 4833SELECT @cnt; 4834@cnt 48351 4836EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = f1(); 4837id select_type table type possible_keys key key_len ref rows filtered Extra 48381 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where 4839Warnings: 4840Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = <cache>(`f1`())) 4841DROP TABLE t1, t2; 4842DROP FUNCTION f1; 4843# End of bug#33546 4844# 4845# BUG#48052: Valgrind warning - uninitialized value in init_read_record() 4846# 4847# Disable Index condition pushdown 4848SELECT @old_optimizer_switch:=@@optimizer_switch; 4849@old_optimizer_switch:=@@optimizer_switch 4850# 4851CREATE TABLE t1 ( 4852pk int(11) NOT NULL, 4853i int(11) DEFAULT NULL, 4854v varchar(1) DEFAULT NULL, 4855PRIMARY KEY (pk) 4856); 4857INSERT INTO t1 VALUES (2,7,'m'); 4858INSERT INTO t1 VALUES (3,9,'m'); 4859SELECT v 4860FROM t1 4861WHERE NOT pk > 0 4862HAVING v <= 't' 4863ORDER BY pk; 4864v 4865# Restore old value for Index condition pushdown 4866SET SESSION optimizer_switch=@old_optimizer_switch; 4867DROP TABLE t1; 4868# 4869# Bug#49489 Uninitialized cache led to a wrong result. 4870# 4871CREATE TABLE t1(c1 DOUBLE(5,4)); 4872INSERT INTO t1 VALUES (9.1234); 4873SELECT * FROM t1 WHERE c1 < 9.12345; 4874c1 48759.1234 4876DROP TABLE t1; 4877# End of test for bug#49489. 4878# 4879# Bug #49517: Inconsistent behavior while using 4880# NULLable BIGINT and INT columns in comparison 4881# 4882CREATE TABLE t1(a BIGINT UNSIGNED NOT NULL, b BIGINT NULL, c INT NULL); 4883INSERT INTO t1 VALUES(105, NULL, NULL); 4884SELECT * FROM t1 WHERE b < 102; 4885a b c 4886SELECT * FROM t1 WHERE c < 102; 4887a b c 4888SELECT * FROM t1 WHERE 102 < b; 4889a b c 4890SELECT * FROM t1 WHERE 102 < c; 4891a b c 4892DROP TABLE t1; 4893# 4894# Bug #54459: Assertion failed: param.sort_length, 4895# file .\filesort.cc, line 149 (part II) 4896# 4897CREATE TABLE t1(a ENUM('') NOT NULL); 4898INSERT INTO t1 VALUES (), (), (); 4899EXPLAIN SELECT 1 FROM t1 ORDER BY a COLLATE latin1_german2_ci; 4900id select_type table type possible_keys key key_len ref rows Extra 49011 SIMPLE t1 ALL NULL NULL NULL NULL 3 NULL 4902SELECT 1 FROM t1 ORDER BY a COLLATE latin1_german2_ci; 49031 49041 49051 49061 4907DROP TABLE t1; 4908# 4909# Bug #58422: Incorrect result when OUTER JOIN'ing 4910# with an empty table 4911# 4912CREATE TABLE t_empty(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM; 4913CREATE TABLE t1(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM; 4914INSERT INTO t1 VALUES (1,1), (2,2), (3,3); 4915CREATE TABLE t2(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM; 4916INSERT INTO t2 VALUES (1,1), (2,2), (3,3); 4917EXPLAIN 4918SELECT * 4919FROM 4920t1 4921LEFT OUTER JOIN 4922(t2 INNER JOIN t_empty ON TRUE) 4923ON t1.pk=t2.pk 4924WHERE t2.pk <> 2; 4925id select_type table type possible_keys key key_len ref rows Extra 49261 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 4927SELECT * 4928FROM 4929t1 4930LEFT OUTER JOIN 4931(t2 INNER JOIN t_empty ON TRUE) 4932ON t1.pk=t2.pk 4933WHERE t2.pk <> 2; 4934pk i pk i pk i 4935EXPLAIN 4936SELECT * 4937FROM 4938t1 4939LEFT OUTER JOIN 4940(t2 CROSS JOIN t_empty) 4941ON t1.pk=t2.pk 4942WHERE t2.pk <> 2; 4943id select_type table type possible_keys key key_len ref rows Extra 49441 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 4945SELECT * 4946FROM 4947t1 4948LEFT OUTER JOIN 4949(t2 CROSS JOIN t_empty) 4950ON t1.pk=t2.pk 4951WHERE t2.pk <> 2; 4952pk i pk i pk i 4953EXPLAIN 4954SELECT * 4955FROM 4956t1 4957LEFT OUTER JOIN 4958(t2 INNER JOIN t_empty ON t_empty.i=t2.i) 4959ON t1.pk=t2.pk 4960WHERE t2.pk <> 2; 4961id select_type table type possible_keys key key_len ref rows Extra 49621 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 4963SELECT * 4964FROM 4965t1 4966LEFT OUTER JOIN 4967(t2 INNER JOIN t_empty ON t_empty.i=t2.i) 4968ON t1.pk=t2.pk 4969WHERE t2.pk <> 2; 4970pk i pk i pk i 4971DROP TABLE t1,t2,t_empty; 4972End of 5.1 tests 4973# 4974# Bug#45227: Lost HAVING clause led to a wrong result. 4975# 4976CREATE TABLE `CC` ( 4977`int_nokey` int(11) NOT NULL, 4978`int_key` int(11) NOT NULL, 4979`varchar_key` varchar(1) NOT NULL, 4980`varchar_nokey` varchar(1) NOT NULL, 4981KEY `int_key` (`int_key`), 4982KEY `varchar_key` (`varchar_key`) 4983); 4984INSERT INTO `CC` VALUES 4985(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' 4986,'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'), 4987(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' 4988,'x'); 4989EXPLAIN SELECT `varchar_nokey` G1 FROM CC WHERE `int_nokey` AND `int_key` <= 4 4990HAVING G1 ORDER BY `varchar_key` LIMIT 6 ; 4991id select_type table type possible_keys key key_len ref rows Extra 49921 SIMPLE CC range int_key int_key 4 NULL 10 Using index condition; Using where; Using MRR; Using filesort 4993SELECT `varchar_nokey` G1 FROM CC WHERE `int_nokey` AND `int_key` <= 4 4994HAVING G1 ORDER BY `varchar_key` LIMIT 6 ; 4995G1 4996Warnings: 4997Warning 1292 Truncated incorrect DOUBLE value: 'j' 4998Warning 1292 Truncated incorrect DOUBLE value: 'z' 4999Warning 1292 Truncated incorrect DOUBLE value: 'a' 5000Warning 1292 Truncated incorrect DOUBLE value: 'q' 5001Warning 1292 Truncated incorrect DOUBLE value: 'm' 5002DROP TABLE CC; 5003# End of test#45227 5004# 5005# Bug#54515: Crash in opt_range.cc::get_best_group_min_max on 5006# SELECT from VIEW with GROUP BY 5007# 5008CREATE TABLE t1 ( 5009col_int_key int DEFAULT NULL, 5010KEY int_key (col_int_key) 5011) ; 5012INSERT INTO t1 VALUES (1),(2); 5013CREATE VIEW view_t1 AS 5014SELECT t1.col_int_key AS col_int_key 5015FROM t1; 5016SELECT col_int_key FROM view_t1 GROUP BY col_int_key; 5017col_int_key 50181 50192 5020DROP VIEW view_t1; 5021DROP TABLE t1; 5022# End of test BUG#54515 5023# 5024# Bug #57203 Assertion `field_length <= 255' failed. 5025# 5026SELECT coalesce((avg(distinct (geomfromtext("point(25379 -22010)"))))) 5027UNION ALL 5028SELECT coalesce((avg(distinct (geomfromtext("point(25379 -22010)"))))) 5029AS foo 5030; 5031coalesce((avg(distinct (geomfromtext("point(25379 -22010)"))))) 50320.0000 50330.0000 5034CREATE table t1(a text); 5035INSERT INTO t1 VALUES (''), (''); 5036SELECT avg(distinct(t1.a)) FROM t1, t1 t2 5037GROUP BY t2.a ORDER BY t1.a; 5038avg(distinct(t1.a)) 50390 5040DROP TABLE t1; 5041# End of test BUG#57203 5042# 5043# Bug#63020: Function "format"'s 'locale' argument is not considered 5044# when creating a "view' 5045# 5046CREATE TABLE t1 (f1 DECIMAL(10,2)); 5047INSERT INTO t1 VALUES (11.67),(17865.3),(12345678.92); 5048CREATE VIEW view_t1 AS SELECT FORMAT(f1,1,'sk_SK') AS f1 FROM t1; 5049SHOW CREATE VIEW view_t1; 5050View Create View character_set_client collation_connection 5051view_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 5052SELECT * FROM view_t1; 5053f1 505411,7 505517 865,3 505612 345 678,9 5057DROP TABLE t1; 5058DROP VIEW view_t1; 5059# End of test BUG#63020 5060# 5061# Bug #13571700 TINYBLOB NOT NULL, CRASH IN PROTOCOL::NET_STORE_DATA 5062# 5063CREATE TABLE t1 (a TINYBLOB NOT NULL); 5064SELECT a, COUNT(*) FROM t1 WHERE 0; 5065a COUNT(*) 5066NULL 0 5067DROP TABLE t1; 5068# End of test BUG#13571700 5069set optimizer_switch=default; 5070set optimizer_switch=default; 5071