bug: Query performance degrades when using Customize dynamic functions

[adofsauron]

DDL

CREATE TABLE `CUSTOM` (
  `USID` varchar(50) NOT NULL COMMENT '????',
  `PASSWORD` varchar(32) NOT NULL COMMENT '????',
  `LGTP` varchar(2) NOT NULL COMMENT '????',
  `USTP` varchar(2) NOT NULL COMMENT '????',
  `STATUS` varchar(2) NOT NULL COMMENT '??????',
  `USQX` varchar(20) NOT NULL COMMENT '????',
  `AUTOFAPIAO` int(11) NOT NULL COMMENT '????????????',
  `USDJ` int(11) DEFAULT NULL COMMENT '????',
  `ISZHIFUSELF` int(11) DEFAULT NULL COMMENT '?????????',
  `ZHIFUID` varchar(50) DEFAULT NULL COMMENT '????????',
  `ISAUDITSELF` int(11) DEFAULT NULL COMMENT '?????????',
  `AUDUSID` varchar(50) DEFAULT NULL COMMENT '????????',
  `TTLB` varchar(2) DEFAULT NULL COMMENT '????',
  `SUSID` varchar(50) DEFAULT NULL COMMENT '?????????',
  `CORPNAME` varchar(100) DEFAULT NULL COMMENT '????',
  `TOURLICENSECODE` varchar(50) DEFAULT NULL COMMENT '???????',
  `BUSINESSLICENSESCODE` varchar(50) DEFAULT NULL COMMENT '??????',
  `TOURLICENSEUPID` int(11) DEFAULT NULL COMMENT '????????',
  `BUSINESSLICENSESUPID` int(11) DEFAULT NULL COMMENT '??????',
  `LNAME` varchar(50) DEFAULT NULL COMMENT '?????',
  `ZJLB` varchar(2) DEFAULT NULL COMMENT '???????',
  `ZJHM` varchar(50) DEFAULT NULL COMMENT '????',
  `IREGIONALID` int(11) DEFAULT NULL COMMENT '???',
  `BANK` varchar(100) DEFAULT NULL COMMENT '????',
  `ACCOUNT` varchar(50) DEFAULT NULL COMMENT '????',
  `BNAME` varchar(100) DEFAULT NULL COMMENT '???',
  `ADDR` varchar(200) DEFAULT NULL COMMENT '??',
  `TELNO` varchar(50) DEFAULT NULL COMMENT '????',
  `MOBILE` varchar(50) DEFAULT NULL COMMENT '????',
  `FAXNO` varchar(50) DEFAULT NULL COMMENT '????',
  `QQ` varchar(50) DEFAULT NULL COMMENT 'QQ??',
  `MSN` varchar(50) DEFAULT NULL COMMENT 'MSN??',
  `POSTNO` varchar(6) DEFAULT NULL COMMENT '????',
  `EMAIL` varchar(50) DEFAULT NULL COMMENT 'email??',
  `LDATE` varchar(20) NOT NULL COMMENT '????',
  `LASTDATE` varchar(20) DEFAULT NULL COMMENT '??????',
  `LOGTIMES` varchar(50) DEFAULT NULL COMMENT '??????',
  `NOTEA` varchar(100) DEFAULT NULL,
  `NOTEB` text,
  `TIMES` int(11) DEFAULT NULL COMMENT '??????',
  `LMDATE` varchar(20) DEFAULT NULL COMMENT '??????????',
  `LPDATE` varchar(20) NOT NULL COMMENT '????????',
  `CDATE` varchar(20) DEFAULT NULL COMMENT '??????',
  `MMQT` varchar(4) DEFAULT NULL COMMENT '??????',
  `MMDA` varchar(100) DEFAULT NULL COMMENT '??????',
  `NOTE1` varchar(100) DEFAULT NULL,
  `NOTE2` varchar(100) DEFAULT NULL,
  `NOTE3` varchar(100) DEFAULT NULL,
  `NOTE4` varchar(100) DEFAULT NULL,
  `NOTE5` varchar(100) DEFAULT NULL,
  `NOTE6` varchar(100) DEFAULT NULL,
  `NOTE7` varchar(100) DEFAULT NULL,
  `NOTE8` varchar(100) DEFAULT NULL,
  `NOTE9` varchar(100) DEFAULT NULL,
  `NOTE10` varchar(100) DEFAULT NULL,
  `INOTE1` int(11) DEFAULT NULL,
  `INOTE2` int(11) DEFAULT NULL,
  `INOTE3` int(11) DEFAULT NULL,
  `INOTE4` int(11) DEFAULT NULL,
  `INOTE5` int(11) DEFAULT NULL,
  `INOTE6` int(11) DEFAULT NULL,
  `INOTE7` int(11) DEFAULT NULL,
  `INOTE8` int(11) DEFAULT NULL,
  `INOTE9` int(11) DEFAULT NULL,
  `INOTE10` int(11) DEFAULT NULL,
  `IBUSINESSID` int(11) NOT NULL COMMENT '??ID',
  `DAOYOUNO` varchar(50) DEFAULT NULL COMMENT '????',
  `DTMAKEDATE` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `PAYPASSWORD` varchar(32) DEFAULT NULL,
  `AGENTID` decimal(18,0) DEFAULT NULL,
  `OPENUSERID` varchar(100) DEFAULT NULL,
  `OPENID` varchar(100) DEFAULT NULL,
  `QRCODEURL` varchar(100) DEFAULT NULL,
  `PROMOTERID` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`USID`) USING BTREE
) ENGINE=TIANMU DEFAULT CHARSET=utf8;

Custom function

DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `FIND_TOP_USID`(PUSID VARCHAR(1000)) RETURNS varchar(1000) CHARSET utf8
BEGIN
DECLARE FINALVAR VARCHAR(100);
WHILE LENGTH(PUSID) > 0 DO 
SET FINALVAR = PUSID;
SELECT SUSID INTO PUSID FROM CUSTOM WHERE USID = FINALVAR;
IF FINALVAR = PUSID 
THEN SET PUSID = ''; 
END IF;
END WHILE;
RETURN FINALVAR;
END ;;
DELIMITER ;

The original slow SQL, whether Tianmu or Innodb, has not produced results for two hours

SELECT C.SUSID,
       C2.CORPNAME AS SCORPNAME,
       C2.BNAME,
       C.USID,
       C.CORPNAME,
       C.IBUSINESSID,
       C.TTLB
  FROM (SELECT USID,
               CORPNAME,
               IBUSINESSID,
               TTLB,
               FIND_TOP_USID(USID) AS SUSID
          FROM CUSTOM
         WHERE LGTP = '02'
           AND USTP = '01'
           AND IBUSINESSID = 2) C
  LEFT JOIN CUSTOM C2
    ON C2.USID = C.SUSID;

Manually rewritten SQL statements can produce results in milliseconds, regardless of Tianmu or Innodb

SELECT FIND_TOP_USID(C.USID) AS SUSID,
       C2.CORPNAME AS SCORPNAME,
       C2.BNAME,
       C.USID,
       C.CORPNAME,
       C.IBUSINESSID,
       C.TTLB
  FROM CUSTOM C
  LEFT JOIN CUSTOM C2
    ON C2.USID = C.SUSID
 WHERE C.LGTP = '02'
   AND C.USTP = '01'
   AND C.IBUSINESSID = 2;

Expected behavior

No response

How To Reproduce

No response

Environment

No response

Are you interested in submitting a PR to solve the problem?

• [x] Yes, I will!

[adofsauron]

mysql> SELECT C.SUSID,
    ->        C2.CORPNAME AS SCORPNAME,
    ->        C2.BNAME,
    ->        C.USID,
    ->        C.CORPNAME,
    ->        C.IBUSINESSID,
    ->        C.TTLB
    ->   FROM (SELECT USID,
    ->                CORPNAME,
    ->                IBUSINESSID,
    ->                TTLB,
    ->                FIND_TOP_USID(USID) AS SUSID
    ->           FROM CUSTOM
    ->          WHERE LGTP = '02'
    ->            AND USTP = '01'
    ->            AND IBUSINESSID = 2) C
    ->   LEFT JOIN CUSTOM C2
    ->     ON C2.USID = C.SUSID;
ERROR 1100 (HY000): Table 'CUSTOM' was not locked with LOCK TABLES