Debian Codegraph @

mysql-test/suite/vcol/inc/vcol_non_stored_columns.inc

raw
1 ################################################################################
2 # inc/vcol_non_stored_columns.inc #
3 # #
4 # Purpose: #
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. #
8 # #
9 #------------------------------------------------------------------------------#
10 # Original Author: Andrey Zhakov #
11 # Original Date: 2008-09-04 #
12 # Change Author: Oleksandr Byelkin (Monty program Ab)
13 # Date: 2009-03-24
14 # Change: Syntax changed
15 ################################################################################
16
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.
20
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);
26 select * from t1;
27 insert into t1 values (2,default);
28 select * from t1;
29 drop table t1;
30
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);
36 select * from t1;
37 insert into t1 values (2,default);
38 select * from t1;
39 drop table t1;
40
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);
46 select * from t1;
47 insert into t1 values (default,2);
48 select * from t1;
49 drop table t1;
50
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);
56 select * from t1;
57 insert into t1 values (default,2);
58 select * from t1;
59 drop table t1;
60
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);
67 select * from t1;
68 insert into t1 values (2,default,default);
69 select * from t1;
70 drop table t1;
71
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_GENERATED_COLUMN
75 alter table t1 modify b int as (a % 2);
76 show create table t1;
77 drop table t1;
78
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_GENERATED_COLUMN
82 alter table t1 modify b int as (a % 2) persistent;
83 show create table t1;
84 drop table t1;
85
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);
93 select * from t2;
94 insert into t2 values (2,default,default);
95 select * from t2;
96 drop table t2;
97 drop table t1;
98
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');
105 select * from t1;
106 alter table t1 drop column a;
107 select * from t1;
108 show create table t1;
109 drop table t1;
110
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');
117 select * from t1;
118 alter table t1 drop column a;
119 select * from t1;
120 show create table t1;
121 drop table t1;
122
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');
127 select * from t1;
128 alter table t1 add column c int as (dayofyear(b)) after a;
129 select * from t1;
130 show create table t1;
131 drop table t1;
132
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');
137 select * from t1;
138 alter table t1 add column c int as (dayofyear(b)) persistent after a;
139 select * from t1;
140 show create table t1;
141 drop table t1;
142
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,0)) persistent);
145 insert into t1 values (1,'2008-09-04',default);
146 insert into t1 values (2,'2008-09-05',default);
147 select * from t1;
148 alter table t1 change column c c int as (week(b,1)) persistent;
149 select * from t1;
150 show create table t1;
151 drop table t1;
152
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,0)));
155 insert into t1 values (1,'2008-09-04',default);
156 insert into t1 values (2,'2008-09-05',default);
157 select * from t1;
158 alter table t1 change column c c int as (week(b,1));
159 select * from t1;
160 show create table t1;
161 drop table t1;
162
163 --echo #
164 --echo # MDEV-6103 - Adding/removing non-materialized virtual column triggers
165 --echo # table recreation
166 --echo #
167 CREATE TABLE t1(a INT);
168 INSERT INTO t1 VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1);
169 enable_info;
170 ALTER TABLE t1 ADD COLUMN b INT AS (a + 1) VIRTUAL;
171 ALTER TABLE t1 DROP COLUMN b;
172 disable_info;
173 CHECK TABLE t1;
174 DROP TABLE t1;