CASE Statement - Order of expression processing
От | Andrea Lombardoni |
---|---|
Тема | CASE Statement - Order of expression processing |
Дата | |
Msg-id | CAMQ5dGq4SuJPbhT2-9XLAPAsvKNUL2-bb0cPyci2Fp+pfSfc3g@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: CASE Statement - Order of expression processing
|
Список | pgsql-general |
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.
$ 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?
Thanks,
Andrea Lombardoni
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?
Thanks,
Andrea Lombardoni
В списке pgsql-general по дате отправления: