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

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

    XMLWordPrintable

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

          Activity

            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.