Debian Codegraph @

mysql-test/suite/gcol/r/gcol_column_def_options_myisam.result

raw
1 CALL mtr.add_suppression("Incorrect information in file");
2 SET @@session.default_storage_engine = 'MyISAM';
3 #
4 # Section 1. Wrong column definition options
5 # - DEFAULT <value>
6 # - AUTO_INCREMENT
7 # - SERIAL DEFAULT VALUE
8 # - ON UPDATE NOW ...
9 # NOT NULL
10 create table t1 (a int, b int generated always as (a+1) virtual not null);
11 insert into t1(a) values(null);
12 ERROR 23000: Column 'b' cannot be null
13 insert into t1(a) values(1);
14 select * from t1;
15 a b
16 1 2
17 drop table t1;
18 create table t1 (a int, b int generated always as (a+1) stored not null);
19 insert into t1(a) values(null);
20 ERROR 23000: Column 'b' cannot be null
21 insert into t1(a) values(1);
22 select * from t1;
23 a b
24 1 2
25 drop table t1;
26 create table t1 (a int);
27 alter table t1 add column b int generated always as (a+1) virtual not null;
28 drop table t1;
29 # NULL
30 create table t1 (a int, b int generated always as (a+1) virtual null);
31 drop table t1;
32 create table t1 (a int);
33 alter table t1 add column b int generated always as (a+1) virtual null;
34 drop table t1;
35 # Added columns mixed with virtual GC and other columns
36 create table t1 (a int);
37 insert into t1 values(1);
38 alter table t1 add column (b int generated always as (a+1) virtual, c int);
39 affected rows: 1
40 info: Records: 1 Duplicates: 0 Warnings: 0
41 alter table t1 add column (d int, e int generated always as (a+1) virtual);
42 affected rows: 1
43 info: Records: 1 Duplicates: 0 Warnings: 0
44 alter table t1 add column (f int generated always as (a+1) virtual, g int as(5) stored);
45 affected rows: 1
46 info: Records: 1 Duplicates: 0 Warnings: 0
47 alter table t1 add column (h int generated always as (a+1) virtual, i int as(5) virtual);
48 affected rows: 1
49 info: Records: 1 Duplicates: 0 Warnings: 0
50 drop table t1;
51 # DEFAULT
52 create table t1 (a int, b int generated always as (a+1) virtual default 0);
53 ERROR HY000: Incorrect usage of DEFAULT and generated column
54 create table t1 (a int);
55 alter table t1 add column b int generated always as (a+1) virtual default 0;
56 ERROR HY000: Incorrect usage of DEFAULT and generated column
57 drop table t1;
58 # AUTO_INCREMENT
59 create table t1 (a int, b int generated always as (a+1) virtual AUTO_INCREMENT);
60 ERROR HY000: Incorrect usage of AUTO_INCREMENT and generated column
61 create table t1 (a int);
62 alter table t1 add column b int generated always as (a+1) virtual AUTO_INCREMENT;
63 ERROR HY000: Incorrect usage of AUTO_INCREMENT and generated column
64 drop table t1;
65 # SERIAL DEFAULT VALUE
66 create table t1 (a int, b int generated always as (a+1) virtual SERIAL DEFAULT VALUE);
67 ERROR HY000: Incorrect usage of SERIAL DEFAULT VALUE and generated column
68 create table t1 (a int);
69 alter table t1 add column b int generated always as (a+1) virtual SERIAL DEFAULT VALUE;
70 ERROR HY000: Incorrect usage of SERIAL DEFAULT VALUE and generated column
71 drop table t1;
72 # ON UPDATE
73 create table t1 (a int, b int generated always as (a+1) virtual ON UPDATE NOW());
74 ERROR HY000: Incorrect usage of ON UPDATE and generated column
75 create table t1 (a int);
76 alter table t1 add column b int generated always as (a+1) virtual ON UPDATE NOW();
77 ERROR HY000: Incorrect usage of ON UPDATE and generated column
78 drop table t1;
79 # [PRIMARY] KEY
80 create table t1 (a int, b int generated always as (a+1) stored key);
81 insert into t1 (a) values (3),(1),(2);
82 select * from t1;
83 a b
84 1 2
85 2 3
86 3 4
87 drop table t1;
88 create table t1 (a int, b int generated always as (a+1) stored primary key);
89 insert into t1 (a) values (3),(1),(2);
90 select * from t1 order by b;
91 a b
92 1 2
93 2 3
94 3 4
95 drop table t1;
96 create table t1 (a int);
97 alter table t1 add column b int generated always as (a+1) stored key;
98 show create table t1;
99 Table Create Table
100 t1 CREATE TABLE `t1` (
101 `a` int DEFAULT NULL,
102 `b` int GENERATED ALWAYS AS ((`a` + 1)) STORED NOT NULL,
103 PRIMARY KEY (`b`)
104 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
105 alter table t1 add column c int generated always as (a+2) stored primary key;
106 ERROR 42000: Multiple primary key defined
107 drop table t1;
108 # Section 2. Other column definition options
109 # - COMMENT
110 # - REFERENCES (only syntax testing here)
111 # - STORED (only systax testing here)
112 create table t1 (a int, b int generated always as (a % 2) virtual comment 'my comment');
113 show create table t1;
114 Table Create Table
115 t1 CREATE TABLE `t1` (
116 `a` int DEFAULT NULL,
117 `b` int GENERATED ALWAYS AS ((`a` % 2)) VIRTUAL COMMENT 'my comment'
118 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
119 describe t1;
120 Field Type Null Key Default Extra
121 a int YES NULL
122 b int YES NULL VIRTUAL GENERATED
123 drop table t1;
124 create table t1 (a int, b int generated always as (a % 2) virtual);
125 alter table t1 modify b int generated always as (a % 2) virtual comment 'my comment';
126 show create table t1;
127 Table Create Table
128 t1 CREATE TABLE `t1` (
129 `a` int DEFAULT NULL,
130 `b` int GENERATED ALWAYS AS ((`a` % 2)) VIRTUAL COMMENT 'my comment'
131 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
132 describe t1;
133 Field Type Null Key Default Extra
134 a int YES NULL
135 b int YES NULL VIRTUAL GENERATED
136 insert into t1 (a) values (1);
137 select * from t1;
138 a b
139 1 1
140 insert into t1 values (2,default);
141 select a,b from t1 order by a;
142 a b
143 1 1
144 2 0
145 create table t2 like t1;
146 show create table t2;
147 Table Create Table
148 t2 CREATE TABLE `t2` (
149 `a` int DEFAULT NULL,
150 `b` int GENERATED ALWAYS AS ((`a` % 2)) VIRTUAL COMMENT 'my comment'
151 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
152 describe t2;
153 Field Type Null Key Default Extra
154 a int YES NULL
155 b int YES NULL VIRTUAL GENERATED
156 insert into t2 (a) values (1);
157 select * from t2;
158 a b
159 1 1
160 insert into t2 values (2,default);
161 select a,b from t2 order by a;
162 a b
163 1 1
164 2 0
165 drop table t2;
166 drop table t1;
167 create table t1 (a int, b int generated always as (a % 2) stored);
168 show create table t1;
169 Table Create Table
170 t1 CREATE TABLE `t1` (
171 `a` int DEFAULT NULL,
172 `b` int GENERATED ALWAYS AS ((`a` % 2)) STORED
173 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
174 describe t1;
175 Field Type Null Key Default Extra
176 a int YES NULL
177 b int YES NULL STORED GENERATED
178 insert into t1 (a) values (1);
179 select * from t1;
180 a b
181 1 1
182 insert into t1 values (2,default);
183 select a,b from t1 order by a;
184 a b
185 1 1
186 2 0
187 drop table t1;
188 create table t2 (a int);
189 create table t1 (a int, b int generated always as (a % 2) stored references t2(a));
190 show create table t1;
191 Table Create Table
192 t1 CREATE TABLE `t1` (
193 `a` int DEFAULT NULL,
194 `b` int GENERATED ALWAYS AS ((`a` % 2)) STORED
195 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
196 drop table t1;
197 create table t1 (a int, b int generated always as (a % 2) virtual);
198 alter table t1 modify b int generated always as (a % 2) stored references t2(a);
199 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'references t2(a)' at line 1
200 show create table t1;
201 Table Create Table
202 t1 CREATE TABLE `t1` (
203 `a` int DEFAULT NULL,
204 `b` int GENERATED ALWAYS AS ((`a` % 2)) VIRTUAL
205 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
206 drop table t1;
207 drop table t2;
208 FK options
209 create table t1(a int, b int as (a % 2), c int as (a) stored);
210 create table t2 (a int primary key);
211 alter table t1 add constraint foreign key fk(d) references t2(a);
212 ERROR 42000: Key column 'd' doesn't exist in table
213 alter table t1 add constraint foreign key fk(c) references t2(a) on delete set null;
214 ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column.
215 alter table t1 add constraint foreign key fk(c) references t2(a) on update set null;
216 ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column.
217 alter table t1 add constraint foreign key fk(c) references t2(a) on update cascade;
218 ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column.
219 drop table t1;
220 drop table t2;
221 Generated alwasy is optional
222 create table t1 (a int, b int as (a % 2) virtual);
223 show create table t1;
224 Table Create Table
225 t1 CREATE TABLE `t1` (
226 `a` int DEFAULT NULL,
227 `b` int GENERATED ALWAYS AS ((`a` % 2)) VIRTUAL
228 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
229 describe t1;
230 Field Type Null Key Default Extra
231 a int YES NULL
232 b int YES NULL VIRTUAL GENERATED
233 drop table t1;
234 create table t1 (a int, b int as (a % 2) stored);
235 show create table t1;
236 Table Create Table
237 t1 CREATE TABLE `t1` (
238 `a` int DEFAULT NULL,
239 `b` int GENERATED ALWAYS AS ((`a` % 2)) STORED
240 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
241 describe t1;
242 Field Type Null Key Default Extra
243 a int YES NULL
244 b int YES NULL STORED GENERATED
245 drop table t1;
246 Default should be non-stored column
247 create table t1 (a int, b int as (a % 2));
248 show create table t1;
249 Table Create Table
250 t1 CREATE TABLE `t1` (
251 `a` int DEFAULT NULL,
252 `b` int GENERATED ALWAYS AS ((`a` % 2)) VIRTUAL
253 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
254 describe t1;
255 Field Type Null Key Default Extra
256 a int YES NULL
257 b int YES NULL VIRTUAL GENERATED
258 drop table t1;
259 Expression can be constant
260 create table t1 (a int, b int as (5 * 2));
261 show create table t1;
262 Table Create Table
263 t1 CREATE TABLE `t1` (
264 `a` int DEFAULT NULL,
265 `b` int GENERATED ALWAYS AS ((5 * 2)) VIRTUAL
266 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
267 describe t1;
268 Field Type Null Key Default Extra
269 a int YES NULL
270 b int YES NULL VIRTUAL GENERATED
271 drop table t1;
272 Test generated columns referencing other generated columns
273 create table t1 (a int unique, b int generated always as(-a) virtual, c int generated always as (b + 1) virtual);
274 insert into t1 (a) values (1), (2);
275 select * from t1;
276 a b c
277 1 -1 0
278 2 -2 -1
279 insert into t1(a) values (1) on duplicate key update a=3;
280 select * from t1;
281 a b c
282 2 -2 -1
283 3 -3 -2
284 update t1 set a=4 where a=2;
285 select * from t1;
286 a b c
287 3 -3 -2
288 4 -4 -3
289 drop table t1;
290 create table t1 (a int, b int generated always as(-b) virtual, c int generated always as (b + 1) virtual);
291 ERROR HY000: Generated column can refer only to generated columns defined prior to it.
292 create table t1 (a int, b int generated always as(-c) virtual, c int generated always as (b + 1) virtual);
293 ERROR HY000: Generated column can refer only to generated columns defined prior to it.
294 CREATE TABLE t1 (pk INTEGER AUTO_INCREMENT, col_int_nokey INTEGER GENERATED ALWAYS AS (pk + col_int_key) STORED, col_int_key INTEGER, PRIMARY KEY (pk));
295 ERROR HY000: Generated column 'col_int_nokey' cannot refer to auto-increment column.
296 # Bug#20339347: FAIL TO USE CREATE ....SELECT STATEMENT TO CREATE A NEW TABLE
297 create table t1 (a int, b int generated always as(-a) virtual, c int generated always as (b + 1) stored);
298 insert into t1(a) values(1),(2);
299 create table tt as select * from t1;
300 select * from t1 order by a;
301 a b c
302 1 -1 0
303 2 -2 -1
304 select * from tt order by a;
305 a b c
306 1 -1 0
307 2 -2 -1
308 drop table t1,tt;
309 # Bug#20769299: INCORRECT KEY ERROR WHEN TRYING TO CREATE INDEX ON
310 # VIRTUAL GC FOR MYISAM
311 CREATE TABLE A (
312 pk INTEGER AUTO_INCREMENT,
313 col_int_nokey INTEGER,
314 col_int_key INTEGER GENERATED ALWAYS AS (pk + col_int_nokey) VIRTUAL, KEY
315 (col_int_key));
316 ERROR HY000: Table storage engine 'MyISAM' does not support the create option 'Index on virtual generated column'
317 # Bug#20745142: GENERATED COLUMNS: ASSERTION FAILED:
318 # THD->CHANGE_LIST.IS_EMPTY()
319 #
320 CREATE TABLE t1(a bigint AS (a between 1 and 1));
321 ERROR HY000: Generated column can refer only to generated columns defined prior to it.
322 # Bug#20757211: GENERATED COLUMNS: ALTER TABLE CRASHES
323 # IN FIND_FIELD_IN_TABLE
324 #
325 CREATE TABLE t1(a int);
326 ALTER TABLE t1 ADD COLUMN z int GENERATED ALWAYS AS
327 ( 1 NOT IN (SELECT 1 FROM t1 WHERE c0006) ) virtual;
328 ERROR HY000: Expression of generated column 'z' contains a disallowed function.
329 DROP TABLE t1;
330 # Bug#20566243: ERROR WHILE DOING CREATE TABLE T1 SELECT (QUERY ON GC COLUMNS)
331 CREATE TABLE t1(a int, b int as (a + 1),
332 c varchar(12) as ("aaaabb") stored, d blob as (c));
333 INSERT INTO t1(a) VALUES(1),(3);
334 SHOW CREATE TABLE t1;
335 Table Create Table
336 t1 CREATE TABLE `t1` (
337 `a` int DEFAULT NULL,
338 `b` int GENERATED ALWAYS AS ((`a` + 1)) VIRTUAL,
339 `c` varchar(12) GENERATED ALWAYS AS (_utf8mb4'aaaabb') STORED,
340 `d` blob GENERATED ALWAYS AS (`c`) VIRTUAL
341 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
342 SELECT * FROM t1 order by a;
343 a b c d
344 1 2 aaaabb aaaabb
345 3 4 aaaabb aaaabb
346 CREATE TABLE t2 LIKE t1;
347 SHOW CREATE TABLE t2;
348 Table Create Table
349 t2 CREATE TABLE `t2` (
350 `a` int DEFAULT NULL,
351 `b` int GENERATED ALWAYS AS ((`a` + 1)) VIRTUAL,
352 `c` varchar(12) GENERATED ALWAYS AS (_utf8mb4'aaaabb') STORED,
353 `d` blob GENERATED ALWAYS AS (`c`) VIRTUAL
354 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
355 CREATE TABLE t3 AS SELECT * FROM t1;
356 SHOW CREATE TABLE t3;
357 Table Create Table
358 t3 CREATE TABLE `t3` (
359 `a` int DEFAULT NULL,
360 `b` int DEFAULT NULL,
361 `c` varchar(12) DEFAULT NULL,
362 `d` blob
363 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
364 SELECT * FROM t3 order by a;
365 a b c d
366 1 2 aaaabb aaaabb
367 3 4 aaaabb aaaabb
368 CREATE TABLE t4 AS SELECT b,c,d FROM t1;
369 SHOW CREATE TABLE t4;
370 Table Create Table
371 t4 CREATE TABLE `t4` (
372 `b` int DEFAULT NULL,
373 `c` varchar(12) DEFAULT NULL,
374 `d` blob
375 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
376 SELECT * FROM t4 order by b;
377 b c d
378 2 aaaabb aaaabb
379 4 aaaabb aaaabb
380 DROP TABLE t1,t2,t3,t4;
381 # Bug#21025003:WL8149:ASSERTION `CTX->NUM_TO_DROP_FK
382 # == HA_ALTER_INFO->ALTER_INFO-> FAILED
383 #
384 CREATE TABLE t1 (
385 col1 int(11) DEFAULT NULL,
386 col2 int(11) DEFAULT NULL,
387 col3 int(11) DEFAULT NULL,
388 col4 int(11) DEFAULT NULL,
389 col5 int(11) GENERATED ALWAYS AS (col4 / col2) VIRTUAL,
390 col6 text
391 );
392 Warnings:
393 Warning 1681 Integer display width is deprecated and will be removed in a future release.
394 Warning 1681 Integer display width is deprecated and will be removed in a future release.
395 Warning 1681 Integer display width is deprecated and will be removed in a future release.
396 Warning 1681 Integer display width is deprecated and will be removed in a future release.
397 Warning 1681 Integer display width is deprecated and will be removed in a future release.
398 INSERT INTO t1(col1,col2,col3,col4,col6) VALUES(NULL,1,4,0,REPEAT(2,1000));
399 ALTER TABLE t1 DROP PRIMARY KEY , ADD KEY idx ( col5, col2 );
400 ERROR 42000: Can't DROP 'PRIMARY'; check that column/key exists
401 DROP TABLE t1;
402 # Bug#20949226:i CAN ASSIGN NON-DEFAULT() VALUE TO GENERATED COLUMN
403 #
404 CREATE TABLE t1 (c1 INT, c2 INT AS (c1 * 2)) SELECT 1 AS c1, 5 AS c2;
405 ERROR HY000: The value specified for generated column 'c2' in table 't1' is not allowed.
406 CREATE TABLE t2 (a int);
407 INSERT INTO t2 values(1);
408 CREATE TABLE t1 (c1 INT, c2 INT AS (c1 * 2)) SELECT 1 AS c1, a AS c2 from t2;
409 ERROR HY000: The value specified for generated column 'c2' in table 't1' is not allowed.
410 CREATE TABLE t1 (c1 INT, c2 INT AS (c1 * 2)) SELECT 1 AS c1, 5;
411 SELECT * FROM t1;
412 c2 c1 5
413 2 1 5
414 DROP TABLE t1, t2;
415 # Bug#20757211: GENERATED COLUMNS: ALTER TABLE CRASHES
416 # IN FIND_FIELD_IN_TABLE
417 #
418 CREATE TABLE t1(a int);
419 ALTER TABLE t1 ADD COLUMN z int GENERATED ALWAYS AS
420 ( 1 NOT IN (SELECT 1 FROM t1 WHERE c0006) ) virtual;
421 ERROR HY000: Expression of generated column 'z' contains a disallowed function.
422 CREATE TABLE t2(a int, b int as (1 NOT IN (SELECT 1 FROM t1 WHERE not_exist_col)));
423 ERROR HY000: Expression of generated column 'b' contains a disallowed function.
424 CREATE TABLE t2(a int, b int as (1 NOT IN (SELECT 1 FROM dual)));
425 ERROR HY000: Expression of generated column 'b' contains a disallowed function.
426 DROP TABLE t1;
427 # Bug#21142905: PARTITIONED GENERATED COLS -
428 # !TABLE || (!TABLE->WRITE_SET || BITMAP_IS_SET
429 #
430 "Skipped since this engine does not support native partitioning.
431 # Bug #20709487: COLLATE OPTION NOT ACCEPTED IN GENERATED COLUMN
432 # DEFINITION
433 #
434 # Check for a valid syntax:
435 CREATE TABLE t1 (c CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin GENERATED ALWAYS AS ("foo bar"));
436 Warnings:
437 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
438 Warning 3778 'utf8mb3_bin' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
439 SHOW CREATE TABLE t1;
440 Table Create Table
441 t1 CREATE TABLE `t1` (
442 `c` char(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin GENERATED ALWAYS AS (_utf8mb4'foo bar') VIRTUAL
443 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
444 DROP TABLE t1;
445 CREATE TABLE t1 (i INT);
446 ALTER TABLE t1 ADD COLUMN c CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin GENERATED ALWAYS AS ("foo bar");
447 Warnings:
448 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
449 Warning 3778 'utf8mb3_bin' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
450 SHOW CREATE TABLE t1;
451 Table Create Table
452 t1 CREATE TABLE `t1` (
453 `i` int DEFAULT NULL,
454 `c` char(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin GENERATED ALWAYS AS (_utf8mb4'foo bar') VIRTUAL
455 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
456 DROP TABLE t1;
457 CREATE TABLE t1 (i INT COLLATE utf8_bin, c INT COLLATE utf8_bin GENERATED ALWAYS AS (10));
458 Warnings:
459 Warning 3778 'utf8mb3_bin' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
460 Warning 3778 'utf8mb3_bin' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
461 SHOW CREATE TABLE t1;
462 Table Create Table
463 t1 CREATE TABLE `t1` (
464 `i` int DEFAULT NULL,
465 `c` int GENERATED ALWAYS AS (10) VIRTUAL
466 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
467 DROP TABLE t1;
468 # Check for a charset mismatch processing:
469 CREATE TABLE t1 (t CHAR(10) CHARACTER SET utf8 COLLATE ascii_bin GENERATED ALWAYS AS ("foo bar"));
470 ERROR 42000: COLLATION 'ascii_bin' is not valid for CHARACTER SET 'utf8mb3'
471 # Check for a sorting order support:
472 CREATE TABLE t1 (c CHAR(10) CHARSET latin1 COLLATE latin1_bin,
473 c_ci CHAR(10) CHARSET latin1 COLLATE latin1_general_ci GENERATED ALWAYS AS (c),
474 c_cs CHAR(10) CHARSET latin1 COLLATE latin1_general_cs GENERATED ALWAYS AS (c));
475 INSERT INTO t1 (c) VALUES ('a'), ('A'), ('b');
476 SELECT * FROM t1 ORDER BY c;
477 c c_ci c_cs
478 A A A
479 a a a
480 b b b
481 SELECT * FROM t1 ORDER BY c_ci, c;
482 c c_ci c_cs
483 A A A
484 a a a
485 b b b
486 SELECT * FROM t1 ORDER BY c_cs;
487 c c_ci c_cs
488 A A A
489 a a a
490 b b b
491 SELECT c, c_ci REGEXP 'A', c_cs REGEXP 'A' FROM t1;
492 c c_ci REGEXP 'A' c_cs REGEXP 'A'
493 A 1 1
494 a 1 0
495 b 0 0
496 DROP TABLE t1;
497 # Bug #21469535: VALGRIND ERROR (CONDITIONAL JUMP) WHEN INSERT
498 # ROWS INTO A PARTITIONED TABLE
499 #
500 "Skipped since this engine does not support native partitioning.
501 # Bug#21465626:ASSERT/CRASH ON DROPPING/ADDING VIRTUAL COLUMN
502 CREATE TABLE t (a int(11), b int(11),
503 c int(11) GENERATED ALWAYS AS (a+b) VIRTUAL,
504 d int(11) GENERATED ALWAYS AS (a+b) VIRTUAL);
505 Warnings:
506 Warning 1681 Integer display width is deprecated and will be removed in a future release.
507 Warning 1681 Integer display width is deprecated and will be removed in a future release.
508 Warning 1681 Integer display width is deprecated and will be removed in a future release.
509 Warning 1681 Integer display width is deprecated and will be removed in a future release.
510 INSERT INTO t(a,b) VALUES(1,2);
511 # Mixed drop/add/rename virtual with non-virtual columns,
512 # ALGORITHM=INPLACE is not supported for InnoDB
513 ALTER TABLE t DROP d, ADD e varchar(10);
514 affected rows: 1
515 info: Records: 1 Duplicates: 0 Warnings: 0
516 ALTER TABLE t ADD d int, ADD f char(10) AS ('aaa');
517 affected rows: 1
518 info: Records: 1 Duplicates: 0 Warnings: 0
519 ALTER TABLE t CHANGE d dd int, CHANGE f ff varchar(10) AS ('bbb');
520 affected rows: 1
521 info: Records: 1 Duplicates: 0 Warnings: 0
522 # Only drop/add/change virtual, inplace is supported for Innodb
523 ALTER TABLE t DROP c, DROP ff;
524 affected rows: 1
525 info: Records: 1 Duplicates: 0 Warnings: 0
526 ALTER TABLE t ADD c int(11) as (a+b), ADD f varchar(10) as ('aaa');
527 affected rows: 1
528 info: Records: 1 Duplicates: 0 Warnings: 1
529 Warnings:
530 Warning 1681 Integer display width is deprecated and will be removed in a future release.
531 ALTER TABLE t CHANGE c c int(11) as (a), CHANGE f f varchar(10) as('bbb');
532 affected rows: 1
533 info: Records: 1 Duplicates: 0 Warnings: 1
534 Warnings:
535 Warning 1681 Integer display width is deprecated and will be removed in a future release.
536 # Change order should be ALGORITHM=INPLACE on Innodb
537 ALTER TABLE t CHANGE c c int(11) as (a) after f;
538 affected rows: 1
539 info: Records: 1 Duplicates: 0 Warnings: 1
540 Warnings:
541 Warning 1681 Integer display width is deprecated and will be removed in a future release.
542 ALTER TABLE t CHANGE b b int(11) after c;
543 affected rows: 1
544 info: Records: 1 Duplicates: 0 Warnings: 1
545 Warnings:
546 Warning 1681 Integer display width is deprecated and will be removed in a future release.
547 # TODO: Changing virtual column type should be ALGORITHM=INPLACE on InnoDB, current it goes only with COPY method
548 ALTER TABLE t CHANGE c c varchar(10) as ('a');
549 affected rows: 1
550 info: Records: 1 Duplicates: 0 Warnings: 0
551 # Changing stored column type is ALGORITHM=COPY
552 ALTER TABLE t CHANGE dd d varchar(10);
553 affected rows: 1
554 info: Records: 1 Duplicates: 0 Warnings: 0
555 DROP TABLE t;
556 #
557 # Bug#21900170: CAN'T CREATE TABLE WITH NULLABLE GENERATED COLUMNS
558 #
559 CREATE TABLE t1 (
560 a INT,
561 b INT GENERATED ALWAYS AS (-a) VIRTUAL NULL,
562 c INT GENERATED ALWAYS AS (a + b) STORED NULL);
563 SHOW FULL COLUMNS FROM t1;
564 Field Type Collation Null Key Default Extra Privileges Comment
565 a int NULL YES NULL #
566 b int NULL YES NULL VIRTUAL GENERATED #
567 c int NULL YES NULL STORED GENERATED #
568 INSERT INTO t1 (a) VALUES (NULL), (1);
569 SELECT * FROM t1;
570 a b c
571 1 -1 0
572 NULL NULL NULL
573 DROP TABLE t1;
574 # Bug#21854004: GCOLS:INNODB: FAILING ASSERTION: I < TABLE->N_DEF
575 CREATE TABLE t1(
576 col1 INTEGER PRIMARY KEY,
577 col2 INTEGER,
578 col3 INTEGER,
579 col4 INTEGER,
580 vgc1 INTEGER AS (col2 + col3) VIRTUAL,
581 sgc1 INTEGER AS (col2 - col3) STORED
582 );
583 INSERT INTO t1(col1, col2, col3) VALUES
584 (1, 10, 100), (2, 20, 200);
585 SELECT * FROM t1 order by col1;
586 col1 col2 col3 col4 vgc1 sgc1
587 1 10 100 NULL 110 -90
588 2 20 200 NULL 220 -180
589 ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 * col3) VIRTUAL;
590 SELECT * FROM t1 order by col1;
591 col1 col2 col3 col4 vgc1 sgc1
592 1 10 100 NULL 1000 -90
593 2 20 200 NULL 4000 -180
594 ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 / col3) STORED;
595 SELECT * FROM t1 order by col1;
596 col1 col2 col3 col4 vgc1 sgc1
597 1 10 100 NULL 1000 0
598 2 20 200 NULL 4000 0
599 ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 + col3) VIRTUAL;
600 ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 - col3) STORED;
601 ALTER TABLE t1 ADD INDEX sgc1 (sgc1);
602 ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 / col3) STORED;
603 SELECT * FROM t1 order by col1;
604 col1 col2 col3 col4 vgc1 sgc1
605 1 10 100 NULL 110 0
606 2 20 200 NULL 220 0
607 SELECT sgc1 FROM t1 order by sgc1;
608 sgc1
609 0
610 0
611 ALTER TABLE t1 DROP INDEX sgc1;
612 ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 - col3) STORED;
613 ALTER TABLE t1 ADD UNIQUE INDEX sgc1 (sgc1);
614 ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 / col3) STORED;
615 ERROR 23000: Duplicate entry '0' for key 't1.sgc1'
616 ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 * col3) VIRTUAL;
617 SELECT * FROM t1 order by col1;
618 col1 col2 col3 col4 vgc1 sgc1
619 1 10 100 NULL 1000 -90
620 2 20 200 NULL 4000 -180
621 SELECT vgc1 FROM t1 order by col1;
622 vgc1
623 1000
624 4000
625 ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 * col3) STORED;
626 SELECT * FROM t1 order by col1;
627 col1 col2 col3 col4 vgc1 sgc1
628 1 10 100 NULL 1000 1000
629 2 20 200 NULL 4000 4000
630 SELECT sgc1 FROM t1 order by sgc1;
631 sgc1
632 1000
633 4000
634 ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 * col3) STORED;
635 ERROR HY000: 'Changing the STORED status' is not supported for generated columns.
636 ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 / col3) VIRTUAL;
637 ERROR HY000: 'Changing the STORED status' is not supported for generated columns.
638 ALTER TABLE t1 MODIFY COLUMN col4 INTEGER AS (col1 + col2 + col3) STORED;
639 SELECT * FROM t1 order by col1;
640 col1 col2 col3 col4 vgc1 sgc1
641 1 10 100 111 1000 1000
642 2 20 200 222 4000 4000
643 ALTER TABLE t1 MODIFY COLUMN col4 INTEGER;
644 SELECT * FROM t1 order by col1;
645 col1 col2 col3 col4 vgc1 sgc1
646 1 10 100 111 1000 1000
647 2 20 200 222 4000 4000
648 DROP TABLE t1;
649 #
650 # Bug#22680839: DEFAULT IS NOT DETERMINISTIC AND SHOULD NOT BE
651 # ALLOWED IN GENERATED COLUMNS
652 #
653 # Test 1: ALTER DEFAULT
654 #
655 CREATE TABLE t1(a INT PRIMARY KEY DEFAULT 5,
656 b INT AS (1 + DEFAULT(a)) STORED,
657 c INT AS (1 + DEFAULT(a)) VIRTUAL);
658 INSERT INTO t1 VALUES ();
659 ALTER TABLE t1 ALTER COLUMN a SET DEFAULT 7, ALGORITHM = INPLACE;
660 Got one of the listed errors
661 ALTER TABLE t1 ALTER COLUMN a SET DEFAULT 7;
662 affected rows: 1
663 info: Records: 1 Duplicates: 0 Warnings: 0
664 ALTER TABLE t1 MODIFY COLUMN a INT DEFAULT 8;
665 affected rows: 1
666 info: Records: 1 Duplicates: 0 Warnings: 0
667 ALTER TABLE t1 CHANGE COLUMN a a DOUBLE DEFAULT 5;
668 affected rows: 1
669 info: Records: 1 Duplicates: 0 Warnings: 0
670 DROP TABLE t1;
671 # Test 2: ALTER DEFAULT + ADD GCOL
672 #
673 CREATE TABLE t1(a INT PRIMARY KEY DEFAULT 5);
674 INSERT INTO t1 VALUES();
675 ALTER TABLE t1 ALTER COLUMN a SET DEFAULT 7,
676 ADD COLUMN b INT AS (1 + DEFAULT(a)) STORED,
677 ALGORITHM = INPLACE;
678 Got one of the listed errors
679 ALTER TABLE t1 ALTER COLUMN a SET DEFAULT 7,
680 ADD COLUMN b INT AS (1 + DEFAULT(a)) STORED,
681 ADD COLUMN c INT AS (1 + DEFAULT(a)) VIRTUAL;
682 affected rows: 1
683 info: Records: 1 Duplicates: 0 Warnings: 0
684 DROP TABLE t1;
685 #
686 # Bug#28836543 "DUPLICATE HANDLING OF GENERATION EXPRESSION CHANGE IN
687 # FILL_ALTER_INPLACE_INFO".
688 #
689 # For ALTER TABLE statements which do not modify generated column type,
690 # expression and storage type INPLACE algorithm should be supported.
691 # ALTER TABLE statements which modify generated column expression or
692 # type should require COPY algorithm. Change of storage type of
693 # generated column is not supported.
694 CREATE TABLE t1 (a INT, g INT GENERATED ALWAYS AS (a + 1) VIRTUAL);
695 ALTER TABLE t1 MODIFY g INT GENERATED ALWAYS AS (a + 1) VIRTUAL COMMENT 'Test', ALGORITHM=INPLACE;
696 ALTER TABLE t1 MODIFY g INT GENERATED ALWAYS AS (a + 2) VIRTUAL COMMENT 'Test', ALGORITHM=INPLACE;
697 ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
698 ALTER TABLE t1 MODIFY g BIGINT GENERATED ALWAYS AS (a + 1) VIRTUAL COMMENT 'Test', ALGORITHM=INPLACE;
699 ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
700 ALTER TABLE t1 MODIFY g INT GENERATED ALWAYS AS (a + 1) STORED COMMENT 'Test', ALGORITHM=INPLACE;
701 ERROR HY000: 'Changing the STORED status' is not supported for generated columns.
702 DROP TABLE t1;
703 CREATE TABLE t2 (a INT, g INT GENERATED ALWAYS AS (a + 1) STORED);
704 ALTER TABLE t2 MODIFY g INT GENERATED ALWAYS AS (a + 1) STORED COMMENT 'Test', ALGORITHM=INPLACE;
705 ALTER TABLE t2 MODIFY g INT GENERATED ALWAYS AS (a + 2) STORED COMMENT 'Test', ALGORITHM=INPLACE;
706 ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
707 # InnoDB has special error message for change of column type
708 ALTER TABLE t2 MODIFY g BIGINT GENERATED ALWAYS AS (a + 1) STORED COMMENT 'Test', ALGORITHM=INPLACE;
709 Got one of the listed errors
710 ALTER TABLE t2 MODIFY g INT GENERATED ALWAYS AS (a + 1) VIRTUAL COMMENT 'Test', ALGORITHM=INPLACE;
711 ERROR HY000: 'Changing the STORED status' is not supported for generated columns.
712 DROP TABLE t2;
713 #
714 # Bug #28848265 "CHANGING DEFAULT BY MAKING COLUMN NULLABLE DOESN'T
715 # UPDATE GENERATED COLUMN".
716 #
717 # Test for original issue.
718 CREATE TABLE t1(a INT NOT NULL DEFAULT 4, gc INT AS (a + DEFAULT(a)) STORED);
719 INSERT INTO t1 (a) VALUES (1);
720 ALTER TABLE t1 MODIFY COLUMN a INT NULL;
721 INSERT INTO t1 (a) VALUES (2);
722 # Generated stored column value must reflect new default (i.e. NULL).
723 SELECT *, a + DEFAULT(a) FROM t1;
724 a gc a + DEFAULT(a)
725 1 NULL NULL
726 2 NULL NULL
727 # Check that change of default on which generated column depends
728 # can't be done in-place.
729 ALTER TABLE t1 MODIFY COLUMN a INT NULL DEFAULT 0, ALGORITHM=INPLACE;
730 ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
731 ALTER TABLE t1 ALTER COLUMN a SET DEFAULT 0, ALGORITHM=INPLACE;
732 ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
733 # However, changing default of some other column can be done
734 # in-place.
735 ALTER TABLE t1 ADD b INT;
736 ALTER TABLE t1 MODIFY COLUMN b INT DEFAULT 0, ALGORITHM=INPLACE;
737 ALTER TABLE t1 ALTER COLUMN b SET DEFAULT 1, ALGORITHM=INPLACE;
738 DROP TABLE t1;
739 DROP VIEW IF EXISTS v1,v2;
740 DROP TABLE IF EXISTS t1,t2,t3;
741 DROP PROCEDURE IF EXISTS p1;
742 DROP FUNCTION IF EXISTS f1;
743 DROP TRIGGER IF EXISTS trg1;
744 DROP TRIGGER IF EXISTS trg2;
745 set sql_warnings = 0;