diff options
Diffstat (limited to 'doc/src/sgml/syntax.sgml')
| -rw-r--r-- | doc/src/sgml/syntax.sgml | 22 |
1 files changed, 22 insertions, 0 deletions
diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml index 2f0680fd0bc..399ae070759 100644 --- a/doc/src/sgml/syntax.sgml +++ b/doc/src/sgml/syntax.sgml @@ -2426,6 +2426,28 @@ SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END; example, it would be better to sidestep the problem by writing <literal>y > 1.5*x</> instead.) </para> + + <para> + A limitation of this technique is that a <literal>CASE</> cannot + prevent evaluation of an aggregate expression contained within it, + because aggregate expressions are computed before <quote>scalar</> + expressions in a <literal>SELECT</> list or <literal>HAVING</> clause + are considered. For example, the following query can cause a + division-by-zero error despite seemingly having protected against it: +<programlisting> +SELECT CASE WHEN min(employees) > 0 + THEN avg(expenses / employees) + END + FROM departments; +</programlisting> + The <function>min()</> and <function>avg()</> aggregates are computed + concurrently over all the input rows, so if any row + has <structfield>employees</> equal to zero, the division-by-zero error + will occur before there is any opportunity to test the result of + <function>min()</>. Instead, use a <literal>WHERE</> + or <literal>FILTER</> clause to prevent problematic input rows from + reaching an aggregate function in the first place. + </para> </sect2> </sect1> |