Debian Codegraph @

mysql-test/suite/gcol_ndb/r/gcol_column_def_options_ndb.result

raw
1 SET @@session.default_storage_engine = 'ndbcluster';
2 #
3 # Section 1. Wrong column definition options
4 # - DEFAULT <value>
5 # - AUTO_INCREMENT
6 # - SERIAL DEFAULT VALUE
7 # - ON UPDATE NOW ...
8 # NOT NULL
9 create table t1 (a int, b int generated always as (a+1) virtual not null);
10 insert into t1(a) values(null);
11 ERROR 23000: Column 'b' cannot be null
12 insert into t1(a) values(1);
13 select * from t1;
14 a b
15 1 2
16 drop table t1;
17 create table t1 (a int, b int generated always as (a+1) stored not null);
18 insert into t1(a) values(null);
19 ERROR 23000: Column 'b' cannot be null
20 insert into t1(a) values(1);
21 select * from t1;
22 a b
23 1 2
24 drop table t1;
25 create table t1 (a int);
26 alter table t1 add column b int generated always as (a+1) virtual not null;
27 drop table t1;
28 # NULL
29 create table t1 (a int, b int generated always as (a+1) virtual null);
30 drop table t1;
31 create table t1 (a int);
32 alter table t1 add column b int generated always as (a+1) virtual null;
33 drop table t1;
34 # Added columns mixed with virtual GC and other columns
35 create table t1 (a int);
36 insert into t1 values(1);
37 alter table t1 add column (b int generated always as (a+1) virtual, c int);
38 affected rows: 1
39 info: Records: 1 Duplicates: 0 Warnings: 0
40 alter table t1 add column (d int, e int generated always as (a+1) virtual);
41 affected rows: 1
42 info: Records: 1 Duplicates: 0 Warnings: 0
43 alter table t1 add column (f int generated always as (a+1) virtual, g int as(5) stored);
44 affected rows: 1
45 info: Records: 1 Duplicates: 0 Warnings: 0
46 alter table t1 add column (h int generated always as (a+1) virtual, i int as(5) virtual);
47 affected rows: 0
48 info: Records: 0 Duplicates: 0 Warnings: 0
49 drop table t1;
50 # DEFAULT
51 create table t1 (a int, b int generated always as (a+1) virtual default 0);
52 ERROR HY000: Incorrect usage of DEFAULT and generated column
53 create table t1 (a int);
54 alter table t1 add column b int generated always as (a+1) virtual default 0;
55 ERROR HY000: Incorrect usage of DEFAULT and generated column
56 drop table t1;
57 # AUTO_INCREMENT
58 create table t1 (a int, b int generated always as (a+1) virtual AUTO_INCREMENT);
59 ERROR HY000: Incorrect usage of AUTO_INCREMENT and generated column
60 create table t1 (a int);
61 alter table t1 add column b int generated always as (a+1) virtual AUTO_INCREMENT;
62 ERROR HY000: Incorrect usage of AUTO_INCREMENT and generated column
63 drop table t1;
64 # SERIAL DEFAULT VALUE
65 create table t1 (a int, b int generated always as (a+1) virtual SERIAL DEFAULT VALUE);
66 ERROR HY000: Incorrect usage of SERIAL DEFAULT VALUE and generated column
67 create table t1 (a int);
68 alter table t1 add column b int generated always as (a+1) virtual SERIAL DEFAULT VALUE;
69 ERROR HY000: Incorrect usage of SERIAL DEFAULT VALUE and generated column
70 drop table t1;
71 # ON UPDATE
72 create table t1 (a int, b int generated always as (a+1) virtual ON UPDATE NOW());
73 ERROR HY000: Incorrect usage of ON UPDATE and generated column
74 create table t1 (a int);
75 alter table t1 add column b int generated always as (a+1) virtual ON UPDATE NOW();
76 ERROR HY000: Incorrect usage of ON UPDATE and generated column
77 drop table t1;
78 # [PRIMARY] KEY
79 create table t1 (a int, b int generated always as (a+1) stored key);
80 insert into t1 (a) values (3),(1),(2);
81 select * from t1;
82 a b
83 1 2
84 2 3
85 3 4
86 drop table t1;
87 create table t1 (a int, b int generated always as (a+1) stored primary key);
88 insert into t1 (a) values (3),(1),(2);
89 select * from t1 order by b;
90 a b
91 1 2
92 2 3
93 3 4
94 drop table t1;
95 create table t1 (a int);
96 alter table t1 add column b int generated always as (a+1) stored key;
97 show create table t1;
98 Table Create Table
99 t1 CREATE TABLE `t1` (
100 `a` int DEFAULT NULL,
101 `b` int GENERATED ALWAYS AS ((`a` + 1)) STORED NOT NULL,
102 PRIMARY KEY (`b`)
103 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
104 alter table t1 add column c int generated always as (a+2) stored primary key;
105 ERROR 42000: Multiple primary key defined
106 drop table t1;
107 # Section 2. Other column definition options
108 # - COMMENT
109 # - REFERENCES (only syntax testing here)
110 # - STORED (only systax testing here)
111 create table t1 (a int, b int generated always as (a % 2) virtual comment 'my comment');
112 show create table t1;
113 Table Create Table
114 t1 CREATE TABLE `t1` (
115 `a` int DEFAULT NULL,
116 `b` int GENERATED ALWAYS AS ((`a` % 2)) VIRTUAL COMMENT 'my comment'
117 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
118 describe t1;
119 Field Type Null Key Default Extra
120 a int YES NULL
121 b int YES NULL VIRTUAL GENERATED
122 drop table t1;
123 create table t1 (a int, b int generated always as (a % 2) virtual);
124 alter table t1 modify b int generated always as (a % 2) virtual comment 'my comment';
125 show create table t1;
126 Table Create Table
127 t1 CREATE TABLE `t1` (
128 `a` int DEFAULT NULL,
129 `b` int GENERATED ALWAYS AS ((`a` % 2)) VIRTUAL COMMENT 'my comment'
130 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
131 describe t1;
132 Field Type Null Key Default Extra
133 a int YES NULL
134 b int YES NULL VIRTUAL GENERATED
135 insert into t1 (a) values (1);
136 select * from t1;
137 a b
138 1 1
139 insert into t1 values (2,default);
140 select a,b from t1 order by a;
141 a b
142 1 1
143 2 0
144 create table t2 like t1;
145 show create table t2;
146 Table Create Table
147 t2 CREATE TABLE `t2` (
148 `a` int DEFAULT NULL,
149 `b` int GENERATED ALWAYS AS ((`a` % 2)) VIRTUAL COMMENT 'my comment'
150 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
151 describe t2;
152 Field Type Null Key Default Extra
153 a int YES NULL
154 b int YES NULL VIRTUAL GENERATED
155 insert into t2 (a) values (1);
156 select * from t2;
157 a b
158 1 1
159 insert into t2 values (2,default);
160 select a,b from t2 order by a;
161 a b
162 1 1
163 2 0
164 drop table t2;
165 drop table t1;
166 create table t1 (a int, b int generated always as (a % 2) stored);
167 show create table t1;
168 Table Create Table
169 t1 CREATE TABLE `t1` (
170 `a` int DEFAULT NULL,
171 `b` int GENERATED ALWAYS AS ((`a` % 2)) STORED
172 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
173 describe t1;
174 Field Type Null Key Default Extra
175 a int YES NULL
176 b int YES NULL STORED GENERATED
177 insert into t1 (a) values (1);
178 select * from t1;
179 a b
180 1 1
181 insert into t1 values (2,default);
182 select a,b from t1 order by a;
183 a b
184 1 1
185 2 0
186 drop table t1;
187 create table t2 (a int);
188 create table t1 (a int, b int generated always as (a % 2) stored references t2(a));
189 show create table t1;
190 Table Create Table
191 t1 CREATE TABLE `t1` (
192 `a` int DEFAULT NULL,
193 `b` int GENERATED ALWAYS AS ((`a` % 2)) STORED
194 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
195 drop table t1;
196 create table t1 (a int, b int generated always as (a % 2) virtual);
197 alter table t1 modify b int generated always as (a % 2) stored references t2(a);
198 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
199 show create table t1;
200 Table Create Table
201 t1 CREATE TABLE `t1` (
202 `a` int DEFAULT NULL,
203 `b` int GENERATED ALWAYS AS ((`a` % 2)) VIRTUAL
204 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
205 drop table t1;
206 drop table t2;
207 FK options
208 create table t1(a int, b int as (a % 2), c int as (a) stored);
209 create table t2 (a int primary key);
210 alter table t1 add constraint foreign key fk(d) references t2(a);
211 ERROR 42000: Key column 'd' doesn't exist in table
212 alter table t1 add constraint foreign key fk(c) references t2(a) on delete set null;
213 ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column.
214 alter table t1 add constraint foreign key fk(c) references t2(a) on update set null;
215 ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column.
216 alter table t1 add constraint foreign key fk(c) references t2(a) on update cascade;
217 ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column.
218 drop table t1;
219 drop table t2;
220 Generated alwasy is optional
221 create table t1 (a int, b int as (a % 2) virtual);
222 show create table t1;
223 Table Create Table
224 t1 CREATE TABLE `t1` (
225 `a` int DEFAULT NULL,
226 `b` int GENERATED ALWAYS AS ((`a` % 2)) VIRTUAL
227 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
228 describe t1;
229 Field Type Null Key Default Extra
230 a int YES NULL
231 b int YES NULL VIRTUAL GENERATED
232 drop table t1;
233 create table t1 (a int, b int as (a % 2) stored);
234 show create table t1;
235 Table Create Table
236 t1 CREATE TABLE `t1` (
237 `a` int DEFAULT NULL,
238 `b` int GENERATED ALWAYS AS ((`a` % 2)) STORED
239 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
240 describe t1;
241 Field Type Null Key Default Extra
242 a int YES NULL
243 b int YES NULL STORED GENERATED
244 drop table t1;
245 Default should be non-stored column
246 create table t1 (a int, b int as (a % 2));
247 show create table t1;
248 Table Create Table
249 t1 CREATE TABLE `t1` (
250 `a` int DEFAULT NULL,
251 `b` int GENERATED ALWAYS AS ((`a` % 2)) VIRTUAL
252 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
253 describe t1;
254 Field Type Null Key Default Extra
255 a int YES NULL
256 b int YES NULL VIRTUAL GENERATED
257 drop table t1;
258 Expression can be constant
259 create table t1 (a int, b int as (5 * 2));
260 show create table t1;
261 Table Create Table
262 t1 CREATE TABLE `t1` (
263 `a` int DEFAULT NULL,
264 `b` int GENERATED ALWAYS AS ((5 * 2)) VIRTUAL
265 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
266 describe t1;
267 Field Type Null Key Default Extra
268 a int YES NULL
269 b int YES NULL VIRTUAL GENERATED
270 drop table t1;
271 Test generated columns referencing other generated columns
272 create table t1 (a int unique, b int generated always as(-a) virtual, c int generated always as (b + 1) virtual);
273 insert into t1 (a) values (1), (2);
274 select * from t1;
275 a b c
276 1 -1 0
277 2 -2 -1
278 insert into t1(a) values (1) on duplicate key update a=3;
279 select * from t1;
280 a b c
281 2 -2 -1
282 3 -3 -2
283 update t1 set a=4 where a=2;
284 select * from t1;
285 a b c
286 3 -3 -2
287 4 -4 -3
288 drop table t1;
289 create table t1 (a int, b int generated always as(-b) virtual, c int generated always as (b + 1) virtual);
290 ERROR HY000: Generated column can refer only to generated columns defined prior to it.
291 create table t1 (a int, b int generated always as(-c) virtual, c int generated always as (b + 1) virtual);
292 ERROR HY000: Generated column can refer only to generated columns defined prior to it.
293 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));
294 ERROR HY000: Generated column 'col_int_nokey' cannot refer to auto-increment column.
295 # Bug#20339347: FAIL TO USE CREATE ....SELECT STATEMENT TO CREATE A NEW TABLE
296 create table t1 (a int, b int generated always as(-a) virtual, c int generated always as (b + 1) stored);
297 insert into t1(a) values(1),(2);
298 create table tt as select * from t1;
299 select * from t1 order by a;
300 a b c
301 1 -1 0
302 2 -2 -1
303 select * from tt order by a;
304 a b c
305 1 -1 0
306 2 -2 -1
307 drop table t1,tt;
308 # Bug#20769299: INCORRECT KEY ERROR WHEN TRYING TO CREATE INDEX ON
309 # VIRTUAL GC FOR MYISAM
310 CREATE TABLE A (
311 pk INTEGER AUTO_INCREMENT,
312 col_int_nokey INTEGER,
313 col_int_key INTEGER GENERATED ALWAYS AS (pk + col_int_nokey) VIRTUAL, KEY
314 (col_int_key));
315 ERROR HY000: Table storage engine 'ndbcluster' does not support the create option 'Index on virtual generated column'
316 # Bug#20745142: GENERATED COLUMNS: ASSERTION FAILED:
317 # THD->CHANGE_LIST.IS_EMPTY()
318 #
319 CREATE TABLE t1(a bigint AS (a between 1 and 1));
320 ERROR HY000: Generated column can refer only to generated columns defined prior to it.
321 # Bug#20757211: GENERATED COLUMNS: ALTER TABLE CRASHES
322 # IN FIND_FIELD_IN_TABLE
323 #
324 CREATE TABLE t1(a int);
325 ALTER TABLE t1 ADD COLUMN z int GENERATED ALWAYS AS
326 ( 1 NOT IN (SELECT 1 FROM t1 WHERE c0006) ) virtual;
327 ERROR HY000: Expression of generated column 'z' contains a disallowed function.
328 DROP TABLE t1;
329 # Bug#20566243: ERROR WHILE DOING CREATE TABLE T1 SELECT (QUERY ON GC COLUMNS)
330 CREATE TABLE t1(a int, b int as (a + 1),
331 c varchar(12) as ("aaaabb") stored, d blob as (c));
332 INSERT INTO t1(a) VALUES(1),(3);
333 SHOW CREATE TABLE t1;
334 Table Create Table
335 t1 CREATE TABLE `t1` (
336 `a` int DEFAULT NULL,
337 `b` int GENERATED ALWAYS AS ((`a` + 1)) VIRTUAL,
338 `c` varchar(12) GENERATED ALWAYS AS (_utf8mb4'aaaabb') STORED,
339 `d` blob GENERATED ALWAYS AS (`c`) VIRTUAL
340 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
341 SELECT * FROM t1 order by a;
342 a b c d
343 1 2 aaaabb aaaabb
344 3 4 aaaabb aaaabb
345 CREATE TABLE t2 LIKE t1;
346 SHOW CREATE TABLE t2;
347 Table Create Table
348 t2 CREATE TABLE `t2` (
349 `a` int DEFAULT NULL,
350 `b` int GENERATED ALWAYS AS ((`a` + 1)) VIRTUAL,
351 `c` varchar(12) GENERATED ALWAYS AS (_utf8mb4'aaaabb') STORED,
352 `d` blob GENERATED ALWAYS AS (`c`) VIRTUAL
353 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
354 CREATE TABLE t3 AS SELECT * FROM t1;
355 Warnings:
356 Warning 1478 Table storage engine 'ndbcluster' does not support the create option 'Binlog of table with BLOB attribute and no PK'
357 SHOW CREATE TABLE t3;
358 Table Create Table
359 t3 CREATE TABLE `t3` (
360 `a` int DEFAULT NULL,
361 `b` int DEFAULT NULL,
362 `c` varchar(12) DEFAULT NULL,
363 `d` blob
364 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
365 SELECT * FROM t3 order by a;
366 a b c d
367 1 2 aaaabb aaaabb
368 3 4 aaaabb aaaabb
369 CREATE TABLE t4 AS SELECT b,c,d FROM t1;
370 Warnings:
371 Warning 1478 Table storage engine 'ndbcluster' does not support the create option 'Binlog of table with BLOB attribute and no PK'
372 SHOW CREATE TABLE t4;
373 Table Create Table
374 t4 CREATE TABLE `t4` (
375 `b` int DEFAULT NULL,
376 `c` varchar(12) DEFAULT NULL,
377 `d` blob
378 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
379 SELECT * FROM t4 order by b;
380 b c d
381 2 aaaabb aaaabb
382 4 aaaabb aaaabb
383 DROP TABLE t1,t2,t3,t4;
384 # Bug#21025003:WL8149:ASSERTION `CTX->NUM_TO_DROP_FK
385 # == HA_ALTER_INFO->ALTER_INFO-> FAILED
386 #
387 CREATE TABLE t1 (
388 col1 int(11) DEFAULT NULL,
389 col2 int(11) DEFAULT NULL,
390 col3 int(11) DEFAULT NULL,
391 col4 int(11) DEFAULT NULL,
392 col5 int(11) GENERATED ALWAYS AS (col4 / col2) VIRTUAL,
393 col6 text
394 );
395 Warnings:
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 Warning 1681 Integer display width is deprecated and will be removed in a future release.
399 Warning 1681 Integer display width is deprecated and will be removed in a future release.
400 Warning 1681 Integer display width is deprecated and will be removed in a future release.
401 Warning 1478 Table storage engine 'ndbcluster' does not support the create option 'Binlog of table with BLOB attribute and no PK'
402 INSERT INTO t1(col1,col2,col3,col4,col6) VALUES(NULL,1,4,0,REPEAT(2,1000));
403 ALTER TABLE t1 DROP PRIMARY KEY , ADD KEY idx ( col5, col2 );
404 ERROR 42000: Can't DROP 'PRIMARY'; check that column/key exists
405 DROP TABLE t1;
406 # Bug#20949226:i CAN ASSIGN NON-DEFAULT() VALUE TO GENERATED COLUMN
407 #
408 CREATE TABLE t1 (c1 INT, c2 INT AS (c1 * 2)) SELECT 1 AS c1, 5 AS c2;
409 ERROR HY000: The value specified for generated column 'c2' in table 't1' is not allowed.
410 CREATE TABLE t2 (a int);
411 INSERT INTO t2 values(1);
412 CREATE TABLE t1 (c1 INT, c2 INT AS (c1 * 2)) SELECT 1 AS c1, a AS c2 from t2;
413 ERROR HY000: The value specified for generated column 'c2' in table 't1' is not allowed.
414 CREATE TABLE t1 (c1 INT, c2 INT AS (c1 * 2)) SELECT 1 AS c1, 5;
415 SELECT * FROM t1;
416 c2 c1 5
417 2 1 5
418 DROP TABLE t1, t2;
419 # Bug#20757211: GENERATED COLUMNS: ALTER TABLE CRASHES
420 # IN FIND_FIELD_IN_TABLE
421 #
422 CREATE TABLE t1(a int);
423 ALTER TABLE t1 ADD COLUMN z int GENERATED ALWAYS AS
424 ( 1 NOT IN (SELECT 1 FROM t1 WHERE c0006) ) virtual;
425 ERROR HY000: Expression of generated column 'z' contains a disallowed function.
426 CREATE TABLE t2(a int, b int as (1 NOT IN (SELECT 1 FROM t1 WHERE not_exist_col)));
427 ERROR HY000: Expression of generated column 'b' contains a disallowed function.
428 CREATE TABLE t2(a int, b int as (1 NOT IN (SELECT 1 FROM dual)));
429 ERROR HY000: Expression of generated column 'b' contains a disallowed function.
430 DROP TABLE t1;
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=ndbcluster 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=ndbcluster 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=ndbcluster 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#21465626:ASSERT/CRASH ON DROPPING/ADDING VIRTUAL COLUMN
498 CREATE TABLE t (a int(11), b int(11),
499 c int(11) GENERATED ALWAYS AS (a+b) VIRTUAL,
500 d int(11) GENERATED ALWAYS AS (a+b) VIRTUAL);
501 Warnings:
502 Warning 1681 Integer display width is deprecated and will be removed in a future release.
503 Warning 1681 Integer display width is deprecated and will be removed in a future release.
504 Warning 1681 Integer display width is deprecated and will be removed in a future release.
505 Warning 1681 Integer display width is deprecated and will be removed in a future release.
506 INSERT INTO t(a,b) VALUES(1,2);
507 # Mixed drop/add/rename virtual with non-virtual columns,
508 # ALGORITHM=INPLACE is not supported for InnoDB
509 ALTER TABLE t DROP d, ADD e varchar(10);
510 affected rows: 1
511 info: Records: 1 Duplicates: 0 Warnings: 0
512 ALTER TABLE t ADD d int, ADD f char(10) AS ('aaa');
513 affected rows: 1
514 info: Records: 1 Duplicates: 0 Warnings: 0
515 ALTER TABLE t CHANGE d dd int, CHANGE f ff varchar(10) AS ('bbb');
516 affected rows: 1
517 info: Records: 1 Duplicates: 0 Warnings: 0
518 # Only drop/add/change virtual, inplace is supported for Innodb
519 ALTER TABLE t DROP c, DROP ff;
520 affected rows: 1
521 info: Records: 1 Duplicates: 0 Warnings: 0
522 ALTER TABLE t ADD c int(11) as (a+b), ADD f varchar(10) as ('aaa');
523 affected rows: 0
524 info: Records: 0 Duplicates: 0 Warnings: 1
525 Warnings:
526 Warning 1681 Integer display width is deprecated and will be removed in a future release.
527 ALTER TABLE t CHANGE c c int(11) as (a), CHANGE f f varchar(10) as('bbb');
528 affected rows: 1
529 info: Records: 1 Duplicates: 0 Warnings: 1
530 Warnings:
531 Warning 1681 Integer display width is deprecated and will be removed in a future release.
532 # Change order should be ALGORITHM=INPLACE on Innodb
533 ALTER TABLE t CHANGE c c int(11) as (a) after f;
534 affected rows: 1
535 info: Records: 1 Duplicates: 0 Warnings: 1
536 Warnings:
537 Warning 1681 Integer display width is deprecated and will be removed in a future release.
538 ALTER TABLE t CHANGE b b int(11) after c;
539 affected rows: 1
540 info: Records: 1 Duplicates: 0 Warnings: 1
541 Warnings:
542 Warning 1681 Integer display width is deprecated and will be removed in a future release.
543 # TODO: Changing virtual column type should be ALGORITHM=INPLACE on InnoDB, current it goes only with COPY method
544 ALTER TABLE t CHANGE c c varchar(10) as ('a');
545 affected rows: 1
546 info: Records: 1 Duplicates: 0 Warnings: 0
547 # Changing stored column type is ALGORITHM=COPY
548 ALTER TABLE t CHANGE dd d varchar(10);
549 affected rows: 1
550 info: Records: 1 Duplicates: 0 Warnings: 0
551 DROP TABLE t;
552 #
553 # Bug#21900170: CAN'T CREATE TABLE WITH NULLABLE GENERATED COLUMNS
554 #
555 CREATE TABLE t1 (
556 a INT,
557 b INT GENERATED ALWAYS AS (-a) VIRTUAL NULL,
558 c INT GENERATED ALWAYS AS (a + b) STORED NULL);
559 SHOW FULL COLUMNS FROM t1;
560 Field Type Collation Null Key Default Extra Privileges Comment
561 a int NULL YES NULL #
562 b int NULL YES NULL VIRTUAL GENERATED #
563 c int NULL YES NULL STORED GENERATED #
564 INSERT INTO t1 (a) VALUES (NULL), (1);
565 SELECT * FROM t1;
566 a b c
567 1 -1 0
568 NULL NULL NULL
569 DROP TABLE t1;
570 # Bug#21854004: GCOLS:INNODB: FAILING ASSERTION: I < TABLE->N_DEF
571 CREATE TABLE t1(
572 col1 INTEGER PRIMARY KEY,
573 col2 INTEGER,
574 col3 INTEGER,
575 col4 INTEGER,
576 vgc1 INTEGER AS (col2 + col3) VIRTUAL,
577 sgc1 INTEGER AS (col2 - col3) STORED
578 );
579 INSERT INTO t1(col1, col2, col3) VALUES
580 (1, 10, 100), (2, 20, 200);
581 SELECT * FROM t1 order by col1;
582 col1 col2 col3 col4 vgc1 sgc1
583 1 10 100 NULL 110 -90
584 2 20 200 NULL 220 -180
585 ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 * col3) VIRTUAL;
586 SELECT * FROM t1 order by col1;
587 col1 col2 col3 col4 vgc1 sgc1
588 1 10 100 NULL 1000 -90
589 2 20 200 NULL 4000 -180
590 ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 / col3) STORED;
591 SELECT * FROM t1 order by col1;
592 col1 col2 col3 col4 vgc1 sgc1
593 1 10 100 NULL 1000 0
594 2 20 200 NULL 4000 0
595 ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 + col3) VIRTUAL;
596 ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 - col3) STORED;
597 ALTER TABLE t1 ADD INDEX sgc1 (sgc1);
598 ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 / col3) STORED;
599 SELECT * FROM t1 order by col1;
600 col1 col2 col3 col4 vgc1 sgc1
601 1 10 100 NULL 110 0
602 2 20 200 NULL 220 0
603 SELECT sgc1 FROM t1 order by sgc1;
604 sgc1
605 0
606 0
607 ALTER TABLE t1 DROP INDEX sgc1;
608 ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 - col3) STORED;
609 ALTER TABLE t1 ADD UNIQUE INDEX sgc1 (sgc1);
610 ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 / col3) STORED;
611 ERROR 23000: Duplicate entry '' for key '*UNKNOWN*'
612 ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 * col3) VIRTUAL;
613 SELECT * FROM t1 order by col1;
614 col1 col2 col3 col4 vgc1 sgc1
615 1 10 100 NULL 1000 -90
616 2 20 200 NULL 4000 -180
617 SELECT vgc1 FROM t1 order by col1;
618 vgc1
619 1000
620 4000
621 ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 * col3) STORED;
622 SELECT * FROM t1 order by col1;
623 col1 col2 col3 col4 vgc1 sgc1
624 1 10 100 NULL 1000 1000
625 2 20 200 NULL 4000 4000
626 SELECT sgc1 FROM t1 order by sgc1;
627 sgc1
628 1000
629 4000
630 ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 * col3) STORED;
631 ERROR HY000: 'Changing the STORED status' is not supported for generated columns.
632 ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 / col3) VIRTUAL;
633 ERROR HY000: 'Changing the STORED status' is not supported for generated columns.
634 ALTER TABLE t1 MODIFY COLUMN col4 INTEGER AS (col1 + col2 + col3) STORED;
635 SELECT * FROM t1 order by col1;
636 col1 col2 col3 col4 vgc1 sgc1
637 1 10 100 111 1000 1000
638 2 20 200 222 4000 4000
639 ALTER TABLE t1 MODIFY COLUMN col4 INTEGER;
640 SELECT * FROM t1 order by col1;
641 col1 col2 col3 col4 vgc1 sgc1
642 1 10 100 111 1000 1000
643 2 20 200 222 4000 4000
644 DROP TABLE t1;
645 #
646 # Bug#22680839: DEFAULT IS NOT DETERMINISTIC AND SHOULD NOT BE
647 # ALLOWED IN GENERATED COLUMNS
648 #
649 # Test 1: ALTER DEFAULT
650 #
651 CREATE TABLE t1(a INT PRIMARY KEY DEFAULT 5,
652 b INT AS (1 + DEFAULT(a)) STORED,
653 c INT AS (1 + DEFAULT(a)) VIRTUAL);
654 INSERT INTO t1 VALUES ();
655 ALTER TABLE t1 ALTER COLUMN a SET DEFAULT 7, ALGORITHM = INPLACE;
656 Got one of the listed errors
657 ALTER TABLE t1 ALTER COLUMN a SET DEFAULT 7;
658 affected rows: 1
659 info: Records: 1 Duplicates: 0 Warnings: 0
660 ALTER TABLE t1 MODIFY COLUMN a INT DEFAULT 8;
661 affected rows: 1
662 info: Records: 1 Duplicates: 0 Warnings: 0
663 ALTER TABLE t1 CHANGE COLUMN a a DOUBLE DEFAULT 5;
664 affected rows: 1
665 info: Records: 1 Duplicates: 0 Warnings: 0
666 DROP TABLE t1;
667 # Test 2: ALTER DEFAULT + ADD GCOL
668 #
669 CREATE TABLE t1(a INT PRIMARY KEY DEFAULT 5);
670 INSERT INTO t1 VALUES();
671 ALTER TABLE t1 ALTER COLUMN a SET DEFAULT 7,
672 ADD COLUMN b INT AS (1 + DEFAULT(a)) STORED,
673 ALGORITHM = INPLACE;
674 Got one of the listed errors
675 ALTER TABLE t1 ALTER COLUMN a SET DEFAULT 7,
676 ADD COLUMN b INT AS (1 + DEFAULT(a)) STORED,
677 ADD COLUMN c INT AS (1 + DEFAULT(a)) VIRTUAL;
678 affected rows: 1
679 info: Records: 1 Duplicates: 0 Warnings: 0
680 DROP TABLE t1;
681 DROP VIEW IF EXISTS v1,v2;
682 DROP TABLE IF EXISTS t1,t2,t3;
683 DROP PROCEDURE IF EXISTS p1;
684 DROP FUNCTION IF EXISTS f1;
685 DROP TRIGGER IF EXISTS trg1;
686 DROP TRIGGER IF EXISTS trg2;
687 set sql_warnings = 0;