Re: A CTE Question

From: Erland Sommarskog <esquel_at_sommarskog.se>
Date: Sat, 28 Aug 2010 19:15:15 +0200
Message-ID: <Xns9DE2C3DDCF0D6Yazorman_at_127.0.0.1>

Thomas Kellerer (OTPXDAJCSJVU_at_spammotel.com) writes:
> Note that the OP incorrectly (at least in my eyes) selects the the
> dep_id from the t_recurs not from the department table, and I think that
> is causing the problem with the endless loop:
>
> with t_recurs (dep_id, parent, iter) as
> (
> select dep_id, parent, 0 from DEPARTMENT
> union all
> select r.dep_id, <-- !!! this should be D.dep_id !!!!
> d.parent, iter + 1
> from t_recurs r, DEPARTMENT d
> where r.parent = d.dep_id and iter < 1000
> )
>
> It should be d.dep_id (because r.dep_id will create a cycle).
> As soon as I select the dep_id from the department table in the second
> part of the union, the endless loop stops.

Having r.dep_id in the recursive part might be a mistake, but it should not cause infinite recursion. The recursion is controlled by the WHERE clause, and the WHERE does not involve r.dep_id.

When I run:

   with t_recurs (DEP_ID, PARENT, iter) as (

      select DEP_ID, PARENT, 0 from DEPARTMENT
      union all
      select r.DEP_ID, d.PARENT, iter + 1
      from  t_recurs r, DEPARTMENT d
      where r.PARENT = d.DEP_ID 

   )
   SELECT * FROM t_recurs ORDER BY iter, DEP_ID

I get this output on SQL Server:

   DEP_ID PARENT iter

• ----------- ----------- 1 -2 0 2 1 0 3 2 0 4 -2 0 5 4 0 6 5 0 7 6 0 8 4 0 9 4 0 10 5 0 11 5 0 2 -2 1 3 1 1 5 -2 1 6 4 1 7 5 1 8 -2 1 9 -2 1 10 4 1 11 4 1 3 -2 2 6 -2 2 7 4 2 10 -2 2 11 -2 2 7 -2 3

Not that I think the output is useful for the problem Michel presented, but I like to point out that there are other situations where this result is useful, at least if you remove all rows with PARENT = -2. To with, this table gives us a quick answer to "does subdept n belong dept m directly or indirectly".

If you need the condition "iter < 1000" on PostgreSQL I assume that this query gives you many more rows than 26. Looking at that output might give a clue of what is going on.

-- 
Erland Sommarskog, Stockholm, esquel_at_sommarskog.se

Received on Sat Aug 28 2010 - 12:15:15 CDT