[#MDEV-32577] Cannot create table with virtual column

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.

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;

[MDEV-32577] Cannot create table with virtual column Created: 2023-10-25 Updated: 2023-10-25 Resolved: 2023-10-25
Status:	Closed
Project:	MariaDB Server
Component/s:	Virtual Columns
Affects Version/s:	10.5.22
Fix Version/s:	N/A

[MDEV-32577] Cannot create table with virtual column Created: 2023-10-25 Updated: 2023-10-25 Resolved: 2023-10-25