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

FOR SYSTEM_TIME BETWEEN and FROM .. TO work with negative intervals

Details

    Description

      Note: I'm not sure it's a bug, and even less sure it's important, it just looks odd and most likely will trigger reports from some meticulous users.

      MariaDB [test]> select *, row_start, row_end from t1 for system_time from '2018-04-23 02:00:00' to '2018-04-23 01:35:00';
      +------+----------------------------+----------------------------+
      | i    | row_start                  | row_end                    |
      +------+----------------------------+----------------------------+
      |    3 | 2018-04-23 01:30:14.208582 | 2038-01-19 05:14:07.999999 |
      +------+----------------------------+----------------------------+
      1 row in set (0.00 sec)
      

      Same for BETWEEN.

      Current documentation just says

      BETWEEN start AND end will show all rows that were visible at any point between two specified points in time. It works inclusively, a row visible exactly at start or exactly at end will be shown too.

      and about the same for FROM .. TO.

      So, technically it doesn't say that start should be earlier than end, although common sense suggests so, and also it's reasonable to expect semantics close to the old operator BETWEEN, which doesn't return anything for backward intervals.

      Attachments

        Activity

          The standard grammar is

          FOR SYSTEM_TIME BETWEEN [ ASYMMETRIC | SYMMETRIC ] <point in time 1> AND <point in time 2>
          

          If SYMMETRIC then points can be specified in any order, if ASYMMETRIC, then point1 must be less or equal than point2.

          If neither is specified, ASYMMETRIC is implied.

          So, you're right, the first point in time should be less or equal than the second one.

          serg Sergei Golubchik added a comment - The standard grammar is FOR SYSTEM_TIME BETWEEN [ ASYMMETRIC | SYMMETRIC ] <point in time 1> AND <point in time 2> If SYMMETRIC then points can be specified in any order, if ASYMMETRIC , then point1 must be less or equal than point2. If neither is specified, ASYMMETRIC is implied. So, you're right, the first point in time should be less or equal than the second one.

          Relevant part of standard is 7.6 <table reference> General Rules

          kevg Eugene Kosov (Inactive) added a comment - Relevant part of standard is 7.6 <table reference> General Rules
          kevg Eugene Kosov (Inactive) added a comment - https://github.com/MariaDB/server/pull/733/files

          People

            kevg Eugene Kosov (Inactive)
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.