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

Hi,

> On May 28, 2026, at 17:11, Richard Guo <guofenglinux(at)gmail(dot)com> wrote:
>
> v2 attached tries to fix the full bug class through a shared walker
> expression_has_grouping_conflict that detects either kind of conflict
> in an expression tree. The walker takes a callback that maps each
> Var to the grouping equality operator for its column (or InvalidOid
> for non-grouping Vars). See the commit message for details.

I haven't yet reviewed all details of the patches in this thread, so
this is mostly a question about the direction.

Following up on the BUG #19534 example Tender posted, I think the
WindowAgg case has the same root cause, but the patch I posted only
targets the nondeterministic PARTITION BY case. An ordinary qual can be
treated as pushdown-safe because it references a PARTITION BY output
column, but the qual is not necessarily constant over the partition
equality class. For example, with a nondeterministic collation,
`ascii(x) = 97` can distinguish values that the partition equality
treats as equal.

I posted a small patch for that specific window case in [1]. It takes a
conservative approach: if an output column matches a window PARTITION BY
key whose collation is nondeterministic, ordinary qual pushdown is not
allowed for that column. It does not try to classify arbitrary
expressions, and deterministic partition keys keep the existing
behavior.

The tradeoff would be that some otherwise-safe pushdowns for
nondeterministic partition keys may be missed, but the scope is limited
to that case. Would that tradeoff be acceptable?

HAVING-to-WHERE seems to have the same underlying issue. If the above
direction is reasonable for WindowAgg, I wonder whether a similar rule
would also make sense there: if a HAVING clause references a
nondeterministic GROUP BY key, keep it in HAVING instead of trying to
prove that the clause is constant over the group equality class.

Does that seem like a reasonable direction?

[1] https://www.postgresql.org/message-id/544F3673-0EC9-4440-9FC5-4BB4F0AA3037%40outlook.com

--
Best regards,
Chengpeng Yan