Re: NULL input for array_agg()?
От | Andrew Gierth |
---|---|
Тема | Re: NULL input for array_agg()? |
Дата | |
Msg-id | 877htsmdrh.fsf@news-spur.riddles.org.uk обсуждение исходный текст |
Ответ на | NULL input for array_agg()? (Hitoshi Harada <umi.tanuki@gmail.com>) |
Ответы |
Re: NULL input for array_agg()?
|
Список | 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)
В списке pgsql-hackers по дате отправления:
Предыдущее
От: George GensureДата:
Сообщение: patch - Report the schema along table name in a referential failure error message