BUG #17963: Recursive table cannot be referenced when using LEFT JOIN instead of LATERAL in recursive term - Mailing list pgsql-bugs

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!