BUG #15604: NOT IN condition incorrectly returns False - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      15604
Logged by:          Sergey Romanovsky
Email address:      sergey@romanovsky.org
PostgreSQL version: 10.6
Operating system:   linux Red Hat 4.8.3-9
Description:

Zhijiang Li <zl256@cornell.edu> and I found the following bug described
here: https://github.com/romanovsky/postgres/blob/master/README.md
# Postgres bug: NOT IN condition incorrectly returns False

## Short description

In case the dataset doesn't fit into available memory and Postgres has
decided to use hash lookup for `NOT IN`
we observe that both queries `1 IN SubSelect` and `1 NOT IN SubSelect`
return empty result set.
If you upgrade AWS RDS instance from `db.r4.large` to `db.r4.xlarge` you
won't be able to reproduce the problem.

## Assumptions made
* Examples below assume that there's no request_id=1:
```sql
db=> SELECT request_id FROM postgres_not_in_bug WHERE request_id=1;
 request_id
------------
(0 rows)
```

* Execution plan looks like:
```sql
db=> EXPLAIN SELECT 'there is no 1' AS foo WHERE 1 NOT IN (SELECT request_id
FROM postgres_not_in_bug);
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Result  (cost=2101.68..2101.69 rows=1 width=32)
   One-Time Filter: (NOT (hashed SubPlan 1))
   SubPlan 1
     ->  Seq Scan on postgres_not_in_bug  (cost=0.00..1791.34 rows=124134
width=6)
(4 rows)
```

## Observed behavior
```sql
db=> SELECT 'there is no 1' AS foo WHERE 1 NOT IN (SELECT request_id FROM
postgres_not_in_bug);
 foo
-----
(0 rows)

db=> SELECT 'there is no 1' as foo WHERE 1 IN (SELECT request_id FROM
postgres_not_in_bug);
 foo
-----
(0 rows)
```

## Expected behavior
```sql
db=> SELECT 'there is no 1' AS foo WHERE 1 NOT IN (SELECT request_id FROM
postgres_not_in_bug);
      foo
---------------
 there is no 1
(1 row)

db=> SELECT 'there is no 1' AS foo WHERE 1 IN (SELECT request_id FROM
postgres_not_in_bug);
       foo
-----------------
(0 rows)
```


## How to reproduce

1. Download `postgres_not_in_bug.pg_dump` from this repo locally to
`/tmp/postgres_not_in_bug.pg_dump`
```bash
curl -H 'Accept: application/vnd.github.v3.raw' -o
/tmp/postgres_not_in_bug.pg_dump -O -L
https://raw.githubusercontent.com/romanovsky/postgres/master/postgres_not_in_bug.pg_dump
```
2. Load the dataset to postgres
```bash
$ cat /tmp/postgres_not_in_bug.pg_dump|psql -h host -U user -d db
--port=5432
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
SET
CREATE TABLE
COPY 124134
```
3. Run queries:
```bash
$ psql -h host -U user -d db --port=5432 --command="SELECT 'there is no 1'
AS foo WHERE 1 NOT IN (SELECT request_id FROM postgres_not_in_bug)"
 foo
-----
(0 rows)

$ psql -h host -U user -d db --port=5432 --command="SELECT 'there is no 1'
AS foo WHERE 1 IN (SELECT request_id FROM postgres_not_in_bug)"
 foo
-----
(0 rows)
```

### Specifications
* Postgres 10.6
```bash
$ psql -h host -U user -d db --port=5432 --command="SELECT version()"
                  
                                                version
--------------------------------------------------------------------------------------------------------
 PostgreSQL 10.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3
20140911 (Red Hat 4.8.3-9), 64-bit
(1 row)
```
* Amazon RDS db.r4.large, i.e. 2 vCPU/15.25GB RAM (see more details here:
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Concepts.DBInstanceClass.html#Concepts.DBInstanceClass.Summary)