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. "

>
> David J.
>
>
>
>

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