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

System-versioned table writes historical rows on update without any changed data

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Duplicate
    • 10.5.9
    • N/A
    • Versioned Tables
    • None

    Description

      When updating a row in a system-versioned table and updating a column's value with the exact current value, a new historical row is written, that has the same ROW_START value as well as the same value for any other column as the previous one, while a ON UPDATE CURRENT_TIMESTAMP() column is not updated on the current row.

      I also experienced that issue to not exist when updating a ENUM column with the exact current value, while existing when updating a INT column with the exact current value. However, in the example below the columns all behave the same.

      CREATE TABLE Foobar (
      		 
        id INT(11) NOT NULL,
      		 
        a INT(11) NOT NULL,
      		 
        b ENUM('foo', 'bar') NOT NULL,
      		 
        c VARCHAR(255) NOT NULL,
      		 
        ts DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP(),
      		 
        PRIMARY KEY (id)
      		 
      ) WITH SYSTEM VERSIONING;
      		 
      INSERT INTO Foobar (id, a, b, c) VALUES (1, 1, 'foo', 'hello');
      		 
      -- 1 row
      		 
      SELECT *, ROW_START, ROW_END FROM Foobar FOR SYSTEM_TIME ALL WHERE id = 1;
      		 
      UPDATE Foobar SET b = 'foo' WHERE id = 1;
      		 
      -- 2 rows with the exact same values except for ROW_END
      		 
      SELECT *, ROW_START, ROW_END FROM Foobar FOR SYSTEM_TIME ALL WHERE id = 1;
      		 
      UPDATE Foobar SET a = 1 WHERE id = 1;
      		 
      -- 3 rows with the exact same values except for ROW_END
      		 
      SELECT *, ROW_START, ROW_END FROM Foobar FOR SYSTEM_TIME ALL WHERE id = 1;

      SELECT *, ROW_START, ROW_END FROM Foobar FOR SYSTEM_TIME AS OF '<row_start_value>' WHERE id = 1 will also return 3 rows, while I would expect only 1.

      I would expect that a historical row is only written when data in the row really change, just like a ON UPDATE column behaves.

      Attachments

        Issue Links

          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31944 UPDATE creates new row in system-versioned tables even if there is no value to change

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32124 System-Versioned Tables, extra rows with UPDATE

          • Major - Major loss of function.
          • Closed
          is part of

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26778 row_start is not updated in current row for InnoDB

          • Major - Major loss of function.
          • Closed

          Activity

            alice Alice Sherepa added a comment - - edited

            Thank you for reporting the issue!

            The decision to insert the historical row even if the row has not changed was intentional (MDEV-23446 ), but start_time should be updated accordingly.
            This is a part of the same bug as MDEV-26778, so I suggest following the progress in that Jira ticket if you'd like, and to close this one. I added the test case there.

            I also tried with enum type, the bug was also reproducible:

            MariaDB [test]> CREATE or replace TABLE t1 ( id enum('a','b') , ts datetime ON UPDATE current_timestamp()) WITH SYSTEM versioning;
            		 
            Query OK, 0 rows affected (0.059 sec)
            		 
             
            		 
            MariaDB [test]> INSERT INTO t1 VALUES ('a',now());
            		 
            Query OK, 1 row affected (0.005 sec)
            		 
             
            		 
            MariaDB [test]> UPDATE t1 SET id = 'a' WHERE id = 'a';
            		 
            Query OK, 0 rows affected (0.004 sec)
            		 
            Rows matched: 1  Changed: 0  Inserted: 1  Warnings: 0
            		 
             
            		 
            MariaDB [test]> UPDATE t1 SET id = 'a' WHERE id = 'a';
            		 
            Query OK, 0 rows affected (0.004 sec)
            		 
            Rows matched: 1  Changed: 0  Inserted: 1  Warnings: 0
            		 
             
            		 
            MariaDB [test]> SELECT *, ROW_START, ROW_END FROM t1 FOR SYSTEM_TIME ALL;
            		 
            +------+---------------------+----------------------------+----------------------------+
            		 
            | id   | ts                  | ROW_START                  | ROW_END                    |
            		 
            +------+---------------------+----------------------------+----------------------------+
            		 
            | a    | 2021-10-07 18:05:41 | 2021-10-07 18:05:41.476392 | 2038-01-19 04:14:07.999999 |
            		 
            | a    | 2021-10-07 18:05:41 | 2021-10-07 18:05:41.476392 | 2021-10-07 18:05:52.143934 |
            		 
            | a    | 2021-10-07 18:05:41 | 2021-10-07 18:05:41.476392 | 2021-10-07 18:06:25.812918 |
            		 
            +------+---------------------+----------------------------+----------------------------+
            		 
            3 rows in set (0.001 sec)

            alice Alice Sherepa added a comment - - edited Thank you for reporting the issue! The decision to insert the historical row even if the row has not changed was intentional ( MDEV-23446 ), but start_time should be updated accordingly. This is a part of the same bug as MDEV-26778 , so I suggest following the progress in that Jira ticket if you'd like, and to close this one. I added the test case there. I also tried with enum type, the bug was also reproducible: MariaDB [test]> CREATE or replace TABLE t1 ( id enum('a','b') , ts datetime ON UPDATE current_timestamp()) WITH SYSTEM versioning; Query OK, 0 rows affected (0.059 sec)   MariaDB [test]> INSERT INTO t1 VALUES ('a',now()); Query OK, 1 row affected (0.005 sec)   MariaDB [test]> UPDATE t1 SET id = 'a' WHERE id = 'a'; Query OK, 0 rows affected (0.004 sec) Rows matched: 1 Changed: 0 Inserted: 1 Warnings: 0   MariaDB [test]> UPDATE t1 SET id = 'a' WHERE id = 'a'; Query OK, 0 rows affected (0.004 sec) Rows matched: 1 Changed: 0 Inserted: 1 Warnings: 0   MariaDB [test]> SELECT *, ROW_START, ROW_END FROM t1 FOR SYSTEM_TIME ALL; +------+---------------------+----------------------------+----------------------------+ | id | ts | ROW_START | ROW_END | +------+---------------------+----------------------------+----------------------------+ | a | 2021-10-07 18:05:41 | 2021-10-07 18:05:41.476392 | 2038-01-19 04:14:07.999999 | | a | 2021-10-07 18:05:41 | 2021-10-07 18:05:41.476392 | 2021-10-07 18:05:52.143934 | | a | 2021-10-07 18:05:41 | 2021-10-07 18:05:41.476392 | 2021-10-07 18:06:25.812918 | +------+---------------------+----------------------------+----------------------------+ 3 rows in set (0.001 sec)

            People

              Unassigned Unassigned
              lkrause Lucas Krause
              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.