Debian Codegraph @

mysql-test/suite/innodb/r/innodb-virtual-columns.result

raw
1 CREATE TABLE IF NOT EXISTS gso_grad_supr (
2 term char(4) NOT NULL DEFAULT '',
3 uw_id int(8) UNSIGNED NOT NULL DEFAULT 0,
4 plan varchar(10) NOT NULL DEFAULT '',
5 wdraw_rsn varchar(4) NOT NULL DEFAULT '',
6 admit_term char(4) NOT NULL DEFAULT '',
7 CONSTRAINT gso_grad_supr_pky PRIMARY KEY (uw_id, term)
8 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
9 Warnings:
10 Warning 1280 Name 'gso_grad_supr_pky' ignored for PRIMARY key.
11 INSERT INTO `gso_grad_supr` VALUES ('1031',2,'CSM','','1009');
12 INSERT INTO `gso_grad_supr` VALUES ('1035',2,'CSM','ACAD','1009');
13 CREATE TABLE IF NOT EXISTS grad_degree (
14 student_id int(8) UNSIGNED NOT NULL,
15 plan varchar(10) NOT NULL,
16 admit_term char(4) NOT NULL,
17 wdraw_rsn varchar(4) NOT NULL DEFAULT '',
18 ofis_deg_status varchar(15) AS (
19 CASE
20 WHEN wdraw_rsn = '' THEN 'In progress'
21 WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed'
22 ELSE 'Not Completed'
23 END) VIRTUAL,
24 deg_start_term char(4) NOT NULL DEFAULT '' COMMENT 'Educated guess at the beginning of the data',
25 deg_as_of_term char(4) NOT NULL COMMENT 'In most cases also end term',
26 CONSTRAINT grad_degree_stu_plan_admit_pky PRIMARY KEY (student_id, plan, admit_term)
27 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
28 Warnings:
29 Warning 1280 Name 'grad_degree_stu_plan_admit_pky' ignored for PRIMARY key.
30 SHOW CREATE TABLE grad_degree;
31 Table Create Table
32 grad_degree CREATE TABLE `grad_degree` (
33 `student_id` int(8) unsigned NOT NULL,
34 `plan` varchar(10) NOT NULL,
35 `admit_term` char(4) NOT NULL,
36 `wdraw_rsn` varchar(4) NOT NULL DEFAULT '',
37 `ofis_deg_status` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed' else 'Not Completed' end) VIRTUAL,
38 `deg_start_term` char(4) NOT NULL DEFAULT '' COMMENT 'Educated guess at the beginning of the data',
39 `deg_as_of_term` char(4) NOT NULL COMMENT 'In most cases also end term',
40 PRIMARY KEY (`student_id`,`plan`,`admit_term`)
41 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_uca1400_ai_ci
42 CREATE INDEX grad_degree_wdraw_rsn_ndx ON grad_degree (wdraw_rsn);
43 CREATE INDEX grad_degree_as_of_term_ndx ON grad_degree (deg_as_of_term);
44 INSERT IGNORE grad_degree (
45 student_id,
46 plan,
47 admit_term,
48 wdraw_rsn,
49 deg_start_term,
50 deg_as_of_term
51 )
52 SELECT
53 ggs.uw_id AS c_student_id,
54 ggs.plan,
55 ggs.admit_term,
56 ggs.wdraw_rsn,
57 IF( (SELECT COUNT(*) FROM grad_degree AS gd WHERE gd.student_id = ggs.uw_id AND gd.admit_term = ggs.admit_term) > 0, ggs.term, ggs.admit_term ) AS c_deg_start_term,
58 ggs.term AS c_as_of_term
59 FROM gso_grad_supr AS ggs
60 LEFT OUTER JOIN
61 grad_degree AS gd
62 ON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term )
63 WHERE
64 ggs.term = 1031 AND
65 gd.student_id IS NULL
66 ;
67 UPDATE grad_degree AS gd
68 INNER JOIN
69 gso_grad_supr AS ggs
70 ON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term )
71 SET
72 gd.wdraw_rsn = ggs.wdraw_rsn,
73 gd.deg_as_of_term = 1035
74 WHERE
75 gd.wdraw_rsn NOT IN ('DCMP', 'TRDC') AND
76 ggs.term = 1035
77 ;
78 drop table grad_degree;
79 CREATE TABLE IF NOT EXISTS grad_degree (
80 student_id int(8) UNSIGNED NOT NULL,
81 plan varchar(10) NOT NULL,
82 admit_term char(4) NOT NULL,
83 wdraw_rsn varchar(4) NOT NULL DEFAULT '',
84 ofis_deg_status varchar(15) AS (
85 CASE
86 WHEN wdraw_rsn = '' THEN 'In progress'
87 WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed'
88 ELSE 'Not Completed'
89 END) VIRTUAL,
90 ofis_deg_status2 varchar(15) AS (
91 CASE
92 WHEN wdraw_rsn = '' THEN 'In progress2'
93 WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed2'
94 ELSE 'Not Completed2'
95 END) VIRTUAL,
96 ofis_deg_status3 varchar(15) AS (
97 CASE
98 WHEN wdraw_rsn = '' THEN 'In progress3'
99 WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed3'
100 ELSE 'Not Completed3'
101 END) VIRTUAL,
102 ofis_deg_status4 varchar(15) AS (
103 CASE
104 WHEN wdraw_rsn = '' THEN 'In progress4'
105 WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed4'
106 ELSE 'Not Completed4'
107 END) VIRTUAL,
108 ofis_deg_status5 varchar(15) AS (
109 CASE
110 WHEN wdraw_rsn = '' THEN 'In progress5'
111 WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed5'
112 ELSE 'Not Completed5'
113 END) VIRTUAL,
114 ofis_deg_status6 varchar(15) AS (
115 CASE
116 WHEN wdraw_rsn = '' THEN 'In progress6'
117 WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed6'
118 ELSE 'Not Completed6'
119 END) VIRTUAL,
120 ofis_deg_status7 varchar(15) AS (
121 CASE
122 WHEN wdraw_rsn = '' THEN 'In progress7'
123 WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed7'
124 ELSE 'Not Completed7'
125 END) VIRTUAL,
126 ofis_deg_status8 varchar(15) AS (
127 CASE
128 WHEN wdraw_rsn = '' THEN 'In progress8'
129 WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed8'
130 ELSE 'Not Completed8'
131 END) VIRTUAL,
132 deg_start_term char(4) NOT NULL DEFAULT '' COMMENT 'Educated guess at the beginning of the data',
133 deg_as_of_term char(4) NOT NULL COMMENT 'In most cases also end term',
134 CONSTRAINT grad_degree_stu_plan_admit_pky PRIMARY KEY (student_id, plan, admit_term)
135 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
136 Warnings:
137 Warning 1280 Name 'grad_degree_stu_plan_admit_pky' ignored for PRIMARY key.
138 SHOW CREATE TABLE grad_degree;
139 Table Create Table
140 grad_degree CREATE TABLE `grad_degree` (
141 `student_id` int(8) unsigned NOT NULL,
142 `plan` varchar(10) NOT NULL,
143 `admit_term` char(4) NOT NULL,
144 `wdraw_rsn` varchar(4) NOT NULL DEFAULT '',
145 `ofis_deg_status` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed' else 'Not Completed' end) VIRTUAL,
146 `ofis_deg_status2` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress2' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed2' else 'Not Completed2' end) VIRTUAL,
147 `ofis_deg_status3` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress3' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed3' else 'Not Completed3' end) VIRTUAL,
148 `ofis_deg_status4` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress4' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed4' else 'Not Completed4' end) VIRTUAL,
149 `ofis_deg_status5` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress5' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed5' else 'Not Completed5' end) VIRTUAL,
150 `ofis_deg_status6` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress6' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed6' else 'Not Completed6' end) VIRTUAL,
151 `ofis_deg_status7` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress7' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed7' else 'Not Completed7' end) VIRTUAL,
152 `ofis_deg_status8` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress8' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed8' else 'Not Completed8' end) VIRTUAL,
153 `deg_start_term` char(4) NOT NULL DEFAULT '' COMMENT 'Educated guess at the beginning of the data',
154 `deg_as_of_term` char(4) NOT NULL COMMENT 'In most cases also end term',
155 PRIMARY KEY (`student_id`,`plan`,`admit_term`)
156 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_uca1400_ai_ci
157 CREATE INDEX grad_degree_wdraw_rsn_ndx ON grad_degree (wdraw_rsn);
158 CREATE INDEX grad_degree_as_of_term_ndx ON grad_degree (deg_as_of_term);
159 INSERT IGNORE grad_degree (
160 student_id,
161 plan,
162 admit_term,
163 wdraw_rsn,
164 deg_start_term,
165 deg_as_of_term
166 )
167 SELECT
168 ggs.uw_id AS c_student_id,
169 ggs.plan,
170 ggs.admit_term,
171 ggs.wdraw_rsn,
172 IF( (SELECT COUNT(*) FROM grad_degree AS gd WHERE gd.student_id = ggs.uw_id AND gd.admit_term = ggs.admit_term) > 0, ggs.term, ggs.admit_term ) AS c_deg_start_term,
173 ggs.term AS c_as_of_term
174 FROM gso_grad_supr AS ggs
175 LEFT OUTER JOIN
176 grad_degree AS gd
177 ON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term )
178 WHERE
179 ggs.term = 1031 AND
180 gd.student_id IS NULL
181 ;
182 UPDATE grad_degree AS gd
183 INNER JOIN
184 gso_grad_supr AS ggs
185 ON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term )
186 SET
187 gd.wdraw_rsn = ggs.wdraw_rsn,
188 gd.deg_as_of_term = 1035
189 WHERE
190 gd.wdraw_rsn NOT IN ('DCMP', 'TRDC') AND
191 ggs.term = 1035
192 ;
193 ALTER TABLE grad_degree DROP INDEX grad_degree_wdraw_rsn_ndx;
194 ALTER TABLE grad_degree DROP COLUMN deg_start_term;
195 SHOW CREATE TABLE grad_degree;
196 Table Create Table
197 grad_degree CREATE TABLE `grad_degree` (
198 `student_id` int(8) unsigned NOT NULL,
199 `plan` varchar(10) NOT NULL,
200 `admit_term` char(4) NOT NULL,
201 `wdraw_rsn` varchar(4) NOT NULL DEFAULT '',
202 `ofis_deg_status` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed' else 'Not Completed' end) VIRTUAL,
203 `ofis_deg_status2` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress2' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed2' else 'Not Completed2' end) VIRTUAL,
204 `ofis_deg_status3` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress3' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed3' else 'Not Completed3' end) VIRTUAL,
205 `ofis_deg_status4` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress4' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed4' else 'Not Completed4' end) VIRTUAL,
206 `ofis_deg_status5` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress5' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed5' else 'Not Completed5' end) VIRTUAL,
207 `ofis_deg_status6` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress6' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed6' else 'Not Completed6' end) VIRTUAL,
208 `ofis_deg_status7` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress7' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed7' else 'Not Completed7' end) VIRTUAL,
209 `ofis_deg_status8` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress8' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed8' else 'Not Completed8' end) VIRTUAL,
210 `deg_as_of_term` char(4) NOT NULL COMMENT 'In most cases also end term',
211 PRIMARY KEY (`student_id`,`plan`,`admit_term`),
212 KEY `grad_degree_as_of_term_ndx` (`deg_as_of_term`)
213 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_uca1400_ai_ci
214 DROP TABLE grad_degree;
215 CREATE TABLE IF NOT EXISTS grad_degree (
216 student_id int(8) UNSIGNED NOT NULL,
217 plan varchar(10) NOT NULL,
218 admit_term char(4) NOT NULL,
219 wdraw_rsn varchar(4) NOT NULL DEFAULT '',
220 ofis_deg_status varchar(15) AS (
221 CASE
222 WHEN wdraw_rsn = '' THEN 'In progress'
223 WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed'
224 ELSE 'Not Completed'
225 END) VIRTUAL,
226 ofis_deg_status2 varchar(15) AS (
227 CASE
228 WHEN wdraw_rsn = '' THEN 'In progress2'
229 WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed2'
230 ELSE 'Not Completed2'
231 END) VIRTUAL,
232 ofis_deg_status3 varchar(15) AS (
233 CASE
234 WHEN wdraw_rsn = '' THEN 'In progress3'
235 WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed3'
236 ELSE 'Not Completed3'
237 END) VIRTUAL,
238 ofis_deg_status4 varchar(15) AS (
239 CASE
240 WHEN wdraw_rsn = '' THEN 'In progress4'
241 WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed4'
242 ELSE 'Not Completed4'
243 END) VIRTUAL,
244 ofis_deg_status5 varchar(15) AS (
245 CASE
246 WHEN wdraw_rsn = '' THEN 'In progress5'
247 WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed5'
248 ELSE 'Not Completed5'
249 END) VIRTUAL,
250 ofis_deg_status6 varchar(15) AS (
251 CASE
252 WHEN wdraw_rsn = '' THEN 'In progress6'
253 WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed6'
254 ELSE 'Not Completed6'
255 END) VIRTUAL,
256 ofis_deg_status7 varchar(15) AS (
257 CASE
258 WHEN wdraw_rsn = '' THEN 'In progress7'
259 WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed7'
260 ELSE 'Not Completed7'
261 END) VIRTUAL,
262 ofis_deg_status8 varchar(15) AS (
263 CASE
264 WHEN wdraw_rsn = '' THEN 'In progress8'
265 WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed8'
266 ELSE 'Not Completed8'
267 END) VIRTUAL,
268 deg_start_term char(4) NOT NULL DEFAULT '' COMMENT 'Educated guess at the beginning of the data',
269 deg_as_of_term char(4) NOT NULL COMMENT 'In most cases also end term',
270 CONSTRAINT grad_degree_stu_plan_admit_pky PRIMARY KEY (student_id, plan, admit_term)
271 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
272 Warnings:
273 Warning 1280 Name 'grad_degree_stu_plan_admit_pky' ignored for PRIMARY key.
274 SHOW CREATE TABLE grad_degree;
275 Table Create Table
276 grad_degree CREATE TABLE `grad_degree` (
277 `student_id` int(8) unsigned NOT NULL,
278 `plan` varchar(10) NOT NULL,
279 `admit_term` char(4) NOT NULL,
280 `wdraw_rsn` varchar(4) NOT NULL DEFAULT '',
281 `ofis_deg_status` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed' else 'Not Completed' end) VIRTUAL,
282 `ofis_deg_status2` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress2' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed2' else 'Not Completed2' end) VIRTUAL,
283 `ofis_deg_status3` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress3' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed3' else 'Not Completed3' end) VIRTUAL,
284 `ofis_deg_status4` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress4' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed4' else 'Not Completed4' end) VIRTUAL,
285 `ofis_deg_status5` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress5' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed5' else 'Not Completed5' end) VIRTUAL,
286 `ofis_deg_status6` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress6' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed6' else 'Not Completed6' end) VIRTUAL,
287 `ofis_deg_status7` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress7' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed7' else 'Not Completed7' end) VIRTUAL,
288 `ofis_deg_status8` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress8' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed8' else 'Not Completed8' end) VIRTUAL,
289 `deg_start_term` char(4) NOT NULL DEFAULT '' COMMENT 'Educated guess at the beginning of the data',
290 `deg_as_of_term` char(4) NOT NULL COMMENT 'In most cases also end term',
291 PRIMARY KEY (`student_id`,`plan`,`admit_term`)
292 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_uca1400_ai_ci
293 CREATE INDEX grad_degree_wdraw_rsn_ndx ON grad_degree (wdraw_rsn);
294 ALTER TABLE grad_degree DROP COLUMN ofis_deg_status2, DROP COLUMN ofis_deg_status3,
295 DROP COLUMN ofis_deg_status4, DROP COLUMN ofis_deg_status5, DROP COLUMN ofis_deg_status6,
296 DROP COLUMN ofis_deg_status7, DROP COLUMN ofis_deg_status8;
297 CREATE INDEX grad_degree_as_of_term_ndx ON grad_degree (deg_as_of_term);
298 INSERT IGNORE grad_degree (
299 student_id,
300 plan,
301 admit_term,
302 wdraw_rsn,
303 deg_start_term,
304 deg_as_of_term
305 )
306 SELECT
307 ggs.uw_id AS c_student_id,
308 ggs.plan,
309 ggs.admit_term,
310 ggs.wdraw_rsn,
311 IF( (SELECT COUNT(*) FROM grad_degree AS gd WHERE gd.student_id = ggs.uw_id AND gd.admit_term = ggs.admit_term) > 0, ggs.term, ggs.admit_term ) AS c_deg_start_term,
312 ggs.term AS c_as_of_term
313 FROM gso_grad_supr AS ggs
314 LEFT OUTER JOIN
315 grad_degree AS gd
316 ON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term )
317 WHERE
318 ggs.term = 1031 AND
319 gd.student_id IS NULL
320 ;
321 UPDATE grad_degree AS gd
322 INNER JOIN
323 gso_grad_supr AS ggs
324 ON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term )
325 SET
326 gd.wdraw_rsn = ggs.wdraw_rsn,
327 gd.deg_as_of_term = 1035
328 WHERE
329 gd.wdraw_rsn NOT IN ('DCMP', 'TRDC') AND
330 ggs.term = 1035
331 ;
332 select * from grad_degree;
333 student_id plan admit_term wdraw_rsn ofis_deg_status deg_start_term deg_as_of_term
334 2 CSM 1009 ACAD Not Completed 1009 1035
335 select * from gso_grad_supr;
336 term uw_id plan wdraw_rsn admit_term
337 1031 2 CSM 1009
338 1035 2 CSM ACAD 1009
339 drop table grad_degree;
340 drop table gso_grad_supr;
341 CREATE TABLE t1 (a INT, b CHAR(12), c INT AS (a) VIRTUAL, FULLTEXT KEY(b)) ENGINE=InnoDB;
342 INSERT INTO t1 (a,b) VALUES (1,'foo');
343 SELECT * FROM t1;
344 a b c
345 1 foo 1
346 DROP TABLE t1;
347 CREATE TABLE t1 (a INT, b CHAR(12), c INT AS (a) VIRTUAL) ENGINE=InnoDB;
348 INSERT INTO t1 (a,b) VALUES (1,'foo');
349 ALTER TABLE t1 ADD FULLTEXT KEY(b);
350 SELECT * FROM t1;
351 a b c
352 1 foo 1
353 DROP TABLE t1;
354 CREATE TABLE t1 (
355 id int primary key,value varchar(2),
356 vcomplete int AS (cast(substr(value,1,1) as int)),
357 va int AS (null),vb int AS (null),vc int AS (null),
358 vresult char(1) AS (substr(value,2)),
359 KEY key1(vresult,vcomplete)
360 ) ENGINE=InnoDB;
361 BEGIN;
362 INSERT INTO t1(id,value) VALUES (1,'0F');
363 UPDATE t1 SET value = '1S';
364 UPDATE t1 SET value = '0F';
365 COMMIT;
366 SET GLOBAL innodb_max_purge_lag_wait=0;
367 SELECT * FROM t1;
368 id value vcomplete va vb vc vresult
369 1 0F 0 NULL NULL NULL F
370 DROP TABLE t1;
371 CREATE TABLE t1 (
372 col1 int primary key,
373 col2 int,
374 col3 int,
375 col4 varchar(5),
376 v_col1 char(1) AS (substr(col4,1,1)),
377 v_col2 char(1) AS (substr(col4,2,1)),
378 v_col3 char(1) AS (substr(col4,3,1)),
379 v_col4 char(1) AS (substr(col4,4,1)),
380 v_col5 char(1) AS (substr(col4,5,1)),
381 KEY key1(v_col2, v_col1, v_col3, v_col4, v_col5),
382 KEY key2(col2, col3)
383 ) ENGINE=InnoDB;
384 INSERT INTO t1(col1,col2,col3,col4) VALUES (1,1,1,'ABCDE');
385 UPDATE t1 SET col2=2, col4 = 'PQRST';
386 SET GLOBAL innodb_max_purge_lag_wait=0;
387 CHECK TABLE t1 EXTENDED;
388 Table Op Msg_type Msg_text
389 test.t1 check status OK
390 DROP TABLE t1;