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
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
