From: Tom Lane <tgl@sss.pgh.pa.us>
To: jiangshan.liu@tju.edu.cn
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17963: Recursive table cannot be referenced when using LEFT JOIN instead of LATERAL in recursive term
Date: Tue, 06 Jun 2023 09:07:11 -0400
Message-ID: <160543.1686056831@sss.pgh.pa.us> (raw)
In-Reply-To: <17963-94341a7d6b29ce47@postgresql.org>
References: <17963-94341a7d6b29ce47@postgresql.org>
PG Bug reporting form <noreply@postgresql.org> writes:
> Since the LATERAL keyword is not in the SQL standard,
Sure it is. It's been there since SQL99, which is the same revision
that added WITH clauses. See <lateral derived table> in SQL99
section 7.6 <table reference>.
> I need to equivalently
> rewrite this statement to also adapt to a DBMS that does not support the
> LATERAL keyword
I'm more than a bit bemused by the idea that there's somebody out there
that supports WITH RECURSIVE but not LATERAL; the latter seems a good
deal simpler to implement.
> -- same error of SQL Code 2 and Code 3
> ERROR: invalid reference to FROM-clause entry for table "run"
> LINE 6: SELECT true, 'rec-term-branch-A' WHERE run.tag='non-re...
> ^
> HINT: There is an entry for table "run", but it cannot be referenced from
> this part of the query.
This is absolutely expected, because in neither of those queries is
"run" in scope in the places where you are trying to reference it.
A recursive CTE does not get some magic exemption from the scope
rules: it has to be mentioned as a base table within the second arm
of the UNION, and then that query can reference that base table
in the normal places, such as WHERE and higher JOIN/ON clauses.
But an independent sub-SELECT is not such a place, unless you
use LATERAL.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, jiangshan.liu@tju.edu.cn, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17963: Recursive table cannot be referenced when using LEFT JOIN instead of LATERAL in recursive term
In-Reply-To: <160543.1686056831@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox