[MDEV-30606] Foreign key cannot be used in the check constraint - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Not a Bug
Affects Version/s: 10.6.12, 10.10.3
Fix Version/s: N/A
Component/s: Data Definition - Create Table
Labels:
- bug
- error
- innodb
Environment:
Using your docker image

Description

I have following code snippet, where I use a foreign key in a check constraint. This code was working with version 10.10.2, but does not anymore with 10.10.3. I checked and it still works with the latest 10.3.38.

DROP TABLE IF EXISTS `feedback`;

CREATE TABLE IF NOT EXISTS `feedback` (

    `id` INT NOT NULL AUTO_INCREMENT,

    PRIMARY KEY (`id`));

DROP TABLE IF EXISTS `message`;

CREATE TABLE IF NOT EXISTS `message` (

	`id` INT NOT NULL AUTO_INCREMENT,

	`feedback_id` INT,

	`value` INT,

	PRIMARY KEY (`id`),

	CONSTRAINT `fk_message_feedback`

		FOREIGN KEY (`feedback_id`)

			REFERENCES `feedback` (`id`)

			ON UPDATE CASCADE,

	CONSTRAINT `failing_constraint`

		CHECK ((`value` >= 10 AND `feedback_id` IS NOT NULL)

		OR (`value` <= 10 AND `feedback_id` IS NULL)));

I get the error code 1901:

Error Code: 1901. Function or expression 'feedback_id' cannot be used in the CHECK clause of `failing_constraint`

Now my question is, if you have introduced a bug in your latest release or if that code snippet is something that should not be working, but was working so far unintentionally. I did not find an exact documentation if a foreign key should not be used in a check constraint.

Attachments

Issue Links

duplicates

MDEV-22880 Honor constraints on UPDATE CASCADE

Open

is caused by

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

Closed

relates to

MDEV-30674 Implement CHECK constraints validation on cascading updates

Closed

MDEV-31322 FKs with 'ON UPDATE CASCADE' can be set in some contexts but not others

Closed

MDEV-31769 Adding a check constraint in "create table" doesn't work if it uses a foreign key with "update cascade"

Closed

Activity

Ascending order - Click to sort in descending order

Pieter Nys added a comment - 2023-02-17 10:41

We are experiencing the same bug in our test-pipeline. When using version 10.6.11, we are able to load in our test-database. Using 10.6.12, this is no longer possible.

The specific section of our database-load-script:

DROP TABLE IF EXISTS `userfield_values`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `userfield_values` (

  `value_id` int(11) NOT NULL AUTO_INCREMENT,

  `field_id` int(11) NOT NULL,

  `party_id` int(11) DEFAULT NULL,

  `case_id` int(11) DEFAULT NULL,

  `case_template_id` int(11) DEFAULT NULL,

  `office_id` int(11) DEFAULT NULL,

  `user_id` int(11) DEFAULT NULL,

  `text` text DEFAULT NULL,

  `number` int(11) DEFAULT NULL,

  `dateandtime` datetime DEFAULT NULL,

  PRIMARY KEY (`value_id`),

  KEY `field_id` (`field_id`),

  KEY `case_id` (`case_id`),

  KEY `party_id` (`party_id`),

  KEY `FK_USERFIELD_VALUES_OFFICE_ID` (`office_id`),

  KEY `FK_USERFIELD_VALUES_USER_ID` (`user_id`),

  KEY `FK_USERFIELD_VALUES_CASE_TEMPLATE_ID` (`case_template_id`),

  CONSTRAINT `FK_USERFIELD_VALUES_CASE_TEMPLATE_ID` FOREIGN KEY (`case_template_id`) REFERENCES `case_templates` (`case_template_id`) ON DELETE CASCADE ON UPDATE CASCADE,

  CONSTRAINT `FK_USERFIELD_VALUES_OFFICE_ID` FOREIGN KEY (`office_id`) REFERENCES `offices` (`office_id`) ON DELETE CASCADE ON UPDATE CASCADE,

  CONSTRAINT `FK_USERFIELD_VALUES_USER_ID` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,

  CONSTRAINT `userfield_values_ibfk_1` FOREIGN KEY (`field_id`) REFERENCES `userfield_names` (`field_id`) ON DELETE CASCADE ON UPDATE CASCADE,

  CONSTRAINT `userfield_values_ibfk_2` FOREIGN KEY (`party_id`) REFERENCES `parties` (`party_id`) ON DELETE CASCADE ON UPDATE CASCADE,

  CONSTRAINT `userfield_values_ibfk_3` FOREIGN KEY (`case_id`) REFERENCES `cases` (`case_id`) ON DELETE CASCADE ON UPDATE CASCADE,

  CONSTRAINT `only_one_link` CHECK (`party_id` is not null and `case_id` is null and `case_template_id` is null and `user_id` is null and `office_id` is null or `case_id` is not null and `party_id` is null and `case_template_id` is null and `user_id` is null and `office_id` is null or `case_template_id` is not null and `party_id` is null and `case_id` is null and `user_id` is null and `office_id` is null or `user_id` is not null and `party_id` is null and `case_id` is null and `case_template_id` is null and `office_id` is null or `office_id` is not null and `party_id` is null and `case_id` is null and `case_template_id` is null and `user_id` is null)

) ENGINE=InnoDB AUTO_INCREMENT=819 DEFAULT CHARSET=utf8mb4;

Our error-message:

General error: 1901 Function or expression 'party_id' cannot be used in the CHECK clause of `only_one_link`

Pieter Nys added a comment - 2023-02-17 10:41 We are experiencing the same bug in our test-pipeline. When using version 10.6.11 , we are able to load in our test-database. Using 10.6.12 , this is no longer possible. The specific section of our database-load-script: DROP TABLE IF EXISTS `userfield_values`; /*!40101 SET @saved_cs_client = @@character_set_client */ ; /*!40101 SET character_set_client = utf8 */ ; CREATE TABLE `userfield_values` ( `value_id` int (11) NOT NULL AUTO_INCREMENT, `field_id` int (11) NOT NULL , `party_id` int (11) DEFAULT NULL , `case_id` int (11) DEFAULT NULL , `case_template_id` int (11) DEFAULT NULL , `office_id` int (11) DEFAULT NULL , `user_id` int (11) DEFAULT NULL , `text` text DEFAULT NULL , `number` int (11) DEFAULT NULL , `dateandtime` datetime DEFAULT NULL , PRIMARY KEY (`value_id`), KEY `field_id` (`field_id`), KEY `case_id` (`case_id`), KEY `party_id` (`party_id`), KEY `FK_USERFIELD_VALUES_OFFICE_ID` (`office_id`), KEY `FK_USERFIELD_VALUES_USER_ID` (`user_id`), KEY `FK_USERFIELD_VALUES_CASE_TEMPLATE_ID` (`case_template_id`), CONSTRAINT `FK_USERFIELD_VALUES_CASE_TEMPLATE_ID` FOREIGN KEY (`case_template_id`) REFERENCES `case_templates` (`case_template_id`) ON DELETE CASCADE ON UPDATE CASCADE , CONSTRAINT `FK_USERFIELD_VALUES_OFFICE_ID` FOREIGN KEY (`office_id`) REFERENCES `offices` (`office_id`) ON DELETE CASCADE ON UPDATE CASCADE , CONSTRAINT `FK_USERFIELD_VALUES_USER_ID` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE , CONSTRAINT `userfield_values_ibfk_1` FOREIGN KEY (`field_id`) REFERENCES `userfield_names` (`field_id`) ON DELETE CASCADE ON UPDATE CASCADE , CONSTRAINT `userfield_values_ibfk_2` FOREIGN KEY (`party_id`) REFERENCES `parties` (`party_id`) ON DELETE CASCADE ON UPDATE CASCADE , CONSTRAINT `userfield_values_ibfk_3` FOREIGN KEY (`case_id`) REFERENCES `cases` (`case_id`) ON DELETE CASCADE ON UPDATE CASCADE , CONSTRAINT `only_one_link` CHECK (`party_id` is not null and `case_id` is null and `case_template_id` is null and `user_id` is null and `office_id` is null or `case_id` is not null and `party_id` is null and `case_template_id` is null and `user_id` is null and `office_id` is null or `case_template_id` is not null and `party_id` is null and `case_id` is null and `user_id` is null and `office_id` is null or `user_id` is not null and `party_id` is null and `case_id` is null and `case_template_id` is null and `office_id` is null or `office_id` is not null and `party_id` is null and `case_id` is null and `case_template_id` is null and `user_id` is null ) ) ENGINE=InnoDB AUTO_INCREMENT=819 DEFAULT CHARSET=utf8mb4; Our error-message: General error: 1901 Function or expression 'party_id' cannot be used in the CHECK clause of `only_one_link`

Sergei Golubchik added a comment - 2023-02-17 12:11

This was an intentional change. If a field is a part of a foreign key with a ON UPDATE CASCADE action, it can be updated internally by InnoDB without informing the server and without validating the CHECK constraint. In other words, such cascading updates can invalidate the constraint and that's why they are not supported.

It is possible to implement CHECK constrain validation on cascading updates, but this would've been too intrusive and risky change in a 10.6 GA branch

Sergei Golubchik added a comment - 2023-02-17 12:11 This was an intentional change. If a field is a part of a foreign key with a ON UPDATE CASCADE action, it can be updated internally by InnoDB without informing the server and without validating the CHECK constraint. In other words, such cascading updates can invalidate the constraint and that's why they are not supported. It is possible to implement CHECK constrain validation on cascading updates, but this would've been too intrusive and risky change in a 10.6 GA branch

Valerii Kravchuk added a comment - 2023-02-17 12:17

I wonder what MDEV introduced this change and why it is not mentioned in the release notes in any visible way?

Valerii Kravchuk added a comment - 2023-02-17 12:17 I wonder what MDEV introduced this change and why it is not mentioned in the release notes in any visible way?

Sergei Golubchik added a comment - 2023-02-17 12:20

it's linked as "is caused by", but here it is again: ~~MDEV-22602~~

Not listed in release notes because they only list major changes and this one was considered a minor one. Perhaps incorrectly. I can add it if you think it's worth it

Sergei Golubchik added a comment - 2023-02-17 12:20 it's linked as "is caused by", but here it is again: MDEV-22602 Not listed in release notes because they only list major changes and this one was considered a minor one. Perhaps incorrectly. I can add it if you think it's worth it

Valerii Kravchuk added a comment - 2023-02-17 12:25

But ~~MDEV-22602~~ says it's fixed years ago, in 10.5.4. How it could cause a change of behaviour in 10.6.12 vs 10.6.11? Do I miss something here?

I'd appreciate more detailed information for the release notes, now that we have several users complaining.

Valerii Kravchuk added a comment - 2023-02-17 12:25 But MDEV-22602 says it's fixed years ago, in 10.5.4. How it could cause a change of behaviour in 10.6.12 vs 10.6.11? Do I miss something here? I'd appreciate more detailed information for the release notes, now that we have several users complaining.

Sergei Golubchik added a comment - 2023-05-22 11:10

This change in behavior was caused by the commit https://github.com/MariaDB/server/commit/a5eff044cb85 (in 10.6.12, indeed)

When working on something else I've noticed that ~~MDEV-22602~~ didn't cover the case of named CHECK constraints and casually fixed that.

Sergei Golubchik added a comment - 2023-05-22 11:10 This change in behavior was caused by the commit https://github.com/MariaDB/server/commit/a5eff044cb85 (in 10.6.12, indeed) When working on something else I've noticed that MDEV-22602 didn't cover the case of named CHECK constraints and casually fixed that.

Tim Koop added a comment - 2023-07-25 14:06

We were bitten by this change too. We would have appreciated reading about it in the release notes.

Tim Koop added a comment - 2023-07-25 14:06 We were bitten by this change too. We would have appreciated reading about it in the release notes.

Sergei Golubchik added a comment - 2023-11-06 09:09

This limitation will be removed in MDEV-22880

Sergei Golubchik added a comment - 2023-11-06 09:09 This limitation will be removed in MDEV-22880

People

Assignee:: Sergei Golubchik

Reporter:: Nikola

Votes:: 2 Vote for this issue

Watchers:: 8 Start watching this issue

Dates

Created:: 2023-02-07 17:59

Updated:: 2024-07-07 19:39

Resolved:: 2023-02-17 12:11

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server