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

Selecting from SYSTEM VERSIONING with BETWEEN not working?

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Not a Bug
    • 11.3.2
    • N/A
    • Versioned Tables
    • None

    Description

      Forgive me if I'm just misreading the results, but I'm not sure why I'm seeing the results I'm getting given the criteria for the system version (temporal table) query.

      select registration_date,order_number,id,row_start,row_end from registration FOR SYSTEM_TIME BETWEEN '2024-04-04 19:10' and NOW() limit 5;
      		 
       
      		 
      +---------------------+--------------+----+----------------------------+----------------------------+
      		 
      | registration_date   | order_number | id | row_start                  | row_end                    |
      		 
      +---------------------+--------------+----+----------------------------+----------------------------+
      		 
      | 2013-02-15 14:54:50 | NULL         |  1 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
      		 
      | 2013-02-15 14:54:49 | NULL         |  2 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
      		 
      | 2013-02-15 14:54:49 | NULL         |  3 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
      		 
      | 2013-02-15 14:54:49 | NULL         |  4 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
      		 
      | 2013-02-15 14:58:10 | NULL         |  5 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
      		 
      +---------------------+--------------+----+----------------------------+----------------------------+

      Looking at the `row_start` I would expect records only AFTER `2024-04-04 19:10` UP TO `NOW()`

      But I'm seeing records BEFORE the start date.

      I am running that query straight from the `mariadb` console inside the docker image.

      I am using `MariaDB 11.3.2-MariaDB-1.11.3.2+maria~ubu2204` which is the current latest `MariaDB` docker image.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          tmulle Tim created issue -
          tmulle Tim made changes -
          Field Original Value New Value
          Description Forgive me if I'm just misreading the results, but I'm not sure why I'm seeing the results I'm getting given the criteria for the system version (temporal table) query.

          ```sql
          select registration_date,order_number,id,row_start,row_end from registration FOR SYSTEM_TIME BETWEEN '2024-04-04 19:10' and NOW() limit 5;
          +---------------------+--------------+----+----------------------------+----------------------------+
          | registration_date | order_number | id | row_start | row_end |
          +---------------------+--------------+----+----------------------------+----------------------------+
          | 2013-02-15 14:54:50 | NULL | 1 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          | 2013-02-15 14:54:49 | NULL | 2 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          | 2013-02-15 14:54:49 | NULL | 3 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          | 2013-02-15 14:54:49 | NULL | 4 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          | 2013-02-15 14:58:10 | NULL | 5 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          +---------------------+--------------+----+----------------------------+----------------------------+
          ```

          Looking at the `row_start` I would expect records only AFTER `2024-04-04 19:10` UP TO `NOW()`

          But I'm seeing records BEFORE the start date.

          I am running that query straight from the `mariadb` console inside the docker image.

          I am using `MariaDB 11.3.2-MariaDB-1.11.3.2+maria~ubu2204` which is the current latest `MariaDB` docker image.
          		Forgive me if I'm just misreading the results, but I'm not sure why I'm seeing the results I'm getting given the criteria for the system version (temporal table) query.

          {code}sql
          select registration_date,order_number,id,row_start,row_end from registration FOR SYSTEM_TIME BETWEEN '2024-04-04 19:10' and NOW() limit 5;
          +---------------------+--------------+----+----------------------------+----------------------------+
          | registration_date | order_number | id | row_start | row_end |
          +---------------------+--------------+----+----------------------------+----------------------------+
          | 2013-02-15 14:54:50 | NULL | 1 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          | 2013-02-15 14:54:49 | NULL | 2 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          | 2013-02-15 14:54:49 | NULL | 3 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          | 2013-02-15 14:54:49 | NULL | 4 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          | 2013-02-15 14:58:10 | NULL | 5 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          +---------------------+--------------+----+----------------------------+----------------------------+
          {code}

          Looking at the `row_start` I would expect records only AFTER `2024-04-04 19:10` UP TO `NOW()`

          But I'm seeing records BEFORE the start date.

          I am running that query straight from the `mariadb` console inside the docker image.

          I am using `MariaDB 11.3.2-MariaDB-1.11.3.2+maria~ubu2204` which is the current latest `MariaDB` docker image.
          tmulle Tim made changes -
          Description Forgive me if I'm just misreading the results, but I'm not sure why I'm seeing the results I'm getting given the criteria for the system version (temporal table) query.

          {code}sql
          select registration_date,order_number,id,row_start,row_end from registration FOR SYSTEM_TIME BETWEEN '2024-04-04 19:10' and NOW() limit 5;
          +---------------------+--------------+----+----------------------------+----------------------------+
          | registration_date | order_number | id | row_start | row_end |
          +---------------------+--------------+----+----------------------------+----------------------------+
          | 2013-02-15 14:54:50 | NULL | 1 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          | 2013-02-15 14:54:49 | NULL | 2 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          | 2013-02-15 14:54:49 | NULL | 3 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          | 2013-02-15 14:54:49 | NULL | 4 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          | 2013-02-15 14:58:10 | NULL | 5 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          +---------------------+--------------+----+----------------------------+----------------------------+
          {code}

          Looking at the `row_start` I would expect records only AFTER `2024-04-04 19:10` UP TO `NOW()`

          But I'm seeing records BEFORE the start date.

          I am running that query straight from the `mariadb` console inside the docker image.

          I am using `MariaDB 11.3.2-MariaDB-1.11.3.2+maria~ubu2204` which is the current latest `MariaDB` docker image.
          		Forgive me if I'm just misreading the results, but I'm not sure why I'm seeing the results I'm getting given the criteria for the system version (temporal table) query.

          {code:sql}
          select registration_date,order_number,id,row_start,row_end from registration FOR SYSTEM_TIME BETWEEN '2024-04-04 19:10' and NOW() limit 5;
          +---------------------+--------------+----+----------------------------+----------------------------+
          | registration_date | order_number | id | row_start | row_end |
          +---------------------+--------------+----+----------------------------+----------------------------+
          | 2013-02-15 14:54:50 | NULL | 1 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          | 2013-02-15 14:54:49 | NULL | 2 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          | 2013-02-15 14:54:49 | NULL | 3 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          | 2013-02-15 14:54:49 | NULL | 4 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          | 2013-02-15 14:58:10 | NULL | 5 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          +---------------------+--------------+----+----------------------------+----------------------------+
          {code}

          Looking at the `row_start` I would expect records only AFTER `2024-04-04 19:10` UP TO `NOW()`

          But I'm seeing records BEFORE the start date.

          I am running that query straight from the `mariadb` console inside the docker image.

          I am using `MariaDB 11.3.2-MariaDB-1.11.3.2+maria~ubu2204` which is the current latest `MariaDB` docker image.
          tmulle Tim made changes -
          Description Forgive me if I'm just misreading the results, but I'm not sure why I'm seeing the results I'm getting given the criteria for the system version (temporal table) query.

          {code:sql}
          select registration_date,order_number,id,row_start,row_end from registration FOR SYSTEM_TIME BETWEEN '2024-04-04 19:10' and NOW() limit 5;
          +---------------------+--------------+----+----------------------------+----------------------------+
          | registration_date | order_number | id | row_start | row_end |
          +---------------------+--------------+----+----------------------------+----------------------------+
          | 2013-02-15 14:54:50 | NULL | 1 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          | 2013-02-15 14:54:49 | NULL | 2 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          | 2013-02-15 14:54:49 | NULL | 3 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          | 2013-02-15 14:54:49 | NULL | 4 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          | 2013-02-15 14:58:10 | NULL | 5 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          +---------------------+--------------+----+----------------------------+----------------------------+
          {code}

          Looking at the `row_start` I would expect records only AFTER `2024-04-04 19:10` UP TO `NOW()`

          But I'm seeing records BEFORE the start date.

          I am running that query straight from the `mariadb` console inside the docker image.

          I am using `MariaDB 11.3.2-MariaDB-1.11.3.2+maria~ubu2204` which is the current latest `MariaDB` docker image.
          		Forgive me if I'm just misreading the results, but I'm not sure why I'm seeing the results I'm getting given the criteria for the system version (temporal table) query.

          {code:sql}
          select registration_date,order_number,id,row_start,row_end from registration FOR SYSTEM_TIME BETWEEN '2024-04-04 19:10' and NOW() limit 5;
          {code}
          +---------------------+--------------+----+----------------------------+----------------------------+
          | registration_date | order_number | id | row_start | row_end |
          +---------------------+--------------+----+----------------------------+----------------------------+
          | 2013-02-15 14:54:50 | NULL | 1 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          | 2013-02-15 14:54:49 | NULL | 2 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          | 2013-02-15 14:54:49 | NULL | 3 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          | 2013-02-15 14:54:49 | NULL | 4 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          | 2013-02-15 14:58:10 | NULL | 5 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          +---------------------+--------------+----+----------------------------+----------------------------+

          Looking at the `row_start` I would expect records only AFTER `2024-04-04 19:10` UP TO `NOW()`

          But I'm seeing records BEFORE the start date.

          I am running that query straight from the `mariadb` console inside the docker image.

          I am using `MariaDB 11.3.2-MariaDB-1.11.3.2+maria~ubu2204` which is the current latest `MariaDB` docker image.
          tmulle Tim made changes -
          Description Forgive me if I'm just misreading the results, but I'm not sure why I'm seeing the results I'm getting given the criteria for the system version (temporal table) query.

          {code:sql}
          select registration_date,order_number,id,row_start,row_end from registration FOR SYSTEM_TIME BETWEEN '2024-04-04 19:10' and NOW() limit 5;
          {code}
          +---------------------+--------------+----+----------------------------+----------------------------+
          | registration_date | order_number | id | row_start | row_end |
          +---------------------+--------------+----+----------------------------+----------------------------+
          | 2013-02-15 14:54:50 | NULL | 1 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          | 2013-02-15 14:54:49 | NULL | 2 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          | 2013-02-15 14:54:49 | NULL | 3 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          | 2013-02-15 14:54:49 | NULL | 4 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          | 2013-02-15 14:58:10 | NULL | 5 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          +---------------------+--------------+----+----------------------------+----------------------------+

          Looking at the `row_start` I would expect records only AFTER `2024-04-04 19:10` UP TO `NOW()`

          But I'm seeing records BEFORE the start date.

          I am running that query straight from the `mariadb` console inside the docker image.

          I am using `MariaDB 11.3.2-MariaDB-1.11.3.2+maria~ubu2204` which is the current latest `MariaDB` docker image.
          		Forgive me if I'm just misreading the results, but I'm not sure why I'm seeing the results I'm getting given the criteria for the system version (temporal table) query.

          {code:sql}
          select registration_date,order_number,id,row_start,row_end from registration FOR SYSTEM_TIME BETWEEN '2024-04-04 19:10' and NOW() limit 5;

          +---------------------+--------------+----+----------------------------+----------------------------+
          | registration_date | order_number | id | row_start | row_end |
          +---------------------+--------------+----+----------------------------+----------------------------+
          | 2013-02-15 14:54:50 | NULL | 1 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          | 2013-02-15 14:54:49 | NULL | 2 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          | 2013-02-15 14:54:49 | NULL | 3 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          | 2013-02-15 14:54:49 | NULL | 4 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          | 2013-02-15 14:58:10 | NULL | 5 | 2024-04-04 19:03:18.695546 | 2038-01-19 03:14:07.999999 |
          +---------------------+--------------+----+----------------------------+----------------------------+
          {code}
          Looking at the `row_start` I would expect records only AFTER `2024-04-04 19:10` UP TO `NOW()`

          But I'm seeing records BEFORE the start date.

          I am running that query straight from the `mariadb` console inside the docker image.

          I am using `MariaDB 11.3.2-MariaDB-1.11.3.2+maria~ubu2204` which is the current latest `MariaDB` docker image.
          tmulle Tim added a comment - - edited

          After asking on stackoverflow I have gotten an explanation of why I'm seeing the results I'm getting.

          The BETWEEN and FROM/TO operators act different than a standard RANGE query (ie. row_start >= :startdate and row_end <= :enddate) I was used to using.

          You can close this issue

          tmulle Tim added a comment - - edited After asking on stackoverflow I have gotten an explanation of why I'm seeing the results I'm getting. The BETWEEN and FROM/TO operators act different than a standard RANGE query (ie. row_start >= :startdate and row_end <= :enddate) I was used to using. You can close this issue
          alice Alice Sherepa added a comment - - edited

          yes, correct. "BETWEEN start AND end will show all rows that were visible at any point between two specified points in time." (https://mariadb.com/kb/en/system-versioned-tables/)
          So at the moment `2024-04-04 19:10`' - this table had 5 records, they were added at 19:03

          alice Alice Sherepa added a comment - - edited yes, correct. "BETWEEN start AND end will show all rows that were visible at any point between two specified points in time." ( https://mariadb.com/kb/en/system-versioned-tables/ ) So at the moment `2024-04-04 19:10`' - this table had 5 records, they were added at 19:03
          alice Alice Sherepa made changes -
          Fix Version/s N/A [ 14700 ]
          Resolution Not a Bug [ 6 ]
          Status Open [ 1 ] Closed [ 6 ]

          People

            Unassigned Unassigned
            tmulle Tim
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.