mirror of
https://github.com/MariaDB/server.git
synced 2025-08-29 22:01:36 +02:00
86 lines
3.5 KiB
Text
86 lines
3.5 KiB
Text
set time_zone='+00:00';
|
|
#
|
|
# MDEV-29750 triggers can modify history
|
|
#
|
|
set sql_mode='', timestamp=unix_timestamp('2010-10-10 10:10:10');
|
|
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;
|
|
insert into t values (1,1, '2022-01-01','2023-01-01'),(2,2, '2022-02-02','2023-02-02');
|
|
Warnings:
|
|
Warning 1906 The value specified for generated column 'b' in table 't' has been ignored
|
|
Warning 1906 The value specified for generated column 's' in table 't' has been ignored
|
|
Warning 1906 The value specified for generated column 'e' in table 't' has been ignored
|
|
Warning 1906 The value specified for generated column 'b' in table 't' has been ignored
|
|
Warning 1906 The value specified for generated column 's' in table 't' has been ignored
|
|
Warning 1906 The value specified for generated column 'e' in table 't' has been ignored
|
|
create trigger tr before insert on t for each row set new.b=1, new.s = '2022-03-03', new.e = '2023-03-03';
|
|
insert into t (a) values (3),(4);
|
|
select * from t for system_time all;
|
|
a b s e
|
|
1 2 2010-10-10 10:10:10.000000 2038-01-19 03:14:07.999999
|
|
2 3 2010-10-10 10:10:10.000000 2038-01-19 03:14:07.999999
|
|
3 4 2010-10-10 10:10:10.000000 2038-01-19 03:14:07.999999
|
|
4 5 2010-10-10 10:10:10.000000 2038-01-19 03:14:07.999999
|
|
drop table t;
|
|
set sql_mode=default, timestamp=default;
|
|
#
|
|
# End of 10.3 tests
|
|
#
|
|
set time_zone='+00:00';
|
|
#
|
|
# MDEV-29721 Inconsistency upon inserting history with visible system versioning columns
|
|
#
|
|
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;
|
|
set system_versioning_insert_history=on;
|
|
set timestamp=unix_timestamp('2010-10-10 10:10:10');
|
|
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');
|
|
set timestamp=unix_timestamp('2010-11-11 11:11:11');
|
|
insert t1 values (5,'2020-01-01',default), (6,'2020-02-02',ignore),(7,default,'2020-03-03'), (8,ignore,'2020-04-04');
|
|
set timestamp=default;
|
|
select * from t1 for system_time all;
|
|
a s e
|
|
1 2020-01-01 00:00:00.000000 2038-01-19 03:14:07.999999
|
|
2 2020-02-02 00:00:00.000000 2038-01-19 03:14:07.999999
|
|
3 2010-10-10 10:10:10.000000 2020-03-03 00:00:00.000000
|
|
4 2010-10-10 10:10:10.000000 2020-04-04 00:00:00.000000
|
|
5 2020-01-01 00:00:00.000000 2038-01-19 03:14:07.999999
|
|
6 2020-02-02 00:00:00.000000 2038-01-19 03:14:07.999999
|
|
7 2010-11-11 11:11:11.000000 2020-03-03 00:00:00.000000
|
|
8 2010-11-11 11:11:11.000000 2020-04-04 00:00:00.000000
|
|
drop table t1;
|
|
#
|
|
# MDEV-29830 Assertion `table->versioned()' in THD::vers_insert_history_fast
|
|
#
|
|
create table t1 (a int) with system versioning;
|
|
insert into t1 values (1),(2);
|
|
create table t2 (a timestamp);
|
|
insert into t2 (a) values (now()),(now());
|
|
select * from t2 where a in (select row_start from t1);
|
|
a
|
|
drop table t1, t2;
|
|
# MDEV-30046 wrong row targeted with "insert ... on duplicate" and "replace"
|
|
# Don't allow changes from the past
|
|
create or replace table t1 (pk int primary key, i int) with system versioning;
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`pk` int(11) NOT NULL,
|
|
`i` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`pk`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING
|
|
set timestamp=12345;
|
|
insert t1(pk, i) values(1,3);
|
|
set timestamp=12344;
|
|
delete from t1;
|
|
select pk,i from t1 for system_time all;
|
|
pk i
|
|
set timestamp=12345;
|
|
insert t1(pk, i) values(1,3);
|
|
set timestamp=12344;
|
|
replace t1(pk, i) values(1,4);
|
|
select pk,i from t1 for system_time all;
|
|
pk i
|
|
1 4
|
|
drop table t1;
|
|
#
|
|
# End of 10.11 tests
|
|
#
|