SET @@session.storage_engine = 'MyISAM';
# Case 1. All non-stored columns.
# This scenario is currently impossible due to the fact that virtual columns
# with a constant expression are not allowed.
# Case 2. CREATE
#  - Column1: "real"
#  - Column 2: virtual non-stored
create table t1 (a int, b int as (-a));
insert into t1 values (1,default);
select * from t1;
a	b
1	-1
insert into t1 values (2,default);
select * from t1;
a	b
1	-1
2	-2
drop table t1;
# Case 3. CREATE
#  - Column1: "real"
#  - Column 2: virtual stored
create table t1 (a int, b int as (-a) persistent);
insert into t1 values (1,default);
select * from t1;
a	b
1	-1
insert into t1 values (2,default);
select * from t1;
a	b
1	-1
2	-2
drop table t1;
# Case 4. CREATE
#  - Column1: virtual non-stored
#  - Column2: "real"
create table t1 (a int as (-b), b int);
insert into t1 values (default,1);
select * from t1;
a	b
-1	1
insert into t1 values (default,2);
select * from t1;
a	b
-1	1
-2	2
drop table t1;
# Case 5. CREATE
#  - Column1: virtual stored
#  - Column2: "real"
create table t1 (a int as (-b) persistent, b int);
insert into t1 values (default,1);
select * from t1;
a	b
-1	1
insert into t1 values (default,2);
select * from t1;
a	b
-1	1
-2	2
drop table t1;
# Case 6. CREATE
#  - Column1: "real"
#  - Column2: virtual non-stored
#  - Column3: virtual stored
create table t1 (a int, b int as (-a), c int as (-a) persistent);
insert into t1 values (1,default,default);
select * from t1;
a	b	c
1	-1	-1
insert into t1 values (2,default,default);
select * from t1;
a	b	c
1	-1	-1
2	-2	-2
drop table t1;
# Case 7. ALTER. Modify virtual stored -> virtual non-stored
create table t1 (a int, b int as (a % 2) persistent);
alter table t1 modify b int as (a % 2);
ERROR HY000: This is not yet supported for computed columns
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) AS (a % 2) PERSISTENT
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
# Case 8. ALTER. Modify virtual non-stored -> virtual stored
create table t1 (a int, b int as (a % 2));
alter table t1 modify b int as (a % 2) persistent;
ERROR HY000: This is not yet supported for computed columns
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) AS (a % 2) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
# Case 9. CREATE LIKE
#  - Column1: "real"
#  - Column2: virtual non-stored
#  - Column3: virtual stored
create table t1 (a int, b int as (-a), c int as (-a) persistent);
create table t2 like t1;
insert into t2 values (1,default,default);
select * from t2;
a	b	c
1	-1	-1
insert into t2 values (2,default,default);
select * from t2;
a	b	c
1	-1	-1
2	-2	-2
drop table t2;
drop table t1;
# Case 10. ALTER. Dropping a virtual non-stored column.
#  - Column1: virtual non-stored
#  - Column2: "real"
create table t1 (a int as (-b), b int, c varchar(5));
insert into t1 values (default,1,'v1');
insert into t1 values (default,2,'v2');
select * from t1;
a	b	c
-1	1	v1
-2	2	v2
alter table t1 drop column a;
select * from t1;
b	c
1	v1
2	v2
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `b` int(11) DEFAULT NULL,
  `c` varchar(5) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
# Case 11. ALTER. Dropping a virtual stored column.
#  - Column1: virtual stored
#  - Column2: "real"
create table t1 (a int as (-b) persistent, b int, c char(5));
insert into t1 values (default,1,'v1');
insert into t1 values (default,2,'v2');
select * from t1;
a	b	c
-1	1	v1
-2	2	v2
alter table t1 drop column a;
select * from t1;
b	c
1	v1
2	v2
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `b` int(11) DEFAULT NULL,
  `c` char(5) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
# Case 12. ALTER. Adding a new virtual non-stored column.
create table t1 (a int, b datetime);
insert into t1 values (1,'2008-09-04');
insert into t1 values (2,'2008-09-05');
select * from t1;
a	b
1	2008-09-04 00:00:00
2	2008-09-05 00:00:00
alter table t1 add column c int as (dayofyear(b)) after a;
select * from t1;
a	c	b
1	248	2008-09-04 00:00:00
2	249	2008-09-05 00:00:00
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `c` int(11) AS (dayofyear(b)) VIRTUAL,
  `b` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
# Case 13. ALTER. Adding a new virtual stored column.
create table t1 (a int, b datetime);
insert into t1 values (1,'2008-09-04');
insert into t1 values (2,'2008-09-05');
select * from t1;
a	b
1	2008-09-04 00:00:00
2	2008-09-05 00:00:00
alter table t1 add column c int as (dayofyear(b)) persistent after a;
select * from t1;
a	c	b
1	248	2008-09-04 00:00:00
2	249	2008-09-05 00:00:00
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `c` int(11) AS (dayofyear(b)) PERSISTENT,
  `b` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
# Case 14. ALTER. Changing the expression of a virtual stored column.
create table t1 (a int, b datetime, c int as (week(b)) persistent);
insert into t1 values (1,'2008-09-04',default);
insert into t1 values (2,'2008-09-05',default);
select * from t1;
a	b	c
1	2008-09-04 00:00:00	35
2	2008-09-05 00:00:00	35
alter table t1 change column c c int as (week(b,1)) persistent;
select * from t1;
a	b	c
1	2008-09-04 00:00:00	36
2	2008-09-05 00:00:00	36
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` datetime DEFAULT NULL,
  `c` int(11) AS (week(b,1)) PERSISTENT
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
# Case 15. ALTER. Changing the expression of a virtual non-stored column.
create table t1 (a int, b datetime, c int as (week(b)));
insert into t1 values (1,'2008-09-04',default);
insert into t1 values (2,'2008-09-05',default);
select * from t1;
a	b	c
1	2008-09-04 00:00:00	35
2	2008-09-05 00:00:00	35
alter table t1 change column c c int as (week(b,1));
select * from t1;
a	b	c
1	2008-09-04 00:00:00	36
2	2008-09-05 00:00:00	36
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` datetime DEFAULT NULL,
  `c` int(11) AS (week(b,1)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;