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 --source include/have_partition.inc
17
18 --echo #
19 --echo # Section 1. Wrong column definition options
20 --echo # - DEFAULT <value>
21 --echo # - AUTO_INCREMENT
22
23 --echo # NOT NULL
24 --error ER_PARSE_ERROR
25 create table t1 (a int, b int generated always as (a+1) virtual not null);
26 --error ER_PARSE_ERROR
27 create table t1 (a int, b int generated always as (a+1) stored not null);
28 create table t1 (a int);
29 --error ER_PARSE_ERROR
30 alter table t1 add column b int generated always as (a+1) virtual not null;
31 drop table t1;
32
33 --error ER_PARSE_ERROR
34 create table t1 (a int, b int generated always as (a+1) virtual null);
35 create table t1 (a int);
36 --error ER_PARSE_ERROR
37 alter table t1 add column b int generated always as (a+1) virtual null;
38 drop table t1;
39
40 --echo # Added columns mixed with virtual GC and other columns
41 create table t1 (a int);
42 insert into t1 values(1);
43 --enable_info
44 alter table t1 add column (b int generated always as (a+1) virtual, c int);
45 alter table t1 add column (d int, e int generated always as (a+1) virtual);
46 alter table t1 add column (f int generated always as (a+1) virtual, g int as(5) stored);
47 alter table t1 add column (h int generated always as (a+1) virtual, i int as(5) virtual);
48 --disable_info
49 drop table t1;
50
51 --echo # DEFAULT
52 --error ER_PARSE_ERROR
53 create table t1 (a int, b int generated always as (a+1) virtual default 0);
54 create table t1 (a int);
55 --error ER_PARSE_ERROR
56 alter table t1 add column b int generated always as (a+1) virtual default 0;
57 drop table t1;
58
59 --echo # AUTO_INCREMENT
60 --error ER_PARSE_ERROR
61 create table t1 (a int, b int generated always as (a+1) virtual AUTO_INCREMENT);
62 create table t1 (a int);
63 --error ER_PARSE_ERROR
64 alter table t1 add column b int generated always as (a+1) virtual AUTO_INCREMENT;
65 drop table t1;
66
67 --echo # [PRIMARY] KEY
68 if ($support_virtual_index)
69 {
70 --error ER_PARSE_ERROR
71 create table t1 (a int, b int generated always as (a+1) virtual key);
72 }
73 --error ER_PARSE_ERROR
74 create table t1 (a int, b int generated always as (a+1) stored key);
75 if ($support_virtual_index)
76 {
77 --error ER_PARSE_ERROR
78 create table t1 (a int, b int generated always as (a+1) virtual primary key);
79 }
80 --error ER_PARSE_ERROR
81 create table t1 (a int, b int generated always as (a+1) stored primary key);
82 create table t1 (a int);
83 if ($support_virtual_index)
84 {
85 --error ER_PARSE_ERROR
86 alter table t1 add column b int generated always as (a+1) virtual key;
87 }
88 --error ER_PARSE_ERROR
89 alter table t1 add column b int generated always as (a+1) stored key;
90 if ($support_virtual_index)
91 {
92 --error ER_PARSE_ERROR
93 alter table t1 add column c int generated always as (a+2) virtual primary key;
94 }
95 show create table t1;
96 --error ER_PARSE_ERROR
97 alter table t1 add column c int generated always as (a+2) stored primary key;
98 drop table t1;
99
100 --echo # Section 2. Other column definition options
101 --echo # - COMMENT
102 --echo # - REFERENCES (only syntax testing here)
103 --echo # - STORED (only systax testing here)
104 create table t1 (a int, b int generated always as (a % 2) virtual comment 'my comment');
105 show create table t1;
106 describe t1;
107 drop table t1;
108 create table t1 (a int, b int generated always as (a % 2) virtual);
109 alter table t1 modify b int generated always as (a % 2) virtual comment 'my comment';
110 show create table t1;
111 describe t1;
112 insert into t1 (a) values (1);
113 select * from t1;
114 insert into t1 values (2,default);
115 select a,b from t1 order by a;
116 create table t2 like t1;
117 show create table t2;
118 describe t2;
119 insert into t2 (a) values (1);
120 select * from t2;
121 insert into t2 values (2,default);
122 select a,b from t2 order by a;
123 drop table t2;
124 drop table t1;
125
126 create table t1 (a int, b int generated always as (a % 2) stored);
127 show create table t1;
128 describe t1;
129 insert into t1 (a) values (1);
130 select * from t1;
131 insert into t1 values (2,default);
132 select a,b from t1 order by a;
133 drop table t1;
134
135
136 create table t2 (a int);
137 --error ER_PARSE_ERROR
138 create table t1 (a int, b int generated always as (a % 2) stored references t2(a));
139 create table t1 (a int, b int generated always as (a % 2) stored);
140 show create table t1;
141 drop table t1;
142 create table t1 (a int, b int generated always as (a % 2) virtual);
143 --error ER_PARSE_ERROR
144 alter table t1 modify b int generated always as (a % 2) stored references t2(a);
145 --error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN
146 alter table t1 modify b int generated always as (a % 2) stored;
147 show create table t1;
148 drop table t1;
149 drop table t2;
150 --echo FK options
151 create table t1(a int, b int as (a % 2), c int as (a) stored);
152 create table t2 (a int);
153 --error ER_KEY_COLUMN_DOES_NOT_EXIST
154 alter table t1 add constraint foreign key fk(d) references t2(a);
155 if ($support_virtual_foreign)
156 {
157 --error ER_CANT_CREATE_TABLE
158 alter table t1 add constraint foreign key fk(b) references t2(a);
159 }
160 --error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN
161 alter table t1 add constraint foreign key fk(c) references t2(a) on delete set null;
162 --error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN
163 alter table t1 add constraint foreign key fk(c) references t2(a) on update set null;
164 --error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN
165 alter table t1 add constraint foreign key fk(c) references t2(a) on update cascade;
166 drop table t1;
167 drop table t2;
168 --echo Generated always is optional
169 create table t1 (a int, b int as (a % 2) virtual);
170 show create table t1;
171 describe t1;
172 drop table t1;
173 create table t1 (a int, b int as (a % 2) stored);
174 show create table t1;
175 describe t1;
176 drop table t1;
177 --echo Default should be non-stored column
178 create table t1 (a int, b int as (a % 2));
179 show create table t1;
180 describe t1;
181 drop table t1;
182 --echo Expression can be constant
183 create table t1 (a int, b int as (5 * 2));
184 show create table t1;
185 describe t1;
186 drop table t1;
187 --echo Test generated columns referencing other generated columns
188 create table t1 (a int unique, b int generated always as(-a) virtual, c int generated always as (b + 1) virtual);
189 insert into t1 (a) values (1), (2);
190 --sorted_result
191 select * from t1;
192 insert into t1(a) values (1) on duplicate key update a=3;
193 --sorted_result
194 select * from t1;
195 update t1 set a=4 where a=2;
196 --sorted_result
197 select * from t1;
198 drop table t1;
199
200 --error ER_EXPRESSION_REFERS_TO_UNINIT_FIELD
201 create table t1 (a int, b int generated always as(-b) virtual, c int generated always as (b + 1) virtual);
202 --error ER_EXPRESSION_REFERS_TO_UNINIT_FIELD
203 create table t1 (a int, b int generated always as(-c) virtual, c int generated always as (b + 1) virtual);
204 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
205 create table t1 (pk int auto_increment primary key, col_int_nokey int generated always as (pk + col_int_key) stored, col_int_key int);
206 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
207 create table t1 (pk int auto_increment primary key, col_int_nokey int generated always as (pk + col_int_key) unique, col_int_key int);
208 create table t1 (pk int auto_increment primary key, col_int_nokey int generated always as (pk + col_int_key), col_int_key int);
209 show create table t1;
210 insert t1 (col_int_key) values (10),(20),(30);
211 select * from t1;
212 drop table t1;
213
214 --echo # Bug#20339347: FAIL TO USE CREATE ....SELECT STATEMENT TO CREATE A NEW TABLE
215 create table t1 (a int, b int generated always as(-a) virtual, c int generated always as (b + 1) stored);
216 insert into t1(a) values(1),(2);
217 create table tt as select * from t1;
218 select * from t1 order by a;
219 select * from tt order by a;
220 drop table t1,tt;
221
222 if (!$support_virtual_index)
223 {
224 --echo # Bug#20769299: INCORRECT KEY ERROR WHEN TRYING TO CREATE INDEX ON
225 --echo # VIRTUAL GC FOR MYISAM
226 --error ER_KEY_BASED_ON_GENERATED_GENERATED_COLUMN
227 CREATE TABLE A (
228 pk INTEGER,
229 col_int_nokey INTEGER,
230 col_int_key INTEGER GENERATED ALWAYS AS (pk + col_int_nokey) VIRTUAL, KEY
231 (col_int_key));
232 }
233
234 --echo # Bug#20745142: GENERATED COLUMNS: ASSERTION FAILED:
235 --echo # THD->CHANGE_LIST.IS_EMPTY()
236 --echo #
237 --error ER_EXPRESSION_REFERS_TO_UNINIT_FIELD
238 CREATE TABLE t1(a bigint AS (a between 1 and 1));
239
240 --echo # Bug#20757211: GENERATED COLUMNS: ALTER TABLE CRASHES
241 --echo # IN FIND_FIELD_IN_TABLE
242 --echo #
243 CREATE TABLE t1(a int);
244 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
245 ALTER TABLE t1 ADD COLUMN z int GENERATED ALWAYS AS
246 ( 1 NOT IN (SELECT 1 FROM t1 WHERE c0006) ) virtual;
247 DROP TABLE t1;
248
249 --echo # Bug#20566243: ERROR WHILE DOING CREATE TABLE T1 SELECT (QUERY ON GC COLUMNS)
250 CREATE TABLE t1(a int, b int as (a + 1),
251 c varchar(12) as ("aaaabb") stored, d blob as (c));
252 INSERT INTO t1(a) VALUES(1),(3);
253 SHOW CREATE TABLE t1;
254 SELECT * FROM t1 order by a;
255 CREATE TABLE t2 LIKE t1;
256 SHOW CREATE TABLE t2;
257 CREATE TABLE t3 AS SELECT * FROM t1;
258 SHOW CREATE TABLE t3;
259 SELECT * FROM t3 order by a;
260 CREATE TABLE t4 AS SELECT b,c,d FROM t1;
261 SHOW CREATE TABLE t4;
262 SELECT * FROM t4 order by b;
263 DROP TABLE t1,t2,t3,t4;
264
265 --echo # Bug#21025003:WL8149:ASSERTION `CTX->NUM_TO_DROP_FK
266 --echo # == HA_ALTER_INFO->ALTER_INFO-> FAILED
267 --echo #
268 CREATE TABLE t1 (
269 col1 int(11) DEFAULT NULL,
270 col2 int(11) DEFAULT NULL,
271 col3 int(11) DEFAULT NULL,
272 col4 int(11) DEFAULT NULL,
273 col5 int(11) GENERATED ALWAYS AS (col4 / col2) VIRTUAL,
274 col6 text
275 );
276 INSERT INTO t1(col1,col2,col3,col4,col6) VALUES(NULL,1,4,0,REPEAT(2,1000));
277 --error ER_CANT_DROP_FIELD_OR_KEY
278 ALTER TABLE t1 DROP PRIMARY KEY , ADD KEY idx ( col5, col2 );
279 DROP TABLE t1;
280 --echo # Bug#20949226:i CAN ASSIGN NON-DEFAULT() VALUE TO GENERATED COLUMN
281 --echo #
282 SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
283 CREATE TABLE t1 (c1 INT, c2 INT AS (c1 * 2)) SELECT 1 AS c1, 5 AS c2;
284 CREATE TABLE t2 (a int);
285 INSERT INTO t2 values(1);
286 DROP TABLE t1;
287 SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
288 CREATE TABLE t1 (c1 INT, c2 INT AS (c1 * 2)) SELECT 1 AS c1, a AS c2 from t2;
289 DROP TABLE t1;
290 CREATE TABLE t1 (c1 INT, c2 INT AS (c1 * 2)) SELECT 1 AS c1, 5;
291 SELECT * FROM t1;
292 DROP TABLE t1, t2;
293
294 if ($support_virtual_index)
295 {
296 --echo # Bug#21074624:i WL8149:SIG 11 INNOBASE_GET_COMPUTED_VALUE |
297 --echo # INNOBASE/HANDLER/HA_INNODB.CC:19082
298 CREATE TABLE t1 (
299 col1 int(11) NOT NULL,
300 col2 int(11) DEFAULT NULL,
301 col3 int(11) NOT NULL,
302 col4 int(11) DEFAULT NULL,
303 col5 int(11) GENERATED ALWAYS AS (col2 % col4) VIRTUAL,
304 col6 int(11) GENERATED ALWAYS AS (col3 + col3) VIRTUAL,
305 col7 int(11) GENERATED ALWAYS AS (col5 / col5) VIRTUAL,
306 col8 int(11) GENERATED ALWAYS AS (col6 / col5) VIRTUAL,
307 col9 text,
308 extra int(11) DEFAULT NULL,
309 KEY idx (col5)
310 );
311 INSERT INTO t1(col1,col2,col3,col4,col9,extra)
312 VALUES(0,6,3,4,REPEAT(4,1000),0);
313 ALTER TABLE t1 DROP COLUMN col1;
314 DROP TABLE t1;
315
316 --echo # Bug#21390605:VALGRIND ERROR ON DELETE FROM TABLE CONTAINING
317 --echo # AN INDEXED VIRTUAL COLUMN
318 CREATE TABLE t1 (
319 a INTEGER,
320 b INTEGER GENERATED ALWAYS AS (a) VIRTUAL,
321 c INTEGER GENERATED ALWAYS AS (b) VIRTUAL,
322 INDEX idx (b,c)
323 );
324 INSERT INTO t1 (a) VALUES (42);
325 DELETE FROM t1 WHERE c = 42;
326 DROP TABLE t1;
327 }
328
329 --echo # Bug#20757211: GENERATED COLUMNS: ALTER TABLE CRASHES
330 --echo # IN FIND_FIELD_IN_TABLE
331 --echo #
332 CREATE TABLE t1(a int);
333 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
334 ALTER TABLE t1 ADD COLUMN z int GENERATED ALWAYS AS
335 ( 1 NOT IN (SELECT 1 FROM t1 WHERE c0006) ) virtual;
336 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
337 CREATE TABLE t2(a int, b int as (1 NOT IN (SELECT 1 FROM t1 WHERE not_exist_col)));
338 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
339 CREATE TABLE t2(a int, b int as (1 NOT IN (SELECT 1 FROM dual)));
340 DROP TABLE t1;
341
342 if(! $testing_ndb) {
343 --echo # Bug#21142905: PARTITIONED GENERATED COLS -
344 --echo # !TABLE || (!TABLE->WRITE_SET || BITMAP_IS_SET
345 --echo #
346 CREATE TABLE t1 (
347 a int,
348 b int generated always as (a) virtual,
349 c int generated always as (b+a) virtual,
350 d int generated always as (b+a) virtual
351 ) PARTITION BY LINEAR HASH (b);
352 INSERT INTO t1(a) VALUES(0);
353 DELETE FROM t1 WHERE c=1;
354 DROP TABLE t1;
355 }
356
357 CREATE TABLE t1 (c CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin GENERATED ALWAYS AS ("foo bar"));
358 SHOW CREATE TABLE t1;
359 DROP TABLE t1;
360 CREATE TABLE t1 (i INT);
361 ALTER TABLE t1 ADD COLUMN c CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin GENERATED ALWAYS AS ("foo bar");
362 SHOW CREATE TABLE t1;
363 DROP TABLE t1;
364 --error ER_PARSE_ERROR
365 CREATE TABLE t1 (i INT COLLATE utf8_bin, c INT COLLATE utf8_bin GENERATED ALWAYS AS (10));
366
367 --echo # Check for a charset mismatch processing:
368
369 --echo # Bug #21469535: VALGRIND ERROR (CONDITIONAL JUMP) WHEN INSERT
370 --echo # ROWS INTO A PARTITIONED TABLE
371 --echo #
372 CREATE TABLE t1 (
373 id INT NOT NULL,
374 store_id INT NOT NULL,
375 x INT GENERATED ALWAYS AS (id + store_id)
376 )
377 PARTITION BY RANGE (store_id) (
378 PARTITION p0 VALUES LESS THAN (6),
379 PARTITION p1 VALUES LESS THAN (11),
380 PARTITION p2 VALUES LESS THAN (16),
381 PARTITION p3 VALUES LESS THAN (21)
382 );
383
384 INSERT INTO t1 VALUES(1, 2, default);
385 DROP TABLE t1;
386
387 --echo # Bug#21465626:ASSERT/CRASH ON DROPPING/ADDING VIRTUAL COLUMN
388 CREATE TABLE t (a int(11), b int(11),
389 c int(11) GENERATED ALWAYS AS (a+b) VIRTUAL,
390 d int(11) GENERATED ALWAYS AS (a+b) VIRTUAL);
391 INSERT INTO t(a,b) VALUES(1,2);
392 --enable_info
393 --echo # Mixed drop/add/rename virtual with non-virtual columns,
394 --echo # ALGORITHM=INPLACE is not supported for InnoDB
395 ALTER TABLE t DROP d, ADD e varchar(10);
396 ALTER TABLE t ADD d int, ADD f char(10) AS ('aaa');
397 ALTER TABLE t CHANGE d dd int, CHANGE f ff varchar(10) AS ('bbb');
398 --echo # Only drop/add/change virtual, inplace is supported for Innodb
399 ALTER TABLE t DROP c, DROP ff;
400 ALTER TABLE t ADD c int(11) as (a+b), ADD f varchar(10) as ('aaa');
401 ALTER TABLE t CHANGE c c int(11) as (a), CHANGE f f varchar(10) as('bbb');
402 --echo # Change order should be ALGORITHM=INPLACE on Innodb
403 ALTER TABLE t CHANGE c c int(11) as (a) after f;
404 ALTER TABLE t CHANGE b b int(11) after c;
405 --echo # TODO: Changing virtual column type should be ALGORITHM=INPLACE on InnoDB, current it goes only with COPY method
406 ALTER TABLE t CHANGE c c varchar(10) as ('a');
407 --echo # Changing stored column type is ALGORITHM=COPY
408 ALTER TABLE t CHANGE dd d varchar(10);
409 if ($support_virtual_index)
410 {
411 # no RENAME INDEX yet
412 #ALTER TABLE t ADD INDEX idx(a), ADD INDEX idx1(c);
413 #ALTER TABLE t RENAME INDEX idx TO idx2, RENAME INDEX idx1 TO idx3;
414 #ALTER TABLE t DROP INDEX idx2, DROP INDEX idx3;
415 ALTER TABLE t ADD INDEX idx(c), ADD INDEX idx1(d);
416 ALTER TABLE t DROP INDEX idx, DROP INDEX idx1;
417 }
418 --disable_info
419 DROP TABLE t;
420
421 --echo # Bug#21854004: GCOLS:INNODB: FAILING ASSERTION: I < TABLE->N_DEF
422 CREATE TABLE t1(
423 col1 INTEGER PRIMARY KEY,
424 col2 INTEGER,
425 col3 INTEGER,
426 col4 INTEGER,
427 vgc1 INTEGER AS (col2 + col3) VIRTUAL,
428 sgc1 INTEGER AS (col2 - col3) STORED
429 );
430
431 INSERT INTO t1(col1, col2, col3) VALUES
432 (1, 10, 100), (2, 20, 200);
433
434 SELECT * FROM t1 order by col1;
435
436 # Change expression of a virtual generated column
437 ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 * col3) VIRTUAL;
438 SELECT * FROM t1 order by col1;
439
440 # Change expression of a stored generated column
441 ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 / col3) STORED;
442 SELECT * FROM t1 order by col1;
443
444 ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 + col3) VIRTUAL;
445 ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 - col3) STORED;
446
447 if ($support_virtual_index)
448 {
449 ALTER TABLE t1 ADD INDEX vgc1 (vgc1);
450 }
451 ALTER TABLE t1 ADD INDEX sgc1 (sgc1);
452
453 if ($support_virtual_index)
454 {
455 # Change expression of a virtual generated column, with index
456 ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 * col3) VIRTUAL;
457 SELECT * FROM t1 order by col1;
458 SELECT vgc1 FROM t1 order by vgc1;
459 }
460
461 # Change expression of a stored generated column, with index
462 ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 / col3) STORED;
463 SELECT * FROM t1 order by col1;
464 SELECT sgc1 FROM t1 order by sgc1;
465
466 if ($support_virtual_index)
467 {
468 ALTER TABLE t1 DROP INDEX vgc1;
469 }
470 ALTER TABLE t1 DROP INDEX sgc1;
471
472 if ($support_virtual_index)
473 {
474 ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 + col3) VIRTUAL;
475 ALTER TABLE t1 ADD UNIQUE INDEX vgc1 (vgc1);
476 }
477 ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 - col3) STORED;
478 ALTER TABLE t1 ADD UNIQUE INDEX sgc1 (sgc1);
479
480 # Change expression of a virtual generated column, with unique index
481 if ($support_virtual_index)
482 {
483 --error ER_DUP_ENTRY
484 ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 / col3) VIRTUAL;
485 }
486 --error ER_DUP_ENTRY
487 ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 / col3) STORED;
488
489 ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 * col3) VIRTUAL;
490 SELECT * FROM t1 order by col1;
491 SELECT vgc1 FROM t1 order by col1;
492
493 ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 * col3) STORED;
494 SELECT * FROM t1 order by col1;
495 SELECT sgc1 FROM t1 order by sgc1;
496
497 # Change virtual generated column to become stored
498 --error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN
499 ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 * col3) STORED;
500
501 # Change stored generated column to become virtual
502 --error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN
503 ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 / col3) VIRTUAL;
504
505 # Change base column to become stored generated column:
506 ALTER TABLE t1 MODIFY COLUMN col4 INTEGER AS (col1 + col2 + col3) STORED;
507 SELECT * FROM t1 order by col1;
508
509 # Change stored generated column to become base column:
510 ALTER TABLE t1 MODIFY COLUMN col4 INTEGER;
511 SELECT * FROM t1 order by col1;
512
513 DROP TABLE t1;
514
515 if ($support_virtual_index)
516 {
517 --echo #
518 --echo # bug#22018979: RECORD NOT FOUND ON UPDATE,
519 --echo # VIRTUAL COLUMN, ASSERTION 0
520 --disable_warnings
521 SET @sql_mode_save= @@sql_mode;
522 SET sql_mode= 'ANSI';
523 CREATE TABLE t1 (
524 a INT,
525 b VARCHAR(10),
526 c CHAR(3) GENERATED ALWAYS AS (substr(b,1,3)) VIRTUAL,
527 PRIMARY KEY (a),
528 KEY c(c)
529 );
530 INSERT INTO t1(a, b) values(1, 'bbbb'), (2, 'cc');
531 SHOW CREATE TABLE t1;
532 SELECT * FROM t1 order by a;
533
534 SET sql_mode= '';
535 FLUSH TABLE t1;
536 SHOW CREATE TABLE t1;
537 SELECT * FROM t1 order by a;
538 DELETE FROM t1 where a= 2;
539
540 SET sql_mode= @sql_mode_save;
541 DROP TABLE t1;
542 --enable_warnings
543 }
544
545
546 --echo #
547 --echo # Bug#22680839: DEFAULT IS NOT DETERMINISTIC AND SHOULD NOT BE
548 --echo # ALLOWED IN GENERATED COLUMNS
549 --echo #
550 if ($support_virtual_index)
551 {
552 CREATE TABLE tzz(a INT DEFAULT 5,
553 gc1 INT AS (a+DEFAULT(a)) VIRTUAL,
554 gc2 INT AS (a+DEFAULT(a)) STORED,
555 KEY k1(gc1));
556 INSERT INTO tzz(A) VALUES (1);
557 SELECT * FROM tzz;
558 SELECT gc1 FROM tzz;
559
560 ALTER TABLE tzz MODIFY COLUMN a INT DEFAULT 6;
561 SELECT * FROM tzz;
562 SELECT gc1 FROM tzz;
563 DROP TABLE tzz;
564 }
565
566 --echo # Test 1: ALTER DEFAULT
567 --echo #
568 CREATE TABLE t1(a INT PRIMARY KEY DEFAULT 5,
569 b INT AS (1 + DEFAULT(a)) STORED,
570 c INT AS (1 + DEFAULT(a)) VIRTUAL);
571 INSERT INTO t1 VALUES ();
572 --disable_warnings
573 # Check how many rows are accessed: >0 = COPY
574 --enable_info
575 ALTER TABLE t1 ALTER COLUMN a SET DEFAULT 7;
576 ALTER TABLE t1 MODIFY COLUMN a INT DEFAULT 8;
577 ALTER TABLE t1 CHANGE COLUMN a a DOUBLE DEFAULT 5;
578 --disable_info
579 DROP TABLE t1;
580
581 --echo # Test 2: ALTER DEFAULT + ADD GCOL
582 --echo #
583 CREATE TABLE t1(a INT PRIMARY KEY DEFAULT 5);
584 INSERT INTO t1 VALUES();
585 --enable_info
586 ALTER TABLE t1 ALTER COLUMN a SET DEFAULT 7,
587 ADD COLUMN b1 INT AS (1 + DEFAULT(a)) STORED;
588 ALTER TABLE t1 ALTER COLUMN a SET DEFAULT 7,
589 ADD COLUMN c1 INT AS (1 + DEFAULT(a)) VIRTUAL;
590 --disable_info
591 # Check how many rows are accessed: >0 = COPY
592 --enable_info
593 ALTER TABLE t1 ALTER COLUMN a SET DEFAULT 7,
594 ADD COLUMN b INT AS (1 + DEFAULT(a)) STORED,
595 ADD COLUMN c INT AS (1 + DEFAULT(a)) VIRTUAL;
596 --disable_info
597 DROP TABLE t1;
598 --enable_warnings
599
600 --echo #
601 --echo # MDEV-26262 frm is corrupted after ER_EXPRESSION_REFERS_TO_UNINIT_FIELD
602 --echo #
603
604 --error ER_EXPRESSION_REFERS_TO_UNINIT_FIELD
605 CREATE TABLE MDEV_26262 (a INT,b INT AS (b) VIRTUAL);
606
607 --let SEARCH_FILE=$MYSQLTEST_VARDIR/log/mysqld.1.err
608 --let SEARCH_PATTERN=Incorrect information in file: './test/MDEV_26262.frm'
609 --source include/search_pattern_in_file.inc