On Sat, Jun 30, 2007 at 00:15:42 -0400, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > "Andrej Ricnik-Bay" <andrej.groups@xxxxxxxxx> writes: > > On 6/30/07, Bruno Wolff III <bruno@xxxxxxxx> wrote: > >> I was recently doing some stuff with greatest() on oracle (9.2.0.8.0) and > >> noticed that it returned null if ANY of the arguments were null. Out of > >> curiosity I checked postgres' definition of that function and found that it > >> returns null only if ALL of the arguments are null. > > > W/o knowing the SQL standard (just from what I'd perceive > > as sensible) I'd say Oracle is broken. :} > > Hmm ... I fear Oracle's behavior is more correct, because if any > argument is null (ie, unknown), then who can say what the greatest or > least value is? It's unknown (ie, null). But I suspect our behavior > is more useful. Comments? In my case I would have prefered Postgres' behavior. I wanted to take the max of values coming from two columns by taking the greatest of two subselects. I ended up rewriting the query to take the max of a union. The annoying thing was I didn't have a good way to use coalesce as I wanted to get a null if both subselects were empty. Also what value should I have used in a coalesce to guaranty still getting the maximum? I think having it work like aggregates and ignoring null values is more convenient. However if the feature was added for oracle compatibility then not working the same is an issue. I was just hoping that perhaps the fact that the semantics are different between oracle and postgres would get noted somewhere so people porting would have a better chance to become aware of the issue.
