CODEWITHSUNDEEP
androidmysqlsqlsqlitefull-text-search
Simas
Simas

Reputation: 44178

Query FTS table MIN

I'm trying to get the lowest _id from my fts table with this query:

SELECT MIN(_id) FROM fts WHERE tbl_no=2 AND parent_id=6

The result I'm getting is 10. However the smallest _id is 9 and it fits the selection arguments.

If I instead use 

SELECT _id FROM fts WHERE tbl_no=2 AND parent_id=6

and select the 1st row, I get the correct result: 9.

Does have something to do with the table being virtual (FTS)? I recently transfered from multiple tables to a single FTS and am experiencing this.

Am I guaranteed to get the results I want with the 2nd query, considering the table never updated and it's sorted by default.

Notes: I am running this on Android (tried rawQuery and query). I have the table in front of me and I know it's correct:

enter image description here

Upvotes: 0

Views: 87

Answers (2)

CL.
CL.

Reputation: 180260

In FTS tables, all columns store string values, and the string '10' is lexicographically smaller than '9'.

Furthermore, MIN(SomeColumn) is not a full-text search query, and thus is not very efficient.

For a unique integer ID in FTS tables, you should use the internal docid column.

Upvotes: 0

Arth
Arth

Reputation: 13110

Is _id a numeric or a string?

With string comparison, '10' < '9'.

Try:

SELECT MIN(CAST(_id AS UNSIGNED)) FROM fts WHERE tbl_no=2 AND parent_id=6

To check. I would not use this in production however as it won't be able to use an index.

Upvotes: 1

Related Questions