On Mon, May 5, 2014 at 9:07 PM, Jay Kreibich <j...@kreibi.ch> wrote: > > 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. >
There is more to it than that. Oracle does not have a boolean type (they recommend Y/N chars or number 0/1). Despite this, Oracle can tell when there are no boolean operations in an expression thus making them invalid in the context of a WHERE clause. Look, I'm not advocating the changes to SQLite. I think I've made it clear that *I* believe the way SQLite works is best for this particular environment, and to change it would potentially cause massive regressions in potentially billions of devices. But to claim it is nothing but a type system problem is plainly wrong. C compilers are able to warn you when you use assignment where you probably meant to use equality, despite the lack of a dedicated boolean type. The request is not that the type system change, it is that syntax checking change. > “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. > Again, it comes across like you're arguing with me after I just said "I don't think 'portable' SQL is that valuable" and gave a possible reason why. You and I agree on virtually every point, but you seem unwilling to concede that it is possible to look for "boolean" operations in the absence of a strict boolean type. That doesn't mean I think it should be changed, just that the original observation comes from a legitimate point of view, even if we don't agree with it 100%. > 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. > Except it doesn't, because C compilers can warn when you type "if (var = constant) ...;" when you probably meant "if (var == constant) ...;". If at least some C compiler implementations can do that within the confines of the ANSI C 89 standard, SQLite certainly *could* do something similar. Even without a boolean type. > 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. > Yes, if you go back and re-read my previous message, you'll see that I said the entire original use case of SQLite being embedded in C programs means that the SQLite way is arguably more useful for the intended audience. Of course, that audience has grown dramatically, probably far more than DRH ever envisioned, so now other people with alternative points of view are chiming in with what they see as a deficiency. I am capable of disagreeing with his point that this should be "fixed" while acknowledging that his point of view has merit for some use cases, rather than just dismissing it as nothing more than a type system issue. (Edit: I see you did agree with my statement about SQLite + C developer use cases being compatible; these long emails are hard to track at times.) > 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. > I suspect the entire subscriber base of this mailing list is not necessarily a representative sample of the universe of SQLite developers. But you could be right. Maybe everyone and their dog knew about this difference between SQLite and other engines except for me and the OP. :) > 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. > I agree, the fact that SQLite is more liberal with what it accepts should generally not be a problem, which is another reason why I would not want to see it changed. Of course, it does become a problem on an individual basis if you spend all your time developing for platform alpha, and that platform is SQLite because of ease of use and deployment, and only at porting time do you discover the incompatibilities. One point of view is not inherently right and the other wrong, they merely are competing points of view. SDR _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
