Re: BUG #12273: CASE Expression BUG

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

> David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > Tom Lane-2 wrote
> >> We're unlikely to change this, because it would cripple optimization
> >> attempts. The fact that const-simplification doesn't happen in the
> other
> >> way you wrote the function is not more-correct behavior, it's just an
> >> implementation artifact that you shouldn't rely on. What you need to do
> >> is code this as an if-then-else sequence, not CASE, so that you don't
> >> attempt to evaluate any expressions with undefined constant
> >> subexpressions.
>
> > Note that this is documented here:
> >
> http://www.postgresql.org/docs/9.4/interactive/functions-conditional.html#FUNCTIONS-CASE
> > Specifically, the "Note" at the end of 9.17.1
>
> 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.

The aggregate expression limitation exists because aggregate expressions
[continue as-is...]

The planner optimization limitation exists because [i'm not sure what to
write here...]

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? The fact
that it is a constant doesn't seem to be enough. Is it only because this
was attempted in pl/pgsql - which has unique planning mechanics compared to
SQL (functions and/or raw) - and should, probably also, be addressed there
(though not sure where you'd put it...).

The answer you provided basically resolved to: avoid the in-query SQL CASE
and instead use a pl/pgsql IF to perform the conditional. It didn't matter
for this inquiry but the fact that both SQL and pl/pgsql have - differently
behaving - CASE expressions/statements may factor into any explanation.

David J.

​