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

Extra history row when deleting from self-referenced table

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

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-21138 Assertion `col->ord_part' or `f.col->ord_part' failed in row_build_index_entry_low, assertion: buf != field_ref_zero in row_merge_buf_add

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            midenok Aleksey Midenkov created issue -
            midenok Aleksey Midenkov made changes -
            Field Original Value New Value
            Labels foreign-keys
            midenok Aleksey Midenkov made changes -
            Description h3. Reproduce
            {code:sql}
            --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;
            {code}             		h3. Reproduce
            {code:sql}
            --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;
            {code}

            h3. Result
            {code:sql}
            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
            {code}

            h3. Expected
            {code:sql}
            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
            {code}

            h3. Good
            Reference from other table works ok:
            {code:sql}
            --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;
            {code}

            {code:sql}
            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
            {code}
            midenok Aleksey Midenkov made changes -
            Description h3. Reproduce
            {code:sql}
            --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;
            {code}

            h3. Result
            {code:sql}
            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
            {code}

            h3. Expected
            {code:sql}
            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
            {code}

            h3. Good
            Reference from other table works ok:
            {code:sql}
            --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;
            {code}

            {code:sql}
            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
            {code}             		h3. Reproduce
            {code:sql}
            --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;
            {code}

            h3. Result
            3 history rows (SET NULL action added one more).
            {code:sql}
            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
            {code}

            h3. Expected
            2 history rows.
            {code:sql}
            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
            {code}

            h3. Good
            Reference from other table works ok:
            {code:sql}
            --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;
            {code}

            {code:sql}
            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
            {code}
            midenok Aleksey Midenkov made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 116817 ] MariaDB v4 [ 142432 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.3 [ 22126 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.4 [ 22408 ]

            People

              midenok Aleksey Midenkov
              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.