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

FOR SYSTEM_TIME ALL query does not return result

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 10.6.19, 10.11.10, 11.4.4
    • N/A
    • Versioned Tables
    • None
    • Ubuntu 24.04. OpenJDK 17.

    Description

      Querying on Version table as some latency issue : same select that is expected to return 4 rows won't return any row on first execution. Waiting a few milliseconds, the expected result is eventually sent.

      to reproduced, just copy the following queries (all at once) on CLI.

      DROP TABLE IF EXISTS VersioningTable;
      CREATE OR REPLACE TABLE `VersioningTable` (
      `id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
      `index` BIGINT NOT NULL,
      `name` VARCHAR(255) NOT NULL,
      `startTS` TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
      `endTS` TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
      PERIOD FOR SYSTEM_TIME(startTS, endTS)
      ) WITH SYSTEM VERSIONING;
      INSERT INTO VersioningTable (`id`, `index`, `name`) VALUES (1, 1, 'Homer'), (2, 2, 'Bart');
      UPDATE VersioningTable SET `index` = 3, `name` = 'Marge' WHERE VersioningTable.id = 1;
      UPDATE VersioningTable SET `index` = 4, `name` = 'Lisa' WHERE VersioningTable.id = 2;
      SELECT id, name, startTS, endTS FROM VersioningTable FOR SYSTEM_TIME ALL WHERE startTS < current_timestamp;
      SELECT SLEEP(0.2);
      SELECT id, name, startTS, endTS FROM VersioningTable FOR SYSTEM_TIME ALL WHERE startTS < current_timestamp;
      
      

      ...
       
      MariaDB [testn]> SELECT id, name, startTS, endTS FROM VersioningTable FOR SYSTEM_TIME ALL WHERE startTS < current_timestamp;
      Empty set (0.000 sec)
       
      MariaDB [testn]> SELECT SLEEP(0.2);
      +------------+
      | SLEEP(0.2) |
      +------------+
      |          0 |
      +------------+
      1 row in set (0.202 sec)
       
      MariaDB [testn]> SELECT id, name, startTS, endTS FROM VersioningTable FOR SYSTEM_TIME ALL WHERE startTS < current_timestamp;
      +----+-------+----------------------------+----------------------------+
      | id | name  | startTS                    | endTS                      |
      +----+-------+----------------------------+----------------------------+
      |  1 | Homer | 2024-11-05 14:59:46.859236 | 2024-11-05 14:59:46.866052 |
      |  1 | Marge | 2024-11-05 14:59:46.866052 | 2038-01-19 04:14:07.999999 |
      |  2 | Bart  | 2024-11-05 14:59:46.859236 | 2024-11-05 14:59:46.871425 |
      |  2 | Lisa  | 2024-11-05 14:59:46.871425 | 2038-01-19 04:14:07.999999 |
      +----+-------+----------------------------+----------------------------+
      4 rows in set (0.000 sec)
      

      Attachments

        Activity

          To add an additional note, without the where clause, it works all the time.

          chungonn Cheong Chung Onn added a comment - To add an additional note, without the where clause, it works all the time.

          The problem exists in Mariadb 11.4.4 version. I have updated the affects version field.

          chungonn Cheong Chung Onn added a comment - The problem exists in Mariadb 11.4.4 version. I have updated the affects version field.

          This is expected. Try to put select current_timestamp before INSERT and after UPDATE in your sql. You'll see that when you copy everything at once current_timestamp doesn't change, it all happens within one second.

          And while your startTS has microsecond precision, current_timestamp has only second precision, on comparison startTS is truncated and you have all values identical, as everything has happened within the same second.

          If you want to compare values with microsecond precision, use current_timestamp(6)

          serg Sergei Golubchik added a comment - This is expected. Try to put select current_timestamp before INSERT and after UPDATE in your sql. You'll see that when you copy everything at once current_timestamp doesn't change, it all happens within one second. And while your startTS has microsecond precision, current_timestamp has only second precision, on comparison startTS is truncated and you have all values identical, as everything has happened within the same second. If you want to compare values with microsecond precision, use current_timestamp(6)

          People

            Unassigned Unassigned
            chungonn Cheong Chung Onn
            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.