Re: CASE Statement - Order of expression processing - Mailing list pgsql-general

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.