1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
|
--source suite/versioning/common.inc
--source include/have_partition.inc
--source suite/versioning/engines.inc
create table t (a int);
--error ER_VERS_NOT_VERSIONED
delete history from t before system_time now();
# TRUNCATE is not DELETE and trigger must not be called.
--replace_result $sys_datatype_expl SYS_TYPE
eval create or replace table t (
a int,
row_start $sys_datatype_expl as row start invisible,
row_end $sys_datatype_expl as row end invisible,
period for system_time (row_start, row_end))
with system versioning;
insert into t values (1);
update t set a=2;
set @test = 'correct';
create trigger trg_before before delete on t for each row set @test = 'incorrect';
create trigger trg_after after delete on t for each row set @test = 'incorrect';
delete history from t;
select @test from t;
drop table t;
--replace_result $sys_datatype_expl SYS_TYPE
eval create or replace table t (
a int,
row_start $sys_datatype_expl as row start invisible,
row_end $sys_datatype_expl as row end invisible,
period for system_time (row_start, row_end))
with system versioning;
insert into t values (1), (2);
update t set a=11 where a=1;
--real_sleep 0.01
set @ts1=now(6);
--real_sleep 0.01
update t set a=22 where a=2;
select * from t for system_time all;
delete history from t before system_time timestamp @ts1;
select * from t for system_time all;
prepare stmt from 'delete history from t';
execute stmt; drop prepare stmt;
select * from t for system_time all;
delete from t;
delimiter ~~;
create or replace procedure truncate_sp()
begin
delete history from t before system_time timestamp now(6);
end~~
delimiter ;~~
call truncate_sp;
select * from t for system_time all;
drop procedure truncate_sp;
--echo # Truncate partitioned
create or replace table t (a int)
with system versioning
partition by system_time limit 1 (
partition p0 history,
partition p1 history,
partition pn current);
insert into t values (1);
update t set a= 2;
update t set a= 3;
delete history from t;
select * from t for system_time all;
--echo # VIEW
--replace_result $sys_datatype_expl SYS_TYPE
eval create or replace table t (
i int,
row_start $sys_datatype_expl as row start invisible,
row_end $sys_datatype_expl as row end invisible,
period for system_time (row_start, row_end))
with system versioning;
delete history from t;
create or replace view v as select * from t;
--error ER_IT_IS_A_VIEW
delete history from v;
create or replace table t (i int);
--error ER_VERS_NOT_VERSIONED
delete history from t;
create or replace view v as select * from t;
--error ER_IT_IS_A_VIEW
delete history from v;
--error ER_VERS_NOT_VERSIONED
prepare stmt from 'delete history from t';
drop table t;
drop view v;
#
# MDEV-15402 Assertion `table' failed in mysql_delete on attempt to delete history from view
#
create or replace table t (i int);
create or replace view v as select * from t;
--error ER_IT_IS_A_VIEW
drop table v;
lock table v write;
--error ER_IT_IS_A_VIEW
delete history from v before system_time now(6);
unlock tables;
drop view v;
drop table t;
#
# MDEV-16783 Assertion `!conds' failed in mysql_delete upon 2nd execution of SP with DELETE HISTORY
#
create table t1 (i int) with system versioning;
create procedure pr() delete history from t1 before system_time now();
call pr;
call pr;
drop procedure pr;
drop table t1;
--echo # MDEV-15966 Behavior for TRUNCATE versioned table is not documented and not covered by tests
create or replace table t1 (id int);
create or replace table t2 (id int) with system versioning;
-- echo # force cleaning table shares
flush tables t1, t2;
truncate table t1;
--error ER_VERS_NOT_SUPPORTED
truncate table t2;
-- echo # fetch table shares
describe t1;
describe t2;
truncate table t1;
--error ER_VERS_NOT_SUPPORTED
truncate table t2;
--echo # enter locked tables mode
lock tables t1 WRITE, t2 WRITE;
truncate t1;
--error ER_VERS_NOT_SUPPORTED
truncate t2;
unlock tables;
--echo #
--echo # MDEV-19814 Assertion `update->n_fields < ulint(table->n_cols + table->n_v_cols)' on DELETE HISTORY
--echo #
--replace_result $sys_datatype_expl SYS_TYPE
eval create or replace table t1 (
f varchar(1),
row_start $sys_datatype_expl as row start,
row_end $sys_datatype_expl as row end,
period for system_time (row_start, row_end))
with system versioning;
insert into t1 (f) values ('a'), ('b'), ('c'), ('d'), ('e'), ('f'), ('g'), ('h');
delete from t1;
delete history from t1;
drop table t1;
--echo #
--echo # MDEV-20186 Wrong result or Assertion on INSERT after DELETE HISTORY
--echo #
create or replace table t1 (a int check (a > 0)) with system versioning;
delete history from t1;
insert into t1 values (1);
select * from t1;
drop table t1;
drop database test;
create database test;
|
