Re: BUG #8237: CASE Expression - Order of expression processing

On 2013-06-18 23:30:44 -0400, Tom Lane wrote:
> Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> > On 2013-06-18 13:17:14 +0000, andrea(at)lombardoni(dot)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