Re: Examples of SQL anomalies?

From: Philipp Post <Post.Philipp_at_googlemail.com>
Date: Sat, 28 Jun 2008 20:13:42 -0700 (PDT)
Message-ID: <46510d64-58cd-4e33-b09a-344da92f5221@p25g2000hsf.googlegroups.com>


> In SQL as in Set Theory, equality (=) and grouping are not the same; the SUM() and the + are  not the same.  They are for different levels of abstraction. <

Thanks for the hint, but the behaviour of the set functions indeed seems to be a bit strange (might also be I still miss something)

I will assume that the column can take no negative values to keep the example easier:

  1. 1 + NULL + 1 = NULL, because one element is unknown, I cannot know the result. That makes sense.
  2. SUM{1, NULL, 1} = 2, however I could just say that the result is AT LEAST 2 but the function cannot state it that precisely and as such it would make more sense if it would then throw an error like "You cannot apply this function on data containing NULL" what would force the developer to SELECT SUM(SomeAmount) .... WHERE NOT SomeAmount IS NULL. If this would make SQL programming more fun is a different question...

brgds

Philipp Post Received on Sat Jun 28 2008 - 22:13:42 CDT

Original text of this message