diff options
| author | monty@mysql.com <> | 2004-09-06 15:14:10 +0300 |
|---|---|---|
| committer | monty@mysql.com <> | 2004-09-06 15:14:10 +0300 |
| commit | 31122efde725e9b472e467a7cb1807100b7e6b1e (patch) | |
| tree | d9ef905036723648a1d354d0523ef8124e4dee76 /mysql-test/r | |
| parent | 32594c99899edd6de0053609c087f04dc21fbbfd (diff) | |
| parent | 5d71817c7049e9db5c22e1667a41f57cf558d8ed (diff) | |
| download | mariadb-git-31122efde725e9b472e467a7cb1807100b7e6b1e.tar.gz | |
Merge with 4.1
(Includes merge of arena code in 4.1 and 5.0)
Diffstat (limited to 'mysql-test/r')
83 files changed, 8227 insertions, 363 deletions
diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index 5d50a3da666..e85ad303564 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -476,3 +476,9 @@ alter table t1 drop key no_such_key; ERROR 42000: Can't DROP 'no_such_key'; check that column/key exists alter table t1 drop key a; drop table t1; +create table t1 (a int); +alter table t1 rename to `t1\\`; +ERROR 42000: Incorrect table name 't1\\' +rename table t1 to `t1\\`; +ERROR 42000: Incorrect table name 't1\\' +drop table t1; diff --git a/mysql-test/r/auto_increment.result b/mysql-test/r/auto_increment.result index 6bc59d4771f..f5ec5f1f852 100644 --- a/mysql-test/r/auto_increment.result +++ b/mysql-test/r/auto_increment.result @@ -201,6 +201,24 @@ a b 202 5 203 6 204 7 +alter table t1 modify b mediumint; +select * from t1 order by b; +a b +1 1 +200 2 +0 3 +201 4 +202 5 +203 6 +204 7 +create table t2 (a int); +insert t2 values (1),(2); +alter table t2 add b int auto_increment primary key; +select * from t2; +a b +1 1 +2 2 +drop table t2; delete from t1 where a=0; update t1 set a=0 where b=5; select * from t1 order by b; diff --git a/mysql-test/r/binary.result b/mysql-test/r/binary.result index f6ad190b05a..405de1158d6 100644 --- a/mysql-test/r/binary.result +++ b/mysql-test/r/binary.result @@ -59,9 +59,15 @@ concat("-",a,"-",b,"-") -hello-hello- select concat("-",a,"-",b,"-") from t1 where b="hello "; concat("-",a,"-",b,"-") +-hello-hello- select concat("-",a,"-",b,"-") from t1 ignore index (b) where b="hello "; concat("-",a,"-",b,"-") +-hello-hello- alter table t1 modify b tinytext not null, drop key b, add key (b(100)); +select concat("-",a,"-",b,"-") from t1; +concat("-",a,"-",b,"-") +-hello-hello- +-hello2-hello2- select concat("-",a,"-",b,"-") from t1 where b="hello "; concat("-",a,"-",b,"-") -hello-hello- @@ -105,8 +111,26 @@ a b aaa bbb select charset(a), charset(b), charset(binary 'ccc') from t1 limit 1; charset(a) charset(b) charset(binary 'ccc') -latin1 binary latin1 +latin1 binary binary select collation(a), collation(b), collation(binary 'ccc') from t1 limit 1; collation(a) collation(b) collation(binary 'ccc') -latin1_bin binary latin1_bin +latin1_bin binary binary +drop table t1; +create table t1( firstname char(20), lastname char(20)); +insert into t1 values ("john","doe"),("John","Doe"); +select * from t1 where firstname='john' and firstname like binary 'john'; +firstname lastname +john doe +select * from t1 where firstname='john' and binary 'john' = firstname; +firstname lastname +john doe +select * from t1 where firstname='john' and firstname = binary 'john'; +firstname lastname +john doe +select * from t1 where firstname='John' and firstname like binary 'john'; +firstname lastname +john doe +select * from t1 where firstname='john' and firstname like binary 'John'; +firstname lastname +John Doe drop table t1; diff --git a/mysql-test/r/connect.result b/mysql-test/r/connect.result index b5d0e4d9ae2..0e4583fbc96 100644 --- a/mysql-test/r/connect.result +++ b/mysql-test/r/connect.result @@ -42,6 +42,9 @@ show tables; Tables_in_test table_type update mysql.user set password=old_password("gambling2") where user=_binary"test"; flush privileges; +set password=""; +set password='gambling3'; +ERROR HY000: Password hash should be a 41-digit hexadecimal number set password=old_password('gambling3'); show tables; Tables_in_mysql table_type diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 699485ff3f7..92c825f547d 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -1,5 +1,5 @@ drop table if exists t1,t2,t3; -drop database if exists test_$1; +drop database if exists mysqltest; create table t1 (b char(0)); insert into t1 values (""),(null); select * from t1; @@ -58,18 +58,18 @@ select 1ea10.1a20,1e+ 1e+10 from 1ea10; drop table 1ea10; create table t1 (t1.index int); drop table t1; -drop database if exists test_$1; +drop database if exists mysqltest; Warnings: -Note 1008 Can't drop database 'test_$1'; database doesn't exist -create database test_$1; -create table test_$1.$test1 (a$1 int, $b int, c$ int); -insert into test_$1.$test1 values (1,2,3); -select a$1, $b, c$ from test_$1.$test1; +Note 1008 Can't drop database 'mysqltest'; database doesn't exist +create database mysqltest; +create table mysqltest.$test1 (a$1 int, $b int, c$ int); +insert into mysqltest.$test1 values (1,2,3); +select a$1, $b, c$ from mysqltest.$test1; a$1 $b c$ 1 2 3 -create table test_$1.test2$ (a int); -drop table test_$1.test2$; -drop database test_$1; +create table mysqltest.test2$ (a int); +drop table mysqltest.test2$; +drop database mysqltest; create table `` (a int); ERROR 42000: Incorrect table name '' drop table if exists ``; @@ -320,9 +320,9 @@ t3 CREATE TABLE `t3` ( select * from t3; id name drop table t2, t3; -create database test_$1; -create table test_$1.t3 like t1; -create temporary table t3 like test_$1.t3; +create database mysqltest; +create table mysqltest.t3 like t1; +create temporary table t3 like mysqltest.t3; show create table t3; Table Create Table t3 CREATE TEMPORARY TABLE `t3` ( @@ -339,7 +339,7 @@ t2 CREATE TABLE `t2` ( select * from t2; id name create table t3 like t1; -create table t3 like test_$1.t3; +create table t3 like mysqltest.t3; ERROR 42S01: Table 't3' already exists create table non_existing_database.t1 like t1; Got one of the listed errors @@ -351,7 +351,7 @@ create table t3 like `a/a`; ERROR 42000: Incorrect table name 'a/a' drop table t1, t2, t3; drop table t3; -drop database test_$1; +drop database mysqltest; SET SESSION storage_engine="heap"; SELECT @@storage_engine; @@storage_engine @@ -488,12 +488,12 @@ Note 1291 Column 'cset' has duplicated value 'b' in SET Note 1291 Column 'cset' has duplicated value 'B' in SET Note 1291 Column 'cset' has duplicated value 'd' in SET drop table t1, t2, t3; -create database test_$1; -use test_$1; +create database mysqltest; +use mysqltest; select database(); database() -test_$1 -drop database test_$1; +mysqltest +drop database mysqltest; select database(); database() NULL diff --git a/mysql-test/r/create_select_tmp.result b/mysql-test/r/create_select_tmp.result index 09ffc9013c7..b99bf3e3591 100644 --- a/mysql-test/r/create_select_tmp.result +++ b/mysql-test/r/create_select_tmp.result @@ -1,19 +1,19 @@ drop table if exists t1, t2; CREATE TABLE t1 ( a int ); INSERT INTO t1 VALUES (1),(2),(1); -CREATE TABLE t2 ( PRIMARY KEY (a) ) TYPE=INNODB SELECT a FROM t1; +CREATE TABLE t2 ( PRIMARY KEY (a) ) ENGINE=INNODB SELECT a FROM t1; ERROR 23000: Duplicate entry '1' for key 1 select * from t2; ERROR 42S02: Table 'test.t2' doesn't exist -CREATE TEMPORARY TABLE t2 ( PRIMARY KEY (a) ) TYPE=INNODB SELECT a FROM t1; +CREATE TEMPORARY TABLE t2 ( PRIMARY KEY (a) ) ENGINE=INNODB SELECT a FROM t1; ERROR 23000: Duplicate entry '1' for key 1 select * from t2; ERROR 42S02: Table 'test.t2' doesn't exist -CREATE TABLE t2 ( PRIMARY KEY (a) ) TYPE=MYISAM SELECT a FROM t1; +CREATE TABLE t2 ( PRIMARY KEY (a) ) ENGINE=MYISAM SELECT a FROM t1; ERROR 23000: Duplicate entry '1' for key 1 select * from t2; ERROR 42S02: Table 'test.t2' doesn't exist -CREATE TEMPORARY TABLE t2 ( PRIMARY KEY (a) ) TYPE=MYISAM SELECT a FROM t1; +CREATE TEMPORARY TABLE t2 ( PRIMARY KEY (a) ) ENGINE=MYISAM SELECT a FROM t1; ERROR 23000: Duplicate entry '1' for key 1 select * from t2; ERROR 42S02: Table 'test.t2' doesn't exist diff --git a/mysql-test/r/csv.result b/mysql-test/r/csv.result new file mode 100644 index 00000000000..ea0d34271b5 --- /dev/null +++ b/mysql-test/r/csv.result @@ -0,0 +1,4931 @@ +drop table if exists t1,t2,t3,t4; +CREATE TABLE t1 ( +Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL, +Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL +) ENGINE = CSV; +INSERT INTO t1 VALUES (9410,9412); +select period from t1; +period +9410 +select * from t1; +Period Varor_period +9410 9412 +select t1.* from t1; +Period Varor_period +9410 9412 +CREATE TABLE t2 ( +auto int not null, +fld1 int(6) unsigned zerofill DEFAULT '000000' NOT NULL, +companynr tinyint(2) unsigned zerofill DEFAULT '00' NOT NULL, +fld3 char(30) DEFAULT '' NOT NULL, +fld4 char(35) DEFAULT '' NOT NULL, +fld5 char(35) DEFAULT '' NOT NULL, +fld6 char(4) DEFAULT '' NOT NULL +) ENGINE = CSV; +select t2.fld3 from t2 where companynr = 58 and fld3 like "%imaginable%"; +fld3 +imaginable +select fld3 from t2 where fld3 like "%cultivation" ; +fld3 +cultivation +select t2.fld3,companynr from t2 where companynr = 57+1 order by fld3; +fld3 companynr +concoct 58 +druggists 58 +engrossing 58 +Eurydice 58 +exclaimers 58 +ferociousness 58 +hopelessness 58 +Huey 58 +imaginable 58 +judges 58 +merging 58 +ostrich 58 +peering 58 +Phelps 58 +presumes 58 +Ruth 58 +sentences 58 +Shylock 58 +straggled 58 +synergy 58 +thanking 58 +tying 58 +unlocks 58 +select fld3,companynr from t2 where companynr = 58 order by fld3; +fld3 companynr +concoct 58 +druggists 58 +engrossing 58 +Eurydice 58 +exclaimers 58 +ferociousness 58 +hopelessness 58 +Huey 58 +imaginable 58 +judges 58 +merging 58 +ostrich 58 +peering 58 +Phelps 58 +presumes 58 +Ruth 58 +sentences 58 +Shylock 58 +straggled 58 +synergy 58 +thanking 58 +tying 58 +unlocks 58 +select fld3 from t2 order by fld3 desc limit 10; +fld3 +youthfulness +yelped +Wotan +workers +Witt +witchcraft +Winsett +Willy +willed +wildcats +select fld3 from t2 order by fld3 desc limit 5; +fld3 +youthfulness +yelped +Wotan +workers +Witt +select fld3 from t2 order by fld3 desc limit 5,5; +fld3 +witchcraft +Winsett +Willy +willed +wildcats +UPDATE t2 SET fld3="foo" WHERE fld3="b%"; +select fld3 from t2; +fld3 +Omaha +breaking +Romans +intercepted +bewilderingly +astound +admonishing +sumac +flanking +combed +subjective +scatterbrain +Eulerian +dubbed +Kane +overlay +perturb +goblins +annihilates +Wotan +snatching +concludes +laterally +yelped +grazing +Baird +celery +misunderstander +handgun +foldout +mystic +succumbed +Nabisco +fingerings +aging +afield +ammonium +boat +intelligibility +Augustine +teethe +dreaded +scholastics +audiology +wallet +parters +eschew +quitter +neat +Steinberg +jarring +tinily +balled +persist +attainments +fanatic +measures +rightfulness +capably +impulsive +starlet +terminators +untying +announces +featherweight +pessimist +daughter +decliner +lawgiver +stated +readable +attrition +cascade +motors +interrogate +pests +stairway +dopers +testicle +Parsifal +leavings +postulation +squeaking +contrasted +leftover +whiteners +erases +Punjab +Merritt +Quixotism +sweetish +dogging +scornfully +bellow +bills +cupboard +sureties +puddings +tapestry +fetters +bivalves +incurring +Adolph +pithed +emergency +Miles +trimmings +tragedies +skulking +flint +flopping +relaxing +offload +suites +lists +animized +multilayer +standardizes +Judas +vacuuming +dentally +humanness +inch +Weissmuller +irresponsibly +luckily +culled +medical +bloodbath +subschema +animals +Micronesia +repetitions +Antares +ventilate +pityingly +interdependent +Graves +neonatal +scribbled +chafe +honoring +realtor +elite +funereal +abrogating +sorters +Conley +lectured +Abraham +Hawaii +cage +hushes +Simla +reporters +Dutchman +descendants +groupings +dissociate +coexist +Beebe +Taoism +Connally +fetched +checkpoints +rusting +galling +obliterates +traitor +resumes +analyzable +terminator +gritty +firearm +minima +Selfridge +disable +witchcraft +betroth +Manhattanize +imprint +peeked +swelling +interrelationships +riser +Gandhian +peacock +bee +kanji +dental +scarf +chasm +insolence +syndicate +alike +imperial +convulsion +railway +validate +normalizes +comprehensive +chewing +denizen +schemer +chronicle +Kline +Anatole +partridges +brunch +recruited +dimensions +Chicana +announced +praised +employing +linear +quagmire +western +relishing +serving +scheduling +lore +eventful +arteriole +disentangle +cured +Fenton +avoidable +drains +detectably +husky +impelling +undoes +evened +squeezes +destroyer +rudeness +beaner +boorish +Everhart +encompass +mushrooms +Alison +externally +pellagra +cult +creek +Huffman +Majorca +governing +gadfly +reassigned +intentness +craziness +psychic +squabbled +burlesque +capped +extracted +DiMaggio +exclamation +subdirectory +fangs +buyer +pithing +transistorizing +nonbiodegradable +dislocate +monochromatic +batting +postcondition +catalog +Remus +devices +bike +qualify +detained +commended +civilize +Elmhurst +anesthetizing +deaf +Brigham +title +coarse +combinations +grayness +innumerable +Caroline +fatty +eastbound +inexperienced +hoarder +scotch +passport +strategic +gated +flog +Pipestone +Dar +Corcoran +flyers +competitions +suppliers +skips +institutes +troop +connective +denies +polka +observations +askers +homeless +Anna +subdirectories +decaying +outwitting +Harpy +crazed +suffocate +provers +technically +Franklinizations +considered +tinnily +uninterruptedly +whistled +automate +gutting +surreptitious +Choctaw +cooks +millivolt +counterpoise +Gothicism +feminine +metaphysically +sanding +contributorily +receivers +adjourn +straggled +druggists +thanking +ostrich +hopelessness +Eurydice +excitation +presumes +imaginable +concoct +peering +Phelps +ferociousness +sentences +unlocks +engrossing +Ruth +tying +exclaimers +synergy +Huey +merging +judges +Shylock +Miltonism +hen +honeybee +towers +dilutes +numerals +democracy +Ibero- +invalids +behavior +accruing +relics +rackets +Fischbein +phony +cross +cleanup +conspirator +label +university +cleansed +ballgown +starlet +aqueous +portrayal +despising +distort +palmed +faced +silverware +assessor +spiders +artificially +reminiscence +Mexican +obnoxious +fragile +apprehensible +births +garages +panty +anteater +displacement +drovers +patenting +far +shrieks +aligning +pragmatism +fevers +reexamines +occupancies +sweats +modulators +demand +Madeira +Viennese +chillier +wildcats +gentle +Angles +accuracies +toggle +Mendelssohn +behaviorally +Rochford +mirror +Modula +clobbering +chronography +Eskimoizeds +British +pitfalls +verify +scatter +Aztecan +acuity +sinking +beasts +Witt +physicists +folksong +strokes +crowder +merry +cadenced +alimony +principled +golfing +undiscovered +irritates +patriots +rooms +towering +displease +photosensitive +inking +gainers +leaning +hydrant +preserve +blinded +interactions +Barry +whiteness +pastimes +Edenization +Muscat +assassinated +labeled +glacial +implied +bibliographies +Buchanan +forgivably +innuendo +den +submarines +mouthful +expiring +unfulfilled +precession +nullified +affects +Cynthia +Chablis +betterments +advertising +rubies +southwest +superstitious +tabernacle +silk +handsomest +Persian +analog +complex +Taoist +suspend +relegated +awesome +Bruxelles +imprecisely +televise +braking +true +disappointing +navally +circus +beetles +trumps +fourscore +Blackfoots +Grady +quiets +floundered +profundity +Garrisonian +Strauss +cemented +contrition +mutations +exhibits +tits +mate +arches +Moll +ropers +bombast +difficultly +adsorption +definiteness +cultivation +heals +Heusen +target +cited +congresswoman +Katherine +titter +aspire +Mardis +Nadia +estimating +stuck +fifteenth +Colombo +survey +staffing +obtain +loaded +slaughtered +lights +circumference +dull +weekly +wetness +visualized +Tannenbaum +moribund +demultiplex +lockings +thugs +unnerves +abut +Chippewa +stratifications +signaled +Italianizes +algorithmic +paranoid +camping +signifying +Patrice +search +Angeles +semblance +taxed +Beatrice +retrace +lockout +grammatic +helmsman +uniform +hamming +disobedience +captivated +transferals +cartographer +aims +Pakistani +burglarized +saucepans +lacerating +corny +megabytes +chancellor +bulk +commits +meson +deputies +northeaster +dipole +machining +therefore +Telefunken +salvaging +Corinthianizes +restlessly +bromides +generalized +mishaps +quelling +spiritual +beguiles +Trobriand +fleeing +Armour +chin +provers +aeronautic +voltage +sash +anaerobic +simultaneous +accumulating +Medusan +shouted +freakish +index +commercially +mistiness +endpoint +straight +flurried +denotative +coming +commencements +gentleman +gifted +Shanghais +sportswriting +sloping +navies +leaflet +shooter +Joplin +babies +subdivision +burstiness +belted +assails +admiring +swaying +Goldstine +fitting +Norwalk +weakening +analogy +deludes +cokes +Clayton +exhausts +causality +sating +icon +throttles +communicants +dehydrate +priceless +publicly +incidentals +commonplace +mumbles +furthermore +cautioned +parametrized +registration +sadly +positioning +babysitting +eternal +hoarder +congregates +rains +workers +sags +unplug +garage +boulder +hollowly +specifics +Teresa +Winsett +convenient +buckboards +amenities +resplendent +priding +configurations +untidiness +Brice +sews +participated +Simon +certificates +Fitzpatrick +Evanston +misted +textures +save +count +rightful +chaperone +Lizzy +clenched +effortlessly +accessed +beaters +Hornblower +vests +indulgences +infallibly +unwilling +excrete +spools +crunches +overestimating +ineffective +humiliation +sophomore +star +rifles +dialysis +arriving +indulge +clockers +languages +Antarctica +percentage +ceiling +specification +regimented +ciphers +pictures +serpents +allot +realized +mayoral +opaquely +hostess +fiftieth +incorrectly +decomposition +stranglings +mixture +electroencephalography +similarities +charges +freest +Greenberg +tinting +expelled +warm +smoothed +deductions +Romano +bitterroot +corset +securing +environing +cute +Crays +heiress +inform +avenge +universals +Kinsey +ravines +bestseller +equilibrium +extents +relatively +pressure +critiques +befouled +rightfully +mechanizing +Latinizes +timesharing +Aden +embassies +males +shapelessly +genres +mastering +Newtonian +finishers +abates +teem +kiting +stodgy +scalps +feed +guitars +airships +store +denounces +Pyle +Saxony +serializations +Peruvian +taxonomically +kingdom +stint +Sault +faithful +Ganymede +tidiness +gainful +contrary +Tipperary +tropics +theorizers +renew +already +terminal +Hegelian +hypothesizer +warningly +journalizing +nested +Lars +saplings +foothill +labeled +imperiously +reporters +furnishings +precipitable +discounts +excises +Stalin +despot +ripeness +Arabia +unruly +mournfulness +boom +slaughter +Sabine +handy +rural +organizer +shipyard +civics +inaccuracy +rules +juveniles +comprised +investigations +stabilizes +seminaries +Hunter +sporty +test +weasels +CERN +tempering +afore +Galatean +techniques +error +veranda +severely +Cassites +forthcoming +guides +vanish +lied +sawtooth +fated +gradually +widens +preclude +Jobrel +hooker +rainstorm +disconnects +cruelty +exponentials +affective +arteries +Crosby +acquaint +evenhandedly +percentage +disobedience +humility +gleaning +petted +bloater +minion +marginal +apiary +measures +precaution +repelled +primary +coverings +Artemia +navigate +spatial +Gurkha +meanwhile +Melinda +Butterfield +Aldrich +previewing +glut +unaffected +inmate +mineral +impending +meditation +ideas +miniaturizes +lewdly +title +youthfulness +creak +Chippewa +clamored +freezes +forgivably +reduce +McGovern +Nazis +epistle +socializes +conceptions +Kevin +uncovering +chews +appendixes +appendixes +appendixes +appendixes +appendixes +appendixes +raining +infest +compartment +minting +ducks +roped +waltz +Lillian +repressions +chillingly +noncritical +lithograph +spongers +parenthood +posed +instruments +filial +fixedly +relives +Pandora +watering +ungrateful +secures +chastisers +icon +reuniting +imagining +abiding +omnisciently +Britannic +scholastics +mechanics +humidly +masterpiece +however +Mendelian +jarred +scolds +infatuate +willed +joyfully +Microsoft +fibrosities +Baltimorean +equestrian +Goodrich +apish +Adlerian +Tropez +nouns +distracting +mutton +bridgeable +stickers +transcontinental +amateurish +Gandhian +stratified +chamberlains +creditably +philosophic +ores +Carleton +tape +afloat +goodness +welcoming +Pinsky +halting +bibliography +decoding +variance +allowed +dire +dub +poisoning +Iraqis +heaving +population +bomb +Majorca +Gershwins +explorers +libretto +occurred +Lagos +rats +bankruptcies +crying +unexpected +accessed +colorful +versatility +cosy +Darius +mastering +Asiaticizations +offerers +uncles +sleepwalk +Ernestine +checksumming +stopped +sicker +Italianization +alphabetic +pharmaceutic +creator +chess +charcoal +Epiphany +bulldozes +Pygmalion +caressing +Palestine +regimented +scars +realest +diffusing +clubroom +Blythe +ahead +reviver +retransmitting +landslide +Eiffel +absentee +aye +forked +Peruvianizes +clerked +tutor +boulevard +shuttered +quotes +Caltech +Mossberg +kept +roundly +features +imaginable +controller +racial +uprisings +narrowed +cannot +vest +famine +sugars +exterminated +belays +Hodges +translatable +duality +recording +rouses +poison +attitude +dusted +encompasses +presentation +Kantian +imprecision +saving +maternal +hewed +kerosene +Cubans +photographers +nymph +bedlam +north +Schoenberg +botany +curs +solidification +inheritresses +stiller +t1 +suite +ransomer +Willy +Rena +Seattle +relaxes +exclaim +exclaim +implicated +distinguish +assayed +homeowner +and +stealth +coinciding +founder +environing +jewelry +lemons +brokenness +bedpost +assurers +annoyers +affixed +warbling +seriously +boasted +Chantilly +Iranizes +violinist +extramarital +spates +cloakroom +gazer +hand +tucked +gems +clinker +refiner +callus +leopards +comfortingly +generically +getters +sexually +spear +serums +Italianization +attendants +spies +Anthony +planar +cupped +cleanser +commuters +honeysuckle +orphanage +skies +crushers +Puritan +squeezer +bruises +bonfire +Colombo +nondecreasing +UPDATE t2 SET fld3="bar" WHERE fld3="s%"; +select fld3 from t2; +fld3 +Omaha +breaking +Romans +intercepted +bewilderingly +astound +admonishing +sumac +flanking +combed +subjective +scatterbrain +Eulerian +dubbed +Kane +overlay +perturb +goblins +annihilates +Wotan +snatching +concludes +laterally +yelped +grazing +Baird +celery +misunderstander +handgun +foldout +mystic +succumbed +Nabisco +fingerings +aging +afield +ammonium +boat +intelligibility +Augustine +teethe +dreaded +scholastics +audiology +wallet +parters +eschew +quitter +neat +Steinberg +jarring +tinily +balled +persist +attainments +fanatic +measures +rightfulness +capably +impulsive +starlet +terminators +untying +announces +featherweight +pessimist +daughter +decliner +lawgiver +stated +readable +attrition +cascade +motors +interrogate +pests +stairway +dopers +testicle +Parsifal +leavings +postulation +squeaking +contrasted +leftover +whiteners +erases +Punjab +Merritt +Quixotism +sweetish +dogging +scornfully +bellow +bills +cupboard +sureties +puddings +tapestry +fetters +bivalves +incurring +Adolph +pithed +emergency +Miles +trimmings +tragedies +skulking +flint +flopping +relaxing +offload +suites +lists +animized +multilayer +standardizes +Judas +vacuuming +dentally +humanness +inch +Weissmuller +irresponsibly +luckily +culled +medical +bloodbath +subschema +animals +Micronesia +repetitions +Antares +ventilate +pityingly +interdependent +Graves +neonatal +scribbled +chafe +honoring +realtor +elite +funereal +abrogating +sorters +Conley +lectured +Abraham +Hawaii +cage +hushes +Simla +reporters +Dutchman +descendants +groupings +dissociate +coexist +Beebe +Taoism +Connally +fetched +checkpoints +rusting +galling +obliterates +traitor +resumes +analyzable +terminator +gritty +firearm +minima +Selfridge +disable +witchcraft +betroth +Manhattanize +imprint +peeked +swelling +interrelationships +riser +Gandhian +peacock +bee +kanji +dental +scarf +chasm +insolence +syndicate +alike +imperial +convulsion +railway +validate +normalizes +comprehensive +chewing +denizen +schemer +chronicle +Kline +Anatole +partridges +brunch +recruited +dimensions +Chicana +announced +praised +employing +linear +quagmire +western +relishing +serving +scheduling +lore +eventful +arteriole +disentangle +cured +Fenton +avoidable +drains +detectably +husky +impelling +undoes +evened +squeezes +destroyer +rudeness +beaner +boorish +Everhart +encompass +mushrooms +Alison +externally +pellagra +cult +creek +Huffman +Majorca +governing +gadfly +reassigned +intentness +craziness +psychic +squabbled +burlesque +capped +extracted +DiMaggio +exclamation +subdirectory +fangs +buyer +pithing +transistorizing +nonbiodegradable +dislocate +monochromatic +batting +postcondition +catalog +Remus +devices +bike +qualify +detained +commended +civilize +Elmhurst +anesthetizing +deaf +Brigham +title +coarse +combinations +grayness +innumerable +Caroline +fatty +eastbound +inexperienced +hoarder +scotch +passport +strategic +gated +flog +Pipestone +Dar +Corcoran +flyers +competitions +suppliers +skips +institutes +troop +connective +denies +polka +observations +askers +homeless +Anna +subdirectories +decaying +outwitting +Harpy +crazed +suffocate +provers +technically +Franklinizations +considered +tinnily +uninterruptedly +whistled +automate +gutting +surreptitious +Choctaw +cooks +millivolt +counterpoise +Gothicism +feminine +metaphysically +sanding +contributorily +receivers +adjourn +straggled +druggists +thanking +ostrich +hopelessness +Eurydice +excitation +presumes +imaginable +concoct +peering +Phelps +ferociousness +sentences +unlocks +engrossing +Ruth +tying +exclaimers +synergy +Huey +merging +judges +Shylock +Miltonism +hen +honeybee +towers +dilutes +numerals +democracy +Ibero- +invalids +behavior +accruing +relics +rackets +Fischbein +phony +cross +cleanup +conspirator +label +university +cleansed +ballgown +starlet +aqueous +portrayal +despising +distort +palmed +faced +silverware +assessor +spiders +artificially +reminiscence +Mexican +obnoxious +fragile +apprehensible +births +garages +panty +anteater +displacement +drovers +patenting +far +shrieks +aligning +pragmatism +fevers +reexamines +occupancies +sweats +modulators +demand +Madeira +Viennese +chillier +wildcats +gentle +Angles +accuracies +toggle +Mendelssohn +behaviorally +Rochford +mirror +Modula +clobbering +chronography +Eskimoizeds +British +pitfalls +verify +scatter +Aztecan +acuity +sinking +beasts +Witt +physicists +folksong +strokes +crowder +merry +cadenced +alimony +principled +golfing +undiscovered +irritates +patriots +rooms +towering +displease +photosensitive +inking +gainers +leaning +hydrant +preserve +blinded +interactions +Barry +whiteness +pastimes +Edenization +Muscat +assassinated +labeled +glacial +implied +bibliographies +Buchanan +forgivably +innuendo +den +submarines +mouthful +expiring +unfulfilled +precession +nullified +affects +Cynthia +Chablis +betterments +advertising +rubies +southwest +superstitious +tabernacle +silk +handsomest +Persian +analog +complex +Taoist +suspend +relegated +awesome +Bruxelles +imprecisely +televise +braking +true +disappointing +navally +circus +beetles +trumps +fourscore +Blackfoots +Grady +quiets +floundered +profundity +Garrisonian +Strauss +cemented +contrition +mutations +exhibits +tits +mate +arches +Moll +ropers +bombast +difficultly +adsorption +definiteness +cultivation +heals +Heusen +target +cited +congresswoman +Katherine +titter +aspire +Mardis +Nadia +estimating +stuck +fifteenth +Colombo +survey +staffing +obtain +loaded +slaughtered +lights +circumference +dull +weekly +wetness +visualized +Tannenbaum +moribund +demultiplex +lockings +thugs +unnerves +abut +Chippewa +stratifications +signaled +Italianizes +algorithmic +paranoid +camping +signifying +Patrice +search +Angeles +semblance +taxed +Beatrice +retrace +lockout +grammatic +helmsman +uniform +hamming +disobedience +captivated +transferals +cartographer +aims +Pakistani +burglarized +saucepans +lacerating +corny +megabytes +chancellor +bulk +commits +meson +deputies +northeaster +dipole +machining +therefore +Telefunken +salvaging +Corinthianizes +restlessly +bromides +generalized +mishaps +quelling +spiritual +beguiles +Trobriand +fleeing +Armour +chin +provers +aeronautic +voltage +sash +anaerobic +simultaneous +accumulating +Medusan +shouted +freakish +index +commercially +mistiness +endpoint +straight +flurried +denotative +coming +commencements +gentleman +gifted +Shanghais +sportswriting +sloping +navies +leaflet +shooter +Joplin +babies +subdivision +burstiness +belted +assails +admiring +swaying +Goldstine +fitting +Norwalk +weakening +analogy +deludes +cokes +Clayton +exhausts +causality +sating +icon +throttles +communicants +dehydrate +priceless +publicly +incidentals +commonplace +mumbles +furthermore +cautioned +parametrized +registration +sadly +positioning +babysitting +eternal +hoarder +congregates +rains +workers +sags +unplug +garage +boulder +hollowly +specifics +Teresa +Winsett +convenient +buckboards +amenities +resplendent +priding +configurations +untidiness +Brice +sews +participated +Simon +certificates +Fitzpatrick +Evanston +misted +textures +save +count +rightful +chaperone +Lizzy +clenched +effortlessly +accessed +beaters +Hornblower +vests +indulgences +infallibly +unwilling +excrete +spools +crunches +overestimating +ineffective +humiliation +sophomore +star +rifles +dialysis +arriving +indulge +clockers +languages +Antarctica +percentage +ceiling +specification +regimented +ciphers +pictures +serpents +allot +realized +mayoral +opaquely +hostess +fiftieth +incorrectly +decomposition +stranglings +mixture +electroencephalography +similarities +charges +freest +Greenberg +tinting +expelled +warm +smoothed +deductions +Romano +bitterroot +corset +securing +environing +cute +Crays +heiress +inform +avenge +universals +Kinsey +ravines +bestseller +equilibrium +extents +relatively +pressure +critiques +befouled +rightfully +mechanizing +Latinizes +timesharing +Aden +embassies +males +shapelessly +genres +mastering +Newtonian +finishers +abates +teem +kiting +stodgy +scalps +feed +guitars +airships +store +denounces +Pyle +Saxony +serializations +Peruvian +taxonomically +kingdom +stint +Sault +faithful +Ganymede +tidiness +gainful +contrary +Tipperary +tropics +theorizers +renew +already +terminal +Hegelian +hypothesizer +warningly +journalizing +nested +Lars +saplings +foothill +labeled +imperiously +reporters +furnishings +precipitable +discounts +excises +Stalin +despot +ripeness +Arabia +unruly +mournfulness +boom +slaughter +Sabine +handy +rural +organizer +shipyard +civics +inaccuracy +rules +juveniles +comprised +investigations +stabilizes +seminaries +Hunter +sporty +test +weasels +CERN +tempering +afore +Galatean +techniques +error +veranda +severely +Cassites +forthcoming +guides +vanish +lied +sawtooth +fated +gradually +widens +preclude +Jobrel +hooker +rainstorm +disconnects +cruelty +exponentials +affective +arteries +Crosby +acquaint +evenhandedly +percentage +disobedience +humility +gleaning +petted +bloater +minion +marginal +apiary +measures +precaution +repelled +primary +coverings +Artemia +navigate +spatial +Gurkha +meanwhile +Melinda +Butterfield +Aldrich +previewing +glut +unaffected +inmate +mineral +impending +meditation +ideas +miniaturizes +lewdly +title +youthfulness +creak +Chippewa +clamored +freezes +forgivably +reduce +McGovern +Nazis +epistle +socializes +conceptions +Kevin +uncovering +chews +appendixes +appendixes +appendixes +appendixes +appendixes +appendixes +raining +infest +compartment +minting +ducks +roped +waltz +Lillian +repressions +chillingly +noncritical +lithograph +spongers +parenthood +posed +instruments +filial +fixedly +relives +Pandora +watering +ungrateful +secures +chastisers +icon +reuniting +imagining +abiding +omnisciently +Britannic +scholastics +mechanics +humidly +masterpiece +however +Mendelian +jarred +scolds +infatuate +willed +joyfully +Microsoft +fibrosities +Baltimorean +equestrian +Goodrich +apish +Adlerian +Tropez +nouns +distracting +mutton +bridgeable +stickers +transcontinental +amateurish +Gandhian +stratified +chamberlains +creditably +philosophic +ores +Carleton +tape +afloat +goodness +welcoming +Pinsky +halting +bibliography +decoding +variance +allowed +dire +dub +poisoning +Iraqis +heaving +population +bomb +Majorca +Gershwins +explorers +libretto +occurred +Lagos +rats +bankruptcies +crying +unexpected +accessed +colorful +versatility +cosy +Darius +mastering +Asiaticizations +offerers +uncles +sleepwalk +Ernestine +checksumming +stopped +sicker +Italianization +alphabetic +pharmaceutic +creator +chess +charcoal +Epiphany +bulldozes +Pygmalion +caressing +Palestine +regimented +scars +realest +diffusing +clubroom +Blythe +ahead +reviver +retransmitting +landslide +Eiffel +absentee +aye +forked +Peruvianizes +clerked +tutor +boulevard +shuttered +quotes +Caltech +Mossberg +kept +roundly +features +imaginable +controller +racial +uprisings +narrowed +cannot +vest +famine +sugars +exterminated +belays +Hodges +translatable +duality +recording +rouses +poison +attitude +dusted +encompasses +presentation +Kantian +imprecision +saving +maternal +hewed +kerosene +Cubans +photographers +nymph +bedlam +north +Schoenberg +botany +curs +solidification +inheritresses +stiller +t1 +suite +ransomer +Willy +Rena +Seattle +relaxes +exclaim +exclaim +implicated +distinguish +assayed +homeowner +and +stealth +coinciding +founder +environing +jewelry +lemons +brokenness +bedpost +assurers +annoyers +affixed +warbling +seriously +boasted +Chantilly +Iranizes +violinist +extramarital +spates +cloakroom +gazer +hand +tucked +gems +clinker +refiner +callus +leopards +comfortingly +generically +getters +sexually +spear +serums +Italianization +attendants +spies +Anthony +planar +cupped +cleanser +commuters +honeysuckle +orphanage +skies +crushers +Puritan +squeezer +bruises +bonfire +Colombo +nondecreasing +DELETE FROM t2 WHERE fld3="r%"; +SELECT fld3 FROM t2; +fld3 +Omaha +breaking +Romans +intercepted +bewilderingly +astound +admonishing +sumac +flanking +combed +subjective +scatterbrain +Eulerian +dubbed +Kane +overlay +perturb +goblins +annihilates +Wotan +snatching +concludes +laterally +yelped +grazing +Baird +celery +misunderstander +handgun +foldout +mystic +succumbed +Nabisco +fingerings +aging +afield +ammonium +boat +intelligibility +Augustine +teethe +dreaded +scholastics +audiology +wallet +parters +eschew +quitter +neat +Steinberg +jarring +tinily +balled +persist +attainments +fanatic +measures +rightfulness +capably +impulsive +starlet +terminators +untying +announces +featherweight +pessimist +daughter +decliner +lawgiver +stated +readable +attrition +cascade +motors +interrogate +pests +stairway +dopers +testicle +Parsifal +leavings +postulation +squeaking +contrasted +leftover +whiteners +erases +Punjab +Merritt +Quixotism +sweetish +dogging +scornfully +bellow +bills +cupboard +sureties +puddings +tapestry +fetters +bivalves +incurring +Adolph +pithed +emergency +Miles +trimmings +tragedies +skulking +flint +flopping +relaxing +offload +suites +lists +animized +multilayer +standardizes +Judas +vacuuming +dentally +humanness +inch +Weissmuller +irresponsibly +luckily +culled +medical +bloodbath +subschema +animals +Micronesia +repetitions +Antares +ventilate +pityingly +interdependent +Graves +neonatal +scribbled +chafe +honoring +realtor +elite +funereal +abrogating +sorters +Conley +lectured +Abraham +Hawaii +cage +hushes +Simla +reporters +Dutchman +descendants +groupings +dissociate +coexist +Beebe +Taoism +Connally +fetched +checkpoints +rusting +galling +obliterates +traitor +resumes +analyzable +terminator +gritty +firearm +minima +Selfridge +disable +witchcraft +betroth +Manhattanize +imprint +peeked +swelling +interrelationships +riser +Gandhian +peacock +bee +kanji +dental +scarf +chasm +insolence +syndicate +alike +imperial +convulsion +railway +validate +normalizes +comprehensive +chewing +denizen +schemer +chronicle +Kline +Anatole +partridges +brunch +recruited +dimensions +Chicana +announced +praised +employing +linear +quagmire +western +relishing +serving +scheduling +lore +eventful +arteriole +disentangle +cured +Fenton +avoidable +drains +detectably +husky +impelling +undoes +evened +squeezes +destroyer +rudeness +beaner +boorish +Everhart +encompass +mushrooms +Alison +externally +pellagra +cult +creek +Huffman +Majorca +governing +gadfly +reassigned +intentness +craziness +psychic +squabbled +burlesque +capped +extracted +DiMaggio +exclamation +subdirectory +fangs +buyer +pithing +transistorizing +nonbiodegradable +dislocate +monochromatic +batting +postcondition +catalog +Remus +devices +bike +qualify +detained +commended +civilize +Elmhurst +anesthetizing +deaf +Brigham +title +coarse +combinations +grayness +innumerable +Caroline +fatty +eastbound +inexperienced +hoarder +scotch +passport +strategic +gated +flog +Pipestone +Dar +Corcoran +flyers +competitions +suppliers +skips +institutes +troop +connective +denies +polka +observations +askers +homeless +Anna +subdirectories +decaying +outwitting +Harpy +crazed +suffocate +provers +technically +Franklinizations +considered +tinnily +uninterruptedly +whistled +automate +gutting +surreptitious +Choctaw +cooks +millivolt +counterpoise +Gothicism +feminine +metaphysically +sanding +contributorily +receivers +adjourn +straggled +druggists +thanking +ostrich +hopelessness +Eurydice +excitation +presumes +imaginable +concoct +peering +Phelps +ferociousness +sentences +unlocks +engrossing +Ruth +tying +exclaimers +synergy +Huey +merging +judges +Shylock +Miltonism +hen +honeybee +towers +dilutes +numerals +democracy +Ibero- +invalids +behavior +accruing +relics +rackets +Fischbein +phony +cross +cleanup +conspirator +label +university +cleansed +ballgown +starlet +aqueous +portrayal +despising +distort +palmed +faced +silverware +assessor +spiders +artificially +reminiscence +Mexican +obnoxious +fragile +apprehensible +births +garages +panty +anteater +displacement +drovers +patenting +far +shrieks +aligning +pragmatism +fevers +reexamines +occupancies +sweats +modulators +demand +Madeira +Viennese +chillier +wildcats +gentle +Angles +accuracies +toggle +Mendelssohn +behaviorally +Rochford +mirror +Modula +clobbering +chronography +Eskimoizeds +British +pitfalls +verify +scatter +Aztecan +acuity +sinking +beasts +Witt +physicists +folksong +strokes +crowder +merry +cadenced +alimony +principled +golfing +undiscovered +irritates +patriots +rooms +towering +displease +photosensitive +inking +gainers +leaning +hydrant +preserve +blinded +interactions +Barry +whiteness +pastimes +Edenization +Muscat +assassinated +labeled +glacial +implied +bibliographies +Buchanan +forgivably +innuendo +den +submarines +mouthful +expiring +unfulfilled +precession +nullified +affects +Cynthia +Chablis +betterments +advertising +rubies +southwest +superstitious +tabernacle +silk +handsomest +Persian +analog +complex +Taoist +suspend +relegated +awesome +Bruxelles +imprecisely +televise +braking +true +disappointing +navally +circus +beetles +trumps +fourscore +Blackfoots +Grady +quiets +floundered +profundity +Garrisonian +Strauss +cemented +contrition +mutations +exhibits +tits +mate +arches +Moll +ropers +bombast +difficultly +adsorption +definiteness +cultivation +heals +Heusen +target +cited +congresswoman +Katherine +titter +aspire +Mardis +Nadia +estimating +stuck +fifteenth +Colombo +survey +staffing +obtain +loaded +slaughtered +lights +circumference +dull +weekly +wetness +visualized +Tannenbaum +moribund +demultiplex +lockings +thugs +unnerves +abut +Chippewa +stratifications +signaled +Italianizes +algorithmic +paranoid +camping +signifying +Patrice +search +Angeles +semblance +taxed +Beatrice +retrace +lockout +grammatic +helmsman +uniform +hamming +disobedience +captivated +transferals +cartographer +aims +Pakistani +burglarized +saucepans +lacerating +corny +megabytes +chancellor +bulk +commits +meson +deputies +northeaster +dipole +machining +therefore +Telefunken +salvaging +Corinthianizes +restlessly +bromides +generalized +mishaps +quelling +spiritual +beguiles +Trobriand +fleeing +Armour +chin +provers +aeronautic +voltage +sash +anaerobic +simultaneous +accumulating +Medusan +shouted +freakish +index +commercially +mistiness +endpoint +straight +flurried +denotative +coming +commencements +gentleman +gifted +Shanghais +sportswriting +sloping +navies +leaflet +shooter +Joplin +babies +subdivision +burstiness +belted +assails +admiring +swaying +Goldstine +fitting +Norwalk +weakening +analogy +deludes +cokes +Clayton +exhausts +causality +sating +icon +throttles +communicants +dehydrate +priceless +publicly +incidentals +commonplace +mumbles +furthermore +cautioned +parametrized +registration +sadly +positioning +babysitting +eternal +hoarder +congregates +rains +workers +sags +unplug +garage +boulder +hollowly +specifics +Teresa +Winsett +convenient +buckboards +amenities +resplendent +priding +configurations +untidiness +Brice +sews +participated +Simon +certificates +Fitzpatrick +Evanston +misted +textures +save +count +rightful +chaperone +Lizzy +clenched +effortlessly +accessed +beaters +Hornblower +vests +indulgences +infallibly +unwilling +excrete +spools +crunches +overestimating +ineffective +humiliation +sophomore +star +rifles +dialysis +arriving +indulge +clockers +languages +Antarctica +percentage +ceiling +specification +regimented +ciphers +pictures +serpents +allot +realized +mayoral +opaquely +hostess +fiftieth +incorrectly +decomposition +stranglings +mixture +electroencephalography +similarities +charges +freest +Greenberg +tinting +expelled +warm +smoothed +deductions +Romano +bitterroot +corset +securing +environing +cute +Crays +heiress +inform +avenge +universals +Kinsey +ravines +bestseller +equilibrium +extents +relatively +pressure +critiques +befouled +rightfully +mechanizing +Latinizes +timesharing +Aden +embassies +males +shapelessly +genres +mastering +Newtonian +finishers +abates +teem +kiting +stodgy +scalps +feed +guitars +airships +store +denounces +Pyle +Saxony +serializations +Peruvian +taxonomically +kingdom +stint +Sault +faithful +Ganymede +tidiness +gainful +contrary +Tipperary +tropics +theorizers +renew +already +terminal +Hegelian +hypothesizer +warningly +journalizing +nested +Lars +saplings +foothill +labeled +imperiously +reporters +furnishings +precipitable +discounts +excises +Stalin +despot +ripeness +Arabia +unruly +mournfulness +boom +slaughter +Sabine +handy +rural +organizer +shipyard +civics +inaccuracy +rules +juveniles +comprised +investigations +stabilizes +seminaries +Hunter +sporty +test +weasels +CERN +tempering +afore +Galatean +techniques +error +veranda +severely +Cassites +forthcoming +guides +vanish +lied +sawtooth +fated +gradually +widens +preclude +Jobrel +hooker +rainstorm +disconnects +cruelty +exponentials +affective +arteries +Crosby +acquaint +evenhandedly +percentage +disobedience +humility +gleaning +petted +bloater +minion +marginal +apiary +measures +precaution +repelled +primary +coverings +Artemia +navigate +spatial +Gurkha +meanwhile +Melinda +Butterfield +Aldrich +previewing +glut +unaffected +inmate +mineral +impending +meditation +ideas +miniaturizes +lewdly +title +youthfulness +creak +Chippewa +clamored +freezes +forgivably +reduce +McGovern +Nazis +epistle +socializes +conceptions +Kevin +uncovering +chews +appendixes +appendixes +appendixes +appendixes +appendixes +appendixes +raining +infest +compartment +minting +ducks +roped +waltz +Lillian +repressions +chillingly +noncritical +lithograph +spongers +parenthood +posed +instruments +filial +fixedly +relives +Pandora +watering +ungrateful +secures +chastisers +icon +reuniting +imagining +abiding +omnisciently +Britannic +scholastics +mechanics +humidly +masterpiece +however +Mendelian +jarred +scolds +infatuate +willed +joyfully +Microsoft +fibrosities +Baltimorean +equestrian +Goodrich +apish +Adlerian +Tropez +nouns +distracting +mutton +bridgeable +stickers +transcontinental +amateurish +Gandhian +stratified +chamberlains +creditably +philosophic +ores +Carleton +tape +afloat +goodness +welcoming +Pinsky +halting +bibliography +decoding +variance +allowed +dire +dub +poisoning +Iraqis +heaving +population +bomb +Majorca +Gershwins +explorers +libretto +occurred +Lagos +rats +bankruptcies +crying +unexpected +accessed +colorful +versatility +cosy +Darius +mastering +Asiaticizations +offerers +uncles +sleepwalk +Ernestine +checksumming +stopped +sicker +Italianization +alphabetic +pharmaceutic +creator +chess +charcoal +Epiphany +bulldozes +Pygmalion +caressing +Palestine +regimented +scars +realest +diffusing +clubroom +Blythe +ahead +reviver +retransmitting +landslide +Eiffel +absentee +aye +forked +Peruvianizes +clerked +tutor +boulevard +shuttered +quotes +Caltech +Mossberg +kept +roundly +features +imaginable +controller +racial +uprisings +narrowed +cannot +vest +famine +sugars +exterminated +belays +Hodges +translatable +duality +recording +rouses +poison +attitude +dusted +encompasses +presentation +Kantian +imprecision +saving +maternal +hewed +kerosene +Cubans +photographers +nymph +bedlam +north +Schoenberg +botany +curs +solidification +inheritresses +stiller +t1 +suite +ransomer +Willy +Rena +Seattle +relaxes +exclaim +exclaim +implicated +distinguish +assayed +homeowner +and +stealth +coinciding +founder +environing +jewelry +lemons +brokenness +bedpost +assurers +annoyers +affixed +warbling +seriously +boasted +Chantilly +Iranizes +violinist +extramarital +spates +cloakroom +gazer +hand +tucked +gems +clinker +refiner +callus +leopards +comfortingly +generically +getters +sexually +spear +serums +Italianization +attendants +spies +Anthony +planar +cupped +cleanser +commuters +honeysuckle +orphanage +skies +crushers +Puritan +squeezer +bruises +bonfire +Colombo +nondecreasing +DELETE FROM t2 WHERE fld3="d%" ORDER BY RAND(); +SELECT fld3 FROM t2; +fld3 +Omaha +breaking +Romans +intercepted +bewilderingly +astound +admonishing +sumac +flanking +combed +subjective +scatterbrain +Eulerian +dubbed +Kane +overlay +perturb +goblins +annihilates +Wotan +snatching +concludes +laterally +yelped +grazing +Baird +celery +misunderstander +handgun +foldout +mystic +succumbed +Nabisco +fingerings +aging +afield +ammonium +boat +intelligibility +Augustine +teethe +dreaded +scholastics +audiology +wallet +parters +eschew +quitter +neat +Steinberg +jarring +tinily +balled +persist +attainments +fanatic +measures +rightfulness +capably +impulsive +starlet +terminators +untying +announces +featherweight +pessimist +daughter +decliner +lawgiver +stated +readable +attrition +cascade +motors +interrogate +pests +stairway +dopers +testicle +Parsifal +leavings +postulation +squeaking +contrasted +leftover +whiteners +erases +Punjab +Merritt +Quixotism +sweetish +dogging +scornfully +bellow +bills +cupboard +sureties +puddings +tapestry +fetters +bivalves +incurring +Adolph +pithed +emergency +Miles +trimmings +tragedies +skulking +flint +flopping +relaxing +offload +suites +lists +animized +multilayer +standardizes +Judas +vacuuming +dentally +humanness +inch +Weissmuller +irresponsibly +luckily +culled +medical +bloodbath +subschema +animals +Micronesia +repetitions +Antares +ventilate +pityingly +interdependent +Graves +neonatal +scribbled +chafe +honoring +realtor +elite +funereal +abrogating +sorters +Conley +lectured +Abraham +Hawaii +cage +hushes +Simla +reporters +Dutchman +descendants +groupings +dissociate +coexist +Beebe +Taoism +Connally +fetched +checkpoints +rusting +galling +obliterates +traitor +resumes +analyzable +terminator +gritty +firearm +minima +Selfridge +disable +witchcraft +betroth +Manhattanize +imprint +peeked +swelling +interrelationships +riser +Gandhian +peacock +bee +kanji +dental +scarf +chasm +insolence +syndicate +alike +imperial +convulsion +railway +validate +normalizes +comprehensive +chewing +denizen +schemer +chronicle +Kline +Anatole +partridges +brunch +recruited +dimensions +Chicana +announced +praised +employing +linear +quagmire +western +relishing +serving +scheduling +lore +eventful +arteriole +disentangle +cured +Fenton +avoidable +drains +detectably +husky +impelling +undoes +evened +squeezes +destroyer +rudeness +beaner +boorish +Everhart +encompass +mushrooms +Alison +externally +pellagra +cult +creek +Huffman +Majorca +governing +gadfly +reassigned +intentness +craziness +psychic +squabbled +burlesque +capped +extracted +DiMaggio +exclamation +subdirectory +fangs +buyer +pithing +transistorizing +nonbiodegradable +dislocate +monochromatic +batting +postcondition +catalog +Remus +devices +bike +qualify +detained +commended +civilize +Elmhurst +anesthetizing +deaf +Brigham +title +coarse +combinations +grayness +innumerable +Caroline +fatty +eastbound +inexperienced +hoarder +scotch +passport +strategic +gated +flog +Pipestone +Dar +Corcoran +flyers +competitions +suppliers +skips +institutes +troop +connective +denies +polka +observations +askers +homeless +Anna +subdirectories +decaying +outwitting +Harpy +crazed +suffocate +provers +technically +Franklinizations +considered +tinnily +uninterruptedly +whistled +automate +gutting +surreptitious +Choctaw +cooks +millivolt +counterpoise +Gothicism +feminine +metaphysically +sanding +contributorily +receivers +adjourn +straggled +druggists +thanking +ostrich +hopelessness +Eurydice +excitation +presumes +imaginable +concoct +peering +Phelps +ferociousness +sentences +unlocks +engrossing +Ruth +tying +exclaimers +synergy +Huey +merging +judges +Shylock +Miltonism +hen +honeybee +towers +dilutes +numerals +democracy +Ibero- +invalids +behavior +accruing +relics +rackets +Fischbein +phony +cross +cleanup +conspirator +label +university +cleansed +ballgown +starlet +aqueous +portrayal +despising +distort +palmed +faced +silverware +assessor +spiders +artificially +reminiscence +Mexican +obnoxious +fragile +apprehensible +births +garages +panty +anteater +displacement +drovers +patenting +far +shrieks +aligning +pragmatism +fevers +reexamines +occupancies +sweats +modulators +demand +Madeira +Viennese +chillier +wildcats +gentle +Angles +accuracies +toggle +Mendelssohn +behaviorally +Rochford +mirror +Modula +clobbering +chronography +Eskimoizeds +British +pitfalls +verify +scatter +Aztecan +acuity +sinking +beasts +Witt +physicists +folksong +strokes +crowder +merry +cadenced +alimony +principled +golfing +undiscovered +irritates +patriots +rooms +towering +displease +photosensitive +inking +gainers +leaning +hydrant +preserve +blinded +interactions +Barry +whiteness +pastimes +Edenization +Muscat +assassinated +labeled +glacial +implied +bibliographies +Buchanan +forgivably +innuendo +den +submarines +mouthful +expiring +unfulfilled +precession +nullified +affects +Cynthia +Chablis +betterments +advertising +rubies +southwest +superstitious +tabernacle +silk +handsomest +Persian +analog +complex +Taoist +suspend +relegated +awesome +Bruxelles +imprecisely +televise +braking +true +disappointing +navally +circus +beetles +trumps +fourscore +Blackfoots +Grady +quiets +floundered +profundity +Garrisonian +Strauss +cemented +contrition +mutations +exhibits +tits +mate +arches +Moll +ropers +bombast +difficultly +adsorption +definiteness +cultivation +heals +Heusen +target +cited +congresswoman +Katherine +titter +aspire +Mardis +Nadia +estimating +stuck +fifteenth +Colombo +survey +staffing +obtain +loaded +slaughtered +lights +circumference +dull +weekly +wetness +visualized +Tannenbaum +moribund +demultiplex +lockings +thugs +unnerves +abut +Chippewa +stratifications +signaled +Italianizes +algorithmic +paranoid +camping +signifying +Patrice +search +Angeles +semblance +taxed +Beatrice +retrace +lockout +grammatic +helmsman +uniform +hamming +disobedience +captivated +transferals +cartographer +aims +Pakistani +burglarized +saucepans +lacerating +corny +megabytes +chancellor +bulk +commits +meson +deputies +northeaster +dipole +machining +therefore +Telefunken +salvaging +Corinthianizes +restlessly +bromides +generalized +mishaps +quelling +spiritual +beguiles +Trobriand +fleeing +Armour +chin +provers +aeronautic +voltage +sash +anaerobic +simultaneous +accumulating +Medusan +shouted +freakish +index +commercially +mistiness +endpoint +straight +flurried +denotative +coming +commencements +gentleman +gifted +Shanghais +sportswriting +sloping +navies +leaflet +shooter +Joplin +babies +subdivision +burstiness +belted +assails +admiring +swaying +Goldstine +fitting +Norwalk +weakening +analogy +deludes +cokes +Clayton +exhausts +causality +sating +icon +throttles +communicants +dehydrate +priceless +publicly +incidentals +commonplace +mumbles +furthermore +cautioned +parametrized +registration +sadly +positioning +babysitting +eternal +hoarder +congregates +rains +workers +sags +unplug +garage +boulder +hollowly +specifics +Teresa +Winsett +convenient +buckboards +amenities +resplendent +priding +configurations +untidiness +Brice +sews +participated +Simon +certificates +Fitzpatrick +Evanston +misted +textures +save +count +rightful +chaperone +Lizzy +clenched +effortlessly +accessed +beaters +Hornblower +vests +indulgences +infallibly +unwilling +excrete +spools +crunches +overestimating +ineffective +humiliation +sophomore +star +rifles +dialysis +arriving +indulge +clockers +languages +Antarctica +percentage +ceiling +specification +regimented +ciphers +pictures +serpents +allot +realized +mayoral +opaquely +hostess +fiftieth +incorrectly +decomposition +stranglings +mixture +electroencephalography +similarities +charges +freest +Greenberg +tinting +expelled +warm +smoothed +deductions +Romano +bitterroot +corset +securing +environing +cute +Crays +heiress +inform +avenge +universals +Kinsey +ravines +bestseller +equilibrium +extents +relatively +pressure +critiques +befouled +rightfully +mechanizing +Latinizes +timesharing +Aden +embassies +males +shapelessly +genres +mastering +Newtonian +finishers +abates +teem +kiting +stodgy +scalps +feed +guitars +airships +store +denounces +Pyle +Saxony +serializations +Peruvian +taxonomically +kingdom +stint +Sault +faithful +Ganymede +tidiness +gainful +contrary +Tipperary +tropics +theorizers +renew +already +terminal +Hegelian +hypothesizer +warningly +journalizing +nested +Lars +saplings +foothill +labeled +imperiously +reporters +furnishings +precipitable +discounts +excises +Stalin +despot +ripeness +Arabia +unruly +mournfulness +boom +slaughter +Sabine +handy +rural +organizer +shipyard +civics +inaccuracy +rules +juveniles +comprised +investigations +stabilizes +seminaries +Hunter +sporty +test +weasels +CERN +tempering +afore +Galatean +techniques +error +veranda +severely +Cassites +forthcoming +guides +vanish +lied +sawtooth +fated +gradually +widens +preclude +Jobrel +hooker +rainstorm +disconnects +cruelty +exponentials +affective +arteries +Crosby +acquaint +evenhandedly +percentage +disobedience +humility +gleaning +petted +bloater +minion +marginal +apiary +measures +precaution +repelled +primary +coverings +Artemia +navigate +spatial +Gurkha +meanwhile +Melinda +Butterfield +Aldrich +previewing +glut +unaffected +inmate +mineral +impending +meditation +ideas +miniaturizes +lewdly +title +youthfulness +creak +Chippewa +clamored +freezes +forgivably +reduce +McGovern +Nazis +epistle +socializes +conceptions +Kevin +uncovering +chews +appendixes +appendixes +appendixes +appendixes +appendixes +appendixes +raining +infest +compartment +minting +ducks +roped +waltz +Lillian +repressions +chillingly +noncritical +lithograph +spongers +parenthood +posed +instruments +filial +fixedly +relives +Pandora +watering +ungrateful +secures +chastisers +icon +reuniting +imagining +abiding +omnisciently +Britannic +scholastics +mechanics +humidly +masterpiece +however +Mendelian +jarred +scolds +infatuate +willed +joyfully +Microsoft +fibrosities +Baltimorean +equestrian +Goodrich +apish +Adlerian +Tropez +nouns +distracting +mutton +bridgeable +stickers +transcontinental +amateurish +Gandhian +stratified +chamberlains +creditably +philosophic +ores +Carleton +tape +afloat +goodness +welcoming +Pinsky +halting +bibliography +decoding +variance +allowed +dire +dub +poisoning +Iraqis +heaving +population +bomb +Majorca +Gershwins +explorers +libretto +occurred +Lagos +rats +bankruptcies +crying +unexpected +accessed +colorful +versatility +cosy +Darius +mastering +Asiaticizations +offerers +uncles +sleepwalk +Ernestine +checksumming +stopped +sicker +Italianization +alphabetic +pharmaceutic +creator +chess +charcoal +Epiphany +bulldozes +Pygmalion +caressing +Palestine +regimented +scars +realest +diffusing +clubroom +Blythe +ahead +reviver +retransmitting +landslide +Eiffel +absentee +aye +forked +Peruvianizes +clerked +tutor +boulevard +shuttered +quotes +Caltech +Mossberg +kept +roundly +features +imaginable +controller +racial +uprisings +narrowed +cannot +vest +famine +sugars +exterminated +belays +Hodges +translatable +duality +recording +rouses +poison +attitude +dusted +encompasses +presentation +Kantian +imprecision +saving +maternal +hewed +kerosene +Cubans +photographers +nymph +bedlam +north +Schoenberg +botany +curs +solidification +inheritresses +stiller +t1 +suite +ransomer +Willy +Rena +Seattle +relaxes +exclaim +exclaim +implicated +distinguish +assayed +homeowner +and +stealth +coinciding +founder +environing +jewelry +lemons +brokenness +bedpost +assurers +annoyers +affixed +warbling +seriously +boasted +Chantilly +Iranizes +violinist +extramarital +spates +cloakroom +gazer +hand +tucked +gems +clinker +refiner +callus +leopards +comfortingly +generically +getters +sexually +spear +serums +Italianization +attendants +spies +Anthony +planar +cupped +cleanser +commuters +honeysuckle +orphanage +skies +crushers +Puritan +squeezer +bruises +bonfire +Colombo +nondecreasing +DROP TABLE t1; +ALTER TABLE t2 RENAME t1 +#; +DROP TABLE t1; +CREATE TABLE t1 ( +Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL, +Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL +) ENGINE = CSV; +INSERT INTO t1 VALUES (9410,9412); +select period from t1; +period +9410 +drop table if exists t1,t2,t3,t4; +Warnings: +Note 1051 Unknown table 't2' +Note 1051 Unknown table 't3' +Note 1051 Unknown table 't4' diff --git a/mysql-test/r/ctype_cp1251.result b/mysql-test/r/ctype_cp1251.result index 2a59f976156..3793e962d40 100644 --- a/mysql-test/r/ctype_cp1251.result +++ b/mysql-test/r/ctype_cp1251.result @@ -49,8 +49,8 @@ a b aaa bbb select charset(a), charset(b), charset(binary 'ccc') from t1 limit 1; charset(a) charset(b) charset(binary 'ccc') -cp1251 binary cp1251 +cp1251 binary binary select collation(a), collation(b), collation(binary 'ccc') from t1 limit 1; collation(a) collation(b) collation(binary 'ccc') -cp1251_bin binary cp1251_bin +cp1251_bin binary binary drop table t1; diff --git a/mysql-test/r/ctype_create.result b/mysql-test/r/ctype_create.result index 0da76c556e2..b35131f62a4 100644 --- a/mysql-test/r/ctype_create.result +++ b/mysql-test/r/ctype_create.result @@ -54,4 +54,12 @@ t1 CREATE TABLE `t1` ( `a` char(10) collate latin1_german1_ci default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci DROP TABLE t1; +create table t1 (a char) character set latin1 character set latin2; +ERROR HY000: Conflicting declarations: 'CHARACTER SET latin1' and 'CHARACTER SET latin2' +create table t1 (a char) character set latin1 collate latin2_bin; +ERROR 42000: COLLATION 'latin2_bin' is not valid for CHARACTER SET 'latin1' +create database d1 default character set latin1 character set latin2; +ERROR HY000: Conflicting declarations: 'CHARACTER SET latin1' and 'CHARACTER SET latin2' +create database d1 default character set latin1 collate latin2_bin; +ERROR 42000: COLLATION 'latin2_bin' is not valid for CHARACTER SET 'latin1' DROP DATABASE mysqltest1; diff --git a/mysql-test/r/ctype_recoding.result b/mysql-test/r/ctype_recoding.result index 69cf1d722ff..1ef185388a8 100644 --- a/mysql-test/r/ctype_recoding.result +++ b/mysql-test/r/ctype_recoding.result @@ -136,6 +136,30 @@ SET character_set_connection=binary; SELECT 'ѵÑÑ' as s; s ѵÑÑ +SET NAMES latin1; +CREATE TABLE t1 (`ä` CHAR(128) DEFAULT 'ä', `ä1` ENUM('ä1','ä2') DEFAULT 'ä2'); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `ä` char(128) default 'ä', + `ä1` enum('ä1','ä2') default 'ä2' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SHOW COLUMNS FROM t1; +Field Type Null Key Default Extra +ä char(128) YES ä +ä1 enum('ä1','ä2') YES ä2 +SET NAMES binary; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `¤` char(128) default '¤', + `¤1` enum('¤1','¤2') default '¤2' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SHOW COLUMNS FROM t1; +Field Type Null Key Default Extra +¤ char(128) YES ¤ +¤1 enum('¤1','¤2') YES ¤2 +DROP TABLE t1; SET NAMES binary; CREATE TABLE `goodÌÏÈÏ` (a int); ERROR HY000: Invalid utf8 character string: 'ÌÏÈÏ' diff --git a/mysql-test/r/ctype_uca.result b/mysql-test/r/ctype_uca.result index 2fd654da434..94fe15fed26 100644 --- a/mysql-test/r/ctype_uca.result +++ b/mysql-test/r/ctype_uca.result @@ -1548,6 +1548,116 @@ Z,z,Ċı,Ċş,Ċğ,Ċĵ Ç Ç Ç +select group_concat(c1 order by c1) from t1 group by c1 collate utf8_slovak_ci; +group_concat(c1 order by c1) +· + +A,a,,,,,
, ,Ħ,˘,£,,Ä,Ä,Ä,Ä,Ä,Ä
,Ç,Ç,Ç,Ç,Ç ,ÇĦ,Çş,Çğ +AA,Aa,aA,aa +,¤ +,Ĥ,Ǣ,Ç£,Çĵ,ǽ +B,b +Ĉ +Ĉ +Ĉ,Ĉ +C,c,,§,Ä,Ä,Ä,Ä,Ä,Ä +cH +Ä,Ä +Ĉ,Ĉ +D,d,Ä,Ä +DZ,Dz,dZ,dz,Ç,Ç
,Ç,Çħ,Dz,dz +Ä,Ä +Ĉ +Ĉ +Ĉ,Ĉ +,° +E,e,,,,,¨,İ,Ş,Ğ,Ä,Ä,Ä,Ä,Ä,Ä,Ä,Ä,Ä,Ä +Ĉ,Ç +Ĉ +Ĉ +F,f +Ĉ,Ĉ +G,g,Ä,Ä,Ä,Ä,Ä ,ÄĦ,Ģ,Ä£,ÇĤ,ǧ,Ç´,ǵ +Ǥ,Ç +Ĉ +Ĉ +Ĉ˘,Ĉ£ +H,h,Ĥ,Ä +CH,Ch,ch +Ĉ,Çĥ +ÄĤ,ħ +I,i,,,,,Ĵ,,,Ż,Ĩ,Äİ,ÄŞ,ÄĞ,ÄĴ,Ä,Ä,ÄŻ,Ä°,Ç,Ç +IJ,Ij,iJ,ij,IJ,ij +Äħ +Ĉ +Ĉ +J,j,Ä´,ĵ,Ç° +K,k,Äĥ,Ä·,Ǩ,Çİ +Ĉ,Ĉ +L,l,Äı,Äş,Äğ,Äĵ,Ľ,Ä +Äż,Ċ +LJ,Lj,lJ,lj,Ç,Ç,Ç +LL,Ll,lL,ll +Ċ,Ċ +Ĉ +Ĉ +M,m +N,n,,ħ,Ċ,Ċ,Ċ
,Ċ,Ċ,Ċ,Ǹ,Çı +NJ,Nj,nJ,nj,Ç,Ç,Ç +Ĉ +Ĉ +Ċ,Ċ +O,o,,,,,²,³,µ,ĥ,Ċ,Ċ,Ċ,Ċ,Ċ,Ċ,Ĉ ,ĈĦ,Ç,Ç,ÇŞ,ÇĞ,ÇĴ,Ç +OE,Oe,oE,oe,Ċ,Ċ +,´ +,¸,Ç,Çż +Ĉ +Ĉ +P,p +Ĉ¤,Ĉ +Q,q +ĸ +R,r,Ċ,Ċ,Ċ,Ċ,Ċ,Ċ +RR,Rr,rR,rr +ĈĤ +S,s,Ċ,Ċ,Ċ,Ċ,Ċ,Ċ,Ċż +SS,Ss,sS,ss, +Ċ ,ĊĦ +Ĉİ +ĈŞ +T,t,Ċ˘,Ċ£,Ċ¤,Ċ +Ĉ +ĊĤ,Ċ§ +ĈĞ +ĈĴ,Ĉ +Ĉ +U,u,,,,,ı,ş,ğ,ĵ,Ċ¨,Ċİ,ĊŞ,ĊĞ,ĊĴ,Ċ,Ċ,ĊŻ,Ċ°,Ċħ,Ċ²,Ċ³,ĈŻ,Ĉ°,Ç,Ç,Ç,Ç,Ç,Ç,Ç,Ç,Ç,Ç +Ĉ +Ĉħ +V,v +Ĉ² +W,w,Ċ´,Ċµ +X,x +Y,y,,½,ż,Ċĥ,Ċ·,Ċ¸ +Ĉ³,Ĉ´ +Z,z,Ċı,Ċş,Ċğ,Ċĵ +Ĉ +Ċ½,Ċ +Ĉµ,Ĉĥ +Ĉ·,Ç,ÇŻ +Ĉ¸,Ĉı +Ĉş +, +Ĉż,Ç· +Ĉğ +Ĉ§,Ĉ¨ +Ĉĵ,Ĉ½ +Ĉ,Ĉ
+Ċ +Ç +Ç +Ç +Ç select group_concat(c1 order by c1) from t1 group by c1 collate utf8_spanish2_ci; group_concat(c1 order by c1) · diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result index 4ab8cc83d0c..a0ac29b7989 100644 --- a/mysql-test/r/ctype_ucs.result +++ b/mysql-test/r/ctype_ucs.result @@ -464,3 +464,19 @@ HEX(a) AAAA 000AAAAA DROP TABLE t1; +create table t1 (s1 char character set `ucs2` collate `ucs2_czech_ci`); +insert into t1 values ('0'),('1'),('2'),('a'),('b'),('c'); +select s1 from t1 where s1 > 'a' order by s1; +s1 +b +c +drop table t1; +create table t1(a char(1)) default charset = ucs2; +insert into t1 values ('a'),('b'),('c'); +alter table t1 modify a char(5); +select a, hex(a) from t1; +a hex(a) +a 0061 +b 0062 +c 0063 +drop table t1; diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index b8ca99fe8f1..f3be539251a 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -1,4 +1,4 @@ -drop table if exists t1; +drop table if exists t1,t2; set names utf8; select left(_utf8 0xD0B0D0B1D0B2,1); left(_utf8 0xD0B0D0B1D0B2,1) @@ -78,6 +78,21 @@ SELECT 'a\t' < 'a'; SELECT 'a\t' < 'a '; 'a\t' < 'a ' 1 +SELECT 'a' = 'a ' collate utf8_bin; +'a' = 'a ' collate utf8_bin +1 +SELECT 'a\0' < 'a' collate utf8_bin; +'a\0' < 'a' collate utf8_bin +1 +SELECT 'a\0' < 'a ' collate utf8_bin; +'a\0' < 'a ' collate utf8_bin +1 +SELECT 'a\t' < 'a' collate utf8_bin; +'a\t' < 'a' collate utf8_bin +1 +SELECT 'a\t' < 'a ' collate utf8_bin; +'a\t' < 'a ' collate utf8_bin +1 CREATE TABLE t1 (a char(10) character set utf8 not null); INSERT INTO t1 VALUES ('a'),('a\0'),('a\t'),('a '); SELECT hex(a),STRCMP(a,'a'), STRCMP(a,'a ') FROM t1; @@ -93,6 +108,9 @@ this is a test select insert("aa",100,1,"b"),insert("aa",1,3,"b"); insert("aa",100,1,"b") insert("aa",1,3,"b") aa b +select char_length(left(@a:='ѵÑÑ',5)), length(@a), @a; +char_length(left(@a:='ѵÑÑ',5)) length(@a) @a +4 8 ѵÑÑ create table t1 select date_format("2004-01-19 10:10:10", "%Y-%m-%d"); show create table t1; Table Create Table @@ -243,3 +261,381 @@ select 'z²°ÑÑz' rlike '[[:<:]]²°ÑÑ[[:>:]]'; CREATE TABLE t1 (a enum ('Y', 'N') DEFAULT 'N' COLLATE utf8_unicode_ci); ALTER TABLE t1 ADD COLUMN b CHAR(20); DROP TABLE t1; +set names utf8; +create table t1 (a enum('aaaa','żÑħ°') character set utf8); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` enum('aaaa','żÑħ°') character set utf8 default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +insert into t1 values ('żÑħ°'); +select * from t1; +a +żÑħ° +create table t2 select ifnull(a,a) from t1; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `ifnull(a,a)` char(5) character set utf8 default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select * from t2; +ifnull(a,a) +żÑħ° +drop table t1; +drop table t2; +create table t1 (c varchar(30) character set utf8, unique(c(10))); +insert into t1 values ('1'),('2'),('3'),('x'),('y'),('z'); +insert into t1 values ('aaaaaaaaaa'); +insert into t1 values ('aaaaaaaaaaa'); +ERROR 23000: Duplicate entry 'aaaaaaaaaaa' for key 1 +insert into t1 values ('aaaaaaaaaaaa'); +ERROR 23000: Duplicate entry 'aaaaaaaaaaaa' for key 1 +insert into t1 values (repeat('b',20)); +select c c1 from t1 where c='1'; +c1 +1 +select c c2 from t1 where c='2'; +c2 +2 +select c c3 from t1 where c='3'; +c3 +3 +select c cx from t1 where c='x'; +cx +x +select c cy from t1 where c='y'; +cy +y +select c cz from t1 where c='z'; +cz +z +select c ca10 from t1 where c='aaaaaaaaaa'; +ca10 +aaaaaaaaaa +select c cb20 from t1 where c=repeat('b',20); +cb20 +bbbbbbbbbbbbbbbbbbbb +drop table t1; +create table t1 (c char(3) character set utf8, unique (c(2))); +insert into t1 values ('1'),('2'),('3'),('4'),('x'),('y'),('z'); +insert into t1 values ('a'); +insert into t1 values ('aa'); +insert into t1 values ('aaa'); +ERROR 23000: Duplicate entry 'aaa' for key 1 +insert into t1 values ('b'); +insert into t1 values ('bb'); +insert into t1 values ('bbb'); +ERROR 23000: Duplicate entry 'bbb' for key 1 +insert into t1 values ('°'); +insert into t1 values ('°°'); +insert into t1 values ('°°°'); +ERROR 23000: Duplicate entry '°°°' for key 1 +insert into t1 values ('ħ'); +insert into t1 values ('ħħ'); +insert into t1 values ('ħħħ'); +ERROR 23000: Duplicate entry 'ħħħ' for key 1 +insert into t1 values ('êŞŞ'); +insert into t1 values ('êŞŞêŞŞ'); +insert into t1 values ('êŞŞêŞŞêŞŞ'); +ERROR 23000: Duplicate entry 'êŞŞêŞŞ' for key 1 +drop table t1; +create table t1 ( +c char(10) character set utf8, +unique key a using hash (c(1)) +) engine=heap; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c` char(10) character set utf8 default NULL, + UNIQUE KEY `a` (`c`(1)) +) ENGINE=HEAP DEFAULT CHARSET=latin1 +insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f'); +insert into t1 values ('aa'); +ERROR 23000: Duplicate entry 'aa' for key 1 +insert into t1 values ('aaa'); +ERROR 23000: Duplicate entry 'aaa' for key 1 +insert into t1 values ('ħ'); +insert into t1 values ('ħħ'); +ERROR 23000: Duplicate entry 'ħ' for key 1 +insert into t1 values ('ħħħ'); +ERROR 23000: Duplicate entry 'ħ' for key 1 +select c as c_all from t1 order by c; +c_all +a +b +c +d +e +f +ħ +select c as c_a from t1 where c='a'; +c_a +a +select c as c_a from t1 where c='ħ'; +c_a +ħ +drop table t1; +create table t1 ( +c char(10) character set utf8, +unique key a using btree (c(1)) +) engine=heap; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c` char(10) character set utf8 default NULL, + UNIQUE KEY `a` TYPE BTREE (`c`(1)) +) ENGINE=HEAP DEFAULT CHARSET=latin1 +insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f'); +insert into t1 values ('aa'); +ERROR 23000: Duplicate entry 'aa' for key 1 +insert into t1 values ('aaa'); +ERROR 23000: Duplicate entry 'aaa' for key 1 +insert into t1 values ('ħ'); +insert into t1 values ('ħħ'); +ERROR 23000: Duplicate entry 'ħ' for key 1 +insert into t1 values ('ħħħ'); +ERROR 23000: Duplicate entry 'ħ' for key 1 +select c as c_all from t1 order by c; +c_all +a +b +c +d +e +f +ħ +select c as c_a from t1 where c='a'; +c_a +a +select c as c_a from t1 where c='ħ'; +c_a +ħ +drop table t1; +create table t1 ( +c char(10) character set utf8, +unique key a (c(1)) +) engine=bdb; +insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f'); +insert into t1 values ('aa'); +ERROR 23000: Duplicate entry 'aa' for key 1 +insert into t1 values ('aaa'); +ERROR 23000: Duplicate entry 'aaa' for key 1 +insert into t1 values ('ħ'); +insert into t1 values ('ħħ'); +ERROR 23000: Duplicate entry 'ħ' for key 1 +insert into t1 values ('ħħħ'); +ERROR 23000: Duplicate entry 'ħ' for key 1 +select c as c_all from t1 order by c; +c_all +a +b +c +d +e +f +ħ +select c as c_a from t1 where c='a'; +c_a +a +select c as c_a from t1 where c='ħ'; +c_a +ħ +drop table t1; +create table t1 (c varchar(30) character set utf8 collate utf8_bin, unique(c(10))); +insert into t1 values ('1'),('2'),('3'),('x'),('y'),('z'); +insert into t1 values ('aaaaaaaaaa'); +insert into t1 values ('aaaaaaaaaaa'); +ERROR 23000: Duplicate entry 'aaaaaaaaaaa' for key 1 +insert into t1 values ('aaaaaaaaaaaa'); +ERROR 23000: Duplicate entry 'aaaaaaaaaaaa' for key 1 +insert into t1 values (repeat('b',20)); +select c c1 from t1 where c='1'; +c1 +1 +select c c2 from t1 where c='2'; +c2 +2 +select c c3 from t1 where c='3'; +c3 +3 +select c cx from t1 where c='x'; +cx +x +select c cy from t1 where c='y'; +cy +y +select c cz from t1 where c='z'; +cz +z +select c ca10 from t1 where c='aaaaaaaaaa'; +ca10 +aaaaaaaaaa +select c cb20 from t1 where c=repeat('b',20); +cb20 +bbbbbbbbbbbbbbbbbbbb +drop table t1; +create table t1 (c char(3) character set utf8 collate utf8_bin, unique (c(2))); +insert into t1 values ('1'),('2'),('3'),('4'),('x'),('y'),('z'); +insert into t1 values ('a'); +insert into t1 values ('aa'); +insert into t1 values ('aaa'); +ERROR 23000: Duplicate entry 'aaa' for key 1 +insert into t1 values ('b'); +insert into t1 values ('bb'); +insert into t1 values ('bbb'); +ERROR 23000: Duplicate entry 'bbb' for key 1 +insert into t1 values ('°'); +insert into t1 values ('°°'); +insert into t1 values ('°°°'); +ERROR 23000: Duplicate entry '°°°' for key 1 +insert into t1 values ('ħ'); +insert into t1 values ('ħħ'); +insert into t1 values ('ħħħ'); +ERROR 23000: Duplicate entry 'ħħħ' for key 1 +insert into t1 values ('êŞŞ'); +insert into t1 values ('êŞŞêŞŞ'); +insert into t1 values ('êŞŞêŞŞêŞŞ'); +ERROR 23000: Duplicate entry 'êŞŞêŞŞ' for key 1 +drop table t1; +create table t1 ( +c char(10) character set utf8 collate utf8_bin, +unique key a using hash (c(1)) +) engine=heap; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c` char(10) character set utf8 collate utf8_bin default NULL, + UNIQUE KEY `a` (`c`(1)) +) ENGINE=HEAP DEFAULT CHARSET=latin1 +insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f'); +insert into t1 values ('aa'); +ERROR 23000: Duplicate entry 'aa' for key 1 +insert into t1 values ('aaa'); +ERROR 23000: Duplicate entry 'aaa' for key 1 +insert into t1 values ('ħ'); +insert into t1 values ('ħħ'); +ERROR 23000: Duplicate entry 'ħ' for key 1 +insert into t1 values ('ħħħ'); +ERROR 23000: Duplicate entry 'ħ' for key 1 +select c as c_all from t1 order by c; +c_all +a +b +c +d +e +f +ħ +select c as c_a from t1 where c='a'; +c_a +a +select c as c_a from t1 where c='ħ'; +c_a +ħ +drop table t1; +create table t1 ( +c char(10) character set utf8 collate utf8_bin, +unique key a using btree (c(1)) +) engine=heap; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c` char(10) character set utf8 collate utf8_bin default NULL, + UNIQUE KEY `a` TYPE BTREE (`c`(1)) +) ENGINE=HEAP DEFAULT CHARSET=latin1 +insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f'); +insert into t1 values ('aa'); +ERROR 23000: Duplicate entry 'aa' for key 1 +insert into t1 values ('aaa'); +ERROR 23000: Duplicate entry 'aaa' for key 1 +insert into t1 values ('ħ'); +insert into t1 values ('ħħ'); +ERROR 23000: Duplicate entry 'ħ' for key 1 +insert into t1 values ('ħħħ'); +ERROR 23000: Duplicate entry 'ħ' for key 1 +select c as c_all from t1 order by c; +c_all +a +b +c +d +e +f +ħ +select c as c_a from t1 where c='a'; +c_a +a +select c as c_a from t1 where c='ħ'; +c_a +ħ +drop table t1; +create table t1 ( +c char(10) character set utf8 collate utf8_bin, +unique key a (c(1)) +) engine=bdb; +insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f'); +insert into t1 values ('aa'); +ERROR 23000: Duplicate entry 'aa' for key 1 +insert into t1 values ('aaa'); +ERROR 23000: Duplicate entry 'aaa' for key 1 +insert into t1 values ('ħ'); +insert into t1 values ('ħħ'); +ERROR 23000: Duplicate entry 'ħ' for key 1 +insert into t1 values ('ħħħ'); +ERROR 23000: Duplicate entry 'ħ' for key 1 +select c as c_all from t1 order by c; +c_all +a +b +c +d +e +f +ħ +select c as c_a from t1 where c='a'; +c_a +a +select c as c_a from t1 where c='ħ'; +c_a +ħ +drop table t1; +create table t1 ( +str varchar(255) character set utf8 not null, +key str (str(2)) +) engine=myisam; +INSERT INTO t1 VALUES ('str'); +INSERT INTO t1 VALUES ('str2'); +select * from t1 where str='str'; +str +str +drop table t1; +create table t1 ( +str varchar(255) character set utf8 not null, +key str using btree (str(2)) +) engine=heap; +INSERT INTO t1 VALUES ('str'); +INSERT INTO t1 VALUES ('str2'); +select * from t1 where str='str'; +str +str +drop table t1; +create table t1 ( +str varchar(255) character set utf8 not null, +key str using hash (str(2)) +) engine=heap; +INSERT INTO t1 VALUES ('str'); +INSERT INTO t1 VALUES ('str2'); +select * from t1 where str='str'; +str +str +drop table t1; +create table t1 ( +str varchar(255) character set utf8 not null, +key str (str(2)) +) engine=bdb; +INSERT INTO t1 VALUES ('str'); +INSERT INTO t1 VALUES ('str2'); +select * from t1 where str='str'; +str +str +drop table t1; diff --git a/mysql-test/r/date_formats.result b/mysql-test/r/date_formats.result index 6a4935ef3f8..758a83defed 100644 --- a/mysql-test/r/date_formats.result +++ b/mysql-test/r/date_formats.result @@ -90,16 +90,23 @@ insert into t1 values ('2003-01-02 11:11:12Pm', '%Y-%m-%d %h:%i:%S%p'), ('10:20:10', '%H:%i:%s'), ('10:20:10', '%h:%i:%s.%f'), +('10:20:10', '%T'), ('10:20:10AM', '%h:%i:%s%p'), +('10:20:10AM', '%r'), ('10:20:10.44AM', '%h:%i:%s.%f%p'), ('15-01-2001 12:59:58', '%d-%m-%Y %H:%i:%S'), ('15 September 2001', '%d %M %Y'), ('15 SEPTEMB 2001', '%d %M %Y'), ('15 MAY 2001', '%d %b %Y'), +('15th May 2001', '%D %b %Y'), ('Sunday 15 MAY 2001', '%W %d %b %Y'), ('Sund 15 MAY 2001', '%W %d %b %Y'), ('Tuesday 00 2002', '%W %U %Y'), ('Thursday 53 1998', '%W %u %Y'), +('Sunday 01 2001', '%W %v %x'), +('Tuesday 52 2001', '%W %V %X'), +('060 2004', '%j %Y'), +('4 53 1998', '%w %u %Y'), ('15-01-2001', '%d-%m-%Y %H:%i:%S'), ('15-01-20', '%d-%m-%y'), ('15-2001-1', '%d-%Y-%c'); @@ -114,16 +121,23 @@ date format str_to_date 2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 2003-01-02 23:11:12 10:20:10 %H:%i:%s 0000-00-00 10:20:10 10:20:10 %h:%i:%s.%f 0000-00-00 10:20:10 +10:20:10 %T 0000-00-00 10:20:10 10:20:10AM %h:%i:%s%p 0000-00-00 10:20:10 +10:20:10AM %r 0000-00-00 10:20:10 10:20:10.44AM %h:%i:%s.%f%p 0000-00-00 10:20:10.440000 15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 2001-01-15 12:59:58 15 September 2001 %d %M %Y 2001-09-15 00:00:00 15 SEPTEMB 2001 %d %M %Y 2001-09-15 00:00:00 15 MAY 2001 %d %b %Y 2001-05-15 00:00:00 +15th May 2001 %D %b %Y 2001-05-15 00:00:00 Sunday 15 MAY 2001 %W %d %b %Y 2001-05-15 00:00:00 Sund 15 MAY 2001 %W %d %b %Y 2001-05-15 00:00:00 Tuesday 00 2002 %W %U %Y 2002-01-01 00:00:00 Thursday 53 1998 %W %u %Y 1998-12-31 00:00:00 +Sunday 01 2001 %W %v %x 2001-01-07 00:00:00 +Tuesday 52 2001 %W %V %X 2002-01-01 00:00:00 +060 2004 %j %Y 2004-02-29 00:00:00 +4 53 1998 %w %u %Y 1998-12-31 00:00:00 15-01-2001 %d-%m-%Y %H:%i:%S 2001-01-15 00:00:00 15-01-20 %d-%m-%y 2020-01-15 00:00:00 15-2001-1 %d-%Y-%c 2001-01-15 00:00:00 @@ -138,16 +152,23 @@ date format con 2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 2003-01-02 23:11:12 10:20:10 %H:%i:%s 0000-00-00 10:20:10 10:20:10 %h:%i:%s.%f 0000-00-00 10:20:10 +10:20:10 %T 0000-00-00 10:20:10 10:20:10AM %h:%i:%s%p 0000-00-00 10:20:10 +10:20:10AM %r 0000-00-00 10:20:10 10:20:10.44AM %h:%i:%s.%f%p 0000-00-00 10:20:10.440000 15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 2001-01-15 12:59:58 15 September 2001 %d %M %Y 2001-09-15 00:00:00 15 SEPTEMB 2001 %d %M %Y 2001-09-15 00:00:00 15 MAY 2001 %d %b %Y 2001-05-15 00:00:00 +15th May 2001 %D %b %Y 2001-05-15 00:00:00 Sunday 15 MAY 2001 %W %d %b %Y 2001-05-15 00:00:00 Sund 15 MAY 2001 %W %d %b %Y 2001-05-15 00:00:00 Tuesday 00 2002 %W %U %Y 2002-01-01 00:00:00 Thursday 53 1998 %W %u %Y 1998-12-31 00:00:00 +Sunday 01 2001 %W %v %x 2001-01-07 00:00:00 +Tuesday 52 2001 %W %V %X 2002-01-01 00:00:00 +060 2004 %j %Y 2004-02-29 00:00:00 +4 53 1998 %w %u %Y 1998-12-31 00:00:00 15-01-2001 %d-%m-%Y %H:%i:%S 2001-01-15 00:00:00 15-01-20 %d-%m-%y 2020-01-15 00:00:00 15-2001-1 %d-%Y-%c 2001-01-15 00:00:00 @@ -162,16 +183,23 @@ date format datetime 2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 2003-01-02 23:11:12 10:20:10 %H:%i:%s 0000-00-00 10:20:10 10:20:10 %h:%i:%s.%f 0000-00-00 10:20:10 +10:20:10 %T 0000-00-00 10:20:10 10:20:10AM %h:%i:%s%p 0000-00-00 10:20:10 +10:20:10AM %r 0000-00-00 10:20:10 10:20:10.44AM %h:%i:%s.%f%p 0000-00-00 10:20:10.440000 15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 2001-01-15 12:59:58 15 September 2001 %d %M %Y 2001-09-15 00:00:00 15 SEPTEMB 2001 %d %M %Y 2001-09-15 00:00:00 15 MAY 2001 %d %b %Y 2001-05-15 00:00:00 +15th May 2001 %D %b %Y 2001-05-15 00:00:00 Sunday 15 MAY 2001 %W %d %b %Y 2001-05-15 00:00:00 Sund 15 MAY 2001 %W %d %b %Y 2001-05-15 00:00:00 Tuesday 00 2002 %W %U %Y 2002-01-01 00:00:00 Thursday 53 1998 %W %u %Y 1998-12-31 00:00:00 +Sunday 01 2001 %W %v %x 2001-01-07 00:00:00 +Tuesday 52 2001 %W %V %X 2002-01-01 00:00:00 +060 2004 %j %Y 2004-02-29 00:00:00 +4 53 1998 %w %u %Y 1998-12-31 00:00:00 15-01-2001 %d-%m-%Y %H:%i:%S 2001-01-15 00:00:00 15-01-20 %d-%m-%y 2020-01-15 00:00:00 15-2001-1 %d-%Y-%c 2001-01-15 00:00:00 @@ -186,16 +214,23 @@ date format date2 2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 2003-01-02 10:20:10 %H:%i:%s 0000-00-00 10:20:10 %h:%i:%s.%f 0000-00-00 +10:20:10 %T 0000-00-00 10:20:10AM %h:%i:%s%p 0000-00-00 +10:20:10AM %r 0000-00-00 10:20:10.44AM %h:%i:%s.%f%p 0000-00-00 15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 2001-01-15 15 September 2001 %d %M %Y 2001-09-15 15 SEPTEMB 2001 %d %M %Y 2001-09-15 15 MAY 2001 %d %b %Y 2001-05-15 +15th May 2001 %D %b %Y 2001-05-15 Sunday 15 MAY 2001 %W %d %b %Y 2001-05-15 Sund 15 MAY 2001 %W %d %b %Y 2001-05-15 Tuesday 00 2002 %W %U %Y 2002-01-01 Thursday 53 1998 %W %u %Y 1998-12-31 +Sunday 01 2001 %W %v %x 2001-01-07 +Tuesday 52 2001 %W %V %X 2002-01-01 +060 2004 %j %Y 2004-02-29 +4 53 1998 %w %u %Y 1998-12-31 15-01-2001 %d-%m-%Y %H:%i:%S 2001-01-15 15-01-20 %d-%m-%y 2020-01-15 15-2001-1 %d-%Y-%c 2001-01-15 @@ -210,16 +245,23 @@ date format time 2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 23:11:12 10:20:10 %H:%i:%s 10:20:10 10:20:10 %h:%i:%s.%f 10:20:10 +10:20:10 %T 10:20:10 10:20:10AM %h:%i:%s%p 10:20:10 +10:20:10AM %r 10:20:10 10:20:10.44AM %h:%i:%s.%f%p 10:20:10.440000 15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 12:59:58 15 September 2001 %d %M %Y 00:00:00 15 SEPTEMB 2001 %d %M %Y 00:00:00 15 MAY 2001 %d %b %Y 00:00:00 +15th May 2001 %D %b %Y 00:00:00 Sunday 15 MAY 2001 %W %d %b %Y 00:00:00 Sund 15 MAY 2001 %W %d %b %Y 00:00:00 Tuesday 00 2002 %W %U %Y 00:00:00 Thursday 53 1998 %W %u %Y 00:00:00 +Sunday 01 2001 %W %v %x 00:00:00 +Tuesday 52 2001 %W %V %X 00:00:00 +060 2004 %j %Y 00:00:00 +4 53 1998 %w %u %Y 00:00:00 15-01-2001 %d-%m-%Y %H:%i:%S 00:00:00 15-01-20 %d-%m-%y 00:00:00 15-2001-1 %d-%Y-%c 00:00:00 @@ -234,16 +276,23 @@ date format time2 2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 23:11:12 10:20:10 %H:%i:%s 10:20:10 10:20:10 %h:%i:%s.%f 10:20:10 +10:20:10 %T 10:20:10 10:20:10AM %h:%i:%s%p 10:20:10 +10:20:10AM %r 10:20:10 10:20:10.44AM %h:%i:%s.%f%p 10:20:10.440000 15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 12:59:58 15 September 2001 %d %M %Y 00:00:00 15 SEPTEMB 2001 %d %M %Y 00:00:00 15 MAY 2001 %d %b %Y 00:00:00 +15th May 2001 %D %b %Y 00:00:00 Sunday 15 MAY 2001 %W %d %b %Y 00:00:00 Sund 15 MAY 2001 %W %d %b %Y 00:00:00 Tuesday 00 2002 %W %U %Y 00:00:00 Thursday 53 1998 %W %u %Y 00:00:00 +Sunday 01 2001 %W %v %x 00:00:00 +Tuesday 52 2001 %W %V %X 00:00:00 +060 2004 %j %Y 00:00:00 +4 53 1998 %w %u %Y 00:00:00 15-01-2001 %d-%m-%Y %H:%i:%S 00:00:00 15-01-20 %d-%m-%y 00:00:00 15-2001-1 %d-%Y-%c 00:00:00 @@ -258,10 +307,13 @@ insert into t1 values ('15 Septembei 2001', '%d %M %Y'), ('15 Ju 2001', '%d %M %Y'), ('Sund 15 MA', '%W %d %b %Y'), -('Sunday 01 2001', '%W %V %X'), ('Thursdai 12 1998', '%W %u %Y'), -(NULL, get_format(DATE,'USA')), -('Tuesday 52 2001', '%W %V %X'); +('Sunday 01 2001', '%W %v %X'), +('Tuesday 52 2001', '%W %V %x'), +('Tuesday 52 2001', '%W %V %Y'), +('Tuesday 52 2001', '%W %u %x'), +('7 53 1998', '%w %u %Y'), +(NULL, get_format(DATE,'USA')); select date,format,str_to_date(date, format) as str_to_date from t1; date format str_to_date 2003-01-02 10:11:12 PM %Y-%m-%d %H:%i:%S %p NULL @@ -273,10 +325,13 @@ date format str_to_date 15 Septembei 2001 %d %M %Y NULL 15 Ju 2001 %d %M %Y NULL Sund 15 MA %W %d %b %Y NULL -Sunday 01 2001 %W %V %X NULL Thursdai 12 1998 %W %u %Y NULL +Sunday 01 2001 %W %v %X NULL +Tuesday 52 2001 %W %V %x NULL +Tuesday 52 2001 %W %V %Y NULL +Tuesday 52 2001 %W %u %x NULL +7 53 1998 %w %u %Y NULL NULL %m.%d.%Y NULL -Tuesday 52 2001 %W %V %X NULL select date,format,concat(str_to_date(date, format),'') as con from t1; date format con 2003-01-02 10:11:12 PM %Y-%m-%d %H:%i:%S %p NULL @@ -288,10 +343,13 @@ date format con 15 Septembei 2001 %d %M %Y NULL 15 Ju 2001 %d %M %Y NULL Sund 15 MA %W %d %b %Y NULL -Sunday 01 2001 %W %V %X NULL Thursdai 12 1998 %W %u %Y NULL +Sunday 01 2001 %W %v %X NULL +Tuesday 52 2001 %W %V %x NULL +Tuesday 52 2001 %W %V %Y NULL +Tuesday 52 2001 %W %u %x NULL +7 53 1998 %w %u %Y NULL NULL %m.%d.%Y NULL -Tuesday 52 2001 %W %V %X NULL truncate table t1; insert into t1 values ('10:20:10AM', '%h:%i:%s'), @@ -321,6 +379,9 @@ a select get_format(DATETIME, 'eur') as a; a %Y-%m-%d %H.%i.%s +select get_format(TIMESTAMP, 'eur') as a; +a +%Y-%m-%d %H.%i.%s select get_format(DATE, 'TEST') as a; a NULL diff --git a/mysql-test/r/endspace.result b/mysql-test/r/endspace.result index 4800bbf4ecb..96210a0e16d 100644 --- a/mysql-test/r/endspace.result +++ b/mysql-test/r/endspace.result @@ -52,13 +52,13 @@ select * from t1 ignore key (key1) where text1='teststring' or text1 like 'tests text1 teststring teststring -select * from t1 where text1='teststring' or text1 like 'teststring_%'; -text1 -teststring -teststring -select * from t1 where text1='teststring' or text1 > 'teststring\t'; -text1 -teststring +select concat('|', text1, '|') from t1 where text1='teststring' or text1 like 'teststring_%'; +concat('|', text1, '|') +|teststring | +|teststring| +select concat('|', text1, '|') from t1 where text1='teststring' or text1 > 'teststring\t'; +concat('|', text1, '|') +|teststring| select text1, length(text1) from t1 order by text1; text1 length(text1) nothing 7 @@ -77,7 +77,28 @@ concat('|', text1, '|') |teststring| |teststring | |teststring | +select concat('|', text1, '|') from t1 where text1='teststring' or text1 > 'teststring\t'; +concat('|', text1, '|') +|teststring| +|teststring | +select concat('|', text1, '|') from t1 where text1='teststring'; +concat('|', text1, '|') +|teststring| +select concat('|', text1, '|') from t1 where text1='teststring '; +concat('|', text1, '|') +|teststring | alter table t1 modify text1 text not null, pack_keys=1; +select concat('|', text1, '|') from t1 where text1='teststring'; +concat('|', text1, '|') +|teststring| +|teststring | +select concat('|', text1, '|') from t1 where text1='teststring '; +concat('|', text1, '|') +|teststring| +|teststring | +explain select concat('|', text1, '|') from t1 where text1='teststring '; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range key1 key1 22 NULL 2 Using where select * from t1 where text1 like 'teststring_%'; text1 teststring @@ -87,10 +108,10 @@ text1 teststring teststring teststring -select * from t1 where text1='teststring' or text1 > 'teststring\t'; -text1 -teststring -teststring +select concat('|', text1, '|') from t1 where text1='teststring' or text1 > 'teststring\t'; +concat('|', text1, '|') +|teststring| +|teststring | select concat('|', text1, '|') from t1 order by text1; concat('|', text1, '|') |nothing| diff --git a/mysql-test/r/exampledb.result b/mysql-test/r/exampledb.result new file mode 100644 index 00000000000..9bfb77c1c0b --- /dev/null +++ b/mysql-test/r/exampledb.result @@ -0,0 +1,6 @@ +drop table if exists t1; +CREATE TABLE t1 ( +Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL, +Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL +) ENGINE=example; +drop table t1; diff --git a/mysql-test/r/flush_block_commit.result b/mysql-test/r/flush_block_commit.result new file mode 100644 index 00000000000..17991f15382 --- /dev/null +++ b/mysql-test/r/flush_block_commit.result @@ -0,0 +1,23 @@ +drop table if exists t1; +create table t1 (a int) engine=innodb; +begin; +insert into t1 values(1); +flush tables with read lock; +select * from t1; +a + commit; +select * from t1; +a +unlock tables; +begin; +select * from t1 for update; +a +1 +begin; + select * from t1 for update; + flush tables with read lock; +commit; +a +1 +unlock tables; +drop table t1; diff --git a/mysql-test/r/fulltext2.result b/mysql-test/r/fulltext2.result index 0fdb7d4dbd1..0b1d8eb9a15 100644 --- a/mysql-test/r/fulltext2.result +++ b/mysql-test/r/fulltext2.result @@ -7,6 +7,15 @@ FULLTEXT KEY (a) repair table t1 quick; Table Op Msg_type Msg_text test.t1 repair status OK +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +optimize table t1; +Table Op Msg_type Msg_text +test.t1 optimize status OK +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK select count(*) from t1 where match a against ('aaaxxx'); count(*) 260 diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result index b64f0ca57dd..665f9262dea 100644 --- a/mysql-test/r/func_gconcat.result +++ b/mysql-test/r/func_gconcat.result @@ -18,7 +18,7 @@ explain extended select grp,group_concat(c) from t1 group by grp; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 9 Using filesort Warnings: -Note 1003 select `test`.`t1`.`grp` AS `grp`,group_concat(`test`.`t1`.`c` seperator ',') AS `group_concat(c)` from `test`.`t1` group by `test`.`t1`.`grp` +Note 1003 select `test`.`t1`.`grp` AS `grp`,group_concat(`test`.`t1`.`c` separator ',') AS `group_concat(c)` from `test`.`t1` group by `test`.`t1`.`grp` select grp,group_concat(a,c) from t1 group by grp; grp group_concat(a,c) 1 1a @@ -93,7 +93,7 @@ explain extended select grp,group_concat(distinct c order by c desc) from t1 gro id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 9 Using filesort Warnings: -Note 1003 select `test`.`t1`.`grp` AS `grp`,group_concat(distinct `test`.`t1`.`c` order by `test`.`t1`.`c` seperator ',') AS `group_concat(distinct c order by c desc)` from `test`.`t1` group by `test`.`t1`.`grp` +Note 1003 select `test`.`t1`.`grp` AS `grp`,group_concat(distinct `test`.`t1`.`c` order by `test`.`t1`.`c` separator ',') AS `group_concat(distinct c order by c desc)` from `test`.`t1` group by `test`.`t1`.`grp` select grp,group_concat(c order by c separator ",") from t1 group by grp; grp group_concat(c order by c separator ",") 1 a @@ -113,7 +113,7 @@ explain extended select grp,group_concat(distinct c order by c separator ",") fr id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 9 Using filesort Warnings: -Note 1003 select `test`.`t1`.`grp` AS `grp`,group_concat(distinct `test`.`t1`.`c` order by `test`.`t1`.`c` seperator ',') AS `group_concat(distinct c order by c separator ",")` from `test`.`t1` group by `test`.`t1`.`grp` +Note 1003 select `test`.`t1`.`grp` AS `grp`,group_concat(distinct `test`.`t1`.`c` order by `test`.`t1`.`c` separator ',') AS `group_concat(distinct c order by c separator ",")` from `test`.`t1` group by `test`.`t1`.`grp` select grp,group_concat(distinct c order by c desc separator ",") from t1 group by grp; grp group_concat(distinct c order by c desc separator ",") 1 a @@ -294,6 +294,21 @@ grp select group_concat(c order by (select mid(group_concat(c order by a),1,5) from t2 where t2.a=t1.a) desc) as grp from t1; grp 2,4,3,5 +select t1.a, group_concat(c order by (select c from t2 where t2.a=t1.a limit 1)) as grp from t1 group by 1; +a grp +1 2 +2 4,3 +3 5 +select t1.a, group_concat(c order by (select mid(group_concat(c order by a),1,5) from t2 where t2.a=t1.a)) as grp from t1 group by 1; +a grp +1 2 +2 4,3 +3 5 +select t1.a, group_concat(c order by (select mid(group_concat(c order by a),1,5) from t2 where t2.a=t1.a) desc) as grp from t1 group by 1; +a grp +1 2 +2 4,3 +3 5 select a,c,(select group_concat(c order by a) from t2 where a=t1.a) as grp from t1 order by grp; a c grp 3 5 3,3 @@ -321,3 +336,33 @@ HAVING LEFT(names, 1) ='J'; names John###Anna###Bill DROP TABLE t1; +CREATE TABLE t1 ( a int, b TEXT ); +INSERT INTO t1 VALUES (1,'First Row'), (2,'Second Row'); +SELECT GROUP_CONCAT(b ORDER BY b) FROM t1 GROUP BY a; +GROUP_CONCAT(b ORDER BY b) +First Row +Second Row +DROP TABLE t1; +CREATE TABLE t1 (a_id tinyint(4) NOT NULL default '0', PRIMARY KEY (a_id)) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t1 VALUES (1),(2),(3); +CREATE TABLE t2 (b_id tinyint(4) NOT NULL default '0',b_a tinyint(4) NOT NULL default '0', PRIMARY KEY (b_id), KEY (b_a), +CONSTRAINT fk_b_a FOREIGN KEY (b_a) REFERENCES t1 (a_id) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t2 VALUES (1,1),(2,1),(3,1),(4,2),(5,2); +SELECT * FROM (SELECT t1.*,GROUP_CONCAT(t2.b_id SEPARATOR ',') as b_list FROM (t1 LEFT JOIN (t2) on t1.a_id = t2.b_a) GROUP BY t1.a_id ) AS xyz; +a_id b_list +1 1,2,3 +2 4,5 +3 NULL +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 (A_ID INT NOT NULL,A_DESC CHAR(3) NOT NULL,PRIMARY KEY (A_ID)); +INSERT INTO t1 VALUES (1,'ABC'), (2,'EFG'), (3,'HIJ'); +CREATE TABLE t2 (A_ID INT NOT NULL,B_DESC CHAR(3) NOT NULL,PRIMARY KEY (A_ID,B_DESC)); +INSERT INTO t2 VALUES (1,'A'),(1,'B'),(3,'F'); +SELECT t1.A_ID, GROUP_CONCAT(t2.B_DESC) AS B_DESC FROM t1 LEFT JOIN t2 ON t1.A_ID=t2.A_ID GROUP BY t1.A_ID ORDER BY t1.A_DESC; +A_ID B_DESC +1 A,B +2 NULL +3 F +DROP TABLE t1; +DROP TABLE t2; diff --git a/mysql-test/r/func_if.result b/mysql-test/r/func_if.result index 36bd9a36d1c..307d1021cdb 100644 --- a/mysql-test/r/func_if.result +++ b/mysql-test/r/func_if.result @@ -43,7 +43,7 @@ explain extended select if(u=1,st,binary st) s from t1 where st like "%a%" order id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where; Using filesort Warnings: -Note 1003 select if((`test`.`t1`.`u` = 1),`test`.`t1`.`st`,(`test`.`t1`.`st` collate _latin1'BINARY')) AS `s` from `test`.`t1` where (`test`.`t1`.`st` like _latin1'%a%') order by if((`test`.`t1`.`u` = 1),`test`.`t1`.`st`,(`test`.`t1`.`st` collate _latin1'BINARY')) +Note 1003 select if((test.t1.u = 1),test.t1.st,cast(test.t1.st as char charset binary)) AS `s` from test.t1 where (test.t1.st like _latin1'%a%') order by if((test.t1.u = 1),test.t1.st,cast(test.t1.st as char charset binary)) select nullif(u=0, 'test') from t1; nullif(u=0, 'test') NULL diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index 025ea02e454..b132edab81d 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -148,6 +148,16 @@ id select_type table type possible_keys key key_len ref rows Extra Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (_latin1'a' in (`test`.`t1`.`a`,`test`.`t1`.`b`,(`test`.`t1`.`c` collate _latin1'latin1_bin'))) drop table t1; +set names utf8; +create table t1 (a char(10) character set utf8 not null); +insert into t1 values ('bbbb'),(_koi8r''),(_latin1'ÄÄÄÄ'); +select a from t1 where a in ('bbbb',_koi8r'',_latin1'ÄÄÄÄ') order by a; +a + +bbbb +ÑÑÑÑ +drop table t1; +set names latin1; select '1.0' in (1,2); '1.0' in (1,2) 1 diff --git a/mysql-test/r/func_like.result b/mysql-test/r/func_like.result index e32a4dcd85a..e9434b1749d 100644 --- a/mysql-test/r/func_like.result +++ b/mysql-test/r/func_like.result @@ -155,3 +155,6 @@ select * from t1 where a like '%ESKA%'; a PPUH PESKA-I Maria Struniarska DROP TABLE t1; +select _cp866'aaaaaaaaa' like _cp866'%aaaa%' collate cp866_bin; +_cp866'aaaaaaaaa' like _cp866'%aaaa%' collate cp866_bin +1 diff --git a/mysql-test/r/func_math.result b/mysql-test/r/func_math.result index 12eef4aa881..90aa04515d7 100644 --- a/mysql-test/r/func_math.result +++ b/mysql-test/r/func_math.result @@ -43,7 +43,7 @@ Warnings: Note 1003 select abs(-(10)) AS `abs(-10)`,sign(-(5)) AS `sign(-5)`,sign(5) AS `sign(5)`,sign(0) AS `sign(0)` select log(exp(10)),exp(log(sqrt(10))*2),log(-1),log(NULL),log(1,1),log(3,9),log(-1,2),log(NULL,2); log(exp(10)) exp(log(sqrt(10))*2) log(-1) log(NULL) log(1,1) log(3,9) log(-1,2) log(NULL,2) -10.000000 10.000000 NULL NULL NULL 2.000000 NULL NULL +10 10 NULL NULL NULL 2 NULL NULL explain extended select log(exp(10)),exp(log(sqrt(10))*2),log(-1),log(NULL),log(1,1),log(3,9),log(-1,2),log(NULL,2); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used @@ -51,7 +51,7 @@ Warnings: Note 1003 select log(exp(10)) AS `log(exp(10))`,exp((log(sqrt(10)) * 2)) AS `exp(log(sqrt(10))*2)`,log(-(1)) AS `log(-1)`,log(NULL) AS `log(NULL)`,log(1,1) AS `log(1,1)`,log(3,9) AS `log(3,9)`,log(-(1),2) AS `log(-1,2)`,log(NULL,2) AS `log(NULL,2)` select ln(exp(10)),exp(ln(sqrt(10))*2),ln(-1),ln(0),ln(NULL); ln(exp(10)) exp(ln(sqrt(10))*2) ln(-1) ln(0) ln(NULL) -10.000000 10.000000 NULL NULL NULL +10 10 NULL NULL NULL explain extended select ln(exp(10)),exp(ln(sqrt(10))*2),ln(-1),ln(0),ln(NULL); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used @@ -59,7 +59,7 @@ Warnings: Note 1003 select ln(exp(10)) AS `ln(exp(10))`,exp((ln(sqrt(10)) * 2)) AS `exp(ln(sqrt(10))*2)`,ln(-(1)) AS `ln(-1)`,ln(0) AS `ln(0)`,ln(NULL) AS `ln(NULL)` select log2(8),log2(15),log2(-2),log2(0),log2(NULL); log2(8) log2(15) log2(-2) log2(0) log2(NULL) -3.000000 3.906891 NULL NULL NULL +3 3.9068905956085 NULL NULL NULL explain extended select log2(8),log2(15),log2(-2),log2(0),log2(NULL); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used @@ -67,7 +67,7 @@ Warnings: Note 1003 select log2(8) AS `log2(8)`,log2(15) AS `log2(15)`,log2(-(2)) AS `log2(-2)`,log2(0) AS `log2(0)`,log2(NULL) AS `log2(NULL)` select log10(100),log10(18),log10(-4),log10(0),log10(NULL); log10(100) log10(18) log10(-4) log10(0) log10(NULL) -2.000000 1.255273 NULL NULL NULL +2 1.2552725051033 NULL NULL NULL explain extended select log10(100),log10(18),log10(-4),log10(0),log10(NULL); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used @@ -75,7 +75,7 @@ Warnings: Note 1003 select log10(100) AS `log10(100)`,log10(18) AS `log10(18)`,log10(-(4)) AS `log10(-4)`,log10(0) AS `log10(0)`,log10(NULL) AS `log10(NULL)` select pow(10,log10(10)),power(2,4); pow(10,log10(10)) power(2,4) -10.000000 16.000000 +10 16 explain extended select pow(10,log10(10)),power(2,4); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used @@ -90,35 +90,35 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 select sql_no_cache rand(999999) AS `rand(999999)`,rand() AS `rand()` -select pi(),sin(pi()/2),cos(pi()/2),abs(tan(pi())),cot(1),asin(1),acos(0),atan(1); -pi() sin(pi()/2) cos(pi()/2) abs(tan(pi())) cot(1) asin(1) acos(0) atan(1) -3.141593 1.000000 0.000000 0.000000 0.64209262 1.570796 1.570796 0.785398 -explain extended select pi(),sin(pi()/2),cos(pi()/2),abs(tan(pi())),cot(1),asin(1),acos(0),atan(1); +select pi(),format(sin(pi()/2),6),format(cos(pi()/2),6),format(abs(tan(pi())),6),format(cot(1),6),format(asin(1),6),format(acos(0),6),format(atan(1),6); +pi() format(sin(pi()/2),6) format(cos(pi()/2),6) format(abs(tan(pi())),6) format(cot(1),6) format(asin(1),6) format(acos(0),6) format(atan(1),6) +3.141593 1.000000 0.000000 0.000000 0.642093 1.570796 1.570796 0.785398 +explain extended select pi(),format(sin(pi()/2),6),format(cos(pi()/2),6),format(abs(tan(pi())),6),format(cot(1),6),format(asin(1),6),format(acos(0),6),format(atan(1),6); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select pi() AS `pi()`,sin((pi() / 2)) AS `sin(pi()/2)`,cos((pi() / 2)) AS `cos(pi()/2)`,abs(tan(pi())) AS `abs(tan(pi()))`,(1 / tan(1)) AS `cot(1)`,asin(1) AS `asin(1)`,acos(0) AS `acos(0)`,atan(1) AS `atan(1)` +Note 1003 select pi() AS `pi()`,format(sin((pi() / 2)),6) AS `format(sin(pi()/2),6)`,format(cos((pi() / 2)),6) AS `format(cos(pi()/2),6)`,format(abs(tan(pi())),6) AS `format(abs(tan(pi())),6)`,format((1 / tan(1)),6) AS `format(cot(1),6)`,format(asin(1),6) AS `format(asin(1),6)`,format(acos(0),6) AS `format(acos(0),6)`,format(atan(1),6) AS `format(atan(1),6)` select degrees(pi()),radians(360); degrees(pi()) radians(360) 180 6.2831853071796 SELECT ACOS(1.0); ACOS(1.0) -0.000000 +0 SELECT ASIN(1.0); ASIN(1.0) -1.570796 +1.5707963267949 SELECT ACOS(0.2*5.0); ACOS(0.2*5.0) -0.000000 +0 SELECT ACOS(0.5*2.0); ACOS(0.5*2.0) -0.000000 +0 SELECT ASIN(0.8+0.2); ASIN(0.8+0.2) -1.570796 +1.5707963267949 SELECT ASIN(1.2-0.2); ASIN(1.2-0.2) -1.570796 +1.5707963267949 explain extended select degrees(pi()),radians(360); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used diff --git a/mysql-test/r/func_set.result b/mysql-test/r/func_set.result index 4918617f85f..2431406c128 100644 --- a/mysql-test/r/func_set.result +++ b/mysql-test/r/func_set.result @@ -41,8 +41,7 @@ interval(null, 1, 10, 100) -1 drop table if exists t1,t2; create table t1 (id int(10) not null unique); -create table t2 (id int(10) not null primary key, -val int(10) not null); +create table t2 (id int(10) not null primary key, val int(10) not null); insert into t1 values (1),(2),(4); insert into t2 values (1,1),(2,1),(3,1),(4,2); select one.id, elt(two.val,'one','two') from t1 one, t2 two where two.id=one.id; @@ -56,3 +55,12 @@ id elt(two.val,'one','two') 2 one 4 two drop table t1,t2; +select find_in_set(binary 'a',binary 'A,B,C'); +find_in_set(binary 'a',binary 'A,B,C') +0 +select find_in_set('a',binary 'A,B,C'); +find_in_set('a',binary 'A,B,C') +0 +select find_in_set(binary 'a', 'A,B,C'); +find_in_set(binary 'a', 'A,B,C') +0 diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index e07ee4f0add..7b2fc4b21a5 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -638,7 +638,7 @@ explain extended select md5('hello'), sha('abc'), sha1('abc'), soundex(''), 'moo id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select md5(_latin1'hello') AS `md5('hello')`,sha(_latin1'abc') AS `sha('abc')`,sha(_latin1'abc') AS `sha1('abc')`,soundex(_latin1'') AS `soundex('')`,(soundex(_latin1'mood') = soundex(_latin1'mud')) AS `'mood' sounds like 'mud'`,aes_decrypt(aes_encrypt(_latin1'abc',_latin1'1'),_latin1'1') AS `aes_decrypt(aes_encrypt('abc','1'),'1')`,concat(_latin1'*',repeat(_latin1' ',5),_latin1'*') AS `concat('*',space(5),'*')`,reverse(_latin1'abc') AS `reverse('abc')`,rpad(_latin1'a',4,_latin1'1') AS `rpad('a',4,'1')`,lpad(_latin1'a',4,_latin1'1') AS `lpad('a',4,'1')`,concat_ws(_latin1',',_latin1'',NULL,_latin1'a') AS `concat_ws(',','',NULL,'a')`,make_set(255,_latin2'a',_latin2'b',_latin2'c') AS `make_set(255,_latin2'a',_latin2'b',_latin2'c')`,elt(2,1) AS `elt(2,1)`,locate(_latin1'a',_latin1'b',2) AS `locate("a","b",2)`,format(130,10) AS `format(130,10)`,char(0) AS `char(0)`,conv(130,16,10) AS `conv(130,16,10)`,hex(130) AS `hex(130)`,(_latin1'HE' collate _latin1'BINARY') AS `binary 'HE'`,export_set(255,_latin2'y',_latin2'n',_latin2' ') AS `export_set(255,_latin2'y',_latin2'n',_latin2' ')`,field((_latin1'b' collate _latin1'latin1_bin'),_latin1'A',_latin1'B') AS `FIELD('b' COLLATE latin1_bin,'A','B')`,find_in_set(_latin1'B',_latin1'a,b,c,d') AS `FIND_IN_SET(_latin1'B',_latin1'a,b,c,d')`,collation(conv(130,16,10)) AS `collation(conv(130,16,10))`,coercibility(conv(130,16,10)) AS `coercibility(conv(130,16,10))`,length(_latin1'\n \r\0\\_\\%\\') AS `length('\n\t\r\b\0\_\%\\')`,bit_length(_latin1'\n \r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`,bit_length(_latin1'\n \r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`,concat(_latin1'monty',_latin1' was here ',_latin1'again') AS `concat('monty',' was here ','again')`,length(_latin1'hello') AS `length('hello')`,char(ascii(_latin1'h')) AS `char(ascii('h'))`,ord(_latin1'h') AS `ord('h')`,quote((1 / 0)) AS `quote(1/0)`,crc32(_latin1'123') AS `crc32("123")`,replace(_latin1'aaaa',_latin1'a',_latin1'b') AS `replace('aaaa','a','b')`,insert(_latin1'txs',2,1,_latin1'hi') AS `insert('txs',2,1,'hi')`,left(_latin2'a',1) AS `left(_latin2'a',1)`,right(_latin2'a',1) AS `right(_latin2'a',1)`,lcase(_latin2'a') AS `lcase(_latin2'a')`,ucase(_latin2'a') AS `ucase(_latin2'a')`,substr(_latin1'abcdefg',3,2) AS `SUBSTR('abcdefg',3,2)`,substr_index(_latin1'1abcd;2abcd;3abcd;4abcd',_latin1';',2) AS `substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2)`,trim(_latin2' a ') AS `trim(_latin2' a ')`,ltrim(_latin2' a ') AS `ltrim(_latin2' a ')`,rtrim(_latin2' a ') AS `rtrim(_latin2' a ')`,decode(encode(repeat(_latin1'a',100000))) AS `decode(encode(repeat("a",100000),"monty"),"monty")` +Note 1003 select md5(_latin1'hello') AS `md5('hello')`,sha(_latin1'abc') AS `sha('abc')`,sha(_latin1'abc') AS `sha1('abc')`,soundex(_latin1'') AS `soundex('')`,(soundex(_latin1'mood') = soundex(_latin1'mud')) AS `'mood' sounds like 'mud'`,aes_decrypt(aes_encrypt(_latin1'abc',_latin1'1'),_latin1'1') AS `aes_decrypt(aes_encrypt('abc','1'),'1')`,concat(_latin1'*',repeat(_latin1' ',5),_latin1'*') AS `concat('*',space(5),'*')`,reverse(_latin1'abc') AS `reverse('abc')`,rpad(_latin1'a',4,_latin1'1') AS `rpad('a',4,'1')`,lpad(_latin1'a',4,_latin1'1') AS `lpad('a',4,'1')`,concat_ws(_latin1',',_latin1'',NULL,_latin1'a') AS `concat_ws(',','',NULL,'a')`,make_set(255,_latin2'a',_latin2'b',_latin2'c') AS `make_set(255,_latin2'a',_latin2'b',_latin2'c')`,elt(2,1) AS `elt(2,1)`,locate(_latin1'a',_latin1'b',2) AS `locate("a","b",2)`,format(130,10) AS `format(130,10)`,char(0) AS `char(0)`,conv(130,16,10) AS `conv(130,16,10)`,hex(130) AS `hex(130)`,cast(_latin1'HE' as char charset binary) AS `binary 'HE'`,export_set(255,_latin2'y',_latin2'n',_latin2' ') AS `export_set(255,_latin2'y',_latin2'n',_latin2' ')`,field((_latin1'b' collate _latin1'latin1_bin'),_latin1'A',_latin1'B') AS `FIELD('b' COLLATE latin1_bin,'A','B')`,find_in_set(_latin1'B',_latin1'a,b,c,d') AS `FIND_IN_SET(_latin1'B',_latin1'a,b,c,d')`,collation(conv(130,16,10)) AS `collation(conv(130,16,10))`,coercibility(conv(130,16,10)) AS `coercibility(conv(130,16,10))`,length(_latin1'\n \r\0\\_\\%\\') AS `length('\n\t\r\b\0\_\%\\')`,bit_length(_latin1'\n \r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`,bit_length(_latin1'\n \r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`,concat(_latin1'monty',_latin1' was here ',_latin1'again') AS `concat('monty',' was here ','again')`,length(_latin1'hello') AS `length('hello')`,char(ascii(_latin1'h')) AS `char(ascii('h'))`,ord(_latin1'h') AS `ord('h')`,quote((1 / 0)) AS `quote(1/0)`,crc32(_latin1'123') AS `crc32("123")`,replace(_latin1'aaaa',_latin1'a',_latin1'b') AS `replace('aaaa','a','b')`,insert(_latin1'txs',2,1,_latin1'hi') AS `insert('txs',2,1,'hi')`,left(_latin2'a',1) AS `left(_latin2'a',1)`,right(_latin2'a',1) AS `right(_latin2'a',1)`,lcase(_latin2'a') AS `lcase(_latin2'a')`,ucase(_latin2'a') AS `ucase(_latin2'a')`,substr(_latin1'abcdefg',3,2) AS `SUBSTR('abcdefg',3,2)`,substr_index(_latin1'1abcd;2abcd;3abcd;4abcd',_latin1';',2) AS `substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2)`,trim(_latin2' a ') AS `trim(_latin2' a ')`,ltrim(_latin2' a ') AS `ltrim(_latin2' a ')`,rtrim(_latin2' a ') AS `rtrim(_latin2' a ')`,decode(encode(repeat(_latin1'a',100000))) AS `decode(encode(repeat("a",100000),"monty"),"monty")` SELECT lpad(12345, 5, "#"); lpad(12345, 5, "#") 12345 @@ -673,3 +673,6 @@ c1 c2 2147483647 4294967295 -2147483648 0 drop table t1; +select left(1234, 3) + 0; +left(1234, 3) + 0 +123 diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index d8ea18f9744..864d9d04ff5 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -466,3 +466,22 @@ insert IGNORE into t1 values ('Garbage'); ERROR HY000: Unknown error alter table t1 add spatial index(a); drop table t1; +create table t1(a geometry not null, spatial index(a)); +insert into t1 values +(GeomFromText('POINT(1 1)')), (GeomFromText('POINT(3 3)')), +(GeomFromText('POINT(4 4)')), (GeomFromText('POINT(6 6)')); +select AsText(a) from t1 where +MBRContains(GeomFromText('Polygon((0 0, 0 2, 2 2, 2 0, 0 0))'), a) +or +MBRContains(GeomFromText('Polygon((2 2, 2 5, 5 5, 5 2, 2 2))'), a); +AsText(a) +POINT(1 1) +POINT(3 3) +POINT(4 4) +select AsText(a) from t1 where +MBRContains(GeomFromText('Polygon((0 0, 0 2, 2 2, 2 0, 0 0))'), a) +and +MBRContains(GeomFromText('Polygon((0 0, 0 7, 7 7, 7 0, 0 0))'), a); +AsText(a) +POINT(1 1) +drop table t1; diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result index 5d18c3f483f..866c19155db 100644 --- a/mysql-test/r/grant.result +++ b/mysql-test/r/grant.result @@ -229,6 +229,25 @@ GRANT SELECT (ËÏÌ) ON `ÂÄ`.`ÔÁÂ` TO 'ÀÚĊÒ'@'localhost' REVOKE SELECT (ËÏÌ) ON ÂÄ.ÔÁ FROM ÀÚĊÒ@localhost; DROP DATABASE ÂÄ; SET NAMES latin1; +insert into mysql.user (host, user) values ('localhost', 'test11'); +insert into mysql.db (host, db, user, select_priv) values +('localhost', 'a%', 'test11', 'Y'), ('localhost', 'ab%', 'test11', 'Y'); +alter table mysql.db order by db asc; +flush privileges; +show grants for test11@localhost; +Grants for test11@localhost +GRANT USAGE ON *.* TO 'test11'@'localhost' +GRANT SELECT ON `ab%`.* TO 'test11'@'localhost' +GRANT SELECT ON `a%`.* TO 'test11'@'localhost' +alter table mysql.db order by db desc; +flush privileges; +show grants for test11@localhost; +Grants for test11@localhost +GRANT USAGE ON *.* TO 'test11'@'localhost' +GRANT SELECT ON `ab%`.* TO 'test11'@'localhost' +GRANT SELECT ON `a%`.* TO 'test11'@'localhost' +delete from mysql.user where user='test11'; +delete from mysql.db where user='test11'; SHOW PRIVILEGES; Privilege Context Comment Alter Tables To alter the table diff --git a/mysql-test/r/have_csv.require b/mysql-test/r/have_csv.require new file mode 100644 index 00000000000..cc2fb28289c --- /dev/null +++ b/mysql-test/r/have_csv.require @@ -0,0 +1,2 @@ +Variable_name Value +have_csv YES diff --git a/mysql-test/r/have_exampledb.require b/mysql-test/r/have_exampledb.require new file mode 100644 index 00000000000..4b0938660fe --- /dev/null +++ b/mysql-test/r/have_exampledb.require @@ -0,0 +1,2 @@ +Variable_name Value +have_exampledb YES diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result index 0556aa3a2b7..2e94974e953 100644 --- a/mysql-test/r/having.result +++ b/mysql-test/r/having.result @@ -87,3 +87,44 @@ sqty 5 9 drop table t1; +CREATE TABLE t1 ( +`id` bigint(20) NOT NULL default '0', +`description` text +) ENGINE=MyISAM; +CREATE TABLE t2 ( +`id` bigint(20) NOT NULL default '0', +`description` varchar(20) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1, 'test'); +INSERT INTO t2 VALUES (1, 'test'); +CREATE TABLE t3 ( +`id` bigint(20) NOT NULL default '0', +`order_id` bigint(20) NOT NULL default '0' +) ENGINE=MyISAM; +select +a.id, a.description, +count(b.id) as c +from t1 a left join t3 b on a.id=b.order_id +group by a.id, a.description +having (a.description is not null) and (c=0); +id description c +1 test 0 +select +a.*, +count(b.id) as c +from t2 a left join t3 b on a.id=b.order_id +group by a.id, a.description +having (a.description is not null) and (c=0); +id description c +1 test 0 +INSERT INTO t1 VALUES (2, 'test2'); +select +a.id, a.description, +count(b.id) as c +from t1 a left join t3 b on a.id=b.order_id +group by a.id, a.description +having (a.description is not null) and (c=0); +id description c +1 test 0 +2 test2 0 +drop table t1,t2,t3; diff --git a/mysql-test/r/heap.result b/mysql-test/r/heap.result index 6100301b2c6..a33d26f9efa 100644 --- a/mysql-test/r/heap.result +++ b/mysql-test/r/heap.result @@ -217,3 +217,13 @@ DELETE from t1 where a < 100; SELECT * from t1; a DROP TABLE t1; +CREATE TABLE `job_titles` ( +`job_title_id` int(6) unsigned NOT NULL default '0', +`job_title` char(18) NOT NULL default '', +PRIMARY KEY (`job_title_id`), +UNIQUE KEY `job_title_id` (`job_title_id`,`job_title`) +) ENGINE=HEAP; +SELECT MAX(job_title_id) FROM job_titles; +MAX(job_title_id) +NULL +DROP TABLE job_titles; diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index db9b051a58f..dc763472b0e 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -283,6 +283,12 @@ ID Value1 Value2 SELECT * FROM t1 NATURAL JOIN t2 WHERE (Value1 = 'A' AND Value2 <> 'B') AND 1; ID Value1 Value2 drop table t1,t2; +CREATE TABLE t1 (a int); +CREATE TABLE t2 (b int); +CREATE TABLE t3 (c int); +SELECT * FROM t1 NATURAL JOIN t2 NATURAL JOIN t3; +a b c +DROP TABLE t1, t2, t3; create table t1 (i int); create table t2 (i int); create table t3 (i int); diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 7915521a9b7..ec377b92371 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -628,7 +628,7 @@ insert into t2 values (10,1),(20,2),(30,3); explain select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index NULL PRIMARY 4 NULL 3 Using index -1 SIMPLE t1 eq_ref PRIMARY PRIMARY 2 const 1 Using index +1 SIMPLE t1 const PRIMARY PRIMARY 2 const 1 Using where; Using index select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30; fooID barID fooID 10 1 NULL @@ -676,3 +676,72 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 drop table t1, t2, t3; +create table t1 ( +match_id tinyint(3) unsigned not null auto_increment, +home tinyint(3) unsigned default '0', +unique key match_id (match_id), +key match_id_2 (match_id) +); +insert into t1 values("1", "2"); +create table t2 ( +player_id tinyint(3) unsigned default '0', +match_1_h tinyint(3) unsigned default '0', +key player_id (player_id) +); +insert into t2 values("1", "5"); +insert into t2 values("2", "9"); +insert into t2 values("3", "3"); +insert into t2 values("4", "7"); +insert into t2 values("5", "6"); +insert into t2 values("6", "8"); +insert into t2 values("7", "4"); +insert into t2 values("8", "12"); +insert into t2 values("9", "11"); +insert into t2 values("10", "10"); +explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from +(t2 s left join t1 m on m.match_id = 1) +order by m.match_id desc; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE s ALL NULL NULL NULL NULL 10 +1 SIMPLE m const match_id,match_id_2 match_id 1 const 1 Using where +explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from +(t2 s left join t1 m on m.match_id = 1) +order by UUX desc; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE s ALL NULL NULL NULL NULL 10 Using temporary; Using filesort +1 SIMPLE m const match_id,match_id_2 match_id 1 const 1 Using where +select s.*, '*', m.*, (s.match_1_h - m.home) UUX from +(t2 s left join t1 m on m.match_id = 1) +order by UUX desc; +player_id match_1_h * match_id home UUX +8 12 * 1 2 10 +9 11 * 1 2 9 +10 10 * 1 2 8 +2 9 * 1 2 7 +6 8 * 1 2 6 +4 7 * 1 2 5 +5 6 * 1 2 4 +1 5 * 1 2 3 +7 4 * 1 2 2 +3 3 * 1 2 1 +explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from +t2 s straight_join t1 m where m.match_id = 1 +order by UUX desc; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE s ALL NULL NULL NULL NULL 10 Using temporary; Using filesort +1 SIMPLE m const match_id,match_id_2 match_id 1 const 1 Using where +select s.*, '*', m.*, (s.match_1_h - m.home) UUX from +t2 s straight_join t1 m where m.match_id = 1 +order by UUX desc; +player_id match_1_h * match_id home UUX +8 12 * 1 2 10 +9 11 * 1 2 9 +10 10 * 1 2 8 +2 9 * 1 2 7 +6 8 * 1 2 6 +4 7 * 1 2 5 +5 6 * 1 2 4 +1 5 * 1 2 3 +7 4 * 1 2 2 +3 3 * 1 2 1 +drop table t1, t2; diff --git a/mysql-test/r/key.result b/mysql-test/r/key.result index dd03ef73883..bb553825caa 100644 --- a/mysql-test/r/key.result +++ b/mysql-test/r/key.result @@ -76,13 +76,12 @@ CCident varchar(50) DEFAULT '' NOT NULL, PRIMARY KEY (name,author,category) ); INSERT INTO t1 VALUES -('patnom','patauteur',0,'p.favre@cryo-networks.fr',NULL,NULL,'#p2sndnq6ae5g1u6t','essai\nsalut','scol://195.242.78.119:patauteur.patnom',NULL,NULL,NULL,950036174,-882087474,NULL,3,0,3,'1','Pub/patnom/futur_divers.scs',NULL,'pat','CC1'); +('patnom','patauteur',0,'p.favre@cryo-networks.fr',NULL,NULL,'#p2sndnq6ae5g1u6t','essai salut','scol://195.242.78.119:patauteur.patnom',NULL,NULL,NULL,950036174,-882087474,NULL,3,0,3,'1','Pub/patnom/futur_divers.scs',NULL,'pat','CC1'); INSERT INTO t1 VALUES ('LeNomDeMonSite','Marc',0,'m.barilley@cryo-networks.fr',NULL,NULL,NULL,NULL,'scol://195.242.78.119:Marc.LeNomDeMonSite',NULL,NULL,NULL,950560434,-881563214,NULL,3,0,3,'1','Pub/LeNomDeMonSite/domus_hibere.scs',NULL,'Marq','CC1'); select * from t1 where name='patnom' and author='patauteur' and category=0; name author category email password proxy bitmap msg urlscol urlhttp timeout nbcnx creation livinguntil lang type subcat subtype reg scs capacity userISP CCident -patnom patauteur 0 p.favre@cryo-networks.fr NULL NULL #p2sndnq6ae5g1u6t essai -salut scol://195.242.78.119:patauteur.patnom NULL NULL NULL 950036174 -882087474 NULL 3 0 3 1 Pub/patnom/futur_divers.scs NULL pat CC1 +patnom patauteur 0 p.favre@cryo-networks.fr NULL NULL #p2sndnq6ae5g1u6t essai salut scol://195.242.78.119:patauteur.patnom NULL NULL NULL 950036174 -882087474 NULL 3 0 3 1 Pub/patnom/futur_divers.scs NULL pat CC1 drop table t1; create table t1 ( @@ -235,3 +234,36 @@ SELECT numeropost FROM t1 WHERE numreponse='1'; numeropost 1 drop table t1; +create table t1 (c varchar(30) character set utf8, t text character set utf8, unique (c(2)), unique (t(3))) engine=myisam; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c` varchar(30) character set utf8 default NULL, + `t` text character set utf8, + UNIQUE KEY `c` (`c`(2)), + UNIQUE KEY `t` (`t`(3)) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +insert t1 values ('cccc', 'tttt'), +(0xD0B1212223D0B1D0B1D0B1D0B1D0B1, 0xD0B1D0B1212223D0B1D0B1D0B1D0B1), +(0xD0B1222123D0B1D0B1D0B1D0B1D0B1, 0xD0B1D0B1222123D0B1D0B1D0B1D0B1); +insert t1 (c) values ('cc22'); +ERROR 23000: Duplicate entry 'cc22' for key 1 +insert t1 (t) values ('ttt22'); +ERROR 23000: Duplicate entry 'ttt22' for key 2 +insert t1 (c) values (0xD0B1212322D0B1D0B1D0B1D0B1D0B1); +ERROR 23000: Duplicate entry 'ħ!#"' for key 1 +insert t1 (t) values (0xD0B1D0B1212322D0B1D0B1D0B1D0B1); +ERROR 23000: Duplicate entry 'ħħ!#"ħ' for key 2 +select c from t1 where c='cccc'; +c +cccc +select t from t1 where t='tttt'; +t +tttt +select c from t1 where c=0xD0B1212223D0B1D0B1D0B1D0B1D0B1; +c +?!"#????? +select t from t1 where t=0xD0B1D0B1212223D0B1D0B1D0B1D0B1; +t +??!"#???? +drop table t1; diff --git a/mysql-test/r/lowercase_table.result b/mysql-test/r/lowercase_table.result index d400dcd9795..a8bc2e3d342 100644 --- a/mysql-test/r/lowercase_table.result +++ b/mysql-test/r/lowercase_table.result @@ -1,4 +1,6 @@ drop table if exists t1,t2,t3,t4; +drop table if exists t0,t5,t6,t7,t8,t9; +drop database if exists mysqltest; create table T1 (id int primary key, Word varchar(40) not null, Index(Word)); create table t4 (id int primary key, Word varchar(40) not null); INSERT INTO T1 VALUES (1, 'a'), (2, 'b'), (3, 'c'); @@ -42,6 +44,26 @@ select count(bags.a) from t1 as Bags; count(bags.a) 0 drop table t1; +create database mysqltest; +use MYSQLTEST; +create table t1 (a int); +select T1.a from MYSQLTEST.T1; +a +select t1.a from MYSQLTEST.T1; +a +select mysqltest.t1.* from MYSQLTEST.t1; +a +select MYSQLTEST.t1.* from MYSQLTEST.t1; +a +select MYSQLTEST.T1.* from MYSQLTEST.T1; +a +select MYSQLTEST.T1.* from T1; +a +alter table t1 rename to T1; +select MYSQLTEST.t1.* from MYSQLTEST.t1; +a +drop database mysqltest; +use test; create table t1 (a int); create table t2 (a int); delete p1.*,P2.* from t1 as p1, t2 as p2 where p1.a=P2.a; diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index 83f8230f11c..5755033190b 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -642,3 +642,12 @@ x y 1 3 1 2 drop table t1,t2,t3; +create table t1 (a int); +create table t2 (a int); +insert into t1 values (0); +insert into t2 values (1); +create table t3 engine=merge union=(t1, t2) select * from t1; +ERROR HY000: You can't specify target table 't1' for update in FROM clause +create table t3 engine=merge union=(t1, t2) select * from t2; +ERROR HY000: You can't specify target table 't2' for update in FROM clause +drop table t1, t2; diff --git a/mysql-test/r/metadata.result b/mysql-test/r/metadata.result index 80a01a0ca90..ced3ca61f80 100644 --- a/mysql-test/r/metadata.result +++ b/mysql-test/r/metadata.result @@ -21,7 +21,7 @@ def test t1 t1 g g 5 4 0 Y 32768 3 63 def test t1 t1 h h 0 7 0 Y 32768 4 63 def test t1 t1 i i 13 4 0 Y 32864 0 63 def test t1 t1 j j 10 10 0 Y 128 0 63 -def test t1 t1 k k 7 19 0 N 1217 0 63 +def test t1 t1 k k 7 19 0 N 1249 0 63 def test t1 t1 l l 12 19 0 Y 128 0 63 def test t1 t1 m m 254 1 0 Y 256 0 8 def test t1 t1 n n 254 3 0 Y 2048 0 8 diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index 9a123729c4b..26dcce43d08 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -428,6 +428,22 @@ select * from t1 where a='807780' and b='477' and c='165'; a b c 807780 477 165 drop table t1; +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 't1' +CREATE TABLE t1 (a varchar(150) NOT NULL, KEY (a)); +INSERT t1 VALUES ("can \tcan"); +INSERT t1 VALUES ("can can"); +INSERT t1 VALUES ("can"); +SELECT * FROM t1; +a +can can +can +can can +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; create table t1 (a blob); insert into t1 values('a '),('a'); select concat(a,'.') from t1 where a='a'; @@ -529,3 +545,12 @@ show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t1 1 a 1 a NULL NULL NULL NULL YES HASH drop table t1,t2; +create table t1 ( a tinytext, b char(1), index idx (a(1),b) ); +insert into t1 values (null,''), (null,''); +explain select count(*) from t1 where a is null; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx idx 4 const 1 Using where +select count(*) from t1 where a is null; +count(*) +2 +drop table t1; diff --git a/mysql-test/r/mysql_protocols.result b/mysql-test/r/mysql_protocols.result new file mode 100644 index 00000000000..272e3bda6f0 --- /dev/null +++ b/mysql-test/r/mysql_protocols.result @@ -0,0 +1,9 @@ +<default> + ok +TCP + ok +SOCKET + ok +ERROR 2047: Wrong or unknown protocol +ERROR 2047: Wrong or unknown protocol +Unknown option to protocol: NullS diff --git a/mysql-test/r/mysqlbinlog2.result b/mysql-test/r/mysqlbinlog2.result new file mode 100644 index 00000000000..3c1b85e05a1 --- /dev/null +++ b/mysql-test/r/mysqlbinlog2.result @@ -0,0 +1,446 @@ +drop table if exists t1; +reset master; +set @a=UNIX_TIMESTAMP("2020-01-21 15:32:22"); +set timestamp=@a; +create table t1 (a int auto_increment not null primary key, b char(3)); +insert into t1 values(null, "a"); +insert into t1 values(null, "b"); +set timestamp=@a+2; +insert into t1 values(null, "c"); +set timestamp=@a+4; +insert into t1 values(null, "d"); +insert into t1 values(null, "e"); +flush logs; +set timestamp=@a+1; +insert into t1 values(null, "f"); + +--- Local -- +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +use test; +SET TIMESTAMP=1579609942; +create table t1 (a int auto_increment not null primary key, b char(3)); +SET INSERT_ID=1; +SET TIMESTAMP=1579609942; +insert into t1 values(null, "a"); +SET INSERT_ID=2; +SET TIMESTAMP=1579609942; +insert into t1 values(null, "b"); +SET INSERT_ID=3; +SET TIMESTAMP=1579609944; +insert into t1 values(null, "c"); +SET INSERT_ID=4; +SET TIMESTAMP=1579609946; +insert into t1 values(null, "d"); +SET INSERT_ID=5; +SET TIMESTAMP=1579609946; +insert into t1 values(null, "e"); + +--- offset -- +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +SET INSERT_ID=1; +use test; +SET TIMESTAMP=1579609942; +insert into t1 values(null, "a"); +SET INSERT_ID=2; +SET TIMESTAMP=1579609942; +insert into t1 values(null, "b"); +SET INSERT_ID=3; +SET TIMESTAMP=1579609944; +insert into t1 values(null, "c"); +SET INSERT_ID=4; +SET TIMESTAMP=1579609946; +insert into t1 values(null, "d"); +SET INSERT_ID=5; +SET TIMESTAMP=1579609946; +insert into t1 values(null, "e"); + +--- start-position -- +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +use test; +SET TIMESTAMP=1579609946; +insert into t1 values(null, "d"); +SET INSERT_ID=5; +SET TIMESTAMP=1579609946; +insert into t1 values(null, "e"); + +--- stop-position -- +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +use test; +SET TIMESTAMP=1579609942; +create table t1 (a int auto_increment not null primary key, b char(3)); +SET INSERT_ID=1; +SET TIMESTAMP=1579609942; +insert into t1 values(null, "a"); +SET INSERT_ID=2; +SET TIMESTAMP=1579609942; +insert into t1 values(null, "b"); +SET INSERT_ID=3; +SET TIMESTAMP=1579609944; +insert into t1 values(null, "c"); +SET INSERT_ID=4; + +--- start-datetime -- +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +SET INSERT_ID=3; +use test; +SET TIMESTAMP=1579609944; +insert into t1 values(null, "c"); +SET INSERT_ID=4; +SET TIMESTAMP=1579609946; +insert into t1 values(null, "d"); +SET INSERT_ID=5; +SET TIMESTAMP=1579609946; +insert into t1 values(null, "e"); + +--- stop-datetime -- +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +use test; +SET TIMESTAMP=1579609942; +create table t1 (a int auto_increment not null primary key, b char(3)); +SET INSERT_ID=1; +SET TIMESTAMP=1579609942; +insert into t1 values(null, "a"); +SET INSERT_ID=2; +SET TIMESTAMP=1579609942; +insert into t1 values(null, "b"); + +--- Local with 2 binlogs on command line -- +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +use test; +SET TIMESTAMP=1579609942; +create table t1 (a int auto_increment not null primary key, b char(3)); +SET INSERT_ID=1; +SET TIMESTAMP=1579609942; +insert into t1 values(null, "a"); +SET INSERT_ID=2; +SET TIMESTAMP=1579609942; +insert into t1 values(null, "b"); +SET INSERT_ID=3; +SET TIMESTAMP=1579609944; +insert into t1 values(null, "c"); +SET INSERT_ID=4; +SET TIMESTAMP=1579609946; +insert into t1 values(null, "d"); +SET INSERT_ID=5; +SET TIMESTAMP=1579609946; +insert into t1 values(null, "e"); +SET INSERT_ID=6; +use test; +SET TIMESTAMP=1579609943; +insert into t1 values(null, "f"); + +--- offset -- +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +SET INSERT_ID=1; +use test; +SET TIMESTAMP=1579609942; +insert into t1 values(null, "a"); +SET INSERT_ID=2; +SET TIMESTAMP=1579609942; +insert into t1 values(null, "b"); +SET INSERT_ID=3; +SET TIMESTAMP=1579609944; +insert into t1 values(null, "c"); +SET INSERT_ID=4; +SET TIMESTAMP=1579609946; +insert into t1 values(null, "d"); +SET INSERT_ID=5; +SET TIMESTAMP=1579609946; +insert into t1 values(null, "e"); +SET INSERT_ID=6; +use test; +SET TIMESTAMP=1579609943; +insert into t1 values(null, "f"); + +--- start-position -- +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +use test; +SET TIMESTAMP=1579609946; +insert into t1 values(null, "d"); +SET INSERT_ID=5; +SET TIMESTAMP=1579609946; +insert into t1 values(null, "e"); +SET INSERT_ID=6; +use test; +SET TIMESTAMP=1579609943; +insert into t1 values(null, "f"); + +--- stop-position -- +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +use test; +SET TIMESTAMP=1579609942; +create table t1 (a int auto_increment not null primary key, b char(3)); +SET INSERT_ID=1; +SET TIMESTAMP=1579609942; +insert into t1 values(null, "a"); +SET INSERT_ID=2; +SET TIMESTAMP=1579609942; +insert into t1 values(null, "b"); +SET INSERT_ID=3; +SET TIMESTAMP=1579609944; +insert into t1 values(null, "c"); +SET INSERT_ID=4; +SET TIMESTAMP=1579609946; +insert into t1 values(null, "d"); +SET INSERT_ID=5; +SET TIMESTAMP=1579609946; +insert into t1 values(null, "e"); +SET INSERT_ID=6; + +--- start-datetime -- +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +SET INSERT_ID=3; +use test; +SET TIMESTAMP=1579609944; +insert into t1 values(null, "c"); +SET INSERT_ID=4; +SET TIMESTAMP=1579609946; +insert into t1 values(null, "d"); +SET INSERT_ID=5; +SET TIMESTAMP=1579609946; +insert into t1 values(null, "e"); +SET INSERT_ID=6; +use test; +SET TIMESTAMP=1579609943; +insert into t1 values(null, "f"); + +--- stop-datetime -- +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +use test; +SET TIMESTAMP=1579609942; +create table t1 (a int auto_increment not null primary key, b char(3)); +SET INSERT_ID=1; +SET TIMESTAMP=1579609942; +insert into t1 values(null, "a"); +SET INSERT_ID=2; +SET TIMESTAMP=1579609942; +insert into t1 values(null, "b"); + +--- Remote -- +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +use test; +SET TIMESTAMP=1579609942; +create table t1 (a int auto_increment not null primary key, b char(3)); +SET INSERT_ID=1; +SET TIMESTAMP=1579609942; +insert into t1 values(null, "a"); +SET INSERT_ID=2; +SET TIMESTAMP=1579609942; +insert into t1 values(null, "b"); +SET INSERT_ID=3; +SET TIMESTAMP=1579609944; +insert into t1 values(null, "c"); +SET INSERT_ID=4; +SET TIMESTAMP=1579609946; +insert into t1 values(null, "d"); +SET INSERT_ID=5; +SET TIMESTAMP=1579609946; +insert into t1 values(null, "e"); + +--- offset -- +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +SET INSERT_ID=1; +use test; +SET TIMESTAMP=1579609942; +insert into t1 values(null, "a"); +SET INSERT_ID=2; +SET TIMESTAMP=1579609942; +insert into t1 values(null, "b"); +SET INSERT_ID=3; +SET TIMESTAMP=1579609944; +insert into t1 values(null, "c"); +SET INSERT_ID=4; +SET TIMESTAMP=1579609946; +insert into t1 values(null, "d"); +SET INSERT_ID=5; +SET TIMESTAMP=1579609946; +insert into t1 values(null, "e"); + +--- start-position -- +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +use test; +SET TIMESTAMP=1579609946; +insert into t1 values(null, "d"); +SET INSERT_ID=5; +SET TIMESTAMP=1579609946; +insert into t1 values(null, "e"); + +--- stop-position -- +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +use test; +SET TIMESTAMP=1579609942; +create table t1 (a int auto_increment not null primary key, b char(3)); +SET INSERT_ID=1; +SET TIMESTAMP=1579609942; +insert into t1 values(null, "a"); +SET INSERT_ID=2; +SET TIMESTAMP=1579609942; +insert into t1 values(null, "b"); +SET INSERT_ID=3; +SET TIMESTAMP=1579609944; +insert into t1 values(null, "c"); +SET INSERT_ID=4; + +--- start-datetime -- +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +SET INSERT_ID=3; +use test; +SET TIMESTAMP=1579609944; +insert into t1 values(null, "c"); +SET INSERT_ID=4; +SET TIMESTAMP=1579609946; +insert into t1 values(null, "d"); +SET INSERT_ID=5; +SET TIMESTAMP=1579609946; +insert into t1 values(null, "e"); + +--- stop-datetime -- +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +use test; +SET TIMESTAMP=1579609942; +create table t1 (a int auto_increment not null primary key, b char(3)); +SET INSERT_ID=1; +SET TIMESTAMP=1579609942; +insert into t1 values(null, "a"); +SET INSERT_ID=2; +SET TIMESTAMP=1579609942; +insert into t1 values(null, "b"); + +--- Remote with 2 binlogs on command line -- +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +use test; +SET TIMESTAMP=1579609942; +create table t1 (a int auto_increment not null primary key, b char(3)); +SET INSERT_ID=1; +SET TIMESTAMP=1579609942; +insert into t1 values(null, "a"); +SET INSERT_ID=2; +SET TIMESTAMP=1579609942; +insert into t1 values(null, "b"); +SET INSERT_ID=3; +SET TIMESTAMP=1579609944; +insert into t1 values(null, "c"); +SET INSERT_ID=4; +SET TIMESTAMP=1579609946; +insert into t1 values(null, "d"); +SET INSERT_ID=5; +SET TIMESTAMP=1579609946; +insert into t1 values(null, "e"); +SET INSERT_ID=6; +use test; +SET TIMESTAMP=1579609943; +insert into t1 values(null, "f"); + +--- offset -- +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +SET INSERT_ID=1; +use test; +SET TIMESTAMP=1579609942; +insert into t1 values(null, "a"); +SET INSERT_ID=2; +SET TIMESTAMP=1579609942; +insert into t1 values(null, "b"); +SET INSERT_ID=3; +SET TIMESTAMP=1579609944; +insert into t1 values(null, "c"); +SET INSERT_ID=4; +SET TIMESTAMP=1579609946; +insert into t1 values(null, "d"); +SET INSERT_ID=5; +SET TIMESTAMP=1579609946; +insert into t1 values(null, "e"); +SET INSERT_ID=6; +use test; +SET TIMESTAMP=1579609943; +insert into t1 values(null, "f"); + +--- start-position -- +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +use test; +SET TIMESTAMP=1579609946; +insert into t1 values(null, "d"); +SET INSERT_ID=5; +SET TIMESTAMP=1579609946; +insert into t1 values(null, "e"); +SET INSERT_ID=6; +use test; +SET TIMESTAMP=1579609943; +insert into t1 values(null, "f"); + +--- stop-position -- +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +use test; +SET TIMESTAMP=1579609942; +create table t1 (a int auto_increment not null primary key, b char(3)); +SET INSERT_ID=1; +SET TIMESTAMP=1579609942; +insert into t1 values(null, "a"); +SET INSERT_ID=2; +SET TIMESTAMP=1579609942; +insert into t1 values(null, "b"); +SET INSERT_ID=3; +SET TIMESTAMP=1579609944; +insert into t1 values(null, "c"); +SET INSERT_ID=4; +SET TIMESTAMP=1579609946; +insert into t1 values(null, "d"); +SET INSERT_ID=5; +SET TIMESTAMP=1579609946; +insert into t1 values(null, "e"); +SET INSERT_ID=6; + +--- start-datetime -- +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +SET INSERT_ID=3; +use test; +SET TIMESTAMP=1579609944; +insert into t1 values(null, "c"); +SET INSERT_ID=4; +SET TIMESTAMP=1579609946; +insert into t1 values(null, "d"); +SET INSERT_ID=5; +SET TIMESTAMP=1579609946; +insert into t1 values(null, "e"); +SET INSERT_ID=6; +use test; +SET TIMESTAMP=1579609943; +insert into t1 values(null, "f"); + +--- stop-datetime -- +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +use test; +SET TIMESTAMP=1579609942; +create table t1 (a int auto_increment not null primary key, b char(3)); +SET INSERT_ID=1; +SET TIMESTAMP=1579609942; +insert into t1 values(null, "a"); +SET INSERT_ID=2; +SET TIMESTAMP=1579609942; +insert into t1 values(null, "b"); + +--- to-last-log -- +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +use test; +SET TIMESTAMP=1579609942; +create table t1 (a int auto_increment not null primary key, b char(3)); +SET INSERT_ID=1; +SET TIMESTAMP=1579609942; +insert into t1 values(null, "a"); +SET INSERT_ID=2; +SET TIMESTAMP=1579609942; +insert into t1 values(null, "b"); +SET INSERT_ID=3; +SET TIMESTAMP=1579609944; +insert into t1 values(null, "c"); +SET INSERT_ID=4; +SET TIMESTAMP=1579609946; +insert into t1 values(null, "d"); +SET INSERT_ID=5; +SET TIMESTAMP=1579609946; +insert into t1 values(null, "e"); +SET INSERT_ID=6; +SET TIMESTAMP=1579609943; +insert into t1 values(null, "f"); + +--- end of test -- +drop table t1; diff --git a/mysql-test/r/ndb_alter_table.result b/mysql-test/r/ndb_alter_table.result index 6cc6a89d5ad..4e7f4b2a72b 100644 --- a/mysql-test/r/ndb_alter_table.result +++ b/mysql-test/r/ndb_alter_table.result @@ -15,15 +15,60 @@ col2 varchar(30) not null, col3 varchar (20) not null, col4 varchar(4) not null, col5 enum('PENDING', 'ACTIVE', 'DISABLED') not null, -col6 int not null, to_be_deleted int); -insert into t1 values (2,4,3,5,"PENDING",1,7); +col6 int not null, to_be_deleted int) ENGINE=ndbcluster; +show table status; +Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment +t1 ndbcluster 9 Dynamic 100 0 0 NULL 0 0 0 NULL NULL NULL latin1_swedish_ci NULL +insert into t1 values +(0,4,3,5,"PENDING",1,7),(NULL,4,3,5,"PENDING",1,7),(31,4,3,5,"PENDING",1,7), (7,4,3,5,"PENDING",1,7), (NULL,4,3,5,"PENDING",1,7), (100,4,3,5,"PENDING",1,7), (99,4,3,5,"PENDING",1,7), (8,4,3,5,"PENDING",1,7), (NULL,4,3,5,"PENDING",1,7); +show table status; +Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment +t1 ndbcluster 9 Dynamic 100 0 0 NULL 0 0 102 NULL NULL NULL latin1_swedish_ci NULL +select * from t1 order by col1; +col1 col2 col3 col4 col5 col6 to_be_deleted +0 4 3 5 PENDING 1 7 +1 4 3 5 PENDING 1 7 +7 4 3 5 PENDING 1 7 +8 4 3 5 PENDING 1 7 +31 4 3 5 PENDING 1 7 +32 4 3 5 PENDING 1 7 +99 4 3 5 PENDING 1 7 +100 4 3 5 PENDING 1 7 +101 4 3 5 PENDING 1 7 alter table t1 add column col4_5 varchar(20) not null after col4, add column col7 varchar(30) not null after col5, add column col8 datetime not null, drop column to_be_deleted, change column col2 fourth varchar(30) not null after col3, modify column col6 int not null first; -select * from t1; +show table status; +Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment +t1 ndbcluster 9 Dynamic 100 0 0 NULL 0 0 102 NULL NULL NULL latin1_swedish_ci NULL +select * from t1 order by col1; col6 col1 col3 fourth col4 col4_5 col5 col7 col8 -1 2 3 4 5 PENDING 0000-00-00 00:00:00 +1 0 3 4 5 PENDING 0000-00-00 00:00:00 +1 1 3 4 5 PENDING 0000-00-00 00:00:00 +1 7 3 4 5 PENDING 0000-00-00 00:00:00 +1 8 3 4 5 PENDING 0000-00-00 00:00:00 +1 31 3 4 5 PENDING 0000-00-00 00:00:00 +1 32 3 4 5 PENDING 0000-00-00 00:00:00 +1 99 3 4 5 PENDING 0000-00-00 00:00:00 +1 100 3 4 5 PENDING 0000-00-00 00:00:00 +1 101 3 4 5 PENDING 0000-00-00 00:00:00 +insert into t1 values (2, NULL,4,3,5,99,"PENDING","EXTRA",'2004-01-01 00:00:00'); +show table status; +Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment +t1 ndbcluster 9 Dynamic 100 0 0 NULL 0 0 103 NULL NULL NULL latin1_swedish_ci NULL +select * from t1 order by col1; +col6 col1 col3 fourth col4 col4_5 col5 col7 col8 +1 0 3 4 5 PENDING 0000-00-00 00:00:00 +1 1 3 4 5 PENDING 0000-00-00 00:00:00 +1 7 3 4 5 PENDING 0000-00-00 00:00:00 +1 8 3 4 5 PENDING 0000-00-00 00:00:00 +1 31 3 4 5 PENDING 0000-00-00 00:00:00 +1 32 3 4 5 PENDING 0000-00-00 00:00:00 +1 99 3 4 5 PENDING 0000-00-00 00:00:00 +1 100 3 4 5 PENDING 0000-00-00 00:00:00 +1 101 3 4 5 PENDING 0000-00-00 00:00:00 +2 102 4 3 5 99 PENDING EXTRA 2004-01-01 00:00:00 drop table t1; diff --git a/mysql-test/r/ndb_autodiscover.result b/mysql-test/r/ndb_autodiscover.result index b86575ff2d9..313003f0a1f 100644 --- a/mysql-test/r/ndb_autodiscover.result +++ b/mysql-test/r/ndb_autodiscover.result @@ -35,10 +35,10 @@ update t1 set name="Autodiscover" where id = 2; show status like 'handler_discover%'; Variable_name Value Handler_discover 4 -select * from t1 order by name; +select * from t1 order by id; id name -2 Autodiscover 1 Autodiscover +2 Autodiscover 3 Discover 3 show status like 'handler_discover%'; Variable_name Value diff --git a/mysql-test/r/ndb_basic.result b/mysql-test/r/ndb_basic.result index 0e7b039a5f9..80e1aa7939a 100644 --- a/mysql-test/r/ndb_basic.result +++ b/mysql-test/r/ndb_basic.result @@ -1,98 +1,106 @@ DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7; +drop database if exists mysqltest; CREATE TABLE t1 ( pk1 INT NOT NULL PRIMARY KEY, -attr1 INT NOT NULL +attr1 INT NOT NULL, +attr2 INT, +attr3 VARCHAR(10) ) ENGINE=ndbcluster; -INSERT INTO t1 VALUES (9410,9412); -SELECT pk1 FROM t1; +INSERT INTO t1 VALUES (9410,9412, NULL, '9412'), (9411,9413, 17, '9413'); +SELECT pk1 FROM t1 ORDER BY pk1; pk1 9410 -SELECT * FROM t1; -pk1 attr1 -9410 9412 -SELECT t1.* FROM t1; -pk1 attr1 -9410 9412 +9411 +SELECT * FROM t1 ORDER BY pk1; +pk1 attr1 attr2 attr3 +9410 9412 NULL 9412 +9411 9413 17 9413 +SELECT t1.* FROM t1 ORDER BY pk1; +pk1 attr1 attr2 attr3 +9410 9412 NULL 9412 +9411 9413 17 9413 UPDATE t1 SET attr1=1 WHERE pk1=9410; -SELECT * FROM t1; -pk1 attr1 -9410 1 +SELECT * FROM t1 ORDER BY pk1; +pk1 attr1 attr2 attr3 +9410 1 NULL 9412 +9411 9413 17 9413 UPDATE t1 SET pk1=2 WHERE attr1=1; -ERROR 42000: Table 't1' uses an extension that doesn't exist in this MySQL version -SELECT * FROM t1; -pk1 attr1 -9410 1 +SELECT * FROM t1 ORDER BY pk1; +pk1 attr1 attr2 attr3 +2 1 NULL 9412 +9411 9413 17 9413 +UPDATE t1 SET pk1=pk1 + 1; +SELECT * FROM t1 ORDER BY pk1; +pk1 attr1 attr2 attr3 +3 1 NULL 9412 +9412 9413 17 9413 DELETE FROM t1; SELECT * FROM t1; -pk1 attr1 -INSERT INTO t1 VALUES (9410,9412), (9411, 9413), (9408, 8765), -(7,8), (8,9), (9,10), (10,11), (11,12), (12,13), (13,14); +pk1 attr1 attr2 attr3 +INSERT INTO t1 VALUES (9410,9412, NULL, '9412'), (9408, 8765, NULL, '8765'), +(7,8, NULL, NULL), (8,9, NULL, NULL), (9,10, NULL, NULL), (10,11, NULL, NULL), (11,12, NULL, NULL), (12,13, NULL, NULL), (13,14, NULL, NULL); UPDATE t1 SET attr1 = 9999; SELECT * FROM t1 ORDER BY pk1; -pk1 attr1 -7 9999 -8 9999 -9 9999 -10 9999 -11 9999 -12 9999 -13 9999 -9408 9999 -9410 9999 -9411 9999 +pk1 attr1 attr2 attr3 +7 9999 NULL NULL +8 9999 NULL NULL +9 9999 NULL NULL +10 9999 NULL NULL +11 9999 NULL NULL +12 9999 NULL NULL +13 9999 NULL NULL +9408 9999 NULL 8765 +9410 9999 NULL 9412 UPDATE t1 SET attr1 = 9998 WHERE pk1 < 1000; SELECT * FROM t1 ORDER BY pk1; -pk1 attr1 -7 9998 -8 9998 -9 9998 -10 9998 -11 9998 -12 9998 -13 9998 -9408 9999 -9410 9999 -9411 9999 +pk1 attr1 attr2 attr3 +7 9998 NULL NULL +8 9998 NULL NULL +9 9998 NULL NULL +10 9998 NULL NULL +11 9998 NULL NULL +12 9998 NULL NULL +13 9998 NULL NULL +9408 9999 NULL 8765 +9410 9999 NULL 9412 UPDATE t1 SET attr1 = 9997 WHERE attr1 = 9999; SELECT * FROM t1 ORDER BY pk1; -pk1 attr1 -7 9998 -8 9998 -9 9998 -10 9998 -11 9998 -12 9998 -13 9998 -9408 9997 -9410 9997 -9411 9997 +pk1 attr1 attr2 attr3 +7 9998 NULL NULL +8 9998 NULL NULL +9 9998 NULL NULL +10 9998 NULL NULL +11 9998 NULL NULL +12 9998 NULL NULL +13 9998 NULL NULL +9408 9997 NULL 8765 +9410 9997 NULL 9412 DELETE FROM t1 WHERE pk1 = 9410; SELECT * FROM t1 ORDER BY pk1; -pk1 attr1 -7 9998 -8 9998 -9 9998 -10 9998 -11 9998 -12 9998 -13 9998 -9408 9997 -9411 9997 +pk1 attr1 attr2 attr3 +7 9998 NULL NULL +8 9998 NULL NULL +9 9998 NULL NULL +10 9998 NULL NULL +11 9998 NULL NULL +12 9998 NULL NULL +13 9998 NULL NULL +9408 9997 NULL 8765 DELETE FROM t1; SELECT * FROM t1; -pk1 attr1 -INSERT INTO t1 values (1, 4), (2, 4), (3, 5), (4, 4), (5, 5); +pk1 attr1 attr2 attr3 +INSERT INTO t1 values (1, 4, NULL, NULL), (2, 4, NULL, NULL), (3, 5, NULL, NULL), (4, 4, NULL, NULL), (5, 5, NULL, NULL); DELETE FROM t1 WHERE attr1=4; SELECT * FROM t1 order by pk1; -pk1 attr1 -3 5 -5 5 +pk1 attr1 attr2 attr3 +3 5 NULL NULL +5 5 NULL NULL DELETE FROM t1; -INSERT INTO t1 VALUES (9410,9412), (9411, 9413); +INSERT INTO t1 VALUES (9410,9412, NULL, NULL), (9411, 9413, NULL, NULL); DELETE FROM t1 WHERE pk1 = 9410; SELECT * FROM t1; -pk1 attr1 -9411 9413 +pk1 attr1 attr2 attr3 +9411 9413 NULL NULL DROP TABLE t1; CREATE TABLE t1 (id INT, id2 int) engine=ndbcluster; INSERT INTO t1 values(3456, 7890); @@ -108,13 +116,17 @@ SELECT * FROM t1; id id2 1234 7890 DELETE FROM t1; -INSERT INTO t1 values(3456, 7890), (3456, 7890), (3456, 7890); -SELECT * FROM t1; +INSERT INTO t1 values(3456, 7890), (3456, 7890), (3456, 7890), (3454, 7890); +SELECT * FROM t1 ORDER BY id; id id2 +3454 7890 3456 7890 3456 7890 3456 7890 DELETE FROM t1 WHERE id = 3456; +SELECT * FROM t1 ORDER BY id; +id id2 +3454 7890 DROP TABLE t1; CREATE TABLE t1 ( pk1 INT NOT NULL PRIMARY KEY, @@ -222,6 +234,18 @@ select * from t4 where a = 7 and b = 17 order by a; a b c d select * from t4 where a = 7 and b != 16 order by b; a b c d +delete from t2 where a > 5; +select x1.a, x1.b from t2 x1, t2 x2 where x1.b = x2.b order by x1.a; +a b +1 10 +3 12 +5 14 +select a, b FROM t2 outer_table where +a = (select a from t2 where b = outer_table.b ) order by a; +a b +1 10 +3 12 +5 14 delete from t2; delete from t3; delete from t4; @@ -338,3 +362,30 @@ select * from t7; adress a b c No adress 8 NULL 12 drop table t7; +CREATE TABLE t1 ( +pk1 INT NOT NULL PRIMARY KEY, +attr1 INT NOT NULL, +attr2 INT, +attr3 VARCHAR(10) +) ENGINE=ndbcluster; +INSERT INTO t1 VALUES (9410,9412, NULL, '9412'), (9411,9413, 17, '9413'); +create database mysqltest; +use mysqltest; +CREATE TABLE t2 ( +a bigint unsigned NOT NULL PRIMARY KEY, +b int unsigned not null, +c int unsigned +) engine=ndbcluster; +insert into t2 select pk1,attr1,attr2 from test.t1; +select * from t2 order by a; +a b c +9410 9412 NULL +9411 9413 17 +select b from test.t1, t2 where c = test.t1.attr2; +b +9413 +select b,test.t1.attr1 from test.t1, t2 where test.t1.pk1 < a; +b attr1 +9413 9412 +drop table test.t1, t2; +drop database mysqltest; diff --git a/mysql-test/r/ndb_blob.result b/mysql-test/r/ndb_blob.result new file mode 100644 index 00000000000..c590815b233 --- /dev/null +++ b/mysql-test/r/ndb_blob.result @@ -0,0 +1,324 @@ +drop table if exists t1; +drop database if exists mysqltest; +set autocommit=0; +create table t1 ( +a int not null primary key, +b text not null, +c int not null, +d longblob, +key (c) +) engine=ndbcluster; +set @x0 = '01234567012345670123456701234567'; +set @x0 = concat(@x0,@x0,@x0,@x0,@x0,@x0,@x0,@x0); +set @b1 = 'b1'; +set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1); +set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1); +set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1); +set @b1 = concat(@b1,@x0); +set @d1 = 'dd1'; +set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1); +set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1); +set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1); +set @b2 = 'b2'; +set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2); +set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2); +set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2); +set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2); +set @d2 = 'dd2'; +set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2); +set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2); +set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2); +set @d2 = concat(@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2,@d2); +select length(@x0),length(@b1),length(@d1) from dual; +length(@x0) length(@b1) length(@d1) +256 2256 3000 +select length(@x0),length(@b2),length(@d2) from dual; +length(@x0) length(@b2) length(@d2) +256 20000 30000 +insert into t1 values(1,@b1,111,@d1); +insert into t1 values(2,@b2,222,@d2); +commit; +explain select * from t1 where a = 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 +select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3) +from t1 where a=1; +a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3) +1 2256 b1 3000 dd1 +select a,length(b),substr(b,1+2*9000,2),length(d),substr(d,1+3*9000,3) +from t1 where a=2; +a length(b) substr(b,1+2*9000,2) length(d) substr(d,1+3*9000,3) +2 20000 b2 30000 dd2 +update t1 set b=@b2,d=@d2 where a=1; +update t1 set b=@b1,d=@d1 where a=2; +commit; +select a,length(b),substr(b,1+2*9000,2),length(d),substr(d,1+3*9000,3) +from t1 where a=1; +a length(b) substr(b,1+2*9000,2) length(d) substr(d,1+3*9000,3) +1 20000 b2 30000 dd2 +select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3) +from t1 where a=2; +a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3) +2 2256 b1 3000 dd1 +update t1 set b=concat(b,b),d=concat(d,d) where a=1; +update t1 set b=concat(b,b),d=concat(d,d) where a=2; +commit; +select a,length(b),substr(b,1+4*9000,2),length(d),substr(d,1+6*9000,3) +from t1 where a=1; +a length(b) substr(b,1+4*9000,2) length(d) substr(d,1+6*9000,3) +1 40000 b2 60000 dd2 +select a,length(b),substr(b,1+4*900,2),length(d),substr(d,1+6*900,3) +from t1 where a=2; +a length(b) substr(b,1+4*900,2) length(d) substr(d,1+6*900,3) +2 4512 b1 6000 dd1 +update t1 set d=null where a=1; +commit; +select a from t1 where d is null; +a +1 +delete from t1 where a=1; +delete from t1 where a=2; +commit; +select count(*) from t1; +count(*) +0 +insert into t1 values(1,@b1,111,@d1); +insert into t1 values(2,@b2,222,@d2); +commit; +explain select * from t1 where c = 111; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref c c 4 const 10 Using where +select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3) +from t1 where c=111; +a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3) +1 2256 b1 3000 dd1 +select a,length(b),substr(b,1+2*9000,2),length(d),substr(d,1+3*9000,3) +from t1 where c=222; +a length(b) substr(b,1+2*9000,2) length(d) substr(d,1+3*9000,3) +2 20000 b2 30000 dd2 +update t1 set b=@b2,d=@d2 where c=111; +update t1 set b=@b1,d=@d1 where c=222; +commit; +select a,length(b),substr(b,1+2*9000,2),length(d),substr(d,1+3*9000,3) +from t1 where c=111; +a length(b) substr(b,1+2*9000,2) length(d) substr(d,1+3*9000,3) +1 20000 b2 30000 dd2 +select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3) +from t1 where c=222; +a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3) +2 2256 b1 3000 dd1 +update t1 set d=null where c=111; +commit; +select a from t1 where d is null; +a +1 +delete from t1 where c=111; +delete from t1 where c=222; +commit; +select count(*) from t1; +count(*) +0 +insert into t1 values(1,'b1',111,'dd1'); +insert into t1 values(2,'b2',222,'dd2'); +insert into t1 values(3,'b3',333,'dd3'); +insert into t1 values(4,'b4',444,'dd4'); +insert into t1 values(5,'b5',555,'dd5'); +insert into t1 values(6,'b6',666,'dd6'); +insert into t1 values(7,'b7',777,'dd7'); +insert into t1 values(8,'b8',888,'dd8'); +insert into t1 values(9,'b9',999,'dd9'); +commit; +explain select * from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 100 +select * from t1 order by a; +a b c d +1 b1 111 dd1 +2 b2 222 dd2 +3 b3 333 dd3 +4 b4 444 dd4 +5 b5 555 dd5 +6 b6 666 dd6 +7 b7 777 dd7 +8 b8 888 dd8 +9 b9 999 dd9 +update t1 set b=concat(a,'x',b),d=concat(a,'x',d); +commit; +select * from t1 order by a; +a b c d +1 1xb1 111 1xdd1 +2 2xb2 222 2xdd2 +3 3xb3 333 3xdd3 +4 4xb4 444 4xdd4 +5 5xb5 555 5xdd5 +6 6xb6 666 6xdd6 +7 7xb7 777 7xdd7 +8 8xb8 888 8xdd8 +9 9xb9 999 9xdd9 +delete from t1; +commit; +select count(*) from t1; +count(*) +0 +insert into t1 values(1,@b1,111,@d1); +insert into t1 values(2,@b2,222,@d2); +commit; +explain select * from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 100 +select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3) +from t1 order by a; +a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3) +1 2256 b1 3000 dd1 +2 20000 b2 30000 dd2 +update t1 set b=concat(b,b),d=concat(d,d); +commit; +select a,length(b),substr(b,1+4*9000,2),length(d),substr(d,1+6*9000,3) +from t1 order by a; +a length(b) substr(b,1+4*9000,2) length(d) substr(d,1+6*9000,3) +1 4512 6000 +2 40000 b2 60000 dd2 +delete from t1; +commit; +select count(*) from t1; +count(*) +0 +insert into t1 values(1,'b1',111,'dd1'); +insert into t1 values(2,'b2',222,'dd2'); +insert into t1 values(3,'b3',333,'dd3'); +insert into t1 values(4,'b4',444,'dd4'); +insert into t1 values(5,'b5',555,'dd5'); +insert into t1 values(6,'b6',666,'dd6'); +insert into t1 values(7,'b7',777,'dd7'); +insert into t1 values(8,'b8',888,'dd8'); +insert into t1 values(9,'b9',999,'dd9'); +commit; +explain select * from t1 where c >= 100 order by a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c c 4 NULL 10 Using where; Using filesort +select * from t1 where c >= 100 order by a; +a b c d +1 b1 111 dd1 +2 b2 222 dd2 +3 b3 333 dd3 +4 b4 444 dd4 +5 b5 555 dd5 +6 b6 666 dd6 +7 b7 777 dd7 +8 b8 888 dd8 +9 b9 999 dd9 +update t1 set b=concat(a,'x',b),d=concat(a,'x',d) +where c >= 100; +commit; +select * from t1 where c >= 100 order by a; +a b c d +1 1xb1 111 1xdd1 +2 2xb2 222 2xdd2 +3 3xb3 333 3xdd3 +4 4xb4 444 4xdd4 +5 5xb5 555 5xdd5 +6 6xb6 666 6xdd6 +7 7xb7 777 7xdd7 +8 8xb8 888 8xdd8 +9 9xb9 999 9xdd9 +select * from t1 order by a; +a b c d +1 1xb1 111 1xdd1 +2 2xb2 222 2xdd2 +3 3xb3 333 3xdd3 +4 4xb4 444 4xdd4 +5 5xb5 555 5xdd5 +6 6xb6 666 6xdd6 +7 7xb7 777 7xdd7 +8 8xb8 888 8xdd8 +9 9xb9 999 9xdd9 +alter table t1 add x int; +select * from t1 order by a; +a b c d x +1 1xb1 111 1xdd1 NULL +2 2xb2 222 2xdd2 NULL +3 3xb3 333 3xdd3 NULL +4 4xb4 444 4xdd4 NULL +5 5xb5 555 5xdd5 NULL +6 6xb6 666 6xdd6 NULL +7 7xb7 777 7xdd7 NULL +8 8xb8 888 8xdd8 NULL +9 9xb9 999 9xdd9 NULL +alter table t1 drop x; +select * from t1 order by a; +a b c d +1 1xb1 111 1xdd1 +2 2xb2 222 2xdd2 +3 3xb3 333 3xdd3 +4 4xb4 444 4xdd4 +5 5xb5 555 5xdd5 +6 6xb6 666 6xdd6 +7 7xb7 777 7xdd7 +8 8xb8 888 8xdd8 +9 9xb9 999 9xdd9 +create database mysqltest; +use mysqltest; +CREATE TABLE t2 ( +a bigint unsigned NOT NULL PRIMARY KEY, +b int unsigned not null, +c int unsigned +) engine=ndbcluster; +insert into t2 values (1,1,1),(2,2,2); +select * from test.t1,t2 where test.t1.a = t2.a order by test.t1.a; +a b c d a b c +1 1xb1 111 1xdd1 1 1 1 +2 2xb2 222 2xdd2 2 2 2 +drop table t2; +use test; +delete from t1 where c >= 100; +commit; +select count(*) from t1; +count(*) +0 +insert into t1 values(1,@b1,111,@d1); +insert into t1 values(2,@b2,222,@d2); +commit; +explain select * from t1 where c >= 100 order by a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c c 4 NULL 10 Using where; Using filesort +select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3) +from t1 where c >= 100 order by a; +a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3) +1 2256 b1 3000 dd1 +2 20000 b2 30000 dd2 +update t1 set b=concat(b,b),d=concat(d,d); +commit; +select a,length(b),substr(b,1+4*9000,2),length(d),substr(d,1+6*9000,3) +from t1 where c >= 100 order by a; +a length(b) substr(b,1+4*9000,2) length(d) substr(d,1+6*9000,3) +1 4512 6000 +2 40000 b2 60000 dd2 +delete from t1 where c >= 100; +commit; +select count(*) from t1; +count(*) +0 +insert into t1 values(1,@b1,111,@d1); +insert into t1 values(2,@b2,222,@d2); +select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3) +from t1 where a = 0; +a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3) +select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3) +from t1 where a = 1; +a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3) +1 2256 b1 3000 dd1 +select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3) +from t1 where a = 2; +a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3) +2 20000 b2 30000 dd2 +select a,length(b),substr(b,1+2*900,2),length(d),substr(d,1+3*900,3) +from t1 order by a; +a length(b) substr(b,1+2*900,2) length(d) substr(d,1+3*900,3) +1 2256 b1 3000 dd1 +2 20000 b2 30000 dd2 +rollback; +select count(*) from t1; +count(*) +0 +drop table t1; +drop database mysqltest; diff --git a/mysql-test/r/ndb_index_ordered.result b/mysql-test/r/ndb_index_ordered.result index 0e3bd555b0a..2f1ad251e40 100644 --- a/mysql-test/r/ndb_index_ordered.result +++ b/mysql-test/r/ndb_index_ordered.result @@ -82,6 +82,24 @@ a b c 4 5 12 5 6 12 6 7 12 +update t1 set b = b + 1 where b > 4 and b < 7; +select * from t1 order by a; +a b c +1 2 13 +2 3 13 +3 4 12 +4 6 12 +5 7 12 +6 7 12 +update t1 set a = a + 10 where b > 1 and b < 7; +select * from t1 order by a; +a b c +5 7 12 +6 7 12 +11 2 13 +12 3 13 +13 4 12 +14 6 12 drop table t1; CREATE TABLE t1 ( a int unsigned NOT NULL PRIMARY KEY, @@ -187,4 +205,55 @@ a b c select * from t1 where b<=5 and c=0 or b<=5 and c=2; a b c 19 4 0 +select count(*) from t1 where b = 0; +count(*) +0 +select count(*) from t1 where b = 1; +count(*) +1 +drop table t1; +CREATE TABLE t1 ( +a int unsigned NOT NULL PRIMARY KEY, +b int unsigned, +c int unsigned, +KEY bc(b,c) +) engine = ndb; +insert into t1 values(1,1,1),(2,NULL,2),(3,NULL,NULL),(4,4,NULL); +select * from t1 use index (bc) where b IS NULL; +a b c +3 NULL NULL +2 NULL 2 +select * from t1 use index (bc)order by a; +a b c +1 1 1 +2 NULL 2 +3 NULL NULL +4 4 NULL +select * from t1 use index (bc) order by a; +a b c +1 1 1 +2 NULL 2 +3 NULL NULL +4 4 NULL +select * from t1 use index (PRIMARY) where b IS NULL order by a; +a b c +2 NULL 2 +3 NULL NULL +select * from t1 use index (bc) where b IS NULL order by a; +a b c +2 NULL 2 +3 NULL NULL +select * from t1 use index (bc) where b IS NULL and c IS NULL order by a; +a b c +3 NULL NULL +select * from t1 use index (bc) where b IS NULL and c = 2 order by a; +a b c +2 NULL 2 +select * from t1 use index (bc) where b < 4 order by a; +a b c +1 1 1 +select * from t1 use index (bc) where b IS NOT NULL order by a; +a b c +1 1 1 +4 4 NULL drop table t1; diff --git a/mysql-test/r/ndb_index_unique.result b/mysql-test/r/ndb_index_unique.result index ed97e0b110a..4362de94b48 100644 --- a/mysql-test/r/ndb_index_unique.result +++ b/mysql-test/r/ndb_index_unique.result @@ -21,6 +21,28 @@ insert into t1 values(7,8,3); select * from t1 where b = 4 order by a; a b c 3 4 6 +insert into t1 values(8, 2, 3); +ERROR 23000: Can't write, because of unique constraint, to table 't1' +select * from t1 order by a; +a b c +1 2 3 +2 3 5 +3 4 6 +4 5 8 +5 6 2 +6 7 2 +7 8 3 +delete from t1 where a = 1; +insert into t1 values(8, 2, 3); +select * from t1 order by a; +a b c +2 3 5 +3 4 6 +4 5 8 +5 6 2 +6 7 2 +7 8 3 +8 2 3 drop table t1; CREATE TABLE t2 ( a int unsigned NOT NULL PRIMARY KEY, @@ -42,6 +64,28 @@ insert into t2 values(7,8,3); select * from t2 where b = 4 order by a; a b c 3 4 6 +insert into t2 values(8, 2, 3); +ERROR 23000: Can't write, because of unique constraint, to table 't2' +select * from t2 order by a; +a b c +1 2 3 +2 3 5 +3 4 6 +4 5 8 +5 6 2 +6 7 2 +7 8 3 +delete from t2 where a = 1; +insert into t2 values(8, 2, 3); +select * from t2 order by a; +a b c +2 3 5 +3 4 6 +4 5 8 +5 6 2 +6 7 2 +7 8 3 +8 2 3 drop table t2; CREATE TABLE t3 ( a int unsigned NOT NULL, @@ -65,6 +109,68 @@ a b c 3 4 6 drop table t3; CREATE TABLE t1 ( +pk int NOT NULL PRIMARY KEY, +a int unsigned, +UNIQUE KEY (a) +) engine=ndbcluster; +insert into t1 values (-1,NULL), (0,0), (1,NULL),(2,2),(3,NULL),(4,4); +select * from t1 order by pk; +pk a +-1 NULL +0 0 +1 NULL +2 2 +3 NULL +4 4 +insert into t1 values (5,0); +ERROR 23000: Can't write, because of unique constraint, to table 't1' +select * from t1 order by pk; +pk a +-1 NULL +0 0 +1 NULL +2 2 +3 NULL +4 4 +delete from t1 where a = 0; +insert into t1 values (5,0); +select * from t1 order by pk; +pk a +-1 NULL +1 NULL +2 2 +3 NULL +4 4 +5 0 +CREATE TABLE t2 ( +pk int NOT NULL PRIMARY KEY, +a int unsigned, +b tinyint NOT NULL, +c VARCHAR(10), +UNIQUE KEY si(a, c) +) engine=ndbcluster; +insert into t2 values (-1,1,17,NULL),(0,NULL,18,NULL),(1,3,19,'abc'); +select * from t2 order by pk; +pk a b c +-1 1 17 NULL +0 NULL 18 NULL +1 3 19 abc +insert into t2 values(2,3,19,'abc'); +ERROR 23000: Can't write, because of unique constraint, to table 't2' +select * from t2 order by pk; +pk a b c +-1 1 17 NULL +0 NULL 18 NULL +1 3 19 abc +delete from t2 where c IS NOT NULL; +insert into t2 values(2,3,19,'abc'); +select * from t2 order by pk; +pk a b c +-1 1 17 NULL +0 NULL 18 NULL +2 3 19 abc +drop table t1, t2; +CREATE TABLE t1 ( cid smallint(5) unsigned NOT NULL default '0', cv varchar(250) NOT NULL default '', PRIMARY KEY (cid), @@ -74,8 +180,10 @@ INSERT INTO t1 VALUES (8,'dummy'); CREATE TABLE t2 ( cid bigint(20) unsigned NOT NULL auto_increment, cap varchar(255) NOT NULL default '', -PRIMARY KEY (cid) +PRIMARY KEY (cid), +UNIQUE KEY (cid, cap) ) engine=ndbcluster; +INSERT INTO t2 VALUES (NULL,'another dummy'); CREATE TABLE t3 ( gid bigint(20) unsigned NOT NULL auto_increment, gn varchar(255) NOT NULL default '', @@ -132,6 +240,9 @@ cid cv 8 dummy select * from t1 where cv = 'test'; cid cv +select * from t2 where cap = 'another dummy'; +cid cap +0 another dummy select * from t4 where uid = 1 and gid=1 and rid=2 and cid=4; uid gid rid cid 1 1 2 4 diff --git a/mysql-test/r/ndb_limit.result b/mysql-test/r/ndb_limit.result new file mode 100644 index 00000000000..6574aa0bb1a --- /dev/null +++ b/mysql-test/r/ndb_limit.result @@ -0,0 +1,31 @@ +DROP TABLE IF EXISTS t2; +CREATE TABLE t2 ( +a bigint unsigned NOT NULL PRIMARY KEY, +b int unsigned not null, +c int unsigned +) engine=ndbcluster; +select count(*) from t2; +count(*) +10000 +delete from t2 limit 1; +select count(*) from t2; +count(*) +9999 +delete from t2 limit 100; +select count(*) from t2; +count(*) +9899 +delete from t2 limit 1000; +select count(*) from t2; +count(*) +8899 +update t2 set c=12345678 limit 100; +select count(*) from t2 where c=12345678; +count(*) +100 +select count(*) from t2 where c=12345678 limit 1000; +count(*) +100 +select * from t2 limit 0; +a b c +drop table t2; diff --git a/mysql-test/r/ndb_lock.result b/mysql-test/r/ndb_lock.result new file mode 100644 index 00000000000..56661913e22 --- /dev/null +++ b/mysql-test/r/ndb_lock.result @@ -0,0 +1,30 @@ +DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7; +create table t1 (x integer not null primary key, y varchar(32)) engine = ndb; +insert into t1 values (1,'one'), (2,'two'); +select * from t1 order by x; +x y +1 one +2 two +select * from t1 order by x; +x y +1 one +2 two +start transaction; +insert into t1 values (3,'three'); +select * from t1 order by x; +x y +1 one +2 two +3 three +start transaction; +select * from t1 order by x; +x y +1 one +2 two +commit; +select * from t1 order by x; +x y +1 one +2 two +3 three +commit; diff --git a/mysql-test/r/ndb_replace.result b/mysql-test/r/ndb_replace.result index 45af0f7fcb5..63fd8b55c8e 100644 --- a/mysql-test/r/ndb_replace.result +++ b/mysql-test/r/ndb_replace.result @@ -11,7 +11,7 @@ insert into t1 (gesuchnr, benutzer_id) value (3,2); replace into t1 (gesuchnr,benutzer_id) values (1,1); replace into t1 (gesuchnr,benutzer_id) values (1,1); insert into t1 (gesuchnr,benutzer_id) values (1,1); -ERROR 23000: Can't write; duplicate key in table 't1' +ERROR 23000: Duplicate entry '1-1' for key 1 replace into t1 (gesuchnr,benutzer_id) values (1,1); select * from t1 order by gesuchnr; gesuchnr benutzer_id diff --git a/mysql-test/r/ndb_transaction.result b/mysql-test/r/ndb_transaction.result new file mode 100644 index 00000000000..691b91b1d36 --- /dev/null +++ b/mysql-test/r/ndb_transaction.result @@ -0,0 +1,257 @@ +DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7; +drop database if exists mysqltest; +CREATE TABLE t1 ( +pk1 INT NOT NULL PRIMARY KEY, +attr1 INT NOT NULL +) ENGINE=ndbcluster; +begin; +insert into t1 values(1,1); +insert into t1 values(2,2); +select count(*) from t1; +count(*) +2 +select * from t1 where pk1 = 1; +pk1 attr1 +1 1 +select t1.attr1 from t1, t1 as t1x where t1.pk1 = t1x.pk1 + 1; +attr1 +2 +rollback; +select count(*) from t1; +count(*) +0 +select * from t1 where pk1 = 1; +pk1 attr1 +select t1.attr1 from t1, t1 as t1x where t1.pk1 = t1x.pk1 + 1; +attr1 +begin; +insert into t1 values(1,1); +insert into t1 values(2,2); +commit; +select count(*) from t1; +count(*) +2 +select * from t1 where pk1 = 1; +pk1 attr1 +1 1 +select t1.attr1 from t1, t1 as t1x where t1.pk1 = t1x.pk1 + 1; +attr1 +2 +begin; +update t1 set attr1 = attr1 * 2; +select count(*) from t1; +count(*) +2 +select * from t1 where pk1 = 1; +pk1 attr1 +1 2 +select * from t1, t1 as t1x where t1x.attr1 = t1.attr1 - 2; +pk1 attr1 pk1 attr1 +2 4 1 2 +rollback; +select count(*) from t1; +count(*) +2 +select * from t1 where pk1 = 1; +pk1 attr1 +1 1 +select * from t1, t1 as t1x where t1x.attr1 = t1.attr1 - 2; +pk1 attr1 pk1 attr1 +begin; +update t1 set attr1 = attr1 * 2; +commit; +select count(*) from t1; +count(*) +2 +select * from t1 where pk1 = 1; +pk1 attr1 +1 2 +select * from t1, t1 as t1x where t1x.attr1 = t1.attr1 - 2; +pk1 attr1 pk1 attr1 +2 4 1 2 +begin; +delete from t1 where attr1 = 2; +select count(*) from t1; +count(*) +1 +select * from t1 where pk1 = 1; +pk1 attr1 +select * from t1, t1 as t1x where t1x.attr1 = t1.attr1 - 2; +pk1 attr1 pk1 attr1 +rollback; +select count(*) from t1; +count(*) +2 +select * from t1 where pk1 = 1; +pk1 attr1 +1 2 +select * from t1, t1 as t1x where t1x.attr1 = t1.attr1 - 2; +pk1 attr1 pk1 attr1 +2 4 1 2 +begin; +delete from t1 where attr1 = 2; +commit; +select count(*) from t1; +count(*) +1 +select * from t1 where pk1 = 1; +pk1 attr1 +select * from t1, t1 as t1x where t1x.attr1 = t1.attr1 - 2; +pk1 attr1 pk1 attr1 +DROP TABLE t1; +CREATE TABLE t1 (id INT, id2 int) engine=ndbcluster; +begin; +insert into t1 values(1,1); +insert into t1 values(2,2); +select sum(id) from t1; +sum(id) +3 +select * from t1 where id = 1; +id id2 +1 1 +select t1.id from t1, t1 as t1x where t1.id2 = t1x.id2 + 1; +id +2 +rollback; +select sum(id) from t1; +sum(id) +NULL +select * from t1 where id = 1; +id id2 +select t1.id from t1, t1 as t1x where t1.id2 = t1x.id2 + 1; +id +begin; +insert into t1 values(1,1); +insert into t1 values(2,2); +commit; +select sum(id) from t1; +sum(id) +3 +select * from t1 where id = 1; +id id2 +1 1 +select t1.id from t1, t1 as t1x where t1.id2 = t1x.id2 + 1; +id +2 +begin; +update t1 set id = id * 2; +select sum(id) from t1; +sum(id) +6 +select * from t1 where id = 2; +id id2 +2 1 +select * from t1, t1 as t1x where t1x.id = t1.id - 2; +id id2 id id2 +4 2 2 1 +rollback; +select sum(id) from t1; +sum(id) +3 +select * from t1 where id = 2; +id id2 +2 2 +select * from t1, t1 as t1x where t1x.id = t1.id - 2; +id id2 id id2 +begin; +update t1 set id = id * 2; +commit; +select sum(id) from t1; +sum(id) +6 +select * from t1 where id = 2; +id id2 +2 1 +select * from t1, t1 as t1x where t1x.id = t1.id - 2; +id id2 id id2 +4 2 2 1 +DROP TABLE t1; +CREATE TABLE t2 ( +a bigint unsigned NOT NULL PRIMARY KEY, +b int unsigned not null, +c int unsigned +) engine=ndbcluster; +CREATE TABLE t3 ( +a bigint unsigned NOT NULL, +b bigint unsigned not null, +c bigint unsigned, +PRIMARY KEY(a) +) engine=ndbcluster; +CREATE TABLE t4 ( +a bigint unsigned NOT NULL, +b bigint unsigned not null, +c bigint unsigned NOT NULL, +d int unsigned, +PRIMARY KEY(a, b, c) +) engine=ndbcluster; +select count(*) from t2; +count(*) +0 +select count(*) from t3; +count(*) +0 +select count(*) from t4; +count(*) +0 +select count(*) from t2; +count(*) +100 +select count(*) from t3; +count(*) +100 +select count(*) from t4; +count(*) +100 +begin; +begin; +drop table t2; +drop table t3; +drop table t4; +CREATE TABLE t1 ( +pk1 INT NOT NULL PRIMARY KEY, +attr1 INT NOT NULL +) ENGINE=ndbcluster; +create database mysqltest; +use mysqltest; +CREATE TABLE t2 ( +a bigint unsigned NOT NULL PRIMARY KEY, +b int unsigned not null, +c int unsigned +) engine=ndbcluster; +begin; +insert into test.t1 values(1,1); +insert into t2 values(1,1,1); +insert into test.t1 values(2,2); +insert into t2 values(2,2,2); +select count(*) from test.t1; +count(*) +2 +select count(*) from t2; +count(*) +2 +select * from test.t1 where pk1 = 1; +pk1 attr1 +1 1 +select * from t2 where a = 1; +a b c +1 1 1 +select test.t1.attr1 +from test.t1, test.t1 as t1x where test.t1.pk1 = t1x.pk1 + 1; +attr1 +2 +select t2.a +from t2, t2 as t2x where t2.a = t2x.a + 1; +a +2 +select test.t1.pk1, a from test.t1,t2 where b > test.t1.attr1; +pk1 a +1 2 +rollback; +select count(*) from test.t1; +count(*) +0 +select count(*) from t2; +count(*) +0 +drop table test.t1, t2; +drop database mysqltest; diff --git a/mysql-test/r/ndb_truncate.result b/mysql-test/r/ndb_truncate.result new file mode 100644 index 00000000000..38f3a78029c --- /dev/null +++ b/mysql-test/r/ndb_truncate.result @@ -0,0 +1,14 @@ +DROP TABLE IF EXISTS t2; +CREATE TABLE t2 ( +a bigint unsigned NOT NULL PRIMARY KEY, +b int unsigned not null, +c int unsigned +) engine=ndbcluster; +select count(*) from t2; +count(*) +5000 +truncate table t2; +select count(*) from t2; +count(*) +0 +drop table t2; diff --git a/mysql-test/r/ndb_types.result b/mysql-test/r/ndb_types.result new file mode 100644 index 00000000000..9a45b77149b --- /dev/null +++ b/mysql-test/r/ndb_types.result @@ -0,0 +1,36 @@ +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( +auto int(5) unsigned NOT NULL auto_increment, +string char(10) default "hello", +tiny tinyint(4) DEFAULT '0' NOT NULL , +short smallint(6) DEFAULT '1' NOT NULL , +medium mediumint(8) DEFAULT '0' NOT NULL, +long_int int(11) DEFAULT '0' NOT NULL, +longlong bigint(13) DEFAULT '0' NOT NULL, +real_float float(13,1) DEFAULT 0.0 NOT NULL, +real_double double(16,4), +utiny tinyint(3) unsigned DEFAULT '0' NOT NULL, +ushort smallint(5) unsigned zerofill DEFAULT '00000' NOT NULL, +umedium mediumint(8) unsigned DEFAULT '0' NOT NULL, +ulong int(11) unsigned DEFAULT '0' NOT NULL, +ulonglong bigint(13) unsigned DEFAULT '0' NOT NULL, +time_stamp timestamp, +date_field date, +time_field time, +date_time datetime, +options enum('one','two','tree') not null, +flags set('one','two','tree') not null, +PRIMARY KEY (auto), +KEY (utiny), +KEY (tiny), +KEY (short), +KEY any_name (medium), +KEY (longlong), +KEY (real_float), +KEY (ushort), +KEY (umedium), +KEY (ulong), +KEY (ulonglong,ulong), +KEY (options,flags) +); +drop table t1; diff --git a/mysql-test/r/negation_elimination.result b/mysql-test/r/negation_elimination.result index a3a2bad7ec6..9193a125cd1 100644 --- a/mysql-test/r/negation_elimination.result +++ b/mysql-test/r/negation_elimination.result @@ -375,4 +375,17 @@ a 13 14 15 +delete from t1 where a > 3; +select a, not(not(a)) from t1; +a not(not(a)) +NULL NULL +0 0 +1 1 +2 1 +3 1 +explain extended select a, not(not(a)), not(a <= 2 and not(a)), not(a not like "1"), not (a not in (1,2)), not(a != 2) from t1 where not(not(a)) having not(not(a)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL a 5 NULL 5 Using where; Using index +Warnings: +Note 1003 select test.t1.a AS `a`,(test.t1.a <> 0) AS `not(not(a))`,((test.t1.a > 2) or test.t1.a) AS `not(a <= 2 and not(a))`,(test.t1.a like _latin1'1') AS `not(a not like "1")`,(test.t1.a in (1,2)) AS `not (a not in (1,2))`,(test.t1.a = 2) AS `not(a != 2)` from test.t1 where test.t1.a having test.t1.a drop table t1; diff --git a/mysql-test/r/null.result b/mysql-test/r/null.result index 1d76fbf2fb3..bd90b3fe3f3 100644 --- a/mysql-test/r/null.result +++ b/mysql-test/r/null.result @@ -156,3 +156,22 @@ drop table t1; select cast(NULL as signed); cast(NULL as signed) NULL +create table t1(i int, key(i)); +insert into t1 values(1); +insert into t1 select i*2 from t1; +insert into t1 select i*2 from t1; +insert into t1 select i*2 from t1; +insert into t1 select i*2 from t1; +insert into t1 select i*2 from t1; +insert into t1 select i*2 from t1; +insert into t1 select i*2 from t1; +insert into t1 select i*2 from t1; +insert into t1 select i*2 from t1; +explain select * from t1 where i=2 or i is null; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref_or_null i i 5 const 10 Using where; Using index +alter table t1 change i i int not null; +explain select * from t1 where i=2 or i is null; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref i i 4 const 7 Using where; Using index +drop table t1; diff --git a/mysql-test/r/olap.result b/mysql-test/r/olap.result index c862a020a93..f398c69a424 100644 --- a/mysql-test/r/olap.result +++ b/mysql-test/r/olap.result @@ -271,3 +271,39 @@ i i COUNT(*) 100 NULL 2 NULL NULL 2 drop table t1,t2; +CREATE TABLE user_day( +user_id INT NOT NULL, +date DATE NOT NULL, +UNIQUE INDEX user_date (user_id, date) +); +INSERT INTO user_day VALUES +(1, '2004-06-06' ), +(1, '2004-06-07' ), +(2, '2004-06-06' ); +SELECT +d.date AS day, +COUNT(d.user_id) as sample, +COUNT(next_day.user_id) AS not_cancelled +FROM user_day d +LEFT JOIN user_day next_day +ON next_day.user_id=d.user_id AND +next_day.date= DATE_ADD( d.date, interval 1 day ) +GROUP BY day; +day sample not_cancelled +2004-06-06 2 1 +2004-06-07 1 0 +SELECT +d.date AS day, +COUNT(d.user_id) as sample, +COUNT(next_day.user_id) AS not_cancelled +FROM user_day d +LEFT JOIN user_day next_day +ON next_day.user_id=d.user_id AND +next_day.date= DATE_ADD( d.date, interval 1 day ) +GROUP BY day +WITH ROLLUP; +day sample not_cancelled +2004-06-06 2 1 +2004-06-07 1 0 +NULL 3 1 +DROP TABLE user_day; diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 6b03dc239e7..a431807a560 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -116,7 +116,7 @@ col1 2 3 2 -select col1 as id from t1 order by t1.id; +select col1 as id from t1 order by id; id 1 1 @@ -126,16 +126,16 @@ id 2 2 3 -select concat(col1) as id from t1 order by t1.id; +select concat(col1) as id from t1 order by id; id -2 -2 1 1 1 2 -3 2 +2 +2 +3 drop table t1; CREATE TABLE t1 (id int auto_increment primary key,aika varchar(40),aikakentta timestamp); insert into t1 (aika) values ('Keskiviikko'); @@ -660,3 +660,66 @@ a b c d 1 1 12 -1 1 1 2 0 drop table t1, t2; +create table t1 (col1 int, col int); +create table t2 (col2 int, col int); +insert into t1 values (1,1),(2,2),(3,3); +insert into t2 values (1,3),(2,2),(3,1); +select t1.* , t2.col as t2_col from t1 left join t2 on (t1.col1=t2.col2) +order by col; +col1 col t2_col +1 1 3 +2 2 2 +3 3 1 +select col1 as col, col from t1 order by col; +ERROR 23000: Column 'col' in order clause is ambiguous +select t1.col as c1, t2.col as c2 from t1, t2 where t1.col1=t2.col2 +order by col; +ERROR 23000: Column 'col' in order clause is ambiguous +select t1.col as c1, t2.col as c2 from t1, t2 where t1.col1=t2.col2 +order by col; +ERROR 23000: Column 'col' in order clause is ambiguous +select col1 from t1, t2 where t1.col1=t2.col2 order by col; +ERROR 23000: Column 'col' in order clause is ambiguous +select t1.col as t1_col, t2.col from t1, t2 where t1.col1=t2.col2 +order by col; +t1_col col +3 1 +2 2 +1 3 +select col2 as c, col as c from t2 order by col; +c c +3 1 +2 2 +1 3 +select col2 as col, col as col2 from t2 order by col; +col col2 +1 3 +2 2 +3 1 +select t1.col as t1_col, t2.col2 from t1, t2 where t1.col1=t2.col2 +order by col; +t1_col col2 +1 1 +2 2 +3 3 +select t2.col2, t2.col, t2.col from t2 order by col; +col2 col col +3 1 1 +2 2 2 +1 3 3 +select t2.col2 as col from t2 order by t2.col; +col +3 +2 +1 +select t2.col2 as col, t2.col from t2 order by t2.col; +col col +3 1 +2 2 +1 3 +select t2.col2, t2.col, t2.col from t2 order by t2.col; +col2 col col +3 1 1 +2 2 2 +1 3 3 +drop table t1, t2; diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index 27f4ce7f815..321b8894796 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -219,3 +219,54 @@ Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length I t1 MyISAM 9 Dynamic 0 0 0 4294967295 1024 0 NULL # # # latin1_swedish_ci NULL deallocate prepare stmt1 ; drop table t1; +create table t1(a varchar(2), b varchar(3)); +prepare stmt1 from "select a, b from t1 where (not (a='aa' and b < 'zzz'))"; +execute stmt1; +a b +execute stmt1; +a b +deallocate prepare stmt1; +drop table t1; +prepare stmt1 from "select 1 into @var"; +execute stmt1; +execute stmt1; +prepare stmt1 from "create table t1 select 1 as i"; +execute stmt1; +drop table t1; +execute stmt1; +prepare stmt1 from "insert into t1 select i from t1"; +execute stmt1; +execute stmt1; +prepare stmt1 from "select * from t1 into outfile 'f1.txt'"; +execute stmt1; +deallocate prepare stmt1; +drop table t1; +prepare stmt1 from 'select 1'; +prepare STMT1 from 'select 2'; +execute sTmT1; +2 +2 +deallocate prepare StMt1; +deallocate prepare Stmt1; +ERROR HY000: Unknown prepared statement handler (Stmt1) given to DEALLOCATE PREPARE +set names utf8; +prepare `ĵ` from 'select 1234'; +execute `ĵ` ; +1234 +1234 +set names latin1; +execute `ü`; +1234 +1234 +set names default; +create table t1 (a varchar(10)) charset=utf8; +insert into t1 (a) values ('yahoo'); +set character_set_connection=latin1; +prepare stmt from 'select a from t1 where a like ?'; +set @var='google'; +execute stmt using @var; +a +execute stmt using @var; +a +deallocate prepare stmt; +drop table t1; diff --git a/mysql-test/r/ps_1general.result b/mysql-test/r/ps_1general.result index 6a21055cd3e..dc67fda7efa 100644 --- a/mysql-test/r/ps_1general.result +++ b/mysql-test/r/ps_1general.result @@ -317,6 +317,7 @@ NDBCLUSTER YES/NO Clustered, fault-tolerant, memory-based tables NDB YES/NO Alias for NDBCLUSTER EXAMPLE YES/NO Example storage engine ARCHIVE YES/NO Archive storage engine +CSV YES/NO CSV storage engine drop table if exists tx; prepare stmt1 from ' drop table if exists tx ' ; execute stmt1 ; @@ -333,12 +334,12 @@ prepare stmt1 from ' deallocate prepare never_prepared ' ; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'never_prepared' at line 1 prepare stmt4 from ' use test ' ; ERROR HY000: This command is not supported in the prepared statement protocol yet -prepare stmt3 from ' create database drop_me '; +prepare stmt3 from ' create database mysqltest '; ERROR HY000: This command is not supported in the prepared statement protocol yet -create database drop_me ; -prepare stmt3 from ' drop database drop_me '; +create database mysqltest ; +prepare stmt3 from ' drop database mysqltest '; ERROR HY000: This command is not supported in the prepared statement protocol yet -drop database drop_me ; +drop database mysqltest ; prepare stmt3 from ' grant all on test.t1 to drop_user@localhost identified by ''looser'' '; ERROR HY000: This command is not supported in the prepared statement protocol yet diff --git a/mysql-test/r/ps_2myisam.result b/mysql-test/r/ps_2myisam.result index a6d09202e4e..344f02bb8ab 100644 --- a/mysql-test/r/ps_2myisam.result +++ b/mysql-test/r/ps_2myisam.result @@ -870,7 +870,7 @@ def test t_many_col_types t_many_col_types c11 c11 0 9 6 Y 32768 4 63 def test t_many_col_types t_many_col_types c12 c12 0 10 6 Y 32768 4 63 def test t_many_col_types t_many_col_types c13 c13 10 10 10 Y 128 0 63 def test t_many_col_types t_many_col_types c14 c14 12 19 19 Y 128 0 63 -def test t_many_col_types t_many_col_types c15 c15 7 19 19 N 1217 0 63 +def test t_many_col_types t_many_col_types c15 c15 7 19 19 N 1249 0 63 def test t_many_col_types t_many_col_types c16 c16 11 8 8 Y 128 0 63 def test t_many_col_types t_many_col_types c17 c17 13 4 4 Y 32864 0 63 def test t_many_col_types t_many_col_types c18 c18 1 1 1 Y 32768 0 63 diff --git a/mysql-test/r/ps_3innodb.result b/mysql-test/r/ps_3innodb.result index 606716f6583..162004c7b0c 100644 --- a/mysql-test/r/ps_3innodb.result +++ b/mysql-test/r/ps_3innodb.result @@ -870,7 +870,7 @@ def test t_many_col_types t_many_col_types c11 c11 0 9 6 Y 32768 4 63 def test t_many_col_types t_many_col_types c12 c12 0 10 6 Y 32768 4 63 def test t_many_col_types t_many_col_types c13 c13 10 10 10 Y 128 0 63 def test t_many_col_types t_many_col_types c14 c14 12 19 19 Y 128 0 63 -def test t_many_col_types t_many_col_types c15 c15 7 19 19 N 1217 0 63 +def test t_many_col_types t_many_col_types c15 c15 7 19 19 N 1249 0 63 def test t_many_col_types t_many_col_types c16 c16 11 8 8 Y 128 0 63 def test t_many_col_types t_many_col_types c17 c17 13 4 4 Y 32864 0 63 def test t_many_col_types t_many_col_types c18 c18 1 1 1 Y 32768 0 63 diff --git a/mysql-test/r/ps_4heap.result b/mysql-test/r/ps_4heap.result index 5643ec637fc..23874c9b02b 100644 --- a/mysql-test/r/ps_4heap.result +++ b/mysql-test/r/ps_4heap.result @@ -871,7 +871,7 @@ def test t_many_col_types t_many_col_types c11 c11 0 9 6 Y 32768 4 63 def test t_many_col_types t_many_col_types c12 c12 0 10 6 Y 32768 4 63 def test t_many_col_types t_many_col_types c13 c13 10 10 10 Y 128 0 63 def test t_many_col_types t_many_col_types c14 c14 12 19 19 Y 128 0 63 -def test t_many_col_types t_many_col_types c15 c15 7 19 19 N 1217 0 63 +def test t_many_col_types t_many_col_types c15 c15 7 19 19 N 1249 0 63 def test t_many_col_types t_many_col_types c16 c16 11 8 8 Y 128 0 63 def test t_many_col_types t_many_col_types c17 c17 13 4 4 Y 32864 0 63 def test t_many_col_types t_many_col_types c18 c18 1 1 1 Y 32768 0 63 diff --git a/mysql-test/r/ps_5merge.result b/mysql-test/r/ps_5merge.result index 97069823c26..d1c3597b22b 100644 --- a/mysql-test/r/ps_5merge.result +++ b/mysql-test/r/ps_5merge.result @@ -913,7 +913,7 @@ def test t_many_col_types t_many_col_types c11 c11 0 9 6 Y 32768 4 63 def test t_many_col_types t_many_col_types c12 c12 0 10 6 Y 32768 4 63 def test t_many_col_types t_many_col_types c13 c13 10 10 10 Y 128 0 63 def test t_many_col_types t_many_col_types c14 c14 12 19 19 Y 128 0 63 -def test t_many_col_types t_many_col_types c15 c15 7 19 19 N 1217 0 63 +def test t_many_col_types t_many_col_types c15 c15 7 19 19 N 1249 0 63 def test t_many_col_types t_many_col_types c16 c16 11 8 8 Y 128 0 63 def test t_many_col_types t_many_col_types c17 c17 13 4 4 Y 32864 0 63 def test t_many_col_types t_many_col_types c18 c18 1 1 1 Y 32768 0 63 @@ -2105,7 +2105,7 @@ def test t_many_col_types t_many_col_types c11 c11 0 9 6 Y 32768 4 63 def test t_many_col_types t_many_col_types c12 c12 0 10 6 Y 32768 4 63 def test t_many_col_types t_many_col_types c13 c13 10 10 10 Y 128 0 63 def test t_many_col_types t_many_col_types c14 c14 12 19 19 Y 128 0 63 -def test t_many_col_types t_many_col_types c15 c15 7 19 19 N 1217 0 63 +def test t_many_col_types t_many_col_types c15 c15 7 19 19 N 1249 0 63 def test t_many_col_types t_many_col_types c16 c16 11 8 8 Y 128 0 63 def test t_many_col_types t_many_col_types c17 c17 13 4 4 Y 32864 0 63 def test t_many_col_types t_many_col_types c18 c18 1 1 1 Y 32768 0 63 diff --git a/mysql-test/r/ps_6bdb.result b/mysql-test/r/ps_6bdb.result index b849d874b9a..31dfdd7bb87 100644 --- a/mysql-test/r/ps_6bdb.result +++ b/mysql-test/r/ps_6bdb.result @@ -870,7 +870,7 @@ def test t_many_col_types t_many_col_types c11 c11 0 9 6 Y 32768 4 63 def test t_many_col_types t_many_col_types c12 c12 0 10 6 Y 32768 4 63 def test t_many_col_types t_many_col_types c13 c13 10 10 10 Y 128 0 63 def test t_many_col_types t_many_col_types c14 c14 12 19 19 Y 128 0 63 -def test t_many_col_types t_many_col_types c15 c15 7 19 19 N 1217 0 63 +def test t_many_col_types t_many_col_types c15 c15 7 19 19 N 1249 0 63 def test t_many_col_types t_many_col_types c16 c16 11 8 8 Y 128 0 63 def test t_many_col_types t_many_col_types c17 c17 13 4 4 Y 32864 0 63 def test t_many_col_types t_many_col_types c18 c18 1 1 1 Y 32768 0 63 diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index bdd87919fd9..01ee018a434 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -475,3 +475,89 @@ id name uid id name uid 1025 Y 25 1025 Y 25 1026 Z 26 1026 Z 26 drop table t1,t2; +create table t1 (x bigint unsigned not null); +insert into t1(x) values (0xfffffffffffffff0); +insert into t1(x) values (0xfffffffffffffff1); +select * from t1; +x +18446744073709551600 +18446744073709551601 +select count(*) from t1 where x>0; +count(*) +2 +select count(*) from t1 where x=0; +count(*) +0 +select count(*) from t1 where x<0; +count(*) +0 +select count(*) from t1 where x < -16; +count(*) +0 +select count(*) from t1 where x = -16; +count(*) +0 +select count(*) from t1 where x > -16; +count(*) +2 +select count(*) from t1 where x = 18446744073709551601; +count(*) +1 +create table t2 (x bigint not null); +insert into t2(x) values (0xfffffffffffffff0); +insert into t2(x) values (0xfffffffffffffff1); +select * from t2; +x +-16 +-15 +select count(*) from t2 where x>0; +count(*) +0 +select count(*) from t2 where x=0; +count(*) +0 +select count(*) from t2 where x<0; +count(*) +2 +select count(*) from t2 where x < -16; +count(*) +0 +select count(*) from t2 where x = -16; +count(*) +1 +select count(*) from t2 where x > -16; +count(*) +1 +select count(*) from t2 where x = 18446744073709551601; +count(*) +0 +drop table t1; +create table t1 (x bigint unsigned not null primary key) engine=innodb; +insert into t1(x) values (0xfffffffffffffff0); +insert into t1(x) values (0xfffffffffffffff1); +select * from t1; +x +18446744073709551600 +18446744073709551601 +select count(*) from t1 where x>0; +count(*) +2 +select count(*) from t1 where x=0; +count(*) +0 +select count(*) from t1 where x<0; +count(*) +0 +select count(*) from t1 where x < -16; +count(*) +0 +select count(*) from t1 where x = -16; +count(*) +0 +select count(*) from t1 where x > -16; +count(*) +1 +select count(*) from t1 where x = 18446744073709551601; +count(*) +1 +drop table t1; diff --git a/mysql-test/r/rename.result b/mysql-test/r/rename.result index c2dfd9fa219..c6da8285479 100644 --- a/mysql-test/r/rename.result +++ b/mysql-test/r/rename.result @@ -1,4 +1,5 @@ drop table if exists t0,t1,t2,t3,t4; +drop table if exists t0,t5,t6,t7,t8,t9; create table t0 SELECT 1,"table 1"; create table t2 SELECT 2,"table 2"; create table t3 SELECT 3,"table 3"; diff --git a/mysql-test/r/rpl_charset.result b/mysql-test/r/rpl_charset.result index 5522fd3e89b..d961173f849 100644 --- a/mysql-test/r/rpl_charset.result +++ b/mysql-test/r/rpl_charset.result @@ -4,41 +4,41 @@ reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; -drop database if exists test2; -drop database if exists test3; -create database test2 character set latin2; +drop database if exists mysqltest2; +drop database if exists mysqltest3; +create database mysqltest2 character set latin2; set @@character_set_server=latin5; -create database test3; +create database mysqltest3; --- --master-- -show create database test2; +show create database mysqltest2; Database Create Database -test2 CREATE DATABASE `test2` /*!40100 DEFAULT CHARACTER SET latin2 */ -show create database test3; +mysqltest2 CREATE DATABASE `mysqltest2` /*!40100 DEFAULT CHARACTER SET latin2 */ +show create database mysqltest3; Database Create Database -test3 CREATE DATABASE `test3` /*!40100 DEFAULT CHARACTER SET latin5 */ +mysqltest3 CREATE DATABASE `mysqltest3` /*!40100 DEFAULT CHARACTER SET latin5 */ --- --slave-- -show create database test2; +show create database mysqltest2; Database Create Database -test2 CREATE DATABASE `test2` /*!40100 DEFAULT CHARACTER SET latin2 */ -show create database test3; +mysqltest2 CREATE DATABASE `mysqltest2` /*!40100 DEFAULT CHARACTER SET latin2 */ +show create database mysqltest3; Database Create Database -test3 CREATE DATABASE `test3` /*!40100 DEFAULT CHARACTER SET latin5 */ +mysqltest3 CREATE DATABASE `mysqltest3` /*!40100 DEFAULT CHARACTER SET latin5 */ set @@collation_server=armscii8_bin; -drop database test3; -create database test3; +drop database mysqltest3; +create database mysqltest3; --- --master-- -show create database test3; +show create database mysqltest3; Database Create Database -test3 CREATE DATABASE `test3` /*!40100 DEFAULT CHARACTER SET armscii8 COLLATE armscii8_bin */ +mysqltest3 CREATE DATABASE `mysqltest3` /*!40100 DEFAULT CHARACTER SET armscii8 COLLATE armscii8_bin */ --- --slave-- -show create database test3; +show create database mysqltest3; Database Create Database -test3 CREATE DATABASE `test3` /*!40100 DEFAULT CHARACTER SET armscii8 COLLATE armscii8_bin */ -use test2; +mysqltest3 CREATE DATABASE `mysqltest3` /*!40100 DEFAULT CHARACTER SET armscii8 COLLATE armscii8_bin */ +use mysqltest2; create table t1 (a int auto_increment primary key, b varchar(100)); set character_set_client=cp850, collation_connection=latin2_croatian_ci; insert into t1 (b) values(@@character_set_server); @@ -57,7 +57,7 @@ a b 5 latin2_croatian_ci --- --slave-- -select * from test2.t1 order by a; +select * from mysqltest2.t1 order by a; a b 1 armscii8 2 armscii8_bin @@ -81,7 +81,7 @@ a b 4 Müller --- --slave-- -select * from test2.t1 order by a; +select * from mysqltest2.t1 order by a; a b 1 latin1_german1_ci 2 Muffler @@ -98,69 +98,69 @@ a b 1 cp850_general_ci --- --slave-- -select * from test2.t1 order by a; +select * from mysqltest2.t1 order by a; a b 1 cp850_general_ci -drop database test2; -drop database test3; +drop database mysqltest2; +drop database mysqltest3; show binlog events from 95; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 95 Query 1 181 use `test`; drop database if exists test2 -master-bin.000001 181 Query 1 267 use `test`; drop database if exists test3 -master-bin.000001 267 Query 1 366 use `test`; create database test2 character set latin2 +master-bin.000001 95 Query 1 181 use `test`; drop database if exists mysqltest2 +master-bin.000001 181 Query 1 267 use `test`; drop database if exists mysqltest3 +master-bin.000001 267 Query 1 366 use `test`; create database mysqltest2 character set latin2 master-bin.000001 366 Query 1 522 use `test`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=8,COLLATION_SERVER=30 -master-bin.000001 522 Query 1 600 use `test`; create database test3 +master-bin.000001 522 Query 1 600 use `test`; create database mysqltest3 master-bin.000001 600 Query 1 756 use `test`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=8,COLLATION_SERVER=64 -master-bin.000001 756 Query 1 832 use `test`; drop database test3 +master-bin.000001 756 Query 1 832 use `test`; drop database mysqltest3 master-bin.000001 832 Query 1 988 use `test`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=8,COLLATION_SERVER=64 -master-bin.000001 988 Query 1 1066 use `test`; create database test3 -master-bin.000001 1066 Query 1 1223 use `test2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 1223 Query 1 1347 use `test2`; create table t1 (a int auto_increment primary key, b varchar(100)) -master-bin.000001 1347 Query 1 1505 use `test2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 988 Query 1 1066 use `test`; create database mysqltest3 +master-bin.000001 1066 Query 1 1223 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 1223 Query 1 1347 use `mysqltest2`; create table t1 (a int auto_increment primary key, b varchar(100)) +master-bin.000001 1347 Query 1 1505 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64 master-bin.000001 1505 Intvar 1 1533 INSERT_ID=1 -master-bin.000001 1533 Query 1 1640 use `test2`; insert into t1 (b) values(@@character_set_server) -master-bin.000001 1640 Query 1 1798 use `test2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 1533 Query 1 1640 use `mysqltest2`; insert into t1 (b) values(@@character_set_server) +master-bin.000001 1640 Query 1 1798 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64 master-bin.000001 1798 Intvar 1 1826 INSERT_ID=2 -master-bin.000001 1826 Query 1 1929 use `test2`; insert into t1 (b) values(@@collation_server) -master-bin.000001 1929 Query 1 2087 use `test2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 1826 Query 1 1929 use `mysqltest2`; insert into t1 (b) values(@@collation_server) +master-bin.000001 1929 Query 1 2087 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64 master-bin.000001 2087 Intvar 1 2115 INSERT_ID=3 -master-bin.000001 2115 Query 1 2222 use `test2`; insert into t1 (b) values(@@character_set_client) -master-bin.000001 2222 Query 1 2380 use `test2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 2115 Query 1 2222 use `mysqltest2`; insert into t1 (b) values(@@character_set_client) +master-bin.000001 2222 Query 1 2380 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64 master-bin.000001 2380 Intvar 1 2408 INSERT_ID=4 -master-bin.000001 2408 Query 1 2519 use `test2`; insert into t1 (b) values(@@character_set_connection) -master-bin.000001 2519 Query 1 2677 use `test2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 2408 Query 1 2519 use `mysqltest2`; insert into t1 (b) values(@@character_set_connection) +master-bin.000001 2519 Query 1 2677 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64 master-bin.000001 2677 Intvar 1 2705 INSERT_ID=5 -master-bin.000001 2705 Query 1 2812 use `test2`; insert into t1 (b) values(@@collation_connection) -master-bin.000001 2812 Query 1 2969 use `test2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=5,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 2969 Query 1 3044 use `test2`; truncate table t1 -master-bin.000001 3044 Query 1 3201 use `test2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=5,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 2705 Query 1 2812 use `mysqltest2`; insert into t1 (b) values(@@collation_connection) +master-bin.000001 2812 Query 1 2969 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=5,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 2969 Query 1 3044 use `mysqltest2`; truncate table t1 +master-bin.000001 3044 Query 1 3201 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=5,COLLATION_DATABASE=9,COLLATION_SERVER=64 master-bin.000001 3201 Intvar 1 3229 INSERT_ID=1 -master-bin.000001 3229 Query 1 3336 use `test2`; insert into t1 (b) values(@@collation_connection) -master-bin.000001 3336 Query 1 3493 use `test2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=5,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 3229 Query 1 3336 use `mysqltest2`; insert into t1 (b) values(@@collation_connection) +master-bin.000001 3336 Query 1 3493 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=5,COLLATION_DATABASE=9,COLLATION_SERVER=64 master-bin.000001 3493 Intvar 1 3521 INSERT_ID=2 -master-bin.000001 3521 Query 1 3631 use `test2`; insert into t1 (b) values(LEAST("Müller","Muffler")) -master-bin.000001 3631 Query 1 3789 use `test2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 3521 Query 1 3631 use `mysqltest2`; insert into t1 (b) values(LEAST("Müller","Muffler")) +master-bin.000001 3631 Query 1 3789 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 master-bin.000001 3789 Intvar 1 3817 INSERT_ID=3 -master-bin.000001 3817 Query 1 3924 use `test2`; insert into t1 (b) values(@@collation_connection) -master-bin.000001 3924 Query 1 4082 use `test2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 3817 Query 1 3924 use `mysqltest2`; insert into t1 (b) values(@@collation_connection) +master-bin.000001 3924 Query 1 4082 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 master-bin.000001 4082 Intvar 1 4110 INSERT_ID=4 -master-bin.000001 4110 Query 1 4220 use `test2`; insert into t1 (b) values(LEAST("Müller","Muffler")) -master-bin.000001 4220 Query 1 4378 use `test2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 4110 Query 1 4220 use `mysqltest2`; insert into t1 (b) values(LEAST("Müller","Muffler")) +master-bin.000001 4220 Query 1 4378 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 master-bin.000001 4378 Intvar 1 4406 INSERT_ID=74 -master-bin.000001 4406 Create_file 1 5074 db=test2;table=t1;file_id=1;block_len=581 -master-bin.000001 5074 Query 1 5232 use `test2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 4406 Create_file 1 5074 db=mysqltest2;table=t1;file_id=1;block_len=581 +master-bin.000001 5074 Query 1 5232 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 master-bin.000001 5232 Intvar 1 5260 INSERT_ID=5 master-bin.000001 5260 Exec_load 1 5283 ;file_id=1 -master-bin.000001 5283 Query 1 5441 use `test2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 5441 Query 1 5516 use `test2`; truncate table t1 -master-bin.000001 5516 Query 1 5674 use `test2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 5283 Query 1 5441 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 5441 Query 1 5516 use `mysqltest2`; truncate table t1 +master-bin.000001 5516 Query 1 5674 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 master-bin.000001 5674 Intvar 1 5702 INSERT_ID=1 master-bin.000001 5702 User var 1 5742 @`a`=_cp850 0x4DFC6C6C6572 COLLATE cp850_general_ci -master-bin.000001 5742 Query 1 5840 use `test2`; insert into t1 (b) values(collation(@a)) -master-bin.000001 5840 Query 1 5998 use `test2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 5998 Query 1 6075 use `test2`; drop database test2 +master-bin.000001 5742 Query 1 5840 use `mysqltest2`; insert into t1 (b) values(collation(@a)) +master-bin.000001 5840 Query 1 5998 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 +master-bin.000001 5998 Query 1 6075 use `mysqltest2`; drop database mysqltest2 master-bin.000001 6075 Query 1 6228 SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64 -master-bin.000001 6228 Query 1 6300 drop database test3 +master-bin.000001 6228 Query 1 6300 drop database mysqltest3 set global character_set_server=latin2; ERROR HY000: Binary logging and replication forbid changing the global server character set or collation set global character_set_server=latin2; diff --git a/mysql-test/r/rpl_delete_all.result b/mysql-test/r/rpl_delete_all.result index 97a535490dd..5ed221823e8 100644 --- a/mysql-test/r/rpl_delete_all.result +++ b/mysql-test/r/rpl_delete_all.result @@ -4,12 +4,12 @@ reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; -create database test1; -drop database if exists test1; +create database mysqltest; +drop database if exists mysqltest; Warnings: -Note 1008 Can't drop database 'test1'; database doesn't exist -show tables from test1; -ERROR HY000: Can't read dir of './test1/' (Errcode: X) +Note 1008 Can't drop database 'mysqltest'; database doesn't exist +show tables from mysqltest; +ERROR HY000: Can't read dir of './mysqltest/' (Errcode: X) create table t1 (a int); drop table if exists t1; Warnings: diff --git a/mysql-test/r/rpl_heap.result b/mysql-test/r/rpl_heap.result index 1556bcd5f25..1facbcb7676 100644 --- a/mysql-test/r/rpl_heap.result +++ b/mysql-test/r/rpl_heap.result @@ -1,22 +1,22 @@ reset master; drop table if exists t1; -create table t1 (a int) type=HEAP; -insert into t1 values(10); +create table t1 type=HEAP select 10 as a; +insert into t1 values(11); show binlog events from 79; Log_name Pos Event_type Server_id Orig_log_pos Info -master-bin.001 79 Query 1 79 use `test`; create table t1 (a int) type=HEAP -master-bin.001 147 Query 1 147 use `test`; DELETE FROM `test`.`t1` -master-bin.001 205 Query 1 205 use `test`; insert into t1 values(10) +master-bin.001 79 Query 1 79 use `test`; create table t1 type=HEAP select 10 as a +master-bin.001 154 Query 1 154 use `test`; insert into t1 values(11) reset slave; start slave; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` int(11) default NULL + `a` bigint(2) NOT NULL default '0' ) TYPE=HEAP select * from t1; a 10 +11 select * from t1; a select * from t1 limit 10; diff --git a/mysql-test/r/select_found.result b/mysql-test/r/select_found.result index 470a3e8439f..00dbcb54d93 100644 --- a/mysql-test/r/select_found.result +++ b/mysql-test/r/select_found.result @@ -81,7 +81,10 @@ email varchar(50) NOT NULL default '', PRIMARY KEY (id), UNIQUE KEY e_n (email,name) ); -INSERT INTO t2 VALUES (1,'name1','email1'),(2,'name2','email2'),(3,'name3','email3'),(4,'name4','email4'),(5,'name5','email5'),(6,'name6','email6'),(7,'name7','email7'),(8,'name8','email8'),(9,'name9','email9'),(10,'name10','email10'),(11,'name11','email11'),(12,'name12','email12'),(13,'name13','email13'),(14,'name14','email14'),(15,'name15','email15'),(16,'name16','email16'),(17,'name17','email17'),(18,'name18','email18'),(19,'name19','email19'),(20,'name20','email20'),(21,'name21','email21'),(22,'name22','email22'),(23,'name23','email23'),(24,'name24','email24'),(25,'name25','email25'),(26,'name26','email26'),(27,'name27','email27'),(28,'name28','email28'),(29,'name29','email29'),(30,'name30','email30'),(31,'name31','email31'),(32,'name32','email32'),(33,'name33','email33'),(34,'name34','email34'),(35,'name35','email35'),(36,'name36','email36'),(37,'name37','email37'),(38,'name38','email38'),(39,'name39','email39'),(40,'name40','email40'),(41,'name41','email41'),(42,'name42','email42'),(43,'name43','email43'),(44,'name44','email44'),(45,'name45','email45'),(46,'name46','email46'),(47,'name47','email47'),(48,'name48','email48'),(49,'name49','email49'),(50,'name50','email50'),(51,'name51','email51'),(52,'name52','email52'),(53,'name53','email53'),(54,'name54','email54'),(55,'name55','email55'),(56,'name56','email56'),(57,'name57','email57'),(58,'name58','email58'),(59,'name59','email59'),(60,'name60','email60'),(61,'name61','email61'),(62,'name62','email62'),(63,'name63','email63'),(64,'name64','email64'),(65,'name65','email65'),(66,'name66','email66'),(67,'name67','email67'),(68,'name68','email68'),(69,'name69','email69'),(70,'name70','email70'),(71,'name71','email71'),(72,'name72','email72'),(73,'name73','email73'),(74,'name74','email74'),(75,'name75','email75'),(76,'name76','email76'),(77,'name77','email77'),(78,'name78','email78'),(79,'name79','email79'),(80,'name80','email80'),(81,'name81','email81'),(82,'name82','email82'),(83,'name83','email83'),(84,'name84','email84'),(85,'name85','email85'),(86,'name86','email86'),(87,'name87','email87'),(88,'name88','email88'),(89,'name89','email89'),(90,'name90','email90'),(91,'name91','email91'),(92,'name92','email92'),(93,'name93','email93'),(94,'name94','email94'),(95,'name95','email95'),(96,'name96','email96'),(97,'name97','email97'),(98,'name98','email98'),(99,'name99','email99'),(100,'name100','email100'),(101,'name101','email101'),(102,'name102','email102'),(103,'name103','email103'),(104,'name104','email104'),(105,'name105','email105'),(106,'name106','email106'),(107,'name107','email107'),(108,'name108','email108'),(109,'name109','email109'),(110,'name110','email110'),(111,'name111','email111'),(112,'name112','email112'),(113,'name113','email113'),(114,'name114','email114'),(115,'name115','email115'),(116,'name116','email116'),(117,'name117','email117'),(118,'name118','email118'),(119,'name119','email119'),(120,'name120','email120'),(121,'name121','email121'),(122,'name122','email122'),(123,'name123','email123'),(124,'name124','email124'),(125,'name125','email125'),(126,'name126','email126'),(127,'name127','email127'),(128,'name128','email128'),(129,'name129','email129'),(130,'name130','email130'),(131,'name131','email131'),(132,'name132','email132'),(133,'name133','email133'),(134,'name134','email134'),(135,'name135','email135'),(136,'name136','email136'),(137,'name137','email137'),(138,'name138','email138'),(139,'name139','email139'),(140,'name140','email140'),(141,'name141','email141'),(142,'name142','email142'),(143,'name143','email143'),(144,'name144','email144'),(145,'name145','email145'),(146,'name146','email146'),(147,'name147','email147'),(148,'name148','email148'),(149,'name149','email149'),(150,'name150','email150'),(151,'name151','email151'),(152,'name152','email152'),(153,'name153','email153'),(154,'name154','email154'),(155,'name155','email155'),(156,'name156','email156'),(157,'name157','email157'),(158,'name158','email158'),(159,'name159','email159'),(160,'name160','email160'),(161,'name161','email161'),(162,'name162','email162'),(163,'name163','email163'),(164,'name164','email164'),(165,'name165','email165'),(166,'name166','email166'),(167,'name167','email167'),(168,'name168','email168'),(169,'name169','email169'),(170,'name170','email170'),(171,'name171','email171'),(172,'name172','email172'),(173,'name173','email173'),(174,'name174','email174'),(175,'name175','email175'),(176,'name176','email176'),(177,'name177','email177'),(178,'name178','email178'),(179,'name179','email179'),(180,'name180','email180'),(181,'name181','email181'),(182,'name182','email182'),(183,'name183','email183'),(184,'name184','email184'),(185,'name185','email185'),(186,'name186','email186'),(187,'name187','email187'),(188,'name188','email188'),(189,'name189','email189'),(190,'name190','email190'),(191,'name191','email191'),(192,'name192','email192'),(193,'name193','email193'),(194,'name194','email194'),(195,'name195','email195'),(196,'name196','email196'),(197,'name197','email197'),(198,'name198','email198'),(199,'name199','email199'),(200,'name200','email200'); +EXPLAIN SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL LIMIT 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system PRIMARY,kid NULL NULL NULL 0 const row not found +1 SIMPLE t2 index NULL e_n 100 NULL 200 SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL LIMIT 10; email email1 diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result index aefebad7e49..ecf91fe6f68 100644 --- a/mysql-test/r/show_check.result +++ b/mysql-test/r/show_check.result @@ -1,4 +1,5 @@ drop table if exists t1,t2; +drop database if exists mysqltest; create table t1 (a int not null primary key, b int not null,c int not null, key(b,c)); insert into t1 values (1,2,2),(2,2,3),(3,2,4),(4,2,4); check table t1 fast; @@ -361,39 +362,39 @@ t1 HEAP 9 Fixed 0 5 # # # 5 NULL NULL NULL NULL latin1_swedish_ci NULL t2 HEAP 9 Fixed 0 5 # # # 5 NULL NULL NULL NULL latin1_swedish_ci NULL t3 HEAP 9 Fixed 0 9 # # # 9 NULL NULL NULL NULL latin1_swedish_ci NULL drop table t1, t2, t3; -create database test_$1; -show create database test_$1; +create database mysqltest; +show create database mysqltest; Database Create Database -test_$1 CREATE DATABASE `test_$1` /*!40100 DEFAULT CHARACTER SET latin1 */ -create table test_$1.t1(a int); -insert into test_$1.t1 values(1); -grant select on `test_$1`.* to mysqltest_1@localhost; -grant usage on `test_$1`.* to mysqltest_2@localhost; -grant drop on `test_$1`.* to mysqltest_3@localhost; +mysqltest CREATE DATABASE `mysqltest` /*!40100 DEFAULT CHARACTER SET latin1 */ +create table mysqltest.t1(a int); +insert into mysqltest.t1 values(1); +grant select on `mysqltest`.* to mysqltest_1@localhost; +grant usage on `mysqltest`.* to mysqltest_2@localhost; +grant drop on `mysqltest`.* to mysqltest_3@localhost; select * from t1; a 1 -show create database test_$1; +show create database mysqltest; Database Create Database -test_$1 CREATE DATABASE `test_$1` /*!40100 DEFAULT CHARACTER SET latin1 */ +mysqltest CREATE DATABASE `mysqltest` /*!40100 DEFAULT CHARACTER SET latin1 */ drop table t1; -ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'test_$1' -drop database test_$1; -ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'test_$1' -select * from test_$1.t1; -ERROR 42000: Access denied for user 'mysqltest_2'@'localhost' to database 'test_$1' -show create database test_$1; -ERROR 42000: Access denied for user 'mysqltest_2'@'localhost' to database 'test_$1' -drop table test_$1.t1; -ERROR 42000: Access denied for user 'mysqltest_2'@'localhost' to database 'test_$1' -drop database test_$1; -ERROR 42000: Access denied for user 'mysqltest_2'@'localhost' to database 'test_$1' -select * from test_$1.t1; -ERROR 42000: Access denied for user 'mysqltest_3'@'localhost' to database 'test_$1' -show create database test_$1; -ERROR 42000: Access denied for user 'mysqltest_3'@'localhost' to database 'test_$1' -drop table test_$1.t1; -drop database test_$1; +ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'mysqltest' +drop database mysqltest; +ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'mysqltest' +select * from mysqltest.t1; +ERROR 42000: Access denied for user 'mysqltest_2'@'localhost' to database 'mysqltest' +show create database mysqltest; +ERROR 42000: Access denied for user 'mysqltest_2'@'localhost' to database 'mysqltest' +drop table mysqltest.t1; +ERROR 42000: Access denied for user 'mysqltest_2'@'localhost' to database 'mysqltest' +drop database mysqltest; +ERROR 42000: Access denied for user 'mysqltest_2'@'localhost' to database 'mysqltest' +select * from mysqltest.t1; +ERROR 42000: Access denied for user 'mysqltest_3'@'localhost' to database 'mysqltest' +show create database mysqltest; +ERROR 42000: Access denied for user 'mysqltest_3'@'localhost' to database 'mysqltest' +drop table mysqltest.t1; +drop database mysqltest; set names binary; delete from mysql.user where user='mysqltest_1' || user='mysqltest_2' || user='mysqltest_3'; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 80dd5c4077a..b67989231e0 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -1259,11 +1259,6 @@ a b 2 NULL 3 1 drop table t1, t2; -create table t1(City VARCHAR(30),Location geometry); -insert into t1 values("Paris",GeomFromText('POINT(2.33 48.87)')); -select City from t1 where (select intersects(GeomFromText(AsText(Location)),GeomFromText('Polygon((2 50, 2.5 50, 2.5 47, 2 47, 2 50))'))=0); -City -drop table t1; CREATE TABLE `t1` ( `id` mediumint(8) unsigned NOT NULL auto_increment, `pseudo` varchar(35) NOT NULL default '', @@ -1907,3 +1902,77 @@ select t000.a, count(*) `C` FROM t1 t000 GROUP BY t000.a HAVING count(*) > ALL ( a C 1 1 drop table t1,t2; +create table t1 (a int not null auto_increment primary key, b varchar(40), fulltext(b)); +insert into t1 (b) values ('ball'),('ball games'), ('games'), ('foo'), ('foobar'), ('Serg'), ('Sergei'),('Georg'), ('Patrik'),('Hakan'); +create table t2 (a int); +insert into t2 values (1),(3),(2),(7); +select a,b from t1 where match(b) against ('Ball') > 0; +a b +1 ball +2 ball games +select a from t2 where a in (select a from t1 where match(b) against ('Ball') > 0); +a +1 +2 +drop table t1,t2; +CREATE TABLE t1(`IZAVORGANG_ID` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_bin,`KUERZEL` VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_bin,`IZAANALYSEART_ID` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_bin,`IZAPMKZ_ID` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_bin); +CREATE INDEX AK01IZAVORGANG ON t1(izaAnalyseart_id,Kuerzel); +INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000001','601','D0000000001','I0000000001'); +INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000002','602','D0000000001','I0000000001'); +INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000003','603','D0000000001','I0000000001'); +INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000004','101','D0000000001','I0000000001'); +SELECT `IZAVORGANG_ID` FROM t1 WHERE `KUERZEL` IN(SELECT MIN(`KUERZEL`)`Feld1` FROM t1 WHERE `KUERZEL` LIKE'601%'And`IZAANALYSEART_ID`='D0000000001'); +IZAVORGANG_ID +D0000000001 +drop table t1; +CREATE TABLE `t1` ( `aid` int(11) NOT NULL default '0', `bid` int(11) NOT NULL default '0', PRIMARY KEY (`aid`,`bid`)); +CREATE TABLE `t2` ( `aid` int(11) NOT NULL default '0', `bid` int(11) NOT NULL default '0', PRIMARY KEY (`aid`,`bid`)); +insert into t1 values (1,1),(1,2),(2,1),(2,2); +insert into t2 values (1,2),(2,2); +select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid); +aid bid +1 1 +2 1 +alter table t2 drop primary key; +alter table t2 add key KEY1 (aid, bid); +select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid); +aid bid +1 1 +2 1 +alter table t2 drop key KEY1; +alter table t2 add primary key (bid, aid); +select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid); +aid bid +1 1 +2 1 +drop table t1,t2; +CREATE TABLE t1 (howmanyvalues bigint, avalue int); +INSERT INTO t1 VALUES (1, 1),(2, 1),(2, 2),(3, 1),(3, 2),(3, 3),(4, 1),(4, 2),(4, 3),(4, 4); +SELECT howmanyvalues, count(*) from t1 group by howmanyvalues; +howmanyvalues count(*) +1 1 +2 2 +3 3 +4 4 +SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.howmanyvalues) as mycount from t1 a group by a.howmanyvalues; +howmanyvalues mycount +1 1 +2 2 +3 3 +4 4 +CREATE INDEX t1_howmanyvalues_idx ON t1 (howmanyvalues); +SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues+1 = a.howmanyvalues+1) as mycount from t1 a group by a.howmanyvalues; +howmanyvalues mycount +1 1 +2 2 +3 3 +4 4 +SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.howmanyvalues) as mycount from t1 a group by a.howmanyvalues; +howmanyvalues mycount +1 1 +2 2 +3 3 +4 4 +SELECT a.howmanyvalues, (SELECT count(*) from t1 b where b.howmanyvalues = a.avalue) as mycount from t1 a group by a.howmanyvalues; +ERROR 42S22: Unknown column 'a.avalue' in 'where clause' +drop table t1; diff --git a/mysql-test/r/subselect_gis.result b/mysql-test/r/subselect_gis.result new file mode 100644 index 00000000000..34ab7748656 --- /dev/null +++ b/mysql-test/r/subselect_gis.result @@ -0,0 +1,8 @@ +drop table if exists t1; +create table t1(City VARCHAR(30),Location geometry); +insert into t1 values("Paris",GeomFromText('POINT(2.33 48.87)')); +select City from t1 where (select +intersects(GeomFromText(AsText(Location)),GeomFromText('Polygon((2 50, 2.5 +50, 2.5 47, 2 47, 2 50))'))=0); +City +drop table t1; diff --git a/mysql-test/r/system_mysql_db.result b/mysql-test/r/system_mysql_db.result index 2d0daae09be..1b5fc4f8b27 100644 --- a/mysql-test/r/system_mysql_db.result +++ b/mysql-test/r/system_mysql_db.result @@ -19,77 +19,77 @@ user BASE TABLE show create table db; Table Create Table db CREATE TABLE `db` ( - `Host` char(60) character set latin1 collate latin1_bin NOT NULL default '', - `Db` char(64) character set latin1 collate latin1_bin NOT NULL default '', - `User` char(16) character set latin1 collate latin1_bin NOT NULL default '', - `Select_priv` enum('N','Y') NOT NULL default 'N', - `Insert_priv` enum('N','Y') NOT NULL default 'N', - `Update_priv` enum('N','Y') NOT NULL default 'N', - `Delete_priv` enum('N','Y') NOT NULL default 'N', - `Create_priv` enum('N','Y') NOT NULL default 'N', - `Drop_priv` enum('N','Y') NOT NULL default 'N', - `Grant_priv` enum('N','Y') NOT NULL default 'N', - `References_priv` enum('N','Y') NOT NULL default 'N', - `Index_priv` enum('N','Y') NOT NULL default 'N', - `Alter_priv` enum('N','Y') NOT NULL default 'N', - `Create_tmp_table_priv` enum('N','Y') NOT NULL default 'N', - `Lock_tables_priv` enum('N','Y') NOT NULL default 'N', - `Create_view_priv` enum('N','Y') NOT NULL default 'N', - `Show_view_priv` enum('N','Y') NOT NULL default 'N', + `Host` char(60) collate utf8_bin NOT NULL default '', + `Db` char(64) collate utf8_bin NOT NULL default '', + `User` char(16) collate utf8_bin NOT NULL default '', + `Select_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Insert_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Update_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Delete_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Create_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Drop_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Grant_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `References_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Index_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Alter_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Create_tmp_table_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Lock_tables_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Create_view_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Show_view_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', PRIMARY KEY (`Host`,`Db`,`User`), KEY `User` (`User`) -) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Database privileges' +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Database privileges' show create table host; Table Create Table host CREATE TABLE `host` ( - `Host` char(60) character set latin1 collate latin1_bin NOT NULL default '', - `Db` char(64) character set latin1 collate latin1_bin NOT NULL default '', - `Select_priv` enum('N','Y') NOT NULL default 'N', - `Insert_priv` enum('N','Y') NOT NULL default 'N', - `Update_priv` enum('N','Y') NOT NULL default 'N', - `Delete_priv` enum('N','Y') NOT NULL default 'N', - `Create_priv` enum('N','Y') NOT NULL default 'N', - `Drop_priv` enum('N','Y') NOT NULL default 'N', - `Grant_priv` enum('N','Y') NOT NULL default 'N', - `References_priv` enum('N','Y') NOT NULL default 'N', - `Index_priv` enum('N','Y') NOT NULL default 'N', - `Alter_priv` enum('N','Y') NOT NULL default 'N', - `Create_tmp_table_priv` enum('N','Y') NOT NULL default 'N', - `Lock_tables_priv` enum('N','Y') NOT NULL default 'N', - `Create_view_priv` enum('N','Y') NOT NULL default 'N', - `Show_view_priv` enum('N','Y') NOT NULL default 'N', + `Host` char(60) collate utf8_bin NOT NULL default '', + `Db` char(64) collate utf8_bin NOT NULL default '', + `Select_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Insert_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Update_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Delete_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Create_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Drop_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Grant_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `References_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Index_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Alter_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Create_tmp_table_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Lock_tables_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Create_view_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Show_view_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', PRIMARY KEY (`Host`,`Db`) -) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Host privileges; Merged with database privileges' +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Host privileges; Merged with database privileges' show create table user; Table Create Table user CREATE TABLE `user` ( - `Host` varchar(60) character set latin1 collate latin1_bin NOT NULL default '', - `User` varchar(16) character set latin1 collate latin1_bin NOT NULL default '', - `Password` varchar(41) character set latin1 collate latin1_bin NOT NULL default '', - `Select_priv` enum('N','Y') NOT NULL default 'N', - `Insert_priv` enum('N','Y') NOT NULL default 'N', - `Update_priv` enum('N','Y') NOT NULL default 'N', - `Delete_priv` enum('N','Y') NOT NULL default 'N', - `Create_priv` enum('N','Y') NOT NULL default 'N', - `Drop_priv` enum('N','Y') NOT NULL default 'N', - `Reload_priv` enum('N','Y') NOT NULL default 'N', - `Shutdown_priv` enum('N','Y') NOT NULL default 'N', - `Process_priv` enum('N','Y') NOT NULL default 'N', - `File_priv` enum('N','Y') NOT NULL default 'N', - `Grant_priv` enum('N','Y') NOT NULL default 'N', - `References_priv` enum('N','Y') NOT NULL default 'N', - `Index_priv` enum('N','Y') NOT NULL default 'N', - `Alter_priv` enum('N','Y') NOT NULL default 'N', - `Show_db_priv` enum('N','Y') NOT NULL default 'N', - `Super_priv` enum('N','Y') NOT NULL default 'N', - `Create_tmp_table_priv` enum('N','Y') NOT NULL default 'N', - `Lock_tables_priv` enum('N','Y') NOT NULL default 'N', - `Execute_priv` enum('N','Y') NOT NULL default 'N', - `Repl_slave_priv` enum('N','Y') NOT NULL default 'N', - `Repl_client_priv` enum('N','Y') NOT NULL default 'N', - `Create_view_priv` enum('N','Y') NOT NULL default 'N', - `Show_view_priv` enum('N','Y') NOT NULL default 'N', - `ssl_type` enum('','ANY','X509','SPECIFIED') NOT NULL default '', + `Host` varchar(60) collate utf8_bin NOT NULL default '', + `User` varchar(16) collate utf8_bin NOT NULL default '', + `Password` varchar(41) collate utf8_bin NOT NULL default '', + `Select_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Insert_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Update_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Delete_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Create_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Drop_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Reload_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Shutdown_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Process_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `File_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Grant_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `References_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Index_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Alter_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Show_db_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Super_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Create_tmp_table_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Lock_tables_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Execute_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Repl_slave_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Repl_client_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Create_view_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `Show_view_priv` enum('N','Y') collate utf8_bin NOT NULL default 'N', + `ssl_type` enum('','ANY','X509','SPECIFIED') collate utf8_bin NOT NULL default '', `ssl_cipher` blob NOT NULL, `x509_issuer` blob NOT NULL, `x509_subject` blob NOT NULL, @@ -97,41 +97,41 @@ user CREATE TABLE `user` ( `max_updates` int(11) unsigned NOT NULL default '0', `max_connections` int(11) unsigned NOT NULL default '0', PRIMARY KEY (`Host`,`User`) -) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Users and global privileges' +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges' show create table func; Table Create Table func CREATE TABLE `func` ( - `name` char(64) character set latin1 collate latin1_bin NOT NULL default '', + `name` char(64) collate utf8_bin NOT NULL default '', `ret` tinyint(1) NOT NULL default '0', - `dl` char(128) NOT NULL default '', - `type` enum('function','aggregate') NOT NULL default 'function', + `dl` char(128) collate utf8_bin NOT NULL default '', + `type` enum('function','aggregate') collate utf8_bin NOT NULL default 'function', PRIMARY KEY (`name`) -) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='User defined functions' +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='User defined functions' show create table tables_priv; Table Create Table tables_priv CREATE TABLE `tables_priv` ( - `Host` char(60) character set latin1 collate latin1_bin NOT NULL default '', - `Db` char(64) character set latin1 collate latin1_bin NOT NULL default '', - `User` char(16) character set latin1 collate latin1_bin NOT NULL default '', - `Table_name` char(64) character set latin1 collate latin1_bin NOT NULL default '', - `Grantor` char(77) NOT NULL default '', + `Host` char(60) collate utf8_bin NOT NULL default '', + `Db` char(64) collate utf8_bin NOT NULL default '', + `User` char(16) collate utf8_bin NOT NULL default '', + `Table_name` char(64) collate utf8_bin NOT NULL default '', + `Grantor` char(77) collate utf8_bin NOT NULL default '', `Timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, - `Table_priv` set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter') NOT NULL default '', - `Column_priv` set('Select','Insert','Update','References') NOT NULL default '', + `Table_priv` set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter') collate utf8_bin NOT NULL default '', + `Column_priv` set('Select','Insert','Update','References') collate utf8_bin NOT NULL default '', PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`), KEY `Grantor` (`Grantor`) -) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Table privileges' +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Table privileges' show create table columns_priv; Table Create Table columns_priv CREATE TABLE `columns_priv` ( - `Host` char(60) character set latin1 collate latin1_bin NOT NULL default '', - `Db` char(64) character set latin1 collate latin1_bin NOT NULL default '', - `User` char(16) character set latin1 collate latin1_bin NOT NULL default '', - `Table_name` char(64) character set latin1 collate latin1_bin NOT NULL default '', - `Column_name` char(64) character set latin1 collate latin1_bin NOT NULL default '', + `Host` char(60) collate utf8_bin NOT NULL default '', + `Db` char(64) collate utf8_bin NOT NULL default '', + `User` char(16) collate utf8_bin NOT NULL default '', + `Table_name` char(64) collate utf8_bin NOT NULL default '', + `Column_name` char(64) collate utf8_bin NOT NULL default '', `Timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, - `Column_priv` set('Select','Insert','Update','References') NOT NULL default '', + `Column_priv` set('Select','Insert','Update','References') collate utf8_bin NOT NULL default '', PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`) -) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Column privileges' +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Column privileges' show tables; Tables_in_test table_type diff --git a/mysql-test/r/timezone2.result b/mysql-test/r/timezone2.result index 5361ff4ffe6..02406b77a65 100644 --- a/mysql-test/r/timezone2.result +++ b/mysql-test/r/timezone2.result @@ -244,3 +244,10 @@ NULL select convert_tz( NULL, 'MET', 'UTC'); convert_tz( NULL, 'MET', 'UTC') NULL +create table t1 (ts timestamp); +set timestamp=1000000000; +insert into t1 (ts) values (now()); +select convert_tz(ts, @@time_zone, 'Japan') from t1; +convert_tz(ts, @@time_zone, 'Japan') +2001-09-09 10:46:40 +drop table t1; diff --git a/mysql-test/r/truncate.result b/mysql-test/r/truncate.result index d777bd184b2..74a6cb72cc6 100644 --- a/mysql-test/r/truncate.result +++ b/mysql-test/r/truncate.result @@ -31,4 +31,25 @@ SELECT * from t1; a 1 2 +delete from t1; +insert into t1 (a) values (NULL),(NULL); +SELECT * from t1; +a +3 +4 +drop table t1; +create temporary table t1 (a integer auto_increment primary key); +insert into t1 (a) values (NULL),(NULL); +truncate table t1; +insert into t1 (a) values (NULL),(NULL); +SELECT * from t1; +a +1 +2 +delete from t1; +insert into t1 (a) values (NULL),(NULL); +SELECT * from t1; +a +3 +4 drop table t1; diff --git a/mysql-test/r/type_blob.result b/mysql-test/r/type_blob.result index 580fc9a8d0b..95bba1d4ec7 100644 --- a/mysql-test/r/type_blob.result +++ b/mysql-test/r/type_blob.result @@ -593,9 +593,12 @@ create table t1 (id integer primary key auto_increment, txt text, unique index t insert into t1 (txt) values ('Chevy'), ('Chevy '), (NULL); select * from t1 where txt='Chevy' or txt is NULL; id txt +3 NULL 1 Chevy 2 Chevy -3 NULL +explain select * from t1 where txt='Chevy' or txt is NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range txt_index txt_index 23 NULL 2 Using where select * from t1 where txt='Chevy '; id txt 1 Chevy @@ -663,6 +666,21 @@ id txt 1 Chevy 2 Chevy 4 Ford +alter table t1 modify column txt blob; +explain select * from t1 where txt='Chevy' or txt is NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref_or_null txt_index txt_index 23 const 2 Using where +select * from t1 where txt='Chevy' or txt is NULL; +id txt +1 Chevy +3 NULL +explain select * from t1 where txt='Chevy' or txt is NULL order by txt; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref_or_null txt_index txt_index 23 const 2 Using where; Using filesort +select * from t1 where txt='Chevy' or txt is NULL order by txt; +id txt +3 NULL +1 Chevy drop table t1; CREATE TABLE t1 ( i int(11) NOT NULL default '0', c text NOT NULL, PRIMARY KEY (i), KEY (c(1),c(1))); INSERT t1 VALUES (1,''),(2,''),(3,'asdfh'),(4,''); diff --git a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result index e77ef9f975b..71d1b9ad381 100644 --- a/mysql-test/r/type_date.result +++ b/mysql-test/r/type_date.result @@ -79,3 +79,20 @@ SELECT DATE_FORMAT(f1, "%l.%i %p") , DATE_FORMAT(f2, "%l.%i %p") FROM t1; DATE_FORMAT(f1, "%l.%i %p") DATE_FORMAT(f2, "%l.%i %p") 9.00 AM 12.00 PM DROP TABLE t1; +CREATE TABLE t1 (f1 DATE); +CREATE TABLE t2 (f2 VARCHAR(8)); +CREATE TABLE t3 (f2 CHAR(8)); +INSERT INTO t1 VALUES ('1978-11-26'); +INSERT INTO t2 SELECT f1+0 FROM t1; +INSERT INTO t2 SELECT f1+0 FROM t1 UNION SELECT f1+0 FROM t1; +INSERT INTO t3 SELECT f1+0 FROM t1; +INSERT INTO t3 SELECT f1+0 FROM t1 UNION SELECT f1+0 FROM t1; +SELECT * FROM t2; +f2 +19781126 +19781126 +SELECT * FROM t3; +f2 +19781126 +19781126 +DROP TABLE t1, t2, t3; diff --git a/mysql-test/r/type_float.result b/mysql-test/r/type_float.result index 30de1e62df7..843bdc2bdc5 100644 --- a/mysql-test/r/type_float.result +++ b/mysql-test/r/type_float.result @@ -75,14 +75,17 @@ insert t1 values (121,"16"); select c1 + c1 * (c2 / 100) as col from t1; col 140.36 -create table t2 select c1 + c1 * (c2 / 100) as col from t1; +create table t2 select c1 + c1 * (c2 / 100) as col1, round(c1, 5) as col2, round(c1, 35) as col3, sqrt(c1*1e-15) col4 from t1; select * from t2; -col -140.36 +col1 col2 col3 col4 +140.36 121.00000 121 3.47850542618522e-07 show create table t2; Table Create Table t2 CREATE TABLE `t2` ( - `col` double default NULL + `col1` double default NULL, + `col2` double(22,5) default NULL, + `col3` double default NULL, + `col4` double default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1,t2; create table t1 (a float); @@ -132,6 +135,9 @@ t1 CREATE TABLE `t1` ( `b` double(200,30) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; +create table t1 (c20 char); +insert into t1 values (5000.0); +drop table t1; create table t1 (f float(54)); ERROR 42000: Incorrect column specifier for column 'f' drop table if exists t1; diff --git a/mysql-test/r/type_ranges.result b/mysql-test/r/type_ranges.result index e803fde14a6..5a65c90c5c7 100644 --- a/mysql-test/r/type_ranges.result +++ b/mysql-test/r/type_ranges.result @@ -272,7 +272,7 @@ auto bigint(17) unsigned NULL PRI 0 select,insert,update,references t1 bigint(1) NULL 0 select,insert,update,references t2 char(1) latin1_swedish_ci select,insert,update,references t3 longtext latin1_swedish_ci select,insert,update,references -t4 longtext latin1_bin select,insert,update,references +t4 longblob NULL select,insert,update,references select * from t2; auto t1 t2 t3 t4 11 1 a aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb diff --git a/mysql-test/r/type_timestamp.result b/mysql-test/r/type_timestamp.result index aa8c0903558..425e4a05586 100644 --- a/mysql-test/r/type_timestamp.result +++ b/mysql-test/r/type_timestamp.result @@ -365,3 +365,15 @@ select * from t1; t1 i 2004-04-01 00:00:00 10 drop table t1; +create table t1 (ts timestamp(19)); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +set TIMESTAMP=1000000000; +insert into t1 values (); +select * from t1; +ts +2001-09-09 04:46:40 +drop table t1; diff --git a/mysql-test/r/type_uint.result b/mysql-test/r/type_uint.result index 07eb47faa7c..d8edf9085b7 100644 --- a/mysql-test/r/type_uint.result +++ b/mysql-test/r/type_uint.result @@ -5,8 +5,12 @@ insert into t1 values (1); insert into t1 values (-1); Warnings: Warning 1264 Data truncated; out of range for column 'this' at row 1 +insert into t1 values ('5000000000'); +Warnings: +Warning 1265 Data truncated for column 'this' at row 1 select * from t1; this 1 0 +4294967295 drop table t1; diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 4a0c500c15c..99e0a69834e 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -436,6 +436,14 @@ select length(version()) > 1 as `*` UNION select 2; * 1 2 +create table t1 (a int); +insert into t1 values (0), (3), (1), (2); +explain (select * from t1) union (select * from t1) order by a; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 +2 UNION t1 ALL NULL NULL NULL NULL 4 +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL Using filesort +drop table t1; CREATE TABLE t1 ( id int(3) unsigned default '0') ENGINE=MyISAM; INSERT INTO t1 (id) VALUES("1"); CREATE TABLE t2 ( id int(3) unsigned default '0', id_master int(5) default '0', text1 varchar(5) default NULL, text2 varchar(5) default NULL) ENGINE=MyISAM; diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index 498e1634dc4..a84cf733f21 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -460,3 +460,19 @@ SELECT @@global.session.key_buffer_size; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key_buffer_size' at line 1 SELECT @@global.local.key_buffer_size; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key_buffer_size' at line 1 +set @tstlw = @@log_warnings; +show global variables like 'log_warnings'; +Variable_name Value +log_warnings 1 +set global log_warnings = 0; +show global variables like 'log_warnings'; +Variable_name Value +log_warnings 0 +set global log_warnings = 42; +show global variables like 'log_warnings'; +Variable_name Value +log_warnings 42 +set global log_warnings = @tstlw; +show global variables like 'log_warnings'; +Variable_name Value +log_warnings 1 |