Re: WITH RECURSIVE doesn't work properly for me

I am sorry but I still don't understand why it doesn't work. Possibly I
misunderstand how with recursive works?
In my opinion,
with recursive table as{
seed statement
union
recursive statement
}
In every iteration, It will just generate results from seed statement union
recursive statement and put them into a new temporary table, and then
compare the results with the former temporary table and check if there are
any new tuples. If no new tuples, just stop iteration. Is there any tricky
things about recursive statement?

Thank you very much:)

Best,
Jing

On Wed, Nov 6, 2013 at 2:13 AM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>wrote:

> Jing Fan wrote:
> > If the grouping inside CTE is executed, I don't think it would generate
> result like
> >
> > src_id | dest_id | dist
> > --------+---------+------
> > 3384 | 6236 | 1
> > 3384 | 1739 | 2
> > 3384 | 6236 | 3
> > 3384 | 1739 | 4
> > 3384 | 6236 | 5
> > 3384 | 1739 | 6
> > 3384 | 6236 | 7
> > 3384 | 1739 | 8
> > 3384 | 6236 | 9
> > 3384 | 1739 | 10
> > 3384 | 6236 | 11
> >
> >
> >
> > for we have min(dist),
> > so it should be like
> >
> >
> > src_id | dest_id | dist
> > --------+---------+------
> > 3384 | 6236 | 1
> > 3384 | 1739 | 2
> >
> >
> >
> > other values will be eliminated by min(). It actually generate no new
> tuples and the iteration should
> > stop.
>
> You forget that the grouping query only spans the second branch
> of the UNION, where you add the new entries.
> So the new entries and the old entries won't be grouped together,
> and the new paths that are longer than the old ones won't get removed.
>
> Unfortunately you cannot have the UNION in a subquery for
> recursive CTEs, but you could use arrays to achieve what you want:
>
> WITH RECURSIVE paths (path) AS (
> SELECT ARRAY[src_id, dest_id] FROM edge
> UNION ALL
> SELECT edge.src_id || paths.path
> FROM paths, edge
> WHERE edge.dest_id = paths.path[array_lower(paths.path, 1)]
> AND edge.src_id <> ALL (paths.path)
> )
> SELECT path[1], path[array_upper(path, 1)], min(array_length(path, 1))
> FROM paths
> GROUP BY 1, 2;
>
> The whole exercise sounds a bit like homework to me.
>
> Yours,
> Laurenz Albe
>