Debian Codegraph @

mysql-test/suite/innodb/t/virtual_fk.test

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