Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.5.22
-
None
-
Ubuntu 22.04
Description
When attempting to import a database dump from a MariaDB 10.5.15 to MariaDB 10.5.22 server, I encounter an error regarding a GENERATED ALWAYS AS / foreign key constraint in a table definition.
This is the error:
ERROR 1901 (HY000) at line 539: Function or expression 'food_id' cannot be used in the GENERATED ALWAYS AS clause of `open_ended`
The error seems to be related to the following change in 10.5.22: MDEV-18114 and I have checked that this error does not occur in MariaDB 10.5.21.
Here is the full statement that fails on 10.5.22 but works in previous releases:
CREATE TABLE `intakes` ( |
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, |
`dataset_id` bigint(20) unsigned NOT NULL, |
`day_id` bigint(20) unsigned DEFAULT NULL, |
`meal_id` bigint(20) unsigned DEFAULT NULL, |
`group_id` bigint(20) unsigned DEFAULT NULL, |
`food_id` bigint(20) unsigned DEFAULT NULL, |
`code` bigint(20) unsigned NOT NULL, |
`line` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, |
`quantity` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, |
`portions` tinyint(3) unsigned DEFAULT NULL, |
`hash` char(40) GENERATED ALWAYS AS (case when `line` is not null then sha(lcase(trim(`line`))) else NULL end) STORED, |
`open_ended` tinyint(1) GENERATED ALWAYS AS (case when `food_id` is null then 1 else 0 end) STORED, |
`attributes` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`attributes`)), |
`created_date` date GENERATED ALWAYS AS (cast(`created_at` as date)) STORED, |
`created_at` timestamp NULL DEFAULT NULL, |
`updated_at` timestamp NULL DEFAULT NULL, |
PRIMARY KEY (`id`), |
KEY `intakes_dataset_id_foreign` (`dataset_id`), |
KEY `intakes_day_id_foreign` (`day_id`), |
KEY `intakes_meal_id_foreign` (`meal_id`), |
KEY `intakes_food_id_foreign` (`food_id`), |
KEY `intakes_code_index` (`code`), |
KEY `intakes_created_date_index` (`created_date`), |
KEY `intakes_open_ended_index` (`open_ended`), |
KEY `intakes_dataset_id_open_ended_index` (`dataset_id`,`open_ended`), |
KEY `intakes_dataset_id_open_ended_created_date_index` (`dataset_id`,`open_ended`,`created_date`), |
KEY `intakes_hash_index` (`hash`), |
KEY `intakes_group_id_foreign` (`group_id`), |
FULLTEXT KEY `intakes_line_fulltext` (`line`), |
CONSTRAINT `intakes_dataset_id_foreign` FOREIGN KEY (`dataset_id`) REFERENCES `datasets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
CONSTRAINT `intakes_day_id_foreign` FOREIGN KEY (`day_id`) REFERENCES `days` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, |
CONSTRAINT `intakes_food_id_foreign` FOREIGN KEY (`food_id`) REFERENCES `foods` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
CONSTRAINT `intakes_group_id_foreign` FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
CONSTRAINT `intakes_meal_id_foreign` FOREIGN KEY (`meal_id`) REFERENCES `meals` (`id`) ON DELETE SET NULL ON UPDATE CASCADE |
) ENGINE=InnoDB AUTO_INCREMENT=438584 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; |