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

Cannot create table with virtual column

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 10.5.22
    • N/A
    • Virtual Columns
    • 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;

      Attachments

        Activity

          serg Sergei Golubchik added a comment -

          It is intentional. The way MariaDB works, and always did, foreign key cascading actions are applied to child tables on a very low level where the server cannot update STORED generated column values anymore. In other words, if a STORED generated column depends on a column that can be modified via a cascade action, this STORED column can become out of sync with other columns. Basically, it'll have an incorrect value. That's why such a table structure is no longer allowed.

          We're planning to lift this restriction. MDEV-31942 (or MDEV-22361) when implemented, will allow us to recalculate STORED generated columns on UPDATE CASCADE and SET NULL actions.

          Note that ON DELETE CASCADE is fine, it's allowed, because it doesn't modify individual columns, but deletes the complete row.

          VIRTUAL columns are also fine.

          serg Sergei Golubchik added a comment - It is intentional. The way MariaDB works, and always did, foreign key cascading actions are applied to child tables on a very low level where the server cannot update STORED generated column values anymore. In other words, if a STORED generated column depends on a column that can be modified via a cascade action, this STORED column can become out of sync with other columns. Basically, it'll have an incorrect value. That's why such a table structure is no longer allowed. We're planning to lift this restriction. MDEV-31942 (or MDEV-22361 ) when implemented, will allow us to recalculate STORED generated columns on UPDATE CASCADE and SET NULL actions. Note that ON DELETE CASCADE is fine, it's allowed, because it doesn't modify individual columns, but deletes the complete row. VIRTUAL columns are also fine.

          People

            serg Sergei Golubchik
            kihen Kim Henriksen
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.