Re:Fix WITHOUT OVERLAPS PKs used for functional grouping

At 2026-10-09 12:30:24, "Paul A Jungwirth" <pj(at)illuminatedcomputing(dot)com> wrote:
>Hi Hackers,
>
>Andres reported that a WITHOUT OVERLAPS primary key is wrongly
>considered valid for functional grouping.[0] The problem is that the
>GiST equals operator may not behave the same as the default btree
>operator, which is what GROUP BY uses. This patch disables functional
>grouping for WITHOUT OVERLAPS PKs.
>
>It would be nice to still prove functional grouping if the GiST
>equality operator has the same proc as the btree equality operator.
>I'd be happy to update this submission if people think that is an
>acceptable backpatch. Otherwise I'll submit it separately for v20.
>
>Incidentally, maybe there is another issue?: The only concrete
>built-in opclass I could find to reproduce the problem was citext. The
>btree opclass compares insensitively, but there is no citext opclass
>in btree_gist, so we use the text opclass instead (by binary
>coercion). Isn't that a problem in general (regardless of WITHOUT
>OVERLAPS)? It only breaks GROUP BY with a primary key, but it still
>seems weird to get case-sensitive comparisons on a citext column.
>Should I send another patch (against v20) adding citext to btree_gist?
>Is there a deeper fix, where GiST doesn't use the text opclass for
>citext columns?

Hi，
Thanks for your patch.
While reviewing this patch, I found that some SQL queries which used to return results now throw errors after applying the patch.

Example:
create table n1 (id int4range, valid_at int4range, title text not null,
primary key (id, valid_at without overlaps));
insert into n1 values ('[1,5)', '[2000,2010)', 'a');
insert into n1 values ('[1,5)', '[2011,2015)', 'b');
select * from n1 order by valid_at;
select id, valid_at, title from n1 group by id, valid_at;

Result:
id | valid_at | title
-------+-------------+-------
[1,5) | [2000,2010) | a
[1,5) | [2011,2015) | b
(2 rows)

Should this case be handled more precisely so that the query results are preserved?

Best regards,
Yilin Zhang