Debian Codegraph @

mysql-test/suite/vcol/r/vcol_non_stored_columns_myisam.result

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