FTS5 Trigram Tokenizer with remove_diacritics: Troubleshooting and Solutions

Issue Overview: FTS5 Trigram Tokenizer and remove_diacritics Not Functioning as Expected

The core issue revolves around the FTS5 (Full-Text Search) extension in SQLite, specifically the trigram tokenizer with the remove_diacritics option. The user expects that when the remove_diacritics option is enabled, the FTS5 virtual table should be able to match text with diacritical marks (e.g., "pečiatka") against a search term without diacritics (e.g., "pec"). However, the observed behavior is that the FTS5 table only returns results when the search term includes the diacritical marks, which contradicts the expected functionality of the remove_diacritics option.

The user’s initial approach involves creating an FTS5 virtual table with the trigram tokenizer and the remove_diacritics option enabled. They then insert a row containing the text "pečiatka" and attempt to query the table using the LIKE operator with both diacritic-free and diacritic-inclusive search terms. The query with the diacritic-inclusive term ("%peč%") successfully returns the row, while the query with the diacritic-free term ("%pec%") does not. This behavior suggests that the remove_diacritics option is not functioning as intended, or that there is a misunderstanding of how it should be used.

The user’s confusion is further compounded by the fact that the FTS5 documentation states that the remove_diacritics option should enable the tokenizer to ignore diacritical marks during indexing and querying. This discrepancy between the expected and observed behavior indicates a potential issue with the configuration or usage of the FTS5 trigram tokenizer.

Possible Causes: Misconfiguration and Misunderstanding of FTS5 Trigram Tokenizer Behavior

There are several potential causes for the observed behavior, ranging from misconfiguration of the FTS5 virtual table to a misunderstanding of how the remove_diacritics option interacts with the trigram tokenizer. Below, we explore these causes in detail.

Misconfiguration of the FTS5 Virtual Table

One possible cause is that the FTS5 virtual table was not configured correctly. The user’s initial setup involves creating the table with the following command:

create virtual table test using fts5('text', tokenize='trigram remove_diacritics 1');

While this command appears to be correct, there are nuances in how the tokenize option is specified. The tokenize option accepts a space-separated list of tokenizer parameters, and the order in which these parameters are specified can affect the behavior of the tokenizer. In this case, the user has specified tokenize='trigram remove_diacritics 1', which is intended to enable the trigram tokenizer with the remove_diacritics option set to 1 (enabled). However, it is possible that the remove_diacritics option is not being applied correctly due to a misconfiguration.

Misunderstanding of the remove_diacritics Option

Another potential cause is a misunderstanding of how the remove_diacritics option works in conjunction with the trigram tokenizer. The remove_diacritics option is designed to strip diacritical marks from text during both indexing and querying, allowing for case-insensitive and diacritic-insensitive searches. However, the user’s query uses the LIKE operator, which is not the recommended way to query FTS5 tables. The FTS5 extension is optimized for use with the MATCH operator, which is specifically designed for full-text search.

The user’s initial query:

select * from test where text like '%pec%';

This query uses the LIKE operator, which is a pattern-matching operator that does not leverage the full-text search capabilities of FTS5. As a result, the remove_diacritics option may not be applied as expected when using LIKE. The FTS5 documentation explicitly states that the remove_diacritics option is intended for use with the MATCH operator, and using LIKE may bypass the intended functionality.

Interaction Between LIKE and remove_diacritics

The FTS5 documentation also notes that the remove_diacritics option is primarily designed to work with the MATCH operator, and its behavior with the LIKE operator may be inconsistent. Specifically, the documentation states:

Unless the remove_diacritics option is set, FTS5 tables that use the trigram tokenizer also support indexed GLOB and LIKE pattern matching. … If an FTS5 trigram tokenizer is created with the case_sensitive option set to 1, it may only index GLOB queries, not LIKE.

This suggests that the remove_diacritics option may not be fully compatible with the LIKE operator, and that using MATCH is the recommended approach for leveraging the full capabilities of the FTS5 trigram tokenizer.

Troubleshooting Steps, Solutions & Fixes: Correct Usage of FTS5 Trigram Tokenizer with remove_diacritics

To resolve the issue and ensure that the FTS5 trigram tokenizer with the remove_diacritics option functions as expected, the following troubleshooting steps and solutions are recommended.

Step 1: Verify FTS5 Virtual Table Configuration

The first step is to verify that the FTS5 virtual table is configured correctly. The user’s initial command:

create virtual table test using fts5('text', tokenize='trigram remove_diacritics 1');

This command should create an FTS5 virtual table with the trigram tokenizer and the remove_diacritics option enabled. However, it is important to ensure that the tokenize option is specified correctly. The correct syntax for enabling the remove_diacritics option is:

create virtual table test using fts5('text', tokenize='trigram remove_diacritics=1');

Note the use of remove_diacritics=1 instead of remove_diacritics 1. This ensures that the option is set correctly and that the tokenizer will strip diacritical marks during indexing and querying.

Step 2: Use the MATCH Operator for Full-Text Search

The next step is to use the MATCH operator instead of the LIKE operator when querying the FTS5 virtual table. The MATCH operator is specifically designed for full-text search and will leverage the capabilities of the FTS5 trigram tokenizer, including the remove_diacritics option.

The user’s initial query:

select * from test where text like '%pec%';

Should be replaced with:

select * from test where text match 'pec';

This query uses the MATCH operator, which will apply the remove_diacritics option and return results that match the search term "pec" regardless of diacritical marks.

Step 3: Test the FTS5 Virtual Table with Correct Queries

After ensuring that the FTS5 virtual table is configured correctly and that the MATCH operator is used for queries, the next step is to test the table with the correct queries. The following example demonstrates the correct usage:

-- Create the FTS5 virtual table with the trigram tokenizer and remove_diacritics option
create virtual table test using fts5('text', tokenize='trigram remove_diacritics=1');

-- Insert a row with text containing diacritical marks
insert into test values ('pečiatka');

-- Query the table using the MATCH operator
select 'one', * from test where text match 'pec';
select 'two', * from test where text match 'peč';

The expected results are:

one|pečiatka
two|pečiatka

This demonstrates that the remove_diacritics option is functioning correctly, as both queries return the row containing "pečiatka" regardless of whether the search term includes diacritical marks.

Step 4: Review FTS5 Documentation for Additional Options

Finally, it is important to review the FTS5 documentation for any additional options or settings that may affect the behavior of the trigram tokenizer. For example, the case_sensitive option can be used to control whether the tokenizer is case-sensitive or case-insensitive. The documentation states:

If an FTS5 trigram tokenizer is created with the case_sensitive option set to 1, it may only index GLOB queries, not LIKE.

This suggests that the case_sensitive option can affect the behavior of the tokenizer, particularly when using the LIKE operator. If the case_sensitive option is set to 1, the tokenizer may not support LIKE queries, and using MATCH is recommended.

Conclusion

In conclusion, the issue with the FTS5 trigram tokenizer and the remove_diacritics option can be resolved by ensuring that the FTS5 virtual table is configured correctly, using the MATCH operator for queries, and reviewing the FTS5 documentation for any additional options or settings that may affect the behavior of the tokenizer. By following these troubleshooting steps and solutions, users can ensure that the FTS5 trigram tokenizer functions as expected and provides accurate and consistent results for full-text search queries.