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

Unable to create stored generated column with "CONCAT" and a "CHAR" column as function argument.

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Not a Bug
    • Affects Version/s: 10.9.3
    • Fix Version/s: N/A
    • Component/s: N/A
    • Environment:
      OS: Linux Ubuntu 20.04.5 LTS
      Kernel: 5.4.0-122-generic
      MariaDB version: 10.9.3 (Community server)

      Description

      I found that for some reason when I try to create a stored generated column that concatenate a "char" column the following error is raised by MariaDB:

      Function or expression 'concat(`field2`,'test')' cannot be used in the GENERATED ALWAYS AS clause of `comb`
       
      Error code 1901.
      

      I cannot reproduce this bug with MariaDB 10.3.36 in Debian 10. so maybe is only an issue with the new versions of MariaDB.

      Steps to reproduce:

      1. Create the following table:

      CREATE TABLE `test` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `field1` varchar(10) CHARACTER SET latin1 DEFAULT NULL,
        `field2` char(10) CHARACTER SET latin1 DEFAULT NULL,
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
      

      2. Add one record to the table:

      INSERT INTO `test` SET `id` = default, `field1` = 'field1', `field2` = 'field2';
      

      3. Try to add the following stored generated column:

      ALTER TABLE `test` 
      ADD COLUMN `comb` VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_general_ci GENERATED ALWAYS AS 
      (CONCAT(field2,'test')) stored;
      

      Received result:

      The following error code is received:

      Error code 1901.
      

      Expected result:

      MariaDB should allow to create the stored generated column in the same way that is possible to do using Maria 10.3.36 or using a virtual generated column like the following example:

      ALTER TABLE `test` 
      ADD COLUMN `comb` VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_general_ci GENERATED ALWAYS AS 
      (CONCAT(field2,'test')) VIRTUAL;
      

      It also works when use "CONCAT" with a "varchar" column, like the following example:

      ALTER TABLE `test` 
      ADD COLUMN `comb` VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_general_ci GENERATED ALWAYS AS 
      (CONCAT(field1,'test')) STORED;
      

      Notes:
      I can also reproduce this issue with a different collation (utf8mb4_general_ci) and charset (utf8mb4).

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              juanparati Juan Lago
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.