Re: Coalesce bug ?

On 12/21/2012 07:03 AM, jg wrote:
> Hi,
>
> In PostgreSQL 9.2, I have the following behavior, and I found it strange.
>
> ps3 is executed or "never executed" ? !!!
>
> JG
>
> [postgres(at)]test=# create or replace function ps3(a int) returns int as $$ BEGIN
> RAISE WARNING 'Call ps3(%)=%',$1,$1; RETURN $1::int;
> END; $$ LANGUAGE plpgsql STRICT IMMUTABLE;
> CREATE FUNCTION
> Temps : 22,632 ms
> [postgres(at)]test=# select coalesce( (select ps3(1)), (SELECT ps3(2)) );
> WARNING: Call ps3(1)=1
> WARNING: Call ps3(2)=2
> coalesce
> ----------
> 1
> (1 ligne)
>
> Temps : 0,692 ms
> [postgres(at)]test=# select coalesce( ps3(1), ps3(2) );
> WARNING: Call ps3(1)=1
> coalesce
> ----------
> 1
> (1 ligne)
>
> Temps : 0,441 ms

Well I believe there is no bug.

In the first case the nested parentheses mean the SELECT statements are
run first and the COALESCE is run on the return results.

In the second case COALESCE is working as advertised. Working left to
right it finds the first non NULL argument and stops.

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com