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@gmail.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)