Re: BUG #12273: CASE Expression BUG

Tom Lane-2 wrote
> jaksits.tibor@

> writes:
>> CREATE OR REPLACE FUNCTION __is_numeric_test_does_not_work(a_text_param
>> text)
>> RETURNS double precision AS
>> $BODY$DECLARE
>> ret double precision;
>> BEGIN
>> SELECT __is_numeric_test
>> INTO ret
>> FROM __is_numeric_test((CASE WHEN is_numeric(a_text_param) THEN
>> a_text_param::double precision ELSE 0.0::double precision END));
>> RETURN ret;
>> END;$BODY$
>> LANGUAGE plpgsql VOLATILE;
>
>> But the query "SELECT * FROM __is_numeric_test_does_not_work('')" I get
>> an
>> error message:
>> invalid input syntax for type double precision: ""
>
> You didn't show us what is_numeric() is, so it's impossible to reproduce
> this example, but I imagine what is happening is that the value of
> a_text_param is being substituted into the SELECT as a text constant, and
> then constant-folding leads to attempting to simplify a_text_param::double
> precision immediately.
>
> 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

Maybe an example would make this got-cha more memorable but it is noted in
the docs right next to the spot where it is described that CASE evaluation
does short-circuit during execution - just not always during planning.

David J.

--
View this message in context: http://postgresql.nabble.com/BUG-12273-CASE-Expression-BUG-tp5831307p5831325.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.