Scott Hess wrote: > On Tue, Oct 27, 2009 at 9:35 PM, John Crenshaw <johncrens...@priacta.com> > wrote: >> Meh, I don't want it THAT badly. I'm just saying that's how it should >> have been in the original design of the SQL language. In fact though, it >> probably wouldn't have mattered. Every different RDBMS seems to treat >> nulls differently in this regard, so I'm not even sure exactly what the >> spec says on the issue (most likely, SQLite is exactly in line with the >> spec, but I haven't bothered to confirm that.) > > The problem is that you only have one concept of NULL, but any > particular case where something is NULL could come from a different > source than another case. Think of NaN in numerics. You probably > don't want the NaN from 0/0 to compare equal to the NaN from sqrt(-1), > but expressing what you do want to do for each possible case of NaN is > hard and prone to mistake. > > While an implementation must represent NULL as a value, NULL is not a > value. Saying "5 <> NULL" is not a sensible statement, because 5 is > as equal to NULL as it is unequal to NULL, or greater than NULL, or > less than NULL. You can't compare them, because one is a scalar > value, and the other is absence of a value. You're doing a > divide-by-zero, and syntactic sugar will not resolve that. > > Check out http://en.wikipedia.org/wiki/Null_(SQL)#Criticisms and just > be glad you don't have to deal with two or three distinct flavors of > NULL!
Actually, the better thing to do is to eliminate the generic NULL concept entirely and use other solutions for the features it provided. And make everything work with simple 2-valued logic, where every "=" or "<>" returns just true or false. For example, just have a different distinct marker, which compares equal to itself but unequal to any others, for each distinct reason why you don't have a normal value. Conceptually each marker would be a singleton type, or alternately they collectively could be values of an enumerated type, or several enumerated types. Of course it would need to be possible for users to define such values, since many reasons why you'd have a "not applicable" versus an "unknown" or whatever NULL is used for, differs per user. The system can pre-define values for all the common suspects, such as one for divide-by-zero and one for every other reason you might have a NaN, as well as generic ones that could be most appropriate as a fill value for an outer join, for example, or generic "not applicable" and "unknown" that users can use when they don't want to define a custom reason. In such a system, comparing a normal value against any of the markers results in false, and any expression "X = X" always results in true, and generic tests like is-null or is-not-null are replaced with type membership tests, such as is-integer or is-not-text, etc. With a DBMS like SQLite, strictly speaking no extra types are needed; existing normal numeric or string etc types can be used, same as numeric is reused to represent booleans, and the meaning is distinguished by context, same as you know whether a number you hold represents a weight in kilograms or a count of bananas, even though intrinsically its just a number. This also means that for the most part users can simply use SQLite this way without SQLite having to be changed at all, and they just try to ignore NULLs where possible, just being explicit on how they mark special values. (Or if SQLite were changed, it could just be to add syntax for example letting users specify how otherwise-NULL fields from an outer-join are filled.) SQL doesn't do that, but that doesn't mean it can't or oughtn't be done in an RDBMS. And no, I'm not talking about stupid things like saying the number 99 represents the infinitely far future, nor am I talking about 1000-valued-logic (or Codd's circa 17-valued-logic). Use your imagination; there are many ways to do what I propose that are both elegant, easy to understand, and pragmatic. -- Darren Duncan _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
