Re: Examples of SQL anomalies?

From: David Cressey <cressey73_at_verizon.net>
Date: Fri, 04 Jul 2008 21:45:51 GMT
Message-ID: <jSwbk.249$rb1.205_at_trndny08>


"Brian Selzer" <brian_at_selzer-software.com> wrote in message news:zOvbk.31378$ZE5.24693_at_nlpi061.nbdc.sbc.com...
>
> "David Cressey" <cressey73_at_verizon.net> wrote in message
> news:D8ubk.240$rb1.211_at_trndny08...
> >
> > "Brian Selzer" <brian_at_selzer-software.com> wrote in message
> > news:gI6bk.11000$cW3.8591_at_nlpi064.nbdc.sbc.com...
> >>
> >> "David Cressey" <cressey73_at_verizon.net> wrote in message
> >> news:wq4bk.28$0V1.10_at_trndny01...
> >> >
> >> > "Brian Selzer" <brian_at_selzer-software.com> wrote in message
> >> > news:nFD9k.5753$LG4.2422_at_nlpi065.nbdc.sbc.com...
> >> >>
> >> >> "-CELKO-" <jcelko212_at_earthlink.net> wrote in message
> >> >>
> >
news:f219a6bd-9d8e-4cfe-9d60-ce9dcaeff16d_at_z66g2000hsc.googlegroups.com...
> >> >> >>> The question is, if these issues are due to the SQL
specification
> > or
> >> >> >>> simply due to a problem in a specific SQL product. Or could it
be,
> >> > that
> >> >> >>> the definition is not precise enough in some points, so that
> > database
> >> >> >>> vendors implemented it differently? <<
> >> >> >
> >> >> > Nope, it is the specs. All aggregate (set) functions begin by
> >> >> > removing the NULLs from their parameter set, then if there is a
> >> >> > DISTINCT option on the parameter, they remove redundant duplicates
> > and
> >> >> > finally do the operation (MIN, MAX, AVG, SUM, COUNT on what is
left.
> >> >> > Since an empty set has no elements upon which to apply an
operation,
> >> >> > SQL returns a NULL (okay, it should be an "undefined" if we were
> >> >> > mathematically correct).
> >> >> >
> >> >>
> >> >> MIN, MAX and AVG are meaningless when applied to an empty bag, but
it
> >> > seems
> >> >> to me that COUNT should always return 0 when the bag is empty, and
> >> >> similarly, SUM should return 0. SUM should only return NULL if one
of
> >> >> the
> >> >> values to be summed is NULL.
> >> >>
> >> >
> >> > By definition, none of the values to be summed are NULL.
> >> >
> >>
> >> OK. OK. Yes, I know. NULL is not a value. I guess to be precise, I
> > should
> >> have said "if the cardinality of the set of rows targeted by the query
is
> >> greater than the number of values to be summed, then SUM should return
> >> NULL," but I think that would have caused more confusion. Bottom line:
> >> if
> >> not all of the amounts are known, then the total amount is suspect.
> >>
> >
> > This has to do with a continuing disagreement between you and me. In my
> > view there is no such thing as the "set of rows targeted by the query".
> > There is a set of data targeted by the query. That is all. Rows that
do
> > not contain any data with regard to the current query are not part of
the
> > target, by definition.
> >
> > Thus, sum (SALARY) from FUBAR is the sum of a bag of salaries. It's
not
> > the sum of a bag of rows from FUBAR. Rows in FUBAR that don't contain a
> > SALARY are not part of the sum. That is all.
> >
>
> What about sum (SALARY) from FUBAR where Fu = 'Bar'? Doesn't "where Fu =
> 'Bar'" restrict the query to the result of evaluating "where Fu = 'Bar'"?
> Clearly if COUNT(*) where Fu = 'Bar' is greater than COUNT(*) where Fu =
> 'Bar' and SALARY is not NULL, then it is obvious that some salaries aren't
> known, hence the total of those salaries, sum (SALARY) where Fu = 'Bar' is
> suspect.
>
> I can't understand that you don't get this since it is just so simple.
>

It is simple, I do get it, and you are wrong. Received on Fri Jul 04 2008 - 23:45:51 CEST

Original text of this message