agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t"
9+ messages / 4 participants
[nested] [flat]
* BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t"
@ 2026-10-03 16:17 PG Bug reporting form <noreply@postgresql.org>
2026-10-03 21:53 ` Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: PG Bug reporting form @ 2026-10-03 16:17 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: feasiblechart@gmail.com
The following bug has been logged on the website:
Bug reference: 19742
Logged by: Junwen AN
Email address: feasiblechart@gmail.com
PostgreSQL version: 19beta4
Operating system: Linux
Description:
Please see the repro. Seems like a regression; 19beta4 and the current main
branch both have this error raised, but 18.6 works fine. I ran it with psql
CREATE TABLE d (a int);
INSERT INTO d VALUES (1), (1), (2), (NULL), (3);
SELECT * FROM (SELECT a FROM d INTERSECT ALL SELECT a FROM d
UNION ALL SELECT a FROM d WHERE false) s
WHERE a = 1;
-- ERROR: XX000: could not find pathkey item to sort
(prepare_sort_from_pathkeys, createplan.c)
-- 18.6: a = 1, 1
-- 19beta4 / main: ERROR: could not find pathkey item to sort
-- EXPLAIN (without ANALYZE) fails the same way: the error is raised while
planning.
Did some more digging with LLM, and it seems this works fine
-- ============ workaround: the same query without a sorted SetOp
============
SET enable_sort = off; -- or enable_hashagg = on with statistics
that favour hashing
SELECT * FROM (SELECT a FROM d INTERSECT ALL SELECT a FROM d
UNION ALL SELECT a FROM d WHERE false) s
WHERE a = 1; -- 1, 1 (HashSetOp Intersect All)
RESET enable_sort;
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t"
2026-10-03 16:17 BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" PG Bug reporting form <noreply@postgresql.org>
@ 2026-10-03 21:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-10-04 01:00 ` Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" Tom Lane <tgl@sss.pgh.pa.us>
2026-10-04 05:23 ` Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" shihao zhong <zhong950419@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Tom Lane @ 2026-10-03 21:53 UTC (permalink / raw)
To: feasiblechart@gmail.com; +Cc: David Rowley <dgrowleyml@gmail.com>; pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> Please see the repro. Seems like a regression; 19beta4 and the current main
> branch both have this error raised, but 18.6 works fine. I ran it with psql
> CREATE TABLE d (a int);
> INSERT INTO d VALUES (1), (1), (2), (NULL), (3);
> SELECT * FROM (SELECT a FROM d INTERSECT ALL SELECT a FROM d
> UNION ALL SELECT a FROM d WHERE false) s
> WHERE a = 1;
> -- ERROR: XX000: could not find pathkey item to sort
Bisecting shows this started with
fdda78e361f136ec2b8de579b366c1e66bba1199 is the first bad commit
commit fdda78e361f136ec2b8de579b366c1e66bba1199
Author: David Rowley <drowley@postgresql.org>
Date: Wed Nov 5 11:48:09 2025 +1300
Fix possible usage of incorrect UPPERREL_SETOP RelOptInfo
03d40e4b5 allowed dummy UNION [ALL] children to be removed from the plan
by checking for is_dummy_rel(). That commit neglected to still account
for the relids from the dummy rel so that the correct UPPERREL_SETOP
RelOptInfo could be found and used for adding the Paths to.
I suspect that that commit just allowed reaching some pre-existing
mistake, but I've not dug into it.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t"
2026-10-03 16:17 BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" PG Bug reporting form <noreply@postgresql.org>
2026-10-03 21:53 ` Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-10-04 01:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-10-04 05:37 ` Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" David Rowley <dgrowleyml@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Tom Lane @ 2026-10-04 01:00 UTC (permalink / raw)
To: feasiblechart@gmail.com; +Cc: David Rowley <dgrowleyml@gmail.com>; pgsql-bugs@lists.postgresql.org
I wrote:
> Bisecting shows this started with
> fdda78e361f136ec2b8de579b366c1e66bba1199 is the first bad commit
> I suspect that that commit just allowed reaching some pre-existing
> mistake, but I've not dug into it.
After looking a bit closer, v18 produces this plan:
Append (cost=84.23..84.46 rows=14 width=4)
-> SetOp Intersect All (cost=84.23..84.39 rows=13 width=4)
-> Sort (cost=42.12..42.15 rows=13 width=4)
Sort Key: d.a
-> Seq Scan on d (cost=0.00..41.88 rows=13 width=4)
Filter: (a = 1)
-> Sort (cost=42.12..42.15 rows=13 width=4)
Sort Key: d_1.a
-> Seq Scan on d d_1 (cost=0.00..41.88 rows=13 width=4)
Filter: (a = 1)
-> Result (cost=0.00..0.00 rows=0 width=0)
One-Time Filter: false
v19/HEAD produce a Path that is equivalent to v18's except for two
things:
* The empty-query Result isn't there; evidently we figured out that
it's useless and tossed it. So now the AppendPath has only one child.
* The AppendPath is marked as having pathkeys:
:path.pathkeys (
{PATHKEY
:pk_eclass
{EQUIVALENCECLASS
:ec_opfamilies (o 1976)
:ec_collation 0
:ec_childmembers_size 0
:ec_members (
{EQUIVALENCEMEMBER
:em_expr
{VAR
:varno 1
:varattno 1
:vartype 23
:vartypmod -1
:varcollid 0
:varnullingrels (b)
:varlevelsup 0
:varreturningtype 0
:varnosyn 1
:varattnosyn 1
:location -1
}
:em_relids (b 1)
...
whereas in v18 it has nil pathkeys. The immediate problem is that
create_append_plan calls prepare_sort_from_pathkeys to try to
create a representation of the pathkey in terms of the Append's
tlist, and what's in the Append's tlist is
{VAR
:varno 0
:varattno 1
:vartype 23
:vartypmod -1
:varcollid 0
:varnullingrels (b)
:varlevelsup 0
:varreturningtype 0
:varnosyn 0
:varattnosyn 1
:location -1
}
that is the tlist has been translated to the "varno zero"
representation that prepunion.c generates. So we fail to
match the 1/1 Var to this 0/1 Var, and kaboom.
So the seeds of this problem go far back, but the immediate
cause is that we're labeling the AppendPath with pathkeys
in cases where we did not before, and our implementation can't
actually support that. Interestingly, this doesn't fail:
explain SELECT * FROM ((SELECT a FROM d INTERSECT ALL SELECT a FROM d)
union all (SELECT a FROM d INTERSECT ALL SELECT a FROM d)) s
WHERE a = 1;
QUERY PLAN
-------------------------------------------------------------------------
Append (cost=84.23..168.92 rows=26 width=4)
-> SetOp Intersect All (cost=84.23..84.39 rows=13 width=4)
-> Sort (cost=42.12..42.15 rows=13 width=4)
Sort Key: d.a
-> Seq Scan on d (cost=0.00..41.88 rows=13 width=4)
Filter: (a = 1)
-> Sort (cost=42.12..42.15 rows=13 width=4)
Sort Key: d_1.a
-> Seq Scan on d d_1 (cost=0.00..41.88 rows=13 width=4)
Filter: (a = 1)
-> SetOp Intersect All (cost=84.23..84.39 rows=13 width=4)
-> Sort (cost=42.12..42.15 rows=13 width=4)
Sort Key: d_2.a
-> Seq Scan on d d_2 (cost=0.00..41.88 rows=13 width=4)
Filter: (a = 1)
-> Sort (cost=42.12..42.15 rows=13 width=4)
Sort Key: d_3.a
-> Seq Scan on d d_3 (cost=0.00..41.88 rows=13 width=4)
Filter: (a = 1)
and the reason it doesn't fail is that the AppendPath has nil pathkeys
in this case. So (I speculate that) we never attached pathkeys to a
UNION ALL AppendPath before, and the reason we're trying to now has
something to do with having reduced the child list to a singleton.
I'm too tired to dig any further tonight.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t"
2026-10-03 16:17 BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" PG Bug reporting form <noreply@postgresql.org>
2026-10-03 21:53 ` Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" Tom Lane <tgl@sss.pgh.pa.us>
2026-10-04 01:00 ` Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-10-04 05:37 ` David Rowley <dgrowleyml@gmail.com>
2026-10-04 05:42 ` Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: David Rowley @ 2026-10-04 05:37 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: feasiblechart@gmail.com; pgsql-bugs@lists.postgresql.org
On Sun, 4 Oct 2026 at 14:00, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> and the reason it doesn't fail is that the AppendPath has nil pathkeys
> in this case. So (I speculate that) we never attached pathkeys to a
> UNION ALL AppendPath before, and the reason we're trying to now has
> something to do with having reduced the child list to a singleton.
It looks like a bug in add_setop_child_rel_equivalences(). It wrongly
assumes that setop_pathkeys will contain a PathKey for each tlist
entry. The problem query has a redundant PathKey due to the WHERE a =
1.
I think the fix needs to be either don't remove redundant pathkeys for
setop_pathkeys or use some other method to figure out which
expressions to add in add_child_eq_member().
David
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t"
2026-10-03 16:17 BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" PG Bug reporting form <noreply@postgresql.org>
2026-10-03 21:53 ` Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" Tom Lane <tgl@sss.pgh.pa.us>
2026-10-04 01:00 ` Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" Tom Lane <tgl@sss.pgh.pa.us>
2026-10-04 05:37 ` Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" David Rowley <dgrowleyml@gmail.com>
@ 2026-10-04 05:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-10-04 17:01 ` Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2026-10-04 05:42 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: feasiblechart@gmail.com; pgsql-bugs@lists.postgresql.org
David Rowley <dgrowleyml@gmail.com> writes:
> I think the fix needs to be either don't remove redundant pathkeys for
> setop_pathkeys or use some other method to figure out which
> expressions to add in add_child_eq_member().
My own thoughts were along the lines of "don't ever assign pathkeys to
an AppendPath"; not sure if that's equivalent to your first idea.
In the long run I'd like to get rid of the varno-zero business
in favor of some less-magic representation; but that's clearly
not reasonable for v19.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t"
2026-10-03 16:17 BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" PG Bug reporting form <noreply@postgresql.org>
2026-10-03 21:53 ` Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" Tom Lane <tgl@sss.pgh.pa.us>
2026-10-04 01:00 ` Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" Tom Lane <tgl@sss.pgh.pa.us>
2026-10-04 05:37 ` Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" David Rowley <dgrowleyml@gmail.com>
2026-10-04 05:42 ` Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-10-04 17:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-10-04 18:41 ` Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" shihao zhong <zhong950419@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2026-10-04 17:01 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: feasiblechart@gmail.com; pgsql-bugs@lists.postgresql.org
I wrote:
> My own thoughts were along the lines of "don't ever assign pathkeys to
> an AppendPath"; not sure if that's equivalent to your first idea.
Concretely, the attached fixes the given test case. There are other
calls to create_append_path in prepunion.c, and I think they may
all need to do likewise, but I didn't analyze them.
It'd be nominally cleaner to add a flag to create_append_path telling
it whether it's allowed to override the given pathkeys. I didn't do
that here because it seems like this is a localized problem that
should eventually be fixed inside prepunion.c, but there's room to
argue differently.
regards, tom lane
Attachments:
[text/x-diff] wip-fix-bad-pathkeys-for-UNION-append.patch (1.0K, ../../401041.1791133299@sss.pgh.pa.us/2-wip-fix-bad-pathkeys-for-UNION-append.patch)
download | inline diff:
diff --git a/src/backend/optimizer/prep/prepunion.c b/src/backend/optimizer/prep/prepunion.c
index b136f12ff3b..c4e95f14dc5 100644
--- a/src/backend/optimizer/prep/prepunion.c
+++ b/src/backend/optimizer/prep/prepunion.c
@@ -862,6 +862,17 @@ generate_union_paths(SetOperationStmt *op, PlannerInfo *root,
apath = (Path *) create_append_path(root, result_rel, cheapest,
NIL, NULL, 0, false, -1);
+ /*
+ * Although we told create_append_path to assign NIL pathkeys to the
+ * AppendPath, it may have overridden that (if there's just one surviving
+ * child path, it will use that path's pathkeys). However, createplan.c
+ * will fail because the append relation's tlist contains varno-0 Vars
+ * (cf. generate_append_tlist), which won't match what is in the pathkeys.
+ * We need to fix that someday, but for now, just force the AppendPath's
+ * pathkeys back to NIL.
+ */
+ apath->pathkeys = NIL;
+
/*
* Initialize the result row estimate to the total input size. This is
* correct for UNION ALL; for the UNION case it is overwritten below with
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t"
2026-10-03 16:17 BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" PG Bug reporting form <noreply@postgresql.org>
2026-10-03 21:53 ` Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" Tom Lane <tgl@sss.pgh.pa.us>
2026-10-04 01:00 ` Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" Tom Lane <tgl@sss.pgh.pa.us>
2026-10-04 05:37 ` Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" David Rowley <dgrowleyml@gmail.com>
2026-10-04 05:42 ` Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" Tom Lane <tgl@sss.pgh.pa.us>
2026-10-04 17:01 ` Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-10-04 18:41 ` shihao zhong <zhong950419@gmail.com>
2026-10-04 19:39 ` Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: shihao zhong @ 2026-10-04 18:41 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David Rowley <dgrowleyml@gmail.com>; feasiblechart@gmail.com; pgsql-bugs@lists.postgresql.org
Hi Tom,
> There are other calls to create_append_path in prepunion.c, and I
> think they may all need to do likewise, but I didn't analyze them.
The EXCEPT ALL one needs it too. With only your patch this still
fails:
set enable_hashagg = off;
(select a from d where a = 1 intersect select a from d where a = 1)
except all select a from d where false;
The v2-0001 I posted upthread changes both places. The partial
Append looks safe, its children always have NIL pathkeys.
Thanks,
Shihao
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t"
2026-10-03 16:17 BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" PG Bug reporting form <noreply@postgresql.org>
2026-10-03 21:53 ` Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" Tom Lane <tgl@sss.pgh.pa.us>
2026-10-04 01:00 ` Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" Tom Lane <tgl@sss.pgh.pa.us>
2026-10-04 05:37 ` Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" David Rowley <dgrowleyml@gmail.com>
2026-10-04 05:42 ` Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" Tom Lane <tgl@sss.pgh.pa.us>
2026-10-04 17:01 ` Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" Tom Lane <tgl@sss.pgh.pa.us>
2026-10-04 18:41 ` Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" shihao zhong <zhong950419@gmail.com>
@ 2026-10-04 19:39 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 9+ messages in thread
From: Tom Lane @ 2026-10-04 19:39 UTC (permalink / raw)
To: shihao zhong <zhong950419@gmail.com>; +Cc: David Rowley <dgrowleyml@gmail.com>; feasiblechart@gmail.com; pgsql-bugs@lists.postgresql.org
shihao zhong <zhong950419@gmail.com> writes:
> Hi Tom,
>> There are other calls to create_append_path in prepunion.c, and I
>> think they may all need to do likewise, but I didn't analyze them.
> The EXCEPT ALL one needs it too. With only your patch this still
> fails:
> set enable_hashagg = off;
> (select a from d where a = 1 intersect select a from d where a = 1)
> except all select a from d where false;
Ah. I'd been trying to make a test case for that one, but I didn't
realize that two levels of setop are required. Something like this
doesn't fail:
explain
select * from
((select * from int8_tbl i81 order by q1)
except all (select * from int8_tbl i81b where false)) ss1,
((select * from int8_tbl i82 order by q1)
except all (select * from int8_tbl i82b where false)) ss2
where ss1.q1 = ss2.q1
;
However, digging into the guts of that doesn't leave a warm feeling
either. Unpatched, we end up with the same situation where a
single-child AppendPath has a tlist containing varno-0 Vars and a
pathkey, and create_append_plan tries to compute sort column info from
that. The reason it fails to fail is that *the equivalence classes
contain varno-0 Vars too*. I've not entirely figured out why this
is different from the original test case --- well, okay, UNION ALL
at the top level is different because it doesn't make any pathkeys,
but if you change that to UNION the test case still fails on unpatched
code, and in that case the pathkey-slinging sure looks the same.
Regardless of the detailed reason for that, having varno-0 Vars in
equivalence classes scares the dickens out of me. Each setop node
is going to have its own varno-0 Vars, and if they match on type then
the equivalence class machinery can't tell them apart, so it sure
seems like we are at risk of drawing false conclusions about whether
different setop outputs are sorted alike. In the above example I was
trying to break it by having it falsely deduce that the outer WHERE
clause could be thrown away or reduced to an IS NOT NULL test.
I failed, which turns out to be because the questionable eclasses are
not at top level but within the two subroots associated with the two
setop nests. So I think the potential bad effects are limited to
maybe mistakenly planning a single setop nest, and so far we've
escaped issues mainly because we don't do that much optimization of
non-UNION-ALL nests. But it's really past time to get rid of the
varno-0 representation. For now, one reason I like forcing these
paths' pathkeys to nil is that it limits the amount of damage that
could be done by false equivalence-class reasoning. In particular,
I wonder whether v19/HEAD are at risk of such bugs in cases that
couldn't occur before we started eliding dummy child setops.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t"
2026-10-03 16:17 BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" PG Bug reporting form <noreply@postgresql.org>
2026-10-03 21:53 ` Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-10-04 05:23 ` shihao zhong <zhong950419@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: shihao zhong @ 2026-10-04 05:23 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: feasiblechart@gmail.com, David Rowley <dgrowleyml@gmail.com>; pgsql-bugs@lists.postgresql.org
Hi Tom,
> I suspect that that commit just allowed reaching some pre-existing
> mistake, but I've not dug into it.
Yes, I think the mistake is older.
The "WHERE false" path is removed, so the UNION ALL has one child
left, the INTERSECT. For an Append with one child,
create_append_path() copies the child's pathkeys. Later
create_append_plan() looks for those sort columns in the Append's
own targetlist. It can't find them, and we get the error.
It can't find them because the SetOp's pathkeys are wrong. A sorted
SetOp reuses the pathkeys of its left input. Here "a = 1" makes the
subquery skip its own sort , so we add a Sort on top of the subquery.
That Sort's pathkeys are built from the subquery's columns, not from
the SetOp's output columns.
There is a second way to get wrong pathkeys, with no SetOp at all.
When the column types differ, recurse_set_operations() adds a
projection but keeps the old pathkeys
(select a from d union select a from d)
union all select a::numeric from d where false;
So I did not fix the SetOp. 0001 makes these one-child Appends drop
the child's pathkeys, which covers both cases. The planner adds a
Sort above if it needs the order. 0002 adds tests for the three
queries.
This is the small fix, meant for 19 and master.
My first try, v1, only fixed the SetOp's pathkeys. It fixed the
reported query but changed many plans, so I think it is too much for
19.
I think a better fix would make the pathkeys right in both places,
so the Append can keep them. I can work on that for master if you
like.
Thanks,
Shihao
Attachments:
[application/octet-stream] v2-0002-Add-tests-for-setop-Appends-left-with-a-single-ch.patch (3.6K, ../../CAGRkXqTK91Bca0Z7+d7CSENAM08LsqhNT8t5KAFRCEY0NzTsUQ@mail.gmail.com/3-v2-0002-Add-tests-for-setop-Appends-left-with-a-single-ch.patch)
download | inline diff:
From 34787017912a7692d82bbc287ef42452ab30b254 Mon Sep 17 00:00:00 2001
From: Shihao <zhong950419@gmail.com>
Date: Sat, 3 Oct 2026 22:00:46 -0600
Subject: [PATCH v2 2/2] Add tests for setop Appends left with a single child
Reported-by: Junwen AN <feasiblechart@gmail.com>
---
src/test/regress/expected/union.out | 61 +++++++++++++++++++++++++++++
src/test/regress/sql/union.sql | 24 ++++++++++++
2 files changed, 85 insertions(+)
diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out
index 84abcd6b14f..ad2573b247b 100644
--- a/src/test/regress/expected/union.out
+++ b/src/test/regress/expected/union.out
@@ -1388,6 +1388,67 @@ SELECT ten FROM tenk1 dummy WHERE 1=2;
Output: t2.four
(11 rows)
+-- Ensure the Append left with a single child after removing an empty input
+-- doesn't use the child's pathkeys.
+SET enable_hashagg = off;
+EXPLAIN (COSTS OFF)
+SELECT two FROM tenk1 WHERE two = 1
+INTERSECT ALL
+SELECT four FROM tenk1 WHERE four = 1
+UNION ALL
+SELECT ten FROM tenk1 WHERE 1=2;
+ QUERY PLAN
+---------------------------------------
+ SetOp Intersect All
+ -> Sort
+ Sort Key: tenk1.two
+ -> Seq Scan on tenk1
+ Filter: (two = 1)
+ -> Sort
+ Sort Key: tenk1_1.four
+ -> Seq Scan on tenk1 tenk1_1
+ Filter: (four = 1)
+(9 rows)
+
+EXPLAIN (COSTS OFF)
+(SELECT two FROM tenk1 WHERE two = 1
+ INTERSECT
+ SELECT four FROM tenk1 WHERE four = 1)
+EXCEPT ALL
+SELECT ten FROM tenk1 WHERE 1=2;
+ QUERY PLAN
+---------------------------------------
+ SetOp Intersect
+ -> Sort
+ Sort Key: tenk1.two
+ -> Seq Scan on tenk1
+ Filter: (two = 1)
+ -> Sort
+ Sort Key: tenk1_1.four
+ -> Seq Scan on tenk1 tenk1_1
+ Filter: (four = 1)
+(9 rows)
+
+-- As above, but the child's output needs a type coercion
+EXPLAIN (COSTS OFF)
+(SELECT two FROM tenk1 UNION SELECT four FROM tenk1)
+UNION ALL
+SELECT ten::numeric FROM tenk1 WHERE 1=2;
+ QUERY PLAN
+---------------------------------------------------
+ Result
+ -> Unique
+ -> Merge Append
+ Sort Key: tenk1.two
+ -> Sort
+ Sort Key: tenk1.two
+ -> Seq Scan on tenk1
+ -> Sort
+ Sort Key: tenk1_1.four
+ -> Seq Scan on tenk1 tenk1_1
+(10 rows)
+
+RESET enable_hashagg;
-- Test constraint exclusion of UNION ALL subqueries
explain (costs off)
SELECT * FROM
diff --git a/src/test/regress/sql/union.sql b/src/test/regress/sql/union.sql
index c8de276c2b5..8a0d32e5400 100644
--- a/src/test/regress/sql/union.sql
+++ b/src/test/regress/sql/union.sql
@@ -531,6 +531,30 @@ SELECT four FROM tenk1 t2
UNION
SELECT ten FROM tenk1 dummy WHERE 1=2;
+-- Ensure the Append left with a single child after removing an empty input
+-- doesn't use the child's pathkeys.
+SET enable_hashagg = off;
+EXPLAIN (COSTS OFF)
+SELECT two FROM tenk1 WHERE two = 1
+INTERSECT ALL
+SELECT four FROM tenk1 WHERE four = 1
+UNION ALL
+SELECT ten FROM tenk1 WHERE 1=2;
+
+EXPLAIN (COSTS OFF)
+(SELECT two FROM tenk1 WHERE two = 1
+ INTERSECT
+ SELECT four FROM tenk1 WHERE four = 1)
+EXCEPT ALL
+SELECT ten FROM tenk1 WHERE 1=2;
+
+-- As above, but the child's output needs a type coercion
+EXPLAIN (COSTS OFF)
+(SELECT two FROM tenk1 UNION SELECT four FROM tenk1)
+UNION ALL
+SELECT ten::numeric FROM tenk1 WHERE 1=2;
+RESET enable_hashagg;
+
-- Test constraint exclusion of UNION ALL subqueries
explain (costs off)
SELECT * FROM
--
2.37.1 (Apple Git-137.1)
[application/octet-stream] v2-0001-Don-t-let-single-child-setop-Appends-inherit-path.patch (1.8K, ../../CAGRkXqTK91Bca0Z7+d7CSENAM08LsqhNT8t5KAFRCEY0NzTsUQ@mail.gmail.com/4-v2-0001-Don-t-let-single-child-setop-Appends-inherit-path.patch)
download | inline diff:
From 9f689d942e6ffa5f213367dfda3e1162a61b57be Mon Sep 17 00:00:00 2001
From: Shihao <zhong950419@gmail.com>
Date: Sat, 3 Oct 2026 22:00:46 -0600
Subject: [PATCH v2 1/2] Don't let single-child setop Appends inherit pathkeys
When all but one input of a UNION is proven empty, or the right
input of an EXCEPT ALL is, we use an Append with a single child.
create_append_path() copies the child's pathkeys in that case, but
in a setop tree those need not match the Append's targetlist, and
planning could fail with "could not find pathkey item to sort".
Clear the pathkeys of such Appends.
Bug: #19742
Reported-by: Junwen AN <feasiblechart@gmail.com>
---
src/backend/optimizer/prep/prepunion.c | 9 +++++++++
1 file changed, 9 insertions(+)
diff --git a/src/backend/optimizer/prep/prepunion.c b/src/backend/optimizer/prep/prepunion.c
index b136f12ff3b..9d0acad49f0 100644
--- a/src/backend/optimizer/prep/prepunion.c
+++ b/src/backend/optimizer/prep/prepunion.c
@@ -862,6 +862,12 @@ generate_union_paths(SetOperationStmt *op, PlannerInfo *root,
apath = (Path *) create_append_path(root, result_rel, cheapest,
NIL, NULL, 0, false, -1);
+ /*
+ * A single-child Append inherits its child's pathkeys, but those might
+ * not match this setop's targetlist.
+ */
+ apath->pathkeys = NIL;
+
/*
* Initialize the result row estimate to the total input size. This is
* correct for UNION ALL; for the UNION case it is overwritten below with
@@ -1225,6 +1231,9 @@ generate_nonunion_paths(SetOperationStmt *op, PlannerInfo *root,
append, NIL, NULL, 0,
false, -1);
+ /* as in generate_union_paths, don't trust child pathkeys */
+ apath->pathkeys = NIL;
+
add_path(result_rel, apath);
return result_rel;
--
2.37.1 (Apple Git-137.1)
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2026-10-04 19:39 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-03 16:17 BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" PG Bug reporting form <noreply@postgresql.org>
2026-10-03 21:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-10-04 01:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-10-04 05:37 ` David Rowley <dgrowleyml@gmail.com>
2026-10-04 05:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-10-04 17:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-10-04 18:41 ` shihao zhong <zhong950419@gmail.com>
2026-10-04 19:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-10-04 05:23 ` shihao zhong <zhong950419@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox