From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andrei Lepikhov <lepihov@gmail.com>
cc: pgsql-bugs@lists.postgresql.org, Peter Eisentraut <peter@eisentraut.org>
Subject: Re: Hashed SAOP on composite type with non-hashable column errors at
 runtime
In-reply-to: <3735597.1780683122@sss.pgh.pa.us>
References: <ed221f95-f09b-4a9c-b05b-e1fed621ec87@gmail.com>
 <3735597.1780683122@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Fri, 05 Jun 2026 14:12:02 -0400"
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0"
Content-ID: <3735895.1780683414.0@sss.pgh.pa.us>
Date: Fri, 05 Jun 2026 14:17:21 -0400
Message-ID: <3735924.1780683441@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3735924.1780683441%40sss.pgh.pa.us>
Precedence: bulk

------- =_aaaaaaaaaa0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3735895.1780683414.1@sss.pgh.pa.us>

I wrote:
> In the second place, the same hazard exists for range and
> multirange types, which can have non-hashable subtypes.
> AFAICT noplace at all is defending against that.

I meant to attach the test case I'd made to demonstrate
that this is indeed broken.  It's a small variant of
your bug-hashed-saop.sql.

			regards, tom lane


------- =_aaaaaaaaaa0
Content-Type: text/plain; name="bug-with-range.sql"; charset="us-ascii"
Content-ID: <3735895.1780683414.2@sss.pgh.pa.us>
Content-Description: bug-with-range.sql
Content-Transfer-Encoding: quoted-printable

create type cashrange as range (subtype=3Dmoney);

CREATE TABLE ctest (a int, b cashrange);
INSERT INTO ctest SELECT g, cashrange(g::money, (g+1)::money)::cashrange F=
ROM generate_series(1, 1000) g;
ANALYZE ctest;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, BUFFERS OFF, SUMMARY OFF)
SELECT count(*) FROM ctest
WHERE (a,b) =3D ANY (ARRAY[(1, '(1,2)'::cashrange), (2, '(3,4)'::cashrange=
)]);

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, BUFFERS OFF, SUMMARY OFF)
SELECT count(*) FROM ctest
WHERE (a,b) =3D ANY (ARRAY[
  (1, '(1,2)'::cashrange), (2, '(2,3)'::cashrange), (3, '(3,4)'::cashrange=
),
  (4, '(4,5)'::cashrange), (5, '(5,6)'::cashrange), (6, '(6,7)'::cashrange=
),
  (7, '(7,8)'::cashrange), (8, '(8,9)'::cashrange), (9, '(9,10)'::cashrang=
e)
  ]);

------- =_aaaaaaaaaa0--