Re: Function array_agg(array)

2014-10-12 19:37 GMT+07:00 Ali Akbar <the(dot)apaan(at)gmail(dot)com>:

> Currently, it cannot handle NULL arrays:
> backend> select array_agg(a) from (values(null::int[])) a(a);
> 1: array_agg (typeid = 1007, len = -1, typmod = -1, byval = f)
> ----
> ERROR: cannot aggregate null arrays
>

While thinking about the function behavior if its input is NULL array (e.g:
NULL:int[]), i've found:
- currentpatch doesn't handle empty array correctly:
- when there is only one array to aggregate, the resulting array is
wrong
- when the first array is empty array, and the second array is also
empty array, it segfaulted
- if we see NULL array as NULL values, the resulting array cannot be
differentiated from array of null ints:
- SELECT array_agg(NULL::int[]) FROM generate_series(1,2); ---> {NULL,
NULL} with type int[]
- SELECT array_agg(NULL::int) FROM generate_series(1,2); --> {NULL,
NULL} with type int[]

Also i've found that handling NULL array is listed as BUG in TODO. The
discussion in the thread is still not finished, with last email from Tom
Lane (http://www.postgresql.org/message-id/18866.1226025853@sss.pgh.pa.us)

> array_lower raise exception if array is empty (there are no dimensions
> > to inquire about)
> > array_upper raise exception if array is empty (there are no dimensions
> > to inquire about)
>
> Well, these beg the question: is an empty array zero-dimensional, or
> is it a one-dimensional array of no elements, or perhaps both of those
> as well as higher-dimensional cases where any axis has zero elements,
> or ???
>
> It's really all kind of messy ... we need to trade off simplicity of
> definition, ease of use, backwards compatibility, and standards
> compliance (though the standard has only 1-D arrays so it's of just
> limited help here).
>

So, is there any idea how we will handle NULL and empty array in
array_agg(anyarray)?
I propose we just reject those input because the output will make no sense:
- array_agg(NULL::int[]) --> the result will be indistinguished from
array_agg of NULL ints.
- array_agg('{}'::int[]) --> how we determine the dimension of the result?
is it 0? Or the result will be just an empty array {} ?

Regards,
--
Ali Akbar