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

Extra history row when deleting from self-referenced table

    XMLWordPrintable

    Details

      Description

      Reproduce

      --source include/have_innodb.inc
       
      create table t1 (
        f1 int, f2 int,
        key(f1),
        foreign key (f2) references t1 (f1) on delete set null)
      with system versioning engine innodb;
      insert into t1 values (1, 1), (1, 1);
      delete from t1;
      select f1, f2, row_start, row_end from t1 for system_time all;
      # cleanup
      drop table t1;
      

      Result

      3 history rows (SET NULL action added one more).

      select f1, f2, row_start, row_end from t1 for system_time all;
      f1      f2      row_start       row_end
      1       1       2020-12-11 19:53:57.624080      2020-12-11 19:53:57.626972
      1       NULL    2020-12-11 19:53:57.626972      2020-12-11 19:53:57.626972
      1       1       2020-12-11 19:53:57.624080      2020-12-11 19:53:57.626972
      

      Expected

      2 history rows.

      select f1, f2, row_start, row_end from t1 for system_time all;
      f1      f2      row_start       row_end
      1       1       2020-12-11 19:53:57.624080      2020-12-11 19:53:57.626972
      1       1       2020-12-11 19:53:57.624080      2020-12-11 19:53:57.626972
      

      Good

      Reference from other table works ok:

      --source include/have_innodb.inc
       
      create table t1 (f1 int, key(f1))
      with system versioning engine innodb;
       
      create table t2 (
        f2 int,
        foreign key (f2) references t1 (f1) on delete set null)
      with system versioning engine innodb;
       
      insert into t1 values (1), (1);
      insert into t2 values (1), (1);
      delete from t2;
      select f1, row_start, row_end from t1 for system_time all;
      select f2, row_start, row_end from t2 for system_time all;
      drop tables t2, t1;
      

      select f1, row_start, row_end from t1 for system_time all;
      f1      row_start       row_end
      1       2020-12-11 19:53:57.671806      2038-01-19 06:14:07.999999
      1       2020-12-11 19:53:57.671806      2038-01-19 06:14:07.999999
      select f2, row_start, row_end from t2 for system_time all;
      f2      row_start       row_end
      1       2020-12-11 19:53:57.674496      2020-12-11 19:53:57.677131
      1       2020-12-11 19:53:57.674496      2020-12-11 19:53:57.677131
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              midenok Aleksey Midenkov
              Reporter:
              midenok Aleksey Midenkov
              Votes:
              0 Vote for this issue
              Watchers:
              1 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.