mysql兼容，500函数返回结果与mysql不同

【请认真填写以下信息，否则可能由于无法定位，导致issue无法解决而被取消】

【标题描述】:
【测试类型：SQL功能/存储功能/接口功能/工具功能/性能/并发/压力长稳/故障注入/安全/资料/编码规范】【测试版本：2.0.0】 问题描述

【操作系统和硬件信息】(查询命令: cat /etc/system-release, uname -a):
Linux gbase8c-5-110 3.10.0-1127.el7.x86_64 #1 SMP Tue Mar 31 23:36:51 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux

CentOS Linux release 7.8.2003 (Core)

【测试环境】(单机/1主x备x级联备):500单机

【被测功能】:mysql 兼容性

【测试类型】:

【数据库版本】(查询命令: gaussdb -V):
gaussdb (single_node GBase8cV5 S5.0.0B09 build 5054ff66) compiled at 2023-06-12 15:08:36 commit 0 last mr 182
【预置条件】:
mysql兼容模式
【操作步骤】(请填写详细的操作步骤):
1、创建表
DROP TABLE IF EXISTS `assetbuy`;
CREATE TABLE `assetbuy`  (
  `ID` int NOT NULL AUTO_INCREMENT,
  `TITLE` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '采购标题',
  `APPLYCODE` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '????',
  `APPLYREASON` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL COMMENT '????',
  `BUYTYPE` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '采购类型',
  `FUND` varchar(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '资金来源：预算内I、预算外O',
  `APPLYORGANID` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '????ID',
  `APPLYORGANNAME` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '??????',
  `APPLYDEPTID` varchar(22) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '????',
  `APPLYDEPTNAME` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '????',
  `APPLYAMONT` decimal(11, 2) NULL DEFAULT NULL COMMENT '????',
  `OPUSERID` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '发起人',
  `OPUSERNAME` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '发起人姓名',
  `OPTIME` datetime NULL DEFAULT NULL COMMENT '发起时间',
  `FLOWSTATUS` varchar(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '流转状态：未提交U，被回退B，撤消C，审批中F，失败N，生效Y',
  `CURTASKNO` decimal(11, 0) NULL DEFAULT NULL COMMENT '当前关',
  `CURTASKNAME` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '当前审核步骤（未开始，...,结束)',
  `BUYCON` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL COMMENT '采购内容',
  `BUDGETAMONT` decimal(11, 2) NULL DEFAULT NULL COMMENT '预算金额',
  `FILENAME` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '附件',
  `OLDFILE` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '附件原名',
  `RESULTS` varchar(200) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '执行结果',
  `BUYTIME` datetime NULL DEFAULT NULL COMMENT '采购时间',
  `REALITYAMONT` decimal(11, 2) NULL DEFAULT NULL COMMENT '实际采购金额',
  `CUSTOMFILED1` varchar(200) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '自定义字段1',
  `CUSTOMFILED2` varchar(200) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '自定义字段2',
  `CUSTOMFILED3` varchar(200) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '自定义字段3',
  `CUSTOMFILED4` varchar(200) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '自定义字段4',
  `CUSTOMFILED5` varchar(200) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '自定义字段5',
  `CUSTOMFILED6` varchar(200) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '自定义字段6',
  `CUSTOMFILED7` varchar(200) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '自定义字段7',
  `CUSTOMFILED8` varchar(200) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '自定义字段8',
  `CUSTOMFILED9` varchar(200) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '自定义字段9',
  `CUSTOMFILED10` varchar(200) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '自定义字段10',
  `CUSTOMFILED11` varchar(200) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '自定义字段11',
  `CUSTOMFILED12` varchar(200) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '自定义字段12',
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 26 CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = Dynamic;

2、创建函数
CREATE OR REPLACE FUNCTION "public"."getflowserialnum"("codeno" varchar, "tablename" varchar, "ymd" varchar, "applycode" varchar)
  RETURNS "pg_catalog"."void" AS $BODY$ BEGIN_B_PROC 
set @cd=CONCAT('SELECT
CASE  WHEN  (select ifnull( (SELECT IFNULL(',applyCode,', "")    FROM ',tableName,'   WHERE SUBSTRING(',applyCode,', 1, (8+LENGTH("',codeNo,'"))) = CONCAT("',codeNo,'",DATE_FORMAT(NOW(), "',ymd,'"))  ORDER BY id DESC LIMIT 1  ),-1) from  dual)=-1   THEN
   CONCAT("',codeNo,'",DATE_FORMAT(NOW(), "',ymd,'"),  LPAD((0 + 1), 5, "0"))
ELSE
  (select CONCAT("',codeNo,'",DATE_FORMAT(NOW(), "%Y%m%d"),LPAD((CONVERT(SUBSTRING(',applyCode,', -5), DECIMAL) + 1), 5, "0")) from  ',tableName,' ORDER BY id DESC LIMIT 1  )
END
 ');

END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

3、执行
call  getFlowSerialNum('ZCCG','ASSETBUY','%Y%m%d','applyCode');
【预期输出】:
![输入图片说明](https://foruda.gitee.com/images/1692756995360497058/93cab4fd_9100768.png "屏幕截图")
【实际输出】:
无返回
【原因分析】:
1. 这个问题的根因
2. 问题推断过程
3. 还有哪些原因可能造成类似现象
4. 该问题是否有临时规避措施
5. 问题解决方案
6. 预计修复问题时间

【日志信息】(请附上日志文件、截图、coredump信息):

【测试代码】:

openGauss/openGauss-server

内容风险标识

评论 (3)

openGauss/openGauss-server .gitee-modal { width: 500px !important; }

内容风险标识