Re: Suboptimal evaluation of CASE expressions

Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> The problem in your example is that you're using aggrgates in the case
> statement.

Yeah. The aggregate results are all computed before we start to evaluate
the SELECT output list --- the fact that the aggregate is referenced
within a CASE doesn't save you if the aggregate's finalfunc fails.

We could maybe change things so that the finalfunc isn't run unless the
result value is actually demanded in the SELECT list or HAVING clause,
but for 99.99% of applications checking that would be a waste of cycles,
so I'm disinclined to do it. As Martijn said, really you want to fix
the finalfunc so that it behaves sanely in corner cases. An aggregate
that fails on zero rows needs work, period.

regards, tom lane