Re: array_agg() NULL Handling

The aggregate docs say:

> The first form of aggregate expression invokes the aggregate across all input rows for which the given expression(s) yield non-null values. (Actually, it is up to the aggregate function whether to ignore null values or not — but all the standard ones do.)

-- http://developer.postgresql.org/pgdocs/postgres/sql-expressions.html#SYNTAX-AGGREGATES

That, however, is not true of array_agg():

try=# CREATE TABLE foo(id int);
CREATE TABLE
try=# INSERT INTO foo values(1), (2), (NULL), (3);
INSERT 0 4
try=# select array_agg(id) from foo;
array_agg
──────────────
{1,2,NULL,3}
(1 row)

So are the docs right, or is array_agg() right?

Best,

David

On 1 September 2010 06:45, David E. Wheeler <david(at)kineticode(dot)com> wrote:
> The aggregate docs say:
>
>> The first form of aggregate expression invokes the aggregate across all input rows for which the given expression(s) yield non-null values. (Actually, it is up to the aggregate function whether to ignore null values or not — but all the standard ones do.)
>
> -- http://developer.postgresql.org/pgdocs/postgres/sql-expressions.html#SYNTAX-AGGREGATES
>
> That, however, is not true of array_agg():
>
> try=# CREATE TABLE foo(id int);
> CREATE TABLE
> try=# INSERT INTO foo values(1), (2), (NULL), (3);
> INSERT 0 4
> try=# select array_agg(id) from foo;
>  array_agg
> ──────────────
>  {1,2,NULL,3}
> (1 row)
>
> So are the docs right, or is array_agg() right?

I think it might be both. array_agg doesn't return NULL, it returns
an array which contains NULL.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

On Aug 31, 2010, at 11:56 PM, Thom Brown wrote:

>>> The first form of aggregate expression invokes the aggregate across all input rows for which the given expression(s) yield non-null values. (Actually, it is up to the aggregate function whether to ignore null values or not — but all the standard ones do.)
>>
>> -- http://developer.postgresql.org/pgdocs/postgres/sql-expressions.html#SYNTAX-AGGREGATES
>>
>> That, however, is not true of array_agg():
>>
>> try=# CREATE TABLE foo(id int);
>> CREATE TABLE
>> try=# INSERT INTO foo values(1), (2), (NULL), (3);
>> INSERT 0 4
>> try=# select array_agg(id) from foo;
>> array_agg
>> ──────────────
>> {1,2,NULL,3}
>> (1 row)
>>
>> So are the docs right, or is array_agg() right?
>
> I think it might be both. array_agg doesn't return NULL, it returns
> an array which contains NULL.

No, string_agg() doesn't work this way, for example:

select string_agg(id::text, ',') from foo;
string_agg
────────────
1,2,3
(1 row)

Note that it's not:

select string_agg(id::text, ',') from foo;
string_agg
────────────
1,2,,3
(1 row)

Best,

David

2010/9/1 David E. Wheeler <david(at)kineticode(dot)com>:
> The aggregate docs say:
>
>> The first form of aggregate expression invokes the aggregate across all input rows for which the given expression(s) yield non-null values. (Actually, it is up to the aggregate function whether to ignore null values or not — but all the standard ones do.)
>
> -- http://developer.postgresql.org/pgdocs/postgres/sql-expressions.html#SYNTAX-AGGREGATES
>
> That, however, is not true of array_agg():
>
> try=# CREATE TABLE foo(id int);
> CREATE TABLE
> try=# INSERT INTO foo values(1), (2), (NULL), (3);
> INSERT 0 4
> try=# select array_agg(id) from foo;
>  array_agg
> ──────────────
>  {1,2,NULL,3}
> (1 row)
>
> So are the docs right, or is array_agg() right?

Docs is wrong :) I like current implementation. You can remove a NULLs
from aggregation very simply, but different direction isn't possible

Regards
Pavel Stehule
>
> Best,
>
> David
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

On 1 September 2010 07:56, Thom Brown <thom(at)linux(dot)com> wrote:
> On 1 September 2010 06:45, David E. Wheeler <david(at)kineticode(dot)com> wrote:
>> The aggregate docs say:
>>
>>> The first form of aggregate expression invokes the aggregate across all input rows for which the given expression(s) yield non-null values. (Actually, it is up to the aggregate function whether to ignore null values or not — but all the standard ones do.)
>>
>> -- http://developer.postgresql.org/pgdocs/postgres/sql-expressions.html#SYNTAX-AGGREGATES
>>
>> That, however, is not true of array_agg():
>>
>> try=# CREATE TABLE foo(id int);
>> CREATE TABLE
>> try=# INSERT INTO foo values(1), (2), (NULL), (3);
>> INSERT 0 4
>> try=# select array_agg(id) from foo;
>>  array_agg
>> ──────────────
>>  {1,2,NULL,3}
>> (1 row)
>>
>> So are the docs right, or is array_agg() right?
>
> I think it might be both.  array_agg doesn't return NULL, it returns
> an array which contains NULL.

The second I wrote that, I realised it was b*ll%$ks, as I was still in
the process of waking up.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

On Sep 1, 2010, at 12:30 AM, Pavel Stehule wrote:

> Docs is wrong :) I like current implementation. You can remove a NULLs
> from aggregation very simply, but different direction isn't possible

Would appreciate the recipe for removing the NULLs.

Best,

David

On Sep 1, 2010, at 1:06 AM, Thom Brown wrote:

>> I think it might be both. array_agg doesn't return NULL, it returns
>> an array which contains NULL.
>
> The second I wrote that, I realised it was b*ll%$ks, as I was still in
> the process of waking up.

I know that feeling.

/me sips his coffee

Best,

David

On Sep 1, 2010, at 12:30 AM, Pavel Stehule wrote:

>> So are the docs right, or is array_agg() right?
>
> Docs is wrong :) I like current implementation. You can remove a NULLs
> from aggregation very simply, but different direction isn't possible

Patch:

diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml
index 9f91939..e301019 100644
*** a/doc/src/sgml/syntax.sgml
--- b/doc/src/sgml/syntax.sgml
*************** sqrt(2)
*** 1543,1549 ****
The first form of aggregate expression invokes the aggregate
across all input rows for which the given expression(s) yield
non-null values. (Actually, it is up to the aggregate function
! whether to ignore null values or not &mdash; but all the standard ones do.)
The second form is the same as the first, since
<literal>ALL</literal> is the default. The third form invokes the
aggregate for all distinct values of the expressions found
--- 1543,1550 ----
The first form of aggregate expression invokes the aggregate
across all input rows for which the given expression(s) yield
non-null values. (Actually, it is up to the aggregate function
! whether to ignore null values or not &mdash; but all the standard
! ones except <function>array_agg</> do.)
The second form is the same as the first, since
<literal>ALL</literal> is the default. The third form invokes the
aggregate for all distinct values of the expressions found

Best,

David

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> *** 1543,1549 ****
> The first form of aggregate expression invokes the aggregate
> across all input rows for which the given expression(s) yield
> non-null values. (Actually, it is up to the aggregate function
> ! whether to ignore null values or not &mdash; but all the standard ones do.)
> The second form is the same as the first, since
> <literal>ALL</literal> is the default. The third form invokes the
> aggregate for all distinct values of the expressions found
> --- 1543,1550 ----
> The first form of aggregate expression invokes the aggregate
> across all input rows for which the given expression(s) yield
> non-null values. (Actually, it is up to the aggregate function
> ! whether to ignore null values or not &mdash; but all the standard
> ! ones except <function>array_agg</> do.)
> The second form is the same as the first, since
> <literal>ALL</literal> is the default. The third form invokes the
> aggregate for all distinct values of the expressions found

I think when that text was written, it was meant to imply "all the
aggregates defined in SQL92". There seems to be a lot of confusion
in this thread about whether "standard" means "defined by SQL spec"
or "built-in in Postgres". Should we try to refine the wording to
clarify that?

Even more to the point, should we deliberately make this vaguer so that
we aren't finding ourselves with obsolete text again and again? You can
bet that people adding new aggregates in the future aren't going to
think to update this sentence, any more than happened with array_agg.

regards, tom lane

On Sep 1, 2010, at 10:12 AM, Tom Lane wrote:

> I think when that text was written, it was meant to imply "all the
> aggregates defined in SQL92". There seems to be a lot of confusion
> in this thread about whether "standard" means "defined by SQL spec"
> or "built-in in Postgres". Should we try to refine the wording to
> clarify that?

Yes please.

> Even more to the point, should we deliberately make this vaguer so that
> we aren't finding ourselves with obsolete text again and again? You can
> bet that people adding new aggregates in the future aren't going to
> think to update this sentence, any more than happened with array_agg.

Perhaps “consult the docs for each aggregate to determine how it handles NULLs.”

Best,

David

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> On Sep 1, 2010, at 10:12 AM, Tom Lane wrote:
>> Even more to the point, should we deliberately make this vaguer so that
>> we aren't finding ourselves with obsolete text again and again? You can
>> bet that people adding new aggregates in the future aren't going to
>> think to update this sentence, any more than happened with array_agg.

> Perhaps consult the docs for each aggregate to determine how it handles NULLs.

Hm, actually the whole para needs work. It was designed at a time when
DISTINCT automatically discarded nulls, which isn't true anymore, and
that fact was patched-in in a very awkward way too. Perhaps something
like

The first form of aggregate expression invokes the aggregate
once for each input row.
The second form is the same as the first, since
<literal>ALL</literal> is the default.
The third form invokes the aggregate once for each distinct value,
or set of values, of the expression(s) found in the input rows.
The last form invokes the aggregate once for each input row; since no
particular input value is specified, it is generally only useful
for the <function>count(*)</function> aggregate function.

Most aggregate functions ignore null inputs, so that rows in which
one or more of the expression(s) yield null are discarded. (This
can be assumed to be true, unless otherwise specified, for all
built-in aggregates.)

Then we have to make sure array_agg is properly documented, but we
don't have to insert something into the description of every single
aggregate, which is what your proposal would require.

regards, tom lane

On Wed, Sep 01, 2010 at 08:16:41AM -0700, David Wheeler wrote:
> On Sep 1, 2010, at 12:30 AM, Pavel Stehule wrote:
>
> > Docs is wrong :) I like current implementation. You can remove a
> > NULLs from aggregation very simply, but different direction isn't
> > possible
>
> Would appreciate the recipe for removing the NULLs.

WHERE clause :P

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On 1 September 2010 18:47, David Fetter <david(at)fetter(dot)org> wrote:
> On Wed, Sep 01, 2010 at 08:16:41AM -0700, David Wheeler wrote:
>> On Sep 1, 2010, at 12:30 AM, Pavel Stehule wrote:
>>
>> > Docs is wrong :) I like current implementation.  You can remove a
>> > NULLs from aggregation very simply, but different direction isn't
>> > possible
>>
>> Would appreciate the recipe for removing the NULLs.
>
> WHERE clause :P

There may be cases where that's undesirable, such as there being more
than one aggregate in the SELECT list, or the column being grouped on
needing to return rows regardless as to whether there's NULLs in the
column being targeted by array_agg() or not.
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

On Sep 1, 2010, at 10:52 AM, Thom Brown wrote:

>>> ould appreciate the recipe for removing the NULLs.
>>
>> WHERE clause :P
>
> There may be cases where that's undesirable, such as there being more
> than one aggregate in the SELECT list, or the column being grouped on
> needing to return rows regardless as to whether there's NULLs in the
> column being targeted by array_agg() or not.

Exactly the issue I ran into:

SELECT name AS distribution,
array_agg(
CASE relstatus WHEN 'stable'
THEN version
ELSE NULL
END ORDER BY version) AS stable,
array_agg(
CASE relstatus
WHEN 'testing'
THEN version
ELSE NULL
END ORDER BY version) AS testing
FROM distributions
GROUP BY name;

distribution │ stable │ testing
──────────────┼───────────────────┼────────────────────
pair │ {NULL,1.0.0,NULL} │ {0.0.1,NULL,1.2.0}
pgtap │ {NULL} │ {0.0.1}
(2 rows)

Annoying.

Best,

David

On Sep 1, 2010, at 10:30 AM, Tom Lane wrote:

> Hm, actually the whole para needs work. It was designed at a time when
> DISTINCT automatically discarded nulls, which isn't true anymore, and
> that fact was patched-in in a very awkward way too. Perhaps something
> like
>
> The first form of aggregate expression invokes the aggregate
> once for each input row.
> The second form is the same as the first, since
> <literal>ALL</literal> is the default.
> The third form invokes the aggregate once for each distinct value,
> or set of values, of the expression(s) found in the input rows.
> The last form invokes the aggregate once for each input row; since no
> particular input value is specified, it is generally only useful
> for the <function>count(*)</function> aggregate function.
>
> Most aggregate functions ignore null inputs, so that rows in which
> one or more of the expression(s) yield null are discarded. (This
> can be assumed to be true, unless otherwise specified, for all
> built-in aggregates.)

I don't think you need the parentheses, though without them, "This" might be better written as "The ignoring of NULLs".

Just my $0.02.

Best,

David

2010/9/1 Thom Brown <thom(at)linux(dot)com>:
> On 1 September 2010 18:47, David Fetter <david(at)fetter(dot)org> wrote:
>> On Wed, Sep 01, 2010 at 08:16:41AM -0700, David Wheeler wrote:
>>> On Sep 1, 2010, at 12:30 AM, Pavel Stehule wrote:
>>>
>>> > Docs is wrong :) I like current implementation.  You can remove a
>>> > NULLs from aggregation very simply, but different direction isn't
>>> > possible
>>>
>>> Would appreciate the recipe for removing the NULLs.
>>
>> WHERE clause :P
>
> There may be cases where that's undesirable, such as there being more
> than one aggregate in the SELECT list, or the column being grouped on
> needing to return rows regardless as to whether there's NULLs in the
> column being targeted by array_agg() or not.

Then you can eliminate NULLs with simple function

CREATE OR REPLACE FUNCTION remove_null(anyarray)
RETURNS anyarray AS $$
SELECT ARRAY(SELECT x FROM unnest($1) g(x) WHERE x IS NOT NULL)
$$ LANGUAGE sql;
> --
> Thom Brown
> Twitter: @darkixion
> IRC (freenode): dark_ixion
> Registered Linux user: #516935
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

On Sep 1, 2010, at 11:09 AM, Pavel Stehule wrote:

> Then you can eliminate NULLs with simple function
>
> CREATE OR REPLACE FUNCTION remove_null(anyarray)
> RETURNS anyarray AS $$
> SELECT ARRAY(SELECT x FROM unnest($1) g(x) WHERE x IS NOT NULL)
> $$ LANGUAGE sql;

Kind of defeats the purpose of the efficiency of the aggregate.

Best,

David

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> On Sep 1, 2010, at 10:30 AM, Tom Lane wrote:
>> Most aggregate functions ignore null inputs, so that rows in which
>> one or more of the expression(s) yield null are discarded. (This
>> can be assumed to be true, unless otherwise specified, for all
>> built-in aggregates.)

> I don't think you need the parentheses, though without them, "This" might be better written as "The ignoring of NULLs".

Done, without the parentheses. I didn't add "The ignoring of NULLs",
it seemed a bit too verbose.

regards, tom lane

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> On Sep 1, 2010, at 11:09 AM, Pavel Stehule wrote:
>> Then you can eliminate NULLs with simple function

> Kind of defeats the purpose of the efficiency of the aggregate.

Well, you can build your own version of array_agg with the same
implementation, except you mark the transition function as strict ...

regards, tom lane

2010/9/1 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> "David E. Wheeler" <david(at)kineticode(dot)com> writes:
>> On Sep 1, 2010, at 11:09 AM, Pavel Stehule wrote:
>>> Then you can eliminate NULLs with simple function
>
>> Kind of defeats the purpose of the efficiency of the aggregate.
>
> Well, you can build your own version of array_agg with the same
> implementation, except you mark the transition function as strict ...
>

I am checking this now, and it is not possible - it needs a some
initial value and there isn't possible to set a "internal" value.
probably some C coding is necessary.

Regards

Pavel

>                        regards, tom lane
>

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> 2010/9/1 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>> Well, you can build your own version of array_agg with the same
>> implementation, except you mark the transition function as strict ...

> I am checking this now, and it is not possible - it needs a some
> initial value and there isn't possible to set a "internal" value.

Well, you can cheat a bit ...

regression=# create or replace function array_agg_transfn_strict(internal, anyelement) returns internal as 'array_agg_transfn' language internal immutable;
CREATE FUNCTION
regression=# create aggregate array_agg_strict(anyelement) (stype = internal,
sfunc = array_agg_transfn_strict, finalfunc = array_agg_finalfn);
CREATE AGGREGATE
regression=# create or replace function array_agg_transfn_strict(internal, anyelement) returns internal as 'array_agg_transfn' language internal strict immutable;
CREATE FUNCTION

regards, tom lane

2010/9/1 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> 2010/9/1 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>>> Well, you can build your own version of array_agg with the same
>>> implementation, except you mark the transition function as strict ...
>
>> I am checking this now, and it is not possible - it needs a some
>> initial value and there isn't possible to set a "internal" value.
>
> Well, you can cheat a bit ...
>
> regression=# create or replace function array_agg_transfn_strict(internal, anyelement) returns internal as 'array_agg_transfn' language internal immutable;
> CREATE FUNCTION
> regression=# create aggregate array_agg_strict(anyelement) (stype = internal,
> sfunc = array_agg_transfn_strict, finalfunc = array_agg_finalfn);
> CREATE AGGREGATE
> regression=# create or replace function array_agg_transfn_strict(internal, anyelement) returns internal as 'array_agg_transfn' language internal strict immutable;
> CREATE FUNCTION
>

nice dark trick :) - but it doesn't work

ERROR: aggregate 16395 needs to have compatible input type and transition type
postgres=#

Pavel

>                        regards, tom lane
>

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> On Sep 1, 2010, at 10:52 AM, Thom Brown wrote:
>
>>>> ould appreciate the recipe for removing the NULLs.
>>>
>>> WHERE clause :P
>>
>> There may be cases where that's undesirable, such as there being more
>> than one aggregate in the SELECT list, or the column being grouped on
>> needing to return rows regardless as to whether there's NULLs in the
>> column being targeted by array_agg() or not.
>
> Exactly the issue I ran into:
>
> SELECT name AS distribution,
> array_agg(
> CASE relstatus WHEN 'stable'
> THEN version
> ELSE NULL
> END ORDER BY version) AS stable,
> array_agg(
> CASE relstatus
> WHEN 'testing'
> THEN version
> ELSE NULL
> END ORDER BY version) AS testing
> FROM distributions
> GROUP BY name;

What about adding WHERE support to aggregates, adding to the ORDER BY
capability they already have?

SELECT array_agg(version WHERE relstatus = 'stable' ORDER BY version)

The current way to do that is using a subquery and unnest() and where
clause there, but that's not a good way to avoid to process stored data
in the aggregate / in the query.

Regards,
--
dim

On Sep 1, 2010, at 11:52 AM, Pavel Stehule wrote:

>> regression=# create or replace function array_agg_transfn_strict(internal, anyelement) returns internal as 'array_agg_transfn' language internal immutable;
>> CREATE FUNCTION
>> regression=# create aggregate array_agg_strict(anyelement) (stype = internal,
>> sfunc = array_agg_transfn_strict, finalfunc = array_agg_finalfn);
>> CREATE AGGREGATE
>> regression=# create or replace function array_agg_transfn_strict(internal, anyelement) returns internal as 'array_agg_transfn' language internal strict immutable;
>> CREATE FUNCTION
>>
>
> nice dark trick :) - but it doesn't work
>
> ERROR: aggregate 16395 needs to have compatible input type and transition type
> postgres=#

I could use this trick now. Anyone got any bright ideas how to fix it?

Thanks,

David