| 1 |
--source suite/versioning/sys_time.inc |
| 2 |
|
| 3 |
# |
| 4 |
# simple tests that don't need to be run in multiple various combinations |
| 5 |
# |
| 6 |
set time_zone='+00:00'; |
| 7 |
|
| 8 |
|
| 9 |
--echo # |
| 10 |
--echo # MDEV-29750 triggers can modify history |
| 11 |
--echo # |
| 12 |
set sql_mode='', timestamp=unix_timestamp('2010-10-10 10:10:10'); |
| 13 |
create table t (a int, b int as (a+1), s timestamp(6) as row start, e timestamp(6) as row end, period for system_time(s,e)) with system versioning; |
| 14 |
insert into t values (1,1, '2022-01-01','2023-01-01'),(2,2, '2022-02-02','2023-02-02'); |
| 15 |
create trigger tr before insert on t for each row set new.b=1, new.s = '2022-03-03', new.e = '2023-03-03'; |
| 16 |
insert into t (a) values (3),(4); |
| 17 |
--replace_result $sys_time_max_replace SYS_TIME_MAX |
| 18 |
select * from t for system_time all; |
| 19 |
drop table t; |
| 20 |
set sql_mode=default, timestamp=default; |
| 21 |
|
| 22 |
--echo # |
| 23 |
--echo # End of 10.3 tests |
| 24 |
--echo # |
| 25 |
# |
| 26 |
# simple tests that don't need to be run in multiple various combinations |
| 27 |
# |
| 28 |
set time_zone='+00:00'; |
| 29 |
|
| 30 |
--echo # |
| 31 |
--echo # MDEV-29721 Inconsistency upon inserting history with visible system versioning columns |
| 32 |
--echo # |
| 33 |
create table t1 (a int, s timestamp(6) as row start, e timestamp(6) as row end, period for system_time(s,e)) with system versioning; |
| 34 |
set system_versioning_insert_history=on; |
| 35 |
set timestamp=unix_timestamp('2010-10-10 10:10:10'); |
| 36 |
insert t1 (a,s,e) values (1,'2020-01-01',default), (2,'2020-02-02',ignore),(3,default,'2020-03-03'), (4,ignore,'2020-04-04'); |
| 37 |
set timestamp=unix_timestamp('2010-11-11 11:11:11'); |
| 38 |
insert t1 values (5,'2020-01-01',default), (6,'2020-02-02',ignore),(7,default,'2020-03-03'), (8,ignore,'2020-04-04'); |
| 39 |
set timestamp=default; |
| 40 |
--replace_result $sys_time_max_replace SYS_TIME_MAX |
| 41 |
select * from t1 for system_time all; |
| 42 |
drop table t1; |
| 43 |
|
| 44 |
--echo # |
| 45 |
--echo # MDEV-29830 Assertion `table->versioned()' in THD::vers_insert_history_fast |
| 46 |
--echo # |
| 47 |
create table t1 (a int) with system versioning; |
| 48 |
insert into t1 values (1),(2); |
| 49 |
create table t2 (a timestamp); |
| 50 |
insert into t2 (a) values (now()),(now()); |
| 51 |
select * from t2 where a in (select row_start from t1); |
| 52 |
drop table t1, t2; |
| 53 |
|
| 54 |
--echo # MDEV-30046 wrong row targeted with "insert ... on duplicate" and "replace" |
| 55 |
--echo # Don't allow changes from the past |
| 56 |
create or replace table t1 (pk int primary key, i int) with system versioning; |
| 57 |
show create table t1; |
| 58 |
set timestamp=12345; |
| 59 |
insert t1(pk, i) values(1,3); |
| 60 |
set timestamp=12344; |
| 61 |
delete from t1; |
| 62 |
|
| 63 |
select pk,i from t1 for system_time all; |
| 64 |
|
| 65 |
set timestamp=12345; |
| 66 |
insert t1(pk, i) values(1,3); |
| 67 |
set timestamp=12344; |
| 68 |
replace t1(pk, i) values(1,4); |
| 69 |
|
| 70 |
select pk,i from t1 for system_time all; |
| 71 |
drop table t1; |
| 72 |
|
| 73 |
--echo # |
| 74 |
--echo # End of 10.11 tests |
| 75 |
--echo # |