MySQL Bugs: #88741: Unnecessary AS clause is set for Expression of virtual column

Bug #88741	Unnecessary AS clause is set for Expression of virtual column
Submitted:	4 Dec 2017 9:53	Modified:	14 Dec 2018 8:09
Reporter:	高木祐介	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.7.19, 5.7.20	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	column name, virtual column

Description:
Unnecessary AS clause is set for Expression of virtual column

Expected Result
edit_distance(trim('"' from json_extract(`sample_json`,'$.Results[0].address')),"ABCD")

Bad Result
edit_distance(trim('"' from json_extract(`sample_json`,'$.Results[0].address')) AS `trim('"' from json_extract(``sample_json``,'$.Results[0].address'))`,'ABCD' AS `"ABCD"`)

How to repeat:
STEP1 CREATE TABLE

CREATE TABLE `tbl_virtual_sample` (
`id` int(11) NOT NULL,
`sample_json` json DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

STEP2 ADD Virtual Column( Expression Contain UDF function )
ALTER TABLE `tbl_virtual_sample` 
ADD COLUMN `virtual_Column` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci GENERATED ALWAYS AS (edit_distance(trim('"' from json_extract(`sample_json`,'$.Results[0].address')),"ABCD") ) VIRTUAL NULL AFTER `sample_json`;

*edit_distance is UDF function.
https://github.com/naoa/mysql-edit-distance

STEP3 SHOW DDL
SHOW CREATE TABLE `tbl_virtual_sample`;

Result
**********************************************************
Table	Create Table
tbl_virtual_sample	CREATE TABLE `tbl_virtual_sample` (
  `id` int(11) NOT NULL,
  `sample_json` json DEFAULT NULL,
  `virtual_Column` varchar(255) GENERATED ALWAYS AS (edit_distance(trim('"' from json_extract(`sample_json`,'$.Results[0].address')) AS `trim('"' from json_extract(``sample_json``,'$.Results[0].address'))`,'ABCD' AS `"ABCD"`)) VIRTUAL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Hello!

Thank you for the report and test case.
Quoting from our manual "Subqueries, parameters, variables, stored functions, and user-defined functions are not permitted." with that it should not have allowed to use at first place and later "AS" sounds odd too.

Thanks,
Umesh

Hi.Umesh Shastry!

I found it!thank you.
https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html

I misunderstood reading the Mysql Server Blog instead of the manual. The mechanism of UDF Virtual Columns is very convenient and it runs faster than Triger and Update, so patch it if possible and do not want to disable the function.

Mysql Server Blog
http://mysqlserverteam.com/virtual-columns-and-effective-functional-indexes-in-innodb/

Because it turned out that recognition was wrong, Close