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