Re: Examples of SQL anomalies?

From: -CELKO- <>
Date: Tue, 1 Jul 2008 09:26:45 -0700 (PDT)
Message-ID: <>

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

 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.

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.

RETURNS <numeric data type>
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.

Now try these statements:

CREATE TABLE Empty no rows possible
  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; --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:



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

Original text of this message