On Tue, May 26, 2026 at 11:06 PM Thom Brown <[email protected]> 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
