I can't seem to find a reference to the following behavior either in SQLite or apsw docs:
I have a table partially defined and populated as:
CREATE TABLE GeoLoc (lat TEXT, lon TEXT);
INSERT INTO GeoLoc (lat, lon) VALUES ('-33.5533422', '151.23422');
When using apsw to get the result for:
SELECT lat FROM GeoLoc;
I get the correct unicode value type for lat column. But running:
SELECT lat FROM GeoLoc WHERE lat + 0 = lat + 0;
I get float type instead! The above example is a silly one, but in my actual query, I'm doing some calculations with lat that turns it into floating point type but I don't expect the type to change everywhere in my query.
I rely on exact value of lat as I stored it. When converted to float, it could change when cast back to string (see section 4.0).
Note: I get the same result with Python's SQLite3 module, but not from as well as SQLite3's own command line client.
This is a bug that was introduced in SQLite version 3.8.3. Now fixed.