MDEV-16144 Default TIMESTAMP clause for SELECT from versioned

1. Removed TIMESTAMP/TRANSACTION unit auto-detection in favor of default TIMESTAMP.

Reasons:

1.1. rare practical use and doubtful advantage of such auto-detection;
1.2. it conflicts with MDEV-16226 (TRX_ID-based versioned tables performance improvement).

Needless check_unit membership removed.

2. SQL: versioning type handling refactoring

Vers_type_handler hierarchy stores versioning properties of type.

virtual Type_handler::vers() accesses specialization of
Vers_type_handler for specific type.

virtual Vers_type_handler::kind() returns versioning kind
(timestamp/trx_id).

Removed Type_handler::Vers_history_point_check_unit() in favor of
Type_handler::vers().

Renames:

require_timestamp() -> require_timestamp_error()
require_trx_id() -> require_trx_id_error()

EDIT by Alexander Barkov (@abarkov):

check_sys_fields() moved to Vers_type_handler::check_sys_fields()

17 files changed, 228 insertions, 280 deletions

diff --git a/mysql-test/suite/versioning/r/create.result b/mysql-test/suite/versioning/r/create.result
index 747da5483ec..b32687932c6 100644
--- a/mysql-test/suite/versioning/r/create.result
+++ b/mysql-test/suite/versioning/r/create.result
@@ -519,7 +519,7 @@ row_start bigint as row start,
 row_end bigint as row end,
 period for system_time (row_start, row_end)
 ) engine=myisam with system versioning;
-ERROR HY000: `row_start` must be of type TIMESTAMP(6) for system-versioned table `t1`
+ERROR HY000: `row_start` must be of type BIGINT(20) UNSIGNED for system-versioned table `t1`
 create table t (
 a int,
 row_start datetime(6) generated always as row start,
diff --git a/mysql-test/suite/versioning/r/select.result b/mysql-test/suite/versioning/r/select.result
index f1ef954baa6..2cd9904b793 100644
--- a/mysql-test/suite/versioning/r/select.result
+++ b/mysql-test/suite/versioning/r/select.result
@@ -383,12 +383,9 @@ x
 select x from t1 for system_time as of transaction @trx_start;
 x
 1
-## no specifier (auto-detection)
+## no specifier (defaults to timestamp)
 select x from t1 for system_time as of @ts;
 x
-select x from t1 for system_time as of @trx_start;
-x
-1
 ### Issue #365, bug 4 (related to #226, optimized fields)
 create or replace table t1 (i int, b int) with system versioning;
 insert into t1 values (0, 0), (0, 0);
diff --git a/mysql-test/suite/versioning/r/select2,trx_id.rdiff b/mysql-test/suite/versioning/r/select2,trx_id.rdiff
index d23eb5afbc0..89399516777 100644
--- a/mysql-test/suite/versioning/r/select2,trx_id.rdiff
+++ b/mysql-test/suite/versioning/r/select2,trx_id.rdiff
@@ -14,7 +14,7 @@
  9	109
  3	33
 -select x as ASOF2_x, y from t1 for system_time as of @t0;
-+select x as ASOF2_x, y from t1 for system_time as of @x0;
++select x as ASOF2_x, y from t1 for system_time as of transaction @x0;
  ASOF2_x	y
  0	100
  1	101
@@ -23,7 +23,7 @@
  8	108
  9	109
 -select x as FROMTO2_x, y from t1 for system_time from '0-0-0 0:0:0' to @t1;
-+select x as FROMTO2_x, y from t1 for system_time from @x0 to @x1;
++select x as FROMTO2_x, y from t1 for system_time from transaction @x0 to transaction @x1;
  FROMTO2_x	y
  0	100
  1	101
@@ -31,7 +31,7 @@
  7	107
  8	108
  9	109
--select x as BETWAND2_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1;
+-select x as BETWAND2_x, y from t1 for system_time between '0-0-0 0:0:0' and @t1;
 +select x as BETWAND2_x, y from t1 for system_time between transaction @x0 and transaction @x1;
  BETWAND2_x	y
  0	100
diff --git a/mysql-test/suite/versioning/r/select2.result b/mysql-test/suite/versioning/r/select2.result
index 22388359885..abe1c821172 100644
--- a/mysql-test/suite/versioning/r/select2.result
+++ b/mysql-test/suite/versioning/r/select2.result
@@ -110,7 +110,7 @@ FROMTO2_x	y
 7	107
 8	108
 9	109
-select x as BETWAND2_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1;
+select x as BETWAND2_x, y from t1 for system_time between '0-0-0 0:0:0' and @t1;
 BETWAND2_x	y
 0	100
 1	101
diff --git a/mysql-test/suite/versioning/r/trx_id.result b/mysql-test/suite/versioning/r/trx_id.result
index 5d4669c46e2..8949f4b8dae 100644
--- a/mysql-test/suite/versioning/r/trx_id.result
+++ b/mysql-test/suite/versioning/r/trx_id.result
@@ -256,41 +256,41 @@ ERROR HY000: Illegal parameter data type row for operation 'FOR SYSTEM_TIME'
 #
 # DOUBLE is not supported, use explicit CAST
 #
-SELECT * FROM t1 FOR SYSTEM_TIME AS OF RAND();
+SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION RAND();
 ERROR HY000: Illegal parameter data type double for operation 'FOR SYSTEM_TIME'
-SELECT * FROM t1 FOR SYSTEM_TIME AS OF (RAND());
+SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION (RAND());
 ERROR HY000: Illegal parameter data type double for operation 'FOR SYSTEM_TIME'
-SELECT * FROM t1 FOR SYSTEM_TIME AS OF COALESCE(RAND());
+SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION COALESCE(RAND());
 ERROR HY000: Illegal parameter data type double for operation 'FOR SYSTEM_TIME'
-SELECT * FROM t2 FOR SYSTEM_TIME AS OF RAND();
+SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION RAND();
 ERROR HY000: Illegal parameter data type double for operation 'FOR SYSTEM_TIME'
-SELECT * FROM t2 FOR SYSTEM_TIME AS OF (RAND());
+SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION (RAND());
 ERROR HY000: Illegal parameter data type double for operation 'FOR SYSTEM_TIME'
-SELECT * FROM t2 FOR SYSTEM_TIME AS OF COALESCE(RAND());
+SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION COALESCE(RAND());
 ERROR HY000: Illegal parameter data type double for operation 'FOR SYSTEM_TIME'
 #
 # DECIMAL is not supported, use explicit CAST
 #
-SELECT * FROM t1 FOR SYSTEM_TIME AS OF 10.1;
+SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION 10.1;
 ERROR HY000: Illegal parameter data type decimal for operation 'FOR SYSTEM_TIME'
-SELECT * FROM t1 FOR SYSTEM_TIME AS OF COALESCE(10.1);
+SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION COALESCE(10.1);
 ERROR HY000: Illegal parameter data type decimal for operation 'FOR SYSTEM_TIME'
-SELECT * FROM t2 FOR SYSTEM_TIME AS OF 10.1;
+SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION 10.1;
 ERROR HY000: Illegal parameter data type decimal for operation 'FOR SYSTEM_TIME'
-SELECT * FROM t2 FOR SYSTEM_TIME AS OF COALESCE(10.1);
+SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION COALESCE(10.1);
 ERROR HY000: Illegal parameter data type decimal for operation 'FOR SYSTEM_TIME'
 #
 # YEAR is not supported, use explicit CAST
 #
 BEGIN NOT ATOMIC
 DECLARE var YEAR;
-SELECT * FROM t1 FOR SYSTEM_TIME AS OF var;
+SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION var;
 END;
 $$
 ERROR HY000: Illegal parameter data type year for operation 'FOR SYSTEM_TIME'
 BEGIN NOT ATOMIC
 DECLARE var YEAR;
-SELECT * FROM t2 FOR SYSTEM_TIME AS OF var;
+SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION var;
 END;
 $$
 ERROR HY000: Illegal parameter data type year for operation 'FOR SYSTEM_TIME'
@@ -299,13 +299,13 @@ ERROR HY000: Illegal parameter data type year for operation 'FOR SYSTEM_TIME'
 #
 BEGIN NOT ATOMIC
 DECLARE var ENUM('xxx') DEFAULT 'xxx';
-SELECT * FROM t1 FOR SYSTEM_TIME AS OF var;
+SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION var;
 END;
 $$
 ERROR HY000: Illegal parameter data type enum for operation 'FOR SYSTEM_TIME'
 BEGIN NOT ATOMIC
 DECLARE var ENUM('xxx') DEFAULT 'xxx';
-SELECT * FROM t2 FOR SYSTEM_TIME AS OF var;
+SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION var;
 END;
 $$
 ERROR HY000: Illegal parameter data type enum for operation 'FOR SYSTEM_TIME'
@@ -314,28 +314,19 @@ ERROR HY000: Illegal parameter data type enum for operation 'FOR SYSTEM_TIME'
 #
 BEGIN NOT ATOMIC
 DECLARE var SET('xxx') DEFAULT 'xxx';
-SELECT * FROM t1 FOR SYSTEM_TIME AS OF var;
+SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION var;
 END;
 $$
 ERROR HY000: Illegal parameter data type set for operation 'FOR SYSTEM_TIME'
 BEGIN NOT ATOMIC
 DECLARE var SET('xxx') DEFAULT 'xxx';
-SELECT * FROM t2 FOR SYSTEM_TIME AS OF var;
+SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION var;
 END;
 $$
 ERROR HY000: Illegal parameter data type set for operation 'FOR SYSTEM_TIME'
-#
-# BIT is resolved to TRANSACTION
-#
-BEGIN NOT ATOMIC
-DECLARE var BIT(10);
-SELECT * FROM t1 FOR SYSTEM_TIME AS OF var;
-END;
-$$
-x
 BEGIN NOT ATOMIC
 DECLARE var BIT(10);
-SELECT * FROM t2 FOR SYSTEM_TIME AS OF var;
+SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION var;
 END;
 $$
 ERROR HY000: Transaction-precise system versioning for `t2` is not supported
@@ -347,20 +338,6 @@ x
 1
 SELECT * FROM t2 FOR SYSTEM_TIME AS OF '2038-12-30 00:00:00';
 x
-#
-# HEX hybrids resolve to TRANSACTION
-#
-SELECT * FROM t1 FOR SYSTEM_TIME AS OF (0xFFFFFFFF);
-ERROR HY000: TRX_ID 4294967295 not found in `mysql.transaction_registry`
-SELECT * FROM t2 FOR SYSTEM_TIME AS OF (0xFFFFFFFF);
-ERROR HY000: Transaction-precise system versioning for `t2` is not supported
-#
-# BIT literals resolve to TRANSACTION
-#
-SELECT * FROM t1 FOR SYSTEM_TIME AS OF (b'11111111111111111111111111111111');
-ERROR HY000: TRX_ID 4294967295 not found in `mysql.transaction_registry`
-SELECT * FROM t2 FOR SYSTEM_TIME AS OF (b'11111111111111111111111111111111');
-ERROR HY000: Transaction-precise system versioning for `t2` is not supported
 DROP TABLE t1, t2;
 #
 # MDEV-16094 Crash when using AS OF with a stored function
@@ -383,11 +360,11 @@ PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp)
 ) ENGINE=InnoDB WITH SYSTEM VERSIONING;
 SELECT * FROM tts FOR SYSTEM_TIME AS OF fts();
 x	start_timestamp	end_timestamp
-SELECT * FROM tts FOR SYSTEM_TIME AS OF ftx();
+SELECT * FROM tts FOR SYSTEM_TIME AS OF TRANSACTION ftx();
 ERROR HY000: Transaction-precise system versioning for `tts` is not supported
 SELECT * FROM ttx FOR SYSTEM_TIME AS OF fts();
 x	start_timestamp	end_timestamp
-SELECT * FROM ttx FOR SYSTEM_TIME AS OF ftx();
+SELECT * FROM ttx FOR SYSTEM_TIME AS OF TRANSACTION ftx();
 x	start_timestamp	end_timestamp
 DROP TABLE tts;
 DROP TABLE ttx;
diff --git a/mysql-test/suite/versioning/r/view.result b/mysql-test/suite/versioning/r/view.result
index e897f0e17d9..8c3114b42a1 100644
--- a/mysql-test/suite/versioning/r/view.result
+++ b/mysql-test/suite/versioning/r/view.result
@@ -147,6 +147,6 @@ i
 create or replace view v1 as select * from t1 for system_time as of date_sub(now(), interval 6 second);
 show create view v1;
 View	Create View	character_set_client	collation_connection
-v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`i` AS `i` from `t1` FOR SYSTEM_TIME AS OF current_timestamp() - interval 6 second	latin1	latin1_swedish_ci
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`i` AS `i` from `t1` FOR SYSTEM_TIME AS OF TIMESTAMP current_timestamp() - interval 6 second	latin1	latin1_swedish_ci
 drop view v1;
 drop table t1;
diff --git a/mysql-test/suite/versioning/t/select.test b/mysql-test/suite/versioning/t/select.test
index c4c1d703ce5..e8d34e55b16 100644
--- a/mysql-test/suite/versioning/t/select.test
+++ b/mysql-test/suite/versioning/t/select.test
@@ -55,8 +55,8 @@ select x as ALL_x, y from t1 for system_time all;
 --disable_query_log
 if ($MTR_COMBINATION_TRX_ID)
 {
-  select x as ASOF2_x, y from t1 for system_time as of @x0;
-  select x as FROMTO2_x, y from t1 for system_time from @x0 to @x1;
+  select x as ASOF2_x, y from t1 for system_time as of transaction @x0;
+  select x as FROMTO2_x, y from t1 for system_time from transaction @x0 to transaction @x1;
   select x as BETWAND2_x, y from t1 for system_time between transaction @x0 and transaction @x1;
 }
 if ($MTR_COMBINATION_TIMESTAMP)
@@ -253,9 +253,8 @@ select x from t1 for system_time as of timestamp @ts;
 --echo ## TRANSACTION specifier
 select x from t1 for system_time as of transaction @trx_start;
 
---echo ## no specifier (auto-detection)
+--echo ## no specifier (defaults to timestamp)
 select x from t1 for system_time as of @ts;
-select x from t1 for system_time as of @trx_start;
 
 --echo ### Issue #365, bug 4 (related to #226, optimized fields)
 create or replace table t1 (i int, b int) with system versioning;
@@ -369,6 +368,12 @@ insert into t1 values (1);
 delete from t1;
 select row_start from t1 for system_time all into @t1;
 select row_end from t1 for system_time all into @t2;
+--disable_query_log
+if($MTR_COMBINATION_TRX_ID) {
+  set @t1= trt_begin_ts(@t1);
+  set @t2= trt_commit_ts(@t2);
+}
+--enable_query_log
 select * from t1 for system_time between @t1 and @t2;
 select * from t1 for system_time between @t2 and @t1;
 select * from t1 for system_time from @t1 to @t2;
diff --git a/mysql-test/suite/versioning/t/select2.test b/mysql-test/suite/versioning/t/select2.test
index d1b73fa799b..f624c512c87 100644
--- a/mysql-test/suite/versioning/t/select2.test
+++ b/mysql-test/suite/versioning/t/select2.test
@@ -41,14 +41,14 @@ select x as BETWAND_x, y from t1 for system_time between '0-0-0 0:0:0' and times
 select x as ALL_x, y from t1 for system_time all;
 
 if($MTR_COMBINATION_TRX_ID) {
-  select x as ASOF2_x, y from t1 for system_time as of @x0;
-  select x as FROMTO2_x, y from t1 for system_time from @x0 to @x1;
+  select x as ASOF2_x, y from t1 for system_time as of transaction @x0;
+  select x as FROMTO2_x, y from t1 for system_time from transaction @x0 to transaction @x1;
   select x as BETWAND2_x, y from t1 for system_time between transaction @x0 and transaction @x1;
 }
 if(!$MTR_COMBINATION_TRX_ID) {
   select x as ASOF2_x, y from t1 for system_time as of @t0;
   select x as FROMTO2_x, y from t1 for system_time from '0-0-0 0:0:0' to @t1;
-  select x as BETWAND2_x, y from t1 for system_time between timestamp '0-0-0 0:0:0' and timestamp @t1;
+  select x as BETWAND2_x, y from t1 for system_time between '0-0-0 0:0:0' and @t1;
 }
 
 drop table t1;
diff --git a/mysql-test/suite/versioning/t/trx_id.test b/mysql-test/suite/versioning/t/trx_id.test
index 943d9182a8a..df4c14a281c 100644
--- a/mysql-test/suite/versioning/t/trx_id.test
+++ b/mysql-test/suite/versioning/t/trx_id.test
@@ -233,18 +233,18 @@ SELECT * FROM t2 FOR SYSTEM_TIME AS OF (1,1);
 --echo #
 
 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
-SELECT * FROM t1 FOR SYSTEM_TIME AS OF RAND();
+SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION RAND();
 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
-SELECT * FROM t1 FOR SYSTEM_TIME AS OF (RAND());
+SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION (RAND());
 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
-SELECT * FROM t1 FOR SYSTEM_TIME AS OF COALESCE(RAND());
+SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION COALESCE(RAND());
 
 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
-SELECT * FROM t2 FOR SYSTEM_TIME AS OF RAND();
+SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION RAND();
 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
-SELECT * FROM t2 FOR SYSTEM_TIME AS OF (RAND());
+SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION (RAND());
 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
-SELECT * FROM t2 FOR SYSTEM_TIME AS OF COALESCE(RAND());
+SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION COALESCE(RAND());
 
 
 --echo #
@@ -252,14 +252,14 @@ SELECT * FROM t2 FOR SYSTEM_TIME AS OF COALESCE(RAND());
 --echo #
 
 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
-SELECT * FROM t1 FOR SYSTEM_TIME AS OF 10.1;
+SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION 10.1;
 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
-SELECT * FROM t1 FOR SYSTEM_TIME AS OF COALESCE(10.1);
+SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION COALESCE(10.1);
 
 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
-SELECT * FROM t2 FOR SYSTEM_TIME AS OF 10.1;
+SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION 10.1;
 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
-SELECT * FROM t2 FOR SYSTEM_TIME AS OF COALESCE(10.1);
+SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION COALESCE(10.1);
 
 
 --echo #
@@ -270,7 +270,7 @@ DELIMITER $$;
 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
 BEGIN NOT ATOMIC
   DECLARE var YEAR;
-  SELECT * FROM t1 FOR SYSTEM_TIME AS OF var;
+  SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION var;
 END;
 $$
 DELIMITER ;$$
@@ -279,7 +279,7 @@ DELIMITER $$;
 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
 BEGIN NOT ATOMIC
   DECLARE var YEAR;
-  SELECT * FROM t2 FOR SYSTEM_TIME AS OF var;
+  SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION var;
 END;
 $$
 DELIMITER ;$$
@@ -293,7 +293,7 @@ DELIMITER $$;
 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
 BEGIN NOT ATOMIC
   DECLARE var ENUM('xxx') DEFAULT 'xxx';
-  SELECT * FROM t1 FOR SYSTEM_TIME AS OF var;
+  SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION var;
 END;
 $$
 DELIMITER ;$$
@@ -303,7 +303,7 @@ DELIMITER $$;
 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
 BEGIN NOT ATOMIC
   DECLARE var ENUM('xxx') DEFAULT 'xxx';
-  SELECT * FROM t2 FOR SYSTEM_TIME AS OF var;
+  SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION var;
 END;
 $$
 DELIMITER ;$$
@@ -317,7 +317,7 @@ DELIMITER $$;
 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
 BEGIN NOT ATOMIC
   DECLARE var SET('xxx') DEFAULT 'xxx';
-  SELECT * FROM t1 FOR SYSTEM_TIME AS OF var;
+  SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION var;
 END;
 $$
 DELIMITER ;$$
@@ -326,29 +326,17 @@ DELIMITER $$;
 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
 BEGIN NOT ATOMIC
   DECLARE var SET('xxx') DEFAULT 'xxx';
-  SELECT * FROM t2 FOR SYSTEM_TIME AS OF var;
+  SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION var;
 END;
 $$
 DELIMITER ;$$
 
 
---echo #
---echo # BIT is resolved to TRANSACTION
---echo #
-
-DELIMITER $$;
-BEGIN NOT ATOMIC
-  DECLARE var BIT(10);
-  SELECT * FROM t1 FOR SYSTEM_TIME AS OF var;
-END;
-$$
-DELIMITER ;$$
-
 DELIMITER $$;
 --error ER_VERS_ENGINE_UNSUPPORTED
 BEGIN NOT ATOMIC
   DECLARE var BIT(10);
-  SELECT * FROM t2 FOR SYSTEM_TIME AS OF var;
+  SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION var;
 END;
 $$
 DELIMITER ;$$
@@ -360,27 +348,6 @@ DELIMITER ;$$
 
 SELECT * FROM t1 FOR SYSTEM_TIME AS OF '2038-12-30 00:00:00';
 SELECT * FROM t2 FOR SYSTEM_TIME AS OF '2038-12-30 00:00:00';
-
-
---echo #
---echo # HEX hybrids resolve to TRANSACTION
---echo #
-
---error ER_VERS_NO_TRX_ID
-SELECT * FROM t1 FOR SYSTEM_TIME AS OF (0xFFFFFFFF);
---error ER_VERS_ENGINE_UNSUPPORTED
-SELECT * FROM t2 FOR SYSTEM_TIME AS OF (0xFFFFFFFF);
-
-
---echo #
---echo # BIT literals resolve to TRANSACTION
---echo #
-
---error ER_VERS_NO_TRX_ID
-SELECT * FROM t1 FOR SYSTEM_TIME AS OF (b'11111111111111111111111111111111');
---error ER_VERS_ENGINE_UNSUPPORTED
-SELECT * FROM t2 FOR SYSTEM_TIME AS OF (b'11111111111111111111111111111111');
-
 DROP TABLE t1, t2;
 
 
@@ -409,9 +376,9 @@ CREATE TABLE tts
 
 SELECT * FROM tts FOR SYSTEM_TIME AS OF fts();
 --error ER_VERS_ENGINE_UNSUPPORTED
-SELECT * FROM tts FOR SYSTEM_TIME AS OF ftx();
+SELECT * FROM tts FOR SYSTEM_TIME AS OF TRANSACTION ftx();
 SELECT * FROM ttx FOR SYSTEM_TIME AS OF fts();
-SELECT * FROM ttx FOR SYSTEM_TIME AS OF ftx();
+SELECT * FROM ttx FOR SYSTEM_TIME AS OF TRANSACTION ftx();
 
 DROP TABLE tts;
 DROP TABLE ttx;
diff --git a/sql/handler.cc b/sql/handler.cc
index 7db7f95daa7..6bd7b5e194d 100644
--- a/sql/handler.cc
+++ b/sql/handler.cc
@@ -7419,11 +7419,7 @@ bool Table_scope_and_contents_source_st::vers_check_system_fields(
   if (!(alter_info->flags & ALTER_ADD_SYSTEM_VERSIONING))
     return false;
 
-  bool can_native= ha_check_storage_engine_flag(db_type,
-                                                HTON_NATIVE_SYS_VERSIONING)
-                   || db_type->db_type == DB_TYPE_PARTITION_DB;
-
-  return vers_info.check_sys_fields(table_name, db, alter_info, can_native);
+  return vers_info.check_sys_fields(table_name, db, alter_info);
 }
 
 
@@ -7527,7 +7523,16 @@ bool Vers_parse_info::fix_alter_info(THD *thd, Alter_info *alter_info,
     return false;
   }
 
-  return fix_implicit(thd, alter_info);
+  if (fix_implicit(thd, alter_info))
+    return true;
+
+  if (alter_info->flags & ALTER_ADD_SYSTEM_VERSIONING)
+  {
+    if (check_sys_fields(table_name, share->db, alter_info))
+      return true;
+  }
+
+  return false;
 }
 
 bool
@@ -7628,84 +7633,121 @@ bool Vers_parse_info::check_conditions(const Lex_table_name &table_name,
   return false;
 }
 
-static bool is_versioning_timestamp(const Create_field *f)
+static bool is_versioning_timestamp(const Column_definition *f)
 {
   return f->type_handler() == &type_handler_timestamp2 &&
          f->length == MAX_DATETIME_FULL_WIDTH;
 }
 
-static bool is_some_bigint(const Create_field *f)
+static bool is_some_bigint(const Column_definition *f)
 {
   return f->type_handler() == &type_handler_slonglong ||
          f->type_handler() == &type_handler_ulonglong ||
          f->type_handler() == &type_handler_vers_trx_id;
 }
 
-static bool is_versioning_bigint(const Create_field *f)
+static bool is_versioning_bigint(const Column_definition *f)
 {
   return is_some_bigint(f) && f->flags & UNSIGNED_FLAG &&
          f->length == MY_INT64_NUM_DECIMAL_DIGITS - 1;
 }
 
-static bool require_timestamp(const Create_field *f, Lex_table_name table_name)
+static void require_timestamp_error(const char *field, const char *table)
 {
-  my_error(ER_VERS_FIELD_WRONG_TYPE, MYF(0), f->field_name.str, "TIMESTAMP(6)",
-           table_name.str);
-  return true;
+  my_error(ER_VERS_FIELD_WRONG_TYPE, MYF(0), field, "TIMESTAMP(6)", table);
 }
-static bool require_bigint(const Create_field *f, Lex_table_name table_name)
+
+static void require_trx_id_error(const char *field, const char *table)
 {
-  my_error(ER_VERS_FIELD_WRONG_TYPE, MYF(0), f->field_name.str,
-           "BIGINT(20) UNSIGNED", table_name.str);
-  return true;
+  my_error(ER_VERS_FIELD_WRONG_TYPE, MYF(0), field, "BIGINT(20) UNSIGNED",
+           table);
+}
+
+
+bool Vers_type_timestamp::check_sys_fields(const LEX_CSTRING &table_name,
+                                           const Column_definition *row_start,
+                                           const Column_definition *row_end) const
+{
+  if (!is_versioning_timestamp(row_start))
+  {
+    require_timestamp_error(row_start->field_name.str, table_name.str);
+    return true;
+  }
+
+  if (row_end->type_handler()->vers() != this ||
+      !is_versioning_timestamp(row_end))
+  {
+    require_timestamp_error(row_end->field_name.str, table_name.str);
+    return true;
+  }
+
+  return false;
+}
+
+
+bool Vers_type_trx::check_sys_fields(const LEX_CSTRING &table_name,
+                                     const Column_definition *row_start,
+                                     const Column_definition *row_end) const
+{
+  if (!is_versioning_bigint(row_start))
+  {
+    require_trx_id_error(row_start->field_name.str, table_name.str);
+    return true;
+  }
+
+  if (row_end->type_handler()->vers() != this ||
+      !is_versioning_bigint(row_end))
+  {
+    require_trx_id_error(row_end->field_name.str, table_name.str);
+    return true;
+  }
+
+  if (!is_some_bigint(row_start))
+  {
+    require_timestamp_error(row_start->field_name.str, table_name.str);
+    return true;
+  }
+
+  if (!TR_table::use_transaction_registry)
+  {
+    my_error(ER_VERS_TRT_IS_DISABLED, MYF(0));
+    return true;
+  }
+
+  return false;
 }
 
 bool Vers_parse_info::check_sys_fields(const Lex_table_name &table_name,
                                        const Lex_table_name &db,
-                                       Alter_info *alter_info,
-                                       bool can_native) const
+                                       Alter_info *alter_info) const
 {
   if (check_conditions(table_name, db))
     return true;
 
+  List_iterator<Create_field> it(alter_info->create_list);
   const Create_field *row_start= NULL;
   const Create_field *row_end= NULL;
-
-  List_iterator<Create_field> it(alter_info->create_list);
-  while (Create_field *f= it++)
+  while (const Create_field *f= it++)
   {
-    if (!row_start && f->flags & VERS_SYS_START_FLAG)
+    if (f->flags & VERS_SYS_START_FLAG && !row_start)
       row_start= f;
-    else if (!row_end && f->flags & VERS_SYS_END_FLAG)
+    if (f->flags & VERS_SYS_END_FLAG && !row_end)
       row_end= f;
   }
 
-  const bool expect_timestamp=
-      !can_native || !is_some_bigint(row_start) || !is_some_bigint(row_end);
+  DBUG_ASSERT(row_start);
+  DBUG_ASSERT(row_end);
 
-  if (expect_timestamp)
-  {
-    if (!is_versioning_timestamp(row_start))
-      return require_timestamp(row_start, table_name);
+  const Vers_type_handler *row_start_vers= row_start->type_handler()->vers();
 
-    if (!is_versioning_timestamp(row_end))
-      return require_timestamp(row_end, table_name);
-  }
-  else
+  if (!row_start_vers)
   {
-    if (!is_versioning_bigint(row_start))
-      return require_bigint(row_start, table_name);
-
-    if (!is_versioning_bigint(row_end))
-      return require_bigint(row_end, table_name);
+    require_timestamp_error(row_start->field_name.str, table_name);
+    return true;
   }
 
-  if (is_versioning_bigint(row_start) && is_versioning_bigint(row_end) &&
-      !TR_table::use_transaction_registry)
-  {
-    my_error(ER_VERS_TRT_IS_DISABLED, MYF(0));
+  if (row_start_vers->check_sys_fields(table_name, row_start, row_end))
     return true;
-  }
 
   return false;
 }
diff --git a/sql/handler.h b/sql/handler.h
index 89af002b1dc..b20ecdd8d37 100644
--- a/sql/handler.h
+++ b/sql/handler.h
@@ -1962,13 +1962,6 @@ struct Schema_specification_st
 
 class Create_field;
 
-enum vers_sys_type_t
-{
-  VERS_UNDEFINED= 0,
-  VERS_TIMESTAMP,
-  VERS_TRX_ID
-};
-
 struct Table_period_info: Sql_alloc
 {
   Table_period_info() :
@@ -2051,8 +2044,7 @@ public:
   bool fix_create_like(Alter_info &alter_info, HA_CREATE_INFO &create_info,
                        TABLE_LIST &src_table, TABLE_LIST &table);
   bool check_sys_fields(const Lex_table_name &table_name,
-                        const Lex_table_name &db, Alter_info *alter_info,
-                        bool can_native) const;
+                        const Lex_table_name &db, Alter_info *alter_info) const;
 
   /**
      At least one field was specified 'WITH/WITHOUT SYSTEM VERSIONING'.
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index eb1d039683d..6cc87ddcbb5 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1023,7 +1023,7 @@ int SELECT_LEX::vers_setup_conds(THD *thd, TABLE_LIST *tables)
         storing vers_conditions as Item and make some magic related to
         vers_system_time_t/VERS_TRX_ID at stage of fix_fields()
         (this is large refactoring). */
-      if (vers_conditions.resolve_units(thd))
+      if (vers_conditions.check_units(thd))
         DBUG_RETURN(-1);
       if (timestamps_only && (vers_conditions.start.unit == VERS_TRX_ID ||
         vers_conditions.end.unit == VERS_TRX_ID))
diff --git a/sql/sql_type.cc b/sql/sql_type.cc
index 5f37851955e..2289b900460 100644
--- a/sql/sql_type.cc
+++ b/sql/sql_type.cc
@@ -83,6 +83,10 @@ Type_handler_blob_compressed type_handler_blob_compressed;
 
 Type_handler_interval_DDhhmmssff type_handler_interval_DDhhmmssff;
 
+Vers_type_timestamp       vers_type_timestamp;
+Vers_type_trx             vers_type_trx;
+
+
 
 class Type_collection_std: public Type_collection
 {
@@ -8200,67 +8204,6 @@ bool Type_handler::
 }
 
 
-/***************************************************************************/
-
-bool Type_handler::Vers_history_point_resolve_unit(THD *thd,
-                                                   Vers_history_point *point)
-                                                   const
-{
-  /*
-    Disallow using non-relevant data types in history points.
-    Even expressions with explicit TRANSACTION or TIMESTAMP units.
-  */
-  point->bad_expression_data_type_error(name().ptr());
-  return true;
-}
-
-
-bool Type_handler_typelib::
-       Vers_history_point_resolve_unit(THD *thd,
-                                       Vers_history_point *point) const
-{
-  /*
-    ENUM/SET have dual type properties (string and numeric).
-    Require explicit CAST to avoid ambiguity.
-  */
-  point->bad_expression_data_type_error(name().ptr());
-  return true;
-}
-
-
-bool Type_handler_general_purpose_int::
-       Vers_history_point_resolve_unit(THD *thd,
-                                       Vers_history_point *point) const
-{
-  return point->resolve_unit_trx_id(thd);
-}
-
-
-bool Type_handler_bit::
-       Vers_history_point_resolve_unit(THD *thd,
-                                       Vers_history_point *point) const
-{
-  return point->resolve_unit_trx_id(thd);
-}
-
-
-bool Type_handler_temporal_result::
-       Vers_history_point_resolve_unit(THD *thd,
-                                       Vers_history_point *point) const
-{
-  return point->resolve_unit_timestamp(thd);
-}
-
-
-bool Type_handler_general_purpose_string::
-       Vers_history_point_resolve_unit(THD *thd,
-                                       Vers_history_point *point) const
-{
-  return point->resolve_unit_timestamp(thd);
-}
-
-/***************************************************************************/
-
 bool Type_handler_null::Item_const_eq(const Item_const *a,
                                       const Item_const *b,
                                       bool binary_cmp) const
@@ -8342,13 +8285,6 @@ Type_handler_hex_hybrid::cast_to_int_type_handler() const
 }
 
 
-const Type_handler *
-Type_handler_hex_hybrid::type_handler_for_system_time() const
-{
-  return &type_handler_ulonglong;
-}
-
-
 /***************************************************************************/
 
 bool Type_handler_row::Item_eq_value(THD *thd, const Type_cmp_attributes *attr,
diff --git a/sql/sql_type.h b/sql/sql_type.h
index 459904f4ac0..70b1ab39da2 100644
--- a/sql/sql_type.h
+++ b/sql/sql_type.h
@@ -3238,6 +3238,59 @@ public:
 };
 
 
+enum vers_sys_type_t
+{
+  VERS_UNDEFINED= 0,
+  VERS_TIMESTAMP,
+  VERS_TRX_ID
+};
+
+
+class Vers_type_handler
+{
+protected:
+  Vers_type_handler() {}
+public:
+  virtual ~Vers_type_handler() {}
+  virtual vers_sys_type_t kind() const
+  {
+    DBUG_ASSERT(0);
+    return VERS_UNDEFINED;
+  }
+  virtual bool check_sys_fields(const LEX_CSTRING &table_name,
+                                const Column_definition *row_start,
+                                const Column_definition *row_end) const= 0;
+};
+
+
+class Vers_type_timestamp: public Vers_type_handler
+{
+public:
+  virtual vers_sys_type_t kind() const
+  {
+    return VERS_TIMESTAMP;
+  }
+  bool check_sys_fields(const LEX_CSTRING &table_name,
+                        const Column_definition *row_start,
+                        const Column_definition *row_end) const;
+};
+extern MYSQL_PLUGIN_IMPORT Vers_type_timestamp vers_type_timestamp;
+
+
+class Vers_type_trx: public Vers_type_handler
+{
+public:
+  virtual vers_sys_type_t kind() const
+  {
+    return VERS_TRX_ID;
+  }
+  bool check_sys_fields(const LEX_CSTRING &table_name,
+                        const Column_definition *row_start,
+                        const Column_definition *row_end) const;
+};
+extern MYSQL_PLUGIN_IMPORT Vers_type_trx vers_type_trx;
+
+
 class Type_handler
 {
 protected:
@@ -3419,10 +3472,6 @@ public:
   {
     return this;
   }
-  virtual const Type_handler *type_handler_for_system_time() const
-  {
-    return this;
-  }
   virtual const Type_handler *type_handler_unsigned() const
   {
     return this;
@@ -3908,8 +3957,7 @@ public:
   virtual bool
   Item_func_mod_fix_length_and_dec(Item_func_mod *func) const= 0;
 
-  virtual bool
-  Vers_history_point_resolve_unit(THD *thd, Vers_history_point *point) const;
+  virtual const Vers_type_handler *vers() const { return NULL; }
 };
 
 
@@ -4750,6 +4798,7 @@ public:
   bool Item_func_div_fix_length_and_dec(Item_func_div *) const;
   bool Item_func_mod_fix_length_and_dec(Item_func_mod *) const;
 
+  const Vers_type_handler *vers() const override { return &vers_type_trx; }
 };
 
 
@@ -4762,8 +4811,7 @@ public:
   {
     return type_limits_int()->char_length();
   }
-  bool Vers_history_point_resolve_unit(THD *thd, Vers_history_point *p)
-    const override;
+  const Vers_type_handler *vers() const override { return &vers_type_trx; }
 };
 
 
@@ -4836,8 +4884,7 @@ public:
   bool Item_func_mul_fix_length_and_dec(Item_func_mul *) const override;
   bool Item_func_div_fix_length_and_dec(Item_func_div *) const override;
   bool Item_func_mod_fix_length_and_dec(Item_func_mod *) const override;
-  bool Vers_history_point_resolve_unit(THD *thd, Vers_history_point *p)
-    const override;
+  const Vers_type_handler *vers() const override { return &vers_type_timestamp; }
 };
 
 
@@ -4990,6 +5037,7 @@ public:
   bool Item_func_mul_fix_length_and_dec(Item_func_mul *) const override;
   bool Item_func_div_fix_length_and_dec(Item_func_div *) const override;
   bool Item_func_mod_fix_length_and_dec(Item_func_mod *) const override;
+  const Vers_type_handler *vers() const override { return &vers_type_timestamp; }
 };
 
 
@@ -4997,8 +5045,6 @@ class Type_handler_general_purpose_string: public Type_handler_string_result
 {
 public:
   bool is_general_purpose_string_type() const override { return true; }
-  bool Vers_history_point_resolve_unit(THD *thd, Vers_history_point *p)
-    const override;
 };
 
 
@@ -5390,6 +5436,7 @@ public:
                                             MYSQL_TIME *to,
                                             date_mode_t fuzzydate)
                                             const override;
+  const Vers_type_handler *vers() const override { return NULL; }
 };
 
 
@@ -5447,8 +5494,6 @@ public:
                                    const Bit_addr &bit,
                                    const Column_definition_attributes *attr,
                                    uint32 flags) const override;
-  bool Vers_history_point_resolve_unit(THD *thd, Vers_history_point *p)
-                                       const override;
 };
 
 
@@ -6456,7 +6501,6 @@ public:
   virtual ~Type_handler_hex_hybrid() {}
   const Name name() const override;
   const Type_handler *cast_to_int_type_handler() const override;
-  const Type_handler *type_handler_for_system_time() const override;
 };
 
 
@@ -6545,6 +6589,7 @@ public:
                                    const Bit_addr &bit,
                                    const Column_definition_attributes *attr,
                                    uint32 flags) const override;
+  const Vers_type_handler *vers() const override { return &vers_type_timestamp; }
 };
 
 
@@ -6675,8 +6720,7 @@ public:
                                          const override;
   void Item_param_set_param_func(Item_param *param,
                                  uchar **pos, ulong len) const override;
-  bool Vers_history_point_resolve_unit(THD *thd, Vers_history_point *p)
-                                       const override;
+  const Vers_type_handler *vers() const override { return NULL; }
 };
 
 
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 5ba8e070246..bc3a3d8cd39 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -9585,7 +9585,7 @@ select_options:
 opt_history_unit:
           /* empty*/         %prec PREC_BELOW_IDENTIFIER_OPT_SPECIAL_CASE
           {
-            $$= VERS_UNDEFINED;
+            $$= VERS_TIMESTAMP;
           }
         | TRANSACTION_SYM
           {
diff --git a/sql/table.cc b/sql/table.cc
index cbf360b563b..c6d9de645ea 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -9540,12 +9540,12 @@ bool TR_table::check(bool error)
   return false;
 }
 
-bool vers_select_conds_t::resolve_units(THD *thd)
+bool vers_select_conds_t::check_units(THD *thd)
 {
   DBUG_ASSERT(type != SYSTEM_TIME_UNSPECIFIED);
   DBUG_ASSERT(start.item);
-  return start.resolve_unit(thd) ||
-         end.resolve_unit(thd);
+  return start.check_unit(thd) ||
+         end.check_unit(thd);
 }
 
 bool vers_select_conds_t::eq(const vers_select_conds_t &conds) const
@@ -9569,22 +9569,21 @@ bool vers_select_conds_t::eq(const vers_select_conds_t &conds) const
 }
 
 
-bool Vers_history_point::resolve_unit(THD *thd)
+bool Vers_history_point::check_unit(THD *thd)
 {
   if (!item)
     return false;
   if (item->fix_fields_if_needed(thd, &item))
     return true;
-  return item->this_item()->real_type_handler()->
-           type_handler_for_system_time()->
-           Vers_history_point_resolve_unit(thd, this);
-}
-
-
-void Vers_history_point::bad_expression_data_type_error(const char *type) const
-{
-  my_error(ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION, MYF(0),
-           type, "FOR SYSTEM_TIME");
+  const Type_handler *t= item->this_item()->real_type_handler();
+  DBUG_ASSERT(t);
+  if (!t->vers())
+  {
+    my_error(ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION, MYF(0),
+             t->name().ptr(), "FOR SYSTEM_TIME");
+    return true;
+  }
+  return false;
 }
 
 
diff --git a/sql/table.h b/sql/table.h
index 1a7e5fbd4dc..7a571d94424 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -32,6 +32,7 @@
 #include "filesort_utils.h"
 #include "parse_file.h"
 #include "sql_i_s.h"
+#include "sql_type.h"               /* vers_sys_type_t */
 
 /* Structs that defines the TABLE */
 
@@ -1841,21 +1842,9 @@ public:
     item= p.item;
     fix_item();
   }
-  void empty() { unit= VERS_UNDEFINED; item= NULL; }
+  void empty() { unit= VERS_TIMESTAMP; item= NULL; }
   void print(String *str, enum_query_type, const char *prefix, size_t plen) const;
-  bool resolve_unit(THD *thd);
-  bool resolve_unit_trx_id(THD *thd)
-  {
-    if (unit == VERS_UNDEFINED)
-      unit= VERS_TRX_ID;
-    return false;
-  }
-  bool resolve_unit_timestamp(THD *thd)
-  {
-    if (unit == VERS_UNDEFINED)
-      unit= VERS_TIMESTAMP;
-    return false;
-  }
+  bool check_unit(THD *thd);
   void bad_expression_data_type_error(const char *type) const;
   bool eq(const vers_history_point_t &point) const;
 };
@@ -1901,7 +1890,7 @@ struct vers_select_conds_t
   {
     return type != SYSTEM_TIME_UNSPECIFIED;
   }
-  bool resolve_units(THD *thd);
+  bool check_units(THD *thd);
   bool eq(const vers_select_conds_t &conds) const;
 };