Improve documentation about CASE and constant subexpressions.

The possibility that constant subexpressions of a CASE might be evaluated
at planning time was touched on in 9.17.1 (CASE expressions), but it really
ought to be explained in 4.2.14 (Expression Evaluation Rules) which is the
primary discussion of such topics.  Add text and an example there, and
revise the <note> under CASE to link there.

Back-patch to all supported branches, since it's acted like this for a
long time (though 9.2+ is probably worse because of its more aggressive
use of constant-folding via replanning of nominally-prepared statements).
Pre-9.4, also back-patch text added in commit 0ce627d4 about CASE versus
aggregate functions.

Tom Lane and David Johnston, per discussion of bug #12273.

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 83359108de1448336344c1925268f268514ae591..91ea7cec5c5f1460abbf94bac39ad1376fd8d491 100644 (file)
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -9292,11 +9292,13 @@ SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;
 
    <note>
     <para>
-     As described in <xref linkend="xfunc-volatility">, functions and
-     operators marked <literal>IMMUTABLE</literal> can be evaluated when
-     the query is planned rather than when it is executed.  This means
-     that constant parts of a subexpression that is not evaluated during
-     query execution might still be evaluated during query planning.
+     As described in <xref linkend="syntax-express-eval">, there are various
+     situations in which subexpressions of an expression are evaluated at
+     different times, so that the principle that <quote><token>CASE</token>
+     evaluates only necessary subexpressions</quote> is not ironclad.  For
+     example a constant <literal>1/0</> subexpression will usually result in
+     a division-by-zero failure at planning time, even if it's within
+     a <token>CASE</token> arm that would never be entered at run time.
     </para>
    </note>
   </sect2>

diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml
index 7a94870b6491818ceaec77023a706bf661a39c20..a348b11f7c747cd35c05e22ac3bf532287cd0c17 100644 (file)
--- a/doc/src/sgml/syntax.sgml
+++ b/doc/src/sgml/syntax.sgml
@@ -2208,6 +2208,55 @@ 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 &gt; 1.5*x</> instead.)
    </para>
+
+   <para>
+    <literal>CASE</> is not a cure-all for such issues, however.
+    One limitation of the technique illustrated above is that it does not
+    prevent early evaluation of constant subexpressions.
+    As described in <xref linkend="xfunc-volatility">, functions and
+    operators marked <literal>IMMUTABLE</literal> can be evaluated when
+    the query is planned rather than when it is executed.  Thus for example
+<programlisting>
+SELECT CASE WHEN x &gt; 0 THEN x ELSE 1/0 END FROM tab;
+</programlisting>
+    is likely to result in a division-by-zero failure due to the planner
+    trying to simplify the constant subexpression,
+    even if every row in the table has <literal>x &gt; 0</> so that the
+    <literal>ELSE</> arm would never be entered at run time.
+   </para>
+
+   <para>
+    While that particular example might seem silly, related cases that don't
+    obviously involve constants can occur in queries executed within
+    functions, since the values of function arguments and local variables
+    can be inserted into queries as constants for planning purposes.
+    Within <application>PL/pgSQL</> functions, for example, using an
+    <literal>IF</>-<literal>THEN</>-<literal>ELSE</> statement to protect
+    a risky computation is much safer than just nesting it in a
+    <literal>CASE</> expression.
+   </para>
+
+   <para>
+    Another limitation of the same kind is that a <literal>CASE</> cannot
+    prevent evaluation of an aggregate expression contained within it,
+    because aggregate expressions are computed before other
+    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</>
+    clause to prevent problematic input rows from
+    reaching an aggregate function in the first place.
+   </para>
   </sect2>
  </sect1>