Обсуждение: 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 >