From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: jiangshan.liu@tju.edu.cn
Subject: BUG #17963: Recursive table cannot be referenced when using LEFT JOIN instead of LATERAL in recursive term
Date: Tue, 06 Jun 2023 08:32:44 +0000
Message-ID: <17963-94341a7d6b29ce47@postgresql.org> (raw)
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!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, jiangshan.liu@tju.edu.cn
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>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox