| 1 |
set @@time_zone='+00:00'; |
| 2 |
create table t1( |
| 3 |
x int unsigned, |
| 4 |
sys_start bigint unsigned as row start invisible, |
| 5 |
sys_end bigint unsigned as row end invisible, |
| 6 |
period for system_time (sys_start, sys_end)) |
| 7 |
with system versioning engine=innodb; |
| 8 |
create table t2(x int unsigned) engine=innodb; |
| 9 |
start transaction; |
| 10 |
insert into t1(x) values(1); |
| 11 |
commit; |
| 12 |
start transaction; |
| 13 |
insert into t2(x) values(1); |
| 14 |
savepoint a; |
| 15 |
insert into t1(x) values(1); |
| 16 |
rollback to a; |
| 17 |
commit; |
| 18 |
insert into t2(x) values (1); |
| 19 |
create or replace table t1 ( |
| 20 |
x int, |
| 21 |
y int as (x) virtual, |
| 22 |
sys_trx_start bigint unsigned as row start invisible, |
| 23 |
sys_trx_end bigint unsigned as row end invisible, |
| 24 |
period for system_time (sys_trx_start, sys_trx_end) |
| 25 |
) engine=innodb with system versioning; |
| 26 |
insert into t1 values (1, null); |
| 27 |
update t1 set x= x + 1; |
| 28 |
select x, y, sys_trx_end = 18446744073709551615 as current from t1 for system_time all; |
| 29 |
x y current |
| 30 |
2 2 1 |
| 31 |
1 1 0 |
| 32 |
create or replace table t1 ( |
| 33 |
x int, |
| 34 |
row_start timestamp(6) as row start invisible, |
| 35 |
row_end timestamp(6) as row end invisible, |
| 36 |
period for system_time (row_start, row_end) |
| 37 |
) with system versioning; |
| 38 |
insert into t1 values (1), (2); |
| 39 |
insert into t1 (row_start) select row_end from t1; |
| 40 |
ERROR HY000: The value specified for generated column 'row_start' in table 't1' has been ignored |
| 41 |
set sql_mode=''; |
| 42 |
insert into t1 (row_start, row_end) values (DEFAULT, 1); |
| 43 |
Warnings: |
| 44 |
Warning 1906 The value specified for generated column 'row_end' in table 't1' has been ignored |
| 45 |
set sql_mode=default; |
| 46 |
select @@sql_mode into @saved_mode; |
| 47 |
set sql_mode= ''; |
| 48 |
insert into t1 (x, row_start, row_end) values (3, 4, 5); |
| 49 |
Warnings: |
| 50 |
Warning 1906 The value specified for generated column 'row_start' in table 't1' has been ignored |
| 51 |
Warning 1906 The value specified for generated column 'row_end' in table 't1' has been ignored |
| 52 |
set sql_mode= @saved_mode; |
| 53 |
insert into t1 (row_start, row_end) values (DEFAULT, DEFAULT); |
| 54 |
select * from t1; |
| 55 |
x |
| 56 |
1 |
| 57 |
2 |
| 58 |
NULL |
| 59 |
3 |
| 60 |
NULL |
| 61 |
# MDEV-14792 INSERT without column list into table with explicit versioning columns produces bad data |
| 62 |
create or replace table t1 ( |
| 63 |
i int, |
| 64 |
s timestamp(6) as row start, |
| 65 |
e timestamp(6) as row end, |
| 66 |
c varchar(8), |
| 67 |
period for system_time(s, e)) |
| 68 |
with system versioning; |
| 69 |
insert into t1 values (1, null, null, 'foo'); |
| 70 |
select i, c, e>LEFT(SYS_TIME_MAX, 10) AS current_row from t1;; |
| 71 |
i c current_row |
| 72 |
1 foo 1 |
| 73 |
drop table t1; |
| 74 |
drop table t2; |
| 75 |
# |
| 76 |
# MDEV-14871 Server crashes in fill_record / fill_record_n_invoke_before_triggers upon inserting into versioned table with trigger |
| 77 |
# |
| 78 |
create or replace table t1 (pk int primary key) with system versioning; |
| 79 |
create trigger tr before insert on t1 for each row select 1 into @a; |
| 80 |
insert into t1 values (1),(2); |
| 81 |
drop table t1; |
| 82 |
create table t1 (pk int primary key, i int) with system versioning; |
| 83 |
replace into t1 values (1,10),(1,100),(1,1000); |
| 84 |
select pk,i,row_end > '2038-01-01' from t1 for system_time all; |
| 85 |
pk i row_end > '2038-01-01' |
| 86 |
1 1000 1 |
| 87 |
drop table t1; |
| 88 |
set @@time_zone=default; |