MATCH with selectArgs looking like queryTerm stopped working on Android 5.0+

Question

This is addendum to my question SQLiteDatabase Cursor empty only on Android 5.0+ devices. That one will be closed as I was not sure what is causing the bug - thought it was a cursor note being properly filled. Now I have discovered what is causing empty cursor.

Up to Android 5.0, search suggestions work by querying the database with the query:

SELECT rowid AS _id, suggest_text_1, suggest_text_2, rowid AS suggest_intent_data_id 
FROM fts 
WHERE (fts MATCH ?) //for example '*e*'

As of Android 5.0+, this does not work any more. I've done dozens of testings and the select query returns nothing to the Cursor object if I set selectionArgs to "*" + query + "*"

String selection = FTS3_TABLE + " MATCH ?";
String[] selectionArgs = new String[]{"*" + query + "*"};

On Android 5.0+ it will work ONLY if selectionArgs are either query or query + "*". If I put asterisk before the query, it will stop working.

NOTE: I tested by querying a letter e, not a special character.

Database querying looks like this and it worked flawlessly up to Android 5.0:

String[] columns = new String[]{
                BaseColumns._ID,
                KEY_QUESTION,
                KEY_ANSWER,
                SearchManager.SUGGEST_COLUMN_INTENT_DATA_ID};
String selection = FTS3_TABLE + " MATCH ?";
String[] selectionArgs = new String[]{"*" + query + "*"};    

//mColumnMap
map.put(KEY_QUESTION, KEY_QUESTION);
map.put(KEY_ANSWER, KEY_ANSWER);
map.put(KEY_CARDID, KEY_CARDID);
map.put(KEY_CARDSET, KEY_CARDSET);
map.put(BaseColumns._ID, "rowid AS " +
         BaseColumns._ID);
map.put(SearchManager.SUGGEST_COLUMN_INTENT_DATA_ID, "rowid AS " +
                SearchManager.SUGGEST_COLUMN_INTENT_DATA_ID);
map.put(SearchManager.SUGGEST_COLUMN_SHORTCUT_ID, "rowid AS " +
                SearchManager.SUGGEST_COLUMN_SHORTCUT_ID);
//mColumnMap part ends

SQLiteQueryBuilder builder = new SQLiteQueryBuilder();
        builder.setTables(FTS3_TABLE);
        builder.setProjectionMap(mColumnMap);

Cursor cursor = builder.query(db,
                columns, selection, selectionArgs, null, null, null);

String temp = DatabaseUtils.dumpCursorToString(cursor); //empty string on Android 5.0+

As you can see, nothing complex or extraordinary.

Why I can no longer use query with asterisk on both sides of it?

What has changed as of Android 5.0 in this part?

Off the cuff, I would assume that SQLite changed the FTS query rules and [Android 5.0 is using a newer edition of SQLite](http://stackoverflow.com/a/4377116/115145). According to [the FTS documentation](http://www.sqlite.org/fts3.html), they support token and token-prefix queries, but I do not where they say that they support token-postfix or token-infix (which would be ones where the asterisk wildcard appears at the beginning). — CommonsWare, Jun 08 '15 at 13:03
From Android 5.0, you can search for the strings beginning with `*`. So it will not work. Wildcards can only be used as suffix as of now. — Prerak Sola, Jun 08 '15 at 13:03

score 1 · Accepted Answer · edited May 23 '17 at 11:51

1

MATCH '*foo*' queries never worked correctly in any version of sqlite. The fact that you got some results earlier was just a coincidence. Just the prefix form MATCH 'foo*' (and MATCH 'foo') are supported.

Lollipop ships with a newer version of sqlite. For detailed list of changes between sqlite versions, see the changelog.

edited May 23 '17 at 11:51

Community

1
1

answered Jun 08 '15 at 13:18

laalto

150,114
66
286
303

Thanks. I personally never used it but found it in the code and got a bug when 5.0 appears. – sandalone Jun 08 '15 at 14:56

MATCH with selectArgs looking like queryTerm stopped working on Android 5.0+

1 Answers1

Linked

MATCH with selectArgs looking like *queryTerm* stopped working on Android 5.0+

1 Answers1

Linked

MATCH with selectArgs looking like queryTerm stopped working on Android 5.0+