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

Il giorno gio 28 mag 2026 alle ore 11:11 Richard Guo <guofenglinux(at)gmail(dot)com>
ha scritto:

> On Wed, May 27, 2026 at 8:04 AM Richard Guo <guofenglinux(at)gmail(dot)com>
> wrote:
> > Thanks for raising these points. For partition pruning,
> > match_clause_to_partition_key() already checks both collation and
> > opfamily compatibility, so I don't think it has similar issues. I'm
> > not sure what is meant by "window function pushdowns", but your
> > question prompted me to look around, and I did notice that pushing
> > restriction clauses down into a subquery suffers from a similar
> > problem, specifically, when the subquery has DISTINCT, DISTINCT ON, or
> > a window PARTITION BY clause.
>
> I think all these issues belong to the same class of bug: the planner
> moves a qual clause across a grouping layer, and the result is wrong
> when the qual's equivalence relation disagrees with the grouping's,
> either an opfamily mismatch or a nondeterministic-collation mismatch.
> This includes HAVING-to-WHERE pushdown, as well as qual pushdown into
> a subquery past its DISTINCT, DISTINCT ON, window PARTITION BY, or
> set-operation grouping layer.
>
> 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.
>
> - Richard

Hi,

The patch fixes DISTINCT/window/set-op subqueries and HAVING, but does it
miss the analogous case for GROUP BY subqueries as the pushdown target?

When an outer qual is pushed into a GROUP BY subquery it lands in
havingQual (correct), but find_having_conflicts then misses the conflict
because the pushed qual carries base-table Vars, not GROUP Vars — so the
clause gets silently moved to WHERE, filtering before aggregation.

Reproducer:
```
CREATE TYPE t_rec AS (x numeric);
CREATE TABLE t_grp (a t_rec);
INSERT INTO t_grp VALUES (ROW(1.0)), (ROW(1.00)), (ROW(2));

-- record_ops (default) considers 1.0 and 1.00 equal; record_image_ops
does not.
-- Expected: one row (1.0), count = 2
-- Got: one row (1.0), count = 1 (wrong)
SELECT * FROM (SELECT a, count(*) FROM t_grp GROUP BY a) s
WHERE a *= ROW(1.0)::t_rec;
```

EXPLAIN shows the *= filter pushed inside the aggregate scan rather than
sitting above it as a Subquery Scan filter.

Cheers,
Florin
--
* Florin Irion *
* https://www.enterprisedb.com <https://www.enterprisedb.com/>*