Path: text.usenetserver.com!out02a.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!y38g2000hsy.googlegroups.com!not-for-mail
From: -CELKO- <jcelko212@earthlink.net>
Newsgroups: comp.databases.theory
Subject: Re: Examples of SQL anomalies?
Date: Tue, 1 Jul 2008 09:26:45 -0700 (PDT)
Organization: http://groups.google.com
Lines: 73
Message-ID: <515d746c-7444-47e2-994c-cf37abf159e1@y38g2000hsy.googlegroups.com>
References: <g3vlk2$6nf$1@aioe.org> <r24864l3hq7evilchjblbdm00hgvmhle0r@4ax.com> 
 <48641b7e$0$4066$9a566e8b@news.aliant.net> <5df510ce-9cbd-4260-b5bc-1ac8ba0b17ba@e39g2000hsf.googlegroups.com> 
 <f219a6bd-9d8e-4cfe-9d60-ce9dcaeff16d@z66g2000hsc.googlegroups.com> 
 <nFD9k.5753$LG4.2422@nlpi065.nbdc.sbc.com> <2871ffa7-4243-49b7-9122-e4e5b2c27443@a1g2000hsb.googlegroups.com> 
 <rL8ak.30856$ZE5.3248@nlpi061.nbdc.sbc.com> <e666b562-eddf-4647-af60-938a3222eeb1@j33g2000pri.googlegroups.com> 
 <48696737$0$4061$9a566e8b@news.aliant.net> <gnfak.66458$gc5.5425@pd7urf2no> 
 <23f5e158-3136-4bb9-8ef8-380c89f5cef2@w34g2000prm.googlegroups.com>
NNTP-Posting-Host: 207.71.25.66
Mime-Version: 1.0
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1214929605 22106 127.0.0.1 (1 Jul 2008 16:26:45 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Tue, 1 Jul 2008 16:26:45 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: y38g2000hsy.googlegroups.com; posting-host=207.71.25.66; 
 posting-account=eTE9_AoAAAD1dS9O9Ccywd_vfKFzS40A
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.11) 
 Gecko/20071127 Firefox/2.0.0.11,gzip(gfe),gzip(gfe)
Xref: usenetserver.com comp.databases.theory:171780
X-Received-Date: Tue, 01 Jul 2008 12:26:45 EDT (text.usenetserver.com)

>> Or one can even appeal to an imperative programmer's inherently procedura=
l bias, and ask him how he'd most simply program it.
 He'd say:

 count =3D 0;
 for each item
   count =3D count + 1
 end for

 And what does that evaluate to if there are no items? <<

Notice how you created zero from nothing?

Things can get really bad.  Your choices for defining a summation
indexed over an empty are to define it as zero to get rid of it or to
define it as undefined to get rid of it.  The important thing is to
get rid of it.

Another convention in traditional Sigma notation is that when the
initial value of an index is greater than the final value, the
summation is zero.  That is not a set-oriented approach to
summations.  Again, the idea that ordering changes the results is
strictly a convention.

What do you want to do about 0^0?   How would you define 0/0 without a
convention?  Instead of a FOR EACH loop, we might define summation
this way in pseudo-SQL.

CREATE FUNCTION Sum(my_set)
RETURNS <numeric data type>
AS
BEGIN
DECLARE total <numeric data type>;
SET total =3D MIN(my_set); -- exists in the data!
REPEAT
SET my_set =3D my_set =96 {MIN(my_set)}; -- remove element from countable
set
SET total =3D  total + MIN (my_set); -- add element that exists in data
to total
UNTIL my_set =3D {}; -- use all the elements
RETURN total;
END;

This handles a set with one element and we don't have to create a zero
out of thin air.  The loop removes the minimal element (could be max,
could be random) from the set and adds it to the running total until
the set is empty.  If the set starts empty, we get a NULL.

Now try these statements:

CREATE TABLE Empty =96 no rows possible
(i INTEGER NOT NULL
  CHECK (1=3D0));

SELECT SUM(i) FROM Empty;  -- returns  null
SELECT SUM(i) FROM Empty GROUP BY i; -- returns an empty table
SELECT SUM(i), COUNT(i), COUNT(*) FROM Empty GROUP BY i; --empty
table, 3 columns

So why is GROUP BY not returning a NULL or a zero?  That will give you
a headache!  A table always has at least one grouping, so there is
always at least one row.  Think of {{}}, a set whose element is the
empty set.  Let's keep going with another table that only has a NULL:

CREATE TABLE JustNulls
(i INTEGER CHECK (i IS NULL));

INSERT INTO JustNull VALUES (NULL);

SELECT SUM(i) FROM JustNull;  -- returns null
SELECT SUM(i) FROM JustNull GROUP BY i; -- returns null
SELECT SUM(i), COUNT(i), COUNT(*) FROM JustNull GROUP BY i; -- null,
0, 1