Обсуждение: CTE bug?
Folks, I tried the following and it broke: WITH RECURSIVE t(j) AS ( WITH RECURSIVE s(i) AS ( VALUES (1) UNION ALL SELECT i+1 FROM s WHERE i < 10 )SELECT i AS j FROM s UNION ALL SELECT j+1 FROM t WHERE j < 10 ) SELECT * FROM t; ERROR: relation "s" does not exist LINE 6: ) SELECT i AS j FROM s ^ Shouldn't this work? Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter <david@fetter.org> writes:
> WITH RECURSIVE t(j) AS (
> WITH RECURSIVE s(i) AS (
> VALUES (1)
> UNION ALL
> SELECT i+1 FROM s WHERE i < 10
> ) SELECT i AS j FROM s
> UNION ALL
> SELECT j+1 FROM t WHERE j < 10
> )
> SELECT * FROM t;
> ERROR: relation "s" does not exist
> LINE 6: ) SELECT i AS j FROM s
> ^
> Shouldn't this work?
Huh, nice test case. It looks like it's trying to do the "throwaway
parse analysis" of the nonrecursive term (around line 200 of
parse_cte.c) without having analyzed the inner WITH clause. We could
probably fix it by doing a throwaway analysis of the inner WITH too
... but ... that whole throwaway thing is pretty ugly and objectionable
from a performance standpoint anyhow. I wonder if it wouldn't be better
to refactor so that transformSetOperationStmt knows when it's dealing
with the body of a recursive UNION and does the analyzeCTETargetList
business after having processed the first UNION arm. This would inject
a bit more coupling between transformSetOperationStmt and the CTE code
than is there now, but it seems to me that if anything it's a less
surprising implementation. If you were looking to find where the
output column types of a recursive union got determined, you'd expect
to find it somewhere near the UNION code, no?
regards, tom lane
I wrote:
> David Fetter <david@fetter.org> writes:
>> WITH RECURSIVE t(j) AS (
>> WITH RECURSIVE s(i) AS (
>> VALUES (1)
>> UNION ALL
>> SELECT i+1 FROM s WHERE i < 10
>> ) SELECT i AS j FROM s
>> UNION ALL
>> SELECT j+1 FROM t WHERE j < 10
>> )
>> SELECT * FROM t;
>> ERROR: relation "s" does not exist
>> LINE 6: ) SELECT i AS j FROM s
>> ^
>> Shouldn't this work?
> Huh, nice test case. It looks like it's trying to do the "throwaway
> parse analysis" of the nonrecursive term (around line 200 of
> parse_cte.c) without having analyzed the inner WITH clause. We could
> probably fix it by doing a throwaway analysis of the inner WITH too
> ... but ... that whole throwaway thing is pretty ugly and objectionable
> from a performance standpoint anyhow. I wonder if it wouldn't be better
> to refactor so that transformSetOperationStmt knows when it's dealing
> with the body of a recursive UNION and does the analyzeCTETargetList
> business after having processed the first UNION arm.
I've committed a fix along those lines. Too late for 8.4.1
unfortunately :-(. In the meantime, you could work around the
problem in this particular case with some more parentheses:
WITH RECURSIVE t(j) AS ( ( WITH RECURSIVE s(i) AS ( VALUES (1) UNION ALL SELECT i+1 FROM s WHERE i < 10
) SELECT i AS j FROM s )
UNION ALL SELECT j+1 FROM t WHERE j < 10
)
SELECT * FROM t;
regards, tom lane
On Tue, Sep 08, 2009 at 11:37:14PM -0400, Tom Lane wrote: > I wrote: > > David Fetter <david@fetter.org> writes: > >> WITH RECURSIVE t(j) AS ( > >> WITH RECURSIVE s(i) AS ( > >> VALUES (1) > >> UNION ALL > >> SELECT i+1 FROM s WHERE i < 10 > >> ) SELECT i AS j FROM s > >> UNION ALL > >> SELECT j+1 FROM t WHERE j < 10 > >> ) > >> SELECT * FROM t; > >> ERROR: relation "s" does not exist > >> LINE 6: ) SELECT i AS j FROM s > >> ^ > >> Shouldn't this work? > > > Huh, nice test case. It looks like it's trying to do the > > "throwaway parse analysis" of the nonrecursive term (around line > > 200 of parse_cte.c) without having analyzed the inner WITH clause. > > We could probably fix it by doing a throwaway analysis of the > > inner WITH too ... but ... that whole throwaway thing is pretty > > ugly and objectionable from a performance standpoint anyhow. I > > wonder if it wouldn't be better to refactor so that > > transformSetOperationStmt knows when it's dealing with the body of > > a recursive UNION and does the analyzeCTETargetList business after > > having processed the first UNION arm. > > I've committed a fix along those lines. Too late for 8.4.1 > unfortunately :-(. I just wish I'd found it sooner :) Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Tue, Sep 08, 2009 at 11:37:14PM -0400, Tom Lane wrote: > I wrote: > > David Fetter <david@fetter.org> writes: > >> WITH RECURSIVE t(j) AS ( > >> WITH RECURSIVE s(i) AS ( > >> VALUES (1) > >> UNION ALL > >> SELECT i+1 FROM s WHERE i < 10 > >> ) SELECT i AS j FROM s > >> UNION ALL > >> SELECT j+1 FROM t WHERE j < 10 > >> ) > >> SELECT * FROM t; > >> ERROR: relation "s" does not exist > >> LINE 6: ) SELECT i AS j FROM s > >> ^ > >> Shouldn't this work? > > > Huh, nice test case. It looks like it's trying to do the "throwaway > > parse analysis" of the nonrecursive term (around line 200 of > > parse_cte.c) without having analyzed the inner WITH clause. We could > > probably fix it by doing a throwaway analysis of the inner WITH too > > ... but ... that whole throwaway thing is pretty ugly and objectionable > > from a performance standpoint anyhow. I wonder if it wouldn't be better > > to refactor so that transformSetOperationStmt knows when it's dealing > > with the body of a recursive UNION and does the analyzeCTETargetList > > business after having processed the first UNION arm. > > I've committed a fix along those lines. Too late for 8.4.1 > unfortunately :-(. In the meantime, you could work around the > problem in this particular case with some more parentheses: > > WITH RECURSIVE t(j) AS ( > ( > WITH RECURSIVE s(i) AS ( > VALUES (1) > UNION ALL > SELECT i+1 FROM s WHERE i < 10 > ) SELECT i AS j FROM s > ) > UNION ALL > SELECT j+1 FROM t WHERE j < 10 > ) > SELECT * FROM t; > > regards, tom lane I tested this with deeper-nested structures, and ran across another question: Should the outer query be able to reference further-in CTEs? WITH RECURSIVE s(i) AS ( WITH RECURSIVE t(j) AS ( VALUES(1) UNION ALL SELECT j+1 FROM t WHERE j < 10 ) SELECT j AS i FROM t UNION ALL SELECT i+1 FROM s WHERE i < 10 ) SELECT * FROM s,t; ERROR: relation "t" does not exist LINE 11: SELECT * FROM s,t; ^ Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter <david@fetter.org> writes:
> Should the outer query be able to reference further-in CTEs?
No, why would you expect that?
regards, tom lane
On Wed, Sep 09, 2009 at 03:00:39PM -0400, Tom Lane wrote: > David Fetter <david@fetter.org> writes: > > Should the outer query be able to reference further-in CTEs? > > No, why would you expect that? No particular reason, I suppose. I'm not clear on what the standard says about this. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter <david@fetter.org> writes:
> On Wed, Sep 09, 2009 at 03:00:39PM -0400, Tom Lane wrote:
>> David Fetter <david@fetter.org> writes:
>>> Should the outer query be able to reference further-in CTEs?
>>
>> No, why would you expect that?
> No particular reason, I suppose. I'm not clear on what the standard
> says about this.
The standard says that the scope of a WITH is the <query expression
body> it precedes.
regards, tom lane