[sqlite] Mixing text and numeric values in comparisons

[Richard Hipp]

On 3/4/16, Paul van Helden <paul at planetgis.co.za> wrote:
> Hi All,
>
> CREATE TABLE test (A TEXT);
> INSERT INTO test VALUES ('1');
> SELECT * FROM test WHERE A=1; // returns 1
> SELECT * FROM test WHERE A=1.0; // returns nothing

Because column A has type TEXT, the RHS of the = is converted to text
and a text comparison is done.  And '1' is not equal to '1.0'.

If you change the type of A to NUMERIC, or REAL, or INT or any other
numeric type, then the RHS of the comparison will be converted to a
number and you'll get the result you expect.

>
> How is the second comparison done?
>
> I realize that you can have double values that are too large to convert to
> int64, but as I understand, SQLite does convert doubles to integer values
> for storage when they are small enough.
>
> The problem presented itself when binding integers from an Access table
> with sqlite3_bind_double because the Access table has the integers in a
> float field. (Apparently common). I had to change my library to check
> integers in double fields and then bind with sqlite3_bind_int64 in order to
> hit a result with WHERE.
>
> What is the best way to check for integers? (fabs(val)<LLONG_MAX) &&
> modf(val, &intpart)<EPSILON  ?
>
> Regards,
>
> Paul.
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
drh at sqlite.org