Re: postgres - CASE evaluates subexpression that is not needed todetermine the result
| От | Pavel Stehule |
|---|---|
| Тема | Re: postgres - CASE evaluates subexpression that is not needed todetermine the result |
| Дата | |
| Msg-id | CAFj8pRAs4VmJyfAHzG2CJAzpZYbnwgvTSjWQaPw+azTPAffh+w@mail.gmail.com обсуждение исходный текст |
| Ответ на | postgres - CASE evaluates subexpression that is not needed todetermine the result (Kamenoqd Zelen <kamenoqd@gmail.com>) |
| Список | pgsql-bugs |
Hi
po 5. 11. 2018 v 14:37 odesílatel Kamenoqd Zelen <kamenoqd@gmail.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 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;
В списке pgsql-bugs по дате отправления: