Debian Codegraph @

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

raw
1 source include/have_innodb.inc;
2 set default_storage_engine=innodb;
3
4 #
5 # MDEV-13708 Crash with indexed virtual columns and FK cascading deletes
6 #
7
8 create table t1 (id int primary key, id2 int as (id) virtual, key id2 (id2));
9 create table t2 (id int key, constraint fk_id foreign key (id) references t1 (id) on delete cascade);
10 insert into t1 (id) values (1), (2);
11 insert into t2 (id) values (1), (2);
12 delete from t1;
13 select * from t1;
14 select * from t2;
15 drop table t2;
16 drop table t1;
17
18 --echo #
19 --echo # End of 10.2 tests
20 --echo #
21
22 --echo #
23 --echo # MDEV-18114 Foreign Key Constraint actions don't affect Virtual Column
24 --echo #
25 create table t1 (id int primary key);
26
27 # note that RESTRICT, NO ACTION, and DELETE CASCADE are fine
28 # because they don't change values of referenced columns
29
30 # virtual indexed
31 create or replace table t2 (id int, id2 int as (id) virtual, key(id2), foreign key (id) references t1 (id) on update restrict);
32 create or replace table t2 (id int, id2 int as (id) virtual, key(id2), foreign key (id) references t1 (id) on update no action);
33 create or replace table t2 (id int, id2 int as (id) virtual, key(id2), foreign key (id) references t1 (id) on delete cascade);
34 create or replace table t2 (id int, id2 int as (id) virtual, key(id2), foreign key (id) references t1 (id) on update cascade);
35 create or replace table t2 (id int, id2 int as (id) virtual, key(id2), foreign key (id) references t1 (id) on delete set null);
36 create or replace table t2 (id int, id2 int as (id) virtual, key(id2), foreign key (id) references t1 (id) on update set null);
37
38 # stored
39 create or replace table t2 (id int, id2 int as (id) stored, foreign key (id) references t1 (id) on update restrict);
40 create or replace table t2 (id int, id2 int as (id) stored, foreign key (id) references t1 (id) on update no action);
41 create or replace table t2 (id int, id2 int as (id) stored, foreign key (id) references t1 (id) on delete cascade);
42 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
43 create or replace table t2 (id int, id2 int as (id) stored, foreign key (id) references t1 (id) on update cascade);
44 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
45 create or replace table t2 (id int, id2 int as (id) stored, foreign key (id) references t1 (id) on delete set null);
46 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
47 create or replace table t2 (id int, id2 int as (id) stored, foreign key (id) references t1 (id) on update set null);
48
49 # stored indirect
50 create or replace table t2 (id int, id2 int as (id) virtual, id3 int as (id2) stored, foreign key (id) references t1 (id) on update restrict);
51 create or replace table t2 (id int, id2 int as (id) virtual, id3 int as (id2) stored, foreign key (id) references t1 (id) on update no action);
52 create or replace table t2 (id int, id2 int as (id) virtual, id3 int as (id2) stored, foreign key (id) references t1 (id) on delete cascade);
53 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
54 create or replace table t2 (id int, id2 int as (id) virtual, id3 int as (id2) stored, foreign key (id) references t1 (id) on update cascade);
55 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
56 create or replace table t2 (id int, id2 int as (id) virtual, id3 int as (id2) stored, foreign key (id) references t1 (id) on delete set null);
57 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
58 create or replace table t2 (id int, id2 int as (id) virtual, id3 int as (id2) stored, foreign key (id) references t1 (id) on update set null);
59
60 # default
61 create or replace table t2 (id int, id2 int default (id), foreign key (id) references t1 (id) on update restrict);
62 create or replace table t2 (id int, id2 int default (id), foreign key (id) references t1 (id) on update no action);
63 create or replace table t2 (id int, id2 int default (id), foreign key (id) references t1 (id) on delete cascade);
64 create or replace table t2 (id int, id2 int default (id), foreign key (id) references t1 (id) on update cascade);
65 create or replace table t2 (id int, id2 int default (id), foreign key (id) references t1 (id) on delete set null);
66 create or replace table t2 (id int, id2 int default (id), foreign key (id) references t1 (id) on update set null);
67
68 # field check
69 create or replace table t2 (id int, id2 int check (id), foreign key (id) references t1 (id) on update restrict);
70 create or replace table t2 (id int, id2 int check (id), foreign key (id) references t1 (id) on update no action);
71 create or replace table t2 (id int, id2 int check (id), foreign key (id) references t1 (id) on delete cascade);
72 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
73 create or replace table t2 (id int, id2 int check (id), foreign key (id) references t1 (id) on update cascade);
74 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
75 create or replace table t2 (id int, id2 int check (id), foreign key (id) references t1 (id) on delete set null);
76 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
77 create or replace table t2 (id int, id2 int check (id), foreign key (id) references t1 (id) on update set null);
78
79 # table check
80 create or replace table t2 (id int, id2 int, check (id), foreign key (id) references t1 (id) on update restrict);
81 create or replace table t2 (id int, id2 int, check (id), foreign key (id) references t1 (id) on update no action);
82 create or replace table t2 (id int, id2 int, check (id), foreign key (id) references t1 (id) on delete cascade);
83 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
84 create or replace table t2 (id int, id2 int, check (id), foreign key (id) references t1 (id) on update cascade);
85 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
86 create or replace table t2 (id int, id2 int, check (id), foreign key (id) references t1 (id) on delete set null);
87 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
88 create or replace table t2 (id int, id2 int, check (id), foreign key (id) references t1 (id) on update set null);
89
90 # table check indirect
91 create or replace table t2 (id int, id2 int as (id) virtual, check (id2), foreign key (id) references t1 (id) on update restrict);
92 create or replace table t2 (id int, id2 int as (id) virtual, check (id2), foreign key (id) references t1 (id) on update no action);
93 create or replace table t2 (id int, id2 int as (id) virtual, check (id2), foreign key (id) references t1 (id) on delete cascade);
94 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
95 create or replace table t2 (id int, id2 int as (id) virtual, check (id2), foreign key (id) references t1 (id) on update cascade);
96 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
97 create or replace table t2 (id int, id2 int as (id) virtual, check (id2), foreign key (id) references t1 (id) on delete set null);
98 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
99 create or replace table t2 (id int, id2 int as (id) virtual, check (id2), foreign key (id) references t1 (id) on update set null);
100
101 drop table if exists t2, t1;
102
103 --echo #
104 --echo # MDEV-31853 Assertion failure in Column_definition::check_vcol_for_key upon adding FK
105 --echo #
106 create table t (a int, b int, c int generated always as (a), key(b), key(c));
107 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
108 alter table t add foreign key (a) references t (b) on update set null, algorithm=nocopy;
109 alter table t add foreign key (a) references t (b);
110 show create table t;
111 drop table t;
112
113 --echo #
114 --echo # End of 10.5 tests
115 --echo #