question

novog-4931 avatar image
0 Votes"
novog-4931 asked EchoLiu-msft answered

Analytic functions get only single row in recursive CTE

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!


sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
1 Vote"
EchoLiu-msft answered

Hi @novog-4931,

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomCooper-6989 avatar image
2 Votes"
TomCooper-6989 answered

Since Example I in https://docs.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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.