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

WITHOUT OVERLAPS constraint is ignored when update is performed via foreign key cascade

Details

    Description

      create or replace table t1 (f1 int, f2 date, f3 date, key(f1,f3,f2)) engine=InnoDB;
      create or replace table t2 (a int, s date, e date, period for p(s,e), primary key(a, p without overlaps), foreign key(a,e,s) references t1(f1,f3,f2) on delete cascade on update cascade) engine=InnoDB;
      insert into t1 values (1,'2020-01-01','2021-01-01'),(2,'2020-06-01','2020-07-01');
      insert into t2 values (1,'2020-01-01','2021-01-01'),(2,'2020-06-01','2020-07-01');
       
      update t1 set f1 = 2 where f1 = 1;
      select * from t2;
      

      10.5 69077dea25

      MariaDB [test]> update t1 set f1 = 2 where f1 = 1;
      Query OK, 1 row affected (0.024 sec)
      Rows matched: 1  Changed: 1  Warnings: 0
       
      MariaDB [test]> select * from t2;
      +---+------------+------------+
      | a | s          | e          |
      +---+------------+------------+
      | 2 | 2020-06-01 | 2020-07-01 |
      | 2 | 2020-01-01 | 2021-01-01 |
      +---+------------+------------+
      2 rows in set (0.001 sec)
      

      So, now we have a non-unique key with an overlap.

      Traditional PK would prevent the update by throwing ER_FOREIGN_DUPLICATE_KEY_WITH_CHILD_INFO.

      Attachments

        Issue Links

          Activity

            It turns out that any sql-side constraints don't work, as well as any triggers: see MDEV-19402 for example.
            So i think it's not a bug due to current circumstances, and just should be documented.

            nikitamalyavin Nikita Malyavin added a comment - It turns out that any sql-side constraints don't work, as well as any triggers: see MDEV-19402 for example. So i think it's not a bug due to current circumstances, and just should be documented.

            Unique constraint works, for example:

            --source include/have_innodb.inc
             
            create or replace table t1 (f1 int, f2 date, f3 date, key(f1,f3,f2)) engine=InnoDB;
            create or replace table t2 (a int, s date, e date, unique (a), foreign key(a,e,s) references t1(f1,f3,f2) on delete cascade on update cascade) engine=InnoDB;
             
            insert into t1 values (1,'2020-01-01','2021-01-01'),(2,'2020-06-01','2020-07-01');
            insert into t2 values (1,'2020-01-01','2021-01-01'),(2,'2020-06-01','2020-07-01');
             
            update t1 set f1 = 2 where f1 = 1;
            

            query 'update t1 set f1 = 2 where f1 = 1' failed: 1761: Foreign key constraint for table 't1', record '2-2021-01-01-2020-01-01' would lead to a duplicate entry in table 't2', key 'a'
            

            CHECK constraints don't work. Not sure if it's a limitation or a bug, both are hard to find in documentation/JIRA. I'll try to search and will file a bug if I don't find an existing one.

            Given that WITHOUT OVERLAPS behaves as a unique key rather than check constraint in other aspects, such as error handling, I'd expect it to do the same here. However, I don't have strict objections to declaring it a limitation, if serg is okay with it.

            elenst Elena Stepanova added a comment - Unique constraint works, for example: --source include/have_innodb.inc   create or replace table t1 (f1 int , f2 date , f3 date , key (f1,f3,f2)) engine=InnoDB; create or replace table t2 (a int , s date , e date , unique (a), foreign key (a,e,s) references t1(f1,f3,f2) on delete cascade on update cascade ) engine=InnoDB;   insert into t1 values (1, '2020-01-01' , '2021-01-01' ),(2, '2020-06-01' , '2020-07-01' ); insert into t2 values (1, '2020-01-01' , '2021-01-01' ),(2, '2020-06-01' , '2020-07-01' );   update t1 set f1 = 2 where f1 = 1; query 'update t1 set f1 = 2 where f1 = 1' failed: 1761: Foreign key constraint for table 't1', record '2-2021-01-01-2020-01-01' would lead to a duplicate entry in table 't2', key 'a' CHECK constraints don't work. Not sure if it's a limitation or a bug, both are hard to find in documentation/JIRA. I'll try to search and will file a bug if I don't find an existing one. Given that WITHOUT OVERLAPS behaves as a unique key rather than check constraint in other aspects, such as error handling, I'd expect it to do the same here. However, I don't have strict objections to declaring it a limitation, if serg is okay with it.

            That unfortunately is a limitation. It will work like UNIQUE(a, e, s) due to how it is implemented

            nikitamalyavin Nikita Malyavin added a comment - That unfortunately is a limitation. It will work like UNIQUE(a, e, s) due to how it is implemented

            I agree we cannot fix it properly now. Shouldn't the server disallow cascading FK constraints that might modify columns in long-uniques, without overlap, or check constraints? Until MDEV-16417 is done, that is?

            serg Sergei Golubchik added a comment - I agree we cannot fix it properly now. Shouldn't the server disallow cascading FK constraints that might modify columns in long-uniques, without overlap, or check constraints? Until MDEV-16417 is done, that is?

            I have fixed to 10.5 to have "disallowing" mentioned above be in 10.5

            sanja Oleksandr Byelkin added a comment - I have fixed to 10.5 to have "disallowing" mentioned above be in 10.5

            fix the goto and OK to push

            sanja Oleksandr Byelkin added a comment - fix the goto and OK to push

            People

              midenok Aleksey Midenkov
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              6 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.