Обсуждение: BUG #8237: CASE Expression - Order of expression processing
The following bug has been logged on the website:
Bug reference: 8237
Logged by: Andrea Lombardoni
Email address: andrea@lombardoni.ch
PostgreSQL version: 9.0.4
Operating system: Linux
Description:
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)
What I expect:
template1=# SELECT CASE WHEN (SELECT 0)=0 THEN 0 ELSE 1/0 END;
0
template1=# SELECT CASE WHEN (SELECT 1)=0 THEN 0 ELSE 1/0 END;
ERROR: division by zero
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."
The discussion on postgresql-general (
http://www.postgresql.org/message-id/CAMQ5dGq4SuJPbhT2-9XLAPAsvKNUL2-bb0cPyci2Fp+pfSfc3g@mail.gmail.com
) also seems to indicate that this is a bug.
At least it is a discrepancy between documentation and behaviour.
On 2013-06-18 13:17:14 +0000, andrea@lombardoni.ch wrote: > 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 Hrmpf. This is rather annoying. Const simplification processes all clauses and evaluates them if it can. Which is - as demonstrated above - broken. The only reason #= SELECT CASE WHEN 1=1 THEN 0 ELSE 1/0 END; works is that we abort even looking at further WHEN clauses if we know that one WHEN succeeds. So it seems we need to stop processing after finding a single WHEN that's not const? Does anybody have a better idea? Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
2013/6/18 Andres Freund <andres@2ndquadrant.com>: > On 2013-06-18 13:17:14 +0000, andrea@lombardoni.ch wrote: >> 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 > > Hrmpf. This is rather annoying. Const simplification processes all > clauses and evaluates them if it can. Which is - as demonstrated above - > broken. The only reason > #= SELECT CASE WHEN 1=1 THEN 0 ELSE 1/0 END; > works is that we abort even looking at further WHEN clauses if we know > that one WHEN succeeds. > So it seems we need to stop processing after finding a single WHEN > that's not const? Does anybody have a better idea? probably we should to evaluate constants under subtransaction, and after exception, we should to stop precalculation of related subexpression. But it is slow :( > > Greetings, > > Andres Freund > > -- > Andres Freund http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services > > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs
Andres Freund <andres@2ndquadrant.com> writes:
> On 2013-06-18 13:17:14 +0000, andrea@lombardoni.ch wrote:
>> template1=# SELECT CASE WHEN (SELECT 0)=0 THEN 0 ELSE 1/0 END;
>> ERROR: division by zero
> Hrmpf. This is rather annoying.
Annoying, maybe. Bug, no. The manual is pretty clear that you don't
have a lot of control over order of evaluation of subexpressions.
> So it seems we need to stop processing after finding a single WHEN
> that's not const?
That's not an acceptable "fix".
regards, tom lane
On 2013-06-18 23:30:44 -0400, Tom Lane wrote: > Andres Freund <andres@2ndquadrant.com> writes: > > On 2013-06-18 13:17:14 +0000, andrea@lombardoni.ch wrote: > >> template1=# SELECT CASE WHEN (SELECT 0)=0 THEN 0 ELSE 1/0 END; > >> ERROR: division by zero > > > Hrmpf. This is rather annoying. > > Annoying, maybe. Bug, no. The manual is pretty clear that you don't > have a lot of control over order of evaluation of subexpressions. For normal clauses I absolutely grant you that. But for CASE? We've always argued that to be escape hatch when you need to force an order. And indeed http://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-EXPRESS-EVAL has the following example: "But this is safe: SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;" If the WHEN clause is independent from the arithmetic expression and the vars were replaced by, say query parameters, this will even crash. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services