FTS3/4 MATCH not supported?

[nappy]

In the sqlcipher documentation I read, that one should favor FTS over LIKE for performance. I wonder why I get the following error. I reduced my real world example to this simplified case:

Schema:
CREATE VIRTUAL TABLE sites USING fts4(domain, url, title, meta_keys, body)
CREATE TABLE keywords (keyword TEXT)

Query:
SELECT keyword FROM keywords INNER JOIN sites ON sites.title MATCH keywords.keyword

I get a info.guardianproject.database.sqlcipher.SQLiteException: "SQL logic error or missing database".

info.guardianproject.database.sqlcipher.SQLiteQuery.native_fill_window(Native Method)
info.guardianproject.database.sqlcipher.SQLiteQuery.fillWindow(SQLiteQuery.java:73)
info.guardianproject.database.sqlcipher.SQLiteCursor.fillWindow(SQLiteCursor.java:290)
info.guardianproject.database.sqlcipher.SQLiteCursor.getCount(SQLiteCursor.java:271)
android.database.CursorWrapper.getCount(CursorWrapper.java:51)

[developernotes]

Hi Nappy,

We've added a test that exercises your example of the full text search issue you reported. Have you resolved your issue? Our example that passes can be found here:

https://github.com/sqlcipher/sqlcipher-android-tests/blob/master/src/main/java/net/zetetic/tests/FullTextSearchTest.java

[nikhilmahesh]

I am facing similar issue.

Query with MATCH not returning any results:
SELECT call_id, caller, recipient, start_time, end_time FROM call_record WHERE caller MATCH 'j*';

Query with LIKE is providing Results:
SELECT call_id, caller, recipient, start_time, end_time FROM Call_record where caller LIKE 'j%';

https://stackoverflow.com/questions/44304688/match-keyword-full-text-search-not-working-with-sqlite3-c-api

[sjlombardo]

@nikhilmahesh were you able to run the test case that @developernotes mentioned in the earlier post? Does your example work if you create using fts4 instead of fts3?

[nikhilmahesh]

@sjlombardo : I am able to do it with FTS5 instead.

[sjlombardo]

@nikhilmahesh I'm glad to hear that fts5 worked for you. I don't think the tokenizer should have any impact on the behavior in this case, since each column is a single term and it shouldn't affect the substring MATCH functionality. Since it sounds like you are using the C API directly, I have a few other questions:

1. Are you using a packaged version of SQLCipher for Android, or one you built yourself?
2. If the former, what version of SQLCipher for Android are you using?
3. If the latter, what build flags are you using to compile the library?
4. Are you able to reproduce this in a standalone test for the SQLCipher for Android test suite? https://github.com/sqlcipher/sqlcipher-android-tests