[SOLVED] Android sqlite fts3 match and compare integer/float

Android sqlite fts3 match and compare integer/float

We have a sqlite fts3 table without enhanced syntax(Android!!!) where we use a query like this:

SELECT * FROM tableName WHERE tableName MATCH 'name*' AND validTo > 1389999600000 LIMIT 10

This works fine on iOS with fts4 and enhanced syntax, the validTo compare don't work as expected:

validTo > anyNumber => true
validTo < anyNumber => false

This has something to do with the that a full text search table contains strings. But then again - it works on iOS. On iOS we have times with a decimal value like 1389999600000.0 but we have tried that without any change.

Any clue on how to solve this?

Solution

You should not use FTS tables to store non-text data.

If you really want to treat data in an FTS table as a number, you have to convert it explicitly:

SELECT * FROM MyTable WHERE CAST(validTo AS NUMERIC) > 1389999600000