1# 2# Find string "NOTE NOTE NOTE" in order to find some 'unsure' tests 3# 4 5# 6# Simple select test 7# 8 9--disable_warnings 10drop table if exists t1,t2,t3,t4,t11; 11# The following may be left from older tests 12drop table if exists t1_1,t1_2,t9_1,t9_2,t1aa,t2aa; 13drop view if exists v1; 14--enable_warnings 15 16CREATE TABLE t1 ( 17 Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL, 18 Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL 19); 20 21INSERT INTO t1 VALUES (9410,9412); 22 23select period from t1; 24select * from t1; 25select t1.* from t1; 26 27# 28# Create test table 29# 30 31CREATE TABLE t2 ( 32 auto int not null auto_increment, 33 fld1 int(6) unsigned zerofill DEFAULT '000000' NOT NULL, 34 companynr tinyint(2) unsigned zerofill DEFAULT '00' NOT NULL, 35 fld3 char(30) DEFAULT '' NOT NULL, 36 fld4 char(35) DEFAULT '' NOT NULL, 37 fld5 char(35) DEFAULT '' NOT NULL, 38 fld6 char(4) DEFAULT '' NOT NULL, 39 UNIQUE fld1 (fld1), 40 KEY fld3 (fld3), 41 PRIMARY KEY (auto) 42); 43 44# 45# Populate table 46# 47 48--disable_query_log 49INSERT INTO t2 VALUES (1,000001,00,'Omaha','teethe','neat',''); 50INSERT INTO t2 VALUES (2,011401,37,'breaking','dreaded','Steinberg','W'); 51INSERT INTO t2 VALUES (3,011402,37,'Romans','scholastics','jarring',''); 52INSERT INTO t2 VALUES (4,011403,37,'intercepted','audiology','tinily',''); 53INSERT INTO t2 VALUES (5,011501,37,'bewilderingly','wallet','balled',''); 54INSERT INTO t2 VALUES (6,011701,37,'astound','parters','persist','W'); 55INSERT INTO t2 VALUES (7,011702,37,'admonishing','eschew','attainments',''); 56INSERT INTO t2 VALUES (8,011703,37,'sumac','quitter','fanatic',''); 57INSERT INTO t2 VALUES (9,012001,37,'flanking','neat','measures','FAS'); 58INSERT INTO t2 VALUES (10,012003,37,'combed','Steinberg','rightfulness',''); 59INSERT INTO t2 VALUES (11,012004,37,'subjective','jarring','capably',''); 60INSERT INTO t2 VALUES (12,012005,37,'scatterbrain','tinily','impulsive',''); 61INSERT INTO t2 VALUES (13,012301,37,'Eulerian','balled','starlet',''); 62INSERT INTO t2 VALUES (14,012302,36,'dubbed','persist','terminators',''); 63INSERT INTO t2 VALUES (15,012303,37,'Kane','attainments','untying',''); 64INSERT INTO t2 VALUES (16,012304,37,'overlay','fanatic','announces','FAS'); 65INSERT INTO t2 VALUES (17,012305,37,'perturb','measures','featherweight','FAS'); 66INSERT INTO t2 VALUES (18,012306,37,'goblins','rightfulness','pessimist','FAS'); 67INSERT INTO t2 VALUES (19,012501,37,'annihilates','capably','daughter',''); 68INSERT INTO t2 VALUES (20,012602,37,'Wotan','impulsive','decliner','FAS'); 69INSERT INTO t2 VALUES (21,012603,37,'snatching','starlet','lawgiver',''); 70INSERT INTO t2 VALUES (22,012604,37,'concludes','terminators','stated',''); 71INSERT INTO t2 VALUES (23,012605,37,'laterally','untying','readable',''); 72INSERT INTO t2 VALUES (24,012606,37,'yelped','announces','attrition',''); 73INSERT INTO t2 VALUES (25,012701,37,'grazing','featherweight','cascade','FAS'); 74INSERT INTO t2 VALUES (26,012702,37,'Baird','pessimist','motors','FAS'); 75INSERT INTO t2 VALUES (27,012703,37,'celery','daughter','interrogate',''); 76INSERT INTO t2 VALUES (28,012704,37,'misunderstander','decliner','pests','W'); 77INSERT INTO t2 VALUES (29,013601,37,'handgun','lawgiver','stairway',''); 78INSERT INTO t2 VALUES (30,013602,37,'foldout','stated','dopers','FAS'); 79INSERT INTO t2 VALUES (31,013603,37,'mystic','readable','testicle','W'); 80INSERT INTO t2 VALUES (32,013604,37,'succumbed','attrition','Parsifal','W'); 81INSERT INTO t2 VALUES (33,013605,37,'Nabisco','cascade','leavings',''); 82INSERT INTO t2 VALUES (34,013606,37,'fingerings','motors','postulation','W'); 83INSERT INTO t2 VALUES (35,013607,37,'aging','interrogate','squeaking',''); 84INSERT INTO t2 VALUES (36,013608,37,'afield','pests','contrasted',''); 85INSERT INTO t2 VALUES (37,013609,37,'ammonium','stairway','leftover',''); 86INSERT INTO t2 VALUES (38,013610,37,'boat','dopers','whiteners',''); 87INSERT INTO t2 VALUES (39,013801,37,'intelligibility','testicle','erases','W'); 88INSERT INTO t2 VALUES (40,013802,37,'Augustine','Parsifal','Punjab','W'); 89INSERT INTO t2 VALUES (41,013803,37,'teethe','leavings','Merritt',''); 90INSERT INTO t2 VALUES (42,013804,37,'dreaded','postulation','Quixotism',''); 91INSERT INTO t2 VALUES (43,013901,37,'scholastics','squeaking','sweetish','FAS'); 92INSERT INTO t2 VALUES (44,016001,37,'audiology','contrasted','dogging','FAS'); 93INSERT INTO t2 VALUES (45,016201,37,'wallet','leftover','scornfully','FAS'); 94INSERT INTO t2 VALUES (46,016202,37,'parters','whiteners','bellow',''); 95INSERT INTO t2 VALUES (47,016301,37,'eschew','erases','bills',''); 96INSERT INTO t2 VALUES (48,016302,37,'quitter','Punjab','cupboard','FAS'); 97INSERT INTO t2 VALUES (49,016303,37,'neat','Merritt','sureties','FAS'); 98INSERT INTO t2 VALUES (50,016304,37,'Steinberg','Quixotism','puddings',''); 99INSERT INTO t2 VALUES (51,018001,37,'jarring','sweetish','tapestry',''); 100INSERT INTO t2 VALUES (52,018002,37,'tinily','dogging','fetters',''); 101INSERT INTO t2 VALUES (53,018003,37,'balled','scornfully','bivalves',''); 102INSERT INTO t2 VALUES (54,018004,37,'persist','bellow','incurring',''); 103INSERT INTO t2 VALUES (55,018005,37,'attainments','bills','Adolph',''); 104INSERT INTO t2 VALUES (56,018007,37,'fanatic','cupboard','pithed',''); 105INSERT INTO t2 VALUES (57,018008,37,'measures','sureties','emergency',''); 106INSERT INTO t2 VALUES (58,018009,37,'rightfulness','puddings','Miles',''); 107INSERT INTO t2 VALUES (59,018010,37,'capably','tapestry','trimmings',''); 108INSERT INTO t2 VALUES (60,018012,37,'impulsive','fetters','tragedies','W'); 109INSERT INTO t2 VALUES (61,018013,37,'starlet','bivalves','skulking','W'); 110INSERT INTO t2 VALUES (62,018014,37,'terminators','incurring','flint',''); 111INSERT INTO t2 VALUES (63,018015,37,'untying','Adolph','flopping','W'); 112INSERT INTO t2 VALUES (64,018016,37,'announces','pithed','relaxing','FAS'); 113INSERT INTO t2 VALUES (65,018017,37,'featherweight','emergency','offload','FAS'); 114INSERT INTO t2 VALUES (66,018018,37,'pessimist','Miles','suites','W'); 115INSERT INTO t2 VALUES (67,018019,37,'daughter','trimmings','lists','FAS'); 116INSERT INTO t2 VALUES (68,018020,37,'decliner','tragedies','animized','FAS'); 117INSERT INTO t2 VALUES (69,018021,37,'lawgiver','skulking','multilayer','W'); 118INSERT INTO t2 VALUES (70,018022,37,'stated','flint','standardizes','FAS'); 119INSERT INTO t2 VALUES (71,018023,37,'readable','flopping','Judas',''); 120INSERT INTO t2 VALUES (72,018024,37,'attrition','relaxing','vacuuming','W'); 121INSERT INTO t2 VALUES (73,018025,37,'cascade','offload','dentally','W'); 122INSERT INTO t2 VALUES (74,018026,37,'motors','suites','humanness','W'); 123INSERT INTO t2 VALUES (75,018027,37,'interrogate','lists','inch','W'); 124INSERT INTO t2 VALUES (76,018028,37,'pests','animized','Weissmuller','W'); 125INSERT INTO t2 VALUES (77,018029,37,'stairway','multilayer','irresponsibly','W'); 126INSERT INTO t2 VALUES (78,018030,37,'dopers','standardizes','luckily','FAS'); 127INSERT INTO t2 VALUES (79,018032,37,'testicle','Judas','culled','W'); 128INSERT INTO t2 VALUES (80,018033,37,'Parsifal','vacuuming','medical','FAS'); 129INSERT INTO t2 VALUES (81,018034,37,'leavings','dentally','bloodbath','FAS'); 130INSERT INTO t2 VALUES (82,018035,37,'postulation','humanness','subschema','W'); 131INSERT INTO t2 VALUES (83,018036,37,'squeaking','inch','animals','W'); 132INSERT INTO t2 VALUES (84,018037,37,'contrasted','Weissmuller','Micronesia',''); 133INSERT INTO t2 VALUES (85,018038,37,'leftover','irresponsibly','repetitions',''); 134INSERT INTO t2 VALUES (86,018039,37,'whiteners','luckily','Antares',''); 135INSERT INTO t2 VALUES (87,018040,37,'erases','culled','ventilate','W'); 136INSERT INTO t2 VALUES (88,018041,37,'Punjab','medical','pityingly',''); 137INSERT INTO t2 VALUES (89,018042,37,'Merritt','bloodbath','interdependent',''); 138INSERT INTO t2 VALUES (90,018043,37,'Quixotism','subschema','Graves','FAS'); 139INSERT INTO t2 VALUES (91,018044,37,'sweetish','animals','neonatal',''); 140INSERT INTO t2 VALUES (92,018045,37,'dogging','Micronesia','scribbled','FAS'); 141INSERT INTO t2 VALUES (93,018046,37,'scornfully','repetitions','chafe','W'); 142INSERT INTO t2 VALUES (94,018048,37,'bellow','Antares','honoring',''); 143INSERT INTO t2 VALUES (95,018049,37,'bills','ventilate','realtor',''); 144INSERT INTO t2 VALUES (96,018050,37,'cupboard','pityingly','elite',''); 145INSERT INTO t2 VALUES (97,018051,37,'sureties','interdependent','funereal',''); 146INSERT INTO t2 VALUES (98,018052,37,'puddings','Graves','abrogating',''); 147INSERT INTO t2 VALUES (99,018053,50,'tapestry','neonatal','sorters',''); 148INSERT INTO t2 VALUES (100,018054,37,'fetters','scribbled','Conley',''); 149INSERT INTO t2 VALUES (101,018055,37,'bivalves','chafe','lectured',''); 150INSERT INTO t2 VALUES (102,018056,37,'incurring','honoring','Abraham',''); 151INSERT INTO t2 VALUES (103,018057,37,'Adolph','realtor','Hawaii','W'); 152INSERT INTO t2 VALUES (104,018058,37,'pithed','elite','cage',''); 153INSERT INTO t2 VALUES (105,018059,36,'emergency','funereal','hushes',''); 154INSERT INTO t2 VALUES (106,018060,37,'Miles','abrogating','Simla',''); 155INSERT INTO t2 VALUES (107,018061,37,'trimmings','sorters','reporters',''); 156INSERT INTO t2 VALUES (108,018101,37,'tragedies','Conley','Dutchman','FAS'); 157INSERT INTO t2 VALUES (109,018102,37,'skulking','lectured','descendants','FAS'); 158INSERT INTO t2 VALUES (110,018103,37,'flint','Abraham','groupings','FAS'); 159INSERT INTO t2 VALUES (111,018104,37,'flopping','Hawaii','dissociate',''); 160INSERT INTO t2 VALUES (112,018201,37,'relaxing','cage','coexist','W'); 161INSERT INTO t2 VALUES (113,018202,37,'offload','hushes','Beebe',''); 162INSERT INTO t2 VALUES (114,018402,37,'suites','Simla','Taoism',''); 163INSERT INTO t2 VALUES (115,018403,37,'lists','reporters','Connally',''); 164INSERT INTO t2 VALUES (116,018404,37,'animized','Dutchman','fetched','FAS'); 165INSERT INTO t2 VALUES (117,018405,37,'multilayer','descendants','checkpoints','FAS'); 166INSERT INTO t2 VALUES (118,018406,37,'standardizes','groupings','rusting',''); 167INSERT INTO t2 VALUES (119,018409,37,'Judas','dissociate','galling',''); 168INSERT INTO t2 VALUES (120,018601,37,'vacuuming','coexist','obliterates',''); 169INSERT INTO t2 VALUES (121,018602,37,'dentally','Beebe','traitor',''); 170INSERT INTO t2 VALUES (122,018603,37,'humanness','Taoism','resumes','FAS'); 171INSERT INTO t2 VALUES (123,018801,37,'inch','Connally','analyzable','FAS'); 172INSERT INTO t2 VALUES (124,018802,37,'Weissmuller','fetched','terminator','FAS'); 173INSERT INTO t2 VALUES (125,018803,37,'irresponsibly','checkpoints','gritty','FAS'); 174INSERT INTO t2 VALUES (126,018804,37,'luckily','rusting','firearm','W'); 175INSERT INTO t2 VALUES (127,018805,37,'culled','galling','minima',''); 176INSERT INTO t2 VALUES (128,018806,37,'medical','obliterates','Selfridge',''); 177INSERT INTO t2 VALUES (129,018807,37,'bloodbath','traitor','disable',''); 178INSERT INTO t2 VALUES (130,018808,37,'subschema','resumes','witchcraft','W'); 179INSERT INTO t2 VALUES (131,018809,37,'animals','analyzable','betroth','W'); 180INSERT INTO t2 VALUES (132,018810,37,'Micronesia','terminator','Manhattanize',''); 181INSERT INTO t2 VALUES (133,018811,37,'repetitions','gritty','imprint',''); 182INSERT INTO t2 VALUES (134,018812,37,'Antares','firearm','peeked',''); 183INSERT INTO t2 VALUES (135,019101,37,'ventilate','minima','swelling',''); 184INSERT INTO t2 VALUES (136,019102,37,'pityingly','Selfridge','interrelationships','W'); 185INSERT INTO t2 VALUES (137,019103,37,'interdependent','disable','riser',''); 186INSERT INTO t2 VALUES (138,019201,37,'Graves','witchcraft','Gandhian','W'); 187INSERT INTO t2 VALUES (139,030501,37,'neonatal','betroth','peacock','A'); 188INSERT INTO t2 VALUES (140,030502,50,'scribbled','Manhattanize','bee','A'); 189INSERT INTO t2 VALUES (141,030503,37,'chafe','imprint','kanji',''); 190INSERT INTO t2 VALUES (142,030504,37,'honoring','peeked','dental',''); 191INSERT INTO t2 VALUES (143,031901,37,'realtor','swelling','scarf','FAS'); 192INSERT INTO t2 VALUES (144,036001,37,'elite','interrelationships','chasm','A'); 193INSERT INTO t2 VALUES (145,036002,37,'funereal','riser','insolence','A'); 194INSERT INTO t2 VALUES (146,036004,37,'abrogating','Gandhian','syndicate',''); 195INSERT INTO t2 VALUES (147,036005,37,'sorters','peacock','alike',''); 196INSERT INTO t2 VALUES (148,038001,37,'Conley','bee','imperial','A'); 197INSERT INTO t2 VALUES (149,038002,37,'lectured','kanji','convulsion','A'); 198INSERT INTO t2 VALUES (150,038003,37,'Abraham','dental','railway','A'); 199INSERT INTO t2 VALUES (151,038004,37,'Hawaii','scarf','validate','A'); 200INSERT INTO t2 VALUES (152,038005,37,'cage','chasm','normalizes','A'); 201INSERT INTO t2 VALUES (153,038006,37,'hushes','insolence','comprehensive',''); 202INSERT INTO t2 VALUES (154,038007,37,'Simla','syndicate','chewing',''); 203INSERT INTO t2 VALUES (155,038008,37,'reporters','alike','denizen',''); 204INSERT INTO t2 VALUES (156,038009,37,'Dutchman','imperial','schemer',''); 205INSERT INTO t2 VALUES (157,038010,37,'descendants','convulsion','chronicle',''); 206INSERT INTO t2 VALUES (158,038011,37,'groupings','railway','Kline',''); 207INSERT INTO t2 VALUES (159,038012,37,'dissociate','validate','Anatole',''); 208INSERT INTO t2 VALUES (160,038013,37,'coexist','normalizes','partridges',''); 209INSERT INTO t2 VALUES (161,038014,37,'Beebe','comprehensive','brunch',''); 210INSERT INTO t2 VALUES (162,038015,37,'Taoism','chewing','recruited',''); 211INSERT INTO t2 VALUES (163,038016,37,'Connally','denizen','dimensions','W'); 212INSERT INTO t2 VALUES (164,038017,37,'fetched','schemer','Chicana','W'); 213INSERT INTO t2 VALUES (165,038018,37,'checkpoints','chronicle','announced',''); 214INSERT INTO t2 VALUES (166,038101,37,'rusting','Kline','praised','FAS'); 215INSERT INTO t2 VALUES (167,038102,37,'galling','Anatole','employing',''); 216INSERT INTO t2 VALUES (168,038103,37,'obliterates','partridges','linear',''); 217INSERT INTO t2 VALUES (169,038104,37,'traitor','brunch','quagmire',''); 218INSERT INTO t2 VALUES (170,038201,37,'resumes','recruited','western','A'); 219INSERT INTO t2 VALUES (171,038202,37,'analyzable','dimensions','relishing',''); 220INSERT INTO t2 VALUES (172,038203,37,'terminator','Chicana','serving','A'); 221INSERT INTO t2 VALUES (173,038204,37,'gritty','announced','scheduling',''); 222INSERT INTO t2 VALUES (174,038205,37,'firearm','praised','lore',''); 223INSERT INTO t2 VALUES (175,038206,37,'minima','employing','eventful',''); 224INSERT INTO t2 VALUES (176,038208,37,'Selfridge','linear','arteriole','A'); 225INSERT INTO t2 VALUES (177,042801,37,'disable','quagmire','disentangle',''); 226INSERT INTO t2 VALUES (178,042802,37,'witchcraft','western','cured','A'); 227INSERT INTO t2 VALUES (179,046101,37,'betroth','relishing','Fenton','W'); 228INSERT INTO t2 VALUES (180,048001,37,'Manhattanize','serving','avoidable','A'); 229INSERT INTO t2 VALUES (181,048002,37,'imprint','scheduling','drains','A'); 230INSERT INTO t2 VALUES (182,048003,37,'peeked','lore','detectably','FAS'); 231INSERT INTO t2 VALUES (183,048004,37,'swelling','eventful','husky',''); 232INSERT INTO t2 VALUES (184,048005,37,'interrelationships','arteriole','impelling',''); 233INSERT INTO t2 VALUES (185,048006,37,'riser','disentangle','undoes',''); 234INSERT INTO t2 VALUES (186,048007,37,'Gandhian','cured','evened',''); 235INSERT INTO t2 VALUES (187,048008,37,'peacock','Fenton','squeezes',''); 236INSERT INTO t2 VALUES (188,048101,37,'bee','avoidable','destroyer','FAS'); 237INSERT INTO t2 VALUES (189,048102,37,'kanji','drains','rudeness',''); 238INSERT INTO t2 VALUES (190,048201,37,'dental','detectably','beaner','FAS'); 239INSERT INTO t2 VALUES (191,048202,37,'scarf','husky','boorish',''); 240INSERT INTO t2 VALUES (192,048203,37,'chasm','impelling','Everhart',''); 241INSERT INTO t2 VALUES (193,048204,37,'insolence','undoes','encompass','A'); 242INSERT INTO t2 VALUES (194,048205,37,'syndicate','evened','mushrooms',''); 243INSERT INTO t2 VALUES (195,048301,37,'alike','squeezes','Alison','A'); 244INSERT INTO t2 VALUES (196,048302,37,'imperial','destroyer','externally','FAS'); 245INSERT INTO t2 VALUES (197,048303,37,'convulsion','rudeness','pellagra',''); 246INSERT INTO t2 VALUES (198,048304,37,'railway','beaner','cult',''); 247INSERT INTO t2 VALUES (199,048305,37,'validate','boorish','creek','A'); 248INSERT INTO t2 VALUES (200,048401,37,'normalizes','Everhart','Huffman',''); 249INSERT INTO t2 VALUES (201,048402,37,'comprehensive','encompass','Majorca','FAS'); 250INSERT INTO t2 VALUES (202,048403,37,'chewing','mushrooms','governing','A'); 251INSERT INTO t2 VALUES (203,048404,37,'denizen','Alison','gadfly','FAS'); 252INSERT INTO t2 VALUES (204,048405,37,'schemer','externally','reassigned','FAS'); 253INSERT INTO t2 VALUES (205,048406,37,'chronicle','pellagra','intentness','W'); 254INSERT INTO t2 VALUES (206,048407,37,'Kline','cult','craziness',''); 255INSERT INTO t2 VALUES (207,048408,37,'Anatole','creek','psychic',''); 256INSERT INTO t2 VALUES (208,048409,37,'partridges','Huffman','squabbled',''); 257INSERT INTO t2 VALUES (209,048410,37,'brunch','Majorca','burlesque',''); 258INSERT INTO t2 VALUES (210,048411,37,'recruited','governing','capped',''); 259INSERT INTO t2 VALUES (211,048412,37,'dimensions','gadfly','extracted','A'); 260INSERT INTO t2 VALUES (212,048413,37,'Chicana','reassigned','DiMaggio',''); 261INSERT INTO t2 VALUES (213,048601,37,'announced','intentness','exclamation','FAS'); 262INSERT INTO t2 VALUES (214,048602,37,'praised','craziness','subdirectory',''); 263INSERT INTO t2 VALUES (215,048603,37,'employing','psychic','fangs',''); 264INSERT INTO t2 VALUES (216,048604,37,'linear','squabbled','buyer','A'); 265INSERT INTO t2 VALUES (217,048801,37,'quagmire','burlesque','pithing','A'); 266INSERT INTO t2 VALUES (218,050901,37,'western','capped','transistorizing','A'); 267INSERT INTO t2 VALUES (219,051201,37,'relishing','extracted','nonbiodegradable',''); 268INSERT INTO t2 VALUES (220,056002,37,'serving','DiMaggio','dislocate',''); 269INSERT INTO t2 VALUES (221,056003,37,'scheduling','exclamation','monochromatic','FAS'); 270INSERT INTO t2 VALUES (222,056004,37,'lore','subdirectory','batting',''); 271INSERT INTO t2 VALUES (223,056102,37,'eventful','fangs','postcondition','A'); 272INSERT INTO t2 VALUES (224,056203,37,'arteriole','buyer','catalog','FAS'); 273INSERT INTO t2 VALUES (225,056204,37,'disentangle','pithing','Remus',''); 274INSERT INTO t2 VALUES (226,058003,37,'cured','transistorizing','devices','A'); 275INSERT INTO t2 VALUES (227,058004,37,'Fenton','nonbiodegradable','bike','A'); 276INSERT INTO t2 VALUES (228,058005,37,'avoidable','dislocate','qualify',''); 277INSERT INTO t2 VALUES (229,058006,37,'drains','monochromatic','detained',''); 278INSERT INTO t2 VALUES (230,058007,37,'detectably','batting','commended',''); 279INSERT INTO t2 VALUES (231,058101,37,'husky','postcondition','civilize',''); 280INSERT INTO t2 VALUES (232,058102,37,'impelling','catalog','Elmhurst',''); 281INSERT INTO t2 VALUES (233,058103,37,'undoes','Remus','anesthetizing',''); 282INSERT INTO t2 VALUES (234,058105,37,'evened','devices','deaf',''); 283INSERT INTO t2 VALUES (235,058111,37,'squeezes','bike','Brigham',''); 284INSERT INTO t2 VALUES (236,058112,37,'destroyer','qualify','title',''); 285INSERT INTO t2 VALUES (237,058113,37,'rudeness','detained','coarse',''); 286INSERT INTO t2 VALUES (238,058114,37,'beaner','commended','combinations',''); 287INSERT INTO t2 VALUES (239,058115,37,'boorish','civilize','grayness',''); 288INSERT INTO t2 VALUES (240,058116,37,'Everhart','Elmhurst','innumerable','FAS'); 289INSERT INTO t2 VALUES (241,058117,37,'encompass','anesthetizing','Caroline','A'); 290INSERT INTO t2 VALUES (242,058118,37,'mushrooms','deaf','fatty','FAS'); 291INSERT INTO t2 VALUES (243,058119,37,'Alison','Brigham','eastbound',''); 292INSERT INTO t2 VALUES (244,058120,37,'externally','title','inexperienced',''); 293INSERT INTO t2 VALUES (245,058121,37,'pellagra','coarse','hoarder','A'); 294INSERT INTO t2 VALUES (246,058122,37,'cult','combinations','scotch','W'); 295INSERT INTO t2 VALUES (247,058123,37,'creek','grayness','passport','A'); 296INSERT INTO t2 VALUES (248,058124,37,'Huffman','innumerable','strategic','FAS'); 297INSERT INTO t2 VALUES (249,058125,37,'Majorca','Caroline','gated',''); 298INSERT INTO t2 VALUES (250,058126,37,'governing','fatty','flog',''); 299INSERT INTO t2 VALUES (251,058127,37,'gadfly','eastbound','Pipestone',''); 300INSERT INTO t2 VALUES (252,058128,37,'reassigned','inexperienced','Dar',''); 301INSERT INTO t2 VALUES (253,058201,37,'intentness','hoarder','Corcoran',''); 302INSERT INTO t2 VALUES (254,058202,37,'craziness','scotch','flyers','A'); 303INSERT INTO t2 VALUES (255,058303,37,'psychic','passport','competitions','W'); 304INSERT INTO t2 VALUES (256,058304,37,'squabbled','strategic','suppliers','FAS'); 305INSERT INTO t2 VALUES (257,058602,37,'burlesque','gated','skips',''); 306INSERT INTO t2 VALUES (258,058603,37,'capped','flog','institutes',''); 307INSERT INTO t2 VALUES (259,058604,37,'extracted','Pipestone','troop','A'); 308INSERT INTO t2 VALUES (260,058605,37,'DiMaggio','Dar','connective','W'); 309INSERT INTO t2 VALUES (261,058606,37,'exclamation','Corcoran','denies',''); 310INSERT INTO t2 VALUES (262,058607,37,'subdirectory','flyers','polka',''); 311INSERT INTO t2 VALUES (263,060401,36,'fangs','competitions','observations','FAS'); 312INSERT INTO t2 VALUES (264,061701,36,'buyer','suppliers','askers',''); 313INSERT INTO t2 VALUES (265,066201,36,'pithing','skips','homeless','FAS'); 314INSERT INTO t2 VALUES (266,066501,36,'transistorizing','institutes','Anna',''); 315INSERT INTO t2 VALUES (267,068001,36,'nonbiodegradable','troop','subdirectories','W'); 316INSERT INTO t2 VALUES (268,068002,36,'dislocate','connective','decaying','FAS'); 317INSERT INTO t2 VALUES (269,068005,36,'monochromatic','denies','outwitting','W'); 318INSERT INTO t2 VALUES (270,068006,36,'batting','polka','Harpy','W'); 319INSERT INTO t2 VALUES (271,068007,36,'postcondition','observations','crazed',''); 320INSERT INTO t2 VALUES (272,068008,36,'catalog','askers','suffocate',''); 321INSERT INTO t2 VALUES (273,068009,36,'Remus','homeless','provers','FAS'); 322INSERT INTO t2 VALUES (274,068010,36,'devices','Anna','technically',''); 323INSERT INTO t2 VALUES (275,068011,36,'bike','subdirectories','Franklinizations',''); 324INSERT INTO t2 VALUES (276,068202,36,'qualify','decaying','considered',''); 325INSERT INTO t2 VALUES (277,068302,36,'detained','outwitting','tinnily',''); 326INSERT INTO t2 VALUES (278,068303,36,'commended','Harpy','uninterruptedly',''); 327INSERT INTO t2 VALUES (279,068401,36,'civilize','crazed','whistled','A'); 328INSERT INTO t2 VALUES (280,068501,36,'Elmhurst','suffocate','automate',''); 329INSERT INTO t2 VALUES (281,068502,36,'anesthetizing','provers','gutting','W'); 330INSERT INTO t2 VALUES (282,068503,36,'deaf','technically','surreptitious',''); 331INSERT INTO t2 VALUES (283,068602,36,'Brigham','Franklinizations','Choctaw',''); 332INSERT INTO t2 VALUES (284,068603,36,'title','considered','cooks',''); 333INSERT INTO t2 VALUES (285,068701,36,'coarse','tinnily','millivolt','FAS'); 334INSERT INTO t2 VALUES (286,068702,36,'combinations','uninterruptedly','counterpoise',''); 335INSERT INTO t2 VALUES (287,068703,36,'grayness','whistled','Gothicism',''); 336INSERT INTO t2 VALUES (288,076001,36,'innumerable','automate','feminine',''); 337INSERT INTO t2 VALUES (289,076002,36,'Caroline','gutting','metaphysically','W'); 338INSERT INTO t2 VALUES (290,076101,36,'fatty','surreptitious','sanding','A'); 339INSERT INTO t2 VALUES (291,076102,36,'eastbound','Choctaw','contributorily',''); 340INSERT INTO t2 VALUES (292,076103,36,'inexperienced','cooks','receivers','FAS'); 341INSERT INTO t2 VALUES (293,076302,36,'hoarder','millivolt','adjourn',''); 342INSERT INTO t2 VALUES (294,076303,36,'scotch','counterpoise','straggled','A'); 343INSERT INTO t2 VALUES (295,076304,36,'passport','Gothicism','druggists',''); 344INSERT INTO t2 VALUES (296,076305,36,'strategic','feminine','thanking','FAS'); 345INSERT INTO t2 VALUES (297,076306,36,'gated','metaphysically','ostrich',''); 346INSERT INTO t2 VALUES (298,076307,36,'flog','sanding','hopelessness','FAS'); 347INSERT INTO t2 VALUES (299,076402,36,'Pipestone','contributorily','Eurydice',''); 348INSERT INTO t2 VALUES (300,076501,36,'Dar','receivers','excitation','W'); 349INSERT INTO t2 VALUES (301,076502,36,'Corcoran','adjourn','presumes','FAS'); 350INSERT INTO t2 VALUES (302,076701,36,'flyers','straggled','imaginable','FAS'); 351INSERT INTO t2 VALUES (303,078001,36,'competitions','druggists','concoct','W'); 352INSERT INTO t2 VALUES (304,078002,36,'suppliers','thanking','peering','W'); 353INSERT INTO t2 VALUES (305,078003,36,'skips','ostrich','Phelps','FAS'); 354INSERT INTO t2 VALUES (306,078004,36,'institutes','hopelessness','ferociousness','FAS'); 355INSERT INTO t2 VALUES (307,078005,36,'troop','Eurydice','sentences',''); 356INSERT INTO t2 VALUES (308,078006,36,'connective','excitation','unlocks',''); 357INSERT INTO t2 VALUES (309,078007,36,'denies','presumes','engrossing','W'); 358INSERT INTO t2 VALUES (310,078008,36,'polka','imaginable','Ruth',''); 359INSERT INTO t2 VALUES (311,078101,36,'observations','concoct','tying',''); 360INSERT INTO t2 VALUES (312,078103,36,'askers','peering','exclaimers',''); 361INSERT INTO t2 VALUES (313,078104,36,'homeless','Phelps','synergy',''); 362INSERT INTO t2 VALUES (314,078105,36,'Anna','ferociousness','Huey','W'); 363INSERT INTO t2 VALUES (315,082101,36,'subdirectories','sentences','merging',''); 364INSERT INTO t2 VALUES (316,083401,36,'decaying','unlocks','judges','A'); 365INSERT INTO t2 VALUES (317,084001,36,'outwitting','engrossing','Shylock','W'); 366INSERT INTO t2 VALUES (318,084002,36,'Harpy','Ruth','Miltonism',''); 367INSERT INTO t2 VALUES (319,086001,36,'crazed','tying','hen','W'); 368INSERT INTO t2 VALUES (320,086102,36,'suffocate','exclaimers','honeybee','FAS'); 369INSERT INTO t2 VALUES (321,086201,36,'provers','synergy','towers',''); 370INSERT INTO t2 VALUES (322,088001,36,'technically','Huey','dilutes','W'); 371INSERT INTO t2 VALUES (323,088002,36,'Franklinizations','merging','numerals','FAS'); 372INSERT INTO t2 VALUES (324,088003,36,'considered','judges','democracy','FAS'); 373INSERT INTO t2 VALUES (325,088004,36,'tinnily','Shylock','Ibero-',''); 374INSERT INTO t2 VALUES (326,088101,36,'uninterruptedly','Miltonism','invalids',''); 375INSERT INTO t2 VALUES (327,088102,36,'whistled','hen','behavior',''); 376INSERT INTO t2 VALUES (328,088103,36,'automate','honeybee','accruing',''); 377INSERT INTO t2 VALUES (329,088104,36,'gutting','towers','relics','A'); 378INSERT INTO t2 VALUES (330,088105,36,'surreptitious','dilutes','rackets',''); 379INSERT INTO t2 VALUES (331,088106,36,'Choctaw','numerals','Fischbein','W'); 380INSERT INTO t2 VALUES (332,088201,36,'cooks','democracy','phony','W'); 381INSERT INTO t2 VALUES (333,088203,36,'millivolt','Ibero-','cross','FAS'); 382INSERT INTO t2 VALUES (334,088204,36,'counterpoise','invalids','cleanup',''); 383INSERT INTO t2 VALUES (335,088302,37,'Gothicism','behavior','conspirator',''); 384INSERT INTO t2 VALUES (336,088303,37,'feminine','accruing','label','FAS'); 385INSERT INTO t2 VALUES (337,088305,37,'metaphysically','relics','university',''); 386INSERT INTO t2 VALUES (338,088402,37,'sanding','rackets','cleansed','FAS'); 387INSERT INTO t2 VALUES (339,088501,36,'contributorily','Fischbein','ballgown',''); 388INSERT INTO t2 VALUES (340,088502,36,'receivers','phony','starlet',''); 389INSERT INTO t2 VALUES (341,088503,36,'adjourn','cross','aqueous',''); 390INSERT INTO t2 VALUES (342,098001,58,'straggled','cleanup','portrayal','A'); 391INSERT INTO t2 VALUES (343,098002,58,'druggists','conspirator','despising','W'); 392INSERT INTO t2 VALUES (344,098003,58,'thanking','label','distort','W'); 393INSERT INTO t2 VALUES (345,098004,58,'ostrich','university','palmed',''); 394INSERT INTO t2 VALUES (346,098005,58,'hopelessness','cleansed','faced',''); 395INSERT INTO t2 VALUES (347,098006,58,'Eurydice','ballgown','silverware',''); 396INSERT INTO t2 VALUES (348,141903,29,'excitation','starlet','assessor',''); 397INSERT INTO t2 VALUES (349,098008,58,'presumes','aqueous','spiders',''); 398INSERT INTO t2 VALUES (350,098009,58,'imaginable','portrayal','artificially',''); 399INSERT INTO t2 VALUES (351,098010,58,'concoct','despising','reminiscence',''); 400INSERT INTO t2 VALUES (352,098011,58,'peering','distort','Mexican',''); 401INSERT INTO t2 VALUES (353,098012,58,'Phelps','palmed','obnoxious',''); 402INSERT INTO t2 VALUES (354,098013,58,'ferociousness','faced','fragile',''); 403INSERT INTO t2 VALUES (355,098014,58,'sentences','silverware','apprehensible',''); 404INSERT INTO t2 VALUES (356,098015,58,'unlocks','assessor','births',''); 405INSERT INTO t2 VALUES (357,098016,58,'engrossing','spiders','garages',''); 406INSERT INTO t2 VALUES (358,098017,58,'Ruth','artificially','panty',''); 407INSERT INTO t2 VALUES (359,098018,58,'tying','reminiscence','anteater',''); 408INSERT INTO t2 VALUES (360,098019,58,'exclaimers','Mexican','displacement','A'); 409INSERT INTO t2 VALUES (361,098020,58,'synergy','obnoxious','drovers','A'); 410INSERT INTO t2 VALUES (362,098021,58,'Huey','fragile','patenting','A'); 411INSERT INTO t2 VALUES (363,098022,58,'merging','apprehensible','far','A'); 412INSERT INTO t2 VALUES (364,098023,58,'judges','births','shrieks',''); 413INSERT INTO t2 VALUES (365,098024,58,'Shylock','garages','aligning','W'); 414INSERT INTO t2 VALUES (366,098025,37,'Miltonism','panty','pragmatism',''); 415INSERT INTO t2 VALUES (367,106001,36,'hen','anteater','fevers','W'); 416INSERT INTO t2 VALUES (368,108001,36,'honeybee','displacement','reexamines','A'); 417INSERT INTO t2 VALUES (369,108002,36,'towers','drovers','occupancies',''); 418INSERT INTO t2 VALUES (370,108003,36,'dilutes','patenting','sweats','FAS'); 419INSERT INTO t2 VALUES (371,108004,36,'numerals','far','modulators',''); 420INSERT INTO t2 VALUES (372,108005,36,'democracy','shrieks','demand','W'); 421INSERT INTO t2 VALUES (373,108007,36,'Ibero-','aligning','Madeira',''); 422INSERT INTO t2 VALUES (374,108008,36,'invalids','pragmatism','Viennese','W'); 423INSERT INTO t2 VALUES (375,108009,36,'behavior','fevers','chillier','W'); 424INSERT INTO t2 VALUES (376,108010,36,'accruing','reexamines','wildcats','FAS'); 425INSERT INTO t2 VALUES (377,108011,36,'relics','occupancies','gentle',''); 426INSERT INTO t2 VALUES (378,108012,36,'rackets','sweats','Angles','W'); 427INSERT INTO t2 VALUES (379,108101,36,'Fischbein','modulators','accuracies',''); 428INSERT INTO t2 VALUES (380,108102,36,'phony','demand','toggle',''); 429INSERT INTO t2 VALUES (381,108103,36,'cross','Madeira','Mendelssohn','W'); 430INSERT INTO t2 VALUES (382,108111,50,'cleanup','Viennese','behaviorally',''); 431INSERT INTO t2 VALUES (383,108105,36,'conspirator','chillier','Rochford',''); 432INSERT INTO t2 VALUES (384,108106,36,'label','wildcats','mirror','W'); 433INSERT INTO t2 VALUES (385,108107,36,'university','gentle','Modula',''); 434INSERT INTO t2 VALUES (386,108108,50,'cleansed','Angles','clobbering',''); 435INSERT INTO t2 VALUES (387,108109,36,'ballgown','accuracies','chronography',''); 436INSERT INTO t2 VALUES (388,108110,36,'starlet','toggle','Eskimoizeds',''); 437INSERT INTO t2 VALUES (389,108201,36,'aqueous','Mendelssohn','British','W'); 438INSERT INTO t2 VALUES (390,108202,36,'portrayal','behaviorally','pitfalls',''); 439INSERT INTO t2 VALUES (391,108203,36,'despising','Rochford','verify','W'); 440INSERT INTO t2 VALUES (392,108204,36,'distort','mirror','scatter','FAS'); 441INSERT INTO t2 VALUES (393,108205,36,'palmed','Modula','Aztecan',''); 442INSERT INTO t2 VALUES (394,108301,36,'faced','clobbering','acuity','W'); 443INSERT INTO t2 VALUES (395,108302,36,'silverware','chronography','sinking','W'); 444INSERT INTO t2 VALUES (396,112101,36,'assessor','Eskimoizeds','beasts','FAS'); 445INSERT INTO t2 VALUES (397,112102,36,'spiders','British','Witt','W'); 446INSERT INTO t2 VALUES (398,113701,36,'artificially','pitfalls','physicists','FAS'); 447INSERT INTO t2 VALUES (399,116001,36,'reminiscence','verify','folksong','A'); 448INSERT INTO t2 VALUES (400,116201,36,'Mexican','scatter','strokes','FAS'); 449INSERT INTO t2 VALUES (401,116301,36,'obnoxious','Aztecan','crowder',''); 450INSERT INTO t2 VALUES (402,116302,36,'fragile','acuity','merry',''); 451INSERT INTO t2 VALUES (403,116601,36,'apprehensible','sinking','cadenced',''); 452INSERT INTO t2 VALUES (404,116602,36,'births','beasts','alimony','A'); 453INSERT INTO t2 VALUES (405,116603,36,'garages','Witt','principled','A'); 454INSERT INTO t2 VALUES (406,116701,36,'panty','physicists','golfing',''); 455INSERT INTO t2 VALUES (407,116702,36,'anteater','folksong','undiscovered',''); 456INSERT INTO t2 VALUES (408,118001,36,'displacement','strokes','irritates',''); 457INSERT INTO t2 VALUES (409,118002,36,'drovers','crowder','patriots','A'); 458INSERT INTO t2 VALUES (410,118003,36,'patenting','merry','rooms','FAS'); 459INSERT INTO t2 VALUES (411,118004,36,'far','cadenced','towering','W'); 460INSERT INTO t2 VALUES (412,118005,36,'shrieks','alimony','displease',''); 461INSERT INTO t2 VALUES (413,118006,36,'aligning','principled','photosensitive',''); 462INSERT INTO t2 VALUES (414,118007,36,'pragmatism','golfing','inking',''); 463INSERT INTO t2 VALUES (415,118008,36,'fevers','undiscovered','gainers',''); 464INSERT INTO t2 VALUES (416,118101,36,'reexamines','irritates','leaning','A'); 465INSERT INTO t2 VALUES (417,118102,36,'occupancies','patriots','hydrant','A'); 466INSERT INTO t2 VALUES (418,118103,36,'sweats','rooms','preserve',''); 467INSERT INTO t2 VALUES (419,118202,36,'modulators','towering','blinded','A'); 468INSERT INTO t2 VALUES (420,118203,36,'demand','displease','interactions','A'); 469INSERT INTO t2 VALUES (421,118204,36,'Madeira','photosensitive','Barry',''); 470INSERT INTO t2 VALUES (422,118302,36,'Viennese','inking','whiteness','A'); 471INSERT INTO t2 VALUES (423,118304,36,'chillier','gainers','pastimes','W'); 472INSERT INTO t2 VALUES (424,118305,36,'wildcats','leaning','Edenization',''); 473INSERT INTO t2 VALUES (425,118306,36,'gentle','hydrant','Muscat',''); 474INSERT INTO t2 VALUES (426,118307,36,'Angles','preserve','assassinated',''); 475INSERT INTO t2 VALUES (427,123101,36,'accuracies','blinded','labeled',''); 476INSERT INTO t2 VALUES (428,123102,36,'toggle','interactions','glacial','A'); 477INSERT INTO t2 VALUES (429,123301,36,'Mendelssohn','Barry','implied','W'); 478INSERT INTO t2 VALUES (430,126001,36,'behaviorally','whiteness','bibliographies','W'); 479INSERT INTO t2 VALUES (431,126002,36,'Rochford','pastimes','Buchanan',''); 480INSERT INTO t2 VALUES (432,126003,36,'mirror','Edenization','forgivably','FAS'); 481INSERT INTO t2 VALUES (433,126101,36,'Modula','Muscat','innuendo','A'); 482INSERT INTO t2 VALUES (434,126301,36,'clobbering','assassinated','den','FAS'); 483INSERT INTO t2 VALUES (435,126302,36,'chronography','labeled','submarines','W'); 484INSERT INTO t2 VALUES (436,126402,36,'Eskimoizeds','glacial','mouthful','A'); 485INSERT INTO t2 VALUES (437,126601,36,'British','implied','expiring',''); 486INSERT INTO t2 VALUES (438,126602,36,'pitfalls','bibliographies','unfulfilled','FAS'); 487INSERT INTO t2 VALUES (439,126702,36,'verify','Buchanan','precession',''); 488INSERT INTO t2 VALUES (440,128001,36,'scatter','forgivably','nullified',''); 489INSERT INTO t2 VALUES (441,128002,36,'Aztecan','innuendo','affects',''); 490INSERT INTO t2 VALUES (442,128003,36,'acuity','den','Cynthia',''); 491INSERT INTO t2 VALUES (443,128004,36,'sinking','submarines','Chablis','A'); 492INSERT INTO t2 VALUES (444,128005,36,'beasts','mouthful','betterments','FAS'); 493INSERT INTO t2 VALUES (445,128007,36,'Witt','expiring','advertising',''); 494INSERT INTO t2 VALUES (446,128008,36,'physicists','unfulfilled','rubies','A'); 495INSERT INTO t2 VALUES (447,128009,36,'folksong','precession','southwest','FAS'); 496INSERT INTO t2 VALUES (448,128010,36,'strokes','nullified','superstitious','A'); 497INSERT INTO t2 VALUES (449,128011,36,'crowder','affects','tabernacle','W'); 498INSERT INTO t2 VALUES (450,128012,36,'merry','Cynthia','silk','A'); 499INSERT INTO t2 VALUES (451,128013,36,'cadenced','Chablis','handsomest','A'); 500INSERT INTO t2 VALUES (452,128014,36,'alimony','betterments','Persian','A'); 501INSERT INTO t2 VALUES (453,128015,36,'principled','advertising','analog','W'); 502INSERT INTO t2 VALUES (454,128016,36,'golfing','rubies','complex','W'); 503INSERT INTO t2 VALUES (455,128017,36,'undiscovered','southwest','Taoist',''); 504INSERT INTO t2 VALUES (456,128018,36,'irritates','superstitious','suspend',''); 505INSERT INTO t2 VALUES (457,128019,36,'patriots','tabernacle','relegated',''); 506INSERT INTO t2 VALUES (458,128020,36,'rooms','silk','awesome','W'); 507INSERT INTO t2 VALUES (459,128021,36,'towering','handsomest','Bruxelles',''); 508INSERT INTO t2 VALUES (460,128022,36,'displease','Persian','imprecisely','A'); 509INSERT INTO t2 VALUES (461,128023,36,'photosensitive','analog','televise',''); 510INSERT INTO t2 VALUES (462,128101,36,'inking','complex','braking',''); 511INSERT INTO t2 VALUES (463,128102,36,'gainers','Taoist','true','FAS'); 512INSERT INTO t2 VALUES (464,128103,36,'leaning','suspend','disappointing','FAS'); 513INSERT INTO t2 VALUES (465,128104,36,'hydrant','relegated','navally','W'); 514INSERT INTO t2 VALUES (466,128106,36,'preserve','awesome','circus',''); 515INSERT INTO t2 VALUES (467,128107,36,'blinded','Bruxelles','beetles',''); 516INSERT INTO t2 VALUES (468,128108,36,'interactions','imprecisely','trumps',''); 517INSERT INTO t2 VALUES (469,128202,36,'Barry','televise','fourscore','W'); 518INSERT INTO t2 VALUES (470,128203,36,'whiteness','braking','Blackfoots',''); 519INSERT INTO t2 VALUES (471,128301,36,'pastimes','true','Grady',''); 520INSERT INTO t2 VALUES (472,128302,36,'Edenization','disappointing','quiets','FAS'); 521INSERT INTO t2 VALUES (473,128303,36,'Muscat','navally','floundered','FAS'); 522INSERT INTO t2 VALUES (474,128304,36,'assassinated','circus','profundity','W'); 523INSERT INTO t2 VALUES (475,128305,36,'labeled','beetles','Garrisonian','W'); 524INSERT INTO t2 VALUES (476,128307,36,'glacial','trumps','Strauss',''); 525INSERT INTO t2 VALUES (477,128401,36,'implied','fourscore','cemented','FAS'); 526INSERT INTO t2 VALUES (478,128502,36,'bibliographies','Blackfoots','contrition','A'); 527INSERT INTO t2 VALUES (479,128503,36,'Buchanan','Grady','mutations',''); 528INSERT INTO t2 VALUES (480,128504,36,'forgivably','quiets','exhibits','W'); 529INSERT INTO t2 VALUES (481,128505,36,'innuendo','floundered','tits',''); 530INSERT INTO t2 VALUES (482,128601,36,'den','profundity','mate','A'); 531INSERT INTO t2 VALUES (483,128603,36,'submarines','Garrisonian','arches',''); 532INSERT INTO t2 VALUES (484,128604,36,'mouthful','Strauss','Moll',''); 533INSERT INTO t2 VALUES (485,128702,36,'expiring','cemented','ropers',''); 534INSERT INTO t2 VALUES (486,128703,36,'unfulfilled','contrition','bombast',''); 535INSERT INTO t2 VALUES (487,128704,36,'precession','mutations','difficultly','A'); 536INSERT INTO t2 VALUES (488,138001,36,'nullified','exhibits','adsorption',''); 537INSERT INTO t2 VALUES (489,138002,36,'affects','tits','definiteness','FAS'); 538INSERT INTO t2 VALUES (490,138003,36,'Cynthia','mate','cultivation','A'); 539INSERT INTO t2 VALUES (491,138004,36,'Chablis','arches','heals','A'); 540INSERT INTO t2 VALUES (492,138005,36,'betterments','Moll','Heusen','W'); 541INSERT INTO t2 VALUES (493,138006,36,'advertising','ropers','target','FAS'); 542INSERT INTO t2 VALUES (494,138007,36,'rubies','bombast','cited','A'); 543INSERT INTO t2 VALUES (495,138008,36,'southwest','difficultly','congresswoman','W'); 544INSERT INTO t2 VALUES (496,138009,36,'superstitious','adsorption','Katherine',''); 545INSERT INTO t2 VALUES (497,138102,36,'tabernacle','definiteness','titter','A'); 546INSERT INTO t2 VALUES (498,138103,36,'silk','cultivation','aspire','A'); 547INSERT INTO t2 VALUES (499,138104,36,'handsomest','heals','Mardis',''); 548INSERT INTO t2 VALUES (500,138105,36,'Persian','Heusen','Nadia','W'); 549INSERT INTO t2 VALUES (501,138201,36,'analog','target','estimating','FAS'); 550INSERT INTO t2 VALUES (502,138302,36,'complex','cited','stuck','A'); 551INSERT INTO t2 VALUES (503,138303,36,'Taoist','congresswoman','fifteenth','A'); 552INSERT INTO t2 VALUES (504,138304,36,'suspend','Katherine','Colombo',''); 553INSERT INTO t2 VALUES (505,138401,29,'relegated','titter','survey','A'); 554INSERT INTO t2 VALUES (506,140102,29,'awesome','aspire','staffing',''); 555INSERT INTO t2 VALUES (507,140103,29,'Bruxelles','Mardis','obtain',''); 556INSERT INTO t2 VALUES (508,140104,29,'imprecisely','Nadia','loaded',''); 557INSERT INTO t2 VALUES (509,140105,29,'televise','estimating','slaughtered',''); 558INSERT INTO t2 VALUES (510,140201,29,'braking','stuck','lights','A'); 559INSERT INTO t2 VALUES (511,140701,29,'true','fifteenth','circumference',''); 560INSERT INTO t2 VALUES (512,141501,29,'disappointing','Colombo','dull','A'); 561INSERT INTO t2 VALUES (513,141502,29,'navally','survey','weekly','A'); 562INSERT INTO t2 VALUES (514,141901,29,'circus','staffing','wetness',''); 563INSERT INTO t2 VALUES (515,141902,29,'beetles','obtain','visualized',''); 564INSERT INTO t2 VALUES (516,142101,29,'trumps','loaded','Tannenbaum',''); 565INSERT INTO t2 VALUES (517,142102,29,'fourscore','slaughtered','moribund',''); 566INSERT INTO t2 VALUES (518,142103,29,'Blackfoots','lights','demultiplex',''); 567INSERT INTO t2 VALUES (519,142701,29,'Grady','circumference','lockings',''); 568INSERT INTO t2 VALUES (520,143001,29,'quiets','dull','thugs','FAS'); 569INSERT INTO t2 VALUES (521,143501,29,'floundered','weekly','unnerves',''); 570INSERT INTO t2 VALUES (522,143502,29,'profundity','wetness','abut',''); 571INSERT INTO t2 VALUES (523,148001,29,'Garrisonian','visualized','Chippewa','A'); 572INSERT INTO t2 VALUES (524,148002,29,'Strauss','Tannenbaum','stratifications','A'); 573INSERT INTO t2 VALUES (525,148003,29,'cemented','moribund','signaled',''); 574INSERT INTO t2 VALUES (526,148004,29,'contrition','demultiplex','Italianizes','A'); 575INSERT INTO t2 VALUES (527,148005,29,'mutations','lockings','algorithmic','A'); 576INSERT INTO t2 VALUES (528,148006,29,'exhibits','thugs','paranoid','FAS'); 577INSERT INTO t2 VALUES (529,148007,29,'tits','unnerves','camping','A'); 578INSERT INTO t2 VALUES (530,148009,29,'mate','abut','signifying','A'); 579INSERT INTO t2 VALUES (531,148010,29,'arches','Chippewa','Patrice','W'); 580INSERT INTO t2 VALUES (532,148011,29,'Moll','stratifications','search','A'); 581INSERT INTO t2 VALUES (533,148012,29,'ropers','signaled','Angeles','A'); 582INSERT INTO t2 VALUES (534,148013,29,'bombast','Italianizes','semblance',''); 583INSERT INTO t2 VALUES (535,148023,36,'difficultly','algorithmic','taxed',''); 584INSERT INTO t2 VALUES (536,148015,29,'adsorption','paranoid','Beatrice',''); 585INSERT INTO t2 VALUES (537,148016,29,'definiteness','camping','retrace',''); 586INSERT INTO t2 VALUES (538,148017,29,'cultivation','signifying','lockout',''); 587INSERT INTO t2 VALUES (539,148018,29,'heals','Patrice','grammatic',''); 588INSERT INTO t2 VALUES (540,148019,29,'Heusen','search','helmsman',''); 589INSERT INTO t2 VALUES (541,148020,29,'target','Angeles','uniform','W'); 590INSERT INTO t2 VALUES (542,148021,29,'cited','semblance','hamming',''); 591INSERT INTO t2 VALUES (543,148022,29,'congresswoman','taxed','disobedience',''); 592INSERT INTO t2 VALUES (544,148101,29,'Katherine','Beatrice','captivated','A'); 593INSERT INTO t2 VALUES (545,148102,29,'titter','retrace','transferals','A'); 594INSERT INTO t2 VALUES (546,148201,29,'aspire','lockout','cartographer','A'); 595INSERT INTO t2 VALUES (547,148401,29,'Mardis','grammatic','aims','FAS'); 596INSERT INTO t2 VALUES (548,148402,29,'Nadia','helmsman','Pakistani',''); 597INSERT INTO t2 VALUES (549,148501,29,'estimating','uniform','burglarized','FAS'); 598INSERT INTO t2 VALUES (550,148502,29,'stuck','hamming','saucepans','A'); 599INSERT INTO t2 VALUES (551,148503,29,'fifteenth','disobedience','lacerating','A'); 600INSERT INTO t2 VALUES (552,148504,29,'Colombo','captivated','corny',''); 601INSERT INTO t2 VALUES (553,148601,29,'survey','transferals','megabytes','FAS'); 602INSERT INTO t2 VALUES (554,148602,29,'staffing','cartographer','chancellor',''); 603INSERT INTO t2 VALUES (555,150701,29,'obtain','aims','bulk','A'); 604INSERT INTO t2 VALUES (556,152101,29,'loaded','Pakistani','commits','A'); 605INSERT INTO t2 VALUES (557,152102,29,'slaughtered','burglarized','meson','W'); 606INSERT INTO t2 VALUES (558,155202,36,'lights','saucepans','deputies',''); 607INSERT INTO t2 VALUES (559,155203,29,'circumference','lacerating','northeaster','A'); 608INSERT INTO t2 VALUES (560,155204,29,'dull','corny','dipole',''); 609INSERT INTO t2 VALUES (561,155205,29,'weekly','megabytes','machining','0'); 610INSERT INTO t2 VALUES (562,156001,29,'wetness','chancellor','therefore',''); 611INSERT INTO t2 VALUES (563,156002,29,'visualized','bulk','Telefunken',''); 612INSERT INTO t2 VALUES (564,156102,29,'Tannenbaum','commits','salvaging',''); 613INSERT INTO t2 VALUES (565,156301,29,'moribund','meson','Corinthianizes','A'); 614INSERT INTO t2 VALUES (566,156302,29,'demultiplex','deputies','restlessly','A'); 615INSERT INTO t2 VALUES (567,156303,29,'lockings','northeaster','bromides',''); 616INSERT INTO t2 VALUES (568,156304,29,'thugs','dipole','generalized','A'); 617INSERT INTO t2 VALUES (569,156305,29,'unnerves','machining','mishaps',''); 618INSERT INTO t2 VALUES (570,156306,29,'abut','therefore','quelling',''); 619INSERT INTO t2 VALUES (571,156501,29,'Chippewa','Telefunken','spiritual','A'); 620INSERT INTO t2 VALUES (572,158001,29,'stratifications','salvaging','beguiles','FAS'); 621INSERT INTO t2 VALUES (573,158002,29,'signaled','Corinthianizes','Trobriand','FAS'); 622INSERT INTO t2 VALUES (574,158101,29,'Italianizes','restlessly','fleeing','A'); 623INSERT INTO t2 VALUES (575,158102,29,'algorithmic','bromides','Armour','A'); 624INSERT INTO t2 VALUES (576,158103,29,'paranoid','generalized','chin','A'); 625INSERT INTO t2 VALUES (577,158201,29,'camping','mishaps','provers','A'); 626INSERT INTO t2 VALUES (578,158202,29,'signifying','quelling','aeronautic','A'); 627INSERT INTO t2 VALUES (579,158203,29,'Patrice','spiritual','voltage','W'); 628INSERT INTO t2 VALUES (580,158204,29,'search','beguiles','sash',''); 629INSERT INTO t2 VALUES (581,158301,29,'Angeles','Trobriand','anaerobic','A'); 630INSERT INTO t2 VALUES (582,158302,29,'semblance','fleeing','simultaneous','A'); 631INSERT INTO t2 VALUES (583,158303,29,'taxed','Armour','accumulating','A'); 632INSERT INTO t2 VALUES (584,158304,29,'Beatrice','chin','Medusan','A'); 633INSERT INTO t2 VALUES (585,158305,29,'retrace','provers','shouted','A'); 634INSERT INTO t2 VALUES (586,158306,29,'lockout','aeronautic','freakish',''); 635INSERT INTO t2 VALUES (587,158501,29,'grammatic','voltage','index','FAS'); 636INSERT INTO t2 VALUES (588,160301,29,'helmsman','sash','commercially',''); 637INSERT INTO t2 VALUES (589,166101,50,'uniform','anaerobic','mistiness','A'); 638INSERT INTO t2 VALUES (590,166102,50,'hamming','simultaneous','endpoint',''); 639INSERT INTO t2 VALUES (591,168001,29,'disobedience','accumulating','straight','A'); 640INSERT INTO t2 VALUES (592,168002,29,'captivated','Medusan','flurried',''); 641INSERT INTO t2 VALUES (593,168003,29,'transferals','shouted','denotative','A'); 642INSERT INTO t2 VALUES (594,168101,29,'cartographer','freakish','coming','FAS'); 643INSERT INTO t2 VALUES (595,168102,29,'aims','index','commencements','FAS'); 644INSERT INTO t2 VALUES (596,168103,29,'Pakistani','commercially','gentleman',''); 645INSERT INTO t2 VALUES (597,168104,29,'burglarized','mistiness','gifted',''); 646INSERT INTO t2 VALUES (598,168202,29,'saucepans','endpoint','Shanghais',''); 647INSERT INTO t2 VALUES (599,168301,29,'lacerating','straight','sportswriting','A'); 648INSERT INTO t2 VALUES (600,168502,29,'corny','flurried','sloping','A'); 649INSERT INTO t2 VALUES (601,168503,29,'megabytes','denotative','navies',''); 650INSERT INTO t2 VALUES (602,168601,29,'chancellor','coming','leaflet','A'); 651INSERT INTO t2 VALUES (603,173001,40,'bulk','commencements','shooter',''); 652INSERT INTO t2 VALUES (604,173701,40,'commits','gentleman','Joplin','FAS'); 653INSERT INTO t2 VALUES (605,173702,40,'meson','gifted','babies',''); 654INSERT INTO t2 VALUES (606,176001,40,'deputies','Shanghais','subdivision','FAS'); 655INSERT INTO t2 VALUES (607,176101,40,'northeaster','sportswriting','burstiness','W'); 656INSERT INTO t2 VALUES (608,176201,40,'dipole','sloping','belted','FAS'); 657INSERT INTO t2 VALUES (609,176401,40,'machining','navies','assails','FAS'); 658INSERT INTO t2 VALUES (610,176501,40,'therefore','leaflet','admiring','W'); 659INSERT INTO t2 VALUES (611,176601,40,'Telefunken','shooter','swaying','0'); 660INSERT INTO t2 VALUES (612,176602,40,'salvaging','Joplin','Goldstine','FAS'); 661INSERT INTO t2 VALUES (613,176603,40,'Corinthianizes','babies','fitting',''); 662INSERT INTO t2 VALUES (614,178001,40,'restlessly','subdivision','Norwalk','W'); 663INSERT INTO t2 VALUES (615,178002,40,'bromides','burstiness','weakening','W'); 664INSERT INTO t2 VALUES (616,178003,40,'generalized','belted','analogy','FAS'); 665INSERT INTO t2 VALUES (617,178004,40,'mishaps','assails','deludes',''); 666INSERT INTO t2 VALUES (618,178005,40,'quelling','admiring','cokes',''); 667INSERT INTO t2 VALUES (619,178006,40,'spiritual','swaying','Clayton',''); 668INSERT INTO t2 VALUES (620,178007,40,'beguiles','Goldstine','exhausts',''); 669INSERT INTO t2 VALUES (621,178008,40,'Trobriand','fitting','causality',''); 670INSERT INTO t2 VALUES (622,178101,40,'fleeing','Norwalk','sating','FAS'); 671INSERT INTO t2 VALUES (623,178102,40,'Armour','weakening','icon',''); 672INSERT INTO t2 VALUES (624,178103,40,'chin','analogy','throttles',''); 673INSERT INTO t2 VALUES (625,178201,40,'provers','deludes','communicants','FAS'); 674INSERT INTO t2 VALUES (626,178202,40,'aeronautic','cokes','dehydrate','FAS'); 675INSERT INTO t2 VALUES (627,178301,40,'voltage','Clayton','priceless','FAS'); 676INSERT INTO t2 VALUES (628,178302,40,'sash','exhausts','publicly',''); 677INSERT INTO t2 VALUES (629,178401,40,'anaerobic','causality','incidentals','FAS'); 678INSERT INTO t2 VALUES (630,178402,40,'simultaneous','sating','commonplace',''); 679INSERT INTO t2 VALUES (631,178403,40,'accumulating','icon','mumbles',''); 680INSERT INTO t2 VALUES (632,178404,40,'Medusan','throttles','furthermore','W'); 681INSERT INTO t2 VALUES (633,178501,40,'shouted','communicants','cautioned','W'); 682INSERT INTO t2 VALUES (634,186002,37,'freakish','dehydrate','parametrized','A'); 683INSERT INTO t2 VALUES (635,186102,37,'index','priceless','registration','A'); 684INSERT INTO t2 VALUES (636,186201,40,'commercially','publicly','sadly','FAS'); 685INSERT INTO t2 VALUES (637,186202,40,'mistiness','incidentals','positioning',''); 686INSERT INTO t2 VALUES (638,186203,40,'endpoint','commonplace','babysitting',''); 687INSERT INTO t2 VALUES (639,186302,37,'straight','mumbles','eternal','A'); 688INSERT INTO t2 VALUES (640,188007,37,'flurried','furthermore','hoarder',''); 689INSERT INTO t2 VALUES (641,188008,37,'denotative','cautioned','congregates',''); 690INSERT INTO t2 VALUES (642,188009,37,'coming','parametrized','rains',''); 691INSERT INTO t2 VALUES (643,188010,37,'commencements','registration','workers','W'); 692INSERT INTO t2 VALUES (644,188011,37,'gentleman','sadly','sags','A'); 693INSERT INTO t2 VALUES (645,188012,37,'gifted','positioning','unplug','W'); 694INSERT INTO t2 VALUES (646,188013,37,'Shanghais','babysitting','garage','A'); 695INSERT INTO t2 VALUES (647,188014,37,'sportswriting','eternal','boulder','A'); 696INSERT INTO t2 VALUES (648,188015,37,'sloping','hoarder','hollowly','A'); 697INSERT INTO t2 VALUES (649,188016,37,'navies','congregates','specifics',''); 698INSERT INTO t2 VALUES (650,188017,37,'leaflet','rains','Teresa',''); 699INSERT INTO t2 VALUES (651,188102,37,'shooter','workers','Winsett',''); 700INSERT INTO t2 VALUES (652,188103,37,'Joplin','sags','convenient','A'); 701INSERT INTO t2 VALUES (653,188202,37,'babies','unplug','buckboards','FAS'); 702INSERT INTO t2 VALUES (654,188301,40,'subdivision','garage','amenities',''); 703INSERT INTO t2 VALUES (655,188302,40,'burstiness','boulder','resplendent','FAS'); 704INSERT INTO t2 VALUES (656,188303,40,'belted','hollowly','priding','FAS'); 705INSERT INTO t2 VALUES (657,188401,37,'assails','specifics','configurations',''); 706INSERT INTO t2 VALUES (658,188402,37,'admiring','Teresa','untidiness','A'); 707INSERT INTO t2 VALUES (659,188503,37,'swaying','Winsett','Brice','W'); 708INSERT INTO t2 VALUES (660,188504,37,'Goldstine','convenient','sews','FAS'); 709INSERT INTO t2 VALUES (661,188505,37,'fitting','buckboards','participated',''); 710INSERT INTO t2 VALUES (662,190701,37,'Norwalk','amenities','Simon','FAS'); 711INSERT INTO t2 VALUES (663,190703,50,'weakening','resplendent','certificates',''); 712INSERT INTO t2 VALUES (664,191701,37,'analogy','priding','Fitzpatrick',''); 713INSERT INTO t2 VALUES (665,191702,37,'deludes','configurations','Evanston','A'); 714INSERT INTO t2 VALUES (666,191703,37,'cokes','untidiness','misted',''); 715INSERT INTO t2 VALUES (667,196001,37,'Clayton','Brice','textures','A'); 716INSERT INTO t2 VALUES (668,196002,37,'exhausts','sews','save',''); 717INSERT INTO t2 VALUES (669,196003,37,'causality','participated','count',''); 718INSERT INTO t2 VALUES (670,196101,37,'sating','Simon','rightful','A'); 719INSERT INTO t2 VALUES (671,196103,37,'icon','certificates','chaperone',''); 720INSERT INTO t2 VALUES (672,196104,37,'throttles','Fitzpatrick','Lizzy','A'); 721INSERT INTO t2 VALUES (673,196201,37,'communicants','Evanston','clenched','A'); 722INSERT INTO t2 VALUES (674,196202,37,'dehydrate','misted','effortlessly',''); 723INSERT INTO t2 VALUES (675,196203,37,'priceless','textures','accessed',''); 724INSERT INTO t2 VALUES (676,198001,37,'publicly','save','beaters','A'); 725INSERT INTO t2 VALUES (677,198003,37,'incidentals','count','Hornblower','FAS'); 726INSERT INTO t2 VALUES (678,198004,37,'commonplace','rightful','vests','A'); 727INSERT INTO t2 VALUES (679,198005,37,'mumbles','chaperone','indulgences','FAS'); 728INSERT INTO t2 VALUES (680,198006,37,'furthermore','Lizzy','infallibly','A'); 729INSERT INTO t2 VALUES (681,198007,37,'cautioned','clenched','unwilling','FAS'); 730INSERT INTO t2 VALUES (682,198008,37,'parametrized','effortlessly','excrete','FAS'); 731INSERT INTO t2 VALUES (683,198009,37,'registration','accessed','spools','A'); 732INSERT INTO t2 VALUES (684,198010,37,'sadly','beaters','crunches','FAS'); 733INSERT INTO t2 VALUES (685,198011,37,'positioning','Hornblower','overestimating','FAS'); 734INSERT INTO t2 VALUES (686,198012,37,'babysitting','vests','ineffective',''); 735INSERT INTO t2 VALUES (687,198013,37,'eternal','indulgences','humiliation','A'); 736INSERT INTO t2 VALUES (688,198014,37,'hoarder','infallibly','sophomore',''); 737INSERT INTO t2 VALUES (689,198015,37,'congregates','unwilling','star',''); 738INSERT INTO t2 VALUES (690,198017,37,'rains','excrete','rifles',''); 739INSERT INTO t2 VALUES (691,198018,37,'workers','spools','dialysis',''); 740INSERT INTO t2 VALUES (692,198019,37,'sags','crunches','arriving',''); 741INSERT INTO t2 VALUES (693,198020,37,'unplug','overestimating','indulge',''); 742INSERT INTO t2 VALUES (694,198021,37,'garage','ineffective','clockers',''); 743INSERT INTO t2 VALUES (695,198022,37,'boulder','humiliation','languages',''); 744INSERT INTO t2 VALUES (696,198023,50,'hollowly','sophomore','Antarctica','A'); 745INSERT INTO t2 VALUES (697,198024,37,'specifics','star','percentage',''); 746INSERT INTO t2 VALUES (698,198101,37,'Teresa','rifles','ceiling','A'); 747INSERT INTO t2 VALUES (699,198103,37,'Winsett','dialysis','specification',''); 748INSERT INTO t2 VALUES (700,198105,37,'convenient','arriving','regimented','A'); 749INSERT INTO t2 VALUES (701,198106,37,'buckboards','indulge','ciphers',''); 750INSERT INTO t2 VALUES (702,198201,37,'amenities','clockers','pictures','A'); 751INSERT INTO t2 VALUES (703,198204,37,'resplendent','languages','serpents','A'); 752INSERT INTO t2 VALUES (704,198301,53,'priding','Antarctica','allot','A'); 753INSERT INTO t2 VALUES (705,198302,53,'configurations','percentage','realized','A'); 754INSERT INTO t2 VALUES (706,198303,53,'untidiness','ceiling','mayoral','A'); 755INSERT INTO t2 VALUES (707,198304,53,'Brice','specification','opaquely','A'); 756INSERT INTO t2 VALUES (708,198401,37,'sews','regimented','hostess','FAS'); 757INSERT INTO t2 VALUES (709,198402,37,'participated','ciphers','fiftieth',''); 758INSERT INTO t2 VALUES (710,198403,37,'Simon','pictures','incorrectly',''); 759INSERT INTO t2 VALUES (711,202101,37,'certificates','serpents','decomposition','FAS'); 760INSERT INTO t2 VALUES (712,202301,37,'Fitzpatrick','allot','stranglings',''); 761INSERT INTO t2 VALUES (713,202302,37,'Evanston','realized','mixture','FAS'); 762INSERT INTO t2 VALUES (714,202303,37,'misted','mayoral','electroencephalography','FAS'); 763INSERT INTO t2 VALUES (715,202304,37,'textures','opaquely','similarities','FAS'); 764INSERT INTO t2 VALUES (716,202305,37,'save','hostess','charges','W'); 765INSERT INTO t2 VALUES (717,202601,37,'count','fiftieth','freest','FAS'); 766INSERT INTO t2 VALUES (718,202602,37,'rightful','incorrectly','Greenberg','FAS'); 767INSERT INTO t2 VALUES (719,202605,37,'chaperone','decomposition','tinting',''); 768INSERT INTO t2 VALUES (720,202606,37,'Lizzy','stranglings','expelled','W'); 769INSERT INTO t2 VALUES (721,202607,37,'clenched','mixture','warm',''); 770INSERT INTO t2 VALUES (722,202901,37,'effortlessly','electroencephalography','smoothed',''); 771INSERT INTO t2 VALUES (723,202902,37,'accessed','similarities','deductions','FAS'); 772INSERT INTO t2 VALUES (724,202903,37,'beaters','charges','Romano','W'); 773INSERT INTO t2 VALUES (725,202904,37,'Hornblower','freest','bitterroot',''); 774INSERT INTO t2 VALUES (726,202907,37,'vests','Greenberg','corset',''); 775INSERT INTO t2 VALUES (727,202908,37,'indulgences','tinting','securing',''); 776INSERT INTO t2 VALUES (728,203101,37,'infallibly','expelled','environing','FAS'); 777INSERT INTO t2 VALUES (729,203103,37,'unwilling','warm','cute',''); 778INSERT INTO t2 VALUES (730,203104,37,'excrete','smoothed','Crays',''); 779INSERT INTO t2 VALUES (731,203105,37,'spools','deductions','heiress','FAS'); 780INSERT INTO t2 VALUES (732,203401,37,'crunches','Romano','inform','FAS'); 781INSERT INTO t2 VALUES (733,203402,37,'overestimating','bitterroot','avenge',''); 782INSERT INTO t2 VALUES (734,203404,37,'ineffective','corset','universals',''); 783INSERT INTO t2 VALUES (735,203901,37,'humiliation','securing','Kinsey','W'); 784INSERT INTO t2 VALUES (736,203902,37,'sophomore','environing','ravines','FAS'); 785INSERT INTO t2 VALUES (737,203903,37,'star','cute','bestseller',''); 786INSERT INTO t2 VALUES (738,203906,37,'rifles','Crays','equilibrium',''); 787INSERT INTO t2 VALUES (739,203907,37,'dialysis','heiress','extents','0'); 788INSERT INTO t2 VALUES (740,203908,37,'arriving','inform','relatively',''); 789INSERT INTO t2 VALUES (741,203909,37,'indulge','avenge','pressure','FAS'); 790INSERT INTO t2 VALUES (742,206101,37,'clockers','universals','critiques','FAS'); 791INSERT INTO t2 VALUES (743,206201,37,'languages','Kinsey','befouled',''); 792INSERT INTO t2 VALUES (744,206202,37,'Antarctica','ravines','rightfully','FAS'); 793INSERT INTO t2 VALUES (745,206203,37,'percentage','bestseller','mechanizing','FAS'); 794INSERT INTO t2 VALUES (746,206206,37,'ceiling','equilibrium','Latinizes',''); 795INSERT INTO t2 VALUES (747,206207,37,'specification','extents','timesharing',''); 796INSERT INTO t2 VALUES (748,206208,37,'regimented','relatively','Aden',''); 797INSERT INTO t2 VALUES (749,208001,37,'ciphers','pressure','embassies',''); 798INSERT INTO t2 VALUES (750,208002,37,'pictures','critiques','males','FAS'); 799INSERT INTO t2 VALUES (751,208003,37,'serpents','befouled','shapelessly','FAS'); 800INSERT INTO t2 VALUES (752,208004,37,'allot','rightfully','genres','FAS'); 801INSERT INTO t2 VALUES (753,208008,37,'realized','mechanizing','mastering',''); 802INSERT INTO t2 VALUES (754,208009,37,'mayoral','Latinizes','Newtonian',''); 803INSERT INTO t2 VALUES (755,208010,37,'opaquely','timesharing','finishers','FAS'); 804INSERT INTO t2 VALUES (756,208011,37,'hostess','Aden','abates',''); 805INSERT INTO t2 VALUES (757,208101,37,'fiftieth','embassies','teem',''); 806INSERT INTO t2 VALUES (758,208102,37,'incorrectly','males','kiting','FAS'); 807INSERT INTO t2 VALUES (759,208103,37,'decomposition','shapelessly','stodgy','FAS'); 808INSERT INTO t2 VALUES (760,208104,37,'stranglings','genres','scalps','FAS'); 809INSERT INTO t2 VALUES (761,208105,37,'mixture','mastering','feed','FAS'); 810INSERT INTO t2 VALUES (762,208110,37,'electroencephalography','Newtonian','guitars',''); 811INSERT INTO t2 VALUES (763,208111,37,'similarities','finishers','airships',''); 812INSERT INTO t2 VALUES (764,208112,37,'charges','abates','store',''); 813INSERT INTO t2 VALUES (765,208113,37,'freest','teem','denounces',''); 814INSERT INTO t2 VALUES (766,208201,37,'Greenberg','kiting','Pyle','FAS'); 815INSERT INTO t2 VALUES (767,208203,37,'tinting','stodgy','Saxony',''); 816INSERT INTO t2 VALUES (768,208301,37,'expelled','scalps','serializations','FAS'); 817INSERT INTO t2 VALUES (769,208302,37,'warm','feed','Peruvian','FAS'); 818INSERT INTO t2 VALUES (770,208305,37,'smoothed','guitars','taxonomically','FAS'); 819INSERT INTO t2 VALUES (771,208401,37,'deductions','airships','kingdom','A'); 820INSERT INTO t2 VALUES (772,208402,37,'Romano','store','stint','A'); 821INSERT INTO t2 VALUES (773,208403,37,'bitterroot','denounces','Sault','A'); 822INSERT INTO t2 VALUES (774,208404,37,'corset','Pyle','faithful',''); 823INSERT INTO t2 VALUES (775,208501,37,'securing','Saxony','Ganymede','FAS'); 824INSERT INTO t2 VALUES (776,208502,37,'environing','serializations','tidiness','FAS'); 825INSERT INTO t2 VALUES (777,208503,37,'cute','Peruvian','gainful','FAS'); 826INSERT INTO t2 VALUES (778,208504,37,'Crays','taxonomically','contrary','FAS'); 827INSERT INTO t2 VALUES (779,208505,37,'heiress','kingdom','Tipperary','FAS'); 828INSERT INTO t2 VALUES (780,210101,37,'inform','stint','tropics','W'); 829INSERT INTO t2 VALUES (781,210102,37,'avenge','Sault','theorizers',''); 830INSERT INTO t2 VALUES (782,210103,37,'universals','faithful','renew','0'); 831INSERT INTO t2 VALUES (783,210104,37,'Kinsey','Ganymede','already',''); 832INSERT INTO t2 VALUES (784,210105,37,'ravines','tidiness','terminal',''); 833INSERT INTO t2 VALUES (785,210106,37,'bestseller','gainful','Hegelian',''); 834INSERT INTO t2 VALUES (786,210107,37,'equilibrium','contrary','hypothesizer',''); 835INSERT INTO t2 VALUES (787,210401,37,'extents','Tipperary','warningly','FAS'); 836INSERT INTO t2 VALUES (788,213201,37,'relatively','tropics','journalizing','FAS'); 837INSERT INTO t2 VALUES (789,213203,37,'pressure','theorizers','nested',''); 838INSERT INTO t2 VALUES (790,213204,37,'critiques','renew','Lars',''); 839INSERT INTO t2 VALUES (791,213205,37,'befouled','already','saplings',''); 840INSERT INTO t2 VALUES (792,213206,37,'rightfully','terminal','foothill',''); 841INSERT INTO t2 VALUES (793,213207,37,'mechanizing','Hegelian','labeled',''); 842INSERT INTO t2 VALUES (794,216101,37,'Latinizes','hypothesizer','imperiously','FAS'); 843INSERT INTO t2 VALUES (795,216103,37,'timesharing','warningly','reporters','FAS'); 844INSERT INTO t2 VALUES (796,218001,37,'Aden','journalizing','furnishings','FAS'); 845INSERT INTO t2 VALUES (797,218002,37,'embassies','nested','precipitable','FAS'); 846INSERT INTO t2 VALUES (798,218003,37,'males','Lars','discounts','FAS'); 847INSERT INTO t2 VALUES (799,218004,37,'shapelessly','saplings','excises','FAS'); 848INSERT INTO t2 VALUES (800,143503,50,'genres','foothill','Stalin',''); 849INSERT INTO t2 VALUES (801,218006,37,'mastering','labeled','despot','FAS'); 850INSERT INTO t2 VALUES (802,218007,37,'Newtonian','imperiously','ripeness','FAS'); 851INSERT INTO t2 VALUES (803,218008,37,'finishers','reporters','Arabia',''); 852INSERT INTO t2 VALUES (804,218009,37,'abates','furnishings','unruly',''); 853INSERT INTO t2 VALUES (805,218010,37,'teem','precipitable','mournfulness',''); 854INSERT INTO t2 VALUES (806,218011,37,'kiting','discounts','boom','FAS'); 855INSERT INTO t2 VALUES (807,218020,37,'stodgy','excises','slaughter','A'); 856INSERT INTO t2 VALUES (808,218021,50,'scalps','Stalin','Sabine',''); 857INSERT INTO t2 VALUES (809,218022,37,'feed','despot','handy','FAS'); 858INSERT INTO t2 VALUES (810,218023,37,'guitars','ripeness','rural',''); 859INSERT INTO t2 VALUES (811,218024,37,'airships','Arabia','organizer',''); 860INSERT INTO t2 VALUES (812,218101,37,'store','unruly','shipyard','FAS'); 861INSERT INTO t2 VALUES (813,218102,37,'denounces','mournfulness','civics','FAS'); 862INSERT INTO t2 VALUES (814,218103,37,'Pyle','boom','inaccuracy','FAS'); 863INSERT INTO t2 VALUES (815,218201,37,'Saxony','slaughter','rules','FAS'); 864INSERT INTO t2 VALUES (816,218202,37,'serializations','Sabine','juveniles','FAS'); 865INSERT INTO t2 VALUES (817,218203,37,'Peruvian','handy','comprised','W'); 866INSERT INTO t2 VALUES (818,218204,37,'taxonomically','rural','investigations',''); 867INSERT INTO t2 VALUES (819,218205,37,'kingdom','organizer','stabilizes','A'); 868INSERT INTO t2 VALUES (820,218301,37,'stint','shipyard','seminaries','FAS'); 869INSERT INTO t2 VALUES (821,218302,37,'Sault','civics','Hunter','A'); 870INSERT INTO t2 VALUES (822,218401,37,'faithful','inaccuracy','sporty','FAS'); 871INSERT INTO t2 VALUES (823,218402,37,'Ganymede','rules','test','FAS'); 872INSERT INTO t2 VALUES (824,218403,37,'tidiness','juveniles','weasels',''); 873INSERT INTO t2 VALUES (825,218404,37,'gainful','comprised','CERN',''); 874INSERT INTO t2 VALUES (826,218407,37,'contrary','investigations','tempering',''); 875INSERT INTO t2 VALUES (827,218408,37,'Tipperary','stabilizes','afore','FAS'); 876INSERT INTO t2 VALUES (828,218409,37,'tropics','seminaries','Galatean',''); 877INSERT INTO t2 VALUES (829,218410,37,'theorizers','Hunter','techniques','W'); 878INSERT INTO t2 VALUES (830,226001,37,'renew','sporty','error',''); 879INSERT INTO t2 VALUES (831,226002,37,'already','test','veranda',''); 880INSERT INTO t2 VALUES (832,226003,37,'terminal','weasels','severely',''); 881INSERT INTO t2 VALUES (833,226004,37,'Hegelian','CERN','Cassites','FAS'); 882INSERT INTO t2 VALUES (834,226005,37,'hypothesizer','tempering','forthcoming',''); 883INSERT INTO t2 VALUES (835,226006,37,'warningly','afore','guides',''); 884INSERT INTO t2 VALUES (836,226007,37,'journalizing','Galatean','vanish','FAS'); 885INSERT INTO t2 VALUES (837,226008,37,'nested','techniques','lied','A'); 886INSERT INTO t2 VALUES (838,226203,37,'Lars','error','sawtooth','FAS'); 887INSERT INTO t2 VALUES (839,226204,37,'saplings','veranda','fated','FAS'); 888INSERT INTO t2 VALUES (840,226205,37,'foothill','severely','gradually',''); 889INSERT INTO t2 VALUES (841,226206,37,'labeled','Cassites','widens',''); 890INSERT INTO t2 VALUES (842,226207,37,'imperiously','forthcoming','preclude',''); 891INSERT INTO t2 VALUES (843,226208,37,'reporters','guides','Jobrel',''); 892INSERT INTO t2 VALUES (844,226209,37,'furnishings','vanish','hooker',''); 893INSERT INTO t2 VALUES (845,226210,37,'precipitable','lied','rainstorm',''); 894INSERT INTO t2 VALUES (846,226211,37,'discounts','sawtooth','disconnects',''); 895INSERT INTO t2 VALUES (847,228001,37,'excises','fated','cruelty',''); 896INSERT INTO t2 VALUES (848,228004,37,'Stalin','gradually','exponentials','A'); 897INSERT INTO t2 VALUES (849,228005,37,'despot','widens','affective','A'); 898INSERT INTO t2 VALUES (850,228006,37,'ripeness','preclude','arteries',''); 899INSERT INTO t2 VALUES (851,228007,37,'Arabia','Jobrel','Crosby','FAS'); 900INSERT INTO t2 VALUES (852,228008,37,'unruly','hooker','acquaint',''); 901INSERT INTO t2 VALUES (853,228009,37,'mournfulness','rainstorm','evenhandedly',''); 902INSERT INTO t2 VALUES (854,228101,37,'boom','disconnects','percentage',''); 903INSERT INTO t2 VALUES (855,228108,37,'slaughter','cruelty','disobedience',''); 904INSERT INTO t2 VALUES (856,228109,37,'Sabine','exponentials','humility',''); 905INSERT INTO t2 VALUES (857,228110,37,'handy','affective','gleaning','A'); 906INSERT INTO t2 VALUES (858,228111,37,'rural','arteries','petted','A'); 907INSERT INTO t2 VALUES (859,228112,37,'organizer','Crosby','bloater','A'); 908INSERT INTO t2 VALUES (860,228113,37,'shipyard','acquaint','minion','A'); 909INSERT INTO t2 VALUES (861,228114,37,'civics','evenhandedly','marginal','A'); 910INSERT INTO t2 VALUES (862,228115,37,'inaccuracy','percentage','apiary','A'); 911INSERT INTO t2 VALUES (863,228116,37,'rules','disobedience','measures',''); 912INSERT INTO t2 VALUES (864,228117,37,'juveniles','humility','precaution',''); 913INSERT INTO t2 VALUES (865,228118,37,'comprised','gleaning','repelled',''); 914INSERT INTO t2 VALUES (866,228119,37,'investigations','petted','primary','FAS'); 915INSERT INTO t2 VALUES (867,228120,37,'stabilizes','bloater','coverings',''); 916INSERT INTO t2 VALUES (868,228121,37,'seminaries','minion','Artemia','A'); 917INSERT INTO t2 VALUES (869,228122,37,'Hunter','marginal','navigate',''); 918INSERT INTO t2 VALUES (870,228201,37,'sporty','apiary','spatial',''); 919INSERT INTO t2 VALUES (871,228206,37,'test','measures','Gurkha',''); 920INSERT INTO t2 VALUES (872,228207,37,'weasels','precaution','meanwhile','A'); 921INSERT INTO t2 VALUES (873,228208,37,'CERN','repelled','Melinda','A'); 922INSERT INTO t2 VALUES (874,228209,37,'tempering','primary','Butterfield',''); 923INSERT INTO t2 VALUES (875,228210,37,'afore','coverings','Aldrich','A'); 924INSERT INTO t2 VALUES (876,228211,37,'Galatean','Artemia','previewing','A'); 925INSERT INTO t2 VALUES (877,228212,37,'techniques','navigate','glut','A'); 926INSERT INTO t2 VALUES (878,228213,37,'error','spatial','unaffected',''); 927INSERT INTO t2 VALUES (879,228214,37,'veranda','Gurkha','inmate',''); 928INSERT INTO t2 VALUES (880,228301,37,'severely','meanwhile','mineral',''); 929INSERT INTO t2 VALUES (881,228305,37,'Cassites','Melinda','impending','A'); 930INSERT INTO t2 VALUES (882,228306,37,'forthcoming','Butterfield','meditation','A'); 931INSERT INTO t2 VALUES (883,228307,37,'guides','Aldrich','ideas',''); 932INSERT INTO t2 VALUES (884,228308,37,'vanish','previewing','miniaturizes','W'); 933INSERT INTO t2 VALUES (885,228309,37,'lied','glut','lewdly',''); 934INSERT INTO t2 VALUES (886,228310,37,'sawtooth','unaffected','title',''); 935INSERT INTO t2 VALUES (887,228311,37,'fated','inmate','youthfulness',''); 936INSERT INTO t2 VALUES (888,228312,37,'gradually','mineral','creak','FAS'); 937INSERT INTO t2 VALUES (889,228313,37,'widens','impending','Chippewa',''); 938INSERT INTO t2 VALUES (890,228314,37,'preclude','meditation','clamored',''); 939INSERT INTO t2 VALUES (891,228401,65,'Jobrel','ideas','freezes',''); 940INSERT INTO t2 VALUES (892,228402,65,'hooker','miniaturizes','forgivably','FAS'); 941INSERT INTO t2 VALUES (893,228403,65,'rainstorm','lewdly','reduce','FAS'); 942INSERT INTO t2 VALUES (894,228404,65,'disconnects','title','McGovern','W'); 943INSERT INTO t2 VALUES (895,228405,65,'cruelty','youthfulness','Nazis','W'); 944INSERT INTO t2 VALUES (896,228406,65,'exponentials','creak','epistle','W'); 945INSERT INTO t2 VALUES (897,228407,65,'affective','Chippewa','socializes','W'); 946INSERT INTO t2 VALUES (898,228408,65,'arteries','clamored','conceptions',''); 947INSERT INTO t2 VALUES (899,228409,65,'Crosby','freezes','Kevin',''); 948INSERT INTO t2 VALUES (900,228410,65,'acquaint','forgivably','uncovering',''); 949INSERT INTO t2 VALUES (901,230301,37,'evenhandedly','reduce','chews','FAS'); 950INSERT INTO t2 VALUES (902,230302,37,'percentage','McGovern','appendixes','FAS'); 951INSERT INTO t2 VALUES (903,230303,37,'disobedience','Nazis','raining',''); 952INSERT INTO t2 VALUES (904,018062,37,'humility','epistle','infest',''); 953INSERT INTO t2 VALUES (905,230501,37,'gleaning','socializes','compartment',''); 954INSERT INTO t2 VALUES (906,230502,37,'petted','conceptions','minting',''); 955INSERT INTO t2 VALUES (907,230503,37,'bloater','Kevin','ducks',''); 956INSERT INTO t2 VALUES (908,230504,37,'minion','uncovering','roped','A'); 957INSERT INTO t2 VALUES (909,230505,37,'marginal','chews','waltz',''); 958INSERT INTO t2 VALUES (910,230506,37,'apiary','appendixes','Lillian',''); 959INSERT INTO t2 VALUES (911,230507,37,'measures','raining','repressions','A'); 960INSERT INTO t2 VALUES (912,230508,37,'precaution','infest','chillingly',''); 961INSERT INTO t2 VALUES (913,230509,37,'repelled','compartment','noncritical',''); 962INSERT INTO t2 VALUES (914,230901,37,'primary','minting','lithograph',''); 963INSERT INTO t2 VALUES (915,230902,37,'coverings','ducks','spongers',''); 964INSERT INTO t2 VALUES (916,230903,37,'Artemia','roped','parenthood',''); 965INSERT INTO t2 VALUES (917,230904,37,'navigate','waltz','posed',''); 966INSERT INTO t2 VALUES (918,230905,37,'spatial','Lillian','instruments',''); 967INSERT INTO t2 VALUES (919,230906,37,'Gurkha','repressions','filial',''); 968INSERT INTO t2 VALUES (920,230907,37,'meanwhile','chillingly','fixedly',''); 969INSERT INTO t2 VALUES (921,230908,37,'Melinda','noncritical','relives',''); 970INSERT INTO t2 VALUES (922,230909,37,'Butterfield','lithograph','Pandora',''); 971INSERT INTO t2 VALUES (923,230910,37,'Aldrich','spongers','watering','A'); 972INSERT INTO t2 VALUES (924,230911,37,'previewing','parenthood','ungrateful',''); 973INSERT INTO t2 VALUES (925,230912,37,'glut','posed','secures',''); 974INSERT INTO t2 VALUES (926,230913,37,'unaffected','instruments','chastisers',''); 975INSERT INTO t2 VALUES (927,230914,37,'inmate','filial','icon',''); 976INSERT INTO t2 VALUES (928,231304,37,'mineral','fixedly','reuniting','A'); 977INSERT INTO t2 VALUES (929,231305,37,'impending','relives','imagining','A'); 978INSERT INTO t2 VALUES (930,231306,37,'meditation','Pandora','abiding','A'); 979INSERT INTO t2 VALUES (931,231307,37,'ideas','watering','omnisciently',''); 980INSERT INTO t2 VALUES (932,231308,37,'miniaturizes','ungrateful','Britannic',''); 981INSERT INTO t2 VALUES (933,231309,37,'lewdly','secures','scholastics','A'); 982INSERT INTO t2 VALUES (934,231310,37,'title','chastisers','mechanics','A'); 983INSERT INTO t2 VALUES (935,231311,37,'youthfulness','icon','humidly','A'); 984INSERT INTO t2 VALUES (936,231312,37,'creak','reuniting','masterpiece',''); 985INSERT INTO t2 VALUES (937,231313,37,'Chippewa','imagining','however',''); 986INSERT INTO t2 VALUES (938,231314,37,'clamored','abiding','Mendelian',''); 987INSERT INTO t2 VALUES (939,231315,37,'freezes','omnisciently','jarred',''); 988INSERT INTO t2 VALUES (940,232102,37,'forgivably','Britannic','scolds',''); 989INSERT INTO t2 VALUES (941,232103,37,'reduce','scholastics','infatuate',''); 990INSERT INTO t2 VALUES (942,232104,37,'McGovern','mechanics','willed','A'); 991INSERT INTO t2 VALUES (943,232105,37,'Nazis','humidly','joyfully',''); 992INSERT INTO t2 VALUES (944,232106,37,'epistle','masterpiece','Microsoft',''); 993INSERT INTO t2 VALUES (945,232107,37,'socializes','however','fibrosities',''); 994INSERT INTO t2 VALUES (946,232108,37,'conceptions','Mendelian','Baltimorean',''); 995INSERT INTO t2 VALUES (947,232601,37,'Kevin','jarred','equestrian',''); 996INSERT INTO t2 VALUES (948,232602,37,'uncovering','scolds','Goodrich',''); 997INSERT INTO t2 VALUES (949,232603,37,'chews','infatuate','apish','A'); 998INSERT INTO t2 VALUES (950,232605,37,'appendixes','willed','Adlerian',''); 999INSERT INTO t2 VALUES (5950,1232605,37,'appendixes','willed','Adlerian',''); 1000INSERT INTO t2 VALUES (5951,1232606,37,'appendixes','willed','Adlerian',''); 1001INSERT INTO t2 VALUES (5952,1232607,37,'appendixes','willed','Adlerian',''); 1002INSERT INTO t2 VALUES (5953,1232608,37,'appendixes','willed','Adlerian',''); 1003INSERT INTO t2 VALUES (5954,1232609,37,'appendixes','willed','Adlerian',''); 1004INSERT INTO t2 VALUES (951,232606,37,'raining','joyfully','Tropez',''); 1005INSERT INTO t2 VALUES (952,232607,37,'infest','Microsoft','nouns',''); 1006INSERT INTO t2 VALUES (953,232608,37,'compartment','fibrosities','distracting',''); 1007INSERT INTO t2 VALUES (954,232609,37,'minting','Baltimorean','mutton',''); 1008INSERT INTO t2 VALUES (955,236104,37,'ducks','equestrian','bridgeable','A'); 1009INSERT INTO t2 VALUES (956,236105,37,'roped','Goodrich','stickers','A'); 1010INSERT INTO t2 VALUES (957,236106,37,'waltz','apish','transcontinental','A'); 1011INSERT INTO t2 VALUES (958,236107,37,'Lillian','Adlerian','amateurish',''); 1012INSERT INTO t2 VALUES (959,236108,37,'repressions','Tropez','Gandhian',''); 1013INSERT INTO t2 VALUES (960,236109,37,'chillingly','nouns','stratified',''); 1014INSERT INTO t2 VALUES (961,236110,37,'noncritical','distracting','chamberlains',''); 1015INSERT INTO t2 VALUES (962,236111,37,'lithograph','mutton','creditably',''); 1016INSERT INTO t2 VALUES (963,236112,37,'spongers','bridgeable','philosophic',''); 1017INSERT INTO t2 VALUES (964,236113,37,'parenthood','stickers','ores',''); 1018INSERT INTO t2 VALUES (965,238005,37,'posed','transcontinental','Carleton',''); 1019INSERT INTO t2 VALUES (966,238006,37,'instruments','amateurish','tape','A'); 1020INSERT INTO t2 VALUES (967,238007,37,'filial','Gandhian','afloat','A'); 1021INSERT INTO t2 VALUES (968,238008,37,'fixedly','stratified','goodness','A'); 1022INSERT INTO t2 VALUES (969,238009,37,'relives','chamberlains','welcoming',''); 1023INSERT INTO t2 VALUES (970,238010,37,'Pandora','creditably','Pinsky','FAS'); 1024INSERT INTO t2 VALUES (971,238011,37,'watering','philosophic','halting',''); 1025INSERT INTO t2 VALUES (972,238012,37,'ungrateful','ores','bibliography',''); 1026INSERT INTO t2 VALUES (973,238013,37,'secures','Carleton','decoding',''); 1027INSERT INTO t2 VALUES (974,240401,41,'chastisers','tape','variance','A'); 1028INSERT INTO t2 VALUES (975,240402,41,'icon','afloat','allowed','A'); 1029INSERT INTO t2 VALUES (976,240901,41,'reuniting','goodness','dire','A'); 1030INSERT INTO t2 VALUES (977,240902,41,'imagining','welcoming','dub','A'); 1031INSERT INTO t2 VALUES (978,241801,41,'abiding','Pinsky','poisoning',''); 1032INSERT INTO t2 VALUES (979,242101,41,'omnisciently','halting','Iraqis','A'); 1033INSERT INTO t2 VALUES (980,242102,41,'Britannic','bibliography','heaving',''); 1034INSERT INTO t2 VALUES (981,242201,41,'scholastics','decoding','population','A'); 1035INSERT INTO t2 VALUES (982,242202,41,'mechanics','variance','bomb','A'); 1036INSERT INTO t2 VALUES (983,242501,41,'humidly','allowed','Majorca','A'); 1037INSERT INTO t2 VALUES (984,242502,41,'masterpiece','dire','Gershwins',''); 1038INSERT INTO t2 VALUES (985,246201,41,'however','dub','explorers',''); 1039INSERT INTO t2 VALUES (986,246202,41,'Mendelian','poisoning','libretto','A'); 1040INSERT INTO t2 VALUES (987,246203,41,'jarred','Iraqis','occurred',''); 1041INSERT INTO t2 VALUES (988,246204,41,'scolds','heaving','Lagos',''); 1042INSERT INTO t2 VALUES (989,246205,41,'infatuate','population','rats',''); 1043INSERT INTO t2 VALUES (990,246301,41,'willed','bomb','bankruptcies','A'); 1044INSERT INTO t2 VALUES (991,246302,41,'joyfully','Majorca','crying',''); 1045INSERT INTO t2 VALUES (992,248001,41,'Microsoft','Gershwins','unexpected',''); 1046INSERT INTO t2 VALUES (993,248002,41,'fibrosities','explorers','accessed','A'); 1047INSERT INTO t2 VALUES (994,248003,41,'Baltimorean','libretto','colorful','A'); 1048INSERT INTO t2 VALUES (995,248004,41,'equestrian','occurred','versatility','A'); 1049INSERT INTO t2 VALUES (996,248005,41,'Goodrich','Lagos','cosy',''); 1050INSERT INTO t2 VALUES (997,248006,41,'apish','rats','Darius','A'); 1051INSERT INTO t2 VALUES (998,248007,41,'Adlerian','bankruptcies','mastering','A'); 1052INSERT INTO t2 VALUES (999,248008,41,'Tropez','crying','Asiaticizations','A'); 1053INSERT INTO t2 VALUES (1000,248009,41,'nouns','unexpected','offerers','A'); 1054INSERT INTO t2 VALUES (1001,248010,41,'distracting','accessed','uncles','A'); 1055INSERT INTO t2 VALUES (1002,248011,41,'mutton','colorful','sleepwalk',''); 1056INSERT INTO t2 VALUES (1003,248012,41,'bridgeable','versatility','Ernestine',''); 1057INSERT INTO t2 VALUES (1004,248013,41,'stickers','cosy','checksumming',''); 1058INSERT INTO t2 VALUES (1005,248014,41,'transcontinental','Darius','stopped',''); 1059INSERT INTO t2 VALUES (1006,248015,41,'amateurish','mastering','sicker',''); 1060INSERT INTO t2 VALUES (1007,248016,41,'Gandhian','Asiaticizations','Italianization',''); 1061INSERT INTO t2 VALUES (1008,248017,41,'stratified','offerers','alphabetic',''); 1062INSERT INTO t2 VALUES (1009,248018,41,'chamberlains','uncles','pharmaceutic',''); 1063INSERT INTO t2 VALUES (1010,248019,41,'creditably','sleepwalk','creator',''); 1064INSERT INTO t2 VALUES (1011,248020,41,'philosophic','Ernestine','chess',''); 1065INSERT INTO t2 VALUES (1012,248021,41,'ores','checksumming','charcoal',''); 1066INSERT INTO t2 VALUES (1013,248101,41,'Carleton','stopped','Epiphany','A'); 1067INSERT INTO t2 VALUES (1014,248102,41,'tape','sicker','bulldozes','A'); 1068INSERT INTO t2 VALUES (1015,248201,41,'afloat','Italianization','Pygmalion','A'); 1069INSERT INTO t2 VALUES (1016,248202,41,'goodness','alphabetic','caressing','A'); 1070INSERT INTO t2 VALUES (1017,248203,41,'welcoming','pharmaceutic','Palestine','A'); 1071INSERT INTO t2 VALUES (1018,248204,41,'Pinsky','creator','regimented','A'); 1072INSERT INTO t2 VALUES (1019,248205,41,'halting','chess','scars','A'); 1073INSERT INTO t2 VALUES (1020,248206,41,'bibliography','charcoal','realest','A'); 1074INSERT INTO t2 VALUES (1021,248207,41,'decoding','Epiphany','diffusing','A'); 1075INSERT INTO t2 VALUES (1022,248208,41,'variance','bulldozes','clubroom','A'); 1076INSERT INTO t2 VALUES (1023,248209,41,'allowed','Pygmalion','Blythe','A'); 1077INSERT INTO t2 VALUES (1024,248210,41,'dire','caressing','ahead',''); 1078INSERT INTO t2 VALUES (1025,248211,50,'dub','Palestine','reviver',''); 1079INSERT INTO t2 VALUES (1026,250501,34,'poisoning','regimented','retransmitting','A'); 1080INSERT INTO t2 VALUES (1027,250502,34,'Iraqis','scars','landslide',''); 1081INSERT INTO t2 VALUES (1028,250503,34,'heaving','realest','Eiffel',''); 1082INSERT INTO t2 VALUES (1029,250504,34,'population','diffusing','absentee',''); 1083INSERT INTO t2 VALUES (1030,250505,34,'bomb','clubroom','aye',''); 1084INSERT INTO t2 VALUES (1031,250601,34,'Majorca','Blythe','forked','A'); 1085INSERT INTO t2 VALUES (1032,250602,34,'Gershwins','ahead','Peruvianizes',''); 1086INSERT INTO t2 VALUES (1033,250603,34,'explorers','reviver','clerked',''); 1087INSERT INTO t2 VALUES (1034,250604,34,'libretto','retransmitting','tutor',''); 1088INSERT INTO t2 VALUES (1035,250605,34,'occurred','landslide','boulevard',''); 1089INSERT INTO t2 VALUES (1036,251001,34,'Lagos','Eiffel','shuttered',''); 1090INSERT INTO t2 VALUES (1037,251002,34,'rats','absentee','quotes','A'); 1091INSERT INTO t2 VALUES (1038,251003,34,'bankruptcies','aye','Caltech',''); 1092INSERT INTO t2 VALUES (1039,251004,34,'crying','forked','Mossberg',''); 1093INSERT INTO t2 VALUES (1040,251005,34,'unexpected','Peruvianizes','kept',''); 1094INSERT INTO t2 VALUES (1041,251301,34,'accessed','clerked','roundly',''); 1095INSERT INTO t2 VALUES (1042,251302,34,'colorful','tutor','features','A'); 1096INSERT INTO t2 VALUES (1043,251303,34,'versatility','boulevard','imaginable','A'); 1097INSERT INTO t2 VALUES (1044,251304,34,'cosy','shuttered','controller',''); 1098INSERT INTO t2 VALUES (1045,251305,34,'Darius','quotes','racial',''); 1099INSERT INTO t2 VALUES (1046,251401,34,'mastering','Caltech','uprisings','A'); 1100INSERT INTO t2 VALUES (1047,251402,34,'Asiaticizations','Mossberg','narrowed','A'); 1101INSERT INTO t2 VALUES (1048,251403,34,'offerers','kept','cannot','A'); 1102INSERT INTO t2 VALUES (1049,251404,34,'uncles','roundly','vest',''); 1103INSERT INTO t2 VALUES (1050,251405,34,'sleepwalk','features','famine',''); 1104INSERT INTO t2 VALUES (1051,251406,34,'Ernestine','imaginable','sugars',''); 1105INSERT INTO t2 VALUES (1052,251801,34,'checksumming','controller','exterminated','A'); 1106INSERT INTO t2 VALUES (1053,251802,34,'stopped','racial','belays',''); 1107INSERT INTO t2 VALUES (1054,252101,34,'sicker','uprisings','Hodges','A'); 1108INSERT INTO t2 VALUES (1055,252102,34,'Italianization','narrowed','translatable',''); 1109INSERT INTO t2 VALUES (1056,252301,34,'alphabetic','cannot','duality','A'); 1110INSERT INTO t2 VALUES (1057,252302,34,'pharmaceutic','vest','recording','A'); 1111INSERT INTO t2 VALUES (1058,252303,34,'creator','famine','rouses','A'); 1112INSERT INTO t2 VALUES (1059,252304,34,'chess','sugars','poison',''); 1113INSERT INTO t2 VALUES (1060,252305,34,'charcoal','exterminated','attitude',''); 1114INSERT INTO t2 VALUES (1061,252306,34,'Epiphany','belays','dusted',''); 1115INSERT INTO t2 VALUES (1062,252307,34,'bulldozes','Hodges','encompasses',''); 1116INSERT INTO t2 VALUES (1063,252308,34,'Pygmalion','translatable','presentation',''); 1117INSERT INTO t2 VALUES (1064,252309,34,'caressing','duality','Kantian',''); 1118INSERT INTO t2 VALUES (1065,256001,34,'Palestine','recording','imprecision','A'); 1119INSERT INTO t2 VALUES (1066,256002,34,'regimented','rouses','saving',''); 1120INSERT INTO t2 VALUES (1067,256003,34,'scars','poison','maternal',''); 1121INSERT INTO t2 VALUES (1068,256004,34,'realest','attitude','hewed',''); 1122INSERT INTO t2 VALUES (1069,256005,34,'diffusing','dusted','kerosene',''); 1123INSERT INTO t2 VALUES (1070,258001,34,'clubroom','encompasses','Cubans',''); 1124INSERT INTO t2 VALUES (1071,258002,34,'Blythe','presentation','photographers',''); 1125INSERT INTO t2 VALUES (1072,258003,34,'ahead','Kantian','nymph','A'); 1126INSERT INTO t2 VALUES (1073,258004,34,'reviver','imprecision','bedlam','A'); 1127INSERT INTO t2 VALUES (1074,258005,34,'retransmitting','saving','north','A'); 1128INSERT INTO t2 VALUES (1075,258006,34,'landslide','maternal','Schoenberg','A'); 1129INSERT INTO t2 VALUES (1076,258007,34,'Eiffel','hewed','botany','A'); 1130INSERT INTO t2 VALUES (1077,258008,34,'absentee','kerosene','curs',''); 1131INSERT INTO t2 VALUES (1078,258009,34,'aye','Cubans','solidification',''); 1132INSERT INTO t2 VALUES (1079,258010,34,'forked','photographers','inheritresses',''); 1133INSERT INTO t2 VALUES (1080,258011,34,'Peruvianizes','nymph','stiller',''); 1134INSERT INTO t2 VALUES (1081,258101,68,'clerked','bedlam','t1','A'); 1135INSERT INTO t2 VALUES (1082,258102,68,'tutor','north','suite','A'); 1136INSERT INTO t2 VALUES (1083,258103,34,'boulevard','Schoenberg','ransomer',''); 1137INSERT INTO t2 VALUES (1084,258104,68,'shuttered','botany','Willy',''); 1138INSERT INTO t2 VALUES (1085,258105,68,'quotes','curs','Rena','A'); 1139INSERT INTO t2 VALUES (1086,258106,68,'Caltech','solidification','Seattle','A'); 1140INSERT INTO t2 VALUES (1087,258107,68,'Mossberg','inheritresses','relaxes','A'); 1141INSERT INTO t2 VALUES (1088,258108,68,'kept','stiller','exclaim',''); 1142INSERT INTO t2 VALUES (1089,258109,68,'roundly','t1','implicated','A'); 1143INSERT INTO t2 VALUES (1090,258110,68,'features','suite','distinguish',''); 1144INSERT INTO t2 VALUES (1091,258111,68,'imaginable','ransomer','assayed',''); 1145INSERT INTO t2 VALUES (1092,258112,68,'controller','Willy','homeowner',''); 1146INSERT INTO t2 VALUES (1093,258113,68,'racial','Rena','and',''); 1147INSERT INTO t2 VALUES (1094,258201,34,'uprisings','Seattle','stealth',''); 1148INSERT INTO t2 VALUES (1095,258202,34,'narrowed','relaxes','coinciding','A'); 1149INSERT INTO t2 VALUES (1096,258203,34,'cannot','exclaim','founder','A'); 1150INSERT INTO t2 VALUES (1097,258204,34,'vest','implicated','environing',''); 1151INSERT INTO t2 VALUES (1098,258205,34,'famine','distinguish','jewelry',''); 1152INSERT INTO t2 VALUES (1099,258301,34,'sugars','assayed','lemons','A'); 1153INSERT INTO t2 VALUES (1100,258401,34,'exterminated','homeowner','brokenness','A'); 1154INSERT INTO t2 VALUES (1101,258402,34,'belays','and','bedpost','A'); 1155INSERT INTO t2 VALUES (1102,258403,34,'Hodges','stealth','assurers','A'); 1156INSERT INTO t2 VALUES (1103,258404,34,'translatable','coinciding','annoyers',''); 1157INSERT INTO t2 VALUES (1104,258405,34,'duality','founder','affixed',''); 1158INSERT INTO t2 VALUES (1105,258406,34,'recording','environing','warbling',''); 1159INSERT INTO t2 VALUES (1106,258407,34,'rouses','jewelry','seriously',''); 1160INSERT INTO t2 VALUES (1107,228123,37,'poison','lemons','boasted',''); 1161INSERT INTO t2 VALUES (1108,250606,34,'attitude','brokenness','Chantilly',''); 1162INSERT INTO t2 VALUES (1109,208405,37,'dusted','bedpost','Iranizes',''); 1163INSERT INTO t2 VALUES (1110,212101,37,'encompasses','assurers','violinist',''); 1164INSERT INTO t2 VALUES (1111,218206,37,'presentation','annoyers','extramarital',''); 1165INSERT INTO t2 VALUES (1112,150401,37,'Kantian','affixed','spates',''); 1166INSERT INTO t2 VALUES (1113,248212,41,'imprecision','warbling','cloakroom',''); 1167INSERT INTO t2 VALUES (1114,128026,00,'saving','seriously','gazer',''); 1168INSERT INTO t2 VALUES (1115,128024,00,'maternal','boasted','hand',''); 1169INSERT INTO t2 VALUES (1116,128027,00,'hewed','Chantilly','tucked',''); 1170INSERT INTO t2 VALUES (1117,128025,00,'kerosene','Iranizes','gems',''); 1171INSERT INTO t2 VALUES (1118,128109,00,'Cubans','violinist','clinker',''); 1172INSERT INTO t2 VALUES (1119,128705,00,'photographers','extramarital','refiner',''); 1173INSERT INTO t2 VALUES (1120,126303,00,'nymph','spates','callus',''); 1174INSERT INTO t2 VALUES (1121,128308,00,'bedlam','cloakroom','leopards',''); 1175INSERT INTO t2 VALUES (1122,128204,00,'north','gazer','comfortingly',''); 1176INSERT INTO t2 VALUES (1123,128205,00,'Schoenberg','hand','generically',''); 1177INSERT INTO t2 VALUES (1124,128206,00,'botany','tucked','getters',''); 1178INSERT INTO t2 VALUES (1125,128207,00,'curs','gems','sexually',''); 1179INSERT INTO t2 VALUES (1126,118205,00,'solidification','clinker','spear',''); 1180INSERT INTO t2 VALUES (1127,116801,00,'inheritresses','refiner','serums',''); 1181INSERT INTO t2 VALUES (1128,116803,00,'stiller','callus','Italianization',''); 1182INSERT INTO t2 VALUES (1129,116804,00,'t1','leopards','attendants',''); 1183INSERT INTO t2 VALUES (1130,116802,00,'suite','comfortingly','spies',''); 1184INSERT INTO t2 VALUES (1131,128605,00,'ransomer','generically','Anthony',''); 1185INSERT INTO t2 VALUES (1132,118308,00,'Willy','getters','planar',''); 1186INSERT INTO t2 VALUES (1133,113702,00,'Rena','sexually','cupped',''); 1187INSERT INTO t2 VALUES (1134,113703,00,'Seattle','spear','cleanser',''); 1188INSERT INTO t2 VALUES (1135,112103,00,'relaxes','serums','commuters',''); 1189INSERT INTO t2 VALUES (1136,118009,00,'exclaim','Italianization','honeysuckle',''); 1190INSERT INTO t2 VALUES (5136,1118009,00,'exclaim','Italianization','honeysuckle',''); 1191INSERT INTO t2 VALUES (1137,138011,00,'implicated','attendants','orphanage',''); 1192INSERT INTO t2 VALUES (1138,138010,00,'distinguish','spies','skies',''); 1193INSERT INTO t2 VALUES (1139,138012,00,'assayed','Anthony','crushers',''); 1194INSERT INTO t2 VALUES (1140,068304,00,'homeowner','planar','Puritan',''); 1195INSERT INTO t2 VALUES (1141,078009,00,'and','cupped','squeezer',''); 1196INSERT INTO t2 VALUES (1142,108013,00,'stealth','cleanser','bruises',''); 1197INSERT INTO t2 VALUES (1143,084004,00,'coinciding','commuters','bonfire',''); 1198INSERT INTO t2 VALUES (1144,083402,00,'founder','honeysuckle','Colombo',''); 1199INSERT INTO t2 VALUES (1145,084003,00,'environing','orphanage','nondecreasing',''); 1200INSERT INTO t2 VALUES (1146,088504,00,'jewelry','skies','innocents',''); 1201INSERT INTO t2 VALUES (1147,088005,00,'lemons','crushers','masked',''); 1202INSERT INTO t2 VALUES (1148,088007,00,'brokenness','Puritan','file',''); 1203INSERT INTO t2 VALUES (1149,088006,00,'bedpost','squeezer','brush',''); 1204INSERT INTO t2 VALUES (1150,148025,00,'assurers','bruises','mutilate',''); 1205INSERT INTO t2 VALUES (1151,148024,00,'annoyers','bonfire','mommy',''); 1206INSERT INTO t2 VALUES (1152,138305,00,'affixed','Colombo','bulkheads',''); 1207INSERT INTO t2 VALUES (1153,138306,00,'warbling','nondecreasing','undeclared',''); 1208INSERT INTO t2 VALUES (1154,152701,00,'seriously','innocents','displacements',''); 1209INSERT INTO t2 VALUES (1155,148505,00,'boasted','masked','nieces',''); 1210INSERT INTO t2 VALUES (1156,158003,00,'Chantilly','file','coeducation',''); 1211INSERT INTO t2 VALUES (1157,156201,00,'Iranizes','brush','brassy',''); 1212INSERT INTO t2 VALUES (1158,156202,00,'violinist','mutilate','authenticator',''); 1213INSERT INTO t2 VALUES (1159,158307,00,'extramarital','mommy','Washoe',''); 1214INSERT INTO t2 VALUES (1160,158402,00,'spates','bulkheads','penny',''); 1215INSERT INTO t2 VALUES (1161,158401,00,'cloakroom','undeclared','Flagler',''); 1216INSERT INTO t2 VALUES (1162,068013,00,'gazer','displacements','stoned',''); 1217INSERT INTO t2 VALUES (1163,068012,00,'hand','nieces','cranes',''); 1218INSERT INTO t2 VALUES (1164,068203,00,'tucked','coeducation','masterful',''); 1219INSERT INTO t2 VALUES (1165,088205,00,'gems','brassy','biracial',''); 1220INSERT INTO t2 VALUES (1166,068704,00,'clinker','authenticator','steamships',''); 1221INSERT INTO t2 VALUES (1167,068604,00,'refiner','Washoe','windmills',''); 1222INSERT INTO t2 VALUES (1168,158502,00,'callus','penny','exploit',''); 1223INSERT INTO t2 VALUES (1169,123103,00,'leopards','Flagler','riverfront',''); 1224INSERT INTO t2 VALUES (1170,148026,00,'comfortingly','stoned','sisterly',''); 1225INSERT INTO t2 VALUES (1171,123302,00,'generically','cranes','sharpshoot',''); 1226INSERT INTO t2 VALUES (1172,076503,00,'getters','masterful','mittens',''); 1227INSERT INTO t2 VALUES (1173,126304,00,'sexually','biracial','interdependency',''); 1228INSERT INTO t2 VALUES (1174,068306,00,'spear','steamships','policy',''); 1229INSERT INTO t2 VALUES (1175,143504,00,'serums','windmills','unleashing',''); 1230INSERT INTO t2 VALUES (1176,160201,00,'Italianization','exploit','pretenders',''); 1231INSERT INTO t2 VALUES (1177,148028,00,'attendants','riverfront','overstatements',''); 1232INSERT INTO t2 VALUES (1178,148027,00,'spies','sisterly','birthed',''); 1233INSERT INTO t2 VALUES (1179,143505,00,'Anthony','sharpshoot','opportunism',''); 1234INSERT INTO t2 VALUES (1180,108014,00,'planar','mittens','showroom',''); 1235INSERT INTO t2 VALUES (1181,076104,00,'cupped','interdependency','compromisingly',''); 1236INSERT INTO t2 VALUES (1182,078106,00,'cleanser','policy','Medicare',''); 1237INSERT INTO t2 VALUES (1183,126102,00,'commuters','unleashing','corresponds',''); 1238INSERT INTO t2 VALUES (1184,128029,00,'honeysuckle','pretenders','hardware',''); 1239INSERT INTO t2 VALUES (1185,128028,00,'orphanage','overstatements','implant',''); 1240INSERT INTO t2 VALUES (1186,018410,00,'skies','birthed','Alicia',''); 1241INSERT INTO t2 VALUES (1187,128110,00,'crushers','opportunism','requesting',''); 1242INSERT INTO t2 VALUES (1188,148506,00,'Puritan','showroom','produced',''); 1243INSERT INTO t2 VALUES (1189,123303,00,'squeezer','compromisingly','criticizes',''); 1244INSERT INTO t2 VALUES (1190,123304,00,'bruises','Medicare','backer',''); 1245INSERT INTO t2 VALUES (1191,068504,00,'bonfire','corresponds','positively',''); 1246INSERT INTO t2 VALUES (1192,068305,00,'Colombo','hardware','colicky',''); 1247INSERT INTO t2 VALUES (1193,000000,00,'nondecreasing','implant','thrillingly',''); 1248--enable_query_log 1249 1250# 1251# Search with a key 1252# 1253 1254select t2.fld3 from t2 where companynr = 58 and fld3 like "%imaginable%"; 1255select fld3 from t2 where fld3 like "%cultivation" ; 1256 1257# 1258# Search with a key using sorting and limit the same time 1259# 1260 1261select t2.fld3,companynr from t2 where companynr = 57+1 order by fld3; 1262select fld3,companynr from t2 where companynr = 58 order by fld3; 1263 1264select fld3 from t2 order by fld3 desc limit 10; 1265select fld3 from t2 order by fld3 desc limit 5; 1266select fld3 from t2 order by fld3 desc limit 5,5; 1267 1268# 1269# Search with a key having a constant with each unique key. 1270# The table is read directly with read-next on fld3 1271# 1272 1273select t2.fld3 from t2 where fld3 = 'honeysuckle'; 1274select t2.fld3 from t2 where fld3 LIKE 'honeysuckl_'; 1275select t2.fld3 from t2 where fld3 LIKE 'hon_ysuckl_'; 1276select t2.fld3 from t2 where fld3 LIKE 'honeysuckle%'; 1277select t2.fld3 from t2 where fld3 LIKE 'h%le'; 1278 1279select t2.fld3 from t2 where fld3 LIKE 'honeysuckle_'; 1280select t2.fld3 from t2 where fld3 LIKE 'don_t_find_me_please%'; 1281 1282# 1283# Test using INDEX and IGNORE INDEX 1284# 1285 1286explain select t2.fld3 from t2 where fld3 = 'honeysuckle'; 1287 1288explain select fld3 from t2 ignore index (fld3) where fld3 = 'honeysuckle'; 1289explain select fld3 from t2 use index (fld1) where fld3 = 'honeysuckle'; 1290 1291explain select fld3 from t2 use index (fld3) where fld3 = 'honeysuckle'; 1292explain select fld3 from t2 use index (fld1,fld3) where fld3 = 'honeysuckle'; 1293 1294# 1295# NOTE NOTE NOTE 1296# The next should give an error 1297# 1298 1299-- error 1176 1300explain select fld3 from t2 ignore index (fld3,not_used); 1301-- error 1176 1302explain select fld3 from t2 use index (not_used); 1303 1304# 1305# Test sorting with a used key (there is no need for sorting) 1306# 1307 1308select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3; 1309explain select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3; 1310select fld1,fld3 from t2 where fld3="Colombo" or fld3 = "nondecreasing" order by fld3; 1311 1312# 1313# Search with a key having a constant with many occurrences 1314# The table is read directly with read-next having fld3 to get the 1315# occurrences 1316# 1317 1318select fld1,fld3 from t2 where companynr = 37 and fld3 = 'appendixes'; 1319 1320# 1321# Search with bunched 'or's. 1322# If one can limit the key to a certain interval only the possible 1323# alternatives will be gone through 1324# 1325 1326select fld1 from t2 where fld1=250501 or fld1="250502"; 1327explain select fld1 from t2 where fld1=250501 or fld1="250502"; 1328select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502; 1329explain select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502; 1330 1331# 1332# Search with a key with LIKE constant 1333# If the like starts with a certain letter key will be used. 1334# 1335 1336--sorted_result 1337select fld1,fld3 from t2 where companynr = 37 and fld3 like 'f%'; 1338select fld3 from t2 where fld3 like "L%" and fld3 = "ok"; 1339select fld3 from t2 where (fld3 like "C%" and fld3 = "Chantilly"); 1340select fld1,fld3 from t2 where fld1 like "25050%"; 1341select fld1,fld3 from t2 where fld1 like "25050_"; 1342 1343# 1344# Search using distinct. An automatic grouping will be done over all the fields, 1345# if only distinct is used. In any other case a temporary table will always 1346# be created. If only the field used for sorting is from the main register, 1347# it will be sorted first before the distinct table is created. 1348# 1349 1350select distinct companynr from t2; 1351select distinct companynr from t2 order by companynr; 1352select distinct companynr from t2 order by companynr desc; 1353select distinct t2.fld3,period from t2,t1 where companynr=37 and fld3 like "O%"; 1354 1355select distinct fld3 from t2 where companynr = 34 order by fld3; 1356select distinct fld3 from t2 limit 10; 1357select distinct fld3 from t2 having fld3 like "A%" limit 10; 1358select distinct substring(fld3,1,3) from t2 where fld3 like "A%"; 1359select distinct substring(fld3,1,3) as a from t2 having a like "A%" order by a limit 10; 1360select distinct substring(fld3,1,3) from t2 where fld3 like "A%" limit 10; 1361select distinct substring(fld3,1,3) as a from t2 having a like "A%" limit 10; 1362 1363# make a big table. 1364 1365create table t3 ( 1366 period int not null, 1367 name char(32) not null, 1368 companynr int not null, 1369 price double(11,0), 1370 price2 double(11,0), 1371 key (period), 1372 key (name) 1373); 1374 1375--disable_query_log 1376INSERT INTO t3 (period,name,companynr,price,price2) VALUES (1001,"Iranizes",37,5987435,234724); 1377INSERT INTO t3 (period,name,companynr,price,price2) VALUES (1002,"violinist",37,28357832,8723648); 1378INSERT INTO t3 (period,name,companynr,price,price2) VALUES (1003,"extramarital",37,39654943,235872); 1379INSERT INTO t3 (period,name,companynr,price,price2) VALUES (1004,"spates",78,726498,72987523); 1380INSERT INTO t3 (period,name,companynr,price,price2) VALUES (1005,"cloakroom",78,98439034,823742); 1381INSERT INTO t3 (period,name,companynr,price,price2) VALUES (1006,"gazer",101,834598,27348324); 1382INSERT INTO t3 (period,name,companynr,price,price2) VALUES (1007,"hand",154,983543950,29837423); 1383INSERT INTO t3 (period,name,companynr,price,price2) VALUES (1008,"tucked",311,234298,3275892); 1384INSERT INTO t3 (period,name,companynr,price,price2) VALUES (1009,"gems",447,2374834,9872392); 1385INSERT INTO t3 (period,name,companynr,price,price2) VALUES (1010,"clinker",512,786542,76234234); 1386--enable_query_log 1387 1388create temporary table tmp engine = myisam select * from t3; 1389 1390insert into t3 select * from tmp; 1391insert into tmp select * from t3; 1392insert into t3 select * from tmp; 1393insert into tmp select * from t3; 1394insert into t3 select * from tmp; 1395insert into tmp select * from t3; 1396insert into t3 select * from tmp; 1397insert into tmp select * from t3; 1398insert into t3 select * from tmp; 1399insert into tmp select * from t3; 1400insert into t3 select * from tmp; 1401insert into tmp select * from t3; 1402insert into t3 select * from tmp; 1403insert into tmp select * from t3; 1404insert into t3 select * from tmp; 1405insert into tmp select * from t3; 1406insert into t3 select * from tmp; 1407#insert into tmp select * from t3; 1408#insert into t3 select * from tmp; 1409 1410alter table t3 add t2nr int not null auto_increment primary key first; 1411 1412drop table tmp; 1413 1414# big table done 1415 1416SET BIG_TABLES=1; 1417select distinct concat(fld3," ",fld3) as namn from t2,t3 where t2.fld1=t3.t2nr order by namn limit 10; 1418SET BIG_TABLES=0; 1419select distinct concat(fld3," ",fld3) from t2,t3 where t2.fld1=t3.t2nr order by fld3 limit 10; 1420select distinct fld5 from t2 limit 10; 1421 1422# 1423# Force use of remove_dupp 1424# 1425 1426select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10; 1427SET BIG_TABLES=1; # Force use of MyISAM 1428select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10; 1429SET BIG_TABLES=0; 1430select distinct fld3,repeat("a",length(fld3)),count(*) from t2 group by companynr,fld3 limit 100,10; 1431 1432# 1433# A big order by that should trigger a merge in filesort 1434# 1435 1436select distinct companynr,rtrim(space(512+companynr)) from t3 order by 1,2; 1437 1438# 1439# Search with distinct and order by with many table. 1440# 1441 1442select distinct fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by fld3; 1443 1444# 1445# Here the last fld3 is optimized away from the order by 1446# 1447 1448explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by t3.t2nr,fld3; 1449 1450# 1451# Some test with ORDER BY and limit 1452# 1453 1454explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period; 1455explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10; 1456explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10; 1457 1458# 1459# Search with a constant table. 1460# 1461 1462select period from t1; 1463select period from t1 where period=1900; 1464select fld3,period from t1,t2 where fld1 = 011401 order by period; 1465 1466# 1467# Search with a constant table and several keyparts. (Rows are read only once 1468# in the beginning of the search) 1469# 1470 1471select fld3,period from t2,t3 where t2.fld1 = 011401 and t2.fld1=t3.t2nr and t3.period=1001; 1472 1473explain select fld3,period from t2,t3 where t2.fld1 = 011401 and t3.t2nr=t2.fld1 and 1001 = t3.period; 1474 1475# 1476# Search with a constant table and several rows from another table 1477# 1478 1479select fld3,period from t2,t1 where companynr*10 = 37*10; 1480 1481# 1482# Search with a table reference and without a key. 1483# t3 will be the main table. 1484# 1485 1486select 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; 1487 1488# 1489# Search with an interval on a table with full key on reference table. 1490# Here t2 will be the main table and only records matching the 1491# t2nr will be checked. 1492# 1493 1494select 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; 1495 1496# 1497# We need another table for join stuff.. 1498# 1499 1500create table t4 ( 1501 companynr tinyint(2) unsigned zerofill NOT NULL default '00', 1502 companyname char(30) NOT NULL default '', 1503 PRIMARY KEY (companynr), 1504 UNIQUE KEY companyname(companyname) 1505) ENGINE=MyISAM MAX_ROWS=50 PACK_KEYS=1 COMMENT='companynames'; 1506 1507--disable_query_log 1508INSERT INTO t4 (companynr, companyname) VALUES (29,'company 1'); 1509INSERT INTO t4 (companynr, companyname) VALUES (34,'company 2'); 1510INSERT INTO t4 (companynr, companyname) VALUES (36,'company 3'); 1511INSERT INTO t4 (companynr, companyname) VALUES (37,'company 4'); 1512INSERT INTO t4 (companynr, companyname) VALUES (40,'company 5'); 1513INSERT INTO t4 (companynr, companyname) VALUES (41,'company 6'); 1514INSERT INTO t4 (companynr, companyname) VALUES (53,'company 7'); 1515INSERT INTO t4 (companynr, companyname) VALUES (58,'company 8'); 1516INSERT INTO t4 (companynr, companyname) VALUES (65,'company 9'); 1517INSERT INTO t4 (companynr, companyname) VALUES (68,'company 10'); 1518INSERT INTO t4 (companynr, companyname) VALUES (50,'company 11'); 1519INSERT INTO t4 (companynr, companyname) VALUES (00,'Unknown'); 1520--enable_query_log 1521 1522# 1523# Test of stright join to force a full join. 1524# 1525 1526select STRAIGHT_JOIN t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr; 1527 1528select SQL_SMALL_RESULT t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr; 1529 1530# 1531# Full join (same alias) 1532# 1533 1534select * from t1,t1 t12; 1535select t2.fld1,t22.fld1 from t2,t2 t22 where t2.fld1 >= 250501 and t2.fld1 <= 250505 and t22.fld1 >= 250501 and t22.fld1 <= 250505; 1536 1537# 1538# Test of left join. 1539# 1540insert into t2 (fld1, companynr) values (999999,99); 1541 1542select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null; 1543select count(*) from t2 left join t4 using (companynr) where t4.companynr is not null; 1544explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null; 1545explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null; 1546 1547select companynr,companyname from t2 left join t4 using (companynr) where companynr is null; 1548select count(*) from t2 left join t4 using (companynr) where companynr is not null; 1549explain select companynr,companyname from t2 left join t4 using (companynr) where companynr is null; 1550explain select companynr,companyname from t4 left join t2 using (companynr) where companynr is null; 1551delete from t2 where fld1=999999; 1552 1553# 1554# Test left join optimization 1555 1556explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0; 1557explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0; 1558explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0; 1559 1560explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0; 1561explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0; 1562explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0; 1563# Following can't be optimized 1564explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null; 1565explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0 or t4.companynr > 0; 1566explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0; 1567 1568explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null; 1569explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0; 1570explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0; 1571 1572# 1573# Joins with forms. 1574# 1575 1576select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; 1577explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; 1578 1579# 1580# Search using 'or' with the same referens group. 1581# An interval search will be done first with the first table and after that 1582# the other table is referenced with a key with a 'test if key in use' for 1583# each record 1584# 1585 1586select 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; 1587 1588select 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; 1589 1590select 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; 1591 1592# 1593# Test of many parenthesis levels 1594# 1595 1596select 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); 1597select 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))); 1598 1599select 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; 1600 1601select fld1 from t2 where fld1 in (250502,98005,98006,250503,250605,250606) and fld1 >=250502 and fld1 not in (250605,250606); 1602 1603select fld1 from t2 where fld1 between 250502 and 250504; 1604 1605select fld3 from t2 where (((fld3 like "_%L%" ) or (fld3 like "%ok%")) and ( fld3 like "L%" or fld3 like "G%")) and fld3 like "L%" ; 1606 1607# 1608# Group on one table. 1609# optimizer: sort table by group and send rows. 1610# 1611 1612select count(*) from t1; 1613select companynr,count(*),sum(fld1) from t2 group by companynr; 1614select companynr,count(*) from t2 group by companynr order by companynr desc limit 5; 1615select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>""; 1616explain extended select count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 where companynr = 34 and fld4<>""; 1617select companynr,count(*),min(fld4),max(fld4),sum(fld1),avg(fld1),std(fld1),variance(fld1) from t2 group by companynr limit 3; 1618select companynr,t2nr,count(price),sum(price),min(price),max(price),avg(price) from t3 where companynr = 37 group by companynr,t2nr limit 10; 1619select /*! 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; 1620select companynr,count(price),sum(price),min(price),max(price),avg(price) from t3 group by companynr ; 1621select distinct mod(companynr,10) from t4 group by companynr; 1622select distinct 1 from t4 group by companynr; 1623select count(distinct fld1) from t2; 1624select companynr,count(distinct fld1) from t2 group by companynr; 1625select companynr,count(*) from t2 group by companynr; 1626select companynr,count(distinct concat(fld1,repeat(65,1000))) from t2 group by companynr; 1627select companynr,count(distinct concat(fld1,repeat(65,200))) from t2 group by companynr; 1628select companynr,count(distinct floor(fld1/100)) from t2 group by companynr; 1629select companynr,count(distinct concat(repeat(65,1000),floor(fld1/100))) from t2 group by companynr; 1630 1631# 1632# group with where on a key field 1633# 1634 1635select sum(fld1),fld3 from t2 where fld3="Romans" group by fld1 limit 10; 1636select name,count(*) from t3 where name='cloakroom' group by name; 1637select name,count(*) from t3 where name='cloakroom' and price>10 group by name; 1638select count(*) from t3 where name='cloakroom' and price2=823742; 1639select name,count(*) from t3 where name='cloakroom' and price2=823742 group by name; 1640select name,count(*) from t3 where name >= "extramarital" and price <= 39654943 group by name; 1641select t2.fld3,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name; 1642 1643# 1644# Group with extra not group fields. 1645# 1646 1647select companynr|0,companyname from t4 group by 1; 1648select t2.companynr,companyname,count(*) from t2,t4 where t2.companynr=t4.companynr group by t2.companynr order by companyname; 1649select t2.fld1,count(*) from t2,t3 where t2.fld1=158402 and t3.name=t2.fld3 group by t3.name; 1650 1651# 1652# Calculation with group functions 1653# 1654 1655select sum(Period)/count(*) from t1; 1656select 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; 1657select companynr,sum(price)/count(price) as avg from t3 group by companynr having avg > 70000000 order by avg; 1658 1659# 1660# Group with order on not first table 1661# optimizer: sort table by group and write group records to tmp table. 1662# sort tmp_table and send rows. 1663# 1664 1665select companynr,count(*) from t2 group by companynr order by 2 desc; 1666select companynr,count(*) from t2 where companynr > 40 group by companynr order by 2 desc; 1667select 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; 1668 1669# 1670# group by with many tables 1671# optimizer: create tmp table with group-by uniq index. 1672# write with update to tmp table. 1673# sort tmp table according to order (or group if no order) 1674# send rows 1675# 1676 1677select t3.companynr,fld3,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = 512 group by companynr,fld3; 1678select 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; 1679 1680# 1681# group with many tables and long group on many tables. group on formula 1682# optimizer: create tmp table with neaded fields 1683# sort tmp table by group and calculate sums to new table 1684# if different order by than group, sort tmp table 1685# send rows 1686# 1687 1688select 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; 1689 1690# 1691# WHERE const folding 1692# optimize: If there is a "field = const" part in the where, change all 1693# instances of field in the and level to const. 1694# All instances of const = const are checked once and removed. 1695# 1696 1697# 1698# Where -> t3.t2nr = 98005 and t2.fld1 = 98005 1699# 1700 1701select 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; 1702 1703select 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; 1704 1705explain select fld3 from t2 where 1>2 or 2>3; 1706explain select fld3 from t2 where fld1=fld1; 1707 1708# 1709# HAVING 1710# 1711 1712select companynr,fld1 from t2 HAVING fld1=250501 or fld1=250502; 1713select companynr,fld1 from t2 WHERE fld1>=250501 HAVING fld1<=250502; 1714select companynr,count(*) as count,sum(fld1) as sum from t2 group by companynr having count > 40 and sum/count >= 120000; 1715select companynr from t2 group by companynr having count(*) > 40 and sum(fld1)/count(*) >= 120000 ; 1716select t2.companynr,companyname,count(*) from t2,t4 where t2.companynr=t4.companynr group by companyname having t2.companynr >= 40; 1717 1718# 1719# MIN(), MAX() and COUNT() optimizing 1720# 1721 1722select count(*) from t2; 1723select count(*) from t2 where fld1 < 098024; 1724# PS does correct pre-zero here. MySQL can't do it as it returns a number. 1725--disable_ps_protocol 1726select min(fld1) from t2 where fld1>= 098024; 1727--enable_ps_protocol 1728select max(fld1) from t2 where fld1>= 098024; 1729select count(*) from t3 where price2=76234234; 1730select count(*) from t3 where companynr=512 and price2=76234234; 1731explain select min(fld1),max(fld1),count(*) from t2; 1732# PS does correct pre-zero here. MySQL can't do it as it returns a number. 1733--disable_ps_protocol 1734select min(fld1),max(fld1),count(*) from t2; 1735--enable_ps_protocol 1736select min(t2nr),max(t2nr) from t3 where t2nr=2115 and price2=823742; 1737select count(*),min(t2nr),max(t2nr) from t3 where name='spates' and companynr=78; 1738select t2nr,count(*) from t3 where name='gems' group by t2nr limit 20; 1739select max(t2nr) from t3 where price=983543950; 1740 1741# 1742# Test of alias 1743# 1744 1745select t1.period from t3 = t1 limit 1; 1746select t1.period from t1 as t1 limit 1; 1747select t1.period as "Nuvarande period" from t1 as t1 limit 1; 1748select period as ok_period from t1 limit 1; 1749select period as ok_period from t1 group by ok_period limit 1; 1750select 1+1 as summa from t1 group by summa limit 1; 1751select period as "Nuvarande period" from t1 group by "Nuvarande period" limit 1; 1752 1753# 1754# Some simple show commands 1755# 1756 1757show tables; 1758show tables from test like "s%"; 1759show tables from test like "t?"; 1760# We mask out the Privileges column because it differs with embedded server 1761--replace_column 8 # 1762show full columns from t2; 1763--replace_column 8 # 1764show full columns from t2 from test like 'f%'; 1765--replace_column 8 # 1766show full columns from t2 from test like 's%'; 1767show keys from t2; 1768 1769drop table t4, t3, t2, t1; 1770 1771# 1772# Test of DO 1773# 1774 1775DO 1; 1776DO benchmark(100,1+1),1,1; 1777 1778# 1779# Bug #6449: do default; 1780# 1781 1782--error ER_PARSE_ERROR 1783do default; 1784--error ER_BAD_FIELD_ERROR 1785do foobar; 1786 1787# 1788# random in WHERE clause 1789# 1790 1791CREATE TABLE t1 ( 1792 id mediumint(8) unsigned NOT NULL auto_increment, 1793 pseudo varchar(35) NOT NULL default '', 1794 PRIMARY KEY (id), 1795 UNIQUE KEY pseudo (pseudo) 1796); 1797INSERT INTO t1 (pseudo) VALUES ('test'); 1798INSERT INTO t1 (pseudo) VALUES ('test1'); 1799SELECT 1 as rnd1 from t1 where rand() > 2; 1800DROP TABLE t1; 1801 1802# 1803# Test of bug with SUM(CASE...) 1804# 1805 1806CREATE 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; 1807INSERT 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); 1808CREATE 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; 1809INSERT INTO t2 VALUES (1,3,10,'2002-06-01 08:00:00',35),(1,3,1010,'2002-06-01 12:00:01',35); 1810# Disable PS becasue we get more warnings from PS than from normal execution 1811--disable_ps_protocol 1812SELECT 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; 1813--enable_ps_protocol 1814# Testing the same select with NULL's instead of invalid datetime values 1815SELECT 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; 1816DROP TABLE t1,t2; 1817 1818# 1819# Test of bigint comparision 1820# 1821 1822create 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'); 1823INSERT INTO t1 VALUES (102935998719055004,'brade',1029359987,2,102935229116544068,102935229216544093); 1824select wss_type from t1 where wss_type ='102935229216544106'; 1825select wss_type from t1 where wss_type ='102935229216544105'; 1826select wss_type from t1 where wss_type ='102935229216544104'; 1827select wss_type from t1 where wss_type ='102935229216544093'; 1828select wss_type from t1 where wss_type =102935229216544093; 1829drop table t1; 1830select 1+2,"aaaa",3.13*2.0 into @a,@b,@c; 1831select @a; 1832select @b; 1833select @c; 1834 1835# 1836# Test of removing redundant braces in the FROM part 1837# (We test each construct with the braced join to the left and right; 1838# the latter case used to cause a syntax errors.) 1839# 1840 1841create table t1 (a int not null auto_increment primary key); 1842insert into t1 values (); 1843insert into t1 values (); 1844insert into t1 values (); 1845# , 1846select * from (t1 as t2 left join t1 as t3 using (a)), t1; 1847select * from t1, (t1 as t2 left join t1 as t3 using (a)); 1848# stright_join 1849select * from (t1 as t2 left join t1 as t3 using (a)) straight_join t1; 1850select * from t1 straight_join (t1 as t2 left join t1 as t3 using (a)); 1851# inner join on 1852select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 on t1.a>1; 1853select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1; 1854# inner join using 1855select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 using ( a ); 1856select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) using ( a ); 1857# left [outer] join on 1858--sorted_result 1859select * from (t1 as t2 left join t1 as t3 using (a)) left outer join t1 on t1.a>1; 1860select * from t1 left outer join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1; 1861# left join using 1862select * from (t1 as t2 left join t1 as t3 using (a)) left join t1 using ( a ); 1863select * from t1 left join (t1 as t2 left join t1 as t3 using (a)) using ( a ); 1864# natural left join 1865select * from (t1 as t2 left join t1 as t3 using (a)) natural left join t1; 1866select * from t1 natural left join (t1 as t2 left join t1 as t3 using (a)); 1867# right join on 1868select * from (t1 as t2 left join t1 as t3 using (a)) right join t1 on t1.a>1; 1869--sorted_result 1870select * from t1 right join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1; 1871# right [outer] joing using 1872select * from (t1 as t2 left join t1 as t3 using (a)) right outer join t1 using ( a ); 1873select * from t1 right outer join (t1 as t2 left join t1 as t3 using (a)) using ( a ); 1874# natural right join 1875select * from (t1 as t2 left join t1 as t3 using (a)) natural right join t1; 1876select * from t1 natural right join (t1 as t2 left join t1 as t3 using (a)); 1877# natural join 1878select * from t1 natural join (t1 as t2 left join t1 as t3 using (a)); 1879select * from (t1 as t2 left join t1 as t3 using (a)) natural join t1; 1880drop table t1; 1881 1882CREATE 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; 1883INSERT INTO t1 VALUES ("1",8264,2506),("2",8299,2517),("3",8301,2518),("4",8302,2519),("5",8303,2520),("6",8304,2521),("7",8305,2522); 1884CREATE TABLE t2 ( id int(11) NOT NULL auto_increment, PRIMARY KEY (id)) ENGINE=MyISAM; 1885INSERT INTO t2 VALUES (2517), (2518), (2519), (2520), (2521), (2522); 1886select * from t1, t2 WHERE t1.t2_id = t2.id and t1.t2_id > 0 order by t1.id LIMIT 0, 5; 1887drop table t1,t2; 1888 1889# 1890# outer join, impossible on condition, where, and usable key for range 1891# 1892create table t1 (id1 int NOT NULL); 1893create table t2 (id2 int NOT NULL); 1894create table t3 (id3 int NOT NULL); 1895create table t4 (id4 int NOT NULL, id44 int NOT NULL, KEY (id4)); 1896 1897insert into t1 values (1); 1898insert into t1 values (2); 1899insert into t2 values (1); 1900insert into t4 values (1,1); 1901 1902explain select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3 1903left join t4 on id3 = id4 where id2 = 1 or id4 = 1; 1904select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3 1905left join t4 on id3 = id4 where id2 = 1 or id4 = 1; 1906 1907drop table t1,t2,t3,t4; 1908# 1909# Bug #2298 1910# 1911 1912create table t1(s varchar(10) not null); 1913create table t2(s varchar(10) not null primary key); 1914create table t3(s varchar(10) not null primary key); 1915insert into t1 values ('one\t'), ('two\t'); 1916insert into t2 values ('one\r'), ('two\t'); 1917insert into t3 values ('one '), ('two\t'); 1918select * from t1 where s = 'one'; 1919select * from t2 where s = 'one'; 1920select * from t3 where s = 'one'; 1921select * from t1,t2 where t1.s = t2.s; 1922select * from t2,t3 where t2.s = t3.s; 1923drop table t1, t2, t3; 1924 1925# 1926# Bug #3759 1927# Both queries should produce identical plans and results. 1928# 1929create table t1 (a integer, b integer, index(a), index(b)); 1930create table t2 (c integer, d integer, index(c), index(d)); 1931insert into t1 values (1,2), (2,2), (3,2), (4,2); 1932insert into t2 values (1,3), (2,3), (3,4), (4,4); 1933explain select * from t1 left join t2 on a=c where d in (4); 1934select * from t1 left join t2 on a=c where d in (4); 1935explain select * from t1 left join t2 on a=c where d = 4; 1936select * from t1 left join t2 on a=c where d = 4; 1937drop table t1, t2; 1938 1939# 1940# Covering index is mentioned in EXPLAIN output for const tables (bug #5333) 1941# 1942 1943CREATE TABLE t1 ( 1944 i int(11) NOT NULL default '0', 1945 c char(10) NOT NULL default '', 1946 PRIMARY KEY (i), 1947 UNIQUE KEY c (c) 1948) ENGINE=MyISAM; 1949 1950INSERT INTO t1 VALUES (1,'a'); 1951INSERT INTO t1 VALUES (2,'b'); 1952INSERT INTO t1 VALUES (3,'c'); 1953 1954EXPLAIN SELECT i FROM t1 WHERE i=1; 1955 1956DROP TABLE t1; 1957 1958# 1959# Test case for bug 7520: a wrong cost of the index for a BLOB field 1960# 1961 1962CREATE TABLE t1 ( a BLOB, INDEX (a(20)) ); 1963CREATE TABLE t2 ( a BLOB, INDEX (a(20)) ); 1964 1965INSERT INTO t1 VALUES ('one'),('two'),('three'),('four'),('five'); 1966INSERT INTO t2 VALUES ('one'),('two'),('three'),('four'),('five'); 1967 1968EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USE INDEX (a) ON t1.a=t2.a; 1969EXPLAIN SELECT * FROM t1 LEFT JOIN t2 FORCE INDEX (a) ON t1.a=t2.a; 1970 1971DROP TABLE t1, t2; 1972 1973# 1974# Test case for bug 7098: substitution of a constant for a string field 1975# 1976 1977CREATE TABLE t1 ( city char(30) ); 1978INSERT INTO t1 VALUES ('London'); 1979INSERT INTO t1 VALUES ('Paris'); 1980 1981SELECT * FROM t1 WHERE city='London'; 1982SELECT * FROM t1 WHERE city='london'; 1983EXPLAIN SELECT * FROM t1 WHERE city='London' AND city='london'; 1984SELECT * FROM t1 WHERE city='London' AND city='london'; 1985EXPLAIN SELECT * FROM t1 WHERE city LIKE '%london%' AND city='London'; 1986SELECT * FROM t1 WHERE city LIKE '%london%' AND city='London'; 1987 1988DROP TABLE t1; 1989 1990# 1991# Bug#7425 inconsistent sort order on unsigned columns result of substraction 1992# 1993 1994create table t1 (a int(11) unsigned, b int(11) unsigned); 1995insert into t1 values (1,0), (1,1), (18446744073709551615,0); 1996select a-b from t1 order by 1; 1997select a-b , (a-b < 0) from t1 order by 1; 1998select a-b as d, (a-b >= 0), b from t1 group by b having d >= 0; 1999select cast((a - b) as unsigned) from t1 order by 1; 2000drop table t1; 2001 2002 2003# 2004# Bug#8733 server accepts malformed query (multiply mentioned distinct) 2005# 2006create table t1 (a int(11)); 2007select all all * from t1; 2008select distinct distinct * from t1; 2009--error 1221 2010select all distinct * from t1; 2011--error 1221 2012select distinct all * from t1; 2013drop table t1; 2014 2015# 2016# Test for BUG#10095 2017# 2018CREATE TABLE t1 ( 2019 kunde_intern_id int(10) unsigned NOT NULL default '0', 2020 kunde_id int(10) unsigned NOT NULL default '0', 2021 FK_firma_id int(10) unsigned NOT NULL default '0', 2022 aktuell enum('Ja','Nein') NOT NULL default 'Ja', 2023 vorname varchar(128) NOT NULL default '', 2024 nachname varchar(128) NOT NULL default '', 2025 geloescht enum('Ja','Nein') NOT NULL default 'Nein', 2026 firma varchar(128) NOT NULL default '' 2027); 2028 2029INSERT INTO t1 VALUES 2030 (3964,3051,1,'Ja','Vorname1','1Nachname','Nein','Print Schau XXXX'), 2031 (3965,3051111,1,'Ja','Vorname1111','1111Nachname','Nein','Print Schau XXXX'); 2032 2033 2034SELECT kunde_id ,FK_firma_id ,aktuell, vorname, nachname, geloescht FROM t1 2035 WHERE 2036 ( 2037 ( 2038 ( '' != '' AND firma LIKE CONCAT('%', '', '%')) 2039 OR 2040 (vorname LIKE CONCAT('%', 'Vorname1', '%') AND 2041 nachname LIKE CONCAT('%', '1Nachname', '%') AND 2042 'Vorname1' != '' AND 'xxxx' != '') 2043 ) 2044 AND 2045 ( 2046 aktuell = 'Ja' AND geloescht = 'Nein' AND FK_firma_id = 2 2047 ) 2048 ) 2049 ; 2050 2051SELECT kunde_id ,FK_firma_id ,aktuell, vorname, nachname, 2052geloescht FROM t1 2053 WHERE 2054 ( 2055 ( 2056 aktuell = 'Ja' AND geloescht = 'Nein' AND FK_firma_id = 2 2057 ) 2058 AND 2059 ( 2060 ( '' != '' AND firma LIKE CONCAT('%', '', '%') ) 2061 OR 2062 ( vorname LIKE CONCAT('%', 'Vorname1', '%') AND 2063nachname LIKE CONCAT('%', '1Nachname', '%') AND 'Vorname1' != '' AND 2064'xxxx' != '') 2065 ) 2066 ) 2067 ; 2068 2069SELECT COUNT(*) FROM t1 WHERE 2070( 0 OR (vorname LIKE '%Vorname1%' AND nachname LIKE '%1Nachname%' AND 1)) 2071AND FK_firma_id = 2; 2072 2073drop table t1; 2074 2075# 2076# 2077# Test for Bug#8009, SELECT failed on bigint unsigned when using HEX 2078# 2079 2080CREATE TABLE t1 (b BIGINT(20) UNSIGNED NOT NULL, PRIMARY KEY (b)); 2081INSERT INTO t1 VALUES (0x8000000000000000); 2082SELECT b FROM t1 WHERE b=0x8000000000000000; 2083DROP TABLE t1; 2084 2085# 2086# IN with outer join condition (BUG#9393) 2087# 2088CREATE TABLE `t1` ( `gid` int(11) default NULL, `uid` int(11) default NULL); 2089 2090CREATE TABLE `t2` ( `ident` int(11) default NULL, `level` char(16) default NULL); 2091INSERT INTO `t2` VALUES (0,'READ'); 2092 2093CREATE TABLE `t3` ( `id` int(11) default NULL, `name` char(16) default NULL); 2094INSERT INTO `t3` VALUES (1,'fs'); 2095 2096select * from t3 left join t1 on t3.id = t1.uid, t2 where t2.ident in (0, t1.gid, t3.id, 0); 2097 2098drop table t1,t2,t3; 2099 2100# Test for BUG#11700 2101CREATE TABLE t1 ( 2102 acct_id int(11) NOT NULL default '0', 2103 profile_id smallint(6) default NULL, 2104 UNIQUE KEY t1$acct_id (acct_id), 2105 KEY t1$profile_id (profile_id) 2106); 2107INSERT INTO t1 VALUES (132,17),(133,18); 2108 2109CREATE TABLE t2 ( 2110 profile_id smallint(6) default NULL, 2111 queue_id int(11) default NULL, 2112 seq int(11) default NULL, 2113 KEY t2$queue_id (queue_id) 2114); 2115INSERT INTO t2 VALUES (17,31,4),(17,30,3),(17,36,2),(17,37,1); 2116 2117CREATE TABLE t3 ( 2118 id int(11) NOT NULL default '0', 2119 qtype int(11) default NULL, 2120 seq int(11) default NULL, 2121 warn_lvl int(11) default NULL, 2122 crit_lvl int(11) default NULL, 2123 rr1 tinyint(4) NOT NULL default '0', 2124 rr2 int(11) default NULL, 2125 default_queue tinyint(4) NOT NULL default '0', 2126 KEY t3$qtype (qtype), 2127 KEY t3$id (id) 2128); 2129 2130INSERT INTO t3 VALUES (30,1,29,NULL,NULL,0,NULL,0),(31,1,28,NULL,NULL,0,NULL,0), 2131 (36,1,34,NULL,NULL,0,NULL,0),(37,1,35,NULL,NULL,0,121,0); 2132 2133SELECT COUNT(*) FROM t1 a STRAIGHT_JOIN t2 pq STRAIGHT_JOIN t3 q 2134WHERE 2135 (pq.profile_id = a.profile_id) AND (a.acct_id = 132) AND 2136 (pq.queue_id = q.id) AND (q.rr1 <> 1); 2137 2138drop table t1,t2,t3; 2139 2140# 2141# Bug #11482 Wrongly applied optimization was erroneously rejecting valid 2142# rows 2143create table t1 (f1 int); 2144insert into t1 values (1),(NULL); 2145create table t2 (f2 int, f3 int, f4 int); 2146create index idx1 on t2 (f4); 2147insert into t2 values (1,2,3),(2,4,6); 2148select A.f2 from t1 left join t2 A on A.f2 = f1 where A.f3=(select min(f3) 2149from t2 C where A.f4 = C.f4) or A.f3 IS NULL; 2150drop table t1,t2; 2151 2152# 2153# Bug #11521 Negative integer keys incorrectly substituted for 0 during 2154# range analysis. 2155 2156create table t2 (a tinyint unsigned); 2157create index t2i on t2(a); 2158insert into t2 values (0), (254), (255); 2159explain select * from t2 where a > -1; 2160select * from t2 where a > -1; 2161drop table t2; 2162 2163# 2164# Bug #11745: SELECT ... FROM DUAL with WHERE condition 2165# 2166 2167CREATE TABLE t1 (a int, b int, c int); 2168INSERT INTO t1 2169 SELECT 50, 3, 3 FROM DUAL 2170 WHERE NOT EXISTS 2171 (SELECT * FROM t1 WHERE a = 50 AND b = 3); 2172SELECT * FROM t1; 2173INSERT INTO t1 2174 SELECT 50, 3, 3 FROM DUAL 2175 WHERE NOT EXISTS 2176 (SELECT * FROM t1 WHERE a = 50 AND b = 3); 2177select found_rows(); 2178SELECT * FROM t1; 2179select count(*) from t1; 2180select found_rows(); 2181select count(*) from t1 limit 2,3; 2182select found_rows(); 2183select SQL_CALC_FOUND_ROWS count(*) from t1 limit 2,3; 2184select found_rows(); 2185 2186DROP TABLE t1; 2187 2188# 2189# Bug 7672 Unknown column error in order clause 2190# 2191CREATE TABLE t1 (a INT, b INT); 2192(SELECT a, b AS c FROM t1) ORDER BY c+1; 2193(SELECT a, b AS c FROM t1) ORDER BY b+1; 2194SELECT a, b AS c FROM t1 ORDER BY c+1; 2195SELECT a, b AS c FROM t1 ORDER BY b+1; 2196drop table t1; 2197 2198# 2199# Bug #13356 assertion failed in resolve_const_item() 2200# 2201create table t1(f1 int, f2 int); 2202create table t2(f3 int); 2203select f1 from t1,t2 where f1=f2 and (f1,f2) = ((1,1)); 2204select f1 from t1,t2 where f1=f2 and (f1,NULL) = ((1,1)); 2205select f1 from t1,t2 where f1=f2 and (f1,f2) = ((1,NULL)); 2206insert into t1 values(1,1),(2,null); 2207insert into t2 values(2); 2208select * from t1,t2 where f1=f3 and (f1,f2) = (2,null); 2209select * from t1,t2 where f1=f3 and (f1,f2) <=> (2,null); 2210drop table t1,t2; 2211 2212# 2213# Bug #13535 2214# 2215create table t1 (f1 int not null auto_increment primary key, f2 varchar(10)); 2216create table t11 like t1; 2217insert into t1 values(1,""),(2,""); 2218--replace_column 7 X 8 X 9 X 10 X 11 X 12 X 13 X 14 X 2219show table status like 't1%'; 2220select 123 as a from t1 where f1 is null; 2221drop table t1,t11; 2222 2223# 2224# Bug #3874 (function in GROUP and LEFT JOIN) 2225# 2226 2227CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL, UNIQUE idx (a,b) ); 2228INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4); 2229CREATE TABLE t2 ( a INT NOT NULL, b INT NOT NULL, e INT ); 2230INSERT INTO t2 VALUES ( 1,10,1), (1,10,2), (1,11,1), (1,11,2), (1,2,1), (1,2,2),(1,2,3); 2231SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2 LEFT JOIN 2232t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY a, b, c; 2233SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2 LEFT JOIN 2234t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY t1.a, t1.b, c; 2235SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2 LEFT JOIN 2236t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY t2.a, t2.b, c; 2237SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2,t1 2238WHERE t2.a = t1.a AND t2.b = t1.b GROUP BY a, b, c; 2239DROP TABLE IF EXISTS t1, t2; 2240 2241# 2242# Bug #13855 select distinct with group by caused server crash 2243# 2244create table t1 (f1 int primary key, f2 int); 2245create table t2 (f3 int, f4 int, primary key(f3,f4)); 2246insert into t1 values (1,1); 2247insert into t2 values (1,1),(1,2); 2248select distinct count(f2) >0 from t1 left join t2 on f1=f3 group by f1; 2249drop table t1,t2; 2250 2251# 2252# Bug #14482 Server crash when subselecting from the same table 2253# 2254create table t1 (f1 int,f2 int); 2255insert into t1 values(1,1); 2256create table t2 (f3 int, f4 int, primary key(f3,f4)); 2257insert into t2 values(1,1); 2258select * from t1 where f1 in (select f3 from t2 where (f3,f4)= (select f3,f4 from t2)); 2259drop table t1,t2; 2260 2261# 2262# Bug #4981: 4.x and 5.x produce non-optimal execution path, 3.23 regression test failure 2263# 2264CREATE TABLE t1(a int, b int, c int, KEY b(b), KEY c(c)); 2265insert into t1 values (1,0,0),(2,0,0); 2266CREATE TABLE t2 (a int, b varchar(2), c varchar(2), PRIMARY KEY(a)); 2267insert into t2 values (1,'',''), (2,'',''); 2268CREATE TABLE t3 (a int, b int, PRIMARY KEY (a,b), KEY a (a), KEY b (b)); 2269insert into t3 values (1,1),(1,2); 2270# must have "range checked" for t2 2271explain select straight_join DISTINCT t2.a,t2.b, t1.c from t1, t3, t2 2272 where (t1.c=t2.a or (t1.c=t3.a and t2.a=t3.b)) and t1.b=556476786 and 2273 t2.b like '%%' order by t2.b limit 0,1; 2274DROP TABLE t1,t2,t3; 2275 2276# 2277# Bug #17873: confusing error message when IGNORE INDEX refers a column name 2278# 2279 2280CREATE TABLE t1 (a int, INDEX idx(a)); 2281INSERT INTO t1 VALUES (2), (3), (1); 2282 2283EXPLAIN SELECT * FROM t1 IGNORE INDEX (idx); 2284--error 1176 2285EXPLAIN SELECT * FROM t1 IGNORE INDEX (a); 2286--error 1176 2287EXPLAIN SELECT * FROM t1 FORCE INDEX (a); 2288 2289DROP TABLE t1; 2290 2291# 2292# Bug #21019: First result of SELECT COUNT(*) different than consecutive runs 2293# 2294CREATE TABLE t1 (a int, b int); 2295INSERT INTO t1 VALUES (1,1), (2,1), (4,10); 2296 2297CREATE TABLE t2 (a int PRIMARY KEY, b int, KEY b (b)); 2298INSERT INTO t2 VALUES (1,NULL), (2,10); 2299ALTER TABLE t1 ENABLE KEYS; 2300 2301EXPLAIN SELECT STRAIGHT_JOIN SQL_NO_CACHE COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; 2302SELECT STRAIGHT_JOIN SQL_NO_CACHE * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; 2303EXPLAIN SELECT STRAIGHT_JOIN SQL_NO_CACHE COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; 2304SELECT STRAIGHT_JOIN SQL_NO_CACHE * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; 2305DROP TABLE IF EXISTS t1,t2; 2306 2307# 2308# Bug #20954 "avg(keyval) retuns 0.38 but max(keyval) returns an empty set" 2309# 2310--disable_ps_protocol 2311CREATE TABLE t1 (key1 float default NULL, UNIQUE KEY key1 (key1)); 2312CREATE TABLE t2 (key2 float default NULL, UNIQUE KEY key2 (key2)); 2313INSERT INTO t1 VALUES (0.3762),(0.3845),(0.6158),(0.7941); 2314INSERT INTO t2 VALUES (1.3762),(1.3845),(1.6158),(1.7941); 2315 2316explain select max(key1) from t1 where key1 <= 0.6158; 2317explain select max(key2) from t2 where key2 <= 1.6158; 2318explain select min(key1) from t1 where key1 >= 0.3762; 2319explain select min(key2) from t2 where key2 >= 1.3762; 2320explain select max(key1), min(key2) from t1, t2 2321where key1 <= 0.6158 and key2 >= 1.3762; 2322explain select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5; 2323explain select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5; 2324 2325select max(key1) from t1 where key1 <= 0.6158; 2326select max(key2) from t2 where key2 <= 1.6158; 2327select min(key1) from t1 where key1 >= 0.3762; 2328select min(key2) from t2 where key2 >= 1.3762; 2329select max(key1), min(key2) from t1, t2 2330where key1 <= 0.6158 and key2 >= 1.3762; 2331select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5; 2332select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5; 2333 2334DROP TABLE t1,t2; 2335--enable_ps_protocol 2336 2337# 2338# Bug #18759 "Incorrect string to numeric conversion" 2339# 2340# This test is here so that the behavior will not be changed to 4.1 2341# and not to 5.0 either. In 4.1 and 5.0 sending an integer as a string 2342# will be converted internally to real (double) value and it is not 2343# as accurate as bigint (longlong) for integers. Thus the results may 2344# vary. In 5.1 internally it is decimal, which is a string type and 2345# will be more accurate. Due to rather big changes needed to fix this 2346# in 4.1 or 5.0 it is not desired to do it in the stable versions. 2347# 2348# This test is here only to make sure that behavior is not changed in 2349# 4.1 and 5.0 2350# 2351CREATE TABLE t1 (i BIGINT UNSIGNED NOT NULL); 2352INSERT INTO t1 VALUES (10); 2353SELECT i='1e+01',i=1e+01, i in (1e+01,1e+01), i in ('1e+01','1e+01') FROM t1; 2354DROP TABLE t1; 2355 2356# 2357# Bug #22533: storing large hex strings 2358# 2359 2360create table t1(a bigint unsigned, b bigint); 2361insert into t1 values (0xfffffffffffffffff, 0xfffffffffffffffff), 2362 (0x10000000000000000, 0x10000000000000000), 2363 (0x8fffffffffffffff, 0x8fffffffffffffff); 2364select hex(a), hex(b) from t1; 2365drop table t1; 2366 2367# 2368# Bug #32103: optimizer crash when join on int and mediumint with variable in 2369# where clause 2370# 2371 2372CREATE TABLE t1 (c0 int); 2373CREATE TABLE t2 (c0 int); 2374 2375# We need any variable that: 2376# 1. has integer type, 2377# 2. can be used with the "@@name" syntax 2378# 3. available in every server build 2379INSERT INTO t1 VALUES(@@connect_timeout); 2380INSERT INTO t2 VALUES(@@connect_timeout); 2381 2382# We only need to ensure 1 row is returned to validate the results 2383--replace_column 1 X 2 X 2384SELECT * FROM t1 JOIN t2 ON t1.c0 = t2.c0 WHERE (t1.c0 <=> @@connect_timeout); 2385 2386DROP TABLE t1, t2; 2387 2388--echo End of 4.1 tests 2389 2390# 2391# Test for bug #6474 2392# 2393 2394CREATE TABLE t1 ( 2395K2C4 varchar(4) character set latin1 collate latin1_bin NOT NULL default '', 2396K4N4 varchar(4) character set latin1 collate latin1_bin NOT NULL default '0000', 2397F2I4 int(11) NOT NULL default '0' 2398) ENGINE=MyISAM DEFAULT CHARSET=latin1; 2399 2400INSERT INTO t1 VALUES 2401('W%RT', '0100', 1), 2402('W-RT', '0100', 1), 2403('WART', '0100', 1), 2404('WART', '0200', 1), 2405('WERT', '0100', 2), 2406('WORT','0200', 2), 2407('WT', '0100', 2), 2408('W_RT', '0100', 2), 2409('WaRT', '0100', 3), 2410('WART', '0300', 3), 2411('WRT' , '0400', 3), 2412('WURM', '0500', 3), 2413('W%T', '0600', 4), 2414('WA%T', '0700', 4), 2415('WA_T', '0800', 4); 2416 2417SELECT K2C4, K4N4, F2I4 FROM t1 2418 WHERE K2C4 = 'WART' AND 2419 (F2I4 = 2 AND K2C4 = 'WART' OR (F2I4 = 2 OR K4N4 = '0200')); 2420SELECT K2C4, K4N4, F2I4 FROM t1 2421 WHERE K2C4 = 'WART' AND (K2C4 = 'WART' OR K4N4 = '0200'); 2422DROP TABLE t1; 2423 2424# 2425# Bug#8670 2426# 2427create table t1 (a int, b int); 2428create table t2 like t1; 2429select t1.a from (t1 inner join t2 on t1.a=t2.a) where t2.a=1; 2430select t1.a from ((t1 inner join t2 on t1.a=t2.a)) where t2.a=1; 2431select 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; 2432drop table t1,t2; 2433 2434# 2435# Bug#9820 2436# 2437 2438create table t1 (s1 varchar(5)); 2439insert into t1 values ('Wall'); 2440select min(s1) from t1 group by s1 with rollup; 2441drop table t1; 2442 2443# 2444# Bug#9799 2445# 2446 2447create table t1 (s1 int) engine=myisam; 2448insert into t1 values (0); 2449select avg(distinct s1) from t1 group by s1 with rollup; 2450drop table t1; 2451 2452# 2453# Bug#9800 2454# 2455 2456create table t1 (s1 int); 2457insert into t1 values (null),(1); 2458select avg(s1) as x from t1 group by s1 with rollup; 2459--error ER_WRONG_USAGE 2460select distinct avg(s1) as x from t1 group by s1 with rollup; 2461drop table t1; 2462 2463 2464# 2465# Test for bug #10084: STRAIGHT_JOIN with ON expression 2466# 2467 2468CREATE TABLE t1 (a int); 2469CREATE TABLE t2 (a int); 2470INSERT INTO t1 VALUES (1), (2), (3), (4), (5); 2471INSERT INTO t2 VALUES (2), (4), (6); 2472 2473SELECT t1.a FROM t1 STRAIGHT_JOIN t2 ON t1.a=t2.a; 2474 2475EXPLAIN SELECT t1.a FROM t1 STRAIGHT_JOIN t2 ON t1.a=t2.a; 2476EXPLAIN SELECT t1.a FROM t1 INNER JOIN t2 ON t1.a=t2.a; 2477 2478DROP TABLE t1,t2; 2479 2480# 2481# Bug #10650 2482# 2483 2484select x'10' + 0, X'10' + 0, b'10' + 0, B'10' + 0; 2485 2486# 2487# Bug #11398 Bug in field_conv() results in wrong result of join with index 2488# 2489create table t1 (f1 varchar(6) default NULL, f2 int(6) primary key not null); 2490create table t2 (f3 varchar(5) not null, f4 varchar(5) not null, UNIQUE KEY UKEY (f3,f4)); 2491insert into t1 values (" 2", 2); 2492insert into t2 values (" 2", " one "),(" 2", " two "); 2493select * from t1 left join t2 on f1 = f3; 2494drop table t1,t2; 2495 2496# 2497# Bug #6558 Views: CREATE VIEW fails with JOIN ... USING 2498# 2499 2500create table t1 (empnum smallint, grp int); 2501create table t2 (empnum int, name char(5)); 2502insert into t1 values(1,1); 2503insert into t2 values(1,'bob'); 2504create view v1 as select * from t2 inner join t1 using (empnum); 2505select * from v1; 2506drop table t1,t2; 2507drop view v1; 2508 2509# 2510# Bug #10646 Columns included in the join between two tables are ambigious 2511# in the select 2512# 2513 2514create table t1 (pk int primary key, b int); 2515create table t2 (pk int primary key, c int); 2516select pk from t1 inner join t2 using (pk); 2517drop table t1,t2; 2518 2519# 2520# Bug #10972 Natural join of view and underlying table gives wrong result 2521# 2522 2523create table t1 (s1 int, s2 char(5), s3 decimal(10)); 2524create view v1 as select s1, s2, 'x' as s3 from t1; 2525select * from t1 natural join v1; 2526insert into t1 values (1,'x',5); 2527select * from t1 natural join v1; 2528drop table t1; 2529drop view v1; 2530 2531# 2532# Bug #6276 A SELECT that does a NATURAL OUTER JOIN without common 2533# columns crashes server because of empty ON condition 2534# 2535 2536create table t1(a1 int); 2537create table t2(a2 int); 2538insert into t1 values(1),(2); 2539insert into t2 values(1),(2); 2540create view v2 (c) as select a1 from t1; 2541 2542--sorted_result 2543select * from t1 natural left join t2; 2544--sorted_result 2545select * from t1 natural right join t2; 2546 2547--sorted_result 2548select * from v2 natural left join t2; 2549--sorted_result 2550select * from v2 natural right join t2; 2551 2552drop table t1, t2; 2553drop view v2; 2554 2555 2556# 2557# Bug #4789 Incosistent results of more than 2-way natural joins due to 2558# incorrect transformation to join ... on. 2559# 2560 2561create table t1 (a int(10), t1_val int(10)); 2562create table t2 (b int(10), t2_val int(10)); 2563create table t3 (a int(10), b int(10)); 2564insert into t1 values (1,1),(2,2); 2565insert into t2 values (1,1),(2,2),(3,3); 2566insert into t3 values (1,1),(2,1),(3,1),(4,1); 2567# the following two queries must return the same result 2568select * from t1 natural join t2 natural join t3; 2569select * from t1 natural join t3 natural join t2; 2570drop table t1, t2, t3; 2571 2572 2573# 2574# Bug #12841: Server crash on DO IFNULL(NULL,NULL) 2575# 2576# (testing returning of int, decimal, real, string) 2577DO IFNULL(NULL, NULL); 2578SELECT CAST(IFNULL(NULL, NULL) AS DECIMAL); 2579SELECT ABS(IFNULL(NULL, NULL)); 2580SELECT IFNULL(NULL, NULL); 2581 2582# 2583# BUG #12595 (ESCAPE must be exactly one) 2584# 2585SET @OLD_SQL_MODE12595=@@SQL_MODE, @@SQL_MODE=''; 2586SHOW LOCAL VARIABLES LIKE 'SQL_MODE'; 2587 2588CREATE TABLE BUG_12595(a varchar(100)); 2589INSERT INTO BUG_12595 VALUES ('hakan%'), ('hakank'), ("ha%an"); 2590SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\%'; 2591SELECT * FROM BUG_12595 WHERE a LIKE 'hakan*%' ESCAPE '*'; 2592-- error 1210 2593SELECT * FROM BUG_12595 WHERE a LIKE 'hakan**%' ESCAPE '**'; 2594# this should work when sql_mode is not NO_BACKSLASH_ESCAPES 2595SELECT * FROM BUG_12595 WHERE a LIKE 'hakan%' ESCAPE ''; 2596SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\%' ESCAPE ''; 2597SELECT * FROM BUG_12595 WHERE a LIKE 'ha\%an' ESCAPE 0x5c; 2598SELECT * FROM BUG_12595 WHERE a LIKE 'ha%%an' ESCAPE '%'; 2599SELECT * FROM BUG_12595 WHERE a LIKE 'ha\%an' ESCAPE '\\'; 2600SELECT * FROM BUG_12595 WHERE a LIKE 'ha|%an' ESCAPE '|'; 2601 2602SET @@SQL_MODE='NO_BACKSLASH_ESCAPES'; 2603SHOW LOCAL VARIABLES LIKE 'SQL_MODE'; 2604SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\%'; 2605SELECT * FROM BUG_12595 WHERE a LIKE 'hakan*%' ESCAPE '*'; 2606-- error 1210 2607SELECT * FROM BUG_12595 WHERE a LIKE 'hakan**%' ESCAPE '**'; 2608-- error 1210 2609SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\%' ESCAPE '\\'; 2610#this gives an error when NO_BACKSLASH_ESCAPES is set 2611-- error 1210 2612SELECT * FROM BUG_12595 WHERE a LIKE 'hakan%' ESCAPE ''; 2613SELECT * FROM BUG_12595 WHERE a LIKE 'ha\%an' ESCAPE 0x5c; 2614SELECT * FROM BUG_12595 WHERE a LIKE 'ha|%an' ESCAPE '|'; 2615-- error 1210 2616SELECT * FROM BUG_12595 WHERE a LIKE 'hakan\n%' ESCAPE '\n'; 2617 2618SET @@SQL_MODE=@OLD_SQL_MODE12595; 2619DROP TABLE BUG_12595; 2620 2621# 2622# Bug #6495 Illogical requirement for column qualification in NATURAL join 2623# 2624 2625create table t1 (a char(1)); 2626create table t2 (a char(1)); 2627insert into t1 values ('a'),('b'),('c'); 2628insert into t2 values ('b'),('c'),('d'); 2629select a from t1 natural join t2; 2630select * from t1 natural join t2 where a = 'b'; 2631drop table t1, t2; 2632 2633# 2634# Bug #12977 Compare table names with qualifying field tables only 2635# for base tables, search all nested join operands of natural joins. 2636# 2637 2638CREATE TABLE t1 (`id` TINYINT); 2639CREATE TABLE t2 (`id` TINYINT); 2640CREATE TABLE t3 (`id` TINYINT); 2641INSERT INTO t1 VALUES (1),(2),(3); 2642INSERT INTO t2 VALUES (2); 2643INSERT INTO t3 VALUES (3); 2644-- error 1052 2645SELECT t1.id,t3.id FROM t1 JOIN t2 ON (t2.id=t1.id) LEFT JOIN t3 USING (id); 2646-- error 1052 2647SELECT t1.id,t3.id FROM t1 JOIN t2 ON (t2.notacolumn=t1.id) LEFT JOIN t3 USING (id); 2648-- error 1052 2649SELECT id,t3.id FROM t1 JOIN t2 ON (t2.id=t1.id) LEFT JOIN t3 USING (id); 2650-- error 1052 2651SELECT id,t3.id FROM (t1 JOIN t2 ON (t2.id=t1.id)) LEFT JOIN t3 USING (id); 2652 2653drop table t1, t2, t3; 2654 2655# 2656# Bug #13067 JOIN xxx USING is case sensitive 2657# 2658 2659create table t1 (a int(10),b int(10)); 2660create table t2 (a int(10),b int(10)); 2661insert into t1 values (1,10),(2,20),(3,30); 2662insert into t2 values (1,10); 2663# both queries should produce the same result 2664select * from t1 inner join t2 using (A); 2665select * from t1 inner join t2 using (a); 2666drop table t1, t2; 2667 2668# 2669# Bug #12943 Incorrect nesting of [INNER| CROSS] JOIN due to unspecified 2670# associativity in the parser. 2671# 2672 2673create table t1 (a int, c int); 2674create table t2 (b int); 2675create table t3 (b int, a int); 2676create table t4 (c int); 2677insert into t1 values (1,1); 2678insert into t2 values (1); 2679insert into t3 values (1,1); 2680insert into t4 values (1); 2681 2682select * from t1 join t2 join t3 on (t2.b = t3.b and t1.a = t3.a); 2683# Notice that ',' has lower priority than 'join', thus we have that: 2684# t1, t2 join t3 <==> t1, (t2 join t3). 2685-- error 1054 2686select * from t1, t2 join t3 on (t2.b = t3.b and t1.a = t3.a); 2687select * from t1 join t2 join t3 join t4 on (t1.a = t4.c and t2.b = t4.c); 2688select * from t1 join t2 join t4 using (c); 2689drop table t1, t2, t3, t4; 2690 2691# 2692# Bug #12291 Table wasn't reinited for index scan after sequential scan 2693# 2694create table t1(x int, y int); 2695create table t2(x int, y int); 2696create table t3(x int, primary key(x)); 2697insert into t1 values (1, 1), (2, 1), (3, 1), (4, 3), (5, 6), (6, 6); 2698insert into t2 values (1, 1), (2, 1), (3, 3), (4, 6), (5, 6); 2699insert into t3 values (1), (2), (3), (4), (5); 2700select t1.x, t3.x from t1, t2, t3 where t1.x = t2.x and t3.x >= t1.y and t3.x <= t2.y; 2701drop table t1,t2,t3; 2702 2703# 2704# Bug #13127 LEFT JOIN against a VIEW returns NULL instead of correct value 2705# 2706 2707create table t1 (id char(16) not null default '', primary key (id)); 2708insert into t1 values ('100'),('101'),('102'); 2709create table t2 (id char(16) default null); 2710insert into t2 values (1); 2711create view v1 as select t1.id from t1; 2712create view v2 as select t2.id from t2; 2713create view v3 as select (t1.id+2) as id from t1 natural left join t2; 2714 2715# all queries must return the same result 2716select t1.id from t1 left join v2 using (id); 2717select t1.id from v2 right join t1 using (id); 2718--sorted_result 2719select t1.id from t1 left join v3 using (id); 2720select * from t1 left join v2 using (id); 2721select * from v2 right join t1 using (id); 2722--sorted_result 2723select * from t1 left join v3 using (id); 2724 2725select v1.id from v1 left join v2 using (id); 2726select v1.id from v2 right join v1 using (id); 2727--sorted_result 2728select v1.id from v1 left join v3 using (id); 2729select * from v1 left join v2 using (id); 2730select * from v2 right join v1 using (id); 2731--sorted_result 2732select * from v1 left join v3 using (id); 2733 2734drop table t1, t2; 2735drop view v1, v2, v3; 2736 2737# 2738# Bug #13597 Column in ON condition not resolved if references a table in 2739# nested right join. 2740# 2741 2742create table t1 (id int(11) not null default '0'); 2743insert into t1 values (123),(191),(192); 2744create table t2 (id char(16) character set utf8 not null); 2745insert into t2 values ('58013'),('58014'),('58015'),('58016'); 2746create table t3 (a_id int(11) not null, b_id char(16) character set utf8); 2747insert into t3 values (123,null),(123,null),(123,null),(123,null),(123,null),(123,'58013'); 2748 2749# both queries are equivalent 2750select count(*) 2751from t1 inner join (t3 left join t2 on t2.id = t3.b_id) on t1.id = t3.a_id; 2752 2753select count(*) 2754from t1 inner join (t2 right join t3 on t2.id = t3.b_id) on t1.id = t3.a_id; 2755 2756drop table t1,t2,t3; 2757 2758# 2759# Bug #13832 Incorrect parse order of join productions due to unspecified 2760# operator priorities results in incorrect join tree. 2761# 2762 2763create table t1 (a int); 2764create table t2 (b int); 2765create table t3 (c int); 2766select * from t1 join t2 join t3 on (t1.a=t3.c); 2767select * from t1 join t2 left join t3 on (t1.a=t3.c); 2768select * from t1 join t2 right join t3 on (t1.a=t3.c); 2769select * from t1 join t2 straight_join t3 on (t1.a=t3.c); 2770drop table t1, t2 ,t3; 2771 2772# 2773# Bug #14093 Query takes a lot of time when date format is not valid 2774# fix optimizes execution. so here we just check that returned set is 2775# correct. 2776create table t1(f1 int, f2 date); 2777insert into t1 values(1,'2005-01-01'),(2,'2005-09-01'),(3,'2005-09-30'), 2778 (4,'2005-10-01'),(5,'2005-12-30'); 2779# should return all records 2780select * from t1 where f2 >= 0 order by f2; 2781select * from t1 where f2 >= '0000-00-00' order by f2; 2782# should return 4,5 2783select * from t1 where f2 >= '2005-09-31' order by f2; 2784select * from t1 where f2 >= '2005-09-3a' order by f2; 2785# should return 1,2,3 2786select * from t1 where f2 <= '2005-09-31' order by f2; 2787select * from t1 where f2 <= '2005-09-3a' order by f2; 2788drop table t1; 2789 2790# 2791# Bug ##14662 ORDER BY on column of a view, with an alias of the same 2792# column causes ambiguous 2793# 2794 2795create table t1 (f1 int, f2 int); 2796insert into t1 values (1, 30), (2, 20), (3, 10); 2797create algorithm=merge view v1 as select f1, f2 from t1; 2798create algorithm=merge view v2 (f2, f1) as select f1, f2 from t1; 2799create algorithm=merge view v3 as select t1.f1 as f2, t1.f2 as f1 from t1; 2800select t1.f1 as x1, f1 from t1 order by t1.f1; 2801select v1.f1 as x1, f1 from v1 order by v1.f1; 2802select v2.f1 as x1, f1 from v2 order by v2.f1; 2803select v3.f1 as x1, f1 from v3 order by v3.f1; 2804select f1, f2, v1.f1 as x1 from v1 order by v1.f1; 2805select f1, f2, v2.f1 as x1 from v2 order by v2.f1; 2806select f1, f2, v3.f1 as x1 from v3 order by v3.f1; 2807drop table t1; 2808drop view v1, v2, v3; 2809 2810# 2811# Bug #15106: lost equality predicate of the form field=const in a join query 2812# 2813 2814CREATE TABLE t1(key_a int4 NOT NULL, optimus varchar(32), PRIMARY KEY(key_a)); 2815CREATE TABLE t2(key_a int4 NOT NULL, prime varchar(32), PRIMARY KEY(key_a)); 2816CREATE table t3(key_a int4 NOT NULL, key_b int4 NOT NULL, foo varchar(32), 2817 PRIMARY KEY(key_a,key_b)); 2818 2819INSERT INTO t1 VALUES (0,''); 2820INSERT INTO t1 VALUES (1,'i'); 2821INSERT INTO t1 VALUES (2,'j'); 2822INSERT INTO t1 VALUES (3,'k'); 2823 2824INSERT INTO t2 VALUES (1,'r'); 2825INSERT INTO t2 VALUES (2,'s'); 2826INSERT INTO t2 VALUES (3,'t'); 2827 2828INSERT INTO t3 VALUES (1,5,'x'); 2829INSERT INTO t3 VALUES (1,6,'y'); 2830INSERT INTO t3 VALUES (2,5,'xx'); 2831INSERT INTO t3 VALUES (2,6,'yy'); 2832INSERT INTO t3 VALUES (2,7,'zz'); 2833INSERT INTO t3 VALUES (3,5,'xxx'); 2834 2835SELECT t2.key_a,foo 2836 FROM t1 INNER JOIN t2 ON t1.key_a = t2.key_a 2837 INNER JOIN t3 ON t1.key_a = t3.key_a 2838 WHERE t2.key_a=2 and key_b=5; 2839EXPLAIN SELECT t2.key_a,foo 2840 FROM t1 INNER JOIN t2 ON t1.key_a = t2.key_a 2841 INNER JOIN t3 ON t1.key_a = t3.key_a 2842 WHERE t2.key_a=2 and key_b=5; 2843 2844SELECT t2.key_a,foo 2845 FROM t1 INNER JOIN t2 ON t2.key_a = t1.key_a 2846 INNER JOIN t3 ON t1.key_a = t3.key_a 2847 WHERE t2.key_a=2 and key_b=5; 2848EXPLAIN SELECT t2.key_a,foo 2849 FROM t1 INNER JOIN t2 ON t2.key_a = t1.key_a 2850 INNER JOIN t3 ON t1.key_a = t3.key_a 2851 WHERE t2.key_a=2 and key_b=5; 2852 2853DROP TABLE t1,t2,t3; 2854 2855# 2856# Bug#15347 Wrong result of subselect when records cache and set functions 2857# are involved 2858# 2859create table t1 (f1 int); 2860insert into t1 values(1),(2); 2861create table t2 (f2 int, f3 int, key(f2)); 2862insert into t2 values(1,1),(2,2); 2863create table t3 (f4 int not null); 2864insert into t3 values (2),(2),(2); 2865select f1,(select count(*) from t2,t3 where f2=f1 and f3=f4) as count from t1; 2866drop table t1,t2,t3; 2867 2868# 2869# Bug #15633 Evaluation of Item_equal for non-const table caused wrong 2870# select result 2871# 2872create table t1 (f1 int unique); 2873create table t2 (f2 int unique); 2874create table t3 (f3 int unique); 2875insert into t1 values(1),(2); 2876insert into t2 values(1),(2); 2877insert into t3 values(1),(NULL); 2878select * from t3 where f3 is null; 2879select t2.f2 from t1 left join t2 on f1=f2 join t3 on f1=f3 where f1=1; 2880drop table t1,t2,t3; 2881 2882# 2883# Bug#15268 Unchecked null value caused server crash 2884# 2885create table t1(f1 char, f2 char not null); 2886insert into t1 values(null,'a'); 2887create table t2 (f2 char not null); 2888insert into t2 values('b'); 2889select * from t1 left join t2 on f1=t2.f2 where t1.f2='a'; 2890drop table t1,t2; 2891 2892# 2893# Bug#15538 unchecked table absense caused server crash. 2894# 2895--error 1064 2896select * from (select * left join t on f1=f2) tt; 2897 2898# 2899# Bug #16504: re-evaluation of Item_equal object after reading const table 2900# 2901 2902CREATE TABLE t1 (sku int PRIMARY KEY, pr int); 2903CREATE TABLE t2 (sku int PRIMARY KEY, sppr int, name varchar(255)); 2904 2905INSERT INTO t1 VALUES 2906 (10, 10), (20, 10), (30, 20), (40, 30), (50, 10), (60, 10); 2907 2908INSERT INTO t2 VALUES 2909 (10, 10, 'aaa'), (20, 10, 'bbb'), (30, 10, 'ccc'), (40, 20, 'ddd'), 2910 (50, 10, 'eee'), (60, 20, 'fff'), (70, 20, 'ggg'), (80, 30, 'hhh'); 2911 2912SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr 2913 FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku); 2914EXPLAIN 2915SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr 2916 FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku); 2917 2918 2919DROP TABLE t1,t2; 2920 2921# 2922# Bug#18712: Truncation problem (needs just documenting and test 2923# cases to prevent fixing this accidently. It is intended behaviour) 2924# 2925 2926SET SQL_MODE='NO_UNSIGNED_SUBTRACTION'; 2927CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL); 2928INSERT t1 SET i = 0; 2929UPDATE t1 SET i = -1; 2930SELECT * FROM t1; 2931UPDATE t1 SET i = CAST(i - 1 AS SIGNED); 2932SELECT * FROM t1; 2933UPDATE t1 SET i = i - 1; 2934SELECT * FROM t1; 2935DROP TABLE t1; 2936SET SQL_MODE=default; 2937 2938# BUG#17379 2939 2940create table t1 (a int); 2941insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 2942create table t2 (a int, b int, c int, e int, primary key(a,b,c)); 2943insert into t2 select A.a, B.a, C.a, C.a from t1 A, t1 B, t1 C; 2944analyze table t2; 2945select 'In next EXPLAIN, B.rows must be exactly 10:' Z; 2946 2947explain select * from t2 A, t2 B where A.a=5 and A.b=5 and A.C<5 2948 and B.a=5 and B.b=A.e and (B.b =1 or B.b = 3 or B.b=5); 2949drop table t1, t2; 2950 2951# 2952#Bug #18940: selection of optimal execution plan caused by equality 2953# propagation (the bug was fixed by the patch for bug #17379) 2954 2955CREATE TABLE t1 (a int PRIMARY KEY, b int, INDEX(b)); 2956INSERT INTO t1 VALUES (1, 3), (9,4), (7,5), (4,5), (6,2), 2957 (3,1), (5,1), (8,9), (2,2), (0,9); 2958 2959CREATE TABLE t2 (c int, d int, f int, INDEX(c,f)); 2960INSERT INTO t2 VALUES 2961 (1,0,0), (1,0,1), (2,0,0), (2,0,1), (3,0,0), (4,0,1), 2962 (5,0,0), (5,0,1), (6,0,0), (0,0,1), (7,0,0), (7,0,1), 2963 (0,0,0), (0,0,1), (8,0,0), (8,0,1), (9,0,0), (9,0,1); 2964 2965EXPLAIN 2966SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6; 2967EXPLAIN 2968SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6 AND a > 0; 2969 2970DROP TABLE t1, t2; 2971 2972# 2973# Bug #18895: BIT values cause joins to fail 2974# 2975create table t1 ( 2976 a int unsigned not null auto_increment primary key, 2977 b bit not null, 2978 c bit not null 2979); 2980 2981create table t2 ( 2982 a int unsigned not null auto_increment primary key, 2983 b bit not null, 2984 c int unsigned not null, 2985 d varchar(50) 2986); 2987 2988insert into t1 (b,c) values (0,1), (0,1); 2989insert into t2 (b,c) values (0,1); 2990 2991# Row 1 should succeed. Row 2 should fail. Both fail. 2992select t1.a, t1.b + 0, t1.c + 0, t2.a, t2.b + 0, t2.c, t2.d 2993from t1 left outer join t2 on t1.a = t2.c and t2.b <> 1 2994where t1.b <> 1 order by t1.a; 2995 2996drop table t1,t2; 2997 2998# 2999# Bug #20569: Garbage in DECIMAL results from some mathematical functions 3000# 3001SELECT 0.9888889889 * 1.011111411911; 3002 3003# 3004# Bug #10977: No warning issued if a column name is truncated 3005# 3006prepare stmt from 'select 1 as " a "'; 3007execute stmt; 3008 3009# 3010# Bug #21390: wrong estimate of rows after elimination of const tables 3011# 3012 3013CREATE TABLE t1 (a int NOT NULL PRIMARY KEY, b int NOT NULL); 3014INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4); 3015 3016CREATE TABLE t2 (c int NOT NULL, INDEX idx(c)); 3017INSERT INTO t2 VALUES 3018 (1), (1), (1), (1), (1), (1), (1), (1), 3019 (2), (2), (2), (2), 3020 (3), (3), 3021 (4); 3022 3023EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=1; 3024EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=4; 3025 3026DROP TABLE t1, t2; 3027 3028# 3029# No matches for a join after substitution of a const table 3030# 3031 3032CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, a int); 3033INSERT INTO t1 VALUES (1,2), (2,NULL), (3,2); 3034 3035CREATE TABLE t2 (b int, c INT, INDEX idx1(b)); 3036INSERT INTO t2 VALUES (2,1), (3,2); 3037 3038CREATE TABLE t3 (d int, e int, INDEX idx1(d)); 3039INSERT INTO t3 VALUES (2,10), (2,20), (1,30), (2,40), (2,50); 3040 3041EXPLAIN 3042SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id 3043 WHERE t1.id=2; 3044SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id 3045 WHERE t1.id=2; 3046 3047DROP TABLE t1,t2,t3; 3048 3049# 3050# Bug#20503: Server crash due to the ORDER clause isn't taken into account 3051# while space allocation 3052# 3053create table t1 (c1 varchar(1), c2 int, c3 int, c4 int, c5 int, c6 int, 3054c7 int, c8 int, c9 int, fulltext key (`c1`)); 3055select distinct match (`c1`) against ('z') , c2, c3, c4,c5, c6,c7, c8 3056 from t1 where c9=1 order by c2, c2; 3057drop table t1; 3058 3059# 3060# Bug #22735: no equality propagation for BETWEEN and IN with STRING arguments 3061# 3062 3063CREATE TABLE t1 (pk varchar(10) PRIMARY KEY, fk varchar(16)); 3064CREATE TABLE t2 (pk varchar(16) PRIMARY KEY, fk varchar(10)); 3065 3066INSERT INTO t1 VALUES 3067 ('d','dddd'), ('i','iii'), ('a','aa'), ('b','bb'), ('g','gg'), 3068 ('e','eee'), ('c','cccc'), ('h','hhh'), ('j','jjj'), ('f','fff'); 3069INSERT INTO t2 VALUES 3070 ('jjj', 'j'), ('cc','c'), ('ccc','c'), ('aaa', 'a'), ('jjjj','j'), 3071 ('hhh','h'), ('gg','g'), ('fff','f'), ('ee','e'), ('ffff','f'), 3072 ('bbb','b'), ('ff','f'), ('cccc','c'), ('dddd','d'), ('jj','j'), 3073 ('aaaa','a'), ('bb','b'), ('eeee','e'), ('aa','a'), ('hh','h'); 3074 3075EXPLAIN SELECT t2.* 3076 FROM t1 JOIN t2 ON t2.fk=t1.pk 3077 WHERE t2.fk < 'c' AND t2.pk=t1.fk; 3078EXPLAIN SELECT t2.* 3079 FROM t1 JOIN t2 ON t2.fk=t1.pk 3080 WHERE t2.fk BETWEEN 'a' AND 'b' AND t2.pk=t1.fk; 3081EXPLAIN SELECT t2.* 3082 FROM t1 JOIN t2 ON t2.fk=t1.pk 3083 WHERE t2.fk IN ('a','b') AND t2.pk=t1.fk; 3084 3085DROP TABLE t1,t2; 3086 3087# 3088# Bug #22367: Optimizer uses ref join type instead of eq_ref for simple 3089# join on strings 3090# 3091CREATE TABLE t1 (a int, b varchar(20) NOT NULL, PRIMARY KEY(a)); 3092CREATE TABLE t2 (a int, b varchar(20) NOT NULL, 3093 PRIMARY KEY (a), UNIQUE KEY (b)); 3094INSERT INTO t1 VALUES (1,'a'),(2,'b'),(3,'c'); 3095INSERT INTO t2 VALUES (1,'a'),(2,'b'),(3,'c'); 3096 3097EXPLAIN SELECT t1.a FROM t1 LEFT JOIN t2 ON t2.b=t1.b WHERE t1.a=3; 3098 3099DROP TABLE t1,t2; 3100 3101# 3102# Bug #19579: predicates that become sargable after reading const tables 3103# are not taken into account by optimizer 3104# 3105 3106CREATE TABLE t1(id int PRIMARY KEY, b int, e int); 3107CREATE TABLE t2(i int, a int, INDEX si(i), INDEX ai(a)); 3108CREATE TABLE t3(a int PRIMARY KEY, c char(4), INDEX ci(c)); 3109 3110INSERT INTO t1 VALUES 3111 (1,10,19), (2,20,22), (4,41,42), (9,93,95), (7, 77,79), 3112 (6,63,67), (5,55,58), (3,38,39), (8,81,89); 3113INSERT INTO t2 VALUES 3114 (21,210), (41,410), (82,820), (83,830), (84,840), 3115 (65,650), (51,510), (37,370), (94,940), (76,760), 3116 (22,220), (33,330), (40,400), (95,950), (38,380), 3117 (67,670), (88,880), (57,570), (96,960), (97,970); 3118INSERT INTO t3 VALUES 3119 (210,'bb'), (950,'ii'), (400,'ab'), (500,'ee'), (220,'gg'), 3120 (440,'gg'), (310,'eg'), (380,'ee'), (840,'bb'), (830,'ff'), 3121 (230,'aa'), (960,'ii'), (410,'aa'), (510,'ee'), (290,'bb'), 3122 (450,'gg'), (320,'dd'), (390,'hh'), (850,'jj'), (860,'ff'); 3123 3124EXPLAIN 3125SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3 3126 WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND 3127 t3.a=t2.a AND t3.c IN ('bb','ee'); 3128EXPLAIN 3129SELECT t3.a FROM t1,t2,t3 3130 WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND 3131 t3.a=t2.a AND t3.c IN ('bb','ee') ; 3132 3133EXPLAIN 3134SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3 3135 WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND 3136 t3.c IN ('bb','ee'); 3137EXPLAIN 3138SELECT t3.a FROM t1,t2,t3 3139 WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND 3140 t3.c IN ('bb','ee'); 3141 3142DROP TABLE t1,t2,t3; 3143 3144# 3145# Bug#25172: Not checked buffer size leads to a server crash 3146# 3147CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int); 3148CREATE TABLE t2 ( f11 int PRIMARY KEY ); 3149INSERT INTO t1 VALUES (1,1,1,0,0,0,0),(2,1,1,3,8,1,0),(3,1,1,4,12,1,0); 3150INSERT INTO t2 VALUES (62); 3151SELECT * FROM t1 LEFT JOIN t2 ON f11 = t1.checked_out GROUP BY f1 ORDER BY f2, f3, f4, f5 LIMIT 0, 1; 3152DROP TABLE t1, t2; 3153 3154# 3155# Bug#6298: LIMIT #, -1 no longer works to set start with no end limit 3156# 3157 3158--disable_warnings 3159DROP TABLE IF EXISTS t1; 3160--enable_warnings 3161 3162CREATE TABLE t1(a int); 3163INSERT into t1 values (1), (2), (3); 3164 3165# LIMIT N, -1 was accepted by accident in 4.0, but was not intended. 3166# This test verifies that this illegal construct is now properly detected. 3167 3168--error ER_PARSE_ERROR 3169SELECT * FROM t1 LIMIT 2, -1; 3170 3171DROP TABLE t1; 3172 3173# 3174# 25407: wrong estimate of NULL keys for unique indexes 3175# 3176 3177CREATE TABLE t1 ( 3178 ID_with_null int NULL, 3179 ID_better int NOT NULL, 3180 INDEX idx1 (ID_with_null), 3181 INDEX idx2 (ID_better) 3182); 3183 3184INSERT INTO t1 VALUES (1,1), (2,1), (null,3), (null,3), (null,3), (null,3); 3185INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; 3186INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; 3187INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; 3188INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; 3189INSERT INTO t1 SELECT * FROM t1 WHERE ID_with_null IS NULL; 3190 3191SELECT COUNT(*) FROM t1 WHERE ID_with_null IS NULL; 3192SELECT COUNT(*) FROM t1 WHERE ID_better=1; 3193 3194EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL; 3195 3196DROP INDEX idx1 ON t1; 3197CREATE UNIQUE INDEX idx1 ON t1(ID_with_null); 3198 3199EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL; 3200 3201DROP TABLE t1; 3202 3203CREATE TABLE t1 ( 3204 ID1_with_null int NULL, 3205 ID2_with_null int NULL, 3206 ID_better int NOT NULL, 3207 INDEX idx1 (ID1_with_null, ID2_with_null), 3208 INDEX idx2 (ID_better) 3209); 3210 3211INSERT INTO t1 VALUES (1,1,1), (2,2,1), (3,null,3), (null,3,3), (null,null,3), 3212 (3,null,3), (null,3,3), (null,null,3), (3,null,3), (null,3,3), (null,null,3); 3213 3214INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; 3215INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; 3216INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; 3217INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; 3218INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; 3219INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; 3220 3221SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null=3; 3222SELECT COUNT(*) FROM t1 WHERE ID1_with_null=3 AND ID2_with_null IS NULL; 3223SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null IS NULL; 3224SELECT COUNT(*) FROM t1 WHERE ID_better=1; 3225 3226EXPLAIN SELECT * FROM t1 3227 WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ; 3228EXPLAIN SELECT * FROM t1 3229 WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null=3 IS NULL ; 3230EXPLAIN SELECT * FROM t1 3231 WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL; 3232 3233DROP INDEX idx1 ON t1; 3234CREATE UNIQUE INDEX idx1 ON t1(ID1_with_null,ID2_with_null); 3235 3236EXPLAIN SELECT * FROM t1 3237 WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ; 3238EXPLAIN SELECT * FROM t1 3239 WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null IS NULL ; 3240EXPLAIN SELECT * FROM t1 3241 WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL; 3242EXPLAIN SELECT * FROM t1 3243 WHERE ID_better=1 AND ID1_with_null IS NULL AND 3244 (ID2_with_null=1 OR ID2_with_null=2); 3245 3246DROP TABLE t1; 3247 3248# 3249# Bug #22344: InnoDB keys act strange on datetime vs timestamp comparison 3250# 3251CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts)); 3252INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00"); 3253ANALYZE TABLE t1; 3254 3255CREATE TABLE t2 (a INT, dt1 DATETIME, dt2 DATETIME, PRIMARY KEY (a)); 3256INSERT INTO t2 VALUES (30, "2006-01-01 00:00:00", "2999-12-31 00:00:00"); 3257INSERT INTO t2 SELECT a+1,dt1,dt2 FROM t2; 3258ANALYZE TABLE t2; 3259 3260EXPLAIN 3261SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30 3262 AND t1.ts BETWEEN t2.dt1 AND t2.dt2 3263 AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31"; 3264 3265SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30 3266 AND t1.ts BETWEEN t2.dt1 AND t2.dt2 3267 AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31"; 3268 3269DROP TABLE t1,t2; 3270# Bug #22026: Warning when using IF statement and large unsigned bigint 3271# 3272 3273create table t1 (a bigint unsigned); 3274insert into t1 values 3275 (if(1, 9223372036854775808, 1)), 3276 (case when 1 then 9223372036854775808 else 1 end), 3277 (coalesce(9223372036854775808, 1)); 3278select * from t1; 3279drop table t1; 3280create table t1 select 3281 if(1, 9223372036854775808, 1) i, 3282 case when 1 then 9223372036854775808 else 1 end c, 3283 coalesce(9223372036854775808, 1) co; 3284show create table t1; 3285drop table t1; 3286# Ensure we handle big values properly 3287select 3288 if(1, cast(1111111111111111111 as unsigned), 1) i, 3289 case when 1 then cast(1111111111111111111 as unsigned) else 1 end c, 3290 coalesce(cast(1111111111111111111 as unsigned), 1) co; 3291 3292# 3293# Bug #22971: indexes on text columns are ignored for ref accesses 3294# 3295 3296CREATE TABLE t1 (name varchar(255)); 3297CREATE TABLE t2 (name varchar(255), n int, KEY (name(3))); 3298INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa '), ('aa'); 3299INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc ',3); 3300INSERT INTO t2 VALUES (concat('cc ', 0x06), 4); 3301INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7); 3302SELECT * FROM t2; 3303SELECT * FROM t2 ORDER BY name; 3304SELECT name, LENGTH(name), n FROM t2 ORDER BY name; 3305 3306EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; 3307SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; 3308EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; 3309SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; 3310EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; 3311SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; 3312EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; 3313SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; 3314 3315DROP TABLE t1,t2; 3316 3317CREATE TABLE t1 (name text); 3318CREATE TABLE t2 (name text, n int, KEY (name(3))); 3319INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa '), ('aa'); 3320INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc ',3); 3321INSERT INTO t2 VALUES (concat('cc ', 0x06), 4); 3322INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7); 3323SELECT * FROM t2; 3324SELECT * FROM t2 ORDER BY name; 3325SELECT name, LENGTH(name), n FROM t2 ORDER BY name; 3326 3327EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; 3328SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; 3329EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; 3330SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; 3331EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; 3332SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; 3333EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; 3334SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; 3335 3336DROP TABLE t1,t2; 3337 3338 3339# 3340# Bug #26963: join with predicates that contain fields from equalities evaluated 3341# to constants after constant table substitution 3342# 3343 3344CREATE TABLE t1 ( 3345 access_id int NOT NULL default '0', 3346 name varchar(20) default NULL, 3347 rank int NOT NULL default '0', 3348 KEY idx (access_id) 3349); 3350 3351CREATE TABLE t2 ( 3352 faq_group_id int NOT NULL default '0', 3353 faq_id int NOT NULL default '0', 3354 access_id int default NULL, 3355 UNIQUE KEY idx1 (faq_id), 3356 KEY idx2 (faq_group_id,faq_id) 3357); 3358 3359INSERT INTO t1 VALUES 3360 (1,'Everyone',2),(2,'Help',3),(3,'Technical Support',1),(4,'Chat User',4); 3361INSERT INTO t2 VALUES 3362 (261,265,1),(490,494,1); 3363 3364 3365SELECT t2.faq_id 3366 FROM t1 INNER JOIN t2 IGNORE INDEX (idx1) 3367 ON (t1.access_id = t2.access_id) 3368 LEFT JOIN t2 t 3369 ON (t.faq_group_id = t2.faq_group_id AND 3370 find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4')) 3371 WHERE 3372 t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265); 3373 3374SELECT t2.faq_id 3375 FROM t1 INNER JOIN t2 3376 ON (t1.access_id = t2.access_id) 3377 LEFT JOIN t2 t 3378 ON (t.faq_group_id = t2.faq_group_id AND 3379 find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4')) 3380 WHERE 3381 t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265); 3382 3383DROP TABLE t1,t2; 3384 3385 3386# 3387# Bug #19372: Optimizer does not use index anymore when WHERE index NOT IN 3388# () is added 3389# 3390CREATE TABLE t1 (a INT, b INT, KEY inx (b,a)); 3391 3392INSERT INTO t1 VALUES (1,1), (1,2), (1,3), (1,4), (1,5), (1, 6), (1,7); 3393EXPLAIN SELECT COUNT(*) FROM t1 f1 INNER JOIN t1 f2 3394 ON ( f1.b=f2.b AND f1.a<f2.a ) 3395 WHERE 1 AND f1.b NOT IN (100,2232,3343,51111); 3396DROP TABLE t1; 3397 3398# 3399# Bug #27352: Incorrect result of nested selects instead of error reporting 3400# 3401 3402CREATE TABLE t1 (c1 INT, c2 INT); 3403INSERT INTO t1 VALUES (1,11), (2,22), (2,22); 3404 3405let $n= 29; 3406let $q= COUNT(c2); 3407while ($n) 3408{ 3409 let $q= (SELECT $q); 3410 dec $n; 3411} 3412--disable_warnings 3413eval EXPLAIN SELECT c1 FROM t1 WHERE $q > 0; 3414--enable_warnings 3415 3416let $n= 64; 3417let $q= COUNT(c2); 3418while ($n) 3419{ 3420 let $q= (SELECT $q); 3421 dec $n; 3422} 3423--error ER_TOO_HIGH_LEVEL_OF_NESTING_FOR_SELECT 3424eval EXPLAIN SELECT c1 FROM t1 WHERE $q > 0; 3425 3426DROP TABLE t1; 3427 3428# 3429# Bug #30396: crash for a join with equalities and sargable predicates 3430# in disjunctive parts of the WHERE condition 3431# 3432 3433CREATE TABLE t1 ( 3434 c1 int(11) NOT NULL AUTO_INCREMENT, 3435 c2 varchar(1000) DEFAULT NULL, 3436 c3 bigint(20) DEFAULT NULL, 3437 c4 bigint(20) DEFAULT NULL, 3438 PRIMARY KEY (c1) 3439); 3440 3441EXPLAIN EXTENDED 3442SELECT join_2.c1 3443FROM 3444 t1 AS join_0, 3445 t1 AS join_1, 3446 t1 AS join_2, 3447 t1 AS join_3, 3448 t1 AS join_4, 3449 t1 AS join_5, 3450 t1 AS join_6, 3451 t1 AS join_7 3452WHERE 3453 join_0.c1=join_1.c1 AND 3454 join_1.c1=join_2.c1 AND 3455 join_2.c1=join_3.c1 AND 3456 join_3.c1=join_4.c1 AND 3457 join_4.c1=join_5.c1 AND 3458 join_5.c1=join_6.c1 AND 3459 join_6.c1=join_7.c1 3460 OR 3461 join_0.c2 < '?' AND 3462 join_1.c2 < '?' AND 3463 join_2.c2 > '?' AND 3464 join_2.c2 < '!' AND 3465 join_3.c2 > '?' AND 3466 join_4.c2 = '?' AND 3467 join_5.c2 <> '?' AND 3468 join_6.c2 <> '?' AND 3469 join_7.c2 >= '?' AND 3470 join_0.c1=join_1.c1 AND 3471 join_1.c1=join_2.c1 AND 3472 join_2.c1=join_3.c1 AND 3473 join_3.c1=join_4.c1 AND 3474 join_4.c1=join_5.c1 AND 3475 join_5.c1=join_6.c1 AND 3476 join_6.c1=join_7.c1 3477GROUP BY 3478 join_3.c1, 3479 join_2.c1, 3480 join_7.c1, 3481 join_1.c1, 3482 join_0.c1; 3483 3484SHOW WARNINGS; 3485 3486DROP TABLE t1; 3487 3488# 3489# Bug #27695: Misleading warning when declaring all space column names and 3490# truncation of one-space column names to zero length names. 3491# 3492 3493--disable_ps_protocol 3494SELECT 1 AS ` `; 3495SELECT 1 AS ` `; 3496SELECT 1 AS ` x`; 3497--enable_ps_protocol 3498 3499--error 1166 3500CREATE VIEW v1 AS SELECT 1 AS ``; 3501 3502--error 1166 3503CREATE VIEW v1 AS SELECT 1 AS ` `; 3504 3505--error 1166 3506CREATE VIEW v1 AS SELECT 1 AS ` `; 3507 3508--error 1166 3509CREATE VIEW v1 AS SELECT (SELECT 1 AS ` `); 3510 3511CREATE VIEW v1 AS SELECT 1 AS ` x`; 3512SELECT `x` FROM v1; 3513 3514--error 1166 3515ALTER VIEW v1 AS SELECT 1 AS ` `; 3516 3517DROP VIEW v1; 3518 3519# 3520# Bug#31800: Date comparison fails with timezone and slashes for greater 3521# than comparison 3522# 3523 3524# On DATETIME-like literals with trailing garbage, BETWEEN fudged in a 3525# DATETIME comparator, while greater/less-than used bin-string comparisons. 3526# Should correctly be compared as DATE or DATETIME, but throw a warning: 3527 3528select str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT' 3529 and '2007/10/20 00:00:00 GMT'; 3530select str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6'; 3531select str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6'; 3532 3533# We have all we need -- and trailing garbage: 3534# (leaving out a leading zero in first example to prove it's a 3535# value-comparison, not a string-comparison!) 3536select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6'; 3537select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6'; 3538select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6'; 3539select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6'; 3540# no time at all: 3541select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6'; 3542# partial time: 3543select str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6'; 3544# fail, different second part: 3545select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6'; 3546# correct syntax, no trailing nonsense -- this one must throw no warning: 3547select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34:56'; 3548# no warning, but failure (different hour parts): 3549select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 12:00:00'; 3550# succeed: 3551select str_to_date('2007-10-01 12','%Y-%m-%d %H') = '2007-10-01 12:00:00'; 3552# succeed, but warn for "trailing garbage" (":34"): 3553select str_to_date('2007-10-01 12:34','%Y-%m-%d %H') = '2007-10-01 12:00:00'; 3554# invalid date (Feb 30) succeeds 3555select str_to_date('2007-02-30 12:34','%Y-%m-%d %H:%i') = '2007-02-30 12:34'; 3556# 0-day for both, just works in default SQL mode. 3557select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'; 3558# 0-day, succeed 3559select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00' 3560 and '2007/10/20 00:00:00'; 3561set SQL_MODE=TRADITIONAL; 3562# 0-day throws warning in traditional mode, and fails 3563select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'; 3564select str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'; 3565# different code-path: get_datetime_value() with 0-day 3566select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34'; 3567select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01' 3568 and '2007/10/20'; 3569set SQL_MODE=DEFAULT; 3570select str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20'; 3571select str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20'; 3572select str_to_date('','%Y-%m-%d %H:%i') = '2007-10-01 12:34'; 3573select str_to_date(NULL,'%Y-%m-%d %H:%i') = '2007-10-01 12:34'; 3574select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = ''; 3575 3576select str_to_date('1','%Y-%m-%d') = '1'; 3577select str_to_date('1','%Y-%m-%d') = '1'; 3578select str_to_date('','%Y-%m-%d') = ''; 3579 3580# these three should work! 3581select str_to_date('1000-01-01','%Y-%m-%d') between '0000-00-00' and NULL; 3582select str_to_date('1000-01-01','%Y-%m-%d') between NULL and '2000-00-00'; 3583select str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL; 3584 3585# 3586# Bug #30666: Incorrect order when using range conditions on 2 tables or more 3587# 3588 3589CREATE TABLE t1 (c11 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY); 3590CREATE TABLE t2 (c21 INT UNSIGNED NOT NULL, 3591 c22 INT DEFAULT NULL, 3592 KEY(c21, c22)); 3593CREATE TABLE t3 (c31 INT UNSIGNED NOT NULL DEFAULT 0, 3594 c32 INT DEFAULT NULL, 3595 c33 INT NOT NULL, 3596 c34 INT UNSIGNED DEFAULT 0, 3597 KEY (c33, c34, c32)); 3598 3599INSERT INTO t1 values (),(),(),(),(); 3600INSERT INTO t2 SELECT a.c11, b.c11 FROM t1 a, t1 b; 3601INSERT INTO t3 VALUES (1, 1, 1, 0), 3602 (2, 2, 0, 0), 3603 (3, 3, 1, 0), 3604 (4, 4, 0, 0), 3605 (5, 5, 1, 0); 3606 3607# Show that ORDER BY produces the correct results order 3608SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND 3609 t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND 3610 t3.c33 = 1 AND t2.c22 in (1, 3) 3611 ORDER BY c32; 3612 3613# Show that ORDER BY DESC produces the correct results order 3614SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND 3615 t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND 3616 t3.c33 = 1 AND t2.c22 in (1, 3) 3617 ORDER BY c32 DESC; 3618 3619DROP TABLE t1, t2, t3; 3620########################################################################### 3621 3622--echo 3623--echo # 3624--echo # Bug#30736: Row Size Too Large Error Creating a Table and 3625--echo # Inserting Data. 3626--echo # 3627 3628--disable_warnings 3629DROP TABLE IF EXISTS t1; 3630DROP TABLE IF EXISTS t2; 3631--enable_warnings 3632 3633--echo 3634 3635CREATE TABLE t1( 3636 c1 DECIMAL(10, 2), 3637 c2 FLOAT); 3638 3639--echo 3640 3641INSERT INTO t1 VALUES (0, 1), (2, 3), (4, 5); 3642 3643--echo 3644 3645CREATE TABLE t2( 3646 c3 DECIMAL(10, 2)) 3647 SELECT 3648 c1 * c2 AS c3 3649 FROM t1; 3650 3651--echo 3652 3653SELECT * FROM t1; 3654 3655--echo 3656 3657SELECT * FROM t2; 3658 3659--echo 3660 3661DROP TABLE t1; 3662DROP TABLE t2; 3663 3664--echo 3665 3666########################################################################### 3667 3668# 3669# Bug #32335: Error on BIGINT > NULL + 1 3670# 3671 3672CREATE TABLE t1 (c1 BIGINT NOT NULL); 3673INSERT INTO t1 (c1) VALUES (1); 3674SELECT * FROM t1 WHERE c1 > NULL + 1; 3675DROP TABLE t1; 3676 3677--echo 3678 3679########################################################################### 3680 3681# 3682# Bug #33764: Wrong result with IN(), CONCAT() and implicit type conversion 3683# 3684 3685CREATE TABLE t1 (a VARCHAR(10) NOT NULL PRIMARY KEY); 3686INSERT INTO t1 (a) VALUES ('foo0'), ('bar0'), ('baz0'); 3687SELECT * FROM t1 WHERE a IN (CONCAT('foo', 0), 'bar'); 3688DROP TABLE t1; 3689 3690# 3691# Bug #32942 now() - interval '7200' second is NOT pre-calculated, causing "full table scan" 3692# 3693 3694CREATE TABLE t1 (a INT, b INT); 3695CREATE TABLE t2 (a INT, c INT, KEY(a)); 3696 3697INSERT INTO t1 VALUES (1, 1), (2, 2); 3698INSERT INTO t2 VALUES (1, 1), (1, 2), (1, 3), (1, 4), (1, 5), 3699 (2, 1), (2, 2), (2, 3), (2, 4), (2, 5), 3700 (3, 1), (3, 2), (3, 3), (3, 4), (3, 5), 3701 (4, 1), (4, 2), (4, 3), (4, 4), (4, 5); 3702 3703FLUSH STATUS; 3704SELECT DISTINCT b FROM t1 LEFT JOIN t2 USING(a) WHERE c <= 3; 3705SHOW STATUS LIKE 'Handler_read%'; 3706DROP TABLE t1, t2; 3707 3708# 3709# Bug#40953 SELECT query throws "ERROR 1062 (23000): Duplicate entry..." error 3710# 3711CREATE TABLE t1 (f1 bigint(20) NOT NULL default '0', 3712 f2 int(11) NOT NULL default '0', 3713 f3 bigint(20) NOT NULL default '0', 3714 f4 varchar(255) NOT NULL default '', 3715 PRIMARY KEY (f1), 3716 KEY key1 (f4), 3717 KEY key2 (f2)); 3718CREATE TABLE t2 (f1 int(11) NOT NULL default '0', 3719 f2 enum('A1','A2','A3') NOT NULL default 'A1', 3720 f3 int(11) NOT NULL default '0', 3721 PRIMARY KEY (f1), 3722 KEY key1 (f3)); 3723CREATE TABLE t3 (f1 bigint(20) NOT NULL default '0', 3724 f2 datetime NOT NULL default '1980-01-01 00:00:00', 3725 PRIMARY KEY (f1)); 3726 3727insert into t1 values (1, 1, 1, 'abc'); 3728insert into t1 values (2, 1, 2, 'def'); 3729insert into t1 values (3, 1, 2, 'def'); 3730insert into t2 values (1, 'A1', 1); 3731insert into t3 values (1, '1980-01-01'); 3732 3733SELECT a.f3, cr.f4, count(*) count 3734FROM t2 a 3735STRAIGHT_JOIN t1 cr ON cr.f2 = a.f1 3736LEFT JOIN 3737(t1 cr2 3738 JOIN t3 ae2 ON cr2.f3 = ae2.f1 3739) ON a.f1 = cr2.f2 AND ae2.f2 < now() - INTERVAL 7 DAY AND 3740cr.f4 = cr2.f4 3741GROUP BY a.f3, cr.f4; 3742 3743drop table t1, t2, t3; 3744 3745 3746# 3747# Bug #40925: Equality propagation takes non indexed attribute 3748# 3749 3750CREATE TABLE t1 (a INT KEY, b INT); 3751INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4); 3752 3753EXPLAIN EXTENDED SELECT a, b FROM t1 WHERE a > 1 AND a = b LIMIT 2; 3754EXPLAIN EXTENDED SELECT a, b FROM t1 WHERE a > 1 AND b = a LIMIT 2; 3755 3756DROP TABLE t1; 3757 3758 3759--echo # 3760--echo # Bug#47019: Assertion failed: 0, file .\rt_mbr.c, line 138 when 3761--echo # forcing a spatial index 3762--echo # 3763CREATE TABLE t1(a LINESTRING NOT NULL, SPATIAL KEY(a)); 3764INSERT INTO t1 VALUES 3765 (GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1)')), 3766 (GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1)')); 3767EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2; 3768SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2; 3769EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a); 3770SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a); 3771DROP TABLE t1; 3772 3773 3774--echo # 3775--echo # Bug #48291 : crash with row() operator,select into @var, and 3776--echo # subquery returning multiple rows 3777--echo # 3778 3779CREATE TABLE t1(a INT); 3780INSERT INTO t1 VALUES (2),(3); 3781 3782--echo # Should not crash 3783--error ER_SUBQUERY_NO_1_ROW 3784SELECT 1 FROM t1 WHERE a <> 1 AND NOT 3785ROW(1,a) <=> ROW(1,(SELECT 1 FROM t1)) 3786INTO @var0; 3787 3788DROP TABLE t1; 3789 3790--echo # 3791--echo # Bug #48458: simple query tries to allocate enormous amount of 3792--echo # memory 3793--echo # 3794 3795CREATE TABLE t1(a INT NOT NULL, b YEAR); 3796INSERT INTO t1 VALUES (); 3797CREATE TABLE t2(c INT); 3798--echo # Should not err out because of out-of-memory 3799SELECT 1 FROM t2 JOIN t1 ON 1=1 3800 WHERE a != '1' AND NOT a >= b OR NOT ROW(b,a )<> ROW(a,a); 3801DROP TABLE t1,t2; 3802 3803 3804--echo # 3805--echo # Bug #49199: Optimizer handles incorrectly: 3806--echo # field='const1' AND field='const2' in some cases 3807--echo 3808CREATE TABLE t1(a DATETIME NOT NULL); 3809INSERT INTO t1 VALUES('2001-01-01'); 3810SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; 3811EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; 3812DROP TABLE t1; 3813 3814CREATE TABLE t1(a DATE NOT NULL); 3815INSERT INTO t1 VALUES('2001-01-01'); 3816SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; 3817EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; 3818DROP TABLE t1; 3819 3820CREATE TABLE t1(a TIMESTAMP NOT NULL NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); 3821INSERT INTO t1 VALUES('2001-01-01'); 3822SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; 3823EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00'; 3824DROP TABLE t1; 3825 3826CREATE TABLE t1(a DATETIME NOT NULL, b DATE NOT NULL); 3827INSERT INTO t1 VALUES('2001-01-01', '2001-01-01'); 3828SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00'; 3829EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00'; 3830DROP TABLE t1; 3831 3832CREATE TABLE t1(a DATETIME NOT NULL, b VARCHAR(20) NOT NULL); 3833INSERT INTO t1 VALUES('2001-01-01', '2001-01-01'); 3834SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00'; 3835EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00'; 3836 3837SELECT * FROM t1 WHERE a='2001-01-01 00:00:00' AND a=b AND b='2001-01-01'; 3838EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01 00:00:00' AND a=b AND b='2001-01-01'; 3839DROP TABLE t1; 3840 3841CREATE TABLE t1(a DATETIME NOT NULL, b DATE NOT NULL); 3842INSERT INTO t1 VALUES('2001-01-01', '2001-01-01'); 3843SELECT x.a, y.a, z.a FROM t1 x 3844 JOIN t1 y ON x.a=y.a 3845 JOIN t1 z ON y.a=z.a 3846 WHERE x.a='2001-01-01' AND z.a='2001-01-01 00:00:00'; 3847EXPLAIN EXTENDED SELECT x.a, y.a, z.a FROM t1 x 3848 JOIN t1 y ON x.a=y.a 3849 JOIN t1 z ON y.a=z.a 3850 WHERE x.a='2001-01-01' AND z.a='2001-01-01 00:00:00'; 3851DROP TABLE t1; 3852 3853 3854--echo # 3855--echo # Bug #49897: crash in ptr_compare when char(0) NOT NULL 3856--echo # column is used for ORDER BY 3857--echo # 3858SET @old_sort_buffer_size= @@session.sort_buffer_size; 3859SET @@sort_buffer_size= 40000; 3860 3861CREATE TABLE t1(a CHAR(0) NOT NULL); 3862--disable_warnings 3863INSERT INTO t1 VALUES (0), (0), (0); 3864--enable_warnings 3865INSERT INTO t1 SELECT t11.a FROM t1 t11, t1 t12; 3866INSERT INTO t1 SELECT t11.a FROM t1 t11, t1 t12; 3867INSERT INTO t1 SELECT t11.a FROM t1 t11, t1 t12; 3868EXPLAIN SELECT a FROM t1 ORDER BY a; 3869--disable_result_log 3870SELECT a FROM t1 ORDER BY a; 3871--enable_result_log 3872DROP TABLE t1; 3873 3874CREATE TABLE t1(a CHAR(0) NOT NULL, b CHAR(0) NOT NULL, c int); 3875--disable_warnings 3876INSERT INTO t1 VALUES (0, 0, 0), (0, 0, 2), (0, 0, 1); 3877--enable_warnings 3878INSERT INTO t1 SELECT t11.a, t11.b, t11.c FROM t1 t11, t1 t12; 3879INSERT INTO t1 SELECT t11.a, t11.b, t11.c FROM t1 t11, t1 t12; 3880INSERT INTO t1 SELECT t11.a, t11.b, t11.c FROM t1 t11, t1 t12; 3881EXPLAIN SELECT a FROM t1 ORDER BY a LIMIT 5; 3882SELECT a FROM t1 ORDER BY a LIMIT 5; 3883EXPLAIN SELECT * FROM t1 ORDER BY a, b LIMIT 5; 3884SELECT * FROM t1 ORDER BY a, b LIMIT 5; 3885EXPLAIN SELECT * FROM t1 ORDER BY a, b, c LIMIT 5; 3886SELECT * FROM t1 ORDER BY a, b, c LIMIT 5; 3887EXPLAIN SELECT * FROM t1 ORDER BY c, a LIMIT 5; 3888SELECT * FROM t1 ORDER BY c, a LIMIT 5; 3889 3890SET @@sort_buffer_size= @old_sort_buffer_size; 3891DROP TABLE t1; 3892 3893 3894--echo End of 5.0 tests 3895 3896# 3897# Bug #30639: limit offset,rowcount wraps when rowcount >= 2^32 in windows 3898# 3899create table t1(a INT, KEY (a)); 3900INSERT INTO t1 VALUES (1),(2),(3),(4),(5); 3901SELECT a FROM t1 ORDER BY a LIMIT 2; 3902SELECT a FROM t1 ORDER BY a LIMIT 2,4294967296; 3903SELECT a FROM t1 ORDER BY a LIMIT 2,4294967297; 3904DROP TABLE t1; 3905 3906# 3907# Bug #37936: ASSERT_COLUMN_MARKED_FOR_WRITE in Field_datetime::store , 3908# Field_varstring::store 3909# 3910 3911CREATE TABLE A (date_key date); 3912 3913CREATE TABLE C ( 3914 pk int, 3915 int_nokey int, 3916 int_key int, 3917 date_key date NOT NULL, 3918 date_nokey date, 3919 varchar_key varchar(1) 3920); 3921 3922INSERT INTO C VALUES 3923(1,1,1,'0000-00-00',NULL,NULL), 3924(1,1,1,'0000-00-00',NULL,NULL); 3925 3926SELECT 1 FROM C WHERE pk > ANY (SELECT 1 FROM C); 3927 3928SELECT COUNT(DISTINCT 1) FROM C 3929 WHERE date_key = (SELECT 1 FROM A WHERE C.date_key IS NULL) GROUP BY pk; 3930SELECT date_nokey FROM C 3931 WHERE int_key IN (SELECT 1 FROM A) 3932 HAVING date_nokey = '10:41:7' 3933 ORDER BY date_key; 3934 3935DROP TABLE A,C; 3936 3937# 3938# Bug #42957: no results from 3939# select where .. (col=col and col=col) or ... (false expression) 3940# 3941CREATE TABLE t1 (a INT NOT NULL, b INT); 3942INSERT INTO t1 VALUES (1, 1); 3943EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND a=a) OR b > 2; 3944SELECT * FROM t1 WHERE (a=a AND a=a) OR b > 2; 3945DROP TABLE t1; 3946 3947CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, c INT NOT NULL); 3948EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND b=b AND c=c) OR b > 20; 3949EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND a=a AND b=b) OR b > 20; 3950EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND b=b AND a=a) OR b > 20; 3951DROP TABLE t1; 3952 3953 3954--echo # 3955--echo # Bug#45266: Uninitialized variable lead to an empty result. 3956--echo # 3957--disable_warnings 3958drop table if exists A,AA,B,BB; 3959CREATE TABLE `A` ( 3960 `pk` int(11) NOT NULL AUTO_INCREMENT, 3961 `date_key` date NOT NULL, 3962 `date_nokey` date NOT NULL, 3963 `datetime_key` datetime NOT NULL, 3964 `int_nokey` int(11) NOT NULL, 3965 `time_key` time NOT NULL, 3966 `time_nokey` time NOT NULL, 3967 PRIMARY KEY (`pk`), 3968 KEY `date_key` (`date_key`), 3969 KEY `time_key` (`time_key`), 3970 KEY `datetime_key` (`datetime_key`) 3971); 3972 3973CREATE TABLE `AA` ( 3974 `pk` int(11) NOT NULL AUTO_INCREMENT, 3975 `int_nokey` int(11) NOT NULL, 3976 `time_key` time NOT NULL, 3977 KEY `time_key` (`time_key`), 3978 PRIMARY KEY (`pk`) 3979); 3980 3981CREATE TABLE `B` ( 3982 `date_nokey` date NOT NULL, 3983 `date_key` date NOT NULL, 3984 `time_key` time NOT NULL, 3985 `datetime_nokey` datetime NOT NULL, 3986 `varchar_key` varchar(1) NOT NULL, 3987 KEY `date_key` (`date_key`), 3988 KEY `time_key` (`time_key`), 3989 KEY `varchar_key` (`varchar_key`) 3990); 3991 3992INSERT 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'); 3993 3994CREATE TABLE `BB` ( 3995 `pk` int(11) NOT NULL AUTO_INCREMENT, 3996 `int_nokey` int(11) NOT NULL, 3997 `date_key` date NOT NULL, 3998 `varchar_nokey` varchar(1) NOT NULL, 3999 `date_nokey` date NOT NULL, 4000 PRIMARY KEY (`pk`), 4001 KEY `date_key` (`date_key`) 4002); 4003 4004INSERT INTO `BB` VALUES (10,8,'0000-00-00','i','0000-00-00'),(11,0,'2005-08-18','','2005-08-18'); 4005# Test #1 4006SELECT table1 . `pk` AS field1 4007 FROM 4008 (BB AS table1 INNER JOIN 4009 (AA AS table2 STRAIGHT_JOIN A AS table3 4010 ON ( table3 . `date_key` = table2 . `pk` )) 4011 ON ( table3 . `datetime_key` = table2 . `int_nokey` )) 4012 WHERE ( table3 . `date_key` <= 4 AND table2 . `pk` = table1 . `varchar_nokey`) 4013 GROUP BY field1 ; 4014 4015SELECT table3 .`date_key` field1 4016 FROM 4017 B table1 LEFT JOIN B table3 JOIN 4018 (BB table6 JOIN A table7 ON table6 .`varchar_nokey`) 4019 ON table6 .`int_nokey` ON table6 .`date_key` 4020 WHERE NOT ( table1 .`varchar_key` AND table7 .`pk`) GROUP BY field1; 4021 4022# Test #2 4023SELECT table4 . `time_nokey` AS field1 FROM 4024 (AA AS table1 CROSS JOIN 4025 (AA AS table2 STRAIGHT_JOIN 4026 (B AS table3 STRAIGHT_JOIN A AS table4 4027 ON ( table4 . `date_key` = table3 . `time_key` )) 4028 ON ( table4 . `pk` = table3 . `date_nokey` )) 4029 ON ( table4 . `time_key` = table3 . `datetime_nokey` )) 4030 WHERE ( table4 . `time_key` < table1 . `time_key` AND 4031 table1 . `int_nokey` != 'f') 4032 GROUP BY field1 ORDER BY field1 , field1; 4033 4034SELECT table1 .`time_key` field2 FROM B table1 LEFT JOIN BB JOIN A table5 ON table5 .`date_nokey` ON table5 .`int_nokey` GROUP BY field2; 4035--enable_warnings 4036 4037drop table A,AA,B,BB; 4038--echo #end of test for bug#45266 4039 4040--echo # 4041--echo # Bug#33546: Slowdown on re-evaluation of constant expressions. 4042--echo # 4043CREATE TABLE t1 (a INT); 4044INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); 4045CREATE TABLE t2 (b INT); 4046INSERT INTO t2 VALUES (2); 4047SELECT * FROM t1 WHERE a = 1 + 1; 4048EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = 1 + 1; 4049SELECT * FROM t1 HAVING a = 1 + 1; 4050EXPLAIN EXTENDED SELECT * FROM t1 HAVING a = 1 + 1; 4051SELECT * FROM t1, t2 WHERE a = b + (1 + 1); 4052EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE a = b + (1 + 1); 4053SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1; 4054EXPLAIN EXTENDED SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1; 4055EXPLAIN EXTENDED SELECT * FROM t1 WHERE a > UNIX_TIMESTAMP('2009-03-10 00:00:00'); 4056 4057delimiter |; 4058CREATE FUNCTION f1() RETURNS INT DETERMINISTIC 4059BEGIN 4060 SET @cnt := @cnt + 1; 4061 RETURN 1; 4062END;| 4063delimiter ;| 4064 4065SET @cnt := 0; 4066SELECT * FROM t1 WHERE a = f1(); 4067SELECT @cnt; 4068EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = f1(); 4069DROP TABLE t1, t2; 4070DROP FUNCTION f1; 4071--echo # End of bug#33546 4072 4073--echo # 4074--echo # BUG#48052: Valgrind warning - uninitialized value in init_read_record() 4075--echo # 4076 4077--echo # Disable Index condition pushdown 4078--replace_column 1 # 4079SELECT @old_optimizer_switch:=@@optimizer_switch; 4080--disable_query_log 4081if (`select locate('index_condition_pushdown', @@optimizer_switch) > 0`) 4082{ 4083 set optimizer_switch='index_condition_pushdown=off'; 4084} 4085--enable_query_log 4086 4087CREATE TABLE t1 ( 4088 pk int(11) NOT NULL, 4089 i int(11) DEFAULT NULL, 4090 v varchar(1) DEFAULT NULL, 4091 PRIMARY KEY (pk) 4092); 4093 4094INSERT INTO t1 VALUES (2,7,'m'); 4095INSERT INTO t1 VALUES (3,9,'m'); 4096 4097SELECT v 4098FROM t1 4099WHERE NOT pk > 0 4100HAVING v <= 't' 4101ORDER BY pk; 4102 4103--echo # Restore old value for Index condition pushdown 4104SET SESSION optimizer_switch=@old_optimizer_switch; 4105 4106DROP TABLE t1; 4107 4108--echo # 4109--echo # Bug#49489 Uninitialized cache led to a wrong result. 4110--echo # 4111CREATE TABLE t1(c1 DOUBLE(5,4)); 4112INSERT INTO t1 VALUES (9.1234); 4113SELECT * FROM t1 WHERE c1 < 9.12345; 4114DROP TABLE t1; 4115--echo # End of test for bug#49489. 4116 4117 4118--echo # 4119--echo # Bug #49517: Inconsistent behavior while using 4120--echo # NULLable BIGINT and INT columns in comparison 4121--echo # 4122CREATE TABLE t1(a BIGINT UNSIGNED NOT NULL, b BIGINT NULL, c INT NULL); 4123INSERT INTO t1 VALUES(105, NULL, NULL); 4124SELECT * FROM t1 WHERE b < 102; 4125SELECT * FROM t1 WHERE c < 102; 4126SELECT * FROM t1 WHERE 102 < b; 4127SELECT * FROM t1 WHERE 102 < c; 4128DROP TABLE t1; 4129 4130 4131--echo # 4132--echo # Bug #54459: Assertion failed: param.sort_length, 4133--echo # file .\filesort.cc, line 149 (part II) 4134--echo # 4135CREATE TABLE t1(a ENUM('') NOT NULL); 4136INSERT INTO t1 VALUES (), (), (); 4137EXPLAIN SELECT 1 FROM t1 ORDER BY a COLLATE latin1_german2_ci; 4138SELECT 1 FROM t1 ORDER BY a COLLATE latin1_german2_ci; 4139DROP TABLE t1; 4140 4141 4142 4143--echo # 4144--echo # Bug #58422: Incorrect result when OUTER JOIN'ing 4145--echo # with an empty table 4146--echo # 4147 4148CREATE TABLE t_empty(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM; 4149CREATE TABLE t1(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM; 4150INSERT INTO t1 VALUES (1,1), (2,2), (3,3); 4151CREATE TABLE t2(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM; 4152INSERT INTO t2 VALUES (1,1), (2,2), (3,3); 4153 4154EXPLAIN 4155SELECT * 4156 FROM 4157 t1 4158 LEFT OUTER JOIN 4159 (t2 INNER JOIN t_empty ON TRUE) 4160 ON t1.pk=t2.pk 4161 WHERE t2.pk <> 2; 4162 4163SELECT * 4164 FROM 4165 t1 4166 LEFT OUTER JOIN 4167 (t2 INNER JOIN t_empty ON TRUE) 4168 ON t1.pk=t2.pk 4169 WHERE t2.pk <> 2; 4170 4171 4172EXPLAIN 4173SELECT * 4174 FROM 4175 t1 4176 LEFT OUTER JOIN 4177 (t2 CROSS JOIN t_empty) 4178 ON t1.pk=t2.pk 4179 WHERE t2.pk <> 2; 4180 4181SELECT * 4182 FROM 4183 t1 4184 LEFT OUTER JOIN 4185 (t2 CROSS JOIN t_empty) 4186 ON t1.pk=t2.pk 4187 WHERE t2.pk <> 2; 4188 4189 4190EXPLAIN 4191SELECT * 4192 FROM 4193 t1 4194 LEFT OUTER JOIN 4195 (t2 INNER JOIN t_empty ON t_empty.i=t2.i) 4196 ON t1.pk=t2.pk 4197 WHERE t2.pk <> 2; 4198 4199SELECT * 4200 FROM 4201 t1 4202 LEFT OUTER JOIN 4203 (t2 INNER JOIN t_empty ON t_empty.i=t2.i) 4204 ON t1.pk=t2.pk 4205 WHERE t2.pk <> 2; 4206 4207 4208 4209DROP TABLE t1,t2,t_empty; 4210 4211 4212--echo End of 5.1 tests 4213 4214--echo # 4215--echo # Bug#45227: Lost HAVING clause led to a wrong result. 4216--echo # 4217CREATE TABLE `CC` ( 4218 `int_nokey` int(11) NOT NULL, 4219 `int_key` int(11) NOT NULL, 4220 `varchar_key` varchar(1) NOT NULL, 4221 `varchar_nokey` varchar(1) NOT NULL, 4222 KEY `int_key` (`int_key`), 4223 KEY `varchar_key` (`varchar_key`) 4224); 4225INSERT INTO `CC` VALUES 4226(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' 4227,'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'), 4228(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' 4229,'x'); 4230EXPLAIN SELECT `varchar_nokey` G1 FROM CC WHERE `int_nokey` AND `int_key` <= 4 4231 HAVING G1 ORDER BY `varchar_key` LIMIT 6 ; 4232 4233SELECT `varchar_nokey` G1 FROM CC WHERE `int_nokey` AND `int_key` <= 4 4234 HAVING G1 ORDER BY `varchar_key` LIMIT 6 ; 4235 4236DROP TABLE CC; 4237--echo # End of test#45227 4238 4239--echo # 4240--echo # Bug#54515: Crash in opt_range.cc::get_best_group_min_max on 4241--echo # SELECT from VIEW with GROUP BY 4242--echo # 4243 4244CREATE TABLE t1 ( 4245 col_int_key int DEFAULT NULL, 4246 KEY int_key (col_int_key) 4247) ; 4248 4249INSERT INTO t1 VALUES (1),(2); 4250 4251CREATE VIEW view_t1 AS 4252 SELECT t1.col_int_key AS col_int_key 4253 FROM t1; 4254 4255SELECT col_int_key FROM view_t1 GROUP BY col_int_key; 4256 4257DROP VIEW view_t1; 4258DROP TABLE t1; 4259 4260--echo # End of test BUG#54515 4261 4262--echo # 4263--echo # Bug #57203 Assertion `field_length <= 255' failed. 4264--echo # 4265 4266SELECT coalesce((avg(distinct (geomfromtext("point(25379 -22010)"))))) 4267UNION ALL 4268SELECT coalesce((avg(distinct (geomfromtext("point(25379 -22010)"))))) 4269AS foo 4270; 4271 4272CREATE table t1(a text); 4273INSERT INTO t1 VALUES (''), (''); 4274SELECT avg(distinct(t1.a)) FROM t1, t1 t2 4275GROUP BY t2.a ORDER BY t1.a; 4276 4277DROP TABLE t1; 4278 4279--echo # End of test BUG#57203 4280 4281--echo # 4282--echo # Bug#63020: Function "format"'s 'locale' argument is not considered 4283--echo # when creating a "view' 4284--echo # 4285 4286CREATE TABLE t1 (f1 DECIMAL(10,2)); 4287INSERT INTO t1 VALUES (11.67),(17865.3),(12345678.92); 4288CREATE VIEW view_t1 AS SELECT FORMAT(f1,1,'sk_SK') AS f1 FROM t1; 4289SHOW CREATE VIEW view_t1; 4290SELECT * FROM view_t1; 4291 4292DROP TABLE t1; 4293DROP VIEW view_t1; 4294 4295--echo # End of test BUG#63020 4296 4297--echo # 4298--echo # Bug #13571700 TINYBLOB NOT NULL, CRASH IN PROTOCOL::NET_STORE_DATA 4299--echo # 4300 4301CREATE TABLE t1 (a TINYBLOB NOT NULL); 4302SELECT a, COUNT(*) FROM t1 WHERE 0; 4303DROP TABLE t1; 4304 4305--echo # End of test BUG#13571700 4306