Re: BUG #12273: CASE Expression BUG

jaksits.tibor@gmail.com 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.

            regards, tom lane