Snowflake存储过程中CONNECT BY语句PRIOR缺失错误排查

问题描述 #

尝试实现一个Snowflake存储过程，返回CONNECT BY查询的结果集，编写的存储过程代码如下：

create or replace procedure test(email varchar(100))
RETURNS TABLE (email_address varchar(100))
LANGUAGE SQL
AS
BEGIN
  let res RESULTSET := (WITH BASE AS (
            select 
                USER_ID
                , MANAGER_ID
                , EMAIL_ADDRESS
            from HIERARCHY 
            WHERE USER_ID <> MANAGER_ID
        ) 
        SELECT EMAIL_ADDRESS
        FROM BASE
        START WITH EMAIL_ADDRESS = :email
        CONNECT BY USER_ID = PRIOR MANAGER_ID
    );
  RETURN TABLE(res);
END;

运行存储过程时收到错误："PRIOR keyword is missing in Connect By statement"，但该查询在存储过程外可正常执行，返回结果如下：

解决方案 #

问题根源在于Snowflake SQL存储过程的静态SQL解析逻辑对CONNECT BY中的PRIOR关键字识别存在异常。改用**动态SQL（EXECUTE IMMEDIATE）**执行查询即可解决这个问题，修改后的存储过程代码如下：

create or replace procedure test(email varchar(100))
RETURNS TABLE (email_address varchar(100))
LANGUAGE SQL
AS
BEGIN
  let res RESULTSET := (EXECUTE IMMEDIATE $$
    WITH BASE AS (
            select 
                USER_ID
                , MANAGER_ID
                , EMAIL_ADDRESS
            from HIERARCHY 
            WHERE USER_ID <> MANAGER_ID
        ) 
        SELECT EMAIL_ADDRESS
        FROM BASE
        START WITH EMAIL_ADDRESS = ?
        CONNECT BY USER_ID = PRIOR MANAGER_ID
  $$ USING (:email));
  RETURN TABLE(res);
END;

说明 #

通过EXECUTE IMMEDIATE将查询转为动态执行模式，让Snowflake在运行时解析CONNECT BY语句，能够正确识别PRIOR关键字的上下文，避免静态解析阶段的误判。

内容的提问来源于stack exchange，提问作者crkuchlenz