|
I create this versioned table:
CREATE OR REPLACE TABLE t1 (
|
x INT,
|
sys_trx_start BIGINT UNSIGNED AS ROW START,
|
sys_trx_end BIGINT UNSIGNED AS ROW END,
|
PERIOD FOR SYSTEM_TIME (sys_trx_start, sys_trx_end)
|
) WITH SYSTEM VERSIONING ENGINE=INNODB;
|
INSERT INTO t1 (x) VALUES (1);
|
Then I set a user variable to some timestamp in the future:
SET @ts= DATE_ADD(NOW(), INTERVAL 1 YEAR);
|
Then I do EXPLAIN for two queries, using @ts in combination with explicit TRANSACTION and TIMESTAMP history units:
EXPLAIN EXTENDED SELECT x FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION @ts;
|
SHOW WARNINGS;
|
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`x` AS `x` from `test`.`t1` FOR SYSTEM_TIME ALL where vtq_trx_sees(`test`.`t1`.`sys_trx_end`,@`ts`) and vtq_trx_sees_eq(@`ts`,`test`.`t1`.`sys_trx_start`) |
|
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
EXPLAIN EXTENDED SELECT x FROM t1 FOR SYSTEM_TIME AS OF TIMESTAMP @ts;
|
SHOW WARNINGS;
|
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`x` AS `x` from `test`.`t1` FOR SYSTEM_TIME ALL where vtq_trx_sees(`test`.`t1`.`sys_trx_end`,<cache>(vtq_trx_id(@`ts`))) and vtq_trx_sees_eq(<cache>(vtq_trx_id(@`ts`)),`test`.`t1`.`sys_trx_start`) |
|
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
Notice, the query with the TRANSACTION unit does not use vtq_trx_id(@ts), while the one with TIMESTAMP does.
Looks good so far.
Now I omit the history point unit and let it be detected automatically:
EXPLAIN EXTENDED SELECT x FROM t1 FOR SYSTEM_TIME AS OF @ts;
|
SHOW WARNINGS;
|
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`x` AS `x` from `test`.`t1` FOR SYSTEM_TIME ALL where vtq_trx_sees(`test`.`t1`.`sys_trx_end`,@`ts`) and vtq_trx_sees_eq(@`ts`,`test`.`t1`.`sys_trx_start`) |
|
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
Notice, it does not have vtq_trx_id(@ts) in the output. The unit was detected as TRANSACTION. Looks wrong. The variable is set to a string, not to a number. The expected result would be to treat such variable as a TIMESTAMP unit.
The problem happens because Vers_history_point::resolve_unit() tests item->result_type() before item->>fix_fields() was called, so result_type() returns REAL_RESULT by default.
|