Thread

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

Richard Guo <guofenglinux@gmail.com> — 2026-05-26T23:04:17Z

On Tue, May 26, 2026 at 11:06 PM Thom Brown <thom@linux.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