array_agg() NULL Handling

[David E. Wheeler]

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


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Thom Brown-2]

On 1 September 2010 06:45, David E. Wheeler <[hidden email]> 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

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[David E. Wheeler]

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


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Pavel Stehule]

2010/9/1 David E. Wheeler <[hidden email]>:

> 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 ([hidden email])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Thom Brown-2]

On 1 September 2010 07:56, Thom Brown <[hidden email]> wrote:

> On 1 September 2010 06:45, David E. Wheeler <[hidden email]> 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

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[David E. Wheeler]

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



--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[David E. Wheeler]

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

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[David E. Wheeler]

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


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Tom Lane-2]

"David E. Wheeler" <[hidden email]> 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

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[David E. Wheeler]

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
--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Tom Lane-2]

"David E. Wheeler" <[hidden email]> 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

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[David Fetter]

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 <[hidden email]> http://fetter.org/
Phone: [phone]  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: [hidden email]
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

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

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Thom Brown-2]

On 1 September 2010 18:47, David Fetter <[hidden email]> 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

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[David E. Wheeler]

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


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[David E. Wheeler]

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


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Pavel Stehule]

2010/9/1 Thom Brown <[hidden email]>:

> On 1 September 2010 18:47, David Fetter <[hidden email]> 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 ([hidden email])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[David E. Wheeler]

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


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Tom Lane-2]

"David E. Wheeler" <[hidden email]> 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

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Tom Lane-2]

"David E. Wheeler" <[hidden email]> 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

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Pavel Stehule]

2010/9/1 Tom Lane <[hidden email]>:
> "David E. Wheeler" <[hidden email]> 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
>

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers