Pivotal Knowledge Base

Follow

Aggregate functions query errors out "aggregate functions with no prelimfn or invprelimfn are not yet supported as window functions"

Environment

Product Version
Pivotal Greenplum 4.3.x

Symptom

A query that has ORDER BY specified inside window OVER clause, for some aggregates it works and for some and it results in the error message.

ERROR: aggregate functions with no prelimfn or invprelimfn are not yet supported as window functions

For example, if you look at two queries mentioned below, you will find order by is supported in the first SQL, but not the second SQL.

dev=# select date,sum(cnt) over(order by date RANGE BETWEEN 7 PRECEDING AND current row) from test.test;
date | sum
------------+-----
2014-04-26 | 1
2014-05-03 | 2
2014-05-10 | 2
2014-05-17 | 2
2014-05-24 | 2
2014-05-31 | 2
(6 rows)

Time: 4.195 ms

dev=# select date,string_agg(id) over(order by date RANGE BETWEEN 7 PRECEDING AND current row) from test.test;
ERROR: aggregate functions with no prelimfn or invprelimfn are not yet supported as window functions

Resolution

It works with the aggregates functions which have "prelimfn" and "invprelimfn".

SUM aggregate has these and "string_agg" does not have these. You can check in pg_aggregate catalog to identify the function that have "prelimfn" and "invprelimfn".

For example.

gpadmin=# select * from pg_aggregate where aggtranstype=2281;
aggfnoid | aggtransfn | agginvtransfn | aggprelimfn | agginvprelimfn | aggfinalfn | aggsortop | aggtranstype | agginitval | aggordered 
-----------------------+--------------------------+---------------+-------------+----------------+--------------------------+-----------+--------------+------------+------------
pg_catalog.string_agg | string_agg_transfn | - | - | - | string_agg_finalfn | 0 | 2281 | | t
pg_catalog.string_agg | string_agg_delim_transfn | - | - | - | string_agg_finalfn | 0 | 2281 | | t


gpadmin=# select * from pg_aggregate where aggtranstype=1700;
aggfnoid | aggtransfn | agginvtransfn | aggprelimfn | agginvprelimfn | aggfinalfn | aggsortop | aggtranstype | agginitval | aggordered 
----------------+-----------------+---------------+-----------------+----------------+------------+-----------+--------------+------------+------------
pg_catalog.sum | int8_sum | int8_invsum | numeric_add | numeric_sub | - | 0 | 1700 | | f
pg_catalog.sum | numeric_add | numeric_sub | numeric_add | numeric_sub | - | 0 | 1700 | | f

For more information on limitation on aggregate functions, please go through "Limitations of Aggregate Expressions" at this link.

Comments

Powered by Zendesk