Re: NULL input for array_agg()? - Mailing list pgsql-hackers

>>>>> "Hitoshi" == Hitoshi Harada <umi.tanuki@gmail.com> writes:
Hitoshi> Hi, During reviewing aggregates ORDER BY, I was reading specHitoshi> and found description like:
Hitoshi> == snip ==
Hitoshi> Of the rows in the aggregation, the following do not qualify:Hitoshi> — If DISTINCT is specified, then
redundantduplicates.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
comparisonrules 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
<arrayaggregate     function> to each row of T1 to obtain the first column of TXA,     and, for all i, 1 (one) i K,
applyingthe <value expression>     simply contained in the i-th <sort key> to each row of T1 to     obtain the (i+1)-th
columnof 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)