Debian Codegraph @

mysql-test/suite/gcol/r/innodb_virtual_fk.result

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