issues: 1243151184

This data as json

id node_id number title user state locked assignee milestone comments created_at updated_at closed_at author_association pull_request body repo type active_lock_reason performed_via_github_app reactions draft state_reason
1243151184 I_kwDOCGYnMM5KGPtQ 434 `detect_fts()` identifies the wrong table if tables have names that are subsets of each other 559711 closed 0     3 2022-05-20T13:28:31Z 2022-06-14T23:24:09Z 2022-06-14T23:24:09Z NONE  

Windows 10 Python 3.9.6

When I was running a full text search through the Python library, I noticed that the query was being run on a different full text search table than the one I was trying to search.

I took a look at the following function

https://github.com/simonw/sqlite-utils/blob/841ad44bacaff05ec79ef78166d12e80c82ba6d7/sqlite_utils/db.py#L2213

and noticed:

python sql LIKE '%VIRTUAL TABLE%USING FTS%content=%{table}%'

My database contains tables with similar names and %{table}% was matching another table that ended differently in its name. I have included a sample test that shows this occurring:

I search for Marsupials in db["books"] and The Clue of the Broken Blade is returned.

This occurs since the search for Marsupials was "successfully" done against db["booksb"] and rowid 1 is returned. "The Clue of the Broken Blade" has a rowid of 1 in db["books"] and this is what is returned from the search.

```python def test_fts_search_with_similar_table_names(fresh_db): db = Database(memory=True) db["books"].insert_all( [ { "title": "The Clue of the Broken Blade", "author": "Franklin W. Dixon", }, { "title": "Habits of Australian Marsupials", "author": "Marlee Hawkins", }, ] ) db["booksb"].insert( { "title": "Habits of Australian Marsupials", "author": "Marlee Hawkins", } )

db["booksb"].enable_fts(["title", "author"])
db["books"].enable_fts(["title", "author"])


query = "Marsupials"

assert [
        {   "rowid": 1,
            "title": "Habits of Australian Marsupials",
            "author": "Marlee Hawkins",
        },
    ] == list(db["books"].search(query))

```

 140912432 issue     
{
    "url": "https://api.github.com/repos/simonw/sqlite-utils/issues/434/reactions",
    "total_count": 0,
    "+1": 0,
    "-1": 0,
    "laugh": 0,
    "hooray": 0,
    "confused": 0,
    "heart": 0,
    "rocket": 0,
    "eyes": 0
}
   completed

Links from other tables