1200371 - ContentResolver.query()'s wildcard % not performing as expected

Status: RESOLVED FIXED

(Firefox for Android Graveyard :: Awesomescreen, defect)

Description

[Allison Naaktgeboren :ally]

For performance, we'd really like to do the filtering of saved search terms in the sql. However, the querys are not matching correctly on the sql wildcard. It does not help that one cannot pass a full sql query to query(). Rather it breaks up the sql statement and does some of its own substitution.

Once the filtering works correctly, we can pass in LIMIT 4 and remove the "counter" variable from the do while loop.

[1] https://developer.android.com/reference/android/content/ContentResolver.html#query%28android.net.Uri,%20java.lang.String[],%20java.lang.String,%20java.lang.String[],%20java.lang.String%29

[2] http://stackoverflow.com/questions/2156363/how-to-filter-the-results-of-content-resolver-in-android



        ContentResolver cr = getContext().getContentResolver();
        String[] columns = new String[] { SearchHistory.QUERY };
        // questionmark must be outside of quote or considered literal by android
        String actualQuery = SearchHistory.QUERY +" LIKE ?";
        String[] queryArgs = new String[] { "'" +searchTerm + "%'" };
        String sortOrderAndLimit = SearchHistory.DATE +" DESC LIMIT 4";

        final Cursor c = cr.query(SearchHistory.CONTENT_URI, columns, actualQuery, queryArgs, sortOrderAndLimit);

-> finds only exact matches. Ignores wildcard at end.

Comment 1

[Richard Newman [:rnewman]]

?-substitution doesn't work the way you think it does. You're doing something a little like this:

  LIKE '\'foo\'%'

which isn't going to work for obvious reasons.

You'll need to process the string and create a literal LIKE expression, or find a way to do this that doesn't involve embedding ? in a string.

ni me if you need more.

Comment 2

[Allison Naaktgeboren :ally]

Why is it that we must avoid embedding ? in the search query? 

The android documentation instructs one to do exactly that, and we do pretty much the same thing here, using a ?: http://mxr.mozilla.org/mozilla-central/source/mobile/android/base/db/LocalBrowserDB.java#552.

Comment 3

[Mark Finkle (:mfinkle) (use needinfo?)]

I assume the way you are using ' in the queryArgs is the problem. See:
http://stackoverflow.com/questions/14262087/android-sqlite-wildcard-to-select-character-pattern

Based on everything I have seen, the code should look like:
String[] queryArgs = new String[] { searchTerm + "%" };

Comment 4

[Richard Newman [:rnewman]]

Yup. ? doesn't do direct substitution; it's a bind value. You were quoting the value, then effectively SQLite was quoting it again. 

But you probably can't directly concat, otherwise I can type

  %foo

in the URL bar, and we'll switch to matching "foo" anywhere in the search term!

Writing tests for this is the best way to narrow down to a solution.

Comment 5

[Allison Naaktgeboren :ally]

Attachment: SavedSearchesSQLissues

y'all are over-rotating on the quotes. Mcomella & I went through some dozen combinations, and this happened to be the last one.

It seems that a wild card of any sort causes a fatal exception in the 2nd argument.

Please note that today we settled on matching substrings, not just the start of the string (mfinkle will be pleased), so that change is also reflected in the sql.

Comment 6

[Michael Comella (:mcomella) [NI reported issues only: ex-Mozilla]]

Comment on attachment 8656874 [details] [diff] [review]
SavedSearchesSQLissues

Review of attachment 8656874 [details] [diff] [review]:
-----------------------------------------------------------------

If it works, wfm.

::: mobile/android/base/home/SearchEngineRow.java
@@ +216,5 @@
>      }
>      private void updateFromSavedSearches(String searchTerm, boolean animate, int suggestionCounter, int recycledSuggestionCount) {
>          final ContentResolver cr = getContext().getContentResolver();
>          String[] columns = new String[] { SearchHistory.QUERY };
> +        String actualQuery = SearchHistory.QUERY +" LIKE ?";

nit: spaces between operators. Here and next two lines.

nit: final here & below

Comment 7

[Pulsebot]

https://hg.mozilla.org/integration/fx-team/rev/2284e129af4e

Comment 8

[Carsten Book [:Tomcat]]

https://hg.mozilla.org/mozilla-central/rev/2284e129af4e