agora inbox for pgsql-bugs@postgresql.org  

help / color / mirror / Atom feed

From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: anncalla@163.com
Subject: BUG #19734: NOT IN type resolution differs for outer Vars, producing different query results
Date: Wed, 30 Sep 2026 12:03:21 +0000
Message-ID: <19734-049ac05dfb08ac4b@postgresql.org> (raw)

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.

Message-ID: <19734-049ac05dfb08ac4b@postgresql.org>
Permalink:  ../19734-049ac05dfb08ac4b@postgresql.org/
Also on:    postgresql.org/message-id/19734-049ac05dfb08ac4b@postgresql.org

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, anncalla@163.com
  Subject: Re: BUG #19734: NOT IN type resolution differs for outer Vars, producing different query results
  In-Reply-To: <19734-049ac05dfb08ac4b@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox