pgsql-bugs@postgresql.org writes:
> In 7.2.1, Both the WHEN and THEN clauses of a CASE statement are evaluated, even if the WHEN clause evaluates to
FALSE.
Not in the normal case.
> SELECT
> CASE
> WHEN 1 = 2 THEN 1 / 0
> WHEN 1 = 1 THEN 1.0
> END;
> ERROR: floating point exception! The last floating point operation either exceeded legal ranges or was a divide by
zero
Hmm. The reason for this is that the constant-expression simplifier
reduces all the subexpressions of the CASE before it tries to discard
the ones with constant-FALSE preconditions. This particular example
could be fixed by rearranging the order of the simplification operations,
but you'd still see a failure with, say,
SELECT CASE WHEN boolCol THEN 1 / 0 END FROM table;
since 1/0 will be const-folded at planning time whether the table
contains any TRUE entries or not.
I don't really consider this a bug; at least, fixing it would imply not
const-simplifying the result expressions of CASEs, which is a cure far
worse than the disease IMHO. Does anyone think we *should* allow CASE
to defeat const-simplification? Are there any real-world cases (as
opposed to made-up examples) where this is necessary?
regards, tom lane