此公用表表达式的结果被评估多少次？ [英] How many times are the results of this common table expression evaluated?

问题描述

我正在尝试解决我们在最后一次测试迭代中发现的错误。它涉及使用公用表表达式的查询。该查询的主要主题是它模拟第一聚合操作（获取此分组的第一行）。

问题是查询似乎选择了在某些情况下完全任意行-返回同一组中的多行，某些组只是完全消除。但是，它总是选择正确的行数。

我创建了一个最小的示例以在此处发布。有客户和地址，以及一个定义它们之间关系的表。这是我正在查看的实际查询的很多简化版本，但是我相信它应该具有相同的特征，并且它是一个很好的示例，可以用来解释我认为出了什么问题。

 创建表[Client]（ClientID int，名称varchar（20））
 CREATE TABLE [地址]（AddressID int，街道varchar（20））
创建表[ClientAddress]（ClientID int，AddressID int）
 
 INSERT [Client]值（1，'Adam'）
 INSERT [客户] VALUES（2，布莱恩）
 INSERT [客户]价值观（3， Charles）
 INSERT [客户]价值（4， Dean）
 INSERT [客户]值（5，'Edward'）
 INSERT [客户]值（6，'Frank'）
 INSERT [客户]值（7，'Gene'）
 INSERT [客户]值（ 8，'Harry'）
 
 INSERT [地址] VALUES（1，'橡子街'）
 INSERT [地址] VALUES（2，'Birch Road'）
 INSERT [地址] VALUES（3，科克大道）
 INSERT [地址] VALUES（4， Derby Grove）
 INSERT [地址] VALUES（5， Evergreen Drive） 
 INSERT [地址]值（6，'Fern Close'）
 
 INSERT [ClientAddress]值（1、1）
 INSERT [ClientAddress]值（1、3）
 INSERT [ClientAddress]值（2，2）
 INSERT [ClientAddress]值（2，4）
 INSERT [ClientAddress]值（2，6）
 INSERT [ClientAddress]值（3，3）
 INSERT [客户地址]值（3，5）
 INSERT [客户地址]值（3，1）
 INSERT [客户地址]值（4，4）
 INSERT [ClientAddress]值（4，6）
 INSERT [ClientAddress]值（5，1）
 INSERT [ClientAddress]值（6，3）
 INSERT [ClientAddress]值（7 ，2）
 INSERT [客户地址]值（8，4）
 INSERT [客户地址]值（5，6）
 INSERT [客户地址]值（6，3）
 INSERT [ClientAddress]值（7，5）
插入[ClientAddress]值（8，1）
 INSERT [ClientAddress]值（5，4）
 INSERT [ClientAddress]值（6，6 ）
 
;与[材料]（[客户ID]，[名称]，[街道]，[行号]）为
（
选择
 [C] 。[ClientID]，
 [C]。[名称]，
 [A]。[街道]，
 ROW_NUMBER（）超过（按[A]排序。[AddressID]）按[ RowNo] 
 FROM 
 [客户] [C]内部加入
 [客户地址] [CA]开
 [C]。[客户ID] = [CA]。[客户ID]内部加入
 [地址] [A]上
 [CA]。[地址ID] = [A]。[地址ID] 
）
选择
 [CTE]。 ClientID]，
 [CTE]。[名称]，
 [CTE]。[街道]，
 [CTE]。[RowNo] 
 FROM 
 [Stuff] [CTE] 
 WHERE 
 [CTE]。[RowNo] IN（从[内容] [CTE2] GROUP BY [CTE2]。[[ClientID]）中选择MIN（[CTE2]。[RowNo]） b $ b按
 [CTE]排序。[名称] ASC，
 [CTE]。[街道] ASC 
 
 DROP TABLE [ClientAddress] 
 DROP TABLE [地址] 
 DROP TABLE [客户端] 
  

该查询旨在获取所有客户端，并且他们的第一个地址（ID最低的地址）。在我看来，它应该可以工作。

我对为什么有时不起作用有一种理论。 CTE之后的声明在两个地方提到了CTE。如果CTE是不确定的，并且运行了不止一次，则CTE的结果在引用的两个地方可能会有所不同。

CTE的RowNo列使用ROW_NUMBER（）和order by子句，当多次运行时，该子句可能会导致不同的排序（我们按地址排序，根据查询的执行方式，客户端可以为任何顺序）。

因为这可能导致CTE和CTE2包含不同的结果吗？还是CTE仅执行一次，我是否需要在其他地方解决该问题？

解决方案

它不能以任何方式保证

SQL Server 可免费评估每个 CTE

您可能需要阅读这篇文章：

• > 在子查询中生成XML

如果您的 CTE 不确定，则必须将其结果存储在临时表或表变量中，并使用它代替 CTE 。

<另一方面，p> PostgreSQL 总是只评估 CTE s一次，将其结果缓存。

I am trying to work out a bug we've found during our last iteration of testing. It involves a query which uses a common table expression. The main theme of the query is that it simulates a 'first' aggregate operation (get the first row for this grouping).

The problem is that the query seems to choose rows completely arbitrarily in some circumstances - multiple rows from the same group get returned, some groups simply get eliminated altogether. However, it always picks the correct number of rows.

I have created a minimal example to post here. There are clients and addresses, and a table which defines the relationships between them. This is a much simplified version of the actual query I'm looking at, but I believe it should have the same characteristics, and it is a good example to use to explain what I think is going wrong.

CREATE TABLE [Client] (ClientID int, Name varchar(20))
CREATE TABLE [Address] (AddressID int, Street varchar(20))
CREATE TABLE [ClientAddress] (ClientID int, AddressID int)

INSERT [Client] VALUES (1, 'Adam')
INSERT [Client] VALUES (2, 'Brian')
INSERT [Client] VALUES (3, 'Charles')
INSERT [Client] VALUES (4, 'Dean')
INSERT [Client] VALUES (5, 'Edward')
INSERT [Client] VALUES (6, 'Frank')
INSERT [Client] VALUES (7, 'Gene')
INSERT [Client] VALUES (8, 'Harry')

INSERT [Address] VALUES (1, 'Acorn Street')
INSERT [Address] VALUES (2, 'Birch Road')
INSERT [Address] VALUES (3, 'Cork Avenue')
INSERT [Address] VALUES (4, 'Derby Grove')
INSERT [Address] VALUES (5, 'Evergreen Drive')
INSERT [Address] VALUES (6, 'Fern Close')

INSERT [ClientAddress] VALUES (1, 1)
INSERT [ClientAddress] VALUES (1, 3)
INSERT [ClientAddress] VALUES (2, 2)
INSERT [ClientAddress] VALUES (2, 4)
INSERT [ClientAddress] VALUES (2, 6)
INSERT [ClientAddress] VALUES (3, 3)
INSERT [ClientAddress] VALUES (3, 5)
INSERT [ClientAddress] VALUES (3, 1)
INSERT [ClientAddress] VALUES (4, 4)
INSERT [ClientAddress] VALUES (4, 6)
INSERT [ClientAddress] VALUES (5, 1)
INSERT [ClientAddress] VALUES (6, 3)
INSERT [ClientAddress] VALUES (7, 2)
INSERT [ClientAddress] VALUES (8, 4)
INSERT [ClientAddress] VALUES (5, 6)
INSERT [ClientAddress] VALUES (6, 3)
INSERT [ClientAddress] VALUES (7, 5)
INSERT [ClientAddress] VALUES (8, 1)
INSERT [ClientAddress] VALUES (5, 4)
INSERT [ClientAddress] VALUES (6, 6)

;WITH [Stuff] ([ClientID], [Name], [Street], [RowNo]) AS
(
    SELECT
        [C].[ClientID],
        [C].[Name],
        [A].[Street],
        ROW_NUMBER() OVER (ORDER BY [A].[AddressID]) AS [RowNo]
    FROM
        [Client] [C] INNER JOIN
        [ClientAddress] [CA] ON
            [C].[ClientID] = [CA].[ClientID] INNER JOIN
        [Address] [A] ON
            [CA].[AddressID] = [A].[AddressID]
)
SELECT
    [CTE].[ClientID],
    [CTE].[Name],
    [CTE].[Street],
    [CTE].[RowNo]
FROM
    [Stuff] [CTE]
WHERE
    [CTE].[RowNo] IN (SELECT MIN([CTE2].[RowNo]) FROM [Stuff] [CTE2] GROUP BY [CTE2].[ClientID])
ORDER BY
    [CTE].[Name] ASC,
    [CTE].[Street] ASC

DROP TABLE [ClientAddress]
DROP TABLE [Address]
DROP TABLE [Client]

The query is designed to get all clients, and their first address (the address with the lowest ID). This appears to me that it should work.

I have a theory about why it sometimes will not work. The statement that follows the CTE refers to the CTE in two places. If the CTE is non-deterministic, and it gets run more than once, the result of the CTE may be different in the two places it's referenced.

In my example, the CTE's RowNo column uses ROW_NUMBER() with an order by clause that will potentially result in different orderings when run multiple times (we're ordering by address, the clients can be in any order depending on how the query is executed).

Because of this is it possible that CTE and CTE2 can contain different results? Or is the CTE only executed once and do I need to look elsewhere for the problem?

解决方案

It is not guaranteed in any way.

SQL Server is free to evaluate CTE each time it's accessed or cache the results, depending on the plan.

You may want to read this article:

• Generating XML in subqueries

If your CTE is not deterministic, you will have to store its result in a temporary table or a table variable and use it instead of the CTE.

PostgreSQL, on the other hand, always evaluates CTEs only once, caching their results.

这篇关于此公用表表达式的结果被评估多少次？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！