From: | Tatsuo Ishii <ishii(at)postgresql(dot)org> |
---|---|
To: | er(at)xs4all(dot)nl |
Cc: | ishii(at)postgresql(dot)org, pgsql-patches(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org, y-asaba(at)sraoss(dot)co(dot)jp |
Subject: | Re: [PATCHES] WITH RECUSIVE patches 0717 |
Date: | 2008-07-20 15:42:16 |
Message-ID: | 20080721.004216.38709223.t-ishii@sraoss.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-patches |
> 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
>
>
>
>
>
Attachment | Content-Type | Size |
---|---|---|
unknown_filename | text/plain | 2.2 KB |
recursive_query.patch.gz | application/octet-stream | 25.1 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2008-07-20 16:04:37 | Re: temp table problem |
Previous Message | Heikki Linnakangas | 2008-07-20 15:27:06 | Re: temp table problem |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2008-07-20 16:22:30 | Re: [PATCHES] WITH RECUSIVE patches 0717 |
Previous Message | Simon Riggs | 2008-07-20 06:37:42 | Re: pg_dump additional options for performance |