0 |
(1 row)
+-- verify correct results for null and NaN inputs
+select sum(null::int4) from generate_series(1,3);
+ sum
+-----
+
+(1 row)
+
+select sum(null::int8) from generate_series(1,3);
+ sum
+-----
+
+(1 row)
+
+select sum(null::numeric) from generate_series(1,3);
+ sum
+-----
+
+(1 row)
+
+select sum(null::float8) from generate_series(1,3);
+ sum
+-----
+
+(1 row)
+
+select avg(null::int4) from generate_series(1,3);
+ avg
+-----
+
+(1 row)
+
+select avg(null::int8) from generate_series(1,3);
+ avg
+-----
+
+(1 row)
+
+select avg(null::numeric) from generate_series(1,3);
+ avg
+-----
+
+(1 row)
+
+select avg(null::float8) from generate_series(1,3);
+ avg
+-----
+
+(1 row)
+
+select sum('NaN'::numeric) from generate_series(1,3);
+ sum
+-----
+ NaN
+(1 row)
+
+select avg('NaN'::numeric) from generate_series(1,3);
+ avg
+-----
+ NaN
+(1 row)
+
-- SQL2003 binary aggregates
SELECT regr_count(b, a) FROM aggtest;
regr_count
group by ten
having exists (select 1 from onek b
where sum(distinct a.four + b.four) = b.four);
-ERROR: aggregates not allowed in WHERE clause
+ERROR: aggregate functions are not allowed in WHERE
LINE 4: where sum(distinct a.four + b.four) = b.four)...
^
-- Test handling of sublinks within outer-level aggregates.
select
(select max((select i.unique2 from tenk1 i where i.unique1 = o.unique1)))
from tenk1 o;
- ?column?
-----------
- 9999
+ max
+------
+ 9999
(1 row)
--
-- Test cases that should be optimized into indexscans instead of
-- the generic aggregate implementation.
--
-analyze tenk1; -- ensure we get consistent plans here
-- Basic cases
explain (costs off)
select min(unique1) from tenk1;
- QUERY PLAN
--------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------
Result
InitPlan 1 (returns $0)
-> Limit
- -> Index Scan using tenk1_unique1 on tenk1
+ -> Index Only Scan using tenk1_unique1 on tenk1
Index Cond: (unique1 IS NOT NULL)
(5 rows)
explain (costs off)
select max(unique1) from tenk1;
- QUERY PLAN
-----------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------
Result
InitPlan 1 (returns $0)
-> Limit
- -> Index Scan Backward using tenk1_unique1 on tenk1
+ -> Index Only Scan Backward using tenk1_unique1 on tenk1
Index Cond: (unique1 IS NOT NULL)
(5 rows)
Result
InitPlan 1 (returns $0)
-> Limit
- -> Index Scan Backward using tenk1_unique1 on tenk1
+ -> Index Only Scan Backward using tenk1_unique1 on tenk1
Index Cond: ((unique1 IS NOT NULL) AND (unique1 < 42))
(5 rows)
Result
InitPlan 1 (returns $0)
-> Limit
- -> Index Scan Backward using tenk1_unique1 on tenk1
+ -> Index Only Scan Backward using tenk1_unique1 on tenk1
Index Cond: ((unique1 IS NOT NULL) AND (unique1 > 42))
(5 rows)
Result
InitPlan 1 (returns $0)
-> Limit
- -> Index Scan Backward using tenk1_unique1 on tenk1
+ -> Index Only Scan Backward using tenk1_unique1 on tenk1
Index Cond: ((unique1 IS NOT NULL) AND (unique1 > 42000))
(5 rows)
-- multi-column index (uses tenk1_thous_tenthous)
explain (costs off)
select max(tenthous) from tenk1 where thousand = 33;
- QUERY PLAN
---------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------
Result
InitPlan 1 (returns $0)
-> Limit
- -> Index Scan Backward using tenk1_thous_tenthous on tenk1
+ -> Index Only Scan Backward using tenk1_thous_tenthous on tenk1
Index Cond: ((thousand = 33) AND (tenthous IS NOT NULL))
(5 rows)
Result
InitPlan 1 (returns $0)
-> Limit
- -> Index Scan using tenk1_thous_tenthous on tenk1
+ -> Index Only Scan using tenk1_thous_tenthous on tenk1
Index Cond: ((thousand = 33) AND (tenthous IS NOT NULL))
(5 rows)
-> Result
InitPlan 1 (returns $1)
-> Limit
- -> Index Scan using tenk1_unique1 on tenk1
+ -> Index Only Scan using tenk1_unique1 on tenk1
Index Cond: ((unique1 IS NOT NULL) AND (unique1 > int4_tbl.f1))
(7 rows)
-- check some cases that were handled incorrectly in 8.3.0
explain (costs off)
select distinct max(unique2) from tenk1;
- QUERY PLAN
-----------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------
HashAggregate
+ Group Key: $0
InitPlan 1 (returns $0)
-> Limit
- -> Index Scan Backward using tenk1_unique2 on tenk1
+ -> Index Only Scan Backward using tenk1_unique2 on tenk1
Index Cond: (unique2 IS NOT NULL)
-> Result
-(6 rows)
+(7 rows)
select distinct max(unique2) from tenk1;
max
explain (costs off)
select max(unique2) from tenk1 order by 1;
- QUERY PLAN
-----------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------
Sort
Sort Key: ($0)
InitPlan 1 (returns $0)
-> Limit
- -> Index Scan Backward using tenk1_unique2 on tenk1
+ -> Index Only Scan Backward using tenk1_unique2 on tenk1
Index Cond: (unique2 IS NOT NULL)
-> Result
(7 rows)
explain (costs off)
select max(unique2) from tenk1 order by max(unique2);
- QUERY PLAN
-----------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------
Sort
Sort Key: ($0)
InitPlan 1 (returns $0)
-> Limit
- -> Index Scan Backward using tenk1_unique2 on tenk1
+ -> Index Only Scan Backward using tenk1_unique2 on tenk1
Index Cond: (unique2 IS NOT NULL)
-> Result
(7 rows)
explain (costs off)
select max(unique2) from tenk1 order by max(unique2)+1;
- QUERY PLAN
-----------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------
Sort
Sort Key: (($0 + 1))
InitPlan 1 (returns $0)
-> Limit
- -> Index Scan Backward using tenk1_unique2 on tenk1
+ -> Index Only Scan Backward using tenk1_unique2 on tenk1
Index Cond: (unique2 IS NOT NULL)
-> Result
(7 rows)
explain (costs off)
select max(unique2), generate_series(1,3) as g from tenk1 order by g desc;
- QUERY PLAN
-----------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------
Sort
Sort Key: (generate_series(1, 3))
InitPlan 1 (returns $0)
-> Limit
- -> Index Scan Backward using tenk1_unique2 on tenk1
+ -> Index Only Scan Backward using tenk1_unique2 on tenk1
Index Cond: (unique2 IS NOT NULL)
-> Result
(7 rows)
insert into minmaxtest3 values(17), (18);
explain (costs off)
select min(f1), max(f1) from minmaxtest;
- QUERY PLAN
---------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------
Result
InitPlan 1 (returns $0)
-> Limit
-> Merge Append
- Sort Key: public.minmaxtest.f1
- -> Index Scan using minmaxtesti on minmaxtest
+ Sort Key: minmaxtest.f1
+ -> Index Only Scan using minmaxtesti on minmaxtest
Index Cond: (f1 IS NOT NULL)
- -> Index Scan using minmaxtest1i on minmaxtest1 minmaxtest
+ -> Index Only Scan using minmaxtest1i on minmaxtest1
Index Cond: (f1 IS NOT NULL)
- -> Index Scan Backward using minmaxtest2i on minmaxtest2 minmaxtest
+ -> Index Only Scan Backward using minmaxtest2i on minmaxtest2
Index Cond: (f1 IS NOT NULL)
- -> Index Scan using minmaxtest3i on minmaxtest3 minmaxtest
+ -> Index Only Scan using minmaxtest3i on minmaxtest3
Index Cond: (f1 IS NOT NULL)
InitPlan 2 (returns $1)
-> Limit
-> Merge Append
- Sort Key: public.minmaxtest.f1
- -> Index Scan Backward using minmaxtesti on minmaxtest
+ Sort Key: minmaxtest_1.f1
+ -> Index Only Scan Backward using minmaxtesti on minmaxtest minmaxtest_1
Index Cond: (f1 IS NOT NULL)
- -> Index Scan Backward using minmaxtest1i on minmaxtest1 minmaxtest
+ -> Index Only Scan Backward using minmaxtest1i on minmaxtest1 minmaxtest1_1
Index Cond: (f1 IS NOT NULL)
- -> Index Scan using minmaxtest2i on minmaxtest2 minmaxtest
+ -> Index Only Scan using minmaxtest2i on minmaxtest2 minmaxtest2_1
Index Cond: (f1 IS NOT NULL)
- -> Index Scan Backward using minmaxtest3i on minmaxtest3 minmaxtest
+ -> Index Only Scan Backward using minmaxtest3i on minmaxtest3 minmaxtest3_1
Index Cond: (f1 IS NOT NULL)
(25 rows)
11 | 18
(1 row)
+-- DISTINCT doesn't do anything useful here, but it shouldn't fail
+explain (costs off)
+ select distinct min(f1), max(f1) from minmaxtest;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------
+ HashAggregate
+ Group Key: $0, $1
+ InitPlan 1 (returns $0)
+ -> Limit
+ -> Merge Append
+ Sort Key: minmaxtest.f1
+ -> Index Only Scan using minmaxtesti on minmaxtest
+ Index Cond: (f1 IS NOT NULL)
+ -> Index Only Scan using minmaxtest1i on minmaxtest1
+ Index Cond: (f1 IS NOT NULL)
+ -> Index Only Scan Backward using minmaxtest2i on minmaxtest2
+ Index Cond: (f1 IS NOT NULL)
+ -> Index Only Scan using minmaxtest3i on minmaxtest3
+ Index Cond: (f1 IS NOT NULL)
+ InitPlan 2 (returns $1)
+ -> Limit
+ -> Merge Append
+ Sort Key: minmaxtest_1.f1
+ -> Index Only Scan Backward using minmaxtesti on minmaxtest minmaxtest_1
+ Index Cond: (f1 IS NOT NULL)
+ -> Index Only Scan Backward using minmaxtest1i on minmaxtest1 minmaxtest1_1
+ Index Cond: (f1 IS NOT NULL)
+ -> Index Only Scan using minmaxtest2i on minmaxtest2 minmaxtest2_1
+ Index Cond: (f1 IS NOT NULL)
+ -> Index Only Scan Backward using minmaxtest3i on minmaxtest3 minmaxtest3_1
+ Index Cond: (f1 IS NOT NULL)
+ -> Result
+(27 rows)
+
+select distinct min(f1), max(f1) from minmaxtest;
+ min | max
+-----+-----
+ 11 | 18
+(1 row)
+
drop table minmaxtest cascade;
NOTICE: drop cascades to 3 other objects
DETAIL: drop cascades to table minmaxtest1
drop cascades to table minmaxtest2
drop cascades to table minmaxtest3
+-- check for correct detection of nested-aggregate errors
+select max(min(unique1)) from tenk1;
+ERROR: aggregate function calls cannot be nested
+LINE 1: select max(min(unique1)) from tenk1;
+ ^
+select (select max(min(unique1)) from int8_tbl) from tenk1;
+ERROR: aggregate function calls cannot be nested
+LINE 1: select (select max(min(unique1)) from int8_tbl) from tenk1;
+ ^
--
-- Test combinations of DISTINCT and/or ORDER BY
--
(1 row)
select pg_get_viewdef('agg_view1'::regclass);
- pg_get_viewdef
---------------------------------------------------------------------------------------------------------------------------------------------------------
- SELECT aggfns(v.a, v.b, v.c) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c);
+ pg_get_viewdef
+---------------------------------------------------------------------------------------------------------------------
+ SELECT aggfns(v.a, v.b, v.c) AS aggfns +
+ FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c);
(1 row)
create or replace view agg_view1 as
(1 row)
select pg_get_viewdef('agg_view1'::regclass);
- pg_get_viewdef
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- SELECT aggfns(DISTINCT v.a, v.b, v.c) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c), generate_series(1, 3) i(i);
+ pg_get_viewdef
+---------------------------------------------------------------------------------------------------------------------
+ SELECT aggfns(DISTINCT v.a, v.b, v.c) AS aggfns +
+ FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c),+
+ generate_series(1, 3) i(i);
(1 row)
create or replace view agg_view1 as
(1 row)
select pg_get_viewdef('agg_view1'::regclass);
- pg_get_viewdef
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- SELECT aggfns(DISTINCT v.a, v.b, v.c ORDER BY v.b) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c), generate_series(1, 3) i(i);
+ pg_get_viewdef
+---------------------------------------------------------------------------------------------------------------------
+ SELECT aggfns(DISTINCT v.a, v.b, v.c ORDER BY v.b) AS aggfns +
+ FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c),+
+ generate_series(1, 3) i(i);
(1 row)
create or replace view agg_view1 as
(1 row)
select pg_get_viewdef('agg_view1'::regclass);
- pg_get_viewdef
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- SELECT aggfns(v.a, v.b, v.c ORDER BY (v.b + 1)) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c);
+ pg_get_viewdef
+---------------------------------------------------------------------------------------------------------------------
+ SELECT aggfns(v.a, v.b, v.c ORDER BY (v.b + 1)) AS aggfns +
+ FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c);
(1 row)
create or replace view agg_view1 as
(1 row)
select pg_get_viewdef('agg_view1'::regclass);
- pg_get_viewdef
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
- SELECT aggfns(v.a, v.a, v.c ORDER BY v.b) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c);
+ pg_get_viewdef
+---------------------------------------------------------------------------------------------------------------------
+ SELECT aggfns(v.a, v.a, v.c ORDER BY v.b) AS aggfns +
+ FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c);
(1 row)
create or replace view agg_view1 as
(1 row)
select pg_get_viewdef('agg_view1'::regclass);
- pg_get_viewdef
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- SELECT aggfns(v.a, v.b, v.c ORDER BY v.c USING ~<~ NULLS LAST) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c);
+ pg_get_viewdef
+---------------------------------------------------------------------------------------------------------------------
+ SELECT aggfns(v.a, v.b, v.c ORDER BY v.c USING ~<~ NULLS LAST) AS aggfns +
+ FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c);
(1 row)
create or replace view agg_view1 as
(1 row)
select pg_get_viewdef('agg_view1'::regclass);
- pg_get_viewdef
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- SELECT aggfns(DISTINCT v.a, v.b, v.c ORDER BY v.a, v.c USING ~<~ NULLS LAST, v.b) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c), generate_series(1, 2) i(i);
+ pg_get_viewdef
+---------------------------------------------------------------------------------------------------------------------
+ SELECT aggfns(DISTINCT v.a, v.b, v.c ORDER BY v.a, v.c USING ~<~ NULLS LAST, v.b) AS aggfns +
+ FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c),+
+ generate_series(1, 2) i(i);
(1 row)
drop view agg_view1;
a,ab,abcd
(1 row)
+-- string_agg bytea tests
+create table bytea_test_table(v bytea);
+select string_agg(v, '') from bytea_test_table;
+ string_agg
+------------
+
+(1 row)
+
+insert into bytea_test_table values(decode('ff','hex'));
+select string_agg(v, '') from bytea_test_table;
+ string_agg
+------------
+ \xff
+(1 row)
+
+insert into bytea_test_table values(decode('aa','hex'));
+select string_agg(v, '') from bytea_test_table;
+ string_agg
+------------
+ \xffaa
+(1 row)
+
+select string_agg(v, NULL) from bytea_test_table;
+ string_agg
+------------
+ \xffaa
+(1 row)
+
+select string_agg(v, decode('ee', 'hex')) from bytea_test_table;
+ string_agg
+------------
+ \xffeeaa
+(1 row)
+
+drop table bytea_test_table;
+-- FILTER tests
+select min(unique1) filter (where unique1 > 100) from tenk1;
+ min
+-----
+ 101
+(1 row)
+
+select ten, sum(distinct four) filter (where four::text ~ '123') from onek a
+group by ten;
+ ten | sum
+-----+-----
+ 0 |
+ 1 |
+ 2 |
+ 3 |
+ 4 |
+ 5 |
+ 6 |
+ 7 |
+ 8 |
+ 9 |
+(10 rows)
+
+select ten, sum(distinct four) filter (where four > 10) from onek a
+group by ten
+having exists (select 1 from onek b where sum(distinct a.four) = b.four);
+ ten | sum
+-----+-----
+ 0 |
+ 2 |
+ 4 |
+ 6 |
+ 8 |
+(5 rows)
+
+select max(foo COLLATE "C") filter (where (bar collate "POSIX") > '0')
+from (values ('a', 'b')) AS v(foo,bar);
+ max
+-----
+ a
+(1 row)
+
+-- outer reference in FILTER (PostgreSQL extension)
+select (select count(*)
+ from (values (1)) t0(inner_c))
+from (values (2),(3)) t1(outer_c); -- inner query is aggregation query
+ count
+-------
+ 1
+ 1
+(2 rows)
+
+select (select count(*) filter (where outer_c <> 0)
+ from (values (1)) t0(inner_c))
+from (values (2),(3)) t1(outer_c); -- outer query is aggregation query
+ count
+-------
+ 2
+(1 row)
+
+select (select count(inner_c) filter (where outer_c <> 0)
+ from (values (1)) t0(inner_c))
+from (values (2),(3)) t1(outer_c); -- inner query is aggregation query
+ count
+-------
+ 1
+ 1
+(2 rows)
+
+select
+ (select max((select i.unique2 from tenk1 i where i.unique1 = o.unique1))
+ filter (where o.unique1 < 10))
+from tenk1 o; -- outer query is aggregation query
+ max
+------
+ 9998
+(1 row)
+
+-- subquery in FILTER clause (PostgreSQL extension)
+select sum(unique1) FILTER (WHERE
+ unique1 IN (SELECT unique1 FROM onek where unique1 < 100)) FROM tenk1;
+ sum
+------
+ 4950
+(1 row)
+
+-- exercise lots of aggregate parts with FILTER
+select aggfns(distinct a,b,c order by a,c using ~<~,b) filter (where a > 1)
+ from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
+ generate_series(1,2) i;
+ aggfns
+---------------------------
+ {"(2,2,bar)","(3,1,baz)"}
+(1 row)
+
+-- variadic aggregates
+select least_agg(q1,q2) from int8_tbl;
+ least_agg
+-------------------
+ -4567890123456789
+(1 row)
+
+select least_agg(variadic array[q1,q2]) from int8_tbl;
+ least_agg
+-------------------
+ -4567890123456789
+(1 row)
+
projects / postgresql / blobdiff