On Mon, May 5, 2014 at 4:15 PM, Jay Kreibich <[email protected]> wrote: > So far this whole discussion seems to boiled down to the fact that SQLite > doesn’t have a native Boolean type. That’s it. No, it doesn’t. Once we > accept that, everything else makes perfect sense based off existing > computer languages and historical ideas. Can we all move on? > > Again (not to you again, just again as in I wrote this a while ago and DRH commented similarly):
The whole discussion has had nothing to do with a lack of boolean type in SQLite. It has to do with the fact that apparently none of the common SQL engines will accept the statement "SELECT 1 FROM 1 - 1". If you go to sqlfiddle.com and type that expression in each of the SQL engines supported (three versions of MySQL, five of PostgreSQL, two MS SQL Server, and one Oracle [and a partridge in a pear tree], in addition to SQLite), none of the other engines will compile and execute the statement. Now, you may not think this is a problem. I don't see it as a problem myself, beyond the fact that it is a way that SQL implementations diverge and thus make it more difficult to write portable SQL code, but I'm not convinced that "portable" SQL code is as valuable as some people (probably because I don't have to support a large number of varying implementations). That being said, this has nothing to do with a boolean type or boolean logic or tri-state logic. The observation / complaint / criticism / whatever is that SQLite will compile and execute a statement that many / most / perhaps all other SQL engines will refuse to recognize as valid. With all that said and out of the way, the reality is that to "fix" this problem would require SQLite parsing statements in an incompatible way going forward. Given the value SQLite places on backward compatibility, I don't see that as a real option. It could be mitigated via the USE of a pragma or compile time option, but that would complicate code and at what cost? You'd have to know about the problem in order to enable the pragma or macro to mitigate it. The reality is that SQLite is not a stand alone SQL engine, and it already deviates in some significant ways from the SQL standard (which every engine does to one extent or another, primarily in the form of what extensions it makes available and syntactic details). For an engine designed to be embedded in C programs as its primary use case to use the integer / boolean logic defined in C makes perfect sense on that basis. So, even though I agree that changing SQLite to disallow "WHERE 1 - 1" would not be a gain, some people seem to be completely missing the point, namely that there is yet another SQLite syntax difference that apparently no one has observed previously. While I may not agree with Petite as to whether it is a large problem or not, I see his point, and that point seems to have been missed by a few people. SDR _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
