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
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
|
set names binary;
--source include/ctype_numconv.inc
--echo #
--echo # End of 5.5 tests
--echo #
SET NAMES binary;
--source include/ctype_like_cond_propagation.inc
--echo #
--echo # MDEV-7629 Regression: Bit and hex string literals changed column names in 10.0.14
--echo #
SELECT _binary 0x7E, _binary X'7E', _binary B'01111110';
SET NAMES utf8, character_set_connection=binary;
--source include/ctype_mdev13118.inc
--echo #
--echo # End of 10.0 tests
--echo #
--echo #
--echo # MDEV-8695 Wrong result for SELECT..WHERE varchar_column='a' AND CRC32(varchar_column)=3904355907
--echo #
CREATE TABLE t1 (a VARBINARY(10));
INSERT INTO t1 VALUES ('a'),('a ');
SELECT a, LENGTH(a), CRC32(a) FROM t1 WHERE CRC32(a)=3904355907;
SELECT a, LENGTH(a), CRC32(a) FROM t1 WHERE a='a' AND CRC32(a)=3904355907;
--echo # Okey to propagate 'a' into CRC32(a)
EXPLAIN EXTENDED SELECT a, LENGTH(a), CRC32(a) FROM t1 WHERE a='a' AND CRC32(a)=3904355907;
SELECT a, HEX(a) FROM t1 WHERE HEX(a)='61';
SELECT *,HEX(a) FROM t1 WHERE a='a' AND HEX(a)='61';
--echo # Okey to propagate 'a' into HEX(a)
EXPLAIN EXTENDED SELECT *,HEX(a) FROM t1 WHERE a='a' AND HEX(a)='61';
SELECT * FROM t1 WHERE a='a';
SELECT * FROM t1 WHERE LENGTH(a)=2;
SELECT * FROM t1 WHERE a='a' AND LENGTH(a)=2;
--echo # Okey to propagate 'a' into LENGTH(a)
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND LENGTH(a)=2;
SELECT * FROM t1 WHERE a='a ';
SELECT * FROM t1 WHERE a='a ' AND LENGTH(a)=2;
--echo # Okey to propagate 'a ' into LENGTH(a)
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a ' AND LENGTH(a)=2;
DROP TABLE t1;
--echo #
--echo # MDEV-8723 Wrong result for SELECT..WHERE COLLATION(a)='binary' AND a='a'
--echo #
CREATE TABLE t1 (a VARBINARY(10));
INSERT INTO t1 VALUES ('a'),('A');
SELECT * FROM t1 WHERE COLLATION(a)='binary' AND a='a';
SELECT * FROM t1 WHERE CHARSET(a)='binary' AND a='a';
SELECT * FROM t1 WHERE COERCIBILITY(a)=2 AND a='a';
SELECT * FROM t1 WHERE WEIGHT_STRING(a)='a' AND a='a';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE COLLATION(a)='binary' AND a='a';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE CHARSET(a)='binary' AND a='a';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE COERCIBILITY(a)=2 AND a='a';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE WEIGHT_STRING(a)='a' AND a='a';
DROP TABLE t1;
--echo #
--echo # MDEV-22111 ERROR 1064 & 1033 and SIGSEGV on CREATE TABLE w/ various charsets on 10.4/5 optimized builds | Assertion `(uint) (table_check_constraints - share->check_constraints) == (uint) (share->table_check_constraints - share->field_check_constraints)' failed
--echo #
CREATE TABLE t1(a ENUM(0x6100,0x6200,0x6300) CHARACTER SET 'Binary');
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES (1),(2),(3);
SELECT HEX(a) FROM t1 ORDER BY a;
DROP TABLE t1;
--echo 0x00 in the middle or in the end of a value
CREATE TABLE t1 (a ENUM(0x6100));
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES (1);
SELECT HEX(a) FROM t1;
DROP TABLE t1;
CREATE TABLE t1 (a ENUM(0x610062));
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES (1);
SELECT HEX(a) FROM t1;
DROP TABLE t1;
--echo 0x00 in the beginning of the first value:
CREATE TABLE t1 (a ENUM(0x0061));
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES(1);
SELECT HEX(a) FROM t1;
DROP TABLE t1;
CREATE TABLE t1 (a ENUM(0x0061), b ENUM('b'));
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES (1,1);
SELECT HEX(a), HEX(b) FROM t1;
DROP TABLE t1;
--echo # 0x00 in the beginning of the second (and following) value of the *last* ENUM/SET in the table:
CREATE TABLE t1 (a ENUM('a',0x0061)) CHARSET=latin1;
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES (1),(2);
SELECT HEX(a) FROM t1 ORDER BY a;
DROP TABLE t1;
CREATE TABLE t1 (a ENUM('a'), b ENUM('b',0x0061)) CHARSET=latin1;
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES (1,1);
INSERT INTO t1 VALUES (1,2);
SELECT HEX(a), HEX(b) FROM t1 ORDER BY a, b;
DROP TABLE t1;
--echo 0x00 in the beginning of a value of a non-last ENUM/SET causes an error:
--replace_regex /'.*t1.frm'/'DIR\/t1.frm'/
--error ER_NOT_FORM_FILE
CREATE TABLE t1 (a ENUM('a',0x0061), b ENUM('b')) CHARSET=latin1;
--echo #
--echo # End of 10.1 tests
--echo #
--echo #
--echo # MDEV-22111 ERROR 1064 & 1033 and SIGSEGV on CREATE TABLE w/ various charsets on 10.4/5 optimized builds | Assertion `(uint) (table_check_constraints - share->check_constraints) == (uint) (share->table_check_constraints - share->field_check_constraints)' failed
--echo # 10.2 tests
--echo #
SET NAMES latin1;
CREATE TABLE t1(c ENUM(0x0061) CHARACTER SET 'Binary', d JSON);
SHOW CREATE TABLE t1;
INSERT INTO t1 (c) VALUES (1);
SELECT HEX(c) FROM t1;
DROP TABLE t1;
CREATE TABLE t1(
c ENUM(0x0061) CHARACTER SET 'Binary',
d INT DEFAULT NULL CHECK (d>0)
);
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES (1,1);
SELECT HEX(c), d FROM t1;
DROP TABLE t1;
CREATE TABLE t1(c ENUM(0x0061) CHARACTER SET 'Binary' CHECK (c>0));
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES (1);
SELECT HEX(c) FROM t1;
DROP TABLE t1;
--echo #
--echo # End of 10.2 tests
--echo #
--echo #
--echo # Start of 10.3 tests
--echo #
--echo #
--echo # MDEV-29561 SHOW CREATE TABLE produces syntactically incorrect structure
--echo #
CREATE TABLE t1 (a ENUM('x') CHARACTER SET BINARY);
SHOW CREATE TABLE t1;
let $def= query_get_value(show create table t1,'Create Table',1);
DROP TABLE t1;
eval $def;
DROP TABLE t1;
CREATE TABLE t1 (a INT ) CHARSET=binary;
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE DATABASE db1 CHARACTER SET BINARY;
SHOW CREATE DATABASE db1;
DROP DATABASE db1;
CREATE FUNCTION f1() RETURNS ENUM('a') CHARACTER SET binary RETURN NULL;
SHOW CREATE FUNCTION f1;
DROP FUNCTION f1;
CREATE FUNCTION f1(a ENUM('a') CHARACTER SET binary) RETURNS INT RETURN NULL;
SHOW CREATE FUNCTION f1;
DROP FUNCTION f1;
--echo #
--echo # End of 10.3 tests
--echo #
--echo #
--echo # Start of 10.4 tests
--echo #
--echo #
--echo # MDEV-28384 UBSAN: null pointer passed as argument 1, which is declared to never be null in my_strnncoll_binary on SELECT ... COUNT or GROUP_CONCAT
--echo #
CREATE TABLE t (c BLOB NOT NULL);
INSERT IGNORE INTO t VALUES (0);
SELECT COUNT(*) FROM t WHERE EXTRACTVALUE(c,'a')='a';
DROP TABLE t;
SET sql_mode='';
CREATE TABLE t (c TEXT NOT NULL);
INSERT INTO t VALUES();
INSERT IGNORE INTO t VALUES (NULL);
SELECT GROUP_CONCAT(c ORDER BY BINARY c) FROM t GROUP BY c;
DROP TABLE t;
--echo #
--echo # MDEV-30982 UBSAN: runtime error: null pointer passed as argument 2, which is declared to never be null in my_strnncoll_binary on DELETE
--echo #
CREATE TABLE t (c1 SET('1','2','3'),c2 BINARY);
INSERT INTO t VALUES (0,0);
DELETE FROM t WHERE c2<c1;
DROP TABLE t;
--echo #
--echo # MDEV-28835 Assertion `(length % 4) == 0' failed in my_lengthsp_utf32 on INSERT
--echo #
SET NAMES latin1,character_set_connection=binary;
--echo # Binary format, binary result
SELECT DATE_FORMAT('2004-02-02','%W');
SELECT HEX(DATE_FORMAT('2004-02-02','%W'));
#Enable after fix MDEV-33936
--disable_view_protocol
#enable after fix MDEV-34215
--disable_cursor_protocol
SELECT DATE_FORMAT(TIME'-01:01:01','%h');
SELECT HEX(DATE_FORMAT(TIME'-01:01:01','%h'));
--enable_cursor_protocol
--enable_view_protocol
--echo # latin1 format, binary result
SELECT DATE_FORMAT('2004-02-02',_latin1'%W');
SELECT HEX(DATE_FORMAT('2004-02-02',_latin1'%W'));
#Enable after fix MDEV-33936
--disable_view_protocol
#enable after fix MDEV-34215
--disable_cursor_protocol
SELECT DATE_FORMAT(TIME'-01:01:01',_latin1'%h');
SELECT HEX(DATE_FORMAT(TIME'-01:01:01',_latin1'%h'));
--enable_cursor_protocol
--enable_view_protocol
--echo # Binary format, latin1 result
SET NAMES latin1;
SELECT DATE_FORMAT('2004-02-02',_binary'%W');
SELECT HEX(DATE_FORMAT('2004-02-02',_binary'%W'));
#Enable after fix MDEV-33936
--disable_view_protocol
#enable after fix MDEV-34215
--disable_cursor_protocol
SELECT DATE_FORMAT(TIME'-01:01:01',_binary'%h');
SELECT HEX(DATE_FORMAT(TIME'-01:01:01',_binary'%h'));
--enable_cursor_protocol
--enable_view_protocol
--echo #
--echo # End of 10.4 tests
--echo #
--echo #
--echo # Start of 10.5 tests
--echo #
--echo #
--echo # MDEV-8844 Unreadable control characters printed as is in warnings
--echo #
SET NAMES binary;
CREATE TABLE t1 (a VARCHAR(20) CHARACTER SET latin1, UNIQUE(a));
INSERT INTO t1 VALUES (0x61000162FF);
--error ER_DUP_ENTRY
INSERT INTO t1 VALUES (0x61000162FF);
INSERT IGNORE INTO t1 VALUES (0x61000162FF);
DROP TABLE t1;
CREATE TABLE t1 (a VARCHAR(20) CHARACTER SET utf8, UNIQUE(a));
INSERT INTO t1 VALUES (_latin1 0x61000162FF);
--error ER_DUP_ENTRY
INSERT INTO t1 VALUES (_latin1 0x61000162FF);
INSERT IGNORE INTO t1 VALUES (_latin1 0x61000162FF);
DROP TABLE t1;
--echo #
--echo # End of 10.5 tests
--echo #
--echo #
--echo # Start of 10.11 tests
--echo #
--echo #
--echo # MDEV-28767 Collation "binary" is not accepted for databases, tables, columns
--echo #
# Column level
CREATE TABLE t1 (a CHAR(1) COLLATE binary);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (a enum('a') CHARACTER SET binary COLLATE binary DEFAULT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (a enum('a') COLLATE binary DEFAULT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
--error ER_COLLATION_CHARSET_MISMATCH
CREATE TABLE t1 (a CHAR(1) CHARACTER SET latin1 COLLATE binary);
# Table level
CREATE TABLE t1 (a CHAR(10)) COLLATE binary;
SHOW CREATE TABLE t1;
DROP TABLE t1;
--error ER_COLLATION_CHARSET_MISMATCH
CREATE TABLE t1 (a CHAR(10)) CHARACTER SET latin1 COLLATE binary;
# Database level
CREATE DATABASE db1 COLLATE binary;
SHOW CREATE DATABASE db1;
DROP DATABASE db1;
--error ER_COLLATION_CHARSET_MISMATCH
CREATE DATABASE db1 CHARACTER SET latin1 COLLATE binary;
--echo #
--echo # End of 10.11 tests
--echo #
|
