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

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

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

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24522 Assertion `inited==NONE' fails upon UPDATE on versioned table with unique blob

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25644 UPDATE not working properly on transaction precise system versioned table

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31944 UPDATE creates new row in system-versioned tables even if there is no value to change

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32124 System-Versioned Tables, extra rows with UPDATE

          • Major - Major loss of function.
          • Closed
          is blocked by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23644 Assertion on evaluating foreign referential action for self-reference in system versioned table

          • Major - Major loss of function.
          • Closed
          is caused by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17089 Updating a System Versioned Table always causes a row to be updated, regardless if the data is the same or not

          • Major - Major loss of function.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24451 Record retains new value on DML when history row insert fails

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-16226 TRX_ID-based System Versioning refactoring

          • Major - Major loss of function.
          • Stalled

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22540 ER_DUP_ENTRY upon REPLACE or Assertion `transactional_table || !changed || thd->transaction.stmt.modified_non_trans_table' failed

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23100 ODKU of non-versioning column inserts history row

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-24064 Utility columns for system-versioned tables

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26778 row_start is not updated in current row for InnoDB

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            user2180613 Remy Fox created issue -
            serg Sergei Golubchik made changes -
            Field Original Value New Value
            serg Sergei Golubchik made changes -
            Affects Version/s 10.3.18 [ 23719 ]
            Affects Version/s 10.3 [ 22126 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.5 [ 23123 ]
            elenst Elena Stepanova made changes -
            Assignee Aleksey Midenkov [ midenok ]
            midenok Aleksey Midenkov made changes -
            Summary UPDATE queries that do not change any values in the rows they match on versioned tables do not insert historical rows UPDATE does not insert history row if the row is not changed
            midenok Aleksey Midenkov made changes -
            Component/s Versioned Tables [ 14303 ]
            midenok Aleksey Midenkov made changes -
            Component/s Data Manipulation - Update [ 10805 ]
            midenok Aleksey Midenkov made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            midenok Aleksey Midenkov made changes -
            Assignee Aleksey Midenkov [ midenok ] Nikita Malyavin [ nikitamalyavin ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            nikitamalyavin Nikita Malyavin made changes -
            Assignee Nikita Malyavin [ nikitamalyavin ] Aleksey Midenkov [ midenok ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            midenok Aleksey Midenkov made changes -
            midenok Aleksey Midenkov made changes -
            midenok Aleksey Midenkov made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            midenok Aleksey Midenkov made changes -
            midenok Aleksey Midenkov made changes -
            midenok Aleksey Midenkov made changes -
            midenok Aleksey Midenkov made changes -
            Fix Version/s 10.3.28 [ 25111 ]
            Fix Version/s 10.4.18 [ 25110 ]
            Fix Version/s 10.5.9 [ 25109 ]
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.5 [ 23123 ]
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            midenok Aleksey Midenkov made changes -
            sanja Oleksandr Byelkin made changes -
            Resolution Fixed [ 1 ]
            Status Closed [ 6 ] Stalled [ 10000 ]
            sanja Oleksandr Byelkin made changes -
            Priority Major [ 3 ] Blocker [ 1 ]
            midenok Aleksey Midenkov made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            midenok Aleksey Midenkov made changes -
            midenok Aleksey Midenkov made changes -
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            midenok Aleksey Midenkov made changes -
            alice Alice Sherepa made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 112205 ] MariaDB v4 [ 158224 ]
            alice Alice Sherepa made changes -
            midenok Aleksey Midenkov made changes -
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -

            People

              midenok Aleksey Midenkov
              user2180613 Remy Fox
              Votes:
              0 Vote for this issue
              Watchers:
              9 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.