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+5p9wMBTka3ciLQwzUU6rXmX14AgAD10wCAACpDAA==
Date: Mon, 20 Apr 2026 06:17:47 +0000
Message-ID: <5CDAD4B4-6DC6-4C31-8AC1-1F45EA6ED480@outlook.com>
References: <A16187AE-2359-4265-9F5E-71D015EC2B2D@outlook.com>
 <CAApHDvoLFhtAFSwJdjsBcs9L4J4SzE09gkHLr9nJSGSj1CBaRw@mail.gmail.com>
 <CAApHDvr7K+Wu+ECgmZw057Mb1H6H87+ZWETHi28Ez_ALBKgmQA@mail.gmail.com>
In-Reply-To: 
 <CAApHDvr7K+Wu+ECgmZw057Mb1H6H87+ZWETHi28Ez_ALBKgmQA@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_|TYRPR01MB13591:EE_
x-ms-office365-filtering-correlation-id: 557b99cb-4018-4ded-3f8b-08de9ea48abe
x-microsoft-antispam: 
 BCL:0;ARA:14566002|8022599003|8060799015|461199028|31061999003|41001999006|19110799012|8062599012|15080799012|24121999003|22091999003|440099028|3412199025|102099032;
x-microsoft-antispam-message-info: 
	=?us-ascii?Q?OA98N+zUtFCClG/Zj3N15XhYSvU0VCcZwaa/+L62CFqOLN+LK2SV8Iy+y4v0?=
 =?us-ascii?Q?7AfVo0RkhGMlHH2v0UkXRWSpky7vDqhEzPkzUWyHzp3cCeMzYM0K/rGlH4Vk?=
 =?us-ascii?Q?frVYBoqzZWSpt3gVXMlmufDWCSTKiFvsNwi+fzn4WMTLEpCk6jBqT6jk6hCO?=
 =?us-ascii?Q?ltHU0icA4yMP1UZRR02Wf25pdRSMbU/ApGi4hvZiTi4kz5iu0JI8rn8f3Jpq?=
 =?us-ascii?Q?cJud0fjonHrgW53wlM6UB/AJZzL+VdgS1X007hwj4iVKdr6TP5G4HW1UDdo7?=
 =?us-ascii?Q?5XShRmGqLfFhvj3+b8c8K1GDDbafSba79ts6D1bhMJhEVKQBiRGyaXDZLxTZ?=
 =?us-ascii?Q?fdJcVH3PdYW57p8jchB2X4agYO8KMbv/Lja46qnrlolxOhRPkkilbRa5rEbr?=
 =?us-ascii?Q?mmVkCGnUmSN1e1NuIx/l5uI7CrC/zxIYSe3MDKoscYTBXDo7lzFB3/QpdJ8B?=
 =?us-ascii?Q?4LQrsc1oEq49OJWsP/toRHlC8v4IwiRBjMDeBXbXZmZgFdoQXjOIn5Iibsbd?=
 =?us-ascii?Q?p2znihx6csfOGQ5G5ISBI15pEPYE5c8cj5go9mp3zDFu/EkTLmucoF65MOr1?=
 =?us-ascii?Q?9NxIoSVAF1+SvEhzfOMc+HVys3lJ+2D+YxAG29UW8SvVDL4Y2mph6LxFe0KM?=
 =?us-ascii?Q?mBIrBsRGObpbsvrYyXwTqe/cp1kmUYWyMUJ+jP7DxCraVOjhPe8laaHtqK+p?=
 =?us-ascii?Q?Rf62mZEa/JVBaGY9XMgn4pasUJp/MFPkA+URq2DzdkPIa+auYXOUce9dUTnP?=
 =?us-ascii?Q?AmFqW5/FkLDUinYzkMozp2xsCt0mORdw08yhcSUJZfcIV3k2xRbHdBUa/3AM?=
 =?us-ascii?Q?OM7Dl/ZdU3FObrXGjlpzkTudi0Wk87LmoXak05pbeg7FC65mKy1kvKFOY6u4?=
 =?us-ascii?Q?ycW/45uX1jriLN4MJl6BnzEYoOQ7woXuJyIFWc5Rc+Cwvrsl7xOmWYUmWr5D?=
 =?us-ascii?Q?bpb0KPVu0lHJ9ZkPaMIsqQ=3D=3D?=
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: 
 =?us-ascii?Q?Nmv+k1pVm+TIB0NxdyQuiotWush4+LkCjauzifKzQrurV4QX85iDvG+qBqPk?=
 =?us-ascii?Q?o2Fygqpari11J1ws6O25g2RXhA6DeMquL4V2CG4wOXVpcCbgS1Luw1kz16I9?=
 =?us-ascii?Q?+2+u0G4xiBN104lWTdowcKrgJB4MP8LSxPU4ON7zZz7K6ETurvruqmFf/62b?=
 =?us-ascii?Q?oNPoXWiXZ+50XCBn3XIuGDMqy9huLYx/0NQof2SyZxB5nQ77ggvdF/3stTO+?=
 =?us-ascii?Q?o79IQW4yychkBrxCKZmRWuTEUkvNVr5sFACVrmOFMkVhe9eVd0V2jG2eeWOn?=
 =?us-ascii?Q?6qCcDSo5wIEaiN9j9GmL9V5NsdxuWHJ6obdhgYesoHZoKbsa1WSztXMG2gM/?=
 =?us-ascii?Q?UuoHRNQsjR17tNIzXDfV3Yg1J95CWpAYuu4IYtyDlaq3pWGzdD9dNvm+xtvr?=
 =?us-ascii?Q?O2KcguJMwWwooaTSDNNgyUG1XJ4Ikd0Nxr3VmaLDv43Mz15SEioSruhyDL8y?=
 =?us-ascii?Q?XOmLUDSyNE8wq5jS3RJcIJSe0kTPwv0grG3tWV/tHWVDy4E6VDqZ6C2BzrXi?=
 =?us-ascii?Q?WDw9vUCOfboR4wRP81t5RQi8EIYXiCnbOiWBzMtt3lUoRrl83hxAdlHYesz6?=
 =?us-ascii?Q?MrYefpLXtgX1fPo5mjDkVEVjn5IplOituTVhETFOKPpeXn2HgBiLZjPZtgWr?=
 =?us-ascii?Q?3xkrnw6Gd858ruSc2LIDBeSWkgytw5at6xc+dVMI++HA/yvLQfZB35dJvVo2?=
 =?us-ascii?Q?h/8Rc20ZDFsxC8tMekf5ydCu5zzFAt7RFaKcqfTm7Zy0HFa/SrVrehFNu/ev?=
 =?us-ascii?Q?mH8QtBoHyxHuEvvM+Se6OZPYOAmj93E7zvEsr49BPab8r3eojid8gShNP3Gb?=
 =?us-ascii?Q?myp8FnTLLctQ6rVhOp5lEDBg02JTn/41xTxmDhOPA0fAmykt1Hcqmg7qqv7U?=
 =?us-ascii?Q?n9lCsydjIGQz2wyHTlh1CCr0cKPlnvPoy+dh/cbZI9D3OoZ+NufaeQfWO0Gr?=
 =?us-ascii?Q?m9LAsPF5wYe9xk35EiiPuJUM2fP5tIfipxcj/xPZ/dyn9n3q26pht/UQd51z?=
 =?us-ascii?Q?S50sTcepMOeIBPM9wzZ0zO3j21xRiLDePHFFAEidoyse38uUr/6N9illsUc7?=
 =?us-ascii?Q?aO90aJgwrM2AimeQ9+w4iOndgO0r+qWAni16bD/j12i78Q3eNsVMndbHacHb?=
 =?us-ascii?Q?vk77l71jdTN7W0QvcLzsZtyRuz9Mc3ec3XpeGCerAUqE+fRCteiGtnGjxB/N?=
 =?us-ascii?Q?X6znpeeGc/Z4NZHhLOTTEUCvg9qlo6fYZ8Eeb1rZNKZeIQqHKakiO/CZmE4q?=
 =?us-ascii?Q?CxBm6o/ZpikQXm3Ld6QSGNYKcQpQEukTuUgr93fN+t0OuEYA06JRl/f7WSA3?=
 =?us-ascii?Q?YSsQUESB7cr53S+i+diDX+Doxz0WEnCltpEoid3u2Aa+PKYxIsm5qFPa4k47?=
 =?us-ascii?Q?NWBGJflcodotm20Gr7Lmh2bH3uXmC9gxmQu/8LIgthWvD1NF2g=3D=3D?=
Content-Type: text/plain; charset="us-ascii"
Content-ID: <38852140E82EC2499BC1199C65241089@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: 
 557b99cb-4018-4ded-3f8b-08de9ea48abe
X-MS-Exchange-CrossTenant-originalarrivaltime: 20 Apr 2026 06:17:47.2049
 (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: TYRPR01MB13591
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/5CDAD4B4-6DC6-4C31-8AC1-1F45EA6ED480%40outlook.com>
Precedence: bulk


> On Apr 20, 2026, at 11:46, David Rowley <dgrowleyml@gmail.com> wrote:
>=20
> Of course, it is possible to make the strict function do that, and
> non-hashed IN / NOT IN handles it, so the hashed version shouldn't
> have an excuse to not do the right thing.
>=20
> I've attached a version that "probes" the equality function for its
> NULL =3D NULL behaviour and its NULL =3D non-NULL behaviour and returns
> whatever the result of the probe was at the appropriate time.
>=20
> What I came up with does feel quite elaborate, so I'd quite like a 2nd op=
inion.
>=20
> The patch does assume that the non-strict function will return the
> same thing for NULL =3D non-NULL as it will for non-NULL =3D NULL.
> Technically, if you coded the function to do something different
> there, the hashed vs non-hashed could differ in their result. My
> thoughts there, if someone is expecting anything sane out of such an
> equality function, then they're probably going to be disappointed due
> to various other optimisations we have.

Hi,

Thanks for the discussion.

I agree with Tom's concern that it does not seem safe to generalize from
NULL =3D first-non-NULL to all non-NULL values. Unless I am missing one, I
do not know of a planner/executor-visible contract that would justify
that assumption.

For the original NULL-LHS bug, a linear fallback still seems like the
safest baseline fix to me. It is conservative, but it matches
ExecEvalScalarArrayOp() without adding extra assumptions. The obvious
downside is performance, although this path only triggers when the
runtime LHS is NULL and the comparator is non-strict. It may also be
possible to cache the NULL-LHS outcome once per expression, since the
RHS array is constant in the hashed SAOP case, which might help reduce
the cost of that fallback.

ChangAo's example also seems to expose a separate correctness issue. If
the comparator can return NULL even for non-NULL inputs, then a lookup
hit seems sufficient, but a miss is no longer enough to distinguish
FALSE for IN / TRUE for NOT IN from NULL.

A conservative fix there would again be a linear fallback after miss,
which should recover the right semantics, but that case does seem much
more performance-sensitive.

So I would be interested to hear what people think about both cases,
especially if there is a better way to preserve correctness without
paying the full linear-fallback cost.

--
Best regards,
Chengpeng Yan