Details
Major 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
- relates to
-
MDEV-18156 Assertion `0' failed or `btr_validate_index(index, 0, false)' in row_upd_sec_index_entry or error code 126: Index is corrupted upon DELETE with PAD_CHAR_TO_FULL_LENGTH
-
- Closed
-
Activity
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
|
@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; |
An easy way to reproduce this issue is with the following SQL: