Debian Codegraph @

mysql-test/suite/ndb_rpl/r/ndb_rpl_gcol_non_stored_columns.result

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