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

От:
BHUVAN CHILUKUTI <c.bhuvan@iitg.ac.in>
Дата:
Вложения:
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 ok lines, with no not ok lines.
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,

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
 
You don't often get email from zhong950419@gmail.com. Learn why this is important
Hi Bhuvan,

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


FAQ