BUG #19922: CYCLE/SEARCH with a window in the recursive term: could not find replacement targetlist entry

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

Browse pgsql-bugs by date

  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