Debian Codegraph @

mysql-test/suite/federated/federatedx_versioning.result

raw
1 create or replace table t1 (
2 x int,
3 row_start SYS_TYPE as row start invisible,
4 row_end SYS_TYPE as row end invisible,
5 period for system_time (row_start, row_end))
6 with system versioning;
7 create or replace table tf engine=FEDERATED connection='mysql://[email protected]:MASTER_MYPORT/test/t1';
8 show create table tf;
9 Table Create Table
10 tf CREATE TABLE `tf` (
11 `x` int(11) DEFAULT NULL,
12 `row_start` SYS_TYPE DEFAULT '1971-01-01 00:00:00.000000' INVISIBLE,
13 `row_end` SYS_TYPE DEFAULT '1971-01-01 00:00:00.000000' INVISIBLE
14 ) ENGINE=FEDERATED DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci CONNECTION='mysql://[email protected]:MASTER_MYPORT/test/t1'
15 # INSERT
16 insert into t1 values (1);
17 select * from tf;
18 x
19 1
20 insert into tf (x) values (2);
21 select * from t1;
22 x
23 1
24 2
25 select * from tf;
26 x
27 1
28 2
29 # UPDATE
30 update tf set x= x + 2;
31 select *, check_row(row_start, row_end) from t1 for system_time all
32 order by x;
33 x check_row(row_start, row_end)
34 1 HISTORICAL ROW
35 2 HISTORICAL ROW
36 3 CURRENT ROW
37 4 CURRENT ROW
38 # DELETE
39 delete from tf;
40 select *, check_row(row_start, row_end) from t1 for system_time all
41 order by x;
42 x check_row(row_start, row_end)
43 1 HISTORICAL ROW
44 2 HISTORICAL ROW
45 3 HISTORICAL ROW
46 4 HISTORICAL ROW
47 select * from tf;
48 x
49 # MDEV-15966: Behavior for TRUNCATE versioned table is not documented
50 # and not covered by tests
51 # As of standard, TRUNCATE on versioned tables is forbidden
52 truncate tf;
53 ERROR HY000: Got error 10000 'Error on remote system: 4137: System-versioned tables do not support TRUNCATE TABLE' from FEDERATED
54 delete history from t1;
55 select * from t1 for system_time all;
56 x
57 # REPLACE
58 create or replace table t2 (
59 id int primary key, y int,
60 row_start SYS_TYPE as row start invisible,
61 row_end SYS_TYPE as row end invisible,
62 period for system_time (row_start, row_end))
63 with system versioning;
64 create or replace table t2f engine=FEDERATED connection='mysql://[email protected]:MASTER_MYPORT/test/t2';
65 insert t2f (id, y) values (1, 2);
66 replace t2f (id, y) values (1, 3);
67 select *, check_row(row_start, row_end) from t2 for system_time all
68 order by y;
69 id y check_row(row_start, row_end)
70 1 2 HISTORICAL ROW
71 1 3 CURRENT ROW
72 # VIEW
73 create or replace view vt1 as select * from tf;
74 insert into vt1 values (3);
75 update vt1 set x= x + 1;
76 select *, check_row(row_start, row_end) from t1 for system_time all
77 order by x;
78 x check_row(row_start, row_end)
79 3 HISTORICAL ROW
80 4 CURRENT ROW
81 delete from vt1;
82 select *, check_row(row_start, row_end) from t1 for system_time all
83 order by x;
84 x check_row(row_start, row_end)
85 3 HISTORICAL ROW
86 4 HISTORICAL ROW
87 # multi-UPDATE
88 delete from t1;
89 delete history from t1;
90 delete from t2;
91 delete history from t2;
92 insert into t1 values (1);
93 insert into t2 values (2, 2);
94 update tf, t2f set tf.x= 11, t2f.y= 22;
95 select *, check_row(row_start, row_end) from t1 for system_time all
96 order by x;
97 x check_row(row_start, row_end)
98 1 HISTORICAL ROW
99 11 CURRENT ROW
100 select *, check_row(row_start, row_end) from t2 for system_time all
101 order by y;
102 id y check_row(row_start, row_end)
103 2 2 HISTORICAL ROW
104 2 22 CURRENT ROW
105 drop view vt1;
106 drop tables t1, t2, t2f, tf;