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

Default TIMESTAMP clause for SELECT from versioned

Details

    Description

      Currently when there is no unit specifier in AS OF query like:

      select * from t1 for system_time as of @p1;

      it will do some heuristics to detect whether it should treat @p1 is trx_id or timestamp.

      Such semantic complexity should be removed in favour of default TIMESTAMP because:

      1. rare practical use and doubtful advantage;
      2. it conflicts with MDEV-16226.

      Attachments

        Issue Links

          blocks

          Task - A task that needs to be done. MDEV-16226 TRX_ID-based System Versioning refactoring

          • Major - Major loss of function.
          • Stalled
          is blocked by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15408 Confusing error message upon ER_VERS_FIELD_WRONG_TYPE while omitting UNSIGNED in BIGINT

          • Major - Major loss of function.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-16094 Crash when using AS OF with a stored function

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-16100 FOR SYSTEM_TIME erroneously resolves string user variables as transaction IDs

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            View 5 older comments
            bar Alexander Barkov added a comment - - edited

            Changes from MDEV-16100 that added new methods in Type_handler are needed anyway, to disallow various non-relevant data types in FOR SYSTEM_TIME, such as GEOMETRY, ROW, ENUM/SET. And we'll be adding new data types soon (e.g. INET4, INET6, UUID), which are also non-relevant in timestamp context.

            I think it's fine to make undefined units to resolve to timestamp by default.
            But in this case the following expressions in FOR SYSTEM_TIME should probably return an error when used without explicit unit:

            • Expressions that have the BIT data type (i.e. table fields, SP variables, hybrid functions like COALESCE)
            • BIT-alike constants B'01011101'
            • HEX hybrid constants 0x616263
              because the user is obviously doing something wrong. These expressions can very rarely represent a valid TIMESTAMP.

            So they should require either an explicit unit, or even an explicit CAST.

            bar Alexander Barkov added a comment - - edited Changes from MDEV-16100 that added new methods in Type_handler are needed anyway, to disallow various non-relevant data types in FOR SYSTEM_TIME, such as GEOMETRY, ROW, ENUM/SET. And we'll be adding new data types soon (e.g. INET4, INET6, UUID), which are also non-relevant in timestamp context. I think it's fine to make undefined units to resolve to timestamp by default. But in this case the following expressions in FOR SYSTEM_TIME should probably return an error when used without explicit unit: Expressions that have the BIT data type (i.e. table fields, SP variables, hybrid functions like COALESCE) BIT-alike constants B'01011101' HEX hybrid constants 0x616263 because the user is obviously doing something wrong. These expressions can very rarely represent a valid TIMESTAMP. So they should require either an explicit unit, or even an explicit CAST.
            midenok Aleksey Midenkov added a comment -

            bar Okay, I see your point. It seems, that it is not possible to simplify anything if type checking is required in SYSTEM_TIME. Unit resolution comes as a "free bonus". Therefore, closing.

            midenok Aleksey Midenkov added a comment - bar Okay, I see your point. It seems, that it is not possible to simplify anything if type checking is required in SYSTEM_TIME. Unit resolution comes as a "free bonus". Therefore, closing.
            midenok Aleksey Midenkov added a comment -

            This is the requirement for MDEV-16226.

            midenok Aleksey Midenkov added a comment - This is the requirement for MDEV-16226 .
            bar Alexander Barkov added a comment - - edited

            Discussed with Aleksey on slack to make the patch more Type_handler friendly.

            bar Alexander Barkov added a comment - - edited Discussed with Aleksey on slack to make the patch more Type_handler friendly.
            bar Alexander Barkov added a comment -

            The latest version in https://github.com/MariaDB/server/pull/860 is now Type_handler friendly. Ok to push from my side.

            bar Alexander Barkov added a comment - The latest version in https://github.com/MariaDB/server/pull/860 is now Type_handler friendly. Ok to push from my side.

            People

              midenok Aleksey Midenkov
              midenok Aleksey Midenkov
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.