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

The following bug has been logged on the website:

Bug reference:      17963
Logged by:          Jiangshan Liu
Email address:      jiangshan.liu@tju.edu.cn
PostgreSQL version: 15.3
Operating system:   Ubuntu
Description:

I got the expected output when I executed the following recursive query: 

-- SQL Code 1
WITH RECURSIVE run(end_flag, tag) AS (
  SELECT true, 'non-rec-term'
    UNION ALL
  SELECT result.* FROM run,
    LATERAL (
      SELECT true, 'rec-term-branch-A' WHERE run.tag='non-rec-term'
        UNION ALL
      SELECT true, 'rec-term-branch-B' WHERE run.tag='rec-term-branch-A'
        UNION ALL
      SELECT false, 'rec-term-branch-C' WHERE run.tag='rec-term-branch-B'
    ) AS result
    WHERE run.end_flag
)
SELECT * FROM run;

-- result of SQL Code 1
 end_flag |        tag        
----------+-------------------
 t        | non-rec-term
 t        | rec-term-branch-A
 t        | rec-term-branch-B
 f        | rec-term-branch-C
(4 rows)


Since the LATERAL keyword is not in the SQL standard, I need to equivalently
rewrite this statement to also adapt to a DBMS that does not support the
LATERAL keyword (code 2 and code 3 are my two attempts), however, I got the
error on postgresql: 

-- SQL Code 2
WITH RECURSIVE run(end_flag, tag) AS (
  SELECT true, 'non-rec-term'
    UNION ALL
  SELECT result.* FROM run
    LEFT JOIN (
      SELECT true, 'rec-term-branch-A' WHERE run.tag='non-rec-term'
        UNION ALL
      SELECT true, 'rec-term-branch-B' WHERE run.tag='rec-term-branch-A'
        UNION ALL
      SELECT false, 'rec-term-branch-C' WHERE run.tag='rec-term-branch-B'
    ) AS result ON true
    WHERE run.end_flag
)
SELECT * FROM run;

-- SQL Code 3
WITH RECURSIVE run(end_flag, tag) AS (
  SELECT true, 'non-rec-term'
    UNION ALL
  SELECT result.* FROM run,
    (SELECT CASE
        WHEN run.tag='non-rec-term' THEN (SELECT true,
'rec-term-branch-A')
        WHEN run.tag='rec-term-branch-A' THEN (SELECT true,
'rec-term-branch-B')
        WHEN run.tag='rec-term-branch-B' THEN (SELECT false,
'rec-term-branch-C')
    END) AS result
    WHERE run.end_flag
)
SELECT * FROM run;

-- 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.


To verify that references to recursive table ("run") are allowed in the
recursive term of a recursive query, I tried the following query and got the
expected results: 

-- SQL Code 4
WITH RECURSIVE run(end_flag, tag) AS (
  SELECT true, 'non-rec-term'
    UNION ALL
  SELECT result.* FROM run
    LEFT JOIN (
      SELECT false, 'rec-term-branch-A'
    ) AS result ON run.tag='non-rec-term'
    WHERE run.end_flag
)
SELECT * FROM run;

-- result of SQL Code 4
 end_flag |        tag        
----------+-------------------
 t        | non-rec-term
 f        | rec-term-branch-A
(2 rows)

But when I put the reference to the recursive table (table "run") in the ON
clause of SQL code 4, I can't seem to write multiple branches in the
recursive term to a table with an alias (like "AS result" in code 2) so that
it can be called uniformly at runtime via SELECT result.*.

The unreferencing in SQL Code 2 and Code 3 is unexpected, is this a bug in
Recursive Query or JOIN operation? If there are no errors in the design and
implementation here, is there any way to rewrite SQL Code 1 without using
the LATERAL keyword? Thank you for your help!

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