Re: [sqlite] select 1 where 1 - 1;
On 2014/05/06 06:47, Scott Robison wrote: 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. Firstly, I'm in agreement to a high extent with most of your observations, and I too thought the OP's complaint wasn't invalid, just insufficient to prompt change, but just a small pedanticism aside due the piece above: YES - C warns you of an assignment where you possibly meant to compare, but C has a compiler that warns you, and you have the luxury to ignore it - i.e. if your intent is to use/abuse the situation you can safely ignore the compiler warning and the compiled code will run as you intended without ever again stopping/warning/anything. This is very different to the SQL engine which doesn't compile your code per se... it either returns results, or it doesn't - do you really wish for it to return an error (because how else would a warning ever be communicated?) than just trying to accommodate what the request might be trying to achieve? It isn't the same ballgame. That said, I agree SQLite *could* be more strict, but I doubt the value and have to agree with Mr. Kreibich in that no returned result is/was unexpected - adapting SQLite to point out wrongful operator assumptions or typos has no benefit to the engine's use - I mean what's next, suggesting a better way to write a query when it disagrees with how you structured your query? :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
On Mon, May 5, 2014 at 9:07 PM, Jay Kreibich wrote: > > On May 5, 2014, at 6:16 PM, Scott Robison wrote: > > > On Mon, May 5, 2014 at 4:15 PM, Jay Kreibich 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 h
Re: [sqlite] select 1 where 1 - 1;
On May 5, 2014, at 6:16 PM, Scott Robison wrote: > On Mon, May 5, 2014 at 4:15 PM, Jay Kreibich 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
Re: [sqlite] select 1 where 1 - 1;
On Mon, May 5, 2014 at 4:15 PM, Jay Kreibich 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
Hihihi, some folks do not share the same references, anyway yes 3 passes that's named super bowle, LOL (warning many bad jokes inside) On Mon, May 5, 2014 at 3:31 PM, Petite Abeille wrote: > > On May 6, 2014, at 12:15 AM, Jay Kreibich wrote: > > > Cross what bridge? > > http://www.youtube.com/watch?v=zKhEw7nD9C4 > > > You seem to be trying to use common sense and semantic meaning to make > an argument. To quote an old CS prof, “If you argue in English**, you’re > wrong.” Math and formal specifications are really the only thing here, and > these do exactly what one would expect. > > ( … must… refrain… from… argh…. ) … never mind… all good and peaceful in > the beautiful kingdom... > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
On May 6, 2014, at 12:15 AM, Jay Kreibich wrote: > Cross what bridge? http://www.youtube.com/watch?v=zKhEw7nD9C4 > You seem to be trying to use common sense and semantic meaning to make an > argument. To quote an old CS prof, “If you argue in English**, you’re > wrong.” Math and formal specifications are really the only thing here, and > these do exactly what one would expect. ( … must… refrain… from… argh…. ) … never mind… all good and peaceful in the beautiful kingdom... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
On May 5, 2014, at 3:03 PM, Petite Abeille wrote: > > On May 5, 2014, at 9:15 PM, RSmith wrote: > >> Je suis desole mon ami… > > Moi aussi :P > > I have no quarrel with you, good Sir Knight, but I must cross this bridge: > > select 1 where 1 is 1; > select 1 where 1 is not 1; > select 1 where 1 is ( 1 = 1 ); Cross what bridge? You seem to be trying to use common sense and semantic meaning to make an argument. To quote an old CS prof, “If you argue in English**, you’re wrong.” Math and formal specifications are really the only thing here, and these do exactly what one would expect. ** or any other spoken language. 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? > select 1 in ( null ); — oh… > select 1 in ( not null ); — really? Since the rules of three-valued logic make it very clear that the value of the expression “not NULL” is “NULL”, where you really expecting something different? What do you expect the opposite of NULL to be? http://en.wikipedia.org/wiki/Three-valued_logic#Kleene_logic Yes, three-valued logic is confusing to people that try to think of it in terms of “Boolean logic with special rules,” but it is all quite formal and clearly defined… it just isn’t Boolean logic. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
LOL be careful not to break the stinger and leave it buried in the skin. Hello Petite Abeille, when you "eval" an expression, are you doing from the right or on the lvalue. Best. On Mon, May 5, 2014 at 1:03 PM, Petite Abeille wrote: > > On May 5, 2014, at 9:15 PM, RSmith wrote: > > > Je suis desole mon ami… > > Moi aussi :P > > I have no quarrel with you, good Sir Knight, but I must cross this bridge: > > select 1 where 1 is 1; > select 1 where 1 is not 1; > select 1 where 1 is ( 1 = 1 ); > select 1 in ( null ); — oh… > select 1 in ( not null ); — really? > > Anyway… if, as Stephan Beal mentioned earlier on, one looks at SQLite’s > SQL parser as some sort of glorified calculator, then, yes, it all makes > perfect sense in some kind of wonderful way... :) > > http://www.sqlite.org/src/finfo?name=src/parse.y > http://www.sqlite.org/src/artifact/22d6a074e5f5a7258947a1dc55a9bf946b765dd0 > > N.B. One more, just for fun: > > sqlite> select 1 in ( null is null ); > > 1 > > sqlite> select 1 in ( null is not null ); > > 0 > > m'kay... > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
On May 5, 2014, at 9:15 PM, RSmith wrote: > Je suis desole mon ami… Moi aussi :P I have no quarrel with you, good Sir Knight, but I must cross this bridge: select 1 where 1 is 1; select 1 where 1 is not 1; select 1 where 1 is ( 1 = 1 ); select 1 in ( null ); — oh… select 1 in ( not null ); — really? Anyway… if, as Stephan Beal mentioned earlier on, one looks at SQLite’s SQL parser as some sort of glorified calculator, then, yes, it all makes perfect sense in some kind of wonderful way... :) http://www.sqlite.org/src/finfo?name=src/parse.y http://www.sqlite.org/src/artifact/22d6a074e5f5a7258947a1dc55a9bf946b765dd0 N.B. One more, just for fun: sqlite> select 1 in ( null is null ); > 1 sqlite> select 1 in ( null is not null ); > 0 m'kay... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
On 2014/05/05 20:46, Petite Abeille wrote: On May 5, 2014, at 8:21 PM, RSmith wrote: the idea that introducing more complication will make erros/bugs less is just false. Straw man argument, unrelated to the topic at hand. Je suis desole mon ami... That comment was not regarding the topic at hand, it was with regard to a dissertation about the perceived vs. actual value of increased complexity. The paragraph after that was more to the topic, and if I'm not mistaken, I did say the complaint was valid. This new post however makes me regret it though, because this argument is baseless - what do you mean to try and point out with these errors vs. non-errors? the "is" operators takes 2 operands same as the Equal "=" operator in that it needs to compare two operands such as "a is b" in the same way that "a = b" works, so that when you say: "WHERE is null" It really means the same as: "WHERE = null" which is just silly and therefore errors out. The fact it sounds grammatically workable in English is a coincidence, albeit an irrelevant one. It is very very different to saying: "WHERE Null" or "WHERE 0" or "WHERE 1-1" How is this confusing you, or more importantly, how do you imagine changing it would solve a real SQL problem as opposed to a perceived one, and if it is possible, what exact logic change would you suggest that will fix it without introducing any other new complex behaviour or breaking any other current defined behaviour? If I was to make a straw-man-o-matic point - it would be: The list of complaints re this issue is negligibly small - and the only reason you even know about it is because you made a typo... so it wasn't like you tried to do it in xxx way while believing it should work, and then found out "Oy vey!, it doen't work this way!" - but rather you were just sad that your typo wasn't picked up by the system - non? Provide a valuable reason to make it better in the way that you use it normally (sans typos), and you would gain my vote too. This is solely about the SQL parser failing short of reporting syntax errors for nonsensical queries. select 1 where null; select 1 where is null; Error: near "is": syntax error select 1 where not null; select 1 where is not null; Error: near "is": syntax error select 1 where not( not null); elect 1 where not( is null ); Error: near "is": syntax error select 1 where not( 1 ); select 1 where ‘When I use a word, Humpty Dumpty said, in rather a scornful tone, it means just what I choose it to mean — neither more nor less.’; select 1 where not 'The question is, said Alice, whether you can make words mean so many different things.’; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
I think everyone agrees that SQLite does not strictly follow the SQL standards for WHERE clause expressions. The question is... should it? One must ask, "what makes SQLite lite?" I think this kind of simplification is of them. However, I can understand that it might rankle some people. Coming from a C background, when I see that SELECT 1 WHERE 1-1 compiles and treats 1-1 as FALSE, I can work with that. Heavens knows the number of times I've been caught by using a single '=' or tacking on a spare ';' on the end of while statement. And it irritated me that the language even allowed it. (Of course, the language DID allow it, so I have no right to complain.) So, since this is an SQL system designed to cut corners but still get the basic job done, I really have to ask why there's an argument at all on this topic? Both sides are right. Yes, SQLite accepts queries that go against SQL standard. Yes, if your query is formatted correctly, it executes correctly, so the fact that it takes a non-standard query is still an error on the programmer's part, even if SQLite didn't flag it. At least it does have rules on how those are handled. Marc -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Petite Abeille Sent: Monday, May 05, 2014 2:46 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] select 1 where 1 - 1; On May 5, 2014, at 8:21 PM, RSmith wrote: > the idea that introducing more complication will make erros/bugs less is just > false. Straw man argument, unrelated to the topic at hand. This is solely about the SQL parser failing short of reporting syntax errors for nonsensical queries. select 1 where null; select 1 where is null; Error: near "is": syntax error select 1 where not null; select 1 where is not null; Error: near "is": syntax error select 1 where not( not null); elect 1 where not( is null ); Error: near "is": syntax error select 1 where not( 1 ); select 1 where 'When I use a word, Humpty Dumpty said, in rather a scornful tone, it means just what I choose it to mean - neither more nor less.'; select 1 where not 'The question is, said Alice, whether you can make words mean so many different things.'; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
On May 5, 2014, at 8:21 PM, RSmith wrote: > the idea that introducing more complication will make erros/bugs less is just > false. Straw man argument, unrelated to the topic at hand. This is solely about the SQL parser failing short of reporting syntax errors for nonsensical queries. select 1 where null; select 1 where is null; Error: near "is": syntax error select 1 where not null; select 1 where is not null; Error: near "is": syntax error select 1 where not( not null); elect 1 where not( is null ); Error: near "is": syntax error select 1 where not( 1 ); select 1 where ‘When I use a word, Humpty Dumpty said, in rather a scornful tone, it means just what I choose it to mean — neither more nor less.’; select 1 where not 'The question is, said Alice, whether you can make words mean so many different things.’; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
On 2014/05/05 20:00, Richard Hipp wrote: I am deeply committed to the concept that simpler is better. And I am deeply skeptical of arguments that making a language more complex by adding new type rules does anything to reduce bugs. To add to this, I have heard a lot of proponents on both sides of the fence, but the idea that introducing more complication will make erros/bugs less is just false. Sure - every rule will prevent a certain error type or bug kind, but it will introduce another thing that can be done wrong. And if you introduce a 100 rules, you will have a 100 things all with their own special exceptions and things that can be done wrong. This in itself does not mean one should only go for the simpler way in computing terms, but it does mean in human terms that it is easier to learn and remember the 20 peculiarities of the 5 simplest basic ways of doing, than the 200 more (but maybe less weird) complications of the added/introduced new 100 rules. The problem, if I may guess, that caused the post is not so much dismay at how it works in SQLite, but more of a "I wasted n hours figuring out that the way it works in x and y, which I am used to, and it simply does not work the same in z, and I'm very dismayed that I did not know z doesn't work like that and it caused me headaches and time to realize." - This not being an invalid complaint, but rather one that is immediately unimportant once the basis is understood - hence not seeing this same complaint crop up a lot previously (or ever, actually). I have never found a computer language of any sort in which I did not, at some point, had a bug due to a typo that took me ages to find. The problem is that bad logic niggles at your mind, and you know it might be needing fixing... but a typo is assumed in your mind to be the thing you intended to type, which should be very correct and above scrutiny of your brain's internal debugger. It's the worst kind. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
On May 5, 2014, at 8:00 PM, Richard Hipp wrote: > Petite's complaint is that in most other SQL database engines, 0 is not > false. If you try to use 0 where a boolean is needed, you get a syntax > error. In strict SQL, boolean and integer are incompatible types that > cannot be interchanged. While I do agree with the above, this not about types per se. And it’s not about the meaning of life, nor metaphysics. I simply wish the *SQL* parser was more strict about what it accepts. My point is that blindly accepting a bare expression as a comparison fails far short of both ‘simplicity’ and ‘sanity'. That’s all. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
On May 5, 2014 11:36 AM, "Stephan Beal" wrote: > > I mean ‘where 1’, or ‘where ‘1 - 1’, or ‘where null’, or ‘where 0 / 0’, or > > any of this nonsense. There is nothing to compare. It’s nonsensical. > > Oh, but there is: 1-1 is an expression, the result of which is integer 0 > (as opposed to string '0'), which, in all programming environments except, > IIRC, Xenix, is boolean false. In fact, the sqlite shell makes a handy ad > hoc calculator for exactly that purpose: > > sqlite> select 1-1; > 0 > sqlite> select typeof(1-1); > integer I personally have no problem with SQLite's implicit conversion between types, but I can appreciate why it would frustrate some people because no other SQL parser I have tested (1 oracle, 2 SQL Server, and 2 or more of MySQL and PostgreSQL) allows that construct, generating errors due to an incorrect expression or some such. That being said, SQLite already deviates from "tradition" in many respects. Given its origin as a C library to provide small fast database access, it makes perfect sense to me why it works this way. Of things that could be improved, this particular syntax checking issue is no where near the top of my list (or would not be if I had such a list). What I really want is a SQL variant that reads my mind via a DWIM interface. "Do What I Mean" is a powerful paradigm, and DRH is just the guy to deliver it! ;) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
On Mon, May 5, 2014 at 1:36 PM, Stephan Beal wrote: > On Mon, May 5, 2014 at 7:31 PM, Petite Abeille >wrote: > > > On May 5, 2014, at 7:15 PM, Stephan Beal wrote: > > > Why expect an error? It's abstractly the same as saying WHERE 'a' = > 'b’, > > > > I mean ‘where 1’, or ‘where ‘1 - 1’, or ‘where null’, or ‘where 0 / 0’, > or > > any of this nonsense. There is nothing to compare. It’s nonsensical. > > > > Oh, but there is: 1-1 is an expression, the result of which is integer 0 > (as opposed to string '0'), which, in all programming environments except, > IIRC, Xenix, is boolean false. > Petite's complaint is that in most other SQL database engines, 0 is not false. If you try to use 0 where a boolean is needed, you get a syntax error. In strict SQL, boolean and integer are incompatible types that cannot be interchanged. There are two diverging philosophies of computer programming at play here. On the one hand, there is the idea that you should have as few primitives as possible but make them as powerful as possible so that you can do everything you need. Thus, since boolean values can be represented easily as integer, there is no reason to have a separate type for boolean as that would merely add gratuitous complication. This is the approach taken by SQLite. It is also the approach taken by C. It is also philosophically aligned with, for example, modern physics, which seeks to find a single set of rules that govern the behavior of seemingly different phenomena. Why have one physics for magnetism and a separate physics for electricity when you can easily combine them into a single unified physics of electromagnetism. The other approach is to have different types for everything, under the theory that this prevents programming bugs. Thus you have a boolean type that is distinct and incomparable to integers. And there are separate rules for dealing with booleans versus integers, adding complexity to the language. Ada takes this approach, for example. The official SQL standard language is also this way. I am deeply committed to the concept that simpler is better. And I am deeply skeptical of arguments that making a language more complex by adding new type rules does anything to reduce bugs. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
The WHERE clause takes only one parameter. WHERE expects an expression, which evaluates down to one value. That expression does not need to be a comparison. It commonly is, but it can be anything, such as a CASE expression, an EXISTS subselect, or a function. As far as SQLite cares, all three of these are identical… and I mean completely identical, once you evaluate the constants: sqlite> SELECT 'true' WHERE 1; true sqlite> SELECT 'true' WHERE 2 - 1; true sqlite> SELECT 'true' WHERE 1 = 1; true sqlite> SELECT 'true' WHERE 0 = 0; true Don’t believe me? Consider the value of the expression “1 = 1” or “0 = 0”: sqlite> SELECT 1 = 1, 0 = 0; 1|1 So in the clause “WHERE 0 = 0" the expression “0 = 0” evaluates to the integer “1”, which is passed to the WHERE clause, considered true, and the row is returned. All the WHERE clause ever sees is “1”. It doesn’t care that the value was derived from a comparison, because that is totally irrelevant. WHERE only cares that there was an expression that evaluates to a value that is considered equivalent to “true”. -j On May 5, 2014, at 12:31 PM, Petite Abeille wrote: > > On May 5, 2014, at 7:15 PM, Stephan Beal wrote: > >> Why expect an error? It's abstractly the same as saying WHERE 'a' = 'b’, > > I mean ‘where 1’, or ‘where ‘1 - 1’, or ‘where null’, or ‘where 0 / 0’, or > any of this nonsense. There is nothing to compare. It’s nonsensical. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
On May 5, 2014, at 7:36 PM, Stephan Beal wrote: > Oh, but there is: 1-1 is an expression, the result of which is integer 0 It’s nonsensical as a where clause expression. > (as opposed to string '0'), which, in all programming environments except, > IIRC, Xenix, is boolean false. This is SQL, not Sparta. > In fact, the sqlite shell makes a handy ad > hoc calculator for exactly that purpose: Non sequitur. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
On Mon, May 5, 2014 at 7:31 PM, Petite Abeille wrote: > On May 5, 2014, at 7:15 PM, Stephan Beal wrote: > > Why expect an error? It's abstractly the same as saying WHERE 'a' = 'b’, > > I mean ‘where 1’, or ‘where ‘1 - 1’, or ‘where null’, or ‘where 0 / 0’, or > any of this nonsense. There is nothing to compare. It’s nonsensical. > Oh, but there is: 1-1 is an expression, the result of which is integer 0 (as opposed to string '0'), which, in all programming environments except, IIRC, Xenix, is boolean false. In fact, the sqlite shell makes a handy ad hoc calculator for exactly that purpose: sqlite> select 1-1; 0 sqlite> select typeof(1-1); integer -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
On May 5, 2014, at 7:15 PM, Stephan Beal wrote: > Why expect an error? It's abstractly the same as saying WHERE 'a' = 'b’, I mean ‘where 1’, or ‘where ‘1 - 1’, or ‘where null’, or ‘where 0 / 0’, or any of this nonsense. There is nothing to compare. It’s nonsensical. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
On Mon, May 5, 2014 at 6:56 PM, Petite Abeille wrote: > > select 1 where 1 = 1; > > Indeed. I would have expected a syntax error along the lines of 'invalid > relational operator’ or such. And that’s that. > Why expect an error? It's abstractly the same as saying WHERE 'a' = 'b', and internally sqlite doesn't really know that it's comparing two constants with the same value until it has compared them to figure that out unless its optimizer does that somehow, but the end result must be the same with and without the optimizer. i.e. it's just a normal comparison operation, for all intents and purposes, and 1=1 === true in all languages i've worked with (except that SQL uses '=' instead of '==' or '===', but that's just a syntactical difference). (NaN tends to have unusual comparison rules, though, e.g. NaN does not compare equal to itself in some languages.) -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
On May 5, 2014, at 1:14 AM, James K. Lowden wrote: > To amplify the point, the issue isn't pure fussiness or obligation to > adhere to standards. A permissive parser invites error. Exactly. > It's not hard to imagine > > select 1 where 1 - 1; > > was intended as > > select 1 where 1 = 1; > > which, in the midst of a large query producing expected results, might > easily be overlooked. You must have been peering over my shoulder! This is exactly how this came about: a bloody typo! :D > I doubt Petite is confused by boolean evaluation, but rather is > dismayed by its appearance in this context. Indeed. I would have expected a syntax error along the lines of 'invalid relational operator’ or such. And that’s that. > > SQL is not C. To the extent the SQL supplied by SQLite is nonstandard, > it might as well be another language entirely. The better one knows > SQL, the harder a nonconformant implementation is to use. > > Not long ago I was helping someone with a query in MS Access. Easy, > just use a correlated subquery in an update statement. Hard, if > it chokes the parser. Perhaps you know the joke with the punchline, > "Assume a can opener." > > --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
On Mon, May 5, 2014 at 11:35 AM, Marc L. Allen wrote: > Really? Interesting. > > So... > > Select 1 Where 1 < inf; ? > > Or is it just when taking inf by itself? > Maybe I'm wrong. It's division by 0.0 that gives NULL as an answer. Perhaps if you insert an inf using sqlite3_bind_double() you will get a TRUE value. I'd have to check. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
Really? Interesting. So... Select 1 Where 1 < inf; ? Or is it just when taking inf by itself? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Monday, May 05, 2014 11:32 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] select 1 where 1 - 1; On Mon, May 5, 2014 at 11:28 AM, mm.w <0xcafef...@gmail.com> wrote: > select inf; should be true or false? :) > Neither true nor false. SQLite considers infinity to be the same as NULL. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
On Mon, May 5, 2014 at 11:28 AM, mm.w <0xcafef...@gmail.com> wrote: > select inf; should be true or false? :) > Neither true nor false. SQLite considers infinity to be the same as NULL. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
I know Simon, just asking, nope an empty string is a valid string, else you say no NULL allowed for strings, might be a backend option 8) On Mon, May 5, 2014 at 8:27 AM, Simon Slavin wrote: > > On 5 May 2014, at 4:18pm, mm.w <0xcafef...@gmail.com> wrote: > > > "an empty string should be false" strongly disagree, a NULL string should > > be solely false, now in this case, the question is: comparisons should be > > handled as bin or by; 'literal' values? or equality/comparison must not > be > > eval'ed and strictly made on type? > > > It's a consequence of some other requirements by SQL and of weak typing. > Sorry. > > It is a requirement that an empty string must evaluate to TRUE or FALSE or > NULL. > > If you take a function that requires a number and feed it an empty string, > it understands the string as meaning zero. > > If you take a test that requires a boolean and feed it zero, it > understands that value as meaning FALSE. > > Therefore an empty string must evaluate to FALSE. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
select inf; should be true or false? :) On Mon, May 5, 2014 at 8:18 AM, mm.w <0xcafef...@gmail.com> wrote: > Hello, > > "an empty string should be false" strongly disagree, a NULL string should > be solely false, now in this case, the question is: comparisons should be > handled as bin or by; 'literal' values? or equality/comparison must not be > eval'ed and strictly made on type? > > one other of the quirk would be to cast to a float, ugly I might admit. > > Best. > > > > On Sun, May 4, 2014 at 4:14 PM, James K. Lowden > wrote: > >> On Fri, 2 May 2014 21:09:46 +0200 >> Petite Abeille wrote: >> >> > >> > On May 2, 2014, at 8:54 PM, Richard Hipp wrote: >> > >> > > I'm guessing that Mr. Abeille is upset that SQLite ? >> > >> > ? doesn?t even bother with SQL syntax and will happily accept any old >> > junk as a sorry excuse for a query. >> > >> > select 1 where null; >> > select 1 where not null; >> > >> > When SQLite 4 sees the light of the day, I wish for a strict SQL >> > parser and proper error messages. >> >> To amplify the point, the issue isn't pure fussiness or obligation to >> adhere to standards. A permissive parser invites error. >> >> It's not hard to imagine >> >> select 1 where 1 - 1; >> >> was intended as >> >> select 1 where 1 = 1; >> >> which, in the midst of a large query producing expected results, might >> easily be overlooked. >> >> I doubt Petite is confused by boolean evaluation, but rather is >> dismayed by its appearance in this context. >> >> SQL is not C. To the extent the SQL supplied by SQLite is nonstandard, >> it might as well be another language entirely. The better one knows >> SQL, the harder a nonconformant implementation is to use. >> >> Not long ago I was helping someone with a query in MS Access. Easy, >> just use a correlated subquery in an update statement. Hard, if >> it chokes the parser. Perhaps you know the joke with the punchline, >> "Assume a can opener." >> >> --jkl >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
On 5 May 2014, at 4:18pm, mm.w <0xcafef...@gmail.com> wrote: > "an empty string should be false" strongly disagree, a NULL string should > be solely false, now in this case, the question is: comparisons should be > handled as bin or by; 'literal' values? or equality/comparison must not be > eval'ed and strictly made on type? It's a consequence of some other requirements by SQL and of weak typing. Sorry. It is a requirement that an empty string must evaluate to TRUE or FALSE or NULL. If you take a function that requires a number and feed it an empty string, it understands the string as meaning zero. If you take a test that requires a boolean and feed it zero, it understands that value as meaning FALSE. Therefore an empty string must evaluate to FALSE. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
Hello, "an empty string should be false" strongly disagree, a NULL string should be solely false, now in this case, the question is: comparisons should be handled as bin or by; 'literal' values? or equality/comparison must not be eval'ed and strictly made on type? one other of the quirk would be to cast to a float, ugly I might admit. Best. On Sun, May 4, 2014 at 4:14 PM, James K. Lowden wrote: > On Fri, 2 May 2014 21:09:46 +0200 > Petite Abeille wrote: > > > > > On May 2, 2014, at 8:54 PM, Richard Hipp wrote: > > > > > I'm guessing that Mr. Abeille is upset that SQLite ? > > > > ? doesn?t even bother with SQL syntax and will happily accept any old > > junk as a sorry excuse for a query. > > > > select 1 where null; > > select 1 where not null; > > > > When SQLite 4 sees the light of the day, I wish for a strict SQL > > parser and proper error messages. > > To amplify the point, the issue isn't pure fussiness or obligation to > adhere to standards. A permissive parser invites error. > > It's not hard to imagine > > select 1 where 1 - 1; > > was intended as > > select 1 where 1 = 1; > > which, in the midst of a large query producing expected results, might > easily be overlooked. > > I doubt Petite is confused by boolean evaluation, but rather is > dismayed by its appearance in this context. > > SQL is not C. To the extent the SQL supplied by SQLite is nonstandard, > it might as well be another language entirely. The better one knows > SQL, the harder a nonconformant implementation is to use. > > Not long ago I was helping someone with a query in MS Access. Easy, > just use a correlated subquery in an update statement. Hard, if > it chokes the parser. Perhaps you know the joke with the punchline, > "Assume a can opener." > > --jkl > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
On Fri, 2 May 2014 21:09:46 +0200 Petite Abeille wrote: > > On May 2, 2014, at 8:54 PM, Richard Hipp wrote: > > > I'm guessing that Mr. Abeille is upset that SQLite ? > > ? doesn?t even bother with SQL syntax and will happily accept any old > junk as a sorry excuse for a query. > > select 1 where null; > select 1 where not null; > > When SQLite 4 sees the light of the day, I wish for a strict SQL > parser and proper error messages. To amplify the point, the issue isn't pure fussiness or obligation to adhere to standards. A permissive parser invites error. It's not hard to imagine select 1 where 1 - 1; was intended as select 1 where 1 = 1; which, in the midst of a large query producing expected results, might easily be overlooked. I doubt Petite is confused by boolean evaluation, but rather is dismayed by its appearance in this context. SQL is not C. To the extent the SQL supplied by SQLite is nonstandard, it might as well be another language entirely. The better one knows SQL, the harder a nonconformant implementation is to use. Not long ago I was helping someone with a query in MS Access. Easy, just use a correlated subquery in an update statement. Hard, if it chokes the parser. Perhaps you know the joke with the punchline, "Assume a can opener." --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
On May 2, 2014, at 10:07 PM, Keith Medcalf wrote: > >> On May 2, 2014, at 8:54 PM, Richard Hipp wrote: > >>> I'm guessing that Mr. Abeille is upset that SQLite ... > >> ... doesn't even bother with SQL syntax and will happily accept any old >> junk as a sorry excuse for a query. > >> select 1 where null; >> select 1 where not null; > > What is the problem? > > The logical value of a NULL is false. > Any operation on a NULL (including NOT) is a NULL and therefore false. ** > special rules for AND and OR The logical value of NULL is NOT False.. it is “NULL.” NULL does not equal False, just as it does not equal True. SQL employs what is called 3VL, or “three valued logic.” It is a logic system that consists of three values… True, False, and NULL. http://en.wikipedia.org/wiki/Three-valued_logic There are not “special rules” for AND and OR, there are just the core rules for 3VL, which considers NULL to be an unknown. There are times when SQL needs a definite yes/no answer. however. In those cases, SQL looks to see if the value is equal to True. For example: sqlite> select 1 where NULL; sqlite> In such cases, it is not that NULL is equal to False (which isn’t True), it is that NULL is not equal to True… just as False is not equal to True. > A numeric value of 0 is false. > Any value that is not 0 is true. > > Strings are, however, handled incorrectly: > sqlite> select 1 where 'A'; > sqlite> select 1 where not 'A'; > 1 > > ^^^ are inverted 'A' should be true, whereas not 'A' should be false; and, For logic operations, strings are converted into numbers. ‘A’ doesn’t translate to a number, so it become 0, or false. Consider: sqlite> select 1 where '1'; 1 sqlite> select 1 where not '1'; sqlite> select CAST( 'A' as integer ); 0 sqlite> select CAST( '' as integer ); 0 sqlite> select CAST( '1' as integer ); 1 > sqlite> select 1 where ''; > sqlite> select 1 where not ''; > 1 > > an empty string should be false, and applying the not operator should result > in true Which it is how it works… and empty string is converted to zero, which is false. A "not false" is true. …just as your examples show. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
>On May 2, 2014, at 8:54 PM, Richard Hipp wrote: >> I'm guessing that Mr. Abeille is upset that SQLite ... >... doesn't even bother with SQL syntax and will happily accept any old >junk as a sorry excuse for a query. >select 1 where null; >select 1 where not null; What is the problem? The logical value of a NULL is false. Any operation on a NULL (including NOT) is a NULL and therefore false. ** special rules for AND and OR A numeric value of 0 is false. Any value that is not 0 is true. Strings are, however, handled incorrectly: sqlite> select 1 where 'A'; sqlite> select 1 where not 'A'; 1 ^^^ are inverted 'A' should be true, whereas not 'A' should be false; and, sqlite> select 1 where ''; sqlite> select 1 where not ''; 1 an empty string should be false, and applying the not operator should result in true (that is, a string is true if its length is non-zero and false if the length is zero). Truth values of strings appears to be incorrectly handled everywhere, and logical operations (AND/OR) work as expected even with NULL but are incorrect in the presence of a string (since the conversion of strings to truth values is incorrect). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
On May 2, 2014, at 9:24 PM, Cory Nelson wrote: > quirks A peculiar behavioral habit. Idiosyncrasy, peculiarity, oddity, eccentricity, foible, whim, vagary, caprice. Indeed. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
On Fri, May 2, 2014 at 2:09 PM, Petite Abeille wrote: > > On May 2, 2014, at 8:54 PM, Richard Hipp wrote: > >> I'm guessing that Mr. Abeille is upset that SQLite … > > … doesn’t even bother with SQL syntax and will happily accept any old junk as > a sorry excuse for a query. > > select 1 where null; > select 1 where not null; > > When SQLite 4 sees the light of the day, I wish for a strict SQL parser and > proper error messages. > You'll have a lot of trouble finding a SQL implementation that doesn't have any non-standard features or quirks. As an Open Source project, you're always develop patches. I think pluggable parsers would actually be pretty interesting. Alternately, you might inquire ways to contribute to documentation what to expect when using this syntax. -- Cory Nelson http://int64.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
On May 2, 2014, at 8:54 PM, Richard Hipp wrote: > I'm guessing that Mr. Abeille is upset that SQLite … … doesn’t even bother with SQL syntax and will happily accept any old junk as a sorry excuse for a query. select 1 where null; select 1 where not null; When SQLite 4 sees the light of the day, I wish for a strict SQL parser and proper error messages. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
On 2 May 2014, at 7:47pm, David King wrote: > Am I missing something? It's very clever for people who write programming languages to invent the idea of a boolean variable, and have 'IF' and 'WHERE' take boolean values as arguments, but I don't think it's obvious. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
On Fri, May 2, 2014 at 2:47 PM, David King wrote: > > Quick, without trying it out, what would you expect the following > statement to return: > > select 1 where 1 - 1; > > (a) one row > > (b) no row > > (c) syntax error > > For extra entertainment, try some variations: > > select 1 where 1; > > select 1 where 0; > > etc... > > Bonus points for a rationalization of any of the behaviors you see or > don’t see. > > I'm not sure why this is surprising. sqlite happily uses 1 and 0 as > booleans: > > sqlite> select 1 = 1; > 1 > sqlite> select 1 = 100; > 0 > > And the WHERE clause is just a boolean expression. Your expressions are > simply "select the literal 1 where true" or "where false" which will either > cause the value to be selected or not, just due to the WHERE clause. > > Am I missing something? > I'm guessing that Mr. Abeille is upset that SQLite uses integers as booleans, the same as C++ does, whereas other SQL database engines have a distinct boolean type that is incomparable with integer. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
> Quick, without trying it out, what would you expect the following statement > to return: > select 1 where 1 - 1; > (a) one row > (b) no row > (c) syntax error > For extra entertainment, try some variations: > select 1 where 1; > select 1 where 0; > etc... > Bonus points for a rationalization of any of the behaviors you see or don’t > see. I'm not sure why this is surprising. sqlite happily uses 1 and 0 as booleans: sqlite> select 1 = 1; 1 sqlite> select 1 = 100; 0 And the WHERE clause is just a boolean expression. Your expressions are simply "select the literal 1 where true" or "where false" which will either cause the value to be selected or not, just due to the WHERE clause. Am I missing something? signature.asc Description: Message signed with OpenPGP using GPGMail ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
