diff options
author | Oleksandr Byelkin <sanja@mariadb.com> | 2021-07-31 22:59:58 +0200 |
---|---|---|
committer | Oleksandr Byelkin <sanja@mariadb.com> | 2021-07-31 22:59:58 +0200 |
commit | 7841a7eb09208f52fcbab7e80e38c7ca29b1339e (patch) | |
tree | 7f9c4d80d3b86a33c54ff0090865ada4d4ed577d /mysql-test/suite/versioning | |
parent | 77992bc710bbc16798c12da7081769817f87791a (diff) | |
parent | 1423cf5e3dcb3c50047f086a5933fe77006cf242 (diff) | |
download | mariadb-git-7841a7eb09208f52fcbab7e80e38c7ca29b1339e.tar.gz |
Merge branch '10.3' into 10.4
Diffstat (limited to 'mysql-test/suite/versioning')
-rw-r--r-- | mysql-test/suite/versioning/r/create.result | 19 | ||||
-rw-r--r-- | mysql-test/suite/versioning/r/partition.result | 21 | ||||
-rw-r--r-- | mysql-test/suite/versioning/r/sysvars-notembedded.result | 30 | ||||
-rw-r--r-- | mysql-test/suite/versioning/r/sysvars.result | 108 | ||||
-rw-r--r-- | mysql-test/suite/versioning/t/create.test | 12 | ||||
-rw-r--r-- | mysql-test/suite/versioning/t/partition.test | 21 | ||||
-rw-r--r-- | mysql-test/suite/versioning/t/sysvars-notembedded.test | 31 | ||||
-rw-r--r-- | mysql-test/suite/versioning/t/sysvars.test | 71 |
8 files changed, 277 insertions, 36 deletions
diff --git a/mysql-test/suite/versioning/r/create.result b/mysql-test/suite/versioning/r/create.result index 001af7bfc6a..75f7b560f30 100644 --- a/mysql-test/suite/versioning/r/create.result +++ b/mysql-test/suite/versioning/r/create.result @@ -592,3 +592,22 @@ t2 CREATE TEMPORARY TABLE `t2` ( ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 drop temporary table t2; drop table t1; +# +# MDEV-16857 system-invisible row_end is displayed in SHOW INDEX +# +create or replace table t1 (id int primary key, x int) with system versioning; +select table_schema, table_name, non_unique, index_schema, index_name, seq_in_index, column_name +from information_schema.statistics where table_name = 't1'; +table_schema table_name non_unique index_schema index_name seq_in_index column_name +test t1 0 test PRIMARY 1 id +show index from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment +t1 0 PRIMARY 1 id # # # # # # +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) NOT NULL, + `x` int(11) DEFAULT NULL, + PRIMARY KEY (`id`) +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING +drop table t1; diff --git a/mysql-test/suite/versioning/r/partition.result b/mysql-test/suite/versioning/r/partition.result index 9eeec045ef7..9ccc900cd12 100644 --- a/mysql-test/suite/versioning/r/partition.result +++ b/mysql-test/suite/versioning/r/partition.result @@ -734,6 +734,27 @@ create or replace table t1 (a int) with system versioning; alter table t1 partition by system_time (partition pn current); ERROR HY000: Wrong partitions for `t1`: must have at least one HISTORY and exactly one last CURRENT drop table t1; +# +# MDEV-22247 History partition overflow leads to wrong SELECT result +# +set timestamp= unix_timestamp('2000-01-01 00:00:00'); +create or replace table t1 (x int) with system versioning +partition by system_time interval 1 hour +(partition p0 history, partition p1 history, partition pn current); +insert into t1 values (0); +update t1 set x= x + 1; +set timestamp= unix_timestamp('2000-01-01 02:00:01'); +update t1 set x= x + 1; +select *, row_start, row_end from t1 for system_time as of '2000-01-01 02:00:00'; +x row_start row_end +1 2000-01-01 00:00:00.000000 2000-01-01 02:00:01.000000 +explain partitions select * from t1 for system_time as of '2000-01-01 02:00:00'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p1,pn ALL NULL NULL NULL NULL # Using where +explain partitions select * from t1; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 pn # NULL NULL NULL NULL # # +drop table t1; # End of 10.3 tests # # MDEV-22283 Server crashes in key_copy or unexpected error 156: The table already existed in the storage engine diff --git a/mysql-test/suite/versioning/r/sysvars-notembedded.result b/mysql-test/suite/versioning/r/sysvars-notembedded.result new file mode 100644 index 00000000000..8b1ad6cfc58 --- /dev/null +++ b/mysql-test/suite/versioning/r/sysvars-notembedded.result @@ -0,0 +1,30 @@ +create table t (a int) with system versioning; +set @before= UNIX_TIMESTAMP(now(6)); +insert into t values (1); +set @after= UNIX_TIMESTAMP(now(6)); +update t set a= 2; +set global system_versioning_asof= FROM_UNIXTIME(@after); +set system_versioning_asof= FROM_UNIXTIME(@after); +select * from t as nonempty; +a +1 +connect subcon,127.0.0.1,root,,,$SERVER_MYPORT_1; +connection subcon; +select * from t as nonempty; +a +1 +disconnect subcon; +connection default; +set global system_versioning_asof= FROM_UNIXTIME(@before); +select * from t as nonempty; +a +1 +connect subcon,127.0.0.1,root,,,$SERVER_MYPORT_1; +connection subcon; +select * from t as empty; +a +disconnect subcon; +connection default; +drop table t; +set global system_versioning_asof= DEFAULT; +set system_versioning_asof= DEFAULT; diff --git a/mysql-test/suite/versioning/r/sysvars.result b/mysql-test/suite/versioning/r/sysvars.result index 66513741631..a5a3f79990c 100644 --- a/mysql-test/suite/versioning/r/sysvars.result +++ b/mysql-test/suite/versioning/r/sysvars.result @@ -1,5 +1,7 @@ create table t (a int) with system versioning; +set @before= UNIX_TIMESTAMP(now(6)); insert into t values (1); +set @after= UNIX_TIMESTAMP(now(6)); update t set a= 2; show global variables like 'system_versioning_asof'; Variable_name Value @@ -56,65 +58,71 @@ ERROR 42000: Variable 'system_versioning_asof' can't be set to the value of '201 set system_versioning_asof= '0000-00-00 00:00'; ERROR 42000: Variable 'system_versioning_asof' can't be set to the value of '0000-00-00 00:00' # GLOBAL @@system_versioning_asof -set global system_versioning_asof= '1911-11-11 11:11:11.1111119'; +set global system_versioning_asof= '1991-11-11 11:11:11.1111119'; Warnings: -Note 1292 Truncated incorrect datetime value: '1911-11-11 11:11:11.1111119' -Note 1292 Truncated incorrect datetime value: '1911-11-11 11:11:11.1111119' +Note 1292 Truncated incorrect datetime value: '1991-11-11 11:11:11.1111119' show global variables like 'system_versioning_asof'; Variable_name Value -system_versioning_asof 1911-11-11 11:11:11.111111 -set global system_versioning_asof= '1900-01-01 00:00:00'; +system_versioning_asof 1991-11-11 11:11:11.111111 +set global system_versioning_asof= '1990-01-01 00:00:00'; show global variables like 'system_versioning_asof'; Variable_name Value -system_versioning_asof 1900-01-01 00:00:00.000000 -set global system_versioning_asof= timestamp'1911-11-11 11:11:11.1111119'; +system_versioning_asof 1990-01-01 00:00:00.000000 +set global system_versioning_asof= timestamp'1991-11-11 11:11:11.1111119'; Warnings: -Note 1292 Truncated incorrect DATETIME value: '1911-11-11 11:11:11.1111119' +Note 1292 Truncated incorrect DATETIME value: '1991-11-11 11:11:11.1111119' show global variables like 'system_versioning_asof'; Variable_name Value -system_versioning_asof 1911-11-11 11:11:11.111111 -set @ts= timestamp'1900-01-01 00:00:00'; +system_versioning_asof 1991-11-11 11:11:11.111111 +set @ts= timestamp'1990-01-01 00:00:00'; set global system_versioning_asof= @ts; show global variables like 'system_versioning_asof'; Variable_name Value -system_versioning_asof 1900-01-01 00:00:00.000000 +system_versioning_asof 1990-01-01 00:00:00.000000 set global system_versioning_asof= default; select @@global.system_versioning_asof; @@global.system_versioning_asof DEFAULT # SESSION @@system_versioning_asof -set system_versioning_asof= '1911-11-11 11:11:11.1111119'; +set system_versioning_asof= '1991-11-11 11:11:11.1111119'; Warnings: -Note 1292 Truncated incorrect datetime value: '1911-11-11 11:11:11.1111119' -Note 1292 Truncated incorrect datetime value: '1911-11-11 11:11:11.1111119' +Note 1292 Truncated incorrect datetime value: '1991-11-11 11:11:11.1111119' show variables like 'system_versioning_asof'; Variable_name Value -system_versioning_asof 1911-11-11 11:11:11.111111 -set system_versioning_asof= '1900-01-01 00:00:00'; +system_versioning_asof 1991-11-11 11:11:11.111111 +set system_versioning_asof= '1990-01-01 00:00:00'; show variables like 'system_versioning_asof'; Variable_name Value -system_versioning_asof 1900-01-01 00:00:00.000000 -set system_versioning_asof= timestamp'1911-11-11 11:11:11.1111119'; +system_versioning_asof 1990-01-01 00:00:00.000000 +set system_versioning_asof= timestamp'1991-11-11 11:11:11.1111119'; Warnings: -Note 1292 Truncated incorrect DATETIME value: '1911-11-11 11:11:11.1111119' +Note 1292 Truncated incorrect DATETIME value: '1991-11-11 11:11:11.1111119' show variables like 'system_versioning_asof'; Variable_name Value -system_versioning_asof 1911-11-11 11:11:11.111111 -set @ts= timestamp'1900-01-01 00:00:00'; +system_versioning_asof 1991-11-11 11:11:11.111111 +set @ts= timestamp'1990-01-01 00:00:00'; set system_versioning_asof= @ts; show variables like 'system_versioning_asof'; Variable_name Value -system_versioning_asof 1900-01-01 00:00:00.000000 +system_versioning_asof 1990-01-01 00:00:00.000000 # DEFAULT: value is copied from GLOBAL to SESSION -set global system_versioning_asof= timestamp'1911-11-11 11:11:11.111111'; -set system_versioning_asof= '1900-01-01 00:00:00'; +set global time_zone= "+03:00"; +set time_zone= "+10:00"; +set global system_versioning_asof= timestamp'1991-11-11 11:11:11.111111'; +set system_versioning_asof= '1990-01-01 00:00:00'; select @@global.system_versioning_asof != @@system_versioning_asof as different; different 1 set system_versioning_asof= default; +select @@global.system_versioning_asof != @@system_versioning_asof as different; +different +1 +set global system_versioning_asof= default; select @@global.system_versioning_asof = @@system_versioning_asof as equal; equal 1 +set global time_zone= DEFAULT; +set time_zone= DEFAULT; set global system_versioning_asof= DEFAULT; set system_versioning_asof= DEFAULT; select @@global.system_versioning_asof, @@system_versioning_asof; @@ -142,6 +150,56 @@ select * from t for system_time between '1970-01-01 00:00' and current_timestamp a 2 1 +# MDEV-16026: Global system_versioning_asof must not be used if client sessions can have non-default time zone +# changing time zone should not abuse `system_versioning_asof` +set session time_zone = '+10:00'; +set global system_versioning_asof = '1999-09-08 00:00:00.000000'; +show global variables like 'system_versioning_asof'; +Variable_name Value +system_versioning_asof 1999-09-08 00:00:00.000000 +set session time_zone = '+03:00'; +show global variables like 'system_versioning_asof'; +Variable_name Value +system_versioning_asof 1999-09-07 17:00:00.000000 +set session time_zone = '+03:00'; +set session system_versioning_asof = '2000-09-08 00:00:00.000000'; +show session variables like 'system_versioning_asof'; +Variable_name Value +system_versioning_asof 2000-09-08 00:00:00.000000 +set session time_zone = '+10:00'; +show session variables like 'system_versioning_asof'; +Variable_name Value +system_versioning_asof 2000-09-08 07:00:00.000000 +# global and local time zones should not interfere +show global variables like 'system_versioning_asof'; +Variable_name Value +system_versioning_asof 1999-09-08 00:00:00.000000 +set time_zone= "+10:00"; +set system_versioning_asof= FROM_UNIXTIME(@before); +select * from t as empty; +a +set system_versioning_asof= FROM_UNIXTIME(@after); +select * from t as nonempty; +a +1 +set time_zone= "+03:00"; +set system_versioning_asof= FROM_UNIXTIME(@before); +select * from t as empty; +a +set system_versioning_asof= FROM_UNIXTIME(@after); +select * from t as nonempty; +a +1 +# MDEV-16481: set global system_versioning_asof=sf() crashes in specific case +# Using global variable inside a stored function should not crash +create or replace function now_global() returns timestamp +return CONVERT_TZ(now(), @@session.time_zone, @@global.time_zone); +set global system_versioning_asof= now_global(); +drop function now_global; +set global time_zone= "SYSTEM"; +set time_zone= "SYSTEM"; +set global system_versioning_asof= default; +set system_versioning_asof= default; show status like "Feature_system_versioning"; Variable_name Value Feature_system_versioning 2 @@ -153,7 +211,7 @@ create or replace table t1 (x int) with system versioning; create or replace table t2 (y int); insert into t1 values (1); insert into t2 values (1); -set system_versioning_asof= '1970-01-01 00:00:00'; +set system_versioning_asof= '1970-01-02 00:00:00'; delete t1, t2 from t1 join t2 where t1.x = t2.y; select * from t1 for system_time as of timestamp now(6); x diff --git a/mysql-test/suite/versioning/t/create.test b/mysql-test/suite/versioning/t/create.test index 1f016fed871..6f8cff3ca2e 100644 --- a/mysql-test/suite/versioning/t/create.test +++ b/mysql-test/suite/versioning/t/create.test @@ -439,3 +439,15 @@ show create table t1; show create table t2; drop temporary table t2; drop table t1; + +--echo # +--echo # MDEV-16857 system-invisible row_end is displayed in SHOW INDEX +--echo # +create or replace table t1 (id int primary key, x int) with system versioning; +select table_schema, table_name, non_unique, index_schema, index_name, seq_in_index, column_name +from information_schema.statistics where table_name = 't1'; +--replace_column 6 # 7 # 8 # 9 # 10 # 11 # +show index from t1; +--replace_result $default_engine DEFAULT_ENGINE +show create table t1; +drop table t1; diff --git a/mysql-test/suite/versioning/t/partition.test b/mysql-test/suite/versioning/t/partition.test index f50e6c07b64..c6279444a6a 100644 --- a/mysql-test/suite/versioning/t/partition.test +++ b/mysql-test/suite/versioning/t/partition.test @@ -697,6 +697,27 @@ alter table t1 partition by system_time (partition pn current); # Cleanup drop table t1; +--echo # +--echo # MDEV-22247 History partition overflow leads to wrong SELECT result +--echo # +set timestamp= unix_timestamp('2000-01-01 00:00:00'); +create or replace table t1 (x int) with system versioning +partition by system_time interval 1 hour +(partition p0 history, partition p1 history, partition pn current); + +insert into t1 values (0); +update t1 set x= x + 1; + +set timestamp= unix_timestamp('2000-01-01 02:00:01'); +update t1 set x= x + 1; + +select *, row_start, row_end from t1 for system_time as of '2000-01-01 02:00:00'; +--replace_column 10 # +explain partitions select * from t1 for system_time as of '2000-01-01 02:00:00'; +--replace_column 5 # 10 # 11 # +explain partitions select * from t1; +drop table t1; + --echo # End of 10.3 tests --echo # diff --git a/mysql-test/suite/versioning/t/sysvars-notembedded.test b/mysql-test/suite/versioning/t/sysvars-notembedded.test new file mode 100644 index 00000000000..314972bc375 --- /dev/null +++ b/mysql-test/suite/versioning/t/sysvars-notembedded.test @@ -0,0 +1,31 @@ +source include/not_embedded.inc; + +create table t (a int) with system versioning; +set @before= UNIX_TIMESTAMP(now(6)); +insert into t values (1); +set @after= UNIX_TIMESTAMP(now(6)); +update t set a= 2; + +set global system_versioning_asof= FROM_UNIXTIME(@after); +set system_versioning_asof= FROM_UNIXTIME(@after); +select * from t as nonempty; + +--connect (subcon,127.0.0.1,root,,,$SERVER_MYPORT_1) +--connection subcon +select * from t as nonempty; +--disconnect subcon +--connection default + +set global system_versioning_asof= FROM_UNIXTIME(@before); +select * from t as nonempty; + +--connect (subcon,127.0.0.1,root,,,$SERVER_MYPORT_1) +--connection subcon +select * from t as empty; +--disconnect subcon +--connection default + +drop table t; + +set global system_versioning_asof= DEFAULT; +set system_versioning_asof= DEFAULT; diff --git a/mysql-test/suite/versioning/t/sysvars.test b/mysql-test/suite/versioning/t/sysvars.test index a1026418e98..ab1f612edf3 100644 --- a/mysql-test/suite/versioning/t/sysvars.test +++ b/mysql-test/suite/versioning/t/sysvars.test @@ -1,5 +1,7 @@ create table t (a int) with system versioning; +set @before= UNIX_TIMESTAMP(now(6)); insert into t values (1); +set @after= UNIX_TIMESTAMP(now(6)); update t set a= 2; show global variables like 'system_versioning_asof'; @@ -51,16 +53,16 @@ set system_versioning_asof= '2011-00-28 00:00'; set system_versioning_asof= '0000-00-00 00:00'; --echo # GLOBAL @@system_versioning_asof -set global system_versioning_asof= '1911-11-11 11:11:11.1111119'; +set global system_versioning_asof= '1991-11-11 11:11:11.1111119'; show global variables like 'system_versioning_asof'; -set global system_versioning_asof= '1900-01-01 00:00:00'; +set global system_versioning_asof= '1990-01-01 00:00:00'; show global variables like 'system_versioning_asof'; -set global system_versioning_asof= timestamp'1911-11-11 11:11:11.1111119'; +set global system_versioning_asof= timestamp'1991-11-11 11:11:11.1111119'; show global variables like 'system_versioning_asof'; -set @ts= timestamp'1900-01-01 00:00:00'; +set @ts= timestamp'1990-01-01 00:00:00'; set global system_versioning_asof= @ts; show global variables like 'system_versioning_asof'; @@ -68,26 +70,32 @@ set global system_versioning_asof= default; select @@global.system_versioning_asof; --echo # SESSION @@system_versioning_asof -set system_versioning_asof= '1911-11-11 11:11:11.1111119'; +set system_versioning_asof= '1991-11-11 11:11:11.1111119'; show variables like 'system_versioning_asof'; -set system_versioning_asof= '1900-01-01 00:00:00'; +set system_versioning_asof= '1990-01-01 00:00:00'; show variables like 'system_versioning_asof'; -set system_versioning_asof= timestamp'1911-11-11 11:11:11.1111119'; +set system_versioning_asof= timestamp'1991-11-11 11:11:11.1111119'; show variables like 'system_versioning_asof'; -set @ts= timestamp'1900-01-01 00:00:00'; +set @ts= timestamp'1990-01-01 00:00:00'; set system_versioning_asof= @ts; show variables like 'system_versioning_asof'; --echo # DEFAULT: value is copied from GLOBAL to SESSION -set global system_versioning_asof= timestamp'1911-11-11 11:11:11.111111'; -set system_versioning_asof= '1900-01-01 00:00:00'; +set global time_zone= "+03:00"; +set time_zone= "+10:00"; +set global system_versioning_asof= timestamp'1991-11-11 11:11:11.111111'; +set system_versioning_asof= '1990-01-01 00:00:00'; select @@global.system_versioning_asof != @@system_versioning_asof as different; set system_versioning_asof= default; +select @@global.system_versioning_asof != @@system_versioning_asof as different; +set global system_versioning_asof= default; select @@global.system_versioning_asof = @@system_versioning_asof as equal; +set global time_zone= DEFAULT; +set time_zone= DEFAULT; set global system_versioning_asof= DEFAULT; set system_versioning_asof= DEFAULT; select @@global.system_versioning_asof, @@system_versioning_asof; @@ -100,6 +108,47 @@ select * from t for system_time all; select * from t for system_time from '1970-01-01 00:00' to current_timestamp(6); select * from t for system_time between '1970-01-01 00:00' and current_timestamp(6); +-- echo # MDEV-16026: Global system_versioning_asof must not be used if client sessions can have non-default time zone +-- echo # changing time zone should not abuse `system_versioning_asof` + +set session time_zone = '+10:00'; +set global system_versioning_asof = '1999-09-08 00:00:00.000000'; +show global variables like 'system_versioning_asof'; +set session time_zone = '+03:00'; +show global variables like 'system_versioning_asof'; + +set session time_zone = '+03:00'; +set session system_versioning_asof = '2000-09-08 00:00:00.000000'; +show session variables like 'system_versioning_asof'; +set session time_zone = '+10:00'; +show session variables like 'system_versioning_asof'; +-- echo # global and local time zones should not interfere +show global variables like 'system_versioning_asof'; + +set time_zone= "+10:00"; +set system_versioning_asof= FROM_UNIXTIME(@before); +select * from t as empty; +set system_versioning_asof= FROM_UNIXTIME(@after); +select * from t as nonempty; + +set time_zone= "+03:00"; +set system_versioning_asof= FROM_UNIXTIME(@before); +select * from t as empty; +set system_versioning_asof= FROM_UNIXTIME(@after); +select * from t as nonempty; + +--echo # MDEV-16481: set global system_versioning_asof=sf() crashes in specific case +--echo # Using global variable inside a stored function should not crash +create or replace function now_global() returns timestamp + return CONVERT_TZ(now(), @@session.time_zone, @@global.time_zone); +set global system_versioning_asof= now_global(); +drop function now_global; + +set global time_zone= "SYSTEM"; +set time_zone= "SYSTEM"; +set global system_versioning_asof= default; +set system_versioning_asof= default; + show status like "Feature_system_versioning"; drop table t; @@ -111,7 +160,7 @@ create or replace table t1 (x int) with system versioning; create or replace table t2 (y int); insert into t1 values (1); insert into t2 values (1); -set system_versioning_asof= '1970-01-01 00:00:00'; +set system_versioning_asof= '1970-01-02 00:00:00'; delete t1, t2 from t1 join t2 where t1.x = t2.y; select * from t1 for system_time as of timestamp now(6); |