1drop table if exists t1,t2,t3,t4,t11; 2drop table if exists t1_1,t1_2,t9_1,t9_2,t1aa,t2aa; 3drop view if exists v1; 4SET optimizer_switch=ifnull(@optimizer_switch_for_select_test,'outer_join_with_cache=off'); 5set join_cache_level=1; 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 tmp_memory_table_size=0; 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 tmp_memory_table_size=default; 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 tmp_memory_table_size=0; 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 tmp_memory_table_size=default; 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 filesort 6091 SIMPLE t3 ref period period 4 test.t1.period 4181 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 index period period 4 NULL 1 6131 SIMPLE t1 ref period period 4 test.t3.period 4181 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 index period period 4 NULL 1 6171 SIMPLE t3 ref period period 4 test.t1.period 4181 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 6321 SIMPLE t3 const PRIMARY,period PRIMARY 4 const 1 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 13641 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 Using where; Not exists 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 13681 SIMPLE t2 ALL NULL NULL NULL NULL 1200 Using where; Not exists 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 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 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 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 ALL PRIMARY NULL NULL NULL 12 Using where 13961 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 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 ALL PRIMARY NULL NULL NULL 12 Using where 14001 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 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 ALL PRIMARY NULL NULL NULL 12 Using where 14041 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 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 14081 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 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 14121 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 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 14161 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 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 ALL PRIMARY NULL NULL NULL 12 Using where 14201 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 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 ALL PRIMARY NULL NULL NULL 12 Using where 14241 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 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 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 (flat, BNL join) 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.5940 10709871.3069 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 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.9757 13352027981.7087 151629 95 abut wetness 14473298 152350.5053 8368.5480 70032594.9026 151734 70 absentee vest 17788966 254128.0857 3272.5940 10709871.3069 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 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 MariaDB 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 Truncated incorrect datetime value: 'wrong-date-value' 2121Warning 1292 Truncated incorrect datetime value: 'wrong-date-value' 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 22011 3 22022 2 22032 3 22043 2 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 22631 2 22641 3 22652 2 22662 3 22673 2 22683 3 2269NULL 1 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 23341 SIMPLE t1 ALL NULL NULL NULL NULL 2 23351 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where 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 23681 SIMPLE t1 ALL a NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) 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 23761 SIMPLE t1 ALL a NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) 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'); 2399INSERT INTO t2 VALUES ('one'),('two'),('three'),('four'),('five'); 2400EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USE INDEX (a) ON t1.a=t2.a; 2401id select_type table type possible_keys key key_len ref rows Extra 24021 SIMPLE t1 ALL NULL NULL NULL NULL 5 24031 SIMPLE t2 ref a a 23 test.t1.a 2 Using where 2404EXPLAIN SELECT * FROM t1 LEFT JOIN t2 FORCE INDEX (a) ON t1.a=t2.a; 2405id select_type table type possible_keys key key_len ref rows Extra 24061 SIMPLE t1 ALL NULL NULL NULL NULL 5 24071 SIMPLE t2 ref a a 23 test.t1.a 2 Using where 2408DROP TABLE t1, t2; 2409CREATE TABLE t1 ( city char(30) ); 2410INSERT INTO t1 VALUES ('London'); 2411INSERT INTO t1 VALUES ('Paris'); 2412SELECT * FROM t1 WHERE city='London'; 2413city 2414London 2415SELECT * FROM t1 WHERE city='london'; 2416city 2417London 2418EXPLAIN SELECT * FROM t1 WHERE city='London' AND city='london'; 2419id select_type table type possible_keys key key_len ref rows Extra 24201 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where 2421SELECT * FROM t1 WHERE city='London' AND city='london'; 2422city 2423London 2424EXPLAIN SELECT * FROM t1 WHERE city LIKE '%london%' AND city='London'; 2425id select_type table type possible_keys key key_len ref rows Extra 24261 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where 2427SELECT * FROM t1 WHERE city LIKE '%london%' AND city='London'; 2428city 2429London 2430DROP TABLE t1; 2431create table t1 (a int(11) unsigned, b int(11) unsigned); 2432insert into t1 values (1,0), (1,1), (18446744073709551615,0); 2433Warnings: 2434Warning 1264 Out of range value for column 'a' at row 3 2435select a-b from t1 order by 1; 2436a-b 24370 24381 24394294967295 2440select a-b , (a-b < 0) from t1 order by 1; 2441a-b (a-b < 0) 24420 0 24431 0 24444294967295 0 2445select a-b as d, (a-b >= 0), b from t1 group by b having d >= 0; 2446d (a-b >= 0) b 24471 1 0 24480 1 1 2449select cast((a - b) as unsigned) from t1 order by 1; 2450cast((a - b) as unsigned) 24510 24521 24534294967295 2454drop table t1; 2455create table t1 (a int(11)); 2456select all all * from t1; 2457a 2458select distinct distinct * from t1; 2459a 2460select all distinct * from t1; 2461ERROR HY000: Incorrect usage of ALL and DISTINCT 2462select distinct all * from t1; 2463ERROR HY000: Incorrect usage of ALL and DISTINCT 2464drop table t1; 2465CREATE TABLE t1 ( 2466kunde_intern_id int(10) unsigned NOT NULL default '0', 2467kunde_id int(10) unsigned NOT NULL default '0', 2468FK_firma_id int(10) unsigned NOT NULL default '0', 2469aktuell enum('Ja','Nein') NOT NULL default 'Ja', 2470vorname varchar(128) NOT NULL default '', 2471nachname varchar(128) NOT NULL default '', 2472geloescht enum('Ja','Nein') NOT NULL default 'Nein', 2473firma varchar(128) NOT NULL default '' 2474); 2475INSERT INTO t1 VALUES 2476(3964,3051,1,'Ja','Vorname1','1Nachname','Nein','Print Schau XXXX'), 2477(3965,3051111,1,'Ja','Vorname1111','1111Nachname','Nein','Print Schau XXXX'); 2478SELECT kunde_id ,FK_firma_id ,aktuell, vorname, nachname, geloescht FROM t1 2479WHERE 2480( 2481( 2482( '' != '' AND firma LIKE CONCAT('%', '', '%')) 2483OR 2484(vorname LIKE CONCAT('%', 'Vorname1', '%') AND 2485nachname LIKE CONCAT('%', '1Nachname', '%') AND 2486'Vorname1' != '' AND 'xxxx' != '') 2487) 2488AND 2489( 2490aktuell = 'Ja' AND geloescht = 'Nein' AND FK_firma_id = 2 2491) 2492) 2493; 2494kunde_id FK_firma_id aktuell vorname nachname geloescht 2495SELECT kunde_id ,FK_firma_id ,aktuell, vorname, nachname, 2496geloescht FROM t1 2497WHERE 2498( 2499( 2500aktuell = 'Ja' AND geloescht = 'Nein' AND FK_firma_id = 2 2501) 2502AND 2503( 2504( '' != '' AND firma LIKE CONCAT('%', '', '%') ) 2505OR 2506( vorname LIKE CONCAT('%', 'Vorname1', '%') AND 2507nachname LIKE CONCAT('%', '1Nachname', '%') AND 'Vorname1' != '' AND 2508'xxxx' != '') 2509) 2510) 2511; 2512kunde_id FK_firma_id aktuell vorname nachname geloescht 2513SELECT COUNT(*) FROM t1 WHERE 2514( 0 OR (vorname LIKE '%Vorname1%' AND nachname LIKE '%1Nachname%' AND 1)) 2515AND FK_firma_id = 2; 2516COUNT(*) 25170 2518drop table t1; 2519CREATE TABLE t1 (b BIGINT(20) UNSIGNED NOT NULL, PRIMARY KEY (b)); 2520INSERT INTO t1 VALUES (0x8000000000000000); 2521SELECT b FROM t1 WHERE b=0x8000000000000000; 2522b 25239223372036854775808 2524DROP TABLE t1; 2525CREATE TABLE `t1` ( `gid` int(11) default NULL, `uid` int(11) default NULL); 2526CREATE TABLE `t2` ( `ident` int(11) default NULL, `level` char(16) default NULL); 2527INSERT INTO `t2` VALUES (0,'READ'); 2528CREATE TABLE `t3` ( `id` int(11) default NULL, `name` char(16) default NULL); 2529INSERT INTO `t3` VALUES (1,'fs'); 2530select * from t3 left join t1 on t3.id = t1.uid, t2 where t2.ident in (0, t1.gid, t3.id, 0); 2531id name gid uid ident level 25321 fs NULL NULL 0 READ 2533drop table t1,t2,t3; 2534CREATE TABLE t1 ( 2535acct_id int(11) NOT NULL default '0', 2536profile_id smallint(6) default NULL, 2537UNIQUE KEY t1$acct_id (acct_id), 2538KEY t1$profile_id (profile_id) 2539); 2540INSERT INTO t1 VALUES (132,17),(133,18); 2541CREATE TABLE t2 ( 2542profile_id smallint(6) default NULL, 2543queue_id int(11) default NULL, 2544seq int(11) default NULL, 2545KEY t2$queue_id (queue_id) 2546); 2547INSERT INTO t2 VALUES (17,31,4),(17,30,3),(17,36,2),(17,37,1); 2548CREATE TABLE t3 ( 2549id int(11) NOT NULL default '0', 2550qtype int(11) default NULL, 2551seq int(11) default NULL, 2552warn_lvl int(11) default NULL, 2553crit_lvl int(11) default NULL, 2554rr1 tinyint(4) NOT NULL default '0', 2555rr2 int(11) default NULL, 2556default_queue tinyint(4) NOT NULL default '0', 2557KEY t3$qtype (qtype), 2558KEY t3$id (id) 2559); 2560INSERT INTO t3 VALUES (30,1,29,NULL,NULL,0,NULL,0),(31,1,28,NULL,NULL,0,NULL,0), 2561(36,1,34,NULL,NULL,0,NULL,0),(37,1,35,NULL,NULL,0,121,0); 2562SELECT COUNT(*) FROM t1 a STRAIGHT_JOIN t2 pq STRAIGHT_JOIN t3 q 2563WHERE 2564(pq.profile_id = a.profile_id) AND (a.acct_id = 132) AND 2565(pq.queue_id = q.id) AND (q.rr1 <> 1); 2566COUNT(*) 25674 2568drop table t1,t2,t3; 2569create table t1 (f1 int); 2570insert into t1 values (1),(NULL); 2571create table t2 (f2 int, f3 int, f4 int); 2572create index idx1 on t2 (f4); 2573insert into t2 values (1,2,3),(2,4,6); 2574select A.f2 from t1 left join t2 A on A.f2 = f1 where A.f3=(select min(f3) 2575from t2 C where A.f4 = C.f4) or A.f3 IS NULL; 2576f2 25771 2578NULL 2579drop table t1,t2; 2580create table t2 (a tinyint unsigned); 2581create index t2i on t2(a); 2582insert into t2 values (0), (254), (255); 2583explain select * from t2 where a > -1; 2584id select_type table type possible_keys key key_len ref rows Extra 25851 SIMPLE t2 index t2i t2i 2 NULL 3 Using where; Using index 2586select * from t2 where a > -1; 2587a 25880 2589254 2590255 2591drop table t2; 2592CREATE TABLE t1 (a int, b int, c int); 2593INSERT INTO t1 2594SELECT 50, 3, 3 FROM DUAL 2595WHERE NOT EXISTS 2596(SELECT * FROM t1 WHERE a = 50 AND b = 3); 2597SELECT * FROM t1; 2598a b c 259950 3 3 2600INSERT INTO t1 2601SELECT 50, 3, 3 FROM DUAL 2602WHERE NOT EXISTS 2603(SELECT * FROM t1 WHERE a = 50 AND b = 3); 2604select found_rows(); 2605found_rows() 26060 2607SELECT * FROM t1; 2608a b c 260950 3 3 2610select count(*) from t1; 2611count(*) 26121 2613select found_rows(); 2614found_rows() 26151 2616select count(*) from t1 limit 2,3; 2617count(*) 2618select found_rows(); 2619found_rows() 26200 2621select SQL_CALC_FOUND_ROWS count(*) from t1 limit 2,3; 2622count(*) 2623select found_rows(); 2624found_rows() 26251 2626DROP TABLE t1; 2627CREATE TABLE t1 (a INT, b INT); 2628(SELECT a, b AS c FROM t1) ORDER BY c+1; 2629a c 2630(SELECT a, b AS c FROM t1) ORDER BY b+1; 2631a c 2632SELECT a, b AS c FROM t1 ORDER BY c+1; 2633a c 2634SELECT a, b AS c FROM t1 ORDER BY b+1; 2635a c 2636drop table t1; 2637create table t1(f1 int, f2 int); 2638create table t2(f3 int); 2639select f1 from t1,t2 where f1=f2 and (f1,f2) = ((1,1)); 2640f1 2641select f1 from t1,t2 where f1=f2 and (f1,NULL) = ((1,1)); 2642f1 2643select f1 from t1,t2 where f1=f2 and (f1,f2) = ((1,NULL)); 2644f1 2645insert into t1 values(1,1),(2,null); 2646insert into t2 values(2); 2647select * from t1,t2 where f1=f3 and (f1,f2) = (2,null); 2648f1 f2 f3 2649select * from t1,t2 where f1=f3 and (f1,f2) <=> (2,null); 2650f1 f2 f3 26512 NULL 2 2652drop table t1,t2; 2653create table t1 (f1 int not null auto_increment primary key, f2 varchar(10)); 2654create table t11 like t1; 2655insert into t1 values(1,""),(2,""); 2656show table status like 't1%'; 2657Name 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 Max_index_length Temporary 2658t1 MyISAM 10 Dynamic 2 20 X X X X X X X X latin1_swedish_ci NULL X N 2659t11 MyISAM 10 Dynamic 0 0 X X X X X X X X latin1_swedish_ci NULL X N 2660select 123 as a from t1 where f1 is null; 2661a 2662drop table t1,t11; 2663CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL, UNIQUE idx (a,b) ); 2664INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4); 2665CREATE TABLE t2 ( a INT NOT NULL, b INT NOT NULL, e INT ); 2666INSERT INTO t2 VALUES ( 1,10,1), (1,10,2), (1,11,1), (1,11,2), (1,2,1), (1,2,2),(1,2,3); 2667SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2 LEFT JOIN 2668t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY a, b, c; 2669a b c d 26701 2 1 1 26711 2 2 1 26721 2 3 1 26731 10 2 26741 11 2 2675SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2 LEFT JOIN 2676t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY t1.a, t1.b, c; 2677a b c d 26781 10 4 26791 2 1 1 26801 2 2 1 26811 2 3 1 2682SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2 LEFT JOIN 2683t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY t2.a, t2.b, c; 2684a b c d 26851 2 1 1 26861 2 2 1 26871 2 3 1 26881 10 2 26891 11 2 2690SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2,t1 2691WHERE t2.a = t1.a AND t2.b = t1.b GROUP BY a, b, c; 2692a b c d 26931 2 1 1 26941 2 2 1 26951 2 3 1 2696DROP TABLE IF EXISTS t1, t2; 2697create table t1 (f1 int primary key, f2 int); 2698create table t2 (f3 int, f4 int, primary key(f3,f4)); 2699insert into t1 values (1,1); 2700insert into t2 values (1,1),(1,2); 2701select distinct count(f2) >0 from t1 left join t2 on f1=f3 group by f1; 2702count(f2) >0 27031 2704drop table t1,t2; 2705create table t1 (f1 int,f2 int); 2706insert into t1 values(1,1); 2707create table t2 (f3 int, f4 int, primary key(f3,f4)); 2708insert into t2 values(1,1); 2709select * from t1 where f1 in (select f3 from t2 where (f3,f4)= (select f3,f4 from t2)); 2710f1 f2 27111 1 2712drop table t1,t2; 2713CREATE TABLE t1(a int, b int, c int, KEY b(b), KEY c(c)); 2714insert into t1 values (1,0,0),(2,0,0); 2715CREATE TABLE t2 (a int, b varchar(2), c varchar(2), PRIMARY KEY(a)); 2716insert into t2 values (1,'',''), (2,'',''); 2717CREATE TABLE t3 (a int, b int, PRIMARY KEY (a,b), KEY a (a), KEY b (b)); 2718insert into t3 values (1,1),(1,2); 2719explain select straight_join DISTINCT t2.a,t2.b, t1.c from t1, t3, t2 2720where (t1.c=t2.a or (t1.c=t3.a and t2.a=t3.b)) and t1.b=556476786 and 2721t2.b like '%%' order by t2.b limit 0,1; 2722id select_type table type possible_keys key key_len ref rows Extra 27231 SIMPLE t1 ref b,c b 5 const 1 Using temporary; Using filesort 27241 SIMPLE t3 index PRIMARY,a,b PRIMARY 8 NULL 2 Using index; Using join buffer (flat, BNL join) 27251 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1) 2726DROP TABLE t1,t2,t3; 2727CREATE TABLE t1 (a int, INDEX idx(a)); 2728INSERT INTO t1 VALUES (2), (3), (1); 2729EXPLAIN SELECT * FROM t1 IGNORE INDEX (idx); 2730id select_type table type possible_keys key key_len ref rows Extra 27311 SIMPLE t1 ALL NULL NULL NULL NULL 3 2732EXPLAIN SELECT * FROM t1 IGNORE INDEX (a); 2733ERROR 42000: Key 'a' doesn't exist in table 't1' 2734EXPLAIN SELECT * FROM t1 FORCE INDEX (a); 2735ERROR 42000: Key 'a' doesn't exist in table 't1' 2736DROP TABLE t1; 2737CREATE TABLE t1 (a int, b int); 2738INSERT INTO t1 VALUES (1,1), (2,1), (4,10); 2739CREATE TABLE t2 (a int PRIMARY KEY, b int, KEY b (b)); 2740INSERT INTO t2 VALUES (1,NULL), (2,10); 2741ALTER TABLE t1 ENABLE KEYS; 2742EXPLAIN SELECT STRAIGHT_JOIN SQL_NO_CACHE COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; 2743id select_type table type possible_keys key key_len ref rows Extra 27441 SIMPLE t2 index b b 5 NULL 2 Using index 27451 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) 2746SELECT STRAIGHT_JOIN SQL_NO_CACHE * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; 2747a b a b 27481 NULL 1 1 27491 NULL 2 1 27501 NULL 4 10 27512 10 4 10 2752EXPLAIN SELECT STRAIGHT_JOIN SQL_NO_CACHE COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; 2753id select_type table type possible_keys key key_len ref rows Extra 27541 SIMPLE t2 index b b 5 NULL 2 Using index 27551 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) 2756SELECT STRAIGHT_JOIN SQL_NO_CACHE * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; 2757a b a b 27581 NULL 1 1 27591 NULL 2 1 27601 NULL 4 10 27612 10 4 10 2762DROP TABLE IF EXISTS t1,t2; 2763CREATE TABLE t1 (key1 float default NULL, UNIQUE KEY key1 (key1)); 2764CREATE TABLE t2 (key2 float default NULL, UNIQUE KEY key2 (key2)); 2765INSERT INTO t1 VALUES (0.3762),(0.3845),(0.6158),(0.7941); 2766INSERT INTO t2 VALUES (1.3762),(1.3845),(1.6158),(1.7941); 2767explain select max(key1) from t1 where key1 <= 0.6158; 2768id select_type table type possible_keys key key_len ref rows Extra 27691 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2770explain select max(key2) from t2 where key2 <= 1.6158; 2771id select_type table type possible_keys key key_len ref rows Extra 27721 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2773explain select min(key1) from t1 where key1 >= 0.3762; 2774id select_type table type possible_keys key key_len ref rows Extra 27751 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2776explain select min(key2) from t2 where key2 >= 1.3762; 2777id select_type table type possible_keys key key_len ref rows Extra 27781 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2779explain select max(key1), min(key2) from t1, t2 2780where key1 <= 0.6158 and key2 >= 1.3762; 2781id select_type table type possible_keys key key_len ref rows Extra 27821 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2783explain select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5; 2784id select_type table type possible_keys key key_len ref rows Extra 27851 SIMPLE t1 range key1 key1 5 NULL 3 Using where; Using index 2786explain select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5; 2787id select_type table type possible_keys key key_len ref rows Extra 27881 SIMPLE t1 range key1 key1 5 NULL 3 Using where; Using index 2789select max(key1) from t1 where key1 <= 0.6158; 2790max(key1) 27910.6158 2792select max(key2) from t2 where key2 <= 1.6158; 2793max(key2) 27941.6158 2795select min(key1) from t1 where key1 >= 0.3762; 2796min(key1) 27970.3762 2798select min(key2) from t2 where key2 >= 1.3762; 2799min(key2) 28001.3762 2801select max(key1), min(key2) from t1, t2 2802where key1 <= 0.6158 and key2 >= 1.3762; 2803max(key1) min(key2) 28040.6158 1.3762 2805select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5; 2806max(key1) 28070.3845 2808select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5; 2809min(key1) 28100.3845 2811DROP TABLE t1,t2; 2812CREATE TABLE t1 (i BIGINT UNSIGNED NOT NULL); 2813INSERT INTO t1 VALUES (10); 2814SELECT i='1e+01',i=1e+01, i in (1e+01,1e+01), i in ('1e+01','1e+01') FROM t1; 2815i='1e+01' i=1e+01 i in (1e+01,1e+01) i in ('1e+01','1e+01') 28161 1 1 1 2817DROP TABLE t1; 2818create table t1(a bigint unsigned, b bigint); 2819insert ignore into t1 values (0xfffffffffffffffff, 0xfffffffffffffffff), 2820(0x10000000000000000, 0x10000000000000000), 2821(0x8fffffffffffffff, 0x8fffffffffffffff); 2822Warnings: 2823Warning 1264 Out of range value for column 'a' at row 1 2824Warning 1264 Out of range value for column 'b' at row 1 2825Warning 1264 Out of range value for column 'a' at row 2 2826Warning 1264 Out of range value for column 'b' at row 2 2827Warning 1264 Out of range value for column 'b' at row 3 2828select hex(a), hex(b) from t1; 2829hex(a) hex(b) 2830FFFFFFFFFFFFFFFF 7FFFFFFFFFFFFFFF 2831FFFFFFFFFFFFFFFF 7FFFFFFFFFFFFFFF 28328FFFFFFFFFFFFFFF 7FFFFFFFFFFFFFFF 2833drop table t1; 2834CREATE TABLE t1 (c0 int); 2835CREATE TABLE t2 (c0 int); 2836INSERT INTO t1 VALUES(@@connect_timeout); 2837INSERT INTO t2 VALUES(@@connect_timeout); 2838SELECT * FROM t1 JOIN t2 ON t1.c0 = t2.c0 WHERE (t1.c0 <=> @@connect_timeout); 2839c0 c0 2840X X 2841DROP TABLE t1, t2; 2842End of 4.1 tests 2843CREATE TABLE t1 ( 2844K2C4 varchar(4) character set latin1 collate latin1_bin NOT NULL default '', 2845K4N4 varchar(4) character set latin1 collate latin1_bin NOT NULL default '0000', 2846F2I4 int(11) NOT NULL default '0' 2847) ENGINE=MyISAM DEFAULT CHARSET=latin1; 2848INSERT INTO t1 VALUES 2849('W%RT', '0100', 1), 2850('W-RT', '0100', 1), 2851('WART', '0100', 1), 2852('WART', '0200', 1), 2853('WERT', '0100', 2), 2854('WORT','0200', 2), 2855('WT', '0100', 2), 2856('W_RT', '0100', 2), 2857('WaRT', '0100', 3), 2858('WART', '0300', 3), 2859('WRT' , '0400', 3), 2860('WURM', '0500', 3), 2861('W%T', '0600', 4), 2862('WA%T', '0700', 4), 2863('WA_T', '0800', 4); 2864SELECT K2C4, K4N4, F2I4 FROM t1 2865WHERE K2C4 = 'WART' AND 2866(F2I4 = 2 AND K2C4 = 'WART' OR (F2I4 = 2 OR K4N4 = '0200')); 2867K2C4 K4N4 F2I4 2868WART 0200 1 2869SELECT K2C4, K4N4, F2I4 FROM t1 2870WHERE K2C4 = 'WART' AND (K2C4 = 'WART' OR K4N4 = '0200'); 2871K2C4 K4N4 F2I4 2872WART 0100 1 2873WART 0200 1 2874WART 0300 3 2875DROP TABLE t1; 2876create table t1 (a int, b int); 2877create table t2 like t1; 2878select t1.a from (t1 inner join t2 on t1.a=t2.a) where t2.a=1; 2879a 2880select t1.a from ((t1 inner join t2 on t1.a=t2.a)) where t2.a=1; 2881a 2882select 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; 2883a a a 2884drop table t1,t2; 2885create table t1 (s1 varchar(5)); 2886insert into t1 values ('Wall'); 2887select min(s1) from t1 group by s1 with rollup; 2888min(s1) 2889Wall 2890Wall 2891drop table t1; 2892create table t1 (s1 int) engine=myisam; 2893insert into t1 values (0); 2894select avg(distinct s1) from t1 group by s1 with rollup; 2895avg(distinct s1) 28960.0000 28970.0000 2898drop table t1; 2899create table t1 (s1 int); 2900insert into t1 values (null),(1); 2901select distinct avg(s1) as x from t1 group by s1 with rollup; 2902x 2903NULL 29041.0000 2905drop table t1; 2906CREATE TABLE t1 (a int); 2907CREATE TABLE t2 (a int); 2908INSERT INTO t1 VALUES (1), (2), (3), (4), (5); 2909INSERT INTO t2 VALUES (2), (4), (6); 2910SELECT t1.a FROM t1 STRAIGHT_JOIN t2 ON t1.a=t2.a; 2911a 29122 29134 2914EXPLAIN SELECT t1.a FROM t1 STRAIGHT_JOIN t2 ON t1.a=t2.a; 2915id select_type table type possible_keys key key_len ref rows Extra 29161 SIMPLE t1 ALL NULL NULL NULL NULL 5 29171 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) 2918EXPLAIN SELECT t1.a FROM t1 INNER JOIN t2 ON t1.a=t2.a; 2919id select_type table type possible_keys key key_len ref rows Extra 29201 SIMPLE t2 ALL NULL NULL NULL NULL 3 29211 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) 2922DROP TABLE t1,t2; 2923select x'3136' + 0, X'3136' + 0, b'10' + 0, B'10' + 0; 2924x'3136' + 0 X'3136' + 0 b'10' + 0 B'10' + 0 292516 16 2 2 2926create table t1 (f1 varchar(6) default NULL, f2 int(6) primary key not null); 2927create table t2 (f3 varchar(5) not null, f4 varchar(5) not null, UNIQUE KEY UKEY (f3,f4)); 2928insert into t1 values (" 2", 2); 2929insert into t2 values (" 2", " one "),(" 2", " two "); 2930select * from t1 left join t2 on f1 = f3; 2931f1 f2 f3 f4 2932 2 2 2 one 2933 2 2 2 two 2934drop table t1,t2; 2935create table t1 (empnum smallint, grp int); 2936create table t2 (empnum int, name char(5)); 2937insert into t1 values(1,1); 2938insert into t2 values(1,'bob'); 2939create view v1 as select * from t2 inner join t1 using (empnum); 2940select * from v1; 2941empnum name grp 29421 bob 1 2943drop table t1,t2; 2944drop view v1; 2945create table t1 (pk int primary key, b int); 2946create table t2 (pk int primary key, c int); 2947select pk from t1 inner join t2 using (pk); 2948pk 2949drop table t1,t2; 2950create table t1 (s1 int, s2 char(5), s3 decimal(10)); 2951create view v1 as select s1, s2, 'x' as s3 from t1; 2952select * from t1 natural join v1; 2953s1 s2 s3 2954insert into t1 values (1,'x',5); 2955select * from t1 natural join v1; 2956s1 s2 s3 2957Warnings: 2958Warning 1292 Truncated incorrect DECIMAL value: 'x' 2959drop table t1; 2960drop view v1; 2961create table t1(a1 int); 2962create table t2(a2 int); 2963insert into t1 values(1),(2); 2964insert into t2 values(1),(2); 2965create view v2 (c) as select a1 from t1; 2966select * from t1 natural left join t2; 2967a1 a2 29681 1 29691 2 29702 1 29712 2 2972select * from t1 natural right join t2; 2973a2 a1 29741 1 29751 2 29762 1 29772 2 2978select * from v2 natural left join t2; 2979c a2 29801 1 29811 2 29822 1 29832 2 2984select * from v2 natural right join t2; 2985a2 c 29861 1 29871 2 29882 1 29892 2 2990drop table t1, t2; 2991drop view v2; 2992create table t1 (a int(10), t1_val int(10)); 2993create table t2 (b int(10), t2_val int(10)); 2994create table t3 (a int(10), b int(10)); 2995insert into t1 values (1,1),(2,2); 2996insert into t2 values (1,1),(2,2),(3,3); 2997insert into t3 values (1,1),(2,1),(3,1),(4,1); 2998select * from t1 natural join t2 natural join t3; 2999a b t1_val t2_val 30001 1 1 1 30012 1 2 1 3002select * from t1 natural join t3 natural join t2; 3003b a t1_val t2_val 30041 1 1 1 30051 2 2 1 3006drop table t1, t2, t3; 3007DO IFNULL(NULL, NULL); 3008SELECT CAST(IFNULL(NULL, NULL) AS DECIMAL); 3009CAST(IFNULL(NULL, NULL) AS DECIMAL) 3010NULL 3011SELECT ABS(IFNULL(NULL, NULL)); 3012ABS(IFNULL(NULL, NULL)) 3013NULL 3014SELECT IFNULL(NULL, NULL); 3015IFNULL(NULL, NULL) 3016NULL 3017SET @OLD_SQL_MODE12595=@@SQL_MODE, @@SQL_MODE=''; 3018SHOW LOCAL VARIABLES LIKE 'SQL_MODE'; 3019Variable_name Value 3020sql_mode 3021CREATE TABLE BUG_12595(a varchar(100)); 3022INSERT INTO BUG_12595 VALUES ('hakan%'), ('hakank'), ("ha%an"); 3023SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\%'; 3024a 3025hakan% 3026SELECT * FROM BUG_12595 WHERE a LIKE 'hakan*%' ESCAPE '*'; 3027a 3028hakan% 3029SELECT * FROM BUG_12595 WHERE a LIKE 'hakan**%' ESCAPE '**'; 3030ERROR HY000: Incorrect arguments to ESCAPE 3031SELECT * FROM BUG_12595 WHERE a LIKE 'hakan%' ESCAPE ''; 3032a 3033hakan% 3034hakank 3035SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\%' ESCAPE ''; 3036a 3037SELECT * FROM BUG_12595 WHERE a LIKE 'ha\%an' ESCAPE 0x5c; 3038a 3039ha%an 3040SELECT * FROM BUG_12595 WHERE a LIKE 'ha%%an' ESCAPE '%'; 3041a 3042ha%an 3043SELECT * FROM BUG_12595 WHERE a LIKE 'ha\%an' ESCAPE '\\'; 3044a 3045ha%an 3046SELECT * FROM BUG_12595 WHERE a LIKE 'ha|%an' ESCAPE '|'; 3047a 3048ha%an 3049SET @@SQL_MODE='NO_BACKSLASH_ESCAPES'; 3050SHOW LOCAL VARIABLES LIKE 'SQL_MODE'; 3051Variable_name Value 3052sql_mode NO_BACKSLASH_ESCAPES 3053SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\%'; 3054a 3055SELECT * FROM BUG_12595 WHERE a LIKE 'hakan*%' ESCAPE '*'; 3056a 3057hakan% 3058SELECT * FROM BUG_12595 WHERE a LIKE 'hakan**%' ESCAPE '**'; 3059ERROR HY000: Incorrect arguments to ESCAPE 3060SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\%' ESCAPE '\\'; 3061ERROR HY000: Incorrect arguments to ESCAPE 3062SELECT * FROM BUG_12595 WHERE a LIKE 'hakan%' ESCAPE ''; 3063ERROR HY000: Incorrect arguments to ESCAPE 3064SELECT * FROM BUG_12595 WHERE a LIKE 'ha\%an' ESCAPE 0x5c; 3065a 3066ha%an 3067SELECT * FROM BUG_12595 WHERE a LIKE 'ha|%an' ESCAPE '|'; 3068a 3069ha%an 3070SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\n%' ESCAPE '\n'; 3071ERROR HY000: Incorrect arguments to ESCAPE 3072SET @@SQL_MODE=@OLD_SQL_MODE12595; 3073DROP TABLE BUG_12595; 3074create table t1 (a char(1)); 3075create table t2 (a char(1)); 3076insert into t1 values ('a'),('b'),('c'); 3077insert into t2 values ('b'),('c'),('d'); 3078select a from t1 natural join t2; 3079a 3080b 3081c 3082select * from t1 natural join t2 where a = 'b'; 3083a 3084b 3085drop table t1, t2; 3086CREATE TABLE t1 (`id` TINYINT); 3087CREATE TABLE t2 (`id` TINYINT); 3088CREATE TABLE t3 (`id` TINYINT); 3089INSERT INTO t1 VALUES (1),(2),(3); 3090INSERT INTO t2 VALUES (2); 3091INSERT INTO t3 VALUES (3); 3092SELECT t1.id,t3.id FROM t1 JOIN t2 ON (t2.id=t1.id) LEFT JOIN t3 USING (id); 3093ERROR 23000: Column 'id' in from clause is ambiguous 3094SELECT t1.id,t3.id FROM t1 JOIN t2 ON (t2.notacolumn=t1.id) LEFT JOIN t3 USING (id); 3095ERROR 23000: Column 'id' in from clause is ambiguous 3096SELECT id,t3.id FROM t1 JOIN t2 ON (t2.id=t1.id) LEFT JOIN t3 USING (id); 3097ERROR 23000: Column 'id' in from clause is ambiguous 3098SELECT id,t3.id FROM (t1 JOIN t2 ON (t2.id=t1.id)) LEFT JOIN t3 USING (id); 3099ERROR 23000: Column 'id' in from clause is ambiguous 3100drop table t1, t2, t3; 3101create table t1 (a int(10),b int(10)); 3102create table t2 (a int(10),b int(10)); 3103insert into t1 values (1,10),(2,20),(3,30); 3104insert into t2 values (1,10); 3105select * from t1 inner join t2 using (A); 3106a b b 31071 10 10 3108select * from t1 inner join t2 using (a); 3109a b b 31101 10 10 3111drop table t1, t2; 3112create table t1 (a int, c int); 3113create table t2 (b int); 3114create table t3 (b int, a int); 3115create table t4 (c int); 3116insert into t1 values (1,1); 3117insert into t2 values (1); 3118insert into t3 values (1,1); 3119insert into t4 values (1); 3120select * from t1 join t2 join t3 on (t2.b = t3.b and t1.a = t3.a); 3121a c b b a 31221 1 1 1 1 3123select * from t1, t2 join t3 on (t2.b = t3.b and t1.a = t3.a); 3124ERROR 42S22: Unknown column 't1.a' in 'on clause' 3125select * from t1 join t2 join t3 join t4 on (t1.a = t4.c and t2.b = t4.c); 3126a c b b a c 31271 1 1 1 1 1 3128select * from t1 join t2 join t4 using (c); 3129c a b 31301 1 1 3131drop table t1, t2, t3, t4; 3132create table t1(x int, y int); 3133create table t2(x int, y int); 3134create table t3(x int, primary key(x)); 3135insert into t1 values (1, 1), (2, 1), (3, 1), (4, 3), (5, 6), (6, 6); 3136insert into t2 values (1, 1), (2, 1), (3, 3), (4, 6), (5, 6); 3137insert into t3 values (1), (2), (3), (4), (5); 3138select t1.x, t3.x from t1, t2, t3 where t1.x = t2.x and t3.x >= t1.y and t3.x <= t2.y; 3139x x 31401 1 31412 1 31423 1 31433 2 31443 3 31454 3 31464 4 31474 5 3148drop table t1,t2,t3; 3149create table t1 (id char(16) not null default '', primary key (id)); 3150insert into t1 values ('100'),('101'),('102'); 3151create table t2 (id char(16) default null); 3152insert into t2 values (1); 3153create view v1 as select t1.id from t1; 3154create view v2 as select t2.id from t2; 3155create view v3 as select (t1.id+2) as id from t1 natural left join t2; 3156select t1.id from t1 left join v2 using (id); 3157id 3158100 3159101 3160102 3161select t1.id from v2 right join t1 using (id); 3162id 3163100 3164101 3165102 3166select t1.id from t1 left join v3 using (id); 3167id 3168100 3169101 3170102 3171select * from t1 left join v2 using (id); 3172id 3173100 3174101 3175102 3176select * from v2 right join t1 using (id); 3177id 3178100 3179101 3180102 3181select * from t1 left join v3 using (id); 3182id 3183100 3184101 3185102 3186select v1.id from v1 left join v2 using (id); 3187id 3188100 3189101 3190102 3191select v1.id from v2 right join v1 using (id); 3192id 3193100 3194101 3195102 3196select v1.id from v1 left join v3 using (id); 3197id 3198100 3199101 3200102 3201select * from v1 left join v2 using (id); 3202id 3203100 3204101 3205102 3206select * from v2 right join v1 using (id); 3207id 3208100 3209101 3210102 3211select * from v1 left join v3 using (id); 3212id 3213100 3214101 3215102 3216drop table t1, t2; 3217drop view v1, v2, v3; 3218create table t1 (id int(11) not null default '0'); 3219insert into t1 values (123),(191),(192); 3220create table t2 (id char(16) character set utf8 not null); 3221insert into t2 values ('58013'),('58014'),('58015'),('58016'); 3222create table t3 (a_id int(11) not null, b_id char(16) character set utf8); 3223insert into t3 values (123,null),(123,null),(123,null),(123,null),(123,null),(123,'58013'); 3224select count(*) 3225from t1 inner join (t3 left join t2 on t2.id = t3.b_id) on t1.id = t3.a_id; 3226count(*) 32276 3228select count(*) 3229from t1 inner join (t2 right join t3 on t2.id = t3.b_id) on t1.id = t3.a_id; 3230count(*) 32316 3232drop table t1,t2,t3; 3233create table t1 (a int); 3234create table t2 (b int); 3235create table t3 (c int); 3236select * from t1 join t2 join t3 on (t1.a=t3.c); 3237a b c 3238select * from t1 join t2 left join t3 on (t1.a=t3.c); 3239a b c 3240select * from t1 join t2 right join t3 on (t1.a=t3.c); 3241a b c 3242select * from t1 join t2 straight_join t3 on (t1.a=t3.c); 3243a b c 3244drop table t1, t2 ,t3; 3245create table t1(f1 int, f2 date); 3246insert into t1 values(1,'2005-01-01'),(2,'2005-09-01'),(3,'2005-09-30'), 3247(4,'2005-10-01'),(5,'2005-12-30'); 3248select * from t1 where f2 >= 0 order by f2; 3249f1 f2 32501 2005-01-01 32512 2005-09-01 32523 2005-09-30 32534 2005-10-01 32545 2005-12-30 3255select * from t1 where f2 >= '0000-00-00' order by f2; 3256f1 f2 32571 2005-01-01 32582 2005-09-01 32593 2005-09-30 32604 2005-10-01 32615 2005-12-30 3262select * from t1 where f2 >= '2005-09-31' order by f2; 3263f1 f2 32644 2005-10-01 32655 2005-12-30 3266select * from t1 where f2 >= '2005-09-3a' order by f2; 3267f1 f2 32683 2005-09-30 32694 2005-10-01 32705 2005-12-30 3271Warnings: 3272Warning 1292 Truncated incorrect date value: '2005-09-3a' 3273select * from t1 where f2 <= '2005-09-31' order by f2; 3274f1 f2 32751 2005-01-01 32762 2005-09-01 32773 2005-09-30 3278select * from t1 where f2 <= '2005-09-3a' order by f2; 3279f1 f2 32801 2005-01-01 32812 2005-09-01 3282Warnings: 3283Warning 1292 Truncated incorrect date value: '2005-09-3a' 3284drop table t1; 3285create table t1 (f1 int, f2 int); 3286insert into t1 values (1, 30), (2, 20), (3, 10); 3287create algorithm=merge view v1 as select f1, f2 from t1; 3288create algorithm=merge view v2 (f2, f1) as select f1, f2 from t1; 3289create algorithm=merge view v3 as select t1.f1 as f2, t1.f2 as f1 from t1; 3290select t1.f1 as x1, f1 from t1 order by t1.f1; 3291x1 f1 32921 1 32932 2 32943 3 3295select v1.f1 as x1, f1 from v1 order by v1.f1; 3296x1 f1 32971 1 32982 2 32993 3 3300select v2.f1 as x1, f1 from v2 order by v2.f1; 3301x1 f1 330210 10 330320 20 330430 30 3305select v3.f1 as x1, f1 from v3 order by v3.f1; 3306x1 f1 330710 10 330820 20 330930 30 3310select f1, f2, v1.f1 as x1 from v1 order by v1.f1; 3311f1 f2 x1 33121 30 1 33132 20 2 33143 10 3 3315select f1, f2, v2.f1 as x1 from v2 order by v2.f1; 3316f1 f2 x1 331710 3 10 331820 2 20 331930 1 30 3320select f1, f2, v3.f1 as x1 from v3 order by v3.f1; 3321f1 f2 x1 332210 3 10 332320 2 20 332430 1 30 3325drop table t1; 3326drop view v1, v2, v3; 3327CREATE TABLE t1(key_a int4 NOT NULL, optimus varchar(32), PRIMARY KEY(key_a)); 3328CREATE TABLE t2(key_a int4 NOT NULL, prime varchar(32), PRIMARY KEY(key_a)); 3329CREATE table t3(key_a int4 NOT NULL, key_b int4 NOT NULL, foo varchar(32), 3330PRIMARY KEY(key_a,key_b)); 3331INSERT INTO t1 VALUES (0,''); 3332INSERT INTO t1 VALUES (1,'i'); 3333INSERT INTO t1 VALUES (2,'j'); 3334INSERT INTO t1 VALUES (3,'k'); 3335INSERT INTO t2 VALUES (1,'r'); 3336INSERT INTO t2 VALUES (2,'s'); 3337INSERT INTO t2 VALUES (3,'t'); 3338INSERT INTO t3 VALUES (1,5,'x'); 3339INSERT INTO t3 VALUES (1,6,'y'); 3340INSERT INTO t3 VALUES (2,5,'xx'); 3341INSERT INTO t3 VALUES (2,6,'yy'); 3342INSERT INTO t3 VALUES (2,7,'zz'); 3343INSERT INTO t3 VALUES (3,5,'xxx'); 3344SELECT t2.key_a,foo 3345FROM t1 INNER JOIN t2 ON t1.key_a = t2.key_a 3346INNER JOIN t3 ON t1.key_a = t3.key_a 3347WHERE t2.key_a=2 and key_b=5; 3348key_a foo 33492 xx 3350EXPLAIN SELECT t2.key_a,foo 3351FROM t1 INNER JOIN t2 ON t1.key_a = t2.key_a 3352INNER JOIN t3 ON t1.key_a = t3.key_a 3353WHERE t2.key_a=2 and key_b=5; 3354id select_type table type possible_keys key key_len ref rows Extra 33551 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index 33561 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 Using index 33571 SIMPLE t3 const PRIMARY PRIMARY 8 const,const 1 3358SELECT t2.key_a,foo 3359FROM t1 INNER JOIN t2 ON t2.key_a = t1.key_a 3360INNER JOIN t3 ON t1.key_a = t3.key_a 3361WHERE t2.key_a=2 and key_b=5; 3362key_a foo 33632 xx 3364EXPLAIN SELECT t2.key_a,foo 3365FROM t1 INNER JOIN t2 ON t2.key_a = t1.key_a 3366INNER JOIN t3 ON t1.key_a = t3.key_a 3367WHERE t2.key_a=2 and key_b=5; 3368id select_type table type possible_keys key key_len ref rows Extra 33691 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index 33701 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 Using index 33711 SIMPLE t3 const PRIMARY PRIMARY 8 const,const 1 3372DROP TABLE t1,t2,t3; 3373create table t1 (f1 int); 3374insert into t1 values(1),(2); 3375create table t2 (f2 int, f3 int, key(f2)); 3376insert into t2 values(1,1),(2,2); 3377create table t3 (f4 int not null); 3378insert into t3 values (2),(2),(2); 3379select f1,(select count(*) from t2,t3 where f2=f1 and f3=f4) as count from t1; 3380f1 count 33811 0 33822 3 3383drop table t1,t2,t3; 3384create table t1 (f1 int unique); 3385create table t2 (f2 int unique); 3386create table t3 (f3 int unique); 3387insert into t1 values(1),(2); 3388insert into t2 values(1),(2); 3389insert into t3 values(1),(NULL); 3390select * from t3 where f3 is null; 3391f3 3392NULL 3393select t2.f2 from t1 left join t2 on f1=f2 join t3 on f1=f3 where f1=1; 3394f2 33951 3396drop table t1,t2,t3; 3397create table t1(f1 char, f2 char not null); 3398insert into t1 values(null,'a'); 3399create table t2 (f2 char not null); 3400insert into t2 values('b'); 3401select * from t1 left join t2 on f1=t2.f2 where t1.f2='a'; 3402f1 f2 f2 3403NULL a NULL 3404drop table t1,t2; 3405select * from (select * left join t on f1=f2) tt; 3406ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'left join t on f1=f2) tt' at line 1 3407CREATE TABLE t1 (sku int PRIMARY KEY, pr int); 3408CREATE TABLE t2 (sku int PRIMARY KEY, sppr int, name varchar(255)); 3409INSERT INTO t1 VALUES 3410(10, 10), (20, 10), (30, 20), (40, 30), (50, 10), (60, 10), (70, 10); 3411INSERT INTO t2 VALUES 3412(10, 10, 'aaa'), (20, 10, 'bbb'), (30, 10, 'ccc'), (40, 20, 'ddd'), 3413(50, 10, 'eee'), (60, 20, 'fff'), (70, 20, 'ggg'), (80, 30, 'hhh'); 3414SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr 3415FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku); 3416sku sppr name sku pr 341720 10 bbb 10 10 341820 10 bbb 20 10 3419EXPLAIN 3420SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr 3421FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku); 3422id select_type table type possible_keys key key_len ref rows Extra 34231 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 34241 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition 3425DROP TABLE t1,t2; 3426SET SQL_MODE='NO_UNSIGNED_SUBTRACTION'; 3427CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL); 3428INSERT t1 SET i = 0; 3429UPDATE t1 SET i = -1; 3430Warnings: 3431Warning 1264 Out of range value for column 'i' at row 1 3432SELECT * FROM t1; 3433i 34340 3435UPDATE t1 SET i = CAST(i - 1 AS SIGNED); 3436Warnings: 3437Warning 1264 Out of range value for column 'i' at row 1 3438SELECT * FROM t1; 3439i 34400 3441UPDATE t1 SET i = i - 1; 3442Warnings: 3443Warning 1264 Out of range value for column 'i' at row 1 3444SELECT * FROM t1; 3445i 34460 3447DROP TABLE t1; 3448SET SQL_MODE=default; 3449create table t1 (a int); 3450insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 3451create table t2 (a int, b int, c int, e int, primary key(a,b,c)); 3452insert into t2 select A.a, B.a, C.a, C.a from t1 A, t1 B, t1 C; 3453analyze table t2; 3454Table Op Msg_type Msg_text 3455test.t2 analyze status Engine-independent statistics collected 3456test.t2 analyze status OK 3457select 'In next EXPLAIN, B.rows must be exactly 10:' Z; 3458Z 3459In next EXPLAIN, B.rows must be exactly 10: 3460explain select * from t2 A, t2 B where A.a=5 and A.b=5 and A.C<5 3461and B.a=5 and B.b=A.e and (B.b =1 or B.b = 3 or B.b=5); 3462id select_type table type possible_keys key key_len ref rows Extra 34631 SIMPLE A range PRIMARY PRIMARY 12 NULL 4 Using index condition; Using where 34641 SIMPLE B ref PRIMARY PRIMARY 8 const,test.A.e 10 3465drop table t1, t2; 3466CREATE TABLE t1 (a int PRIMARY KEY, b int, INDEX(b)); 3467INSERT INTO t1 VALUES (1, 3), (9,4), (7,5), (4,5), (6,2), 3468(3,1), (5,1), (8,9), (2,2), (0,9); 3469CREATE TABLE t2 (c int, d int, f int, INDEX(c,f)); 3470INSERT INTO t2 VALUES 3471(1,0,0), (1,0,1), (2,0,0), (2,0,1), (3,0,0), (4,0,1), 3472(5,0,0), (5,0,1), (6,0,0), (0,0,1), (7,0,0), (7,0,1), 3473(0,0,0), (0,0,1), (8,0,0), (8,0,1), (9,0,0), (9,0,1); 3474EXPLAIN 3475SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6; 3476id select_type table type possible_keys key key_len ref rows Extra 34771 SIMPLE t2 ALL c NULL NULL NULL 18 Using where 34781 SIMPLE t1 eq_ref|filter PRIMARY,b PRIMARY|b 4|5 test.t2.c 1 (30%) Using where; Using rowid filter 3479EXPLAIN 3480SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6 AND a > 0; 3481id select_type table type possible_keys key key_len ref rows Extra 34821 SIMPLE t2 ALL c NULL NULL NULL 18 Using where 34831 SIMPLE t1 eq_ref|filter PRIMARY,b PRIMARY|b 4|5 test.t2.c 1 (30%) Using where; Using rowid filter 3484DROP TABLE t1, t2; 3485create table t1 ( 3486a int unsigned not null auto_increment primary key, 3487b bit not null, 3488c bit not null 3489); 3490create table t2 ( 3491a int unsigned not null auto_increment primary key, 3492b bit not null, 3493c int unsigned not null, 3494d varchar(50) 3495); 3496insert into t1 (b,c) values (0,1), (0,1); 3497insert into t2 (b,c) values (0,1); 3498select t1.a, t1.b + 0, t1.c + 0, t2.a, t2.b + 0, t2.c, t2.d 3499from t1 left outer join t2 on t1.a = t2.c and t2.b <> 1 3500where t1.b <> 1 order by t1.a; 3501a t1.b + 0 t1.c + 0 a t2.b + 0 c d 35021 0 1 1 0 1 NULL 35032 0 1 NULL NULL NULL NULL 3504drop table t1,t2; 3505SELECT 0.9888889889 * 1.011111411911; 35060.9888889889 * 1.011111411911 35070.9998769417899202067879 3508prepare stmt from 'select 1 as " a "'; 3509Warnings: 3510Warning 1466 Leading spaces are removed from name ' a ' 3511execute stmt; 3512a 35131 3514CREATE TABLE t1 (a int NOT NULL PRIMARY KEY, b int NOT NULL); 3515INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4); 3516CREATE TABLE t2 (c int NOT NULL, INDEX idx(c)); 3517INSERT INTO t2 VALUES 3518(1), (1), (1), (1), (1), (1), (1), (1), 3519(2), (2), (2), (2), 3520(3), (3), 3521(4); 3522EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=1; 3523id select_type table type possible_keys key key_len ref rows Extra 35241 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 35251 SIMPLE t2 ref idx idx 4 const 8 Using index 3526EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=4; 3527id select_type table type possible_keys key key_len ref rows Extra 35281 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 35291 SIMPLE t2 ref idx idx 4 const 1 Using index 3530DROP TABLE t1, t2; 3531CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, a int); 3532INSERT INTO t1 VALUES (1,2), (2,NULL), (3,2); 3533CREATE TABLE t2 (b int, c INT, INDEX idx1(b)); 3534INSERT INTO t2 VALUES (2,1), (3,2); 3535CREATE TABLE t3 (d int, e int, INDEX idx1(d)); 3536INSERT INTO t3 VALUES (2,10), (2,20), (1,30), (2,40), (2,50), (1,60), (3,70), (1,80), (3,90); 3537EXPLAIN 3538SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id 3539WHERE t1.id=2; 3540id select_type table type possible_keys key key_len ref rows Extra 35411 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 35421 SIMPLE t2 const idx1 NULL NULL NULL 1 35431 SIMPLE t3 ref idx1 idx1 5 const 4 3544SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id 3545WHERE t1.id=2; 3546id a b c d e 35472 NULL NULL NULL 2 10 35482 NULL NULL NULL 2 20 35492 NULL NULL NULL 2 40 35502 NULL NULL NULL 2 50 3551DROP TABLE t1,t2,t3; 3552create table t1 (c1 varchar(1), c2 int, c3 int, c4 int, c5 int, c6 int, 3553c7 int, c8 int, c9 int, fulltext key (`c1`)); 3554select distinct match (`c1`) against ('z') , c2, c3, c4,c5, c6,c7, c8 3555from t1 where c9=1 order by c2, c2; 3556match (`c1`) against ('z') c2 c3 c4 c5 c6 c7 c8 3557drop table t1; 3558CREATE TABLE t1 (pk varchar(10) PRIMARY KEY, fk varchar(16)); 3559CREATE TABLE t2 (pk varchar(16) PRIMARY KEY, fk varchar(10)); 3560INSERT INTO t1 VALUES 3561('d','dddd'), ('i','iii'), ('a','aa'), ('b','bb'), ('g','gg'), 3562('e','eee'), ('c','cccc'), ('h','hhh'), ('j','jjj'), ('f','fff'); 3563INSERT INTO t2 VALUES 3564('jjj', 'j'), ('cc','c'), ('ccc','c'), ('aaa', 'a'), ('jjjj','j'), 3565('hhh','h'), ('gg','g'), ('fff','f'), ('ee','e'), ('ffff','f'), 3566('bbb','b'), ('ff','f'), ('cccc','c'), ('dddd','d'), ('jj','j'), 3567('aaaa','a'), ('bb','b'), ('eeee','e'), ('aa','a'), ('hh','h'); 3568EXPLAIN SELECT t2.* 3569FROM t1 JOIN t2 ON t2.fk=t1.pk 3570WHERE t2.fk < 'c' AND t2.pk=t1.fk; 3571id select_type table type possible_keys key key_len ref rows Extra 35721 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using where 35731 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where 3574EXPLAIN SELECT t2.* 3575FROM t1 JOIN t2 ON t2.fk=t1.pk 3576WHERE t2.fk BETWEEN 'a' AND 'b' AND t2.pk=t1.fk; 3577id select_type table type possible_keys key key_len ref rows Extra 35781 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using where 35791 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where 3580EXPLAIN SELECT t2.* 3581FROM t1 JOIN t2 ON t2.fk=t1.pk 3582WHERE t2.fk IN ('a','b') AND t2.pk=t1.fk; 3583id select_type table type possible_keys key key_len ref rows Extra 35841 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using where 35851 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where 3586DROP TABLE t1,t2; 3587CREATE TABLE t1 (a int, b varchar(20) NOT NULL, PRIMARY KEY(a)); 3588CREATE TABLE t2 (a int, b varchar(20) NOT NULL, 3589PRIMARY KEY (a), UNIQUE KEY (b)); 3590INSERT INTO t1 VALUES (1,'a'),(2,'b'),(3,'c'); 3591INSERT INTO t2 VALUES (1,'a'),(2,'b'),(3,'c'); 3592EXPLAIN SELECT t1.a FROM t1 LEFT JOIN t2 ON t2.b=t1.b WHERE t1.a=3; 3593id select_type table type possible_keys key key_len ref rows Extra 35941 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 3595DROP TABLE t1,t2; 3596CREATE TABLE t1(id int PRIMARY KEY, b int, e int); 3597CREATE TABLE t2(i int, a int, INDEX si(i), INDEX ai(a)); 3598CREATE TABLE t3(a int PRIMARY KEY, c char(4), INDEX ci(c)); 3599INSERT INTO t1 VALUES 3600(1,10,19), (2,20,22), (4,41,42), (9,93,95), (7, 77,79), 3601(6,63,67), (5,55,58), (3,38,39), (8,81,89); 3602INSERT INTO t2 VALUES 3603(21,210), (41,410), (82,820), (83,830), (84,840), 3604(65,650), (51,510), (37,370), (94,940), (76,760), 3605(22,220), (33,330), (40,400), (95,950), (38,380), 3606(67,670), (88,880), (57,570), (96,960), (97,970); 3607INSERT INTO t3 VALUES 3608(210,'bb'), (950,'ii'), (400,'ab'), (500,'ee'), (220,'gg'), 3609(440,'gg'), (310,'eg'), (380,'ee'), (840,'bb'), (830,'ff'), 3610(230,'aa'), (960,'ii'), (410,'aa'), (510,'ee'), (290,'bb'), 3611(450,'gg'), (320,'dd'), (390,'hh'), (850,'jj'), (860,'ff'); 3612EXPLAIN 3613SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3 3614WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND 3615t3.a=t2.a AND t3.c IN ('bb','ee'); 3616id select_type table type possible_keys key key_len ref rows Extra 36171 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 36181 SIMPLE t2 range si si 5 NULL 4 Using index condition; Using where 36191 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (30%) Using where; Using rowid filter 3620EXPLAIN 3621SELECT t3.a FROM t1,t2,t3 3622WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND 3623t3.a=t2.a AND t3.c IN ('bb','ee') ; 3624id select_type table type possible_keys key key_len ref rows Extra 36251 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 36261 SIMPLE t2 range si,ai si 5 NULL 4 Using index condition; Using where 36271 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (30%) Using where; Using rowid filter 3628EXPLAIN 3629SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3 3630WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND 3631t3.c IN ('bb','ee'); 3632id select_type table type possible_keys key key_len ref rows Extra 36331 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 36341 SIMPLE t2 range si si 5 NULL 2 Using index condition; Using where 36351 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (30%) Using where; Using rowid filter 3636EXPLAIN 3637SELECT t3.a FROM t1,t2,t3 3638WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND 3639t3.c IN ('bb','ee'); 3640id select_type table type possible_keys key key_len ref rows Extra 36411 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 36421 SIMPLE t2 range si,ai si 5 NULL 2 Using index condition; Using where 36431 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (30%) Using where; Using rowid filter 3644DROP TABLE t1,t2,t3; 3645CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int); 3646CREATE TABLE t2 ( f11 int PRIMARY KEY ); 3647INSERT INTO t1 VALUES (1,1,1,0,0,0,0),(2,1,1,3,8,1,0),(3,1,1,4,12,1,0); 3648INSERT INTO t2 VALUES (62); 3649SELECT * FROM t1 LEFT JOIN t2 ON f11 = t1.checked_out GROUP BY f1 ORDER BY f2, f3, f4, f5 LIMIT 0, 1; 3650f1 f2 f3 f4 f5 f6 checked_out f11 36511 1 1 0 0 0 0 NULL 3652DROP TABLE t1, t2; 3653DROP TABLE IF EXISTS t1; 3654CREATE TABLE t1(a int); 3655INSERT into t1 values (1), (2), (3); 3656SELECT * FROM t1 LIMIT 2, -1; 3657ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-1' at line 1 3658DROP TABLE t1; 3659CREATE TABLE t1 ( 3660ID_with_null int NULL, 3661ID_better int NOT NULL, 3662INDEX idx1 (ID_with_null), 3663INDEX idx2 (ID_better) 3664); 3665INSERT INTO t1 VALUES (1,1), (2,1), (null,3), (null,3), (null,3), (null,3); 3666INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; 3667INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; 3668INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; 3669INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; 3670INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; 3671SELECT COUNT(*) FROM t1 WHERE ID_with_null IS NULL; 3672COUNT(*) 3673128 3674SELECT COUNT(*) FROM t1 WHERE ID_better=1; 3675COUNT(*) 36762 3677EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL; 3678id select_type table type possible_keys key key_len ref rows Extra 36791 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where 3680DROP INDEX idx1 ON t1; 3681CREATE UNIQUE INDEX idx1 ON t1(ID_with_null); 3682EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL; 3683id select_type table type possible_keys key key_len ref rows Extra 36841 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where 3685DROP TABLE t1; 3686CREATE TABLE t1 ( 3687ID1_with_null int NULL, 3688ID2_with_null int NULL, 3689ID_better int NOT NULL, 3690INDEX idx1 (ID1_with_null, ID2_with_null), 3691INDEX idx2 (ID_better) 3692); 3693INSERT INTO t1 VALUES (1,1,1), (2,2,1), (3,null,3), (null,3,3), (null,null,3), 3694(3,null,3), (null,3,3), (null,null,3), (3,null,3), (null,3,3), (null,null,3); 3695INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; 3696INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; 3697INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; 3698INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; 3699INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; 3700INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; 3701ANALYZE TABLE t1; 3702Table Op Msg_type Msg_text 3703test.t1 analyze status Engine-independent statistics collected 3704test.t1 analyze status OK 3705SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null=3; 3706COUNT(*) 370724 3708SELECT COUNT(*) FROM t1 WHERE ID1_with_null=3 AND ID2_with_null IS NULL; 3709COUNT(*) 371024 3711SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null IS NULL; 3712COUNT(*) 3713192 3714SELECT COUNT(*) FROM t1 WHERE ID_better=1; 3715COUNT(*) 37162 3717EXPLAIN SELECT * FROM t1 3718WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ; 3719id select_type table type possible_keys key key_len ref rows Extra 37201 SIMPLE t1 ref|filter idx1,idx2 idx2|idx1 4|10 const 2 (6%) Using where; Using rowid filter 3721EXPLAIN SELECT * FROM t1 3722WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null=3 IS NULL ; 3723id select_type table type possible_keys key key_len ref rows Extra 37241 SIMPLE t1 ref idx1,idx2 idx2 4 const 2 Using where 3725EXPLAIN SELECT * FROM t1 3726WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL; 3727id select_type table type possible_keys key key_len ref rows Extra 37281 SIMPLE t1 ref idx1,idx2 idx2 4 const 2 Using where 3729DROP INDEX idx1 ON t1; 3730CREATE UNIQUE INDEX idx1 ON t1(ID1_with_null,ID2_with_null); 3731EXPLAIN SELECT * FROM t1 3732WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ; 3733id select_type table type possible_keys key key_len ref rows Extra 37341 SIMPLE t1 ref|filter idx1,idx2 idx2|idx1 4|10 const 2 (7%) Using where; Using rowid filter 3735EXPLAIN SELECT * FROM t1 3736WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null IS NULL ; 3737id select_type table type possible_keys key key_len ref rows Extra 37381 SIMPLE t1 ref idx1,idx2 idx2 4 const 2 Using where 3739EXPLAIN SELECT * FROM t1 3740WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL; 3741id select_type table type possible_keys key key_len ref rows Extra 37421 SIMPLE t1 ref idx1,idx2 idx2 4 const 2 Using where 3743EXPLAIN SELECT * FROM t1 3744WHERE ID_better=1 AND ID1_with_null IS NULL AND 3745(ID2_with_null=1 OR ID2_with_null=2); 3746id select_type table type possible_keys key key_len ref rows Extra 37471 SIMPLE t1 ref|filter idx1,idx2 idx1|idx2 5|4 const 2 (1%) Using index condition; Using where; Using rowid filter 3748DROP TABLE t1; 3749CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts)); 3750INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00"); 3751ANALYZE TABLE t1; 3752Table Op Msg_type Msg_text 3753test.t1 analyze status Engine-independent statistics collected 3754test.t1 analyze status OK 3755CREATE TABLE t2 (a INT, dt1 DATETIME, dt2 DATETIME, PRIMARY KEY (a)); 3756INSERT INTO t2 VALUES (30, "2006-01-01 00:00:00", "2999-12-31 00:00:00"); 3757INSERT INTO t2 SELECT a+1,dt1,dt2 FROM t2; 3758ANALYZE TABLE t2; 3759Table Op Msg_type Msg_text 3760test.t2 analyze status Engine-independent statistics collected 3761test.t2 analyze status OK 3762EXPLAIN 3763SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30 3764AND t1.ts BETWEEN t2.dt1 AND t2.dt2 3765AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31"; 3766id select_type table type possible_keys key key_len ref rows Extra 37671 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 37681 SIMPLE t1 range ts ts 4 NULL 2 Using index condition; Using where 3769SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30 3770AND t1.ts BETWEEN t2.dt1 AND t2.dt2 3771AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31"; 3772a ts a dt1 dt2 377330 2006-01-03 23:00:00 30 2006-01-01 00:00:00 2999-12-31 00:00:00 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, 3794 `c` decimal(19,0) NOT NULL, 3795 `co` decimal(19,0) NOT NULL 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); 3810INSERT INTO t2 VALUES ('bb ',8), ('aa',9), ('aa',10), ('bb',11); 3811SELECT * FROM t2; 3812name n 3813bb 1 3814aa 2 3815cc 3 3816cc 4 3817cc 5 3818bb 6 3819cc 7 3820bb 8 3821aa 9 3822aa 10 3823bb 11 3824SELECT * FROM t2 ORDER BY name; 3825name n 3826aa 2 3827aa 10 3828aa 9 3829bb 1 3830bb 8 3831bb 6 3832bb 11 3833cc 4 3834cc 7 3835cc 5 3836cc 3 3837SELECT name, LENGTH(name), n FROM t2 ORDER BY name; 3838name LENGTH(name) n 3839aa 2 2 3840aa 2 10 3841aa 2 9 3842bb 2 1 3843bb 3 8 3844bb 3 6 3845bb 2 11 3846cc 4 4 3847cc 3 7 3848cc 2 5 3849cc 5 3 3850EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; 3851id select_type table type possible_keys key key_len ref rows Extra 38521 SIMPLE t2 ref name name 6 const 4 Using where 3853SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; 3854name LENGTH(name) n 3855cc 5 3 3856cc 2 5 3857cc 3 7 3858EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; 3859id select_type table type possible_keys key key_len ref rows Extra 38601 SIMPLE t2 range name name 6 NULL 4 Using where 3861SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; 3862name LENGTH(name) n 3863cc 5 3 3864cc 4 4 3865cc 2 5 3866cc 3 7 3867EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; 3868id select_type table type possible_keys key key_len ref rows Extra 38691 SIMPLE t2 range name name 6 NULL 4 Using where; Using filesort 3870SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; 3871name LENGTH(name) n 3872cc 4 4 3873cc 5 3 3874cc 2 5 3875cc 3 7 3876EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; 3877id select_type table type possible_keys key key_len ref rows Extra 38781 SIMPLE t1 ALL NULL NULL NULL NULL 5 38791 SIMPLE t2 ref name name 6 test.t1.name 2 Using where 3880SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; 3881name name n 3882ccc NULL NULL 3883bb bb 1 3884bb bb 6 3885bb bb 8 3886bb bb 11 3887cc cc 3 3888cc cc 5 3889cc cc 7 3890aa aa 2 3891aa aa 9 3892aa aa 10 3893aa aa 2 3894aa aa 9 3895aa aa 10 3896DROP TABLE t1,t2; 3897CREATE TABLE t1 (name text); 3898CREATE TABLE t2 (name text, n int, KEY (name(3))); 3899INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa '), ('aa'); 3900INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc ',3); 3901INSERT INTO t2 VALUES (concat('cc ', 0x06), 4); 3902INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7); 3903INSERT INTO t2 VALUES ('bb ',8), ('aa',9), ('aa',10), ('bb',11); 3904SELECT * FROM t2; 3905name n 3906bb 1 3907aa 2 3908cc 3 3909cc 4 3910cc 5 3911bb 6 3912cc 7 3913bb 8 3914aa 9 3915aa 10 3916bb 11 3917SELECT * FROM t2 ORDER BY name; 3918name n 3919aa 2 3920aa 9 3921aa 10 3922bb 1 3923bb 6 3924bb 8 3925bb 11 3926cc 4 3927cc 3 3928cc 5 3929cc 7 3930SELECT name, LENGTH(name), n FROM t2 ORDER BY name; 3931name LENGTH(name) n 3932aa 2 2 3933aa 2 9 3934aa 2 10 3935bb 2 1 3936bb 3 6 3937bb 3 8 3938bb 2 11 3939cc 4 4 3940cc 5 3 3941cc 2 5 3942cc 3 7 3943EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; 3944id select_type table type possible_keys key key_len ref rows Extra 39451 SIMPLE t2 ref name name 6 const 4 Using where 3946SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; 3947name LENGTH(name) n 3948cc 5 3 3949cc 2 5 3950cc 3 7 3951EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; 3952id select_type table type possible_keys key key_len ref rows Extra 39531 SIMPLE t2 range name name 6 NULL 4 Using where 3954SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; 3955name LENGTH(name) n 3956cc 5 3 3957cc 4 4 3958cc 2 5 3959cc 3 7 3960EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; 3961id select_type table type possible_keys key key_len ref rows Extra 39621 SIMPLE t2 range name name 6 NULL 4 Using where; Using filesort 3963SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; 3964name LENGTH(name) n 3965cc 4 4 3966cc 5 3 3967cc 2 5 3968cc 3 7 3969EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; 3970id select_type table type possible_keys key key_len ref rows Extra 39711 SIMPLE t1 ALL NULL NULL NULL NULL 5 39721 SIMPLE t2 ref name name 6 test.t1.name 2 Using where 3973SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; 3974name name n 3975ccc NULL NULL 3976bb bb 1 3977bb bb 6 3978bb bb 8 3979bb bb 11 3980cc cc 3 3981cc cc 5 3982cc cc 7 3983aa aa 2 3984aa aa 9 3985aa aa 10 3986aa aa 2 3987aa aa 9 3988aa aa 10 3989DROP TABLE t1,t2; 3990CREATE TABLE t1 ( 3991access_id int NOT NULL default '0', 3992name varchar(20) default NULL, 3993rank int NOT NULL default '0', 3994KEY idx (access_id) 3995); 3996CREATE TABLE t2 ( 3997faq_group_id int NOT NULL default '0', 3998faq_id int NOT NULL default '0', 3999access_id int default NULL, 4000UNIQUE KEY idx1 (faq_id), 4001KEY idx2 (faq_group_id,faq_id) 4002); 4003INSERT INTO t1 VALUES 4004(1,'Everyone',2),(2,'Help',3),(3,'Technical Support',1),(4,'Chat User',4); 4005INSERT INTO t2 VALUES 4006(261,265,1),(490,494,1); 4007SELECT t2.faq_id 4008FROM t1 INNER JOIN t2 IGNORE INDEX (idx1) 4009ON (t1.access_id = t2.access_id) 4010LEFT JOIN t2 t 4011ON (t.faq_group_id = t2.faq_group_id AND 4012find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4')) 4013WHERE 4014t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265); 4015faq_id 4016265 4017SELECT t2.faq_id 4018FROM t1 INNER JOIN t2 4019ON (t1.access_id = t2.access_id) 4020LEFT JOIN t2 t 4021ON (t.faq_group_id = t2.faq_group_id AND 4022find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4')) 4023WHERE 4024t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265); 4025faq_id 4026265 4027DROP TABLE t1,t2; 4028CREATE TABLE t1 (a INT, b INT, KEY inx (b,a)); 4029INSERT INTO t1 VALUES (1,1), (1,2), (1,3), (1,4), (1,5), (1, 6), (1,7); 4030EXPLAIN SELECT COUNT(*) FROM t1 f1 INNER JOIN t1 f2 4031ON ( f1.b=f2.b AND f1.a<f2.a ) 4032WHERE 1 AND f1.b NOT IN (100,2232,3343,51111); 4033id select_type table type possible_keys key key_len ref rows Extra 40341 SIMPLE f1 range inx inx 5 NULL 7 Using where; Using index 40351 SIMPLE f2 ref inx inx 5 test.f1.b 1 Using where; Using index 4036DROP TABLE t1; 4037CREATE TABLE t1 (c1 INT, c2 INT); 4038INSERT INTO t1 VALUES (1,11), (2,22), (2,22); 4039EXPLAIN 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 COUNT(c2)))))))))))))))))))))))))))))))) > 0; 4040id select_type table type possible_keys key key_len ref rows Extra 40411 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where 404231 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 404332 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 4044EXPLAIN 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; 4045ERROR HY000: Too high level of nesting for select 4046DROP TABLE t1; 4047CREATE TABLE t1 ( 4048c1 int(11) NOT NULL AUTO_INCREMENT, 4049c2 varchar(1000) DEFAULT NULL, 4050c3 bigint(20) DEFAULT NULL, 4051c4 bigint(20) DEFAULT NULL, 4052PRIMARY KEY (c1) 4053); 4054EXPLAIN EXTENDED 4055SELECT join_2.c1 4056FROM 4057t1 AS join_0, 4058t1 AS join_1, 4059t1 AS join_2, 4060t1 AS join_3, 4061t1 AS join_4, 4062t1 AS join_5, 4063t1 AS join_6, 4064t1 AS join_7 4065WHERE 4066join_0.c1=join_1.c1 AND 4067join_1.c1=join_2.c1 AND 4068join_2.c1=join_3.c1 AND 4069join_3.c1=join_4.c1 AND 4070join_4.c1=join_5.c1 AND 4071join_5.c1=join_6.c1 AND 4072join_6.c1=join_7.c1 4073OR 4074join_0.c2 < '?' AND 4075join_1.c2 < '?' AND 4076join_2.c2 > '?' AND 4077join_2.c2 < '!' AND 4078join_3.c2 > '?' AND 4079join_4.c2 = '?' AND 4080join_5.c2 <> '?' AND 4081join_6.c2 <> '?' AND 4082join_7.c2 >= '?' AND 4083join_0.c1=join_1.c1 AND 4084join_1.c1=join_2.c1 AND 4085join_2.c1=join_3.c1 AND 4086join_3.c1=join_4.c1 AND 4087join_4.c1=join_5.c1 AND 4088join_5.c1=join_6.c1 AND 4089join_6.c1=join_7.c1 4090GROUP BY 4091join_3.c1, 4092join_2.c1, 4093join_7.c1, 4094join_1.c1, 4095join_0.c1; 4096id select_type table type possible_keys key key_len ref rows filtered Extra 40971 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 4098Warnings: 4099Note 1003 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 4100SHOW WARNINGS; 4101Level Code Message 4102Note 1003 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 4103DROP TABLE t1; 4104SELECT 1 AS ` `; 4105 41061 4107Warnings: 4108Warning 1474 Name ' ' has become '' 4109SELECT 1 AS ` `; 4110 41111 4112Warnings: 4113Warning 1474 Name ' ' has become '' 4114SELECT 1 AS ` x`; 4115x 41161 4117Warnings: 4118Warning 1466 Leading spaces are removed from name ' x' 4119CREATE VIEW v1 AS SELECT 1 AS ``; 4120ERROR 42000: Incorrect column name '' 4121CREATE VIEW v1 AS SELECT 1 AS ` `; 4122ERROR 42000: Incorrect column name ' ' 4123CREATE VIEW v1 AS SELECT 1 AS ` `; 4124ERROR 42000: Incorrect column name ' ' 4125CREATE VIEW v1 AS SELECT (SELECT 1 AS ` `); 4126ERROR 42000: Incorrect column name ' ' 4127CREATE VIEW v1 AS SELECT 1 AS ` x`; 4128Warnings: 4129Warning 1466 Leading spaces are removed from name ' x' 4130SELECT `x` FROM v1; 4131x 41321 4133ALTER VIEW v1 AS SELECT 1 AS ` `; 4134ERROR 42000: Incorrect column name ' ' 4135DROP VIEW v1; 4136select str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT' 4137 and '2007/10/20 00:00:00 GMT'; 4138str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT' 4139 and '2007/10/20 00:00:00 GMT' 41401 4141Warnings: 4142Warning 1292 Truncated incorrect datetime value: '2007/10/01 00:00:00 GMT' 4143Warning 1292 Truncated incorrect datetime value: '2007/10/20 00:00:00 GMT' 4144select str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6'; 4145str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6' 41461 4147Warnings: 4148Warning 1292 Truncated incorrect datetime value: '2007/10/01 00:00:00 GMT-6' 4149select str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/20 00:00:00 GMT-6'; 4150str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/20 00:00:00 GMT-6' 41511 4152Warnings: 4153Warning 1292 Truncated incorrect datetime value: '2007/10/20 00:00:00 GMT-6' 4154select str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6'; 4155str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6' 41560 4157Warnings: 4158Warning 1292 Truncated incorrect datetime value: '2007/10/2000:00:00 GMT-6' 4159select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6'; 4160str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6' 41611 4162Warnings: 4163Warning 1292 Truncated incorrect datetime value: '2007-10-1 00:00:00 GMT-6' 4164select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6'; 4165str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6' 41661 4167Warnings: 4168Warning 1292 Truncated incorrect date value: '2007-10-01 x00:00:00 GMT-6' 4169select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6'; 4170str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6' 41711 4172Warnings: 4173Warning 1292 Truncated incorrect datetime value: '2007-10-01 00:00:00 GMT-6' 4174select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6'; 4175str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6' 41761 4177Warnings: 4178Warning 1292 Truncated incorrect datetime value: '2007-10-01 00:x00:00 GMT-6' 4179select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6'; 4180str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6' 41811 4182Warnings: 4183Warning 1292 Truncated incorrect date value: '2007-10-01 x12:34:56 GMT-6' 4184select str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6'; 4185str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6' 41861 4187Warnings: 4188Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34x:56 GMT-6' 4189select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6'; 4190str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6' 41910 4192Warnings: 4193Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34x:56 GMT-6' 4194select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34:56'; 4195str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34:56' 41961 4197select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 12:00:00'; 4198str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 12:00:00' 41990 4200select str_to_date('2007-10-01 12','%Y-%m-%d %H') = '2007-10-01 12:00:00'; 4201str_to_date('2007-10-01 12','%Y-%m-%d %H') = '2007-10-01 12:00:00' 42021 4203select str_to_date('2007-10-01 12:34','%Y-%m-%d %H') = '2007-10-01 12:00:00'; 4204str_to_date('2007-10-01 12:34','%Y-%m-%d %H') = '2007-10-01 12:00:00' 42051 4206Warnings: 4207Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34' 4208select str_to_date('2007-02-30 12:34','%Y-%m-%d %H:%i') = '2007-02-30 12:34'; 4209str_to_date('2007-02-30 12:34','%Y-%m-%d %H:%i') = '2007-02-30 12:34' 42101 4211select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'; 4212str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34' 42131 4214select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00' 4215 and '2007/10/20 00:00:00'; 4216str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00' 4217 and '2007/10/20 00:00:00' 42181 4219set SQL_MODE=TRADITIONAL; 4220select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'; 4221str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34' 4222NULL 4223Warnings: 4224Warning 1411 Incorrect datetime value: '2007-10-00 12:34' for function str_to_date 4225select str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'; 4226str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34' 42270 4228select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34'; 4229str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34' 4230NULL 4231Warnings: 4232Warning 1411 Incorrect datetime value: '2007-10-00 12:34' for function str_to_date 4233select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01' 4234 and '2007/10/20'; 4235str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01' 4236 and '2007/10/20' 4237NULL 4238Warnings: 4239Warning 1411 Incorrect datetime value: '2007-10-00' for function str_to_date 4240set SQL_MODE=DEFAULT; 4241select str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20'; 4242str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20' 42431 4244Warnings: 4245Warning 1292 Truncated incorrect datetime value: '' 4246select str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20'; 4247str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20' 42480 4249select str_to_date('','%Y-%m-%d %H:%i') = '2007-10-01 12:34'; 4250str_to_date('','%Y-%m-%d %H:%i') = '2007-10-01 12:34' 42510 4252select str_to_date(NULL,'%Y-%m-%d %H:%i') = '2007-10-01 12:34'; 4253str_to_date(NULL,'%Y-%m-%d %H:%i') = '2007-10-01 12:34' 4254NULL 4255select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = ''; 4256str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '' 42570 4258Warnings: 4259Warning 1292 Truncated incorrect datetime value: '' 4260select str_to_date('1','%Y-%m-%d') = '1'; 4261str_to_date('1','%Y-%m-%d') = '1' 42620 4263Warnings: 4264Warning 1292 Truncated incorrect datetime value: '1' 4265select str_to_date('1','%Y-%m-%d') = '1'; 4266str_to_date('1','%Y-%m-%d') = '1' 42670 4268Warnings: 4269Warning 1292 Truncated incorrect datetime value: '1' 4270select str_to_date('','%Y-%m-%d') = ''; 4271str_to_date('','%Y-%m-%d') = '' 42721 4273Warnings: 4274Warning 1292 Truncated incorrect datetime value: '' 4275select str_to_date('2000-01-01','%Y-%m-%d') between '1000-01-01' and '2001-01-01'; 4276str_to_date('2000-01-01','%Y-%m-%d') between '1000-01-01' and '2001-01-01' 42771 4278select str_to_date('2000-01-01','%Y-%m-%d') between '1000-01-01' and NULL; 4279str_to_date('2000-01-01','%Y-%m-%d') between '1000-01-01' and NULL 4280NULL 4281select str_to_date('2000-01-01','%Y-%m-%d') between NULL and '2001-01-01'; 4282str_to_date('2000-01-01','%Y-%m-%d') between NULL and '2001-01-01' 4283NULL 4284select str_to_date('2000-01-01','%Y-%m-%d') between '2001-01-01' and NULL; 4285str_to_date('2000-01-01','%Y-%m-%d') between '2001-01-01' and NULL 42860 4287select str_to_date('2000-01-01','%Y-%m-%d') between NULL and '1000-01-01'; 4288str_to_date('2000-01-01','%Y-%m-%d') between NULL and '1000-01-01' 42890 4290select str_to_date('2000-01-01','%Y-%m-%d') between NULL and NULL; 4291str_to_date('2000-01-01','%Y-%m-%d') between NULL and NULL 4292NULL 4293CREATE TABLE t1 (c11 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY); 4294CREATE TABLE t2 (c21 INT UNSIGNED NOT NULL, 4295c22 INT DEFAULT NULL, 4296KEY(c21, c22)); 4297CREATE TABLE t3 (c31 INT UNSIGNED NOT NULL DEFAULT 0, 4298c32 INT DEFAULT NULL, 4299c33 INT NOT NULL, 4300c34 INT UNSIGNED DEFAULT 0, 4301KEY (c33, c34, c32)); 4302INSERT INTO t1 values (),(),(),(),(); 4303INSERT INTO t2 SELECT a.c11, b.c11 FROM t1 a, t1 b; 4304INSERT INTO t3 VALUES (1, 1, 1, 0), 4305(2, 2, 0, 0), 4306(3, 3, 1, 0), 4307(4, 4, 0, 0), 4308(5, 5, 1, 0); 4309SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND 4310t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND 4311t3.c33 = 1 AND t2.c22 in (1, 3) 4312ORDER BY c32; 4313c32 43141 43151 43163 43173 43185 43195 4320SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND 4321t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND 4322t3.c33 = 1 AND t2.c22 in (1, 3) 4323ORDER BY c32 DESC; 4324c32 43255 43265 43273 43283 43291 43301 4331DROP TABLE t1, t2, t3; 4332 4333# 4334# Bug#30736: Row Size Too Large Error Creating a Table and 4335# Inserting Data. 4336# 4337DROP TABLE IF EXISTS t1; 4338DROP TABLE IF EXISTS t2; 4339 4340CREATE TABLE t1( 4341c1 DECIMAL(10, 2), 4342c2 FLOAT); 4343 4344INSERT INTO t1 VALUES (0, 1), (2, 3), (4, 5); 4345 4346CREATE TABLE t2( 4347c3 DECIMAL(10, 2)) 4348SELECT 4349c1 * c2 AS c3 4350FROM t1; 4351 4352SELECT * FROM t1; 4353c1 c2 43540.00 1 43552.00 3 43564.00 5 4357 4358SELECT * FROM t2; 4359c3 43600.00 43616.00 436220.00 4363 4364DROP TABLE t1; 4365DROP TABLE t2; 4366 4367CREATE TABLE t1 (c1 BIGINT NOT NULL); 4368INSERT INTO t1 (c1) VALUES (1); 4369SELECT * FROM t1 WHERE c1 > NULL + 1; 4370c1 4371DROP TABLE t1; 4372 4373CREATE TABLE t1 (a VARCHAR(10) NOT NULL PRIMARY KEY); 4374INSERT INTO t1 (a) VALUES ('foo0'), ('bar0'), ('baz0'); 4375SELECT * FROM t1 WHERE a IN (CONCAT('foo', 0), 'bar'); 4376a 4377foo0 4378DROP TABLE t1; 4379CREATE TABLE t1 (a INT, b INT); 4380CREATE TABLE t2 (a INT, c INT, KEY(a)); 4381INSERT INTO t1 VALUES (1, 1), (2, 2); 4382INSERT INTO t2 VALUES (1, 1), (1, 2), (1, 3), (1, 4), (1, 5), 4383(2, 1), (2, 2), (2, 3), (2, 4), (2, 5), 4384(3, 1), (3, 2), (3, 3), (3, 4), (3, 5), 4385(4, 1), (4, 2), (4, 3), (4, 4), (4, 5); 4386FLUSH STATUS; 4387SELECT DISTINCT b FROM t1 LEFT JOIN t2 USING(a) WHERE c <= 3; 4388b 43891 43902 4391SHOW STATUS LIKE 'Handler_read%'; 4392Variable_name Value 4393Handler_read_first 0 4394Handler_read_key 2 4395Handler_read_last 0 4396Handler_read_next 0 4397Handler_read_prev 0 4398Handler_read_retry 0 4399Handler_read_rnd 0 4400Handler_read_rnd_deleted 0 4401Handler_read_rnd_next 6 4402DROP TABLE t1, t2; 4403CREATE TABLE t1 (f1 bigint(20) NOT NULL default '0', 4404f2 int(11) NOT NULL default '0', 4405f3 bigint(20) NOT NULL default '0', 4406f4 varchar(255) NOT NULL default '', 4407PRIMARY KEY (f1), 4408KEY key1 (f4), 4409KEY key2 (f2)); 4410CREATE TABLE t2 (f1 int(11) NOT NULL default '0', 4411f2 enum('A1','A2','A3') NOT NULL default 'A1', 4412f3 int(11) NOT NULL default '0', 4413PRIMARY KEY (f1), 4414KEY key1 (f3)); 4415CREATE TABLE t3 (f1 bigint(20) NOT NULL default '0', 4416f2 datetime NOT NULL default '1980-01-01 00:00:00', 4417PRIMARY KEY (f1)); 4418insert into t1 values (1, 1, 1, 'abc'); 4419insert into t1 values (2, 1, 2, 'def'); 4420insert into t1 values (3, 1, 2, 'def'); 4421insert into t2 values (1, 'A1', 1); 4422insert into t3 values (1, '1980-01-01'); 4423SELECT a.f3, cr.f4, count(*) count 4424FROM t2 a 4425STRAIGHT_JOIN t1 cr ON cr.f2 = a.f1 4426LEFT JOIN 4427(t1 cr2 4428JOIN t3 ae2 ON cr2.f3 = ae2.f1 4429) ON a.f1 = cr2.f2 AND ae2.f2 < now() - INTERVAL 7 DAY AND 4430cr.f4 = cr2.f4 4431GROUP BY a.f3, cr.f4; 4432f3 f4 count 44331 abc 1 44341 def 2 4435drop table t1, t2, t3; 4436CREATE TABLE t1 (a INT KEY, b INT); 4437INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4); 4438EXPLAIN EXTENDED SELECT a, b FROM t1 WHERE a > 1 AND a = b LIMIT 2; 4439id select_type table type possible_keys key key_len ref rows filtered Extra 44401 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using index condition; Using where 4441Warnings: 4442Note 1003 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 4443EXPLAIN EXTENDED SELECT a, b FROM t1 WHERE a > 1 AND b = a LIMIT 2; 4444id select_type table type possible_keys key key_len ref rows filtered Extra 44451 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using index condition; Using where 4446Warnings: 4447Note 1003 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 4448DROP TABLE t1; 4449# 4450# Bug#47019: Assertion failed: 0, file .\rt_mbr.c, line 138 when 4451# forcing a spatial index 4452# 4453CREATE TABLE t1(a LINESTRING NOT NULL, SPATIAL KEY(a)); 4454INSERT INTO t1 VALUES 4455(GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1)')), 4456(GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1)')); 4457EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2; 4458id select_type table type possible_keys key key_len ref rows Extra 44591 SIMPLE t1 ALL NULL NULL NULL NULL 2 44601 SIMPLE t2 ALL a NULL NULL NULL 2 Range checked for each record (index map: 0x1) 4461SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2; 44621 44631 44641 44651 44661 4467EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a); 4468id select_type table type possible_keys key key_len ref rows Extra 44691 SIMPLE t1 ALL NULL NULL NULL NULL 2 44701 SIMPLE t2 ALL a NULL NULL NULL 2 Range checked for each record (index map: 0x1) 4471SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a); 44721 44731 44741 44751 44761 4477DROP TABLE t1; 4478# 4479# Bug #48291 : crash with row() operator,select into @var, and 4480# subquery returning multiple rows 4481# 4482CREATE TABLE t1(a INT); 4483INSERT INTO t1 VALUES (2),(3); 4484# Should not crash 4485SELECT 1 FROM t1 WHERE a <> 1 AND NOT 4486ROW(1,a) <=> ROW(1,(SELECT 1 FROM t1)) 4487INTO @var0; 4488ERROR 21000: Subquery returns more than 1 row 4489DROP TABLE t1; 4490# 4491# Bug #48458: simple query tries to allocate enormous amount of 4492# memory 4493# 4494CREATE TABLE t1(a INT NOT NULL, b YEAR); 4495INSERT IGNORE INTO t1 VALUES (); 4496Warnings: 4497Warning 1364 Field 'a' doesn't have a default value 4498CREATE TABLE t2(c INT); 4499# Should not err out because of out-of-memory 4500SELECT 1 FROM t2 JOIN t1 ON 1=1 4501WHERE a != '1' AND NOT a >= b OR NOT ROW(b,a )<> ROW(a,a); 45021 4503DROP TABLE t1,t2; 4504# 4505# Bug #49199: Optimizer handles incorrectly: 4506# field='const1' AND field='const2' in some cases 4507 4508CREATE TABLE t1(a DATETIME NOT NULL); 4509INSERT INTO t1 VALUES('2001-01-01'); 4510SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; 4511a 45122001-01-01 00:00:00 4513EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; 4514id select_type table type possible_keys key key_len ref rows filtered Extra 45151 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 4516Warnings: 4517Note 1003 select '2001-01-01 00:00:00' AS `a` from dual where 1 4518DROP TABLE t1; 4519CREATE TABLE t1(a DATE NOT NULL); 4520INSERT INTO t1 VALUES('2001-01-01'); 4521SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; 4522a 45232001-01-01 4524EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; 4525id select_type table type possible_keys key key_len ref rows filtered Extra 45261 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 4527Warnings: 4528Note 1003 select '2001-01-01' AS `a` from dual where 1 4529DROP TABLE t1; 4530CREATE TABLE t1(a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); 4531INSERT INTO t1 VALUES('2001-01-01'); 4532SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; 4533a 45342001-01-01 00:00:00 4535EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; 4536id select_type table type possible_keys key key_len ref rows filtered Extra 45371 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 4538Warnings: 4539Note 1003 select '2001-01-01 00:00:00' AS `a` from dual where 1 4540DROP TABLE t1; 4541CREATE TABLE t1(a DATETIME NOT NULL, b DATE NOT NULL); 4542INSERT INTO t1 VALUES('2001-01-01', '2001-01-01'); 4543SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00'; 4544a b 45452001-01-01 00:00:00 2001-01-01 4546EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00'; 4547id select_type table type possible_keys key key_len ref rows filtered Extra 45481 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 4549Warnings: 4550Note 1003 select '2001-01-01 00:00:00' AS `a`,'2001-01-01' AS `b` from dual where 1 4551DROP TABLE t1; 4552CREATE TABLE t1(a DATETIME NOT NULL, b VARCHAR(20) NOT NULL); 4553INSERT INTO t1 VALUES('2001-01-01', '2001-01-01'); 4554SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00'; 4555a b 4556EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00'; 4557id select_type table type possible_keys key key_len ref rows filtered Extra 45581 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 4559Warnings: 4560Note 1003 select '2001-01-01 00:00:00' AS `a`,'2001-01-01' AS `b` from dual where 0 4561SELECT * FROM t1 WHERE a='2001-01-01 00:00:00' AND a=b AND b='2001-01-01'; 4562a b 45632001-01-01 00:00:00 2001-01-01 4564EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01 00:00:00' AND a=b AND b='2001-01-01'; 4565id select_type table type possible_keys key key_len ref rows filtered Extra 45661 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 4567Warnings: 4568Note 1003 select '2001-01-01 00:00:00' AS `a`,'2001-01-01' AS `b` from dual where 1 4569DROP TABLE t1; 4570CREATE TABLE t1(a DATETIME NOT NULL, b DATE NOT NULL); 4571INSERT INTO t1 VALUES('2001-01-01', '2001-01-01'); 4572SELECT x.a, y.a, z.a FROM t1 x 4573JOIN t1 y ON x.a=y.a 4574JOIN t1 z ON y.a=z.a 4575WHERE x.a='2001-01-01' AND z.a='2001-01-01 00:00:00'; 4576a a a 45772001-01-01 00:00:00 2001-01-01 00:00:00 2001-01-01 00:00:00 4578EXPLAIN EXTENDED SELECT x.a, y.a, z.a FROM t1 x 4579JOIN t1 y ON x.a=y.a 4580JOIN t1 z ON y.a=z.a 4581WHERE x.a='2001-01-01' AND z.a='2001-01-01 00:00:00'; 4582id select_type table type possible_keys key key_len ref rows filtered Extra 45831 SIMPLE x system NULL NULL NULL NULL 1 100.00 45841 SIMPLE y system NULL NULL NULL NULL 1 100.00 45851 SIMPLE z system NULL NULL NULL NULL 1 100.00 4586Warnings: 4587Note 1003 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 4588DROP TABLE t1; 4589# 4590# Bug #49897: crash in ptr_compare when char(0) NOT NULL 4591# column is used for ORDER BY 4592# 4593SET @old_sort_buffer_size= @@session.sort_buffer_size; 4594SET @@sort_buffer_size= 40000; 4595CREATE TABLE t1(a CHAR(0) NOT NULL); 4596INSERT IGNORE INTO t1 VALUES (0), (0), (0); 4597INSERT INTO t1 SELECT t11.a FROM t1 t11, t1 t12; 4598INSERT INTO t1 SELECT t11.a FROM t1 t11, t1 t12; 4599INSERT INTO t1 SELECT t11.a FROM t1 t11, t1 t12; 4600EXPLAIN SELECT a FROM t1 ORDER BY a; 4601id select_type table type possible_keys key key_len ref rows Extra 46021 SIMPLE t1 ALL NULL NULL NULL NULL 24492 4603SELECT a FROM t1 ORDER BY a; 4604DROP TABLE t1; 4605CREATE TABLE t1(a CHAR(0) NOT NULL, b CHAR(0) NOT NULL, c int); 4606INSERT IGNORE INTO t1 VALUES (0, 0, 0), (0, 0, 2), (0, 0, 1); 4607INSERT INTO t1 SELECT t11.a, t11.b, t11.c FROM t1 t11, t1 t12; 4608INSERT INTO t1 SELECT t11.a, t11.b, t11.c FROM t1 t11, t1 t12; 4609INSERT INTO t1 SELECT t11.a, t11.b, t11.c FROM t1 t11, t1 t12; 4610EXPLAIN SELECT a FROM t1 ORDER BY a LIMIT 5; 4611id select_type table type possible_keys key key_len ref rows Extra 46121 SIMPLE t1 ALL NULL NULL NULL NULL 24492 4613SELECT a FROM t1 ORDER BY a LIMIT 5; 4614a 4615 4616 4617 4618 4619 4620EXPLAIN SELECT * FROM t1 ORDER BY a, b LIMIT 5; 4621id select_type table type possible_keys key key_len ref rows Extra 46221 SIMPLE t1 ALL NULL NULL NULL NULL 24492 4623SELECT * FROM t1 ORDER BY a, b LIMIT 5; 4624a b c 4625 0 4626 2 4627 1 4628 0 4629 2 4630EXPLAIN SELECT * FROM t1 ORDER BY a, b, c LIMIT 5; 4631id select_type table type possible_keys key key_len ref rows Extra 46321 SIMPLE t1 ALL NULL NULL NULL NULL 24492 Using filesort 4633SELECT * FROM t1 ORDER BY a, b, c LIMIT 5; 4634a b c 4635 0 4636 0 4637 0 4638 0 4639 0 4640EXPLAIN SELECT * FROM t1 ORDER BY c, a LIMIT 5; 4641id select_type table type possible_keys key key_len ref rows Extra 46421 SIMPLE t1 ALL NULL NULL NULL NULL 24492 Using filesort 4643SELECT * FROM t1 ORDER BY c, a LIMIT 5; 4644a b c 4645 0 4646 0 4647 0 4648 0 4649 0 4650SET @@sort_buffer_size= @old_sort_buffer_size; 4651DROP TABLE t1; 4652End of 5.0 tests 4653create table t1(a INT, KEY (a)); 4654INSERT INTO t1 VALUES (1),(2),(3),(4),(5); 4655SELECT a FROM t1 ORDER BY a LIMIT 2; 4656a 46571 46582 4659SELECT a FROM t1 ORDER BY a LIMIT 2,4294967296; 4660a 46613 46624 46635 4664SELECT a FROM t1 ORDER BY a LIMIT 2,4294967297; 4665a 46663 46674 46685 4669DROP TABLE t1; 4670CREATE TABLE t1 (date_key date); 4671CREATE TABLE t2 ( 4672pk int, 4673int_nokey int, 4674int_key int, 4675date_key date NOT NULL, 4676date_nokey date, 4677varchar_key varchar(1) 4678); 4679INSERT INTO t2 VALUES 4680(1,1,1,'0000-00-00',NULL,NULL), 4681(1,1,1,'0000-00-00',NULL,NULL); 4682SELECT 1 FROM t2 WHERE pk > ANY (SELECT 1 FROM t2); 46831 4684SELECT COUNT(DISTINCT 1) FROM t2 4685WHERE date_key = (SELECT 1 FROM t1 WHERE t2.date_key IS NULL) GROUP BY pk; 4686COUNT(DISTINCT 1) 4687SELECT date_nokey FROM t2 4688WHERE int_key IN (SELECT 1 FROM t1) 4689HAVING date_nokey = '10:41:7' 4690ORDER BY date_key; 4691date_nokey 4692Warnings: 4693Warning 1292 Truncated incorrect datetime value: '10:41:7' 4694DROP TABLE t1,t2; 4695CREATE TABLE t1 (a INT NOT NULL, b INT); 4696INSERT INTO t1 VALUES (1, 1); 4697EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND a=a) OR b > 2; 4698id select_type table type possible_keys key key_len ref rows filtered Extra 46991 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 4700Warnings: 4701Note 1003 select 1 AS `a`,1 AS `b` from dual where 1 4702SELECT * FROM t1 WHERE (a=a AND a=a) OR b > 2; 4703a b 47041 1 4705DROP TABLE t1; 4706CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, c INT NOT NULL); 4707EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND b=b AND c=c) OR b > 20; 4708id select_type table type possible_keys key key_len ref rows filtered Extra 47091 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 Const row not found 4710Warnings: 4711Note 1003 select NULL AS `a`,NULL AS `b`,NULL AS `c` from `test`.`t1` where 1 4712EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND a=a AND b=b) OR b > 20; 4713id select_type table type possible_keys key key_len ref rows filtered Extra 47141 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 Const row not found 4715Warnings: 4716Note 1003 select NULL AS `a`,NULL AS `b`,NULL AS `c` from `test`.`t1` where 1 4717EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND b=b AND a=a) OR b > 20; 4718id select_type table type possible_keys key key_len ref rows filtered Extra 47191 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 Const row not found 4720Warnings: 4721Note 1003 select NULL AS `a`,NULL AS `b`,NULL AS `c` from `test`.`t1` where 1 4722DROP TABLE t1; 4723# 4724# Bug#45266: Uninitialized variable lead to an empty result. 4725# 4726drop table if exists A,AA,B,BB; 4727CREATE TABLE `A` ( 4728`pk` int(11) NOT NULL AUTO_INCREMENT, 4729`date_key` date NOT NULL, 4730`date_nokey` date NOT NULL, 4731`datetime_key` datetime NOT NULL, 4732`int_nokey` int(11) NOT NULL, 4733`time_key` time NOT NULL, 4734`time_nokey` time NOT NULL, 4735PRIMARY KEY (`pk`), 4736KEY `date_key` (`date_key`), 4737KEY `time_key` (`time_key`), 4738KEY `datetime_key` (`datetime_key`) 4739); 4740CREATE TABLE `AA` ( 4741`pk` int(11) NOT NULL AUTO_INCREMENT, 4742`int_nokey` int(11) NOT NULL, 4743`time_key` time NOT NULL, 4744KEY `time_key` (`time_key`), 4745PRIMARY KEY (`pk`) 4746); 4747CREATE TABLE `B` ( 4748`date_nokey` date NOT NULL, 4749`date_key` date NOT NULL, 4750`time_key` time NOT NULL, 4751`datetime_nokey` datetime NOT NULL, 4752`varchar_key` varchar(1) NOT NULL, 4753KEY `date_key` (`date_key`), 4754KEY `time_key` (`time_key`), 4755KEY `varchar_key` (`varchar_key`) 4756); 4757INSERT 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'); 4758CREATE TABLE `BB` ( 4759`pk` int(11) NOT NULL AUTO_INCREMENT, 4760`int_nokey` int(11) NOT NULL, 4761`date_key` date NOT NULL, 4762`varchar_nokey` varchar(1) NOT NULL, 4763`date_nokey` date NOT NULL, 4764PRIMARY KEY (`pk`), 4765KEY `date_key` (`date_key`) 4766); 4767INSERT INTO `BB` VALUES (10,8,'0000-00-00','i','0000-00-00'),(11,0,'2005-08-18','','2005-08-18'); 4768SELECT table1 . `pk` AS field1 4769FROM 4770(BB AS table1 INNER JOIN 4771(AA AS table2 STRAIGHT_JOIN A AS table3 4772ON ( table3 . `date_key` = table2 . `pk` )) 4773ON ( table3 . `datetime_key` = table2 . `int_nokey` )) 4774WHERE ( table3 . `date_key` <= 4 AND table2 . `pk` = table1 . `varchar_nokey`) 4775GROUP BY field1 ; 4776field1 4777SELECT table3 .`date_key` field1 4778FROM 4779B table1 LEFT JOIN B table3 JOIN 4780(BB table6 JOIN A table7 ON table6 .`varchar_nokey`) 4781ON table6 .`int_nokey` ON table6 .`date_key` 4782 WHERE NOT ( table1 .`varchar_key` AND table7 .`pk`) GROUP BY field1; 4783field1 4784NULL 4785SELECT table4 . `time_nokey` AS field1 FROM 4786(AA AS table1 CROSS JOIN 4787(AA AS table2 STRAIGHT_JOIN 4788(B AS table3 STRAIGHT_JOIN A AS table4 4789ON ( table4 . `date_key` = table3 . `time_key` )) 4790ON ( table4 . `pk` = table3 . `date_nokey` )) 4791ON ( table4 . `time_key` = table3 . `datetime_nokey` )) 4792WHERE ( table4 . `time_key` < table1 . `time_key` AND 4793table1 . `int_nokey` != 'f') 4794GROUP BY field1 ORDER BY field1 , field1; 4795field1 4796SELECT table1 .`time_key` field2 FROM B table1 LEFT JOIN BB JOIN A table5 ON table5 .`date_nokey` ON table5 .`int_nokey` GROUP BY field2; 4797field2 479800:05:48 479915:13:38 4800drop table A,AA,B,BB; 4801#end of test for bug#45266 4802# 4803# Bug#33546: Slowdown on re-evaluation of constant expressions. 4804# 4805CREATE TABLE t1 (a INT); 4806INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); 4807CREATE TABLE t2 (b INT); 4808INSERT INTO t2 VALUES (2); 4809SELECT * FROM t1 WHERE a = 1 + 1; 4810a 48112 4812EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = 1 + 1; 4813id select_type table type possible_keys key key_len ref rows filtered Extra 48141 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where 4815Warnings: 4816Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = <cache>(1 + 1) 4817SELECT * FROM t1 HAVING a = 1 + 1; 4818a 48192 4820EXPLAIN EXTENDED SELECT * FROM t1 HAVING a = 1 + 1; 4821id select_type table type possible_keys key key_len ref rows filtered Extra 48221 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 4823Warnings: 4824Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` having `test`.`t1`.`a` = <cache>(1 + 1) 4825SELECT * FROM t1, t2 WHERE a = b + (1 + 1); 4826a b 48274 2 4828EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE a = b + (1 + 1); 4829id select_type table type possible_keys key key_len ref rows filtered Extra 48301 SIMPLE t2 system NULL NULL NULL NULL 1 100.00 48311 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where 4832Warnings: 4833Note 1003 select `test`.`t1`.`a` AS `a`,2 AS `b` from `test`.`t1` where `test`.`t1`.`a` = <cache>(2 + (1 + 1)) 4834SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1; 4835b a 48362 3 4837EXPLAIN EXTENDED SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1; 4838id select_type table type possible_keys key key_len ref rows filtered Extra 48391 SIMPLE t2 system NULL NULL NULL NULL 1 100.00 48401 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where 4841Warnings: 4842Note 1003 select 2 AS `b`,`test`.`t1`.`a` AS `a` from `test`.`t1` where 1 4843EXPLAIN EXTENDED SELECT * FROM t1 WHERE a > UNIX_TIMESTAMP('2009-03-10 00:00:00'); 4844id select_type table type possible_keys key key_len ref rows filtered Extra 48451 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where 4846Warnings: 4847Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > <cache>(unix_timestamp('2009-03-10 00:00:00')) 4848CREATE FUNCTION f1() RETURNS INT DETERMINISTIC 4849BEGIN 4850SET @cnt := @cnt + 1; 4851RETURN 1; 4852END;| 4853SET @cnt := 0; 4854SELECT * FROM t1 WHERE a = f1(); 4855a 48561 4857SELECT @cnt; 4858@cnt 48591 4860EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = f1(); 4861id select_type table type possible_keys key key_len ref rows filtered Extra 48621 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where 4863Warnings: 4864Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = <cache>(`f1`()) 4865DROP TABLE t1, t2; 4866DROP FUNCTION f1; 4867# End of bug#33546 4868# 4869# BUG#48052: Valgrind warning - uninitialized value in init_read_record() 4870# 4871CREATE TABLE t1 ( 4872pk int(11) NOT NULL, 4873i int(11) DEFAULT NULL, 4874v varchar(1) DEFAULT NULL, 4875PRIMARY KEY (pk) 4876); 4877INSERT INTO t1 VALUES (2,7,'m'); 4878INSERT INTO t1 VALUES (3,9,'m'); 4879SELECT v 4880FROM t1 4881WHERE NOT pk > 0 4882HAVING v <= 't' 4883ORDER BY pk; 4884v 4885DROP TABLE t1; 4886# 4887# Bug#49489 Uninitialized cache led to a wrong result. 4888# 4889CREATE TABLE t1(c1 DOUBLE(5,4)); 4890INSERT INTO t1 VALUES (9.1234); 4891SELECT * FROM t1 WHERE c1 < 9.12345; 4892c1 48939.1234 4894DROP TABLE t1; 4895# End of test for bug#49489. 4896# 4897# Bug #49517: Inconsistent behavior while using 4898# NULLable BIGINT and INT columns in comparison 4899# 4900CREATE TABLE t1(a BIGINT UNSIGNED NOT NULL, b BIGINT NULL, c INT NULL); 4901INSERT INTO t1 VALUES(105, NULL, NULL); 4902SELECT * FROM t1 WHERE b < 102; 4903a b c 4904SELECT * FROM t1 WHERE c < 102; 4905a b c 4906SELECT * FROM t1 WHERE 102 < b; 4907a b c 4908SELECT * FROM t1 WHERE 102 < c; 4909a b c 4910DROP TABLE t1; 4911# 4912# Bug #54459: Assertion failed: param.sort_length, 4913# file .\filesort.cc, line 149 (part II) 4914# 4915CREATE TABLE t1(a ENUM('') NOT NULL); 4916INSERT INTO t1 VALUES (), (), (); 4917EXPLAIN SELECT 1 FROM t1 ORDER BY a COLLATE latin1_german2_ci; 4918id select_type table type possible_keys key key_len ref rows Extra 49191 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort 4920SELECT 1 FROM t1 ORDER BY a COLLATE latin1_german2_ci; 49211 49221 49231 49241 4925DROP TABLE t1; 4926# 4927# Bug #702310: usage of 2 join buffers after ref access to an empty table 4928# 4929CREATE TABLE t1 (f1 int) ; 4930INSERT INTO t1 VALUES (9); 4931CREATE TABLE t2 (f1 int); 4932INSERT INTO t2 VALUES (3),(7),(18); 4933INSERT INTO t2 VALUES (3),(7),(18); 4934INSERT INTO t2 VALUES (3),(7),(18); 4935INSERT INTO t2 VALUES (3),(7),(18); 4936CREATE TABLE t3 (f1 int); 4937INSERT INTO t3 VALUES (17); 4938CREATE TABLE t4 (f1 int PRIMARY KEY, f2 varchar(1024)) ; 4939CREATE TABLE t5 (f1 int) ; 4940INSERT INTO t5 VALUES (20),(5); 4941CREATE TABLE t6(f1 int); 4942INSERT INTO t6 VALUES (9),(7); 4943SET @save_join_buffer_size=@@join_buffer_size,@@join_buffer_size = 2176; 4944EXPLAIN 4945SELECT STRAIGHT_JOIN * FROM t2, (t1 LEFT JOIN (t3,t4) ON t1.f1 = t4.f1), t5, t6; 4946id select_type table type possible_keys key key_len ref rows Extra 49471 SIMPLE t2 ALL NULL NULL NULL NULL 12 49481 SIMPLE t1 ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) 49491 SIMPLE t3 ALL NULL NULL NULL NULL 1 Using where 49501 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t1.f1 1 49511 SIMPLE t5 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) 49521 SIMPLE t6 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) 4953SELECT STRAIGHT_JOIN * FROM t2, (t1 LEFT JOIN (t3,t4) ON t1.f1 = t4.f1), t5, t6; 4954f1 f1 f1 f1 f2 f1 f1 49553 9 NULL NULL NULL 20 9 49567 9 NULL NULL NULL 20 9 495718 9 NULL NULL NULL 20 9 49583 9 NULL NULL NULL 20 9 49597 9 NULL NULL NULL 20 9 49603 9 NULL NULL NULL 20 7 49617 9 NULL NULL NULL 20 7 496218 9 NULL NULL NULL 20 7 49633 9 NULL NULL NULL 20 7 49647 9 NULL NULL NULL 20 7 496518 9 NULL NULL NULL 20 9 49663 9 NULL NULL NULL 20 9 49673 9 NULL NULL NULL 5 9 49687 9 NULL NULL NULL 5 9 496918 9 NULL NULL NULL 5 9 497018 9 NULL NULL NULL 20 7 49713 9 NULL NULL NULL 20 7 49723 9 NULL NULL NULL 5 7 49737 9 NULL NULL NULL 5 7 497418 9 NULL NULL NULL 5 7 49753 9 NULL NULL NULL 5 9 49767 9 NULL NULL NULL 5 9 497718 9 NULL NULL NULL 5 9 49783 9 NULL NULL NULL 5 9 49797 9 NULL NULL NULL 20 9 49803 9 NULL NULL NULL 5 7 49817 9 NULL NULL NULL 5 7 498218 9 NULL NULL NULL 5 7 49833 9 NULL NULL NULL 5 7 49847 9 NULL NULL NULL 20 7 498518 9 NULL NULL NULL 20 9 49863 9 NULL NULL NULL 20 9 49877 9 NULL NULL NULL 20 9 498818 9 NULL NULL NULL 20 9 49897 9 NULL NULL NULL 5 9 499018 9 NULL NULL NULL 20 7 49913 9 NULL NULL NULL 20 7 49927 9 NULL NULL NULL 20 7 499318 9 NULL NULL NULL 20 7 49947 9 NULL NULL NULL 5 7 499518 9 NULL NULL NULL 5 9 49963 9 NULL NULL NULL 5 9 49977 9 NULL NULL NULL 5 9 499818 9 NULL NULL NULL 5 9 499918 9 NULL NULL NULL 5 7 50003 9 NULL NULL NULL 5 7 50017 9 NULL NULL NULL 5 7 500218 9 NULL NULL NULL 5 7 5003SET SESSION join_buffer_size = @save_join_buffer_size; 5004DROP TABLE t1,t2,t3,t4,t5,t6; 5005# 5006# Bug #698882: best equality substitution not applied to ref 5007# 5008CREATE TABLE t1 (a1 int NOT NULL, b1 char(10), INDEX idx (a1)); 5009CREATE TABLE t2 (a2 int NOT NULL, b2 char(10), INDEX idx (a2)); 5010CREATE TABLE t3 (a3 int NOT NULL, b3 char(10), INDEX idx (a3)); 5011INSERT INTO t1 VALUES (2,'xx'), (1,'xxx'), (11,'xxxxxxx'); 5012INSERT INTO t2 VALUES 5013(7,'yyyy'), (2,'y'), (3,'yyy'), (1,'yy'), (1,'yyyyy'), 5014(3,'yy'), (1,'y'), (4,'yyy'), (7,'y'), (4,'yyyyy'), (7,'yyy'), 5015(7,'yyyy'), (2,'yy'), (3,'yyy'), (1,'yyyyyyyy'), (1,'yyyyy'), 5016(3,'yy'), (1,'yyy'), (4,'yyy'), (7,'y'), (4,'yyyyy'), (7,'yyy'); 5017INSERT INTO t3 VALUES 5018(9,'zzzzzzz'), (2,'zzzzz'), (1,'z'), (9,'zz'), (1,'zz'), (5,'zzzzzzz'), 5019(4,'zz'), (3,'z'), (5,'zzzzzz'), (3,'zz'), (4,'zzzz'), (3,'z'), 5020(9,'zzzzzzzz'), (2,'zz'), (1,'zz'), (9,'zzz'), (1,'zzz'), (5,'zzzzzzzz'), 5021(4,'zzz'), (3,'zz'), (5,'zzzzzzz'), (3,'zzz'), (4,'zzzzz'), (3,'zz'), 5022(9,'zzzzzz'), (2,'zzzz'), (1,'zzz'), (9,'z'), (1,'z'), (5,'zzzzzz'), 5023(4,'z'), (3,'zzz'), (5,'zzzzz'), (3,'z'), (4,'zzz'), (3,'zzzz'), 5024(9,'zzzzz'), (2,'zzz'), (1,'zzzz'), (9,'zzz'), (1,'zzzz'), (5,'zzzzz'), 5025(4,'zzz'), (3,'zzzz'), (5,'zzzz'), (3,'zzz'), (4,'zz'), (3,'zzzzz'); 5026set @tmp= @@optimizer_switch; 5027SET SESSION optimizer_switch='index_condition_pushdown=off'; 5028EXPLAIN SELECT * from t1,t2,t3 WHERE t3.a3=t1.a1 AND t2.a2=t1.a1; 5029id select_type table type possible_keys key key_len ref rows Extra 50301 SIMPLE t1 ALL idx NULL NULL NULL 3 50311 SIMPLE t2 ref idx idx 4 test.t1.a1 2 50321 SIMPLE t3 ref idx idx 4 test.t1.a1 5 5033EXPLAIN SELECT * FROM t1,t2,t3 WHERE t2.a2=t1.a1 AND t3.a3=t1.a1; 5034id select_type table type possible_keys key key_len ref rows Extra 50351 SIMPLE t1 ALL idx NULL NULL NULL 3 50361 SIMPLE t2 ref idx idx 4 test.t1.a1 2 50371 SIMPLE t3 ref idx idx 4 test.t1.a1 5 5038EXPLAIN SELECT * FROM t1,t2,t3 WHERE t2.a2=t1.a1 AND t3.a3=t2.a2; 5039id select_type table type possible_keys key key_len ref rows Extra 50401 SIMPLE t1 ALL idx NULL NULL NULL 3 50411 SIMPLE t2 ref idx idx 4 test.t1.a1 2 50421 SIMPLE t3 ref idx idx 4 test.t1.a1 5 5043SELECT * from t1,t2,t3 5044WHERE t3.a3=t1.a1 AND t2.a2=t1.a1 AND 5045LENGTH(CONCAT(CONCAT(t1.b1,t2.b2),t3.b3)) <= 7; 5046a1 b1 a2 b2 a3 b3 50471 xxx 1 y 1 z 50481 xxx 1 y 1 z 50491 xxx 1 y 1 zz 50501 xxx 1 y 1 zz 50511 xxx 1 y 1 zzz 50521 xxx 1 y 1 zzz 50531 xxx 1 yy 1 z 50541 xxx 1 yy 1 z 50551 xxx 1 yy 1 zz 50561 xxx 1 yy 1 zz 50571 xxx 1 yyy 1 z 50581 xxx 1 yyy 1 z 50592 xx 2 y 2 zz 50602 xx 2 y 2 zzz 50612 xx 2 y 2 zzzz 50622 xx 2 yy 2 zz 50632 xx 2 yy 2 zzz 5064SELECT * FROM t1,t2,t3 5065WHERE t2.a2=t1.a1 AND t3.a3=t1.a1 AND 5066LENGTH(CONCAT(CONCAT(t1.b1,t2.b2),t3.b3)) <= 7; 5067a1 b1 a2 b2 a3 b3 50681 xxx 1 y 1 z 50691 xxx 1 y 1 z 50701 xxx 1 y 1 zz 50711 xxx 1 y 1 zz 50721 xxx 1 y 1 zzz 50731 xxx 1 y 1 zzz 50741 xxx 1 yy 1 z 50751 xxx 1 yy 1 z 50761 xxx 1 yy 1 zz 50771 xxx 1 yy 1 zz 50781 xxx 1 yyy 1 z 50791 xxx 1 yyy 1 z 50802 xx 2 y 2 zz 50812 xx 2 y 2 zzz 50822 xx 2 y 2 zzzz 50832 xx 2 yy 2 zz 50842 xx 2 yy 2 zzz 5085SELECT * FROM t1,t2,t3 5086WHERE t2.a2=t1.a1 AND t3.a3=t2.a2 AND 5087LENGTH(CONCAT(CONCAT(t1.b1,t2.b2),t3.b3)) <= 7; 5088a1 b1 a2 b2 a3 b3 50891 xxx 1 y 1 z 50901 xxx 1 y 1 z 50911 xxx 1 y 1 zz 50921 xxx 1 y 1 zz 50931 xxx 1 y 1 zzz 50941 xxx 1 y 1 zzz 50951 xxx 1 yy 1 z 50961 xxx 1 yy 1 z 50971 xxx 1 yy 1 zz 50981 xxx 1 yy 1 zz 50991 xxx 1 yyy 1 z 51001 xxx 1 yyy 1 z 51012 xx 2 y 2 zz 51022 xx 2 y 2 zzz 51032 xx 2 y 2 zzzz 51042 xx 2 yy 2 zz 51052 xx 2 yy 2 zzz 5106SET SESSION optimizer_switch=@tmp; 5107DROP TABLE t1,t2,t3; 5108# 5109# Bug #707555: crash with equality substitution in ref 5110# 5111CREATE TABLE t1 (f11 int, f12 int, PRIMARY KEY (f11), KEY (f12)) ; 5112INSERT INTO t1 VALUES (1,NULL), (8,NULL); 5113CREATE TABLE t2 (f21 int, f22 int, f23 int, KEY (f22)) ; 5114INSERT INTO t2 VALUES (1,NULL,3), (2,7,8); 5115CREATE TABLE t3 (f31 int, f32 int(11), PRIMARY KEY (f31), KEY (f32)) ; 5116INSERT INTO t3 VALUES (1,494862336); 5117CREATE TABLE t4 (f41 int, f42 int, PRIMARY KEY (f41), KEY (f42)) ; 5118INSERT INTO t4 VALUES (1,NULL), (8,NULL); 5119CREATE TABLE t5 (f51 int, PRIMARY KEY (f51)) ; 5120INSERT IGNORE INTO t5 VALUES (100); 5121CREATE TABLE t6 (f61 int, f62 int, KEY (f61)) ; 5122INSERT INTO t6 VALUES (NULL,1), (3,10); 5123CREATE TABLE t7 (f71 int, f72 int, KEY (f72)) ; 5124INSERT INTO t7 VALUES (1,NULL), (2,7); 5125EXPLAIN 5126SELECT t2.f23 FROM 5127(t1 LEFT JOIN (t2 JOIN t3 ON t2.f22=t3.f32) ON t1.f11=t3.f31) 5128LEFT JOIN 5129(((t4 JOIN t5 ON t4.f42=t5.f51) LEFT JOIN t6 ON t6.f62>0) JOIN t7 ON t6.f61>0) 5130ON t3.f31 = t6.f61 5131WHERE t7.f71>0; 5132id select_type table type possible_keys key key_len ref rows Extra 51331 SIMPLE t3 system PRIMARY,f32 NULL NULL NULL 1 51341 SIMPLE t5 system PRIMARY NULL NULL NULL 1 51351 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index 51361 SIMPLE t2 ref f22 f22 5 const 1 51371 SIMPLE t4 ref f42 f42 5 const 1 Using index 51381 SIMPLE t6 ref f61 f61 5 const 1 Using where 51391 SIMPLE t7 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) 5140SELECT t2.f23 FROM 5141(t1 LEFT JOIN (t2 JOIN t3 ON t2.f22=t3.f32) ON t1.f11=t3.f31) 5142LEFT JOIN 5143(((t4 JOIN t5 ON t4.f42=t5.f51) LEFT JOIN t6 ON t6.f62>0) JOIN t7 ON t6.f61>0) 5144ON t3.f31 = t6.f61 5145WHERE t7.f71>0; 5146f23 5147DROP TABLE t1,t2,t3,t4,t5,t6,t7; 5148CREATE TABLE t1(f1 int UNSIGNED) engine=myisam; 5149INSERT INTO t1 VALUES (3),(2),(1); 5150set sql_buffer_result=0; 5151SELECT f1 FROM t1 GROUP BY 1; 5152f1 51531 51542 51553 5156SELECT f1 FROM t1 GROUP BY '123' = 'abc'; 5157f1 51583 5159SELECT 1 FROM t1 GROUP BY 1; 51601 51611 5162set sql_buffer_result=1; 5163SELECT f1 FROM t1 GROUP BY 1; 5164f1 51651 51662 51673 5168SELECT f1 FROM t1 GROUP BY '123' = 'abc'; 5169f1 51703 5171SELECT 1 FROM t1 GROUP BY 1; 51721 51731 5174drop table t1; 5175set sql_buffer_result= 0; 5176# 5177# Bug #58422: Incorrect result when OUTER JOIN'ing 5178# with an empty table 5179# 5180CREATE TABLE t_empty(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM; 5181CREATE TABLE t1(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM; 5182INSERT INTO t1 VALUES (1,1), (2,2), (3,3); 5183CREATE TABLE t2(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM; 5184INSERT INTO t2 VALUES (1,1), (2,2), (3,3); 5185EXPLAIN 5186SELECT * 5187FROM 5188t1 5189LEFT OUTER JOIN 5190(t2 INNER JOIN t_empty ON TRUE) 5191ON t1.pk=t2.pk 5192WHERE t2.pk <> 2; 5193id select_type table type possible_keys key key_len ref rows Extra 51941 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 5195SELECT * 5196FROM 5197t1 5198LEFT OUTER JOIN 5199(t2 INNER JOIN t_empty ON TRUE) 5200ON t1.pk=t2.pk 5201WHERE t2.pk <> 2; 5202pk i pk i pk i 5203EXPLAIN 5204SELECT * 5205FROM 5206t1 5207LEFT OUTER JOIN 5208(t2 CROSS JOIN t_empty) 5209ON t1.pk=t2.pk 5210WHERE t2.pk <> 2; 5211id select_type table type possible_keys key key_len ref rows Extra 52121 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 5213SELECT * 5214FROM 5215t1 5216LEFT OUTER JOIN 5217(t2 CROSS JOIN t_empty) 5218ON t1.pk=t2.pk 5219WHERE t2.pk <> 2; 5220pk i pk i pk i 5221EXPLAIN 5222SELECT * 5223FROM 5224t1 5225LEFT OUTER JOIN 5226(t2 INNER JOIN t_empty ON t_empty.i=t2.i) 5227ON t1.pk=t2.pk 5228WHERE t2.pk <> 2; 5229id select_type table type possible_keys key key_len ref rows Extra 52301 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 5231SELECT * 5232FROM 5233t1 5234LEFT OUTER JOIN 5235(t2 INNER JOIN t_empty ON t_empty.i=t2.i) 5236ON t1.pk=t2.pk 5237WHERE t2.pk <> 2; 5238pk i pk i pk i 5239DROP TABLE t1,t2,t_empty; 5240End of 5.1 tests 5241# 5242# Bug#45227: Lost HAVING clause led to a wrong result. 5243# 5244CREATE TABLE `CC` ( 5245`int_nokey` int(11) NOT NULL, 5246`int_key` int(11) NOT NULL, 5247`varchar_key` varchar(1) NOT NULL, 5248`varchar_nokey` varchar(1) NOT NULL, 5249KEY `int_key` (`int_key`), 5250KEY `varchar_key` (`varchar_key`) 5251); 5252INSERT INTO `CC` VALUES 5253(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' 5254,'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'), 5255(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' 5256,'x'); 5257EXPLAIN SELECT `varchar_nokey` G1 FROM CC WHERE `int_nokey` AND `int_key` <= 4 5258HAVING G1 ORDER BY `varchar_key` LIMIT 6 ; 5259id select_type table type possible_keys key key_len ref rows Extra 52601 SIMPLE CC range int_key int_key 4 NULL 9 Using index condition; Using where; Using filesort 5261SELECT `varchar_nokey` G1 FROM CC WHERE `int_nokey` AND `int_key` <= 4 5262HAVING G1 ORDER BY `varchar_key` LIMIT 6 ; 5263G1 5264Warnings: 5265Warning 1292 Truncated incorrect DOUBLE value: 'z' 5266Warning 1292 Truncated incorrect DOUBLE value: 'a' 5267Warning 1292 Truncated incorrect DOUBLE value: 'q' 5268Warning 1292 Truncated incorrect DOUBLE value: 'm' 5269Warning 1292 Truncated incorrect DOUBLE value: 'j' 5270DROP TABLE CC; 5271# End of test#45227 5272# 5273# BUG#776274: substitution of a single row table 5274# 5275CREATE TABLE t1 (a int NOT NULL , b int); 5276INSERT INTO t1 VALUES (2,2); 5277SELECT * FROM t1 WHERE a = b; 5278a b 52792 2 5280EXPLAIN 5281SELECT * FROM t1 WHERE a = b; 5282id select_type table type possible_keys key key_len ref rows Extra 52831 SIMPLE t1 system NULL NULL NULL NULL 1 5284DROP TABLE t1; 5285# 5286# Bug#54515: Crash in opt_range.cc::get_best_group_min_max on 5287# SELECT from VIEW with GROUP BY 5288# 5289CREATE TABLE t1 ( 5290col_int_key int DEFAULT NULL, 5291KEY int_key (col_int_key) 5292) ; 5293INSERT INTO t1 VALUES (1),(2); 5294CREATE VIEW view_t1 AS 5295SELECT t1.col_int_key AS col_int_key 5296FROM t1; 5297SELECT col_int_key FROM view_t1 GROUP BY col_int_key; 5298col_int_key 52991 53002 5301DROP VIEW view_t1; 5302DROP TABLE t1; 5303# End of test BUG#54515 5304# 5305# Bug #57203 Assertion `field_length <= 255' failed. 5306# 5307SELECT coalesce((avg(distinct (geomfromtext("point(25379 -22010)"))))) 5308UNION ALL 5309SELECT coalesce((avg(distinct (geomfromtext("point(25379 -22010)"))))) 5310AS foo 5311; 5312ERROR HY000: Illegal parameter data type geometry for operation 'avg' 5313CREATE table t1(a text); 5314INSERT INTO t1 VALUES (''), (''); 5315SELECT avg(distinct(t1.a)) FROM t1, t1 t2 5316GROUP BY t2.a ORDER BY t1.a; 5317avg(distinct(t1.a)) 53180 5319DROP TABLE t1; 5320# End of test BUG#57203 5321# 5322# lp:822760 Wrong result with view + invalid dates 5323# 5324CREATE TABLE t1 (f1 date); 5325INSERT IGNORE INTO t1 VALUES ('0000-00-00'); 5326CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1; 5327SELECT * FROM t1 HAVING f1 = 'zz'; 5328f1 53290000-00-00 5330Warnings: 5331Warning 1292 Truncated incorrect datetime value: 'zz' 5332SELECT * FROM t1 HAVING f1 <= 'aa' ; 5333f1 53340000-00-00 5335Warnings: 5336Warning 1292 Truncated incorrect datetime value: 'aa' 5337SELECT * FROM t1 HAVING f1 = 'zz' AND f1 <= 'aa' ; 5338f1 53390000-00-00 5340Warnings: 5341Warning 1292 Truncated incorrect datetime value: 'zz' 5342Warning 1292 Truncated incorrect datetime value: 'aa' 5343SELECT * FROM t1 WHERE f1 = 'zz' AND f1 <= 'aa' ; 5344f1 53450000-00-00 5346Warnings: 5347Warning 1292 Truncated incorrect datetime value: 'zz' 5348Warning 1292 Truncated incorrect datetime value: 'aa' 5349SELECT * FROM v1 HAVING f1 = 'zz' AND f1 <= 'aa' ; 5350f1 53510000-00-00 5352Warnings: 5353Warning 1292 Truncated incorrect datetime value: 'zz' 5354Warning 1292 Truncated incorrect datetime value: 'aa' 5355DROP TABLE t1; 5356DROP VIEW v1; 5357# 5358# Bug#63020: Function "format"'s 'locale' argument is not considered 5359# when creating a "view' 5360# 5361CREATE TABLE t1 (f1 DECIMAL(10,2)); 5362INSERT INTO t1 VALUES (11.67),(17865.3),(12345678.92); 5363CREATE VIEW view_t1 AS SELECT FORMAT(f1,1,'sk_SK') AS f1 FROM t1; 5364SHOW CREATE VIEW view_t1; 5365View Create View character_set_client collation_connection 5366view_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 5367SELECT * FROM view_t1; 5368f1 536911,7 537017 865,3 537112 345 678,9 5372DROP TABLE t1; 5373DROP VIEW view_t1; 5374# End of test BUG#63020 5375# 5376# Bug #13571700 TINYBLOB NOT NULL, CRASH IN PROTOCOL::NET_STORE_DATA 5377# 5378CREATE TABLE t1 (a TINYBLOB NOT NULL); 5379SELECT a, COUNT(*) FROM t1 WHERE 0; 5380a COUNT(*) 5381NULL 0 5382DROP TABLE t1; 5383SET @@optimizer_switch=@save_optimizer_switch; 5384# 5385# LP bug#994275 Assertion `real->type() == Item::FIELD_ITEM' failed 5386# in add_not_null_conds(JOIN*) with JOIN, ZEROFILL column, PK 5387# 5388CREATE TABLE t1 ( a INT(6) ZEROFILL ); 5389INSERT INTO t1 VALUES (1),(2); 5390CREATE TABLE t2 ( b INT PRIMARY KEY ); 5391INSERT INTO t2 VALUES (3),(4); 5392SELECT * FROM t1, t2 WHERE a=3 AND a=b; 5393a b 5394drop table t1,t2; 5395# 5396# Bug mdev-4250: wrong transformation of WHERE condition with OR 5397# 5398CREATE TABLE t1 (pk int PRIMARY KEY, a int); 5399INSERT INTO t1 VALUES (3,0), (2,0), (4,1), (5,0), (1,0); 5400SELECT * FROM t1 WHERE (1=2 OR t1.pk=2) AND t1.a <> 0; 5401pk a 5402EXPLAIN EXTENDED 5403SELECT * FROM t1 WHERE (1=2 OR t1.pk=2) AND t1.a <> 0; 5404id select_type table type possible_keys key key_len ref rows filtered Extra 54051 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 5406Warnings: 5407Note 1003 select 2 AS `pk`,0 AS `a` from `test`.`t1` where 0 5408DROP TABLE t1; 5409SELECT * FROM mysql.time_zone 5410WHERE ( NOT (Use_leap_seconds <= Use_leap_seconds AND Time_zone_id != 1) 5411AND Time_zone_id = Time_zone_id 5412OR Time_zone_id <> Time_zone_id ) 5413AND Use_leap_seconds <> 'N'; 5414Time_zone_id Use_leap_seconds 5415# 5416# Bug mdev-4274: result of simplification of OR badly merged 5417# into embedding AND 5418# 5419CREATE TABLE t1 (a int, b int, INDEX idx(b)) ENGINE=MyISAM; 5420INSERT INTO t1 VALUES (8,8); 5421CREATE TABLE t2 (c int, INDEX idx(c)) ENGINE=MyISAM; 5422INSERT INTO t2 VALUES (8), (9); 5423EXPLAIN EXTENDED 5424SELECT * FROM t1 INNER JOIN t2 ON ( c = a ) 5425WHERE 1 IS NULL OR b < 33 AND b = c; 5426id select_type table type possible_keys key key_len ref rows filtered Extra 54271 SIMPLE t1 system idx NULL NULL NULL 1 100.00 54281 SIMPLE t2 ref idx idx 5 const 1 100.00 Using index 5429Warnings: 5430Note 1003 select 8 AS `a`,8 AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where `test`.`t2`.`c` = 8 5431SELECT * FROM t1 INNER JOIN t2 ON ( c = a ) 5432WHERE 1 IS NULL OR b < 33 AND b = c; 5433a b c 54348 8 8 5435DROP TABLE t1,t2; 5436# 5437# Bug mdev-4413: another manifestations of bug mdev-4274 5438# (valgrind complains) 5439# 5440CREATE TABLE t1 (a int, b int) ENGINE=MyISAM; 5441INSERT INTO t1 VALUES (7,1); 5442CREATE TABLE t2 (c int) ENGINE=MyISAM; 5443INSERT INTO t2 VALUES (0), (8); 5444SELECT * FROM t1, t2 5445WHERE c = a AND 5446( 0 OR ( b BETWEEN 45 AND 300 OR a > 45 AND a < 100 ) AND b = c ); 5447a b c 5448DROP TABLE t1, t2; 5449# 5450# Bug mdev-4355: equalities from the result of simplification of OR 5451# are not propagated to lower AND levels 5452# 5453CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM; 5454INSERT INTO t1 VALUES (1,101),(2,102),(3,103),(4,104),(5,11); 5455EXPLAIN EXTENDED 5456SELECT * FROM t1 WHERE (1 != 1 OR a = 5) AND (b != 1 OR a = 1); 5457id select_type table type possible_keys key key_len ref rows filtered Extra 54581 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where 5459Warnings: 5460Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` = 5 and `test`.`t1`.`b` <> 1 5461SELECT * FROM t1 WHERE (1 != 1 OR a = 5) AND (b != 1 OR a = 1); 5462a b 54635 11 5464EXPLAIN EXTENDED 5465SELECT * FROM t1 WHERE (b != 1 OR a = 1) AND (1 != 1 OR a = 5); 5466id select_type table type possible_keys key key_len ref rows filtered Extra 54671 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where 5468Warnings: 5469Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` = 5 and `test`.`t1`.`b` <> 1 5470SELECT * FROM t1 WHERE (b != 1 OR a = 1) AND (1 != 1 OR a = 5); 5471a b 54725 11 5473EXPLAIN EXTENDED 5474SELECT * FROM t1 WHERE (b != 1 OR a = 1) AND (a = 5 OR 1 != 1); 5475id select_type table type possible_keys key key_len ref rows filtered Extra 54761 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where 5477Warnings: 5478Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` = 5 and `test`.`t1`.`b` <> 1 5479SELECT * FROM t1 WHERE (b != 1 OR a = 1) AND (a = 5 OR 1 != 1); 5480a b 54815 11 5482EXPLAIN EXTENDED 5483SELECT * FROM t1 WHERE (b = 1 OR a = 1) AND (b = 5 AND a = 5 OR 1 != 1); 5484id select_type table type possible_keys key key_len ref rows filtered Extra 54851 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 5486Warnings: 5487Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where 0 5488SELECT * FROM t1 WHERE (b = 1 OR a = 1) AND (b = 5 AND a = 5 OR 1 != 1); 5489a b 5490EXPLAIN EXTENDED 5491SELECT * FROM t1 WHERE (b = 1 OR a = 5) AND (b = 5 AND a = 5 OR 1 != 1); 5492id select_type table type possible_keys key key_len ref rows filtered Extra 54931 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where 5494Warnings: 5495Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` = 5 and `test`.`t1`.`a` = 5 5496SELECT * FROM t1 WHERE (b = 1 OR a = 5) AND (b = 5 AND a = 5 OR 1 != 1); 5497a b 5498DROP TABLE t1; 5499# 5500# Bug mdev-4418: impossible multiple equality in OR formula 5501# after row substitution 5502# 5503CREATE TABLE t1 (a int, b varchar(1)) ENGINE=MyISAM; 5504INSERT INTO t1 VALUES (0,'j'), (8,'v'); 5505CREATE TABLE t2 (c varchar(1), d varchar(1)) ENGINE=MyISAM; 5506INSERT INTO t2 VALUES ('k','k'); 5507EXPLAIN EXTENDED 5508SELECT * FROM t1, t2 WHERE c=b AND (1=2 OR ((b='h' OR a=136) AND d=b)); 5509id select_type table type possible_keys key key_len ref rows filtered Extra 55101 SIMPLE t2 system NULL NULL NULL NULL 1 100.00 55111 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 5512Warnings: 5513Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,'k' AS `c`,'k' AS `d` from `test`.`t1` where `test`.`t1`.`b` = 'k' and `test`.`t1`.`a` = 136 5514SELECT * FROM t1, t2 WHERE c=b AND (1=2 OR ((b='h' OR a=136) AND d=b)); 5515a b c d 5516DROP TABLE t1,t2; 5517# 5518# Bug mdev-4944: range conditition in OR formula with fields 5519# belonging to multiple equalities 5520# 5521CREATE TABLE t1 (i1 int, j1 int) ENGINE=MyISAM; 5522INSERT INTO t1 VALUES (1,8); 5523CREATE TABLE t2 (i2 int, INDEX idx (i2)) ENGINE=MyISAM; 5524INSERT INTO t2 VALUES (8), (9); 5525EXPLAIN EXTENDED 5526SELECT * FROM t1, t2 5527WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 ); 5528id select_type table type possible_keys key key_len ref rows filtered Extra 55291 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 5530Warnings: 5531Note 1003 select 1 AS `i1`,8 AS `j1`,NULL AS `i2` from `test`.`t2` where 0 5532SELECT * FROM t1, t2 5533WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 ); 5534i1 j1 i2 5535DROP TABLE t1,t2; 5536# 5537# Bug mdev-4971: equality propagation after merging degenerate 5538# disjunction into embedding AND level 5539# 5540CREATE TABLE t1 (pk1 int, a1 int, b1 int, PRIMARY KEY(pk1)) ENGINE=MyISAM; 5541INSERT INTO t1 VALUES (1,10,100), (2,20,200) ; 5542CREATE TABLE t2 (pk2 int, a2 int, PRIMARY KEY(pk2)) ENGINE=MyISAM; 5543INSERT INTO t2 VALUES (1,1); 5544SELECT * FROM t1, t2 5545WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL ); 5546pk1 a1 b1 pk2 a2 5547EXPLAIN EXTENDED 5548SELECT * FROM t1, t2 5549WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL ); 5550id select_type table type possible_keys key key_len ref rows filtered Extra 55511 SIMPLE t2 system PRIMARY NULL NULL NULL 1 100.00 55521 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 5553Warnings: 5554Note 1003 select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,1 AS `pk2`,1 AS `a2` from `test`.`t1` where `test`.`t1`.`a1` = 1 and `test`.`t1`.`b1` = 6 5555INSERT INTO t1 VALUES (3,1,6); 5556SELECT * FROM t1, t2 5557WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL ); 5558pk1 a1 b1 pk2 a2 55593 1 6 1 1 5560DROP TABLE t1,t2; 5561End of 5.3 tests 5562# 5563# mysql BUG#1271 Undefined variable in PASSWORD() 5564# function is not handled correctly 5565# 5566create table t1 ( 5567name VARCHAR(50) NOT NULL PRIMARY KEY, 5568pw VARCHAR(41) NOT NULL); 5569INSERT INTO t1 (name, pw) 5570VALUES ('tom', PASSWORD('my_pw')); 5571SET @pass='my_pw'; 5572SET @wrong='incorrect'; 5573select * from t1; 5574name pw 5575tom *F305E8EC27734F687F2EB6EC03CF0F7AF27C18E1 5576select length(PASSWORD(@pass)); 5577length(PASSWORD(@pass)) 557841 5579SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@pass); 5580name 5581tom 5582SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@wrong); 5583name 5584SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@undefined); 5585name 5586select (SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@pass)); 5587(SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@pass)) 5588tom 5589select (SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@wrong)); 5590(SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@wrong)) 5591NULL 5592select (SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@undefined)); 5593(SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@undefined)) 5594NULL 5595drop table t1; 5596End of 10.0 tests 5597