SQL Recursion CTE infinite loop

Question

I have a train lines table which looks like this:

The task is to get every possible destination from a given Start (recursively).

I've tried to do it like this:

    with cte (st, nd) as(
    select from1, to1
    from RailwayTbl
    where from1 = 'Berlin'

    union all

    select e.from1, e.to1
    from RailwayTbl e join cte
    on cte.nd = e.from1
    )
    select * from cte;

When executed, I got the following error:

The statement terminated. The maximum recursion 100 has been exhausted before statement completion. ((It is an infinite loop..))

And when I removed the all and used union only I got this error:

Recursive common table expression 'cte' does not contain a top-level UNION ALL operator.

Any ideas?
Thanks in advance!

MySQL <> SQL Server. What are you actually using? That looks like SQL Server error, not MySQL. — Thom A
– Thom A ♦, Commented Oct 10, 2019 at 15:55
Your data is an image, rather than text, so we can't test, but that looks like you have a multiple circular references. For example 'Amsterdam' -> 'Berlin' -> 'Amsterdam' -> 'Berlin' -> 'Amsterdam' .... -> 'Berlin' -> 'Amsterdam' ... -> 'Berlin' -> 'Amsterdam'... you get the idea — Thom A
– Thom A ♦, Commented Oct 10, 2019 at 15:56

Gordon Linoff · Accepted Answer · 2019-10-10 15:59:40Z

1

You need to stop the recursion, which is a pain in SQL Server. The following keeps a string of the visited stations to stop the recursion:

 with cte as (
       select from1 as st, to1 as nd, concat(',', st, ',', to1) as dests, 1 as lev
       from RailwayTbl
       where from1 = 'Berlin'
       union all
       select e.from1, e.to1, concat(cte.dests, e.to1, ',') as dests, lev + 1
       from RailwayTbl e join
            cte
            on cte.nd = e.from1
       where dests not like concat('%,', e.nd, ',%')
    )
select * from cte;

I also added lev. Often I use where lev < 10 on the recursive part of the cte definition for debugging.

answered Oct 10, 2019 at 15:59

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

T N Over a year ago

Possible correction to the above: I believe the initial visited list calculation should be concat(',', st, ',', to1, ',') as dests, with an extra trailing delimiter, and the terminating condition where dests not like concat('%,', e.to1, ',%') so that it checks that the new to1 has not yet been visited already.

Collectives™ on Stack Overflow

SQL Recursion CTE infinite loop

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related