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

From: Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Re: Fix HAVING-to-WHERE pushdown with mismatched operator families
Date: 2026-06-20 21:46:19
Message-ID: CAN4CZFP4PrDi9-OKbFXTe9M1VEZHtj0nBxTiwM_p7fmZ9C2Xyw@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello!

I tested the patch, and I see two possible issues with it

1. It seems to only look for direct operands:

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);
select r, count(*) from t_having group by r having r *=
row(100,1)::t_rec; -- 2, correct
select r, count(*) from t_having group by r having
row((r).a,(r).b)::t_rec *= row(100,1)::t_rec; -- 1, incorrect

2. unknown operators (non btree/hash) seem to behave incorrectly, they
default to non-conflicting but they should conflict?

CREATE FUNCTION num_image_eq(numeric, numeric) RETURNS bool
LANGUAGE sql IMMUTABLE AS $$ SELECT $1::text = $2::text $$;
CREATE OPERATOR === (LEFTARG = numeric, RIGHTARG = numeric, FUNCTION =
num_image_eq);
CREATE TABLE g_hole (g numeric, v int);
INSERT INTO g_hole VALUES (100, 1), (100.0, 2), (100.00, 3);
SELECT g, count(*), sum(v) FROM g_hole GROUP BY g; -- 100 | 3 | 6
SELECT g, count(*), sum(v) FROM g_hole GROUP BY g HAVING g === 100.0;
-- 100 | 1 | 2

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Andreas Karlsson 2026-06-20 22:14:01 Re: [PATCH] DOCS: Distinguish table and index storage parameters
Previous Message Zsolt Parragi 2026-06-20 21:30:58 Re: SQL/JSON: JSON_TRANSFORM (SQL standard, subclause 6.44)