Re: WITH RECURSIVE doesn't work properly for me

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