测试类型

SQL功能

测试版本

7.0.0LTS

问题描述

某存储过程在MySQL创建成功，但是在openGauss创建失败，存储过程内没有复杂语法

操作系统和硬件信息

openEuler release 20.03 (LTS-SP3)
Linux openGauss88 4.19.90-2112.8.0.0131.oe1.aarch64 #1 SMP Fri Dec 31 19:53:20 UTC 2021 aarch64 aarch64 aarch64 GNU/Linux

测试环境

极简版

被测功能

MySQL 兼容性

预置条件

无

操作步骤

step1. 在 openGauss B 库创建存储过程，以MySQL语法

delimiter //
CREATE PROCEDURE `db_replace`(
    IN `search` varchar(100),
    IN `replacement` varchar(100),
    IN `db_name` varchar(100)
)
BEGIN
    DECLARE t_name VARCHAR(50);
    DECLARE done int default 0;
    DECLARE cur CURSOR FOR SELECT DISTINCT table_name as name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema=db_name;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    OPEN cur;
    FETCH cur INTO t_name;
        WHILE (done<>1) DO
            call tbl_replace(search,replacement,db_name,t_name);
            FETCH cur INTO t_name;
        END WHILE;
END
//

delimiter ;

预期输出

step1. 成功

实际输出

step1. 失败，相同语句在MySQL 5.7 执行成功

日志信息

test_b=# delimiter //
SET
test_b=# CREATE PROCEDURE `db_replace`(
test_b(#     IN `search` varchar(100),
test_b(#     IN `replacement` varchar(100),
test_b(#     IN `db_name` varchar(100)
test_b(# )
test_b-# BEGIN
test_b-#     DECLARE t_name VARCHAR(50);
test_b-#     DECLARE done int default 0;
test_b-#     DECLARE cur CURSOR FOR SELECT DISTINCT table_name as name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema=db_name;
test_b-#     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
test_b-#     OPEN cur;
test_b-#     FETCH cur INTO t_name;
test_b-#         WHILE (done<>1) DO
test_b-#             call tbl_replace(search,replacement,db_name,t_name);
test_b-#             FETCH cur INTO t_name;
test_b-#         END WHILE;
test_b-# END
test_b-# //
ERROR:  syntax error at or near ""
LINE 1: DECLARE PRAGMA AUTONOMOUS_TRANSACTION;
        ^
QUERY:  DECLARE PRAGMA AUTONOMOUS_TRANSACTION;
oldId int:=0;objId int:=0;allNum int:=0;
begin
 select count(*) from dbe_pldeveloper.gs_source into allNum where nspid=2200 and name='db_replace' and type='procedure';if allNum > 0 then select id from dbe_pldeveloper.gs_source into oldId where nspid=2200 and name='db_replace' and type='procedure';objId := oldId; else objId := 0;end if;delete from DBE_PLDEVELOPER.gs_source where nspid=2200 and name='db_replace' and type = 'procedure';
delete from DBE_PLDEVELOPER.gs_source where nspid=2200 and name='db_replace' and type = 'procedure';
delete from DBE_PLDEVELOPER.gs_errors where nspid=2200 and name='db_replace' and type = 'procedure';
insert into DBE_PLDEVELOPER.gs_source values(objId, 10, 2200,'db_replace', 'procedure', 'f', $gssource$CREATE PROCEDURE `db_replace`(
    IN `search` varchar(100),
    IN `replacement` varchar(100),
    IN `db_name` varchar(100)
)
BEGIN
    DECLARE t_name VARCHAR(50);
    DECLARE done int default 0;
    DECLARE cur CURSOR FOR SELECT DISTINCT table_name as name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema=db_name;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    OPEN cur;
    FETCH cur INTO t_name;
        WHILE (done<>1) DO
            call tbl_replace(search,replacement,db_name,t_name);
            FETCH cur INTO t_name;
        END WHILE;
END
/$gssource$);
EXCEPTION WHEN OTHERS THEN NULL; 
end

提单组织

内部开发

测试代码