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

Updating a System Versioned Table always causes a row to be updated, regardless if the data is the same or not

    XMLWordPrintable

Details

    Description

      Good Morning,

      If you run an update on a non-system versioned table, and the column(s) you update contain the same data, then no rows are updated. However, if you add system versioning to a table, then an update will always update the rows selected by the WHERE clause; regardless of if the values were updated or not.

      This has the side effect of unnecessarily increasing the size of the history, as a new history row is created, despite there being no change. I am not certain if this is intended behaviour or not, so I believe as a minimum this should be documented (as SV tables behave differently to normal tables) - however ideally this would be changed, or a configuration option added to control if an no change UPDATE causes a new history row or not.

      Test Script

      /* Setup */
      CREATE TABLE `bugTest`.`nsvTable` (
        `i` INT UNSIGNED NOT NULL,
        PRIMARY KEY (`i`)
      ) ENGINE = INNODB;
       
      INSERT INTO `nsvTable` (`i`) VALUES(1);
       
      CREATE TABLE `bugTest`.`svTable` (
        `i` INT UNSIGNED NOT NULL,
        PRIMARY KEY (`i`)
      ) ENGINE = INNODB WITH SYSTEM VERSIONING;
       
      INSERT INTO `svTable` (`i`) VALUES(1);
       
      /* Test Updates */
      UPDATE `nsvTable` SET `i` = 1 WHERE `i` = 1;
      /* 0 row(s) affected */
       
      UPDATE `svTable` SET `i` = 1 WHERE `i` = 1;
      /* 1 row(s) affected */
       
      /* Cleanup */
      DROP TABLE `nsvTable`, `svTable`;
      

      Attachments

        Issue Links

          Activity

            People

              midenok Aleksey Midenkov
              rmhumphries Robert Humphries
              Votes:
              1 Vote for this issue
              Watchers:
              7 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.