Analytic functions get only single row in recursive CTE

Question

I would like clarification (preferrably official) on whether certain behavior is by design or a longstanding bug. The MSDN documentation states that in the context of a recursive common table expression, an analytic function will "operate only on the subset of data passed to them by the current recursion level and not the entire set of data passed to the recursive part of the CTE." This restriction is logical and expected. However, to the contrary, the analytic function will receive only the current row rather than the current recursion level. To me, referring to "subset" and "current recursion level" clearly implies that the analytic function will receive all data in the current recursion level, and certainly not that it will see only the current row.

This unexpected behavior can be observed in the MSDN example query "Using analytical functions in a recursive CTE". However, the accompanying text explains the behavior in terms of restriction to the current recursion level, which is not the issue at all. That query is also unnecessarily convoluted; here is a simpler version that demonstrates the issue:

WITH r (Id, LvlIndex, Lvl) AS (  
	SELECT N, ROW_NUMBER() OVER (PARTITION BY 0 ORDER BY N), 0  
	FROM (VALUES (1), (2), (3), (4)) AS v (N)  
  
	UNION ALL  
  
	SELECT Id, ROW_NUMBER() OVER (PARTITION BY Lvl + 1 ORDER BY Id), Lvl + 1  
	FROM r  
	WHERE Lvl < 1  
)  
SELECT Id, LvlIndex, Lvl  
FROM r  
ORDER BY Lvl, Id;  

The results from the base case have the expected ROW_NUMBER results, while the recursive case rows all have 1, despite being partitioned by a common value.

Could anyone provide or point me to a definitive answer on whether or not this behavior is as intended? Thanks!

Answer 1

Hi @novog ,

Welcome to microsoft TSQL Q&A forum!

This is not a mistake, but the result of working as designed.Sometimes, for some reasons, some functions in sql server are limited at the time of design.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Since Example I in https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15 shows this behavior with the explanation "For each of the iterations of the recursive part of the query, only one row is passed to ROWNUMBER", it is doing what the documentation says it will do. And therefore, "works as designed".

Avoid using analytical or aggregate functions in recursive parts of CTE's. Usually, you can just move them to a succeeding CTE or the final query. For example to get the result you want, you could do

WITH r (Id, Lvl) AS (  
    SELECT N, 0  
    FROM (VALUES (1), (2), (3), (4)) AS v (N)  
     
    UNION ALL  
     
    SELECT Id, Lvl + 1  
    FROM r   
    WHERE Lvl < 1  
)  
SELECT Id, ROW_NUMBER() OVER(PARTITION BY Lvl ORDER BY Id) AS LvlIndex, Lvl  
FROM r  
ORDER BY Lvl, Id;  

Tom