Re: Fix HAVING-to-WHERE pushdown with mismatched operator families - Mailing list pgsql-hackers

On Fri, Jun 19, 2026 at 12:34 AM Florin Irion <irionr@gmail.com> wrote:
> When an outer qual is pushed into a GROUP BY subquery it lands in havingQual (correct), but find_having_conflicts
thenmisses the conflict because the pushed qual carries base-table Vars, not GROUP Vars — so the clause gets silently
movedto WHERE, filtering before aggregation. 

I don't think so.  The pushed qual carries the GROUP Vars, not the
base-table Vars.

> 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;
> ```

I ran your reproducer, and I got the Expected result:

SELECT * FROM (SELECT a, count(*) FROM t_grp GROUP BY a) s
  WHERE a *= ROW(1.0)::t_rec;
   a   | count
-------+-------
 (1.0) |     2
(1 row)

Curious how you got the wrong result with this patch.

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

Here is the EXPLAIN I got:

EXPLAIN (COSTS OFF)
SELECT * FROM (SELECT a, count(*) FROM t_grp GROUP BY a) s
  WHERE a *= ROW(1.0)::t_rec;
              QUERY PLAN
---------------------------------------
 HashAggregate
   Group Key: t_grp.a
   Filter: (t_grp.a *= '(1.0)'::t_rec)
   ->  Seq Scan on t_grp
(4 rows)

So the filter stays in HAVING instead of being pushed to Scan, which
is expected.  I wonder how you get a plan with the filter being pushed
to scan.  Can you show your output of EXPLAIN?

- Richard