Debian Codegraph @

mysql-test/suite/vcol/t/vcol_sql_mode.test

raw
1 --echo #
2 --echo # Start of 10.2 tests
3 --echo #
4
5 --echo #
6 --echo # MDEV-18156 Assertion `0' failed or `btr_validate_index(index, 0, false)' in row_upd_sec_index_entry or error code 126: Index is corrupted upon DELETE with PAD_CHAR_TO_FULL_LENGTH
7 --echo #
8
9 --echo #
10 --echo # PAD_CHAR_TO_FULL_LENGTH + various virtual column data types
11 --echo #
12
13 CREATE TABLE t1 (a CHAR(5), v CHAR(5) AS (a) VIRTUAL, KEY(v));
14 SHOW CREATE TABLE t1;
15 DROP TABLE t1;
16
17 CREATE TABLE t1 (a CHAR(5), v INT AS (a) VIRTUAL, KEY(v));
18 SHOW CREATE TABLE t1;
19 DROP TABLE t1;
20
21 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
22 CREATE TABLE t1 (a CHAR(5), v TIME AS (a) VIRTUAL, KEY(v));
23
24 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
25 CREATE TABLE t1 (c CHAR(8), v BINARY(8) AS (c), KEY(v));
26 SHOW WARNINGS;
27
28 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
29 CREATE TABLE t1 (a CHAR(5), v BIT(64) AS (a) VIRTUAL, KEY(v));
30 SHOW WARNINGS;
31
32 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
33 CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (a) VIRTUAL, KEY(v));
34 SHOW WARNINGS;
35
36 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
37 CREATE TABLE t1 (a CHAR(5), v TEXT AS (a) VIRTUAL, KEY(v(100)));
38 SHOW WARNINGS;
39
40
41 --echo # PAD_CHAR_TO_FULL_LENGTH + TRIM resolving dependency
42
43 CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RTRIM(a)) VIRTUAL, KEY(v));
44 SHOW CREATE TABLE t1;
45 DROP TABLE t1;
46
47 CREATE TABLE t1 (a CHAR(5), v TEXT AS (RTRIM(a)) VIRTUAL, KEY(v(100)));
48 SHOW CREATE TABLE t1;
49 DROP TABLE t1;
50
51 CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(TRAILING ' ' FROM a)) VIRTUAL, KEY(v));
52 SHOW CREATE TABLE t1;
53 DROP TABLE t1;
54
55 CREATE TABLE t1 (a CHAR(5), v TEXT AS (TRIM(TRAILING ' ' FROM a)) VIRTUAL, KEY(v(100)));
56 SHOW CREATE TABLE t1;
57 DROP TABLE t1;
58
59 CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(BOTH ' ' FROM a)) VIRTUAL, KEY(v));
60 SHOW CREATE TABLE t1;
61 DROP TABLE t1;
62
63 CREATE TABLE t1 (a CHAR(5), v TEXT AS (TRIM(BOTH ' ' FROM a)) VIRTUAL, KEY(v(100)));
64 SHOW CREATE TABLE t1;
65 DROP TABLE t1;
66
67 CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(TRAILING NULL FROM a)) VIRTUAL, KEY(v));
68 SHOW CREATE TABLE t1;
69 DROP TABLE t1;
70
71 CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(BOTH NULL FROM a)) VIRTUAL, KEY(v));
72 SHOW CREATE TABLE t1;
73 DROP TABLE t1;
74
75
76 --echo # PAD_CHAR_TO_FULL_LENGTH + TRIM not resolving dependency
77
78 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
79 CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(LEADING ' ' FROM a)) VIRTUAL, KEY(v));
80 SHOW WARNINGS;
81
82 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
83 CREATE TABLE t1 (a CHAR(5), v TEXT AS (TRIM(LEADING ' ' FROM a)) VIRTUAL, KEY(v(100)));
84 SHOW WARNINGS;
85
86 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
87 CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(TRAILING '' FROM a)) VIRTUAL, KEY(v));
88 SHOW WARNINGS;
89
90 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
91 CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(BOTH '' FROM a)) VIRTUAL, KEY(v));
92 SHOW WARNINGS;
93
94 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
95 CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(TRAILING 'x' FROM a)) VIRTUAL, KEY(v));
96 SHOW WARNINGS;
97
98 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
99 CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(BOTH 'x' FROM a)) VIRTUAL, KEY(v));
100 SHOW WARNINGS;
101
102 # more than one space
103 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
104 CREATE TABLE t1 (
105 a CHAR(5),
106 v VARCHAR(5) AS (TRIM(TRAILING ' ' FROM a)) VIRTUAL, KEY(v));
107 SHOW WARNINGS;
108
109 # more than one space
110 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
111 CREATE TABLE t1 (
112 a CHAR(5),
113 v VARCHAR(5) AS (TRIM(BOTH ' ' FROM a)) VIRTUAL, KEY(v));
114 SHOW WARNINGS;
115
116
117 --echo # PAD_CHAR_TO_FULL_LENGTH + TRIM(... non_constant FROM a)
118 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
119 CREATE TABLE t1 (
120 a CHAR(5),
121 b CHAR(5),
122 v TEXT AS (TRIM(TRAILING b FROM a)) VIRTUAL, KEY(v(100)));
123 SHOW WARNINGS;
124
125
126 --echo # PAD_CHAR_TO_FULL_LENGTH + RPAD resolving dependency
127
128 CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RPAD(a,5,' ')) VIRTUAL, KEY(v));
129 SHOW CREATE TABLE t1;
130 DROP TABLE t1;
131
132 CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RPAD(a,6,' ')) VIRTUAL, KEY(v));
133 SHOW CREATE TABLE t1;
134 DROP TABLE t1;
135
136 CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RPAD(a,6,NULL)) VIRTUAL, KEY(v));
137 SHOW CREATE TABLE t1;
138 DROP TABLE t1;
139
140 CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RPAD(a,NULL,' ')) VIRTUAL, KEY(v));
141 SHOW CREATE TABLE t1;
142 DROP TABLE t1;
143
144
145 --echo # PAD_CHAR_TO_FULL_LENGTH + RPAD not resolving dependency
146
147 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
148 CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RPAD(a,4,' ')) VIRTUAL, KEY(v));
149 SHOW WARNINGS;
150
151 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
152 CREATE TABLE t1 (
153 a CHAR(5),
154 b CHAR(5),
155 v VARCHAR(5) AS (RPAD(a,NULL,b)) VIRTUAL,
156 KEY(v)
157 );
158 SHOW WARNINGS;
159
160
161 --echo # PAD_CHAR_TO_FULL_LENGTH + comparison
162
163 CREATE TABLE t1 (a CHAR(5), v INT AS (a='a') VIRTUAL, KEY(v));
164 SHOW CREATE TABLE t1;
165 DROP TABLE t1;
166
167 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
168 CREATE TABLE t1 (
169 a CHAR(5) CHARACTER SET latin1 COLLATE latin1_nopad_bin,
170 v INT AS (a='a') VIRTUAL, KEY(v)
171 );
172 SHOW WARNINGS;
173
174
175 --echo # PAD_CHAR_TO_FULL_LENGTH + LIKE
176
177 CREATE TABLE t1 (a CHAR(5), v INT AS (a LIKE 'a%') VIRTUAL, KEY(v));
178 SHOW CREATE TABLE t1;
179 DROP TABLE t1;
180
181 CREATE TABLE t1 (a CHAR(5), v INT AS (a LIKE NULL) VIRTUAL, KEY(v));
182 SHOW CREATE TABLE t1;
183 DROP TABLE t1;
184
185 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
186 CREATE TABLE t1 (a CHAR(5), v INT AS (a LIKE 'a') VIRTUAL, KEY(v));
187 SHOW WARNINGS;
188
189
190 --echo # PAD_CHAR_TO_FULL_LENGTH + LENGTH(char_column) = hard dependency
191
192 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
193 CREATE TABLE t1 (a CHAR(5), v INT AS (LENGTH(a)) VIRTUAL, KEY(v));
194 SHOW WARNINGS;
195
196
197 --echo #
198 --echo # Testing NO_UNSIGNED_SUBTRACTION
199 --echo #
200
201 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
202 CREATE TABLE t1 (
203 a INT UNSIGNED,
204 b INT UNSIGNED,
205 c INT GENERATED ALWAYS AS (a-b) VIRTUAL,
206 KEY (c)
207 );
208 SHOW WARNINGS;
209
210 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
211 CREATE TABLE t1 (
212 a INT UNSIGNED,
213 b INT UNSIGNED,
214 c INT GENERATED ALWAYS AS (CAST(a AS SIGNED)-b) VIRTUAL,
215 KEY (c)
216 );
217 SHOW WARNINGS;
218
219 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
220 CREATE TABLE t1 (
221 a INT UNSIGNED,
222 b INT UNSIGNED,
223 c INT GENERATED ALWAYS AS (a-CAST(b AS SIGNED)) VIRTUAL,
224 KEY (c)
225 );
226 SHOW WARNINGS;
227
228 CREATE TABLE t1 (
229 a INT UNSIGNED,
230 b INT UNSIGNED,
231 c INT GENERATED ALWAYS AS (CAST(a AS SIGNED)-CAST(b AS SIGNED)) VIRTUAL,
232 KEY (c)
233 );
234 SHOW CREATE TABLE t1;
235 DROP TABLE t1;
236
237 # 'CAST(signed AS DECIMAL)' does not copy 'unsigned_flag' from the argument.
238 # So the below is safe.
239 CREATE TABLE t1 (
240 a INT UNSIGNED,
241 b INT UNSIGNED,
242 c INT GENERATED ALWAYS AS (CAST(a AS DECIMAL(20,0))-CAST(b AS DECIMAL(20,0))) VIRTUAL,
243 KEY (c)
244 );
245 SHOW CREATE TABLE t1;
246 DROP TABLE t1;
247
248
249 --echo #
250 --echo # Comnination: PAD_CHAR_TO_FULL_LENGTH + NO_UNSIGNED_SUBTRACTION
251 --echo #
252
253 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
254 CREATE TABLE t1 (
255 a INT UNSIGNED,
256 b INT UNSIGNED,
257 c CHAR(5),
258 v VARCHAR(5) GENERATED ALWAYS AS (RPAD(c,a-b,' ')) VIRTUAL,
259 KEY (v)
260 );
261 SHOW WARNINGS;
262
263
264 # The below solves the dependency on NO_UNSIGNED_SUBTRACTION
265 # but does not solve the dependency on PAD_CHAR_TO_FULL_LENGTH,
266 # because the 'length' argument to RPAD() is not a constant.
267
268 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
269 CREATE TABLE t1 (
270 a INT UNSIGNED,
271 b INT UNSIGNED,
272 c CHAR(5),
273 v VARCHAR(5) GENERATED ALWAYS AS (RPAD(c,CAST(a AS DECIMAL(20,1))-b,' ')) VIRTUAL,
274 KEY (v)
275 );
276 SHOW WARNINGS;
277
278
279
280 --echo # ALTER TABLE ADD KEY(vcol_depending_on_sql_mode) --> error
281
282 # This makes sure that QT_ITEM_IDENT_SKIP_DB_NAMES and
283 # QT_ITEM_IDENT_SKIP_TABLE_NAMES are passed to print()
284 # to avoid temporary table names like `test`.`#sql-50a6_4`.`c`
285 # in the error message.
286 #
287
288 CREATE TABLE t1 (
289 a INT UNSIGNED,
290 b INT UNSIGNED,
291 c CHAR(5),
292 v VARCHAR(5) GENERATED ALWAYS AS (c) VIRTUAL
293 );
294 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
295 ALTER TABLE t1 ADD KEY(v);
296 SHOW WARNINGS;
297 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
298 CREATE INDEX v ON t1 (v);
299 SHOW WARNINGS;
300 DROP TABLE t1;
301
302
303 --echo # A virtual column on the second position in an index - cannot depend on sql_mode
304
305 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
306 CREATE TABLE t1 (id int, a CHAR(5), v TEXT AS (a) VIRTUAL, KEY(id, v(100)));
307 SHOW WARNINGS;
308
309
310 --echo # A persisten virtual column cannot depend on sql_mode
311
312 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
313 CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (a) PERSISTENT);
314 SHOW WARNINGS;
315
316 CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RTRIM(a)) PERSISTENT);
317 SHOW CREATE TABLE t1;
318 DROP TABLE t1;
319
320 --echo #
321 --echo # End of 10.2 tests
322 --echo #