diff options
Diffstat (limited to 'mysql-test')
25 files changed, 371 insertions, 191 deletions
diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index 6a710a8de10..644d92b477e 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -436,7 +436,7 @@ alter table t1 change a a char(10) character set cp1251; select a,hex(a) from t1; a hex(a) F2E5F1F2 -alter table t1 change a a binary(10); +alter table t1 change a a binary(4); select a,hex(a) from t1; a hex(a) F2E5F1F2 diff --git a/mysql-test/r/binary.result b/mysql-test/r/binary.result index 5b5f673b071..a8d6c3bf411 100644 --- a/mysql-test/r/binary.result +++ b/mysql-test/r/binary.result @@ -85,7 +85,7 @@ NULL select b from t1 having binary b like ''; b drop table t1; -create table t1 (a char(15) binary, b binary(15)); +create table t1 (a char(3) binary, b binary(3)); insert into t1 values ('aaa','bbb'),('AAA','BBB'); select upper(a),upper(b) from t1; upper(a) upper(b) diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result index fa8e810cf2b..9f79a15a181 100644 --- a/mysql-test/r/cast.result +++ b/mysql-test/r/cast.result @@ -231,7 +231,7 @@ t1 CREATE TABLE `t1` ( `c5` varchar(2) character set utf8 NOT NULL default '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; -create table t1 (a binary(10), b char(10) character set koi8r); +create table t1 (a binary(4), b char(4) character set koi8r); insert into t1 values (_binary'',_binary''); select a,b,cast(a as char character set cp1251),cast(b as binary) from t1; a b cast(a as char character set cp1251) cast(b as binary) diff --git a/mysql-test/r/ctype_cp1251.result b/mysql-test/r/ctype_cp1251.result index c65055e726d..47797af3cbe 100644 --- a/mysql-test/r/ctype_cp1251.result +++ b/mysql-test/r/ctype_cp1251.result @@ -23,7 +23,7 @@ a b c drop table t1; -create table t1 (a char(15) binary, b binary(15)) character set cp1251; +create table t1 (a char(3) binary, b binary(3)) character set cp1251; insert into t1 values ('aaa','bbb'),('AAA','BBB'); select upper(a),upper(b) from t1; upper(a) upper(b) diff --git a/mysql-test/r/ctype_many.result b/mysql-test/r/ctype_many.result index 87b83acfe0f..125a3fc4286 100644 --- a/mysql-test/r/ctype_many.result +++ b/mysql-test/r/ctype_many.result @@ -340,7 +340,7 @@ CYR CAPIT SOFT SIGN CYR CAPIT E CYR CAPIT YU CYR CAPIT YA -ALTER TABLE t1 ADD bin_f CHAR(32) BYTE NOT NULL default ''; +ALTER TABLE t1 ADD bin_f CHAR(1) BYTE NOT NULL default ''; UPDATE t1 SET bin_f=koi8_ru_f; SELECT COUNT(DISTINCT bin_f),COUNT(DISTINCT koi8_ru_f),COUNT(DISTINCT utf8_f) FROM t1; COUNT(DISTINCT bin_f) COUNT(DISTINCT koi8_ru_f) COUNT(DISTINCT utf8_f) @@ -1331,146 +1331,146 @@ UPDATE t1 SET greek_f=CONVERT(ucs2_f USING greek) WHERE comment LIKE _latin2'GRE UPDATE t1 SET armscii8_f=CONVERT(ucs2_f USING armscii8) WHERE comment LIKE _latin2'ARM%'; UPDATE t1 SET utf8_f=CONVERT(ucs2_f USING utf8) WHERE utf8_f=_utf8''; UPDATE t1 SET ucs2_f=CONVERT(utf8_f USING ucs2) WHERE ucs2_f=_ucs2''; -SELECT * FROM t1; -comment koi8_ru_f utf8_f bin_f ucs2_f armscii8_f greek_f -LAT SMALL A a a a a -LAT SMALL B b b b b -LAT SMALL C c c c c -LAT SMALL D d d d d -LAT SMALL E e e e e -LAT SMALL F f f f f -LAT SMALL G g g g g -LAT SMALL H h h h h -LAT SMALL I i i i i -LAT SMALL J j j j j -LAT SMALL K k k k k -LAT SMALL L l l l l -LAT SMALL M m m m m -LAT SMALL N n n n n -LAT SMALL O o o o o -LAT SMALL P p p p p -LAT SMALL Q q q q q -LAT SMALL R r r r r -LAT SMALL S s s s s -LAT SMALL T t t t t -LAT SMALL U u u u u -LAT SMALL V v v v v -LAT SMALL W w w w w -LAT SMALL X x x x x -LAT SMALL Y y y y y -LAT SMALL Z z z z z -LAT CAPIT A A A A A -LAT CAPIT B B B B B -LAT CAPIT C C C C C -LAT CAPIT D D D D D -LAT CAPIT E E E E E -LAT CAPIT F F F F F -LAT CAPIT G G G G G -LAT CAPIT H H H H H -LAT CAPIT I I I I I -LAT CAPIT J J J J J -LAT CAPIT K K K K K -LAT CAPIT L L L L L -LAT CAPIT M M M M M -LAT CAPIT N N N N N -LAT CAPIT O O O O O -LAT CAPIT P P P P P -LAT CAPIT Q Q Q Q Q -LAT CAPIT R R R R R -LAT CAPIT S S S S S -LAT CAPIT T T T T T -LAT CAPIT U U U U U -LAT CAPIT V V V V V -LAT CAPIT W W W W W -LAT CAPIT X X X X X -LAT CAPIT Y Y Y Y Y -LAT CAPIT Z Z Z Z Z -CYR SMALL A а а а -CYR SMALL BE б б б -CYR SMALL VE в в в -CYR SMALL GE г г г -CYR SMALL DE д д д -CYR SMALL IE е е е -CYR SMALL IO ё ё ё -CYR SMALL ZHE ж ж ж -CYR SMALL ZE з з з -CYR SMALL I и и и -CYR SMALL KA к к к -CYR SMALL EL л л л -CYR SMALL EM м м м -CYR SMALL EN н н н -CYR SMALL O о о о -CYR SMALL PE п п п -CYR SMALL ER р р р -CYR SMALL ES с с с -CYR SMALL TE т т т -CYR SMALL U у у у -CYR SMALL EF ф ф ф -CYR SMALL HA х х х -CYR SMALL TSE ц ц ц -CYR SMALL CHE ч ч ч -CYR SMALL SHA ш ш ш -CYR SMALL SCHA щ щ щ -CYR SMALL HARD SIGN ъ ъ ъ -CYR SMALL YERU ы ы ы -CYR SMALL SOFT SIGN ь ь ь -CYR SMALL E э э э -CYR SMALL YU ю ю ю -CYR SMALL YA я я я -CYR CAPIT A А А А -CYR CAPIT BE Б Б Б -CYR CAPIT VE В В В -CYR CAPIT GE Г Г Г -CYR CAPIT DE Д Д Д -CYR CAPIT IE Е Е Е -CYR CAPIT IO Ё Ё Ё -CYR CAPIT ZHE Ж Ж Ж -CYR CAPIT ZE З З З -CYR CAPIT I И И И -CYR CAPIT KA К К К -CYR CAPIT EL Л Л Л -CYR CAPIT EM М М М -CYR CAPIT EN Н Н Н -CYR CAPIT O О О О -CYR CAPIT PE П П П -CYR CAPIT ER Р Р Р -CYR CAPIT ES С С С -CYR CAPIT TE Т Т Т -CYR CAPIT U У У У -CYR CAPIT EF Ф Ф Ф -CYR CAPIT HA Х Х Х -CYR CAPIT TSE Ц Ц Ц -CYR CAPIT CHE Ч Ч Ч -CYR CAPIT SHA Ш Ш Ш -CYR CAPIT SCHA Щ Щ Щ -CYR CAPIT HARD SIGN Ъ Ъ Ъ -CYR CAPIT YERU Ы Ы Ы -CYR CAPIT SOFT SIGN Ь Ь Ь -CYR CAPIT E Э Э Э -CYR CAPIT YU Ю Ю Ю -CYR CAPIT YA Я Я Я -GREEK CAPIT ALPHA Α Α Α -GREEK CAPIT BETA Β Β Β -GREEK CAPIT GAMMA Γ Γ Γ -GREEK CAPIT DELTA Δ Δ Δ -GREEK CAPIT EPSILON Ε Ε Ε -GREEK SMALL ALPHA α α α -GREEK SMALL BETA β β β -GREEK SMALL GAMMA γ γ γ -GREEK SMALL DELTA δ δ δ -GREEK SMALL EPSILON ε ε ε -ARMENIAN CAPIT AYB Ա Ա Ա -ARMENIAN CAPIT BEN Բ Բ Բ -ARMENIAN CAPIT GIM Գ Գ Գ -ARMENIAN CAPIT DA Դ Դ Դ -ARMENIAN CAPIT ECH Ե Ե Ե -ARMENIAN CAPIT ZA Զ Զ Զ -ARMENIAN SMALL YAB ա ա ա -ARMENIAN SMALL BEN բ բ բ -ARMENIAN SMALL GIM գ գ գ -ARMENIAN SMALL DA դ դ դ -ARMENIAN SMALL ECH ե ե ե -ARMENIAN SMALL ZA զ զ զ +SELECT comment, koi8_ru_f, utf8_f, hex(bin_f), ucs2_f, armscii8_f, greek_f FROM t1; +comment koi8_ru_f utf8_f hex(bin_f) ucs2_f armscii8_f greek_f +LAT SMALL A a a 61 a +LAT SMALL B b b 62 b +LAT SMALL C c c 63 c +LAT SMALL D d d 64 d +LAT SMALL E e e 65 e +LAT SMALL F f f 66 f +LAT SMALL G g g 67 g +LAT SMALL H h h 68 h +LAT SMALL I i i 69 i +LAT SMALL J j j 6A j +LAT SMALL K k k 6B k +LAT SMALL L l l 6C l +LAT SMALL M m m 6D m +LAT SMALL N n n 6E n +LAT SMALL O o o 6F o +LAT SMALL P p p 70 p +LAT SMALL Q q q 71 q +LAT SMALL R r r 72 r +LAT SMALL S s s 73 s +LAT SMALL T t t 74 t +LAT SMALL U u u 75 u +LAT SMALL V v v 76 v +LAT SMALL W w w 77 w +LAT SMALL X x x 78 x +LAT SMALL Y y y 79 y +LAT SMALL Z z z 7A z +LAT CAPIT A A A 41 A +LAT CAPIT B B B 42 B +LAT CAPIT C C C 43 C +LAT CAPIT D D D 44 D +LAT CAPIT E E E 45 E +LAT CAPIT F F F 46 F +LAT CAPIT G G G 47 G +LAT CAPIT H H H 48 H +LAT CAPIT I I I 49 I +LAT CAPIT J J J 4A J +LAT CAPIT K K K 4B K +LAT CAPIT L L L 4C L +LAT CAPIT M M M 4D M +LAT CAPIT N N N 4E N +LAT CAPIT O O O 4F O +LAT CAPIT P P P 50 P +LAT CAPIT Q Q Q 51 Q +LAT CAPIT R R R 52 R +LAT CAPIT S S S 53 S +LAT CAPIT T T T 54 T +LAT CAPIT U U U 55 U +LAT CAPIT V V V 56 V +LAT CAPIT W W W 57 W +LAT CAPIT X X X 58 X +LAT CAPIT Y Y Y 59 Y +LAT CAPIT Z Z Z 5A Z +CYR SMALL A а а C1 а +CYR SMALL BE б б C2 б +CYR SMALL VE в в D7 в +CYR SMALL GE г г C7 г +CYR SMALL DE д д C4 д +CYR SMALL IE е е C5 е +CYR SMALL IO ё ё A3 ё +CYR SMALL ZHE ж ж D6 ж +CYR SMALL ZE з з DA з +CYR SMALL I и и C9 и +CYR SMALL KA к к CB к +CYR SMALL EL л л CC л +CYR SMALL EM м м CD м +CYR SMALL EN н н CE н +CYR SMALL O о о CF о +CYR SMALL PE п п D0 п +CYR SMALL ER р р D2 р +CYR SMALL ES с с D3 с +CYR SMALL TE т т D4 т +CYR SMALL U у у D5 у +CYR SMALL EF ф ф C6 ф +CYR SMALL HA х х C8 х +CYR SMALL TSE ц ц C3 ц +CYR SMALL CHE ч ч DE ч +CYR SMALL SHA ш ш DB ш +CYR SMALL SCHA щ щ DD щ +CYR SMALL HARD SIGN ъ ъ DF ъ +CYR SMALL YERU ы ы D9 ы +CYR SMALL SOFT SIGN ь ь D8 ь +CYR SMALL E э э DC э +CYR SMALL YU ю ю C0 ю +CYR SMALL YA я я D1 я +CYR CAPIT A А А E1 А +CYR CAPIT BE Б Б E2 Б +CYR CAPIT VE В В F7 В +CYR CAPIT GE Г Г E7 Г +CYR CAPIT DE Д Д E4 Д +CYR CAPIT IE Е Е E5 Е +CYR CAPIT IO Ё Ё B3 Ё +CYR CAPIT ZHE Ж Ж F6 Ж +CYR CAPIT ZE З З FA З +CYR CAPIT I И И E9 И +CYR CAPIT KA К К EB К +CYR CAPIT EL Л Л EC Л +CYR CAPIT EM М М ED М +CYR CAPIT EN Н Н EE Н +CYR CAPIT O О О EF О +CYR CAPIT PE П П F0 П +CYR CAPIT ER Р Р F2 Р +CYR CAPIT ES С С F3 С +CYR CAPIT TE Т Т F4 Т +CYR CAPIT U У У F5 У +CYR CAPIT EF Ф Ф E6 Ф +CYR CAPIT HA Х Х E8 Х +CYR CAPIT TSE Ц Ц E3 Ц +CYR CAPIT CHE Ч Ч FE Ч +CYR CAPIT SHA Ш Ш FB Ш +CYR CAPIT SCHA Щ Щ FD Щ +CYR CAPIT HARD SIGN Ъ Ъ FF Ъ +CYR CAPIT YERU Ы Ы F9 Ы +CYR CAPIT SOFT SIGN Ь Ь F8 Ь +CYR CAPIT E Э Э FC Э +CYR CAPIT YU Ю Ю E0 Ю +CYR CAPIT YA Я Я F1 Я +GREEK CAPIT ALPHA Α 00 Α Α +GREEK CAPIT BETA Β 00 Β Β +GREEK CAPIT GAMMA Γ 00 Γ Γ +GREEK CAPIT DELTA Δ 00 Δ Δ +GREEK CAPIT EPSILON Ε 00 Ε Ε +GREEK SMALL ALPHA α 00 α α +GREEK SMALL BETA β 00 β β +GREEK SMALL GAMMA γ 00 γ γ +GREEK SMALL DELTA δ 00 δ δ +GREEK SMALL EPSILON ε 00 ε ε +ARMENIAN CAPIT AYB Ա 00 Ա Ա +ARMENIAN CAPIT BEN Բ 00 Բ Բ +ARMENIAN CAPIT GIM Գ 00 Գ Գ +ARMENIAN CAPIT DA Դ 00 Դ Դ +ARMENIAN CAPIT ECH Ե 00 Ե Ե +ARMENIAN CAPIT ZA Զ 00 Զ Զ +ARMENIAN SMALL YAB ա 00 ա ա +ARMENIAN SMALL BEN բ 00 բ բ +ARMENIAN SMALL GIM գ 00 գ գ +ARMENIAN SMALL DA դ 00 դ դ +ARMENIAN SMALL ECH ե 00 ե ե +ARMENIAN SMALL ZA զ 00 զ զ SET CHARACTER SET 'binary'; SELECT * FROM t1; comment koi8_ru_f utf8_f bin_f ucs2_f armscii8_f greek_f @@ -1590,28 +1590,28 @@ CYR CAPIT SOFT SIGN Ь , CYR CAPIT E Э - CYR CAPIT YU Ю . CYR CAPIT YA Я / -GREEK CAPIT ALPHA Α -GREEK CAPIT BETA Β -GREEK CAPIT GAMMA Γ -GREEK CAPIT DELTA Δ -GREEK CAPIT EPSILON Ε -GREEK SMALL ALPHA α -GREEK SMALL BETA β -GREEK SMALL GAMMA γ -GREEK SMALL DELTA δ -GREEK SMALL EPSILON ε -ARMENIAN CAPIT AYB Ա 1 -ARMENIAN CAPIT BEN Բ 2 -ARMENIAN CAPIT GIM Գ 3 -ARMENIAN CAPIT DA Դ 4 -ARMENIAN CAPIT ECH Ե 5 -ARMENIAN CAPIT ZA Զ 6 -ARMENIAN SMALL YAB ա a -ARMENIAN SMALL BEN բ b -ARMENIAN SMALL GIM գ c -ARMENIAN SMALL DA դ d -ARMENIAN SMALL ECH ե e -ARMENIAN SMALL ZA զ f +GREEK CAPIT ALPHA Α +GREEK CAPIT BETA Β +GREEK CAPIT GAMMA Γ +GREEK CAPIT DELTA Δ +GREEK CAPIT EPSILON Ε +GREEK SMALL ALPHA α +GREEK SMALL BETA β +GREEK SMALL GAMMA γ +GREEK SMALL DELTA δ +GREEK SMALL EPSILON ε +ARMENIAN CAPIT AYB Ա +ARMENIAN CAPIT BEN Բ +ARMENIAN CAPIT GIM Գ +ARMENIAN CAPIT DA Դ +ARMENIAN CAPIT ECH Ե +ARMENIAN CAPIT ZA Զ +ARMENIAN SMALL YAB ա +ARMENIAN SMALL BEN բ +ARMENIAN SMALL GIM գ +ARMENIAN SMALL DA դ +ARMENIAN SMALL ECH ե +ARMENIAN SMALL ZA զ SELECT min(comment),count(*) FROM t1 GROUP BY ucs2_f; min(comment) count(*) LAT CAPIT A 2 diff --git a/mysql-test/r/federated.result b/mysql-test/r/federated.result index e0e0bba3271..b5a1016987e 100644 --- a/mysql-test/r/federated.result +++ b/mysql-test/r/federated.result @@ -504,7 +504,7 @@ DROP TABLE IF EXISTS federated.t1; CREATE TABLE federated.t1 ( `id` int NOT NULL auto_increment, `name` char(32) NOT NULL DEFAULT '', -`bincol` binary(4) NOT NULL, +`bincol` binary(1) NOT NULL, `floatval` decimal(5,2) NOT NULL DEFAULT 0.0, `other` int NOT NULL DEFAULT 0, PRIMARY KEY (id), @@ -515,7 +515,7 @@ DROP TABLE IF EXISTS federated.t1; CREATE TABLE federated.t1 ( `id` int NOT NULL auto_increment, `name` char(32) NOT NULL DEFAULT '', -`bincol` binary(4) NOT NULL, +`bincol` binary(1) NOT NULL, `floatval` decimal(5,2) NOT NULL DEFAULT 0.0, `other` int NOT NULL DEFAULT 0, PRIMARY KEY (id), diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index 8562937f1ac..60022ae0d8f 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -187,7 +187,7 @@ select 1 in ('1.1',2); select 1 in ('1.1',2.0); 1 in ('1.1',2.0) 0 -create table t1 (a char(20) character set binary); +create table t1 (a char(2) character set binary); insert into t1 values ('aa'), ('bb'); select * from t1 where a in (NULL, 'aa'); a diff --git a/mysql-test/r/ndb_condition_pushdown.result b/mysql-test/r/ndb_condition_pushdown.result index 1c3da1b486f..3e46a487c07 100644 --- a/mysql-test/r/ndb_condition_pushdown.result +++ b/mysql-test/r/ndb_condition_pushdown.result @@ -3,7 +3,7 @@ CREATE TABLE t1 ( auto int(5) unsigned NOT NULL auto_increment, string char(10), vstring varchar(10), -bin binary(7), +bin binary(2), vbin varbinary(7), tiny tinyint(4) DEFAULT '0' NOT NULL , short smallint(6) DEFAULT '1' NOT NULL , diff --git a/mysql-test/r/ndb_types.result b/mysql-test/r/ndb_types.result index 37ce7732f65..6938277f01d 100644 --- a/mysql-test/r/ndb_types.result +++ b/mysql-test/r/ndb_types.result @@ -3,7 +3,7 @@ CREATE TABLE t1 ( auto int(5) unsigned NOT NULL auto_increment, string char(10) default "hello", vstring varchar(10) default "hello", -bin binary(7), +bin binary(2), vbin varbinary(7), tiny tinyint(4) DEFAULT '0' NOT NULL , short smallint(6) DEFAULT '1' NOT NULL , diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 1b8cde6d3db..e3e379ac65d 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -1916,7 +1916,7 @@ select bug3788()| bug3788() 2005-03-04 drop function bug3788| -create function bug3788() returns binary(5) return 5| +create function bug3788() returns binary(1) return 5| select bug3788()| bug3788() 5 diff --git a/mysql-test/r/system_mysql_db.result b/mysql-test/r/system_mysql_db.result index f68c4805d72..999f12a0573 100644 --- a/mysql-test/r/system_mysql_db.result +++ b/mysql-test/r/system_mysql_db.result @@ -73,7 +73,7 @@ Table Create Table user CREATE TABLE `user` ( `Host` char(60) collate utf8_bin NOT NULL default '', `User` char(16) collate utf8_bin NOT NULL default '', - `Password` binary(41) NOT NULL default '', + `Password` char(41) character set latin1 collate latin1_bin NOT NULL default '', `Select_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Insert_priv` enum('N','Y') character set utf8 NOT NULL default 'N', `Update_priv` enum('N','Y') character set utf8 NOT NULL default 'N', diff --git a/mysql-test/r/type_binary.result b/mysql-test/r/type_binary.result new file mode 100644 index 00000000000..49fd7ba5633 --- /dev/null +++ b/mysql-test/r/type_binary.result @@ -0,0 +1,113 @@ +create table t1 (s1 binary(3)); +insert into t1 values (0x61), (0x6120), (0x612020); +select hex(s1) from t1; +hex(s1) +610000 +612000 +612020 +drop table t1; +create table t1 (s1 binary(2), s2 varbinary(2)); +insert into t1 values (0x4100,0x4100); +select length(concat('*',s1,'*',s2,'*')) from t1; +length(concat('*',s1,'*',s2,'*')) +7 +delete from t1; +insert into t1 values (0x4120,0x4120); +select length(concat('*',s1,'*',s2,'*')) from t1; +length(concat('*',s1,'*',s2,'*')) +7 +drop table t1; +create table t1 (s1 varbinary(20), s2 varbinary(20)); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `s1` varbinary(20) default NULL, + `s2` varbinary(20) default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +insert into t1 values (0x41,0x4100),(0x41,0x4120),(0x4100,0x4120); +select hex(s1), hex(s2) from t1; +hex(s1) hex(s2) +41 4100 +41 4120 +4100 4120 +select count(*) from t1 where s1 < s2; +count(*) +3 +drop table t1; +create table t1 (s1 varbinary(2), s2 varchar(1)); +insert into t1 values (0x41,'a'), (0x4100,'b'), (0x41,'c'), (0x4100,'d'); +select hex(s1),s2 from t1 order by s1,s2; +hex(s1) s2 +41 a +41 c +4100 b +4100 d +drop table t1; +create table t1 (s1 binary(2) primary key); +insert into t1 values (0x01); +insert into t1 values (0x0120); +insert into t1 values (0x0100); +ERROR 23000: Duplicate entry '' for key 1 +select hex(s1) from t1 order by s1; +hex(s1) +0100 +0120 +select hex(s1) from t1 where s1=0x01; +hex(s1) +select hex(s1) from t1 where s1=0x0120; +hex(s1) +0120 +select hex(s1) from t1 where s1=0x0100; +hex(s1) +0100 +select count(distinct s1) from t1; +count(distinct s1) +2 +alter table t1 drop primary key; +select hex(s1) from t1 where s1=0x01; +hex(s1) +select hex(s1) from t1 where s1=0x0120; +hex(s1) +0120 +select hex(s1) from t1 where s1=0x0100; +hex(s1) +0100 +select count(distinct s1) from t1; +count(distinct s1) +2 +drop table t1; +create table t1 (s1 varbinary(2) primary key); +insert into t1 values (0x01); +insert into t1 values (0x0120); +insert into t1 values (0x0100); +select hex(s1) from t1 order by s1; +hex(s1) +01 +0100 +0120 +select hex(s1) from t1 where s1=0x01; +hex(s1) +01 +select hex(s1) from t1 where s1=0x0120; +hex(s1) +0120 +select hex(s1) from t1 where s1=0x0100; +hex(s1) +0100 +select count(distinct s1) from t1; +count(distinct s1) +3 +alter table t1 drop primary key; +select hex(s1) from t1 where s1=0x01; +hex(s1) +01 +select hex(s1) from t1 where s1=0x0120; +hex(s1) +0120 +select hex(s1) from t1 where s1=0x0100; +hex(s1) +0100 +select count(distinct s1) from t1; +count(distinct s1) +3 +drop table t1; diff --git a/mysql-test/r/type_blob.result b/mysql-test/r/type_blob.result index 193ed298339..fd478c916c9 100644 --- a/mysql-test/r/type_blob.result +++ b/mysql-test/r/type_blob.result @@ -67,7 +67,7 @@ select * from t1; a Where drop table t1; -create table t1 (t text,c char(10),b blob, d binary(10)); +create table t1 (t text,c char(10),b blob, d varbinary(10)); insert into t1 values (NULL,NULL,NULL,NULL); insert into t1 values ("","","",""); insert into t1 values ("hello","hello","hello","hello"); @@ -83,14 +83,14 @@ Field Type Collation Null Key Default Extra Privileges Comment t text latin1_swedish_ci YES NULL # c char(10) latin1_swedish_ci YES NULL # b blob NULL YES NULL # -d binary(10) NULL YES NULL # +d varbinary(10) NULL YES NULL # lock tables t1 WRITE; show full fields from t1; Field Type Collation Null Key Default Extra Privileges Comment t text latin1_swedish_ci YES NULL # c char(10) latin1_swedish_ci YES NULL # b blob NULL YES NULL # -d binary(10) NULL YES NULL # +d varbinary(10) NULL YES NULL # unlock tables; select t from t1 where t like "hello"; t diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index 5695822be6b..bc3a4b0261d 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -278,7 +278,7 @@ insert into t1 values (''); select a,hex(a) from t1; alter table t1 change a a char(10) character set cp1251; select a,hex(a) from t1; -alter table t1 change a a binary(10); +alter table t1 change a a binary(4); select a,hex(a) from t1; alter table t1 change a a char(10) character set cp1251; select a,hex(a) from t1; diff --git a/mysql-test/t/binary.test b/mysql-test/t/binary.test index 02773b32302..1ac0cfebb28 100644 --- a/mysql-test/t/binary.test +++ b/mysql-test/t/binary.test @@ -56,7 +56,7 @@ drop table t1; # # Test of binary and upper/lower # -create table t1 (a char(15) binary, b binary(15)); +create table t1 (a char(3) binary, b binary(3)); insert into t1 values ('aaa','bbb'),('AAA','BBB'); select upper(a),upper(b) from t1; select lower(a),lower(b) from t1; diff --git a/mysql-test/t/cast.test b/mysql-test/t/cast.test index 6220b4cbae7..ca16791d9af 100644 --- a/mysql-test/t/cast.test +++ b/mysql-test/t/cast.test @@ -101,7 +101,7 @@ drop table t1; # Bug 2202 # CAST from BINARY to non-BINARY and from non-BINARY to BINARY # -create table t1 (a binary(10), b char(10) character set koi8r); +create table t1 (a binary(4), b char(4) character set koi8r); insert into t1 values (_binary'',_binary''); select a,b,cast(a as char character set cp1251),cast(b as binary) from t1; set names koi8r; diff --git a/mysql-test/t/ctype_cp1251.test b/mysql-test/t/ctype_cp1251.test index 1aafe7b7266..463a9cea4bc 100644 --- a/mysql-test/t/ctype_cp1251.test +++ b/mysql-test/t/ctype_cp1251.test @@ -21,7 +21,7 @@ drop table t1; # # Test of binary and upper/lower # -create table t1 (a char(15) binary, b binary(15)) character set cp1251; +create table t1 (a char(3) binary, b binary(3)) character set cp1251; insert into t1 values ('aaa','bbb'),('AAA','BBB'); select upper(a),upper(b) from t1; select lower(a),lower(b) from t1; diff --git a/mysql-test/t/ctype_many.test b/mysql-test/t/ctype_many.test index 22e844c6868..0903c3dd7fa 100644 --- a/mysql-test/t/ctype_many.test +++ b/mysql-test/t/ctype_many.test @@ -148,7 +148,7 @@ SET CHARACTER SET koi8r; SELECT * FROM t1; -ALTER TABLE t1 ADD bin_f CHAR(32) BYTE NOT NULL default ''; +ALTER TABLE t1 ADD bin_f CHAR(1) BYTE NOT NULL default ''; UPDATE t1 SET bin_f=koi8_ru_f; SELECT COUNT(DISTINCT bin_f),COUNT(DISTINCT koi8_ru_f),COUNT(DISTINCT utf8_f) FROM t1; @@ -204,7 +204,7 @@ UPDATE t1 SET greek_f=CONVERT(ucs2_f USING greek) WHERE comment LIKE _latin2'GRE UPDATE t1 SET armscii8_f=CONVERT(ucs2_f USING armscii8) WHERE comment LIKE _latin2'ARM%'; UPDATE t1 SET utf8_f=CONVERT(ucs2_f USING utf8) WHERE utf8_f=_utf8''; UPDATE t1 SET ucs2_f=CONVERT(utf8_f USING ucs2) WHERE ucs2_f=_ucs2''; -SELECT * FROM t1; +SELECT comment, koi8_ru_f, utf8_f, hex(bin_f), ucs2_f, armscii8_f, greek_f FROM t1; SET CHARACTER SET 'binary'; SELECT * FROM t1; SELECT min(comment),count(*) FROM t1 GROUP BY ucs2_f; diff --git a/mysql-test/t/federated.test b/mysql-test/t/federated.test index c401468a940..43679c54caf 100644 --- a/mysql-test/t/federated.test +++ b/mysql-test/t/federated.test @@ -348,7 +348,7 @@ DROP TABLE IF EXISTS federated.t1; CREATE TABLE federated.t1 ( `id` int NOT NULL auto_increment, `name` char(32) NOT NULL DEFAULT '', - `bincol` binary(4) NOT NULL, + `bincol` binary(1) NOT NULL, `floatval` decimal(5,2) NOT NULL DEFAULT 0.0, `other` int NOT NULL DEFAULT 0, PRIMARY KEY (id), @@ -363,7 +363,7 @@ DROP TABLE IF EXISTS federated.t1; eval CREATE TABLE federated.t1 ( `id` int NOT NULL auto_increment, `name` char(32) NOT NULL DEFAULT '', - `bincol` binary(4) NOT NULL, + `bincol` binary(1) NOT NULL, `floatval` decimal(5,2) NOT NULL DEFAULT 0.0, `other` int NOT NULL DEFAULT 0, PRIMARY KEY (id), diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test index dd4edd8ca48..0472968f918 100644 --- a/mysql-test/t/func_in.test +++ b/mysql-test/t/func_in.test @@ -97,7 +97,7 @@ select 1 in ('1.1',2.0); # Test case for bug #6365 -create table t1 (a char(20) character set binary); +create table t1 (a char(2) character set binary); insert into t1 values ('aa'), ('bb'); select * from t1 where a in (NULL, 'aa'); drop table t1; diff --git a/mysql-test/t/ndb_condition_pushdown.test b/mysql-test/t/ndb_condition_pushdown.test index d090d12e81b..9f512430085 100644 --- a/mysql-test/t/ndb_condition_pushdown.test +++ b/mysql-test/t/ndb_condition_pushdown.test @@ -12,7 +12,7 @@ CREATE TABLE t1 ( auto int(5) unsigned NOT NULL auto_increment, string char(10), vstring varchar(10), - bin binary(7), + bin binary(2), vbin varbinary(7), tiny tinyint(4) DEFAULT '0' NOT NULL , short smallint(6) DEFAULT '1' NOT NULL , diff --git a/mysql-test/t/ndb_types.test b/mysql-test/t/ndb_types.test index 3446a409b2a..10b8eb87e2c 100644 --- a/mysql-test/t/ndb_types.test +++ b/mysql-test/t/ndb_types.test @@ -12,7 +12,7 @@ CREATE TABLE t1 ( auto int(5) unsigned NOT NULL auto_increment, string char(10) default "hello", vstring varchar(10) default "hello", - bin binary(7), + bin binary(2), vbin varbinary(7), tiny tinyint(4) DEFAULT '0' NOT NULL , short smallint(6) DEFAULT '1' NOT NULL , diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index e16e7456056..1f4d5b6bad5 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -2431,7 +2431,7 @@ create function bug3788() returns date return cast("2005-03-04" as date)| select bug3788()| drop function bug3788| -create function bug3788() returns binary(5) return 5| +create function bug3788() returns binary(1) return 5| select bug3788()| drop function bug3788| diff --git a/mysql-test/t/type_binary.test b/mysql-test/t/type_binary.test new file mode 100644 index 00000000000..b5928cb14c4 --- /dev/null +++ b/mysql-test/t/type_binary.test @@ -0,0 +1,67 @@ +# check 0x00 padding +create table t1 (s1 binary(3)); +insert into t1 values (0x61), (0x6120), (0x612020); +select hex(s1) from t1; +drop table t1; + +# check that 0x00 is not stripped in val_str +create table t1 (s1 binary(2), s2 varbinary(2)); +insert into t1 values (0x4100,0x4100); +select length(concat('*',s1,'*',s2,'*')) from t1; +delete from t1; +insert into t1 values (0x4120,0x4120); +select length(concat('*',s1,'*',s2,'*')) from t1; +drop table t1; + +# check that trailing 0x00 and 0x20 do matter on comparison +create table t1 (s1 varbinary(20), s2 varbinary(20)); +show create table t1; +insert into t1 values (0x41,0x4100),(0x41,0x4120),(0x4100,0x4120); +select hex(s1), hex(s2) from t1; +select count(*) from t1 where s1 < s2; +drop table t1; + +# check that trailing 0x00 do matter on filesort +create table t1 (s1 varbinary(2), s2 varchar(1)); +insert into t1 values (0x41,'a'), (0x4100,'b'), (0x41,'c'), (0x4100,'d'); +select hex(s1),s2 from t1 order by s1,s2; +drop table t1; + +# check that 0x01 is padded to 0x0100 and thus we get a duplicate value +create table t1 (s1 binary(2) primary key); +insert into t1 values (0x01); +insert into t1 values (0x0120); +--error 1062 +insert into t1 values (0x0100); +select hex(s1) from t1 order by s1; +# check index search +select hex(s1) from t1 where s1=0x01; +select hex(s1) from t1 where s1=0x0120; +select hex(s1) from t1 where s1=0x0100; +select count(distinct s1) from t1; +alter table t1 drop primary key; +# check non-indexed search +select hex(s1) from t1 where s1=0x01; +select hex(s1) from t1 where s1=0x0120; +select hex(s1) from t1 where s1=0x0100; +select count(distinct s1) from t1; +drop table t1; + +# check that 0x01 is not padded, and all three values are unique +create table t1 (s1 varbinary(2) primary key); +insert into t1 values (0x01); +insert into t1 values (0x0120); +insert into t1 values (0x0100); +select hex(s1) from t1 order by s1; +# check index search +select hex(s1) from t1 where s1=0x01; +select hex(s1) from t1 where s1=0x0120; +select hex(s1) from t1 where s1=0x0100; +select count(distinct s1) from t1; +alter table t1 drop primary key; +# check non-indexed search +select hex(s1) from t1 where s1=0x01; +select hex(s1) from t1 where s1=0x0120; +select hex(s1) from t1 where s1=0x0100; +select count(distinct s1) from t1; +drop table t1; diff --git a/mysql-test/t/type_blob.test b/mysql-test/t/type_blob.test index 1ec5c600296..4f3c02b0465 100644 --- a/mysql-test/t/type_blob.test +++ b/mysql-test/t/type_blob.test @@ -67,9 +67,9 @@ select * from t1; drop table t1; # -# test of blob, text, char and char binary +# test of blob, text, char and varbinary # -create table t1 (t text,c char(10),b blob, d binary(10)); +create table t1 (t text,c char(10),b blob, d varbinary(10)); insert into t1 values (NULL,NULL,NULL,NULL); insert into t1 values ("","","",""); insert into t1 values ("hello","hello","hello","hello"); |