1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
|
# 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 ER_DUP_ENTRY
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;
# check that cast appends trailing zeros
select hex(cast(0x10 as binary(2)));
#
# Bug #14299: BINARY space truncation should cause warning or error
#
create table t1 (b binary(2), vb varbinary(2));
insert into t1 values(0x4120, 0x4120);
insert ignore into t1 values(0x412020, 0x412020);
drop table t1;
create table t1 (c char(2), vc varchar(2));
insert into t1 values(0x4120, 0x4120);
insert into t1 values(0x412020, 0x412020);
drop table t1;
set @old_sql_mode= @@sql_mode, sql_mode= 'traditional';
create table t1 (b binary(2), vb varbinary(2));
insert into t1 values(0x4120, 0x4120);
--error ER_DATA_TOO_LONG
insert into t1 values(0x412020, NULL);
--error ER_DATA_TOO_LONG
insert into t1 values(NULL, 0x412020);
drop table t1;
set @@sql_mode= @old_sql_mode;
#
# Bug#14171: Wrong default value for a BINARY field
#
create table t1(f1 int, f2 binary(2) not null, f3 char(2) not null);
insert ignore into t1 set f1=1;
select hex(f2), hex(f3) from t1;
drop table t1;
--echo End of 5.0 tests
--echo #
--echo # Start of 10.0 tests
--echo #
--echo #
--echo # MDEV-8472 BINARY, VARBINARY and BLOB return different warnings on CAST to DECIMAL
--echo #
SET NAMES utf8;
CREATE TABLE t1 (a BINARY(30));
INSERT INTO t1 VALUES ('1äÖüß@µ*$');
SELECT CAST(a AS DECIMAL) FROM t1;
DROP TABLE t1;
CREATE TABLE t1 (a VARBINARY(30));
INSERT INTO t1 VALUES ('1äÖüß@µ*$');
SELECT CAST(a AS DECIMAL) FROM t1;
DROP TABLE t1;
CREATE TABLE t1 (a BLOB);
INSERT INTO t1 VALUES ('1äÖüß@µ*$');
SELECT CAST(a AS DECIMAL) FROM t1;
DROP TABLE t1;
--echo #
--echo # End of 10.0 tests
--echo #
--echo #
--echo # Start of 10.5 tests
--echo #
--echo #
--echo # MDEV-20818 ER_CRASHED_ON_USAGE or Assertion `length <= column->length' failed in write_block_record on temporary table
--echo #
CREATE TABLE t1 (a VARCHAR(39));
#check after fix MDEV-31540
--disable_cursor_protocol
--disable_view_protocol
--enable_metadata
SELECT
CAST(a AS BINARY(0)),
CAST(a AS BINARY(1)),
CAST(a AS BINARY(16)),
CAST(a AS BINARY(255)),
CAST(a AS BINARY(256)),
CAST(a AS BINARY(512)),
CAST(a AS BINARY(513)),
CAST(a AS BINARY(65532)),
CAST(a AS BINARY(65533)),
CAST(a AS BINARY(65534)),
CAST(a AS BINARY(65535)),
CAST(a AS BINARY(65536)),
CAST(a AS BINARY(16777215)),
CAST(a AS BINARY(16777216))
FROM t1;
--disable_metadata
--enable_view_protocol
--enable_cursor_protocol
DROP TABLE t1;
--echo #
--echo # MDEV-32203 Raise notes when an index cannot be used on data type mismatch
--echo #
SET note_verbosity=unusable_keys;
CREATE TABLE t1 (indexed_col VARBINARY(32), KEY(indexed_col));
DELIMITER $$;
FOR i IN 1..31
DO
INSERT INTO t1 VALUES (20230100+i);
END FOR;
$$
DELIMITER ;$$
--source unusable_keys_literals.inc
--source unusable_keys_joins.inc
DROP TABLE t1;
SET note_verbosity=DEFAULT;
--echo #
--echo # MDEV-36235 Incorrect result for BETWEEN over unique blob prefix
--echo #
CREATE TABLE t1 (c1 BINARY(16), UNIQUE (c1));
INSERT INTO t1 (c1) VALUES (-2),(-1),(1),(2);
SELECT HEX(c1) FROM t1 WHERE 'a' BETWEEN 0 AND (c1);
SELECT HEX(c1) FROM t1 IGNORE KEY(c1) WHERE 'a' BETWEEN 0 AND (c1);
SELECT HEX(c1) FROM t1 WHERE '#' BETWEEN c1 AND 0;
SELECT HEX(c1) FROM t1 IGNORE KEY(c1) WHERE '#' BETWEEN c1 AND 0;
DROP TABLE t1;
|
