Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3(EOL)
-
None
Description
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.
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
MDEV-16144 Default TIMESTAMP clause for SELECT from versioned
-
- Closed
-