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

          Ascending order - Click to sort in descending order
          chungonn Cheong Chung Onn created issue -
          diego dupin Diego Dupin made changes -
          Field Original Value New Value
          Summary FOR SYSTEM_TIME ALL query does not return result using connector/J FOR SYSTEM_TIME ALL query does not return result
          diego dupin Diego Dupin made changes -
          Description The queries below, q1 does not yield any result however q2 does return 2 rows using Connector/J.

          In Mysql CLI, q1 yields 4 rows and q2 yields 2 rows. The script for the table creation and related sql statements (in Scala 3) are below

          *q1 response from CLI*

          {quote}MariaDB [systemversioningdb]> SELECT id, name, startTS, endTS FROM VersioningTable FOR SYSTEM_TIME ALL WHERE startTS < current_timestamp;
          +----+-------+----------------------------+----------------------------+
          | id | name | startTS | endTS |
          +----+-------+----------------------------+----------------------------+
          | 1 | Homer | 2024-11-05 16:50:23.370891 | 2024-11-05 16:50:23.371126 |
          | 1 | Marge | 2024-11-05 16:50:23.371126 | 2038-01-19 11:14:07.999999 |
          | 2 | Bart | 2024-11-05 16:50:23.370891 | 2024-11-05 16:50:23.372008 |
          | 2 | Lisa | 2024-11-05 16:50:23.372008 | 2038-01-19 11:14:07.999999 |
          +----+-------+----------------------------+----------------------------+
          4 rows in set (0.000 sec)
          {quote}


          *q2 response from CLI*

          {quote}MariaDB [systemversioningdb]> SELECT id, name, startTS, endTS FROM VersioningTable FOR SYSTEM_TIME ALL WHERE endTS < current_timestamp;
          +----+-------+----------------------------+----------------------------+
          | id | name | startTS | endTS |
          +----+-------+----------------------------+----------------------------+
          | 1 | Homer | 2024-11-05 16:49:00.123469 | 2024-11-05 16:49:00.123702 |
          | 2 | Bart | 2024-11-05 16:49:00.123469 | 2024-11-05 16:49:00.124601 |
          +----+-------+----------------------------+----------------------------+
          2 rows in set (0.000 sec){quote}

          ---
          Table create statement -

          {quote}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;{quote}

          val s = connection.createStatement()
                s.executeUpdate("INSERT INTO VersioningTable (`id`, `index`, `name`) VALUES (1, 1, 'Homer'), (2, 2, 'Bart')")
                s.executeUpdate("UPDATE VersioningTable SET `index` = 3, `name` = 'Marge' WHERE VersioningTable.id = 1")
                s.executeUpdate("UPDATE VersioningTable SET `index` = 4, `name` = 'Lisa' WHERE VersioningTable.id = 2")
                val q1 = "SELECT id, name, startTS, endTS FROM VersioningTable FOR SYSTEM_TIME ALL WHERE startTS < current_timestamp"
                val q2 = "SELECT id, name, startTS, endTS FROM VersioningTable FOR SYSTEM_TIME AS OF CURRENT_TIMESTAMP"
                val rs = s.executeQuery(q1)
                while (rs.next()) {
                  val id = rs.getInt("id")
                  val name = rs.getString("name")
                  val startTS = rs.getTimestamp("startTS")
                  val endTS = rs.getTimestamp("endTS")

                  println(s"id: $id, name: $name, startTS: $startTS, endTS: $endTS")
                }

                // Clean up
                rs.close()
                s.close()





          {code:java}
          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;

          {code}
          results:
          diego dupin Diego Dupin made changes -
          Description

          {code:java}
          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;

          {code}
          results:
          		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.

          {code:java}
          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;

          {code}


          {code:java}
          MariaDB [testn]> DROP TABLE IF EXISTS VersioningTable;
          Query OK, 0 rows affected (0.020 sec)

          MariaDB [testn]> 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;
          Query OK, 0 rows affected (0.018 sec)

          MariaDB [testn]> INSERT INTO VersioningTable (`id`, `index`, `name`) VALUES (1, 1, 'Homer'), (2, 2, 'Bart');
          Query OK, 2 rows affected (0.005 sec)
          Records: 2 Duplicates: 0 Warnings: 0

          MariaDB [testn]> UPDATE VersioningTable SET `index` = 3, `name` = 'Marge' WHERE VersioningTable.id = 1;
          Query OK, 1 row affected (0.005 sec)
          Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0

          MariaDB [testn]> UPDATE VersioningTable SET `index` = 4, `name` = 'Lisa' WHERE VersioningTable.id = 2;
          Query OK, 1 row affected (0.010 sec)
          Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0

          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)
          {code}

          diego dupin Diego Dupin made changes -
          Key CONJ-1209 MDEV-35349
          Affects Version/s 10.6.19 [ 29833 ]
          Affects Version/s 3.5.0 [ 29943 ]
          Project MariaDB Connector/J [ 10301 ] MariaDB Server [ 10000 ]
          diego dupin Diego Dupin made changes -
          Assignee Diego Dupin [ diego dupin ]
          diego dupin Diego Dupin made changes -
          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.

          {code:java}
          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;

          {code}


          {code:java}
          MariaDB [testn]> DROP TABLE IF EXISTS VersioningTable;
          Query OK, 0 rows affected (0.020 sec)

          MariaDB [testn]> 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;
          Query OK, 0 rows affected (0.018 sec)

          MariaDB [testn]> INSERT INTO VersioningTable (`id`, `index`, `name`) VALUES (1, 1, 'Homer'), (2, 2, 'Bart');
          Query OK, 2 rows affected (0.005 sec)
          Records: 2 Duplicates: 0 Warnings: 0

          MariaDB [testn]> UPDATE VersioningTable SET `index` = 3, `name` = 'Marge' WHERE VersioningTable.id = 1;
          Query OK, 1 row affected (0.005 sec)
          Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0

          MariaDB [testn]> UPDATE VersioningTable SET `index` = 4, `name` = 'Lisa' WHERE VersioningTable.id = 2;
          Query OK, 1 row affected (0.010 sec)
          Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0

          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)
          {code}

          		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.

          {code:java}
          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;

          {code}


          {code:java}
          ...

          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)
          {code}

          diego dupin Diego Dupin made changes -
          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.

          {code:java}
          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;

          {code}


          {code:java}
          ...

          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)
          {code}

          		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.
          {code:java}
          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;

          {code}


          {code:java}
          ...

          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)
          {code}

          diego dupin Diego Dupin made changes -
          Affects Version/s 10.11.10 [ 29904 ]
          chungonn Cheong Chung Onn added a comment -

          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.
          chungonn Cheong Chung Onn made changes -
          Affects Version/s 11.4.4 [ 29907 ]
          chungonn Cheong Chung Onn added a comment -

          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.
          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)

          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)
          serg Sergei Golubchik made changes -
          Component/s Versioned Tables [ 14303 ]
          Fix Version/s N/A [ 14700 ]
          Resolution Not a Bug [ 6 ]
          Status Open [ 1 ] Closed [ 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.