Re: postgres - CASE evaluates subexpression that is not needed to determine the result

Hi

po 5. 11. 2018 v 14:37 odesílatel Kamenoqd Zelen <kamenoqd(at)gmail(dot)com>
napsal:

> Hello,
> I have the following 3 examples of case expressions in postgres, which I
> would expect to evaluate in the same way. However the first and the third
> give ERROR: invalid input syntax for integer: "2017.7". The second one
> returns true. Why is the difference?
>

I am not sure, but it looks so there is a problem in simplification in
planning time. Any expression used in SQL query is checked and if it is
possible - then it is evaluated in planning time. This evaluation is
limited to some deterministic operations over constant. On my comp doesn't
work only first when the constant should be casted to bigint. When you try
to assign empty string, then this simplification is not possible - and
because the expression is not evaluated in running time, then you got
expected result.

It is not a bug. This is side effect of expression simplification at parser
time.

Regards

Pavel

> Postgres documentation
> <https://www.postgresql.org/docs/7.4/static/functions-conditional.html>
> states:
>
> "A CASE expression does not evaluate any subexpressions that are not
> needed to determine the result."
>
> select case when 0 = 0 then 1 < 2
>
> when 0 = 2 then 2000 = ('2017.7')::bigint
>
> end;
>
>
> select case when 0 = 0 then 1 < 2
>
> when 0 = 2 then 2000 = ('2017.7'||'')::bigint
>
> end;
>
>
> select case when (array[1,2])[1] =1 then 1 < 2
>
> when (array[1,2])[1] = 2 then 2000 = ('2017.7'||'')::bigint
>
> end;
>