Re: missing information and aggregates

"Mikito Harakiri" <mikharakiri_at_ywho.com> wrote in message news:Wd36b.21$pP2.69_at_news.oracle.com...
>
> "Bob Badour" <bbadour_at_golden.net> wrote in message
> news:8rR5b.528$0o6.49213552_at_mantis.golden.net...
> > "Christopher Browne" <cbbrowne_at_acm.org> wrote in message
> > > The right Default Value for addition aggregates would be 0.
> > >
> > > The right Default Value for multiplicative aggregates would be 1.
> >
> > I think you are confusing default values with identity elements. 0 is
the
> > identity element for addition and 1 is the identity element for
> > multiplication.
>
> Identity elements are default values! It is obvious that aggregates should
> return non-null value at least in cases like these:
>
> select sum(sal) from emp where 1=0
>
> the correct result is 0, not the NULL (SQL standard?)

I agree that SQL got that one wrong. I find it strange, arbitrary and inconsistent that COUNT(*) returns 0 for zero rows but SUM(1) returns NULL;

> select multiply(sal) from emp where 1=0
>
> the correct result is 1, not the NULL

I agree with this too. I once had a discussion with Celko about this issue where he claimed the answer to these aggregates over zero rows is undefined, and he quoted a passage from _Concrete Mathematics_ by Graham, Knuth and Patashnik a page or two before the passage that explicitly states the answer is defined as the identity element.

> select max(sal) from emp where 1=0
>
> the correct result is minus infinity, not the NULL

I would accept the minimum value of the data type, which is a close enough approximation to minus infinity. The other option is an underflow exception. Received on Sat Sep 06 2003 - 04:09:38 CEST