[MDEV-22602] WITHOUT OVERLAPS constraint is ignored when update is performed via foreign key cascade Created: 2020-05-17  Updated: 2023-11-05  Resolved: 2020-06-12

Status: Closed
Project: MariaDB Server
Component/s: Versioned Tables
Affects Version/s: 10.5
Fix Version/s: 10.5.4

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Aleksey Midenkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Problem/Incident
causes MDEV-30606 Foreign key cannot be used in the che... Closed
Relates
relates to MDEV-19402 Invoke triggers for foreign key casca... Closed
relates to MDEV-30674 Implement CHECK constraints validatio... Closed
relates to MDEV-16417 Store Foreign Key metadata outside of... In Review
relates to MDEV-18114 Foreign Key Constraint actions don't ... Closed
relates to MDEV-22880 Honor constraints on UPDATE CASCADE Open

 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.

 Comments   
Comment by Nikita Malyavin [ 2020-06-04 ]

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.

Comment by Elena Stepanova [ 2020-06-04 ]

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.

Comment by Nikita Malyavin [ 2020-06-04 ]

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

Comment by Sergei Golubchik [ 2020-06-07 ]

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?

Comment by Oleksandr Byelkin [ 2020-06-08 ]

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

Comment by Oleksandr Byelkin [ 2020-06-12 ]

fix the goto and OK to push

Generated at Thu Feb 08 09:16:01 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.