Debian Codegraph @

mysql-test/suite/vcol/r/vcol_sql_mode.result

raw
1 #
2 # Start of 10.2 tests
3 #
4 #
5 # 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
6 #
7 #
8 # PAD_CHAR_TO_FULL_LENGTH + various virtual column data types
9 #
10 CREATE TABLE t1 (a CHAR(5), v CHAR(5) AS (a) VIRTUAL, KEY(v));
11 SHOW CREATE TABLE t1;
12 Table Create Table
13 t1 CREATE TABLE `t1` (
14 `a` char(5) DEFAULT NULL,
15 `v` char(5) GENERATED ALWAYS AS (`a`) VIRTUAL,
16 KEY `v` (`v`)
17 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
18 DROP TABLE t1;
19 CREATE TABLE t1 (a CHAR(5), v INT AS (a) VIRTUAL, KEY(v));
20 SHOW CREATE TABLE t1;
21 Table Create Table
22 t1 CREATE TABLE `t1` (
23 `a` char(5) DEFAULT NULL,
24 `v` int(11) GENERATED ALWAYS AS (`a`) VIRTUAL,
25 KEY `v` (`v`)
26 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
27 DROP TABLE t1;
28 CREATE TABLE t1 (a CHAR(5), v TIME AS (a) VIRTUAL, KEY(v));
29 ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
30 CREATE TABLE t1 (c CHAR(8), v BINARY(8) AS (c), KEY(v));
31 ERROR HY000: Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v`
32 SHOW WARNINGS;
33 Level Code Message
34 Error 1901 Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v`
35 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
36 CREATE TABLE t1 (a CHAR(5), v BIT(64) AS (a) VIRTUAL, KEY(v));
37 ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
38 SHOW WARNINGS;
39 Level Code Message
40 Error 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
41 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
42 CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (a) VIRTUAL, KEY(v));
43 ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
44 SHOW WARNINGS;
45 Level Code Message
46 Error 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
47 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
48 CREATE TABLE t1 (a CHAR(5), v TEXT AS (a) VIRTUAL, KEY(v(100)));
49 ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
50 SHOW WARNINGS;
51 Level Code Message
52 Error 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
53 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
54 # PAD_CHAR_TO_FULL_LENGTH + TRIM resolving dependency
55 CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RTRIM(a)) VIRTUAL, KEY(v));
56 SHOW CREATE TABLE t1;
57 Table Create Table
58 t1 CREATE TABLE `t1` (
59 `a` char(5) DEFAULT NULL,
60 `v` varchar(5) GENERATED ALWAYS AS (rtrim(`a`)) VIRTUAL,
61 KEY `v` (`v`)
62 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
63 DROP TABLE t1;
64 CREATE TABLE t1 (a CHAR(5), v TEXT AS (RTRIM(a)) VIRTUAL, KEY(v(100)));
65 SHOW CREATE TABLE t1;
66 Table Create Table
67 t1 CREATE TABLE `t1` (
68 `a` char(5) DEFAULT NULL,
69 `v` text GENERATED ALWAYS AS (rtrim(`a`)) VIRTUAL,
70 KEY `v` (`v`(100))
71 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
72 DROP TABLE t1;
73 CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(TRAILING ' ' FROM a)) VIRTUAL, KEY(v));
74 SHOW CREATE TABLE t1;
75 Table Create Table
76 t1 CREATE TABLE `t1` (
77 `a` char(5) DEFAULT NULL,
78 `v` varchar(5) GENERATED ALWAYS AS (trim(trailing ' ' from `a`)) VIRTUAL,
79 KEY `v` (`v`)
80 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
81 DROP TABLE t1;
82 CREATE TABLE t1 (a CHAR(5), v TEXT AS (TRIM(TRAILING ' ' FROM a)) VIRTUAL, KEY(v(100)));
83 SHOW CREATE TABLE t1;
84 Table Create Table
85 t1 CREATE TABLE `t1` (
86 `a` char(5) DEFAULT NULL,
87 `v` text GENERATED ALWAYS AS (trim(trailing ' ' from `a`)) VIRTUAL,
88 KEY `v` (`v`(100))
89 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
90 DROP TABLE t1;
91 CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(BOTH ' ' FROM a)) VIRTUAL, KEY(v));
92 SHOW CREATE TABLE t1;
93 Table Create Table
94 t1 CREATE TABLE `t1` (
95 `a` char(5) DEFAULT NULL,
96 `v` varchar(5) GENERATED ALWAYS AS (trim(both ' ' from `a`)) VIRTUAL,
97 KEY `v` (`v`)
98 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
99 DROP TABLE t1;
100 CREATE TABLE t1 (a CHAR(5), v TEXT AS (TRIM(BOTH ' ' FROM a)) VIRTUAL, KEY(v(100)));
101 SHOW CREATE TABLE t1;
102 Table Create Table
103 t1 CREATE TABLE `t1` (
104 `a` char(5) DEFAULT NULL,
105 `v` text GENERATED ALWAYS AS (trim(both ' ' from `a`)) VIRTUAL,
106 KEY `v` (`v`(100))
107 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
108 DROP TABLE t1;
109 CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(TRAILING NULL FROM a)) VIRTUAL, KEY(v));
110 SHOW CREATE TABLE t1;
111 Table Create Table
112 t1 CREATE TABLE `t1` (
113 `a` char(5) DEFAULT NULL,
114 `v` varchar(5) GENERATED ALWAYS AS (trim(trailing NULL from `a`)) VIRTUAL,
115 KEY `v` (`v`)
116 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
117 DROP TABLE t1;
118 CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(BOTH NULL FROM a)) VIRTUAL, KEY(v));
119 SHOW CREATE TABLE t1;
120 Table Create Table
121 t1 CREATE TABLE `t1` (
122 `a` char(5) DEFAULT NULL,
123 `v` varchar(5) GENERATED ALWAYS AS (trim(both NULL from `a`)) VIRTUAL,
124 KEY `v` (`v`)
125 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
126 DROP TABLE t1;
127 # PAD_CHAR_TO_FULL_LENGTH + TRIM not resolving dependency
128 CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(LEADING ' ' FROM a)) VIRTUAL, KEY(v));
129 ERROR HY000: Function or expression 'trim(leading ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
130 SHOW WARNINGS;
131 Level Code Message
132 Error 1901 Function or expression 'trim(leading ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
133 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
134 CREATE TABLE t1 (a CHAR(5), v TEXT AS (TRIM(LEADING ' ' FROM a)) VIRTUAL, KEY(v(100)));
135 ERROR HY000: Function or expression 'trim(leading ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
136 SHOW WARNINGS;
137 Level Code Message
138 Error 1901 Function or expression 'trim(leading ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
139 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
140 CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(TRAILING '' FROM a)) VIRTUAL, KEY(v));
141 ERROR HY000: Function or expression 'trim(trailing '' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
142 SHOW WARNINGS;
143 Level Code Message
144 Error 1901 Function or expression 'trim(trailing '' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
145 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
146 CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(BOTH '' FROM a)) VIRTUAL, KEY(v));
147 ERROR HY000: Function or expression 'trim(both '' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
148 SHOW WARNINGS;
149 Level Code Message
150 Error 1901 Function or expression 'trim(both '' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
151 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
152 CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(TRAILING 'x' FROM a)) VIRTUAL, KEY(v));
153 ERROR HY000: Function or expression 'trim(trailing 'x' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
154 SHOW WARNINGS;
155 Level Code Message
156 Error 1901 Function or expression 'trim(trailing 'x' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
157 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
158 CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(BOTH 'x' FROM a)) VIRTUAL, KEY(v));
159 ERROR HY000: Function or expression 'trim(both 'x' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
160 SHOW WARNINGS;
161 Level Code Message
162 Error 1901 Function or expression 'trim(both 'x' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
163 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
164 CREATE TABLE t1 (
165 a CHAR(5),
166 v VARCHAR(5) AS (TRIM(TRAILING ' ' FROM a)) VIRTUAL, KEY(v));
167 ERROR HY000: Function or expression 'trim(trailing ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
168 SHOW WARNINGS;
169 Level Code Message
170 Error 1901 Function or expression 'trim(trailing ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
171 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
172 CREATE TABLE t1 (
173 a CHAR(5),
174 v VARCHAR(5) AS (TRIM(BOTH ' ' FROM a)) VIRTUAL, KEY(v));
175 ERROR HY000: Function or expression 'trim(both ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
176 SHOW WARNINGS;
177 Level Code Message
178 Error 1901 Function or expression 'trim(both ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
179 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
180 # PAD_CHAR_TO_FULL_LENGTH + TRIM(... non_constant FROM a)
181 CREATE TABLE t1 (
182 a CHAR(5),
183 b CHAR(5),
184 v TEXT AS (TRIM(TRAILING b FROM a)) VIRTUAL, KEY(v(100)));
185 ERROR HY000: Function or expression 'trim(trailing `b` from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
186 SHOW WARNINGS;
187 Level Code Message
188 Error 1901 Function or expression 'trim(trailing `b` from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
189 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
190 # PAD_CHAR_TO_FULL_LENGTH + RPAD resolving dependency
191 CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RPAD(a,5,' ')) VIRTUAL, KEY(v));
192 SHOW CREATE TABLE t1;
193 Table Create Table
194 t1 CREATE TABLE `t1` (
195 `a` char(5) DEFAULT NULL,
196 `v` varchar(5) GENERATED ALWAYS AS (rpad(`a`,5,' ')) VIRTUAL,
197 KEY `v` (`v`)
198 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
199 DROP TABLE t1;
200 CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RPAD(a,6,' ')) VIRTUAL, KEY(v));
201 SHOW CREATE TABLE t1;
202 Table Create Table
203 t1 CREATE TABLE `t1` (
204 `a` char(5) DEFAULT NULL,
205 `v` varchar(5) GENERATED ALWAYS AS (rpad(`a`,6,' ')) VIRTUAL,
206 KEY `v` (`v`)
207 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
208 DROP TABLE t1;
209 CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RPAD(a,6,NULL)) VIRTUAL, KEY(v));
210 SHOW CREATE TABLE t1;
211 Table Create Table
212 t1 CREATE TABLE `t1` (
213 `a` char(5) DEFAULT NULL,
214 `v` varchar(5) GENERATED ALWAYS AS (rpad(`a`,6,NULL)) VIRTUAL,
215 KEY `v` (`v`)
216 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
217 DROP TABLE t1;
218 CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RPAD(a,NULL,' ')) VIRTUAL, KEY(v));
219 SHOW CREATE TABLE t1;
220 Table Create Table
221 t1 CREATE TABLE `t1` (
222 `a` char(5) DEFAULT NULL,
223 `v` varchar(5) GENERATED ALWAYS AS (rpad(`a`,NULL,' ')) VIRTUAL,
224 KEY `v` (`v`)
225 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
226 DROP TABLE t1;
227 # PAD_CHAR_TO_FULL_LENGTH + RPAD not resolving dependency
228 CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RPAD(a,4,' ')) VIRTUAL, KEY(v));
229 ERROR HY000: Function or expression 'rpad(`a`,4,' ')' cannot be used in the GENERATED ALWAYS AS clause of `v`
230 SHOW WARNINGS;
231 Level Code Message
232 Error 1901 Function or expression 'rpad(`a`,4,' ')' cannot be used in the GENERATED ALWAYS AS clause of `v`
233 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
234 CREATE TABLE t1 (
235 a CHAR(5),
236 b CHAR(5),
237 v VARCHAR(5) AS (RPAD(a,NULL,b)) VIRTUAL,
238 KEY(v)
239 );
240 ERROR HY000: Function or expression 'rpad(`a`,NULL,`b`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
241 SHOW WARNINGS;
242 Level Code Message
243 Error 1901 Function or expression 'rpad(`a`,NULL,`b`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
244 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
245 # PAD_CHAR_TO_FULL_LENGTH + comparison
246 CREATE TABLE t1 (a CHAR(5), v INT AS (a='a') VIRTUAL, KEY(v));
247 SHOW CREATE TABLE t1;
248 Table Create Table
249 t1 CREATE TABLE `t1` (
250 `a` char(5) DEFAULT NULL,
251 `v` int(11) GENERATED ALWAYS AS (`a` = 'a') VIRTUAL,
252 KEY `v` (`v`)
253 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
254 DROP TABLE t1;
255 CREATE TABLE t1 (
256 a CHAR(5) CHARACTER SET latin1 COLLATE latin1_nopad_bin,
257 v INT AS (a='a') VIRTUAL, KEY(v)
258 );
259 ERROR HY000: Function or expression '`a` = 'a'' cannot be used in the GENERATED ALWAYS AS clause of `v`
260 SHOW WARNINGS;
261 Level Code Message
262 Error 1901 Function or expression '`a` = 'a'' cannot be used in the GENERATED ALWAYS AS clause of `v`
263 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
264 # PAD_CHAR_TO_FULL_LENGTH + LIKE
265 CREATE TABLE t1 (a CHAR(5), v INT AS (a LIKE 'a%') VIRTUAL, KEY(v));
266 SHOW CREATE TABLE t1;
267 Table Create Table
268 t1 CREATE TABLE `t1` (
269 `a` char(5) DEFAULT NULL,
270 `v` int(11) GENERATED ALWAYS AS (`a` like 'a%') VIRTUAL,
271 KEY `v` (`v`)
272 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
273 DROP TABLE t1;
274 CREATE TABLE t1 (a CHAR(5), v INT AS (a LIKE NULL) VIRTUAL, KEY(v));
275 SHOW CREATE TABLE t1;
276 Table Create Table
277 t1 CREATE TABLE `t1` (
278 `a` char(5) DEFAULT NULL,
279 `v` int(11) GENERATED ALWAYS AS (`a` like NULL) VIRTUAL,
280 KEY `v` (`v`)
281 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
282 DROP TABLE t1;
283 CREATE TABLE t1 (a CHAR(5), v INT AS (a LIKE 'a') VIRTUAL, KEY(v));
284 ERROR HY000: Function or expression '`a` like 'a'' cannot be used in the GENERATED ALWAYS AS clause of `v`
285 SHOW WARNINGS;
286 Level Code Message
287 Error 1901 Function or expression '`a` like 'a'' cannot be used in the GENERATED ALWAYS AS clause of `v`
288 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
289 # PAD_CHAR_TO_FULL_LENGTH + LENGTH(char_column) = hard dependency
290 CREATE TABLE t1 (a CHAR(5), v INT AS (LENGTH(a)) VIRTUAL, KEY(v));
291 ERROR HY000: Function or expression 'octet_length(`a`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
292 SHOW WARNINGS;
293 Level Code Message
294 Error 1901 Function or expression 'octet_length(`a`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
295 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
296 #
297 # Testing NO_UNSIGNED_SUBTRACTION
298 #
299 CREATE TABLE t1 (
300 a INT UNSIGNED,
301 b INT UNSIGNED,
302 c INT GENERATED ALWAYS AS (a-b) VIRTUAL,
303 KEY (c)
304 );
305 ERROR HY000: Function or expression '`a` - `b`' cannot be used in the GENERATED ALWAYS AS clause of `c`
306 SHOW WARNINGS;
307 Level Code Message
308 Error 1901 Function or expression '`a` - `b`' cannot be used in the GENERATED ALWAYS AS clause of `c`
309 Warning 1105 Expression depends on the @@sql_mode value NO_UNSIGNED_SUBTRACTION
310 CREATE TABLE t1 (
311 a INT UNSIGNED,
312 b INT UNSIGNED,
313 c INT GENERATED ALWAYS AS (CAST(a AS SIGNED)-b) VIRTUAL,
314 KEY (c)
315 );
316 ERROR HY000: Function or expression 'cast(`a` as signed) - `b`' cannot be used in the GENERATED ALWAYS AS clause of `c`
317 SHOW WARNINGS;
318 Level Code Message
319 Error 1901 Function or expression 'cast(`a` as signed) - `b`' cannot be used in the GENERATED ALWAYS AS clause of `c`
320 Warning 1105 Expression depends on the @@sql_mode value NO_UNSIGNED_SUBTRACTION
321 CREATE TABLE t1 (
322 a INT UNSIGNED,
323 b INT UNSIGNED,
324 c INT GENERATED ALWAYS AS (a-CAST(b AS SIGNED)) VIRTUAL,
325 KEY (c)
326 );
327 ERROR HY000: Function or expression '`a` - cast(`b` as signed)' cannot be used in the GENERATED ALWAYS AS clause of `c`
328 SHOW WARNINGS;
329 Level Code Message
330 Error 1901 Function or expression '`a` - cast(`b` as signed)' cannot be used in the GENERATED ALWAYS AS clause of `c`
331 Warning 1105 Expression depends on the @@sql_mode value NO_UNSIGNED_SUBTRACTION
332 CREATE TABLE t1 (
333 a INT UNSIGNED,
334 b INT UNSIGNED,
335 c INT GENERATED ALWAYS AS (CAST(a AS SIGNED)-CAST(b AS SIGNED)) VIRTUAL,
336 KEY (c)
337 );
338 SHOW CREATE TABLE t1;
339 Table Create Table
340 t1 CREATE TABLE `t1` (
341 `a` int(10) unsigned DEFAULT NULL,
342 `b` int(10) unsigned DEFAULT NULL,
343 `c` int(11) GENERATED ALWAYS AS (cast(`a` as signed) - cast(`b` as signed)) VIRTUAL,
344 KEY `c` (`c`)
345 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
346 DROP TABLE t1;
347 CREATE TABLE t1 (
348 a INT UNSIGNED,
349 b INT UNSIGNED,
350 c INT GENERATED ALWAYS AS (CAST(a AS DECIMAL(20,0))-CAST(b AS DECIMAL(20,0))) VIRTUAL,
351 KEY (c)
352 );
353 SHOW CREATE TABLE t1;
354 Table Create Table
355 t1 CREATE TABLE `t1` (
356 `a` int(10) unsigned DEFAULT NULL,
357 `b` int(10) unsigned DEFAULT NULL,
358 `c` int(11) GENERATED ALWAYS AS (cast(`a` as decimal(20,0)) - cast(`b` as decimal(20,0))) VIRTUAL,
359 KEY `c` (`c`)
360 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
361 DROP TABLE t1;
362 #
363 # Comnination: PAD_CHAR_TO_FULL_LENGTH + NO_UNSIGNED_SUBTRACTION
364 #
365 CREATE TABLE t1 (
366 a INT UNSIGNED,
367 b INT UNSIGNED,
368 c CHAR(5),
369 v VARCHAR(5) GENERATED ALWAYS AS (RPAD(c,a-b,' ')) VIRTUAL,
370 KEY (v)
371 );
372 ERROR HY000: Function or expression 'rpad(`c`,`a` - `b`,' ')' cannot be used in the GENERATED ALWAYS AS clause of `v`
373 SHOW WARNINGS;
374 Level Code Message
375 Error 1901 Function or expression 'rpad(`c`,`a` - `b`,' ')' cannot be used in the GENERATED ALWAYS AS clause of `v`
376 Warning 1105 Expression depends on the @@sql_mode value NO_UNSIGNED_SUBTRACTION
377 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
378 CREATE TABLE t1 (
379 a INT UNSIGNED,
380 b INT UNSIGNED,
381 c CHAR(5),
382 v VARCHAR(5) GENERATED ALWAYS AS (RPAD(c,CAST(a AS DECIMAL(20,1))-b,' ')) VIRTUAL,
383 KEY (v)
384 );
385 ERROR HY000: Function or expression 'rpad(`c`,cast(`a` as decimal(20,1)) - `b`,' ')' cannot be used in the GENERATED ALWAYS AS clause of `v`
386 SHOW WARNINGS;
387 Level Code Message
388 Error 1901 Function or expression 'rpad(`c`,cast(`a` as decimal(20,1)) - `b`,' ')' cannot be used in the GENERATED ALWAYS AS clause of `v`
389 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
390 # ALTER TABLE ADD KEY(vcol_depending_on_sql_mode) --> error
391 CREATE TABLE t1 (
392 a INT UNSIGNED,
393 b INT UNSIGNED,
394 c CHAR(5),
395 v VARCHAR(5) GENERATED ALWAYS AS (c) VIRTUAL
396 );
397 ALTER TABLE t1 ADD KEY(v);
398 ERROR HY000: Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v`
399 SHOW WARNINGS;
400 Level Code Message
401 Error 1901 Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v`
402 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
403 CREATE INDEX v ON t1 (v);
404 ERROR HY000: Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v`
405 SHOW WARNINGS;
406 Level Code Message
407 Error 1901 Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v`
408 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
409 DROP TABLE t1;
410 # A virtual column on the second position in an index - cannot depend on sql_mode
411 CREATE TABLE t1 (id int, a CHAR(5), v TEXT AS (a) VIRTUAL, KEY(id, v(100)));
412 ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
413 SHOW WARNINGS;
414 Level Code Message
415 Error 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
416 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
417 # A persisten virtual column cannot depend on sql_mode
418 CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (a) PERSISTENT);
419 ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
420 SHOW WARNINGS;
421 Level Code Message
422 Error 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
423 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
424 CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RTRIM(a)) PERSISTENT);
425 SHOW CREATE TABLE t1;
426 Table Create Table
427 t1 CREATE TABLE `t1` (
428 `a` char(5) DEFAULT NULL,
429 `v` varchar(5) GENERATED ALWAYS AS (rtrim(`a`)) STORED
430 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
431 DROP TABLE t1;
432 #
433 # End of 10.2 tests
434 #