Re: [BUGS] BUG #8237: CASE Expression - Order of expression processing
On 2013-06-18 23:30:44 -0400, Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: On 2013-06-18 13:17:14 +, and...@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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] 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: and...@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+pfsf...@mail.gmail.com ) also seems to indicate that this is a bug. At least it is a discrepancy between documentation and behaviour. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #8237: CASE Expression - Order of expression processing
On 2013-06-18 13:17:14 +, and...@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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #8237: CASE Expression - Order of expression processing
2013/6/18 Andres Freund and...@2ndquadrant.com: On 2013-06-18 13:17:14 +, and...@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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #8237: CASE Expression - Order of expression processing
Andres Freund and...@2ndquadrant.com writes: On 2013-06-18 13:17:14 +, and...@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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs