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

System-versioned table creates history row although UPDATE does not change row values

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Critical
    • Resolution: Unresolved
    • 11.4.10
    • 10.11, 11.4, 11.8, 12.3
    • None
    • debian-linux-gnu / 11.4.10-MariaDB-ubu2404 / mariadb.org binary distribution
    • Not for Release Notes

    Description

      I observed that a system-versioned table creates a new history version even when an UPDATE statement does not modify any column value.

      According to the MariaDB documentation:

      “System-versioned tables store the history of all changes”

      Source: https://mariadb.com/docs/server/reference/sql-structure/temporal-tables/system-versioned-tables/

      In the following example, the UPDATE statement does not change the row values:

      CREATE TABLE t1 (
        x INT UNIQUE,
        y INT
      ) WITH SYSTEM VERSIONING;
       
      INSERT INTO t1 (x, y) VALUES (1, 1);
       
      UPDATE t1
      SET x = 1;
       
      SELECT *, ROW_START, ROW_END
      FROM t1
      FOR SYSTEM_TIME ALL;
      

      Expected result

      Only one row should exist in history because no data values changed.

      Actual result

      Two versions are returned.

      Although no column value changed, a new history version was created.

      Question

      Is this behavior intended?

      If yes, the documentation may need clarification because it currently refers to storing the history of “changes”, while this behavior appears to version every UPDATE statement regardless of whether row values changed.

      Attachments

        Issue Links

          Activity

            People

              stefan.hinz Stefan Hinz
              mkduew Marc Kemmer
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.