The following bug has been logged on the website:
Bug reference: 19734
Logged by: Ann Calla
Email address: anncalla@163.com
PostgreSQL version: 18.6
Operating system: Ubuntu 24.04 x86_64
Description:
I found a case where moving the same NOT IN predicate into a correlated
EXISTS subquery changes the query result.
Minimal reproducer:
SELECT 'INNER_JOIN' AS q, COUNT(*) AS n
FROM (VALUES (-969514295)) a(x)
JOIN (VALUES (1)) b(y)
ON x::real NOT IN (x, 0.7);
SELECT 'EXISTS' AS q, COUNT(*) AS n
FROM (VALUES (-969514295)) a(x)
WHERE EXISTS (
SELECT 1
FROM (VALUES (1)) b(y)
WHERE x::real NOT IN (x, 0.7)
);
Actual result:
q | n
------------+---
INNER_JOIN | 1
q | n
--------+---
EXISTS | 0
I expected both queries to return the same count.
b contains exactly one row, and its column y is not referenced by the
predicate. The condition applied to the row from a is the same in both
cases:
x::real NOT IN (x, 0.7)
The difference appears to happen during parsing/type resolution rather than
because of the data itself.
In the first query, x is a Var of the current query level. In the correlated
subquery, it is an outer Var. This appears to cause NOT IN to be
represented/resolved differently, with the correlated form potentially using
a ScalarArrayOpExpr.
This may be related to how transformAExprIn() distinguishes current-level
Vars from outer Vars.
The value -969514295 makes the difference observable because conversion to
real loses precision.
No tables, indexes, extensions, custom types, or non-default configuration
are required to reproduce the issue.
