X-Git-Url: https://granicus.if.org/sourcecode?a=blobdiff_plain;f=src%2Ftest%2Fregress%2Fexpected%2Faggregates.out;h=1a0ca5c5f3c109b92e5fb936efa42dafe982c81a;hb=f26099057a2818d85edc2a16e2d3161f4bd96bdc;hp=087b4679d4d826e362a09b3bcf28a9ca4fcc08f9;hpb=fba999cb2c8aeafc113b965682bcc9f9ce081c88;p=postgresql diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out index 087b4679d4..1a0ca5c5f3 100644 --- a/src/test/regress/expected/aggregates.out +++ b/src/test/regress/expected/aggregates.out @@ -137,6 +137,67 @@ SELECT stddev_pop(3.0::numeric), stddev_samp(4.0::numeric); 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 @@ -292,7 +353,7 @@ select ten, sum(distinct four) from onek a 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. @@ -300,9 +361,9 @@ LINE 4: where sum(distinct a.four + b.four) = b.four)... select (select max((select i.unique2 from tenk1 i where i.unique1 = o.unique1))) from tenk1 o; - ?column? ----------- - 9999 + max +------ + 9999 (1 row) -- @@ -317,7 +378,7 @@ CREATE TEMPORARY TABLE bitwise_test( y BIT(4) ); -- empty case -SELECT +SELECT BIT_AND(i2) AS "?", BIT_OR(i4) AS "?" FROM bitwise_test; @@ -386,7 +447,7 @@ SELECT t | t | t | t | t | t | t | t | t (1 row) -CREATE TEMPORARY TABLE bool_test( +CREATE TEMPORARY TABLE bool_test( b1 BOOL, b2 BOOL, b3 BOOL, @@ -442,29 +503,89 @@ FROM bool_test; (1 row) -- --- Test several cases that should be optimized into indexscans instead of --- the generic aggregate implementation. We can't actually verify that they --- are done as indexscans, but we can check that the results are correct. +-- Test cases that should be optimized into indexscans instead of +-- the generic aggregate implementation. -- -- Basic cases +explain (costs off) + select min(unique1) from tenk1; + QUERY PLAN +------------------------------------------------------------ + Result + InitPlan 1 (returns $0) + -> Limit + -> Index Only Scan using tenk1_unique1 on tenk1 + Index Cond: (unique1 IS NOT NULL) +(5 rows) + +select min(unique1) from tenk1; + min +----- + 0 +(1 row) + +explain (costs off) + select max(unique1) from tenk1; + QUERY PLAN +--------------------------------------------------------------------- + Result + InitPlan 1 (returns $0) + -> Limit + -> Index Only Scan Backward using tenk1_unique1 on tenk1 + Index Cond: (unique1 IS NOT NULL) +(5 rows) + select max(unique1) from tenk1; max ------ 9999 (1 row) +explain (costs off) + select max(unique1) from tenk1 where unique1 < 42; + QUERY PLAN +------------------------------------------------------------------------ + Result + InitPlan 1 (returns $0) + -> Limit + -> Index Only Scan Backward using tenk1_unique1 on tenk1 + Index Cond: ((unique1 IS NOT NULL) AND (unique1 < 42)) +(5 rows) + select max(unique1) from tenk1 where unique1 < 42; max ----- 41 (1 row) +explain (costs off) + select max(unique1) from tenk1 where unique1 > 42; + QUERY PLAN +------------------------------------------------------------------------ + Result + InitPlan 1 (returns $0) + -> Limit + -> Index Only Scan Backward using tenk1_unique1 on tenk1 + Index Cond: ((unique1 IS NOT NULL) AND (unique1 > 42)) +(5 rows) + select max(unique1) from tenk1 where unique1 > 42; max ------ 9999 (1 row) +explain (costs off) + select max(unique1) from tenk1 where unique1 > 42000; + QUERY PLAN +--------------------------------------------------------------------------- + Result + InitPlan 1 (returns $0) + -> Limit + -> Index Only Scan Backward using tenk1_unique1 on tenk1 + Index Cond: ((unique1 IS NOT NULL) AND (unique1 > 42000)) +(5 rows) + select max(unique1) from tenk1 where unique1 > 42000; max ----- @@ -472,12 +593,34 @@ select max(unique1) from tenk1 where unique1 > 42000; (1 row) -- multi-column index (uses tenk1_thous_tenthous) +explain (costs off) + select max(tenthous) from tenk1 where thousand = 33; + QUERY PLAN +---------------------------------------------------------------------------- + Result + InitPlan 1 (returns $0) + -> Limit + -> Index Only Scan Backward using tenk1_thous_tenthous on tenk1 + Index Cond: ((thousand = 33) AND (tenthous IS NOT NULL)) +(5 rows) + select max(tenthous) from tenk1 where thousand = 33; max ------ 9033 (1 row) +explain (costs off) + select min(tenthous) from tenk1 where thousand = 33; + QUERY PLAN +-------------------------------------------------------------------------- + Result + InitPlan 1 (returns $0) + -> Limit + -> Index Only Scan using tenk1_thous_tenthous on tenk1 + Index Cond: ((thousand = 33) AND (tenthous IS NOT NULL)) +(5 rows) + select min(tenthous) from tenk1 where thousand = 33; min ----- @@ -485,8 +628,22 @@ select min(tenthous) from tenk1 where thousand = 33; (1 row) -- check parameter propagation into an indexscan subquery +explain (costs off) + select f1, (select min(unique1) from tenk1 where unique1 > f1) AS gt + from int4_tbl; + QUERY PLAN +----------------------------------------------------------------------------------------- + Seq Scan on int4_tbl + SubPlan 2 + -> Result + InitPlan 1 (returns $1) + -> Limit + -> Index Only Scan using tenk1_unique1 on tenk1 + Index Cond: ((unique1 IS NOT NULL) AND (unique1 > int4_tbl.f1)) +(7 rows) + select f1, (select min(unique1) from tenk1 where unique1 > f1) AS gt -from int4_tbl; + from int4_tbl; f1 | gt -------------+---- 0 | 1 @@ -497,30 +654,95 @@ from int4_tbl; (5 rows) -- check some cases that were handled incorrectly in 8.3.0 +explain (costs off) + select distinct max(unique2) from tenk1; + QUERY PLAN +--------------------------------------------------------------------- + HashAggregate + Group Key: $0 + InitPlan 1 (returns $0) + -> Limit + -> Index Only Scan Backward using tenk1_unique2 on tenk1 + Index Cond: (unique2 IS NOT NULL) + -> Result +(7 rows) + select distinct max(unique2) from tenk1; max ------ 9999 (1 row) +explain (costs off) + select max(unique2) from tenk1 order by 1; + QUERY PLAN +--------------------------------------------------------------------- + Sort + Sort Key: ($0) + InitPlan 1 (returns $0) + -> Limit + -> Index Only Scan Backward using tenk1_unique2 on tenk1 + Index Cond: (unique2 IS NOT NULL) + -> Result +(7 rows) + select max(unique2) from tenk1 order by 1; max ------ 9999 (1 row) +explain (costs off) + select max(unique2) from tenk1 order by max(unique2); + QUERY PLAN +--------------------------------------------------------------------- + Sort + Sort Key: ($0) + InitPlan 1 (returns $0) + -> Limit + -> Index Only Scan Backward using tenk1_unique2 on tenk1 + Index Cond: (unique2 IS NOT NULL) + -> Result +(7 rows) + select max(unique2) from tenk1 order by max(unique2); max ------ 9999 (1 row) +explain (costs off) + select max(unique2) from tenk1 order by max(unique2)+1; + QUERY PLAN +--------------------------------------------------------------------- + Sort + Sort Key: (($0 + 1)) + InitPlan 1 (returns $0) + -> Limit + -> Index Only Scan Backward using tenk1_unique2 on tenk1 + Index Cond: (unique2 IS NOT NULL) + -> Result +(7 rows) + select max(unique2) from tenk1 order by max(unique2)+1; max ------ 9999 (1 row) +explain (costs off) + select max(unique2), generate_series(1,3) as g from tenk1 order by g desc; + QUERY PLAN +--------------------------------------------------------------------- + Sort + Sort Key: (generate_series(1, 3)) + InitPlan 1 (returns $0) + -> Limit + -> Index Only Scan Backward using tenk1_unique2 on tenk1 + Index Cond: (unique2 IS NOT NULL) + -> Result +(7 rows) + select max(unique2), generate_series(1,3) as g from tenk1 order by g desc; max | g ------+--- @@ -529,6 +751,110 @@ select max(unique2), generate_series(1,3) as g from tenk1 order by g desc; 9999 | 1 (3 rows) +-- try it on an inheritance tree +create table minmaxtest(f1 int); +create table minmaxtest1() inherits (minmaxtest); +create table minmaxtest2() inherits (minmaxtest); +create table minmaxtest3() inherits (minmaxtest); +create index minmaxtesti on minmaxtest(f1); +create index minmaxtest1i on minmaxtest1(f1); +create index minmaxtest2i on minmaxtest2(f1 desc); +create index minmaxtest3i on minmaxtest3(f1) where f1 is not null; +insert into minmaxtest values(11), (12); +insert into minmaxtest1 values(13), (14); +insert into minmaxtest2 values(15), (16); +insert into minmaxtest3 values(17), (18); +explain (costs off) + select min(f1), max(f1) from minmaxtest; + QUERY PLAN +---------------------------------------------------------------------------------------------- + Result + 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) +(25 rows) + +select min(f1), max(f1) from minmaxtest; + min | max +-----+----- + 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 -- @@ -679,9 +1005,10 @@ select * from agg_view1; (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 @@ -695,9 +1022,11 @@ select * from agg_view1; (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 @@ -711,9 +1040,11 @@ select * from agg_view1; (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 @@ -726,9 +1057,10 @@ select * from agg_view1; (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 @@ -741,9 +1073,10 @@ select * from agg_view1; (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 @@ -756,9 +1089,10 @@ select * from agg_view1; (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 @@ -772,9 +1106,11 @@ select * from agg_view1; (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; @@ -800,12 +1136,6 @@ ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argum LINE 1: select aggfns(distinct a,a,c order by a,b) ^ -- string_agg tests -select string_agg(a) from (values('aaaa'),('bbbb'),('cccc')) g(a); - string_agg --------------- - aaaabbbbcccc -(1 row) - select string_agg(a,',') from (values('aaaa'),('bbbb'),('cccc')) g(a); string_agg ---------------- @@ -818,10 +1148,10 @@ select string_agg(a,',') from (values('aaaa'),(null),('bbbb'),('cccc')) g(a); aaaa,bbbb,cccc (1 row) -select string_agg(a,',') from (values(null),(null),('bbbb'),('cccc')) g(a); +select string_agg(a,'AB') from (values(null),(null),('bbbb'),('cccc')) g(a); string_agg ------------ - bbbb,cccc + bbbbABcccc (1 row) select string_agg(a,',') from (values(null),(null)) g(a); @@ -831,23 +1161,166 @@ select string_agg(a,',') from (values(null),(null)) g(a); (1 row) -- check some implicit casting cases, as per bug #5564 -select string_agg(distinct f1 order by f1) from varchar_tbl; -- ok +select string_agg(distinct f1, ',' order by f1) from varchar_tbl; -- ok string_agg ------------ - aababcd + a,ab,abcd (1 row) -select string_agg(distinct f1::text order by f1) from varchar_tbl; -- not ok +select string_agg(distinct f1::text, ',' order by f1) from varchar_tbl; -- not ok ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list -LINE 1: select string_agg(distinct f1::text order by f1) from varcha... - ^ -select string_agg(distinct f1 order by f1::text) from varchar_tbl; -- not ok +LINE 1: select string_agg(distinct f1::text, ',' order by f1) from v... + ^ +select string_agg(distinct f1, ',' order by f1::text) from varchar_tbl; -- not ok ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list -LINE 1: select string_agg(distinct f1 order by f1::text) from varcha... - ^ -select string_agg(distinct f1::text order by f1::text) from varchar_tbl; -- ok +LINE 1: select string_agg(distinct f1, ',' order by f1::text) from v... + ^ +select string_agg(distinct f1::text, ',' order by f1::text) from varchar_tbl; -- ok + string_agg +------------ + 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 ------------ - aababcd + \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)