| From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
|---|---|
| To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
| Cc: | tharakan(at)gmail(dot)com |
| Subject: | BUG #19922: CYCLE/SEARCH with a window in the recursive term: could not find replacement targetlist entry |
| Date: | 2026-11-19 03:27:49 |
| Message-ID: | 19922-a0d338245d361a4c@postgresql.org |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 19922
Logged by: Robins Tharakan
Email address: tharakan(at)gmail(dot)com
PostgreSQL version: Unsupported/Unknown
Operating system: Unsupported/Unknown
Description:
A recursive query with a CYCLE or SEARCH clause fails when its
recursive term has a resjunk column, such as a window function's
ORDER BY column.
With CYCLE, builds without assertions, and assert-enabled builds
of 17 and older, raise
ERROR: XX000: could not find replacement targetlist entry for attno 2
LOCATION: ReplaceVarFromTargetList, rewriteManip.c:1903
and assert-enabled builds of 18 and later fail an assertion before
that:
TRAP: failed Assert("OidIsValid(eqop)"), File: "allpaths.c",
Line: 4602
With SEARCH in place of CYCLE, the same recursive term fails on every
branch, with or without assertions:
ERROR: 42804: attribute 2 of type record has wrong type
DETAIL: Table has type integer, but query expects record[].
LOCATION: CheckVarSlotCompatibility, execExprInterp.c:2429
Commit
======
Master: 8303f2e13c5 (2026-11-18) affected
Also affects: REL_14_STABLE through REL_19_STABLE
Built with --enable-cassert --enable-debug at -O0, and master also
without --enable-cassert at -O2 (gcc 14.2.0, glibc 2.41, Debian 13,
x86_64).
Error Log
========
master (8303f2e13c5), query 1:
TRAP: failed Assert("OidIsValid(eqop)"), File: "allpaths.c", Line: 4602,
PID: 1176274
LOG: client backend (PID 1176274) was terminated by signal 6: Aborted
master without assertions, and REL_17_STABLE (6c8717359a4), query 1:
ERROR: could not find replacement targetlist entry for attno 2
Backtrace
========
The assertion on master, query 1:
#0 ExceptionalCondition (conditionName="OidIsValid(eqop)", ...)
#1 pushdown_var_grouping_eqop (...) at allpaths.c:4602
#2 grouping_conflict_walker (...) at clauses.c:6576
#5 grouping_conflict_walker (...) at clauses.c:6710
#6 expression_has_grouping_conflict (...) at clauses.c:6533
#7 qual_is_pushdown_safe (...) at allpaths.c:4569
#8 set_subquery_pathlist (...) at allpaths.c:2774
#9 set_rel_size (...) at allpaths.c:472
#14 subquery_planner (...) at planner.c:1368
#15 recurse_set_operations (...) at prepunion.c:252
#16 generate_recursion_path (...) at prepunion.c:405
SQL
===
-- 1. CYCLE, with the window's ORDER BY column in the recursive term.
-- Builds without assertions, and 17 and older:
-- ERROR: could not find replacement targetlist entry for attno 2
-- Assert-enabled builds of 18 and later end the session at
-- TRAP: failed Assert("OidIsValid(eqop)"), File: "allpaths.c"
-- It should return (1, f, {(1)}) and (1, t, {(1),(1)}).
WITH RECURSIVE t(x) AS (
SELECT 1
UNION ALL
SELECT max(x) OVER (ORDER BY x) FROM t
) CYCLE x SET m USING p
SELECT * FROM t;
-- 2. The same recursive term with SEARCH, on every branch:
-- ERROR: attribute 2 of type record has wrong type
-- It should return what the term without the window does:
-- 1 | {(1)}
-- 2 | {(1),(2)}
-- 3 | {(1),(2),(3)}
WITH RECURSIVE t(x) AS (
SELECT 1
UNION ALL
SELECT max(x) OVER (ORDER BY x) + 1 FROM t WHERE x < 3
) SEARCH DEPTH FIRST BY x SET o
SELECT * FROM t;
-- 3. Both clauses, on every branch, with or without assertions:
-- ERROR: could not find replacement targetlist entry for attno 4
-- It should return one empty row.
WITH RECURSIVE a AS (
SELECT NULL b, NULL ~ NULL c
UNION
SELECT NULL, NULL ~ max(NULL) OVER (PARTITION BY 0 ORDER BY 1) FROM a
) SEARCH BREADTH FIRST BY b SET d CYCLE c SET e USING f,
g AS (SELECT FROM a)
SELECT;
All three queries fail on master and every supported branch
(and on 14.25). A release build of master (no --enable-cassert, -O2)
raises the XX000 and 42804 errors.
A recursive term with DISTINCT fails the same assertion on 18 and
later, for a different reason: a regression of 44fb59fc605. I'm
reporting that separately.
Found using SQLsmith, SQLReduce and Claude.
-
Robins Tharakan
Databricks
| From | Date | Subject | |
|---|---|---|---|
| Next Message | shihao zhong | 2026-11-19 05:06:11 | Re: BUG #19893: REPLICA IDENTITY FULL: UPDATE/DELETE skipped when a subscriber-only column has a value |
| Previous Message | PG Bug reporting form | 2026-11-19 01:44:39 | BUG #19921: auto_explain Reads SQL Function Parameters from an Unpinned Buffer |