Обсуждение: BUG #6498: with recursive / union all

The following bug has been logged on the website:

Bug reference:      6498
Logged by:          kanishka
Email address:      nish2575@gmail.com
PostgreSQL version: 9.0.6
Operating system:   Linux 2.6.41.4-1.fc15.i686 / fedora 15
Description:=20=20=20=20=20=20=20=20

here is an example of what i believe the system is not obeying the
expectation of a union all instead of a union, in a recursive with query
that uses a join. the documentation page on with queries also has an example
with union all over an acyclic graph data set that i believe should loop
infinitely according the descriptoin of with query processing.

this doesn't loop infinitely:

 insert into acyc values ('a','b') ,('b','c');

 with recursive paths as (
select frm, too, 1 as lvl from acyc
union all
select fnd.frm, a.too, lvl + 1 from acyc a join paths fnd on fnd.too =3D
a.frm ) select * from paths;


while this does loop infinitely:

 with recursive p as (select 5 union all select * from p ) select * from p;


my guess is the performing of the join is causing duplicates inadvertantly.

nish2575@gmail.com writes:
> this doesn't loop infinitely:

>  insert into acyc values ('a','b') ,('b','c');

>  with recursive paths as (
> select frm, too, 1 as lvl from acyc
> union all
> select fnd.frm, a.too, lvl + 1 from acyc a join paths fnd on fnd.too =
> a.frm ) select * from paths;

Why would it?  You don't have any loops in the data.  In particular that
means there will be no join matches after a certain number of levels.

            regards, tom lane

but {5} union {5} = {5, 5}
so ('a', 'c', 2) union all ('a','c', 2) = {('a', 'c', 2), ('a', 'c', 2)}

the second ('a', 'c', 2) should result from joining the latest working
table in paths which includes {('a', 'b', 1), ('b', 'c', 1), ('a',
'c', 2)} with {('a','b'), ('b', 'c')} specifically ('a', 'b', 1) with
('a', 'b')

maybe i'm stupid or not understanding some assumption, but i interpret
not removing duplicates within and between tables as stated in the
docs for the union all case, as not removing the a,c,2 above.

-kanishka

On 2/29/12, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> nish2575@gmail.com writes:
>> this doesn't loop infinitely:
>
>>  insert into acyc values ('a','b') ,('b','c');
>
>>  with recursive paths as (
>> select frm, too, 1 as lvl from acyc
>> union all
>> select fnd.frm, a.too, lvl + 1 from acyc a join paths fnd on fnd.too =
>> a.frm ) select * from paths;
>
> Why would it?  You don't have any loops in the data.  In particular that
> means there will be no join matches after a certain number of levels.
>
>             regards, tom lane
>