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