HAWQ-987. Add feature test for agg derived win.
Project: http://git-wip-us.apache.org/repos/asf/incubator-hawq/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-hawq/commit/e0a072b0 Tree: http://git-wip-us.apache.org/repos/asf/incubator-hawq/tree/e0a072b0 Diff: http://git-wip-us.apache.org/repos/asf/incubator-hawq/diff/e0a072b0 Branch: refs/heads/master Commit: e0a072b0c7cf95f8d3d151c96ada53181485a7c0 Parents: d73117e Author: ztao1987 <[email protected]> Authored: Tue Aug 9 07:02:01 2016 +0800 Committer: ztao1987 <[email protected]> Committed: Tue Aug 9 10:19:24 2016 +0800 ---------------------------------------------------------------------- src/test/feature/query/ans/agg-derived-win.ans | 342 ++++++++++++++++++++ src/test/feature/query/sql/agg-derived-win.sql | 161 +++++++++ src/test/feature/query/test_aggregate.cpp | 6 +- src/test/regress/expected/agg_derived_win.out | 322 ------------------ src/test/regress/known_good_schedule | 1 - src/test/regress/sql/agg_derived_win.sql | 161 --------- 6 files changed, 508 insertions(+), 485 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/e0a072b0/src/test/feature/query/ans/agg-derived-win.ans ---------------------------------------------------------------------- diff --git a/src/test/feature/query/ans/agg-derived-win.ans b/src/test/feature/query/ans/agg-derived-win.ans new file mode 100644 index 0000000..271250b --- /dev/null +++ b/src/test/feature/query/ans/agg-derived-win.ans @@ -0,0 +1,342 @@ +-- start_ignore +SET SEARCH_PATH=TestAggregate_TestAggregateDerivedWin; +SET +-- end_ignore +-- Objective: test aggregate derived window functions in HAWQ. +-- Aggregate derived window functions are nothing but user defined +-- aggregates when used with "OVER()" clause. Refer to GPSQL-1418. +-- Begin EMA (copied from attachment to MPP-14845) +-- +-- Definition of ema -- exponential moving average +-- +-- Given a sequence of numbers +-- V = (V_0, V_1, ... , V_n-1) +-- and a real number smoothing factor +-- X such that 0 < X <= 1 +-- then +-- ema(V) = E = (E_0, E_1, ... , E_n-1) +-- and +-- E_0 = V_0 +-- +-- E_i = E_i-1 * (1-X) + V_i * X +-- = E_i-1 + (V_i - E_i-1) * X +-- +-- Here the sequence V is represented by table ema_test ordered by k. +drop type if exists ema_type cascade; +psql:/tmp/TestAggregate_TestAggregateDerivedWin.sql:26: NOTICE: type "ema_type" does not exist, skipping +DROP TYPE +drop table if exists ema_test cascade; +psql:/tmp/TestAggregate_TestAggregateDerivedWin.sql:27: NOTICE: table "ema_test" does not exist, skipping +DROP TABLE +create type ema_type as (x float, e float); +CREATE TYPE +create function ema_adv(t ema_type, v float, x float) + returns ema_type + as $$ + begin + if t.e is null then + t.e = v; + t.x = x; + else + if t.x != x then + raise exception 'ema smoothing x may not vary'; + end if; + t.e = t.e + (v - t.e) * t.x; + end if; + return t; + end; + $$ language plpgsql; +CREATE FUNCTION +create function ema_fin(t ema_type) + returns float + as $$ + begin + return t.e; + end; + $$ language plpgsql; +CREATE FUNCTION +-- Work around for MPP-14845: define a placebo prefunc. This should +-- never be called. +create function ema_pre(s1 ema_type, s2 ema_type) + returns ema_type + as $$ + select '(,)'::ema_type; + $$ language sql; +CREATE FUNCTION +create aggregate ema(float, float) ( + sfunc = ema_adv, + stype = ema_type, + finalfunc = ema_fin, + prefunc = ema_pre, + initcond = '(,)' + ); +CREATE AGGREGATE +create table ema_test + ( k int, v float ) + distributed by (k); +CREATE TABLE +insert into ema_test + select i, 4*(22/7::float) + 10.0*(1+cos(radians(i*5))) + from generate_series(0,19) i(i); +INSERT 0 20 +select + k, v, + ema(v, 0.9) over (order by k rows between unbounded preceding and current row) +from ema_test +order by k; + k | v | ema +----+------------------+------------------ + 0 | 32.5714285714286 | 32.5714285714286 + 1 | 32.533375552346 | 32.5371808542543 + 2 | 32.4195061015507 | 32.431273576821 + 3 | 32.2306868343193 | 32.2507455085694 + 4 | 31.9683547792877 | 31.9965938522158 + 5 | 31.6345064417951 | 31.6707151828371 + 6 | 31.231682609273 | 31.2755858666294 + 7 | 30.7629490143185 | 30.8142126995496 + 8 | 30.2318730026184 | 30.2901069723115 + 9 | 29.642496383294 | 29.7072574421958 + 10 | 28.999304668294 | 29.0700999456841 + 11 | 28.307192934939 | 28.3834836360135 + 12 | 27.5714285714286 | 27.6526340778871 + 13 | 26.7976111888356 | 26.8831134777407 + 14 | 25.9916300046853 | 26.0807783519908 + 15 | 25.1596190224538 | 25.2517349554075 + 16 | 24.3079103480979 | 24.4022928088288 + 17 | 23.4429859989052 | 23.5389166798975 + 18 | 22.5714285714286 | 22.6681773822755 + 19 | 21.699871143952 | 21.7967017677843 +(20 rows) + +select + k, v, + ema(v, 0.9) over (order by k) +from ema_test +order by k; + k | v | ema +----+------------------+------------------ + 0 | 32.5714285714286 | 32.5714285714286 + 1 | 32.533375552346 | 32.5371808542543 + 2 | 32.4195061015507 | 32.431273576821 + 3 | 32.2306868343193 | 32.2507455085694 + 4 | 31.9683547792877 | 31.9965938522158 + 5 | 31.6345064417951 | 31.6707151828371 + 6 | 31.231682609273 | 31.2755858666294 + 7 | 30.7629490143185 | 30.8142126995496 + 8 | 30.2318730026184 | 30.2901069723115 + 9 | 29.642496383294 | 29.7072574421958 + 10 | 28.999304668294 | 29.0700999456841 + 11 | 28.307192934939 | 28.3834836360135 + 12 | 27.5714285714286 | 27.6526340778871 + 13 | 26.7976111888356 | 26.8831134777407 + 14 | 25.9916300046853 | 26.0807783519908 + 15 | 25.1596190224538 | 25.2517349554075 + 16 | 24.3079103480979 | 24.4022928088288 + 17 | 23.4429859989052 | 23.5389166798975 + 18 | 22.5714285714286 | 22.6681773822755 + 19 | 21.699871143952 | 21.7967017677843 +(20 rows) + +-- End EMA (MPP-14845) +-- +-- Aggregate derived equivalent of "lag()" window function. +-- +create function mylag_transfn(st int[], val int, lag int) + returns int[] + as $$ + declare + local_st int[] := st; + local_lag int := lag; + begin + if local_st is null then + local_st := '{}'::int[]; + while local_lag >= 0 + loop + select array_append(local_st, null::int) into local_st; + local_lag := local_lag - 1; + end loop; + end if; + return array_append(local_st[2:lag+1], val); + end; + $$ language plpgsql; +CREATE FUNCTION +create function mylag_finalfn(st int[]) + returns int + as $$ + begin + return st[1]; + end; + $$ language plpgsql; +CREATE FUNCTION +create function mylag_prefn(st1 int[], st2 int[]) + returns int[] + as $$ + select '{}'::int[]; + $$ language sql; +CREATE FUNCTION +create aggregate mylag(int, int) ( + sfunc = mylag_transfn, + stype = int[], + finalfunc = mylag_finalfn, + prefunc = mylag_prefn, + initcond = '{null,null}' + ); +CREATE AGGREGATE +-- This will be executed only on master, not on segments. +select i, mylag(i, 2) over (order by i) from generate_series(1,10)i; + i | mylag +----+------- + 1 | + 2 | 1 + 3 | 2 + 4 | 3 + 5 | 4 + 6 | 5 + 7 | 6 + 8 | 7 + 9 | 8 + 10 | 9 +(10 rows) + +create table t1 (a int, b int) distributed by (a); +CREATE TABLE +insert into t1 select i%3, 22*i/7 from generate_series(0,10)i; +INSERT 0 11 +select a,b,mylag(b,1) over (order by b) from t1; + a | b | mylag +---+----+------- + 0 | 0 | + 1 | 3 | 0 + 2 | 6 | 3 + 0 | 9 | 6 + 1 | 12 | 9 + 2 | 15 | 12 + 0 | 18 | 15 + 1 | 22 | 18 + 2 | 25 | 22 + 0 | 28 | 25 + 1 | 31 | 28 +(11 rows) + +-- +-- Misc tests - cover different ways of defining a window. +-- +CREATE AGGREGATE mysum (int) ( + STYPE = bigint, + SFUNC = int4_sum, + prefunc = int8pl +); +CREATE AGGREGATE +SELECT a,b,mysum(b) over (order by b) FROM t1; + a | b | mysum +---+----+------- + 0 | 0 | 0 + 1 | 3 | 3 + 2 | 6 | 9 + 0 | 9 | 18 + 1 | 12 | 30 + 2 | 15 | 45 + 0 | 18 | 63 + 1 | 22 | 85 + 2 | 25 | 110 + 0 | 28 | 138 + 1 | 31 | 169 +(11 rows) + +SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as (); -- mvd 1,2->3 + a | b | mysum +---+----+------- + 0 | 0 | 169 + 2 | 6 | 169 + 0 | 9 | 169 + 2 | 15 | 169 + 0 | 18 | 169 + 2 | 25 | 169 + 0 | 28 | 169 + 1 | 3 | 169 + 1 | 12 | 169 + 1 | 22 | 169 + 1 | 31 | 169 +(11 rows) + +SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as (PARTITION BY a); -- mvd 1,2->3 + a | b | mysum +---+----+------- + 0 | 28 | 55 + 0 | 9 | 55 + 0 | 18 | 55 + 0 | 0 | 55 + 2 | 25 | 46 + 2 | 15 | 46 + 2 | 6 | 46 + 1 | 3 | 68 + 1 | 12 | 68 + 1 | 22 | 68 + 1 | 31 | 68 +(11 rows) + +SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as (ORDER BY b); -- mvd 1,2->3 + a | b | mysum +---+----+------- + 0 | 0 | 0 + 1 | 3 | 3 + 2 | 6 | 9 + 0 | 9 | 18 + 1 | 12 | 30 + 2 | 15 | 45 + 0 | 18 | 63 + 1 | 22 | 85 + 2 | 25 | 110 + 0 | 28 | 138 + 1 | 31 | 169 +(11 rows) + +SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as (PARTITION BY a ORDER BY b); -- mvd 1,2->3 + a | b | mysum +---+----+------- + 1 | 3 | 3 + 1 | 12 | 15 + 1 | 22 | 37 + 1 | 31 | 68 + 0 | 0 | 0 + 0 | 9 | 9 + 0 | 18 | 27 + 0 | 28 | 55 + 2 | 6 | 6 + 2 | 15 | 21 + 2 | 25 | 46 +(11 rows) + +SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as + (PARTITION BY a ORDER BY b ROWS BETWEEN 1 preceding and current row) order by a,b; -- mvd 1,2->3 + a | b | mysum +---+----+------- + 0 | 0 | 0 + 0 | 9 | 9 + 0 | 18 | 27 + 0 | 28 | 46 + 1 | 3 | 3 + 1 | 12 | 15 + 1 | 22 | 34 + 1 | 31 | 53 + 2 | 6 | 6 + 2 | 15 | 21 + 2 | 25 | 40 +(11 rows) + +select a,b,mylag(b, 1) over (partition by a order by b) from t1 order by a,b; -- mvd 1,2->3 + a | b | mylag +---+----+------- + 0 | 0 | + 0 | 9 | 0 + 0 | 18 | 9 + 0 | 28 | 18 + 1 | 3 | + 1 | 12 | 3 + 1 | 22 | 12 + 1 | 31 | 22 + 2 | 6 | + 2 | 15 | 6 + 2 | 25 | 15 +(11 rows) + http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/e0a072b0/src/test/feature/query/sql/agg-derived-win.sql ---------------------------------------------------------------------- diff --git a/src/test/feature/query/sql/agg-derived-win.sql b/src/test/feature/query/sql/agg-derived-win.sql new file mode 100644 index 0000000..68f61cf --- /dev/null +++ b/src/test/feature/query/sql/agg-derived-win.sql @@ -0,0 +1,161 @@ +-- Objective: test aggregate derived window functions in HAWQ. +-- Aggregate derived window functions are nothing but user defined +-- aggregates when used with "OVER()" clause. Refer to GPSQL-1418. + +-- Begin EMA (copied from attachment to MPP-14845) +-- +-- Definition of ema -- exponential moving average +-- +-- Given a sequence of numbers +-- V = (V_0, V_1, ... , V_n-1) +-- and a real number smoothing factor +-- X such that 0 < X <= 1 +-- then +-- ema(V) = E = (E_0, E_1, ... , E_n-1) +-- and +-- E_0 = V_0 +-- +-- E_i = E_i-1 * (1-X) + V_i * X +-- = E_i-1 + (V_i - E_i-1) * X +-- +-- Here the sequence V is represented by table ema_test ordered by k. + +drop type if exists ema_type cascade; +drop table if exists ema_test cascade; + +create type ema_type as (x float, e float); + +create function ema_adv(t ema_type, v float, x float) + returns ema_type + as $$ + begin + if t.e is null then + t.e = v; + t.x = x; + else + if t.x != x then + raise exception 'ema smoothing x may not vary'; + end if; + t.e = t.e + (v - t.e) * t.x; + end if; + return t; + end; + $$ language plpgsql; + +create function ema_fin(t ema_type) + returns float + as $$ + begin + return t.e; + end; + $$ language plpgsql; + +-- Work around for MPP-14845: define a placebo prefunc. This should +-- never be called. +create function ema_pre(s1 ema_type, s2 ema_type) + returns ema_type + as $$ + select '(,)'::ema_type; + $$ language sql; + +create aggregate ema(float, float) ( + sfunc = ema_adv, + stype = ema_type, + finalfunc = ema_fin, + prefunc = ema_pre, + initcond = '(,)' + ); + +create table ema_test + ( k int, v float ) + distributed by (k); + +insert into ema_test + select i, 4*(22/7::float) + 10.0*(1+cos(radians(i*5))) + from generate_series(0,19) i(i); + +select + k, v, + ema(v, 0.9) over (order by k rows between unbounded preceding and current row) +from ema_test +order by k; + +select + k, v, + ema(v, 0.9) over (order by k) +from ema_test +order by k; + +-- End EMA (MPP-14845) + +-- +-- Aggregate derived equivalent of "lag()" window function. +-- +create function mylag_transfn(st int[], val int, lag int) + returns int[] + as $$ + declare + local_st int[] := st; + local_lag int := lag; + begin + if local_st is null then + local_st := '{}'::int[]; + while local_lag >= 0 + loop + select array_append(local_st, null::int) into local_st; + local_lag := local_lag - 1; + end loop; + end if; + return array_append(local_st[2:lag+1], val); + end; + $$ language plpgsql; + +create function mylag_finalfn(st int[]) + returns int + as $$ + begin + return st[1]; + end; + $$ language plpgsql; + +create function mylag_prefn(st1 int[], st2 int[]) + returns int[] + as $$ + select '{}'::int[]; + $$ language sql; + +create aggregate mylag(int, int) ( + sfunc = mylag_transfn, + stype = int[], + finalfunc = mylag_finalfn, + prefunc = mylag_prefn, + initcond = '{null,null}' + ); + +-- This will be executed only on master, not on segments. +select i, mylag(i, 2) over (order by i) from generate_series(1,10)i; + +create table t1 (a int, b int) distributed by (a); + +insert into t1 select i%3, 22*i/7 from generate_series(0,10)i; + +select a,b,mylag(b,1) over (order by b) from t1; + +-- +-- Misc tests - cover different ways of defining a window. +-- +CREATE AGGREGATE mysum (int) ( + STYPE = bigint, + SFUNC = int4_sum, + prefunc = int8pl +); +SELECT a,b,mysum(b) over (order by b) FROM t1; +SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as (); -- mvd 1,2->3 +SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as (PARTITION BY a); -- mvd 1,2->3 +SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as (ORDER BY b); -- mvd 1,2->3 +SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as (PARTITION BY a ORDER BY b); -- mvd 1,2->3 +SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as + (PARTITION BY a ORDER BY b ROWS BETWEEN 1 preceding and current row) order by a,b; -- mvd 1,2->3 + +select a,b,mylag(b, 1) over (partition by a order by b) from t1 order by a,b; -- mvd 1,2->3 + http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/e0a072b0/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 fedb8c2..589ff4f 100644 --- a/src/test/feature/query/test_aggregate.cpp +++ b/src/test/feature/query/test_aggregate.cpp @@ -72,4 +72,8 @@ TEST_F(TestAggregate, TestAggregateWithNull) { "1||aa|\n0||WET|\n0|51||\n"); } - +TEST_F(TestAggregate, TestAggregateDerivedWin) { + hawq::test::SQLUtility util; + util.execSQLFile("query/sql/agg-derived-win.sql", + "query/ans/agg-derived-win.ans"); +} http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/e0a072b0/src/test/regress/expected/agg_derived_win.out ---------------------------------------------------------------------- diff --git a/src/test/regress/expected/agg_derived_win.out b/src/test/regress/expected/agg_derived_win.out deleted file mode 100644 index ab950e6..0000000 --- a/src/test/regress/expected/agg_derived_win.out +++ /dev/null @@ -1,322 +0,0 @@ --- Objective: test aggregate derived window functions in HAWQ. --- Aggregate derived window functions are nothing but user defined --- aggregates when used with "OVER()" clause. Refer to GPSQL-1418. --- Begin EMA (copied from attachment to MPP-14845) --- --- Definition of ema -- exponential moving average --- --- Given a sequence of numbers --- V = (V_0, V_1, ... , V_n-1) --- and a real number smoothing factor --- X such that 0 < X <= 1 --- then --- ema(V) = E = (E_0, E_1, ... , E_n-1) --- and --- E_0 = V_0 --- --- E_i = E_i-1 * (1-X) + V_i * X --- = E_i-1 + (V_i - E_i-1) * X --- --- Here the sequence V is represented by table ema_test ordered by k. -drop type if exists ema_type cascade; -NOTICE: type "ema_type" does not exist, skipping -drop table if exists ema_test cascade; -NOTICE: table "ema_test" does not exist, skipping -create type ema_type as (x float, e float); -create function ema_adv(t ema_type, v float, x float) - returns ema_type - as $$ - begin - if t.e is null then - t.e = v; - t.x = x; - else - if t.x != x then - raise exception 'ema smoothing x may not vary'; - end if; - t.e = t.e + (v - t.e) * t.x; - end if; - return t; - end; - $$ language plpgsql; -create function ema_fin(t ema_type) - returns float - as $$ - begin - return t.e; - end; - $$ language plpgsql; --- Work around for MPP-14845: define a placebo prefunc. This should --- never be called. -create function ema_pre(s1 ema_type, s2 ema_type) - returns ema_type - as $$ - select '(,)'::ema_type; - $$ language sql; -create aggregate ema(float, float) ( - sfunc = ema_adv, - stype = ema_type, - finalfunc = ema_fin, - prefunc = ema_pre, - initcond = '(,)' - ); -create table ema_test - ( k int, v float ) - distributed by (k); -insert into ema_test - select i, 4*(22/7::float) + 10.0*(1+cos(radians(i*5))) - from generate_series(0,19) i(i); -select - k, v, - ema(v, 0.9) over (order by k rows between unbounded preceding and current row) -from ema_test -order by k; - k | v | ema -----+------------------+------------------ - 0 | 32.5714285714286 | 32.5714285714286 - 1 | 32.533375552346 | 32.5371808542543 - 2 | 32.4195061015507 | 32.431273576821 - 3 | 32.2306868343193 | 32.2507455085694 - 4 | 31.9683547792877 | 31.9965938522158 - 5 | 31.6345064417951 | 31.6707151828371 - 6 | 31.231682609273 | 31.2755858666294 - 7 | 30.7629490143185 | 30.8142126995496 - 8 | 30.2318730026184 | 30.2901069723115 - 9 | 29.642496383294 | 29.7072574421958 - 10 | 28.999304668294 | 29.0700999456841 - 11 | 28.307192934939 | 28.3834836360135 - 12 | 27.5714285714286 | 27.6526340778871 - 13 | 26.7976111888356 | 26.8831134777407 - 14 | 25.9916300046853 | 26.0807783519908 - 15 | 25.1596190224538 | 25.2517349554075 - 16 | 24.3079103480979 | 24.4022928088288 - 17 | 23.4429859989052 | 23.5389166798975 - 18 | 22.5714285714286 | 22.6681773822755 - 19 | 21.699871143952 | 21.7967017677843 -(20 rows) - -select - k, v, - ema(v, 0.9) over (order by k) -from ema_test -order by k; - k | v | ema -----+------------------+------------------ - 0 | 32.5714285714286 | 32.5714285714286 - 1 | 32.533375552346 | 32.5371808542543 - 2 | 32.4195061015507 | 32.431273576821 - 3 | 32.2306868343193 | 32.2507455085694 - 4 | 31.9683547792877 | 31.9965938522158 - 5 | 31.6345064417951 | 31.6707151828371 - 6 | 31.231682609273 | 31.2755858666294 - 7 | 30.7629490143185 | 30.8142126995496 - 8 | 30.2318730026184 | 30.2901069723115 - 9 | 29.642496383294 | 29.7072574421958 - 10 | 28.999304668294 | 29.0700999456841 - 11 | 28.307192934939 | 28.3834836360135 - 12 | 27.5714285714286 | 27.6526340778871 - 13 | 26.7976111888356 | 26.8831134777407 - 14 | 25.9916300046853 | 26.0807783519908 - 15 | 25.1596190224538 | 25.2517349554075 - 16 | 24.3079103480979 | 24.4022928088288 - 17 | 23.4429859989052 | 23.5389166798975 - 18 | 22.5714285714286 | 22.6681773822755 - 19 | 21.699871143952 | 21.7967017677843 -(20 rows) - --- End EMA (MPP-14845) --- --- Aggregate derived equivalent of "lag()" window function. --- -create function mylag_transfn(st int[], val int, lag int) - returns int[] - as $$ - declare - local_st int[] := st; - local_lag int := lag; - begin - if local_st is null then - local_st := '{}'::int[]; - while local_lag >= 0 - loop - select array_append(local_st, null::int) into local_st; - local_lag := local_lag - 1; - end loop; - end if; - return array_append(local_st[2:lag+1], val); - end; - $$ language plpgsql; -create function mylag_finalfn(st int[]) - returns int - as $$ - begin - return st[1]; - end; - $$ language plpgsql; -create function mylag_prefn(st1 int[], st2 int[]) - returns int[] - as $$ - select '{}'::int[]; - $$ language sql; -create aggregate mylag(int, int) ( - sfunc = mylag_transfn, - stype = int[], - finalfunc = mylag_finalfn, - prefunc = mylag_prefn, - initcond = '{null,null}' - ); --- This will be executed only on master, not on segments. -select i, mylag(i, 2) over (order by i) from generate_series(1,10)i; - i | mylag -----+------- - 1 | - 2 | 1 - 3 | 2 - 4 | 3 - 5 | 4 - 6 | 5 - 7 | 6 - 8 | 7 - 9 | 8 - 10 | 9 -(10 rows) - -create table t1 (a int, b int) distributed by (a); -insert into t1 select i%3, 22*i/7 from generate_series(0,10)i; -select a,b,mylag(b,1) over (order by b) from t1; - a | b | mylag ----+----+------- - 0 | 0 | - 1 | 3 | 0 - 2 | 6 | 3 - 0 | 9 | 6 - 1 | 12 | 9 - 2 | 15 | 12 - 0 | 18 | 15 - 1 | 22 | 18 - 2 | 25 | 22 - 0 | 28 | 25 - 1 | 31 | 28 -(11 rows) - --- --- Misc tests - cover different ways of defining a window. --- -CREATE AGGREGATE mysum (int) ( - STYPE = bigint, - SFUNC = int4_sum, - prefunc = int8pl -); -SELECT a,b,mysum(b) over (order by b) FROM t1; - a | b | mysum ----+----+------- - 0 | 0 | 0 - 1 | 3 | 3 - 2 | 6 | 9 - 0 | 9 | 18 - 1 | 12 | 30 - 2 | 15 | 45 - 0 | 18 | 63 - 1 | 22 | 85 - 2 | 25 | 110 - 0 | 28 | 138 - 1 | 31 | 169 -(11 rows) - -SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as (); -- mvd 1,2->3 - a | b | mysum ----+----+------- - 0 | 0 | 169 - 2 | 6 | 169 - 0 | 9 | 169 - 2 | 15 | 169 - 0 | 18 | 169 - 2 | 25 | 169 - 0 | 28 | 169 - 1 | 3 | 169 - 1 | 12 | 169 - 1 | 22 | 169 - 1 | 31 | 169 -(11 rows) - -SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as (PARTITION BY a); -- mvd 1,2->3 - a | b | mysum ----+----+------- - 1 | 3 | 68 - 1 | 12 | 68 - 1 | 22 | 68 - 1 | 31 | 68 - 0 | 28 | 55 - 0 | 9 | 55 - 0 | 18 | 55 - 0 | 0 | 55 - 2 | 25 | 46 - 2 | 15 | 46 - 2 | 6 | 46 -(11 rows) - -SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as (ORDER BY b); -- mvd 1,2->3 - a | b | mysum ----+----+------- - 0 | 0 | 0 - 1 | 3 | 3 - 2 | 6 | 9 - 0 | 9 | 18 - 1 | 12 | 30 - 2 | 15 | 45 - 0 | 18 | 63 - 1 | 22 | 85 - 2 | 25 | 110 - 0 | 28 | 138 - 1 | 31 | 169 -(11 rows) - -SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as (PARTITION BY a ORDER BY b); -- mvd 1,2->3 - a | b | mysum ----+----+------- - 1 | 3 | 3 - 1 | 12 | 15 - 1 | 22 | 37 - 1 | 31 | 68 - 0 | 0 | 0 - 0 | 9 | 9 - 0 | 18 | 27 - 0 | 28 | 55 - 2 | 6 | 6 - 2 | 15 | 21 - 2 | 25 | 46 -(11 rows) - -SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as - (PARTITION BY a ORDER BY b ROWS BETWEEN 1 preceding and current row); -- mvd 1,2->3 - a | b | mysum ----+----+------- - 1 | 3 | 3 - 1 | 12 | 15 - 1 | 22 | 34 - 1 | 31 | 53 - 0 | 0 | 0 - 0 | 9 | 9 - 0 | 18 | 27 - 0 | 28 | 46 - 2 | 6 | 6 - 2 | 15 | 21 - 2 | 25 | 40 -(11 rows) - -select a,b,mylag(b, 1) over (partition by a order by b) from t1; -- mvd 1,2->3 - a | b | mylag ----+----+------- - 0 | 0 | - 0 | 9 | 0 - 0 | 18 | 9 - 0 | 28 | 18 - 2 | 6 | - 2 | 15 | 6 - 2 | 25 | 15 - 1 | 3 | - 1 | 12 | 3 - 1 | 22 | 12 - 1 | 31 | 22 -(11 rows) - http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/e0a072b0/src/test/regress/known_good_schedule ---------------------------------------------------------------------- diff --git a/src/test/regress/known_good_schedule b/src/test/regress/known_good_schedule index afd70ff..6c18613 100755 --- a/src/test/regress/known_good_schedule +++ b/src/test/regress/known_good_schedule @@ -170,7 +170,6 @@ ignore: percentile ignore: resource_queue_function ignore: gp_optimizer ignore: co_nestloop_idxscan -test: agg_derived_win ignore: orca_udfs # # the following test needs some special filespace/tablespace. http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/e0a072b0/src/test/regress/sql/agg_derived_win.sql ---------------------------------------------------------------------- diff --git a/src/test/regress/sql/agg_derived_win.sql b/src/test/regress/sql/agg_derived_win.sql deleted file mode 100644 index 66b0b9d..0000000 --- a/src/test/regress/sql/agg_derived_win.sql +++ /dev/null @@ -1,161 +0,0 @@ --- Objective: test aggregate derived window functions in HAWQ. --- Aggregate derived window functions are nothing but user defined --- aggregates when used with "OVER()" clause. Refer to GPSQL-1418. - --- Begin EMA (copied from attachment to MPP-14845) --- --- Definition of ema -- exponential moving average --- --- Given a sequence of numbers --- V = (V_0, V_1, ... , V_n-1) --- and a real number smoothing factor --- X such that 0 < X <= 1 --- then --- ema(V) = E = (E_0, E_1, ... , E_n-1) --- and --- E_0 = V_0 --- --- E_i = E_i-1 * (1-X) + V_i * X --- = E_i-1 + (V_i - E_i-1) * X --- --- Here the sequence V is represented by table ema_test ordered by k. - -drop type if exists ema_type cascade; -drop table if exists ema_test cascade; - -create type ema_type as (x float, e float); - -create function ema_adv(t ema_type, v float, x float) - returns ema_type - as $$ - begin - if t.e is null then - t.e = v; - t.x = x; - else - if t.x != x then - raise exception 'ema smoothing x may not vary'; - end if; - t.e = t.e + (v - t.e) * t.x; - end if; - return t; - end; - $$ language plpgsql; - -create function ema_fin(t ema_type) - returns float - as $$ - begin - return t.e; - end; - $$ language plpgsql; - --- Work around for MPP-14845: define a placebo prefunc. This should --- never be called. -create function ema_pre(s1 ema_type, s2 ema_type) - returns ema_type - as $$ - select '(,)'::ema_type; - $$ language sql; - -create aggregate ema(float, float) ( - sfunc = ema_adv, - stype = ema_type, - finalfunc = ema_fin, - prefunc = ema_pre, - initcond = '(,)' - ); - -create table ema_test - ( k int, v float ) - distributed by (k); - -insert into ema_test - select i, 4*(22/7::float) + 10.0*(1+cos(radians(i*5))) - from generate_series(0,19) i(i); - -select - k, v, - ema(v, 0.9) over (order by k rows between unbounded preceding and current row) -from ema_test -order by k; - -select - k, v, - ema(v, 0.9) over (order by k) -from ema_test -order by k; - --- End EMA (MPP-14845) - --- --- Aggregate derived equivalent of "lag()" window function. --- -create function mylag_transfn(st int[], val int, lag int) - returns int[] - as $$ - declare - local_st int[] := st; - local_lag int := lag; - begin - if local_st is null then - local_st := '{}'::int[]; - while local_lag >= 0 - loop - select array_append(local_st, null::int) into local_st; - local_lag := local_lag - 1; - end loop; - end if; - return array_append(local_st[2:lag+1], val); - end; - $$ language plpgsql; - -create function mylag_finalfn(st int[]) - returns int - as $$ - begin - return st[1]; - end; - $$ language plpgsql; - -create function mylag_prefn(st1 int[], st2 int[]) - returns int[] - as $$ - select '{}'::int[]; - $$ language sql; - -create aggregate mylag(int, int) ( - sfunc = mylag_transfn, - stype = int[], - finalfunc = mylag_finalfn, - prefunc = mylag_prefn, - initcond = '{null,null}' - ); - --- This will be executed only on master, not on segments. -select i, mylag(i, 2) over (order by i) from generate_series(1,10)i; - -create table t1 (a int, b int) distributed by (a); - -insert into t1 select i%3, 22*i/7 from generate_series(0,10)i; - -select a,b,mylag(b,1) over (order by b) from t1; - --- --- Misc tests - cover different ways of defining a window. --- -CREATE AGGREGATE mysum (int) ( - STYPE = bigint, - SFUNC = int4_sum, - prefunc = int8pl -); -SELECT a,b,mysum(b) over (order by b) FROM t1; -SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as (); -- mvd 1,2->3 -SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as (PARTITION BY a); -- mvd 1,2->3 -SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as (ORDER BY b); -- mvd 1,2->3 -SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as (PARTITION BY a ORDER BY b); -- mvd 1,2->3 -SELECT a,b,mysum(b) over (w) FROM t1 WINDOW w as - (PARTITION BY a ORDER BY b ROWS BETWEEN 1 preceding and current row); -- mvd 1,2->3 - -select a,b,mylag(b, 1) over (partition by a order by b) from t1; -- mvd 1,2->3 -
