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
-