Search Postgresql Archives

Re: Coalesce bug ?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



> -----Original Message-----
> From: Adrian Klaver [mailto:adrian.klaver@xxxxxxxxx]
> Sent: Friday, December 21, 2012 10:57 AM
> To: David Johnston
> Cc: 'jg'; pgsql-general@xxxxxxxxxxxxxx
> Subject: Re:  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.




-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux