RE: [sqlite] SQLite version 3 design question: '500'=500?
Actually, I don't think MS SQL's reaction is really all that stupid. In the statement SELECT '500' = 500, I believe it is treating '500' as a field name, and assigning it the value 500. On the other hand, the statement SELECT 500 = '500' would in fact fail, because you can't use an integer as a field name without having it in quotes. Correct me if I'm wrong, but I believe this is why MS SQL returned the results that it did. Eric Pankoke Founder Point Of Light Software http://www.polsoftware.com "Lighting The Path To Glory" -Original Message- From: Andrew Piskorski [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 12, 2004 8:15 PM To: [EMAIL PROTECTED] Subject: Re: [sqlite] SQLite version 3 design question: '500'=500? On Wed, May 12, 2004 at 05:37:49PM -0700, Keith Herold wrote: > In MS SQL 2000, through the query analyzer > >SELECT '500' = 500 > > returns 500 . > >SELECT 500 = '500' > > returns > Server: Msg 170, Level 15, State 1, Line 1 > Line 1: Incorrect syntax near '='. Well that's sure inordinately stupid behavior. The test "A = B" should give the same result as "B = A", at least! In Oracle 8.1.7.4, string '5' does equal integer 5, but you seem to have to ask it via a case statement: SQL> select 5 = 5 from dual; ERROR at line 1: ORA-00923: FROM keyword not found where expected SQL> select case when 5 = '5' then 1 else 0 end as bool from dual; BOOL -- 1 SQL> select case when '5' = 5 then 1 else 0 end as bool from dual; BOOL -- 1 > > But if that is the case, then clearly, '500' != 500. So > > unless somebody can come up with a better idea, SQLite > > version 3.0 will return "0" for the following: > > > > SELECT '500'=500; > > > > On the other hand, the following two statements will return > > "1" (or true): > > > > SELECT '500'+0=500; > > SELECT '500'=(500||''); That sounds ok to me. Wouldn't it be clearer to have explicit cast statements though, rather than doing weird no-ops like "||''" solely to force a type conversion? Also, since you're introducing manifest typing, it would probably be very handy to have good boolean tests both for "is X currently of type Y?" and "CAN X be of type Y?". E.g., "CAN this thing be an integer?", where by "can" I mean, "Is it currently an integer, or if it is currently stored as a some other type, can it be LOSSLESSLY converted to an integer?" Some languages (e.g., S-Plus), make the latter oddly difficult. (Not THAT difficult; in S you can write that "can be integer" test in 5 lines, but those 5 lines are also somewhat easy to get wrong.) I think that in any system where objects can automatically change type, both the those sorts of "can be" boolean tests should also be built in. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
FW: [sqlite] SQLite version 3 design question: '500'=500?
-Original Message- From: Fred Williams [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 12, 2004 8:37 PM To: [EMAIL PROTECTED] Subject: RE: [sqlite] SQLite version 3 design question: '500'=500? In MySQL: Both return 1 > -Original Message- > From: Keith Herold [mailto:[EMAIL PROTECTED] > Sent: Wednesday, May 12, 2004 7:38 PM > To: [EMAIL PROTECTED] > Subject: RE: [sqlite] SQLite version 3 design question: '500'=500? > > > In MS SQL 2000, through the query analyzer > >SELECT '500' = 500 > > returns 500 . > >SELECT 500 = '500' > > returns > Server: Msg 170, Level 15, State 1, Line 1 > Line 1: Incorrect syntax near '='. > > Beyond these, I have no preference on whether they are true or false; I am > less worried about what the programming languages say as far as ... - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] SQLite version 3 design question: '500'=500?
Results from Firebird 1.5 (thanks for the syntax, Andrew)... SQL> select '500' = 500; Statement failed, SQLCODE = -104 Dynamic SQL Error -SQL error code = -104 -Token unknown - line 1, char 14 -= SQL> select 500 = '500'; Statement failed, SQLCODE = -104 Dynamic SQL Error -SQL error code = -104 -Token unknown - line 1, char 12 -= SQL> select 500 = 500; Statement failed, SQLCODE = -104 Dynamic SQL Error -SQL error code = -104 -Token unknown - line 1, char 12 -= SQL> select case when 5 = '5' then 1 else 0 end as bool from t1; BOOL 1 SQL> select case when '5' = 5 then 1 else 0 end as bool from t1; BOOL 1 e - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] SQLite version 3 design question: '500'=500?
In MS SQL 2000, through the query analyzer SELECT '500' = 500 returns 500 . SELECT 500 = '500' returns Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near '='. Beyond these, I have no preference on whether they are true or false; I am less worried about what the programming languages say as far as PERL, etc. . I would rather the return look like the majority vote on what the 'other' SQL engines/manufacturers do. --Keith > -Original Message- > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] > Sent: Wednesday, May 12, 2004 5:20 PM > To: [EMAIL PROTECTED] > Subject: [sqlite] SQLite version 3 design question: '500'=500? > > > The development team is making progress on SQLite version > 3.0. But we've run across an interesting puzzle. What should > be returned by this: > > SELECT '500'=500; > > Is the result "0" or "1"? In other words, what happens when > you compare a number to a string that looks like that number. > Are they equal or not? > > Other languages return a mixture of results here. Strings > and number compare equal in AWK, Perl, PHP, Tcl, and SQLite > version 2. String and numbers are not equal in Python and Ruby. > > Based on my experience, I would choose to make strings and > numbers equal. But there are complications to that approach > in SQLite 3.0. SQLite 3 supports manifest typing with 4 > basic types: NULL, NUMERIC, TEXT, and BLOB. Objects sort in > that order: NULLs first, followed by NUMERICs in numerical > order, then TEXT in a user-defined collating sequence and > finally BLOBs in memcmp() order. So '500' occurs at a > completely different place in the sort order from 500. If > comparison operators are to be consistent with sort order, > the following must be true: > > 500 < 600 > 600 < '500' > > But if that is the case, then clearly, '500' != 500. So > unless somebody can come up with a better idea, SQLite > version 3.0 will return "0" for the following: > > SELECT '500'=500; > > On the other hand, the following two statements will return > "1" (or true): > > SELECT '500'+0=500; > SELECT '500'=(500||''); > > Note that in other SQL engines, it is an error to compare > a string to an integer (I think - somebody please correct > me if I am wrong) so we cannot get any guidance there. > > Your thoughts? > -- > D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 > > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] SQLite version 3 design question: '500'=500?
Hello, On 12 may 2004, at 20:19, D. Richard Hipp wrote: SELECT '500'=500; Is the result "0" or "1"? In other words, what happens when you compare a number to a string that looks like that number. Are they equal or not? I vote for "0". Regards, -- Tito - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] SQLite version 3 design question: '500'=500?
The development team is making progress on SQLite version 3.0. But we've run across an interesting puzzle. What should be returned by this: SELECT '500'=500; Is the result "0" or "1"? In other words, what happens when you compare a number to a string that looks like that number. Are they equal or not? Other languages return a mixture of results here. Strings and number compare equal in AWK, Perl, PHP, Tcl, and SQLite version 2. String and numbers are not equal in Python and Ruby. Based on my experience, I would choose to make strings and numbers equal. But there are complications to that approach in SQLite 3.0. SQLite 3 supports manifest typing with 4 basic types: NULL, NUMERIC, TEXT, and BLOB. Objects sort in that order: NULLs first, followed by NUMERICs in numerical order, then TEXT in a user-defined collating sequence and finally BLOBs in memcmp() order. So '500' occurs at a completely different place in the sort order from 500. If comparison operators are to be consistent with sort order, the following must be true: 500 < 600 600 < '500' But if that is the case, then clearly, '500' != 500. So unless somebody can come up with a better idea, SQLite version 3.0 will return "0" for the following: SELECT '500'=500; On the other hand, the following two statements will return "1" (or true): SELECT '500'+0=500; SELECT '500'=(500||''); Note that in other SQL engines, it is an error to compare a string to an integer (I think - somebody please correct me if I am wrong) so we cannot get any guidance there. Your thoughts? -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] execution order of Update
As a side note, I thought that the was one of the nice things about SQL in general, that there was no real strong ordering requirement with respect to statements. That was why T-SQL and some of the others introduced more procedural constructs. Is this wrong? --Keith ++ [EMAIL PROTECTED] Lead Speech Recognition Engineer Toll-Free: (877) 977 - 0707 Phone: (858) 707 - 0707 x238 www.LumenVox.com Fax: (858) 707 - 7072 LumenVox, LLC 3615 Kearny Villa Rd #202 San Diego, CA 92123 ++ > -Original Message- > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] > Sent: Wednesday, May 12, 2004 5:19 AM > Cc: [EMAIL PROTECTED] > Subject: Re: [sqlite] execution order of Update > > > Rubens Jr. wrote: > > > > Is the update command executed in same order that was writen in the > > sql command ? example : > > UPDATE t1 SET f1 = f2, f2 = '' WHERE > > Is garanted that with this command f1 will have the value > of f2 BEFORE f2 > > receive value xxx ? > > I need to save the value of f2 than update f2, but > > I'm not shure that this may be done with only one sql command ... > > > > The right-hand side of every term is evaluated first, before > any changes are made. So you can say this: > > UPDATE t1 SET f1=f2, f2=f1; > > And it will swap the values of F1 and F2. > > > -- > D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 > > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] execution order of Update
Rubens Jr. wrote: Is the update command executed in same order that was writen in the sql command ? example : UPDATE t1 SET f1 = f2, f2 = '' WHERE Is garanted that with this command f1 will have the value of f2 BEFORE f2 receive value xxx ? I need to save the value of f2 than update f2, but I'm not shure that this may be done with only one sql command ... The right-hand side of every term is evaluated first, before any changes are made. So you can say this: UPDATE t1 SET f1=f2, f2=f1; And it will swap the values of F1 and F2. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] execution order of Update
> > No, all consts or data from other column are put into expression before > execution. Read > http://www.sqlite.org/lang.html#update > > I did not notice the " ...All expressions are evaluated before any assignments are made.." Thanks Again ! Rubens Jr. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.682 / Virus Database: 444 - Release Date: 11/05/2004 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] execution order of Update
No, all consts or data from other column are put into expression before execution. Read http://www.sqlite.org/lang.html#update -Original Message- From: Rubens Jr. [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 12, 2004 1:54 PM To: [EMAIL PROTECTED] Subject: Re: [sqlite] execution order of Update > > UPDATE command is evaluated before execution, so you can change a few fields > with one UPDATE. > > Best regards > Michal > Thanks for the fast reply !! But, may the update command evaluate in the inverse order ? In this case : First f2 wil receive value 'xxx', than f1 will receive value of f2 (now with xxx) !! - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] execution order of Update
> > UPDATE command is evaluated before execution, so you can change a few fields > with one UPDATE. > > Best regards > Michal > Thanks for the fast reply !! But, may the update command evaluate in the inverse order ? In this case : First f2 wil receive value 'xxx', than f1 will receive value of f2 (now with xxx) !! Rubens Jr. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.682 / Virus Database: 444 - Release Date: 11/05/2004 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] execution order of Update
> Is the update command executed in same order that was writen in the sql command ? example : UPDATE t1 SET f1 = f2, f2 = '' WHERE Is garanted that with > this command f1 will have the value of f2 BEFORE f2 receive value xxx ? I need to save the value of f2 than update f2, but I'm not shure that this may be done with only > one sql command ... UPDATE command is evaluated before execution, so you can change a few fields with one UPDATE. Best regards Michal - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] execution order of Update
Hi ! Is the update command executed in same order that was writen in the sql command ? example : UPDATE t1 SET f1 = f2, f2 = '' WHERE Is garanted that with this command f1 will have the value of f2 BEFORE f2 receive value xxx ? I need to save the value of f2 than update f2, but I'm not shure that this may be done with only one sql command ... Thanks Rubens Jr. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.682 / Virus Database: 444 - Release Date: 11/05/2004 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
