Repository: incubator-hawq Updated Branches: refs/heads/master 17744320e -> 96a6656e1
HAWQ-988. Add feature test for create agg. Project: http://git-wip-us.apache.org/repos/asf/incubator-hawq/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-hawq/commit/96a6656e Tree: http://git-wip-us.apache.org/repos/asf/incubator-hawq/tree/96a6656e Diff: http://git-wip-us.apache.org/repos/asf/incubator-hawq/diff/96a6656e Branch: refs/heads/master Commit: 96a6656e17269df783737018ca7e9e8dc24927eb Parents: e0a072b Author: ztao1987 <[email protected]> Authored: Tue Aug 9 07:35:57 2016 +0800 Committer: ztao1987 <[email protected]> Committed: Tue Aug 9 10:19:24 2016 +0800 ---------------------------------------------------------------------- src/test/feature/query/test_aggregate.cpp | 2 +- src/test/regress/expected/create_aggregate.out | 361 -------------------- src/test/regress/goh_schedule | 1 - src/test/regress/known_good_schedule | 1 - src/test/regress/parallel_schedule | 2 +- src/test/regress/serial_schedule | 1 - src/test/regress/sql/create_aggregate.sql | 304 ----------------- 7 files changed, 2 insertions(+), 670 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/96a6656e/src/test/feature/query/test_aggregate.cpp ---------------------------------------------------------------------- diff --git a/src/test/feature/query/test_aggregate.cpp b/src/test/feature/query/test_aggregate.cpp index 589ff4f..2cb24bf 100644 --- a/src/test/feature/query/test_aggregate.cpp +++ b/src/test/feature/query/test_aggregate.cpp @@ -45,7 +45,7 @@ TEST_F(TestAggregate, TestCreateAggregate) { util.execute("drop table if exists t"); hawq::test::DataGenerator dGen(&util); dGen.genSimpleTable("t"); - util.query("select avg(b) as bavg from t group by a order by bavg","3|\n15|\n62|\n"); + util.query("select sum2(a,b) as absum from t group by a,b order by absum","4|\n29|\n113|\n"); util.query("select newavg(b) as bavg from t group by a order by bavg","3|\n15|\n62|\n"); } http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/96a6656e/src/test/regress/expected/create_aggregate.out ---------------------------------------------------------------------- diff --git a/src/test/regress/expected/create_aggregate.out b/src/test/regress/expected/create_aggregate.out deleted file mode 100755 index ca638fc..0000000 --- a/src/test/regress/expected/create_aggregate.out +++ /dev/null @@ -1,361 +0,0 @@ --- --- CREATE_AGGREGATE --- --- all functions CREATEd -CREATE AGGREGATE newavg ( - sfunc = int4_avg_accum, basetype = int4, stype = bytea, - finalfunc = int8_avg, - initcond1 = '{0}' -); --- test comments -COMMENT ON AGGREGATE newavg_wrong (int4) IS 'an agg comment'; -ERROR: aggregate newavg_wrong(integer) does not exist -COMMENT ON AGGREGATE newavg (int4) IS 'an agg comment'; -COMMENT ON AGGREGATE newavg (int4) IS NULL; --- without finalfunc; test obsolete spellings 'sfunc1' etc -CREATE AGGREGATE newsum ( - sfunc1 = int4pl, basetype = int4, stype1 = int4, - initcond1 = '0' -); --- zero-argument aggregate -CREATE AGGREGATE newcnt (*) ( - sfunc = int8inc, stype = int8, - initcond = '0' -); --- old-style spelling of same -CREATE AGGREGATE oldcnt ( - sfunc = int8inc, basetype = 'ANY', stype = int8, - initcond = '0' -); --- aggregate that only cares about null/nonnull input -CREATE AGGREGATE newcnt ("any") ( - sfunc = int8inc_any, stype = int8, - initcond = '0' -); --- multi-argument aggregate -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' -); --- multi-argument aggregates sensitive to distinct/order, strict/nonstrict -/* --- MPP: In Postgres this creates an array type with it, in GP it does not. -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[], - initcond = '{}' -); -*/ -create function aggf_trans(text[],integer,integer,text) returns text[] -as 'select array_append($1, textin(record_out(ROW($2,$3,$4))))' -language sql strict immutable; -create function aggfns_trans(text[],integer,integer,text) returns text[] -as 'select array_append($1, textin(record_out(ROW($2,$3,$4))))' -language sql immutable; -create ordered aggregate aggfstr(integer,integer,text) ( - stype = text[], - sfunc = aggf_trans, - initcond = '{}' -); -create ordered aggregate aggfns(integer,integer,text) ( - stype = text[], - sfunc = aggfns_trans, - initcond = '{}' -); --- Negative test: "ordered aggregate prefunc is not supported" -create ordered aggregate should_error(integer,integer,text) ( - stype = text[], - sfunc = aggfns_trans, - prefunc = array_cat, - initcond = '{}' -); -ERROR: ordered aggregate prefunc is not supported --- Comments on aggregates -COMMENT ON AGGREGATE nosuchagg (*) IS 'should fail'; -ERROR: aggregate nosuchagg(*) does not exist -COMMENT ON AGGREGATE newcnt (*) IS 'an agg(*) comment'; -COMMENT ON AGGREGATE newcnt ("any") IS 'an agg(any) comment'; --- MPP-2863: ensure that aggregate declarations with an initial value == '' --- do not get converted to an initial value == NULL -create function str_concat(t1 text, t2 text) returns text as -$$ - select $1 || $2; -$$ language sql; -CREATE AGGREGATE string_concat (sfunc = str_concat, prefunc=str_concat, basetype = 'text', stype = text,initcond = ''); -create table aggtest2(i int, t text) DISTRIBUTED BY (i); -insert into aggtest2 values(1, 'hello'); -insert into aggtest2 values(2, 'hello'); -select string_concat(t) from aggtest2; - string_concat ---------------- - hellohello -(1 row) - -select string_concat(t) from (select * from aggtest2 limit 2000) tmp; - string_concat ---------------- - hellohello -(1 row) - -drop table aggtest2; -drop aggregate string_concat(text); -drop function str_concat(text, text); --- Test aggregates with prefunc and finalfunc property -CREATE FUNCTION sum(numeric, numeric) RETURNS -numeric - AS 'select $1 + $2' - LANGUAGE SQL - IMMUTABLE - RETURNS NULL ON NULL INPUT; -CREATE FUNCTION pre_sum(numeric, numeric) RETURNS -numeric - AS 'select $1 + $2' - LANGUAGE SQL - IMMUTABLE - RETURNS NULL ON NULL INPUT; -CREATE FUNCTION final_sum(numeric) RETURNS -numeric - AS 'select $1 + $1' - LANGUAGE SQL - IMMUTABLE - RETURNS NULL ON NULL INPUT; -CREATE AGGREGATE agg_sum(numeric) ( - SFUNC = sum, - STYPE = numeric, - INITCOND = 0); -CREATE AGGREGATE agg_prefunc(numeric) ( - SFUNC = sum, - STYPE = numeric, - PREFUNC =pre_sum, - INITCOND = 0); -CREATE AGGREGATE agg_finalfunc(numeric) ( - SFUNC = sum, - STYPE = numeric, - FINALFUNC =final_sum, - INITCOND = 0); -CREATE AGGREGATE agg_pre_final(numeric) ( - SFUNC = sum, - STYPE = numeric, - PREFUNC =pre_sum, - FINALFUNC =final_sum, - INITCOND = 0); -create table aggtest2(a int); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. -insert into aggtest2 select * from generate_series(1,3); -insert into aggtest2 select * from generate_series(1,3); -select agg_sum(a) from aggtest2; - agg_sum ---------- - 12 -(1 row) - -select agg_prefunc(a) from aggtest2; - agg_prefunc -------------- - 12 -(1 row) - -select agg_finalfunc(a) from aggtest2; - agg_finalfunc ---------------- - 24 -(1 row) - -select agg_pre_final(a) from aggtest2; - agg_pre_final ---------------- - 24 -(1 row) - --- Test initcond functionality -CREATE FUNCTION simple_func(numeric, numeric) RETURNS -numeric - AS 'select $1 + $2' - LANGUAGE SQL - IMMUTABLE - RETURNS NULL ON NULL INPUT; -create aggregate simple_agg(numeric)(sfunc = simple_func, stype = numeric, initcond = 10); -create table foo as select i from generate_series(1, 20)i; -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'i' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. -select simple_agg(i) from foo; - simple_agg ------------- - 220 -(1 row) - -drop aggregate simple_agg(numeric); -create aggregate simple_agg(numeric)(sfunc = simple_func, stype = numeric, initcond = -10); -select simple_agg(i) from foo; - simple_agg ------------- - 200 -(1 row) - -drop table foo; -drop aggregate simple_agg(numeric); --- Test multiple aggregates in the same query -create aggregate simple_agg(numeric)(sfunc = simple_func, stype = numeric, initcond = 0); -create table foo (a int, b int); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. -insert into foo values (1,2); -insert into foo values (3,4); -insert into foo values (5,6); -select simple_agg(a), simple_agg(b) from foo; - simple_agg | simple_agg -------------+------------ - 9 | 12 -(1 row) - -drop table foo; -drop function simple_func(numeric, numeric) cascade; -NOTICE: drop cascades to function simple_agg(numeric) --- Test ordered aggregate -create table sch_quantity ( prod_key integer, qty integer, price integer, product character(3)); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'prod_key' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. -insert into sch_quantity values (1,100, 50, 'p1'); -insert into sch_quantity values (2,200, 100, 'p2'); -insert into sch_quantity values (3,300, 200, 'p3'); -insert into sch_quantity values (4,400, 35, 'p4'); -insert into sch_quantity values (5,500, 40, 'p5'); -insert into sch_quantity values (1,150, 50, 'p1'); -insert into sch_quantity values (2,50, 100, 'p2'); -insert into sch_quantity values (3,150, 200, 'p3'); -insert into sch_quantity values (4,200, 35, 'p4'); -insert into sch_quantity values (5,300, 40, 'p5'); -CREATE ORDERED AGGREGATE sch_array_accum_final (anyelement) -( - sfunc = array_append, - stype = anyarray, - finalfunc = array_out, - initcond = '{}' -); -select prod_key, sch_array_accum_final(qty order by prod_key,qty) from sch_quantity group by prod_key having prod_key < 5 order by prod_key; - prod_key | sch_array_accum_final -----------+----------------------- - 1 | {100,150} - 2 | {50,200} - 3 | {150,300} - 4 | {200,400} -(4 rows) - -drop table sch_quantity; -drop aggregate sch_array_accum_final(anyelement); --- Test privileges with aggregates -create role aggregate_user1 with login nosuperuser nocreatedb; -NOTICE: resource queue required -- using default resource queue "pg_default" -create role aggregate_user2 with login nosuperuser nocreatedb; -NOTICE: resource queue required -- using default resource queue "pg_default" -create role aggregate_user3 with login nosuperuser nocreatedb; -NOTICE: resource queue required -- using default resource queue "pg_default" -set session authorization aggregate_user1; -CREATE AGGREGATE user1_aggregate_avg ( - sfunc = int4_avg_accum, basetype = int4, stype = bytea, - finalfunc = int8_avg, - initcond1 = '{0}' -); -create table user1_aggregate_table(i int, j int); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. -insert into user1_aggregate_table select i , i %3 from generate_series(1, 20) i; -select user1_aggregate_avg(i) from user1_aggregate_table; - user1_aggregate_avg ---------------------- - 10.5 -(1 row) - -GRANT ALL on table user1_aggregate_table to aggregate_user2; -GRANT ALL on function user1_aggregate_avg(integer) to aggregate_user2; --- only grant permissions on the table to user3 -GRANT ALL on table user1_aggregate_table to aggregate_user3; -reset session authorization; -set session authorization aggregate_user2; --- should work fine since user2 has permissions on aggregate and table -select user1_aggregate_avg(i) from user1_aggregate_table; - user1_aggregate_avg ---------------------- - 10.5 -(1 row) - ---drops should fail -drop table user1_aggregate_table; -ERROR: must be owner of relation user1_aggregate_table -drop aggregate user1_aggregate_avg(integer); -ERROR: must be owner of function user1_aggregate_avg -reset session authorization; -set session authorization aggregate_user3; --- user3 is able to execute the average even if no permissions ? -SELECT user1_aggregate_avg(i) from user1_aggregate_table; - user1_aggregate_avg ---------------------- - 10.5 -(1 row) - --- drop should fail -drop aggregate user1_aggregate_avg(integer); -ERROR: must be owner of function user1_aggregate_avg -reset session authorization; -set session authorization aggregate_user1; --- drops should work fine -drop table user1_aggregate_table; -drop aggregate user1_aggregate_avg(integer); -reset session authorization; -set session authorization aggregate_user1; -CREATE AGGREGATE user1_aggregate_avg ( - sfunc = int4_avg_accum, basetype = int4, stype = bytea, - finalfunc = int8_avg, - initcond1 = '{0}' -); -reset session authorization; -ALTER AGGREGATE user1_aggregate_avg(integer) OWNER to aggregate_user2; -set session authorization aggregate_user2; -ALTER AGGREGATE user1_aggregate_avg(integer) RENAME to aggregate_rename; -select proname from pg_proc where proname = 'aggregate_rename'; - proname ------------------- - aggregate_rename -(1 row) - -create table user1_aggregate_table(i int, j int); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. -insert into user1_aggregate_table select i , i %3 from generate_series(1, 20) i; -SELECT aggregate_rename(i) from user1_aggregate_table; - aggregate_rename ------------------- - 10.5 -(1 row) - -reset session authorization; -create schema aggschema; -alter aggregate aggregate_rename(integer) set schema aggschema; -SELECT aggschema.aggregate_rename(i) from user1_aggregate_table; - aggregate_rename ------------------- - 10.5 -(1 row) - -drop aggregate aggschema.aggregate_rename(integer); -drop table user1_aggregate_table; -drop role aggregate_user1; -drop role aggregate_user2; -drop role aggregate_user3; http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/96a6656e/src/test/regress/goh_schedule ---------------------------------------------------------------------- diff --git a/src/test/regress/goh_schedule b/src/test/regress/goh_schedule index bb6cb29..fa8ba59 100755 --- a/src/test/regress/goh_schedule +++ b/src/test/regress/goh_schedule @@ -4,7 +4,6 @@ test: create_function_1 test: create_type test: create_table test: copy -test: create_aggregate test: vacuum test: subselect test: arrays http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/96a6656e/src/test/regress/known_good_schedule ---------------------------------------------------------------------- diff --git a/src/test/regress/known_good_schedule b/src/test/regress/known_good_schedule index 6c18613..da228fd 100755 --- a/src/test/regress/known_good_schedule +++ b/src/test/regress/known_good_schedule @@ -49,7 +49,6 @@ ignore: copyselect ignore: constraints ignore: triggers ignore: create_misc -test: create_aggregate ignore: create_operator ignore: create_index ignore: drop_if_exists http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/96a6656e/src/test/regress/parallel_schedule ---------------------------------------------------------------------- diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index a610932..fc61a36 100755 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -18,7 +18,7 @@ test: create_function_2 test: copy copyselect -test: constraints create_misc create_aggregate create_operator drop_if_exists vacuum +test: constraints create_misc create_operator drop_if_exists vacuum ignore: triggers ignore: inherit http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/96a6656e/src/test/regress/serial_schedule ---------------------------------------------------------------------- diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index f790608..2174adc 100755 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -48,7 +48,6 @@ test: copyselect test: constraints ignore: triggers test: create_misc -test: create_aggregate test: create_operator test: create_index test: drop_if_exists http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/96a6656e/src/test/regress/sql/create_aggregate.sql ---------------------------------------------------------------------- diff --git a/src/test/regress/sql/create_aggregate.sql b/src/test/regress/sql/create_aggregate.sql deleted file mode 100644 index ddf38bb..0000000 --- a/src/test/regress/sql/create_aggregate.sql +++ /dev/null @@ -1,304 +0,0 @@ --- --- CREATE_AGGREGATE --- - --- all functions CREATEd -CREATE AGGREGATE newavg ( - sfunc = int4_avg_accum, basetype = int4, stype = bytea, - finalfunc = int8_avg, - initcond1 = '{0}' -); - --- test comments -COMMENT ON AGGREGATE newavg_wrong (int4) IS 'an agg comment'; -COMMENT ON AGGREGATE newavg (int4) IS 'an agg comment'; -COMMENT ON AGGREGATE newavg (int4) IS NULL; - --- without finalfunc; test obsolete spellings 'sfunc1' etc -CREATE AGGREGATE newsum ( - sfunc1 = int4pl, basetype = int4, stype1 = int4, - initcond1 = '0' -); - --- zero-argument aggregate -CREATE AGGREGATE newcnt (*) ( - sfunc = int8inc, stype = int8, - initcond = '0' -); - --- old-style spelling of same -CREATE AGGREGATE oldcnt ( - sfunc = int8inc, basetype = 'ANY', stype = int8, - initcond = '0' -); - --- aggregate that only cares about null/nonnull input -CREATE AGGREGATE newcnt ("any") ( - sfunc = int8inc_any, stype = int8, - initcond = '0' -); - --- multi-argument aggregate -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' -); - - --- multi-argument aggregates sensitive to distinct/order, strict/nonstrict - -/* --- MPP: In Postgres this creates an array type with it, in GP it does not. -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[], - initcond = '{}' -); -*/ -create function aggf_trans(text[],integer,integer,text) returns text[] -as 'select array_append($1, textin(record_out(ROW($2,$3,$4))))' -language sql strict immutable; - -create function aggfns_trans(text[],integer,integer,text) returns text[] -as 'select array_append($1, textin(record_out(ROW($2,$3,$4))))' -language sql immutable; - -create ordered aggregate aggfstr(integer,integer,text) ( - stype = text[], - sfunc = aggf_trans, - initcond = '{}' -); - -create ordered aggregate aggfns(integer,integer,text) ( - stype = text[], - sfunc = aggfns_trans, - initcond = '{}' -); - --- Negative test: "ordered aggregate prefunc is not supported" -create ordered aggregate should_error(integer,integer,text) ( - stype = text[], - sfunc = aggfns_trans, - prefunc = array_cat, - initcond = '{}' -); - - - --- Comments on aggregates -COMMENT ON AGGREGATE nosuchagg (*) IS 'should fail'; -COMMENT ON AGGREGATE newcnt (*) IS 'an agg(*) comment'; -COMMENT ON AGGREGATE newcnt ("any") IS 'an agg(any) comment'; - --- MPP-2863: ensure that aggregate declarations with an initial value == '' --- do not get converted to an initial value == NULL -create function str_concat(t1 text, t2 text) returns text as -$$ - select $1 || $2; -$$ language sql; - -CREATE AGGREGATE string_concat (sfunc = str_concat, prefunc=str_concat, basetype = 'text', stype = text,initcond = ''); - -create table aggtest2(i int, t text) DISTRIBUTED BY (i); -insert into aggtest2 values(1, 'hello'); -insert into aggtest2 values(2, 'hello'); -select string_concat(t) from aggtest2; -select string_concat(t) from (select * from aggtest2 limit 2000) tmp; -drop table aggtest2; -drop aggregate string_concat(text); -drop function str_concat(text, text); - --- Test aggregates with prefunc and finalfunc property -CREATE FUNCTION sum(numeric, numeric) RETURNS -numeric - AS 'select $1 + $2' - LANGUAGE SQL - IMMUTABLE - RETURNS NULL ON NULL INPUT; - -CREATE FUNCTION pre_sum(numeric, numeric) RETURNS -numeric - AS 'select $1 + $2' - LANGUAGE SQL - IMMUTABLE - RETURNS NULL ON NULL INPUT; - -CREATE FUNCTION final_sum(numeric) RETURNS -numeric - AS 'select $1 + $1' - LANGUAGE SQL - IMMUTABLE - RETURNS NULL ON NULL INPUT; - -CREATE AGGREGATE agg_sum(numeric) ( - SFUNC = sum, - STYPE = numeric, - INITCOND = 0); - -CREATE AGGREGATE agg_prefunc(numeric) ( - SFUNC = sum, - STYPE = numeric, - PREFUNC =pre_sum, - INITCOND = 0); - -CREATE AGGREGATE agg_finalfunc(numeric) ( - SFUNC = sum, - STYPE = numeric, - FINALFUNC =final_sum, - INITCOND = 0); - -CREATE AGGREGATE agg_pre_final(numeric) ( - SFUNC = sum, - STYPE = numeric, - PREFUNC =pre_sum, - FINALFUNC =final_sum, - INITCOND = 0); - -create table aggtest2(a int); -insert into aggtest2 select * from generate_series(1,3); -insert into aggtest2 select * from generate_series(1,3); - -select agg_sum(a) from aggtest2; -select agg_prefunc(a) from aggtest2; -select agg_finalfunc(a) from aggtest2; -select agg_pre_final(a) from aggtest2; - --- Test initcond functionality -CREATE FUNCTION simple_func(numeric, numeric) RETURNS -numeric - AS 'select $1 + $2' - LANGUAGE SQL - IMMUTABLE - RETURNS NULL ON NULL INPUT; - -create aggregate simple_agg(numeric)(sfunc = simple_func, stype = numeric, initcond = 10); -create table foo as select i from generate_series(1, 20)i; -select simple_agg(i) from foo; -drop aggregate simple_agg(numeric); -create aggregate simple_agg(numeric)(sfunc = simple_func, stype = numeric, initcond = -10); -select simple_agg(i) from foo; -drop table foo; -drop aggregate simple_agg(numeric); - --- Test multiple aggregates in the same query -create aggregate simple_agg(numeric)(sfunc = simple_func, stype = numeric, initcond = 0); -create table foo (a int, b int); -insert into foo values (1,2); -insert into foo values (3,4); -insert into foo values (5,6); -select simple_agg(a), simple_agg(b) from foo; -drop table foo; -drop function simple_func(numeric, numeric) cascade; - --- Test ordered aggregate -create table sch_quantity ( prod_key integer, qty integer, price integer, product character(3)); -insert into sch_quantity values (1,100, 50, 'p1'); -insert into sch_quantity values (2,200, 100, 'p2'); -insert into sch_quantity values (3,300, 200, 'p3'); -insert into sch_quantity values (4,400, 35, 'p4'); -insert into sch_quantity values (5,500, 40, 'p5'); -insert into sch_quantity values (1,150, 50, 'p1'); -insert into sch_quantity values (2,50, 100, 'p2'); -insert into sch_quantity values (3,150, 200, 'p3'); -insert into sch_quantity values (4,200, 35, 'p4'); -insert into sch_quantity values (5,300, 40, 'p5'); -CREATE ORDERED AGGREGATE sch_array_accum_final (anyelement) -( - sfunc = array_append, - stype = anyarray, - finalfunc = array_out, - initcond = '{}' -); -select prod_key, sch_array_accum_final(qty order by prod_key,qty) from sch_quantity group by prod_key having prod_key < 5 order by prod_key; -drop table sch_quantity; -drop aggregate sch_array_accum_final(anyelement); - - --- Test privileges with aggregates -create role aggregate_user1 with login nosuperuser nocreatedb; -create role aggregate_user2 with login nosuperuser nocreatedb; -create role aggregate_user3 with login nosuperuser nocreatedb; - -set session authorization aggregate_user1; - -CREATE AGGREGATE user1_aggregate_avg ( - sfunc = int4_avg_accum, basetype = int4, stype = bytea, - finalfunc = int8_avg, - initcond1 = '{0}' -); -create table user1_aggregate_table(i int, j int); -insert into user1_aggregate_table select i , i %3 from generate_series(1, 20) i; -select user1_aggregate_avg(i) from user1_aggregate_table; - -GRANT ALL on table user1_aggregate_table to aggregate_user2; -GRANT ALL on function user1_aggregate_avg(integer) to aggregate_user2; - --- only grant permissions on the table to user3 -GRANT ALL on table user1_aggregate_table to aggregate_user3; -reset session authorization; - - -set session authorization aggregate_user2; --- should work fine since user2 has permissions on aggregate and table -select user1_aggregate_avg(i) from user1_aggregate_table; ---drops should fail -drop table user1_aggregate_table; -drop aggregate user1_aggregate_avg(integer); -reset session authorization; - - -set session authorization aggregate_user3; --- user3 is able to execute the average even if no permissions ? -SELECT user1_aggregate_avg(i) from user1_aggregate_table; --- drop should fail -drop aggregate user1_aggregate_avg(integer); -reset session authorization; - -set session authorization aggregate_user1; --- drops should work fine -drop table user1_aggregate_table; -drop aggregate user1_aggregate_avg(integer); -reset session authorization; - -set session authorization aggregate_user1; -CREATE AGGREGATE user1_aggregate_avg ( - sfunc = int4_avg_accum, basetype = int4, stype = bytea, - finalfunc = int8_avg, - initcond1 = '{0}' -); -reset session authorization; -ALTER AGGREGATE user1_aggregate_avg(integer) OWNER to aggregate_user2; - -set session authorization aggregate_user2; -ALTER AGGREGATE user1_aggregate_avg(integer) RENAME to aggregate_rename; -select proname from pg_proc where proname = 'aggregate_rename'; -create table user1_aggregate_table(i int, j int); -insert into user1_aggregate_table select i , i %3 from generate_series(1, 20) i; -SELECT aggregate_rename(i) from user1_aggregate_table; -reset session authorization; - -create schema aggschema; -alter aggregate aggregate_rename(integer) set schema aggschema; -SELECT aggschema.aggregate_rename(i) from user1_aggregate_table; -drop aggregate aggschema.aggregate_rename(integer); -drop table user1_aggregate_table; -drop role aggregate_user1; -drop role aggregate_user2; -drop role aggregate_user3;
