1

As the title states, I have a recursive CTE that bombs out when I change the operators in the WHERE clause, even if there are only two rows of data.

CREATE TABLE #Recursion
(Parent varchar(10), Child varchar(10), TopDate datetime)


INSERT INTO #Recursion (Parent, Child, TopDate)
VALUES
('00003137', '00003137', '2018-08-31'),
('04536347', '00003137', '2017-02-28'),
('05458040', '05458040', '9999-12-31'),
('00269705',' 05458040',' 9999-12-31')

;WITH 
Parent AS
  ( SELECT parent
     , child
     , 1 as sort
     , TopDate
 FROM #Recursion 
WHERE parent = child
 UNION ALL
   SELECT s.parent
        , d.child
        , d.sort + 1
        , s.TopDate
 FROM Parent as d
 JOIN #Recursion s
   ON s.child= d.parent
WHERE s.TopDate < d.TopDate
)

  SELECT parent
       , child
       , sort
    FROM Parent 
ORDER BY parent, sort desc
OPTION (maxrecursion 0);

DROP TABLE #Recursion

If I leave the where clause as such, it works

WHERE s.TopDate < d.TopDate

As soon as I change it to this, it bombs and will not complete

WHERE s.TopDate <= d.TopDate

I do need to run some testing if the dates are the less than or equal. How come this wont work, even with only two rows of data?

Here is some example data. Note how the date field can be identical

parent      child       sort    TopDate
00003137    04536347    2       2017-02-28
00003137    00003137    1       2018-08-31
00269705    00269705    2       9999-12-31      
00269705    05458040    1       9999-12-31

I can get this to work on all data except data that has identical TopDates

5

If you model root nodes as having parent=child, you must exclude them from the recursive clause or you'll get an infinite loop. Your s.TopDate < d.TopDate is currently the only thing in your query preventing an infinite loop.

EG, if you change < to <= the query will enter an infinite loop. Note this is why (maxrecursion 0) is perhaps not a good idea.

drop table if exists #Recursion
go
create table #Recursion(parent int, child int, topdate datetime)
go
insert into #Recursion(parent,child,topdate) values (1,1,getdate());

;WITH 
Parent AS
  ( SELECT parent
         , child
         , 1 as [order]
         , TopDate
     FROM #Recursion 
    WHERE parent = child
 UNION ALL
   SELECT s.parent
        , d.child
        , d.[order] + 1
        , s.TopDate
     FROM Parent as d
     JOIN #Recursion s
       ON s.child = d.parent
    WHERE s.TopDate < d.TopDate
)

  SELECT parent
       , child
       , [order]
    FROM Parent 
ORDER BY parent, [order] desc
OPTION (maxrecursion 0);

Also

ON s.child = d.parent

should probably be

ON d.child = s.parent

As you appear to be starting at the root nodes, so you need to recurse down by UNION ALL-ing rows whose parent is the previous iteration's child.

  • Thank you for the detailed explanation. It now makes sense – jdids Sep 4 '18 at 15:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.