"Unsupported subquery type cannot be evaluated"在雪花 UDF 中使用两种不同的日期比较

Question

在Snowflake中，当我这样使用UDF时，我会得到上面的错误(在完全合法的查询中(：

SELECT
dd.date,
person_count(dd.date) AS cnt
FROM dim_date dd
WHERE dd.date BETWEEN '2019-11-01' and '2019-11-07'

本质上，这是有效的：

CREATE OR REPLACE FUNCTION person_count(d date)
RETURNS number 
AS 
$$
SELECT COUNT(DISTINCT person_id) 
FROM persons
WHERE (deceased_date > d)
$$;

这就产生了错误：

CREATE OR REPLACE FUNCTION person_count(d date)
RETURNS number 
AS 
$$
SELECT COUNT(DISTINCT person_id) 
FROM persons
WHERE (deceased_date = '1901-01-01' OR deceased_date > d)
$$;

我已尝试在日期字符串上使用TO_DATE。我尝试过使用deceased_date < '1901-01-02'，并尝试过将所有日期转换为字符串。似乎什么都不管用。我想这可能是个bug。

Answer 1

同意第一个代码有效，

create or replace table persons (person_id number, deceased_date date);
insert into persons values (1,'2019-10-01'),(1,'2019-09-01'),(2,'1901-01-01'),(3,'2019-11-04');
SELECT COUNT(DISTINCT person_id) 
FROM persons
WHERE (deceased_date = '1901-01-01' OR deceased_date > '2019-11-04');
CREATE OR REPLACE FUNCTION person_count(d date)
RETURNS number 
AS 
$$
SELECT COUNT(DISTINCT person_id) 
FROM persons
WHERE (deceased_date > d)
$$;
select column1 as date
,person_count(column1) as cnt 
from values ('2019-11-01'), ('2019-11-02'), ('2019-11-03'), ('2019-11-04'), ('2019-11-05'), ('2019-11-06'), ('2019-11-07')
order by 1;

但这些替代方案并非如此。创建或替换函数person_count(d日期(退货编号作为$$选择计数(DISTINCT person_id(发件人其中(decased_date="1901-01-01"或decased_date>d($$；

CREATE OR REPLACE FUNCTION person_count(d date)
RETURNS number 
AS 
$$
SELECT COUNT(DISTINCT person_id)
FROM (
SELECT person_id FROM PERSONS WHERE deceased_date > d
UNION 
SELECT person_id FROM PERSONS WHERE deceased_date = '1901-01-01'
)
$$;  

原因是，当他们扩展FUNCTION时，他们会将其变成一个相关的子查询(即使它可以作为JOIN完成(，并且只能处理非常简单的相关子查询，而您无法控制这一点。当我们想使用TABLE FUNCTIONS作为查找表时，我们已经碰到了这个问题。在一个地方，我们只是推出了一个CASE语句来避免查找(gross(，在另一个地方我们使用Javascript UDF来进行查找。

但它从根本上是一个错误，所以我会报告它

Answer 2

问题是在子查询支持中OR谓词存在一些限制。

Answer 3

我认为UDF与错误无关。Snowflake似乎在查询解析过程中尽可能地进行语法替换，在这种情况下，它的结果是：

SELECT
dd.date,
(SELECT COUNT(DISTINCT person_id) 
FROM persons
WHERE (deceased_date = '1901-01-01' OR deceased_date > dd.date)) AS cnt
FROM dim_date dd
WHERE dd.date BETWEEN '2019-11-01' and '2019-11-07';

这会产生完全相同的错误。正如Seeling Cheung所提到的，只有当存在多个WHERE条件时，这才会失败。

对于Snowflake来说，使用子查询(相关或不相关(和多条件过滤的查询解析似乎是一个持续的问题，例如Date Cast中的Bug。。。