Обсуждение: CASE Statement - Order of expression processing

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?

with psql v9.2.4:

pg924=# SELECT CASE WHEN (SELECT 0) = 0 THEN 0 END;
  case
------
     0
(1 row)

is like documented.

pg924=#  SELECT CASE WHEN (SELECT 0) != 0 THEN 0 END;
  case
------

(1 row)

also like documented "If no match is found, the result of the ELSE
clause (or a null value) is returned."

pg924=#  SELECT CASE WHEN (SELECT 0) != 0 THEN 0 ELSE 1 END;
  case
------
     1
(1 row)

also ok, now it returns the result of the ELSE clause.

So maybe "The data types of all the result expressions must be
convertible to a single output type. See Section 10.5 for more details."
The checking of convertibility is eagerly tried in case there is a
SELECT expression to be evaluated in the condition?

A simple arithmetic expression does not trigger this:

pg924=#  SELECT CASE WHEN (0+0) != 0 THEN 1/0 ELSE 1 END;
  case
------
     1
(1 row)

Now is a subquery "(SELECT 1) != 1" a valid expression for a condition
:-?) or does it trigger some unwanted checking:

pg924=#  SELECT CASE WHEN (SELECT 1) != 1 THEN 1/0 END;
ERROR:  division by zero

A subquery inside a "matched" ELSE clause (e.g.) does not trigger
evaluation of the 1/0 inside the unmatched WHEN clause:

pg924=#  SELECT CASE WHEN 1 != 1 THEN 1/0 ELSE ((SELECT 1)=1)::integer END;
  case
------
     1
(1 row)

here the 1/0 is happily ignored.

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

All the best,
Stefan.

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

Andrea Lombardoni wrote:
> It gets even stranger:
> 
> template1=# SELECT CASE WHEN (SELECT 0)=0 THEN 0 ELSE 1/(select 0) END;
>  case
> ------
>     0
> (1 row)
> 
> Here it seems that the ELSE does not get evaluated (which is correct).

Yes, of course, because both subselects will not get evaluated at
planning time.

The problem occurs only if one of the branches gets evaluated at plan time
but the condition doesn't.

Yours,
Laurenz Albe

>> 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.

Just to keep you updated:

We have updated the documentation to alert people to this behaviour:
http://www.postgresql.org/docs/devel/static/functions-conditional.html#FUNCTIONS-CASE

There were considerations to change the behaviour, but
that would mean that query execution time suffers in
many cases.  It was decided that the problem occurs only
in rather artificial queries, and that it would not be worth
changing the normally useful behaviour of constant folding
during query planning.

Yours,
Laurenz Albe