2
create table t1 (s1 binary(3));
3
insert into t1 values (0x61), (0x6120), (0x612020);
4
select hex(s1) from t1;
7
# check that 0x00 is not stripped in val_str
8
create table t1 (s1 binary(2), s2 varbinary(2));
9
insert into t1 values (0x4100,0x4100);
10
select length(concat('*',s1,'*',s2,'*')) from t1;
12
insert into t1 values (0x4120,0x4120);
13
select length(concat('*',s1,'*',s2,'*')) from t1;
16
# check that trailing 0x00 and 0x20 do matter on comparison
17
create table t1 (s1 varbinary(20), s2 varbinary(20));
19
insert into t1 values (0x41,0x4100),(0x41,0x4120),(0x4100,0x4120);
20
select hex(s1), hex(s2) from t1;
21
select count(*) from t1 where s1 < s2;
24
# check that trailing 0x00 do matter on filesort
25
create table t1 (s1 varbinary(2), s2 varchar(1));
26
insert into t1 values (0x41,'a'), (0x4100,'b'), (0x41,'c'), (0x4100,'d');
27
select hex(s1),s2 from t1 order by s1,s2;
30
# check that 0x01 is padded to 0x0100 and thus we get a duplicate value
31
create table t1 (s1 binary(2) primary key);
32
insert into t1 values (0x01);
33
insert into t1 values (0x0120);
35
insert into t1 values (0x0100);
36
select hex(s1) from t1 order by s1;
38
select hex(s1) from t1 where s1=0x01;
39
select hex(s1) from t1 where s1=0x0120;
40
select hex(s1) from t1 where s1=0x0100;
41
select count(distinct s1) from t1;
42
alter table t1 drop primary key;
43
# check non-indexed search
44
select hex(s1) from t1 where s1=0x01;
45
select hex(s1) from t1 where s1=0x0120;
46
select hex(s1) from t1 where s1=0x0100;
47
select count(distinct s1) from t1;
50
# check that 0x01 is not padded, and all three values are unique
51
create table t1 (s1 varbinary(2) primary key);
52
insert into t1 values (0x01);
53
insert into t1 values (0x0120);
54
insert into t1 values (0x0100);
55
select hex(s1) from t1 order by s1;
57
select hex(s1) from t1 where s1=0x01;
58
select hex(s1) from t1 where s1=0x0120;
59
select hex(s1) from t1 where s1=0x0100;
60
select count(distinct s1) from t1;
61
alter table t1 drop primary key;
62
# check non-indexed search
63
select hex(s1) from t1 where s1=0x01;
64
select hex(s1) from t1 where s1=0x0120;
65
select hex(s1) from t1 where s1=0x0100;
66
select count(distinct s1) from t1;
69
# check that cast appends trailing zeros
70
select hex(cast(0x10 as binary(2)));
73
# Bug #14299: BINARY space truncation should cause warning or error
75
create table t1 (b binary(2), vb varbinary(2));
76
insert into t1 values(0x4120, 0x4120);
77
insert into t1 values(0x412020, 0x412020);
79
create table t1 (c char(2), vc varchar(2));
80
insert into t1 values(0x4120, 0x4120);
81
insert into t1 values(0x412020, 0x412020);
84
set @old_sql_mode= @@sql_mode, sql_mode= 'traditional';
85
create table t1 (b binary(2), vb varbinary(2));
86
insert into t1 values(0x4120, 0x4120);
87
--error ER_DATA_TOO_LONG
88
insert into t1 values(0x412020, NULL);
89
--error ER_DATA_TOO_LONG
90
insert into t1 values(NULL, 0x412020);
92
set @@sql_mode= @old_sql_mode;
95
# Bug#14171: Wrong default value for a BINARY field
97
create table t1(f1 int, f2 binary(2) not null, f3 char(2) not null);
98
insert into t1 set f1=1;
99
select hex(f2), hex(f3) from t1;
102
--echo End of 5.0 tests