SET @@session.storage_engine = 'MyISAM'; # # Section 1. Wrong column definition options # - NOT NULL # - NULL # - DEFAULT <value> # - AUTO_INCREMENT # - [PRIMARY] KEY # NOT NULL create table t1 (a int, b int as (a+1) 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 as (a+1) 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; # NULL create table t1 (a int, b int as (a+1) 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 as (a+1) 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; # DEFAULT create table t1 (a int, b int as (a+1) 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 as (a+1) 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 as (a+1) 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 as (a+1) 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 as (a+1) 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 as (a+1) 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 as (a+1) 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 as (a+1) 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 as (a % 2) 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` % 2) VIRTUAL COMMENT 'my comment' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 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)); alter table t1 modify b int as (a % 2) 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` % 2) VIRTUAL COMMENT 'my comment' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 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; 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` % 2) VIRTUAL COMMENT 'my comment' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 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; a b 1 1 2 0 drop table t2; drop table t1; create table t1 (a int, b int as (a % 2) persistent); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) GENERATED ALWAYS AS (`a` % 2) STORED ) ENGINE=MyISAM DEFAULT CHARSET=latin1 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; a b 1 1 2 0 drop table t1; create table t2 (a int); create table t1 (a int, b int as (a % 2) persistent references t2(a)); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) GENERATED ALWAYS AS (`a` % 2) STORED ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (a int, b int as (a % 2)); alter table t1 modify b int as (a % 2) persistent 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 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) GENERATED ALWAYS AS (`a` % 2) VIRTUAL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1;