[MDEV-26994] Transaction-precise tables ignore most recent row versions when queried with "FOR SYSTEM_TIME FROM ts TO ts" - Jira

Details

Type: Bug
Status: Stalled (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: None
Fix Version/s: 10.4(EOL)
Component/s: Versioned Tables
Labels:
- trx-versioning

Description

Transaction-precise tables seem to ignore the most recent row versions when queried with FOR SYSTEM_TIME FROM ts TO ts.

Let's say that we start with the following DDL and DML:

CREATE TABLE accounts (

    id SERIAL PRIMARY KEY,

    name VARCHAR(255),

    amount INT,

    start_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW START INVISIBLE,

    end_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW END INVISIBLE,

    PERIOD FOR SYSTEM_TIME(start_trxid, end_trxid)

) WITH SYSTEM VERSIONING;

INSERT INTO accounts (name, amount)

VALUES ("Smith", 400),

       ("Orson", 300),

       ("Serio", 500),

       ("Wallace", 200),

       ("March", 600),

       ("Douglas", 100);

UPDATE accounts

SET amount = 1000

WHERE id = 1

   OR id = 3;

This results in the following current and historical row versions:

MariaDB [test]> SELECT *, start_trxid, end_trxid

    -> FROM accounts

    -> FOR SYSTEM_TIME ALL;

+----+---------+--------+-------------+----------------------+

| id | name    | amount | start_trxid | end_trxid            |

+----+---------+--------+-------------+----------------------+

|  1 | Smith   |    400 |         102 |                  107 |

|  1 | Smith   |   1000 |         107 | 18446744073709551615 |

|  2 | Orson   |    300 |         102 | 18446744073709551615 |

|  3 | Serio   |    500 |         102 |                  107 |

|  3 | Serio   |   1000 |         107 | 18446744073709551615 |

|  4 | Wallace |    200 |         102 | 18446744073709551615 |

|  5 | March   |    600 |         102 | 18446744073709551615 |

|  6 | Douglas |    100 |         102 | 18446744073709551615 |

+----+---------+--------+-------------+----------------------+

And the following transaction metadata:

MariaDB [test]> SELECT * FROM mysql.transaction_registry;

+----------------+-----------+----------------------------+----------------------------+-----------------+

| transaction_id | commit_id | begin_timestamp            | commit_timestamp           | isolation_level |

+----------------+-----------+----------------------------+----------------------------+-----------------+

|            102 |       103 | 2021-10-27 20:12:56.367871 | 2021-10-27 20:12:56.368546 | REPEATABLE-READ |

|            107 |       108 | 2021-10-27 20:13:02.423844 | 2021-10-27 20:13:02.431790 | REPEATABLE-READ |

+----------------+-----------+----------------------------+----------------------------+-----------------+

2 rows in set (0.000 sec)

Given the timestamps of these transactions, I would expect the following queries to return all current and historical row versions:

SELECT *

FROM accounts

FOR SYSTEM_TIME

   BETWEEN '2021-10-27 20:10'

   AND '2038-01-19 03:14:07.999999';

SELECT *

FROM accounts

FOR SYSTEM_TIME

   FROM '2021-10-27 20:10'

   TO '2038-01-19 03:14:07.999999';

The query that uses the BETWEEN .. AND .. clause seems to work properly:

MariaDB [test]> SELECT *

    -> FROM accounts

    -> FOR SYSTEM_TIME

    ->    BETWEEN '2021-10-27 20:10'

    ->    AND '2038-01-19 03:14:07.999999';

+----+---------+--------+

| id | name    | amount |

+----+---------+--------+

|  1 | Smith   |    400 |

|  1 | Smith   |   1000 |

|  2 | Orson   |    300 |

|  3 | Serio   |    500 |

|  3 | Serio   |   1000 |

|  4 | Wallace |    200 |

|  5 | March   |    600 |

|  6 | Douglas |    100 |

+----+---------+--------+

8 rows in set (0.001 sec)

But the query that uses the FROM .. TO .. clause seems to ignore the most recent row versions if there are historical row versions:

MariaDB [test]> SELECT *

    -> FROM accounts

    -> FOR SYSTEM_TIME

    ->    FROM '2021-10-27 20:10'

    ->    TO '2038-01-19 03:14:07.999999';

+----+---------+--------+

| id | name    | amount |

+----+---------+--------+

|  1 | Smith   |    400 |

|  2 | Orson   |    300 |

|  3 | Serio   |    500 |

|  4 | Wallace |    200 |

|  5 | March   |    600 |

|  6 | Douglas |    100 |

+----+---------+--------+

6 rows in set (0.001 sec)

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

Screenshot from 2021-10-29 15-59-42.png
394 kB
2021-10-29 13:00

Issue Links

is caused by

MDEV-12894 System-versioned tables

Closed

Activity

Ascending order - Click to sort in descending order

Geoff Montee (Inactive) added a comment - 2021-11-01 16:38

I believe this issue can be simplified to the following test case:

CREATE TABLE accounts_svt (

       id SERIAL PRIMARY KEY,

       name VARCHAR(255),

       amount INT

) WITH SYSTEM VERSIONING;

CREATE TABLE accounts_tp (

    id SERIAL PRIMARY KEY,

    name VARCHAR(255),

    amount INT,

    start_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW START INVISIBLE,

    end_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW END INVISIBLE,

    PERIOD FOR SYSTEM_TIME(start_trxid, end_trxid)

) WITH SYSTEM VERSIONING;

BEGIN;

INSERT INTO accounts_svt (name, amount)

VALUES ("Smith", 400),

       ("Orson", 300),

       ("Serio", 500),

       ("Wallace", 200),

       ("March", 600),

       ("Douglas", 100);

INSERT INTO accounts_tp (name, amount)

VALUES ("Smith", 400),

       ("Orson", 300),

       ("Serio", 500),

       ("Wallace", 200),

       ("March", 600),

       ("Douglas", 100);

COMMIT;

BEGIN;

UPDATE accounts_svt

SET amount = 1000

WHERE id = 1

   OR id = 3;

UPDATE accounts_tp

SET amount = 1000

WHERE id = 1

   OR id = 3;

COMMIT;

SELECT * FROM mysql.transaction_registry;

SELECT *

FROM accounts_svt

FOR SYSTEM_TIME

   FROM '2021-10-27 20:10'

   TO '2038-01-19 03:14:07.999999';

SELECT *

FROM accounts_tp

FOR SYSTEM_TIME

   FROM '2021-10-27 20:10'

   TO '2038-01-19 03:14:07.999999';

The final queries using FROM .. TO .. return different results for system-versioned tables and transaction-precise tables:

MariaDB [test]> SELECT *

    -> FROM accounts_svt

    -> FOR SYSTEM_TIME

    ->    FROM '2021-10-27 20:10'

    ->    TO '2038-01-19 03:14:07.999999';

+----+---------+--------+

| id | name    | amount |

+----+---------+--------+

|  1 | Smith   |    400 |

|  1 | Smith   |   1000 |

|  2 | Orson   |    300 |

|  3 | Serio   |    500 |

|  3 | Serio   |   1000 |

|  4 | Wallace |    200 |

|  5 | March   |    600 |

|  6 | Douglas |    100 |

+----+---------+--------+

8 rows in set (0.000 sec)

MariaDB [test]> SELECT *

    -> FROM accounts_tp

    -> FOR SYSTEM_TIME

    ->    FROM '2021-10-27 20:10'

    ->    TO '2038-01-19 03:14:07.999999';

+----+---------+--------+

| id | name    | amount |

+----+---------+--------+

|  1 | Smith   |    400 |

|  2 | Orson   |    300 |

|  3 | Serio   |    500 |

|  4 | Wallace |    200 |

|  5 | March   |    600 |

|  6 | Douglas |    100 |

+----+---------+--------+

6 rows in set (0.001 sec)

Geoff Montee (Inactive) added a comment - 2021-11-01 16:38 I believe this issue can be simplified to the following test case: CREATE TABLE accounts_svt ( id SERIAL PRIMARY KEY , name VARCHAR (255), amount INT ) WITH SYSTEM VERSIONING; CREATE TABLE accounts_tp ( id SERIAL PRIMARY KEY , name VARCHAR (255), amount INT , start_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW START INVISIBLE, end_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW END INVISIBLE, PERIOD FOR SYSTEM_TIME(start_trxid, end_trxid) ) WITH SYSTEM VERSIONING; BEGIN ; INSERT INTO accounts_svt ( name , amount) VALUES ( "Smith" , 400), ( "Orson" , 300), ( "Serio" , 500), ( "Wallace" , 200), ( "March" , 600), ( "Douglas" , 100); INSERT INTO accounts_tp ( name , amount) VALUES ( "Smith" , 400), ( "Orson" , 300), ( "Serio" , 500), ( "Wallace" , 200), ( "March" , 600), ( "Douglas" , 100); COMMIT ; BEGIN ; UPDATE accounts_svt SET amount = 1000 WHERE id = 1 OR id = 3; UPDATE accounts_tp SET amount = 1000 WHERE id = 1 OR id = 3; COMMIT ; SELECT * FROM mysql.transaction_registry; SELECT * FROM accounts_svt FOR SYSTEM_TIME FROM '2021-10-27 20:10' TO '2038-01-19 03:14:07.999999' ; SELECT * FROM accounts_tp FOR SYSTEM_TIME FROM '2021-10-27 20:10' TO '2038-01-19 03:14:07.999999' ; The final queries using FROM .. TO .. return different results for system-versioned tables and transaction-precise tables: MariaDB [test]> SELECT * -> FROM accounts_svt -> FOR SYSTEM_TIME -> FROM '2021-10-27 20:10' -> TO '2038-01-19 03:14:07.999999' ; + ----+---------+--------+ | id | name | amount | + ----+---------+--------+ | 1 | Smith | 400 | | 1 | Smith | 1000 | | 2 | Orson | 300 | | 3 | Serio | 500 | | 3 | Serio | 1000 | | 4 | Wallace | 200 | | 5 | March | 600 | | 6 | Douglas | 100 | + ----+---------+--------+ 8 rows in set (0.000 sec) MariaDB [test]> SELECT * -> FROM accounts_tp -> FOR SYSTEM_TIME -> FROM '2021-10-27 20:10' -> TO '2038-01-19 03:14:07.999999' ; + ----+---------+--------+ | id | name | amount | + ----+---------+--------+ | 1 | Smith | 400 | | 2 | Orson | 300 | | 3 | Serio | 500 | | 4 | Wallace | 200 | | 5 | March | 600 | | 6 | Douglas | 100 | + ----+---------+--------+ 6 rows in set (0.001 sec)

Nikita Malyavin added a comment - 2021-11-01 21:21 - edited

Yes, it seems that transactional and timestamp-based versioning produce different results.

I have created the test to show the result differences

source suite/versioning/engines.inc;

source suite/versioning/common.inc;

SELECT TIMESTAMP '9999-12-31 23:59:59';

CREATE TABLE t(

  x INT,

  start_timestamp TIMESTAMP(6) NOT NULL,

  end_timestamp TIMESTAMP(6) NOT NULL DEFAULT now()

);

insert into t values (1, TIMESTAMP '2021-10-27 20:10', TIMESTAMP '2038-01-19 03:14:07.999999');

select * from t;

select * from t WHERE start_timestamp >= TIMESTAMP '0001-01-01 00:00:00' and end_timestamp < TIMESTAMP '9999-12-31 23:59:59';

drop table t;

replace_result $sys_datatype_expl SYS_DATATYPE;

eval create or replace table t(

  id SERIAL PRIMARY KEY,

  x INT,

  start_timestamp $sys_datatype_expl AS ROW START,

  end_timestamp $sys_datatype_expl AS ROW END,

  PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp)

) WITH SYSTEM VERSIONING;

INSERT INTO t(x)

  VALUES (400),

         (300),

         (500),

         (200),

         (600),

         (100);

UPDATE t

SET x = 999

  WHERE id = 1

     OR id = 3;

select id,x from t;

--sorted_result

SELECT id, x

  FROM t

    FOR SYSTEM_TIME

  FROM '2021-10-27 20:10'

    TO '2038-01-19 03:14:07.999999';

--sorted_result

SELECT id,x

  FROM t

    FOR SYSTEM_TIME

    BETWEEN '2021-10-27 20:10'

    AND '2038-01-19 03:14:07.999999';

#select * from mysql.transaction_registry;

drop table t;

--source suite/versioning/common_finish.inc

produces:

versioning.bug 'innodb,timestamp' [ pass ] 16
versioning.bug 'innodb,trx_id' [ fail ]
Test ended at 2021-11-02 00:16:31

CURRENT_TEST: versioning.bug
--- /home/nik/mariadb/mysql-test/suite/versioning/r/bug.result 2021-11-02 00:16:24.799553838 +0300
+++ /home/nik/mariadb/mysql-test/suite/versioning/r/bug.reject 2021-11-02 00:16:31.282928508 +0300
@@ -47,10 +47,8 @@
TO '2038-01-19 03:14:07.999999';
id x
1 400
-1 999
2 300
3 500
-3 999
4 200
5 600
6 100

mysqltest: Result length mismatch

Nikita Malyavin added a comment - 2021-11-01 21:21 - edited Yes, it seems that transactional and timestamp-based versioning produce different results. I have created the test to show the result differences source suite/versioning/engines.inc; source suite/versioning/common.inc; SELECT TIMESTAMP '9999-12-31 23:59:59'; CREATE TABLE t( x INT, start_timestamp TIMESTAMP(6) NOT NULL, end_timestamp TIMESTAMP(6) NOT NULL DEFAULT now() ); insert into t values (1, TIMESTAMP '2021-10-27 20:10', TIMESTAMP '2038-01-19 03:14:07.999999'); select * from t; select * from t WHERE start_timestamp >= TIMESTAMP '0001-01-01 00:00:00' and end_timestamp < TIMESTAMP '9999-12-31 23:59:59'; drop table t; replace_result $sys_datatype_expl SYS_DATATYPE; eval create or replace table t( id SERIAL PRIMARY KEY, x INT, start_timestamp $sys_datatype_expl AS ROW START, end_timestamp $sys_datatype_expl AS ROW END, PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp) ) WITH SYSTEM VERSIONING; INSERT INTO t(x) VALUES (400), (300), (500), (200), (600), (100); UPDATE t SET x = 999 WHERE id = 1 OR id = 3; select id,x from t; --sorted_result SELECT id, x FROM t FOR SYSTEM_TIME FROM '2021-10-27 20:10' TO '2038-01-19 03:14:07.999999'; --sorted_result SELECT id,x FROM t FOR SYSTEM_TIME BETWEEN '2021-10-27 20:10' AND '2038-01-19 03:14:07.999999'; #select * from mysql.transaction_registry; drop table t; --source suite/versioning/common_finish.inc produces: versioning.bug 'innodb,timestamp' [ pass ] 16 versioning.bug 'innodb,trx_id' [ fail ] Test ended at 2021-11-02 00:16:31 CURRENT_TEST: versioning.bug --- /home/nik/mariadb/mysql-test/suite/versioning/r/bug.result 2021-11-02 00:16:24.799553838 +0300 +++ /home/nik/mariadb/mysql-test/suite/versioning/r/bug.reject 2021-11-02 00:16:31.282928508 +0300 @@ -47,10 +47,8 @@ TO '2038-01-19 03:14:07.999999'; id x 1 400 -1 999 2 300 3 500 -3 999 4 200 5 600 6 100 mysqltest: Result length mismatch

Nikita Malyavin added a comment - 2021-11-02 10:40

GeoffMontee I have just got familiar with MDEV-16226. If transaction-based versioning will be redesigned like explained there, the problem should go

Nikita Malyavin added a comment - 2021-11-02 10:40 GeoffMontee I have just got familiar with MDEV-16226 . If transaction-based versioning will be redesigned like explained there, the problem should go

MariaDB Server

Transaction-precise tables ignore most recent row versions when queried with "FOR SYSTEM_TIME FROM ts TO ts"

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration