From: Chengpeng Yan <chengpeng_yan@outlook.com>
To: David Rowley <dgrowleyml@gmail.com>
CC: PostgreSQL-development <pgsql-hackers@postgresql.org>, cca5507
	<cca5507@qq.com>, Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: [PATCH] Fix hashed ScalarArrayOp semantics for NULL LHS with
 non-strict comparators
Thread-Topic: [PATCH] Fix hashed ScalarArrayOp semantics for NULL LHS with
 non-strict comparators
Thread-Index: AQHczaEqi+5p9wMBTka3ciLQwzUU6rXmX14AgAD10wCAACpDAIAERiEAgABTKAA=
Date: Thu, 23 Apr 2026 04:31:24 +0000
Message-ID: <614EFDA0-6858-4C14-9E6A-53FC4EABE1AA@outlook.com>
References: <A16187AE-2359-4265-9F5E-71D015EC2B2D@outlook.com>
 <CAApHDvoLFhtAFSwJdjsBcs9L4J4SzE09gkHLr9nJSGSj1CBaRw@mail.gmail.com>
 <CAApHDvr7K+Wu+ECgmZw057Mb1H6H87+ZWETHi28Ez_ALBKgmQA@mail.gmail.com>
 <5CDAD4B4-6DC6-4C31-8AC1-1F45EA6ED480@outlook.com>
 <CAApHDvo=bOVURpRH_ydBYT_2R9PU4EvzzFfez1xV4RKBPRUPVA@mail.gmail.com>
In-Reply-To: 
 <CAApHDvo=bOVURpRH_ydBYT_2R9PU4EvzzFfez1xV4RKBPRUPVA@mail.gmail.com>
Accept-Language: zh-CN, en-US
Content-Language: en-US
x-ms-exchange-messagesentrepresentingtype: 1
x-ms-publictraffictype: Email
x-ms-traffictypediagnostic: TYYPR01MB14116:EE_|TYYPR01MB6729:EE_
x-ms-office365-filtering-correlation-id: 9ca69991-ea9a-4db6-0d70-08dea0f12d67
x-microsoft-antispam: 
 BCL:0;ARA:14566002|19110799012|31061999003|24021099003|22091999003|12121999013|15080799012|461199028|24121999003|8022599003|8060799015|8062599012|440099028|3412199025|102099032;
x-microsoft-antispam-message-info: 
 =?us-ascii?Q?DApnpA0TgXhG2vQO8u+rTBdEFR9l9LHSmzzUEkbEDBjde7CHfpLBVs7zvWaY?=
 =?us-ascii?Q?nzCA0ihNT8YtTaS/oH9jn6Rqv0tuFqx5d3l24E7z6R0FP4uVvarY3EE+iUtr?=
 =?us-ascii?Q?m0EU/EIzZhszSr4Z6Yeyra9ouFB0Fp904QMit5ocWuhBw5j7eN1IW/6Fc6c2?=
 =?us-ascii?Q?a0pejfzPag4kbhV+aLV3hxzwkxNylL1YYE6MWZuStn+Q/SWsEQGuEkqkzauH?=
 =?us-ascii?Q?Raia23ZsnrXCXuVgzpdS35ac+wxcUM7pE9XjrueybYQlJ6qZCpYA+yWEcCIr?=
 =?us-ascii?Q?RICZlotbfw2EW5ovq1Ib9DnYkUu/NNOuOyYMtV54VCSL2wULw8AdevXVilbm?=
 =?us-ascii?Q?VfG3GBlqaVnbtWm6SnX4qd1wzqn2R2w0SYMZEYlYZWM4duDhlMKoSj0tfV7f?=
 =?us-ascii?Q?PpredNryPcnHRhTlHZOB+ydWe+iwAfnzs8ppPLiYACvHZyyDdUi61Tt94jce?=
 =?us-ascii?Q?5dYCWyxQb572KfE9xX3U2DRlKO7HzNPoF3u0zikD3+pYDzVgBl7jtq+v5dEe?=
 =?us-ascii?Q?me2c2PiIS6dBMYkAPS2us7/vpEMF2I6V8dJDvyRJ4cYOwC9UMAU8z84zqdZJ?=
 =?us-ascii?Q?2mIOTx4YgAEnKj1ort8RF/sxJjWynpHC0FV1j0SDdljBICoJnhYq4EuDLcR/?=
 =?us-ascii?Q?oZQ+ojtxDTfNHb9mL32sT3N+vFrnC+t0Br+glMxy+iXAi74QPo339SbmeoIm?=
 =?us-ascii?Q?Ro1A4hplXoHgyXhBgpctNOPL5VTCZ88GY1ksywAtzPP6UjQyluMKUx7LLf1v?=
 =?us-ascii?Q?VxfhJm4mq9hVUVOfjZUMa3V+ezhhTE6zJc8njjk1IrxqGF61TsUnUh2Y5kHe?=
 =?us-ascii?Q?9UY9WaXF20Lsr83wuGtU4zfPvAPO5hYhnvGv685A/aSlq0EqwGJ76UrB48IX?=
 =?us-ascii?Q?tSAP9nBPg5Rbe6nFA/1WuEtYWWVSshuUzOyhnm12ATLSsYtCJxoBOxUMLOpQ?=
 =?us-ascii?Q?whE7tO9eOP1oQzKJB9PngFRHl7RtlDMTzrCyddtP5jWWox7GJ4lmxbSy/ngE?=
 =?us-ascii?Q?B+57?=
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: 
 =?us-ascii?Q?Be4c+qKGu1fBpYo29L3BhDzZc+5asf7yi+IQmgvVCGNu4SvchlrUxEM94tM+?=
 =?us-ascii?Q?ltSBxX6gk8nutoZd/g5y87CtYo/a8fQ8+S0ti+eI/93RbV+Q60K7p8FXwZor?=
 =?us-ascii?Q?iH1UDc9XBvyiw1xvdOEKMyjKfduavgVNSMgdFpxIQBMsbwth7XJf720ZXbPy?=
 =?us-ascii?Q?WeA8VNbmYqCXE2VdrK9IMMJVZppnuMIvRqYi9as75VcyYIminR0kMYFjMlrF?=
 =?us-ascii?Q?ExyuTo7ZCJRYAXdEmvD4B0wtdw2F6+TucwhddPbHmPdt7K01azbAw1HWKoPi?=
 =?us-ascii?Q?rwo2H/jVeGX/MhLSw+JQPkRGo0Aww4exC1QFjd4enIiBJN+gCYYKMFVs6MZ/?=
 =?us-ascii?Q?XmCj8WqfhXlD13wg0ri/zMCoZtYw/YQzWpQPMzeovhpemGD3ZfLVggiVxatS?=
 =?us-ascii?Q?gbRw0zWVQMb3sSKLPYtSCxduIxWs/pUdINGYP7aWzF3aotTUfAJ++Xh+icgZ?=
 =?us-ascii?Q?DgH7UzQ2KrbK3f/rXdL/EgIn0dqm2Aj2IvXLoUiX8d6zJeif3xJbuF+s7e9u?=
 =?us-ascii?Q?KPuubzHoSYUCcLfOGjcLbsSw/OSH3cwtkpNuTAQVvGkMEWuU7n90nfPXDS8z?=
 =?us-ascii?Q?peKEM7n0tl8FgSl76vIrdh4ZafxdQVHF1RTnEWS9h+NlVYpdbgrzbEg7USTe?=
 =?us-ascii?Q?y0sszAjGycWrtQeAkKLXYBSeBkToStKm7lWxEzI7tdvneEomi0P5Vzk4cOTV?=
 =?us-ascii?Q?yfiuGMvKLVniWW2sqGVmWSPfJHQ+qBxwreNJWqGhhWS76QYb4FvEqA/fAKFj?=
 =?us-ascii?Q?+v3TagoKs7SX/RjoMhXcyuK5cB4WkI4payWn90nIUtfSRmYIJjAV+6DhQDcq?=
 =?us-ascii?Q?42TXn2TrJeiAE3s2k9Cg/Q6cq3Dc7XFvNzWNkjUQlvPTIA11Lkal8fgweRLt?=
 =?us-ascii?Q?n8fSGQRyabWLn6JoJA2Kz0X6fs8M7mpTSOMF/7wDVj7kPFJFwCFxO/ALOtXh?=
 =?us-ascii?Q?0gns/WwAaaNIY2gxploJ45Ffjj/xIEp3Y53FzxMraDa2WUtewULRi3HqKeE1?=
 =?us-ascii?Q?bvRBw/2vs/+BFr3mxuHxy4QEsKKnOAHMjxZ7vi/DdW8EghHI81ccexaDSXfn?=
 =?us-ascii?Q?9uaUoFPqko09//rILJtbISM1kueKqrUR7hPavK3isuW4Di2uBuAwcRJpL7sp?=
 =?us-ascii?Q?8Ly1WrY0yNhSw+uhJMwEkH6t+PUvBfR3ZvCJUOfUT73DpIhUMgXhxRMVTch7?=
 =?us-ascii?Q?EZbev376IttR4hWR0ydKAZClDpWn1kYPWazV7EcdZb4iD/qdvc9s+adRVWEI?=
 =?us-ascii?Q?7xczDRjop7tHzV3pRwSLN9ep72vIEKdXtHKGdjiOr/+dJSXN92PcA7Jqe+ag?=
 =?us-ascii?Q?8g+M3cgHzI1U9wnqBP/XWjuPr+80SoAKeXmPq/MI02Esz5hHjiVHvgYKYSxu?=
 =?us-ascii?Q?08VPI9zFJc4sQgS1ETwbjBNGJoR7eeiOCzZPeHpcAdxnzjojOQ=3D=3D?=
Content-Type: text/plain; charset="us-ascii"
Content-ID: <4CAB8882145AFE46AFB6ED9A96A2F1F5@jpnprd01.prod.outlook.com>
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-OriginatorOrg: outlook.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: TYYPR01MB14116.jpnprd01.prod.outlook.com
X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg: 
 00000000-0000-0000-0000-000000000000
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 9ca69991-ea9a-4db6-0d70-08dea0f12d67
X-MS-Exchange-CrossTenant-originalarrivaltime: 23 Apr 2026 04:31:24.2253
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa
X-MS-Exchange-CrossTenant-rms-persistedconsumerorg: 
 00000000-0000-0000-0000-000000000000
X-MS-Exchange-Transport-CrossTenantHeadersStamped: TYYPR01MB6729
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Archived-At: 
 <https://www.postgresql.org/message-id/614EFDA0-6858-4C14-9E6A-53FC4EABE1AA%40outlook.com>
Precedence: bulk

Hi,

> On Apr 23, 2026, at 07:33, David Rowley <dgrowleyml@gmail.com> wrote:
>=20
> Yeah, it doesn't make sense to repeatedly perform a linear search over
> the array to check if NULL matches anything in the array. Let's just
> do that once when we build the hash table and reuse that cached value
> whenever we see a NULL. We can skip that step with strict functions
> since we'll short-circuit earlier.
>=20
> A patch for that is attached.

Thanks for working on this. Overall, this version looks good to me, and
I'm fine with the current approach. One possible improvement, though not
a blocker, would be to defer the lhs-NULL handling until we actually
encounter the first NULL on the lhs. That could avoid a bit of extra
work in the common case where the lhs contains no NULLs. That said, I
think the current implementation is perfectly OK as-is.

> IMO it's unrealistic to assume we can do anything sane with an
> equality function that always returns NULL.
>=20
> I really doubt it's worth troubling over that. If we did want to do
> something, then it would be more efficient to probe the hash table
> directly after we insert a Datum and verify we can find it again. If
> we can't find any value we just inserted, mark the entire table as
> broken and have it so we check for that and do a linear search.

I tend to agree. Even if such a case can be constructed, it seems rare
enough that I am not sure it is worth adding more complexity, or extra
overhead in the common hashed SAOP path, to handle it in this patch. I
think we can revisit that separately if a concrete case turns up that
seems worth looking into.

--
Best regards,
Chengpeng Yan