I agree, I would like to see compatibility with results from other SQL engines...
Shawn -----Original Message----- From: Keith Herold [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 12, 2004 8: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 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] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
