1set @save_optimizer_switch_jcl6=@@optimizer_switch; 2set @@optimizer_switch='optimize_join_buffer_size=on'; 3set @@optimizer_switch='semijoin_with_cache=on'; 4set @@optimizer_switch='outer_join_with_cache=on'; 5set @@optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; 6set join_cache_level=6; 7show variables like 'join_cache_level'; 8Variable_name Value 9join_cache_level 6 10set @optimizer_switch_for_select_test=@@optimizer_switch; 11set @join_cache_level_for_select_test=@@join_cache_level; 12drop table if exists t1,t2,t3,t4,t11; 13drop table if exists t1_1,t1_2,t9_1,t9_2,t1aa,t2aa; 14drop view if exists v1; 15SET optimizer_switch=ifnull(@optimizer_switch_for_select_test,'outer_join_with_cache=off'); 16set join_cache_level=@join_cache_level_for_select_test; 17CREATE TABLE t1 ( 18Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL, 19Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL 20); 21INSERT INTO t1 VALUES (9410,9412); 22select period from t1; 23period 249410 25select * from t1; 26Period Varor_period 279410 9412 28select t1.* from t1; 29Period Varor_period 309410 9412 31CREATE TABLE t2 ( 32auto int not null auto_increment, 33fld1 int(6) unsigned zerofill DEFAULT '000000' NOT NULL, 34companynr tinyint(2) unsigned zerofill DEFAULT '00' NOT NULL, 35fld3 char(30) DEFAULT '' NOT NULL, 36fld4 char(35) DEFAULT '' NOT NULL, 37fld5 char(35) DEFAULT '' NOT NULL, 38fld6 char(4) DEFAULT '' NOT NULL, 39UNIQUE fld1 (fld1), 40KEY fld3 (fld3), 41PRIMARY KEY (auto) 42); 43select t2.fld3 from t2 where companynr = 58 and fld3 like "%imaginable%"; 44fld3 45imaginable 46select fld3 from t2 where fld3 like "%cultivation" ; 47fld3 48cultivation 49select t2.fld3,companynr from t2 where companynr = 57+1 order by fld3; 50fld3 companynr 51concoct 58 52druggists 58 53engrossing 58 54Eurydice 58 55exclaimers 58 56ferociousness 58 57hopelessness 58 58Huey 58 59imaginable 58 60judges 58 61merging 58 62ostrich 58 63peering 58 64Phelps 58 65presumes 58 66Ruth 58 67sentences 58 68Shylock 58 69straggled 58 70synergy 58 71thanking 58 72tying 58 73unlocks 58 74select fld3,companynr from t2 where companynr = 58 order by fld3; 75fld3 companynr 76concoct 58 77druggists 58 78engrossing 58 79Eurydice 58 80exclaimers 58 81ferociousness 58 82hopelessness 58 83Huey 58 84imaginable 58 85judges 58 86merging 58 87ostrich 58 88peering 58 89Phelps 58 90presumes 58 91Ruth 58 92sentences 58 93Shylock 58 94straggled 58 95synergy 58 96thanking 58 97tying 58 98unlocks 58 99select fld3 from t2 order by fld3 desc limit 10; 100fld3 101youthfulness 102yelped 103Wotan 104workers 105Witt 106witchcraft 107Winsett 108Willy 109willed 110wildcats 111select fld3 from t2 order by fld3 desc limit 5; 112fld3 113youthfulness 114yelped 115Wotan 116workers 117Witt 118select fld3 from t2 order by fld3 desc limit 5,5; 119fld3 120witchcraft 121Winsett 122Willy 123willed 124wildcats 125select t2.fld3 from t2 where fld3 = 'honeysuckle'; 126fld3 127honeysuckle 128select t2.fld3 from t2 where fld3 LIKE 'honeysuckl_'; 129fld3 130honeysuckle 131select t2.fld3 from t2 where fld3 LIKE 'hon_ysuckl_'; 132fld3 133honeysuckle 134select t2.fld3 from t2 where fld3 LIKE 'honeysuckle%'; 135fld3 136honeysuckle 137select t2.fld3 from t2 where fld3 LIKE 'h%le'; 138fld3 139honeysuckle 140select t2.fld3 from t2 where fld3 LIKE 'honeysuckle_'; 141fld3 142select t2.fld3 from t2 where fld3 LIKE 'don_t_find_me_please%'; 143fld3 144explain select t2.fld3 from t2 where fld3 = 'honeysuckle'; 145id select_type table type possible_keys key key_len ref rows Extra 1461 SIMPLE t2 ref fld3 fld3 30 const 1 Using where; Using index 147explain select fld3 from t2 ignore index (fld3) where fld3 = 'honeysuckle'; 148id select_type table type possible_keys key key_len ref rows Extra 1491 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 150explain select fld3 from t2 use index (fld1) where fld3 = 'honeysuckle'; 151id select_type table type possible_keys key key_len ref rows Extra 1521 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 153explain select fld3 from t2 use index (fld3) where fld3 = 'honeysuckle'; 154id select_type table type possible_keys key key_len ref rows Extra 1551 SIMPLE t2 ref fld3 fld3 30 const 1 Using where; Using index 156explain select fld3 from t2 use index (fld1,fld3) where fld3 = 'honeysuckle'; 157id select_type table type possible_keys key key_len ref rows Extra 1581 SIMPLE t2 ref fld3 fld3 30 const 1 Using where; Using index 159explain select fld3 from t2 ignore index (fld3,not_used); 160ERROR 42000: Key 'not_used' doesn't exist in table 't2' 161explain select fld3 from t2 use index (not_used); 162ERROR 42000: Key 'not_used' doesn't exist in table 't2' 163select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3; 164fld3 165honeysuckle 166honoring 167explain select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3; 168id select_type table type possible_keys key key_len ref rows Extra 1691 SIMPLE t2 range fld3 fld3 30 NULL 2 Using where; Using index 170select fld1,fld3 from t2 where fld3="Colombo" or fld3 = "nondecreasing" order by fld3; 171fld1 fld3 172148504 Colombo 173068305 Colombo 174000000 nondecreasing 175select fld1,fld3 from t2 where companynr = 37 and fld3 = 'appendixes'; 176fld1 fld3 177232605 appendixes 1781232605 appendixes 1791232606 appendixes 1801232607 appendixes 1811232608 appendixes 1821232609 appendixes 183select fld1 from t2 where fld1=250501 or fld1="250502"; 184fld1 185250501 186250502 187explain select fld1 from t2 where fld1=250501 or fld1="250502"; 188id select_type table type possible_keys key key_len ref rows Extra 1891 SIMPLE t2 range fld1 fld1 4 NULL 2 Using where; Using index 190select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502; 191fld1 192250501 193250502 194250505 195250601 196explain select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502; 197id select_type table type possible_keys key key_len ref rows Extra 1981 SIMPLE t2 range fld1 fld1 4 NULL 4 Using where; Using index 199select fld1,fld3 from t2 where companynr = 37 and fld3 like 'f%'; 200fld1 fld3 201012001 flanking 202013602 foldout 203013606 fingerings 204018007 fanatic 205018017 featherweight 206018054 fetters 207018103 flint 208018104 flopping 209036002 funereal 210038017 fetched 211038205 firearm 212058004 Fenton 213088303 feminine 214186002 freakish 215188007 flurried 216188505 fitting 217198006 furthermore 218202301 Fitzpatrick 219208101 fiftieth 220208113 freest 221218008 finishers 222218022 feed 223218401 faithful 224226205 foothill 225226209 furnishings 226228306 forthcoming 227228311 fated 228231315 freezes 229232102 forgivably 230238007 filial 231238008 fixedly 232select fld3 from t2 where fld3 like "L%" and fld3 = "ok"; 233fld3 234select fld3 from t2 where (fld3 like "C%" and fld3 = "Chantilly"); 235fld3 236Chantilly 237select fld1,fld3 from t2 where fld1 like "25050%"; 238fld1 fld3 239250501 poisoning 240250502 Iraqis 241250503 heaving 242250504 population 243250505 bomb 244select fld1,fld3 from t2 where fld1 like "25050_"; 245fld1 fld3 246250501 poisoning 247250502 Iraqis 248250503 heaving 249250504 population 250250505 bomb 251select distinct companynr from t2; 252companynr 25300 25437 25536 25650 25758 25829 25940 26053 26165 26241 26334 26468 265select distinct companynr from t2 order by companynr; 266companynr 26700 26829 26934 27036 27137 27240 27341 27450 27553 27658 27765 27868 279select distinct companynr from t2 order by companynr desc; 280companynr 28168 28265 28358 28453 28550 28641 28740 28837 28936 29034 29129 29200 293select distinct t2.fld3,period from t2,t1 where companynr=37 and fld3 like "O%"; 294fld3 period 295obliterates 9410 296offload 9410 297opaquely 9410 298organizer 9410 299overestimating 9410 300overlay 9410 301select distinct fld3 from t2 where companynr = 34 order by fld3; 302fld3 303absentee 304accessed 305ahead 306alphabetic 307Asiaticizations 308attitude 309aye 310bankruptcies 311belays 312Blythe 313bomb 314boulevard 315bulldozes 316cannot 317caressing 318charcoal 319checksumming 320chess 321clubroom 322colorful 323cosy 324creator 325crying 326Darius 327diffusing 328duality 329Eiffel 330Epiphany 331Ernestine 332explorers 333exterminated 334famine 335forked 336Gershwins 337heaving 338Hodges 339Iraqis 340Italianization 341Lagos 342landslide 343libretto 344Majorca 345mastering 346narrowed 347occurred 348offerers 349Palestine 350Peruvianizes 351pharmaceutic 352poisoning 353population 354Pygmalion 355rats 356realest 357recording 358regimented 359retransmitting 360reviver 361rouses 362scars 363sicker 364sleepwalk 365stopped 366sugars 367translatable 368uncles 369unexpected 370uprisings 371versatility 372vest 373select distinct fld3 from t2 limit 10; 374fld3 375abates 376abiding 377Abraham 378abrogating 379absentee 380abut 381accessed 382accruing 383accumulating 384accuracies 385select distinct fld3 from t2 having fld3 like "A%" limit 10; 386fld3 387abates 388abiding 389Abraham 390abrogating 391absentee 392abut 393accessed 394accruing 395accumulating 396accuracies 397select distinct substring(fld3,1,3) from t2 where fld3 like "A%"; 398substring(fld3,1,3) 399aba 400abi 401Abr 402abs 403abu 404acc 405acq 406acu 407Ade 408adj 409Adl 410adm 411Ado 412ads 413adv 414aer 415aff 416afi 417afl 418afo 419agi 420ahe 421aim 422air 423Ald 424alg 425ali 426all 427alp 428alr 429ama 430ame 431amm 432ana 433and 434ane 435Ang 436ani 437Ann 438Ant 439api 440app 441aqu 442Ara 443arc 444Arm 445arr 446Art 447Asi 448ask 449asp 450ass 451ast 452att 453aud 454Aug 455aut 456ave 457avo 458awe 459aye 460Azt 461select distinct substring(fld3,1,3) as a from t2 having a like "A%" order by a limit 10; 462a 463aba 464abi 465Abr 466abs 467abu 468acc 469acq 470acu 471Ade 472adj 473select distinct substring(fld3,1,3) from t2 where fld3 like "A%" limit 10; 474substring(fld3,1,3) 475aba 476abi 477Abr 478abs 479abu 480acc 481acq 482acu 483Ade 484adj 485select distinct substring(fld3,1,3) as a from t2 having a like "A%" limit 10; 486a 487aba 488abi 489Abr 490abs 491abu 492acc 493acq 494acu 495Ade 496adj 497create table t3 ( 498period int not null, 499name char(32) not null, 500companynr int not null, 501price double(11,0), 502price2 double(11,0), 503key (period), 504key (name) 505); 506create temporary table tmp engine = myisam select * from t3; 507insert into t3 select * from tmp; 508insert into tmp select * from t3; 509insert into t3 select * from tmp; 510insert into tmp select * from t3; 511insert into t3 select * from tmp; 512insert into tmp select * from t3; 513insert into t3 select * from tmp; 514insert into tmp select * from t3; 515insert into t3 select * from tmp; 516insert into tmp select * from t3; 517insert into t3 select * from tmp; 518insert into tmp select * from t3; 519insert into t3 select * from tmp; 520insert into tmp select * from t3; 521insert into t3 select * from tmp; 522insert into tmp select * from t3; 523insert into t3 select * from tmp; 524alter table t3 add t2nr int not null auto_increment primary key first; 525drop table tmp; 526set tmp_memory_table_size=0; 527select distinct concat(fld3," ",fld3) as namn from t2,t3 where t2.fld1=t3.t2nr order by namn limit 10; 528namn 529Abraham Abraham 530abrogating abrogating 531admonishing admonishing 532Adolph Adolph 533afield afield 534aging aging 535ammonium ammonium 536analyzable analyzable 537animals animals 538animized animized 539set tmp_memory_table_size=default; 540select distinct concat(fld3," ",fld3) from t2,t3 where t2.fld1=t3.t2nr order by fld3 limit 10; 541concat(fld3," ",fld3) 542Abraham Abraham 543abrogating abrogating 544admonishing admonishing 545Adolph Adolph 546afield afield 547aging aging 548ammonium ammonium 549analyzable analyzable 550animals animals 551animized animized 552select distinct fld5 from t2 limit 10; 553fld5 554neat 555Steinberg 556jarring 557tinily 558balled 559persist 560attainments 561fanatic 562measures 563rightfulness 564select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10; 565fld3 count(*) 566affixed 1 567and 1 568annoyers 1 569Anthony 1 570assayed 1 571assurers 1 572attendants 1 573bedlam 1 574bedpost 1 575boasted 1 576set tmp_memory_table_size=0; 577select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10; 578fld3 count(*) 579affixed 1 580and 1 581annoyers 1 582Anthony 1 583assayed 1 584assurers 1 585attendants 1 586bedlam 1 587bedpost 1 588boasted 1 589set tmp_memory_table_size=default; 590select distinct fld3,repeat("a",length(fld3)),count(*) from t2 group by companynr,fld3 limit 100,10; 591fld3 repeat("a",length(fld3)) count(*) 592circus aaaaaa 1 593cited aaaaa 1 594Colombo aaaaaaa 1 595congresswoman aaaaaaaaaaaaa 1 596contrition aaaaaaaaaa 1 597corny aaaaa 1 598cultivation aaaaaaaaaaa 1 599definiteness aaaaaaaaaaaa 1 600demultiplex aaaaaaaaaaa 1 601disappointing aaaaaaaaaaaaa 1 602select distinct companynr,rtrim(space(512+companynr)) from t3 order by 1,2; 603companynr rtrim(space(512+companynr)) 60437 60578 606101 607154 608311 609447 610512 611select distinct fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by fld3; 612fld3 613explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by t3.t2nr,fld3; 614id select_type table type possible_keys key key_len ref rows Extra 6151 SIMPLE t2 ALL fld1 NULL NULL NULL 1199 Using where; Using temporary; Using filesort 6161 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.fld1 1 Using where; Using index 617explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period; 618id select_type table type possible_keys key key_len ref rows Extra 6191 SIMPLE t1 ALL period NULL NULL NULL 41810 Using temporary; Using filesort 6201 SIMPLE t3 ref period period 4 test.t1.period 4181 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 621explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10; 622id select_type table type possible_keys key key_len ref rows Extra 6231 SIMPLE t3 ALL period NULL NULL NULL 41810 Using temporary; Using filesort 6241 SIMPLE t1 ref period period 4 test.t3.period 4181 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 625explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10; 626id select_type table type possible_keys key key_len ref rows Extra 6271 SIMPLE t1 ALL period NULL NULL NULL 41810 Using temporary; Using filesort 6281 SIMPLE t3 ref period period 4 test.t1.period 4181 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 629select period from t1; 630period 6319410 632select period from t1 where period=1900; 633period 634select fld3,period from t1,t2 where fld1 = 011401 order by period; 635fld3 period 636breaking 9410 637select fld3,period from t2,t3 where t2.fld1 = 011401 and t2.fld1=t3.t2nr and t3.period=1001; 638fld3 period 639breaking 1001 640explain select fld3,period from t2,t3 where t2.fld1 = 011401 and t3.t2nr=t2.fld1 and 1001 = t3.period; 641id select_type table type possible_keys key key_len ref rows Extra 6421 SIMPLE t2 const fld1 fld1 4 const 1 6431 SIMPLE t3 const PRIMARY,period PRIMARY 4 const 1 644select fld3,period from t2,t1 where companynr*10 = 37*10; 645fld3 period 646breaking 9410 647Romans 9410 648intercepted 9410 649bewilderingly 9410 650astound 9410 651admonishing 9410 652sumac 9410 653flanking 9410 654combed 9410 655subjective 9410 656scatterbrain 9410 657Eulerian 9410 658Kane 9410 659overlay 9410 660perturb 9410 661goblins 9410 662annihilates 9410 663Wotan 9410 664snatching 9410 665concludes 9410 666laterally 9410 667yelped 9410 668grazing 9410 669Baird 9410 670celery 9410 671misunderstander 9410 672handgun 9410 673foldout 9410 674mystic 9410 675succumbed 9410 676Nabisco 9410 677fingerings 9410 678aging 9410 679afield 9410 680ammonium 9410 681boat 9410 682intelligibility 9410 683Augustine 9410 684teethe 9410 685dreaded 9410 686scholastics 9410 687audiology 9410 688wallet 9410 689parters 9410 690eschew 9410 691quitter 9410 692neat 9410 693Steinberg 9410 694jarring 9410 695tinily 9410 696balled 9410 697persist 9410 698attainments 9410 699fanatic 9410 700measures 9410 701rightfulness 9410 702capably 9410 703impulsive 9410 704starlet 9410 705terminators 9410 706untying 9410 707announces 9410 708featherweight 9410 709pessimist 9410 710daughter 9410 711decliner 9410 712lawgiver 9410 713stated 9410 714readable 9410 715attrition 9410 716cascade 9410 717motors 9410 718interrogate 9410 719pests 9410 720stairway 9410 721dopers 9410 722testicle 9410 723Parsifal 9410 724leavings 9410 725postulation 9410 726squeaking 9410 727contrasted 9410 728leftover 9410 729whiteners 9410 730erases 9410 731Punjab 9410 732Merritt 9410 733Quixotism 9410 734sweetish 9410 735dogging 9410 736scornfully 9410 737bellow 9410 738bills 9410 739cupboard 9410 740sureties 9410 741puddings 9410 742fetters 9410 743bivalves 9410 744incurring 9410 745Adolph 9410 746pithed 9410 747Miles 9410 748trimmings 9410 749tragedies 9410 750skulking 9410 751flint 9410 752flopping 9410 753relaxing 9410 754offload 9410 755suites 9410 756lists 9410 757animized 9410 758multilayer 9410 759standardizes 9410 760Judas 9410 761vacuuming 9410 762dentally 9410 763humanness 9410 764inch 9410 765Weissmuller 9410 766irresponsibly 9410 767luckily 9410 768culled 9410 769medical 9410 770bloodbath 9410 771subschema 9410 772animals 9410 773Micronesia 9410 774repetitions 9410 775Antares 9410 776ventilate 9410 777pityingly 9410 778interdependent 9410 779Graves 9410 780neonatal 9410 781chafe 9410 782honoring 9410 783realtor 9410 784elite 9410 785funereal 9410 786abrogating 9410 787sorters 9410 788Conley 9410 789lectured 9410 790Abraham 9410 791Hawaii 9410 792cage 9410 793hushes 9410 794Simla 9410 795reporters 9410 796Dutchman 9410 797descendants 9410 798groupings 9410 799dissociate 9410 800coexist 9410 801Beebe 9410 802Taoism 9410 803Connally 9410 804fetched 9410 805checkpoints 9410 806rusting 9410 807galling 9410 808obliterates 9410 809traitor 9410 810resumes 9410 811analyzable 9410 812terminator 9410 813gritty 9410 814firearm 9410 815minima 9410 816Selfridge 9410 817disable 9410 818witchcraft 9410 819betroth 9410 820Manhattanize 9410 821imprint 9410 822peeked 9410 823swelling 9410 824interrelationships 9410 825riser 9410 826Gandhian 9410 827peacock 9410 828bee 9410 829kanji 9410 830dental 9410 831scarf 9410 832chasm 9410 833insolence 9410 834syndicate 9410 835alike 9410 836imperial 9410 837convulsion 9410 838railway 9410 839validate 9410 840normalizes 9410 841comprehensive 9410 842chewing 9410 843denizen 9410 844schemer 9410 845chronicle 9410 846Kline 9410 847Anatole 9410 848partridges 9410 849brunch 9410 850recruited 9410 851dimensions 9410 852Chicana 9410 853announced 9410 854praised 9410 855employing 9410 856linear 9410 857quagmire 9410 858western 9410 859relishing 9410 860serving 9410 861scheduling 9410 862lore 9410 863eventful 9410 864arteriole 9410 865disentangle 9410 866cured 9410 867Fenton 9410 868avoidable 9410 869drains 9410 870detectably 9410 871husky 9410 872impelling 9410 873undoes 9410 874evened 9410 875squeezes 9410 876destroyer 9410 877rudeness 9410 878beaner 9410 879boorish 9410 880Everhart 9410 881encompass 9410 882mushrooms 9410 883Alison 9410 884externally 9410 885pellagra 9410 886cult 9410 887creek 9410 888Huffman 9410 889Majorca 9410 890governing 9410 891gadfly 9410 892reassigned 9410 893intentness 9410 894craziness 9410 895psychic 9410 896squabbled 9410 897burlesque 9410 898capped 9410 899extracted 9410 900DiMaggio 9410 901exclamation 9410 902subdirectory 9410 903Gothicism 9410 904feminine 9410 905metaphysically 9410 906sanding 9410 907Miltonism 9410 908freakish 9410 909index 9410 910straight 9410 911flurried 9410 912denotative 9410 913coming 9410 914commencements 9410 915gentleman 9410 916gifted 9410 917Shanghais 9410 918sportswriting 9410 919sloping 9410 920navies 9410 921leaflet 9410 922shooter 9410 923Joplin 9410 924babies 9410 925assails 9410 926admiring 9410 927swaying 9410 928Goldstine 9410 929fitting 9410 930Norwalk 9410 931analogy 9410 932deludes 9410 933cokes 9410 934Clayton 9410 935exhausts 9410 936causality 9410 937sating 9410 938icon 9410 939throttles 9410 940communicants 9410 941dehydrate 9410 942priceless 9410 943publicly 9410 944incidentals 9410 945commonplace 9410 946mumbles 9410 947furthermore 9410 948cautioned 9410 949parametrized 9410 950registration 9410 951sadly 9410 952positioning 9410 953babysitting 9410 954eternal 9410 955hoarder 9410 956congregates 9410 957rains 9410 958workers 9410 959sags 9410 960unplug 9410 961garage 9410 962boulder 9410 963specifics 9410 964Teresa 9410 965Winsett 9410 966convenient 9410 967buckboards 9410 968amenities 9410 969resplendent 9410 970sews 9410 971participated 9410 972Simon 9410 973certificates 9410 974Fitzpatrick 9410 975Evanston 9410 976misted 9410 977textures 9410 978save 9410 979count 9410 980rightful 9410 981chaperone 9410 982Lizzy 9410 983clenched 9410 984effortlessly 9410 985accessed 9410 986beaters 9410 987Hornblower 9410 988vests 9410 989indulgences 9410 990infallibly 9410 991unwilling 9410 992excrete 9410 993spools 9410 994crunches 9410 995overestimating 9410 996ineffective 9410 997humiliation 9410 998sophomore 9410 999star 9410 1000rifles 9410 1001dialysis 9410 1002arriving 9410 1003indulge 9410 1004clockers 9410 1005languages 9410 1006Antarctica 9410 1007percentage 9410 1008ceiling 9410 1009specification 9410 1010regimented 9410 1011ciphers 9410 1012pictures 9410 1013serpents 9410 1014allot 9410 1015realized 9410 1016mayoral 9410 1017opaquely 9410 1018hostess 9410 1019fiftieth 9410 1020incorrectly 9410 1021decomposition 9410 1022stranglings 9410 1023mixture 9410 1024electroencephalography 9410 1025similarities 9410 1026charges 9410 1027freest 9410 1028Greenberg 9410 1029tinting 9410 1030expelled 9410 1031warm 9410 1032smoothed 9410 1033deductions 9410 1034Romano 9410 1035bitterroot 9410 1036corset 9410 1037securing 9410 1038environing 9410 1039cute 9410 1040Crays 9410 1041heiress 9410 1042inform 9410 1043avenge 9410 1044universals 9410 1045Kinsey 9410 1046ravines 9410 1047bestseller 9410 1048equilibrium 9410 1049extents 9410 1050relatively 9410 1051pressure 9410 1052critiques 9410 1053befouled 9410 1054rightfully 9410 1055mechanizing 9410 1056Latinizes 9410 1057timesharing 9410 1058Aden 9410 1059embassies 9410 1060males 9410 1061shapelessly 9410 1062mastering 9410 1063Newtonian 9410 1064finishers 9410 1065abates 9410 1066teem 9410 1067kiting 9410 1068stodgy 9410 1069feed 9410 1070guitars 9410 1071airships 9410 1072store 9410 1073denounces 9410 1074Pyle 9410 1075Saxony 9410 1076serializations 9410 1077Peruvian 9410 1078taxonomically 9410 1079kingdom 9410 1080stint 9410 1081Sault 9410 1082faithful 9410 1083Ganymede 9410 1084tidiness 9410 1085gainful 9410 1086contrary 9410 1087Tipperary 9410 1088tropics 9410 1089theorizers 9410 1090renew 9410 1091already 9410 1092terminal 9410 1093Hegelian 9410 1094hypothesizer 9410 1095warningly 9410 1096journalizing 9410 1097nested 9410 1098Lars 9410 1099saplings 9410 1100foothill 9410 1101labeled 9410 1102imperiously 9410 1103reporters 9410 1104furnishings 9410 1105precipitable 9410 1106discounts 9410 1107excises 9410 1108Stalin 9410 1109despot 9410 1110ripeness 9410 1111Arabia 9410 1112unruly 9410 1113mournfulness 9410 1114boom 9410 1115slaughter 9410 1116Sabine 9410 1117handy 9410 1118rural 9410 1119organizer 9410 1120shipyard 9410 1121civics 9410 1122inaccuracy 9410 1123rules 9410 1124juveniles 9410 1125comprised 9410 1126investigations 9410 1127stabilizes 9410 1128seminaries 9410 1129Hunter 9410 1130sporty 9410 1131test 9410 1132weasels 9410 1133CERN 9410 1134tempering 9410 1135afore 9410 1136Galatean 9410 1137techniques 9410 1138error 9410 1139veranda 9410 1140severely 9410 1141Cassites 9410 1142forthcoming 9410 1143guides 9410 1144vanish 9410 1145lied 9410 1146sawtooth 9410 1147fated 9410 1148gradually 9410 1149widens 9410 1150preclude 9410 1151evenhandedly 9410 1152percentage 9410 1153disobedience 9410 1154humility 9410 1155gleaning 9410 1156petted 9410 1157bloater 9410 1158minion 9410 1159marginal 9410 1160apiary 9410 1161measures 9410 1162precaution 9410 1163repelled 9410 1164primary 9410 1165coverings 9410 1166Artemia 9410 1167navigate 9410 1168spatial 9410 1169Gurkha 9410 1170meanwhile 9410 1171Melinda 9410 1172Butterfield 9410 1173Aldrich 9410 1174previewing 9410 1175glut 9410 1176unaffected 9410 1177inmate 9410 1178mineral 9410 1179impending 9410 1180meditation 9410 1181ideas 9410 1182miniaturizes 9410 1183lewdly 9410 1184title 9410 1185youthfulness 9410 1186creak 9410 1187Chippewa 9410 1188clamored 9410 1189freezes 9410 1190forgivably 9410 1191reduce 9410 1192McGovern 9410 1193Nazis 9410 1194epistle 9410 1195socializes 9410 1196conceptions 9410 1197Kevin 9410 1198uncovering 9410 1199chews 9410 1200appendixes 9410 1201appendixes 9410 1202appendixes 9410 1203appendixes 9410 1204appendixes 9410 1205appendixes 9410 1206raining 9410 1207infest 9410 1208compartment 9410 1209minting 9410 1210ducks 9410 1211roped 9410 1212waltz 9410 1213Lillian 9410 1214repressions 9410 1215chillingly 9410 1216noncritical 9410 1217lithograph 9410 1218spongers 9410 1219parenthood 9410 1220posed 9410 1221instruments 9410 1222filial 9410 1223fixedly 9410 1224relives 9410 1225Pandora 9410 1226watering 9410 1227ungrateful 9410 1228secures 9410 1229poison 9410 1230dusted 9410 1231encompasses 9410 1232presentation 9410 1233Kantian 9410 1234select 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; 1235fld3 period price price2 1236admonishing 1002 28357832 8723648 1237analyzable 1002 28357832 8723648 1238annihilates 1001 5987435 234724 1239Antares 1002 28357832 8723648 1240astound 1001 5987435 234724 1241audiology 1001 5987435 234724 1242Augustine 1002 28357832 8723648 1243Baird 1002 28357832 8723648 1244bewilderingly 1001 5987435 234724 1245breaking 1001 5987435 234724 1246Conley 1001 5987435 234724 1247dentally 1002 28357832 8723648 1248dissociate 1002 28357832 8723648 1249elite 1001 5987435 234724 1250eschew 1001 5987435 234724 1251Eulerian 1001 5987435 234724 1252flanking 1001 5987435 234724 1253foldout 1002 28357832 8723648 1254funereal 1002 28357832 8723648 1255galling 1002 28357832 8723648 1256Graves 1001 5987435 234724 1257grazing 1001 5987435 234724 1258groupings 1001 5987435 234724 1259handgun 1001 5987435 234724 1260humility 1002 28357832 8723648 1261impulsive 1002 28357832 8723648 1262inch 1001 5987435 234724 1263intelligibility 1001 5987435 234724 1264jarring 1001 5987435 234724 1265lawgiver 1001 5987435 234724 1266lectured 1002 28357832 8723648 1267Merritt 1002 28357832 8723648 1268neonatal 1001 5987435 234724 1269offload 1002 28357832 8723648 1270parters 1002 28357832 8723648 1271pityingly 1002 28357832 8723648 1272puddings 1002 28357832 8723648 1273Punjab 1001 5987435 234724 1274quitter 1002 28357832 8723648 1275realtor 1001 5987435 234724 1276relaxing 1001 5987435 234724 1277repetitions 1001 5987435 234724 1278resumes 1001 5987435 234724 1279Romans 1002 28357832 8723648 1280rusting 1001 5987435 234724 1281scholastics 1001 5987435 234724 1282skulking 1002 28357832 8723648 1283stated 1002 28357832 8723648 1284suites 1002 28357832 8723648 1285sureties 1001 5987435 234724 1286testicle 1002 28357832 8723648 1287tinily 1002 28357832 8723648 1288tragedies 1001 5987435 234724 1289trimmings 1001 5987435 234724 1290vacuuming 1001 5987435 234724 1291ventilate 1001 5987435 234724 1292wallet 1001 5987435 234724 1293Weissmuller 1002 28357832 8723648 1294Wotan 1002 28357832 8723648 1295select 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; 1296fld1 fld3 period price price2 1297018201 relaxing 1001 5987435 234724 1298018601 vacuuming 1001 5987435 234724 1299018801 inch 1001 5987435 234724 1300018811 repetitions 1001 5987435 234724 1301create table t4 ( 1302companynr tinyint(2) unsigned zerofill NOT NULL default '00', 1303companyname char(30) NOT NULL default '', 1304PRIMARY KEY (companynr), 1305UNIQUE KEY companyname(companyname) 1306) ENGINE=MyISAM MAX_ROWS=50 PACK_KEYS=1 COMMENT='companynames'; 1307select STRAIGHT_JOIN t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr; 1308companynr companyname 130900 Unknown 131029 company 1 131134 company 2 131236 company 3 131337 company 4 131440 company 5 131541 company 6 131650 company 11 131753 company 7 131858 company 8 131965 company 9 132068 company 10 1321select SQL_SMALL_RESULT t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr; 1322companynr companyname 132300 Unknown 132429 company 1 132534 company 2 132636 company 3 132737 company 4 132840 company 5 132941 company 6 133050 company 11 133153 company 7 133258 company 8 133365 company 9 133468 company 10 1335select * from t1,t1 t12; 1336Period Varor_period Period Varor_period 13379410 9412 9410 9412 1338select t2.fld1,t22.fld1 from t2,t2 t22 where t2.fld1 >= 250501 and t2.fld1 <= 250505 and t22.fld1 >= 250501 and t22.fld1 <= 250505; 1339fld1 fld1 1340250501 250501 1341250502 250501 1342250503 250501 1343250504 250501 1344250505 250501 1345250501 250502 1346250502 250502 1347250503 250502 1348250504 250502 1349250505 250502 1350250501 250503 1351250502 250503 1352250503 250503 1353250504 250503 1354250505 250503 1355250501 250504 1356250502 250504 1357250503 250504 1358250504 250504 1359250505 250504 1360250501 250505 1361250502 250505 1362250503 250505 1363250504 250505 1364250505 250505 1365insert into t2 (fld1, companynr) values (999999,99); 1366select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null; 1367companynr companyname 136899 NULL 1369select count(*) from t2 left join t4 using (companynr) where t4.companynr is not null; 1370count(*) 13711199 1372explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null; 1373id select_type table type possible_keys key key_len ref rows Extra 13741 SIMPLE t2 ALL NULL NULL NULL NULL 1200 13751 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 Using where; Not exists; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 1376explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null; 1377id select_type table type possible_keys key key_len ref rows Extra 13781 SIMPLE t4 ALL NULL NULL NULL NULL 12 13791 SIMPLE t2 hash_ALL NULL #hash#$hj 1 test.t4.companynr 1200 Using where; Not exists; Using join buffer (flat, BNLH join) 1380select companynr,companyname from t2 left join t4 using (companynr) where companynr is null; 1381companynr companyname 1382select count(*) from t2 left join t4 using (companynr) where companynr is not null; 1383count(*) 13841200 1385explain select companynr,companyname from t2 left join t4 using (companynr) where companynr is null; 1386id select_type table type possible_keys key key_len ref rows Extra 13871 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 1388explain select companynr,companyname from t4 left join t2 using (companynr) where companynr is null; 1389id select_type table type possible_keys key key_len ref rows Extra 13901 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 1391delete from t2 where fld1=999999; 1392explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0; 1393id select_type table type possible_keys key key_len ref rows Extra 13941 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 13951 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 1396explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0; 1397id select_type table type possible_keys key key_len ref rows Extra 13981 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 13991 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 1400explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0; 1401id select_type table type possible_keys key key_len ref rows Extra 14021 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where 14031 SIMPLE t4 eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 1404explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0; 1405id select_type table type possible_keys key key_len ref rows Extra 14061 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where 14071 SIMPLE t2 hash_ALL NULL #hash#$hj 1 test.t4.companynr 1199 Using where; Using join buffer (flat, BNLH join) 1408explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0; 1409id select_type table type possible_keys key key_len ref rows Extra 14101 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where 14111 SIMPLE t2 hash_ALL NULL #hash#$hj 1 test.t4.companynr 1199 Using where; Using join buffer (flat, BNLH join) 1412explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0; 1413id select_type table type possible_keys key key_len ref rows Extra 14141 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where 14151 SIMPLE t2 hash_ALL NULL #hash#$hj 1 test.t4.companynr 1199 Using where; Using join buffer (flat, BNLH join) 1416explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null; 1417id select_type table type possible_keys key key_len ref rows Extra 14181 SIMPLE t4 ALL NULL NULL NULL NULL 12 14191 SIMPLE t2 hash_ALL NULL #hash#$hj 1 test.t4.companynr 1199 Using where; Using join buffer (flat, BNLH join) 1420explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0 or t4.companynr > 0; 1421id select_type table type possible_keys key key_len ref rows Extra 14221 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 14231 SIMPLE t2 hash_ALL NULL #hash#$hj 1 test.t4.companynr 1199 Using where; Using join buffer (flat, BNLH join) 1424explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0; 1425id select_type table type possible_keys key key_len ref rows Extra 14261 SIMPLE t4 ALL NULL NULL NULL NULL 12 14271 SIMPLE t2 hash_ALL NULL #hash#$hj 1 test.t4.companynr 1199 Using where; Using join buffer (flat, BNLH join) 1428explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null; 1429id select_type table type possible_keys key key_len ref rows Extra 14301 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where 14311 SIMPLE t2 hash_ALL NULL #hash#$hj 1 test.t4.companynr 1199 Using where; Using join buffer (flat, BNLH join) 1432explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0; 1433id select_type table type possible_keys key key_len ref rows Extra 14341 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where 14351 SIMPLE t2 hash_ALL NULL #hash#$hj 1 test.t4.companynr 1199 Using where; Using join buffer (flat, BNLH join) 1436explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0; 1437id select_type table type possible_keys key key_len ref rows Extra 14381 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where 14391 SIMPLE t2 hash_ALL NULL #hash#$hj 1 test.t4.companynr 1199 Using where; Using join buffer (flat, BNLH join) 1440select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; 1441companynr companynr 144237 36 144341 40 1444explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; 1445id select_type table type possible_keys key key_len ref rows Extra 14461 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using index; Using temporary 14471 SIMPLE t2 hash_ALL NULL #hash#$hj 1 func 1199 Using where; Using join buffer (flat, BNLH join) 1448select 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; 1449fld1 companynr fld3 period 1450038008 37 reporters 1008 1451038208 37 Selfridge 1008 1452select 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; 1453fld1 companynr fld3 period 1454038008 37 reporters 1008 1455038208 37 Selfridge 1008 1456select 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; 1457fld1 companynr fld3 period 1458038008 37 reporters 1008 1459038208 37 Selfridge 1008 1460select 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); 1461period 14629410 1463select 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))); 1464period 14659410 1466select 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; 1467fld1 1468250501 1469250502 1470250503 1471250505 1472select fld1 from t2 where fld1 in (250502,98005,98006,250503,250605,250606) and fld1 >=250502 and fld1 not in (250605,250606); 1473fld1 1474250502 1475250503 1476select fld1 from t2 where fld1 between 250502 and 250504; 1477fld1 1478250502 1479250503 1480250504 1481select fld3 from t2 where (((fld3 like "_%L%" ) or (fld3 like "%ok%")) and ( fld3 like "L%" or fld3 like "G%")) and fld3 like "L%" ; 1482fld3 1483label 1484labeled 1485labeled 1486landslide 1487laterally 1488leaflet 1489lewdly 1490Lillian 1491luckily 1492select count(*) from t1; 1493count(*) 14941 1495select companynr,count(*),sum(fld1) from t2 group by companynr; 1496companynr count(*) sum(fld1) 149700 82 10355753 149829 95 14473298 149934 70 17788966 150036 215 22786296 150137 588 83602098 150240 37 6618386 150341 52 12816335 150450 11 1595438 150553 4 793210 150658 23 2254293 150765 10 2284055 150868 12 3097288 1509select companynr,count(*) from t2 group by companynr order by companynr desc limit 5; 1510companynr count(*) 151168 12 151265 10 151358 23 151453 4 151550 11 1516select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>""; 1517count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1) 151870 absentee vest 17788966 254128.0857 3272.5940 10709871.3069 1519explain extended select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>""; 1520id select_type table type possible_keys key key_len ref rows filtered Extra 15211 SIMPLE t2 ALL NULL NULL NULL NULL 1199 100.00 Using where 1522Warnings: 1523Note 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` <> '' 1524select companynr,count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 group by companynr limit 3; 1525companynr count(*) min(fld4) max(fld4) sum(fld1) avg(fld1) std(fld1) variance(fld1) 152600 82 Anthony windmills 10355753 126289.6707 115550.9757 13352027981.7087 152729 95 abut wetness 14473298 152350.5053 8368.5480 70032594.9026 152834 70 absentee vest 17788966 254128.0857 3272.5940 10709871.3069 1529select companynr,t2nr,count(price),sum(price),min(price),max(price),avg(price) from t3 where companynr = 37 group by companynr,t2nr limit 10; 1530companynr t2nr count(price) sum(price) min(price) max(price) avg(price) 153137 1 1 5987435 5987435 5987435 5987435.0000 153237 2 1 28357832 28357832 28357832 28357832.0000 153337 3 1 39654943 39654943 39654943 39654943.0000 153437 11 1 5987435 5987435 5987435 5987435.0000 153537 12 1 28357832 28357832 28357832 28357832.0000 153637 13 1 39654943 39654943 39654943 39654943.0000 153737 21 1 5987435 5987435 5987435 5987435.0000 153837 22 1 28357832 28357832 28357832 28357832.0000 153937 23 1 39654943 39654943 39654943 39654943.0000 154037 31 1 5987435 5987435 5987435 5987435.0000 1541select /*! 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; 1542companynr t2nr count(price) sum(price) min(price) max(price) avg(price) 154337 1 1 5987435 5987435 5987435 5987435.0000 154437 2 1 28357832 28357832 28357832 28357832.0000 154537 3 1 39654943 39654943 39654943 39654943.0000 154637 11 1 5987435 5987435 5987435 5987435.0000 154737 12 1 28357832 28357832 28357832 28357832.0000 154837 13 1 39654943 39654943 39654943 39654943.0000 154937 21 1 5987435 5987435 5987435 5987435.0000 155037 22 1 28357832 28357832 28357832 28357832.0000 155137 23 1 39654943 39654943 39654943 39654943.0000 155237 31 1 5987435 5987435 5987435 5987435.0000 1553select companynr,count(price),sum(price),min(price),max(price),avg(price) from t3 group by companynr ; 1554companynr count(price) sum(price) min(price) max(price) avg(price) 155537 12543 309394878010 5987435 39654943 24666736.6667 155678 8362 414611089292 726498 98439034 49582766.0000 1557101 4181 3489454238 834598 834598 834598.0000 1558154 4181 4112197254950 983543950 983543950 983543950.0000 1559311 4181 979599938 234298 234298 234298.0000 1560447 4181 9929180954 2374834 2374834 2374834.0000 1561512 4181 3288532102 786542 786542 786542.0000 1562select distinct mod(companynr,10) from t4 group by companynr; 1563mod(companynr,10) 15640 15659 15664 15676 15687 15691 15703 15718 15725 1573select distinct 1 from t4 group by companynr; 15741 15751 1576select count(distinct fld1) from t2; 1577count(distinct fld1) 15781199 1579select companynr,count(distinct fld1) from t2 group by companynr; 1580companynr count(distinct fld1) 158100 82 158229 95 158334 70 158436 215 158537 588 158640 37 158741 52 158850 11 158953 4 159058 23 159165 10 159268 12 1593select companynr,count(*) from t2 group by companynr; 1594companynr count(*) 159500 82 159629 95 159734 70 159836 215 159937 588 160040 37 160141 52 160250 11 160353 4 160458 23 160565 10 160668 12 1607select companynr,count(distinct concat(fld1,repeat(65,1000))) from t2 group by companynr; 1608companynr count(distinct concat(fld1,repeat(65,1000))) 160900 82 161029 95 161134 70 161236 215 161337 588 161440 37 161541 52 161650 11 161753 4 161858 23 161965 10 162068 12 1621select companynr,count(distinct concat(fld1,repeat(65,200))) from t2 group by companynr; 1622companynr count(distinct concat(fld1,repeat(65,200))) 162300 82 162429 95 162534 70 162636 215 162737 588 162840 37 162941 52 163050 11 163153 4 163258 23 163365 10 163468 12 1635select companynr,count(distinct floor(fld1/100)) from t2 group by companynr; 1636companynr count(distinct floor(fld1/100)) 163700 47 163829 35 163934 14 164036 69 164137 108 164240 16 164341 11 164450 9 164553 1 164658 1 164765 1 164868 1 1649select companynr,count(distinct concat(repeat(65,1000),floor(fld1/100))) from t2 group by companynr; 1650companynr count(distinct concat(repeat(65,1000),floor(fld1/100))) 165100 47 165229 35 165334 14 165436 69 165537 108 165640 16 165741 11 165850 9 165953 1 166058 1 166165 1 166268 1 1663select sum(fld1),fld3 from t2 where fld3="Romans" group by fld1 limit 10; 1664sum(fld1) fld3 166511402 Romans 1666select name,count(*) from t3 where name='cloakroom' group by name; 1667name count(*) 1668cloakroom 4181 1669select name,count(*) from t3 where name='cloakroom' and price>10 group by name; 1670name count(*) 1671cloakroom 4181 1672select count(*) from t3 where name='cloakroom' and price2=823742; 1673count(*) 16744181 1675select name,count(*) from t3 where name='cloakroom' and price2=823742 group by name; 1676name count(*) 1677cloakroom 4181 1678select name,count(*) from t3 where name >= "extramarital" and price <= 39654943 group by name; 1679name count(*) 1680extramarital 4181 1681gazer 4181 1682gems 4181 1683Iranizes 4181 1684spates 4181 1685tucked 4181 1686violinist 4181 1687select t2.fld3,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name; 1688fld3 count(*) 1689spates 4181 1690select companynr|0,companyname from t4 group by 1; 1691companynr|0 companyname 16920 Unknown 169329 company 1 169434 company 2 169536 company 3 169637 company 4 169740 company 5 169841 company 6 169950 company 11 170053 company 7 170158 company 8 170265 company 9 170368 company 10 1704select t2.companynr,companyname,count(*) from t2,t4 where t2.companynr=t4.companynr group by t2.companynr order by companyname; 1705companynr companyname count(*) 170629 company 1 95 170768 company 10 12 170850 company 11 11 170934 company 2 70 171036 company 3 215 171137 company 4 588 171240 company 5 37 171341 company 6 52 171453 company 7 4 171558 company 8 23 171665 company 9 10 171700 Unknown 82 1718select t2.fld1,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name; 1719fld1 count(*) 1720158402 4181 1721select sum(Period)/count(*) from t1; 1722sum(Period)/count(*) 17239410.0000 1724select 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; 1725companynr count sum diff func 172637 12543 309394878010 0.0000 464091 172778 8362 414611089292 0.0000 652236 1728101 4181 3489454238 0.0000 422281 1729154 4181 4112197254950 0.0000 643874 1730311 4181 979599938 0.0000 1300291 1731447 4181 9929180954 0.0000 1868907 1732512 4181 3288532102 0.0000 2140672 1733select companynr,sum(price)/count(price) as avg from t3 group by companynr having avg > 70000000 order by avg; 1734companynr avg 1735154 983543950.0000 1736select companynr,count(*) from t2 group by companynr order by 2 desc; 1737companynr count(*) 173837 588 173936 215 174029 95 174100 82 174234 70 174341 52 174440 37 174558 23 174668 12 174750 11 174865 10 174953 4 1750select companynr,count(*) from t2 where companynr > 40 group by companynr order by 2 desc; 1751companynr count(*) 175241 52 175358 23 175468 12 175550 11 175665 10 175753 4 1758select 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; 1759fld4 fld1 count(price) sum(price) min(price) max(price) avg(price) 1760teethe 000001 1 5987435 5987435 5987435 5987435.0000 1761dreaded 011401 1 5987435 5987435 5987435 5987435.0000 1762scholastics 011402 1 28357832 28357832 28357832 28357832.0000 1763audiology 011403 1 39654943 39654943 39654943 39654943.0000 1764wallet 011501 1 5987435 5987435 5987435 5987435.0000 1765parters 011701 1 5987435 5987435 5987435 5987435.0000 1766eschew 011702 1 28357832 28357832 28357832 28357832.0000 1767quitter 011703 1 39654943 39654943 39654943 39654943.0000 1768neat 012001 1 5987435 5987435 5987435 5987435.0000 1769Steinberg 012003 1 39654943 39654943 39654943 39654943.0000 1770balled 012301 1 5987435 5987435 5987435 5987435.0000 1771persist 012302 1 28357832 28357832 28357832 28357832.0000 1772attainments 012303 1 39654943 39654943 39654943 39654943.0000 1773capably 012501 1 5987435 5987435 5987435 5987435.0000 1774impulsive 012602 1 28357832 28357832 28357832 28357832.0000 1775starlet 012603 1 39654943 39654943 39654943 39654943.0000 1776featherweight 012701 1 5987435 5987435 5987435 5987435.0000 1777pessimist 012702 1 28357832 28357832 28357832 28357832.0000 1778daughter 012703 1 39654943 39654943 39654943 39654943.0000 1779lawgiver 013601 1 5987435 5987435 5987435 5987435.0000 1780stated 013602 1 28357832 28357832 28357832 28357832.0000 1781readable 013603 1 39654943 39654943 39654943 39654943.0000 1782testicle 013801 1 5987435 5987435 5987435 5987435.0000 1783Parsifal 013802 1 28357832 28357832 28357832 28357832.0000 1784leavings 013803 1 39654943 39654943 39654943 39654943.0000 1785squeaking 013901 1 5987435 5987435 5987435 5987435.0000 1786contrasted 016001 1 5987435 5987435 5987435 5987435.0000 1787leftover 016201 1 5987435 5987435 5987435 5987435.0000 1788whiteners 016202 1 28357832 28357832 28357832 28357832.0000 1789erases 016301 1 5987435 5987435 5987435 5987435.0000 1790Punjab 016302 1 28357832 28357832 28357832 28357832.0000 1791Merritt 016303 1 39654943 39654943 39654943 39654943.0000 1792sweetish 018001 1 5987435 5987435 5987435 5987435.0000 1793dogging 018002 1 28357832 28357832 28357832 28357832.0000 1794scornfully 018003 1 39654943 39654943 39654943 39654943.0000 1795fetters 018012 1 28357832 28357832 28357832 28357832.0000 1796bivalves 018013 1 39654943 39654943 39654943 39654943.0000 1797skulking 018021 1 5987435 5987435 5987435 5987435.0000 1798flint 018022 1 28357832 28357832 28357832 28357832.0000 1799flopping 018023 1 39654943 39654943 39654943 39654943.0000 1800Judas 018032 1 28357832 28357832 28357832 28357832.0000 1801vacuuming 018033 1 39654943 39654943 39654943 39654943.0000 1802medical 018041 1 5987435 5987435 5987435 5987435.0000 1803bloodbath 018042 1 28357832 28357832 28357832 28357832.0000 1804subschema 018043 1 39654943 39654943 39654943 39654943.0000 1805interdependent 018051 1 5987435 5987435 5987435 5987435.0000 1806Graves 018052 1 28357832 28357832 28357832 28357832.0000 1807neonatal 018053 1 39654943 39654943 39654943 39654943.0000 1808sorters 018061 1 5987435 5987435 5987435 5987435.0000 1809epistle 018062 1 28357832 28357832 28357832 28357832.0000 1810Conley 018101 1 5987435 5987435 5987435 5987435.0000 1811lectured 018102 1 28357832 28357832 28357832 28357832.0000 1812Abraham 018103 1 39654943 39654943 39654943 39654943.0000 1813cage 018201 1 5987435 5987435 5987435 5987435.0000 1814hushes 018202 1 28357832 28357832 28357832 28357832.0000 1815Simla 018402 1 28357832 28357832 28357832 28357832.0000 1816reporters 018403 1 39654943 39654943 39654943 39654943.0000 1817coexist 018601 1 5987435 5987435 5987435 5987435.0000 1818Beebe 018602 1 28357832 28357832 28357832 28357832.0000 1819Taoism 018603 1 39654943 39654943 39654943 39654943.0000 1820Connally 018801 1 5987435 5987435 5987435 5987435.0000 1821fetched 018802 1 28357832 28357832 28357832 28357832.0000 1822checkpoints 018803 1 39654943 39654943 39654943 39654943.0000 1823gritty 018811 1 5987435 5987435 5987435 5987435.0000 1824firearm 018812 1 28357832 28357832 28357832 28357832.0000 1825minima 019101 1 5987435 5987435 5987435 5987435.0000 1826Selfridge 019102 1 28357832 28357832 28357832 28357832.0000 1827disable 019103 1 39654943 39654943 39654943 39654943.0000 1828witchcraft 019201 1 5987435 5987435 5987435 5987435.0000 1829betroth 030501 1 5987435 5987435 5987435 5987435.0000 1830Manhattanize 030502 1 28357832 28357832 28357832 28357832.0000 1831imprint 030503 1 39654943 39654943 39654943 39654943.0000 1832swelling 031901 1 5987435 5987435 5987435 5987435.0000 1833interrelationships 036001 1 5987435 5987435 5987435 5987435.0000 1834riser 036002 1 28357832 28357832 28357832 28357832.0000 1835bee 038001 1 5987435 5987435 5987435 5987435.0000 1836kanji 038002 1 28357832 28357832 28357832 28357832.0000 1837dental 038003 1 39654943 39654943 39654943 39654943.0000 1838railway 038011 1 5987435 5987435 5987435 5987435.0000 1839validate 038012 1 28357832 28357832 28357832 28357832.0000 1840normalizes 038013 1 39654943 39654943 39654943 39654943.0000 1841Kline 038101 1 5987435 5987435 5987435 5987435.0000 1842Anatole 038102 1 28357832 28357832 28357832 28357832.0000 1843partridges 038103 1 39654943 39654943 39654943 39654943.0000 1844recruited 038201 1 5987435 5987435 5987435 5987435.0000 1845dimensions 038202 1 28357832 28357832 28357832 28357832.0000 1846Chicana 038203 1 39654943 39654943 39654943 39654943.0000 1847select t3.companynr,fld3,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 512 group by companynr,fld3; 1848companynr fld3 sum(price) 1849512 boat 786542 1850512 capably 786542 1851512 cupboard 786542 1852512 decliner 786542 1853512 descendants 786542 1854512 dopers 786542 1855512 erases 786542 1856512 Micronesia 786542 1857512 Miles 786542 1858512 skies 786542 1859select 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; 1860companynr count(*) min(fld3) max(fld3) sum(price) avg(price) 186100 1 Omaha Omaha 5987435 5987435.0000 186236 1 dubbed dubbed 28357832 28357832.0000 186337 83 Abraham Wotan 1908978016 22999735.1325 186450 2 scribbled tapestry 68012775 34006387.5000 1865select 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; 1866t3.companynr+0 t2nr fld3 sum(price) 186737 1 Omaha 5987435 186837 11401 breaking 5987435 186937 11402 Romans 28357832 187037 11403 intercepted 39654943 187137 11501 bewilderingly 5987435 187237 11701 astound 5987435 187337 11702 admonishing 28357832 187437 11703 sumac 39654943 187537 12001 flanking 5987435 187637 12003 combed 39654943 187737 12301 Eulerian 5987435 187837 12302 dubbed 28357832 187937 12303 Kane 39654943 188037 12501 annihilates 5987435 188137 12602 Wotan 28357832 188237 12603 snatching 39654943 188337 12701 grazing 5987435 188437 12702 Baird 28357832 188537 12703 celery 39654943 188637 13601 handgun 5987435 188737 13602 foldout 28357832 188837 13603 mystic 39654943 188937 13801 intelligibility 5987435 189037 13802 Augustine 28357832 189137 13803 teethe 39654943 189237 13901 scholastics 5987435 189337 16001 audiology 5987435 189437 16201 wallet 5987435 189537 16202 parters 28357832 189637 16301 eschew 5987435 189737 16302 quitter 28357832 189837 16303 neat 39654943 189937 18001 jarring 5987435 190037 18002 tinily 28357832 190137 18003 balled 39654943 190237 18012 impulsive 28357832 190337 18013 starlet 39654943 190437 18021 lawgiver 5987435 190537 18022 stated 28357832 190637 18023 readable 39654943 190737 18032 testicle 28357832 190837 18033 Parsifal 39654943 190937 18041 Punjab 5987435 191037 18042 Merritt 28357832 191137 18043 Quixotism 39654943 191237 18051 sureties 5987435 191337 18052 puddings 28357832 191437 18053 tapestry 39654943 191537 18061 trimmings 5987435 191637 18062 humility 28357832 191737 18101 tragedies 5987435 191837 18102 skulking 28357832 191937 18103 flint 39654943 192037 18201 relaxing 5987435 192137 18202 offload 28357832 192237 18402 suites 28357832 192337 18403 lists 39654943 192437 18601 vacuuming 5987435 192537 18602 dentally 28357832 192637 18603 humanness 39654943 192737 18801 inch 5987435 192837 18802 Weissmuller 28357832 192937 18803 irresponsibly 39654943 193037 18811 repetitions 5987435 193137 18812 Antares 28357832 193237 19101 ventilate 5987435 193337 19102 pityingly 28357832 193437 19103 interdependent 39654943 193537 19201 Graves 5987435 193637 30501 neonatal 5987435 193737 30502 scribbled 28357832 193837 30503 chafe 39654943 193937 31901 realtor 5987435 194037 36001 elite 5987435 194137 36002 funereal 28357832 194237 38001 Conley 5987435 194337 38002 lectured 28357832 194437 38003 Abraham 39654943 194537 38011 groupings 5987435 194637 38012 dissociate 28357832 194737 38013 coexist 39654943 194837 38101 rusting 5987435 194937 38102 galling 28357832 195037 38103 obliterates 39654943 195137 38201 resumes 5987435 195237 38202 analyzable 28357832 195337 38203 terminator 39654943 1954select 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; 1955sum(price) 1956234298 1957select 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; 1958fld1 sum(price) 1959038008 234298 1960explain select fld3 from t2 where 1>2 or 2>3; 1961id select_type table type possible_keys key key_len ref rows Extra 19621 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 1963explain select fld3 from t2 where fld1=fld1; 1964id select_type table type possible_keys key key_len ref rows Extra 19651 SIMPLE t2 ALL NULL NULL NULL NULL 1199 1966select companynr,fld1 from t2 HAVING fld1=250501 or fld1=250502; 1967companynr fld1 196834 250501 196934 250502 1970select companynr,fld1 from t2 WHERE fld1>=250501 HAVING fld1<=250502; 1971companynr fld1 197234 250501 197334 250502 1974select companynr,count(*) as count,sum(fld1) as sum from t2 group by companynr having count > 40 and sum/count >= 120000; 1975companynr count sum 197600 82 10355753 197729 95 14473298 197834 70 17788966 197937 588 83602098 198041 52 12816335 1981select companynr from t2 group by companynr having count(*) > 40 and sum(fld1)/count(*) >= 120000 ; 1982companynr 198300 198429 198534 198637 198741 1988select t2.companynr,companyname,count(*) from t2,t4 where t2.companynr=t4.companynr group by companyname having t2.companynr >= 40; 1989companynr companyname count(*) 199068 company 10 12 199150 company 11 11 199240 company 5 37 199341 company 6 52 199453 company 7 4 199558 company 8 23 199665 company 9 10 1997select count(*) from t2; 1998count(*) 19991199 2000select count(*) from t2 where fld1 < 098024; 2001count(*) 2002387 2003select min(fld1) from t2 where fld1>= 098024; 2004min(fld1) 200598024 2006select max(fld1) from t2 where fld1>= 098024; 2007max(fld1) 20081232609 2009select count(*) from t3 where price2=76234234; 2010count(*) 20114181 2012select count(*) from t3 where companynr=512 and price2=76234234; 2013count(*) 20144181 2015explain select min(fld1),max(fld1),count(*) from t2; 2016id select_type table type possible_keys key key_len ref rows Extra 20171 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2018select min(fld1),max(fld1),count(*) from t2; 2019min(fld1) max(fld1) count(*) 20200 1232609 1199 2021select min(t2nr),max(t2nr) from t3 where t2nr=2115 and price2=823742; 2022min(t2nr) max(t2nr) 20232115 2115 2024select count(*),min(t2nr),max(t2nr) from t3 where name='spates' and companynr=78; 2025count(*) min(t2nr) max(t2nr) 20264181 4 41804 2027select t2nr,count(*) from t3 where name='gems' group by t2nr limit 20; 2028t2nr count(*) 20299 1 203019 1 203129 1 203239 1 203349 1 203459 1 203569 1 203679 1 203789 1 203899 1 2039109 1 2040119 1 2041129 1 2042139 1 2043149 1 2044159 1 2045169 1 2046179 1 2047189 1 2048199 1 2049select max(t2nr) from t3 where price=983543950; 2050max(t2nr) 205141807 2052select t1.period from t3 = t1 limit 1; 2053period 20541001 2055select t1.period from t1 as t1 limit 1; 2056period 20579410 2058select t1.period as "Nuvarande period" from t1 as t1 limit 1; 2059Nuvarande period 20609410 2061select period as ok_period from t1 limit 1; 2062ok_period 20639410 2064select period as ok_period from t1 group by ok_period limit 1; 2065ok_period 20669410 2067select 1+1 as summa from t1 group by summa limit 1; 2068summa 20692 2070select period as "Nuvarande period" from t1 group by "Nuvarande period" limit 1; 2071Nuvarande period 20729410 2073show tables; 2074Tables_in_test 2075t1 2076t2 2077t3 2078t4 2079show tables from test like "s%"; 2080Tables_in_test (s%) 2081show tables from test like "t?"; 2082Tables_in_test (t?) 2083show full columns from t2; 2084Field Type Collation Null Key Default Extra Privileges Comment 2085auto int(11) NULL NO PRI NULL auto_increment # 2086fld1 int(6) unsigned zerofill NULL NO UNI 000000 # 2087companynr tinyint(2) unsigned zerofill NULL NO 00 # 2088fld3 char(30) latin1_swedish_ci NO MUL # 2089fld4 char(35) latin1_swedish_ci NO # 2090fld5 char(35) latin1_swedish_ci NO # 2091fld6 char(4) latin1_swedish_ci NO # 2092show full columns from t2 from test like 'f%'; 2093Field Type Collation Null Key Default Extra Privileges Comment 2094fld1 int(6) unsigned zerofill NULL NO UNI 000000 # 2095fld3 char(30) latin1_swedish_ci NO MUL # 2096fld4 char(35) latin1_swedish_ci NO # 2097fld5 char(35) latin1_swedish_ci NO # 2098fld6 char(4) latin1_swedish_ci NO # 2099show full columns from t2 from test like 's%'; 2100Field Type Collation Null Key Default Extra Privileges Comment 2101show keys from t2; 2102Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 2103t2 0 PRIMARY 1 auto A 1199 NULL NULL BTREE 2104t2 0 fld1 1 fld1 A 1199 NULL NULL BTREE 2105t2 1 fld3 1 fld3 A NULL NULL NULL BTREE 2106drop table t4, t3, t2, t1; 2107DO 1; 2108DO benchmark(100,1+1),1,1; 2109do default; 2110ERROR 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 2111do foobar; 2112ERROR 42S22: Unknown column 'foobar' in 'field list' 2113CREATE TABLE t1 ( 2114id mediumint(8) unsigned NOT NULL auto_increment, 2115pseudo varchar(35) NOT NULL default '', 2116PRIMARY KEY (id), 2117UNIQUE KEY pseudo (pseudo) 2118); 2119INSERT INTO t1 (pseudo) VALUES ('test'); 2120INSERT INTO t1 (pseudo) VALUES ('test1'); 2121SELECT 1 as rnd1 from t1 where rand() > 2; 2122rnd1 2123DROP TABLE t1; 2124CREATE 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; 2125INSERT 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); 2126CREATE 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; 2127INSERT INTO t2 VALUES (1,3,10,'2002-06-01 08:00:00',35),(1,3,1010,'2002-06-01 12:00:01',35); 2128SELECT 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; 2129gvid the_success the_fail the_size the_time 2130Warnings: 2131Warning 1292 Truncated incorrect datetime value: 'wrong-date-value' 2132Warning 1292 Truncated incorrect datetime value: 'wrong-date-value' 2133SELECT 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; 2134gvid the_success the_fail the_size the_time 2135DROP TABLE t1,t2; 2136create 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'); 2137INSERT INTO t1 VALUES (102935998719055004,'brade',1029359987,2,102935229116544068,102935229216544093); 2138select wss_type from t1 where wss_type ='102935229216544106'; 2139wss_type 2140select wss_type from t1 where wss_type ='102935229216544105'; 2141wss_type 2142select wss_type from t1 where wss_type ='102935229216544104'; 2143wss_type 2144select wss_type from t1 where wss_type ='102935229216544093'; 2145wss_type 2146102935229216544093 2147select wss_type from t1 where wss_type =102935229216544093; 2148wss_type 2149102935229216544093 2150drop table t1; 2151select 1+2,"aaaa",3.13*2.0 into @a,@b,@c; 2152select @a; 2153@a 21543 2155select @b; 2156@b 2157aaaa 2158select @c; 2159@c 21606.260 2161create table t1 (a int not null auto_increment primary key); 2162insert into t1 values (); 2163insert into t1 values (); 2164insert into t1 values (); 2165select * from (t1 as t2 left join t1 as t3 using (a)), t1; 2166a a 21671 1 21682 1 21693 1 21701 2 21712 2 21723 2 21731 3 21742 3 21753 3 2176select * from t1, (t1 as t2 left join t1 as t3 using (a)); 2177a a 21781 1 21792 1 21803 1 21811 2 21822 2 21833 2 21841 3 21852 3 21863 3 2187select * from (t1 as t2 left join t1 as t3 using (a)) straight_join t1; 2188a a 21891 1 21902 1 21913 1 21921 2 21932 2 21943 2 21951 3 21962 3 21973 3 2198select * from t1 straight_join (t1 as t2 left join t1 as t3 using (a)); 2199a a 22001 1 22012 1 22023 1 22031 2 22042 2 22053 2 22061 3 22072 3 22083 3 2209select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 on t1.a>1; 2210a a 22111 2 22121 3 22132 2 22142 3 22153 2 22163 3 2217select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1; 2218a a 22192 1 22203 1 22212 2 22223 2 22232 3 22243 3 2225select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 using ( a ); 2226a 22271 22282 22293 2230select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) using ( a ); 2231a 22321 22332 22343 2235select * from (t1 as t2 left join t1 as t3 using (a)) left outer join t1 on t1.a>1; 2236a a 22371 2 22381 3 22392 2 22402 3 22413 2 22423 3 2243select * from t1 left outer join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1; 2244a a 22451 NULL 22462 1 22472 2 22482 3 22493 1 22503 2 22513 3 2252select * from (t1 as t2 left join t1 as t3 using (a)) left join t1 using ( a ); 2253a 22541 22552 22563 2257select * from t1 left join (t1 as t2 left join t1 as t3 using (a)) using ( a ); 2258a 22591 22602 22613 2262select * from (t1 as t2 left join t1 as t3 using (a)) natural left join t1; 2263a 22641 22652 22663 2267select * from t1 natural left join (t1 as t2 left join t1 as t3 using (a)); 2268a 22691 22702 22713 2272select * from (t1 as t2 left join t1 as t3 using (a)) right join t1 on t1.a>1; 2273a a 22741 2 22751 3 22762 2 22772 3 22783 2 22793 3 2280NULL 1 2281select * from t1 right join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1; 2282a a 22832 1 22842 2 22852 3 22863 1 22873 2 22883 3 2289select * from (t1 as t2 left join t1 as t3 using (a)) right outer join t1 using ( a ); 2290a 22911 22922 22933 2294select * from t1 right outer join (t1 as t2 left join t1 as t3 using (a)) using ( a ); 2295a 22961 22972 22983 2299select * from (t1 as t2 left join t1 as t3 using (a)) natural right join t1; 2300a 23011 23022 23033 2304select * from t1 natural right join (t1 as t2 left join t1 as t3 using (a)); 2305a 23061 23072 23083 2309select * from t1 natural join (t1 as t2 left join t1 as t3 using (a)); 2310a 23111 23122 23133 2314select * from (t1 as t2 left join t1 as t3 using (a)) natural join t1; 2315a 23161 23172 23183 2319drop table t1; 2320CREATE 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; 2321INSERT INTO t1 VALUES ("1",8264,2506),("2",8299,2517),("3",8301,2518),("4",8302,2519),("5",8303,2520),("6",8304,2521),("7",8305,2522); 2322CREATE TABLE t2 ( id int(11) NOT NULL auto_increment, PRIMARY KEY (id)) ENGINE=MyISAM; 2323INSERT INTO t2 VALUES (2517), (2518), (2519), (2520), (2521), (2522); 2324select * from t1, t2 WHERE t1.t2_id = t2.id and t1.t2_id > 0 order by t1.id LIMIT 0, 5; 2325aa id t2_id id 23262 8299 2517 2517 23273 8301 2518 2518 23284 8302 2519 2519 23295 8303 2520 2520 23306 8304 2521 2521 2331drop table t1,t2; 2332create table t1 (id1 int NOT NULL); 2333create table t2 (id2 int NOT NULL); 2334create table t3 (id3 int NOT NULL); 2335create table t4 (id4 int NOT NULL, id44 int NOT NULL, KEY (id4)); 2336insert into t1 values (1); 2337insert into t1 values (2); 2338insert into t2 values (1); 2339insert into t4 values (1,1); 2340explain select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3 2341left join t4 on id3 = id4 where id2 = 1 or id4 = 1; 2342id select_type table type possible_keys key key_len ref rows Extra 23431 SIMPLE t3 system NULL NULL NULL NULL 0 Const row not found 23441 SIMPLE t4 const id4 NULL NULL NULL 1 23451 SIMPLE t1 ALL NULL NULL NULL NULL 2 23461 SIMPLE t2 hash_ALL NULL #hash#$hj 4 test.t1.id1 1 Using where; Using join buffer (flat, BNLH join) 2347select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3 2348left join t4 on id3 = id4 where id2 = 1 or id4 = 1; 2349id1 id2 id3 id4 id44 23501 1 NULL NULL NULL 2351drop table t1,t2,t3,t4; 2352create table t1(s varchar(10) not null); 2353create table t2(s varchar(10) not null primary key); 2354create table t3(s varchar(10) not null primary key); 2355insert into t1 values ('one\t'), ('two\t'); 2356insert into t2 values ('one\r'), ('two\t'); 2357insert into t3 values ('one '), ('two\t'); 2358select * from t1 where s = 'one'; 2359s 2360select * from t2 where s = 'one'; 2361s 2362select * from t3 where s = 'one'; 2363s 2364one 2365select * from t1,t2 where t1.s = t2.s; 2366s s 2367two two 2368select * from t2,t3 where t2.s = t3.s; 2369s s 2370two two 2371drop table t1, t2, t3; 2372create table t1 (a integer, b integer, index(a), index(b)); 2373create table t2 (c integer, d integer, index(c), index(d)); 2374insert into t1 values (1,2), (2,2), (3,2), (4,2); 2375insert into t2 values (1,3), (2,3), (3,4), (4,4); 2376explain select * from t1 left join t2 on a=c where d in (4); 2377id select_type table type possible_keys key key_len ref rows Extra 23781 SIMPLE t2 ref c,d d 5 const 2 23791 SIMPLE t1 ALL a NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) 2380select * from t1 left join t2 on a=c where d in (4); 2381a b c d 23823 2 3 4 23834 2 4 4 2384explain select * from t1 left join t2 on a=c where d = 4; 2385id select_type table type possible_keys key key_len ref rows Extra 23861 SIMPLE t2 ref c,d d 5 const 2 23871 SIMPLE t1 ALL a NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) 2388select * from t1 left join t2 on a=c where d = 4; 2389a b c d 23903 2 3 4 23914 2 4 4 2392drop table t1, t2; 2393CREATE TABLE t1 ( 2394i int(11) NOT NULL default '0', 2395c char(10) NOT NULL default '', 2396PRIMARY KEY (i), 2397UNIQUE KEY c (c) 2398) ENGINE=MyISAM; 2399INSERT INTO t1 VALUES (1,'a'); 2400INSERT INTO t1 VALUES (2,'b'); 2401INSERT INTO t1 VALUES (3,'c'); 2402EXPLAIN SELECT i FROM t1 WHERE i=1; 2403id select_type table type possible_keys key key_len ref rows Extra 24041 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index 2405DROP TABLE t1; 2406CREATE TABLE t1 ( a BLOB, INDEX (a(20)) ); 2407CREATE TABLE t2 ( a BLOB, INDEX (a(20)) ); 2408INSERT INTO t1 VALUES ('one'),('two'),('three'),('four'),('five'); 2409INSERT INTO t2 VALUES ('one'),('two'),('three'),('four'),('five'); 2410INSERT INTO t2 VALUES ('one'),('two'),('three'),('four'),('five'); 2411EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USE INDEX (a) ON t1.a=t2.a; 2412id select_type table type possible_keys key key_len ref rows Extra 24131 SIMPLE t1 ALL NULL NULL NULL NULL 5 24141 SIMPLE t2 ref a a 23 test.t1.a 2 Using where 2415EXPLAIN SELECT * FROM t1 LEFT JOIN t2 FORCE INDEX (a) ON t1.a=t2.a; 2416id select_type table type possible_keys key key_len ref rows Extra 24171 SIMPLE t1 ALL NULL NULL NULL NULL 5 24181 SIMPLE t2 ref a a 23 test.t1.a 2 Using where 2419DROP TABLE t1, t2; 2420CREATE TABLE t1 ( city char(30) ); 2421INSERT INTO t1 VALUES ('London'); 2422INSERT INTO t1 VALUES ('Paris'); 2423SELECT * FROM t1 WHERE city='London'; 2424city 2425London 2426SELECT * FROM t1 WHERE city='london'; 2427city 2428London 2429EXPLAIN SELECT * FROM t1 WHERE city='London' AND city='london'; 2430id select_type table type possible_keys key key_len ref rows Extra 24311 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where 2432SELECT * FROM t1 WHERE city='London' AND city='london'; 2433city 2434London 2435EXPLAIN SELECT * FROM t1 WHERE city LIKE '%london%' AND city='London'; 2436id select_type table type possible_keys key key_len ref rows Extra 24371 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where 2438SELECT * FROM t1 WHERE city LIKE '%london%' AND city='London'; 2439city 2440London 2441DROP TABLE t1; 2442create table t1 (a int(11) unsigned, b int(11) unsigned); 2443insert into t1 values (1,0), (1,1), (18446744073709551615,0); 2444Warnings: 2445Warning 1264 Out of range value for column 'a' at row 3 2446select a-b from t1 order by 1; 2447a-b 24480 24491 24504294967295 2451select a-b , (a-b < 0) from t1 order by 1; 2452a-b (a-b < 0) 24530 0 24541 0 24554294967295 0 2456select a-b as d, (a-b >= 0), b from t1 group by b having d >= 0; 2457d (a-b >= 0) b 24581 1 0 24590 1 1 2460select cast((a - b) as unsigned) from t1 order by 1; 2461cast((a - b) as unsigned) 24620 24631 24644294967295 2465drop table t1; 2466create table t1 (a int(11)); 2467select all all * from t1; 2468a 2469select distinct distinct * from t1; 2470a 2471select all distinct * from t1; 2472ERROR HY000: Incorrect usage of ALL and DISTINCT 2473select distinct all * from t1; 2474ERROR HY000: Incorrect usage of ALL and DISTINCT 2475drop table t1; 2476CREATE TABLE t1 ( 2477kunde_intern_id int(10) unsigned NOT NULL default '0', 2478kunde_id int(10) unsigned NOT NULL default '0', 2479FK_firma_id int(10) unsigned NOT NULL default '0', 2480aktuell enum('Ja','Nein') NOT NULL default 'Ja', 2481vorname varchar(128) NOT NULL default '', 2482nachname varchar(128) NOT NULL default '', 2483geloescht enum('Ja','Nein') NOT NULL default 'Nein', 2484firma varchar(128) NOT NULL default '' 2485); 2486INSERT INTO t1 VALUES 2487(3964,3051,1,'Ja','Vorname1','1Nachname','Nein','Print Schau XXXX'), 2488(3965,3051111,1,'Ja','Vorname1111','1111Nachname','Nein','Print Schau XXXX'); 2489SELECT kunde_id ,FK_firma_id ,aktuell, vorname, nachname, geloescht FROM t1 2490WHERE 2491( 2492( 2493( '' != '' AND firma LIKE CONCAT('%', '', '%')) 2494OR 2495(vorname LIKE CONCAT('%', 'Vorname1', '%') AND 2496nachname LIKE CONCAT('%', '1Nachname', '%') AND 2497'Vorname1' != '' AND 'xxxx' != '') 2498) 2499AND 2500( 2501aktuell = 'Ja' AND geloescht = 'Nein' AND FK_firma_id = 2 2502) 2503) 2504; 2505kunde_id FK_firma_id aktuell vorname nachname geloescht 2506SELECT kunde_id ,FK_firma_id ,aktuell, vorname, nachname, 2507geloescht FROM t1 2508WHERE 2509( 2510( 2511aktuell = 'Ja' AND geloescht = 'Nein' AND FK_firma_id = 2 2512) 2513AND 2514( 2515( '' != '' AND firma LIKE CONCAT('%', '', '%') ) 2516OR 2517( vorname LIKE CONCAT('%', 'Vorname1', '%') AND 2518nachname LIKE CONCAT('%', '1Nachname', '%') AND 'Vorname1' != '' AND 2519'xxxx' != '') 2520) 2521) 2522; 2523kunde_id FK_firma_id aktuell vorname nachname geloescht 2524SELECT COUNT(*) FROM t1 WHERE 2525( 0 OR (vorname LIKE '%Vorname1%' AND nachname LIKE '%1Nachname%' AND 1)) 2526AND FK_firma_id = 2; 2527COUNT(*) 25280 2529drop table t1; 2530CREATE TABLE t1 (b BIGINT(20) UNSIGNED NOT NULL, PRIMARY KEY (b)); 2531INSERT INTO t1 VALUES (0x8000000000000000); 2532SELECT b FROM t1 WHERE b=0x8000000000000000; 2533b 25349223372036854775808 2535DROP TABLE t1; 2536CREATE TABLE `t1` ( `gid` int(11) default NULL, `uid` int(11) default NULL); 2537CREATE TABLE `t2` ( `ident` int(11) default NULL, `level` char(16) default NULL); 2538INSERT INTO `t2` VALUES (0,'READ'); 2539CREATE TABLE `t3` ( `id` int(11) default NULL, `name` char(16) default NULL); 2540INSERT INTO `t3` VALUES (1,'fs'); 2541select * from t3 left join t1 on t3.id = t1.uid, t2 where t2.ident in (0, t1.gid, t3.id, 0); 2542id name gid uid ident level 25431 fs NULL NULL 0 READ 2544drop table t1,t2,t3; 2545CREATE TABLE t1 ( 2546acct_id int(11) NOT NULL default '0', 2547profile_id smallint(6) default NULL, 2548UNIQUE KEY t1$acct_id (acct_id), 2549KEY t1$profile_id (profile_id) 2550); 2551INSERT INTO t1 VALUES (132,17),(133,18); 2552CREATE TABLE t2 ( 2553profile_id smallint(6) default NULL, 2554queue_id int(11) default NULL, 2555seq int(11) default NULL, 2556KEY t2$queue_id (queue_id) 2557); 2558INSERT INTO t2 VALUES (17,31,4),(17,30,3),(17,36,2),(17,37,1); 2559CREATE TABLE t3 ( 2560id int(11) NOT NULL default '0', 2561qtype int(11) default NULL, 2562seq int(11) default NULL, 2563warn_lvl int(11) default NULL, 2564crit_lvl int(11) default NULL, 2565rr1 tinyint(4) NOT NULL default '0', 2566rr2 int(11) default NULL, 2567default_queue tinyint(4) NOT NULL default '0', 2568KEY t3$qtype (qtype), 2569KEY t3$id (id) 2570); 2571INSERT INTO t3 VALUES (30,1,29,NULL,NULL,0,NULL,0),(31,1,28,NULL,NULL,0,NULL,0), 2572(36,1,34,NULL,NULL,0,NULL,0),(37,1,35,NULL,NULL,0,121,0); 2573SELECT COUNT(*) FROM t1 a STRAIGHT_JOIN t2 pq STRAIGHT_JOIN t3 q 2574WHERE 2575(pq.profile_id = a.profile_id) AND (a.acct_id = 132) AND 2576(pq.queue_id = q.id) AND (q.rr1 <> 1); 2577COUNT(*) 25784 2579drop table t1,t2,t3; 2580create table t1 (f1 int); 2581insert into t1 values (1),(NULL); 2582create table t2 (f2 int, f3 int, f4 int); 2583create index idx1 on t2 (f4); 2584insert into t2 values (1,2,3),(2,4,6); 2585select A.f2 from t1 left join t2 A on A.f2 = f1 where A.f3=(select min(f3) 2586from t2 C where A.f4 = C.f4) or A.f3 IS NULL; 2587f2 25881 2589NULL 2590drop table t1,t2; 2591create table t2 (a tinyint unsigned); 2592create index t2i on t2(a); 2593insert into t2 values (0), (254), (255); 2594explain select * from t2 where a > -1; 2595id select_type table type possible_keys key key_len ref rows Extra 25961 SIMPLE t2 index t2i t2i 2 NULL 3 Using where; Using index 2597select * from t2 where a > -1; 2598a 25990 2600254 2601255 2602drop table t2; 2603CREATE TABLE t1 (a int, b int, c int); 2604INSERT INTO t1 2605SELECT 50, 3, 3 FROM DUAL 2606WHERE NOT EXISTS 2607(SELECT * FROM t1 WHERE a = 50 AND b = 3); 2608SELECT * FROM t1; 2609a b c 261050 3 3 2611INSERT INTO t1 2612SELECT 50, 3, 3 FROM DUAL 2613WHERE NOT EXISTS 2614(SELECT * FROM t1 WHERE a = 50 AND b = 3); 2615select found_rows(); 2616found_rows() 26170 2618SELECT * FROM t1; 2619a b c 262050 3 3 2621select count(*) from t1; 2622count(*) 26231 2624select found_rows(); 2625found_rows() 26261 2627select count(*) from t1 limit 2,3; 2628count(*) 2629select found_rows(); 2630found_rows() 26310 2632select SQL_CALC_FOUND_ROWS count(*) from t1 limit 2,3; 2633count(*) 2634select found_rows(); 2635found_rows() 26361 2637DROP TABLE t1; 2638CREATE TABLE t1 (a INT, b INT); 2639(SELECT a, b AS c FROM t1) ORDER BY c+1; 2640a c 2641(SELECT a, b AS c FROM t1) ORDER BY b+1; 2642a c 2643SELECT a, b AS c FROM t1 ORDER BY c+1; 2644a c 2645SELECT a, b AS c FROM t1 ORDER BY b+1; 2646a c 2647drop table t1; 2648create table t1(f1 int, f2 int); 2649create table t2(f3 int); 2650select f1 from t1,t2 where f1=f2 and (f1,f2) = ((1,1)); 2651f1 2652select f1 from t1,t2 where f1=f2 and (f1,NULL) = ((1,1)); 2653f1 2654select f1 from t1,t2 where f1=f2 and (f1,f2) = ((1,NULL)); 2655f1 2656insert into t1 values(1,1),(2,null); 2657insert into t2 values(2); 2658select * from t1,t2 where f1=f3 and (f1,f2) = (2,null); 2659f1 f2 f3 2660select * from t1,t2 where f1=f3 and (f1,f2) <=> (2,null); 2661f1 f2 f3 26622 NULL 2 2663drop table t1,t2; 2664create table t1 (f1 int not null auto_increment primary key, f2 varchar(10)); 2665create table t11 like t1; 2666insert into t1 values(1,""),(2,""); 2667show table status like 't1%'; 2668Name 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 2669t1 MyISAM 10 Dynamic 2 20 X X X X X X X X latin1_swedish_ci NULL X N 2670t11 MyISAM 10 Dynamic 0 0 X X X X X X X X latin1_swedish_ci NULL X N 2671select 123 as a from t1 where f1 is null; 2672a 2673drop table t1,t11; 2674CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL, UNIQUE idx (a,b) ); 2675INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4); 2676CREATE TABLE t2 ( a INT NOT NULL, b INT NOT NULL, e INT ); 2677INSERT INTO t2 VALUES ( 1,10,1), (1,10,2), (1,11,1), (1,11,2), (1,2,1), (1,2,2),(1,2,3); 2678SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2 LEFT JOIN 2679t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY a, b, c; 2680a b c d 26811 2 1 1 26821 2 2 1 26831 2 3 1 26841 10 2 26851 11 2 2686SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2 LEFT JOIN 2687t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY t1.a, t1.b, c; 2688a b c d 26891 10 4 26901 2 1 1 26911 2 2 1 26921 2 3 1 2693SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2 LEFT JOIN 2694t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY t2.a, t2.b, c; 2695a b c d 26961 2 1 1 26971 2 2 1 26981 2 3 1 26991 10 2 27001 11 2 2701SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2,t1 2702WHERE t2.a = t1.a AND t2.b = t1.b GROUP BY a, b, c; 2703a b c d 27041 2 1 1 27051 2 2 1 27061 2 3 1 2707DROP TABLE IF EXISTS t1, t2; 2708create table t1 (f1 int primary key, f2 int); 2709create table t2 (f3 int, f4 int, primary key(f3,f4)); 2710insert into t1 values (1,1); 2711insert into t2 values (1,1),(1,2); 2712select distinct count(f2) >0 from t1 left join t2 on f1=f3 group by f1; 2713count(f2) >0 27141 2715drop table t1,t2; 2716create table t1 (f1 int,f2 int); 2717insert into t1 values(1,1); 2718create table t2 (f3 int, f4 int, primary key(f3,f4)); 2719insert into t2 values(1,1); 2720select * from t1 where f1 in (select f3 from t2 where (f3,f4)= (select f3,f4 from t2)); 2721f1 f2 27221 1 2723drop table t1,t2; 2724CREATE TABLE t1(a int, b int, c int, KEY b(b), KEY c(c)); 2725insert into t1 values (1,0,0),(2,0,0); 2726CREATE TABLE t2 (a int, b varchar(2), c varchar(2), PRIMARY KEY(a)); 2727insert into t2 values (1,'',''), (2,'',''); 2728CREATE TABLE t3 (a int, b int, PRIMARY KEY (a,b), KEY a (a), KEY b (b)); 2729insert into t3 values (1,1),(1,2); 2730explain select straight_join DISTINCT t2.a,t2.b, t1.c from t1, t3, t2 2731where (t1.c=t2.a or (t1.c=t3.a and t2.a=t3.b)) and t1.b=556476786 and 2732t2.b like '%%' order by t2.b limit 0,1; 2733id select_type table type possible_keys key key_len ref rows Extra 27341 SIMPLE t1 ref b,c b 5 const 1 Using temporary; Using filesort 27351 SIMPLE t3 index PRIMARY,a,b PRIMARY 8 NULL 2 Using index; Using join buffer (flat, BNL join) 27361 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1) 2737DROP TABLE t1,t2,t3; 2738CREATE TABLE t1 (a int, INDEX idx(a)); 2739INSERT INTO t1 VALUES (2), (3), (1); 2740EXPLAIN SELECT * FROM t1 IGNORE INDEX (idx); 2741id select_type table type possible_keys key key_len ref rows Extra 27421 SIMPLE t1 ALL NULL NULL NULL NULL 3 2743EXPLAIN SELECT * FROM t1 IGNORE INDEX (a); 2744ERROR 42000: Key 'a' doesn't exist in table 't1' 2745EXPLAIN SELECT * FROM t1 FORCE INDEX (a); 2746ERROR 42000: Key 'a' doesn't exist in table 't1' 2747DROP TABLE t1; 2748CREATE TABLE t1 (a int, b int); 2749INSERT INTO t1 VALUES (1,1), (2,1), (4,10); 2750CREATE TABLE t2 (a int PRIMARY KEY, b int, KEY b (b)); 2751INSERT INTO t2 VALUES (1,NULL), (2,10); 2752ALTER TABLE t1 ENABLE KEYS; 2753EXPLAIN SELECT STRAIGHT_JOIN SQL_NO_CACHE COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; 2754id select_type table type possible_keys key key_len ref rows Extra 27551 SIMPLE t2 index b b 5 NULL 2 Using index 27561 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) 2757SELECT STRAIGHT_JOIN SQL_NO_CACHE * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; 2758a b a b 27591 NULL 1 1 27601 NULL 2 1 27611 NULL 4 10 27622 10 4 10 2763EXPLAIN SELECT STRAIGHT_JOIN SQL_NO_CACHE COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; 2764id select_type table type possible_keys key key_len ref rows Extra 27651 SIMPLE t2 index b b 5 NULL 2 Using index 27661 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) 2767SELECT STRAIGHT_JOIN SQL_NO_CACHE * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; 2768a b a b 27691 NULL 1 1 27701 NULL 2 1 27711 NULL 4 10 27722 10 4 10 2773DROP TABLE IF EXISTS t1,t2; 2774CREATE TABLE t1 (key1 float default NULL, UNIQUE KEY key1 (key1)); 2775CREATE TABLE t2 (key2 float default NULL, UNIQUE KEY key2 (key2)); 2776INSERT INTO t1 VALUES (0.3762),(0.3845),(0.6158),(0.7941); 2777INSERT INTO t2 VALUES (1.3762),(1.3845),(1.6158),(1.7941); 2778explain select max(key1) from t1 where key1 <= 0.6158; 2779id select_type table type possible_keys key key_len ref rows Extra 27801 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2781explain select max(key2) from t2 where key2 <= 1.6158; 2782id select_type table type possible_keys key key_len ref rows Extra 27831 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2784explain select min(key1) from t1 where key1 >= 0.3762; 2785id select_type table type possible_keys key key_len ref rows Extra 27861 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2787explain select min(key2) from t2 where key2 >= 1.3762; 2788id select_type table type possible_keys key key_len ref rows Extra 27891 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2790explain select max(key1), min(key2) from t1, t2 2791where key1 <= 0.6158 and key2 >= 1.3762; 2792id select_type table type possible_keys key key_len ref rows Extra 27931 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2794explain select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5; 2795id select_type table type possible_keys key key_len ref rows Extra 27961 SIMPLE t1 range key1 key1 5 NULL 3 Using where; Using index 2797explain select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5; 2798id select_type table type possible_keys key key_len ref rows Extra 27991 SIMPLE t1 range key1 key1 5 NULL 3 Using where; Using index 2800select max(key1) from t1 where key1 <= 0.6158; 2801max(key1) 28020.6158 2803select max(key2) from t2 where key2 <= 1.6158; 2804max(key2) 28051.6158 2806select min(key1) from t1 where key1 >= 0.3762; 2807min(key1) 28080.3762 2809select min(key2) from t2 where key2 >= 1.3762; 2810min(key2) 28111.3762 2812select max(key1), min(key2) from t1, t2 2813where key1 <= 0.6158 and key2 >= 1.3762; 2814max(key1) min(key2) 28150.6158 1.3762 2816select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5; 2817max(key1) 28180.3845 2819select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5; 2820min(key1) 28210.3845 2822DROP TABLE t1,t2; 2823CREATE TABLE t1 (i BIGINT UNSIGNED NOT NULL); 2824INSERT INTO t1 VALUES (10); 2825SELECT i='1e+01',i=1e+01, i in (1e+01,1e+01), i in ('1e+01','1e+01') FROM t1; 2826i='1e+01' i=1e+01 i in (1e+01,1e+01) i in ('1e+01','1e+01') 28271 1 1 1 2828DROP TABLE t1; 2829create table t1(a bigint unsigned, b bigint); 2830insert ignore into t1 values (0xfffffffffffffffff, 0xfffffffffffffffff), 2831(0x10000000000000000, 0x10000000000000000), 2832(0x8fffffffffffffff, 0x8fffffffffffffff); 2833Warnings: 2834Warning 1264 Out of range value for column 'a' at row 1 2835Warning 1264 Out of range value for column 'b' at row 1 2836Warning 1264 Out of range value for column 'a' at row 2 2837Warning 1264 Out of range value for column 'b' at row 2 2838Warning 1264 Out of range value for column 'b' at row 3 2839select hex(a), hex(b) from t1; 2840hex(a) hex(b) 2841FFFFFFFFFFFFFFFF 7FFFFFFFFFFFFFFF 2842FFFFFFFFFFFFFFFF 7FFFFFFFFFFFFFFF 28438FFFFFFFFFFFFFFF 7FFFFFFFFFFFFFFF 2844drop table t1; 2845CREATE TABLE t1 (c0 int); 2846CREATE TABLE t2 (c0 int); 2847INSERT INTO t1 VALUES(@@connect_timeout); 2848INSERT INTO t2 VALUES(@@connect_timeout); 2849SELECT * FROM t1 JOIN t2 ON t1.c0 = t2.c0 WHERE (t1.c0 <=> @@connect_timeout); 2850c0 c0 2851X X 2852DROP TABLE t1, t2; 2853End of 4.1 tests 2854CREATE TABLE t1 ( 2855K2C4 varchar(4) character set latin1 collate latin1_bin NOT NULL default '', 2856K4N4 varchar(4) character set latin1 collate latin1_bin NOT NULL default '0000', 2857F2I4 int(11) NOT NULL default '0' 2858) ENGINE=MyISAM DEFAULT CHARSET=latin1; 2859INSERT INTO t1 VALUES 2860('W%RT', '0100', 1), 2861('W-RT', '0100', 1), 2862('WART', '0100', 1), 2863('WART', '0200', 1), 2864('WERT', '0100', 2), 2865('WORT','0200', 2), 2866('WT', '0100', 2), 2867('W_RT', '0100', 2), 2868('WaRT', '0100', 3), 2869('WART', '0300', 3), 2870('WRT' , '0400', 3), 2871('WURM', '0500', 3), 2872('W%T', '0600', 4), 2873('WA%T', '0700', 4), 2874('WA_T', '0800', 4); 2875SELECT K2C4, K4N4, F2I4 FROM t1 2876WHERE K2C4 = 'WART' AND 2877(F2I4 = 2 AND K2C4 = 'WART' OR (F2I4 = 2 OR K4N4 = '0200')); 2878K2C4 K4N4 F2I4 2879WART 0200 1 2880SELECT K2C4, K4N4, F2I4 FROM t1 2881WHERE K2C4 = 'WART' AND (K2C4 = 'WART' OR K4N4 = '0200'); 2882K2C4 K4N4 F2I4 2883WART 0100 1 2884WART 0200 1 2885WART 0300 3 2886DROP TABLE t1; 2887create table t1 (a int, b int); 2888create table t2 like t1; 2889select t1.a from (t1 inner join t2 on t1.a=t2.a) where t2.a=1; 2890a 2891select t1.a from ((t1 inner join t2 on t1.a=t2.a)) where t2.a=1; 2892a 2893select 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; 2894a a a 2895drop table t1,t2; 2896create table t1 (s1 varchar(5)); 2897insert into t1 values ('Wall'); 2898select min(s1) from t1 group by s1 with rollup; 2899min(s1) 2900Wall 2901Wall 2902drop table t1; 2903create table t1 (s1 int) engine=myisam; 2904insert into t1 values (0); 2905select avg(distinct s1) from t1 group by s1 with rollup; 2906avg(distinct s1) 29070.0000 29080.0000 2909drop table t1; 2910create table t1 (s1 int); 2911insert into t1 values (null),(1); 2912select distinct avg(s1) as x from t1 group by s1 with rollup; 2913x 2914NULL 29151.0000 2916drop table t1; 2917CREATE TABLE t1 (a int); 2918CREATE TABLE t2 (a int); 2919INSERT INTO t1 VALUES (1), (2), (3), (4), (5); 2920INSERT INTO t2 VALUES (2), (4), (6); 2921SELECT t1.a FROM t1 STRAIGHT_JOIN t2 ON t1.a=t2.a; 2922a 29232 29244 2925EXPLAIN SELECT t1.a FROM t1 STRAIGHT_JOIN t2 ON t1.a=t2.a; 2926id select_type table type possible_keys key key_len ref rows Extra 29271 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where 29281 SIMPLE t2 hash_ALL NULL #hash#$hj 5 test.t1.a 3 Using where; Using join buffer (flat, BNLH join) 2929EXPLAIN SELECT t1.a FROM t1 INNER JOIN t2 ON t1.a=t2.a; 2930id select_type table type possible_keys key key_len ref rows Extra 29311 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where 29321 SIMPLE t1 hash_ALL NULL #hash#$hj 5 test.t2.a 5 Using where; Using join buffer (flat, BNLH join) 2933DROP TABLE t1,t2; 2934select x'3136' + 0, X'3136' + 0, b'10' + 0, B'10' + 0; 2935x'3136' + 0 X'3136' + 0 b'10' + 0 B'10' + 0 293616 16 2 2 2937create table t1 (f1 varchar(6) default NULL, f2 int(6) primary key not null); 2938create table t2 (f3 varchar(5) not null, f4 varchar(5) not null, UNIQUE KEY UKEY (f3,f4)); 2939insert into t1 values (" 2", 2); 2940insert into t2 values (" 2", " one "),(" 2", " two "); 2941select * from t1 left join t2 on f1 = f3; 2942f1 f2 f3 f4 2943 2 2 2 one 2944 2 2 2 two 2945drop table t1,t2; 2946create table t1 (empnum smallint, grp int); 2947create table t2 (empnum int, name char(5)); 2948insert into t1 values(1,1); 2949insert into t2 values(1,'bob'); 2950create view v1 as select * from t2 inner join t1 using (empnum); 2951select * from v1; 2952empnum name grp 29531 bob 1 2954drop table t1,t2; 2955drop view v1; 2956create table t1 (pk int primary key, b int); 2957create table t2 (pk int primary key, c int); 2958select pk from t1 inner join t2 using (pk); 2959pk 2960drop table t1,t2; 2961create table t1 (s1 int, s2 char(5), s3 decimal(10)); 2962create view v1 as select s1, s2, 'x' as s3 from t1; 2963select * from t1 natural join v1; 2964s1 s2 s3 2965insert into t1 values (1,'x',5); 2966select * from t1 natural join v1; 2967s1 s2 s3 2968Warnings: 2969Warning 1292 Truncated incorrect DECIMAL value: 'x' 2970drop table t1; 2971drop view v1; 2972create table t1(a1 int); 2973create table t2(a2 int); 2974insert into t1 values(1),(2); 2975insert into t2 values(1),(2); 2976create view v2 (c) as select a1 from t1; 2977select * from t1 natural left join t2; 2978a1 a2 29791 1 29801 2 29812 1 29822 2 2983select * from t1 natural right join t2; 2984a2 a1 29851 1 29861 2 29872 1 29882 2 2989select * from v2 natural left join t2; 2990c a2 29911 1 29921 2 29932 1 29942 2 2995select * from v2 natural right join t2; 2996a2 c 29971 1 29981 2 29992 1 30002 2 3001drop table t1, t2; 3002drop view v2; 3003create table t1 (a int(10), t1_val int(10)); 3004create table t2 (b int(10), t2_val int(10)); 3005create table t3 (a int(10), b int(10)); 3006insert into t1 values (1,1),(2,2); 3007insert into t2 values (1,1),(2,2),(3,3); 3008insert into t3 values (1,1),(2,1),(3,1),(4,1); 3009select * from t1 natural join t2 natural join t3; 3010a b t1_val t2_val 30111 1 1 1 30122 1 2 1 3013select * from t1 natural join t3 natural join t2; 3014b a t1_val t2_val 30151 1 1 1 30161 2 2 1 3017drop table t1, t2, t3; 3018DO IFNULL(NULL, NULL); 3019SELECT CAST(IFNULL(NULL, NULL) AS DECIMAL); 3020CAST(IFNULL(NULL, NULL) AS DECIMAL) 3021NULL 3022SELECT ABS(IFNULL(NULL, NULL)); 3023ABS(IFNULL(NULL, NULL)) 3024NULL 3025SELECT IFNULL(NULL, NULL); 3026IFNULL(NULL, NULL) 3027NULL 3028SET @OLD_SQL_MODE12595=@@SQL_MODE, @@SQL_MODE=''; 3029SHOW LOCAL VARIABLES LIKE 'SQL_MODE'; 3030Variable_name Value 3031sql_mode 3032CREATE TABLE BUG_12595(a varchar(100)); 3033INSERT INTO BUG_12595 VALUES ('hakan%'), ('hakank'), ("ha%an"); 3034SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\%'; 3035a 3036hakan% 3037SELECT * FROM BUG_12595 WHERE a LIKE 'hakan*%' ESCAPE '*'; 3038a 3039hakan% 3040SELECT * FROM BUG_12595 WHERE a LIKE 'hakan**%' ESCAPE '**'; 3041ERROR HY000: Incorrect arguments to ESCAPE 3042SELECT * FROM BUG_12595 WHERE a LIKE 'hakan%' ESCAPE ''; 3043a 3044hakan% 3045hakank 3046SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\%' ESCAPE ''; 3047a 3048SELECT * FROM BUG_12595 WHERE a LIKE 'ha\%an' ESCAPE 0x5c; 3049a 3050ha%an 3051SELECT * FROM BUG_12595 WHERE a LIKE 'ha%%an' ESCAPE '%'; 3052a 3053ha%an 3054SELECT * FROM BUG_12595 WHERE a LIKE 'ha\%an' ESCAPE '\\'; 3055a 3056ha%an 3057SELECT * FROM BUG_12595 WHERE a LIKE 'ha|%an' ESCAPE '|'; 3058a 3059ha%an 3060SET @@SQL_MODE='NO_BACKSLASH_ESCAPES'; 3061SHOW LOCAL VARIABLES LIKE 'SQL_MODE'; 3062Variable_name Value 3063sql_mode NO_BACKSLASH_ESCAPES 3064SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\%'; 3065a 3066SELECT * FROM BUG_12595 WHERE a LIKE 'hakan*%' ESCAPE '*'; 3067a 3068hakan% 3069SELECT * FROM BUG_12595 WHERE a LIKE 'hakan**%' ESCAPE '**'; 3070ERROR HY000: Incorrect arguments to ESCAPE 3071SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\%' ESCAPE '\\'; 3072ERROR HY000: Incorrect arguments to ESCAPE 3073SELECT * FROM BUG_12595 WHERE a LIKE 'hakan%' ESCAPE ''; 3074ERROR HY000: Incorrect arguments to ESCAPE 3075SELECT * FROM BUG_12595 WHERE a LIKE 'ha\%an' ESCAPE 0x5c; 3076a 3077ha%an 3078SELECT * FROM BUG_12595 WHERE a LIKE 'ha|%an' ESCAPE '|'; 3079a 3080ha%an 3081SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\n%' ESCAPE '\n'; 3082ERROR HY000: Incorrect arguments to ESCAPE 3083SET @@SQL_MODE=@OLD_SQL_MODE12595; 3084DROP TABLE BUG_12595; 3085create table t1 (a char(1)); 3086create table t2 (a char(1)); 3087insert into t1 values ('a'),('b'),('c'); 3088insert into t2 values ('b'),('c'),('d'); 3089select a from t1 natural join t2; 3090a 3091b 3092c 3093select * from t1 natural join t2 where a = 'b'; 3094a 3095b 3096drop table t1, t2; 3097CREATE TABLE t1 (`id` TINYINT); 3098CREATE TABLE t2 (`id` TINYINT); 3099CREATE TABLE t3 (`id` TINYINT); 3100INSERT INTO t1 VALUES (1),(2),(3); 3101INSERT INTO t2 VALUES (2); 3102INSERT INTO t3 VALUES (3); 3103SELECT t1.id,t3.id FROM t1 JOIN t2 ON (t2.id=t1.id) LEFT JOIN t3 USING (id); 3104ERROR 23000: Column 'id' in from clause is ambiguous 3105SELECT t1.id,t3.id FROM t1 JOIN t2 ON (t2.notacolumn=t1.id) LEFT JOIN t3 USING (id); 3106ERROR 23000: Column 'id' in from clause is ambiguous 3107SELECT id,t3.id FROM t1 JOIN t2 ON (t2.id=t1.id) LEFT JOIN t3 USING (id); 3108ERROR 23000: Column 'id' in from clause is ambiguous 3109SELECT id,t3.id FROM (t1 JOIN t2 ON (t2.id=t1.id)) LEFT JOIN t3 USING (id); 3110ERROR 23000: Column 'id' in from clause is ambiguous 3111drop table t1, t2, t3; 3112create table t1 (a int(10),b int(10)); 3113create table t2 (a int(10),b int(10)); 3114insert into t1 values (1,10),(2,20),(3,30); 3115insert into t2 values (1,10); 3116select * from t1 inner join t2 using (A); 3117a b b 31181 10 10 3119select * from t1 inner join t2 using (a); 3120a b b 31211 10 10 3122drop table t1, t2; 3123create table t1 (a int, c int); 3124create table t2 (b int); 3125create table t3 (b int, a int); 3126create table t4 (c int); 3127insert into t1 values (1,1); 3128insert into t2 values (1); 3129insert into t3 values (1,1); 3130insert into t4 values (1); 3131select * from t1 join t2 join t3 on (t2.b = t3.b and t1.a = t3.a); 3132a c b b a 31331 1 1 1 1 3134select * from t1, t2 join t3 on (t2.b = t3.b and t1.a = t3.a); 3135ERROR 42S22: Unknown column 't1.a' in 'on clause' 3136select * from t1 join t2 join t3 join t4 on (t1.a = t4.c and t2.b = t4.c); 3137a c b b a c 31381 1 1 1 1 1 3139select * from t1 join t2 join t4 using (c); 3140c a b 31411 1 1 3142drop table t1, t2, t3, t4; 3143create table t1(x int, y int); 3144create table t2(x int, y int); 3145create table t3(x int, primary key(x)); 3146insert into t1 values (1, 1), (2, 1), (3, 1), (4, 3), (5, 6), (6, 6); 3147insert into t2 values (1, 1), (2, 1), (3, 3), (4, 6), (5, 6); 3148insert into t3 values (1), (2), (3), (4), (5); 3149select t1.x, t3.x from t1, t2, t3 where t1.x = t2.x and t3.x >= t1.y and t3.x <= t2.y; 3150x x 31511 1 31522 1 31533 1 31543 2 31553 3 31564 3 31574 4 31584 5 3159drop table t1,t2,t3; 3160create table t1 (id char(16) not null default '', primary key (id)); 3161insert into t1 values ('100'),('101'),('102'); 3162create table t2 (id char(16) default null); 3163insert into t2 values (1); 3164create view v1 as select t1.id from t1; 3165create view v2 as select t2.id from t2; 3166create view v3 as select (t1.id+2) as id from t1 natural left join t2; 3167select t1.id from t1 left join v2 using (id); 3168id 3169100 3170101 3171102 3172select t1.id from v2 right join t1 using (id); 3173id 3174100 3175101 3176102 3177select t1.id from t1 left join v3 using (id); 3178id 3179100 3180101 3181102 3182select * from t1 left join v2 using (id); 3183id 3184100 3185101 3186102 3187select * from v2 right join t1 using (id); 3188id 3189100 3190101 3191102 3192select * from t1 left join v3 using (id); 3193id 3194100 3195101 3196102 3197select v1.id from v1 left join v2 using (id); 3198id 3199100 3200101 3201102 3202select v1.id from v2 right join v1 using (id); 3203id 3204100 3205101 3206102 3207select v1.id from v1 left join v3 using (id); 3208id 3209100 3210101 3211102 3212select * from v1 left join v2 using (id); 3213id 3214100 3215101 3216102 3217select * from v2 right join v1 using (id); 3218id 3219100 3220101 3221102 3222select * from v1 left join v3 using (id); 3223id 3224100 3225101 3226102 3227drop table t1, t2; 3228drop view v1, v2, v3; 3229create table t1 (id int(11) not null default '0'); 3230insert into t1 values (123),(191),(192); 3231create table t2 (id char(16) character set utf8 not null); 3232insert into t2 values ('58013'),('58014'),('58015'),('58016'); 3233create table t3 (a_id int(11) not null, b_id char(16) character set utf8); 3234insert into t3 values (123,null),(123,null),(123,null),(123,null),(123,null),(123,'58013'); 3235select count(*) 3236from t1 inner join (t3 left join t2 on t2.id = t3.b_id) on t1.id = t3.a_id; 3237count(*) 32386 3239select count(*) 3240from t1 inner join (t2 right join t3 on t2.id = t3.b_id) on t1.id = t3.a_id; 3241count(*) 32426 3243drop table t1,t2,t3; 3244create table t1 (a int); 3245create table t2 (b int); 3246create table t3 (c int); 3247select * from t1 join t2 join t3 on (t1.a=t3.c); 3248a b c 3249select * from t1 join t2 left join t3 on (t1.a=t3.c); 3250a b c 3251select * from t1 join t2 right join t3 on (t1.a=t3.c); 3252a b c 3253select * from t1 join t2 straight_join t3 on (t1.a=t3.c); 3254a b c 3255drop table t1, t2 ,t3; 3256create table t1(f1 int, f2 date); 3257insert into t1 values(1,'2005-01-01'),(2,'2005-09-01'),(3,'2005-09-30'), 3258(4,'2005-10-01'),(5,'2005-12-30'); 3259select * from t1 where f2 >= 0 order by f2; 3260f1 f2 32611 2005-01-01 32622 2005-09-01 32633 2005-09-30 32644 2005-10-01 32655 2005-12-30 3266select * from t1 where f2 >= '0000-00-00' order by f2; 3267f1 f2 32681 2005-01-01 32692 2005-09-01 32703 2005-09-30 32714 2005-10-01 32725 2005-12-30 3273select * from t1 where f2 >= '2005-09-31' order by f2; 3274f1 f2 32754 2005-10-01 32765 2005-12-30 3277select * from t1 where f2 >= '2005-09-3a' order by f2; 3278f1 f2 32793 2005-09-30 32804 2005-10-01 32815 2005-12-30 3282Warnings: 3283Warning 1292 Truncated incorrect date value: '2005-09-3a' 3284select * from t1 where f2 <= '2005-09-31' order by f2; 3285f1 f2 32861 2005-01-01 32872 2005-09-01 32883 2005-09-30 3289select * from t1 where f2 <= '2005-09-3a' order by f2; 3290f1 f2 32911 2005-01-01 32922 2005-09-01 3293Warnings: 3294Warning 1292 Truncated incorrect date value: '2005-09-3a' 3295drop table t1; 3296create table t1 (f1 int, f2 int); 3297insert into t1 values (1, 30), (2, 20), (3, 10); 3298create algorithm=merge view v1 as select f1, f2 from t1; 3299create algorithm=merge view v2 (f2, f1) as select f1, f2 from t1; 3300create algorithm=merge view v3 as select t1.f1 as f2, t1.f2 as f1 from t1; 3301select t1.f1 as x1, f1 from t1 order by t1.f1; 3302x1 f1 33031 1 33042 2 33053 3 3306select v1.f1 as x1, f1 from v1 order by v1.f1; 3307x1 f1 33081 1 33092 2 33103 3 3311select v2.f1 as x1, f1 from v2 order by v2.f1; 3312x1 f1 331310 10 331420 20 331530 30 3316select v3.f1 as x1, f1 from v3 order by v3.f1; 3317x1 f1 331810 10 331920 20 332030 30 3321select f1, f2, v1.f1 as x1 from v1 order by v1.f1; 3322f1 f2 x1 33231 30 1 33242 20 2 33253 10 3 3326select f1, f2, v2.f1 as x1 from v2 order by v2.f1; 3327f1 f2 x1 332810 3 10 332920 2 20 333030 1 30 3331select f1, f2, v3.f1 as x1 from v3 order by v3.f1; 3332f1 f2 x1 333310 3 10 333420 2 20 333530 1 30 3336drop table t1; 3337drop view v1, v2, v3; 3338CREATE TABLE t1(key_a int4 NOT NULL, optimus varchar(32), PRIMARY KEY(key_a)); 3339CREATE TABLE t2(key_a int4 NOT NULL, prime varchar(32), PRIMARY KEY(key_a)); 3340CREATE table t3(key_a int4 NOT NULL, key_b int4 NOT NULL, foo varchar(32), 3341PRIMARY KEY(key_a,key_b)); 3342INSERT INTO t1 VALUES (0,''); 3343INSERT INTO t1 VALUES (1,'i'); 3344INSERT INTO t1 VALUES (2,'j'); 3345INSERT INTO t1 VALUES (3,'k'); 3346INSERT INTO t2 VALUES (1,'r'); 3347INSERT INTO t2 VALUES (2,'s'); 3348INSERT INTO t2 VALUES (3,'t'); 3349INSERT INTO t3 VALUES (1,5,'x'); 3350INSERT INTO t3 VALUES (1,6,'y'); 3351INSERT INTO t3 VALUES (2,5,'xx'); 3352INSERT INTO t3 VALUES (2,6,'yy'); 3353INSERT INTO t3 VALUES (2,7,'zz'); 3354INSERT INTO t3 VALUES (3,5,'xxx'); 3355SELECT t2.key_a,foo 3356FROM t1 INNER JOIN t2 ON t1.key_a = t2.key_a 3357INNER JOIN t3 ON t1.key_a = t3.key_a 3358WHERE t2.key_a=2 and key_b=5; 3359key_a foo 33602 xx 3361EXPLAIN SELECT t2.key_a,foo 3362FROM t1 INNER JOIN t2 ON t1.key_a = t2.key_a 3363INNER JOIN t3 ON t1.key_a = t3.key_a 3364WHERE t2.key_a=2 and key_b=5; 3365id select_type table type possible_keys key key_len ref rows Extra 33661 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index 33671 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 Using index 33681 SIMPLE t3 const PRIMARY PRIMARY 8 const,const 1 3369SELECT t2.key_a,foo 3370FROM t1 INNER JOIN t2 ON t2.key_a = t1.key_a 3371INNER JOIN t3 ON t1.key_a = t3.key_a 3372WHERE t2.key_a=2 and key_b=5; 3373key_a foo 33742 xx 3375EXPLAIN SELECT t2.key_a,foo 3376FROM t1 INNER JOIN t2 ON t2.key_a = t1.key_a 3377INNER JOIN t3 ON t1.key_a = t3.key_a 3378WHERE t2.key_a=2 and key_b=5; 3379id select_type table type possible_keys key key_len ref rows Extra 33801 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index 33811 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 Using index 33821 SIMPLE t3 const PRIMARY PRIMARY 8 const,const 1 3383DROP TABLE t1,t2,t3; 3384create table t1 (f1 int); 3385insert into t1 values(1),(2); 3386create table t2 (f2 int, f3 int, key(f2)); 3387insert into t2 values(1,1),(2,2); 3388create table t3 (f4 int not null); 3389insert into t3 values (2),(2),(2); 3390select f1,(select count(*) from t2,t3 where f2=f1 and f3=f4) as count from t1; 3391f1 count 33921 0 33932 3 3394drop table t1,t2,t3; 3395create table t1 (f1 int unique); 3396create table t2 (f2 int unique); 3397create table t3 (f3 int unique); 3398insert into t1 values(1),(2); 3399insert into t2 values(1),(2); 3400insert into t3 values(1),(NULL); 3401select * from t3 where f3 is null; 3402f3 3403NULL 3404select t2.f2 from t1 left join t2 on f1=f2 join t3 on f1=f3 where f1=1; 3405f2 34061 3407drop table t1,t2,t3; 3408create table t1(f1 char, f2 char not null); 3409insert into t1 values(null,'a'); 3410create table t2 (f2 char not null); 3411insert into t2 values('b'); 3412select * from t1 left join t2 on f1=t2.f2 where t1.f2='a'; 3413f1 f2 f2 3414NULL a NULL 3415drop table t1,t2; 3416select * from (select * left join t on f1=f2) tt; 3417ERROR 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 3418CREATE TABLE t1 (sku int PRIMARY KEY, pr int); 3419CREATE TABLE t2 (sku int PRIMARY KEY, sppr int, name varchar(255)); 3420INSERT INTO t1 VALUES 3421(10, 10), (20, 10), (30, 20), (40, 30), (50, 10), (60, 10), (70, 10); 3422INSERT INTO t2 VALUES 3423(10, 10, 'aaa'), (20, 10, 'bbb'), (30, 10, 'ccc'), (40, 20, 'ddd'), 3424(50, 10, 'eee'), (60, 20, 'fff'), (70, 20, 'ggg'), (80, 30, 'hhh'); 3425SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr 3426FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku); 3427sku sppr name sku pr 342820 10 bbb 10 10 342920 10 bbb 20 10 3430EXPLAIN 3431SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr 3432FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku); 3433id select_type table type possible_keys key key_len ref rows Extra 34341 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 34351 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Rowid-ordered scan 3436DROP TABLE t1,t2; 3437SET SQL_MODE='NO_UNSIGNED_SUBTRACTION'; 3438CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL); 3439INSERT t1 SET i = 0; 3440UPDATE t1 SET i = -1; 3441Warnings: 3442Warning 1264 Out of range value for column 'i' at row 1 3443SELECT * FROM t1; 3444i 34450 3446UPDATE t1 SET i = CAST(i - 1 AS SIGNED); 3447Warnings: 3448Warning 1264 Out of range value for column 'i' at row 1 3449SELECT * FROM t1; 3450i 34510 3452UPDATE t1 SET i = i - 1; 3453Warnings: 3454Warning 1264 Out of range value for column 'i' at row 1 3455SELECT * FROM t1; 3456i 34570 3458DROP TABLE t1; 3459SET SQL_MODE=default; 3460create table t1 (a int); 3461insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 3462create table t2 (a int, b int, c int, e int, primary key(a,b,c)); 3463insert into t2 select A.a, B.a, C.a, C.a from t1 A, t1 B, t1 C; 3464analyze table t2; 3465Table Op Msg_type Msg_text 3466test.t2 analyze status Engine-independent statistics collected 3467test.t2 analyze status OK 3468select 'In next EXPLAIN, B.rows must be exactly 10:' Z; 3469Z 3470In next EXPLAIN, B.rows must be exactly 10: 3471explain select * from t2 A, t2 B where A.a=5 and A.b=5 and A.C<5 3472and B.a=5 and B.b=A.e and (B.b =1 or B.b = 3 or B.b=5); 3473id select_type table type possible_keys key key_len ref rows Extra 34741 SIMPLE A range PRIMARY PRIMARY 12 NULL 4 Using index condition; Using where; Rowid-ordered scan 34751 SIMPLE B ref PRIMARY PRIMARY 8 const,test.A.e 10 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 3476drop table t1, t2; 3477CREATE TABLE t1 (a int PRIMARY KEY, b int, INDEX(b)); 3478INSERT INTO t1 VALUES (1, 3), (9,4), (7,5), (4,5), (6,2), 3479(3,1), (5,1), (8,9), (2,2), (0,9); 3480CREATE TABLE t2 (c int, d int, f int, INDEX(c,f)); 3481INSERT INTO t2 VALUES 3482(1,0,0), (1,0,1), (2,0,0), (2,0,1), (3,0,0), (4,0,1), 3483(5,0,0), (5,0,1), (6,0,0), (0,0,1), (7,0,0), (7,0,1), 3484(0,0,0), (0,0,1), (8,0,0), (8,0,1), (9,0,0), (9,0,1); 3485EXPLAIN 3486SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6; 3487id select_type table type possible_keys key key_len ref rows Extra 34881 SIMPLE t2 ALL c NULL NULL NULL 18 Using where 34891 SIMPLE t1 eq_ref|filter PRIMARY,b PRIMARY|b 4|5 test.t2.c 1 (30%) Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter 3490EXPLAIN 3491SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6 AND a > 0; 3492id select_type table type possible_keys key key_len ref rows Extra 34931 SIMPLE t2 ALL c NULL NULL NULL 18 Using where 34941 SIMPLE t1 eq_ref|filter PRIMARY,b PRIMARY|b 4|5 test.t2.c 1 (30%) Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter 3495DROP TABLE t1, t2; 3496create table t1 ( 3497a int unsigned not null auto_increment primary key, 3498b bit not null, 3499c bit not null 3500); 3501create table t2 ( 3502a int unsigned not null auto_increment primary key, 3503b bit not null, 3504c int unsigned not null, 3505d varchar(50) 3506); 3507insert into t1 (b,c) values (0,1), (0,1); 3508insert into t2 (b,c) values (0,1); 3509select t1.a, t1.b + 0, t1.c + 0, t2.a, t2.b + 0, t2.c, t2.d 3510from t1 left outer join t2 on t1.a = t2.c and t2.b <> 1 3511where t1.b <> 1 order by t1.a; 3512a t1.b + 0 t1.c + 0 a t2.b + 0 c d 35131 0 1 1 0 1 NULL 35142 0 1 NULL NULL NULL NULL 3515drop table t1,t2; 3516SELECT 0.9888889889 * 1.011111411911; 35170.9888889889 * 1.011111411911 35180.9998769417899202067879 3519prepare stmt from 'select 1 as " a "'; 3520Warnings: 3521Warning 1466 Leading spaces are removed from name ' a ' 3522execute stmt; 3523a 35241 3525CREATE TABLE t1 (a int NOT NULL PRIMARY KEY, b int NOT NULL); 3526INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4); 3527CREATE TABLE t2 (c int NOT NULL, INDEX idx(c)); 3528INSERT INTO t2 VALUES 3529(1), (1), (1), (1), (1), (1), (1), (1), 3530(2), (2), (2), (2), 3531(3), (3), 3532(4); 3533EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=1; 3534id select_type table type possible_keys key key_len ref rows Extra 35351 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 35361 SIMPLE t2 ref idx idx 4 const 8 Using index 3537EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=4; 3538id select_type table type possible_keys key key_len ref rows Extra 35391 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 35401 SIMPLE t2 ref idx idx 4 const 1 Using index 3541DROP TABLE t1, t2; 3542CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, a int); 3543INSERT INTO t1 VALUES (1,2), (2,NULL), (3,2); 3544CREATE TABLE t2 (b int, c INT, INDEX idx1(b)); 3545INSERT INTO t2 VALUES (2,1), (3,2); 3546CREATE TABLE t3 (d int, e int, INDEX idx1(d)); 3547INSERT INTO t3 VALUES (2,10), (2,20), (1,30), (2,40), (2,50), (1,60), (3,70), (1,80), (3,90); 3548EXPLAIN 3549SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id 3550WHERE t1.id=2; 3551id select_type table type possible_keys key key_len ref rows Extra 35521 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 35531 SIMPLE t2 const idx1 NULL NULL NULL 1 35541 SIMPLE t3 ref idx1 idx1 5 const 4 3555SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id 3556WHERE t1.id=2; 3557id a b c d e 35582 NULL NULL NULL 2 10 35592 NULL NULL NULL 2 20 35602 NULL NULL NULL 2 40 35612 NULL NULL NULL 2 50 3562DROP TABLE t1,t2,t3; 3563create table t1 (c1 varchar(1), c2 int, c3 int, c4 int, c5 int, c6 int, 3564c7 int, c8 int, c9 int, fulltext key (`c1`)); 3565select distinct match (`c1`) against ('z') , c2, c3, c4,c5, c6,c7, c8 3566from t1 where c9=1 order by c2, c2; 3567match (`c1`) against ('z') c2 c3 c4 c5 c6 c7 c8 3568drop table t1; 3569CREATE TABLE t1 (pk varchar(10) PRIMARY KEY, fk varchar(16)); 3570CREATE TABLE t2 (pk varchar(16) PRIMARY KEY, fk varchar(10)); 3571INSERT INTO t1 VALUES 3572('d','dddd'), ('i','iii'), ('a','aa'), ('b','bb'), ('g','gg'), 3573('e','eee'), ('c','cccc'), ('h','hhh'), ('j','jjj'), ('f','fff'); 3574INSERT INTO t2 VALUES 3575('jjj', 'j'), ('cc','c'), ('ccc','c'), ('aaa', 'a'), ('jjjj','j'), 3576('hhh','h'), ('gg','g'), ('fff','f'), ('ee','e'), ('ffff','f'), 3577('bbb','b'), ('ff','f'), ('cccc','c'), ('dddd','d'), ('jj','j'), 3578('aaaa','a'), ('bb','b'), ('eeee','e'), ('aa','a'), ('hh','h'); 3579EXPLAIN SELECT t2.* 3580FROM t1 JOIN t2 ON t2.fk=t1.pk 3581WHERE t2.fk < 'c' AND t2.pk=t1.fk; 3582id select_type table type possible_keys key key_len ref rows Extra 35831 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using where; Rowid-ordered scan 35841 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 3585EXPLAIN SELECT t2.* 3586FROM t1 JOIN t2 ON t2.fk=t1.pk 3587WHERE t2.fk BETWEEN 'a' AND 'b' AND t2.pk=t1.fk; 3588id select_type table type possible_keys key key_len ref rows Extra 35891 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using where; Rowid-ordered scan 35901 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 3591EXPLAIN SELECT t2.* 3592FROM t1 JOIN t2 ON t2.fk=t1.pk 3593WHERE t2.fk IN ('a','b') AND t2.pk=t1.fk; 3594id select_type table type possible_keys key key_len ref rows Extra 35951 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using where; Rowid-ordered scan 35961 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 3597DROP TABLE t1,t2; 3598CREATE TABLE t1 (a int, b varchar(20) NOT NULL, PRIMARY KEY(a)); 3599CREATE TABLE t2 (a int, b varchar(20) NOT NULL, 3600PRIMARY KEY (a), UNIQUE KEY (b)); 3601INSERT INTO t1 VALUES (1,'a'),(2,'b'),(3,'c'); 3602INSERT INTO t2 VALUES (1,'a'),(2,'b'),(3,'c'); 3603EXPLAIN SELECT t1.a FROM t1 LEFT JOIN t2 ON t2.b=t1.b WHERE t1.a=3; 3604id select_type table type possible_keys key key_len ref rows Extra 36051 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 3606DROP TABLE t1,t2; 3607CREATE TABLE t1(id int PRIMARY KEY, b int, e int); 3608CREATE TABLE t2(i int, a int, INDEX si(i), INDEX ai(a)); 3609CREATE TABLE t3(a int PRIMARY KEY, c char(4), INDEX ci(c)); 3610INSERT INTO t1 VALUES 3611(1,10,19), (2,20,22), (4,41,42), (9,93,95), (7, 77,79), 3612(6,63,67), (5,55,58), (3,38,39), (8,81,89); 3613INSERT INTO t2 VALUES 3614(21,210), (41,410), (82,820), (83,830), (84,840), 3615(65,650), (51,510), (37,370), (94,940), (76,760), 3616(22,220), (33,330), (40,400), (95,950), (38,380), 3617(67,670), (88,880), (57,570), (96,960), (97,970); 3618INSERT INTO t3 VALUES 3619(210,'bb'), (950,'ii'), (400,'ab'), (500,'ee'), (220,'gg'), 3620(440,'gg'), (310,'eg'), (380,'ee'), (840,'bb'), (830,'ff'), 3621(230,'aa'), (960,'ii'), (410,'aa'), (510,'ee'), (290,'bb'), 3622(450,'gg'), (320,'dd'), (390,'hh'), (850,'jj'), (860,'ff'); 3623EXPLAIN 3624SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3 3625WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND 3626t3.a=t2.a AND t3.c IN ('bb','ee'); 3627id select_type table type possible_keys key key_len ref rows Extra 36281 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 36291 SIMPLE t2 range si si 5 NULL 4 Using index condition; Using where; Rowid-ordered scan 36301 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (30%) Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter 3631EXPLAIN 3632SELECT t3.a FROM t1,t2,t3 3633WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND 3634t3.a=t2.a AND t3.c IN ('bb','ee') ; 3635id select_type table type possible_keys key key_len ref rows Extra 36361 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 36371 SIMPLE t2 range si,ai si 5 NULL 4 Using index condition; Using where; Rowid-ordered scan 36381 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (30%) Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter 3639EXPLAIN 3640SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3 3641WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND 3642t3.c IN ('bb','ee'); 3643id select_type table type possible_keys key key_len ref rows Extra 36441 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 36451 SIMPLE t2 range si si 5 NULL 2 Using index condition; Using where; Rowid-ordered scan 36461 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (30%) Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter 3647EXPLAIN 3648SELECT t3.a FROM t1,t2,t3 3649WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND 3650t3.c IN ('bb','ee'); 3651id select_type table type possible_keys key key_len ref rows Extra 36521 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 36531 SIMPLE t2 range si,ai si 5 NULL 2 Using index condition; Using where; Rowid-ordered scan 36541 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (30%) Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter 3655DROP TABLE t1,t2,t3; 3656CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int); 3657CREATE TABLE t2 ( f11 int PRIMARY KEY ); 3658INSERT INTO t1 VALUES (1,1,1,0,0,0,0),(2,1,1,3,8,1,0),(3,1,1,4,12,1,0); 3659INSERT INTO t2 VALUES (62); 3660SELECT * FROM t1 LEFT JOIN t2 ON f11 = t1.checked_out GROUP BY f1 ORDER BY f2, f3, f4, f5 LIMIT 0, 1; 3661f1 f2 f3 f4 f5 f6 checked_out f11 36621 1 1 0 0 0 0 NULL 3663DROP TABLE t1, t2; 3664DROP TABLE IF EXISTS t1; 3665CREATE TABLE t1(a int); 3666INSERT into t1 values (1), (2), (3); 3667SELECT * FROM t1 LIMIT 2, -1; 3668ERROR 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 3669DROP TABLE t1; 3670CREATE TABLE t1 ( 3671ID_with_null int NULL, 3672ID_better int NOT NULL, 3673INDEX idx1 (ID_with_null), 3674INDEX idx2 (ID_better) 3675); 3676INSERT INTO t1 VALUES (1,1), (2,1), (null,3), (null,3), (null,3), (null,3); 3677INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; 3678INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; 3679INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; 3680INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; 3681INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; 3682SELECT COUNT(*) FROM t1 WHERE ID_with_null IS NULL; 3683COUNT(*) 3684128 3685SELECT COUNT(*) FROM t1 WHERE ID_better=1; 3686COUNT(*) 36872 3688EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL; 3689id select_type table type possible_keys key key_len ref rows Extra 36901 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where 3691DROP INDEX idx1 ON t1; 3692CREATE UNIQUE INDEX idx1 ON t1(ID_with_null); 3693EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL; 3694id select_type table type possible_keys key key_len ref rows Extra 36951 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where 3696DROP TABLE t1; 3697CREATE TABLE t1 ( 3698ID1_with_null int NULL, 3699ID2_with_null int NULL, 3700ID_better int NOT NULL, 3701INDEX idx1 (ID1_with_null, ID2_with_null), 3702INDEX idx2 (ID_better) 3703); 3704INSERT INTO t1 VALUES (1,1,1), (2,2,1), (3,null,3), (null,3,3), (null,null,3), 3705(3,null,3), (null,3,3), (null,null,3), (3,null,3), (null,3,3), (null,null,3); 3706INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; 3707INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; 3708INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; 3709INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; 3710INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; 3711INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; 3712ANALYZE TABLE t1; 3713Table Op Msg_type Msg_text 3714test.t1 analyze status Engine-independent statistics collected 3715test.t1 analyze status OK 3716SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null=3; 3717COUNT(*) 371824 3719SELECT COUNT(*) FROM t1 WHERE ID1_with_null=3 AND ID2_with_null IS NULL; 3720COUNT(*) 372124 3722SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null IS NULL; 3723COUNT(*) 3724192 3725SELECT COUNT(*) FROM t1 WHERE ID_better=1; 3726COUNT(*) 37272 3728EXPLAIN SELECT * FROM t1 3729WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ; 3730id select_type table type possible_keys key key_len ref rows Extra 37311 SIMPLE t1 ref|filter idx1,idx2 idx2|idx1 4|10 const 2 (6%) Using where; Using rowid filter 3732EXPLAIN SELECT * FROM t1 3733WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null=3 IS NULL ; 3734id select_type table type possible_keys key key_len ref rows Extra 37351 SIMPLE t1 ref idx1,idx2 idx2 4 const 2 Using where 3736EXPLAIN SELECT * FROM t1 3737WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL; 3738id select_type table type possible_keys key key_len ref rows Extra 37391 SIMPLE t1 ref idx1,idx2 idx2 4 const 2 Using where 3740DROP INDEX idx1 ON t1; 3741CREATE UNIQUE INDEX idx1 ON t1(ID1_with_null,ID2_with_null); 3742EXPLAIN SELECT * FROM t1 3743WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ; 3744id select_type table type possible_keys key key_len ref rows Extra 37451 SIMPLE t1 ref|filter idx1,idx2 idx2|idx1 4|10 const 2 (7%) Using where; Using rowid filter 3746EXPLAIN SELECT * FROM t1 3747WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null IS NULL ; 3748id select_type table type possible_keys key key_len ref rows Extra 37491 SIMPLE t1 ref idx1,idx2 idx2 4 const 2 Using where 3750EXPLAIN SELECT * FROM t1 3751WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL; 3752id select_type table type possible_keys key key_len ref rows Extra 37531 SIMPLE t1 ref idx1,idx2 idx2 4 const 2 Using where 3754EXPLAIN SELECT * FROM t1 3755WHERE ID_better=1 AND ID1_with_null IS NULL AND 3756(ID2_with_null=1 OR ID2_with_null=2); 3757id select_type table type possible_keys key key_len ref rows Extra 37581 SIMPLE t1 ref|filter idx1,idx2 idx1|idx2 5|4 const 2 (1%) Using index condition; Using where; Using rowid filter 3759DROP TABLE t1; 3760CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts)); 3761INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00"); 3762ANALYZE TABLE t1; 3763Table Op Msg_type Msg_text 3764test.t1 analyze status Engine-independent statistics collected 3765test.t1 analyze status OK 3766CREATE TABLE t2 (a INT, dt1 DATETIME, dt2 DATETIME, PRIMARY KEY (a)); 3767INSERT INTO t2 VALUES (30, "2006-01-01 00:00:00", "2999-12-31 00:00:00"); 3768INSERT INTO t2 SELECT a+1,dt1,dt2 FROM t2; 3769ANALYZE TABLE t2; 3770Table Op Msg_type Msg_text 3771test.t2 analyze status Engine-independent statistics collected 3772test.t2 analyze status OK 3773EXPLAIN 3774SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30 3775AND t1.ts BETWEEN t2.dt1 AND t2.dt2 3776AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31"; 3777id select_type table type possible_keys key key_len ref rows Extra 37781 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 37791 SIMPLE t1 range ts ts 4 NULL 2 Using index condition; Using where; Rowid-ordered scan 3780SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30 3781AND t1.ts BETWEEN t2.dt1 AND t2.dt2 3782AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31"; 3783a ts a dt1 dt2 378430 2006-01-03 23:00:00 30 2006-01-01 00:00:00 2999-12-31 00:00:00 3785DROP TABLE t1,t2; 3786create table t1 (a bigint unsigned); 3787insert into t1 values 3788(if(1, 9223372036854775808, 1)), 3789(case when 1 then 9223372036854775808 else 1 end), 3790(coalesce(9223372036854775808, 1)); 3791select * from t1; 3792a 37939223372036854775808 37949223372036854775808 37959223372036854775808 3796drop table t1; 3797create table t1 select 3798if(1, 9223372036854775808, 1) i, 3799case when 1 then 9223372036854775808 else 1 end c, 3800coalesce(9223372036854775808, 1) co; 3801show create table t1; 3802Table Create Table 3803t1 CREATE TABLE `t1` ( 3804 `i` decimal(19,0) NOT NULL, 3805 `c` decimal(19,0) NOT NULL, 3806 `co` decimal(19,0) NOT NULL 3807) ENGINE=MyISAM DEFAULT CHARSET=latin1 3808drop table t1; 3809select 3810if(1, cast(1111111111111111111 as unsigned), 1) i, 3811case when 1 then cast(1111111111111111111 as unsigned) else 1 end c, 3812coalesce(cast(1111111111111111111 as unsigned), 1) co; 3813i c co 38141111111111111111111 1111111111111111111 1111111111111111111 3815CREATE TABLE t1 (name varchar(255)); 3816CREATE TABLE t2 (name varchar(255), n int, KEY (name(3))); 3817INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa '), ('aa'); 3818INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc ',3); 3819INSERT INTO t2 VALUES (concat('cc ', 0x06), 4); 3820INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7); 3821INSERT INTO t2 VALUES ('bb ',8), ('aa',9), ('aa',10), ('bb',11); 3822SELECT * FROM t2; 3823name n 3824bb 1 3825aa 2 3826cc 3 3827cc 4 3828cc 5 3829bb 6 3830cc 7 3831bb 8 3832aa 9 3833aa 10 3834bb 11 3835SELECT * FROM t2 ORDER BY name; 3836name n 3837aa 2 3838aa 10 3839aa 9 3840bb 1 3841bb 8 3842bb 6 3843bb 11 3844cc 4 3845cc 7 3846cc 5 3847cc 3 3848SELECT name, LENGTH(name), n FROM t2 ORDER BY name; 3849name LENGTH(name) n 3850aa 2 2 3851aa 2 10 3852aa 2 9 3853bb 2 1 3854bb 3 8 3855bb 3 6 3856bb 2 11 3857cc 4 4 3858cc 3 7 3859cc 2 5 3860cc 5 3 3861EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; 3862id select_type table type possible_keys key key_len ref rows Extra 38631 SIMPLE t2 ref name name 6 const 4 Using where 3864SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; 3865name LENGTH(name) n 3866cc 5 3 3867cc 2 5 3868cc 3 7 3869EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; 3870id select_type table type possible_keys key key_len ref rows Extra 38711 SIMPLE t2 range name name 6 NULL 4 Using where 3872SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; 3873name LENGTH(name) n 3874cc 5 3 3875cc 4 4 3876cc 2 5 3877cc 3 7 3878EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; 3879id select_type table type possible_keys key key_len ref rows Extra 38801 SIMPLE t2 range name name 6 NULL 4 Using where; Using filesort 3881SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; 3882name LENGTH(name) n 3883cc 4 4 3884cc 5 3 3885cc 2 5 3886cc 3 7 3887EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; 3888id select_type table type possible_keys key key_len ref rows Extra 38891 SIMPLE t1 ALL NULL NULL NULL NULL 5 38901 SIMPLE t2 ref name name 6 test.t1.name 2 Using where 3891SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; 3892name name n 3893ccc NULL NULL 3894bb bb 1 3895bb bb 6 3896bb bb 8 3897bb bb 11 3898cc cc 3 3899cc cc 5 3900cc cc 7 3901aa aa 2 3902aa aa 9 3903aa aa 10 3904aa aa 2 3905aa aa 9 3906aa aa 10 3907DROP TABLE t1,t2; 3908CREATE TABLE t1 (name text); 3909CREATE TABLE t2 (name text, n int, KEY (name(3))); 3910INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa '), ('aa'); 3911INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc ',3); 3912INSERT INTO t2 VALUES (concat('cc ', 0x06), 4); 3913INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7); 3914INSERT INTO t2 VALUES ('bb ',8), ('aa',9), ('aa',10), ('bb',11); 3915SELECT * FROM t2; 3916name n 3917bb 1 3918aa 2 3919cc 3 3920cc 4 3921cc 5 3922bb 6 3923cc 7 3924bb 8 3925aa 9 3926aa 10 3927bb 11 3928SELECT * FROM t2 ORDER BY name; 3929name n 3930aa 2 3931aa 9 3932aa 10 3933bb 1 3934bb 6 3935bb 8 3936bb 11 3937cc 4 3938cc 3 3939cc 5 3940cc 7 3941SELECT name, LENGTH(name), n FROM t2 ORDER BY name; 3942name LENGTH(name) n 3943aa 2 2 3944aa 2 9 3945aa 2 10 3946bb 2 1 3947bb 3 6 3948bb 3 8 3949bb 2 11 3950cc 4 4 3951cc 5 3 3952cc 2 5 3953cc 3 7 3954EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; 3955id select_type table type possible_keys key key_len ref rows Extra 39561 SIMPLE t2 ref name name 6 const 4 Using where 3957SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; 3958name LENGTH(name) n 3959cc 5 3 3960cc 2 5 3961cc 3 7 3962EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; 3963id select_type table type possible_keys key key_len ref rows Extra 39641 SIMPLE t2 range name name 6 NULL 4 Using where 3965SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; 3966name LENGTH(name) n 3967cc 5 3 3968cc 4 4 3969cc 2 5 3970cc 3 7 3971EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; 3972id select_type table type possible_keys key key_len ref rows Extra 39731 SIMPLE t2 range name name 6 NULL 4 Using where; Using filesort 3974SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; 3975name LENGTH(name) n 3976cc 4 4 3977cc 5 3 3978cc 2 5 3979cc 3 7 3980EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; 3981id select_type table type possible_keys key key_len ref rows Extra 39821 SIMPLE t1 ALL NULL NULL NULL NULL 5 39831 SIMPLE t2 ref name name 6 test.t1.name 2 Using where 3984SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; 3985name name n 3986ccc NULL NULL 3987bb bb 1 3988bb bb 6 3989bb bb 8 3990bb bb 11 3991cc cc 3 3992cc cc 5 3993cc cc 7 3994aa aa 2 3995aa aa 9 3996aa aa 10 3997aa aa 2 3998aa aa 9 3999aa aa 10 4000DROP TABLE t1,t2; 4001CREATE TABLE t1 ( 4002access_id int NOT NULL default '0', 4003name varchar(20) default NULL, 4004rank int NOT NULL default '0', 4005KEY idx (access_id) 4006); 4007CREATE TABLE t2 ( 4008faq_group_id int NOT NULL default '0', 4009faq_id int NOT NULL default '0', 4010access_id int default NULL, 4011UNIQUE KEY idx1 (faq_id), 4012KEY idx2 (faq_group_id,faq_id) 4013); 4014INSERT INTO t1 VALUES 4015(1,'Everyone',2),(2,'Help',3),(3,'Technical Support',1),(4,'Chat User',4); 4016INSERT INTO t2 VALUES 4017(261,265,1),(490,494,1); 4018SELECT t2.faq_id 4019FROM t1 INNER JOIN t2 IGNORE INDEX (idx1) 4020ON (t1.access_id = t2.access_id) 4021LEFT JOIN t2 t 4022ON (t.faq_group_id = t2.faq_group_id AND 4023find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4')) 4024WHERE 4025t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265); 4026faq_id 4027265 4028SELECT t2.faq_id 4029FROM t1 INNER JOIN t2 4030ON (t1.access_id = t2.access_id) 4031LEFT JOIN t2 t 4032ON (t.faq_group_id = t2.faq_group_id AND 4033find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4')) 4034WHERE 4035t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265); 4036faq_id 4037265 4038DROP TABLE t1,t2; 4039CREATE TABLE t1 (a INT, b INT, KEY inx (b,a)); 4040INSERT INTO t1 VALUES (1,1), (1,2), (1,3), (1,4), (1,5), (1, 6), (1,7); 4041EXPLAIN SELECT COUNT(*) FROM t1 f1 INNER JOIN t1 f2 4042ON ( f1.b=f2.b AND f1.a<f2.a ) 4043WHERE 1 AND f1.b NOT IN (100,2232,3343,51111); 4044id select_type table type possible_keys key key_len ref rows Extra 40451 SIMPLE f1 range inx inx 5 NULL 7 Using where; Using index 40461 SIMPLE f2 ref inx inx 5 test.f1.b 1 Using where; Using index 4047DROP TABLE t1; 4048CREATE TABLE t1 (c1 INT, c2 INT); 4049INSERT INTO t1 VALUES (1,11), (2,22), (2,22); 4050EXPLAIN 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; 4051id select_type table type possible_keys key key_len ref rows Extra 40521 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where 405331 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 405432 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 4055EXPLAIN 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; 4056ERROR HY000: Too high level of nesting for select 4057DROP TABLE t1; 4058CREATE TABLE t1 ( 4059c1 int(11) NOT NULL AUTO_INCREMENT, 4060c2 varchar(1000) DEFAULT NULL, 4061c3 bigint(20) DEFAULT NULL, 4062c4 bigint(20) DEFAULT NULL, 4063PRIMARY KEY (c1) 4064); 4065EXPLAIN EXTENDED 4066SELECT join_2.c1 4067FROM 4068t1 AS join_0, 4069t1 AS join_1, 4070t1 AS join_2, 4071t1 AS join_3, 4072t1 AS join_4, 4073t1 AS join_5, 4074t1 AS join_6, 4075t1 AS join_7 4076WHERE 4077join_0.c1=join_1.c1 AND 4078join_1.c1=join_2.c1 AND 4079join_2.c1=join_3.c1 AND 4080join_3.c1=join_4.c1 AND 4081join_4.c1=join_5.c1 AND 4082join_5.c1=join_6.c1 AND 4083join_6.c1=join_7.c1 4084OR 4085join_0.c2 < '?' AND 4086join_1.c2 < '?' AND 4087join_2.c2 > '?' AND 4088join_2.c2 < '!' AND 4089join_3.c2 > '?' AND 4090join_4.c2 = '?' AND 4091join_5.c2 <> '?' AND 4092join_6.c2 <> '?' AND 4093join_7.c2 >= '?' AND 4094join_0.c1=join_1.c1 AND 4095join_1.c1=join_2.c1 AND 4096join_2.c1=join_3.c1 AND 4097join_3.c1=join_4.c1 AND 4098join_4.c1=join_5.c1 AND 4099join_5.c1=join_6.c1 AND 4100join_6.c1=join_7.c1 4101GROUP BY 4102join_3.c1, 4103join_2.c1, 4104join_7.c1, 4105join_1.c1, 4106join_0.c1; 4107id select_type table type possible_keys key key_len ref rows filtered Extra 41081 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 4109Warnings: 4110Note 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 4111SHOW WARNINGS; 4112Level Code Message 4113Note 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 4114DROP TABLE t1; 4115SELECT 1 AS ` `; 4116 41171 4118Warnings: 4119Warning 1474 Name ' ' has become '' 4120SELECT 1 AS ` `; 4121 41221 4123Warnings: 4124Warning 1474 Name ' ' has become '' 4125SELECT 1 AS ` x`; 4126x 41271 4128Warnings: 4129Warning 1466 Leading spaces are removed from name ' x' 4130CREATE VIEW v1 AS SELECT 1 AS ``; 4131ERROR 42000: Incorrect column name '' 4132CREATE VIEW v1 AS SELECT 1 AS ` `; 4133ERROR 42000: Incorrect column name ' ' 4134CREATE VIEW v1 AS SELECT 1 AS ` `; 4135ERROR 42000: Incorrect column name ' ' 4136CREATE VIEW v1 AS SELECT (SELECT 1 AS ` `); 4137ERROR 42000: Incorrect column name ' ' 4138CREATE VIEW v1 AS SELECT 1 AS ` x`; 4139Warnings: 4140Warning 1466 Leading spaces are removed from name ' x' 4141SELECT `x` FROM v1; 4142x 41431 4144ALTER VIEW v1 AS SELECT 1 AS ` `; 4145ERROR 42000: Incorrect column name ' ' 4146DROP VIEW v1; 4147select str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT' 4148 and '2007/10/20 00:00:00 GMT'; 4149str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT' 4150 and '2007/10/20 00:00:00 GMT' 41511 4152Warnings: 4153Warning 1292 Truncated incorrect datetime value: '2007/10/01 00:00:00 GMT' 4154Warning 1292 Truncated incorrect datetime value: '2007/10/20 00:00:00 GMT' 4155select str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6'; 4156str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6' 41571 4158Warnings: 4159Warning 1292 Truncated incorrect datetime value: '2007/10/01 00:00:00 GMT-6' 4160select str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/20 00:00:00 GMT-6'; 4161str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/20 00:00:00 GMT-6' 41621 4163Warnings: 4164Warning 1292 Truncated incorrect datetime value: '2007/10/20 00:00:00 GMT-6' 4165select str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6'; 4166str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6' 41670 4168Warnings: 4169Warning 1292 Truncated incorrect datetime value: '2007/10/2000:00:00 GMT-6' 4170select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6'; 4171str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6' 41721 4173Warnings: 4174Warning 1292 Truncated incorrect datetime value: '2007-10-1 00:00:00 GMT-6' 4175select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6'; 4176str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6' 41771 4178Warnings: 4179Warning 1292 Truncated incorrect date value: '2007-10-01 x00:00:00 GMT-6' 4180select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6'; 4181str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6' 41821 4183Warnings: 4184Warning 1292 Truncated incorrect datetime value: '2007-10-01 00:00:00 GMT-6' 4185select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6'; 4186str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6' 41871 4188Warnings: 4189Warning 1292 Truncated incorrect datetime value: '2007-10-01 00:x00:00 GMT-6' 4190select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6'; 4191str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6' 41921 4193Warnings: 4194Warning 1292 Truncated incorrect date value: '2007-10-01 x12:34:56 GMT-6' 4195select str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6'; 4196str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6' 41971 4198Warnings: 4199Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34x:56 GMT-6' 4200select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6'; 4201str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6' 42020 4203Warnings: 4204Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34x:56 GMT-6' 4205select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34:56'; 4206str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34:56' 42071 4208select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 12:00:00'; 4209str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 12:00:00' 42100 4211select str_to_date('2007-10-01 12','%Y-%m-%d %H') = '2007-10-01 12:00:00'; 4212str_to_date('2007-10-01 12','%Y-%m-%d %H') = '2007-10-01 12:00:00' 42131 4214select str_to_date('2007-10-01 12:34','%Y-%m-%d %H') = '2007-10-01 12:00:00'; 4215str_to_date('2007-10-01 12:34','%Y-%m-%d %H') = '2007-10-01 12:00:00' 42161 4217Warnings: 4218Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34' 4219select str_to_date('2007-02-30 12:34','%Y-%m-%d %H:%i') = '2007-02-30 12:34'; 4220str_to_date('2007-02-30 12:34','%Y-%m-%d %H:%i') = '2007-02-30 12:34' 42211 4222select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'; 4223str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34' 42241 4225select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00' 4226 and '2007/10/20 00:00:00'; 4227str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00' 4228 and '2007/10/20 00:00:00' 42291 4230set SQL_MODE=TRADITIONAL; 4231select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'; 4232str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34' 4233NULL 4234Warnings: 4235Warning 1411 Incorrect datetime value: '2007-10-00 12:34' for function str_to_date 4236select str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'; 4237str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34' 42380 4239select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34'; 4240str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34' 4241NULL 4242Warnings: 4243Warning 1411 Incorrect datetime value: '2007-10-00 12:34' for function str_to_date 4244select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01' 4245 and '2007/10/20'; 4246str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01' 4247 and '2007/10/20' 4248NULL 4249Warnings: 4250Warning 1411 Incorrect datetime value: '2007-10-00' for function str_to_date 4251set SQL_MODE=DEFAULT; 4252select str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20'; 4253str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20' 42541 4255Warnings: 4256Warning 1292 Truncated incorrect datetime value: '' 4257select str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20'; 4258str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20' 42590 4260select str_to_date('','%Y-%m-%d %H:%i') = '2007-10-01 12:34'; 4261str_to_date('','%Y-%m-%d %H:%i') = '2007-10-01 12:34' 42620 4263select str_to_date(NULL,'%Y-%m-%d %H:%i') = '2007-10-01 12:34'; 4264str_to_date(NULL,'%Y-%m-%d %H:%i') = '2007-10-01 12:34' 4265NULL 4266select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = ''; 4267str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '' 42680 4269Warnings: 4270Warning 1292 Truncated incorrect datetime value: '' 4271select str_to_date('1','%Y-%m-%d') = '1'; 4272str_to_date('1','%Y-%m-%d') = '1' 42730 4274Warnings: 4275Warning 1292 Truncated incorrect datetime value: '1' 4276select str_to_date('1','%Y-%m-%d') = '1'; 4277str_to_date('1','%Y-%m-%d') = '1' 42780 4279Warnings: 4280Warning 1292 Truncated incorrect datetime value: '1' 4281select str_to_date('','%Y-%m-%d') = ''; 4282str_to_date('','%Y-%m-%d') = '' 42831 4284Warnings: 4285Warning 1292 Truncated incorrect datetime value: '' 4286select str_to_date('2000-01-01','%Y-%m-%d') between '1000-01-01' and '2001-01-01'; 4287str_to_date('2000-01-01','%Y-%m-%d') between '1000-01-01' and '2001-01-01' 42881 4289select str_to_date('2000-01-01','%Y-%m-%d') between '1000-01-01' and NULL; 4290str_to_date('2000-01-01','%Y-%m-%d') between '1000-01-01' and NULL 4291NULL 4292select str_to_date('2000-01-01','%Y-%m-%d') between NULL and '2001-01-01'; 4293str_to_date('2000-01-01','%Y-%m-%d') between NULL and '2001-01-01' 4294NULL 4295select str_to_date('2000-01-01','%Y-%m-%d') between '2001-01-01' and NULL; 4296str_to_date('2000-01-01','%Y-%m-%d') between '2001-01-01' and NULL 42970 4298select str_to_date('2000-01-01','%Y-%m-%d') between NULL and '1000-01-01'; 4299str_to_date('2000-01-01','%Y-%m-%d') between NULL and '1000-01-01' 43000 4301select str_to_date('2000-01-01','%Y-%m-%d') between NULL and NULL; 4302str_to_date('2000-01-01','%Y-%m-%d') between NULL and NULL 4303NULL 4304CREATE TABLE t1 (c11 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY); 4305CREATE TABLE t2 (c21 INT UNSIGNED NOT NULL, 4306c22 INT DEFAULT NULL, 4307KEY(c21, c22)); 4308CREATE TABLE t3 (c31 INT UNSIGNED NOT NULL DEFAULT 0, 4309c32 INT DEFAULT NULL, 4310c33 INT NOT NULL, 4311c34 INT UNSIGNED DEFAULT 0, 4312KEY (c33, c34, c32)); 4313INSERT INTO t1 values (),(),(),(),(); 4314INSERT INTO t2 SELECT a.c11, b.c11 FROM t1 a, t1 b; 4315INSERT INTO t3 VALUES (1, 1, 1, 0), 4316(2, 2, 0, 0), 4317(3, 3, 1, 0), 4318(4, 4, 0, 0), 4319(5, 5, 1, 0); 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; 4324c32 43251 43261 43273 43283 43295 43305 4331SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND 4332t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND 4333t3.c33 = 1 AND t2.c22 in (1, 3) 4334ORDER BY c32 DESC; 4335c32 43365 43375 43383 43393 43401 43411 4342DROP TABLE t1, t2, t3; 4343 4344# 4345# Bug#30736: Row Size Too Large Error Creating a Table and 4346# Inserting Data. 4347# 4348DROP TABLE IF EXISTS t1; 4349DROP TABLE IF EXISTS t2; 4350 4351CREATE TABLE t1( 4352c1 DECIMAL(10, 2), 4353c2 FLOAT); 4354 4355INSERT INTO t1 VALUES (0, 1), (2, 3), (4, 5); 4356 4357CREATE TABLE t2( 4358c3 DECIMAL(10, 2)) 4359SELECT 4360c1 * c2 AS c3 4361FROM t1; 4362 4363SELECT * FROM t1; 4364c1 c2 43650.00 1 43662.00 3 43674.00 5 4368 4369SELECT * FROM t2; 4370c3 43710.00 43726.00 437320.00 4374 4375DROP TABLE t1; 4376DROP TABLE t2; 4377 4378CREATE TABLE t1 (c1 BIGINT NOT NULL); 4379INSERT INTO t1 (c1) VALUES (1); 4380SELECT * FROM t1 WHERE c1 > NULL + 1; 4381c1 4382DROP TABLE t1; 4383 4384CREATE TABLE t1 (a VARCHAR(10) NOT NULL PRIMARY KEY); 4385INSERT INTO t1 (a) VALUES ('foo0'), ('bar0'), ('baz0'); 4386SELECT * FROM t1 WHERE a IN (CONCAT('foo', 0), 'bar'); 4387a 4388foo0 4389DROP TABLE t1; 4390CREATE TABLE t1 (a INT, b INT); 4391CREATE TABLE t2 (a INT, c INT, KEY(a)); 4392INSERT INTO t1 VALUES (1, 1), (2, 2); 4393INSERT INTO t2 VALUES (1, 1), (1, 2), (1, 3), (1, 4), (1, 5), 4394(2, 1), (2, 2), (2, 3), (2, 4), (2, 5), 4395(3, 1), (3, 2), (3, 3), (3, 4), (3, 5), 4396(4, 1), (4, 2), (4, 3), (4, 4), (4, 5); 4397FLUSH STATUS; 4398SELECT DISTINCT b FROM t1 LEFT JOIN t2 USING(a) WHERE c <= 3; 4399b 44001 44012 4402SHOW STATUS LIKE 'Handler_read%'; 4403Variable_name Value 4404Handler_read_first 0 4405Handler_read_key 2 4406Handler_read_last 0 4407Handler_read_next 10 4408Handler_read_prev 0 4409Handler_read_retry 0 4410Handler_read_rnd 10 4411Handler_read_rnd_deleted 1 4412Handler_read_rnd_next 6 4413DROP TABLE t1, t2; 4414CREATE TABLE t1 (f1 bigint(20) NOT NULL default '0', 4415f2 int(11) NOT NULL default '0', 4416f3 bigint(20) NOT NULL default '0', 4417f4 varchar(255) NOT NULL default '', 4418PRIMARY KEY (f1), 4419KEY key1 (f4), 4420KEY key2 (f2)); 4421CREATE TABLE t2 (f1 int(11) NOT NULL default '0', 4422f2 enum('A1','A2','A3') NOT NULL default 'A1', 4423f3 int(11) NOT NULL default '0', 4424PRIMARY KEY (f1), 4425KEY key1 (f3)); 4426CREATE TABLE t3 (f1 bigint(20) NOT NULL default '0', 4427f2 datetime NOT NULL default '1980-01-01 00:00:00', 4428PRIMARY KEY (f1)); 4429insert into t1 values (1, 1, 1, 'abc'); 4430insert into t1 values (2, 1, 2, 'def'); 4431insert into t1 values (3, 1, 2, 'def'); 4432insert into t2 values (1, 'A1', 1); 4433insert into t3 values (1, '1980-01-01'); 4434SELECT a.f3, cr.f4, count(*) count 4435FROM t2 a 4436STRAIGHT_JOIN t1 cr ON cr.f2 = a.f1 4437LEFT JOIN 4438(t1 cr2 4439JOIN t3 ae2 ON cr2.f3 = ae2.f1 4440) ON a.f1 = cr2.f2 AND ae2.f2 < now() - INTERVAL 7 DAY AND 4441cr.f4 = cr2.f4 4442GROUP BY a.f3, cr.f4; 4443f3 f4 count 44441 abc 1 44451 def 2 4446drop table t1, t2, t3; 4447CREATE TABLE t1 (a INT KEY, b INT); 4448INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4); 4449EXPLAIN EXTENDED SELECT a, b FROM t1 WHERE a > 1 AND a = b LIMIT 2; 4450id select_type table type possible_keys key key_len ref rows filtered Extra 44511 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using index condition; Using where; Rowid-ordered scan 4452Warnings: 4453Note 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 4454EXPLAIN EXTENDED SELECT a, b FROM t1 WHERE a > 1 AND b = a LIMIT 2; 4455id select_type table type possible_keys key key_len ref rows filtered Extra 44561 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using index condition; Using where; Rowid-ordered scan 4457Warnings: 4458Note 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 4459DROP TABLE t1; 4460# 4461# Bug#47019: Assertion failed: 0, file .\rt_mbr.c, line 138 when 4462# forcing a spatial index 4463# 4464CREATE TABLE t1(a LINESTRING NOT NULL, SPATIAL KEY(a)); 4465INSERT INTO t1 VALUES 4466(GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1)')), 4467(GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1)')); 4468EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2; 4469id select_type table type possible_keys key key_len ref rows Extra 44701 SIMPLE t1 ALL NULL NULL NULL NULL 2 44711 SIMPLE t2 hash_ALL a #hash#$hj 6 test.t1.a 2 Using where; Using join buffer (flat, BNLH join) 4472SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2; 44731 44741 44751 44761 44771 4478EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a); 4479id select_type table type possible_keys key key_len ref rows Extra 44801 SIMPLE t1 ALL NULL NULL NULL NULL 2 44811 SIMPLE t2 hash_ALL a #hash#$hj 6 test.t1.a 2 Using where; Using join buffer (flat, BNLH join) 4482SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a); 44831 44841 44851 44861 44871 4488DROP TABLE t1; 4489# 4490# Bug #48291 : crash with row() operator,select into @var, and 4491# subquery returning multiple rows 4492# 4493CREATE TABLE t1(a INT); 4494INSERT INTO t1 VALUES (2),(3); 4495# Should not crash 4496SELECT 1 FROM t1 WHERE a <> 1 AND NOT 4497ROW(1,a) <=> ROW(1,(SELECT 1 FROM t1)) 4498INTO @var0; 4499ERROR 21000: Subquery returns more than 1 row 4500DROP TABLE t1; 4501# 4502# Bug #48458: simple query tries to allocate enormous amount of 4503# memory 4504# 4505CREATE TABLE t1(a INT NOT NULL, b YEAR); 4506INSERT IGNORE INTO t1 VALUES (); 4507Warnings: 4508Warning 1364 Field 'a' doesn't have a default value 4509CREATE TABLE t2(c INT); 4510# Should not err out because of out-of-memory 4511SELECT 1 FROM t2 JOIN t1 ON 1=1 4512WHERE a != '1' AND NOT a >= b OR NOT ROW(b,a )<> ROW(a,a); 45131 4514DROP TABLE t1,t2; 4515# 4516# Bug #49199: Optimizer handles incorrectly: 4517# field='const1' AND field='const2' in some cases 4518 4519CREATE TABLE t1(a DATETIME 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 00:00:00 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 00:00:00' AS `a` from dual where 1 4529DROP TABLE t1; 4530CREATE TABLE t1(a DATE NOT NULL); 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 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' AS `a` from dual where 1 4540DROP TABLE t1; 4541CREATE TABLE t1(a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); 4542INSERT INTO t1 VALUES('2001-01-01'); 4543SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; 4544a 45452001-01-01 00:00:00 4546EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='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` from dual where 1 4551DROP TABLE t1; 4552CREATE TABLE t1(a DATETIME NOT NULL, b DATE 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 45562001-01-01 00:00:00 2001-01-01 4557EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00'; 4558id select_type table type possible_keys key key_len ref rows filtered Extra 45591 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 4560Warnings: 4561Note 1003 select '2001-01-01 00:00:00' AS `a`,'2001-01-01' AS `b` from dual where 1 4562DROP TABLE t1; 4563CREATE TABLE t1(a DATETIME NOT NULL, b VARCHAR(20) NOT NULL); 4564INSERT INTO t1 VALUES('2001-01-01', '2001-01-01'); 4565SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00'; 4566a b 4567EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00'; 4568id select_type table type possible_keys key key_len ref rows filtered Extra 45691 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 4570Warnings: 4571Note 1003 select '2001-01-01 00:00:00' AS `a`,'2001-01-01' AS `b` from dual where 0 4572SELECT * FROM t1 WHERE a='2001-01-01 00:00:00' AND a=b AND b='2001-01-01'; 4573a b 45742001-01-01 00:00:00 2001-01-01 4575EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01 00:00:00' AND a=b AND b='2001-01-01'; 4576id select_type table type possible_keys key key_len ref rows filtered Extra 45771 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 4578Warnings: 4579Note 1003 select '2001-01-01 00:00:00' AS `a`,'2001-01-01' AS `b` from dual where 1 4580DROP TABLE t1; 4581CREATE TABLE t1(a DATETIME NOT NULL, b DATE NOT NULL); 4582INSERT INTO t1 VALUES('2001-01-01', '2001-01-01'); 4583SELECT x.a, y.a, z.a FROM t1 x 4584JOIN t1 y ON x.a=y.a 4585JOIN t1 z ON y.a=z.a 4586WHERE x.a='2001-01-01' AND z.a='2001-01-01 00:00:00'; 4587a a a 45882001-01-01 00:00:00 2001-01-01 00:00:00 2001-01-01 00:00:00 4589EXPLAIN EXTENDED SELECT x.a, y.a, z.a FROM t1 x 4590JOIN t1 y ON x.a=y.a 4591JOIN t1 z ON y.a=z.a 4592WHERE x.a='2001-01-01' AND z.a='2001-01-01 00:00:00'; 4593id select_type table type possible_keys key key_len ref rows filtered Extra 45941 SIMPLE x system NULL NULL NULL NULL 1 100.00 45951 SIMPLE y system NULL NULL NULL NULL 1 100.00 45961 SIMPLE z system NULL NULL NULL NULL 1 100.00 4597Warnings: 4598Note 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 4599DROP TABLE t1; 4600# 4601# Bug #49897: crash in ptr_compare when char(0) NOT NULL 4602# column is used for ORDER BY 4603# 4604SET @old_sort_buffer_size= @@session.sort_buffer_size; 4605SET @@sort_buffer_size= 40000; 4606CREATE TABLE t1(a CHAR(0) NOT NULL); 4607INSERT IGNORE INTO t1 VALUES (0), (0), (0); 4608INSERT INTO t1 SELECT t11.a FROM t1 t11, t1 t12; 4609INSERT INTO t1 SELECT t11.a FROM t1 t11, t1 t12; 4610INSERT INTO t1 SELECT t11.a FROM t1 t11, t1 t12; 4611EXPLAIN SELECT a FROM t1 ORDER BY a; 4612id select_type table type possible_keys key key_len ref rows Extra 46131 SIMPLE t1 ALL NULL NULL NULL NULL 24492 4614SELECT a FROM t1 ORDER BY a; 4615DROP TABLE t1; 4616CREATE TABLE t1(a CHAR(0) NOT NULL, b CHAR(0) NOT NULL, c int); 4617INSERT IGNORE INTO t1 VALUES (0, 0, 0), (0, 0, 2), (0, 0, 1); 4618INSERT INTO t1 SELECT t11.a, t11.b, t11.c FROM t1 t11, t1 t12; 4619INSERT INTO t1 SELECT t11.a, t11.b, t11.c FROM t1 t11, t1 t12; 4620INSERT INTO t1 SELECT t11.a, t11.b, t11.c FROM t1 t11, t1 t12; 4621EXPLAIN SELECT a FROM t1 ORDER BY a LIMIT 5; 4622id select_type table type possible_keys key key_len ref rows Extra 46231 SIMPLE t1 ALL NULL NULL NULL NULL 24492 4624SELECT a FROM t1 ORDER BY a LIMIT 5; 4625a 4626 4627 4628 4629 4630 4631EXPLAIN SELECT * FROM t1 ORDER BY a, b LIMIT 5; 4632id select_type table type possible_keys key key_len ref rows Extra 46331 SIMPLE t1 ALL NULL NULL NULL NULL 24492 4634SELECT * FROM t1 ORDER BY a, b LIMIT 5; 4635a b c 4636 0 4637 2 4638 1 4639 0 4640 2 4641EXPLAIN SELECT * FROM t1 ORDER BY a, b, c LIMIT 5; 4642id select_type table type possible_keys key key_len ref rows Extra 46431 SIMPLE t1 ALL NULL NULL NULL NULL 24492 Using filesort 4644SELECT * FROM t1 ORDER BY a, b, c LIMIT 5; 4645a b c 4646 0 4647 0 4648 0 4649 0 4650 0 4651EXPLAIN SELECT * FROM t1 ORDER BY c, a LIMIT 5; 4652id select_type table type possible_keys key key_len ref rows Extra 46531 SIMPLE t1 ALL NULL NULL NULL NULL 24492 Using filesort 4654SELECT * FROM t1 ORDER BY c, a LIMIT 5; 4655a b c 4656 0 4657 0 4658 0 4659 0 4660 0 4661SET @@sort_buffer_size= @old_sort_buffer_size; 4662DROP TABLE t1; 4663End of 5.0 tests 4664create table t1(a INT, KEY (a)); 4665INSERT INTO t1 VALUES (1),(2),(3),(4),(5); 4666SELECT a FROM t1 ORDER BY a LIMIT 2; 4667a 46681 46692 4670SELECT a FROM t1 ORDER BY a LIMIT 2,4294967296; 4671a 46723 46734 46745 4675SELECT a FROM t1 ORDER BY a LIMIT 2,4294967297; 4676a 46773 46784 46795 4680DROP TABLE t1; 4681CREATE TABLE t1 (date_key date); 4682CREATE TABLE t2 ( 4683pk int, 4684int_nokey int, 4685int_key int, 4686date_key date NOT NULL, 4687date_nokey date, 4688varchar_key varchar(1) 4689); 4690INSERT INTO t2 VALUES 4691(1,1,1,'0000-00-00',NULL,NULL), 4692(1,1,1,'0000-00-00',NULL,NULL); 4693SELECT 1 FROM t2 WHERE pk > ANY (SELECT 1 FROM t2); 46941 4695SELECT COUNT(DISTINCT 1) FROM t2 4696WHERE date_key = (SELECT 1 FROM t1 WHERE t2.date_key IS NULL) GROUP BY pk; 4697COUNT(DISTINCT 1) 4698SELECT date_nokey FROM t2 4699WHERE int_key IN (SELECT 1 FROM t1) 4700HAVING date_nokey = '10:41:7' 4701ORDER BY date_key; 4702date_nokey 4703Warnings: 4704Warning 1292 Truncated incorrect datetime value: '10:41:7' 4705DROP TABLE t1,t2; 4706CREATE TABLE t1 (a INT NOT NULL, b INT); 4707INSERT INTO t1 VALUES (1, 1); 4708EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND a=a) OR b > 2; 4709id select_type table type possible_keys key key_len ref rows filtered Extra 47101 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 4711Warnings: 4712Note 1003 select 1 AS `a`,1 AS `b` from dual where 1 4713SELECT * FROM t1 WHERE (a=a AND a=a) OR b > 2; 4714a b 47151 1 4716DROP TABLE t1; 4717CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, c INT NOT NULL); 4718EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND b=b AND c=c) OR b > 20; 4719id select_type table type possible_keys key key_len ref rows filtered Extra 47201 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 Const row not found 4721Warnings: 4722Note 1003 select NULL AS `a`,NULL AS `b`,NULL AS `c` from `test`.`t1` where 1 4723EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND a=a AND b=b) OR b > 20; 4724id select_type table type possible_keys key key_len ref rows filtered Extra 47251 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 Const row not found 4726Warnings: 4727Note 1003 select NULL AS `a`,NULL AS `b`,NULL AS `c` from `test`.`t1` where 1 4728EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND b=b AND a=a) OR b > 20; 4729id select_type table type possible_keys key key_len ref rows filtered Extra 47301 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 Const row not found 4731Warnings: 4732Note 1003 select NULL AS `a`,NULL AS `b`,NULL AS `c` from `test`.`t1` where 1 4733DROP TABLE t1; 4734# 4735# Bug#45266: Uninitialized variable lead to an empty result. 4736# 4737drop table if exists A,AA,B,BB; 4738CREATE TABLE `A` ( 4739`pk` int(11) NOT NULL AUTO_INCREMENT, 4740`date_key` date NOT NULL, 4741`date_nokey` date NOT NULL, 4742`datetime_key` datetime NOT NULL, 4743`int_nokey` int(11) NOT NULL, 4744`time_key` time NOT NULL, 4745`time_nokey` time NOT NULL, 4746PRIMARY KEY (`pk`), 4747KEY `date_key` (`date_key`), 4748KEY `time_key` (`time_key`), 4749KEY `datetime_key` (`datetime_key`) 4750); 4751CREATE TABLE `AA` ( 4752`pk` int(11) NOT NULL AUTO_INCREMENT, 4753`int_nokey` int(11) NOT NULL, 4754`time_key` time NOT NULL, 4755KEY `time_key` (`time_key`), 4756PRIMARY KEY (`pk`) 4757); 4758CREATE TABLE `B` ( 4759`date_nokey` date NOT NULL, 4760`date_key` date NOT NULL, 4761`time_key` time NOT NULL, 4762`datetime_nokey` datetime NOT NULL, 4763`varchar_key` varchar(1) NOT NULL, 4764KEY `date_key` (`date_key`), 4765KEY `time_key` (`time_key`), 4766KEY `varchar_key` (`varchar_key`) 4767); 4768INSERT 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'); 4769CREATE TABLE `BB` ( 4770`pk` int(11) NOT NULL AUTO_INCREMENT, 4771`int_nokey` int(11) NOT NULL, 4772`date_key` date NOT NULL, 4773`varchar_nokey` varchar(1) NOT NULL, 4774`date_nokey` date NOT NULL, 4775PRIMARY KEY (`pk`), 4776KEY `date_key` (`date_key`) 4777); 4778INSERT INTO `BB` VALUES (10,8,'0000-00-00','i','0000-00-00'),(11,0,'2005-08-18','','2005-08-18'); 4779SELECT table1 . `pk` AS field1 4780FROM 4781(BB AS table1 INNER JOIN 4782(AA AS table2 STRAIGHT_JOIN A AS table3 4783ON ( table3 . `date_key` = table2 . `pk` )) 4784ON ( table3 . `datetime_key` = table2 . `int_nokey` )) 4785WHERE ( table3 . `date_key` <= 4 AND table2 . `pk` = table1 . `varchar_nokey`) 4786GROUP BY field1 ; 4787field1 4788SELECT table3 .`date_key` field1 4789FROM 4790B table1 LEFT JOIN B table3 JOIN 4791(BB table6 JOIN A table7 ON table6 .`varchar_nokey`) 4792ON table6 .`int_nokey` ON table6 .`date_key` 4793 WHERE NOT ( table1 .`varchar_key` AND table7 .`pk`) GROUP BY field1; 4794field1 4795NULL 4796SELECT table4 . `time_nokey` AS field1 FROM 4797(AA AS table1 CROSS JOIN 4798(AA AS table2 STRAIGHT_JOIN 4799(B AS table3 STRAIGHT_JOIN A AS table4 4800ON ( table4 . `date_key` = table3 . `time_key` )) 4801ON ( table4 . `pk` = table3 . `date_nokey` )) 4802ON ( table4 . `time_key` = table3 . `datetime_nokey` )) 4803WHERE ( table4 . `time_key` < table1 . `time_key` AND 4804table1 . `int_nokey` != 'f') 4805GROUP BY field1 ORDER BY field1 , field1; 4806field1 4807SELECT table1 .`time_key` field2 FROM B table1 LEFT JOIN BB JOIN A table5 ON table5 .`date_nokey` ON table5 .`int_nokey` GROUP BY field2; 4808field2 480900:05:48 481015:13:38 4811drop table A,AA,B,BB; 4812#end of test for bug#45266 4813# 4814# Bug#33546: Slowdown on re-evaluation of constant expressions. 4815# 4816CREATE TABLE t1 (a INT); 4817INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); 4818CREATE TABLE t2 (b INT); 4819INSERT INTO t2 VALUES (2); 4820SELECT * FROM t1 WHERE a = 1 + 1; 4821a 48222 4823EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = 1 + 1; 4824id select_type table type possible_keys key key_len ref rows filtered Extra 48251 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where 4826Warnings: 4827Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = <cache>(1 + 1) 4828SELECT * FROM t1 HAVING a = 1 + 1; 4829a 48302 4831EXPLAIN EXTENDED SELECT * FROM t1 HAVING a = 1 + 1; 4832id select_type table type possible_keys key key_len ref rows filtered Extra 48331 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 4834Warnings: 4835Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` having `test`.`t1`.`a` = <cache>(1 + 1) 4836SELECT * FROM t1, t2 WHERE a = b + (1 + 1); 4837a b 48384 2 4839EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE a = b + (1 + 1); 4840id select_type table type possible_keys key key_len ref rows filtered Extra 48411 SIMPLE t2 system NULL NULL NULL NULL 1 100.00 48421 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where 4843Warnings: 4844Note 1003 select `test`.`t1`.`a` AS `a`,2 AS `b` from `test`.`t1` where `test`.`t1`.`a` = <cache>(2 + (1 + 1)) 4845SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1; 4846b a 48472 3 4848EXPLAIN EXTENDED SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1; 4849id select_type table type possible_keys key key_len ref rows filtered Extra 48501 SIMPLE t2 system NULL NULL NULL NULL 1 100.00 48511 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where 4852Warnings: 4853Note 1003 select 2 AS `b`,`test`.`t1`.`a` AS `a` from `test`.`t1` where 1 4854EXPLAIN EXTENDED SELECT * FROM t1 WHERE a > UNIX_TIMESTAMP('2009-03-10 00:00:00'); 4855id select_type table type possible_keys key key_len ref rows filtered Extra 48561 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where 4857Warnings: 4858Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > <cache>(unix_timestamp('2009-03-10 00:00:00')) 4859CREATE FUNCTION f1() RETURNS INT DETERMINISTIC 4860BEGIN 4861SET @cnt := @cnt + 1; 4862RETURN 1; 4863END;| 4864SET @cnt := 0; 4865SELECT * FROM t1 WHERE a = f1(); 4866a 48671 4868SELECT @cnt; 4869@cnt 48701 4871EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = f1(); 4872id select_type table type possible_keys key key_len ref rows filtered Extra 48731 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where 4874Warnings: 4875Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = <cache>(`f1`()) 4876DROP TABLE t1, t2; 4877DROP FUNCTION f1; 4878# End of bug#33546 4879# 4880# BUG#48052: Valgrind warning - uninitialized value in init_read_record() 4881# 4882CREATE TABLE t1 ( 4883pk int(11) NOT NULL, 4884i int(11) DEFAULT NULL, 4885v varchar(1) DEFAULT NULL, 4886PRIMARY KEY (pk) 4887); 4888INSERT INTO t1 VALUES (2,7,'m'); 4889INSERT INTO t1 VALUES (3,9,'m'); 4890SELECT v 4891FROM t1 4892WHERE NOT pk > 0 4893HAVING v <= 't' 4894ORDER BY pk; 4895v 4896DROP TABLE t1; 4897# 4898# Bug#49489 Uninitialized cache led to a wrong result. 4899# 4900CREATE TABLE t1(c1 DOUBLE(5,4)); 4901INSERT INTO t1 VALUES (9.1234); 4902SELECT * FROM t1 WHERE c1 < 9.12345; 4903c1 49049.1234 4905DROP TABLE t1; 4906# End of test for bug#49489. 4907# 4908# Bug #49517: Inconsistent behavior while using 4909# NULLable BIGINT and INT columns in comparison 4910# 4911CREATE TABLE t1(a BIGINT UNSIGNED NOT NULL, b BIGINT NULL, c INT NULL); 4912INSERT INTO t1 VALUES(105, NULL, NULL); 4913SELECT * FROM t1 WHERE b < 102; 4914a b c 4915SELECT * FROM t1 WHERE c < 102; 4916a b c 4917SELECT * FROM t1 WHERE 102 < b; 4918a b c 4919SELECT * FROM t1 WHERE 102 < c; 4920a b c 4921DROP TABLE t1; 4922# 4923# Bug #54459: Assertion failed: param.sort_length, 4924# file .\filesort.cc, line 149 (part II) 4925# 4926CREATE TABLE t1(a ENUM('') NOT NULL); 4927INSERT INTO t1 VALUES (), (), (); 4928EXPLAIN SELECT 1 FROM t1 ORDER BY a COLLATE latin1_german2_ci; 4929id select_type table type possible_keys key key_len ref rows Extra 49301 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort 4931SELECT 1 FROM t1 ORDER BY a COLLATE latin1_german2_ci; 49321 49331 49341 49351 4936DROP TABLE t1; 4937# 4938# Bug #702310: usage of 2 join buffers after ref access to an empty table 4939# 4940CREATE TABLE t1 (f1 int) ; 4941INSERT INTO t1 VALUES (9); 4942CREATE TABLE t2 (f1 int); 4943INSERT INTO t2 VALUES (3),(7),(18); 4944INSERT INTO t2 VALUES (3),(7),(18); 4945INSERT INTO t2 VALUES (3),(7),(18); 4946INSERT INTO t2 VALUES (3),(7),(18); 4947CREATE TABLE t3 (f1 int); 4948INSERT INTO t3 VALUES (17); 4949CREATE TABLE t4 (f1 int PRIMARY KEY, f2 varchar(1024)) ; 4950CREATE TABLE t5 (f1 int) ; 4951INSERT INTO t5 VALUES (20),(5); 4952CREATE TABLE t6(f1 int); 4953INSERT INTO t6 VALUES (9),(7); 4954SET @save_join_buffer_size=@@join_buffer_size,@@join_buffer_size = 2176; 4955EXPLAIN 4956SELECT STRAIGHT_JOIN * FROM t2, (t1 LEFT JOIN (t3,t4) ON t1.f1 = t4.f1), t5, t6; 4957id select_type table type possible_keys key key_len ref rows Extra 49581 SIMPLE t2 ALL NULL NULL NULL NULL 12 49591 SIMPLE t1 ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) 49601 SIMPLE t3 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) 49611 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t1.f1 1 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 49621 SIMPLE t5 ALL NULL NULL NULL NULL 2 Using join buffer (incremental, BNL join) 49631 SIMPLE t6 ALL NULL NULL NULL NULL 2 Using join buffer (incremental, BNL join) 4964SELECT STRAIGHT_JOIN * FROM t2, (t1 LEFT JOIN (t3,t4) ON t1.f1 = t4.f1), t5, t6; 4965f1 f1 f1 f1 f2 f1 f1 49663 9 NULL NULL NULL 20 9 49677 9 NULL NULL NULL 20 9 496818 9 NULL NULL NULL 20 9 49693 9 NULL NULL NULL 20 9 49707 9 NULL NULL NULL 20 9 497118 9 NULL NULL NULL 20 9 49723 9 NULL NULL NULL 20 9 49737 9 NULL NULL NULL 20 9 497418 9 NULL NULL NULL 20 9 49753 9 NULL NULL NULL 20 9 49767 9 NULL NULL NULL 20 9 497718 9 NULL NULL NULL 20 9 49783 9 NULL NULL NULL 5 9 49797 9 NULL NULL NULL 5 9 498018 9 NULL NULL NULL 5 9 49813 9 NULL NULL NULL 5 9 49827 9 NULL NULL NULL 5 9 498318 9 NULL NULL NULL 5 9 49843 9 NULL NULL NULL 5 9 49857 9 NULL NULL NULL 5 9 498618 9 NULL NULL NULL 5 9 49873 9 NULL NULL NULL 5 9 49887 9 NULL NULL NULL 5 9 498918 9 NULL NULL NULL 5 9 49903 9 NULL NULL NULL 20 7 49917 9 NULL NULL NULL 20 7 499218 9 NULL NULL NULL 20 7 49933 9 NULL NULL NULL 20 7 49947 9 NULL NULL NULL 20 7 499518 9 NULL NULL NULL 20 7 49963 9 NULL NULL NULL 20 7 49977 9 NULL NULL NULL 20 7 499818 9 NULL NULL NULL 20 7 49993 9 NULL NULL NULL 20 7 50007 9 NULL NULL NULL 20 7 500118 9 NULL NULL NULL 20 7 50023 9 NULL NULL NULL 5 7 50037 9 NULL NULL NULL 5 7 500418 9 NULL NULL NULL 5 7 50053 9 NULL NULL NULL 5 7 50067 9 NULL NULL NULL 5 7 500718 9 NULL NULL NULL 5 7 50083 9 NULL NULL NULL 5 7 50097 9 NULL NULL NULL 5 7 501018 9 NULL NULL NULL 5 7 50113 9 NULL NULL NULL 5 7 50127 9 NULL NULL NULL 5 7 501318 9 NULL NULL NULL 5 7 5014SET SESSION join_buffer_size = @save_join_buffer_size; 5015DROP TABLE t1,t2,t3,t4,t5,t6; 5016# 5017# Bug #698882: best equality substitution not applied to ref 5018# 5019CREATE TABLE t1 (a1 int NOT NULL, b1 char(10), INDEX idx (a1)); 5020CREATE TABLE t2 (a2 int NOT NULL, b2 char(10), INDEX idx (a2)); 5021CREATE TABLE t3 (a3 int NOT NULL, b3 char(10), INDEX idx (a3)); 5022INSERT INTO t1 VALUES (2,'xx'), (1,'xxx'), (11,'xxxxxxx'); 5023INSERT INTO t2 VALUES 5024(7,'yyyy'), (2,'y'), (3,'yyy'), (1,'yy'), (1,'yyyyy'), 5025(3,'yy'), (1,'y'), (4,'yyy'), (7,'y'), (4,'yyyyy'), (7,'yyy'), 5026(7,'yyyy'), (2,'yy'), (3,'yyy'), (1,'yyyyyyyy'), (1,'yyyyy'), 5027(3,'yy'), (1,'yyy'), (4,'yyy'), (7,'y'), (4,'yyyyy'), (7,'yyy'); 5028INSERT INTO t3 VALUES 5029(9,'zzzzzzz'), (2,'zzzzz'), (1,'z'), (9,'zz'), (1,'zz'), (5,'zzzzzzz'), 5030(4,'zz'), (3,'z'), (5,'zzzzzz'), (3,'zz'), (4,'zzzz'), (3,'z'), 5031(9,'zzzzzzzz'), (2,'zz'), (1,'zz'), (9,'zzz'), (1,'zzz'), (5,'zzzzzzzz'), 5032(4,'zzz'), (3,'zz'), (5,'zzzzzzz'), (3,'zzz'), (4,'zzzzz'), (3,'zz'), 5033(9,'zzzzzz'), (2,'zzzz'), (1,'zzz'), (9,'z'), (1,'z'), (5,'zzzzzz'), 5034(4,'z'), (3,'zzz'), (5,'zzzzz'), (3,'z'), (4,'zzz'), (3,'zzzz'), 5035(9,'zzzzz'), (2,'zzz'), (1,'zzzz'), (9,'zzz'), (1,'zzzz'), (5,'zzzzz'), 5036(4,'zzz'), (3,'zzzz'), (5,'zzzz'), (3,'zzz'), (4,'zz'), (3,'zzzzz'); 5037set @tmp= @@optimizer_switch; 5038SET SESSION optimizer_switch='index_condition_pushdown=off'; 5039EXPLAIN SELECT * from t1,t2,t3 WHERE t3.a3=t1.a1 AND t2.a2=t1.a1; 5040id select_type table type possible_keys key key_len ref rows Extra 50411 SIMPLE t1 ALL idx NULL NULL NULL 3 50421 SIMPLE t2 ref idx idx 4 test.t1.a1 2 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 50431 SIMPLE t3 ref idx idx 4 test.t1.a1 5 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 5044EXPLAIN SELECT * FROM t1,t2,t3 WHERE t2.a2=t1.a1 AND t3.a3=t1.a1; 5045id select_type table type possible_keys key key_len ref rows Extra 50461 SIMPLE t1 ALL idx NULL NULL NULL 3 50471 SIMPLE t2 ref idx idx 4 test.t1.a1 2 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 50481 SIMPLE t3 ref idx idx 4 test.t1.a1 5 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 5049EXPLAIN SELECT * FROM t1,t2,t3 WHERE t2.a2=t1.a1 AND t3.a3=t2.a2; 5050id select_type table type possible_keys key key_len ref rows Extra 50511 SIMPLE t1 ALL idx NULL NULL NULL 3 50521 SIMPLE t2 ref idx idx 4 test.t1.a1 2 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 50531 SIMPLE t3 ref idx idx 4 test.t1.a1 5 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 5054SELECT * from t1,t2,t3 5055WHERE t3.a3=t1.a1 AND t2.a2=t1.a1 AND 5056LENGTH(CONCAT(CONCAT(t1.b1,t2.b2),t3.b3)) <= 7; 5057a1 b1 a2 b2 a3 b3 50581 xxx 1 y 1 z 50591 xxx 1 y 1 z 50601 xxx 1 y 1 zz 50611 xxx 1 y 1 zz 50621 xxx 1 y 1 zzz 50631 xxx 1 y 1 zzz 50641 xxx 1 yy 1 z 50651 xxx 1 yy 1 z 50661 xxx 1 yy 1 zz 50671 xxx 1 yy 1 zz 50681 xxx 1 yyy 1 z 50691 xxx 1 yyy 1 z 50702 xx 2 y 2 zz 50712 xx 2 y 2 zzz 50722 xx 2 y 2 zzzz 50732 xx 2 yy 2 zz 50742 xx 2 yy 2 zzz 5075SELECT * FROM t1,t2,t3 5076WHERE t2.a2=t1.a1 AND t3.a3=t1.a1 AND 5077LENGTH(CONCAT(CONCAT(t1.b1,t2.b2),t3.b3)) <= 7; 5078a1 b1 a2 b2 a3 b3 50791 xxx 1 y 1 z 50801 xxx 1 y 1 z 50811 xxx 1 y 1 zz 50821 xxx 1 y 1 zz 50831 xxx 1 y 1 zzz 50841 xxx 1 y 1 zzz 50851 xxx 1 yy 1 z 50861 xxx 1 yy 1 z 50871 xxx 1 yy 1 zz 50881 xxx 1 yy 1 zz 50891 xxx 1 yyy 1 z 50901 xxx 1 yyy 1 z 50912 xx 2 y 2 zz 50922 xx 2 y 2 zzz 50932 xx 2 y 2 zzzz 50942 xx 2 yy 2 zz 50952 xx 2 yy 2 zzz 5096SELECT * FROM t1,t2,t3 5097WHERE t2.a2=t1.a1 AND t3.a3=t2.a2 AND 5098LENGTH(CONCAT(CONCAT(t1.b1,t2.b2),t3.b3)) <= 7; 5099a1 b1 a2 b2 a3 b3 51001 xxx 1 y 1 z 51011 xxx 1 y 1 z 51021 xxx 1 y 1 zz 51031 xxx 1 y 1 zz 51041 xxx 1 y 1 zzz 51051 xxx 1 y 1 zzz 51061 xxx 1 yy 1 z 51071 xxx 1 yy 1 z 51081 xxx 1 yy 1 zz 51091 xxx 1 yy 1 zz 51101 xxx 1 yyy 1 z 51111 xxx 1 yyy 1 z 51122 xx 2 y 2 zz 51132 xx 2 y 2 zzz 51142 xx 2 y 2 zzzz 51152 xx 2 yy 2 zz 51162 xx 2 yy 2 zzz 5117SET SESSION optimizer_switch=@tmp; 5118DROP TABLE t1,t2,t3; 5119# 5120# Bug #707555: crash with equality substitution in ref 5121# 5122CREATE TABLE t1 (f11 int, f12 int, PRIMARY KEY (f11), KEY (f12)) ; 5123INSERT INTO t1 VALUES (1,NULL), (8,NULL); 5124CREATE TABLE t2 (f21 int, f22 int, f23 int, KEY (f22)) ; 5125INSERT INTO t2 VALUES (1,NULL,3), (2,7,8); 5126CREATE TABLE t3 (f31 int, f32 int(11), PRIMARY KEY (f31), KEY (f32)) ; 5127INSERT INTO t3 VALUES (1,494862336); 5128CREATE TABLE t4 (f41 int, f42 int, PRIMARY KEY (f41), KEY (f42)) ; 5129INSERT INTO t4 VALUES (1,NULL), (8,NULL); 5130CREATE TABLE t5 (f51 int, PRIMARY KEY (f51)) ; 5131INSERT IGNORE INTO t5 VALUES (100); 5132CREATE TABLE t6 (f61 int, f62 int, KEY (f61)) ; 5133INSERT INTO t6 VALUES (NULL,1), (3,10); 5134CREATE TABLE t7 (f71 int, f72 int, KEY (f72)) ; 5135INSERT INTO t7 VALUES (1,NULL), (2,7); 5136EXPLAIN 5137SELECT t2.f23 FROM 5138(t1 LEFT JOIN (t2 JOIN t3 ON t2.f22=t3.f32) ON t1.f11=t3.f31) 5139LEFT JOIN 5140(((t4 JOIN t5 ON t4.f42=t5.f51) LEFT JOIN t6 ON t6.f62>0) JOIN t7 ON t6.f61>0) 5141ON t3.f31 = t6.f61 5142WHERE t7.f71>0; 5143id select_type table type possible_keys key key_len ref rows Extra 51441 SIMPLE t3 system PRIMARY,f32 NULL NULL NULL 1 51451 SIMPLE t5 system PRIMARY NULL NULL NULL 1 51461 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index 51471 SIMPLE t2 ref f22 f22 5 const 1 51481 SIMPLE t4 ref f42 f42 5 const 1 Using index 51491 SIMPLE t6 ref f61 f61 5 const 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 51501 SIMPLE t7 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) 5151SELECT t2.f23 FROM 5152(t1 LEFT JOIN (t2 JOIN t3 ON t2.f22=t3.f32) ON t1.f11=t3.f31) 5153LEFT JOIN 5154(((t4 JOIN t5 ON t4.f42=t5.f51) LEFT JOIN t6 ON t6.f62>0) JOIN t7 ON t6.f61>0) 5155ON t3.f31 = t6.f61 5156WHERE t7.f71>0; 5157f23 5158DROP TABLE t1,t2,t3,t4,t5,t6,t7; 5159CREATE TABLE t1(f1 int UNSIGNED) engine=myisam; 5160INSERT INTO t1 VALUES (3),(2),(1); 5161set sql_buffer_result=0; 5162SELECT f1 FROM t1 GROUP BY 1; 5163f1 51641 51652 51663 5167SELECT f1 FROM t1 GROUP BY '123' = 'abc'; 5168f1 51693 5170SELECT 1 FROM t1 GROUP BY 1; 51711 51721 5173set sql_buffer_result=1; 5174SELECT f1 FROM t1 GROUP BY 1; 5175f1 51761 51772 51783 5179SELECT f1 FROM t1 GROUP BY '123' = 'abc'; 5180f1 51813 5182SELECT 1 FROM t1 GROUP BY 1; 51831 51841 5185drop table t1; 5186set sql_buffer_result= 0; 5187# 5188# Bug #58422: Incorrect result when OUTER JOIN'ing 5189# with an empty table 5190# 5191CREATE TABLE t_empty(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM; 5192CREATE TABLE t1(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM; 5193INSERT INTO t1 VALUES (1,1), (2,2), (3,3); 5194CREATE TABLE t2(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM; 5195INSERT INTO t2 VALUES (1,1), (2,2), (3,3); 5196EXPLAIN 5197SELECT * 5198FROM 5199t1 5200LEFT OUTER JOIN 5201(t2 INNER JOIN t_empty ON TRUE) 5202ON t1.pk=t2.pk 5203WHERE t2.pk <> 2; 5204id select_type table type possible_keys key key_len ref rows Extra 52051 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 5206SELECT * 5207FROM 5208t1 5209LEFT OUTER JOIN 5210(t2 INNER JOIN t_empty ON TRUE) 5211ON t1.pk=t2.pk 5212WHERE t2.pk <> 2; 5213pk i pk i pk i 5214EXPLAIN 5215SELECT * 5216FROM 5217t1 5218LEFT OUTER JOIN 5219(t2 CROSS JOIN t_empty) 5220ON t1.pk=t2.pk 5221WHERE t2.pk <> 2; 5222id select_type table type possible_keys key key_len ref rows Extra 52231 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 5224SELECT * 5225FROM 5226t1 5227LEFT OUTER JOIN 5228(t2 CROSS JOIN t_empty) 5229ON t1.pk=t2.pk 5230WHERE t2.pk <> 2; 5231pk i pk i pk i 5232EXPLAIN 5233SELECT * 5234FROM 5235t1 5236LEFT OUTER JOIN 5237(t2 INNER JOIN t_empty ON t_empty.i=t2.i) 5238ON t1.pk=t2.pk 5239WHERE t2.pk <> 2; 5240id select_type table type possible_keys key key_len ref rows Extra 52411 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 5242SELECT * 5243FROM 5244t1 5245LEFT OUTER JOIN 5246(t2 INNER JOIN t_empty ON t_empty.i=t2.i) 5247ON t1.pk=t2.pk 5248WHERE t2.pk <> 2; 5249pk i pk i pk i 5250DROP TABLE t1,t2,t_empty; 5251End of 5.1 tests 5252# 5253# Bug#45227: Lost HAVING clause led to a wrong result. 5254# 5255CREATE TABLE `CC` ( 5256`int_nokey` int(11) NOT NULL, 5257`int_key` int(11) NOT NULL, 5258`varchar_key` varchar(1) NOT NULL, 5259`varchar_nokey` varchar(1) NOT NULL, 5260KEY `int_key` (`int_key`), 5261KEY `varchar_key` (`varchar_key`) 5262); 5263INSERT INTO `CC` VALUES 5264(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' 5265,'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'), 5266(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' 5267,'x'); 5268EXPLAIN SELECT `varchar_nokey` G1 FROM CC WHERE `int_nokey` AND `int_key` <= 4 5269HAVING G1 ORDER BY `varchar_key` LIMIT 6 ; 5270id select_type table type possible_keys key key_len ref rows Extra 52711 SIMPLE CC range int_key int_key 4 NULL 9 Using index condition; Using where; Rowid-ordered scan; Using filesort 5272SELECT `varchar_nokey` G1 FROM CC WHERE `int_nokey` AND `int_key` <= 4 5273HAVING G1 ORDER BY `varchar_key` LIMIT 6 ; 5274G1 5275Warnings: 5276Warning 1292 Truncated incorrect DOUBLE value: 'j' 5277Warning 1292 Truncated incorrect DOUBLE value: 'z' 5278Warning 1292 Truncated incorrect DOUBLE value: 'a' 5279Warning 1292 Truncated incorrect DOUBLE value: 'q' 5280Warning 1292 Truncated incorrect DOUBLE value: 'm' 5281DROP TABLE CC; 5282# End of test#45227 5283# 5284# BUG#776274: substitution of a single row table 5285# 5286CREATE TABLE t1 (a int NOT NULL , b int); 5287INSERT INTO t1 VALUES (2,2); 5288SELECT * FROM t1 WHERE a = b; 5289a b 52902 2 5291EXPLAIN 5292SELECT * FROM t1 WHERE a = b; 5293id select_type table type possible_keys key key_len ref rows Extra 52941 SIMPLE t1 system NULL NULL NULL NULL 1 5295DROP TABLE t1; 5296# 5297# Bug#54515: Crash in opt_range.cc::get_best_group_min_max on 5298# SELECT from VIEW with GROUP BY 5299# 5300CREATE TABLE t1 ( 5301col_int_key int DEFAULT NULL, 5302KEY int_key (col_int_key) 5303) ; 5304INSERT INTO t1 VALUES (1),(2); 5305CREATE VIEW view_t1 AS 5306SELECT t1.col_int_key AS col_int_key 5307FROM t1; 5308SELECT col_int_key FROM view_t1 GROUP BY col_int_key; 5309col_int_key 53101 53112 5312DROP VIEW view_t1; 5313DROP TABLE t1; 5314# End of test BUG#54515 5315# 5316# Bug #57203 Assertion `field_length <= 255' failed. 5317# 5318SELECT coalesce((avg(distinct (geomfromtext("point(25379 -22010)"))))) 5319UNION ALL 5320SELECT coalesce((avg(distinct (geomfromtext("point(25379 -22010)"))))) 5321AS foo 5322; 5323ERROR HY000: Illegal parameter data type geometry for operation 'avg' 5324CREATE table t1(a text); 5325INSERT INTO t1 VALUES (''), (''); 5326SELECT avg(distinct(t1.a)) FROM t1, t1 t2 5327GROUP BY t2.a ORDER BY t1.a; 5328avg(distinct(t1.a)) 53290 5330DROP TABLE t1; 5331# End of test BUG#57203 5332# 5333# lp:822760 Wrong result with view + invalid dates 5334# 5335CREATE TABLE t1 (f1 date); 5336INSERT IGNORE INTO t1 VALUES ('0000-00-00'); 5337CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1; 5338SELECT * FROM t1 HAVING f1 = 'zz'; 5339f1 53400000-00-00 5341Warnings: 5342Warning 1292 Truncated incorrect datetime value: 'zz' 5343SELECT * FROM t1 HAVING f1 <= 'aa' ; 5344f1 53450000-00-00 5346Warnings: 5347Warning 1292 Truncated incorrect datetime value: 'aa' 5348SELECT * FROM t1 HAVING f1 = 'zz' AND f1 <= 'aa' ; 5349f1 53500000-00-00 5351Warnings: 5352Warning 1292 Truncated incorrect datetime value: 'zz' 5353Warning 1292 Truncated incorrect datetime value: 'aa' 5354SELECT * FROM t1 WHERE f1 = 'zz' AND f1 <= 'aa' ; 5355f1 53560000-00-00 5357Warnings: 5358Warning 1292 Truncated incorrect datetime value: 'zz' 5359Warning 1292 Truncated incorrect datetime value: 'aa' 5360SELECT * FROM v1 HAVING f1 = 'zz' AND f1 <= 'aa' ; 5361f1 53620000-00-00 5363Warnings: 5364Warning 1292 Truncated incorrect datetime value: 'zz' 5365Warning 1292 Truncated incorrect datetime value: 'aa' 5366DROP TABLE t1; 5367DROP VIEW v1; 5368# 5369# Bug#63020: Function "format"'s 'locale' argument is not considered 5370# when creating a "view' 5371# 5372CREATE TABLE t1 (f1 DECIMAL(10,2)); 5373INSERT INTO t1 VALUES (11.67),(17865.3),(12345678.92); 5374CREATE VIEW view_t1 AS SELECT FORMAT(f1,1,'sk_SK') AS f1 FROM t1; 5375SHOW CREATE VIEW view_t1; 5376View Create View character_set_client collation_connection 5377view_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 5378SELECT * FROM view_t1; 5379f1 538011,7 538117 865,3 538212 345 678,9 5383DROP TABLE t1; 5384DROP VIEW view_t1; 5385# End of test BUG#63020 5386# 5387# Bug #13571700 TINYBLOB NOT NULL, CRASH IN PROTOCOL::NET_STORE_DATA 5388# 5389CREATE TABLE t1 (a TINYBLOB NOT NULL); 5390SELECT a, COUNT(*) FROM t1 WHERE 0; 5391a COUNT(*) 5392NULL 0 5393DROP TABLE t1; 5394SET @@optimizer_switch=@save_optimizer_switch; 5395# 5396# LP bug#994275 Assertion `real->type() == Item::FIELD_ITEM' failed 5397# in add_not_null_conds(JOIN*) with JOIN, ZEROFILL column, PK 5398# 5399CREATE TABLE t1 ( a INT(6) ZEROFILL ); 5400INSERT INTO t1 VALUES (1),(2); 5401CREATE TABLE t2 ( b INT PRIMARY KEY ); 5402INSERT INTO t2 VALUES (3),(4); 5403SELECT * FROM t1, t2 WHERE a=3 AND a=b; 5404a b 5405drop table t1,t2; 5406# 5407# Bug mdev-4250: wrong transformation of WHERE condition with OR 5408# 5409CREATE TABLE t1 (pk int PRIMARY KEY, a int); 5410INSERT INTO t1 VALUES (3,0), (2,0), (4,1), (5,0), (1,0); 5411SELECT * FROM t1 WHERE (1=2 OR t1.pk=2) AND t1.a <> 0; 5412pk a 5413EXPLAIN EXTENDED 5414SELECT * FROM t1 WHERE (1=2 OR t1.pk=2) AND t1.a <> 0; 5415id select_type table type possible_keys key key_len ref rows filtered Extra 54161 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 5417Warnings: 5418Note 1003 select 2 AS `pk`,0 AS `a` from `test`.`t1` where 0 5419DROP TABLE t1; 5420SELECT * FROM mysql.time_zone 5421WHERE ( NOT (Use_leap_seconds <= Use_leap_seconds AND Time_zone_id != 1) 5422AND Time_zone_id = Time_zone_id 5423OR Time_zone_id <> Time_zone_id ) 5424AND Use_leap_seconds <> 'N'; 5425Time_zone_id Use_leap_seconds 5426# 5427# Bug mdev-4274: result of simplification of OR badly merged 5428# into embedding AND 5429# 5430CREATE TABLE t1 (a int, b int, INDEX idx(b)) ENGINE=MyISAM; 5431INSERT INTO t1 VALUES (8,8); 5432CREATE TABLE t2 (c int, INDEX idx(c)) ENGINE=MyISAM; 5433INSERT INTO t2 VALUES (8), (9); 5434EXPLAIN EXTENDED 5435SELECT * FROM t1 INNER JOIN t2 ON ( c = a ) 5436WHERE 1 IS NULL OR b < 33 AND b = c; 5437id select_type table type possible_keys key key_len ref rows filtered Extra 54381 SIMPLE t1 system idx NULL NULL NULL 1 100.00 54391 SIMPLE t2 ref idx idx 5 const 1 100.00 Using index 5440Warnings: 5441Note 1003 select 8 AS `a`,8 AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where `test`.`t2`.`c` = 8 5442SELECT * FROM t1 INNER JOIN t2 ON ( c = a ) 5443WHERE 1 IS NULL OR b < 33 AND b = c; 5444a b c 54458 8 8 5446DROP TABLE t1,t2; 5447# 5448# Bug mdev-4413: another manifestations of bug mdev-4274 5449# (valgrind complains) 5450# 5451CREATE TABLE t1 (a int, b int) ENGINE=MyISAM; 5452INSERT INTO t1 VALUES (7,1); 5453CREATE TABLE t2 (c int) ENGINE=MyISAM; 5454INSERT INTO t2 VALUES (0), (8); 5455SELECT * FROM t1, t2 5456WHERE c = a AND 5457( 0 OR ( b BETWEEN 45 AND 300 OR a > 45 AND a < 100 ) AND b = c ); 5458a b c 5459DROP TABLE t1, t2; 5460# 5461# Bug mdev-4355: equalities from the result of simplification of OR 5462# are not propagated to lower AND levels 5463# 5464CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM; 5465INSERT INTO t1 VALUES (1,101),(2,102),(3,103),(4,104),(5,11); 5466EXPLAIN EXTENDED 5467SELECT * FROM t1 WHERE (1 != 1 OR a = 5) AND (b != 1 OR a = 1); 5468id select_type table type possible_keys key key_len ref rows filtered Extra 54691 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where 5470Warnings: 5471Note 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 5472SELECT * FROM t1 WHERE (1 != 1 OR a = 5) AND (b != 1 OR a = 1); 5473a b 54745 11 5475EXPLAIN EXTENDED 5476SELECT * FROM t1 WHERE (b != 1 OR a = 1) AND (1 != 1 OR a = 5); 5477id select_type table type possible_keys key key_len ref rows filtered Extra 54781 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where 5479Warnings: 5480Note 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 5481SELECT * FROM t1 WHERE (b != 1 OR a = 1) AND (1 != 1 OR a = 5); 5482a b 54835 11 5484EXPLAIN EXTENDED 5485SELECT * FROM t1 WHERE (b != 1 OR a = 1) AND (a = 5 OR 1 != 1); 5486id select_type table type possible_keys key key_len ref rows filtered Extra 54871 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where 5488Warnings: 5489Note 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 5490SELECT * FROM t1 WHERE (b != 1 OR a = 1) AND (a = 5 OR 1 != 1); 5491a b 54925 11 5493EXPLAIN EXTENDED 5494SELECT * FROM t1 WHERE (b = 1 OR a = 1) AND (b = 5 AND a = 5 OR 1 != 1); 5495id select_type table type possible_keys key key_len ref rows filtered Extra 54961 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 5497Warnings: 5498Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where 0 5499SELECT * FROM t1 WHERE (b = 1 OR a = 1) AND (b = 5 AND a = 5 OR 1 != 1); 5500a b 5501EXPLAIN EXTENDED 5502SELECT * FROM t1 WHERE (b = 1 OR a = 5) AND (b = 5 AND a = 5 OR 1 != 1); 5503id select_type table type possible_keys key key_len ref rows filtered Extra 55041 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where 5505Warnings: 5506Note 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 5507SELECT * FROM t1 WHERE (b = 1 OR a = 5) AND (b = 5 AND a = 5 OR 1 != 1); 5508a b 5509DROP TABLE t1; 5510# 5511# Bug mdev-4418: impossible multiple equality in OR formula 5512# after row substitution 5513# 5514CREATE TABLE t1 (a int, b varchar(1)) ENGINE=MyISAM; 5515INSERT INTO t1 VALUES (0,'j'), (8,'v'); 5516CREATE TABLE t2 (c varchar(1), d varchar(1)) ENGINE=MyISAM; 5517INSERT INTO t2 VALUES ('k','k'); 5518EXPLAIN EXTENDED 5519SELECT * FROM t1, t2 WHERE c=b AND (1=2 OR ((b='h' OR a=136) AND d=b)); 5520id select_type table type possible_keys key key_len ref rows filtered Extra 55211 SIMPLE t2 system NULL NULL NULL NULL 1 100.00 55221 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 5523Warnings: 5524Note 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 5525SELECT * FROM t1, t2 WHERE c=b AND (1=2 OR ((b='h' OR a=136) AND d=b)); 5526a b c d 5527DROP TABLE t1,t2; 5528# 5529# Bug mdev-4944: range conditition in OR formula with fields 5530# belonging to multiple equalities 5531# 5532CREATE TABLE t1 (i1 int, j1 int) ENGINE=MyISAM; 5533INSERT INTO t1 VALUES (1,8); 5534CREATE TABLE t2 (i2 int, INDEX idx (i2)) ENGINE=MyISAM; 5535INSERT INTO t2 VALUES (8), (9); 5536EXPLAIN EXTENDED 5537SELECT * FROM t1, t2 5538WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 ); 5539id select_type table type possible_keys key key_len ref rows filtered Extra 55401 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 5541Warnings: 5542Note 1003 select 1 AS `i1`,8 AS `j1`,NULL AS `i2` from `test`.`t2` where 0 5543SELECT * FROM t1, t2 5544WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 ); 5545i1 j1 i2 5546DROP TABLE t1,t2; 5547# 5548# Bug mdev-4971: equality propagation after merging degenerate 5549# disjunction into embedding AND level 5550# 5551CREATE TABLE t1 (pk1 int, a1 int, b1 int, PRIMARY KEY(pk1)) ENGINE=MyISAM; 5552INSERT INTO t1 VALUES (1,10,100), (2,20,200) ; 5553CREATE TABLE t2 (pk2 int, a2 int, PRIMARY KEY(pk2)) ENGINE=MyISAM; 5554INSERT INTO t2 VALUES (1,1); 5555SELECT * FROM t1, t2 5556WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL ); 5557pk1 a1 b1 pk2 a2 5558EXPLAIN EXTENDED 5559SELECT * FROM t1, t2 5560WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL ); 5561id select_type table type possible_keys key key_len ref rows filtered Extra 55621 SIMPLE t2 system PRIMARY NULL NULL NULL 1 100.00 55631 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 5564Warnings: 5565Note 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 5566INSERT INTO t1 VALUES (3,1,6); 5567SELECT * FROM t1, t2 5568WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL ); 5569pk1 a1 b1 pk2 a2 55703 1 6 1 1 5571DROP TABLE t1,t2; 5572End of 5.3 tests 5573# 5574# mysql BUG#1271 Undefined variable in PASSWORD() 5575# function is not handled correctly 5576# 5577create table t1 ( 5578name VARCHAR(50) NOT NULL PRIMARY KEY, 5579pw VARCHAR(41) NOT NULL); 5580INSERT INTO t1 (name, pw) 5581VALUES ('tom', PASSWORD('my_pw')); 5582SET @pass='my_pw'; 5583SET @wrong='incorrect'; 5584select * from t1; 5585name pw 5586tom *F305E8EC27734F687F2EB6EC03CF0F7AF27C18E1 5587select length(PASSWORD(@pass)); 5588length(PASSWORD(@pass)) 558941 5590SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@pass); 5591name 5592tom 5593SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@wrong); 5594name 5595SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@undefined); 5596name 5597select (SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@pass)); 5598(SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@pass)) 5599tom 5600select (SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@wrong)); 5601(SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@wrong)) 5602NULL 5603select (SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@undefined)); 5604(SELECT name FROM t1 WHERE name='tom' AND pw=PASSWORD(@undefined)) 5605NULL 5606drop table t1; 5607End of 10.0 tests 5608set join_cache_level=default; 5609set @@optimizer_switch=@save_optimizer_switch_jcl6; 5610set @optimizer_switch_for_select_test=NULL; 5611set @join_cache_level_for_select_test=NULL; 5612