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

On Tue, May 26, 2026 at 11:06 PM Thom Brown <thom(at)linux(dot)com> wrote:
> Makes sense to me, but out of curiosity, while digging into these
> opfamily mismatches, have you noticed if this same record_ops vs
> record_image_ops inequality poses any risks to other optimisation
> paths like window function pushdowns or partition pruning? And
> apologies if that has already been discussed, but I couldn't find
> mention of it.

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.

create type t_rec as (a numeric);
create table t (a t_rec, b int);
insert into t values (row(1.0), 10), (row(1.00), 20);

-- wrong result: should be 0 rows
select * from
(select distinct on (a) a, b from t order by a, b) s
where a *= row(1.00)::t_rec;
a | b
--------+----
(1.00) | 20
(1 row)

-- wrong result: rk should be 2
select * from
(select a, b, rank() over (partition by a order by b) as rk from t) s
where a *= row(1.00)::t_rec;
a | b | rk
--------+----+----
(1.00) | 20 | 1
(1 row)

In addtition, collation mismatch can also cause wrong results in this
area.

create collation ci (provider = icu, locale = 'und-u-ks-level2',
deterministic = false);
create table t1 (a text collate ci, b int);
insert into t1 values ('abc', 1), ('ABC', 2);

-- wrong result: should be 0 rows
select * from
(select distinct on (a) a, b from t1 order by a, b) s
where a = 'ABC' collate "C";
a | b
-----+---
ABC | 2
(1 row)

-- wrong result: rk should be 2
select * from
(select a, b, rank() over (partition by a order by b) as rk from t1) s
where a = 'ABC' collate "C";
a | b | rk
-----+---+----
ABC | 2 | 1
(1 row)

- Richard