# CTE Puzzle with TOP, SUM & NEWID

### Question

• Shouldn't the following two sums(nested CTE and final query) be equal? Thanks.

```WITH CTE
AS (SELECT  TOP ( 5 ) ProductName = Name,
ReorderPoint
ORDER BY NEWID()),
sumCTE
AS (SELECT SumReorder = SUM(ReorderPoint)
FROM  CTE)
SELECT RecalculatedSUM = SUM(ReorderPoint),
CTEsum = MAX(SumReorder)
FROM  CTE
CROSS JOIN sumCTE
/*
RecalculatedSUM		CTEsum
1506				1581
*/
```

Kalman Toth, SQL Server & BI Training; SQL 2008 GRAND SLAM
Saturday, March 26, 2011 11:19 AM

• Why shoud the by the same? A CTE is expanded when used to the actual query, take a look at the execution plan. Thus this equivalent statement is used:

```USE AdventureWorksLT2008R2;

SELECT SUM(ListPrice),
MAX(SumReorder)
FROM  (
SELECT TOP 5
Name,
ListPrice
FROM  SalesLT.Product
ORDER BY NEWID()) CTE
CROSS JOIN (SELECT SUM(ListPrice) AS SumReorder
FROM  (
SELECT TOP 5
Name,
ListPrice
FROM  SalesLT.Product
ORDER BY NEWID()) CTE ) sumCTE ;
```
So you don't work with the same set in the CTE as in sumCTE as these differ due to the usage NEWID().

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
Saturday, March 26, 2011 11:54 AM
• Kalman,

This is a known issue with the QO moving the evaluation of certain expressions up or down.

Example: (Here I expect to get same value, but you can notice that they are different)

```WITH rs AS (
SELECT GETDATE() AS c1, NEWID() AS c2
)
SELECT
A.c1 AS [A_c1], A.c2 AS [A_c2], B.c1 AS [B_c1]
FROM
rs AS A CROSS JOIN (SELECT 1 AS c1 UNION ALL SELECT 2) AS B;
GO

/*

A_c1	A_c2	B_c1
2011-03-26 16:47:54.513	C05686F2-6C9E-4206-9A3B-D0769E7D1F86	1
2011-03-26 16:47:54.513	E28C26EC-E738-4A8F-881E-A30E6B9C3116	2

*/```

http://connect.microsoft.com/SQLServer/feedback/details/618303/optimizer-moving-computer-scalar-yields-unexpected-result

AMB

Some guidelines for posting q

Saturday, March 26, 2011 8:36 PM
• There are certain functions that are processed differently, like GETDATE().

```WITH rs AS (
SELECT GETDATE() AS c1
)
SELECT
A.c1 AS [A_c1], B.c1 AS [B_c1]
FROM
rs AS A CROSS JOIN rs AS B;
GO

/*

A_c1	B_c1
2011-03-26 16:43:24.743	2011-03-26 16:43:24.743

*/
```

AMB

Some guidelines for posting questions...

Saturday, March 26, 2011 8:44 PM

### All replies

• Why shoud the by the same? A CTE is expanded when used to the actual query, take a look at the execution plan. Thus this equivalent statement is used:

```USE AdventureWorksLT2008R2;

SELECT SUM(ListPrice),
MAX(SumReorder)
FROM  (
SELECT TOP 5
Name,
ListPrice
FROM  SalesLT.Product
ORDER BY NEWID()) CTE
CROSS JOIN (SELECT SUM(ListPrice) AS SumReorder
FROM  (
SELECT TOP 5
Name,
ListPrice
FROM  SalesLT.Product
ORDER BY NEWID()) CTE ) sumCTE ;
```
So you don't work with the same set in the CTE as in sumCTE as these differ due to the usage NEWID().

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
Saturday, March 26, 2011 11:54 AM
• Thanks Stefan for the explanation. If I don't use NEWID, they are the same.

The outer of the nested CTEs sumCTE builds upon the inner CTE, I would expect the construct to be determinate. I am surprised that it is not.  The CTE is evaluated twice, is the optimizer sleeping?

```WITH CTE

AS (SELECT TOP ( 5 ) ProductName = Name,

ReorderPoint

),

sumCTE

AS (SELECT SumReorder = SUM(ReorderPoint)

FROM CTE)

SELECT RecalculatedSUM = SUM(ReorderPoint),

CTEsum = MAX(SumReorder)

FROM CTE

CROSS JOIN sumCTE

/*

3300	3300

*/

```

Kalman Toth, SQL Server & BI Training; SQL 2008 GRAND SLAM
Saturday, March 26, 2011 12:51 PM
• Kalman,

This is a known issue with the QO moving the evaluation of certain expressions up or down.

Example: (Here I expect to get same value, but you can notice that they are different)

```WITH rs AS (
SELECT GETDATE() AS c1, NEWID() AS c2
)
SELECT
A.c1 AS [A_c1], A.c2 AS [A_c2], B.c1 AS [B_c1]
FROM
rs AS A CROSS JOIN (SELECT 1 AS c1 UNION ALL SELECT 2) AS B;
GO

/*

A_c1	A_c2	B_c1
2011-03-26 16:47:54.513	C05686F2-6C9E-4206-9A3B-D0769E7D1F86	1
2011-03-26 16:47:54.513	E28C26EC-E738-4A8F-881E-A30E6B9C3116	2

*/```

http://connect.microsoft.com/SQLServer/feedback/details/618303/optimizer-moving-computer-scalar-yields-unexpected-result

AMB

Some guidelines for posting q

Saturday, March 26, 2011 8:36 PM
• There are certain functions that are processed differently, like GETDATE().

```WITH rs AS (
SELECT GETDATE() AS c1
)
SELECT
A.c1 AS [A_c1], B.c1 AS [B_c1]
FROM
rs AS A CROSS JOIN rs AS B;
GO

/*

A_c1	B_c1
2011-03-26 16:43:24.743	2011-03-26 16:43:24.743

*/
```

AMB

Some guidelines for posting questions...

Saturday, March 26, 2011 8:44 PM
• Thanks Alejandro. Good to know. Multi-step workarounds with @tablevariables or #temptables.

Kalman Toth, SQL Server & BI Training; SQL 2008 GRAND SLAM
Saturday, March 26, 2011 10:01 PM
• Kalman,

Right, we have to materialize the set if we want to avoid this.

AMB

Some guidelines for posting questions...

Sunday, March 27, 2011 4:02 PM
• hi,

another solution may be the use of a USE PLAN with the Eager Spool operation:

http://explainextended.com/2009/05/28/generating-xml-in-subqueries/

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
Monday, March 28, 2011 11:24 AM