pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #16978: Nested CTEs give ERROR in v13
5+ messages / 3 participants
[nested] [flat]
* BUG #16978: Nested CTEs give ERROR in v13
@ 2021-04-23 00:13 PG Bug reporting form <noreply@postgresql.org>
2021-04-23 14:08 ` Re: BUG #16978: Nested CTEs give ERROR in v13 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: PG Bug reporting form @ 2021-04-23 00:13 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: tharakan@gmail.com
The following bug has been logged on the website:
Bug reference: 16978
Logged by: Robins Tharakan
Email address: tharakan@gmail.com
PostgreSQL version: 13.2
Operating system: Ubuntu
Description:
This narrowed down SQL (from what SQLSmith generated) seems to error out
only with N levels of CTE nesting - no tables / views / 1-row output.
The SQL works fine with v12.4 but raises an ERROR in v13 which seems like a
regression. Is this owing a recent change in v13 - that could affect this?
(This was found during a larger investigation around a v13 SegFault but this
does seem worthy of its own bug-report).
→ psql -h localhost
psql (14devel, server 13.2)
Type "help" for help.
postgres=# --Having w_err in the SQL gives an error about UNION ALL
postgres=# WITH RECURSIVE w6(c6) AS
postgres-# (WITH w6(c6) AS
postgres(# (WITH w8(c8) AS
postgres(# (WITH w9(c9) AS
postgres(# (WITH w10(c10) AS
postgres(# (WITH w11(c11) AS
postgres(# (WITH w_err(c12) AS
postgres(# (SELECT 1)
postgres(# SELECT * FROM w_err)
postgres(# SELECT * FROM w11)
postgres(# SELECT * FROM w10)
postgres(# SELECT * FROM w9)
postgres(# SELECT * FROM w8)
postgres(# SELECT * FROM w6)
postgres-# SELECT * FROM w6;
ERROR: recursive query "w6" does not have the form non-recursive-term UNION
[ALL] recursive-term
LINE 1: WITH RECURSIVE w6(c6) AS
^
postgres=#
postgres=#
postgres=#
postgres=# -- Removing w_err it works
postgres=# WITH RECURSIVE w6(c6) AS
postgres-# (WITH w6(c6) AS
postgres(# (WITH w8(c8) AS
postgres(# (WITH w9(c9) AS
postgres(# (WITH w10(c10) AS
postgres(# (WITH w11(c11) AS
postgres(# -- (WITH w_err(c12) AS
postgres(# (SELECT 1)
postgres(# -- SELECT * FROM w_err)
postgres(# SELECT * FROM w11)
postgres(# SELECT * FROM w10)
postgres(# SELECT * FROM w9)
postgres(# SELECT * FROM w8)
postgres(# SELECT * FROM w6)
postgres-# SELECT * FROM w6;
c6
----
1
(1 row)
postgres=> \q
→ r.sh 12
psql (14devel, server 12.4)
Type "help" for help.
postgres=> -- No error on v12 with or without w_err
postgres=>
postgres=> WITH RECURSIVE w6(c6) AS
postgres-> (WITH w6(c6) AS
postgres(> (WITH w8(c8) AS
postgres(> (WITH w9(c9) AS
postgres(> (WITH w10(c10) AS
postgres(> (WITH w11(c11) AS
postgres(> (WITH w_err(c12) AS
postgres(> (SELECT 1)
postgres(> SELECT * FROM w_err)
postgres(> SELECT * FROM w11)
postgres(> SELECT * FROM w10)
postgres(> SELECT * FROM w9)
postgres(> SELECT * FROM w8)
postgres(> SELECT * FROM w6)
postgres-> SELECT * FROM w6;
c6
----
1
(1 row)
postgres=> -- No error on v12 with or without w_err
postgres=>
postgres=> WITH RECURSIVE w6(c6) AS
postgres-> (WITH w6(c6) AS
postgres(> (WITH w8(c8) AS
postgres(> (WITH w9(c9) AS
postgres(> (WITH w10(c10) AS
postgres(> (WITH w11(c11) AS
postgres(> -- (WITH w_err(c12) AS
postgres(> (SELECT 1)
postgres(> -- SELECT * FROM w_err)
postgres(> SELECT * FROM w11)
postgres(> SELECT * FROM w10)
postgres(> SELECT * FROM w9)
postgres(> SELECT * FROM w8)
postgres(> SELECT * FROM w6)
postgres-> SELECT * FROM w6;
c6
----
1
(1 row)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #16978: Nested CTEs give ERROR in v13
2021-04-23 00:13 BUG #16978: Nested CTEs give ERROR in v13 PG Bug reporting form <noreply@postgresql.org>
@ 2021-04-23 14:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-04-23 14:56 ` Re: BUG #16978: Nested CTEs give ERROR in v13 Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2021-04-23 14:08 UTC (permalink / raw)
To: tharakan@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> This narrowed down SQL (from what SQLSmith generated) seems to error out
> only with N levels of CTE nesting - no tables / views / 1-row output.
> The SQL works fine with v12.4 but raises an ERROR in v13 which seems like a
> regression. Is this owing a recent change in v13 - that could affect this?
FWIW, I don't see any error from this, either in HEAD or the
back branches.
$ cat recursive.sql
WITH RECURSIVE w6(c6) AS
(WITH w6(c6) AS
(WITH w8(c8) AS
(WITH w9(c9) AS
(WITH w10(c10) AS
(WITH w11(c11) AS
(WITH w_err(c12) AS
(SELECT 1)
SELECT * FROM w_err)
SELECT * FROM w11)
SELECT * FROM w10)
SELECT * FROM w9)
SELECT * FROM w8)
SELECT * FROM w6)
SELECT * FROM w6;
$ psql -f recursive.sql
c6
----
1
(1 row)
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #16978: Nested CTEs give ERROR in v13
2021-04-23 00:13 BUG #16978: Nested CTEs give ERROR in v13 PG Bug reporting form <noreply@postgresql.org>
2021-04-23 14:08 ` Re: BUG #16978: Nested CTEs give ERROR in v13 Tom Lane <tgl@sss.pgh.pa.us>
@ 2021-04-23 14:56 ` Bruce Momjian <bruce@momjian.us>
2021-04-23 15:37 ` Re: BUG #16978: Nested CTEs give ERROR in v13 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Bruce Momjian @ 2021-04-23 14:56 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: tharakan@gmail.com; pgsql-bugs@lists.postgresql.org
On Fri, Apr 23, 2021 at 10:08:06AM -0400, Tom Lane wrote:
> PG Bug reporting form <noreply@postgresql.org> writes:
> > This narrowed down SQL (from what SQLSmith generated) seems to error out
> > only with N levels of CTE nesting - no tables / views / 1-row output.
> > The SQL works fine with v12.4 but raises an ERROR in v13 which seems like a
> > regression. Is this owing a recent change in v13 - that could affect this?
>
> FWIW, I don't see any error from this, either in HEAD or the
> back branches.
>
> $ cat recursive.sql
> WITH RECURSIVE w6(c6) AS
> (WITH w6(c6) AS
> (WITH w8(c8) AS
> (WITH w9(c9) AS
> (WITH w10(c10) AS
> (WITH w11(c11) AS
> (WITH w_err(c12) AS
> (SELECT 1)
> SELECT * FROM w_err)
> SELECT * FROM w11)
> SELECT * FROM w10)
> SELECT * FROM w9)
> SELECT * FROM w8)
> SELECT * FROM w6)
> SELECT * FROM w6;
> $ psql -f recursive.sql
> c6
> ----
> 1
> (1 row)
Uh, I don't see the failure in 13 head or master, but I do see it from a
13.2 tree checkout. I will try to find the post-13.2 commit cause.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #16978: Nested CTEs give ERROR in v13
2021-04-23 00:13 BUG #16978: Nested CTEs give ERROR in v13 PG Bug reporting form <noreply@postgresql.org>
2021-04-23 14:08 ` Re: BUG #16978: Nested CTEs give ERROR in v13 Tom Lane <tgl@sss.pgh.pa.us>
2021-04-23 14:56 ` Re: BUG #16978: Nested CTEs give ERROR in v13 Bruce Momjian <bruce@momjian.us>
@ 2021-04-23 15:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-04-23 18:03 ` Re: BUG #16978: Nested CTEs give ERROR in v13 Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2021-04-23 15:37 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: tharakan@gmail.com; pgsql-bugs@lists.postgresql.org
Bruce Momjian <bruce@momjian.us> writes:
> On Fri, Apr 23, 2021 at 10:08:06AM -0400, Tom Lane wrote:
>> FWIW, I don't see any error from this, either in HEAD or the
>> back branches.
> Uh, I don't see the failure in 13 head or master, but I do see it from a
> 13.2 tree checkout. I will try to find the post-13.2 commit cause.
Oh!
[ checks commit log ]
I bet it's 80ca8464f.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #16978: Nested CTEs give ERROR in v13
2021-04-23 00:13 BUG #16978: Nested CTEs give ERROR in v13 PG Bug reporting form <noreply@postgresql.org>
2021-04-23 14:08 ` Re: BUG #16978: Nested CTEs give ERROR in v13 Tom Lane <tgl@sss.pgh.pa.us>
2021-04-23 14:56 ` Re: BUG #16978: Nested CTEs give ERROR in v13 Bruce Momjian <bruce@momjian.us>
2021-04-23 15:37 ` Re: BUG #16978: Nested CTEs give ERROR in v13 Tom Lane <tgl@sss.pgh.pa.us>
@ 2021-04-23 18:03 ` Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 5+ messages in thread
From: Bruce Momjian @ 2021-04-23 18:03 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: tharakan@gmail.com; pgsql-bugs@lists.postgresql.org
On Fri, Apr 23, 2021 at 11:37:35AM -0400, Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > On Fri, Apr 23, 2021 at 10:08:06AM -0400, Tom Lane wrote:
> >> FWIW, I don't see any error from this, either in HEAD or the
> >> back branches.
>
> > Uh, I don't see the failure in 13 head or master, but I do see it from a
> > 13.2 tree checkout. I will try to find the post-13.2 commit cause.
>
> Oh!
>
> [ checks commit log ]
>
> I bet it's 80ca8464f.
Confirmed. The bug was added by this commit in July 2019:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1cff1b95a
and was included in PG 13. It was fixed by this post-PG 13.2 commit on
February 26, 2021:
https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=80ca8464f
The commit message is very clear:
makeDependencyGraphWalker and checkWellFormedRecursionWalker
thought they could hold onto a pointer to a list's first
cons cell while the list was modified by recursive calls.
That was okay when the cons cell was actually separately
--> palloc'd ... but since commit 1cff1b95a, it's quite unsafe,
leading to core dumps or incorrect complaints of faulty
WITH nesting.
--> In the field this'd require at least a seven-deep WITH nest
to cause an issue, but enabling DEBUG_LIST_MEMORY_USAGE
allows the bug to be seen with lesser nesting depths.
Per bug #16801 from Alexander Lakhin. Back-patch to v13.
It mentions the commit it fixed, and the fact that "this'd require at
least a seven-deep WITH nest to cause an issue", which is exactly what
you saw. :-)
I am attaching this later patch in a version that will cleanly apply to
PG 13.2, in case you need it. If not, the fix will appear in 13.3 which
will be released on May 13, 2021.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
Attachments:
cte-13.2.diff (text/x-diff, 4.7K) download
diff --git a/src/backend/parser/parse_cte.c b/src/backend/parser/parse_cte.c
index 1fca7485ca..b24989e417 100644
--- a/src/backend/parser/parse_cte.c
+++ b/src/backend/parser/parse_cte.c
@@ -537,15 +537,15 @@ makeDependencyGraphWalker(Node *node, CteState *cstate)
* In the non-RECURSIVE case, query names are visible to the
* WITH items after them and to the main query.
*/
- ListCell *cell1;
-
cstate->innerwiths = lcons(NIL, cstate->innerwiths);
- cell1 = list_head(cstate->innerwiths);
foreach(lc, stmt->withClause->ctes)
{
CommonTableExpr *cte = (CommonTableExpr *) lfirst(lc);
+ ListCell *cell1;
(void) makeDependencyGraphWalker(cte->ctequery, cstate);
+ /* note that recursion could mutate innerwiths list */
+ cell1 = list_head(cstate->innerwiths);
lfirst(cell1) = lappend((List *) lfirst(cell1), cte);
}
(void) raw_expression_tree_walker(node,
@@ -813,15 +813,15 @@ checkWellFormedRecursionWalker(Node *node, CteState *cstate)
* In the non-RECURSIVE case, query names are visible to the
* WITH items after them and to the main query.
*/
- ListCell *cell1;
-
cstate->innerwiths = lcons(NIL, cstate->innerwiths);
- cell1 = list_head(cstate->innerwiths);
foreach(lc, stmt->withClause->ctes)
{
CommonTableExpr *cte = (CommonTableExpr *) lfirst(lc);
+ ListCell *cell1;
(void) checkWellFormedRecursionWalker(cte->ctequery, cstate);
+ /* note that recursion could mutate innerwiths list */
+ cell1 = list_head(cstate->innerwiths);
lfirst(cell1) = lappend((List *) lfirst(cell1), cte);
}
checkWellFormedSelectStmt(stmt, cstate);
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
index 67eaeb4f3e..0d506348e1 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -167,6 +167,65 @@ ERROR: operator does not exist: text + integer
LINE 4: SELECT n+1 FROM t WHERE n < 10
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
+-- Deeply nested WITH caused a list-munging problem in v13
+-- Detection of cross-references and self-references
+WITH RECURSIVE w1(c1) AS
+ (WITH w2(c2) AS
+ (WITH w3(c3) AS
+ (WITH w4(c4) AS
+ (WITH w5(c5) AS
+ (WITH RECURSIVE w6(c6) AS
+ (WITH w6(c6) AS
+ (WITH w8(c8) AS
+ (SELECT 1)
+ SELECT * FROM w8)
+ SELECT * FROM w6)
+ SELECT * FROM w6)
+ SELECT * FROM w5)
+ SELECT * FROM w4)
+ SELECT * FROM w3)
+ SELECT * FROM w2)
+SELECT * FROM w1;
+ c1
+----
+ 1
+(1 row)
+
+-- Detection of invalid self-references
+WITH RECURSIVE outermost(x) AS (
+ SELECT 1
+ UNION (WITH innermost1 AS (
+ SELECT 2
+ UNION (WITH innermost2 AS (
+ SELECT 3
+ UNION (WITH innermost3 AS (
+ SELECT 4
+ UNION (WITH innermost4 AS (
+ SELECT 5
+ UNION (WITH innermost5 AS (
+ SELECT 6
+ UNION (WITH innermost6 AS
+ (SELECT 7)
+ SELECT * FROM innermost6))
+ SELECT * FROM innermost5))
+ SELECT * FROM innermost4))
+ SELECT * FROM innermost3))
+ SELECT * FROM innermost2))
+ SELECT * FROM outermost
+ UNION SELECT * FROM innermost1)
+ )
+ SELECT * FROM outermost ORDER BY 1;
+ x
+---
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+(7 rows)
+
--
-- Some examples with a tree
--
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql
index f85645efde..43cd2e10af 100644
--- a/src/test/regress/sql/with.sql
+++ b/src/test/regress/sql/with.sql
@@ -87,6 +87,50 @@ UNION ALL
)
SELECT n, n IS OF (int) AS is_int FROM t;
+-- Deeply nested WITH caused a list-munging problem in v13
+-- Detection of cross-references and self-references
+WITH RECURSIVE w1(c1) AS
+ (WITH w2(c2) AS
+ (WITH w3(c3) AS
+ (WITH w4(c4) AS
+ (WITH w5(c5) AS
+ (WITH RECURSIVE w6(c6) AS
+ (WITH w6(c6) AS
+ (WITH w8(c8) AS
+ (SELECT 1)
+ SELECT * FROM w8)
+ SELECT * FROM w6)
+ SELECT * FROM w6)
+ SELECT * FROM w5)
+ SELECT * FROM w4)
+ SELECT * FROM w3)
+ SELECT * FROM w2)
+SELECT * FROM w1;
+-- Detection of invalid self-references
+WITH RECURSIVE outermost(x) AS (
+ SELECT 1
+ UNION (WITH innermost1 AS (
+ SELECT 2
+ UNION (WITH innermost2 AS (
+ SELECT 3
+ UNION (WITH innermost3 AS (
+ SELECT 4
+ UNION (WITH innermost4 AS (
+ SELECT 5
+ UNION (WITH innermost5 AS (
+ SELECT 6
+ UNION (WITH innermost6 AS
+ (SELECT 7)
+ SELECT * FROM innermost6))
+ SELECT * FROM innermost5))
+ SELECT * FROM innermost4))
+ SELECT * FROM innermost3))
+ SELECT * FROM innermost2))
+ SELECT * FROM outermost
+ UNION SELECT * FROM innermost1)
+ )
+ SELECT * FROM outermost ORDER BY 1;
+
--
-- Some examples with a tree
--
end of thread, other threads:[~2021-04-23 18:03 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-04-23 00:13 BUG #16978: Nested CTEs give ERROR in v13 PG Bug reporting form <noreply@postgresql.org>
2021-04-23 14:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-04-23 14:56 ` Bruce Momjian <bruce@momjian.us>
2021-04-23 15:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-04-23 18:03 ` Bruce Momjian <bruce@momjian.us>
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