On Sat, Nov 08, 2008 at 09:48:10PM -0500, Rob Sciuk scratched on the wall: > Thanks, Igor, but I'm not looking for the type (either declared or > affined), but rather whether the actual value is valid, or NULL. That was > the point of the original post. My problems arose from the fact that it > is the sqlite3_column_type() function which returns the fact that the > current value is NULL, and this is somewhat counterintuitive. > > In my books, the data type is the type, and the data value is either NULL, > or has a value in the domain of that data type.
This might be where things are getting a bit confused. Under the Relational Model, "NULL" is not a value. As you point out, it is not inside any type domain. In fact, the whole point of NULL is a special "marker" of sorts that is specifically outside of any normal valid value domain. What exactly that marker means is open to academic debate (the "any value" vs. "unknown value" argument is a bit of a religious thing), but from a theory standpoint it very accepted that NULL is not a value in the traditional sense of an element inside a type domain. The actual SQL syntax is pretty good about recognizing this. As many a new SQL developer has found out (usually the hard way) SQL doesn't like the idea of "...WHERE id = NULL...". The SQL standard, on the other hand, gets a bit sloppy with this. Then again, so does any text on the subject of SQL data manipulation. It is very hard not to. If tasked with describing a statement like "UPDATE t set id = NULL..." to a general audience, avoiding the use of the words "NULL" and "value" in conjunction often makes for clumsy and difficult to understand prose. In my own writings on the subject I've often found myself using phrases like "a NULL assignment", but there are times when restructuring sentences to avoid the use of the phrase "NULL value" is just too cumbersome to the rest of the flow. That doesn't make it completely correct, however. Writing style aside, I've found SQLite's handling of NULLs as a unique data type with (essentially) and empty domain to be very consistent. In some ways, accepting this idea has allowed me to adjust my mental model of SQL and relational data systems in a way that's easier to accept NULL and all the weird syntax that goes with it. Something like the need for "...WHERE id IS NULL..." over "WHERE id = NULL..." is a bit easier to accept if you think of NULL as a type rather than a value. Then again, that's within the context of SQLite's Manifest Typing-- something that is hugely useful, but about as far outside of "proper" relational theory as you can get. The Manifest Typing system already requires SQLite to define all kinds of weird logic relations, like sort orders for different type domains, as well as values within a specific domain. Adding one more type to the mix that has some specific and unique interactions seems like a very clean way to deal with the uniqueness of NULLs. > One would not query the > data type to determine whether the value of the datum is defined or not. You would if the data container your asking about can hold anything from a string to an integer. While most of us tend to build databases where all the values of a given column are of the same type (especially if you've had prior database experience), SQLite doesn't require this. So think of it as asking not so much the type, but the domain from which this value came from, and if the datum is defined in that domain. > Of course, SQLite has a unique affinity model, and I'm still wrestling > with this, though in my gut, I feel that along with the > sqlite3_column_xxx() functions, should be a sqlite3_column_isNULL() > function. Once you get used to the Manifest Typing system, you'll realize that something like "sqlite3_column_isNULL()" isn't really all that different from something like "sqlite3_column_isBLOB()". And from that point, you're back to just asking for the "type" or domain of the column with the existing API. Chances are, you're just not used to asking questions like "is this a BLOB?" because you've never had to deal with a database that might offer different answers to that question for the same column. That's not good or bad... I've got a lot of different and sometimes conflicting opinions on Manifest Typing, although I have to admit that, at the end of the day, I think it is a positive point for SQLite and the market it is trying to serve. But that's a different can of worms. Most of this is related to the mental model each of us has about how SQL, relational systems, and our specific database needs fit together. I'm not trying to say your own model is "wrong", or that the SQLite way is the best way. I'm only trying to show that if you see things from a specific viewpoint, they're at least consistent. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
