Debian Codegraph @

mysql-test/suite/gcol/t/innodb_virtual_fk.test

raw
1 -- source include/have_innodb.inc
2
3 set default_storage_engine=innodb;
4
5 --echo #
6 --echo # Bug#22469130: FOREIGN KEY ON DELETE CASCADE NOT ALLOWED
7 --echo # WHEN A VIRTUAL INDEX EXISTS.
8
9
10 --echo # UPDATE CASCADE
11 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
12 CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL, KEY(fld2),
13 FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE CASCADE);
14 INSERT INTO t1 VALUES(1);
15 INSERT INTO t2 VALUES(1, DEFAULT);
16 UPDATE t1 SET fld1= 2;
17 SELECT fld2 FROM t2;
18 SELECT * FROM t2;
19 DROP TABLE t2, t1;
20
21 --echo # UPDATE SET NULL
22 CREATE TABLE t1(fld1 INT NOT NULL, fld2 INT NOT NULL PRIMARY KEY,
23 KEY(fld1));
24 CREATE TABLE t2(fld1 INT, fld2 INT AS (fld1) VIRTUAL, KEY(fld2),
25 FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE SET NULL);
26 INSERT INTO t1 VALUES(1, 2);
27 INSERT INTO t2 VALUES(1, DEFAULT);
28 UPDATE t1 SET fld1= 2;
29 SELECT fld2 FROM t2;
30 SELECT * FROM t2;
31 DROP TABLE t2, t1;
32
33 --echo # DELETE CASCADE
34 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
35 CREATE TABLE t2(fld1 INT, fld2 INT AS (fld1) VIRTUAL, KEY(fld2),
36 FOREIGN KEY(fld1) REFERENCES t1(fld1) ON DELETE CASCADE);
37 INSERT INTO t1 VALUES(1);
38 INSERT INTO t1 VALUES(2);
39 INSERT INTO t2 VALUES(1, DEFAULT);
40 INSERT INTO t2 VALUES(2, DEFAULT);
41 DELETE FROM t1 WHERE fld1= 1;
42 SELECT fld2 FROM t2;
43 SELECT * FROM t2;
44 DROP TABLE t2, t1;
45
46 --echo # DELETE SET NULL
47 CREATE TABLE t1(fld1 INT NOT NULL, fld2 INT NOT NULL PRIMARY KEY, KEY(fld1));
48 CREATE TABLE t2(fld1 INT, fld2 INT AS (fld1) VIRTUAL, KEY(fld2),
49 FOREIGN KEY(fld1) REFERENCES t1(fld1) ON DELETE SET NULL);
50 INSERT INTO t1 VALUES(1, 1);
51 INSERT INTO t1 VALUES(2, 2);
52 INSERT INTO t2 VALUES(1, DEFAULT);
53 INSERT INTO t2 VALUES(2, DEFAULT);
54 DELETE FROM t1 WHERE fld1= 1;
55 SELECT fld2 FROM t2;
56 SELECT * FROM t2;
57 DROP TABLE t2, t1;
58
59 --echo # VIRTUAL INDEX CONTAINS FK CONSTRAINT COLUMN
60 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
61 CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT, fld3 INT AS (fld2) VIRTUAL,
62 KEY(fld3, fld1),
63 FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE CASCADE);
64 INSERT INTO t1(fld1) VALUES(1);
65 INSERT INTO t2(fld1, fld2) VALUES(1, 3);
66 UPDATE t1 SET fld1= 2;
67 SELECT fld3, fld1 FROM t2;
68 SELECT * FROM t2;
69 DROP TABLE t2, t1;
70
71 --echo # Multiple level of VIRTUAL columns.
72
73 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
74 CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL,
75 fld3 INT AS (fld2) VIRTUAL, KEY(fld3), KEY(fld2),
76 FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE CASCADE);
77 INSERT INTO t1(fld1) VALUES(1);
78 INSERT INTO t2(fld1) VALUES(1);
79 UPDATE t1 SET fld1= 2;
80 SELECT fld2 FROM t2;
81 SELECT fld3 FROM t2;
82 SELECT * FROM t2;
83 DROP TABLE t2, t1;
84
85 --echo # Drop the VIRTUAL INDEX using alter copy ALGORITHM
86 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
87 CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL, KEY vk(fld2),
88 KEY(fld1), FOREIGN KEY(fld1) REFERENCES t1(fld1)
89 ON UPDATE CASCADE);
90 INSERT INTO t1(fld1) VALUES(1);
91 INSERT INTO t2(fld1) VALUES(1);
92 UPDATE t1 SET fld1= 2;
93 SELECT fld2, fld1 FROM t2;
94 ALTER TABLE t2 DROP INDEX vk, ALGORITHM= COPY;
95 UPDATE t1 SET fld1= 3;
96 SELECT fld2, fld1 FROM t2;
97 DROP TABLE t2, t1;
98
99 --echo # Drop the VIRTUAL INDEX using INPLACE alter ALGORITHM
100 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
101 CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL,
102 KEY vk(fld2), KEY(fld1), FOREIGN KEY(fld1) REFERENCES t1(fld1)
103 ON UPDATE CASCADE);
104 INSERT INTO t1(fld1) VALUES(1);
105 INSERT INTO t2(fld1) VALUES(1);
106 UPDATE t1 SET fld1= 2;
107 SELECT fld2, fld1 FROM t2;
108 ALTER TABLE t2 DROP INDEX vk, ALGORITHM= COPY;
109 UPDATE t1 SET fld1= 3;
110 SELECT fld2, fld1 FROM t2;
111 DROP TABLE t2, t1;
112
113 --echo # Add the VIRTUAL INDEX using COPY alter ALGORITHM
114 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
115 CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL,
116 KEY(fld1), FOREIGN KEY(fld1) REFERENCES t1(fld1)
117 ON UPDATE CASCADE);
118 INSERT INTO t1(fld1) VALUES(1);
119 INSERT INTO t2(fld1) VALUES(1);
120 UPDATE t1 SET fld1= 2;
121 SELECT fld2, fld1 FROM t2;
122 ALTER TABLE t2 ADD INDEX vk(fld2), ALGORITHM= COPY;
123 UPDATE t1 SET fld1= 3;
124 SELECT fld2, fld1 FROM t2;
125 DROP TABLE t2, t1;
126
127 --echo # Add the VIRTUAL INDEX using INPLACE alter ALGORITHM
128 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
129 CREATE TABLE t2(fld1 INT NOT NULL,fld2 INT AS (fld1) VIRTUAL,
130 KEY(fld1), FOREIGN KEY(fld1) REFERENCES t1(fld1)
131 ON UPDATE CASCADE);
132 INSERT INTO t1(fld1) VALUES(1);
133 INSERT INTO t2(fld1) VALUES(1);
134 UPDATE t1 SET fld1= 2;
135 SELECT fld2, fld1 FROM t2;
136 ALTER TABLE t2 ADD INDEX vk(fld2), ALGORITHM= INPLACE;
137 UPDATE t1 SET fld1= 3;
138 SELECT fld2, fld1 FROM t2;
139 DROP TABLE t2, t1;
140
141 --echo # Drop the VIRTUAL INDEX contains fk constraint column
142 --echo # using alter copy ALGORITHM
143 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
144 CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT NOT NULL,
145 fld3 INT AS (fld2) VIRTUAL, KEY vk(fld3, fld1),
146 KEY(fld1), FOREIGN KEY(fld1) REFERENCES t1(fld1)
147 ON UPDATE CASCADE);
148 INSERT INTO t1(fld1) VALUES(1);
149 INSERT INTO t2(fld1, fld2) VALUES(1, 2);
150 UPDATE t1 SET fld1= 2;
151 SELECT fld3, fld1 FROM t2;
152 ALTER TABLE t2 DROP INDEX vk, ALGORITHM= COPY;
153 UPDATE t1 SET fld1= 3;
154 SELECT fld3, fld1 FROM t2;
155 DROP TABLE t2, t1;
156
157 --echo # Drop the VIRTUAL INDEX which contains fk constraint column
158 --echo # using INPLACE alter operation
159 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
160 CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT NOT NULL,
161 fld3 INT AS (fld2) VIRTUAL, KEY vk(fld3, fld1),
162 KEY(fld1), FOREIGN KEY(fld1) REFERENCES t1(fld1)
163 ON UPDATE CASCADE);
164 INSERT INTO t1(fld1) VALUES(1);
165 INSERT INTO t2(fld1, fld2) VALUES(1, 2);
166 UPDATE t1 SET fld1= 2;
167 SELECT fld3, fld1 FROM t2;
168 alter TABLE t2 DROP INDEX vk, ALGORITHM= INPLACE;
169 UPDATE t1 SET fld1= 3;
170 SELECT fld3, fld1 FROM t2;
171 DROP TABLE t2, t1;
172
173 --echo # Add the VIRTUAL INDEX contains fk constraint column
174 --echo # using copy alter operatiON
175 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
176 CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT NOT NULL,
177 fld3 INT AS (fld2) VIRTUAL, KEY(fld1),
178 FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE CASCADE);
179 INSERT INTO t1(fld1) VALUES(1);
180 INSERT INTO t2(fld1, fld2) VALUES(1, 2);
181 UPDATE t1 SET fld1= 2;
182 SELECT fld3, fld1 FROM t2;
183 alter TABLE t2 ADD INDEX vk(fld3, fld1), ALGORITHM= COPY;
184 UPDATE t1 SET fld1= 3;
185 SELECT fld3, fld1 FROM t2;
186 DROP TABLE t2, t1;
187
188 --echo # Cascading UPDATEs and DELETEs for the multiple
189 --echo # fk dependent TABLEs
190
191 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
192 CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL,
193 KEY(fld1), KEY(fld2, fld1),
194 FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE CASCADE);
195 CREATE TABLE t3(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL,
196 KEY(fld2, fld1),
197 FOREIGN KEY(fld1) REFERENCES t2(fld1) ON UPDATE CASCADE);
198 INSERT INTO t1 VALUES(1), (2);
199 INSERT INTO t2(fld1) VALUES(1), (2);
200 INSERT INTO t3(fld1) VALUES(1), (2);
201 UPDATE t1 SET fld1= 4 WHERE fld1= 1;
202 SELECT fld2, fld1 FROM t2;
203 SELECT fld2, fld1 FROM t3;
204 DROP TABLE t3, t2, t1;
205
206 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
207 CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT NOT NULL,
208 fld3 INT AS (fld2) VIRTUAL, KEY(fld3, fld1), KEY(fld1),
209 FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE CASCADE);
210 CREATE TABLE t3(fld1 INT NOT NULL, fld2 INT NOT NULL,
211 fld3 INT AS (fld2) VIRTUAL, KEY(fld3, fld1),
212 FOREIGN KEY(fld1) REFERENCES t2(fld1) ON UPDATE CASCADE);
213 INSERT INTO t1 VALUES(1), (2);
214 INSERT INTO t2 VALUES(1, 1, DEFAULT), (2, 2, default);
215 INSERT INTO t3 VALUES(1, 1, DEFAULT), (2, 2, default);
216 UPDATE t1 SET fld1= 4 WHERE fld1= 1;
217 SELECT fld3, fld1 FROM t2;
218 SELECT fld3, fld1 FROM t3;
219 DROP TABLE t3, t2, t1;
220
221 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
222 CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL,
223 KEY(fld1), KEY(fld2, fld1),
224 FOREIGN KEY(fld1) REFERENCES t1(fld1) ON DELETE CASCADE);
225 CREATE TABLE t3(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL,
226 KEY(fld2, fld1), FOREIGN KEY(fld1) REFERENCES t2(fld1)
227 ON DELETE CASCADE);
228 INSERT INTO t1 VALUES(1), (2);
229 INSERT INTO t2(fld1) VALUES(1), (2);
230 INSERT INTO t3(fld1) VALUES(1), (2);
231 DELETE FROM t1 WHERE fld1= 1;
232 SELECT fld2, fld1 FROM t2;
233 SELECT fld2, fld1 FROM t3;
234 DROP TABLE t3, t2, t1;
235
236 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
237 CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT NOT NULL,
238 fld3 INT AS (fld2) VIRTUAL,
239 KEY(fld3, fld1), KEY(fld1),
240 FOREIGN KEY(fld1) REFERENCES t1(fld1)
241 ON DELETE CASCADE);
242 CREATE TABLE t3(fld1 INT NOT NULL, fld2 INT NOT NULL,
243 fld3 INT AS (fld2) VIRTUAL, KEY(fld3, fld1),
244 FOREIGN KEY(fld1) REFERENCES t2(fld1)
245 ON DELETE CASCADE);
246 INSERT INTO t1 VALUES(1), (2);
247 INSERT INTO t2 VALUES(1, 1, DEFAULT), (2, 2, default);
248 INSERT INTO t3 VALUES(1, 1, DEFAULT), (2, 2, default);
249 DELETE FROM t1 WHERE fld1= 1;
250 SELECT fld3, fld1 FROM t2;
251 SELECT fld3, fld1 FROM t3;
252 DROP TABLE t3, t2, t1;
253
254 --echo # RENAME TABLE
255 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
256 CREATE TABLE t2(fld1 INT NOT NULL,
257 fld2 INT AS (fld1) VIRTUAL,
258 KEY(fld2, fld1),
259 FOREIGN KEY(fld1) REFERENCES t1(fld1)
260 ON DELETE CASCADE);
261 INSERT INTO t1 VALUES(1), (2);
262 INSERT INTO t2 VALUES(1, DEFAULT), (2, default);
263 RENAME TABLE t2 to t3;
264 DELETE FROM t1 WHERE fld1= 1;
265 SELECT fld2, fld1 FROM t3;
266 DROP TABLE t3, t1;
267
268 --echo # FOREIGN_KEY_CHECKS disabled DURING INPLACE ALTER
269 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
270 CREATE TABLE t2(fld1 INT NOT NULL,
271 fld2 INT AS (fld1) VIRTUAL,
272 FOREIGN KEY(fld1) REFERENCES t1(fld1)
273 ON UPDATE CASCADE);
274 INSERT INTO t1 VALUES(1), (2);
275 INSERT INTO t2 VALUES(1, DEFAULT), (2, default);
276 SET foreign_key_checks = 0;
277 ALTER TABLE t2 ADD INDEX vk(fld2), ALGORITHM=INPLACE;
278 SET foreign_key_checks = 1;
279 UPDATE t1 SET fld1= 3 WHERE fld1= 2;
280 SELECT fld2 FROM t2;
281 DROP TABLE t2, t1;
282
283 --echo # GENERATED COLUMN COMPUTATION FAILS when SQL_MODE
284 --echo # is set to ERROR_FOR_DIVISION_BY_ZERO
285 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
286 CREATE TABLE t2(fld1 INT NOT NULL,
287 fld2 INT AS (100/fld1) VIRTUAL,
288 KEY(fld2),
289 FOREIGN KEY(fld1) REFERENCES t1(fld1)
290 ON UPDATE CASCADE);
291 INSERT INTO t1 VALUES(1), (2);
292 INSERT INTO t2 VALUES(1, DEFAULT), (2, default);
293 #--error ER_DIVISION_BY_ZERO
294 UPDATE IGNORE t1 SET fld1= 0 WHERE fld1= 2;
295 SELECT fld2 FROM t2;
296 DROP TABLE t2, t1;
297
298 --echo # CHANGE SQL_MODE and try the ERROR_FOR_DIVISION_BY_ZERO
299 SET sql_mode = STRICT_ALL_TABLES;
300 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
301 CREATE TABLE t2(fld1 INT NOT NULL,
302 fld2 INT AS (100/fld1) VIRTUAL,
303 KEY(fld2),
304 FOREIGN KEY(fld1) REFERENCES t1(fld1)
305 ON UPDATE CASCADE);
306 INSERT INTO t1 VALUES(1), (2);
307 INSERT INTO t2 VALUES(1, DEFAULT), (2, default);
308 UPDATE t1 SET fld1= 0 WHERE fld1= 2;
309 SELECT fld2 FROM t2;
310 SELECT * FROM t2;
311 DROP TABLE t2, t1;
312 SET sql_mode = default;
313
314 --echo # ADD FOREIGN CONSTRAINT USING COPY
315 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
316 CREATE TABLE t2(fld1 INT NOT NULL,
317 fld2 INT AS (fld1) VIRTUAL, KEY(fld2));
318 ALTER TABLE t2 ADD FOREIGN KEY (fld1)
319 REFERENCES t1(fld1) ON UPDATE CASCADE,
320 ALGORITHM=copy;
321 INSERT INTO t1 VALUES(1);
322 INSERT INTO t2 VALUES(1, DEFAULT);
323 UPDATE t1 SET fld1= 2;
324 SELECT fld2 FROM t2;
325 SELECT * FROM t2;
326 DROP TABLE t2, t1;
327
328 --echo # ADD FOREIGN CONSTRAINT USING INPLACE
329 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
330 CREATE TABLE t2(fld1 INT NOT NULL,
331 fld2 INT AS (fld1) VIRTUAL, KEY(fld2));
332 SET foreign_key_checks = 0;
333 ALTER TABLE t2 ADD FOREIGN KEY (fld1)
334 REFERENCES t1(fld1) ON UPDATE CASCADE,
335 ALGORITHM=inplace;
336 SET foreign_key_checks = 1;
337 INSERT INTO t1 VALUES(1);
338 INSERT INTO t2 VALUES(1, DEFAULT);
339 UPDATE t1 SET fld1= 2;
340 SELECT fld2 FROM t2;
341 SELECT * FROM t2;
342 DROP TABLE t2, t1;
343
344 --echo # DROP FOREIGN CONSTRAINT USING COPY
345 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
346 CREATE TABLE t2(fld1 INT NOT NULL,
347 fld2 INT AS (fld1) VIRTUAL, KEY(fld2),
348 CONSTRAINT fidx FOREIGN KEY (fld1) REFERENCES t1(fld1)
349 ON UPDATE CASCADE);
350 INSERT INTO t1 VALUES(1);
351 INSERT INTO t2 VALUES(1, DEFAULT);
352 ALTER TABLE t2 DROP FOREIGN KEY fidx, ALGORITHM=COPY;
353 UPDATE t1 SET fld1= 2;
354 SELECT fld2 FROM t2;
355 SELECT * FROM t2;
356 DROP TABLE t2, t1;
357
358 --echo # DROP FOREIGN CONSTRAINT USING INPLACE
359 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
360 CREATE TABLE t2(fld1 INT NOT NULL,
361 fld2 INT AS (fld1) VIRTUAL, KEY(fld2),
362 CONSTRAINT fidx FOREIGN KEY (fld1) REFERENCES t1(fld1)
363 ON UPDATE CASCADE);
364 SET foreign_key_checks = 0;
365 ALTER TABLE t2 DROP FOREIGN KEY fidx, ALGORITHM=INPLACE;
366 SET foreign_key_checks = 1;
367 INSERT INTO t1 VALUES(1);
368 INSERT INTO t2 VALUES(1, DEFAULT);
369 UPDATE t1 SET fld1= 2;
370 SELECT fld2 FROM t2;
371 SELECT * FROM t2;
372 DROP TABLE t2, t1;
373
374 --echo # ADD VC INDEX and ADD FK IN SAME COPY ALTER
375 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
376 CREATE TABLE t2(fld1 INT NOT NULL,
377 fld2 INT AS (fld1) VIRTUAL);
378 INSERT INTO t1 VALUES(1);
379 INSERT INTO t2 VALUES(1, DEFAULT);
380 ALTER TABLE t2 ADD INDEX(fld2), ADD FOREIGN KEY (fld1) REFERENCES t1(fld1)
381 ON UPDATE CASCADE, ALGORITHM=copy;
382 UPDATE t1 SET fld1= 2;
383 SELECT fld2 FROM t2;
384 SELECT * FROM t2;
385 DROP TABLE t2, t1;
386
387 --echo # ADD VC INDEX and ADD FK IN SAME INPLACE ALTER
388 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
389 CREATE TABLE t2(fld1 INT NOT NULL,
390 fld2 INT AS (fld1) VIRTUAL);
391 INSERT INTO t1 VALUES(1);
392 INSERT INTO t2 VALUES(1, DEFAULT);
393 SET foreign_key_checks = 0;
394 ALTER TABLE t2 ADD INDEX(fld2), ADD FOREIGN KEY (fld1) REFERENCES t1(fld1)
395 ON UPDATE CASCADE, ALGORITHM=inplace;
396 SET foreign_key_checks = 1;
397 UPDATE t1 SET fld1= 2;
398 SELECT fld2 FROM t2;
399 SELECT * FROM t2;
400 DROP TABLE t2, t1;
401
402 --echo # ADD VC INDEX and DROP FK IN SAME COPY ALTER
403 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
404 CREATE TABLE t2(fld1 INT NOT NULL,
405 fld2 INT AS (fld1) VIRTUAL,
406 CONSTRAINT fidx FOREIGN KEY(fld1) REFERENCES t1(fld1)
407 ON UPDATE CASCADE);
408 INSERT INTO t1 VALUES(1);
409 INSERT INTO t2 VALUES(1, DEFAULT);
410 ALTER TABLE t2 ADD INDEX(fld2), DROP FOREIGN KEY fidx, ALGORITHM=copy;
411 UPDATE t1 SET fld1= 2;
412 SELECT fld2 FROM t2;
413 SELECT * FROM t2;
414 DROP TABLE t2, t1;
415
416 --echo # ADD VC INDEX and DROP FK IN SAME INPLACE ALTER
417 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
418 CREATE TABLE t2(fld1 INT NOT NULL,
419 fld2 INT AS (fld1) VIRTUAL,
420 CONSTRAINT fidx FOREIGN KEY(fld1) REFERENCES t1(fld1)
421 ON UPDATE CASCADE);
422 INSERT INTO t1 VALUES(1);
423 INSERT INTO t2 VALUES(1, DEFAULT);
424 SET foreign_key_checks = 0;
425 ALTER TABLE t2 ADD INDEX(fld2), DROP FOREIGN KEY fidx, ALGORITHM=inplace;
426 SET foreign_key_checks = 1;
427 UPDATE t1 SET fld1= 2;
428 SELECT fld2 FROM t2;
429 SELECT * FROM t2;
430 DROP TABLE t2, t1;
431
432 --echo # DROP VC INDEX and ADD FK IN SAME COPY ALTER
433 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
434 CREATE TABLE t2(fld1 INT NOT NULL,
435 fld2 INT AS (fld1) VIRTUAL,
436 KEY idx(fld2));
437 INSERT INTO t1 VALUES(1);
438 INSERT INTO t2 VALUES(1, DEFAULT);
439 ALTER TABLE t2 DROP INDEX idx, ADD FOREIGN KEY (fld1) REFERENCES t1(fld1)
440 ON UPDATE CASCADE, ALGORITHM=COPY;
441 UPDATE t1 SET fld1= 2;
442 SELECT fld2 FROM t2;
443 SELECT * FROM t2;
444 DROP TABLE t2, t1;
445
446 --echo # DROP VC INDEX and ADD FK IN SAME INPLACE ALTER
447 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
448 CREATE TABLE t2(fld1 INT NOT NULL,
449 fld2 INT AS (fld1) VIRTUAL,
450 KEY idx(fld2));
451 INSERT INTO t1 VALUES(1);
452 INSERT INTO t2 VALUES(1, DEFAULT);
453 SET foreign_key_checks = 0;
454 ALTER TABLE t2 DROP INDEX idx, ADD FOREIGN KEY (fld1) REFERENCES t1(fld1)
455 ON UPDATE CASCADE, ALGORITHM=INPLACE;
456 SET foreign_key_checks = 1;
457 UPDATE t1 SET fld1= 2;
458 SELECT fld2 FROM t2;
459 SELECT * FROM t2;
460 DROP TABLE t2, t1;
461
462 --echo # DROP VC INDEX and DROP FK IN SAME COPY ALTER
463 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
464 CREATE TABLE t2(fld1 INT NOT NULL,
465 fld2 INT AS (fld1) VIRTUAL,
466 KEY idx(fld2),
467 CONSTRAINT fidx FOREIGN KEY(fld1) REFERENCES t1(fld1)
468 ON UPDATE CASCADE);
469 INSERT INTO t1 VALUES(1);
470 INSERT INTO t2 VALUES(1, DEFAULT);
471 ALTER TABLE t2 DROP KEY idx, DROP FOREIGN KEY fidx, ALGORITHM=COPY;
472 UPDATE t1 SET fld1= 2;
473 SELECT fld2 FROM t2;
474 SELECT * FROM t2;
475 DROP TABLE t2, t1;
476
477 --echo # DROP VC INDEX and DROP FK IN SAME INPLACE ALTER
478 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
479 CREATE TABLE t2(fld1 INT NOT NULL,
480 fld2 INT AS (fld1) VIRTUAL,
481 KEY idx(fld2),
482 CONSTRAINT fidx FOREIGN KEY(fld1) REFERENCES t1(fld1)
483 ON UPDATE CASCADE);
484 INSERT INTO t1 VALUES(1);
485 INSERT INTO t2 VALUES(1, DEFAULT);
486 SET foreign_key_checks = 0;
487 ALTER TABLE t2 DROP KEY idx, DROP FOREIGN KEY fidx, ALGORITHM=INPLACE;
488 SET foreign_key_checks = 1;
489 UPDATE t1 SET fld1= 2;
490 SELECT fld2 FROM t2;
491 SELECT * FROM t2;
492 DROP TABLE t2, t1;
493
494 # Foreign key constraint references to virtual index
495 CREATE TABLE t1 (f1 INT NOT NULL PRIMARY KEY)ENGINE=INNODB;
496 CREATE TABLE t2 (f1 INT NOT NULL, f2 INT AS (f1) VIRTUAL,
497 KEY (f1, f2), FOREIGN KEY(f1) REFERENCES t1(f1))ENGINE=INNODB;
498 INSERT INTO t1 VALUES(1);
499 INSERT INTO t2(f1) VALUES(1);
500 EXPLAIN SELECT f1, f2 FROM t2;
501 SELECT f1, f2 FROM t2;
502 --error ER_NO_REFERENCED_ROW_2
503 INSERT INTO t2(f1) VALUES(2);
504 DROP TABLE t2, t1;
505
506 # Update foreign key constraint references to virtual index
507 CREATE TABLE t1 (f1 INT NOT NULL PRIMARY KEY)ENGINE=INNODB;
508 CREATE TABLE t2 (f1 INT NOT NULL, f2 INT AS (f1) VIRTUAL,
509 KEY (f1, f2), FOREIGN KEY(f1) REFERENCES t1(f1)
510 ON UPDATE CASCADE)ENGINE=INNODB;
511 INSERT INTO t1 VALUES(1);
512 INSERT INTO t2(f1) VALUES(1);
513 EXPLAIN SELECT f1, f2 FROM t2;
514 SELECT f1, f2 FROM t2;
515 UPDATE t1 SET f1 = 2 WHERE f1 = 1;
516 EXPLAIN SELECT f1, f2 FROM t2;
517 SELECT f1, f2 FROM t2;
518 DROP TABLE t2, t1;
519
520 # Add foreign key constraint via inplace alter references to virtual index
521
522 CREATE TABLE t1 (f1 INT NOT NULL PRIMARY KEY)ENGINE=INNODB;
523 CREATE TABLE t2 (f1 INT NOT NULL, f2 INT AS (f1) VIRTUAL,
524 KEY (f1, f2))ENGINE=INNODB;
525 INSERT INTO t1 VALUES(1);
526 INSERT INTO t2(f1) VALUES(1);
527 SET FOREIGN_KEY_CHECKS = 0;
528 ALTER TABLE t2 ADD FOREIGN KEY (f1) REFERENCES t1(f1)
529 ON UPDATE CASCADE, ALGORITHM=INPLACE;
530 SET FOREIGN_KEY_CHECKS = 1;
531 UPDATE t1 SET f1 = 3;
532 EXPLAIN SELECT f1, f2 FROM t2;
533 SELECT f1, f2 FROM t2;
534 DROP TABLE t2, t1;
535
536 # Add foreign key constraint via copy alter references to virtual index
537
538 CREATE TABLE t1 (f1 INT NOT NULL PRIMARY KEY)ENGINE=INNODB;
539 CREATE TABLE t2 (f1 INT NOT NULL, f2 INT AS (f1) VIRTUAL,
540 KEY (f1, f2))ENGINE=INNODB;
541 INSERT INTO t1 VALUES(1);
542 INSERT INTO t2(f1) VALUES(1);
543 ALTER TABLE t2 ADD FOREIGN KEY (f1) REFERENCES t1(f1)
544 ON UPDATE CASCADE, ALGORITHM=COPY;
545 UPDATE t1 SET f1 = 3;
546 EXPLAIN SELECT f1, f2 FROM t2;
547 SELECT f1, f2 FROM t2;
548 DROP TABLE t2, t1;
549
550 # Drop column via inplace alter which triggers to remove the FK index idx
551
552 CREATE TABLE t1(f1 INT NOT NULL, PRIMARY KEY(f1))ENGINE=INNODB;
553 CREATE TABLE t2(f1 INT NOT NULL, f2 INT AS (1) VIRTUAL,
554 f3 INT AS (2) VIRTUAL,
555 FOREIGN KEY idx (f1) REFERENCES t1(f1) ON UPDATE CASCADE,
556 KEY idx1 (f2, f1, f3))ENGINE=INNODB;
557 INSERT INTO t1 VALUES(1);
558 INSERT INTO t2(f1) VALUES(1);
559 ALTER TABLE t2 DROP COLUMN f2, ALGORITHM=INPLACE;
560 UPDATE t1 SET f1 = 3;
561 EXPLAIN SELECT f1, f3 FROM t2;
562 SELECT f1, f3 FROM t2;
563 DROP TABLE t2, t1;
564
565 # Drop column via copy alter which triggers to remove the FK index idx
566
567 CREATE TABLE t1(f1 INT NOT NULL, PRIMARY KEY(f1))ENGINE=INNODB;
568 CREATE TABLE t2(f1 INT NOT NULL, f2 INT AS (1) VIRTUAL,
569 f3 INT AS (2) VIRTUAL,
570 FOREIGN KEY idx (f1) REFERENCES t1(f1) ON UPDATE CASCADE,
571 KEY idx1 (f2, f1, f3))ENGINE=INNODB;
572 INSERT INTO t1 VALUES(1);
573 INSERT INTO t2(f1) VALUES(1);
574 ALTER TABLE t2 DROP COLUMN f2, ALGORITHM=COPY;
575 UPDATE t1 SET f1 = 3;
576 EXPLAIN SELECT f1, f3 FROM t2;
577 SELECT f1, f3 FROM t2;
578 DROP TABLE t2, t1;
579
580 --echo #
581 --echo # MDEV-15553 Assertion failed in dict_table_get_col_name
582 --echo #
583 CREATE TABLE t1 (
584 c1 TIMESTAMP,
585 c2 YEAR,
586 c3 TIME,
587 c4 CHAR(10),
588 v1 TIMESTAMP AS (c1) VIRTUAL,
589 v2 YEAR AS (c2) VIRTUAL,
590 v3 TIME AS (c3) VIRTUAL,
591 v4 CHAR(10) AS (c4) VIRTUAL
592 ) ENGINE=InnoDB;
593 --error ER_CANT_CREATE_TABLE
594 ALTER TABLE t1 ADD CONSTRAINT fk FOREIGN KEY (v4) REFERENCES nosuch(col);
595 SET foreign_key_checks=0;
596 --error ER_FK_NO_INDEX_CHILD
597 ALTER TABLE t1 ADD CONSTRAINT fk FOREIGN KEY (v4) REFERENCES nosuch(col);
598 ALTER TABLE t1 ADD INDEX(v4);
599 ALTER TABLE t1 ADD CONSTRAINT fk FOREIGN KEY (v4) REFERENCES nosuch(col);
600 SET foreign_key_checks=1;
601 SHOW CREATE TABLE t1;
602 ALTER TABLE t1 DROP FOREIGN KEY fk;
603 --error ER_CANT_CREATE_TABLE
604 ALTER TABLE t1 ADD CONSTRAINT fk FOREIGN KEY (v4) REFERENCES nosuch(col);
605 SHOW CREATE TABLE t1;
606 # Cleanup
607 DROP TABLE t1;
608
609 --echo #
610 --echo # MDEV-20396 Server crashes after DELETE with SEL NULL Foreign key and a
611 --echo # virtual column in index
612 --echo #
613 CREATE TABLE parent
614 (
615 ID int unsigned NOT NULL,
616 PRIMARY KEY (ID)
617 );
618
619 CREATE TABLE child
620 (
621 ID int unsigned NOT NULL,
622 ParentID int unsigned NULL,
623 Value int unsigned NOT NULL DEFAULT 0,
624 Flag int unsigned AS (Value) VIRTUAL,
625 PRIMARY KEY (ID),
626 KEY (ParentID, Flag),
627 FOREIGN KEY (ParentID) REFERENCES parent (ID) ON DELETE SET NULL
628 ON UPDATE CASCADE
629 );
630
631 INSERT INTO parent (ID) VALUES (100);
632 INSERT INTO child (ID,ParentID,Value) VALUES (123123,100,1);
633 DELETE FROM parent WHERE ID=100;
634 select * from child;
635 INSERT INTO parent (ID) VALUES (100);
636 UPDATE child SET ParentID=100 WHERE ID=123123;
637
638 # Cleanup
639 DROP TABLE child, parent;
640 --echo #
641 --echo # MDEV-23387 dict_load_foreign() fails to load the table during alter
642 --echo #
643 SET FOREIGN_KEY_CHECKS=0;
644 CREATE TABLE t1(f1 INT NOT NULL, f2 INT NOT NULL,
645 f3 INT AS (f1) VIRTUAL,
646 INDEX(f1), INDEX(f2))ENGINE=InnoDB;
647 ALTER TABLE t1 ADD CONSTRAINT r FOREIGN KEY(f2) REFERENCES t1(f1), LOCK=NONE;
648 SHOW CREATE TABLE t1;
649 ALTER TABLE t1 DROP INDEX f1;
650 ALTER TABLE t1 DROP f3;
651 DROP TABLE t1;
652
653 --echo #
654 --echo # MDEV-24041 Generated column DELETE with FOREIGN KEY crash InnoDB
655 --echo #
656 SET FOREIGN_KEY_CHECKS=1;
657 CREATE DATABASE `a-b`;
658 USE `a-b`;
659 CREATE TABLE emails (
660 id int,
661 PRIMARY KEY (id)
662 ) ENGINE=InnoDB;
663
664 CREATE TABLE email_stats (
665 id int,
666 email_id int,
667 date_sent char(4),
668 generated_email_id int as (email_id),
669 #generated_sent_date DATE GENERATED ALWAYS AS (date_sent),
670 PRIMARY KEY (id),
671 KEY mautic_generated_sent_date_email_id (generated_email_id),
672 FOREIGN KEY (email_id) REFERENCES emails (id) ON DELETE SET NULL
673 ON UPDATE CASCADE
674 ) ENGINE=InnoDB;
675
676
677 CREATE TABLE emails_metadata (
678 email_id int,
679 PRIMARY KEY (email_id),
680 CONSTRAINT FK FOREIGN KEY (email_id) REFERENCES emails (id) ON DELETE CASCADE
681 ON UPDATE CASCADE
682 ) ENGINE=InnoDB;
683
684
685 INSERT INTO emails VALUES (1);
686 INSERT INTO email_stats (id, email_id, date_sent) VALUES (1,1,'Jan');
687 INSERT INTO emails_metadata VALUES (1);
688
689 UPDATE emails SET id=2;
690 DELETE FROM emails;
691
692 DROP TABLE email_stats;
693 DROP TABLE emails_metadata;
694 DROP TABLE emails;
695 DROP DATABASE `a-b`;
696 USE test;
697
698 --echo #
699 --echo # Bug#33053297 VIRTUAL INDEX CORRUPTED DURING CASCADE UPDATE ON CHILD TABLE
700 --echo #
701
702 --echo # Test-Case 1
703 CREATE TABLE emails (
704 id int unsigned NOT NULL AUTO_INCREMENT,
705 PRIMARY KEY (id)
706 ) ENGINE=InnoDB;
707
708 CREATE TABLE email_stats (
709 id bigint unsigned NOT NULL AUTO_INCREMENT,
710 email_id int unsigned DEFAULT NULL,
711 date_sent datetime NOT NULL,
712 generated_sent_date date GENERATED ALWAYS AS
713 (concat(year(date_sent), '-', lpad(month(date_sent), 2, '0'),
714 '-', lpad(dayofmonth(date_sent), 2, '0'))),
715 PRIMARY KEY (id),
716 KEY IDX_ES1 (email_id),
717 KEY mautic_generated_sent_date_email_id(generated_sent_date, email_id),
718 FOREIGN KEY (email_id) REFERENCES emails (id) ON DELETE SET NULL
719 ) ENGINE = InnoDB;
720
721 INSERT INTO emails VALUES (1);
722 INSERT INTO email_stats (id, email_id, date_sent)
723 VALUES (1, 1, '2020-10-22 13:32:41');
724 SELECT * FROM email_stats;
725
726 DELETE FROM emails;
727 DELETE FROM email_stats;
728
729 --echo # Clean up.
730 DROP TABLE email_stats;
731 DROP TABLE emails;
732
733 --echo # Test-Case 2
734 CREATE TABLE emails (
735 id int unsigned NOT NULL AUTO_INCREMENT,
736 PRIMARY KEY (id)
737 ) ENGINE = InnoDB
738 DEFAULT CHARSET = utf8mb4
739 COLLATE = utf8mb4_unicode_ci
740 ROW_FORMAT = DYNAMIC;
741
742 CREATE TABLE email_stats (
743 id bigint unsigned NOT NULL AUTO_INCREMENT,
744 email_id int unsigned DEFAULT NULL,
745 date_sent datetime NOT NULL,
746 generated_sent_date date GENERATED ALWAYS AS
747 (concat(year(date_sent), '-', lpad(month(date_sent), 2, '0'),
748 '-', lpad(dayofmonth(date_sent), 2, '0'))),
749 PRIMARY KEY (id),
750 KEY IDX_ES1 (email_id),
751 KEY mautic_generated_sent_date_email_id(generated_sent_date, email_id),
752 FOREIGN KEY (email_id) REFERENCES emails (id)
753 ON DELETE SET NULL
754 ON UPDATE SET NULL
755 ) ENGINE = InnoDB;
756
757 INSERT INTO emails VALUES (1);
758 INSERT INTO email_stats (id, email_id, date_sent)
759 VALUES (1, 1, '2020-10-22 13:32:41');
760
761 UPDATE emails SET id = 2 where id = 1;
762
763 SELECT id FROM email_stats WHERE generated_sent_date IS NULL;
764 SELECT * FROM email_stats;
765 UPDATE email_stats
766 SET email_id=2
767 WHERE DATE(generated_sent_date) = '2020-10-22';
768 SELECT * FROM email_stats;
769
770 --echo # Clean up.
771 DROP TABLE email_stats;
772 DROP TABLE emails;
773
774 --echo # Test-case 3
775 CREATE TABLE emails (
776 id int unsigned NOT NULL AUTO_INCREMENT,
777 PRIMARY KEY (id)
778 ) ENGINE = INNODB
779 DEFAULT CHARSET = utf8mb4
780 COLLATE = utf8mb4_unicode_ci
781 ROW_FORMAT = DYNAMIC;
782 CREATE TABLE email_stats (
783 id bigint unsigned NOT NULL AUTO_INCREMENT,
784 email_id int unsigned DEFAULT NULL,
785 date_sent datetime NOT NULL,
786 generated_sent_email varchar(20) GENERATED ALWAYS AS
787 (CONCAT(YEAR(date_sent), '-', COALESCE(email_id, '$'))),
788 PRIMARY KEY (id),
789 KEY idx_es1 (email_id),
790 KEY mautic_generated_sent_date_email(generated_sent_email, email_id),
791 FOREIGN KEY (email_id) REFERENCES emails (id) ON DELETE SET NULL
792 ) ENGINE = INNODB;
793
794 INSERT INTO emails VALUES (1);
795 INSERT INTO email_stats (id, email_id, date_sent)
796 VALUES (1, 1, '2020-10-22 13:32:41');
797 SELECT * FROM email_stats;
798 SELECT date_sent FROM email_stats WHERE generated_sent_email = '2020-1';
799
800 DELETE FROM emails;
801
802 SELECT * FROM email_stats;
803 SELECT date_sent FROM email_stats WHERE generated_sent_email = '2020-$';
804
805 --echo # Clean up.
806 DROP TABLE email_stats;
807 DROP TABLE emails;
808
809 --echo # Test-case 4
810 CREATE TABLE emails (
811 id int unsigned NOT NULL AUTO_INCREMENT,
812 PRIMARY KEY (id)
813 ) ENGINE = INNODB;
814
815 CREATE TABLE email_stats (
816 id bigint unsigned NOT NULL AUTO_INCREMENT,
817 email_id int unsigned DEFAULT NULL,
818 date_sent datetime NOT NULL,
819 generated_sent_email varchar(20) GENERATED ALWAYS AS
820 (CONCAT(YEAR(date_sent), '-', COALESCE(email_id, '$'))),
821 PRIMARY KEY (id),
822 KEY idx_es1 (email_id),
823 KEY mautic_generated_sent_date_email(generated_sent_email, email_id),
824 FOREIGN KEY (email_id) REFERENCES emails (id) ON UPDATE SET NULL
825 ) ENGINE = INNODB;
826
827 INSERT INTO emails VALUES (1);
828 INSERT INTO email_stats (id, email_id, date_sent)
829 VALUES (1, 1, '2020-10-22 13:32:41');
830 SELECT * FROM email_stats;
831 SELECT date_sent FROM email_stats WHERE generated_sent_email = '2020-1';
832
833 UPDATE emails SET id = 2 WHERE id = 1;
834
835 SELECT * FROM email_stats;
836 SELECT date_sent FROM email_stats WHERE generated_sent_email = '2020-$';
837
838 #clean up.
839 DROP TABLE email_stats;
840 DROP TABLE emails;
841
842 CREATE TABLE emails (breaker int unsigned,
843 KEY (breaker),
844 id int unsigned NOT NULL AUTO_INCREMENT,
845 PRIMARY KEY (id)
846 ) ENGINE=INNODB;
847
848 CREATE TABLE email_stats (
849 id bigint unsigned NOT NULL AUTO_INCREMENT,
850 email_id int unsigned DEFAULT NULL,
851 date_sent datetime NOT NULL,
852 generated_sent_email varchar(20) GENERATED ALWAYS AS
853 (CONCAT(YEAR(date_sent),
854 '-',
855 COALESCE(email_id, '$'))),
856 PRIMARY KEY (id),
857 KEY idx_es1 (email_id),
858 KEY mautic_generated_sent_date_email (generated_sent_email, email_id),
859 FOREIGN KEY fk_ea1 (email_id) REFERENCES emails (breaker)
860 ON DELETE SET NULL
861 ) ENGINE=INNODB;
862
863 show create table email_stats;
864 INSERT INTO emails VALUES (1,1);
865 INSERT INTO email_stats(id, email_id, date_sent)
866 VALUES (1, 1, '2020-10-22 13:32:41');
867 SELECT * FROM email_stats;
868 SELECT date_sent FROM email_stats WHERE generated_sent_email = '2020-1';
869 DELETE FROM emails;
870 SELECT * FROM email_stats;
871 SELECT date_sent
872 FROM email_stats force index (mautic_generated_sent_date_email)
873 WHERE generated_sent_email = '2020-$';
874 SELECT date_sent
875 FROM email_stats force index (idx_es1)
876 WHERE generated_sent_email = '2020-$';
877
878 DROP TABLE email_stats;
879 DROP TABLE emails;