title: Full Text Search FTS4 error - unable to use function MATCH
body: <p>I am trying to implement FTS4 in my app which also uses Room. It's not a very complicated set-up based on the documentation and tutorials I have read but I have a persistent error triggered by my query which I can't seem to figure out.</p>
  <p>My fts table is being created and populated correctly as I can see in the db inspector. Also, I can run queries on it and with no problems until I try to run any query with MATCH. I get the following</p>
  <pre><code>Error retrieving data from table.: unable to use function MATCH in the requested context (code 1 SQLITE_ERROR)
  </code></pre>
  <p>For instance, this works (although it doesn't make sense to use):</p>
  <pre><code>SELECT * FROM community_features JOIN features_fts ON community_features.rowid = features_fts.rowid WHERE features_fts.feature_name LIKE '%TEST%'
  </code></pre>
  <p>But this doesn't:</p>
  <pre><code>SELECT * FROM community_features JOIN features_fts ON community_features.rowid = features_fts.rowid WHERE features_fts.feature_name MATCH '*TEST*'
  </code></pre>
  <p>UPDATE - Comparing my app to another, I noticed that their FTS is generating several more files than mine. In my database inspector I can see the completed features_fts table but I don't see the _docsize, _segdir, _segments or _stat tables that appear in the other app. I'm guessing these should be autogenerated somehow and that's why my MATCH function won't work. Did I miss a step in the process?</p>
  <p>Below are my files:</p>
  <p>DB set-up</p>
  <pre><code>    public class FeatureIndexDBClient {
  
      private final FeatureIndexDB mDb;
      private static String mDbName;
      private static FeatureIndexDBClient mInstance;
  
      private FeatureIndexDBClient(Context context, String dbName) {
          if(mDbName == null || !mDbName.equalsIgnoreCase(dbName)) {
              mDbName = dbName;
          }
          mDb = Room.databaseBuilder(context, FeatureIndexDB.class, dbName)
                  .addCallback(new RoomDatabase.Callback() {
                      @Override
                      public void onCreate(@NonNull SupportSQLiteDatabase db) {
                          super.onCreate(db);
                          db.execSQL(&quot;INSERT INTO features_fts(feature_name) VALUES ('rebuild')&quot;);
                      }
                  })
                  .fallbackToDestructiveMigration()
                  .build();
      }
  
      public static synchronized FeatureIndexDBClient getInstance(Context context, String dbName) {
          if (mInstance == null || mDbName == null || !mDbName.equalsIgnoreCase(dbName)) {
              mInstance = new FeatureIndexDBClient(context, dbName);
          }
          return mInstance;
      }
  
      public FeatureIndexDB getAppDatabase() {
          return mDb;
      }
  
      public void close() {
          this.mDb.close();
      }
  
      public SimpleSQLiteQuery getFeatureFTSQuery() {
          return new SimpleSQLiteQuery(&quot;INSERT INTO features_fts(feature_name) SELECT community_features.feature_name FROM community_features&quot;);
      }
  }
  </code></pre>
  <p>Entities:</p>
  <pre><code>@Entity(tableName = &quot;community_features&quot;)
  public class OSCommunityFeature implements Parcelable {
  
      @NonNull
      @PrimaryKey(autoGenerate = true)
      @ColumnInfo(name = &quot;rowid&quot;)
      private Long rowId;
  
      @ColumnInfo(name = &quot;_id&quot;)
      private String _id;
  
      @ColumnInfo(name = &quot;community_id&quot;)
      private int communityId;
  
      @ColumnInfo(name = &quot;feature_id&quot;)
      private int featureId;
  
      @ColumnInfo(name = &quot;feature_type&quot;)
      private Integer featureType;
  
      @ColumnInfo(name = &quot;feature_name&quot;)
      private String feature_name;
  
      public OSCommunityFeature() {
  
      }
  
      ... constructors, getters, setters
  }
  
  
  
  @Entity(tableName = &quot;features_fts&quot;)
  @Fts4(contentEntity = OSCommunityFeature.class)
  public class CommunityFeatureFTS {
  
      @PrimaryKey
      @ColumnInfo(name = &quot;rowid&quot;)
      private Long rowId;
  
      @ColumnInfo(name = &quot;feature_name&quot;)
      private String feature_name;
  
      public CommunityFeatureFTS() {
      }
  
      public CommunityFeatureFTS(Long id, String name) {
          this.rowId = id;
          this.feature_name = name;
      }
  </code></pre>
  <p>The table is populated by after retrieving data from the server. Once the data is loaded into the db I then call:</p>
  <pre><code>&quot;INSERT INTO features_fts(feature_name) SELECT community_features.feature_name FROM community_features&quot;
  </code></pre>
  <p>My dao is as follows:</p>
  <pre><code>@Dao
  public interface FeatureIndexDAO {
  
  // Community Features --------------------------------------------------------------------------
  
  @Insert(onConflict = OnConflictStrategy.REPLACE)
  void insertAllFeatures(List&lt;OSCommunityFeature&gt; features);
  
  @Transaction
  @Query(&quot;SELECT * FROM community_features&quot;)
  List&lt;OSCommunityFeature&gt; getAllFeatures();
  
  @RawQuery
  boolean setFeatureFTSTable(SupportSQLiteQuery query);
  
  @Query(&quot;SELECT * FROM community_features JOIN features_fts ON community_features.rowid = features_fts.rowid WHERE features_fts.feature_name MATCH :searchFor&quot;)
  List&lt;OSCommunityFeature&gt; searchForFeatures(String searchFor);
  </code></pre>
  <p>Gradle:</p>
  <pre><code>implementation &quot;androidx.room:room-runtime:2.2.6&quot;
  annotationProcessor  'android.arch.persistence.room:compiler:2.2.6'
  </code></pre>
  <p>I have been reading everything I can find but have been using <a href="https://www.raywenderlich.com/14292824-full-text-search-in-room-tutorial-getting-started" rel="nofollow noreferrer">this tutorial</a> and <a href="https://medium.com/@sienatime/enabling-sqlite-fts-in-room-2-1-75e17d0f0ff8" rel="nofollow noreferrer">this</a>. I can download the example project from the first link, build it, and run it with no problems. I really can't tell what the difference could be. I've been going back and forth for about a day now.</p>
  <p>Anybody have any hints or clues that might point me in the right direction?</p>