Debian Codegraph @

mysql-test/suite/gcol/inc/gcol_column_def_options.inc

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