Debian Codegraph @

mysql-test/suite/innodb/t/innodb-virtual-columns.test

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