Re: BUG #12273: CASE Expression BUG

David Johnston <david.g.johnston@gmail.com> writes:
> On Thu, Dec 18, 2014 at 10:14 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Hmm ... I'd just been looking at 4.2.14:
>> http://www.postgresql.org/docs/9.4/static/sql-expressions.html#SYNTAX-EXPRESS-EVAL
>> and thinking that maybe it should mention this.  Perhaps we ought to
>> relocate the text about constant subexpressions into 4.2.14 (and add an
>> example), and then link there from 9.17.1.

> +1

> Something like:
> Before "A limitation of this technique [...]"
> The are two limitations to this technique: planner optimizations may occur
> and aggregate expressions will be evaluated.

Yeah, I've just been working on some text to put there.  I'm still
wordsmithing it, but right now it's as attached.

> The question is how detailed do we need to get here...is it an issue
> specific to casting or is there some other interplay happening?

It's not particularly specific to casting, any constant subexpression that
could throw errors is at risk.  I'm using divide-by-zero as the canonical
example in this area.

            regards, tom lane
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index ef69b94..5ef1911 100644
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
*************** SELECT ... WHERE CASE WHEN x <> 0
*** 11179,11189 ****

     <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.
      </para>
     </note>
    </sect2>
--- 11179,11192 ----

     <note>
      <para>
!      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>
!      does not evaluate any subexpressions that are not needed to determine
!      the result</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 6f8b7e8..d2ece37 100644
*** a/doc/src/sgml/syntax.sgml
--- b/doc/src/sgml/syntax.sgml
*************** SELECT ... WHERE CASE WHEN x > 0 THEN
*** 2439,2445 ****
     </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
--- 2439,2472 ----
     </para>

     <para>
!     <literal>CASE</> is not a panacea 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 > 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 > 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 parameters and local variables
!     can be inserted into queries as constants for planning purposes.
!     Thus for example in <application>PL/pgSQL</> functions, 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 <quote>scalar</>
      expressions in a <literal>SELECT</> list or <literal>HAVING</> clause