时间戳

首页 > 解决方案 > 不稳定的“延迟”CTE 评估？

snowflake-cloud-data-platform - 不稳定的“延迟”CTE 评估？

问题描述

我观察到 CTE 的行为是我没想到的（而且似乎不一致）。不太确定它是否正确......

基本上，通过 CTE，我过滤行以避免特定问题，然后使用该 CTE 的结果来执行计算，这些计算会破坏我认为我在 CTE 中消除的有问题的行......

取一个带有 varchar 列的简单表，其中通常包含一个数字，但并非总是如此

CREATE TABLE MY_TABLE(ROW_ID      INTEGER NOT NULL
                    , GOOD_ROW    BOOLEAN NOT NULL
                    , SOME_VALUE  VARCHAR NOT NULL);

INSERT INTO MY_TABLE(ROW_ID, GOOD_ROW, SOME_VALUE)
    VALUES(1, TRUE, '1'), (2, TRUE, '2'), (3, FALSE, 'ABC');

我还创建了一个只有数字的小表加入

CREATE TABLE NUMBERS(NUMBER_ID INTEGER NOT NULL);
INSERT INTO NUMBERS(NUMBER_ID) VALUES(1), (2), (3);

在 SOME_VALUE 上加入这两个表会导致错误，因为 'ABC' 不是数字，并且似乎在 WHERE 子句之前评估了 JOIN （此处对性能的不良影响......）

SELECT *
  FROM MY_TABLE
  INNER JOIN NUMBERS ON NUMBERS.NUMBER_ID = TO_NUMBER(SOME_VALUE)
  WHERE ROW_ID < 3; --> ERROR

所以，我尝试通过 CTE 过滤我的第一个表，它只返回 SOME_VALUE 为数字的行

WITH ONLY_GOOD_ONES
AS (
  SELECT SOME_VALUE
    FROM MY_TABLE
    WHERE GOOD_ROW = TRUE
)
SELECT *
  FROM ONLY_GOOD_ONES;

现在，我希望能够使用此 CTE 的结果，其中 SOME_VALUE 为数字。

WITH ONLY_GOOD_ONES
AS (
  SELECT SOME_VALUE 
    FROM MY_TABLE
    WHERE GOOD_ROW = TRUE
)
SELECT *
  FROM ONLY_GOOD_ONES 
  INNER JOIN NUMBERS ON NUMBERS.NUMBER_ID = TO_NUMBER(SOME_VALUE);

奇迹！！！

有效！我得到了我的 2 条预期记录。到目前为止，一切都很好...

但是，如果我对 CTE 的定义略有不同（过滤相同记录的 WHERE 子句）

WITH ONLY_GOOD_ONES
AS (
  SELECT SOME_VALUE 
    FROM MY_TABLE
    WHERE ROW_ID < 3
)
SELECT *
  FROM ONLY_GOOD_ONES;

此 CTE 返回与以前完全相同的内容

但是，如果我尝试加入，它会失败！

WITH ONLY_GOOD_ONES
AS (
  SELECT * 
    FROM MY_TABLE
    WHERE ROW_ID < 3
)
SELECT *
  FROM ONLY_GOOD_ONES 
  INNER JOIN NUMBERS ON NUMBERS.NUMBER_ID = TO_NUMBER(SOME_VALUE);

我收到以下错误...

SQL 错误 [100038] [22018]：无法识别数值“ABC”

对 CTE 的第二个版本的行为不同有什么特别的解释吗？？？

标签： snowflake-cloud-data-platform

解决方案

实际答案是因为雪花不遵循 SQL 标准，而是按照给定的顺序执行 SQL。

当优化器决定需要时，它们会在过滤之前对数据应用转换。

MY_TABLE所以当你做的时候你的桌子

SELECT some_value::NUMBER FROM my_table WHERE row_id IN (1,2);

在某些情况下，您将在所有行上发生 as_number 转换，并在'ABC'. 这违反了 SQL 规则，即在 SELECT 转换完成之前对 WHERE 进行评估，但 Snowflake 多年来就知道这一点，这是故意的，因为它使事情运行得更快。

解决方案是了解您有混合数据，因此假设代码可以并且将会乱序运行，因此使用TRY_TO_NUMBER等函数的保护版本

关键是你可以编写一些嵌套的 SELECT 来避免这个问题，然后在代码周围放置一个窗口函数之类的东西，优化器会跳回这个行为，你的 SQL 会再次爆炸。因此，解决方案是了解您是否有混合数据并处理它。哦，抱怨这是一个错误。

推荐阅读