Debian Codegraph @

mysql-test/suite/innodb/r/virtual_fk_restart.result

raw
1 #
2 # Bug#22469130: FOREIGN KEY ON DELETE CASCADE NOT ALLOWED
3 # WHEN A VIRTUAL INDEX EXISTS.
4 # Add the VIRTUAL INDEX contains fk constraINT column
5 # using INPLACE alter operatiON
6 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
7 CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT NOT NULL,
8 fld3 INT AS (fld2) VIRTUAL, KEY(fld1),
9 FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE CASCADE);
10 INSERT INTO t1(fld1) VALUES(1);
11 INSERT INTO t2(fld1, fld2) VALUES(1, 2);
12 # restart
13 UPDATE t1 SET fld1= 2;
14 SELECT fld3, fld1 FROM t2;
15 fld3 fld1
16 2 2
17 alter TABLE t2 ADD INDEX vk(fld3, fld1), ALGORITHM=INPLACE;
18 UPDATE t1 SET fld1=3;
19 SELECT fld3, fld1 FROM t2;
20 fld3 fld1
21 2 3
22 EXPLAIN SELECT fld3, fld1 FROM t2;
23 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
24 x x t2 x index x vk x x x x x
25 DROP TABLE t2, t1;
26 # TEMPORARY TABLE NAME and CHILD TABLE NAME are same
27 CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
28 CREATE TABLE t2(fld1 INT NOT NULL,
29 fld2 INT AS (fld1) VIRTUAL,
30 KEY(fld2),
31 FOREIGN KEY(fld1) REFERENCES t1(fld1)
32 ON UPDATE CASCADE);
33 INSERT INTO t1 VALUES(1), (2);
34 INSERT INTO t2 VALUES(1, DEFAULT), (2, default);
35 # restart
36 CREATE TEMPORARY TABLE t2 (fld1 INT NOT NULL)ENGINE=INNODB;
37 UPDATE t1 SET fld1= 3 WHERE fld1= 2;
38 SELECT fld2 FROM t2;
39 fld2
40 1
41 3
42 EXPLAIN SELECT fld2 FROM t2;
43 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
44 x x t2 x index x fld2 x x x x x
45 CHECK TABLE t2;
46 Table Op Msg_type Msg_text
47 test.t2 check status OK
48 DROP TABLE t2;
49 DROP TABLE t2, t1;