1--source suite/versioning/engines.inc 2--source suite/versioning/common.inc 3--source include/have_partition.inc 4 5create or replace table t1 (x int) with system versioning; 6insert into t1 values (1); 7 8select now(6) into @t1; 9update t1 set x= 2; 10 11select now(6) into @t2; 12delete from t1; 13 14set @vt1= concat("create or replace view vt1 as select * from t1 for system_time as of timestamp '", @t1, "'"); 15prepare stmt from @vt1; execute stmt; drop prepare stmt; 16 17set @vt2= concat("create or replace view vt2 as select *, row_end from t1 for system_time as of timestamp '", @t2, "'"); 18prepare stmt from @vt2; execute stmt; drop prepare stmt; 19 20select * from t1; 21 22create or replace view vt1 as select * from t1; 23--replace_result 18446744073709551615 MAX_RESULT "TIMESTAMP'2038-01-19 03:14:07.999999'" MAX_RESULT 24show create view vt1; 25 26drop view vt1; 27drop view vt2; 28 29create or replace view vt1 as select * from t1 for system_time all; 30select * from vt1; 31prepare stmt from 'select * from vt1'; execute stmt; drop prepare stmt; 32 33set @str= concat('create or replace view vt1 as 34select * from t1 for system_time as of timestamp "', @t1, '"'); 35prepare stmt from @str; execute stmt; drop prepare stmt; 36select * from t1 for system_time as of timestamp @t1; 37select * from vt1; 38 39insert into vt1 values (3); 40select * from t1; 41select * from vt1; 42 43create or replace table t1 (x int) with system versioning; 44insert into t1 values (1), (2); 45set @t1=now(6); 46delete from t1 where x=2; 47set @t2=now(6); 48delete from t1 where x=1; 49set @t3=now(6); 50 51set @tmp= concat("create or replace view vt1 as select * from t1 for system_time as of timestamp '", @t1, "'"); 52prepare stmt from @tmp; execute stmt; drop prepare stmt; 53 54select * from vt1; 55 56--echo # VIEW with parameters [tempesta-tech/mariadb#151] 57create or replace table t1 (x int) with system versioning; 58create or replace view vt1(c) as select x from t1; 59--replace_result 18446744073709551615 MAX_RESULT "TIMESTAMP'2038-01-19 03:14:07.999999'" MAX_RESULT 60show create view vt1; 61 62--echo # VIEW over JOIN of versioned tables [tempesta-tech/mariadb#153] 63create or replace table t1 (a int) with system versioning; 64create or replace table t2 (b int) with system versioning; 65insert into t1 values (1); 66insert into t2 values (2); 67create or replace view vt12 as select * from t1 cross join t2; 68select * from vt12; 69create or replace view vt12 as select * from t1 for system_time as of timestamp ('1970-01-01 00:00') cross join t2; 70select * from vt12; 71 72--echo # VIEW improvements [tempesta-tech/mariadb#183] 73create or replace table t3 (x int); 74create or replace view vt1 as select * from t1, t2, t3; 75--replace_result 18446744073709551615 MAX_RESULT "TIMESTAMP'2038-01-19 03:14:07.999999'" MAX_RESULT 76show create view vt1; 77create or replace view vt1 as select * from t3, t2, t1; 78--replace_result 18446744073709551615 MAX_RESULT "TIMESTAMP'2038-01-19 03:14:07.999999'" MAX_RESULT 79show create view vt1; 80create or replace view vt1 as select a, t2.row_end as endo from t3, t1, t2; 81--replace_result 18446744073709551615 MAX_RESULT "TIMESTAMP'2038-01-19 03:14:07.999999'" MAX_RESULT 82show create view vt1; 83 84--echo # VIEW over UNION [tempesta-tech/mariadb#269] 85create or replace view vt1 as select * from t1 union select * from t1; 86select * from vt1; 87 88--echo # VIEW over UNION with non-versioned [tempesta-tech/mariadb#393] 89create or replace table t2 (a int); 90create or replace view vt1 as select * from t1 union select * from t2; 91select * from vt1; 92 93--echo # 94--echo # MDEV-14689 crash on second PS execute 95--echo # 96create or replace table t1 (a int); 97create or replace view v1 as select * from t1; 98create or replace table t2 (b int) with system versioning; 99prepare stmt from 'select a from v1 inner join t2 group by a order by a'; 100execute stmt; 101execute stmt; 102drop view v1; 103drop tables t1, t2; 104 105--echo # 106--echo # MDEV-15146 SQLError[4122]: View is not system versioned 107--echo # 108create or replace table t1 (a int) with system versioning; 109insert t1 values (1),(2); 110set @a=now(6); 111create or replace view v1 as select * from t1; 112delete from t1; 113select * from v1; 114select * from v1 for system_time as of @a; 115show create view v1; 116 117--echo # 118--echo # MDEV-15378 Valid query causes invalid view definition due to syntax limitation in FOR SYSTEM_TIME 119--echo # 120create or replace table t1 (i int) with system versioning; 121select * from t1 for system_time as of now() - interval 6 second; 122create or replace view v1 as select * from t1 for system_time as of date_sub(now(), interval 6 second); 123show create view v1; 124 125drop view v1, vt1, vt12; 126drop tables t1, t3; 127 128--echo # 129--echo # MDEV-18727 improve DML operation of System Versioning 130--echo # 131--replace_result $sys_datatype_expl SYS_DATATYPE 132eval create or replace table t1 ( 133 x int, 134 row_start $sys_datatype_expl as row start invisible, 135 row_end $sys_datatype_expl as row end invisible, 136 period for system_time (row_start, row_end) 137) with system versioning; 138insert into t1 values (1), (2); 139create or replace view v1 as select * from t1 where x > 1; 140--echo # update, delete 141update v1 set x= x + 1; 142select *, check_row(row_start, row_end) from t1 for system_time all order by x; 143insert v1 values (4); 144select *, check_row(row_start, row_end) from t1 for system_time all order by x; 145delete from v1 where x < 4; 146select *, check_row(row_start, row_end) from t1 for system_time all order by x; 147--echo # multi-update 148create or replace table t2 like t1; 149insert into t2 values (1), (2); 150create or replace view v2 as select * from t2 where x > 1; 151update v1, v2 set v1.x= v1.x + 1, v2.x= v2.x + 1 where v1.x = v2.x + 2; 152select *, check_row(row_start, row_end) from t1 for system_time all order by x; 153select *, check_row(row_start, row_end) from t2 for system_time all order by x; 154--echo # multi-delete 155delete v1, v2 from v1 join v2 where v1.x = v2.x + 2; 156select *, check_row(row_start, row_end) from t1 for system_time all order by x; 157select *, check_row(row_start, row_end) from t2 for system_time all order by x; 158--echo # replace 159--replace_result $sys_datatype_expl SYS_DATATYPE 160eval create or replace table t1 ( 161 x int primary key, y int, 162 row_start $sys_datatype_expl as row start invisible, 163 row_end $sys_datatype_expl as row end invisible, 164 period for system_time (row_start, row_end) 165) with system versioning; 166insert into t1 values (1, 0), (2, 0); 167create or replace view v1 as select * from t1 where x > 1; 168replace v1 values (1, 1); 169replace v1 values (2, 1); 170replace v1 values (3, 1); 171--echo # REPLACE ignores VIEW condition because itself doesn't use WHERE 172select *, check_row(row_start, row_end) from t1 for system_time all order by x, row_end; 173--echo # insert-select, on duplicate key 174insert v1 select * from t1 where x = 1 on duplicate key update x = v1.x - 1; 175select *, check_row(row_start, row_end) from t1 for system_time all order by x, row_end; 176drop view v1, v2; 177drop tables t1, t2; 178 179--echo # 180--echo # MDEV-21146 Assertion `m_lock_type == 2' in handler::ha_drop_table upon LOAD DATA 181--echo # 182create table t1 (a int); 183create view v1 as select * from t1; 184create or replace table t1 (b int) with system versioning; 185--error ER_VIEW_INVALID 186load data infile 'xx' into table v1; 187 188# cleanup 189drop view v1; 190drop table t1; 191 192--echo # 193--echo # MDEV-21155 Assertion with versioned table upon DELETE from view of view after replacing first view 194--echo # 195create table t1 (a int); 196insert into t1 values (1); 197--replace_result $sys_datatype_expl SYS_DATATYPE 198eval create table t2 ( 199 b int, 200 row_start $sys_datatype_expl as row start invisible, 201 row_end $sys_datatype_expl as row end invisible, 202 period for system_time (row_start, row_end) 203) with system versioning; 204insert into t2 values (2); 205create view v1 as select * from t1; 206create view v2 as select * from v1; 207create or replace view v1 as select * from t2; 208delete from v2; 209select * from t1; 210select *, check_row(row_start, row_end) from t2 for system_time all; 211 212# cleanup 213drop view v2; 214drop view v1; 215drop table t1, t2; 216 217--echo # 218--echo # MDEV-23779 Error upon querying the view, that selecting from versioned table with partitions 219--echo # 220create table t1 (i int) with system versioning 221partition by system_time (partition p0 history, partition pn current); 222create view v1 as select * from t1 partition (pn); 223show create view v1; 224 225# cleanup 226drop view v1; 227drop table t1; 228 229--source suite/versioning/common_finish.inc 230