MDEV-16026 MDEV-16481 refactor Sys_var_vers_asof

MDEV-16026: Forbid global system_versioning_asof in non-default time zone

* store `system_versioning_asof` in unix time;
* both session and global vars are processed in session timezone;
* setting `default` does not copy global variable anymore. Instead, it sets
  system_time to SYSTEM_TIME_UNSPECIFIED, which means that no 'AS OF' time
  is applied and `now()` can be assumed

As a regression, we cannot assign values below 1970 (UTC) anymore

MDEV-16481: set global system_versioning_asof=sf() crashes in specific case

* sys_vars.h: add `MYSQL_TIME` field to `set_var::save_result`
* sys_vars.ic: get rid of calling `var->value->get_date()` from
 `Sys_var_vers_asof::update()`
* versioning.sysvars: add test; remove double warning

refactor Sys_var_vers_asof

* inherit from sys_var rather than Sys_var_enum
* remove junk "DEFAULT" keyword. There is DEFAULT in SQL grammar for it.
* make all conversions in check() to avoid possible errors
* avoid double var->value evaluation, which could
  consequence in undefined behavior

2 files changed, 182 insertions, 36 deletions

diff --git a/mysql-test/suite/versioning/r/sysvars.result b/mysql-test/suite/versioning/r/sysvars.result
index cd858d6cf69..e3af236a7fa 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,77 @@ 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
+set global 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;
+# 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 +232,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/sysvars.test b/mysql-test/suite/versioning/t/sysvars.test
index 2f62262fc8e..248020010b6 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,65 @@ 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;
+
+set global 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
+
+--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 +178,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);