Debian Codegraph @

mysql-test/suite/period/t/update.test

raw
1 source suite/period/engines.inc;
2 source include/have_log_bin.inc;
3
4 create table t (id int, s date, e date, period for apptime(s,e));
5
6 insert into t values(1, '1999-01-01', '2018-12-12');
7 insert into t values(1, '1999-01-01', '2017-01-01');
8 insert into t values(1, '2017-01-01', '2019-01-01');
9 insert into t values(2, '1998-01-01', '2018-12-12');
10 insert into t values(3, '1997-01-01', '2015-01-01');
11 insert into t values(4, '2016-01-01', '2020-01-01');
12 insert into t values(5, '2010-01-01', '2015-01-01');
13
14 create or replace table t1 (id int, s date, e date, period for apptime(s,e));
15 insert t1 select * from t;
16 create or replace table t2 (id int, s date, e date, period for apptime(s,e));
17 insert t2 select * from t;
18
19 update t for portion of apptime from '2000-01-01' to '2018-01-01'
20 set id=id + 6;
21 --sorted_result
22 select * from t;
23
24 --echo # Check triggers
25 --let $trig_cols=id, s, e
26 --let $trig_table=t1
27 --source suite/period/create_triggers.inc
28
29 update t1 for portion of apptime from '2000-01-01' to '2018-01-01'
30 set id=id + 6;
31 --sorted_result
32 select * from t1;
33 select * from log_tbl order by id;
34
35 --echo # INSERT trigger only also works
36 --let $trig_cols=id, s, e
37 --let $trig_table=t2
38 --source suite/period/create_triggers.inc
39 drop trigger tr1upd_t2;
40 drop trigger tr2upd_t2;
41 update t2 for portion of apptime from '2000-01-01' to '2018-01-01'
42 set id=id + 6;
43 --sorted_result
44 select * from t2;
45 select * from log_tbl order by id;
46
47 --error ER_PARSE_ERROR
48 select * from t for portion of apptime from 0 to 1 for system_time all;
49 --error ER_PARSE_ERROR
50 update t for portion of apptime from 0 to 1 for system_time all set id=1;
51
52 --echo # Modifying period start/end fields is forbidden.
53 --echo # SQL16: 14.14 <update statement: searched>, Syntax Rules, 7)a)ii)
54 --echo # Neither BSTARTCOL nor BENDCOL shall be an explicit <object column>
55 --echo # contained in the <set clause list>.
56 --error ER_PERIOD_COLUMNS_UPDATED
57 update t for portion of apptime from '2000-01-01' to '2018-01-01'
58 set id= id + 6, s=subdate(s, 5), e=adddate(e, 5);
59
60 --echo # Precision timestamps
61 create or replace table t (id int, s timestamp(5), e timestamp(5),
62 period for apptime(s,e));
63 insert into t values(1, '1999-01-01', '2018-12-12');
64 insert into t values(1, '1999-01-01', '2017-01-01');
65 update t for portion of apptime from '2000-01-01 00:00:00.00015'
66 to '2018-01-01 12:34:56.31415'
67 set id= id + 5;
68 --sorted_result
69 select * from t;
70
71 -- echo # Strings
72 create or replace table t (id int, str text, s date, e date,
73 period for apptime(s,e));
74
75 insert into t values(1, 'data', '1999-01-01', '2018-12-12');
76 insert into t values(1, 'other data', '1999-01-01', '2018-12-12');
77 update t for portion of apptime from '2000-01-01' to '2018-01-01'
78 set id= id + 5;
79 --sorted_result
80 select * from t;
81
82 --echo # multi-table UPDATE is impossible
83 create or replace table t1(x int);
84 --error ER_PARSE_ERROR
85 update t for portion of apptime from '2000-01-01' to '2018-01-01', t1
86 set t.id= t.id + 5;
87
88 --error ER_PARSE_ERROR
89 update t1 set x= (select id from t for portion of apptime from '2000-01-01' to '2018-01-01');
90
91 --echo # single-table views
92 create or replace view v1 as select * from t where id<10;
93 --error ER_IT_IS_A_VIEW
94 update v1 for portion of apptime from '2000-01-01' to '2018-01-01' set id= id + 5;
95
96 --echo # multi-table views
97 create or replace view v1 as select * from t, t1 where x=id;
98 --error ER_IT_IS_A_VIEW
99 update v1 for portion of apptime from '2000-01-01' to '2018-01-01' set id= id + 5;
100
101 --echo # SQL16: 14.14 <update statement: searched>, Syntax Rules, 7)a) iii-iv)
102 --echo # Let FROMVAL be <point in time 1>. FROMVAL shall not generally contain a
103 --echo # reference to a column of T or a <routine invocation>
104 --echo # whose subject routine is an SQL-invoked routine that
105 --echo # is possibly non-deterministic or that possibly modifies SQL-data.
106 --echo # ...Same for <point in time 2> (TOVAL)
107 --error ER_NOT_CONSTANT_EXPRESSION
108 update t for portion of apptime from 5*(5+s) to 1 set t.id= t.id + 5;
109 --error ER_NOT_CONSTANT_EXPRESSION
110 update t for portion of apptime from 1 to e set t.id= t.id + 5;
111
112 set @s= '2000-01-01';
113 set @e= '2018-01-01';
114
115 create or replace function f() returns date return @e;
116 create or replace function g() returns date not deterministic return @e;
117 create or replace function h() returns date deterministic return @e;
118
119 --error ER_NOT_CONSTANT_EXPRESSION
120 update t for portion of apptime from @s to f() set t.id= t.id + 5;
121 --error ER_NOT_CONSTANT_EXPRESSION
122 update t for portion of apptime from @s to g() set t.id= t.id + 5;
123
124 --echo # success
125 update t for portion of apptime from @s to h() set t.id= t.id + 5;
126 --error ER_NOT_CONSTANT_EXPRESSION
127 update t for portion of apptime from (select s from t2 limit 1) to h() set t.id= t.id + 5;
128
129 --echo # auto_inrement field is updated
130 create or replace table t (id int primary key auto_increment, x int,
131 s date, e date, period for apptime(s, e));
132 insert into t values (default, 1, '1999-01-01', '2018-12-12');
133 update t for portion of apptime from '2000-01-01' to '2018-01-01' set x= x + 5;
134 --sorted_result
135 select * from t;
136
137 truncate t;
138 insert into t values (default, 1, '1999-01-01', '2018-12-12');
139 update t for portion of apptime from '2000-01-01' to '2018-01-01' set x= 1;
140 --sorted_result
141 select * from t;
142
143 --echo # generated columns are updated
144 create or replace table t (x int, s date, e date,
145 xs date as (s) stored, xe date as (e) stored,
146 period for apptime(s, e));
147 insert into t values(1, '1999-01-01', '2018-12-12', default, default);
148 --sorted_result
149 select * from t;
150 update t for portion of apptime from '2000-01-01' to '2018-01-01' set x= x + 5;
151 --sorted_result
152 select *, xs=s and xe=e from t;
153
154 --echo # MDEV-18921 Server crashes in bitmap_bits_set or bitmap_is_set upon
155 --echo # UPDATE IGNORE .. FOR PORTION with binary logging
156 create or replace table t1 (f int, s date, e date, period for app(s,e));
157 insert into t1 values (1,'2016-09-21','2019-06-14');
158 update ignore t1 for portion of app from '2019-03-13' to '2019-03-14' set f = 1;
159
160 drop table t,t1,t2,log_tbl;
161 drop view v1;
162 drop function f;
163 drop function g;
164 drop function h;
165 drop procedure log;
166
167 --echo #
168 --echo # MDEV-21471 ER_CRASHED_ON_USAGE upon UPDATE FOR PORTION on Aria table
169 --echo #
170 create table t1 (s date, e date, period for app(s,e), f varchar(8)) engine=aria row_format=fixed;
171 insert into t1 values ('2024-05-13','2026-03-25','foo');
172 update t1 for portion of app from '2024-04-02' to '2026-03-15' set f = 'bar';
173
174 # cleanup
175 drop table t1;
176
177 --echo # MDEV-19130 Assertion
178 --echo # `next_insert_id >= auto_inc_interval_for_cur_row.minimum()'
179 --echo # failed in handler::update_auto_increment after error 167
180 create table t1 (id int auto_increment, f int, s datetime, e datetime, period for p(s,e), primary key(id));
181 insert into t1 (s,e) values ('1994-01-06','2004-11-30'),('1994-06-21','1997-06-20');
182 update ignore t1 set id = 2429681664;
183 update ignore t1 for portion of p from '1995-07-06' to '2009-01-12' set f = 1;
184
185 drop table t1;
186
187 --echo #
188 --echo # MDEV-22805 SIGSEGV in check_fields on UPDATE (optimized builds) | Assertion `thd->lex->sql_command == SQLCOM_UPDATE' failed.
189 --echo #
190 CREATE TABLE t1 (a INT, b DATE, c DATE, PERIOD FOR APPTIME(b, c));
191
192 INSERT INTO t1 VALUES(1, '1999-01-01', '2018-12-12');
193
194 # Without a patch the following statement crashs a server built in debug mode
195 let $stmt= UPDATE t1 FOR PORTION OF APPTIME FROM (SELECT '1999-01-01' FROM t1 WHERE a=2) TO '2018-01-01' SET a = 100;
196 --error ER_NOT_SUPPORTED_YET
197 eval $stmt;
198 eval set @tmp= "$stmt";
199 --error ER_NOT_SUPPORTED_YET
200 execute immediate @tmp;
201
202 CREATE VIEW v1 AS SELECT * FROM t1;
203 --error ER_IT_IS_A_VIEW
204 UPDATE v1 FOR PORTION OF APPTIME FROM (SELECT '1999-01-01' FROM t1 WHERE a=2) TO '2018-01-01' SET a = 100;
205
206 DROP VIEW v1;
207 DROP TABLE t1;