Incorrect NOT IN results with implicit casts returning NULL after anti-join optimization
Incorrect NOT IN results with implicit casts returning NULL after anti-join optimization
Hi,
I am reporting a query correctness issue in PostgreSQL involving the optimization of
NOT IN subqueries into anti-joins when an implicit cast on the inner comparison operand can return SQL NULL for a non-NULL input.Summary
The issue occurs when PostgreSQL converts a
NOT IN (SELECT ...) predicate into an anti-join without preserving the NULL semantics of the inner comparison after an implicit cast. The investigation attributes the introduction of this behavior to commit
383eb21ebffe9ddd97dca03e529fa363580e7ccf, dated 2026-03-12. I would appreciate confirmation from the PostgreSQL maintainers.Steps to reproduce
On a fresh test database, using a sufficiently privileged role, run:
CREATE FUNCTION i2t(int) RETURNS text IMMUTABLE STRICT LANGUAGE sql AS $$ SELECT CASE WHEN $1 <> 0 THEN format('%s', $1) END $$; CREATE CAST (int AS text) WITH FUNCTION i2t(int) AS IMPLICIT; CREATE TABLE o (t text NOT NULL); INSERT INTO o VALUES ('1'); CREATE TABLE s (i int NOT NULL); INSERT INTO s VALUES (0); SELECT t, t NOT IN (SELECT i FROM s) AS not_in FROM o; SELECT t FROM o WHERE t NOT IN (SELECT i FROM s);
The function returns SQL
NULL for input 0. Therefore, the NOT IN predicate must not evaluate to TRUE for the outer row.Expected behavior
The scalar expression should evaluate to
NULL, and the query with the predicate in the WHERE clause should return zero rows.Actual behavior
On the affected builds tested, the
WHERE query incorrectly returns the outer row containing '1'. The investigation also found that control queries retaining the SubPlan return the expected result.Tested versions
The issue was reproduced on the tested PostgreSQL 19 RC1 build, the tested
REL_19_STABLE tip (015f4c9fc35f91a1ab46bafab05a3686cfe09d46), and the tested master tip (896246ea10cb9d20f69d0442223ce5fa9b365636). The tested PostgreSQL 18.6 build was unaffected. These results describe the specific builds tested; they are not a claim about every build of these branches.
Proposed fix and testing
I have prepared a proposed patch that prevents the unsafe anti-join conversion when the inner comparison operand is not a direct
PARAM_SUBLINK, allowing for a surrounding RelabelType. The patch includes regression tests. On patched master, the recorded results were:
- Core regression suite: 239/239 passed.
- Isolation tests: 134/134 passed.
- Contrib tests: 191 successful
oklines, with nonot oklines.
The patch has not been fully built and tested on
REL_19_STABLE; there, only patch applicability was checked. Additional checks, including check-world/TAP-enabled testing, a non-assert production-style build, pgindent, and dedicated performance measurements, remain outstanding. The fix may cause some queries involving non-binary casts to use a SubPlan instead of an anti-join. This is a potential performance trade-off that should be considered during review.
Attachments
I am including the proposed source fix and regression tests as a separate patch. I can provide the full investigation report, reproduction scripts, and captured logs if further evidence is useful.
I would appreciate your review of the reported behavior and the proposed fix. Please let me know if additional reproduction details or testing are required.
Regards,
Bhuvan Chilukuti
Re: Incorrect NOT IN results with implicit casts returning NULL after anti-join optimization
От:
shihao zhong <zhong950419@gmail.com>
Дата:
Hi Bhuvan,
numeric_col NOT IN (SELECT int_col ...)
text_col NOT IN (SELECT char_col ...)
Could we keep the anti-join for built-in cast functions, and use the
SubPlan only for user-defined ones?
Thanks for reporting it. I can reproduce this on master and on
REL_19_STABLE. It is a v19 regression from 383eb21ebff.One concern from code. The patch also removes the anti-join fo
built-in casts, which never return NULL.
numeric_col NOT IN (SELECT int_col ...)
text_col NOT IN (SELECT char_col ...)
Could we keep the anti-join for built-in cast functions, and use the
SubPlan only for user-defined ones?
Thanks,
Shihao
Re: Incorrect NOT IN results with implicit casts returning NULL after anti-join optimization
От:
BHUVAN CHILUKUTI <c.bhuvan@iitg.ac.in>
Дата:
Hi Shihao,
Thank you for verifying the report and getting back to me. I've reviewed
the suggestion and agree that we can keep the anti-join for built-in cast
functions and use the SubPlan only for user-defined ones.
Regards,
Bhuvan
From: shihao zhong <zhong950419@gmail.com>
Sent: Sunday, November 1, 2026 8:45 AM
To: BHUVAN CHILUKUTI <c.bhuvan@iitg.ac.in>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; Guofenglinux <guofenglinux@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Incorrect NOT IN results with implicit casts returning NULL after anti-join optimization
Sent: Sunday, November 1, 2026 8:45 AM
To: BHUVAN CHILUKUTI <c.bhuvan@iitg.ac.in>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; Guofenglinux <guofenglinux@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Incorrect NOT IN results with implicit casts returning NULL after anti-join optimization
You don't often get email from zhong950419@gmail.com. Learn why this is important |
Hi Bhuvan,
numeric_col NOT IN (SELECT int_col ...)
text_col NOT IN (SELECT char_col ...)
Could we keep the anti-join for built-in cast functions, and use the
SubPlan only for user-defined ones?
Thanks for reporting it. I can reproduce this on master and on
REL_19_STABLE. It is a v19 regression from 383eb21ebff. One concern from code. The patch also removes the anti-join fo
built-in casts, which never return NULL.
numeric_col NOT IN (SELECT int_col ...)
text_col NOT IN (SELECT char_col ...)
Could we keep the anti-join for built-in cast functions, and use the
SubPlan only for user-defined ones?
Thanks,
Shihao
Re: Incorrect NOT IN results with implicit casts returning NULL after anti-join optimization
От:
Richard Guo <guofenglinux@gmail.com>
Дата:
On Sat, Oct 24, 2026 at 3:44 PM BHUVAN CHILUKUTI wrote: > I am reporting a query correctness issue in PostgreSQL involving the optimization of NOT IN subqueries into anti-joins when an implicit cast on the inner comparison operand can return SQL NULL for a non-NULL input. I've pushed a fix for this bug. As for keeping the conversion for built-in cast functions, checking that the function is built-in is not enough, because a user can create an implicit cast using a built-in function that returns NULL for some non-null input. One could argue that we should instead check whether the coercion comes from a built-in pg_cast entry, but I'm not inclined to add that much code for such a rare and AI-artificial case. I'd rather just return false, in the spirit of the comment there: * It is acceptable for this check not to be exhaustive. We can err on the * side of conservatism: if we're not sure, it's okay to return FALSE. - Richard