Re: BUG #12273: CASE Expression BUG - Mailing list pgsql-bugs
From | Tom Lane |
---|---|
Subject | Re: BUG #12273: CASE Expression BUG |
Date | |
Msg-id | 2065.1418933405@sss.pgh.pa.us Whole thread Raw |
In response to | Re: BUG #12273: CASE Expression BUG (David Johnston <david.g.johnston@gmail.com>) |
Responses |
Re: BUG #12273: CASE Expression BUG
|
List | pgsql-bugs |
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
pgsql-bugs by date: