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

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: jiangshan(dot)liu(at)tju(dot)edu(dot)cn
Subject: BUG #17963: Recursive table cannot be referenced when using LEFT JOIN instead of LATERAL in recursive term
Date: 2023-06-06 08:32:44
Message-ID: 17963-94341a7d6b29ce47@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 17963
Logged by: Jiangshan Liu
Email address: jiangshan(dot)liu(at)tju(dot)edu(dot)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!

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Nikolay Shaplov 2023-06-06 10:39:06 Re: BUG #17962: postgresql 11 hangs on poly_contain with specific data
Previous Message Kyotaro Horiguchi 2023-06-06 05:17:36 Re: BUG #17962: postgresql 11 hangs on poly_contain with specific data