BUG #18465: Wrong results from SELECT DISTINCT MIN in scalar subquery using HashAggregate

The following bug has been logged on the website:

Bug reference: 18465
Logged by: Hal Takahara
Email address: mtakahar(at)gmail(dot)com
PostgreSQL version: 15.7
Operating system: macOS 13.6
Description:

* The query in the example below returns wrong results when HashAggregate is
used for eliminating the duplicates for DISTINCT.

postgres=# select version();
version

------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 15.7 (Homebrew) on x86_64-apple-darwin22.6.0, compiled by Apple
clang version 15.0.0 (clang-1500.1.0.2.5), 64-bit
(1 row)

postgres=# CREATE TABLE b (col_int int);
CREATE TABLE cc (col_int int);

INSERT INTO b values (1);
INSERT INTO cc values (null), (1), (2);

CREATE TABLE
postgres=# CREATE TABLE
postgres=# postgres=# INSERT 0 1
postgres=# INSERT 0 3
postgres=# postgres=# \pset null '<null>'
Null display is "<null>".
postgres=# SET enable_hashagg = ON; SET enable_sort = OFF;
SET
SET
postgres=# SELECT ( SELECT DISTINCT MIN( col_int ) FROM B AS
SUBQUERY1_t1 WHERE SUBQUERY1_t1 .col_int = table1 .col_int ), table1
.col_int FROM CC AS table1 ;
min | col_int
--------+---------
<null> | <null>
<null> | 1 <------- *** wrong ***
<null> | 2
(3 rows)

postgres=# EXPLAIN SELECT ( SELECT DISTINCT MIN( col_int ) FROM B AS
SUBQUERY1_t1 WHERE SUBQUERY1_t1 .col_int = table1 .col_int ), table1
.col_int FROM CC AS table1 ;
QUERY PLAN

----------------------------------------------------------------------------------------
Seq Scan on cc table1 (cost=0.00..8306.82 rows=2550 width=8)
SubPlan 2
-> HashAggregate (cost=3.23..3.24 rows=1 width=4)
Group Key: $1
InitPlan 1 (returns $1)
-> Limit (cost=0.00..3.22 rows=1 width=4)
-> Seq Scan on b subquery1_t1 (cost=0.00..41.88 rows=13
width=4)
Filter: ((col_int IS NOT NULL) AND (col_int =
table1.col_int))
-> Result (cost=3.22..3.23 rows=1 width=4)
(9 rows)

postgres=# SET enable_hashagg = OFF; SET enable_sort = ON;
SET
SET
postgres=# SELECT ( SELECT DISTINCT MIN( col_int ) FROM B AS
SUBQUERY1_t1 WHERE SUBQUERY1_t1 .col_int = table1 .col_int ), table1
.col_int FROM CC AS table1 ;
min | col_int
--------+---------
<null> | <null>
1 | 1 <------- *** correct ***
<null> | 2
(3 rows)

postgres=# EXPLAIN SELECT ( SELECT DISTINCT MIN( col_int ) FROM B AS
SUBQUERY1_t1 WHERE SUBQUERY1_t1 .col_int = table1 .col_int ), table1
.col_int FROM CC AS table1 ;
QUERY PLAN

----------------------------------------------------------------------------------------
Seq Scan on cc table1 (cost=0.00..8319.57 rows=2550 width=8)
SubPlan 2
-> Unique (cost=3.24..3.25 rows=1 width=4)
InitPlan 1 (returns $1)
-> Limit (cost=0.00..3.22 rows=1 width=4)
-> Seq Scan on b subquery1_t1 (cost=0.00..41.88 rows=13
width=4)
Filter: ((col_int IS NOT NULL) AND (col_int =
table1.col_int))
-> Sort (cost=3.24..3.25 rows=1 width=4)
Sort Key: ($1)
-> Result (cost=3.22..3.23 rows=1 width=4)
(10 rows)

* The subquery is returning the first result for all the subsequent
tuples:

postgres=# SET enable_hashagg = ON; SET enable_sort = OFF;
SET
SET
postgres=# TRUNCATE TABLE cc;
TRUNCATE TABLE
postgres=# INSERT INTO cc values (1), (2), (null);
INSERT 0 3
postgres=# SET enable_hashagg = ON; SET enable_sort = OFF;
SET
SET
postgres=# SELECT ( SELECT DISTINCT MIN( col_int ) FROM B AS
SUBQUERY1_t1 WHERE SUBQUERY1_t1 .col_int = table1 .col_int ), table1
.col_int FROM CC AS table1 ;
min | col_int
-----+---------
1 | 1 <------- *** correct ***
1 | 2 <------- *** wrong ***
1 | <null> <------- *** wrong ***
(3 rows)