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
|
#remove this include after fix MDEV-27871
--source include/no_view_protocol.inc
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));
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));
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'));
--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.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 #
|
