Debian Codegraph @

mysql-test/suite/versioning/t/misc.test

raw
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 #