On Sunday, 25 April 1999 2:01, Tom Lane [SMTP:tgl@sss.pgh.pa.us] wrote:
> If you can demonstrate that this behavior [NULL result from aggregate
> functions] is not compliant with the SQL92 standard, then the Postgres
> backend will get fixed.
>
> There's been discussion of this point before on the hackers list,
> with some people feeling that the current behavior is OK and others
> not happy with it, but so far no one has made a convincing case about
> what the standard expects.
It seems that the standard is quite ambiguous as to what it expects and doesn't
address the limit case (what happens for an aggregate function result when a
table is empty). My SQL Programmer's Reference (ISBN 1-56604-760-9) has
nothing to say on this matter and I haven't successfully found a copy of the
SQL92 specifications (seems harder than tracking down a printed copy of
"Historia Regum Britanniae"). I suspect that the SQL specifications may also
overlook the issue as well. *sigh*
Upon reflection on possible scenarios, it seems that the aggregates should
return a row with a null value (but I'm not wholly certain on this). In SQL
the following is valid:
select column1, column2, column3from table twhere column3 = (select max(column3) from table where column2 =
t.column2)
Should the nested select have no max value (being an empty result), then
perhaps a null is required to be returned such that the comparison for the
outer select may still be made. I had overlooked this possibility. With it
coming to light, it appears we should have no problems with the current
implementation as this example seems to indirectly point to a null value return
being a valid result. Someone may wish to check my logic on this.
My apologies for ever doubting that things were not working correctly. :)
Best regards,
JonB.
