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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
