Re: WITH RECURSIVE doesn't work properly for me - Mailing list pgsql-general

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