public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tender Wang <[email protected]>
To: [email protected]
To: [email protected]
Subject: Re: BUG #19534: Qual pushdown across a window subquery is unsafe with nondeterministic partition collations
Date: Sun, 28 Jun 2026 19:33:47 +0800
Message-ID: <CAHewXNk8xt1pNepcEiCx_e6_SW_=bm1GNnU7+bPczoZJuLC5qQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
PG Bug reporting form <[email protected]> 于2026年6月27日周六 20:55写道：
>
> The following bug has been logged on the website:
>
> Bug reference: 19534
> Logged by: Qifan Liu
> Email address: [email protected]
> PostgreSQL version: 18.4
> Operating system: Ubuntu 20.04 x86-64, docker image postgres:18.4
> Description:
>
> ## Expected Behavior
>
> The window query should return the same result as the reference query. Since
> `'abc'` and `'ABC'` are in the same nondeterministic partition, the
> partition sum for row `'abc'` should be `3`.
>
> ## Actual Behavior
>
> The window query returns `abc | 1 | 1`, while the reference query returns
> `abc | 1 | 3`. `EXPLAIN` shows that the strict-collation filter is pushed
> below `WindowAgg`, which changes the partition before the window sum is
> computed.
>
Yes, I can reproduce this on HEAD.
The whereClause was pushed down to the scan level of table t_window_ci.
So the row with 'ABC' was ignored by the filter.
In current logic, we have no logic to process a nondeterministic
partition clause when considering whether the
qual can be pushed down to the subquery.
I try to fix this with the attached patch.
I add a new flag UNSAFE_HAS_NONDETERMINITIC for
pushdown_safety_info.unsafeFlags.
If the var is in the partition clause and it is nondeterministic, we
set this flag.
In qual_is_pushdown_safe(), we check the qual's inputcollid; if it is
deterministic, but the var in
the partition clause is nondeterministic, it is PUSHDOWN_UNSAFE.
Any thoughts?
--
Thanks,
Tender Wang
Attachments:
[application/octet-stream] 0001-Fix-qual-pushdown-with-nondeterministic-partition-co.patch (2.8K, ../CAHewXNk8xt1pNepcEiCx_e6_SW_=bm1GNnU7+bPczoZJuLC5qQ@mail.gmail.com/2-0001-Fix-qual-pushdown-with-nondeterministic-partition-co.patch)
download | inline diff:
From c99dcb3de3d0e42d03af71027ca4b1cd6d130127 Mon Sep 17 00:00:00 2001
From: Ubuntu <[email protected]>
Date: Sun, 28 Jun 2026 19:21:40 +0800
Subject: [PATCH] Fix qual pushdown with nondeterministic partition collations
---
src/backend/optimizer/path/allpaths.c | 37 +++++++++++++++++++++++++++
1 file changed, 37 insertions(+)
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index c134594a21a..995972e15dc 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -57,6 +57,7 @@
#define UNSAFE_NOTIN_DISTINCTON_CLAUSE (1 << 2)
#define UNSAFE_NOTIN_PARTITIONBY_CLAUSE (1 << 3)
#define UNSAFE_TYPE_MISMATCH (1 << 4)
+#define UNSAFE_HAS_NONDETERMINITIC (1 << 5)
/* results of subquery_is_pushdown_safe */
typedef struct pushdown_safety_info
@@ -159,6 +160,7 @@ static void check_output_expressions(Query *subquery,
static void compare_tlist_datatypes(List *tlist, List *colTypes,
pushdown_safety_info *safetyInfo);
static bool targetIsInAllPartitionLists(TargetEntry *tle, Query *query);
+static bool contain_nondeterministic_partition_clause(TargetEntry *tle, Query *query);
static pushdown_safe_type qual_is_pushdown_safe(Query *subquery, Index rti,
RestrictInfo *rinfo,
pushdown_safety_info *safetyInfo);
@@ -4345,6 +4347,16 @@ check_output_expressions(Query *subquery, pushdown_safety_info *safetyInfo)
safetyInfo->unsafeFlags[tle->resno] |= UNSAFE_NOTIN_PARTITIONBY_CLAUSE;
continue;
}
+
+ if (subquery->hasWindowFuncs &&
+ (safetyInfo->unsafeFlags[tle->resno] &
+ UNSAFE_HAS_NONDETERMINITIC) == 0 &&
+ contain_nondeterministic_partition_clause(tle, subquery))
+ {
+ /* has nondeterministic partition collations */
+ safetyInfo->unsafeFlags[tle->resno] |= UNSAFE_HAS_NONDETERMINITIC;
+ continue;
+ }
}
}
@@ -4413,6 +4425,22 @@ targetIsInAllPartitionLists(TargetEntry *tle, Query *query)
return true;
}
+static bool
+contain_nondeterministic_partition_clause(TargetEntry *tle, Query *query)
+{
+ Oid collid;
+
+ if (!targetIsInAllPartitionLists(tle, query))
+ return false;
+
+ collid = exprCollation((Node *) tle->expr);
+
+ if (OidIsValid(collid))
+ return !get_collation_isdeterministic(collid);
+
+ return false;
+}
+
/*
* qual_is_pushdown_safe - is a particular rinfo safe to push down?
*
@@ -4525,6 +4553,15 @@ qual_is_pushdown_safe(Query *subquery, Index rti, RestrictInfo *rinfo,
safe = PUSHDOWN_UNSAFE;
break;
}
+ else if (safetyInfo->unsafeFlags[var->varattno] &
+ UNSAFE_HAS_NONDETERMINITIC)
+ {
+ if (get_collation_isdeterministic(exprInputCollation(qual)))
+ {
+ safe = PUSHDOWN_UNSAFE;
+ break;
+ }
+ }
else
{
/* UNSAFE_NOTIN_PARTITIONBY_CLAUSE is ok for run conditions */
--
2.34.1
view thread (6+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: BUG #19534: Qual pushdown across a window subquery is unsafe with nondeterministic partition collations
In-Reply-To: <CAHewXNk8xt1pNepcEiCx_e6_SW_=bm1GNnU7+bPczoZJuLC5qQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox