SET @@session.default_storage_engine = 'MyISAM'; # # Section 1. Wrong column definition options # - DEFAULT # - AUTO_INCREMENT # NOT NULL create table t1 (a int, b int generated always as (a+1) virtual not null); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'not null)' at line 1 create table t1 (a int, b int generated always as (a+1) stored not null); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'not null)' at line 1 create table t1 (a int); alter table t1 add column b int generated always as (a+1) virtual not null; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'not null' at line 1 drop table t1; create table t1 (a int, b int generated always as (a+1) virtual null); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'null)' at line 1 create table t1 (a int); alter table t1 add column b int generated always as (a+1) virtual null; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'null' at line 1 drop table t1; # Added columns mixed with virtual GC and other columns create table t1 (a int); insert into t1 values(1); alter table t1 add column (b int generated always as (a+1) virtual, c int); affected rows: 1 info: Records: 1 Duplicates: 0 Warnings: 0 alter table t1 add column (d int, e int generated always as (a+1) virtual); affected rows: 1 info: Records: 1 Duplicates: 0 Warnings: 0 alter table t1 add column (f int generated always as (a+1) virtual, g int as(5) stored); affected rows: 1 info: Records: 1 Duplicates: 0 Warnings: 0 alter table t1 add column (h int generated always as (a+1) virtual, i int as(5) virtual); affected rows: 1 info: Records: 1 Duplicates: 0 Warnings: 0 drop table t1; # DEFAULT create table t1 (a int, b int generated always as (a+1) virtual default 0); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'default 0)' at line 1 create table t1 (a int); alter table t1 add column b int generated always as (a+1) virtual default 0; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'default 0' at line 1 drop table t1; # AUTO_INCREMENT create table t1 (a int, b int generated always as (a+1) virtual AUTO_INCREMENT); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AUTO_INCREMENT)' at line 1 create table t1 (a int); alter table t1 add column b int generated always as (a+1) virtual AUTO_INCREMENT; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AUTO_INCREMENT' at line 1 drop table t1; # [PRIMARY] KEY create table t1 (a int, b int generated always as (a+1) virtual key); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'key)' at line 1 create table t1 (a int, b int generated always as (a+1) stored key); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'key)' at line 1 create table t1 (a int, b int generated always as (a+1) virtual primary key); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'primary key)' at line 1 create table t1 (a int, b int generated always as (a+1) stored primary key); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'primary key)' at line 1 create table t1 (a int); alter table t1 add column b int generated always as (a+1) virtual key; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'key' at line 1 alter table t1 add column b int generated always as (a+1) stored key; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'key' at line 1 alter table t1 add column c int generated always as (a+2) virtual primary key; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'primary key' at line 1 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci alter table t1 add column c int generated always as (a+2) stored primary key; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'primary key' at line 1 drop table t1; # Section 2. Other column definition options # - COMMENT # - REFERENCES (only syntax testing here) # - STORED (only systax testing here) create table t1 (a int, b int generated always as (a % 2) virtual comment 'my comment'); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) VIRTUAL COMMENT 'my comment' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci describe t1; Field Type Null Key Default Extra a int(11) YES NULL b int(11) YES NULL VIRTUAL GENERATED drop table t1; create table t1 (a int, b int generated always as (a % 2) virtual); alter table t1 modify b int generated always as (a % 2) virtual comment 'my comment'; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) VIRTUAL COMMENT 'my comment' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci describe t1; Field Type Null Key Default Extra a int(11) YES NULL b int(11) YES NULL VIRTUAL GENERATED insert into t1 (a) values (1); select * from t1; a b 1 1 insert into t1 values (2,default); select a,b from t1 order by a; a b 1 1 2 0 create table t2 like t1; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a` int(11) DEFAULT NULL, `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) VIRTUAL COMMENT 'my comment' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci describe t2; Field Type Null Key Default Extra a int(11) YES NULL b int(11) YES NULL VIRTUAL GENERATED insert into t2 (a) values (1); select * from t2; a b 1 1 insert into t2 values (2,default); select a,b from t2 order by a; a b 1 1 2 0 drop table t2; drop table t1; create table t1 (a int, b int generated always as (a % 2) stored); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) STORED ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci describe t1; Field Type Null Key Default Extra a int(11) YES NULL b int(11) YES NULL STORED GENERATED insert into t1 (a) values (1); select * from t1; a b 1 1 insert into t1 values (2,default); select a,b from t1 order by a; a b 1 1 2 0 drop table t1; create table t2 (a int); create table t1 (a int, b int generated always as (a % 2) stored references t2(a)); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1 create table t1 (a int, b int generated always as (a % 2) stored); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) STORED ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci drop table t1; create table t1 (a int, b int generated always as (a % 2) virtual); alter table t1 modify b int generated always as (a % 2) stored references t2(a); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'references t2(a)' at line 1 alter table t1 modify b int generated always as (a % 2) stored; ERROR HY000: This is not yet supported for generated columns show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) VIRTUAL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci drop table t1; drop table t2; FK options create table t1(a int, b int as (a % 2), c int as (a) stored); create table t2 (a int); alter table t1 add constraint foreign key fk(d) references t2(a); ERROR 42000: Key column 'd' doesn't exist in table alter table t1 add constraint foreign key fk(c) references t2(a) on delete set null; ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column alter table t1 add constraint foreign key fk(c) references t2(a) on update set null; ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column alter table t1 add constraint foreign key fk(c) references t2(a) on update cascade; ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column drop table t1; drop table t2; Generated always is optional create table t1 (a int, b int as (a % 2) virtual); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) VIRTUAL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci describe t1; Field Type Null Key Default Extra a int(11) YES NULL b int(11) YES NULL VIRTUAL GENERATED drop table t1; create table t1 (a int, b int as (a % 2) stored); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) STORED ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci describe t1; Field Type Null Key Default Extra a int(11) YES NULL b int(11) YES NULL STORED GENERATED drop table t1; Default should be non-stored column create table t1 (a int, b int as (a % 2)); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) VIRTUAL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci describe t1; Field Type Null Key Default Extra a int(11) YES NULL b int(11) YES NULL VIRTUAL GENERATED drop table t1; Expression can be constant create table t1 (a int, b int as (5 * 2)); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) GENERATED ALWAYS AS (5 * 2) VIRTUAL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci describe t1; Field Type Null Key Default Extra a int(11) YES NULL b int(11) YES NULL VIRTUAL GENERATED drop table t1; Test generated columns referencing other generated columns create table t1 (a int unique, b int generated always as(-a) virtual, c int generated always as (b + 1) virtual); insert into t1 (a) values (1), (2); select * from t1; a b c 1 -1 0 2 -2 -1 insert into t1(a) values (1) on duplicate key update a=3; select * from t1; a b c 2 -2 -1 3 -3 -2 update t1 set a=4 where a=2; select * from t1; a b c 3 -3 -2 4 -4 -3 drop table t1; create table t1 (a int, b int generated always as(-b) virtual, c int generated always as (b + 1) virtual); ERROR 01000: Expression for field `b` is referring to uninitialized field `b` create table t1 (a int, b int generated always as(-c) virtual, c int generated always as (b + 1) virtual); ERROR 01000: Expression for field `b` is referring to uninitialized field `c` 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); ERROR HY000: Function or expression 'AUTO_INCREMENT' cannot be used in the GENERATED ALWAYS AS clause of `pk` 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); ERROR HY000: Function or expression 'AUTO_INCREMENT' cannot be used in the GENERATED ALWAYS AS clause of `pk` create table t1 (pk int auto_increment primary key, col_int_nokey int generated always as (pk + col_int_key), col_int_key int); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `pk` int(11) NOT NULL AUTO_INCREMENT, `col_int_nokey` int(11) GENERATED ALWAYS AS (`pk` + `col_int_key`) VIRTUAL, `col_int_key` int(11) DEFAULT NULL, PRIMARY KEY (`pk`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci insert t1 (col_int_key) values (10),(20),(30); select * from t1; pk col_int_nokey col_int_key 1 11 10 2 22 20 3 33 30 drop table t1; # Bug#20339347: FAIL TO USE CREATE ....SELECT STATEMENT TO CREATE A NEW TABLE create table t1 (a int, b int generated always as(-a) virtual, c int generated always as (b + 1) stored); insert into t1(a) values(1),(2); create table tt as select * from t1; select * from t1 order by a; a b c 1 -1 0 2 -2 -1 select * from tt order by a; a b c 1 -1 0 2 -2 -1 drop table t1,tt; # Bug#20745142: GENERATED COLUMNS: ASSERTION FAILED: # THD->CHANGE_LIST.IS_EMPTY() # CREATE TABLE t1(a bigint AS (a between 1 and 1)); ERROR 01000: Expression for field `a` is referring to uninitialized field `a` # Bug#20757211: GENERATED COLUMNS: ALTER TABLE CRASHES # IN FIND_FIELD_IN_TABLE # CREATE TABLE t1(a int); ALTER TABLE t1 ADD COLUMN z int GENERATED ALWAYS AS ( 1 NOT IN (SELECT 1 FROM t1 WHERE c0006) ) virtual; ERROR HY000: Function or expression 'select ...' cannot be used in the GENERATED ALWAYS AS clause of `z` DROP TABLE t1; # Bug#20566243: ERROR WHILE DOING CREATE TABLE T1 SELECT (QUERY ON GC COLUMNS) CREATE TABLE t1(a int, b int as (a + 1), c varchar(12) as ("aaaabb") stored, d blob as (c)); INSERT INTO t1(a) VALUES(1),(3); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) GENERATED ALWAYS AS (`a` + 1) VIRTUAL, `c` varchar(12) GENERATED ALWAYS AS ('aaaabb') STORED, `d` blob GENERATED ALWAYS AS (`c`) VIRTUAL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci SELECT * FROM t1 order by a; a b c d 1 2 aaaabb aaaabb 3 4 aaaabb aaaabb CREATE TABLE t2 LIKE t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `a` int(11) DEFAULT NULL, `b` int(11) GENERATED ALWAYS AS (`a` + 1) VIRTUAL, `c` varchar(12) GENERATED ALWAYS AS ('aaaabb') STORED, `d` blob GENERATED ALWAYS AS (`c`) VIRTUAL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci CREATE TABLE t3 AS SELECT * FROM t1; SHOW CREATE TABLE t3; Table Create Table t3 CREATE TABLE `t3` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` varchar(12) DEFAULT NULL, `d` blob DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci SELECT * FROM t3 order by a; a b c d 1 2 aaaabb aaaabb 3 4 aaaabb aaaabb CREATE TABLE t4 AS SELECT b,c,d FROM t1; SHOW CREATE TABLE t4; Table Create Table t4 CREATE TABLE `t4` ( `b` int(11) DEFAULT NULL, `c` varchar(12) DEFAULT NULL, `d` blob DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci SELECT * FROM t4 order by b; b c d 2 aaaabb aaaabb 4 aaaabb aaaabb DROP TABLE t1,t2,t3,t4; # Bug#21025003:WL8149:ASSERTION `CTX->NUM_TO_DROP_FK # == HA_ALTER_INFO->ALTER_INFO-> FAILED # CREATE TABLE t1 ( col1 int(11) DEFAULT NULL, col2 int(11) DEFAULT NULL, col3 int(11) DEFAULT NULL, col4 int(11) DEFAULT NULL, col5 int(11) GENERATED ALWAYS AS (col4 / col2) VIRTUAL, col6 text ); INSERT INTO t1(col1,col2,col3,col4,col6) VALUES(NULL,1,4,0,REPEAT(2,1000)); ALTER TABLE t1 DROP PRIMARY KEY , ADD KEY idx ( col5, col2 ); ERROR 42000: Can't DROP INDEX `PRIMARY`; check that it exists DROP TABLE t1; # Bug#20949226:i CAN ASSIGN NON-DEFAULT() VALUE TO GENERATED COLUMN # SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR CREATE TABLE t1 (c1 INT, c2 INT AS (c1 * 2)) SELECT 1 AS c1, 5 AS c2; Warnings: Warning 1906 The value specified for generated column 'c2' in table 't1' has been ignored CREATE TABLE t2 (a int); INSERT INTO t2 values(1); DROP TABLE t1; SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR CREATE TABLE t1 (c1 INT, c2 INT AS (c1 * 2)) SELECT 1 AS c1, a AS c2 from t2; Warnings: Warning 1906 The value specified for generated column 'c2' in table 't1' has been ignored DROP TABLE t1; CREATE TABLE t1 (c1 INT, c2 INT AS (c1 * 2)) SELECT 1 AS c1, 5; SELECT * FROM t1; c2 c1 5 2 1 5 DROP TABLE t1, t2; # Bug#21074624:i WL8149:SIG 11 INNOBASE_GET_COMPUTED_VALUE | # INNOBASE/HANDLER/HA_INNODB.CC:19082 CREATE TABLE t1 ( col1 int(11) NOT NULL, col2 int(11) DEFAULT NULL, col3 int(11) NOT NULL, col4 int(11) DEFAULT NULL, col5 int(11) GENERATED ALWAYS AS (col2 % col4) VIRTUAL, col6 int(11) GENERATED ALWAYS AS (col3 + col3) VIRTUAL, col7 int(11) GENERATED ALWAYS AS (col5 / col5) VIRTUAL, col8 int(11) GENERATED ALWAYS AS (col6 / col5) VIRTUAL, col9 text, extra int(11) DEFAULT NULL, KEY idx (col5) ); INSERT INTO t1(col1,col2,col3,col4,col9,extra) VALUES(0,6,3,4,REPEAT(4,1000),0); ALTER TABLE t1 DROP COLUMN col1; DROP TABLE t1; # Bug#21390605:VALGRIND ERROR ON DELETE FROM TABLE CONTAINING # AN INDEXED VIRTUAL COLUMN CREATE TABLE t1 ( a INTEGER, b INTEGER GENERATED ALWAYS AS (a) VIRTUAL, c INTEGER GENERATED ALWAYS AS (b) VIRTUAL, INDEX idx (b,c) ); INSERT INTO t1 (a) VALUES (42); DELETE FROM t1 WHERE c = 42; DROP TABLE t1; # Bug#20757211: GENERATED COLUMNS: ALTER TABLE CRASHES # IN FIND_FIELD_IN_TABLE # CREATE TABLE t1(a int); ALTER TABLE t1 ADD COLUMN z int GENERATED ALWAYS AS ( 1 NOT IN (SELECT 1 FROM t1 WHERE c0006) ) virtual; ERROR HY000: Function or expression 'select ...' cannot be used in the GENERATED ALWAYS AS clause of `z` CREATE TABLE t2(a int, b int as (1 NOT IN (SELECT 1 FROM t1 WHERE not_exist_col))); ERROR HY000: Function or expression 'select ...' cannot be used in the GENERATED ALWAYS AS clause of `b` CREATE TABLE t2(a int, b int as (1 NOT IN (SELECT 1 FROM dual))); ERROR HY000: Function or expression 'select ...' cannot be used in the GENERATED ALWAYS AS clause of `b` DROP TABLE t1; # Bug#21142905: PARTITIONED GENERATED COLS - # !TABLE || (!TABLE->WRITE_SET || BITMAP_IS_SET # CREATE TABLE t1 ( a int, b int generated always as (a) virtual, c int generated always as (b+a) virtual, d int generated always as (b+a) virtual ) PARTITION BY LINEAR HASH (b); INSERT INTO t1(a) VALUES(0); DELETE FROM t1 WHERE c=1; DROP TABLE t1; CREATE TABLE t1 (c CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin GENERATED ALWAYS AS ("foo bar")); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c` char(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin GENERATED ALWAYS AS ('foo bar') VIRTUAL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1; CREATE TABLE t1 (i INT); ALTER TABLE t1 ADD COLUMN c CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin GENERATED ALWAYS AS ("foo bar"); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `i` int(11) DEFAULT NULL, `c` char(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin GENERATED ALWAYS AS ('foo bar') VIRTUAL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1; CREATE TABLE t1 (i INT COLLATE utf8_bin, c INT COLLATE utf8_bin GENERATED ALWAYS AS (10)); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'GENERATED ALWAYS AS (10))' at line 1 # Check for a charset mismatch processing: # Bug #21469535: VALGRIND ERROR (CONDITIONAL JUMP) WHEN INSERT # ROWS INTO A PARTITIONED TABLE # CREATE TABLE t1 ( id INT NOT NULL, store_id INT NOT NULL, x INT GENERATED ALWAYS AS (id + store_id) ) PARTITION BY RANGE (store_id) ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11), PARTITION p2 VALUES LESS THAN (16), PARTITION p3 VALUES LESS THAN (21) ); INSERT INTO t1 VALUES(1, 2, default); DROP TABLE t1; # Bug#21465626:ASSERT/CRASH ON DROPPING/ADDING VIRTUAL COLUMN CREATE TABLE t (a int(11), b int(11), c int(11) GENERATED ALWAYS AS (a+b) VIRTUAL, d int(11) GENERATED ALWAYS AS (a+b) VIRTUAL); INSERT INTO t(a,b) VALUES(1,2); # Mixed drop/add/rename virtual with non-virtual columns, # ALGORITHM=INPLACE is not supported for InnoDB ALTER TABLE t DROP d, ADD e varchar(10); affected rows: 1 info: Records: 1 Duplicates: 0 Warnings: 0 ALTER TABLE t ADD d int, ADD f char(10) AS ('aaa'); affected rows: 1 info: Records: 1 Duplicates: 0 Warnings: 0 ALTER TABLE t CHANGE d dd int, CHANGE f ff varchar(10) AS ('bbb'); affected rows: 1 info: Records: 1 Duplicates: 0 Warnings: 0 # Only drop/add/change virtual, inplace is supported for Innodb ALTER TABLE t DROP c, DROP ff; affected rows: 1 info: Records: 1 Duplicates: 0 Warnings: 0 ALTER TABLE t ADD c int(11) as (a+b), ADD f varchar(10) as ('aaa'); affected rows: 1 info: Records: 1 Duplicates: 0 Warnings: 0 ALTER TABLE t CHANGE c c int(11) as (a), CHANGE f f varchar(10) as('bbb'); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 # Change order should be ALGORITHM=INPLACE on Innodb ALTER TABLE t CHANGE c c int(11) as (a) after f; affected rows: 1 info: Records: 1 Duplicates: 0 Warnings: 0 ALTER TABLE t CHANGE b b int(11) after c; affected rows: 1 info: Records: 1 Duplicates: 0 Warnings: 0 # TODO: Changing virtual column type should be ALGORITHM=INPLACE on InnoDB, current it goes only with COPY method ALTER TABLE t CHANGE c c varchar(10) as ('a'); affected rows: 1 info: Records: 1 Duplicates: 0 Warnings: 0 # Changing stored column type is ALGORITHM=COPY ALTER TABLE t CHANGE dd d varchar(10); affected rows: 1 info: Records: 1 Duplicates: 0 Warnings: 0 ALTER TABLE t ADD INDEX idx(c), ADD INDEX idx1(d); affected rows: 1 info: Records: 1 Duplicates: 0 Warnings: 0 ALTER TABLE t DROP INDEX idx, DROP INDEX idx1; affected rows: 1 info: Records: 1 Duplicates: 0 Warnings: 0 DROP TABLE t; # Bug#21854004: GCOLS:INNODB: FAILING ASSERTION: I < TABLE->N_DEF CREATE TABLE t1( col1 INTEGER PRIMARY KEY, col2 INTEGER, col3 INTEGER, col4 INTEGER, vgc1 INTEGER AS (col2 + col3) VIRTUAL, sgc1 INTEGER AS (col2 - col3) STORED ); INSERT INTO t1(col1, col2, col3) VALUES (1, 10, 100), (2, 20, 200); SELECT * FROM t1 order by col1; col1 col2 col3 col4 vgc1 sgc1 1 10 100 NULL 110 -90 2 20 200 NULL 220 -180 ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 * col3) VIRTUAL; SELECT * FROM t1 order by col1; col1 col2 col3 col4 vgc1 sgc1 1 10 100 NULL 1000 -90 2 20 200 NULL 4000 -180 ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 / col3) STORED; SELECT * FROM t1 order by col1; col1 col2 col3 col4 vgc1 sgc1 1 10 100 NULL 1000 0 2 20 200 NULL 4000 0 ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 + col3) VIRTUAL; ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 - col3) STORED; ALTER TABLE t1 ADD INDEX vgc1 (vgc1); ALTER TABLE t1 ADD INDEX sgc1 (sgc1); ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 * col3) VIRTUAL; SELECT * FROM t1 order by col1; col1 col2 col3 col4 vgc1 sgc1 1 10 100 NULL 1000 -90 2 20 200 NULL 4000 -180 SELECT vgc1 FROM t1 order by vgc1; vgc1 1000 4000 ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 / col3) STORED; SELECT * FROM t1 order by col1; col1 col2 col3 col4 vgc1 sgc1 1 10 100 NULL 1000 0 2 20 200 NULL 4000 0 SELECT sgc1 FROM t1 order by sgc1; sgc1 0 0 ALTER TABLE t1 DROP INDEX vgc1; ALTER TABLE t1 DROP INDEX sgc1; ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 + col3) VIRTUAL; ALTER TABLE t1 ADD UNIQUE INDEX vgc1 (vgc1); ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 - col3) STORED; ALTER TABLE t1 ADD UNIQUE INDEX sgc1 (sgc1); ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 / col3) VIRTUAL; ERROR 23000: Duplicate entry '0' for key 'vgc1' ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 / col3) STORED; ERROR 23000: Duplicate entry '0' for key 'sgc1' ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 * col3) VIRTUAL; SELECT * FROM t1 order by col1; col1 col2 col3 col4 vgc1 sgc1 1 10 100 NULL 1000 -90 2 20 200 NULL 4000 -180 SELECT vgc1 FROM t1 order by col1; vgc1 1000 4000 ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 * col3) STORED; SELECT * FROM t1 order by col1; col1 col2 col3 col4 vgc1 sgc1 1 10 100 NULL 1000 1000 2 20 200 NULL 4000 4000 SELECT sgc1 FROM t1 order by sgc1; sgc1 1000 4000 ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 * col3) STORED; ERROR HY000: This is not yet supported for generated columns ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 / col3) VIRTUAL; ERROR HY000: This is not yet supported for generated columns ALTER TABLE t1 MODIFY COLUMN col4 INTEGER AS (col1 + col2 + col3) STORED; SELECT * FROM t1 order by col1; col1 col2 col3 col4 vgc1 sgc1 1 10 100 111 1000 1000 2 20 200 222 4000 4000 ALTER TABLE t1 MODIFY COLUMN col4 INTEGER; SELECT * FROM t1 order by col1; col1 col2 col3 col4 vgc1 sgc1 1 10 100 111 1000 1000 2 20 200 222 4000 4000 DROP TABLE t1; # # bug#22018979: RECORD NOT FOUND ON UPDATE, # VIRTUAL COLUMN, ASSERTION 0 SET @sql_mode_save= @@sql_mode; SET sql_mode= 'ANSI'; CREATE TABLE t1 ( a INT, b VARCHAR(10), c CHAR(3) GENERATED ALWAYS AS (substr(b,1,3)) VIRTUAL, PRIMARY KEY (a), KEY c(c) ); INSERT INTO t1(a, b) values(1, 'bbbb'), (2, 'cc'); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE "t1" ( "a" int(11) NOT NULL, "b" varchar(10) DEFAULT NULL, "c" char(3) GENERATED ALWAYS AS (substr("b",1,3)) VIRTUAL, PRIMARY KEY ("a"), KEY "c" ("c") ) SELECT * FROM t1 order by a; a b c 1 bbbb bbb 2 cc cc SET sql_mode= ''; FLUSH TABLE t1; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` varchar(10) DEFAULT NULL, `c` char(3) GENERATED ALWAYS AS (substr(`b`,1,3)) VIRTUAL, PRIMARY KEY (`a`), KEY `c` (`c`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci SELECT * FROM t1 order by a; a b c 1 bbbb bbb 2 cc cc DELETE FROM t1 where a= 2; SET sql_mode= @sql_mode_save; DROP TABLE t1; # # Bug#22680839: DEFAULT IS NOT DETERMINISTIC AND SHOULD NOT BE # ALLOWED IN GENERATED COLUMNS # CREATE TABLE tzz(a INT DEFAULT 5, gc1 INT AS (a+DEFAULT(a)) VIRTUAL, gc2 INT AS (a+DEFAULT(a)) STORED, KEY k1(gc1)); INSERT INTO tzz(A) VALUES (1); SELECT * FROM tzz; a gc1 gc2 1 6 6 SELECT gc1 FROM tzz; gc1 6 ALTER TABLE tzz MODIFY COLUMN a INT DEFAULT 6; SELECT * FROM tzz; a gc1 gc2 1 7 7 SELECT gc1 FROM tzz; gc1 7 DROP TABLE tzz; # Test 1: ALTER DEFAULT # CREATE TABLE t1(a INT PRIMARY KEY DEFAULT 5, b INT AS (1 + DEFAULT(a)) STORED, c INT AS (1 + DEFAULT(a)) VIRTUAL); INSERT INTO t1 VALUES (); ALTER TABLE t1 ALTER COLUMN a SET DEFAULT 7; affected rows: 1 info: Records: 1 Duplicates: 0 Warnings: 0 ALTER TABLE t1 MODIFY COLUMN a INT DEFAULT 8; affected rows: 1 info: Records: 1 Duplicates: 0 Warnings: 0 ALTER TABLE t1 CHANGE COLUMN a a DOUBLE DEFAULT 5; affected rows: 1 info: Records: 1 Duplicates: 0 Warnings: 0 DROP TABLE t1; # Test 2: ALTER DEFAULT + ADD GCOL # CREATE TABLE t1(a INT PRIMARY KEY DEFAULT 5); INSERT INTO t1 VALUES(); ALTER TABLE t1 ALTER COLUMN a SET DEFAULT 7, ADD COLUMN b1 INT AS (1 + DEFAULT(a)) STORED; affected rows: 1 info: Records: 1 Duplicates: 0 Warnings: 0 ALTER TABLE t1 ALTER COLUMN a SET DEFAULT 7, ADD COLUMN c1 INT AS (1 + DEFAULT(a)) VIRTUAL; affected rows: 1 info: Records: 1 Duplicates: 0 Warnings: 0 ALTER TABLE t1 ALTER COLUMN a SET DEFAULT 7, ADD COLUMN b INT AS (1 + DEFAULT(a)) STORED, ADD COLUMN c INT AS (1 + DEFAULT(a)) VIRTUAL; affected rows: 1 info: Records: 1 Duplicates: 0 Warnings: 0 DROP TABLE t1; # # MDEV-26262 frm is corrupted after ER_EXPRESSION_REFERS_TO_UNINIT_FIELD # CREATE TABLE MDEV_26262 (a INT,b INT AS (b) VIRTUAL); ERROR 01000: Expression for field `b` is referring to uninitialized field `b` NOT FOUND /Incorrect information in file: './test/MDEV_26262.frm'/ in mysqld.1.err DROP VIEW IF EXISTS v1,v2; DROP TABLE IF EXISTS t1,t2,t3; DROP PROCEDURE IF EXISTS p1; DROP FUNCTION IF EXISTS f1; DROP TRIGGER IF EXISTS trg1; DROP TRIGGER IF EXISTS trg2; set sql_warnings = 0;