Re: Examples of SQL anomalies?

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

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.

Now try these statements:

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; --empty

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,