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

FOREIGN CASCADE operations in system versioned referenced tables

    XMLWordPrintable

    Details

      Description

      create or replace table author (
      	id smallint unsigned not null auto_increment primary key,
      	name varchar(100) not null
      ) engine = innodb;
       
      create or replace table book (
      	id mediumint unsigned not null auto_increment primary key,
      	title varchar(200) not null,
      	author_id smallint unsigned not null,
      	constraint `fk_book_author`
      		foreign key (author_id) references author (id)
      		on delete cascade
      		on update cascade
      ) engine = innodb with system versioning;
       
      insert into author (name) values ('abdul alhazred');
      insert into book (title, author_id) values ('necronomicon', last_insert_id());
       
      select author_id, row_start, row_end from book for system_time all;
      update author set id = 11;
      select author_id, row_start, row_end from book for system_time all;
      delete from author;
      select author_id, row_start, row_end from book for system_time all;
      

      Last SELECT returns this:

      select author_id, row_start, row_end from book for system_time all;
      author_id	row_start	row_end
      11	2018-02-20 18:20:05.655342	2018-02-20 18:20:05.661452
      

      So, history row was not inserted on UPDATE. This is a bug. MS SQL produces a history in that case: http://sqlfiddle.com/#!18/a073b/15

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              thiru Thirunarayanan Balathandayuthapani
              Reporter:
              kevg Eugene Kosov
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: