BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t"
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>
Дата:
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;
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>
Дата:
PG Bug reporting form 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
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
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>
Дата:
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
Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t"
Hi Tom,
> 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.
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 thesubquery 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
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.
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
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>
Дата:
On Sun, 4 Oct 2026 at 14:00, Tom Lane 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
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>
Дата:
David Rowley 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
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>
Дата:
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 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
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>
Дата:
Hi Tom,
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,
> 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.
> 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
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>
Дата:
shihao zhong 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
Re: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t"
Hi Tom,
I looked for one with random queries. The attached script builds
nested setops with empty arms and mixed int and numeric columns,
puts an ORDER BY, a join or a second setop nest above them, and
checks the output against an answer worked out in Python. Obviously
> 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.
> cases that couldn't occur before we started eliding dummy child
> setops.
I looked for one with random queries. The attached script builds
nested setops with empty arms and mixed int and numeric columns,
puts an ORDER BY, a join or a second setop nest above them, and
checks the output against an answer worked out in Python. Obviously
I worked with Cladue to get that script generated.
I ran 20000 queries, each with seven sets of planner settings. On
HEAD 2516 of the 140000 runs hit the pathkey error. With both
Appends forced to NIL pathkeys none did. There were no wrong
answers in either build.
It did find one more varno 0 problem, in EXPLAIN only. 18 is fine.
create table d1 (a int);
create table d2 (b numeric);
explain (verbose, costs off)
select b from d2 except
(select a from d1 where false except all select a from d1);
ERROR: bogus varno: 0
HEAD 2516 of the 140000 runs hit the pathkey error. With both
Appends forced to NIL pathkeys none did. There were no wrong
answers in either build.
It did find one more varno 0 problem, in EXPLAIN only. 18 is fine.
create table d1 (a int);
create table d2 (b numeric);
explain (verbose, costs off)
select b from d2 except
(select a from d1 where false except all select a from d1);
ERROR: bogus varno: 0
928df067d1e handles a plain Var in the dummy Result's tlist. Here
the Var is under an int to numeric cast, so set_plan_refs() misses
it.
the Var is under an int to numeric cast, so set_plan_refs() misses
it.
The attached patch walks the whole expression. It keeps the
varno 1 rewrite, so for a nested setop the name shown can come from
another child, as in the new test's output.
varno 1 rewrite, so for a nested setop the name shown can come from
another child, as in the new test's output.
Thanks,
Shihao
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>
Дата:
shihao zhong writes:
> 928df067d1e handles a plain Var in the dummy Result's tlist. Here
> the Var is under an int to numeric cast, so set_plan_refs() misses
> it.
> The attached patch walks the whole expression. It keeps the
> varno 1 rewrite, so for a nested setop the name shown can come from
> another child, as in the new test's output.
Good catch, but there's another problem here. I wondered why the
Result is claiming to output "two", when that is not either of
the columns being output by the removed setop leaf queries.
This same code is at fault: it's injecting varno "1" without
regard for which of the leaf queries are actually represented.
Fortunately, now that we have Result.relids, it's pretty easy
to discover which leaf queries are represented and choose the
leftmost one. Hence, v2 attached.
By the way, I'm still not super happy about
+ Replaces: Aggregate on unnamed_subquery, unnamed_subquery_1
when there is no aggregation going on anywhere. But that's
because Robert took shortcuts: show_result_replacement_info
does
case RESULT_TYPE_UPPER:
/* a small white lie */
replacement_type = "Aggregate";
break;
without regard for the actual reason the Result got injected.
I recall complaining about that and Robert not wanting to add
yet more complexity to what he was doing. Which is fair,
but I still think we're gonna get bug reports about this.
regards, tom lane
From 40c46b9c5147780668da5670f62e94af3301a5a8 Mon Sep 17 00:00:00 2001
From: Tom Lane
Date: Sun, 4 Oct 2026 20:26:31 -0400
Subject: [PATCH v2] Fix EXPLAIN of dummy set operations some more.
EXPLAIN failed to deal with varno-0 Vars that are made by prepunion.c
and can survive into a finished plan in the case where a provably
empty set operation is replaced by a dummy Result (which is possible
since 03d40e4b5). Commit 928df067d tried to fix this, but it was a
couple bricks shy of a load. First, it only dealt with varno-0 Vars
at the top level of the Result's tlist, but they could be buried
under coercion expressions. Fix that by doing a recursive mutation.
Second, it always replaced varno 0 with varno 1, but that's just
wrong: the Result might represent a group of setop leaf queries that
do not include the leftmost leaf. That led to displaying the wrong
variable(s) as outputs of the Result, risking confusion. Fortunately,
we can get the actual child relids from the recently-added
Result.relids field, and use that to discover the leftmost child
represented by the Result.
This was found in discussion of bug #19742, but it's really an
independent issue.
Author: shihao zhong
Co-authored-by: Tom Lane
Discussion: https://postgr.es/m/CAGRkXqTFwygKmjLG_Y=kbXRHsePFmD6k+qrzVLP4_KrG+-=oRg@mail.gmail.com
Backpatch-through: 19
---
src/backend/optimizer/plan/setrefs.c | 69 ++++++++++++++++++++--------
src/test/regress/expected/union.out | 20 ++++++++
src/test/regress/sql/union.sql | 9 ++++
3 files changed, 79 insertions(+), 19 deletions(-)
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index 8a641402a96..327c0febe89 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -155,6 +155,7 @@ static Plan *set_mergeappend_references(PlannerInfo *root,
int rtoffset);
static void set_hash_references(PlannerInfo *root, Plan *plan, int rtoffset);
static Relids offset_relid_set(Relids relids, int rtoffset);
+static Node *fix_dummy_setop_vars_mutator(Node *node, int *first_child_relid);
static Node *fix_scan_expr(PlannerInfo *root, Node *node,
int rtoffset, double num_exec);
static Node *fix_scan_expr_mutator(Node *node, fix_scan_expr_context *context);
@@ -1041,6 +1042,8 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset)
set_upper_references(root, plan, rtoffset);
else
{
+ int first_child_relid;
+
/*
* The tlist of a childless Result could contain
* unresolved ROWID_VAR Vars, in case it's representing a
@@ -1054,33 +1057,35 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset)
* shouldn't be seen by fix_scan_expr.
*
* We also must handle the case where set operations have
- * been short-circuited resulting in a dummy Result node.
- * prepunion.c uses varno==0 for the set op targetlist.
- * See generate_setop_tlist() and generate_setop_tlist().
- * Here we rewrite these to use varno==1, which is the
- * varno of the first set-op child. Without this, EXPLAIN
+ * been proven empty, resulting in a dummy Result node.
+ * Because prepunion.c uses varno 0 for setop targetlists,
+ * that's what we'll find here. Replace such Vars with
+ * Vars pointing at the Result's lowest-numbered replaced
+ * rel, which will be its leftmost set-op child. While we
+ * can assume that ROWID_VARs are at top level, varno 0
+ * Vars might be buried in coercion expressions, so that
+ * needs a recursive traversal. Without this, EXPLAIN
* will have trouble displaying targetlists of dummy set
* operations.
+ *
+ * Note that some Results have empty relids, leading to
+ * first_child_relid being negative. We assume such
+ * Results can't contain any varno 0 Vars.
*/
+ first_child_relid = bms_next_member(splan->relids, -1);
foreach(l, splan->plan.targetlist)
{
TargetEntry *tle = (TargetEntry *) lfirst(l);
Var *var = (Var *) tle->expr;
- if (var && IsA(var, Var))
- {
- if (var->varno == ROWID_VAR)
- tle->expr = (Expr *) makeNullConst(var->vartype,
- var->vartypmod,
- var->varcollid);
- else if (var->varno == 0)
- tle->expr = (Expr *) makeVar(1,
- var->varattno,
- var->vartype,
- var->vartypmod,
- var->varcollid,
- var->varlevelsup);
- }
+ if (var && IsA(var, Var) && var->varno == ROWID_VAR)
+ tle->expr = (Expr *) makeNullConst(var->vartype,
+ var->vartypmod,
+ var->varcollid);
+ else if (first_child_relid > 0)
+ tle->expr = (Expr *)
+ fix_dummy_setop_vars_mutator((Node *) tle->expr,
+ &first_child_relid);
}
splan->plan.targetlist =
@@ -2246,6 +2251,32 @@ fix_alternative_subplan(PlannerInfo *root, AlternativeSubPlan *asplan,
return (Node *) bestplan;
}
+/*
+ * fix_dummy_setop_vars_mutator
+ * Change the varno 0 Vars made by prepunion.c to varno *first_child_relid.
+ */
+static Node *
+fix_dummy_setop_vars_mutator(Node *node, int *first_child_relid)
+{
+ if (node == NULL)
+ return NULL;
+ if (IsA(node, Var))
+ {
+ Var *var = (Var *) node;
+
+ if (var->varno == 0)
+ return (Node *) makeVar(*first_child_relid,
+ var->varattno,
+ var->vartype,
+ var->vartypmod,
+ var->varcollid,
+ var->varlevelsup);
+ return node;
+ }
+ return expression_tree_mutator(node, fix_dummy_setop_vars_mutator,
+ first_child_relid);
+}
+
/*
* fix_scan_expr
* Do set_plan_references processing on a scan-level expression
diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out
index 84abcd6b14f..f07b6141e75 100644
--- a/src/test/regress/expected/union.out
+++ b/src/test/regress/expected/union.out
@@ -1388,6 +1388,26 @@ SELECT ten FROM tenk1 dummy WHERE 1=2;
Output: t2.four
(11 rows)
+-- Ensure EXPLAIN can show a dummy set operation whose output is coerced
+-- to another type by the parent set operation.
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT two::numeric FROM tenk1 t1
+EXCEPT
+(SELECT four FROM tenk1 dummy WHERE 1=2
+ EXCEPT ALL
+ SELECT ten FROM tenk1 t2);
+ QUERY PLAN
+---------------------------------------------------------------------
+ HashSetOp Except
+ Output: ((t1.two)::numeric)
+ -> Seq Scan on public.tenk1 t1
+ Output: (t1.two)::numeric
+ -> Result
+ Output: unnamed_subquery.four
+ Replaces: Aggregate on unnamed_subquery, unnamed_subquery_1
+ One-Time Filter: false
+(8 rows)
+
-- 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..a787dd25d23 100644
--- a/src/test/regress/sql/union.sql
+++ b/src/test/regress/sql/union.sql
@@ -531,6 +531,15 @@ SELECT four FROM tenk1 t2
UNION
SELECT ten FROM tenk1 dummy WHERE 1=2;
+-- Ensure EXPLAIN can show a dummy set operation whose output is coerced
+-- to another type by the parent set operation.
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT two::numeric FROM tenk1 t1
+EXCEPT
+(SELECT four FROM tenk1 dummy WHERE 1=2
+ EXCEPT ALL
+ SELECT ten FROM tenk1 t2);
+
-- Test constraint exclusion of UNION ALL subqueries
explain (costs off)
SELECT * FROM
--
2.52.0
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>
Дата:
Hi Tom,
> Fortunately, now that we have Result.relids, it's pretty easy> to discover which leaf queries are represented and choose the
> leftmost one. Hence, v2 attached.
Thanks, v2 looks good to me. With it, EXPLAIN works on all of the
same 20000 random queries.
With the dummy setop inside a subquery, under a JOIN or GROUP BY,
the Result shows the columns of its own left-most leaf.
Thanks,
Shihao
Thanks,
Shihao
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>
Дата:
shihao zhong writes: > This is the small fix, meant for 19 and master. Sorry for having overlooked this email earlier. It's substantially the same fix I came up with, so I credited you as co-author. I've pushed these two fixes and marked the open item as done. > 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. If you're interested in working on a long-term fix, I think the path forward ought to be to get rid of these "varno 0" Vars in favor of using ordinary Vars that reference real RangeTblEntrys. Right now, a Query level that represents a set-op nest only has RTE_SUBQUERY RTEs for the leaf queries. I'm imagining inventing a new RTEKind, say RTE_SETOP, and building one of those for each set operation in the nest. Then the Vars representing the output columns of that set operation could carry that RTE's index, and everything gets a lot less magic. I'm not sure that there would be any large reduction in total lines of code, but it'd be cleaner, and there are some things such as tlist width estimation that would work better. While we could move much of what's in SetOperationStmt into such RTEs, I'd be inclined not to, because additional fields in RangeTblEntry would just be bloat for non-SETOP RTEs. So my druthers would be to add no new fields to RangeTblEntry, just re-use whatever ones are there that are useful. SetOperationStmt probably needs to gain a field for the index of the associated RTE, though. IIRC, there are XXX comments in prepunion.c whining about how building the setop tlists ought to be done at parse time, so that's something we could look into at the same time, or as a follow-on patch. regards, tom lane
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>
Дата:
Hi Tom,
Thanks for pushing these, and for the credit.
> I'm imagining inventing a new RTEKind, say RTE_SETOP, and building
> one of those for each set operation in the nest.
Yes, I would like to work on that. I will start with the RTE and the
Vars, and leave building the setop tlists at parse time for a
follow-on patch.
I will post the first version in a new thread on hackers.
Thanks,
Shihao
Thanks for pushing these, and for the credit.
> I'm imagining inventing a new RTEKind, say RTE_SETOP, and building
> one of those for each set operation in the nest.
Yes, I would like to work on that. I will start with the RTE and the
Vars, and leave building the setop tlists at parse time for a
follow-on patch.
I will post the first version in a new thread on hackers.
Thanks,
Shihao