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
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
|
create table t (id int, s date, e date, period for apptime(s,e));
insert into t values(1, '1999-01-01', '2018-12-12');
insert into t values(1, '1999-01-01', '2017-01-01');
insert into t values(1, '2017-01-01', '2019-01-01');
insert into t values(2, '1998-01-01', '2018-12-12');
insert into t values(3, '1997-01-01', '2015-01-01');
insert into t values(4, '2016-01-01', '2020-01-01');
insert into t values(5, '2010-01-01', '2015-01-01');
create or replace table t1 (id int, s date, e date, period for apptime(s,e));
insert t1 select * from t;
create or replace table t2 (id int, s date, e date, period for apptime(s,e));
insert t2 select * from t;
update t for portion of apptime from '2000-01-01' to '2018-01-01'
set id=id + 6;
select * from t;
id s e
1 1999-01-01 2000-01-01
1 1999-01-01 2000-01-01
1 2018-01-01 2018-12-12
1 2018-01-01 2019-01-01
10 2016-01-01 2018-01-01
11 2010-01-01 2015-01-01
2 1998-01-01 2000-01-01
2 2018-01-01 2018-12-12
3 1997-01-01 2000-01-01
4 2018-01-01 2020-01-01
7 2000-01-01 2017-01-01
7 2000-01-01 2018-01-01
7 2017-01-01 2018-01-01
8 2000-01-01 2018-01-01
9 2000-01-01 2015-01-01
# Check triggers
create or replace table log_tbl(id int auto_increment primary key, log text) engine=myisam;
create or replace procedure log(s text)
insert into log_tbl(log) values(s);
create trigger tr1upd_t1 before update on t1
for each row call log(CONCAT('>UPD: ', old.id, ", ", old.s, ", ", old.e, ' -> ', new.id, ", ", new.s, ", ", new.e));
create trigger tr2upd_t1 after update on t1
for each row call log(CONCAT('<UPD: ', old.id, ", ", old.s, ", ", old.e, ' -> ', new.id, ", ", new.s, ", ", new.e));
create trigger tr1del_t1 before delete on t1
for each row call log(CONCAT('>DEL: ', old.id, ", ", old.s, ", ", old.e));
create trigger tr2del_t1 after delete on t1
for each row call log(CONCAT('<DEL: ', old.id, ", ", old.s, ", ", old.e));
create trigger tr1ins_t1 before insert on t1
for each row call log(CONCAT('>INS: ', new.id, ", ", new.s, ", ", new.e));
create trigger tr2ins_t1 after insert on t1
for each row call log(CONCAT('<INS: ', new.id, ", ", new.s, ", ", new.e));
update t1 for portion of apptime from '2000-01-01' to '2018-01-01'
set id=id + 6;
select * from t1;
id s e
1 1999-01-01 2000-01-01
1 1999-01-01 2000-01-01
1 2018-01-01 2018-12-12
1 2018-01-01 2019-01-01
10 2016-01-01 2018-01-01
11 2010-01-01 2015-01-01
2 1998-01-01 2000-01-01
2 2018-01-01 2018-12-12
3 1997-01-01 2000-01-01
4 2018-01-01 2020-01-01
7 2000-01-01 2017-01-01
7 2000-01-01 2018-01-01
7 2017-01-01 2018-01-01
8 2000-01-01 2018-01-01
9 2000-01-01 2015-01-01
select * from log_tbl order by id;
id log
1 >UPD: 1, 1999-01-01, 2018-12-12 -> 7, 2000-01-01, 2018-01-01
2 >INS: 1, 1999-01-01, 2000-01-01
3 <INS: 1, 1999-01-01, 2000-01-01
4 >INS: 1, 2018-01-01, 2018-12-12
5 <INS: 1, 2018-01-01, 2018-12-12
6 <UPD: 1, 1999-01-01, 2018-12-12 -> 7, 2000-01-01, 2018-01-01
7 >UPD: 1, 1999-01-01, 2017-01-01 -> 7, 2000-01-01, 2017-01-01
8 >INS: 1, 1999-01-01, 2000-01-01
9 <INS: 1, 1999-01-01, 2000-01-01
10 <UPD: 1, 1999-01-01, 2017-01-01 -> 7, 2000-01-01, 2017-01-01
11 >UPD: 1, 2017-01-01, 2019-01-01 -> 7, 2017-01-01, 2018-01-01
12 >INS: 1, 2018-01-01, 2019-01-01
13 <INS: 1, 2018-01-01, 2019-01-01
14 <UPD: 1, 2017-01-01, 2019-01-01 -> 7, 2017-01-01, 2018-01-01
15 >UPD: 2, 1998-01-01, 2018-12-12 -> 8, 2000-01-01, 2018-01-01
16 >INS: 2, 1998-01-01, 2000-01-01
17 <INS: 2, 1998-01-01, 2000-01-01
18 >INS: 2, 2018-01-01, 2018-12-12
19 <INS: 2, 2018-01-01, 2018-12-12
20 <UPD: 2, 1998-01-01, 2018-12-12 -> 8, 2000-01-01, 2018-01-01
21 >UPD: 3, 1997-01-01, 2015-01-01 -> 9, 2000-01-01, 2015-01-01
22 >INS: 3, 1997-01-01, 2000-01-01
23 <INS: 3, 1997-01-01, 2000-01-01
24 <UPD: 3, 1997-01-01, 2015-01-01 -> 9, 2000-01-01, 2015-01-01
25 >UPD: 4, 2016-01-01, 2020-01-01 -> 10, 2016-01-01, 2018-01-01
26 >INS: 4, 2018-01-01, 2020-01-01
27 <INS: 4, 2018-01-01, 2020-01-01
28 <UPD: 4, 2016-01-01, 2020-01-01 -> 10, 2016-01-01, 2018-01-01
29 >UPD: 5, 2010-01-01, 2015-01-01 -> 11, 2010-01-01, 2015-01-01
30 <UPD: 5, 2010-01-01, 2015-01-01 -> 11, 2010-01-01, 2015-01-01
# INSERT trigger only also works
create or replace table log_tbl(id int auto_increment primary key, log text) engine=myisam;
create or replace procedure log(s text)
insert into log_tbl(log) values(s);
create trigger tr1upd_t2 before update on t2
for each row call log(CONCAT('>UPD: ', old.id, ", ", old.s, ", ", old.e, ' -> ', new.id, ", ", new.s, ", ", new.e));
create trigger tr2upd_t2 after update on t2
for each row call log(CONCAT('<UPD: ', old.id, ", ", old.s, ", ", old.e, ' -> ', new.id, ", ", new.s, ", ", new.e));
create trigger tr1del_t2 before delete on t2
for each row call log(CONCAT('>DEL: ', old.id, ", ", old.s, ", ", old.e));
create trigger tr2del_t2 after delete on t2
for each row call log(CONCAT('<DEL: ', old.id, ", ", old.s, ", ", old.e));
create trigger tr1ins_t2 before insert on t2
for each row call log(CONCAT('>INS: ', new.id, ", ", new.s, ", ", new.e));
create trigger tr2ins_t2 after insert on t2
for each row call log(CONCAT('<INS: ', new.id, ", ", new.s, ", ", new.e));
drop trigger tr1upd_t2;
drop trigger tr2upd_t2;
update t2 for portion of apptime from '2000-01-01' to '2018-01-01'
set id=id + 6;
select * from t2;
id s e
1 1999-01-01 2000-01-01
1 1999-01-01 2000-01-01
1 2018-01-01 2018-12-12
1 2018-01-01 2019-01-01
10 2016-01-01 2018-01-01
11 2010-01-01 2015-01-01
2 1998-01-01 2000-01-01
2 2018-01-01 2018-12-12
3 1997-01-01 2000-01-01
4 2018-01-01 2020-01-01
7 2000-01-01 2017-01-01
7 2000-01-01 2018-01-01
7 2017-01-01 2018-01-01
8 2000-01-01 2018-01-01
9 2000-01-01 2015-01-01
select * from log_tbl order by id;
id log
1 >INS: 1, 1999-01-01, 2000-01-01
2 <INS: 1, 1999-01-01, 2000-01-01
3 >INS: 1, 2018-01-01, 2018-12-12
4 <INS: 1, 2018-01-01, 2018-12-12
5 >INS: 1, 1999-01-01, 2000-01-01
6 <INS: 1, 1999-01-01, 2000-01-01
7 >INS: 1, 2018-01-01, 2019-01-01
8 <INS: 1, 2018-01-01, 2019-01-01
9 >INS: 2, 1998-01-01, 2000-01-01
10 <INS: 2, 1998-01-01, 2000-01-01
11 >INS: 2, 2018-01-01, 2018-12-12
12 <INS: 2, 2018-01-01, 2018-12-12
13 >INS: 3, 1997-01-01, 2000-01-01
14 <INS: 3, 1997-01-01, 2000-01-01
15 >INS: 4, 2018-01-01, 2020-01-01
16 <INS: 4, 2018-01-01, 2020-01-01
select * from t for portion of apptime from 0 to 1 for system_time all;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'portion of apptime from 0 to 1 for system_time all' at line 1
update t for portion of apptime from 0 to 1 for system_time all set id=1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'system_time all set id=1' at line 1
# Modifying period start/end fields is forbidden.
# SQL16: 14.14 <update statement: searched>, Syntax Rules, 7)a)ii)
# Neither BSTARTCOL nor BENDCOL shall be an explicit <object column>
# contained in the <set clause list>.
update t for portion of apptime from '2000-01-01' to '2018-01-01'
set id= id + 6, s=subdate(s, 5), e=adddate(e, 5);
ERROR HY000: Column `s` used in period `apptime` specified in update SET list
# Precision timestamps
create or replace table t (id int, s timestamp(5), e timestamp(5),
period for apptime(s,e));
insert into t values(1, '1999-01-01', '2018-12-12');
insert into t values(1, '1999-01-01', '2017-01-01');
update t for portion of apptime from '2000-01-01 00:00:00.00015'
to '2018-01-01 12:34:56.31415'
set id= id + 5;
select * from t;
id s e
1 1999-01-01 00:00:00.00000 2000-01-01 00:00:00.00015
1 1999-01-01 00:00:00.00000 2000-01-01 00:00:00.00015
1 2018-01-01 12:34:56.31415 2018-12-12 00:00:00.00000
6 2000-01-01 00:00:00.00015 2017-01-01 00:00:00.00000
6 2000-01-01 00:00:00.00015 2018-01-01 12:34:56.31415
# Strings
create or replace table t (id int, str text, s date, e date,
period for apptime(s,e));
insert into t values(1, 'data', '1999-01-01', '2018-12-12');
insert into t values(1, 'other data', '1999-01-01', '2018-12-12');
update t for portion of apptime from '2000-01-01' to '2018-01-01'
set id= id + 5;
select * from t;
id str s e
1 data 1999-01-01 2000-01-01
1 data 2018-01-01 2018-12-12
1 other data 1999-01-01 2000-01-01
1 other data 2018-01-01 2018-12-12
6 data 2000-01-01 2018-01-01
6 other data 2000-01-01 2018-01-01
# multi-table UPDATE is impossible
create or replace table t1(x int);
update t for portion of apptime from '2000-01-01' to '2018-01-01', t1
set t.id= t.id + 5;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' t1
set t.id= t.id + 5' at line 1
update t1 set x= (select id from t for portion of apptime from '2000-01-01' to '2018-01-01');
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'portion of apptime from '2000-01-01' to '2018-01-01')' at line 1
# single-table views
create or replace view v1 as select * from t where id<10;
update v1 for portion of apptime from '2000-01-01' to '2018-01-01' set id= id + 5;
ERROR 42S02: 'v1' is a view
# multi-table views
create or replace view v1 as select * from t, t1 where x=id;
update v1 for portion of apptime from '2000-01-01' to '2018-01-01' set id= id + 5;
ERROR 42S02: 'v1' is a view
# SQL16: 14.14 <update statement: searched>, Syntax Rules, 7)a) iii-iv)
# Let FROMVAL be <point in time 1>. FROMVAL shall not generally contain a
# reference to a column of T or a <routine invocation>
# whose subject routine is an SQL-invoked routine that
# is possibly non-deterministic or that possibly modifies SQL-data.
# ...Same for <point in time 2> (TOVAL)
update t for portion of apptime from 5*(5+s) to 1 set t.id= t.id + 5;
ERROR HY000: Expression in FOR PORTION OF must be constant
update t for portion of apptime from 1 to e set t.id= t.id + 5;
ERROR HY000: Expression in FOR PORTION OF must be constant
set @s= '2000-01-01';
set @e= '2018-01-01';
create or replace function f() returns date return @e;
create or replace function g() returns date not deterministic return @e;
create or replace function h() returns date deterministic return @e;
update t for portion of apptime from @s to f() set t.id= t.id + 5;
ERROR HY000: Expression in FOR PORTION OF must be constant
update t for portion of apptime from @s to g() set t.id= t.id + 5;
ERROR HY000: Expression in FOR PORTION OF must be constant
# success
update t for portion of apptime from @s to h() set t.id= t.id + 5;
# select value is cached
update t for portion of apptime from (select s from t2 limit 1) to h() set t.id= t.id + 5;
# auto_inrement field is updated
create or replace table t (id int primary key auto_increment, x int,
s date, e date, period for apptime(s, e));
insert into t values (default, 1, '1999-01-01', '2018-12-12');
update t for portion of apptime from '2000-01-01' to '2018-01-01' set x= x + 5;
select * from t;
id x s e
1 6 2000-01-01 2018-01-01
2 1 1999-01-01 2000-01-01
3 1 2018-01-01 2018-12-12
truncate t;
insert into t values (default, 1, '1999-01-01', '2018-12-12');
update t for portion of apptime from '2000-01-01' to '2018-01-01' set x= 1;
select * from t;
id x s e
1 1 2000-01-01 2018-01-01
2 1 1999-01-01 2000-01-01
3 1 2018-01-01 2018-12-12
# generated columns are updated
create or replace table t (x int, s date, e date,
xs date as (s) stored, xe date as (e) stored,
period for apptime(s, e));
insert into t values(1, '1999-01-01', '2018-12-12', default, default);
select * from t;
x s e xs xe
1 1999-01-01 2018-12-12 1999-01-01 2018-12-12
update t for portion of apptime from '2000-01-01' to '2018-01-01' set x= x + 5;
select *, xs=s and xe=e from t;
x s e xs xe xs=s and xe=e
1 1999-01-01 2000-01-01 1999-01-01 2000-01-01 1
1 2018-01-01 2018-12-12 2018-01-01 2018-12-12 1
6 2000-01-01 2018-01-01 2000-01-01 2018-01-01 1
# MDEV-18921 Server crashes in bitmap_bits_set or bitmap_is_set upon
# UPDATE IGNORE .. FOR PORTION with binary logging
create or replace table t1 (f int, s date, e date, period for app(s,e));
insert into t1 values (1,'2016-09-21','2019-06-14');
update ignore t1 for portion of app from '2019-03-13' to '2019-03-14' set f = 1;
drop table t,t1,t2,log_tbl;
drop view v1;
drop function f;
drop function g;
drop function h;
drop procedure log;
#
# MDEV-21471 ER_CRASHED_ON_USAGE upon UPDATE FOR PORTION on Aria table
#
create table t1 (s date, e date, period for app(s,e), f varchar(8)) engine=aria row_format=fixed;
insert into t1 values ('2024-05-13','2026-03-25','foo');
update t1 for portion of app from '2024-04-02' to '2026-03-15' set f = 'bar';
drop table t1;
# MDEV-19130 Assertion
# `next_insert_id >= auto_inc_interval_for_cur_row.minimum()'
# failed in handler::update_auto_increment after error 167
create table t1 (id int auto_increment, f int, s datetime, e datetime, period for p(s,e), primary key(id));
insert into t1 (s,e) values ('1994-01-06','2004-11-30'),('1994-06-21','1997-06-20');
update ignore t1 set id = 2429681664;
Warnings:
Warning 1264 Out of range value for column 'id' at row 1
Warning 1264 Out of range value for column 'id' at row 2
update ignore t1 for portion of p from '1995-07-06' to '2009-01-12' set f = 1;
drop table t1;
|
