--
-- AGGREGATES
--
CREATE EXTENSION sqlite_fdw;
CREATE SERVER sqlite_svr FOREIGN DATA WRAPPER sqlite_fdw
OPTIONS (database '/tmp/sqlitefdw_test_core.db');
CREATE FOREIGN TABLE onek(
  unique1   int4 OPTIONS (key 'true'),
  unique2   int4,
  two     int4,
  four    int4,
  ten     int4,
  twenty    int4,
  hundred   int4,
  thousand  int4,
  twothousand int4,
  fivethous int4,
  tenthous  int4,
  odd     int4,
  even    int4,
  stringu1  name,
  stringu2  name,
  string4   name
) SERVER sqlite_svr;
CREATE FOREIGN TABLE aggtest (
  a       int2,
  b     float4
) SERVER sqlite_svr;
CREATE FOREIGN TABLE student (
  name    text,
  age     int4,
  location  point,
  gpa     float8
) SERVER sqlite_svr;
CREATE FOREIGN TABLE tenk1 (
  unique1   int4,
  unique2   int4,
  two     int4,
  four    int4,
  ten     int4,
  twenty    int4,
  hundred   int4,
  thousand  int4,
  twothousand int4,
  fivethous int4,
  tenthous  int4,
  odd     int4,
  even    int4,
  stringu1  name,
  stringu2  name,
  string4   name
) SERVER sqlite_svr;
CREATE FOREIGN TABLE INT8_TBL(
  q1 int8 OPTIONS (key 'true'),
  q2 int8 OPTIONS (key 'true')
) SERVER sqlite_svr;
CREATE FOREIGN TABLE INT4_TBL(f1 int4 OPTIONS (key 'true')) SERVER sqlite_svr; 
CREATE FOREIGN TABLE multi_arg_agg (a int OPTIONS (key 'true'), b int, c text) SERVER sqlite_svr;
CREATE FOREIGN TABLE VARCHAR_TBL(f1 varchar(4) OPTIONS (key 'true')) SERVER sqlite_svr;
CREATE FOREIGN TABLE FLOAT8_TBL(f1 float8 OPTIONS (key 'true')) SERVER sqlite_svr;
SELECT avg(four) AS avg_1 FROM onek;
 avg_1 
-------
   1.5
(1 row)

SELECT avg(a) AS avg_32 FROM aggtest WHERE a < 100;
      avg_32      
------------------
 32.6666666666667
(1 row)

-- In 7.1, avg(float4) is computed using float8 arithmetic.
-- Round the result to 3 digits to avoid platform-specific results.
SELECT avg(b)::numeric(10,3) AS avg_107_943 FROM aggtest;
 avg_107_943 
-------------
     107.943
(1 row)

SELECT avg(gpa) AS avg_3_4 FROM ONLY student;
 avg_3_4 
---------
     3.4
(1 row)

SELECT sum(four) AS sum_1500 FROM onek;
 sum_1500 
----------
     1500
(1 row)

SELECT sum(a) AS sum_198 FROM aggtest;
 sum_198 
---------
     198
(1 row)

SELECT sum(b) AS avg_431_773 FROM aggtest;
 avg_431_773 
-------------
     431.773
(1 row)

SELECT sum(gpa) AS avg_6_8 FROM ONLY student;
 avg_6_8 
---------
     6.8
(1 row)

SELECT max(four) AS max_3 FROM onek;
 max_3 
-------
     3
(1 row)

SELECT max(a) AS max_100 FROM aggtest;
 max_100 
---------
     100
(1 row)

SELECT max(aggtest.b) AS max_324_78 FROM aggtest;
 max_324_78 
------------
     324.78
(1 row)

SELECT max(student.gpa) AS max_3_7 FROM student;
 max_3_7 
---------
     3.7
(1 row)

SELECT stddev_pop(b) FROM aggtest;
   stddev_pop    
-----------------
 131.10703231895
(1 row)

SELECT stddev_samp(b) FROM aggtest;
   stddev_samp    
------------------
 151.389360803998
(1 row)

SELECT var_pop(b) FROM aggtest;
     var_pop      
------------------
 17189.0539234823
(1 row)

SELECT var_samp(b) FROM aggtest;
     var_samp     
------------------
 22918.7385646431
(1 row)

SELECT stddev_pop(b::numeric) FROM aggtest;
    stddev_pop    
------------------
 131.107032862199
(1 row)

SELECT stddev_samp(b::numeric) FROM aggtest;
   stddev_samp    
------------------
 151.389361431288
(1 row)

SELECT var_pop(b::numeric) FROM aggtest;
      var_pop       
--------------------
 17189.054065929769
(1 row)

SELECT var_samp(b::numeric) FROM aggtest;
      var_samp      
--------------------
 22918.738754573025
(1 row)

-- SQL2003 binary aggregates
SELECT regr_count(b, a) FROM aggtest;
 regr_count 
------------
          4
(1 row)

SELECT regr_sxx(b, a) FROM aggtest;
 regr_sxx 
----------
     5099
(1 row)

SELECT regr_syy(b, a) FROM aggtest;
     regr_syy     
------------------
 68756.2156939293
(1 row)

SELECT regr_sxy(b, a) FROM aggtest;
     regr_sxy     
------------------
 2614.51582155004
(1 row)

SELECT regr_avgx(b, a), regr_avgy(b, a) FROM aggtest;
 regr_avgx |    regr_avgy     
-----------+------------------
      49.5 | 107.943152273074
(1 row)

SELECT regr_r2(b, a) FROM aggtest;
      regr_r2       
--------------------
 0.0194977982031803
(1 row)

SELECT regr_slope(b, a), regr_intercept(b, a) FROM aggtest;
    regr_slope     |  regr_intercept  
-------------------+------------------
 0.512750700441271 | 82.5619926012309
(1 row)

SELECT covar_pop(b, a), covar_samp(b, a) FROM aggtest;
    covar_pop    |    covar_samp    
-----------------+------------------
 653.62895538751 | 871.505273850014
(1 row)

SELECT corr(b, a) FROM aggtest;
       corr        
-------------------
 0.139634516517873
(1 row)

SELECT count(four) AS cnt_1000 FROM onek;
 cnt_1000 
----------
     1000
(1 row)

SELECT count(DISTINCT four) AS cnt_4 FROM onek;
 cnt_4 
-------
     4
(1 row)

select ten, count(*), sum(four) from onek
group by ten order by ten;
 ten | count | sum 
-----+-------+-----
   0 |   100 | 100
   1 |   100 | 200
   2 |   100 | 100
   3 |   100 | 200
   4 |   100 | 100
   5 |   100 | 200
   6 |   100 | 100
   7 |   100 | 200
   8 |   100 | 100
   9 |   100 | 200
(10 rows)

select ten, count(four), sum(DISTINCT four) from onek
group by ten order by ten;
 ten | count | sum 
-----+-------+-----
   0 |   100 |   2
   1 |   100 |   4
   2 |   100 |   2
   3 |   100 |   4
   4 |   100 |   2
   5 |   100 |   4
   6 |   100 |   2
   7 |   100 |   4
   8 |   100 |   2
   9 |   100 |   4
(10 rows)

-- user-defined aggregates
CREATE AGGREGATE newavg (
   sfunc = int4_avg_accum, basetype = int4, stype = _int8,
   finalfunc = int8_avg,
   initcond1 = '{0,0}'
);
CREATE AGGREGATE newsum (
   sfunc1 = int4pl, basetype = int4, stype1 = int4,
   initcond1 = '0'
);
CREATE AGGREGATE newcnt (*) (
   sfunc = int8inc, stype = int8,
   initcond = '0', parallel = safe
);
CREATE AGGREGATE newcnt ("any") (
   sfunc = int8inc_any, stype = int8,
   initcond = '0'
);
CREATE AGGREGATE oldcnt (
   sfunc = int8inc, basetype = 'ANY', stype = int8,
   initcond = '0'
);
create function sum3(int8,int8,int8) returns int8 as
'select $1 + $2 + $3' language sql strict immutable;
create aggregate sum2(int8,int8) (
   sfunc = sum3, stype = int8,
   initcond = '0'
);
SELECT newavg(four) AS avg_1 FROM onek;
       avg_1        
--------------------
 1.5000000000000000
(1 row)

SELECT newsum(four) AS sum_1500 FROM onek;
 sum_1500 
----------
     1500
(1 row)

SELECT newcnt(four) AS cnt_1000 FROM onek;
 cnt_1000 
----------
     1000
(1 row)

SELECT newcnt(*) AS cnt_1000 FROM onek;
 cnt_1000 
----------
     1000
(1 row)

SELECT oldcnt(*) AS cnt_1000 FROM onek;
 cnt_1000 
----------
     1000
(1 row)

SELECT sum2(q1,q2) FROM int8_tbl;
       sum2        
-------------------
 18271560493827981
(1 row)

-- test for outer-level aggregates
-- this should work
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);
 ten | sum 
-----+-----
   0 |   2
   2 |   2
   4 |   2
   6 |   2
   8 |   2
(5 rows)

-- this should fail because subquery has an agg of its own in WHERE
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:  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.
-- Per bug report from Daniel Grace.
select
  (select max((select i.unique2 from tenk1 i where i.unique1 = o.unique1)))
from tenk1 o;
 max  
------
 9999
(1 row)

--
-- test for bitwise integer aggregates
--
CREATE FOREIGN TABLE bitwise_test(
  i2 INT2,
  i4 INT4,
  i8 INT8,
  i INTEGER,
  x INT2
) SERVER sqlite_svr;
-- empty case
SELECT
  BIT_AND(i2) AS "?",
  BIT_OR(i4)  AS "?"
FROM bitwise_test;
 ? | ? 
---+---
   |  
(1 row)

INSERT INTO bitwise_test VALUES
  (1, 1, 1, 1, 1),
  (3, 3, 3, null, 2),
  (7, 7, 7, 3, 4);
SELECT
  BIT_AND(i2) AS "1",
  BIT_AND(i4) AS "1",
  BIT_AND(i8) AS "1",
  BIT_AND(i)  AS "?",
  BIT_AND(x)  AS "0",
  BIT_OR(i2)  AS "7",
  BIT_OR(i4)  AS "7",
  BIT_OR(i8)  AS "7",
  BIT_OR(i)   AS "?",
  BIT_OR(x)   AS "7"
FROM bitwise_test;
 1 | 1 | 1 | ? | 0 | 7 | 7 | 7 | ? | 7 
---+---+---+---+---+---+---+---+---+---
 1 | 1 | 1 | 1 | 0 | 7 | 7 | 7 | 3 | 7
(1 row)

CREATE FOREIGN TABLE bool_test(
  b1 BOOL,
  b2 BOOL,
  b3 BOOL,
  b4 BOOL
) SERVER sqlite_svr;
-- empty case
SELECT
  BOOL_AND(b1)   AS "n",
  BOOL_OR(b3)    AS "n"
FROM bool_test;
 n | n 
---+---
   | 
(1 row)

INSERT INTO bool_test VALUES
  (TRUE, null, FALSE, null),
  (FALSE, TRUE, null, null),
  (null, TRUE, FALSE, null);
SELECT
  BOOL_AND(b1)     AS "f",
  BOOL_AND(b2)     AS "t",
  BOOL_AND(b3)     AS "f",
  BOOL_AND(b4)     AS "n",
  BOOL_AND(NOT b2) AS "f",
  BOOL_AND(NOT b3) AS "t"
FROM bool_test;
 f | t | f | n | f | t 
---+---+---+---+---+---
 f | t | f |   | f | t
(1 row)

SELECT
  EVERY(b1)     AS "f",
  EVERY(b2)     AS "t",
  EVERY(b3)     AS "f",
  EVERY(b4)     AS "n",
  EVERY(NOT b2) AS "f",
  EVERY(NOT b3) AS "t"
FROM bool_test;
 f | t | f | n | f | t 
---+---+---+---+---+---
 f | t | f |   | f | t
(1 row)

SELECT
  BOOL_OR(b1)      AS "t",
  BOOL_OR(b2)      AS "t",
  BOOL_OR(b3)      AS "f",
  BOOL_OR(b4)      AS "n",
  BOOL_OR(NOT b2)  AS "f",
  BOOL_OR(NOT b3)  AS "t"
FROM bool_test;
 t | t | f | n | f | t 
---+---+---+---+---+---
 t | t | f |   | f | t
(1 row)

--
-- 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  
--------------
 Foreign Scan
(1 row)

select min(unique1) from tenk1;
 min 
-----
   0
(1 row)

explain (costs off)
  select max(unique1) from tenk1;
  QUERY PLAN  
--------------
 Foreign Scan
(1 row)

select max(unique1) from tenk1;
 max  
------
 9999
(1 row)

explain (costs off)
  select max(unique1) from tenk1 where unique1 < 42;
  QUERY PLAN  
--------------
 Foreign Scan
(1 row)

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  
--------------
 Foreign Scan
(1 row)

select max(unique1) from tenk1 where unique1 > 42;
 max  
------
 9999
(1 row)

-- the planner may choose a generic aggregate here if parallel query is
-- enabled, since that plan will be parallel safe and the "optimized"
-- plan, which has almost identical cost, will not be.  we want to test
-- the optimized plan, so temporarily disable parallel query.
begin;
set local max_parallel_workers_per_gather = 0;
explain (costs off)
  select max(unique1) from tenk1 where unique1 > 42000;
  QUERY PLAN  
--------------
 Foreign Scan
(1 row)

select max(unique1) from tenk1 where unique1 > 42000;
 max 
-----
    
(1 row)

rollback;
-- multi-column index (uses tenk1_thous_tenthous)
explain (costs off)
  select max(tenthous) from tenk1 where thousand = 33;
  QUERY PLAN  
--------------
 Foreign Scan
(1 row)

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  
--------------
 Foreign Scan
(1 row)

select min(tenthous) from tenk1 where thousand = 33;
 min 
-----
  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        
--------------------------
 Foreign Scan on int4_tbl
   SubPlan 1
     ->  Foreign Scan
(3 rows)

select f1, (select min(unique1) from tenk1 where unique1 > f1) AS gt
  from int4_tbl;
     f1      | gt 
-------------+----
           0 |  1
      123456 |   
     -123456 |  0
  2147483647 |   
 -2147483647 |  0
(5 rows)

-- check some cases that were handled incorrectly in 8.3.0
explain (costs off)
  select distinct max(unique2) from tenk1;
            QUERY PLAN            
----------------------------------
 Unique
   ->  Sort
         Sort Key: (max(unique2))
         ->  Foreign Scan
(4 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: (max(unique2))
   ->  Foreign Scan
(3 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: (max(unique2))
   ->  Foreign Scan
(3 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: ((max(unique2) + 1))
   ->  Foreign Scan
(3 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)) DESC
   ->  ProjectSet
         ->  Foreign Scan
(4 rows)

select max(unique2), generate_series(1,3) as g from tenk1 order by g desc;
 max  | g 
------+---
 9999 | 3
 9999 | 2
 9999 | 1
(3 rows)

-- interesting corner case: constant gets optimized into a seqscan
explain (costs off)
  select max(100) from tenk1;
  QUERY PLAN  
--------------
 Foreign Scan
(1 row)

select max(100) from tenk1;
 max 
-----
 100
(1 row)

-- try it on an inheritance tree
create foreign table minmaxtest(f1 int) server sqlite_svr;;
create table minmaxtest1() inherits (minmaxtest);
create table minmaxtest2() inherits (minmaxtest);
create table minmaxtest3() inherits (minmaxtest);
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               
----------------------------------------
 Aggregate
   ->  Append
         ->  Foreign Scan on minmaxtest
         ->  Seq Scan on minmaxtest1
         ->  Seq Scan on minmaxtest2
         ->  Seq Scan on minmaxtest3
(6 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                          
--------------------------------------------------------------
 Unique
   ->  Sort
         Sort Key: (min(minmaxtest.f1)), (max(minmaxtest.f1))
         ->  Aggregate
               ->  Append
                     ->  Foreign Scan on minmaxtest
                     ->  Seq Scan on minmaxtest1
                     ->  Seq Scan on minmaxtest2
                     ->  Seq Scan on minmaxtest3
(9 rows)

select distinct min(f1), max(f1) from minmaxtest;
 min | max 
-----+-----
  11 |  18
(1 row)

-- 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 removal of redundant GROUP BY columns
--
create foreign table agg_t1 (a int OPTIONS (key 'true'), b int OPTIONS (key 'true'), c int, d int) server sqlite_svr;
create foreign table agg_t2 (x int OPTIONS (key 'true'), y int OPTIONS (key 'true'), z int) server sqlite_svr;
-- Non-primary-key columns can be removed from GROUP BY
explain (costs off) select * from agg_t1 group by a,b,c,d;
  QUERY PLAN  
--------------
 Foreign Scan
(1 row)

-- No removal can happen if the complete PK is not present in GROUP BY
explain (costs off) select a,c from agg_t1 group by a,c,d;
  QUERY PLAN  
--------------
 Foreign Scan
(1 row)

-- Test removal across multiple relations
explain (costs off) select *
from agg_t1 inner join agg_t2 on agg_t1.a = agg_t2.x and agg_t1.b = agg_t2.y
group by agg_t1.a,agg_t1.b,agg_t1.c,agg_t1.d,agg_t2.x,agg_t2.y,agg_t2.z;
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Group
   Group Key: agg_t1.a, agg_t1.b, agg_t1.c, agg_t1.d, agg_t2.x, agg_t2.y, agg_t2.z
   ->  Sort
         Sort Key: agg_t1.a, agg_t1.b, agg_t1.c, agg_t1.d, agg_t2.z
         ->  Merge Join
               Merge Cond: ((agg_t1.a = agg_t2.x) AND (agg_t1.b = agg_t2.y))
               ->  Sort
                     Sort Key: agg_t1.a, agg_t1.b
                     ->  Foreign Scan on agg_t1
               ->  Materialize
                     ->  Sort
                           Sort Key: agg_t2.x, agg_t2.y
                           ->  Foreign Scan on agg_t2
(13 rows)

-- Test case where agg_t1 can be optimized but not agg_t2
explain (costs off) select agg_t1.*,agg_t2.x,agg_t2.z
from agg_t1 inner join agg_t2 on agg_t1.a = agg_t2.x and agg_t1.b = agg_t2.y
group by agg_t1.a,agg_t1.b,agg_t1.c,agg_t1.d,agg_t2.x,agg_t2.z;
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Group
   Group Key: agg_t1.a, agg_t1.b, agg_t1.c, agg_t1.d, agg_t2.x, agg_t2.z
   ->  Sort
         Sort Key: agg_t1.a, agg_t1.b, agg_t1.c, agg_t1.d, agg_t2.z
         ->  Merge Join
               Merge Cond: ((agg_t1.a = agg_t2.x) AND (agg_t1.b = agg_t2.y))
               ->  Sort
                     Sort Key: agg_t1.a, agg_t1.b
                     ->  Foreign Scan on agg_t1
               ->  Materialize
                     ->  Sort
                           Sort Key: agg_t2.x, agg_t2.y
                           ->  Foreign Scan on agg_t2
(13 rows)

--
-- Test combinations of DISTINCT and/or ORDER BY
--
begin;
delete from INT8_TBL;
insert into INT8_TBL values (1,4),(2,3),(3,1),(4,2);
select array_agg(q1 order by q2)
  from INT8_TBL;
 array_agg 
-----------
 {3,4,2,1}
(1 row)

select array_agg(q1 order by q1)
  from INT8_TBL;
 array_agg 
-----------
 {1,2,3,4}
(1 row)

select array_agg(q1 order by q1 desc)
  from INT8_TBL;
 array_agg 
-----------
 {4,3,2,1}
(1 row)

select array_agg(q2 order by q1 desc)
  from INT8_TBL;
 array_agg 
-----------
 {2,1,3,4}
(1 row)

delete from INT4_TBL;
insert into INT4_TBL values (1),(2),(1),(3),(null),(2);
select array_agg(distinct f1)
  from INT4_TBL;
  array_agg   
--------------
 {1,2,3,NULL}
(1 row)

select array_agg(distinct f1 order by f1)
  from INT4_TBL;
  array_agg   
--------------
 {1,2,3,NULL}
(1 row)

select array_agg(distinct f1 order by f1 desc)
  from INT4_TBL;
  array_agg   
--------------
 {NULL,3,2,1}
(1 row)

select array_agg(distinct f1 order by f1 desc nulls last)
  from INT4_TBL;
  array_agg   
--------------
 {3,2,1,NULL}
(1 row)

rollback;
-- multi-arg aggs, strict/nonstrict, distinct/order by
create type aggtype as (a integer, b integer, c text);
create function aggf_trans(aggtype[],integer,integer,text) returns aggtype[]
as 'select array_append($1,ROW($2,$3,$4)::aggtype)'
language sql strict immutable;
create function aggfns_trans(aggtype[],integer,integer,text) returns aggtype[]
as 'select array_append($1,ROW($2,$3,$4)::aggtype)'
language sql immutable;
create aggregate aggfstr(integer,integer,text) (
   sfunc = aggf_trans, stype = aggtype[],
   initcond = '{}'
);
create aggregate aggfns(integer,integer,text) (
   sfunc = aggfns_trans, stype = aggtype[], sspace = 10000,
   initcond = '{}'
);
begin;
insert into multi_arg_agg values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz');
select aggfstr(a,b,c) from multi_arg_agg;
                aggfstr                
---------------------------------------
 {"(1,3,foo)","(2,2,bar)","(3,1,baz)"}
(1 row)

select aggfns(a,b,c) from multi_arg_agg;
                    aggfns                     
-----------------------------------------------
 {"(1,3,foo)","(0,,)","(2,2,bar)","(3,1,baz)"}
(1 row)

select aggfstr(distinct a,b,c) from multi_arg_agg, generate_series(1,3) i;
                aggfstr                
---------------------------------------
 {"(1,3,foo)","(2,2,bar)","(3,1,baz)"}
(1 row)

select aggfns(distinct a,b,c) from multi_arg_agg, generate_series(1,3) i;
                    aggfns                     
-----------------------------------------------
 {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"}
(1 row)

select aggfstr(distinct a,b,c order by b) from multi_arg_agg, generate_series(1,3) i;
                aggfstr                
---------------------------------------
 {"(3,1,baz)","(2,2,bar)","(1,3,foo)"}
(1 row)

select aggfns(distinct a,b,c order by b) from multi_arg_agg, generate_series(1,3) i;
                    aggfns                     
-----------------------------------------------
 {"(3,1,baz)","(2,2,bar)","(1,3,foo)","(0,,)"}
(1 row)

-- test specific code paths
select aggfns(distinct a,a,c order by c using ~<~,a) from multi_arg_agg, generate_series(1,2) i;
                     aggfns                     
------------------------------------------------
 {"(2,2,bar)","(3,3,baz)","(1,1,foo)","(0,0,)"}
(1 row)

select aggfns(distinct a,a,c order by c using ~<~) from multi_arg_agg, generate_series(1,2) i;
                     aggfns                     
------------------------------------------------
 {"(2,2,bar)","(3,3,baz)","(1,1,foo)","(0,0,)"}
(1 row)

select aggfns(distinct a,a,c order by a) from multi_arg_agg, generate_series(1,2) i;
                     aggfns                     
------------------------------------------------
 {"(0,0,)","(1,1,foo)","(2,2,bar)","(3,3,baz)"}
(1 row)

select aggfns(distinct a,b,c order by a,c using ~<~,b) from multi_arg_agg, generate_series(1,2) i;
                    aggfns                     
-----------------------------------------------
 {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"}
(1 row)

-- check node I/O via view creation and usage, also deparsing logic
create view agg_view1 as
  select aggfns(a,b,c) from multi_arg_agg;
select * from agg_view1;
                    aggfns                     
-----------------------------------------------
 {"(1,3,foo)","(0,,)","(2,2,bar)","(3,1,baz)"}
(1 row)

select pg_get_viewdef('agg_view1'::regclass);
                               pg_get_viewdef                                
-----------------------------------------------------------------------------
  SELECT aggfns(multi_arg_agg.a, multi_arg_agg.b, multi_arg_agg.c) AS aggfns+
    FROM multi_arg_agg;
(1 row)

create or replace view agg_view1 as
  select aggfns(distinct a,b,c) from multi_arg_agg, generate_series(1,3) i;
select * from agg_view1;
                    aggfns                     
-----------------------------------------------
 {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"}
(1 row)

select pg_get_viewdef('agg_view1'::regclass);
                                    pg_get_viewdef                                    
--------------------------------------------------------------------------------------
  SELECT aggfns(DISTINCT multi_arg_agg.a, multi_arg_agg.b, multi_arg_agg.c) AS aggfns+
    FROM multi_arg_agg,                                                              +
     generate_series(1, 3) i(i);
(1 row)

create or replace view agg_view1 as
  select aggfns(distinct a,b,c order by b) from multi_arg_agg, generate_series(1,3) i;
select * from agg_view1;
                    aggfns                     
-----------------------------------------------
 {"(3,1,baz)","(2,2,bar)","(1,3,foo)","(0,,)"}
(1 row)

select pg_get_viewdef('agg_view1'::regclass);
                                                pg_get_viewdef                                                 
---------------------------------------------------------------------------------------------------------------
  SELECT aggfns(DISTINCT multi_arg_agg.a, multi_arg_agg.b, multi_arg_agg.c ORDER BY multi_arg_agg.b) AS aggfns+
    FROM multi_arg_agg,                                                                                       +
     generate_series(1, 3) i(i);
(1 row)

create or replace view agg_view1 as
  select aggfns(a,b,c order by b+1) from multi_arg_agg;
select * from agg_view1;
                    aggfns                     
-----------------------------------------------
 {"(3,1,baz)","(2,2,bar)","(1,3,foo)","(0,,)"}
(1 row)

select pg_get_viewdef('agg_view1'::regclass);
                                               pg_get_viewdef                                               
------------------------------------------------------------------------------------------------------------
  SELECT aggfns(multi_arg_agg.a, multi_arg_agg.b, multi_arg_agg.c ORDER BY (multi_arg_agg.b + 1)) AS aggfns+
    FROM multi_arg_agg;
(1 row)

create or replace view agg_view1 as
  select aggfns(a,a,c order by b) from multi_arg_agg;
select * from agg_view1;
                     aggfns                     
------------------------------------------------
 {"(3,3,baz)","(2,2,bar)","(1,1,foo)","(0,0,)"}
(1 row)

select pg_get_viewdef('agg_view1'::regclass);
                                            pg_get_viewdef                                            
------------------------------------------------------------------------------------------------------
  SELECT aggfns(multi_arg_agg.a, multi_arg_agg.a, multi_arg_agg.c ORDER BY multi_arg_agg.b) AS aggfns+
    FROM multi_arg_agg;
(1 row)

create or replace view agg_view1 as
  select aggfns(a,b,c order by c using ~<~) from multi_arg_agg;
select * from agg_view1;
                    aggfns                     
-----------------------------------------------
 {"(2,2,bar)","(3,1,baz)","(1,3,foo)","(0,,)"}
(1 row)

select pg_get_viewdef('agg_view1'::regclass);
                                                      pg_get_viewdef                                                       
---------------------------------------------------------------------------------------------------------------------------
  SELECT aggfns(multi_arg_agg.a, multi_arg_agg.b, multi_arg_agg.c ORDER BY multi_arg_agg.c USING ~<~ NULLS LAST) AS aggfns+
    FROM multi_arg_agg;
(1 row)

create or replace view agg_view1 as
  select aggfns(distinct a,b,c order by a,c using ~<~,b) from multi_arg_agg, generate_series(1,2) i;
select * from agg_view1;
                    aggfns                     
-----------------------------------------------
 {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"}
(1 row)

select pg_get_viewdef('agg_view1'::regclass);
                                                                            pg_get_viewdef                                                                            
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
  SELECT aggfns(DISTINCT multi_arg_agg.a, multi_arg_agg.b, multi_arg_agg.c ORDER BY multi_arg_agg.a, multi_arg_agg.c USING ~<~ NULLS LAST, multi_arg_agg.b) AS aggfns+
    FROM multi_arg_agg,                                                                                                                                              +
     generate_series(1, 2) i(i);
(1 row)

drop view agg_view1;
rollback;
-- incorrect DISTINCT usage errors
insert into multi_arg_agg values (1,1,'foo');
select aggfns(distinct a,b,c order by i) from multi_arg_agg, generate_series(1,2) i;
ERROR:  in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
LINE 1: select aggfns(distinct a,b,c order by i) from multi_arg_agg,...
                                              ^
select aggfns(distinct a,b,c order by a,b+1) from multi_arg_agg, generate_series(1,2) i;
ERROR:  in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
LINE 1: select aggfns(distinct a,b,c order by a,b+1) from multi_arg_...
                                                ^
select aggfns(distinct a,b,c order by a,b,i,c) from multi_arg_agg, generate_series(1,2) i;
ERROR:  in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
LINE 1: select aggfns(distinct a,b,c order by a,b,i,c) from multi_ar...
                                                  ^
select aggfns(distinct a,a,c order by a,b) from multi_arg_agg, generate_series(1,2) i;
ERROR:  in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
LINE 1: select aggfns(distinct a,a,c order by a,b) from multi_arg_ag...
                                                ^
-- string_agg tests
begin;
delete from varchar_tbl;
insert into varchar_tbl values ('aaaa'),('bbbb'),('cccc');
select string_agg(f1,',') from varchar_tbl;
   string_agg   
----------------
 aaaa,bbbb,cccc
(1 row)

delete from varchar_tbl;
insert into varchar_tbl values ('aaaa'),(null),('bbbb'),('cccc');
select string_agg(f1,',') from varchar_tbl;
   string_agg   
----------------
 aaaa,bbbb,cccc
(1 row)

delete from varchar_tbl;
insert into varchar_tbl values (null),(null),('bbbb'),('cccc');
select string_agg(f1,'AB') from varchar_tbl;
 string_agg 
------------
 bbbbABcccc
(1 row)

delete from varchar_tbl;
insert into varchar_tbl values (null),(null);
select string_agg(f1,',') from varchar_tbl;
 string_agg 
------------
 
(1 row)

rollback;
-- check some implicit casting cases, as per bug #5564
select string_agg(distinct f1, ',' order by f1) from varchar_tbl;  -- ok
 string_agg 
------------
 a,ab,abcd
(1 row)

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 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 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 foreign table bytea_test_table(v bytea) server sqlite_svr;
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 foreign table bytea_test_table;
-- FILTER tests
select min(unique1) filter (where unique1 > 100) from tenk1;
 min 
-----
 101
(1 row)

select sum(1/ten) filter (where ten > 0) from tenk1;
 sum  
------
 1000
(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
  (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
begin;
delete from multi_arg_agg;
insert into multi_arg_agg values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz');
select aggfns(distinct a,b,c order by a,c using ~<~,b) filter (where a > 1) from multi_arg_agg, generate_series(1,2) i;
          aggfns           
---------------------------
 {"(2,2,bar)","(3,1,baz)"}
(1 row)

rollback;
-- ordered-set aggregates
begin;
delete from FLOAT8_TBL;
insert into FLOAT8_TBL values (0::float8),(0.1),(0.25),(0.4),(0.5),(0.6),(0.75),(0.9),(1);
select f1, percentile_cont(f1) within group (order by x::float8)
from generate_series(1,5) x,
     FLOAT8_TBL
group by f1 order by f1;
  f1  | percentile_cont 
------+-----------------
    0 |               1
  0.1 |             1.4
 0.25 |               2
  0.4 |             2.6
  0.5 |               3
  0.6 |             3.4
 0.75 |               4
  0.9 |             4.6
    1 |               5
(9 rows)

rollback;
begin;
delete from FLOAT8_TBL;
insert into FLOAT8_TBL values (0::float8),(0.1),(0.25),(0.4),(0.5),(0.6),(0.75),(0.9),(1);
select f1, percentile_cont(f1 order by f1) within group (order by x)  -- error
from generate_series(1,5) x,
     FLOAT8_TBL
group by f1 order by f1;
ERROR:  cannot use multiple ORDER BY clauses with WITHIN GROUP
LINE 1: select f1, percentile_cont(f1 order by f1) within group (ord...
                                                   ^
rollback;
begin;
delete from FLOAT8_TBL;
insert into FLOAT8_TBL values (0::float8),(0.1),(0.25),(0.4),(0.5),(0.6),(0.75),(0.9),(1);
select f1, sum() within group (order by x::float8)  -- error
from generate_series(1,5) x,
     FLOAT8_TBL
group by f1 order by f1;
ERROR:  sum is not an ordered-set aggregate, so it cannot have WITHIN GROUP
LINE 1: select f1, sum() within group (order by x::float8)  
                   ^
rollback;
begin;
delete from FLOAT8_TBL;
insert into FLOAT8_TBL values (0::float8),(0.1),(0.25),(0.4),(0.5),(0.6),(0.75),(0.9),(1);
select f1, percentile_cont(f1,f1)  -- error
from generate_series(1,5) x,
     FLOAT8_TBL
group by f1 order by f1;
ERROR:  WITHIN GROUP is required for ordered-set aggregate percentile_cont
LINE 1: select f1, percentile_cont(f1,f1)  
                   ^
rollback;
select percentile_cont(0.5) within group (order by b) from aggtest;
 percentile_cont  
------------------
 53.4485001564026
(1 row)

select percentile_cont(0.5) within group (order by b), sum(b) from aggtest;
 percentile_cont  |   sum   
------------------+---------
 53.4485001564026 | 431.773
(1 row)

select percentile_cont(0.5) within group (order by thousand) from tenk1;
 percentile_cont 
-----------------
           499.5
(1 row)

select percentile_disc(0.5) within group (order by thousand) from tenk1;
 percentile_disc 
-----------------
             499
(1 row)

begin;
delete from INT4_TBL;
insert into INT4_TBL values (1),(1),(2),(2),(3),(3),(4);
select rank(3) within group (order by f1) from INT4_TBL;
 rank 
------
    5
(1 row)

select cume_dist(3) within group (order by f1) from INT4_TBL;
 cume_dist 
-----------
     0.875
(1 row)

select percent_rank(3) within group (order by f1) from INT4_TBL;
   percent_rank    
-------------------
 0.571428571428571
(1 row)

select dense_rank(3) within group (order by f1) from INT4_TBL;
 dense_rank 
------------
          3
(1 row)

rollback;
select percentile_disc(array[0,0.1,0.25,0.5,0.75,0.9,1]) within group (order by thousand)
from tenk1;
      percentile_disc       
----------------------------
 {0,99,249,499,749,899,999}
(1 row)

select percentile_cont(array[0,0.25,0.5,0.75,1]) within group (order by thousand)
from tenk1;
       percentile_cont       
-----------------------------
 {0,249.75,499.5,749.25,999}
(1 row)

select percentile_disc(array[[null,1,0.5],[0.75,0.25,null]]) within group (order by thousand)
from tenk1;
         percentile_disc         
---------------------------------
 {{NULL,999,499},{749,249,NULL}}
(1 row)

select ten, mode() within group (order by string4) from tenk1 group by ten;
 ten |  mode  
-----+--------
   0 | HHHHxx
   1 | OOOOxx
   2 | VVVVxx
   3 | OOOOxx
   4 | HHHHxx
   5 | HHHHxx
   6 | OOOOxx
   7 | AAAAxx
   8 | VVVVxx
   9 | VVVVxx
(10 rows)

-- ordered-set aggs created with CREATE AGGREGATE
create aggregate my_percentile_disc(float8 ORDER BY anyelement) (
  stype = internal,
  sfunc = ordered_set_transition,
  finalfunc = percentile_disc_final,
  finalfunc_extra = true,
  finalfunc_modify = read_write
);
alter aggregate my_percentile_disc(float8 ORDER BY anyelement)
  rename to test_percentile_disc;
select test_percentile_disc(0.5) within group (order by thousand) from tenk1;
 test_percentile_disc 
----------------------
                  499
(1 row)

-- hypothetical-set type unification and argument-count failures:
select rank(3) within group (order by stringu1,stringu2) from tenk1;
ERROR:  function rank(integer, name, name) does not exist
LINE 1: select rank(3) within group (order by stringu1,stringu2) fro...
               ^
HINT:  To use the hypothetical-set aggregate rank, the number of hypothetical direct arguments (here 1) must match the number of ordering columns (here 2).
-- deparse and multiple features:
create view aggordview1 as
select ten,
       percentile_disc(0.5) within group (order by thousand) as p50,
       percentile_disc(0.5) within group (order by thousand) filter (where hundred=1) as px,
       rank(5,'AZZZZ',50) within group (order by hundred, string4 desc, hundred)
  from tenk1
 group by ten order by ten;
select pg_get_viewdef('aggordview1');
                                                        pg_get_viewdef                                                         
-------------------------------------------------------------------------------------------------------------------------------
  SELECT tenk1.ten,                                                                                                           +
     percentile_disc((0.5)::double precision) WITHIN GROUP (ORDER BY tenk1.thousand) AS p50,                                  +
     percentile_disc((0.5)::double precision) WITHIN GROUP (ORDER BY tenk1.thousand) FILTER (WHERE (tenk1.hundred = 1)) AS px,+
     rank(5, 'AZZZZ'::name, 50) WITHIN GROUP (ORDER BY tenk1.hundred, tenk1.string4 DESC, tenk1.hundred) AS rank              +
    FROM tenk1                                                                                                                +
   GROUP BY tenk1.ten                                                                                                         +
   ORDER BY tenk1.ten;
(1 row)

select * from aggordview1 order by ten;
 ten | p50 | px  | rank 
-----+-----+-----+------
   0 | 490 |     |  101
   1 | 491 | 401 |  101
   2 | 492 |     |  101
   3 | 493 |     |  101
   4 | 494 |     |  101
   5 | 495 |     |   67
   6 | 496 |     |    1
   7 | 497 |     |    1
   8 | 498 |     |    1
   9 | 499 |     |    1
(10 rows)

drop view aggordview1;
-- variadic aggregates
create function least_accum(anyelement, variadic anyarray)
returns anyelement language sql as
  'select least($1, min($2[i])) from generate_subscripts($2,1) g(i)';
create aggregate least_agg(variadic items anyarray) (
  stype = anyelement, sfunc = least_accum
);
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)

-- test that the aggregate transition logic correctly handles
-- transition / combine functions returning NULL
-- First test the case of a normal transition function returning NULL
BEGIN;
CREATE FUNCTION balkifnull(int8, int4)
RETURNS int8
STRICT
LANGUAGE plpgsql AS $$
BEGIN
    IF $1 IS NULL THEN
       RAISE 'erroneously called with NULL argument';
    END IF;
    RETURN NULL;
END$$;
CREATE AGGREGATE balk(int4)
(
    SFUNC = balkifnull(int8, int4),
    STYPE = int8,
    PARALLEL = SAFE,
    INITCOND = '0'
);
SELECT balk(hundred) FROM tenk1;
 balk 
------
     
(1 row)

ROLLBACK;
DO $d$
declare
  l_rec record;
begin
  for l_rec in (select foreign_table_schema, foreign_table_name 
                from information_schema.foreign_tables) loop
     execute format('drop foreign table %I.%I cascade;', l_rec.foreign_table_schema, l_rec.foreign_table_name);
  end loop;
end;
$d$;
NOTICE:  drop cascades to 3 other objects
DETAIL:  drop cascades to table minmaxtest1
drop cascades to table minmaxtest2
drop cascades to table minmaxtest3
DROP SERVER sqlite_svr;
DROP EXTENSION sqlite_fdw CASCADE;