Snowflake WHERE EXISTS子句COALESCE引用外层列报错，是Bug还是功能限制？

核心原因 #

这是Snowflake未在公开文档明确标注的关联子查询功能限制：当EXISTS子句的过滤条件依赖外层表和子查询表列的COALESCE运算结果时，Snowflake的SQL编译引擎会抛出SQL compilation error: Unsupported subquery type cannot be evaluated错误，该语法在SQL Server、Postgres等主流SQL方言中均支持，不属于用户写法错误。

问题复现代码 #

CREATE OR REPLACE TEMPORARY TABLE Employee (
   Emp_SK INT NOT NULL
);

CREATE OR REPLACE TEMPORARY TABLE Employee_X_Pay_Rate (
   Emp_SK INT NOT NULL, Pay_Rate_SK INT NOT NULL, Start_Date TIMESTAMP_NTZ NOT NULL, End_Date TIMESTAMP_NTZ NOT NULL
);

CREATE OR REPLACE TEMPORARY TABLE Employee_X_Location (
   Emp_SK INT NOT NULL, Location_SK INT NOT NULL, Start_Date TIMESTAMP_NTZ NOT NULL, End_Date TIMESTAMP_NTZ NULL
);
INSERT INTO Employee
VALUES (1);

INSERT INTO Employee_X_Pay_Rate 
VALUES 
    (1, 1, '2018-01-01', '2019-03-31')
   ,(1, 2, '2019-04-01', '2021-03-31')
   ,(1, 3, '2021-04-01', '2099-12-31')
;

INSERT INTO Employee_X_Location
VALUES
    (1, 101, '2018-01-01', '2019-12-31')
   ,(1, 102, '2020-01-01', '2020-12-31')
   ,(1, 103, '2021-01-01', NULL)
;
SET Asof_Date = TO_DATE('2021-05-31', 'yyyy-mm-dd'); -- 改为TO_TIMESTAMP也无法解决问题
SELECT 
   emp.Emp_SK
   ,empPay.Pay_Rate_SK
   ,$Asof_Date AS Report_Date
   ,empPay.Start_Date AS Pay_Start_Date
   ,empPay.End_Date AS Pay_End_Date
FROM Employee emp
   INNER JOIN Employee_X_Pay_Rate empPay
      ON emp.Emp_SK = empPay.Emp_SK
      AND $Asof_Date BETWEEN empPay.Start_Date AND empPay.End_Date
WHERE EXISTS (
   SELECT 1 FROM Employee_X_Location empLoc
   WHERE emp.Emp_SK = empLoc.Emp_SK
      -- 问题出在这一行：empLoc.End_Date允许为空
      AND $Asof_Date BETWEEN empLoc.Start_Date AND COALESCE(empLoc.End_Date, empPay.End_Date)
);

可用解决方案 #

将触发错误的COALESCE写法替换为以下任意一种即可正常运行：

• 方案1：将COALESCE逻辑展开为等价的分支判断

AND (
   $Asof_Date >= empLoc.Start_Date
   AND ($Asof_Date <= empLoc.End_Date OR (empLoc.End_Date IS NULL AND $Asof_Date <= empPay.End_Date))
)

• 方案2：移除COALESCE中引用的外层表列，改为常量或固定函数返回值

AND $Asof_Date BETWEEN empLoc.Start_Date AND COALESCE(empLoc.End_Date, CURRENT_DATE)

内容的提问来源于stack exchange，提问作者Nicole L.