Bug #105277 The user-defined function failed as the second parameter query of the FIND_IN_SE
Submitted: 20 Oct 2021 10:18 Modified: 21 Oct 2021 13:37
Reporter: chwest cheng Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: User-defined functions ( UDF ) Severity:S3 (Non-critical)
Version:8.0.23 OS:CentOS (Centos7)
Assigned to: CPU Architecture:x86 (x86_64)

[20 Oct 2021 10:18] chwest cheng
Description:
1、origin

For MySQL version 8.0.23, a timeout error is reported when the following SQL is executed. Through the slow log, find the SQL that reported the error. SQL is as follows:

SELECT * FROM t_dict WHERE FIND_IN_SET(id,(SELECT getDictChildrenNew(
        (select id from t_dict where code = 'metadata_classify' 
     and root_code = 'metadata_classify' ),
        (select code from t_dict where code = 'metadata_classify' 
     and root_code = 'metadata_classify' ),
        (select root_code from t_dict where code = 'metadata_classify' 
     and root_code = 'metadata_classify' )
        )))
        order by dict_level,dict_order asc;

The structure of the table t_dict:

CREATE TABLE `t_dict` (
  `id` decimal(10,0) NOT NULL ,
  `parent_code` varchar(255) DEFAULT NULL ,
  `parent_name` varchar(255) DEFAULT NULL ,
  `code` varchar(255) DEFAULT NULL ,
  `dict_name` varchar(255) DEFAULT NULL ,
  `dict_value` varchar(255) DEFAULT NULL ,
  `company_id` varchar(255) DEFAULT NULL ,
  `status` tinyint DEFAULT NULL ,
  `dict_sort` decimal(10,0) DEFAULT '0' ,
  `deleted` tinyint DEFAULT NULL ,
  `remark` varchar(255) DEFAULT NULL ,
  `root_code` varchar(255) DEFAULT NULL ,
  `enable_expand` tinyint DEFAULT '1' ,
  `dict_level` bigint DEFAULT '1' ,
  `level_code` varchar(255) DEFAULT NULL ,
  `level_remark` varchar(255) DEFAULT NULL ,
  `dict_order` decimal(10,0) DEFAULT NULL ,
  `default_num` decimal(10,0) DEFAULT NULL ,
  `dict_type` varchar(255) DEFAULT NULL ,
  `create_date` datetime DEFAULT CURRENT_TIMESTAMP ,
  `create_user` varchar(64) DEFAULT NULL ,
  `create_ip` varchar(64) DEFAULT NULL ,
  `update_date` datetime DEFAULT NULL ,
  `update_user` varchar(64) DEFAULT NULL ,
  `update_ip` varchar(64) DEFAULT NULL ,
  `inner_code` varchar(255) DEFAULT NULL ,
  `serial_num` varchar(500) DEFAULT NULL ,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

This SQL uses a custom function getDictChildrenNew. The function content is as follows:

CREATE DEFINER=`user_name`@`%` FUNCTION `user_name`.`getDictChildrenNew`(iid varchar(255),icode varchar(255),ircode varchar(255)) RETURNS varchar(5000) CHARSET utf8mb4
    READS SQL DATA
BEGIN
DECLARE oTemp VARCHAR(5000);
DECLARE oTempChild VARCHAR(5000);
DECLARE oTempId VARCHAR(5000);
DECLARE oRootCode VARCHAR(5000);

SET oTemp = '';
SET oTempChild = icode;
SET oTempId = iid;
SET oRootCode = ircode;

WHILE oTempChild IS NOT NULL
DO
SET oTemp = CONCAT(oTemp,',',oTempId);

SELECT GROUP_CONCAT(ID) INTO oTempId
FROM t_dict
WHERE ROOT_CODE = oRootCode AND  FIND_IN_SET(PARENT_CODE,oTempChild);

SELECT GROUP_CONCAT(CODE) INTO oTempChild
FROM t_dict
WHERE ROOT_CODE = oRootCode AND FIND_IN_SET(PARENT_CODE,oTempChild);

END WHILE;
RETURN oTemp;
END

2、Test phenomenon
In my environment, the SQL that takes the second input parameter of FIND_IN_SET alone only needs to be executed, and the results can be run normally, and it only takes 122ms

SELECT getDictChildrenNew(
        (select id from t_dict where code = 'metadata_classify' #58
     and root_code = 'metadata_classify' ),
        (select code from t_dict where code = 'metadata_classify' #metadata_classify
     and root_code = 'metadata_classify' ),
        (select root_code from t_dict where code = 'metadata_classify' #metadata_classify
     and root_code = 'metadata_classify' )
        )

Or I can pass the value of this query as a string as the second parameter of FIND_IN_SET and execute it quickly. It only takes 79ms.

But if you execute the above complete SQL alone, there will be a timeout error.

The strange thing is that the same amount of data, table structure, operating system environment, this SQL has no problems on MySQL 8.0.18. Can calculate the result quickly and normally

How to repeat:
This exception occurs when the SQL in 1 above is executed in MySQL8.0.23. MySQL8.0.18 will not

Suggested fix:
Revert to be consistent with the MySQL8.0.18 version
[21 Oct 2021 13:37] MySQL Verification Team
Hi Mr. cheng,

Thank you for your bug report.

However, we can not yet further process your report for several reasons.

First of all, you are using and old release of the version 8.0. Please use 8.0.26 or 8.0.27 and let us know if you observe any changes.

Second, it is not clear from your test what kind of error are you writing about and what kind of exception are you oberving. This is vital for the processing of the report.

Next, your category is a user defined function and all that we see is one stored function, not user defined function.

Next, you are complaining about the timeout ..... Timeout could be a consequence of the badly written stored function and you have not supplied us with sufficient information. There are 16 (sixteen) different timeouts on MySQL 8.0 and we do not know which timeout do you get !!!!!

Next, timeout can be easily be a consequence of the badly written stored function. We. observe that you have written a loop that could be an eternal loop. The condition in WHILE could never resolve in FALSE, so you have got an always-running code. 

This is a forum for the bugs in MySQL server and utilities and not about bugs in the application's stored functions.