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

row_start is not updated in current row for InnoDB

    XMLWordPrintable

Details

    Description

      After upgrading to MariaDB 10.6.4, we noticed an issue with system versioned tables where it inserts a new row for update commands that don't change anything. Previous versions such as 10.5.5 would ignore such dummy updates and not change the SVT history. Small test case to illustrate:

      • CREATE TABLE t(
        x INT,
        start_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
        end_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
        PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp)
        ) WITH SYSTEM VERSIONING;
      • "INSERT INTO t( x ) VALUES (1);"
      • "UPDATE t SET x = 1 WHERE x = 1;" # no change, dummy update

      With MariaDB 10.5.5, the above prints:
      Rows matched: 1 Changed: 0 Inserted: 0 Warnings: 0

      With MariaDB 10.6.4:
      Rows matched: 1 Changed: 0 Inserted: 1 Warnings: 0

      • "SELECT * FROM t for SYSTEM_TIME between (NOW() - INTERVAL 1 YEAR) AND NOW();"

      The above will print 2 records (with same start_time but different end_time) for MariaDB 10.6.4 and only 1 record for MariaDB 10.5.5

      Looks like the 10.6 version stores dummy updates which don't make any real change to the data. While that's okay, shouldn't the start time stamp also be updated for the new row? The current behavior is problematic because queries such as "SELECT * FROM t FOR SYSTEM_TIME AS OF ..." will print 2 records for certain timestamps which doesn't make sense.

      Attachments

        Issue Links

          Activity

            People

              midenok Aleksey Midenkov
              sridhu88 Sridhar Sadasivam
              Votes:
              2 Vote for this issue
              Watchers:
              8 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.