diff options
| -rw-r--r-- | src/test/regress/expected/aggregates.out | 31 | ||||
| -rw-r--r-- | src/test/regress/sql/aggregates.sql | 10 |
2 files changed, 41 insertions, 0 deletions
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out index 56c361ccef7..2442342e9d1 100644 --- a/src/test/regress/expected/aggregates.out +++ b/src/test/regress/expected/aggregates.out @@ -1273,6 +1273,37 @@ NOTICE: drop cascades to 3 other objects DETAIL: drop cascades to table minmaxtest1 drop cascades to table minmaxtest2 drop cascades to table minmaxtest3 +-- DISTINCT can also trigger wrong answers with hash aggregation (bug #18465) +begin; +set local enable_sort = off; +explain (costs off) + select f1, (select distinct min(t1.f1) from int4_tbl t1 where t1.f1 = t0.f1) + from int4_tbl t0; + QUERY PLAN +--------------------------------------------------------------------- + Seq Scan on int4_tbl t0 + SubPlan 2 + -> HashAggregate + Group Key: (InitPlan 1).col1 + InitPlan 1 + -> Limit + -> Seq Scan on int4_tbl t1 + Filter: ((f1 IS NOT NULL) AND (f1 = t0.f1)) + -> Result +(9 rows) + +select f1, (select distinct min(t1.f1) from int4_tbl t1 where t1.f1 = t0.f1) +from int4_tbl t0; + f1 | min +-------------+------------- + 0 | 0 + 123456 | 123456 + -123456 | -123456 + 2147483647 | 2147483647 + -2147483647 | -2147483647 +(5 rows) + +rollback; -- check for correct detection of nested-aggregate errors select max(min(unique1)) from tenk1; ERROR: aggregate function calls cannot be nested diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql index d28338ba3da..61a3424c845 100644 --- a/src/test/regress/sql/aggregates.sql +++ b/src/test/regress/sql/aggregates.sql @@ -436,6 +436,16 @@ select distinct min(f1), max(f1) from minmaxtest; drop table minmaxtest cascade; +-- DISTINCT can also trigger wrong answers with hash aggregation (bug #18465) +begin; +set local enable_sort = off; +explain (costs off) + select f1, (select distinct min(t1.f1) from int4_tbl t1 where t1.f1 = t0.f1) + from int4_tbl t0; +select f1, (select distinct min(t1.f1) from int4_tbl t1 where t1.f1 = t0.f1) +from int4_tbl t0; +rollback; + -- check for correct detection of nested-aggregate errors select max(min(unique1)) from tenk1; select (select max(min(unique1)) from int8_tbl) from tenk1; |