Re: A CTE Question

From: Erland Sommarskog <esquel_at_sommarskog.se>
Date: Sat, 28 Aug 2010 14:38:45 +0200
Message-ID: <Xns9DE294FCD26B8Yazorman_at_127.0.0.1>

Thomas Kellerer (OTPXDAJCSJVU_at_spammotel.com) writes:
> Erland Sommarskog wrote on 28.08.2010 11:36:

>>>> 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 and iter<   1000
>>>> )
>>>
>>> That query is wrong, and that's why you need this "where iter<  1000"
>>> workaround.
>>
>> The recursion as works: you start from the bottom and work your
>> way up. You will stop recurse when you reach the top level. However,
>> he would need a WHERE NOT EXISTS to only start at the leaves. Now
>> he starts on all nodes.

>
> Hmm, for me the above query yields an endless "loop" when you remove the "
> iter < 1000"

On PostgreSQL?

It should not, as long as there are no cycles in the data. (And if there is, you will get stuck, no matter where you start in the graph.) When you come to the top node, the the recursive part of the CTE will not produce any more rows, and recursion should not continue.

I noticed that in your own query you used the RECURSIVE keyword which is unknown to SQL Server. Maybe that is needed on PostgreSQL to prevent recursion from continuing?

> It works on PostgreSQL with the test data supplied :)

Interesting. I certainly don't get the expected result on SQL Server. I don't have PostgresSQL, but it looks like a bug to me. If you try the extra test data I composed, does it still work? (See below.)  

>> (And uses -2 rather than NULL to signify a root.)
> Yes, correct. Copy & Paste error. I just don't like "magic values" ;)
 

Nor do I!

Here is my test data again. In my previous post I had inadvertendly left out the extra persons

create table DEPARTMENT
(
DEP_ID integer not null primary key,
NAME VARCHAR(32) not null,
PARENT integer not null
) ;

insert into DEPARTMENT

VALUES (1,'A',-2),
       (2,'B',1),
       (3,'C',2),
       (4,'D',-2),
       (5,'E',4),
       (6,'F',5),
       (7,'G',6),
       (8,'D1', 4),
       (9,'D2', 4),
       (10,'E2', 5),
       (11,'E3', 5)  

create table PERSON (
PERSON_ID integer not null primary key,
NAME VARCHAR(128) not null,
DEP_ID integer,
constraint FK_01 foreign key (DEP_ID) references DEPARTMENT(DEP_ID) );

insert into PERSON values

(1,'John',2),(2,'Mary',2),(3,'Suzan',3),
(4,'Joe',3), (5,'Lewis',3),(6,'Elvis',4),
(7,'Presley',5),(8,'Preston',5),(9,'Pee',7),
(10,'Wee',7),(11,'Steve-o',7),(12,'Demi Moore',7),
(13,'Pelle',8),(14,'Nisse',8),(15,'Petra',9),
(16,'Jöns',10),(17,'Pekka',11),(18,'Lalle',11),
(19,'Putte',11); 

go

-- 
Erland Sommarskog, Stockholm, esquel_at_sommarskog.se

Received on Sat Aug 28 2010 - 07:38:45 CDT