On Tue, Nov 11, 2014 at 4:37 PM, Marti Raudsepp <ma...@juffo.org> wrote:
> Why? This page explains in what order the expressions in a SELECT
> query are processed. Aggregates can only be found in SELECT queries
> and their semantics are non-trivial, so it seems like a big omission
> to me that their relation to other clauses is not discussed.
>
> I think that's the most appropriate place for that, because GROUP BY,
> HAVING and aggregates are closely related. And their execution occurs
> after evaluating GROUP BY keys and before HAVING predicates, where
> else would one put it?
Ah, OK. I wasn't aware of this execution order. Now we could as well
split the description flow into more tiny paragraphs but this does not
seem worth it. So what about the attached then?
--
Michael
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 7e5bcd9..7f04e504 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -11254,6 +11254,25 @@ SELECT NULLIF(value, '(none)') ...
all are NULL.
</para>
</sect2>
+
+ <sect2 id="functions-conditional-implementation">
+ <title>Implementation Notes</title>
+ <para>
+ When a conditional expression depends on the results of aggregate
+ functions in a <command>SELECT</> or <literal>HAVING</> clause, then all
+ the aggregate functions are always evaluated prior to determining which
+ branch the conditional should take. For example, the following query can
+ cause a division-by-zero error despite only using division in a branch
+ that requires positive values:
+
+<programlisting>
+SELECT CASE WHEN min(employees) > 0
+ THEN average(expenses / employees)
+ END
+ FROM departments;
+</programlisting>
+ </para>
+ </sect2>
</sect1>
<sect1 id="functions-array">
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 473939a..36f4941 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -111,9 +111,11 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
<para>
If the <literal>GROUP BY</literal> clause is specified, the
output is combined into groups of rows that match on one or more
- values. If the <literal>HAVING</literal> clause is present, it
- eliminates groups that do not satisfy the given condition. (See
- <xref linkend="sql-groupby" endterm="sql-groupby-title"> and
+ values. All aggregate functions and their argument expressions
+ in the <command>SELECT</> and <literal>HAVING</> are evaluated.
+ If the <literal>HAVING</literal> clause is present, it eliminates
+ groups that do not satisfy the given condition. (See
+ <xref linkend="sql-groupby" endterm="sql-groupby-title">, and
<xref linkend="sql-having" endterm="sql-having-title"> below.)
</para>
</listitem>
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
