Lists: | pgsql-hackers |
---|
From: | Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | NULL input for array_agg()? |
Date: | 2009-11-15 07:27:49 |
Message-ID: | e08cc0400911142327l70b29fdfwb73258bd8a944631@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Hi,
During reviewing aggregates ORDER BY, I was reading spec and found
description like:
== snip ==
Of the rows in the aggregation, the following do not qualify:
— If DISTINCT is specified, then redundant duplicates.
— Every row in which the <value expression> evaluates to the null value.
== /snip ==
... and array_agg() is among the functions that description above
refers to. So I wonder if array_agg doesn't accept NULL input (with
STRICT trans function). Did we discussed about this issue when
implementing it for 8.4?
Regards,
--
Hitoshi Harada
From: | Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> |
---|---|
To: | umi(dot)tanuki(at)gmail(dot)com (Hitoshi Harada), pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: NULL input for array_agg()? |
Date: | 2009-11-15 07:52:20 |
Message-ID: | 877htsmdrh.fsf@news-spur.riddles.org.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
>>>>> "Hitoshi" == Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com> writes:
Hitoshi> Hi, During reviewing aggregates ORDER BY, I was reading spec
Hitoshi> and found description like:
Hitoshi> == snip ==
Hitoshi> Of the rows in the aggregation, the following do not qualify:
Hitoshi> — If DISTINCT is specified, then redundant duplicates.
Hitoshi> — Every row in which the <value expression> evaluates to the null value.
Hitoshi> == /snip ==
Where did you find that?
The SQL2008 last-call draft says this:
4) If <general set function> is specified, then:
a) Let TX be the single-column table that is the result of applying
the <value expression> to each row of T1 and eliminating null
values. If one or more null values are eliminated, then a
completion condition is raised: warning -- null value eliminated
in set function.
b) Case:
i) If DISTINCT is specified, then let TXA be the result of
eliminating redundant duplicate values from TX, using the
comparison rules specified in Subclause 8.2, "<comparison
predicate>", to identify the redundant duplicate values.
ii) Otherwise, let TXA be TX.
[more subclauses of rule (4) snipped as irrelevant]
8) If <array aggregate function> is specified, then:
a) If <sort specification list> is specified, then let K be the
number of <sort key>s; otherwise, let K be 0 (zero).
b) Let TXA be the table of K+1 columns obtained by applying the
<value expression> immediately contained in the <array aggregate
function> to each row of T1 to obtain the first column of TXA,
and, for all i, 1 (one) i K, applying the <value expression>
simply contained in the i-th <sort key> to each row of T1 to
obtain the (i+1)-th column of TXA.
c) Let TXA be ordered according to the values of the <sort key>s
found in the second through (K+1)-th columns of TXA. If K is 0
(zero), then the ordering of TXA is implementation-dependent.
d) Let N be the number of rows in TXA.
e) If N is greater than IDMC, then an exception condition is
raised: data exception -- array data, right truncation.
f) Let Ri, 1 (one) i N, be the rows of TXA according to the
ordering of TXA.
g) Case:
i) If TXA is empty, then the result of <array aggregate
function> is the null value.
ii) Otherwise, the result of <array aggregate function> is an
array of N elements such that for all i, 1 (one) i N, the
value of the i-th element is the value of the first column
of Ri.
NOTE 267 -- Null values are not eliminated when computing <array
aggregate function>. This, plus the optional <sort specification
list>, sets <array aggregate function> apart from <general set
function>s.
array_agg is an <array aggregate function> (in fact the only such),
whereas <general set function> includes almost all the other single-arg
aggregates (avg, min, max, etc.)
--
Andrew (irc:RhodiumToad)
From: | Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com> |
---|---|
To: | Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: NULL input for array_agg()? |
Date: | 2009-11-15 08:05:16 |
Message-ID: | e08cc0400911150005r82bc48du726d02b680c7920c@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
2009/11/15 Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>:
>>>>>> "Hitoshi" == Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com> writes:
>
> Hitoshi> Hi, During reviewing aggregates ORDER BY, I was reading spec
> Hitoshi> and found description like:
>
> Hitoshi> == snip ==
>
> Hitoshi> Of the rows in the aggregation, the following do not qualify:
> Hitoshi> — If DISTINCT is specified, then redundant duplicates.
> Hitoshi> — Every row in which the <value expression> evaluates to the null value.
>
> Hitoshi> == /snip ==
>
> Where did you find that?
In 4.15.4 Aggregate functions. But your snip clarified <array
aggregate function> is special case.
> NOTE 267 -- Null values are not eliminated when computing <array
> aggregate function>. This, plus the optional <sort specification
> list>, sets <array aggregate function> apart from <general set
> function>s.
Regards,
--
Hitoshi Harada