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

change in system versioned table behavior in 10.6

    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

              Assignee:
              midenok Aleksey Midenkov
              Reporter:
              sridhu88 Sridhar Sadasivam
              Votes:
              1 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration