Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19734: NOT IN type resolution differs for outer Vars,
 producing different query results
To: pgsql-bugs@lists.postgresql.org
From: PG Bug reporting form <noreply@postgresql.org>
Cc: anncalla@163.com
Reply-To: anncalla@163.com, pgsql-bugs@lists.postgresql.org
Date: Wed, 30 Sep 2026 12:03:21 +0000
Message-ID: <19734-049ac05dfb08ac4b@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: 
 <https://www.postgresql.org/message-id/19734-049ac05dfb08ac4b%40postgresql.org>
Precedence: bulk

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:       =20

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.