From: | "David Johnston" <polobo(at)yahoo(dot)com> |
---|---|
To: | "'Adrian Klaver'" <adrian(dot)klaver(at)gmail(dot)com> |
Cc: | "'jg'" <jg(at)rilk(dot)com>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Coalesce bug ? |
Date: | 2012-12-21 16:20:02 |
Message-ID: | 009701cddf97$081d3500$18579f00$@yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> -----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.
From | Date | Subject | |
---|---|---|---|
Next Message | ERR ORR | 2012-12-21 16:21:59 | Question about indexes and operator classes |
Previous Message | Tom Lane | 2012-12-21 16:15:53 | Re: Coalesce bug ? |