CTE recursion infinite loop

Question

I'm working with a stored procedure and using a CTE in SQL Server and I'm trying to reach some data from a 2 tables, but when the execution goes to the CTE query it gets an infinite loop and never ends, is there a way to prevent that infinite loop?

This is the query that I create:

WITH tableName(Id, enddate, statusDte, closeId, shceDte, calcDte, closeEndDte, ParentId, LastClose, lasCloseDte, closeClass,addSe,twon,code)
AS
(
    SELECT 
        tba.Id,
        CASE WHEN tb.ParentId IS NOT NULL 
                THEN tb.Id
             WHEN tb.statusDte IN (1,2,3) 
                THEN  tb.calcDte ELSE tb.shceDte 
                END ForecastDueDate, 
        statusDte, closeId, shceDte, calcDte, 
        CASE WHEN tb.ParentId IS NULL 
                THEN closeEndDte ELSE NULL END, tb.ParentId, 0, 
        CASE WHEN tb.ParentId IS NOT NULL 
                THEN statusDte
             WHEN tb.statusDte = 5
             AND (tb.calcDte BETWEEN '1/1/2020 12:00:00 AM' AND '12/31/2020 11:59:59 PM' 
             OR tb.closeEndDte BETWEEN '1/1/2020 12:00:00 AM' AND '12/31/2020 11:59:59 PM') 
                THEN ams.GetPreviousNthFullAuditDate(tb.Id, tb.AuditID, 2)  ELSE a.statusDate END as lastDate,
        a.closeClass, tba.addSe,tba.town,tba.code
    FROM 
        tableA tba 
    INNER JOIN 
        tableB tb ON tb.Id = tba.Id 
    WHERE 
        statusDte NOT IN (3,4) AND tba.IsAtve = 1

    UNION ALL

    SELECT 
        Id, enddate, 
        statusDte, statusDte, shceDte, calcDte, closeEndDte, ParentId,
        0, lasCloseDte, closeClass,addSe,twon,code
    FROM 
        tableName
    WHERE 
        enddate BETWEEN enddate AND '12/31/2020 11:59:59 PM'
)
SELECT * 
FROM tableName
OPTION (maxrecursion 0)

Expected results

Id                  enddate          statusDte      closeId         shceDte                 calcDte             closeEndDte                 parentId          lastClose       lastCloseDte         closeClass  addSe                               town                      code
----------- ----------------------- ------------- ----------- ----------------------- ----------------------- ----------------------- ----------------------- ----------- ----------------------- ----------- --------------------------------- ---------------------- --------------------------------------------------
133           2011-04-04 00:00:00.000 22            14453       NULL                    2011-04-04 00:00:00.000 2099-12-31 00:00:00.000 NULL                    0           NULL                    1           4707 EXECUTIVE DRIVE  ''             SAN DIEGO               123
56           2018-12-07 13:00:00.000 22            52354       NULL                    2018-12-07 13:00:00.000 2019-12-07 00:00:00.000 NULL                    0           NULL                    1           75 STATE ST FL 24  ''                BOSTON                   345
12          2021-02-05 17:00:00.000 22            75751       NULL                    2021-02-05 17:00:00.000 NULL                    NULL                    0           NULL                    1           1450 FRAZEE RD STE 308  ''           SAN DIEGO                 678
334          2019-03-07 16:30:00.000 15            66707       2019-03-07 16:30:00.000 2019-03-23 21:00:00.000 NULL                    NULL                    0           2019-03-07 16:30:00.000 1           42690 RIO NEDO, STE E  ''            TEMECULA                 91011
33          2020-01-10 17:00:00.000 22            65568       NULL                    2020-01-10 17:00:00.000 NULL                    NULL                    0           2018-01-10 17:00:00.000 1           2518 UNICORNIO ST.  ''               CARLSBAD                  136
55          2020-04-16 20:00:00.000 22            67812       NULL                    2020-04-16 20:00:00.000 NULL                    NULL                    0           2018-04-17 20:00:00.000 1           4534 OSPREY STREET  ''               SAN DIEGO                 653
66          2020-02-21 17:00:00.000 22            75956       NULL                    2020-02-21 17:00:00.000 NULL                    NULL                    0           2019-02-21 17:00:00.000 1           3511 CAMINO DEL RIO S, STE 305  ''   SAN DIEGO                 0484
094          2021-02-20 21:00:00.000 22            75629       NULL                    2021-02-20 21:00:00.000 NULL                    NULL                    0           NULL                    1           29349 EAGLE DR  ''                   MURRIETA                 345

The recursive part should relate somehow to the anchor part. — Alex Kudryashev, Mar 28 '19 at 00:28
[This](https://stackoverflow.com/questions/15080922/infinite-loop-cte-with-option-maxrecursion-0/15081353#15081353) answer demonstrates one way to terminate recursion when a loop is detected. A handy debugging technique is to add a column to the anchor, e.g. `0 as Depth`, and increment it in the recursive part of the query, `tableName.Depth + 1`, then use it in the recursive `where` clause to limit the recursion depth, e.g. `where Depth <= 2 and ...`, and examine the results. You should be able to spot the problem, though it may require increasing the depth limit until the issue becomes clear. — HABO, Mar 28 '19 at 02:26

score 1 · Answer 1 · answered Mar 28 '19 at 01:17

First, let's try to add some best practices. Qualify all your columns with the appropriate table alias. Just doing some of them is inconsistent and inconsistent style is difficult to read and prone to errors.

Next, you've (hopefully) dumbed down your actual query. Generic names like "tableA" hinder understanding.

Next - your first case expression seems highly suspicious. You have one branch returns tb.id and the others return what appears to be a date (or datetime). You can, unfortunately, cast an int to a datetime. Might not make any sense and it won't generate an error. So - does this make sense?

Next - you've made a common mistake with your datetime boundaries. Depending on your data you might never know this. But there is no reason to expect that and there is every reason to write your logic so that it avoids any possibility. Tibor discusses in great detail here. Shorter version - your upper boundary should always be an exclusive one to support all possible values of time for your datatype. 23:59:59 will ignore any time values with non-zero milliseconds. And use a literal format that is not dependent on language or connection settings.

Next, you add confusion. You named your columns in the cte declaration but your code also includes aliases for some (but not all - see, refer to the consistency comment) columns which differ significantly from the actual column name for the cte. The 2nd column for the cte is "enddate", the anchor query uses the alias "ForecastDueDate"

Next, you have this: tb.statusDte = 5. The name implies date; the literal implies something different. You have other columns that end in "Dte" that are obviously dates, but not this one? Danger, danger!

Next, you refer to columns "a.closeClass" and "a.statusDate". There is no table or alias named "a".

Lastly, you have:

WHERE enddate BETWEEN enddate AND '12/31/2020 11:59:59 PM'

Think about what you wrote. Is not enddate always between enddate and Dec 31 2010 (so long as enddate <= that value)? I think this is the source of your issue. You're not computing or adjusting anything from the anchor, so the recursed part just keeps selecting and selecting and selecting. There is no logic to end the recursion.

The next question is obviously "now to fix it". That is impossible to say without knowing your schema, what it represents, and your goal. The use of recursion here is not obvious.

thanks for the comment, yes the code is confusing, actually this is legacy code, I'm just trying to find a solution for this, I did a huge research about the cte and recursive query but nothing have help, but you are right this code does not have good practices at all. But thanks for the recomendations, I appreciate every comment or help! :) — GeekDev, Mar 28 '19 at 01:52
Something doesn't match. You said this is legacy code which implies you are porting/migrating it to something "new". And that implies you have added the recursion, otherwise it would also not work in the legacy system. Again - you need to understand exactly what this code does in the legacy system and what your goals are in migrating it (maybe "enhancing" is a better term). — SMor, Mar 28 '19 at 13:12

score 0 · Answer 2 · answered Mar 28 '19 at 05:47

0

If the data is in a structure that the hierarchy between records is is a loop then recursion goes to infinite causing a problem in SQL. You will see the resources used by SQL process is increasing tremendously. If you use MAXRECURSION with a different value than 0 (zero lets SQL to continue recursion without a limit) you will be able to limit the recursion. With data that is looping or referencing each other you can this MAXRECURSION parameter

answered Mar 28 '19 at 05:47

Eralper

6,461
2
21
27

Thanks @Eralper! I tried with other values like 1,2 and 100 those values stop the infinite loop but in the app, I'm getting an error when that happens, because the query throws and alert message saying that `The statement terminated. The maximum recursion 100 has been exhausted before statement completion.` So the dataset in the app does not get filled ans shows an error. – GeekDev Mar 28 '19 at 15:50

CTE recursion infinite loop

2 Answers2