| From: | Richard Guo <guofenglinux(at)gmail(dot)com> |
|---|---|
| To: | Pg Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Subject: | Fix HAVING-to-WHERE pushdown with mismatched operator families |
| Date: | 2026-05-26 08:48:24 |
| Message-ID: | CAMbWs4-QLZpn3UVOpeG2fOxxhdnkDNMZ_3Zcm3dqJwRAphz68g@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Continued with the rabbit-hole around equality-relation issues, here
is another one in the HAVING-to-WHERE pushdown path.
f76686ce7 stopped the planner from pushing a HAVING clause to WHERE
when the clause's collation disagreed with the GROUP BY's
nondeterministic collation. The same shape of bug exists with
operator families: when a HAVING clause uses a comparison operator
from a different opfamily than the GROUP BY's eqop, pushing it to
WHERE can produce wrong results.
create type t_rec as (a numeric, b int);
create table t_having (id int, r t_rec);
insert into t_having values
(1, row(100, 1)::t_rec),
(2, row(100.0, 1)::t_rec),
(3, row(2, 2)::t_rec);
-- wrong result: count should be 2
select r, count(*) from t_having group by r having r *= row(100, 1)::t_rec;
r | count
---------+-------
(100,1) | 1
(1 row)
The fix in the attached patch mirrors f76686ce7's structure. We
detect the conflict before flatten_group_exprs while the HAVING clause
still contains GROUP Vars, and record the indices of unsafe clauses in
a Bitmapset that's consulted by the existing pushdown loop.
This issue can be reproduced as far back as v14. However, the fix
relies on RTE_GROUP to identify grouping expressions via GROUP Vars on
pre-flatten havingQual. As with f76686ce7, I'm inclined to back-patch
to v18 only.
Thoughts?
- Richard
| Attachment | Content-Type | Size |
|---|---|---|
| v1-0001-Fix-HAVING-to-WHERE-pushdown-with-mismatched-oper.patch | application/octet-stream | 20.1 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Aleksander Alekseev | 2026-05-26 09:16:50 | Re: [PATCH] Little refactoring of portalcmds.c |
| Previous Message | jian he | 2026-05-26 08:32:29 | Re: Fix bug of CHECK constraint enforceability recursion |