Hi again,
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 the RHS is
converted to '1.0'
This would not have been a problem if CAST(1.0 AS TEXT) produced '1' (like
FloatToStr does in Delphi)
If this seems silly, what I'm doing above is simulating a
sqlite3_bind_double if you change 1.0 to ? (Integers stored in float fields
will never find a match stored in a text field, without casting)
However:
CREATE TABLE tleft (Txt TEXT);
INSERT INTO tleft VALUES ('1');
CREATE TABLE tright (Flt REAL);
INSERT INTO tright VALUES (1.0);
SELECT * FROM tleft, tright WHERE Txt=Flt; -- returns a match!
Is it because of the following from https://www.sqlite.org/datatype3.html
> (As an internal optimization, small floating point values with no
> fractional component and stored in columns with REAL affinity are written
> to disk as integers in order to take up less space and are automatically
> converted back into floating point as the value is read out. This
> optimization is completely invisible at the SQL level and can only be
> detected by examining the raw bits of the database file.)
>
Does the cast of Flt to TEXT use the stored integer value in the
comparison? I want my query to do the same!
I therefore make my case that SELECT '1'=1.0 should return 1, and not 0 as
it does at the moment, i.e. CAST(1.0 AS TEXT) must produce '1'
Paul.
