pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17243: explain wtih recursive cte error?
2+ messages / 2 participants
[nested] [flat]
* BUG #17243: explain wtih recursive cte error?
@ 2021-10-22 00:49 PG Bug reporting form <noreply@postgresql.org>
2021-10-22 13:52 ` Re: BUG #17243: explain wtih recursive cte error? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2021-10-22 00:49 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: mark.a.sloan@gmail.com
The following bug has been logged on the website:
Bug reference: 17243
Logged by: mark sloan
Email address: mark.a.sloan@gmail.com
PostgreSQL version: 14.0
Operating system: MacOS
Description:
Seeing a issue with 'EXPLAIN' throwing an error on a query that otherwise
runs without issue.
example query case.
---------------------------------------------
select x.v into explainctebug from (values('foo'),('bar'),('baz')) as
x(v);
query:
with recursive a as
(
select v, '' as x, 0 as n from explainctebug
union all
select v, substring(v,n+1,1) as x, n+1 as n from a where n < length(v)
)
select x, count(*) from a where n > 0
group by x order by count(*) desc;
returns.
x | count
---+-------
a | 2
b | 2
o | 2
r | 1
z | 1
f | 1
(6 rows)
--------
EXPLAIN verbose with recursive a as
(
select v, '' as x, 0 as n from explainctebug
union all
select v, substring(v,n+1,1) as x, n+1 as n from a where n < length(v)
)
select x, count(*) from a where n > 0
group by x order by count(*) desc ;
returns
QUERY PLAN
---------------------------------------------------------------------------------------
Sort (cost=5835.36..5835.86 rows=200 width=40)
Output: a.x, (count(*))
Sort Key: (count(*)) DESC
CTE a
-> Recursive Union (cost=0.00..4697.37 rows=46690 width=68)
-> Seq Scan on public.explainctebug (cost=0.00..23.60 rows=1360
width=68)
Output: explainctebug.v, ''::text, 0
-> WorkTable Scan on a a_1 (cost=0.00..374.00 rows=4533
width=68)
Output: a_1.v, "substring"(a_1.v, (a_1.n + 1), 1), (a_1.n +
1)
Filter: (a_1.n < length(a_1.v))
-> HashAggregate (cost=1128.34..1130.34 rows=200 width=40)
Output: a.x, count(*)
Group Key: a.x
-> CTE Scan on a (cost=0.00..1050.52 rows=15563 width=32)
Output: a.v, a.x, a.n
Filter: (a.n > 0)
(16 rows)
EXPLAIN with recursive a as
(
select v, '' as x, 0 as n from explainctebug
union all
select v, substring(v,n+1,1) as x, n+1 as n from a where n < length(v)
)
select x, count(*) from a where n > 0
group by x order by count(*) desc ;
returns.
ERROR: could not find RecursiveUnion for WorkTableScan with wtParam 0
this seems like it might be a bug?
PostgreSQL 14.0 on x86_64-apple-darwin20.6.0, compiled by Apple clang
version 13.0.0 (clang-1300.0.29.3), 64-bit
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #17243: explain wtih recursive cte error?
2021-10-22 00:49 BUG #17243: explain wtih recursive cte error? PG Bug reporting form <noreply@postgresql.org>
@ 2021-10-22 13:52 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2021-10-22 13:52 UTC (permalink / raw)
To: mark.a.sloan@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> Seeing a issue with 'EXPLAIN' throwing an error on a query that otherwise
> runs without issue.
Thanks for the report! It's a duplicate though; we already fixed this
for 14.1 [1]. Or at least, your example works for me on HEAD and
REL_14_STABLE branch tip.
regards, tom lane
[1] https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=2c25db32e
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2021-10-22 13:52 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-10-22 00:49 BUG #17243: explain wtih recursive cte error? PG Bug reporting form <noreply@postgresql.org>
2021-10-22 13:52 ` Tom Lane <tgl@sss.pgh.pa.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