Re: Coalesce bug ?

> -----Original Message-----
> From: Adrian Klaver [mailto:adrian(dot)klaver(at)gmail(dot)com]
> Sent: Friday, December 21, 2012 10:57 AM
> To: David Johnston
> Cc: 'jg'; pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] Coalesce bug ?
>
> On 12/21/2012 07:49 AM, David Johnston wrote:
> >> -----Original Message-----
>
> >>
> >> 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 second one should not be executed. Also, the Query
> Plan shown "never executed" the second scalar-sub-SELECT (from the same
> query 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 is no lazy instantiation in SQL.
> Both "SELECT" statements because they have to be run before the COALESCE
> function call be evaluated. Whether the ps3(?) function has to be executed
> then only depends on whether enough information exists in memory to
> optimize the call away.
>
> I am just going by the docs and what my observations have been:)
>
> http://www.postgresql.org/docs/9.2/interactive/functions-
> conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL
>
> "Like a CASE expression, COALESCE only evaluates the arguments that are
> needed to determine the result; that is, arguments to the right of the first
> non-null argument are not evaluated. "
>

Which is how I thought things to work but per that the following should be equivalent:

select coalesce( (select ps3(1)), (SELECT ps3(2)) );

select coalesce( ps3(1), ps3(2) );

Neither of these should EVER result in the "ps3(2)" function call being evaluated...regardless of the mutability modifier.

I guess the addition of SELECT and/or () to the first expression is having an impact but I have no idea where to even look in the documentation for where that difference would be defined.

David J.