hawq-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ztao1...@apache.org
Subject [2/6] incubator-hawq git commit: HAWQ-987. Add feature test for agg derived win.
Date Tue, 09 Aug 2016 02:19:44 GMT
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 <zhenglin.taozl@gmail.com>
Authored: Tue Aug 9 07:02:01 2016 +0800
Committer: ztao1987 <zhenglin.taozl@gmail.com>
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
-


Mime
View raw message