1source suite/period/engines.inc; 2source include/have_log_bin.inc; 3 4create table t (id int, s date, e date, period for apptime(s,e)); 5 6insert into t values(1, '1999-01-01', '2018-12-12'); 7insert into t values(1, '1999-01-01', '2017-01-01'); 8insert into t values(1, '2017-01-01', '2019-01-01'); 9insert into t values(2, '1998-01-01', '2018-12-12'); 10insert into t values(3, '1997-01-01', '2015-01-01'); 11insert into t values(4, '2016-01-01', '2020-01-01'); 12insert into t values(5, '2010-01-01', '2015-01-01'); 13 14create or replace table t1 (id int, s date, e date, period for apptime(s,e)); 15insert t1 select * from t; 16create or replace table t2 (id int, s date, e date, period for apptime(s,e)); 17insert t2 select * from t; 18create or replace table t3 (id int, s date, e date, period for apptime(s,e)); 19insert t3 select * from t; 20 21--let $trig_cols=id, s, e 22--let $trig_table=t1 23--source suite/period/create_triggers.inc 24 25delete from t for portion of apptime from '2000-01-01' to '2018-01-01'; 26delete from t1 for portion of APPTIME from '2000-01-01' to '2018-01-01'; 27--sorted_result 28select * from t; 29--sorted_result 30select * from t1; 31select * from log_tbl order by id; 32 33--echo # INSERT trigger only also works 34--let $trig_cols=id, s, e 35--let $trig_table=t2 36--source suite/period/create_triggers.inc 37drop trigger tr1del_t2; 38drop trigger tr2del_t2; 39delete from t2 for portion of APPTIME from '2000-01-01' to '2018-01-01'; 40select * from log_tbl order by id; 41 42--echo # removing BEFORE INSERT trigger enables internal substitution 43--echo # DELETE+INSERT -> UPDATE, but without any side effects. 44--echo # The optimization is disabled for non-transactional engines 45--let $trig_table=t3 46--source suite/period/create_triggers.inc 47drop trigger tr1ins_t3; 48delete from t3 for portion of APPTIME from '2000-01-01' to '2018-01-01'; 49select * from log_tbl order by id; 50 51--echo # multi-table DELETE is not possible 52--error ER_PARSE_ERROR 53delete t, t1 from t1, t for portion of apptime from '2000-01-01' to '2018-01-01'; 54 55--error ER_PARSE_ERROR 56delete t for portion of apptime from '2000-01-01' to '2018-01-01', t1 from t, t1; 57 58--echo # Here another check fails before parsing ends 59--error ER_UNKNOWN_TABLE 60delete t, t1 from t for portion of apptime from '2000-01-01' to '2018-01-01', t1; 61 62--error ER_PARSE_ERROR 63delete history from t2 for portion of apptime from '2000-01-01' to '2018-01-01'; 64 65--error ER_PERIOD_NOT_FOUND 66delete from t for portion of othertime from '2000-01-01' to '2018-01-01'; 67--error ER_PARSE_ERROR 68delete from t for portion of system_time from '2000-01-01' to '2018-01-01'; 69 70create or replace table t (id int, str text, s date, e date, 71 period for apptime(s,e)); 72 73insert into t values(1, 'data', '1999-01-01', '2018-12-12'); 74insert into t values(1, 'other data', '1999-01-01', '2018-12-12'); 75insert into t values(1, 'deleted', '2000-01-01', '2018-01-01'); 76delete from t for portion of apptime from '2000-01-01' to '2018-01-01'; 77show warnings; 78--sorted_result 79select * from t; 80 81drop table t1; 82 83--echo # SQL16, Part 2, 15.7 <Effect of deleting rows from base tables>, 84--echo # General rules, 8)b)i) 85--echo # If the column descriptor that corresponds to the i-th field of BR 86--echo # describes an identity column, a generated column, a system-time period 87--echo # start column, or a system-time period end column, then let V i be 88--echo # DEFAULT. 89 90--echo # auto_increment field is updated 91create or replace table t (id int primary key auto_increment, s date, e date, 92 period for apptime(s, e)); 93insert into t values (default, '1999-01-01', '2018-12-12'); 94select * from t; 95delete from t for portion of apptime from '2000-01-01' to '2018-01-01'; 96--sorted_result 97select * from t; 98truncate t; 99--echo # same for trigger case 100insert into t values (default, '1999-01-01', '2018-12-12'); 101--let $trig_table=t 102--source suite/period/create_triggers.inc 103 104delete from t for portion of apptime from '2000-01-01' to '2018-01-01'; 105--sorted_result 106select * from t; 107select * from log_tbl order by id; 108 109--echo # generated columns are updated 110create or replace table t (s date, e date, 111 xs date as (s) stored, xe date as (e) stored, 112 period for apptime(s, e)); 113insert into t values('1999-01-01', '2018-12-12', default, default); 114--sorted_result 115select * from t; 116delete from t for portion of apptime from '2000-01-01' to '2018-01-01'; 117--sorted_result 118select * from t; 119truncate t; 120--echo # same for trigger case 121insert into t values('1999-01-01', '2018-12-12', default, default); 122--let $trig_table=t 123--source suite/period/create_triggers.inc 124 125delete from t for portion of apptime from '2000-01-01' to '2018-01-01'; 126--sorted_result 127select * from t; 128select * from log_tbl order by id; 129 130--echo # View can't be used 131create or replace view v as select * from t; 132--error ER_IT_IS_A_VIEW 133delete from v for portion of p from '2000-01-01' to '2018-01-01'; 134 135--echo # View can't be used 136create or replace view v as select t.* from t, t as t1; 137--error ER_VIEW_DELETE_MERGE_VIEW 138delete from v for portion of p from '2000-01-01' to '2018-01-01'; 139 140--echo # auto_increment field overflow 141create or replace table t (id tinyint auto_increment primary key, 142 s date, e date, period for apptime(s,e)); 143 144insert into t values(127, '1999-01-01', '2018-12-12'); 145 146--error HA_ERR_AUTOINC_ERANGE 147delete from t for portion of apptime from '2000-01-01' to '2018-01-01'; 148select * from t; 149 150--echo # same for trigger case 151--let $trig_table=t 152--source suite/period/create_triggers.inc 153--echo # negotiate side effects of non-transactional MyISAM engine 154replace into t values(127, '1999-01-01', '2018-12-12'); 155select * from t; 156truncate table log_tbl; 157 158--error HA_ERR_AUTOINC_ERANGE 159delete from t for portion of apptime from '2000-01-01' to '2018-01-01'; 160select * from t; 161select * from log_tbl order by id; 162 163--echo # custom constraint for period fields 164create or replace table t(id int, s date, e date, period for apptime(s,e), 165 constraint dist2days check (datediff(e, s) >= 2)); 166insert into t values(1, '1999-01-01', '2018-12-12'), 167 (2, '1999-01-01', '1999-12-12'); 168--error ER_CONSTRAINT_FAILED 169delete from t for portion of apptime from '1999-01-02' to '2018-12-12'; 170--echo # negotiate side effects of non-transactional MyISAM engine 171truncate t; 172insert into t values(1, '1999-01-01', '2018-12-12'), 173 (2, '1999-01-01', '1999-12-12'); 174--error ER_CONSTRAINT_FAILED 175delete from t for portion of apptime from '1999-01-01' to '2018-12-11'; 176truncate t; 177insert into t values(1, '1999-01-01', '2018-12-12'), 178 (2, '1999-01-01', '1999-12-12'); 179 180delete from t for portion of apptime from '1999-01-03' to '2018-12-10'; 181--sorted_result 182select *, datediff(e, s) from t; 183 184--echo # 185--echo # MDEV-18929 2nd execution of SP does not detect ER_VERS_NOT_VERSIONED 186--echo # 187create or replace table t1 (id int, s date, e date, period for apptime(s,e)); 188create or replace procedure sp() 189delete from t1 for portion of othertime from '2000-01-01' to '2018-01-01'; 190--error ER_PERIOD_NOT_FOUND 191call sp; 192--error ER_PERIOD_NOT_FOUND 193call sp; 194drop table t1; 195drop procedure sp; 196 197drop table t,t2,t3,log_tbl; 198drop view v; 199drop procedure log; 200 201--echo # MDEV-19130 Assertion 202--echo # `next_insert_id >= auto_inc_interval_for_cur_row.minimum()' 203--echo # failed in handler::update_auto_increment after error 167 204 205create or replace table t (f tinyint auto_increment null, 206 s timestamp, e timestamp, 207 period for app(s,e), key(f, s)); 208insert into t (s,e) values 209 ('2021-08-22 10:28:43', '2023-09-17 00:00:00'), 210 ('2019-05-09 21:45:24', '2020-04-22 14:38:49'); 211insert into t (s,e) select s,e from t; 212insert into t (s,e) select s,e from t; 213insert into t (s,e) select s,e from t; 214insert into t (s,e) values ('2015-07-07 00:00:00','2020-03-11 08:48:52'); 215insert into t (s,e) select s,e from t; 216insert into t (s,e) select s,e from t; 217 218--replace_regex /row \d+/row ROW/ 219--error HA_ERR_AUTOINC_ERANGE 220insert into t select * from t; 221 222--disable_warnings 223delete ignore from t 224 for portion of app from '2015-07-07 00:00:00' to '2020-03-11 08:48:52'; 225--enable_warnings 226 227drop table t; 228