MySQL Bugs: #104294: [REGRESSION] Unexpected escaped string returned in GENERATION

Bug #104294	[REGRESSION] Unexpected escaped string returned in GENERATION_EXPRESSION
Submitted:	13 Jul 2021 9:04	Modified:	16 Jul 2021 6:35
Reporter:	Laurents Meyer (OCA)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S2 (Serious)
Version:	8.0.25, 8.0.11	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
When using strings in generated expressions in MySQL 8.0.25 (also tested in 
 MySQL 8.0.15), the outer quotes of the string gets escaped:

MySQL 8.0.25:
concat(`firstname`,_utf8mb4\' \',`lastname`)

MySQL 5.7.34:
concat(`firstname`,_utf8mb4' ',`lastname`)

This is not an issue in MySQL 5.7.

It is a breaking change and seems not documented anywhere.
It also does not seem to make any sense (if this has been a deliberate choice, please explain what it solves).

We need to know the exact version, where this issue appears first and we need this fixed for future releases.

How to repeat:
drop database if exists `generated_expression_issue`;
create database `generated_expression_issue`;
use `generated_expression_issue`;

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(150) NOT NULL,
  `lastname` varchar(150) NOT NULL,
  `fullname` varchar(301) GENERATED ALWAYS AS (concat(`firstname`, _utf8mb4' ', `lastname`)) VIRTUAL,
  PRIMARY KEY (`id`)
);

SELECT
    `COLUMN_NAME`,
    `ORDINAL_POSITION`,
    `GENERATION_EXPRESSION`
FROM
    `INFORMATION_SCHEMA`.`COLUMNS`
WHERE
    `TABLE_SCHEMA` = SCHEMA()
AND
    `TABLE_NAME` = 'user'
ORDER BY
    `ORDINAL_POSITION`;

Suggested fix:
Return strings which outer quotes are not escaped, as has been done in previous versions.

Hello Laurents,

Thank you for the report and feedback.

regards,
Umesh

Looks like this bug is a duplicate of "Bug#100607-mysql escapes output where it shouldn't. Breaks schema generation tools". Hence, updated status to "Duplicate".