Re: Fix HAVING-to-WHERE pushdown with nondeterministic collations

On Tue, Mar 31, 2026 at 12:41 PM Richard Guo <guofenglinux@gmail.com> wrote:
> The attached draft patch fixes this for HEAD by leveraging GROUP Vars
> (Vars referencing RTE_GROUP) to detect collation conflicts on a
> per-clause basis, so only unsafe clauses are kept in HAVING while safe
> ones are still pushed.  Please see the commit message for more
> details.

I noticed a bug in this patch.  The pull_var_clause call in
having_collation_conflict_walker needs to recurse through Aggrefs,
since they can still be present in havingQual at this point and we
need to look through them to reach any GROUP Vars in their direct
arguments.  v2 attached fixes this.

> For versions prior to v18, we do not have GROUP Vars.  I wonder if we
> can take a conservative approach: skipping the HAVING-to-WHERE
> pushdown optimization entirely if any GROUP BY expression uses a
> nondeterministic collation.

I'm afraid this approach would regress performance for queries that
currently benefit from the optimization.  But a proper pre-v18 fix
would require a different approach from the v18+ one, since GROUP Vars
don't exist on earlier branches.  Given the absence of field reports,
I don't think the risk of carrying a different fix on stable branches
is justified.  So I'm inclined to back-patch this fix to v18 only.

Any thoughts?

- Richard