postgres - CASE evaluates subexpression that is not needed to determine the result
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?
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;
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
Show quoted text
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;
Kamenoqd Zelen <kamenoqd@gmail.com> writes:
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?
Constant subexpressions get folded regardless of location.
regards, tom lane