From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: mtakahar@gmail.com
Subject: BUG #18465: Wrong results from SELECT DISTINCT MIN in scalar subquery using HashAggregate
Date: Tue, 14 May 2024 21:14:34 +0000
Message-ID: <18465-2fae927718976b22@postgresql.org> (raw)
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)
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, mtakahar@gmail.com
Subject: Re: BUG #18465: Wrong results from SELECT DISTINCT MIN in scalar subquery using HashAggregate
In-Reply-To: <18465-2fae927718976b22@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 DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox