Re: BUG #12273: CASE Expression BUG

On Thu, Dec 18, 2014 at 1:10 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> David Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > On Thu, Dec 18, 2014 at 10:14 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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.
>
>
>
​
"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."

so the principle that [...] only evaluates necessary subexpressions is not
ironclad.

The problem with your example is that the following does not error:

SELECT CASE WHEN false THEN 1/0 ELSE 1 END;

which is where I threw up my hands and realized I do not know
enough...though a more complete example will fail:

WITH tbl (v) AS ( VALUES (1),(2),(3) )
SELECT CASE WHEN v > 0 THEN v ELSE 1/0 END
FROM tbl;

Some observations/suggestions:

While it is a great word I don't really believe "panacea" is good for our
documentation given the diverse English language experiences of its readers.

"the values of function parameters" == "function arguments" ... though not
everyone is fully aware (or remembers) the difference between arguments and
parameters.

"Thus for example in [...] functions..." -> Therefore, in [...] functions
it is more reliable to use an [...] statement to protect a risky [...]
statement compared to using a nested CASE expression.

David J.
​