none
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 
         FROM   AdventureWorks2008.Production.Product 
         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

Answers

  • 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 
    
       FROM  AdventureWorks2008.Production.Product 
    
       ), 
    
      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