Snowflake传递结构化类型数组至存储过程遇类型不兼容错误

问题现象 #

测试Snowflake的CREATE TYPE特性，创建结构化类型MVE_RANGE后，将其数组类型传入存储过程时触发如下异常：

Uncaught exception of type 'STATEMENT_ERROR' on line 11 at position 4 : SQL compilation error: incompatible types: [UNKNOWN] and [STRUCTURED_OBJECT]

通过SHOW PROCEDURES确认存储过程参数类型正确，但调用时ARRAY类型似乎丢失，相关代码如下：

CREATE OR REPLACE SCHEMA MVE;

CREATE OR REPLACE TYPE MVE_RANGE AS OBJECT (
    RANGE_START INTEGER,
    RANGE_END   INTEGER
);

CREATE OR REPLACE PROCEDURE TEST_MVE_ARRAY(A ARRAY(MVE_RANGE))
RETURNS INTEGER
LANGUAGE SQL
AS
BEGIN
    RETURN ARRAY_SIZE(:A);
END;

DECLARE
    A ARRAY(MVE_RANGE NOT NULL) DEFAULT ARRAY_CONSTRUCT()::ARRAY(MVE_RANGE);
    R INTEGER;
BEGIN
    SELECT ARRAY_APPEND(
               ARRAY_CONSTRUCT()::ARRAY(MVE_RANGE),
               {'RANGE_START':1, 'RANGE_END':2}::MVE_RANGE
           )
    INTO   :A;
    A:=A::ARRAY(MVE_RANGE);
    R := (CALL TEST_MVE_ARRAY((:A)::ARRAY(MVE_RANGE)));
    RETURN :R;
END;

问题原因 #

在SQL存储过程中，使用R := (CALL ...)这种赋值方式时，Snowflake无法正确推断结构化类型数组的具体类型，导致参数被识别为UNKNOWN类型，与存储过程期望的STRUCTURED_OBJECT数组不兼容。

解决方案 #

改用CALL ... INTO的语法来接收存储过程的返回值，这种方式能正确保留参数的类型信息：

修正后的完整代码：

CREATE OR REPLACE SCHEMA MVE;

CREATE OR REPLACE TYPE MVE_RANGE AS OBJECT (
    RANGE_START INTEGER,
    RANGE_END   INTEGER
);

CREATE OR REPLACE PROCEDURE TEST_MVE_ARRAY(A ARRAY(MVE_RANGE))
RETURNS INTEGER
LANGUAGE SQL
AS
BEGIN
    RETURN ARRAY_SIZE(:A);
END;

DECLARE
    A ARRAY(MVE_RANGE NOT NULL) DEFAULT ARRAY_CONSTRUCT()::ARRAY(MVE_RANGE);
    R INTEGER;
BEGIN
    SELECT ARRAY_APPEND(
               ARRAY_CONSTRUCT()::ARRAY(MVE_RANGE),
               {'RANGE_START':1, 'RANGE_END':2}::MVE_RANGE
           )
    INTO   :A;
    -- 改用CALL ... INTO语法接收返回值
    CALL TEST_MVE_ARRAY(:A) INTO :R;
    RETURN :R;
END;

也可以简化变量初始化和赋值步骤，去掉多余的类型转换，代码依然正常运行：

DECLARE
    A ARRAY(MVE_RANGE NOT NULL) := ARRAY_CONSTRUCT({'RANGE_START':1, 'RANGE_END':2}::MVE_RANGE);
    R INTEGER;
BEGIN
    CALL TEST_MVE_ARRAY(:A) INTO :R;
    RETURN :R;
END;

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