主页 / dba / 问题 / 294319
Accepted
Doug Coats
Asked: 2021-06-16 10:18:53 +0800 CST

嵌套 CTE 在某些情况下返回不正确的结果

  • 772

好的，首先让我说我从以下方面得到了这个问题：

我试图帮助找出问题，但在尝试一次调试代码时被难住了。我知道问题是由于嵌套的 CTE（因为在调试过程中，如果您将每个步骤（也称为 cteX）转储到临时表中，则会获得正确的结果）但不知道它们是如何“在幕后”工作的，我无法在外面以合理的方式解释它“它不工作哟。” 我怀疑这与编译器如何在运行时同时评估它们有关，但没有更多上下文我不能肯定地说。

我的问题只是试图了解它们如何在幕后工作以及它与这种情况有何关系。现在我参与其中，我只想了解这个问题，以便将来可以与它交谈并同时学习一些有趣的东西。

在这里回答的人也可以在 SO 上发帖并在那里回答。

代码设置：

declare @t1 TABLE (ID varchar(max),Action varchar(max), DateTime datetime );
INSERT INTO @t1
Select *
from
(
VALUES 
('w2337','Open','2020-11-06 12:28:10.000'),
('w2337','Hold','2021-06-14 14:50:59.000'),
('w2337','Open','2021-06-14 14:51:26.000'),
('w2337','Hold','2021-06-15 14:50:59.000'),
('w2337','Open','2021-06-17 14:51:26.000'),
('w2337','Open','2021-06-18 14:51:26.000')

) t (ID, Action, DateTime);

with cte1 as (
select [ID],[Action],[DateTime]
,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as [RegIndex]
,DENSE_RANK () OVER (ORDER BY ID) as [Index by ID]
,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [DateTime]) as [Index by DateTimeID]
,CASE when [Action]='Hold' then ROW_NUMBER() OVER (PARTITION BY ID,Action ORDER BY DateTime) end as [TimesHeld]
FROM @t1 
)
,cte2 as (
select *, MAX([TimesHeld]) OVER (PARTITION BY ID ORDER BY RegIndex ROWS UNBOUNDED PRECEDING) as [FD] from cte1 
)
,cte3 as(
select *, CASE when [Action]='Open' then ROW_NUMBER() OVER (PARTITION BY ID,Action ORDER BY DateTime) end as [TimesOpened]
from cte2 
where FD is not null
)
select 
    a.*, ' ' as thing, b.DateTime  -- b.*   alternating between the direct column versus all is the issue
from cte3 a 
LEFT OUTER JOIN cte3 b
ON a.ID=b.ID and a.TimesHeld=b.TimesOpened 
where a.TimesHeld is not null and b.TimesOpened is not null

细节：

在编译下面的查询时，在最后的 LEFT OUTER JOIN 中，如果您选择 b.*，您会得到正确的结果。但是，如果您只选择一列（例如日期时间），则结果不正确。

正确的：

在此处输入图像描述

不正确：

在此处输入图像描述

sql-server cte

2 个回答

  1. Best Answer

    问题不在于结果不正确，而在于ROW_NUMBER窗口函数的非确定性排序。

    我将参考 Itzik Ben-Gan 的精彩文章，有空的时候一定要完整查看：Row numbers with nondeterministic order

    RegIndex在 cte1 中计算的方式明显是不确定的：

    ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as [RegIndex]

    通过产生常量值的子查询进行排序，让优化器知道这里的顺序无关紧要：

    ...使用此解决方案，SQL Server 的优化器认识到没有排序相关性，因此不会强加不必要的排序或将存储引擎的选择限制为必须保证顺序的选择

    此后，FD在 cte2 中基于RegIndex，因此也是非确定性的：

    MAX([TimesHeld]) OVER (PARTITION BY ID ORDER BY RegIndex ROWS UNBOUNDED PRECEDING) as [FD]

    最后，cte3 中的查询由 的值过滤FD：

    where FD is not null

    最终结果是，根据查询的优化方式，一组不同的行最终会出现在 cte3 中。并且列表中包含的列的数量和数据类型SELECT绝对会影响查询的优化方式，从而导致此处的结果不同。

    • 25

  2. cte1当第一个 cte ( ) 使用时，您如何期望确定性结果：

    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as RegIndex

    然后在以下 CTE 中使用 Regindex 列？

    OVER (ORDER BY (SELECT NULL))基本上对优化器说“选择你喜欢的任何顺序”并给我行号。

    可以很好地预料，下次运行它时，您可能会（如您所做的那样）得到不同的订单——因为你再次说“选择任何你喜欢的订单”——因此会得到不同的结果。

    • 12

相关问题