Re: BUG #17963: Recursive table cannot be referenced when using LEFT JOIN instead of LATERAL in recursive term

PG Bug reporting form <noreply(at)postgresql(dot)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