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

UPDATE does not insert history row if the row is not changed

    XMLWordPrintable

    Details

      Description

      UPDATE queries that do not change any values in the rows they match on versioned tables do not insert historical rows.

      Example:

      CREATE TABLE test (a INT) WITH SYSTEM VERSIONING;
      INSERT INTO test (a) VALUES (1);
      UPDATE test SET a = 1 WHERE a = 1;
      SELECT *, row_start, row_end FROM test FOR SYSTEM_TIME ALL;
      

      Disclaimer: I don't know whether this is a bug or was purposefully designed like this. In the latter case, I want to make a case to reconsider it:

      • This behavior contrasts with triggers: UPDATE queries that do not change any values in the rows they match still carry out registered triggers for each row.
      • Now some UPDATEs cannot be logged historically. Versioned table fail to capture that they happened. Yet logging such queries and the timestamps at which they were issued can still be valuable information.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              midenok Aleksey Midenkov
              Reporter:
              user2180613 Remy Fox
              Votes:
              0 Vote for this issue
              Watchers:
              9 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: