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]
