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]
