Debian Codegraph @

mysql-test/suite/gcol/r/gcol_non_stored_columns_innodb.result

raw
1 SET @@session.default_storage_engine = 'InnoDB';
2 # Case 1. All non-stored columns.
3 create table t1 (a int generated always as (2+3) virtual);
4 insert into t1 values (default);
5 select * from t1;
6 a
7 5
8 insert into t1 values (default);
9 select * from t1;
10 a
11 5
12 5
13 drop table t1;
14 # Case 2. CREATE
15 # - Column1: "real"
16 # - Column 2: virtual non-stored
17 create table t1 (a int, b int generated always as (-a) virtual);
18 insert into t1 values (1,default);
19 select * from t1;
20 a b
21 1 -1
22 insert into t1 values (2,default);
23 select * from t1 order by a;
24 a b
25 1 -1
26 2 -2
27 drop table t1;
28 # Case 3. CREATE
29 # - Column1: "real"
30 # - Column 2: virtual stored
31 create table t1 (a int, b int generated always as (-a) stored);
32 insert into t1 values (1,default);
33 select * from t1;
34 a b
35 1 -1
36 insert into t1 values (2,default);
37 select * from t1 order by a;
38 a b
39 1 -1
40 2 -2
41 drop table t1;
42 # Case 4. CREATE
43 # - Column1: virtual non-stored
44 # - Column2: "real"
45 create table t1 (a int generated always as (-b) virtual, b int);
46 insert into t1 values (default,1);
47 select * from t1;
48 a b
49 -1 1
50 insert into t1 values (default,2);
51 select * from t1 order by a;
52 a b
53 -2 2
54 -1 1
55 drop table t1;
56 # Case 5. CREATE
57 # - Column1: virtual stored
58 # - Column2: "real"
59 create table t1 (a int generated always as (-b) stored, b int);
60 insert into t1 values (default,1);
61 select * from t1;
62 a b
63 -1 1
64 insert into t1 values (default,2);
65 select * from t1 order by a;
66 a b
67 -2 2
68 -1 1
69 drop table t1;
70 # Case 6. CREATE
71 # - Column1: "real"
72 # - Column2: virtual non-stored
73 # - Column3: virtual stored
74 create table t1 (a int, b int generated always as (-a), c int generated always as (-a) stored);
75 insert into t1 values (1,default,default);
76 select * from t1;
77 a b c
78 1 -1 -1
79 insert into t1 values (2,default,default);
80 select * from t1 order by a;
81 a b c
82 1 -1 -1
83 2 -2 -2
84 drop table t1;
85 # Case 7. ALTER. Modify virtual stored -> virtual non-stored
86 create table t1 (a int, b int generated always as (a % 2) stored);
87 alter table t1 modify b int generated always as (a % 2) virtual;
88 ERROR HY000: 'Changing the STORED status' is not supported for generated columns.
89 show create table t1;
90 Table Create Table
91 t1 CREATE TABLE `t1` (
92 `a` int DEFAULT NULL,
93 `b` int GENERATED ALWAYS AS ((`a` % 2)) STORED
94 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
95 drop table t1;
96 # Case 8. ALTER. Modify virtual non-stored -> virtual stored
97 create table t1 (a int, b int generated always as (a % 2) virtual);
98 alter table t1 modify b int generated always as (a % 2) stored;
99 ERROR HY000: 'Changing the STORED status' is not supported for generated columns.
100 show create table t1;
101 Table Create Table
102 t1 CREATE TABLE `t1` (
103 `a` int DEFAULT NULL,
104 `b` int GENERATED ALWAYS AS ((`a` % 2)) VIRTUAL
105 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
106 drop table t1;
107 # Case 9. CREATE LIKE
108 # - Column1: "real"
109 # - Column2: virtual non-stored
110 # - Column3: virtual stored
111 create table t1 (a int, b int generated always as (-a), c int generated always as (-a) stored);
112 create table t2 like t1;
113 insert into t2 values (1,default,default);
114 select * from t2;
115 a b c
116 1 -1 -1
117 insert into t2 values (2,default,default);
118 select * from t2 order by a;
119 a b c
120 1 -1 -1
121 2 -2 -2
122 drop table t2;
123 drop table t1;
124 # Case 10. ALTER. Dropping a virtual non-stored column.
125 # - Column1: virtual non-stored
126 # - Column2: "real"
127 create table t1 (a int generated always as (-b) virtual, b int, c varchar(5));
128 insert into t1 values (default,1,'v1');
129 insert into t1 values (default,2,'v2');
130 select * from t1 order by b;
131 a b c
132 -1 1 v1
133 -2 2 v2
134 alter table t1 drop column a;
135 select * from t1 order by b;
136 b c
137 1 v1
138 2 v2
139 show create table t1;
140 Table Create Table
141 t1 CREATE TABLE `t1` (
142 `b` int DEFAULT NULL,
143 `c` varchar(5) DEFAULT NULL
144 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
145 drop table t1;
146 # Case 11. ALTER. Dropping a virtual stored column.
147 # - Column1: virtual stored
148 # - Column2: "real"
149 create table t1 (a int generated always as (-b) stored, b int, c char(5));
150 insert into t1 values (default,1,'v1');
151 insert into t1 values (default,2,'v2');
152 select * from t1 order by b;
153 a b c
154 -1 1 v1
155 -2 2 v2
156 alter table t1 drop column a;
157 select * from t1 order by b;
158 b c
159 1 v1
160 2 v2
161 show create table t1;
162 Table Create Table
163 t1 CREATE TABLE `t1` (
164 `b` int DEFAULT NULL,
165 `c` char(5) DEFAULT NULL
166 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
167 drop table t1;
168 # Case 12. ALTER. Adding a new virtual non-stored column.
169 create table t1 (a int, b datetime);
170 insert into t1 values (1,'2008-09-04');
171 insert into t1 values (2,'2008-09-05');
172 select * from t1 order by a;
173 a b
174 1 2008-09-04 00:00:00
175 2 2008-09-05 00:00:00
176 alter table t1 add column c int generated always as (dayofyear(b)) virtual after a;
177 select * from t1 order by a;
178 a c b
179 1 248 2008-09-04 00:00:00
180 2 249 2008-09-05 00:00:00
181 show create table t1;
182 Table Create Table
183 t1 CREATE TABLE `t1` (
184 `a` int DEFAULT NULL,
185 `c` int GENERATED ALWAYS AS (dayofyear(`b`)) VIRTUAL,
186 `b` datetime DEFAULT NULL
187 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
188 drop table t1;
189 # Case 13. ALTER. Adding a new virtual stored column.
190 create table t1 (a int, b datetime);
191 insert into t1 values (1,'2008-09-04');
192 insert into t1 values (2,'2008-09-05');
193 select * from t1 order by a;
194 a b
195 1 2008-09-04 00:00:00
196 2 2008-09-05 00:00:00
197 alter table t1 add column c int generated always as (dayofyear(b)) stored after a;
198 select * from t1 order by a;
199 a c b
200 1 248 2008-09-04 00:00:00
201 2 249 2008-09-05 00:00:00
202 show create table t1;
203 Table Create Table
204 t1 CREATE TABLE `t1` (
205 `a` int DEFAULT NULL,
206 `c` int GENERATED ALWAYS AS (dayofyear(`b`)) STORED,
207 `b` datetime DEFAULT NULL
208 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
209 drop table t1;
210 # Case 14. ALTER. Changing the expression of a virtual stored column.
211 create table t1 (a int, b datetime, c int generated always as (week(b)) stored);
212 insert into t1 values (1,'2008-09-04',default);
213 insert into t1 values (2,'2008-09-05',default);
214 select * from t1 order by a;
215 a b c
216 1 2008-09-04 00:00:00 35
217 2 2008-09-05 00:00:00 35
218 alter table t1 change column c c int generated always as (week(b,1)) stored;
219 select * from t1 order by a;
220 a b c
221 1 2008-09-04 00:00:00 36
222 2 2008-09-05 00:00:00 36
223 show create table t1;
224 Table Create Table
225 t1 CREATE TABLE `t1` (
226 `a` int DEFAULT NULL,
227 `b` datetime DEFAULT NULL,
228 `c` int GENERATED ALWAYS AS (week(`b`,1)) STORED
229 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
230 drop table t1;
231 # Case 15. ALTER. Changing the expression of a virtual non-stored column.
232 create table t1 (a int, b datetime, c int generated always as (week(b)) virtual);
233 insert into t1 values (1,'2008-09-04',default);
234 insert into t1 values (2,'2008-09-05',default);
235 select * from t1 order by a;
236 a b c
237 1 2008-09-04 00:00:00 35
238 2 2008-09-05 00:00:00 35
239 alter table t1 change column c c int generated always as (week(b,1)) virtual;
240 select * from t1 order by a;
241 a b c
242 1 2008-09-04 00:00:00 36
243 2 2008-09-05 00:00:00 36
244 show create table t1;
245 Table Create Table
246 t1 CREATE TABLE `t1` (
247 `a` int DEFAULT NULL,
248 `b` datetime DEFAULT NULL,
249 `c` int GENERATED ALWAYS AS (week(`b`,1)) VIRTUAL
250 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
251 drop table t1;
252 DROP VIEW IF EXISTS v1,v2;
253 DROP TABLE IF EXISTS t1,t2,t3;
254 DROP PROCEDURE IF EXISTS p1;
255 DROP FUNCTION IF EXISTS f1;
256 DROP TRIGGER IF EXISTS trg1;
257 DROP TRIGGER IF EXISTS trg2;
258 set sql_warnings = 0;