Re: [HACKERS] [PATCHES] WITH RECUSIVE patches 0717

> This crashes the backend:
>
> WITH RECURSIVE t(n) AS (
> VALUES (1)
> UNION ALL
> SELECT n+1 FROM t WHERE n < 5 ORDER BY 1
> )
> SELECT n FROM t;
>
> apparently because of the ORDER BY 1

Thanks for the report. I think ORDER BY in this case is useless
anyway. ORDER BY affects (VALUES (1) UNION ALL SELECT n+1 FROM t WHERE
n < 5). Since this is a recursive query, value for (VALUES (1) UNION
ALL SELECT n+1 FROM t WHERE n < 5) will not be determined until the
recursion stops. So the meaning of ORDER BY is vague. If caller wants
to get the sorted result of the recursion, he could always write:

WITH RECURSIVE t(n) AS (
VALUES (1)
UNION ALL
SELECT n+1 FROM t WHERE n < 5
)
SELECT n FROM t ORDER BY 1;

Thus I think we should avoid this kind of ORDER BY. Probably we should
avoid LIMIT/OFFSET and FOR UPDATE as well. Included patches add the
checking plus minor error messages clarifications. Also I include new
error cases sql.

> ( ORDER BY t.n will just error out )
>
> Compiled with:
>
> ./configure \
> --prefix=${install_dir} \
> --with-pgport=${pgport} \
> --quiet \
> --enable-depend \
> --enable-cassert \
> --enable-debug \
> --with-openssl
>
>
> hth
>
> Erik Rijkers
>
>
>
>
>