[MDEV-29777] Unable to create stored generated column with "CONCAT" and a "CHAR" column as function argument. Created: 2022-10-12  Updated: 2022-10-12  Resolved: 2022-10-12

Status: Closed
Project: MariaDB Server
Component/s: N/A
Affects Version/s: 10.9.3
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Juan Lago Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: bug, columns, community, generated
Environment:

OS: Linux Ubuntu 20.04.5 LTS
Kernel: 5.4.0-122-generic
MariaDB version: 10.9.3 (Community server)

Issue Links:
Relates
relates to MDEV-18156 Assertion `0' failed or `btr_validate... Closed

 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).

 Comments   
Comment by Juan Lago [ 2022-10-12 ]

An easy way to reproduce this issue is with the following SQL:

CREATE TABLE `test` (
 
  `id` int(11) NOT NULL AUTO_INCREMENT,
 
  `field1` varchar(10) DEFAULT NULL,
 
  `field2` char(10) DEFAULT NULL,
 
  `comb` varchar(20) GENERATED ALWAYS AS (concat(`field2`,'test')) STORED,
 
  PRIMARY KEY (`id`)
 
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4

Comment by Alice Sherepa [ 2022-10-12 ]

10.3-10.4 return the warning, that explains the problem - dependence on the sql_mode

CREATE or replace TABLE t( a char(10), b varchar(10) GENERATED ALWAYS AS (a)  STORED );
 
Warnings:
 
Warning	1901	Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `b`
 
Warning	1105	Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH

10.5-10.11 - error + warning (in MDEV-18156 it was decided to not break compatibility in GA releases, that is why 10.3 and 10.4 still allow creating such tables)

MariaDB [test]> CREATE or replace TABLE t( a char(10), b varchar(10) GENERATED ALWAYS AS (a)  STORED );
 
ERROR 1901 (HY000): Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `b`
 
Error (Code 1901): Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `b`
 
Warning (Code 1105): Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH

Comment by Juan Lago [ 2022-10-12 ]

@AliceSherepa: Thank you for the clarification.

I can see that trimming the var column doesn't raise any warning.

Workaround in case PAD_CHAR_TO_FULL_LENGTH mode is not changed:

CREATE TABLE `test` (
 
  `id` int(11) NOT NULL AUTO_INCREMENT,
 
  `field1` varchar(10) DEFAULT NULL,
 
  `field2` char(10) DEFAULT NULL,
 
  `comb` varchar(20) GENERATED ALWAYS AS (concat(TRIM(`field2`),'test')) STORED,
 
  PRIMARY KEY (`id`)
 
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

Generated at Thu Feb 08 10:11:13 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.