SQLiteDatabase Cursor empty only on Android 5.0+ devices

Question

The application has a SearchView which fetches suggestions from a specific database table. Everything worked without any errors until Android 5.0 appeared.

As of then, when the SQLiteQueryBuilder queries the database to fill the Cursor object, the return is empty cursor. Not NULL, but empty.

On other platforms, I can output the Cursor's content via DatabaseUtils.dumpCursorToString(cursorObject), but on Android 5.0+ the method reports output on null objects

Dumping cursor null
<<<<<

Even more: when I extract database file from 5.0+ devices and run the local SQL query, I can fetch all data. So the database is valid indeed. And the query is the simplest one

SELECT rowid AS _id, suggest_text_1, suggest_text_2, rowid AS suggest_intent_data_id 
FROM fts 
WHERE (fts MATCH '*e*') //<-- I pressed "e" on the keyboard

The logic for selecting data from the database and filling the Cursor object is really simple

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

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

I tried looking for some deprecated methods, but was without any luck.

I have already spent 3 days debugging each step in the process and I am out of any ideas what could be causing such behaviour.

Anyone has any ideas?

EDIT

The output of the method buildQuery()

String query = builder.buildQuery(columns, selection, null, null, null, null);

RESULT:   
SELECT rowid AS _id, suggest_text_1, suggest_text_2, rowid AS suggest_intent_data_id  
FROM fts   
WHERE (fts MATCH ?)

selection and selectionArgs parameters are created like this

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

The RAW query resulted the same thing as query via builder.query()

String query = builder.buildQuery(columns, selection, null, null, null, null);
Cursor temp = db.rawQuery(query, selectionArgs);
String output = DatabaseUtils.dumpCursorToString(temp);

Output: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@295023a7
        <<<<<

did you try to run that query using sqlite3 app on your device? or did you try to run SQLiteDatabase.rawQuery (String sql, String[] selectionArgs) ? — pskink, Jun 05 '15 at 15:02
if db.rawQuery() produces an empty Cursor them you will know why SQLiteQueryBuilder produces an empty Cursor, see http://androidxref.com/5.1.0_r1/xref/frameworks/base/core/java/android/database/sqlite/SQLiteQueryBuilder.java#372 — pskink, Jun 05 '15 at 15:08
@pskink So even deeper debugging :(. Thanks for trying to help. — sandalone, Jun 05 '15 at 15:38
Conversation moved to http://stackoverflow.com/q/30709715/437039 — sandalone, Jun 08 '15 at 12:55

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

2

Answered https://stackoverflow.com/a/30710226/437039 by laalto

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 Jun 20 '20 at 09:12

Community

1
1

answered Jun 08 '15 at 15:40

sandalone

41,141
63
222
338

score 0 · Answer 2 · answered Jun 05 '15 at 15:12

0

Have you tried to query using the SQLiteDatabase query method like the following?

String selection = "fts" + " =? AND " +

 String[] selectionArgs = new String [] {
                "*e*"
        };
 String[] projection = new String [] { "rowId", "suggested_text"};
 SQLiteDatabase db = mDbHelper.getReadableDatabase();
 Cursor cursor = db.query("tableName",
                projection,selection, selectionArgs, null, null, null);

answered Jun 05 '15 at 15:12

Proxy32

711
5
18

I have to use `MATCH`. `=` will not work in this case – sandalone Jun 05 '15 at 15:34
This may help resolve your issue : http://stackoverflow.com/questions/16022427/android-sqlite-query-to-match-column-containing-text – Proxy32 Jun 05 '15 at 18:07
This is also another link to someone with the same issue as you : http://stackoverflow.com/questions/9428069/android-sqlite-match-returns-nothing – Proxy32 Jun 05 '15 at 18:09

SQLiteDatabase Cursor empty only on Android 5.0+ devices

2 Answers2

Linked