Re: [PATCHES] WITH RECURSIVE patch V0.1

David Fetter írta:
> On Sun, May 18, 2008 at 08:51:29PM +0900, Tatsuo Ishii wrote:
>
>> WITH RECURSIVE patch V0.1
>>
>> Here are patches to implement WITH RECURSIVE clause. There are some
>> limitiations and TODO items(see the "Current limitations" section
>> below). Comments are welcome.
>>
>> 1. Credit
>>
>> These patches were developed by Yoshiyuki Asaba (y-asab@sraoss.co.jp)
>> with some discussions with Tatsuo Ishii (ishii@sraoss.co.jp).
>>
>
> This is really great!  Kudos to all who made this happen :)
>
> I tried a bunch of different queries, and so far, only these two
> haven't worked.  Any ideas what I'm doing wrong here?
>
> WITH RECURSIVE t(n) AS (
>     SELECT 1
> UNION ALL
>     SELECT n+1
>     FROM t
>     WHERE n < 100
> )
> SELECT * FROM t;
> ERROR:  cannot extract attribute from empty tuple slot
>
> WITH RECURSIVE t(n) AS (
>     VALUES (1)
> UNION ALL
>     SELECT n+1
>     FROM t
>     WHERE n < 100
> )
> SELECT * FROM t;
> ERROR:  cannot extract attribute from empty tuple slot
>
> Cheers,
> David.
>

Here's a test case attached shamelessly stolen from
http://www.adp-gmbh.ch/ora/sql/connect_by.html

This query (without naming toplevel columns) works:

# with recursive x as (select * from test_connect_by where parent is
null union all select base.* from test_connect_by as base, x where
base.parent = x.child) select * from x;
 parent | child
--------+-------
        |    38
        |    26
        |    18
     18 |    11
     18 |     7
     26 |    13
     26 |     1
     26 |    12
     38 |    15
     38 |    17
     38 |     6
     17 |     9
     17 |     8
     15 |    10
     15 |     5
      5 |     2
      5 |     3
(17 rows)

It even works when I add my "level" column:

# with recursive x(level, parent, child) as (select 1::bigint, * from
test_connect_by where parent is null union all select x.level + 1,
base.* from test_connect_by as base, x where base.parent = x.child)
select * from x;
 level | parent | child
-------+--------+-------
     1 |        |    38
     1 |        |    26
     1 |        |    18
     2 |     18 |    11
     2 |     18 |     7
     2 |     26 |    13
     2 |     26 |     1
     2 |     26 |    12
     2 |     38 |    15
     2 |     38 |    17
     2 |     38 |     6
     3 |     17 |     9
     3 |     17 |     8
     3 |     15 |    10
     3 |     15 |     5
     4 |      5 |     2
     4 |      5 |     3
(17 rows)

But I have a little problem with the output.
If it's not obvious, here is the query tweaked a little below.

# with recursive x(level, parent, child) as (select 1::integer, * from
test_connect_by where parent is null union all select x.level + 1,
base.* from test_connect_by as base, x where base.parent = x.child)
select lpad(' ', 4*level - 1) || child from x;
     ?column?
------------------
    38
    26
    18
        11
        7
        13
        1
        12
        15
        17
        6
            9
            8
            10
            5
                2
                3
(17 rows)

Can we get the rows in tree order, please? I.e. something like this:

     ?column?
------------------
    38
        15
            10
            5
                2
                3
        17
            9
            8
        6
    26
        13
        1
        12
    18
        11
        7
(17 rows)

After all, I didn't specify any ORDER BY clauses in the base, recursive
or the final queries.

Also, it seems there are no infinite recursion detection:

# with recursive x(level, parent, child) as (
    select 1::integer, * from test_connect_by where parent is null
    union all
    select x.level + 1, base.* from test_connect_by as base, x where
base.child = x.child
) select * from x;
... it waits and waits and waits ...

Also, there's another rough edge:

# with recursive x as (select * from test_connect_by where parent is
null) select * from x;
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

Best regards,
Zoltán Böszörményi

--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/

create table test_connect_by (
  parent     integer,
  child      integer,
  constraint uq_tcb unique (child)
);

insert into test_connect_by values ( 5, 2);
insert into test_connect_by values ( 5, 3);

insert into test_connect_by values (18,11);
insert into test_connect_by values (18, 7);

insert into test_connect_by values (17, 9);
insert into test_connect_by values (17, 8);

insert into test_connect_by values (26,13);
insert into test_connect_by values (26, 1);
insert into test_connect_by values (26,12);

insert into test_connect_by values (15,10);
insert into test_connect_by values (15, 5);

insert into test_connect_by values (38,15);
insert into test_connect_by values (38,17);
insert into test_connect_by values (38, 6);

insert into test_connect_by values (null, 38);
insert into test_connect_by values (null, 26);
insert into test_connect_by values (null, 18);