On May 5, 2014, at 6:16 PM, Scott Robison <sc...@casaderobison.com> wrote:
> On Mon, May 5, 2014 at 4:15 PM, Jay Kreibich <j...@kreibi.ch> 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. But the reason the statement fails is because it trips a type-check for WHERE… that the expression value is not a Boolean, and the WHERE clause in those databases is strongly type-checked to only accept a Boolean. It isn’t about the “sensibility” of the expression, it is a simple type failure. SQLite has no native Boolean type, so it cannot verify the “sensibility" of an expression based off if it resolves to a Boolean or not. Of course, as a programmer that primary works in languages other than SQL, the idea that a condition must resolve to a Boolean strikes me as somewhere between odd and frustrating. > 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). “Portable” SQL is a battle that was lost 30+ years ago. It doesn’t exist, and anyone that things it does, or that there is any hope in finding it, needs to get out a bit more and try a few other RDBMS languages. There are dozens and dozens of ways SQLite SQL is different from “common” SQL, never mind the standard. Considering some the extreme differences, such as manifest typing, transaction aware DDL, the lack of NUMERC type— not to mention no time, date, or duration types— getting worked up about the fact that WHERE is not strongly typed and will accept an integer seems almost comical. The “nonsense” examples given seem almost common place to a programmer that works in any language other than SQL. > 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. Except it does, because the whole reason this issue exists is the lack of a Boolean type. The “fix”-- if you want to make it like every other database-- is to introduce a Boolean type, make WHERE type aware, and prevent type conversation of the WHERE expression. That’s the only way to make it “like other databases." So the whole thing revolves around the existence (or lack there of) of a Boolean type, even if the base argument is not about the lack of a Boolean. If SQLite had a native Boolean type, chances are good this whole thing would be a non-issue because it would have been written that way in the first place. Actually, on second thought, I kind of doubt it would. SQLite shows a very strong tie to C and the way C does things. This is reflected in the lack of a NUMERIC type, and the usage of native integers and floating-point numbers. Even if SQLite did have a native Boolean type— which, by itself, goes against the “embedded, close to C” design— I’m going to guess that conditional statements, such as WHERE would still accept integer values and just do type conversion. > 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. Exactly. Agreed. > 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. Or, rather, that plenty of people observed it, and never cared or thought twice about it, because we write code like that all the time in other languages. Similarly, for ever example Petite gave, I was able to predict what SQLite would do, and none of the answers surprised me. I’m sure that’s true for many others on this list. All of the “nonsense” examples have very logical conclusions that are well within the bounds of standard type conversations and three valued logic. Yes, the examples are contrived, but any language that attempts to prevent contrived examples is trying to be way too smart. My only surprise was that most SQL engines won’t auto convert integers to Booleans. I never noticed that and it strikes me as a bit obsessive, considering all the other times database engines will happily convert types back and forth. The flip side is that if you’re coming from a different SQL engine, or trying to write cross-platform SQL, then the fact that SQLite is more liberal should never be an issue. Within this context, any statement that works on a different SQL engine will work on SQLite. The only difference is that SQLite is a bit more accepting, and as others have already pointed out, that’s not a big deal and often seen as a good thing. The only way it can be bad is if someone learns on SQLite and is convinced that it is the One True SQL. The same problem, if it exists, is true of Oracle and MySQL as well. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > Author of “Using SQLite”, O’Reilly Media, Inc. http://shop.oreilly.com/product/9780596521196.do _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
