diff options
author | Aleksey Midenkov <midenok@gmail.com> | 2019-11-22 14:29:03 +0300 |
---|---|---|
committer | Aleksey Midenkov <midenok@gmail.com> | 2019-11-22 14:29:03 +0300 |
commit | 0076dce2c89248b6c0252ec4385879194f9aadbf (patch) | |
tree | 1774fb25fcbe854aee956841ab28298029b548f6 /mysql-test/suite/versioning/t | |
parent | a14544260c33dcdb057d2f62c4aab33cb09ebcb1 (diff) | |
download | mariadb-git-0076dce2c89248b6c0252ec4385879194f9aadbf.tar.gz |
MDEV-18727 improve DML operation of System Versioning
MDEV-18957 UPDATE with LIMIT clause is wrong for versioned partitioned tables
UPDATE, DELETE: replace linear search of current/historical records
with vers_setup_conds().
Additional DML cases in view.test
Diffstat (limited to 'mysql-test/suite/versioning/t')
-rw-r--r-- | mysql-test/suite/versioning/t/delete.test | 10 | ||||
-rw-r--r-- | mysql-test/suite/versioning/t/delete_history.test (renamed from mysql-test/suite/versioning/t/truncate.test) | 0 | ||||
-rw-r--r-- | mysql-test/suite/versioning/t/partition.test | 14 | ||||
-rw-r--r-- | mysql-test/suite/versioning/t/view.test | 47 |
4 files changed, 57 insertions, 14 deletions
diff --git a/mysql-test/suite/versioning/t/delete.test b/mysql-test/suite/versioning/t/delete.test index b9045898bb0..4f1ba4b1d8e 100644 --- a/mysql-test/suite/versioning/t/delete.test +++ b/mysql-test/suite/versioning/t/delete.test @@ -1,6 +1,7 @@ source suite/versioning/engines.inc; source suite/versioning/common.inc; +--echo # Basic + delete from view replace_result $sys_datatype_expl SYS_DATATYPE; eval create or replace table t1( XNo int unsigned, @@ -31,7 +32,7 @@ select XNo as XNo_vt1 from vt1; drop view vt1; drop table t1; - +--echo # Check sys_start, sys_end replace_result $sys_datatype_expl SYS_DATATYPE; eval create or replace table t1( x int, @@ -47,6 +48,7 @@ select * from t1; select x = 1 as A, sys_start = @sys_start as B, sys_end > sys_start as C from t1 for system_time all; drop table t1; +--echo # Multi-delete replace_result $sys_datatype_expl SYS_DATATYPE; eval create or replace table t1( x int, @@ -69,12 +71,6 @@ select x as t2_x_all from t2 for system_time all; drop table t1; drop table t2; ---echo # Basic + delete from view - ---echo # Check sys_start, sys_end - ---echo # Multi-delete - --echo # Update + delete create or replace table t1 (x int) with system versioning; insert into t1 values (1); diff --git a/mysql-test/suite/versioning/t/truncate.test b/mysql-test/suite/versioning/t/delete_history.test index 8a7f8e84a76..8a7f8e84a76 100644 --- a/mysql-test/suite/versioning/t/truncate.test +++ b/mysql-test/suite/versioning/t/delete_history.test diff --git a/mysql-test/suite/versioning/t/partition.test b/mysql-test/suite/versioning/t/partition.test index eca322d9ef4..ce8c2e5ec1a 100644 --- a/mysql-test/suite/versioning/t/partition.test +++ b/mysql-test/suite/versioning/t/partition.test @@ -517,4 +517,18 @@ execute immediate 'select * from t1 for update'; drop view v1; drop tables t, t1, t2, t3, t4; +--echo # +--echo # MDEV-18957 UPDATE with LIMIT clause is wrong for versioned partitioned tables +--echo # +create or replace table t1 ( + x int, + a varchar(255) +) with system versioning partition by system_time (partition p1 history, partition pn current); + +insert into t1 (x) values (1), (2), (3), (4); +update t1 set a= 'foo' limit 3; +update t1 set a= 'bar' limit 4; +select * from t1; +drop table t1; + --source suite/versioning/common_finish.inc diff --git a/mysql-test/suite/versioning/t/view.test b/mysql-test/suite/versioning/t/view.test index 5a03a50f1d3..c05fbfd3866 100644 --- a/mysql-test/suite/versioning/t/view.test +++ b/mysql-test/suite/versioning/t/view.test @@ -52,13 +52,13 @@ prepare stmt from @tmp; execute stmt; drop prepare stmt; select * from vt1; ---echo # VIEW with parameters [#151] +--echo # VIEW with parameters [tempesta-tech/mariadb#151] create or replace table t1 (x int) with system versioning; create or replace view vt1(c) as select x from t1; --replace_result 18446744073709551615 MAX_RESULT "TIMESTAMP'2038-01-19 03:14:07.999999'" MAX_RESULT show create view vt1; ---echo # VIEW over JOIN of versioned tables [#153] +--echo # VIEW over JOIN of versioned tables [tempesta-tech/mariadb#153] create or replace table t1 (a int) with system versioning; create or replace table t2 (b int) with system versioning; insert into t1 values (1); @@ -68,7 +68,7 @@ select * from vt12; create or replace view vt12 as select * from t1 for system_time as of timestamp ('0-0-0') cross join t2; select * from vt12; ---echo # VIEW improvements [#183] +--echo # VIEW improvements [tempesta-tech/mariadb#183] create or replace table t3 (x int); create or replace view vt1 as select * from t1, t2, t3; --replace_result 18446744073709551615 MAX_RESULT "TIMESTAMP'2038-01-19 03:14:07.999999'" MAX_RESULT @@ -80,11 +80,11 @@ create or replace view vt1 as select a, t2.row_end as endo from t3, t1, t2; --replace_result 18446744073709551615 MAX_RESULT "TIMESTAMP'2038-01-19 03:14:07.999999'" MAX_RESULT show create view vt1; ---echo # VIEW over UNION [#269] +--echo # VIEW over UNION [tempesta-tech/mariadb#269] create or replace view vt1 as select * from t1 union select * from t1; select * from vt1; ---echo # VIEW over UNION with non-versioned [#393] +--echo # VIEW over UNION with non-versioned [tempesta-tech/mariadb#393] create or replace table t2 (a int); create or replace view vt1 as select * from t1 union select * from t2; select * from vt1; @@ -104,10 +104,10 @@ drop tables t1, t2; --echo # --echo # MDEV-15146 SQLError[4122]: View is not system versioned --echo # -create table t1 (a int) with system versioning; +create or replace table t1 (a int) with system versioning; insert t1 values (1),(2); set @a=now(6); -create view v1 as select * from t1; +create or replace view v1 as select * from t1; delete from t1; select * from v1; select * from v1 for system_time as of @a; @@ -124,4 +124,37 @@ show create view v1; drop view v1, vt1, vt12; drop tables t1, t3; +--echo # +--echo # MDEV-18727 improve DML operation of System Versioning +--echo # +--replace_result $sys_datatype_expl SYS_DATATYPE +eval create or replace table t1 ( + x int, + row_start $sys_datatype_expl as row start invisible, + row_end $sys_datatype_expl as row end invisible, + period for system_time (row_start, row_end) +) with system versioning; +insert into t1 values (1), (2); +create or replace view v1 as select * from t1 where x > 1; +update v1 set x= x + 1; +select *, check_row(row_start, row_end) from t1 for system_time all order by x; +insert v1 values (4); +select *, check_row(row_start, row_end) from t1 for system_time all order by x; +delete from v1 where x < 4; +select *, check_row(row_start, row_end) from t1 for system_time all order by x; +--echo # multi-update +create or replace table t2 like t1; +insert into t2 values (1), (2); +create or replace view v2 as select * from t2 where x > 1; +update v1, v2 set v1.x= v1.x + 1, v2.x= v2.x + 1 where v1.x = v2.x + 2; +select *, check_row(row_start, row_end) from t1 for system_time all order by x; +select *, check_row(row_start, row_end) from t2 for system_time all order by x; +--echo # multi-delete +delete v1, v2 from v1 join v2 where v1.x = v2.x + 2; +select *, check_row(row_start, row_end) from t1 for system_time all order by x; +select *, check_row(row_start, row_end) from t2 for system_time all order by x; + +drop view v1, v2; +drop tables t1, t2; + --source suite/versioning/common_finish.inc |