Bug #104294 [REGRESSION] Unexpected escaped string returned in GENERATION_EXPRESSION
Submitted: 13 Jul 9:04 Modified: 16 Jul 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

[13 Jul 9:04] Laurents Meyer
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.
[13 Jul 9:09] MySQL Verification Team
Hello Laurents,

Thank you for the report and feedback.

regards,
Umesh
[16 Jul 6:35] Praveenkumar Hulakund
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".