Re: COALESCE implementation question

Philip Warner <pjw@rhyme.com.au> writes:
> I realize that the standard says:

>     2) COALESCE (V(1), V(2)) is equivalent to the following <case
>        specification> :
>            CASE WHEN V(1) IS NOT NULL THEN V(1) ELSE V(2) END

> I was wondering if there was a reason that we interpret this literally,
> rather than implement a function?

Well, the standard is perfectly clear, isn't it?  If V(1) has side
effects then trying to optimize this into just one evaluation of V(1)
will generate non-spec-compliant results.

I'd have to agree that two evaluations are pretty annoying, though,
and I wonder whether the spec authors *really* meant to demand
double evaluation of the "winning" case item.  Can anyone check
whether Oracle and other DBMSes perform double evaluation?

BTW, the "BETWEEN" expression has exactly the same issue.
        regards, tom lane