Re: COALESCE implementation question

At 22:36 5/08/00 -0400, Tom Lane wrote:
>Philip Warner <pjw(at)rhyme(dot)com(dot)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.

At least with the new function manager, if I feel te need I can write a
'CoalesceValues' function (at least for fixed numbers of parameters).

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

It's very hard to believe that is what they meant, or even if they even
considered the ramifications of their proposed implementation (I'm not
really sure why they chose to describe the implementation and specifically
to implement a 'function' as a case statement). eg. the result of the first
execution *could* mean that the second execution returns NULL - fine for
CASE, lousy for COALESCE. In fact it's pretty easy to write a function that
causes COALESCE(f(), 1) to return NULL...

Sadly, my usual yard stick (Dec/RDB) seems to evaluate twice (at least
that's what it's planner says). And dumping a view with a coalesce
statement produces a CASE statement, so it probably has no choice.

Just seems daft to me.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/