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

row_start not set properly when updating field without system versioning using on duplicate key update

    XMLWordPrintable

Details

    Description

      While experimenting with temporal tables, I encountered what appears to be a bug with updating fields without system versioning in otherwise temporal tables.

      The bug

      When using `UPDATE` to only update a field without system versioning, everything works as expected (i.e. not creating a new row), except when using `ON DUPLICATE KEY UPDATE`. By doing so, a new row gets created, even though only a non-versioned field gets updated. Additionally and even worse, the row_start does not get set properly but instead is copied from the most recent row. On the other hand, when using `ON DUPLICATE KEY UPDATE` to update the value of a system versioned field, a new row gets created with the proper timestamp for the row_start, as expected.

      Example

      MariaDB [test]> create table test_table (id int unsigned not null primary key, n int unsigned not null without system versioning) with system versioning;
      Query OK, 0 rows affected (0,027 sec)
       
      MariaDB [test]> insert into test_table (id, n) values (1, 1) on duplicate key update n=2;
      Query OK, 1 row affected (0,007 sec)
       
      MariaDB [test]> insert into test_table (id, n) values (1, 1) on duplicate key update n=2;
      Query OK, 3 rows affected (0,005 sec)
       
      MariaDB [test]> select *, row_start, row_end from test_table for system_time all;
      +----+---+----------------------------+----------------------------+
      | id | n | row_start                  | row_end                    |
      +----+---+----------------------------+----------------------------+
      |  1 | 1 | 2023-02-21 12:17:21.303652 | 2023-02-21 12:17:23.935926 |
      |  1 | 2 | 2023-02-21 12:17:21.303652 | 2038-01-19 04:14:07.999999 |
      +----+---+----------------------------+----------------------------+
      2 rows in set (0,000 sec)
      

      Misc

      This issue may well be related to MDEV-29723 and/or MDEV-25644.

      Attachments

        Issue Links

          Activity

            People

              midenok Aleksey Midenkov
              HenryK Henry Knopsmeier
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.