Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-16100

FOR SYSTEM_TIME erroneously resolves string user variables as transaction IDs

    XMLWordPrintable

    Details

      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

            Activity

              People

              Assignee:
              bar Alexander Barkov
              Reporter:
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: