| 1 |
set time_zone='+00:00'; |
| 2 |
# |
| 3 |
# MDEV-29750 triggers can modify history |
| 4 |
# |
| 5 |
set sql_mode='', timestamp=unix_timestamp('2010-10-10 10:10:10'); |
| 6 |
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; |
| 7 |
insert into t values (1,1, '2022-01-01','2023-01-01'),(2,2, '2022-02-02','2023-02-02'); |
| 8 |
Warnings: |
| 9 |
Warning 1906 The value specified for generated column 'b' in table 't' has been ignored |
| 10 |
Warning 1906 The value specified for generated column 's' in table 't' has been ignored |
| 11 |
Warning 1906 The value specified for generated column 'e' in table 't' has been ignored |
| 12 |
Warning 1906 The value specified for generated column 'b' in table 't' has been ignored |
| 13 |
Warning 1906 The value specified for generated column 's' in table 't' has been ignored |
| 14 |
Warning 1906 The value specified for generated column 'e' in table 't' has been ignored |
| 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 |
select * from t for system_time all; |
| 18 |
a b s e |
| 19 |
1 2 2010-10-10 10:10:10.000000 SYS_TIME_MAX |
| 20 |
2 3 2010-10-10 10:10:10.000000 SYS_TIME_MAX |
| 21 |
3 4 2010-10-10 10:10:10.000000 SYS_TIME_MAX |
| 22 |
4 5 2010-10-10 10:10:10.000000 SYS_TIME_MAX |
| 23 |
drop table t; |
| 24 |
set sql_mode=default, timestamp=default; |
| 25 |
# |
| 26 |
# End of 10.3 tests |
| 27 |
# |
| 28 |
set time_zone='+00:00'; |
| 29 |
# |
| 30 |
# MDEV-29721 Inconsistency upon inserting history with visible system versioning columns |
| 31 |
# |
| 32 |
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; |
| 33 |
set system_versioning_insert_history=on; |
| 34 |
set timestamp=unix_timestamp('2010-10-10 10:10:10'); |
| 35 |
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'); |
| 36 |
set timestamp=unix_timestamp('2010-11-11 11:11:11'); |
| 37 |
insert t1 values (5,'2020-01-01',default), (6,'2020-02-02',ignore),(7,default,'2020-03-03'), (8,ignore,'2020-04-04'); |
| 38 |
set timestamp=default; |
| 39 |
select * from t1 for system_time all; |
| 40 |
a s e |
| 41 |
1 2020-01-01 00:00:00.000000 SYS_TIME_MAX |
| 42 |
2 2020-02-02 00:00:00.000000 SYS_TIME_MAX |
| 43 |
3 2010-10-10 10:10:10.000000 2020-03-03 00:00:00.000000 |
| 44 |
4 2010-10-10 10:10:10.000000 2020-04-04 00:00:00.000000 |
| 45 |
5 2020-01-01 00:00:00.000000 SYS_TIME_MAX |
| 46 |
6 2020-02-02 00:00:00.000000 SYS_TIME_MAX |
| 47 |
7 2010-11-11 11:11:11.000000 2020-03-03 00:00:00.000000 |
| 48 |
8 2010-11-11 11:11:11.000000 2020-04-04 00:00:00.000000 |
| 49 |
drop table t1; |
| 50 |
# |
| 51 |
# MDEV-29830 Assertion `table->versioned()' in THD::vers_insert_history_fast |
| 52 |
# |
| 53 |
create table t1 (a int) with system versioning; |
| 54 |
insert into t1 values (1),(2); |
| 55 |
create table t2 (a timestamp); |
| 56 |
insert into t2 (a) values (now()),(now()); |
| 57 |
select * from t2 where a in (select row_start from t1); |
| 58 |
a |
| 59 |
drop table t1, t2; |
| 60 |
# MDEV-30046 wrong row targeted with "insert ... on duplicate" and "replace" |
| 61 |
# Don't allow changes from the past |
| 62 |
create or replace table t1 (pk int primary key, i int) with system versioning; |
| 63 |
show create table t1; |
| 64 |
Table Create Table |
| 65 |
t1 CREATE TABLE `t1` ( |
| 66 |
`pk` int(11) NOT NULL, |
| 67 |
`i` int(11) DEFAULT NULL, |
| 68 |
PRIMARY KEY (`pk`) |
| 69 |
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING |
| 70 |
set timestamp=12345; |
| 71 |
insert t1(pk, i) values(1,3); |
| 72 |
set timestamp=12344; |
| 73 |
delete from t1; |
| 74 |
select pk,i from t1 for system_time all; |
| 75 |
pk i |
| 76 |
set timestamp=12345; |
| 77 |
insert t1(pk, i) values(1,3); |
| 78 |
set timestamp=12344; |
| 79 |
replace t1(pk, i) values(1,4); |
| 80 |
select pk,i from t1 for system_time all; |
| 81 |
pk i |
| 82 |
1 4 |
| 83 |
drop table t1; |
| 84 |
# |
| 85 |
# End of 10.11 tests |
| 86 |
# |