diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/aggregates.out | 37 | ||||
| -rw-r--r-- | src/test/regress/expected/sqljson.out | 24 | ||||
| -rw-r--r-- | src/test/regress/sql/aggregates.sql | 19 |
3 files changed, 68 insertions, 12 deletions
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out index 30af83f23cc..9b97fcf40b5 100644 --- a/src/test/regress/expected/aggregates.out +++ b/src/test/regress/expected/aggregates.out @@ -1587,6 +1587,43 @@ select sum(two order by two) from tenk1; reset enable_presorted_aggregate; -- +-- Test cases with FILTER clause +-- +-- Ensure we presort when the aggregate contains plain Vars +explain (costs off) +select sum(two order by two) filter (where two > 1) from tenk1; + QUERY PLAN +------------------------------- + Aggregate + -> Sort + Sort Key: two + -> Seq Scan on tenk1 +(4 rows) + +-- Ensure we presort for RelabelType'd Vars +explain (costs off) +select string_agg(distinct f1, ',') filter (where length(f1) > 1) +from varchar_tbl; + QUERY PLAN +------------------------------------- + Aggregate + -> Sort + Sort Key: f1 + -> Seq Scan on varchar_tbl +(4 rows) + +-- Ensure we don't presort when the aggregate's argument contains an +-- explicit cast. +explain (costs off) +select string_agg(distinct f1::varchar(2), ',') filter (where length(f1) > 1) +from varchar_tbl; + QUERY PLAN +------------------------------- + Aggregate + -> Seq Scan on varchar_tbl +(2 rows) + +-- -- Test combinations of DISTINCT and/or ORDER BY -- select array_agg(a order by b) diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out index 67b68dfc232..9e52914a182 100644 --- a/src/test/regress/expected/sqljson.out +++ b/src/test/regress/expected/sqljson.out @@ -835,22 +835,22 @@ SELECT FROM (VALUES (NULL), (3), (1), (NULL), (NULL), (5), (2), (4), (NULL)) foo(bar); -[ RECORD 1 ]--------------------+------------------------------------------------------------------------------------------------------------------------- -no_options | [1, 2, 3, 4, 5] -returning_jsonb | [1, 2, 3, 4, 5] -absent_on_null | [1, 2, 3, 4, 5] -absentonnull_returning_jsonb | [1, 2, 3, 4, 5] -null_on_null | [1, 2, 3, 4, 5, null, null, null, null] -nullonnull_returning_jsonb | [1, 2, 3, 4, 5, null, null, null, null] -row_no_options | [{"bar":1}, + - | {"bar":2}, + +no_options | [3, 1, 5, 2, 4] +returning_jsonb | [3, 1, 5, 2, 4] +absent_on_null | [3, 1, 5, 2, 4] +absentonnull_returning_jsonb | [3, 1, 5, 2, 4] +null_on_null | [null, 3, 1, null, null, 5, 2, 4, null] +nullonnull_returning_jsonb | [null, 3, 1, null, null, 5, 2, 4, null] +row_no_options | [{"bar":null}, + | {"bar":3}, + - | {"bar":4}, + - | {"bar":5}, + - | {"bar":null}, + + | {"bar":1}, + | {"bar":null}, + | {"bar":null}, + + | {"bar":5}, + + | {"bar":2}, + + | {"bar":4}, + | {"bar":null}] -row_returning_jsonb | [{"bar": 1}, {"bar": 2}, {"bar": 3}, {"bar": 4}, {"bar": 5}, {"bar": null}, {"bar": null}, {"bar": null}, {"bar": null}] +row_returning_jsonb | [{"bar": null}, {"bar": 3}, {"bar": 1}, {"bar": null}, {"bar": null}, {"bar": 5}, {"bar": 2}, {"bar": 4}, {"bar": null}] row_filtered_agg | [{"bar":3}, + | {"bar":4}, + | {"bar":5}] diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql index 675b23add59..fe4d89aec6a 100644 --- a/src/test/regress/sql/aggregates.sql +++ b/src/test/regress/sql/aggregates.sql @@ -596,6 +596,25 @@ select sum(two order by two) from tenk1; reset enable_presorted_aggregate; -- +-- Test cases with FILTER clause +-- + +-- Ensure we presort when the aggregate contains plain Vars +explain (costs off) +select sum(two order by two) filter (where two > 1) from tenk1; + +-- Ensure we presort for RelabelType'd Vars +explain (costs off) +select string_agg(distinct f1, ',') filter (where length(f1) > 1) +from varchar_tbl; + +-- Ensure we don't presort when the aggregate's argument contains an +-- explicit cast. +explain (costs off) +select string_agg(distinct f1::varchar(2), ',') filter (where length(f1) > 1) +from varchar_tbl; + +-- -- Test combinations of DISTINCT and/or ORDER BY -- |