Re: Fix HAVING-to-WHERE pushdown with mismatched operator families

On Mon, Jun 29, 2026 at 5:32 PM Tender Wang <tndrwang(at)gmail(dot)com> wrote:
> If we find a function wrapped on the Var, we give up the qual pushed down.
> We still have the optimization opportunity that if the OpExpr contains
> a bare Var on one side.

After some consideration, I'm convinced this is the right solution.
The previous patches detected an unsafe push with a walker that kept a
stack of the inputcollids contributed by the ancestors of each
grouping Var. At a Var with a nondeterministic collation, it reported
a conflict when any ancestor on the path applied a different collation.
The intent was that a function over the column was fine as long as the
same nondeterministic collation was carried all the way down.

That approach is unsound. It assumes that any function carrying the
grouping collation also preserves the collation's notion of equality,
which is simply not true. As an example, consider:

WHERE ascii(x)::text = '97' COLLATE ci;

ascii() leaves the collatable domain and ::text brings it back, so the
stack sees ci from top to bottom and allows the push, yet the
comparison distinguishes values the grouping had merged. The root
issue is that we have no way to prove an arbitrary expression
preserves that equality.

The attached patch drops the stack for a simpler rule: a grouping
column with a nondeterministic collation is safe to push only as a
direct operand of a comparison under its own collation.

This would make us fail to push some clauses that are in fact safe but
that we cannot prove safe. But I think those cases are very narrow:
they require a nondeterministic grouping key with a function-wrapped
qual on it, and a plain direct comparison such as x = 'foo' COLLATE ci
still pushes, so the optimization loss is small and limited to
nondeterministic collations.

Thoughts?

- Richard