Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps
 (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <pgsql-bugs-owner+archive@lists.postgresql.org>)
	id 1q6WPB-00061X-RY
	for pgsql-bugs@arkaria.postgresql.org; Tue, 06 Jun 2023 13:07:21 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-bugs-owner+archive@lists.postgresql.org>)
	id 1q6WP8-00045v-Le
	for pgsql-bugs@arkaria.postgresql.org; Tue, 06 Jun 2023 13:07:18 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1q6WP8-00045Z-D2
	for pgsql-bugs@lists.postgresql.org; Tue, 06 Jun 2023 13:07:18 +0000
Received: from sss.pgh.pa.us ([66.207.139.130])
	by makus.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1q6WP4-000X9W-PI
	for pgsql-bugs@lists.postgresql.org; Tue, 06 Jun 2023 13:07:16 +0000
Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1])
	by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 356D7B0p160544;
	Tue, 6 Jun 2023 09:07:13 -0400
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
In-reply-to: <17963-94341a7d6b29ce47@postgresql.org>
References: <17963-94341a7d6b29ce47@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Tue, 06 Jun 2023 08:32:44 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <160542.1686056831.1@sss.pgh.pa.us>
Date: Tue, 06 Jun 2023 09:07:11 -0400
Message-ID: <160543.1686056831@sss.pgh.pa.us>
List-Id: <pgsql-bugs.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-bugs@lists.postgresql.org>
List-Owner: <mailto:pgsql-bugs-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-bugs>
Archived-At: 
 <https://www.postgresql.org/message-id/160543.1686056831%40sss.pgh.pa.us>
Precedence: bulk

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