System.Data.Sqlite and FTS4

Question

Why when i write a query with fulltext Search syntax like:

SELECT * FROM TABLENAME WHERE TABLENAME MATCH 'ColumnA:word1 OR ColumnB:word2'

The query result always return 0 rows?

I'm using VBnet and the latest Ado.net provider from sqlite.org

The problem is when i try the query with and external tool, it works well (with sqlite 3.7.9).

Any clues? Thanks in advance

Answer 1

Although System.Data.Sqlite is compiled using SQLITE_ENABLE_FTS3 and supports FTS, it doesn't compiled with SQLITE_ENABLE_FTS3_PARENTHESIS which enables Enhanced Query Syntax. And you are using Enhanced Query Syntax in your query, thus it didn't works as you expect.

You need to either modify you query, or do a custom build (follow build procedures) of System.Data.Sqlite with SQLITE_ENABLE_FTS3_PARENTHESIS defined.