Re: Coalesce bug ? - Mailing list pgsql-general

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Adrian Klaver
> Sent: Friday, December 21, 2012 10:27 AM
> To: jg
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] 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@]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@]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@]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.

The first case is:

SELECT COALESCE( (SELECT), (SELECT) );  I am not sure exactly what the parentheses surrounding the scalar-sub-SELECTs
do(turn them into anonymously typed rows?) but if the first scalar-sub-select results in a non-null result then the
secondone should not be executed.  Also, the Query Plan shown "never executed" the second scalar-sub-SELECT (from the
samequery form) yet it knows that the result of the second call was "OUTPUT: 2" 

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

I thought that in order to call the Coalesce function the system would have to know the value of all parameters.  There
isno lazy instantiation in SQL.  Both "SELECT" statements because they have to be run before the COALESCE function call
beevaluated.  Whether the ps3(?) function has to be executed then only depends on whether enough information exists in
memoryto optimize the call away. 

David J.