Re: Examples of SQL anomalies?

"Philipp Post" <Post.Philipp_at_googlemail.com> wrote in message news:46510d64-58cd-4e33-b09a-344da92f5221_at_p25g2000hsf.googlegroups.com...
>b) 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...

I disagree. If NULL meant "there is a value for this entry, but we don't know what it is" then your interpretation might be correct. However, if NULL means "there is no value for this entry" then excluding it from consideration is the only sensible approach.

Consider the missing row. The missing row is a row that should have been inserted but, due to some screwup in the application or the people supplying the data, it was never inserted. I'm sure you'll agree that, in this case, the only sane thing to do with SUM is to leave the missing row out of the SUM. And surely the only sane thing to do with AVG is to leave the missing row out of both the numerator and the denominator of the fraction that results in AVG.

Well a NULL, properly construed, is a row that is partly not there.

I realize there are a lot of experts who disagree with my opinion here, but I'll stand by it. Received on Wed Jul 02 2008 - 20:39:48 CEST