Jay, I thank you for an inciteful and well considered position piece, and I respond in-line below ... (sorry for the length, but I found it difficult to excise much of the excellent description that you wrote).
On Mon, 10 Nov 2008, Jay A. Kreibich wrote: [regretfully elided owing to space ...] > 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. Which harkens to my argument, to be able to *CONSISTENTLY* ascertain whether the datum contains a 'value', within the usable domain of the data type, and the corresponding sqlite3_column_xxx() function will return it, or it does not (eg: isNULL). It is counter intuitive (to me) that the 'type' of a datum would transmogrify simply owing to the fact that it was not previously initialized, or indeed, was computed as an undefined operation (eg: div by zero). Under normal circumstances, (ie: in other dbms systems, not SQLite) columns do not change type, and must defined within their domains, but within each domain, there exists a value which denotes a 'representation outside the domain'. Some models do this better than others, but the relational model only has NULL. > > 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. Arguable, yes. The thing about the 'blob' which makes it special, is that the onus is put upon the user to classify the domain of a blob. Is it an image? if so what type? if not then what? how do I enter/modify/retrieve/delete it? I think the varchar or integer/NULL vs Blob/NULL argument is not quite the same argument, but I will grant you for the sake of argument, there are certain similarities. > > 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. Of course, I see this, and I would never argue for a different approach than manifest typing for SQLite ... it has many advantages, and really lends itself to the scripting approach (tcl/tk) from whence it seems to have been derived. I like tcl/tk almost as much as I like SQLite!!. That being said, I understand that with each sqlite3_step() of a prepared query, and as one would expect -- the data values will change, and in SQLite, so also may the type, so a combination of sqlite3_column_xxx() and sqlite3_column_type() will give me what I want -- either the actual value, or an indication that a NULL value/type/thingy would be returned. > > > 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 I suppose my problem, and I don't believe that I really have one, arises more because the software I'm writing is database agnostic, and will eventually run on PostGreSQL/MySQL/Firebird? and possibly commercial offerings as well. Moreover, I am implementing a rich data dictionary on top of the underlying databases, so ultimately this problem quickly disappears once past the low level dbms driver ... I have no problem with SQLite per se, nor with its approach to typing, nor with the existing SQLite API ... but I *DID* have a couple of unexpected stumbles in translating documentation into practice owing to the differences between "my mental model", as you put it, and SQLite's consistent handling and "orthogonal" API. In this regard, and given your very excellent defense of SQLite vis a vis manifest typing and NULL handling, would it really break either the consistent handling of NULL, or SQLite's manifest typing to add sqlite3_column_isNULL() to the API?? It would have certainly simplified life for me, at what I believe to be little to no cost with respect to consistency -- even for the purists. Sincerely, Rob Sciuk _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
