Debian Codegraph @

mysql-test/suite/gcol_ndb/t/gcol_stored_fk_cascade.test

raw
1 --source include/have_ndb.inc
2
3 # ----------------------------------------
4 # Test 1
5 # DEMONSTRATE CASCADING UPDATE WITH NDB
6
7 create table purchase_order_v1 (
8 id int not null primary key,
9 sequence_no int not null,
10 user varchar(30),
11 unique index (sequence_no)
12 ) engine = ndb;
13
14 create table order_item_v1 (
15 id int not null auto_increment primary key,
16 order_seq_no int not null,
17 description varchar(40),
18 quantity int not null,
19 unit_cost decimal(6, 2),
20 foreign key (order_seq_no) references purchase_order_v1 (sequence_no)
21 on delete cascade on update cascade
22 ) engine = ndb;
23
24
25 # CREATE AN ORDER WITH ONE ITEM
26 insert into purchase_order_v1 values (1,1,"jdd");
27 insert into order_item_v1 values(default, 1, "10mm hex nut", 1000, .04);
28
29 # UPDATE THE SEQUENCE NUMBER.
30 # NDB CORRECTLY SHOWS THE CASCADING UPDATE.
31 update purchase_order_v1 set sequence_no = 2 where id = 1;
32 select * from order_item_v1 ;
33
34
35
36 # ----------------------------------------
37 # Test 2
38 # DEMONSTRATE (BUGGY) CASCADING UPDATE WITH gcol as source of cascade
39
40
41 create table purchase_order_v2 (
42 id int not null primary key,
43 sequence_no int as (id) stored unique,
44 user varchar(30)
45 ) engine = ndb;
46
47 create table order_item_v2 (
48 id int not null auto_increment primary key,
49 order_seq_no int not null,
50 description varchar(40),
51 quantity int not null,
52 unit_cost decimal(6, 2),
53 foreign key (order_seq_no) references purchase_order_v2 (sequence_no)
54 on delete cascade on update cascade
55 ) engine = ndb;
56
57
58 # CREATE AN ORDER WITH ONE ITEM
59 insert into purchase_order_v2 (id, user) values (1, "jdd");
60 insert into order_item_v2 values(default, 1, "10mm hex nut", 1000, .04);
61 select * from purchase_order_v2 ;
62 select * from order_item_v2;
63
64 # UPDATE THE PRIMARY KEY ==> GCOL ==> CASCADE TO ORDER_ITEM
65 #
66 # *** NOTE UNDOCUMENTED BEHAVIOR ***
67 # RATHER THAN UPDATING THE REFERENCED ROW, NDB DELETES IT.
68 #
69 update purchase_order_v2 set id = 2 where id = 1;
70 select * from order_item_v2 ;
71
72
73
74 # ----------------------------------------
75 # Test 3
76 # DEMONSTRATE (BUGGY) CASCADING UPDATE WHERE gcol depends on target of the cascade
77 # The cascade causes a change to the base column, but its dependent gcol is not updated
78
79 create table order_item_v3 (
80 id int not null auto_increment primary key,
81 order_seq_no int not null,
82 description varchar(40),
83 quantity int not null,
84 unit_cost decimal(6, 2),
85 gcol int as (100 + order_seq_no) stored,
86 foreign key (order_seq_no) references purchase_order_v1 (sequence_no)
87 on delete cascade on update cascade
88 ) engine = ndb;
89
90 # CREATE AN ORDER WITH ONE ITEM
91 insert into order_item_v3 values(default, 2, "10mm hex nut", 1000, .04, default);
92 select * from order_item_v3;
93 # UPDATE THE PARENT TABLE
94 # THIS CAUSES A CASCADING UPDATE TO THE FOREIGN KEY COLUMN
95 #
96 # *** NOTE UNDOCUMENTED BEHAVIOR ***
97 # THE GENERATED COLUMN WHICH DEPENDS ON THE FOREIGN KEY IS NOT UPDATED AFTER THE CASCADE
98 #
99 update purchase_order_v1 set sequence_no = 3 where id = 1;
100 select * from order_item_v3;
101
102
103 # CLEAN UP AFTER THIS TEST
104 drop table order_item_v3;
105 drop table order_item_v2;
106 drop table order_item_v1;
107 drop table purchase_order_v2;
108 drop table purchase_order_v1;