# Re: Examples of SQL anomalies?

Date: Tue, 1 Jul 2008 09:26:45 -0700 (PDT)

Message-ID: <515d746c-7444-47e2-994c-cf37abf159e1_at_y38g2000hsy.googlegroups.com>

count = 0;

for each item

count = 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.

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 = MIN(my_set); -- exists in the data!
**REPEAT
**

SET my_set = my_set – {MIN(my_set)}; -- remove element from countable
set

SET total = total + MIN (my_set); -- add element that exists in data
to total

UNTIL my_set = {}; -- 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.

CREATE TABLE Empty – no rows possible

(i INTEGER NOT NULL

** CHECK (1=0));
**

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; --emptytable, 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 Received on Tue Jul 01 2008 - 18:26:45 CEST