1
################################################################################
2
# inc/vcol_non_stored_columns.inc #
5
# Ensure that MySQL behaviour is consistent irrelevant of #
6
# - the place of a non-stored column among other columns, #
7
# - the total number of non-stored fields. #
9
#------------------------------------------------------------------------------#
10
# Original Author: Andrey Zhakov #
11
# Original Date: 2008-09-04 #
12
# Change Author: Oleksandr Byelkin (Monty program Ab)
14
# Change: Syntax changed
15
################################################################################
17
--echo # Case 1. All non-stored columns.
18
--echo # This scenario is currently impossible due to the fact that virtual columns
19
--echo # with a constant expression are not allowed.
21
--echo # Case 2. CREATE
22
--echo # - Column1: "real"
23
--echo # - Column 2: virtual non-stored
24
create table t1 (a int, b int as (-a));
25
insert into t1 values (1,default);
27
insert into t1 values (2,default);
31
--echo # Case 3. CREATE
32
--echo # - Column1: "real"
33
--echo # - Column 2: virtual stored
34
create table t1 (a int, b int as (-a) persistent);
35
insert into t1 values (1,default);
37
insert into t1 values (2,default);
41
--echo # Case 4. CREATE
42
--echo # - Column1: virtual non-stored
43
--echo # - Column2: "real"
44
create table t1 (a int as (-b), b int);
45
insert into t1 values (default,1);
47
insert into t1 values (default,2);
51
--echo # Case 5. CREATE
52
--echo # - Column1: virtual stored
53
--echo # - Column2: "real"
54
create table t1 (a int as (-b) persistent, b int);
55
insert into t1 values (default,1);
57
insert into t1 values (default,2);
61
--echo # Case 6. CREATE
62
--echo # - Column1: "real"
63
--echo # - Column2: virtual non-stored
64
--echo # - Column3: virtual stored
65
create table t1 (a int, b int as (-a), c int as (-a) persistent);
66
insert into t1 values (1,default,default);
68
insert into t1 values (2,default,default);
72
--echo # Case 7. ALTER. Modify virtual stored -> virtual non-stored
73
create table t1 (a int, b int as (a % 2) persistent);
74
--error ER_UNSUPPORTED_ACTION_ON_VIRTUAL_COLUMN
75
alter table t1 modify b int as (a % 2);
79
--echo # Case 8. ALTER. Modify virtual non-stored -> virtual stored
80
create table t1 (a int, b int as (a % 2));
81
--error ER_UNSUPPORTED_ACTION_ON_VIRTUAL_COLUMN
82
alter table t1 modify b int as (a % 2) persistent;
86
--echo # Case 9. CREATE LIKE
87
--echo # - Column1: "real"
88
--echo # - Column2: virtual non-stored
89
--echo # - Column3: virtual stored
90
create table t1 (a int, b int as (-a), c int as (-a) persistent);
91
create table t2 like t1;
92
insert into t2 values (1,default,default);
94
insert into t2 values (2,default,default);
99
--echo # Case 10. ALTER. Dropping a virtual non-stored column.
100
--echo # - Column1: virtual non-stored
101
--echo # - Column2: "real"
102
create table t1 (a int as (-b), b int, c varchar(5));
103
insert into t1 values (default,1,'v1');
104
insert into t1 values (default,2,'v2');
106
alter table t1 drop column a;
108
show create table t1;
111
--echo # Case 11. ALTER. Dropping a virtual stored column.
112
--echo # - Column1: virtual stored
113
--echo # - Column2: "real"
114
create table t1 (a int as (-b) persistent, b int, c char(5));
115
insert into t1 values (default,1,'v1');
116
insert into t1 values (default,2,'v2');
118
alter table t1 drop column a;
120
show create table t1;
123
--echo # Case 12. ALTER. Adding a new virtual non-stored column.
124
create table t1 (a int, b datetime);
125
insert into t1 values (1,'2008-09-04');
126
insert into t1 values (2,'2008-09-05');
128
alter table t1 add column c int as (dayofyear(b)) after a;
130
show create table t1;
133
--echo # Case 13. ALTER. Adding a new virtual stored column.
134
create table t1 (a int, b datetime);
135
insert into t1 values (1,'2008-09-04');
136
insert into t1 values (2,'2008-09-05');
138
alter table t1 add column c int as (dayofyear(b)) persistent after a;
140
show create table t1;
143
--echo # Case 14. ALTER. Changing the expression of a virtual stored column.
144
create table t1 (a int, b datetime, c int as (week(b)) persistent);
145
insert into t1 values (1,'2008-09-04',default);
146
insert into t1 values (2,'2008-09-05',default);
148
alter table t1 change column c c int as (week(b,1)) persistent;
150
show create table t1;
153
--echo # Case 15. ALTER. Changing the expression of a virtual non-stored column.
154
create table t1 (a int, b datetime, c int as (week(b)));
155
insert into t1 values (1,'2008-09-04',default);
156
insert into t1 values (2,'2008-09-05',default);
158
alter table t1 change column c c int as (week(b,1));
160
show create table t1;