Re: CASE Statement - Order of expression processing

Stefan Drees wrote:

> On 2013-06-17 22:17 +02:00, Andrea Lombardoni wrote:
>> I observed the following behaviour (I tested the following statements in
>> 9.0.4, 9.0.5 and 9.3beta1):
>>
>> $ psql template1
>> template1=# SELECT CASE WHEN 0=0 THEN 0 ELSE 1/0 END;
>> case
>> ------
>> 0
>> (1 row)
>>
>> template1=# SELECT CASE WHEN 1=0 THEN 0 ELSE 1/0 END;
>> ERROR: division by zero
>>
>> In this case the CASE behaves as expected.
>>
>> But in the following expression:
>>
>> template1=# SELECT CASE WHEN (SELECT 0)=0 THEN 0 ELSE 1/0 END;
>> ERROR: division by zero
>>
>> (Just to be sure, a "SELECT (SELECT 0)=0;" returns true)
>>
>> It seems that when the "CASE WHEN expression" is a query, the evaluation
>> order changes.
>> According to the documentation, this behaviour is wrong.
>>
>> http://www.postgresql.org/docs/9.0/static/sql-expressions.html (4.2.13.
>> Expression Evaluation Rules):
>> "When it is essential to force evaluation order, a CASE construct (see
>> Section 9.16) can be used. "
>>
>> http://www.postgresql.org/docs/9.0/static/functions-conditional.html
>> (9.16.1. CASE):
>> "If the condition's result is true, the value of the CASE expression is
>> the result that follows the condition, and the remainder of the CASE
>> expression is not processed."
>> "A CASE expression does not evaluate any subexpressions that are not
>> needed to determine the result."
>>
>> Did I miss anything? Or is this really a bug?

> So it's us two already with a blind spot, or it's a bug.

The problem is that "0=0" is evaluated and known as true during query planning,
so the ELSE branch is not even planned.

"(SELECT 0) = 0" will get evaluated during query execution, so the ELSE
branch is planned. The constant expression "1/0" is evaluated during
planning and leads to the error immediately, before the condition is
even evaluated.

As an illustration, look at the output of
EXPLAIN (VERBOSE) SELECT CASE WHEN (SELECT 0)=0 THEN 1 ELSE 60/5 END;

I'd concur that this is a bug since it contradicts the documentation
and is surprising (I could not find anything in the Standard that
says that CASE statements need to short-circuit).

It would also lead to IMMUTABLE functions in the ELSE branch
being evaluated.

If possible, I think the fix should be to not evaluate constant
expressions in the branches at plan time unless the condition is constant.

Yours,
Laurenz Albe