BUG #18465: Wrong results from SELECT DISTINCT MIN in scalar subquery using HashAggregate
От | PG Bug reporting form |
---|---|
Тема | BUG #18465: Wrong results from SELECT DISTINCT MIN in scalar subquery using HashAggregate |
Дата | |
Msg-id | 18465-2fae927718976b22@postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #18465: Wrong results from SELECT DISTINCT MIN in scalar subquery using HashAggregate
(Dmitry Dolgov <9erthalion6@gmail.com>)
|
Список | pgsql-bugs |
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)
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Tom LaneДата:
Сообщение: Re: BUG #18463: Possible bug in stored procedures with polymorphic OUT parameters
Следующее
От: Melanie PlagemanДата:
Сообщение: Re: relfrozenxid may disagree with row XIDs after 1ccc1e05ae