Martijn van Oosterhout <kleptog@svana.org> writes:
> --f2QGlHpHGjS2mn6Y
> On Tue, Apr 11, 2006 at 11:22:53PM +0200, Andreas Tille wrote:
>> I'm lacking experience here so I perfectly trust you that keeping
>> the default case as it is. The question is, whether adding an
>> option to change the default might make sense.
> Can you give an example of a simple case where PostgreSQL doesn't do
> this already.
I think we might be confusing each other with varying meanings for the
word "case" ;-). The facts as I see them are:
1. The CASE expression does indeed not evaluate unneeded subexpressions.
2. However, aggregate functions are evaluated in a separate pass before
we start to evaluate the SELECT's output list (or the HAVING clause if
any). So you cannot use a CASE to suppress evaluation of an aggregate's
finalfunc ... much less its state transition function.
3. There are other situations where a CASE might "not work" to suppress
contained evaluations. For instance, this example is pretty misleading:
> test=3D# select case when true then 5 else 1/0 end;
> case=20
> ------
> 5
> (1 row)
> test=3D# select case when false then 5 else 1/0 end;
> ERROR: division by zero
A counterexample is:
regression=# select f1, case when true then 5 else 1/0 end from int4_tbl;
f1 | case
-------------+------
0 | 5
123456 | 5
-123456 | 5
2147483647 | 5
-2147483647 | 5
(5 rows)
regression=# select f1, case when f1 <> 42 then 5 else 1/0 end from int4_tbl;
ERROR: division by zero
regression=#
The reason the latter fails is that constant-folding encounters the 1/0
before we actually start to run the SELECT. The first three examples
work only because the WHEN clause is a plan-time constant and so the
constant folder never reaches the ELSE clause.
I'm not really inclined to remove the constant folder just to make the
world safe for silly examples like this, so the bottom line is that you
have to be aware of there being multiple passes of evaluation.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
