SQLite FTS4 Offsets Mismatch with Diacritics in Latin Characters

Understanding the Impact of Diacritics on FTS4 Offsets in SQLite

When working with SQLite’s Full-Text Search (FTS) extension, particularly FTS4, one of the most powerful features is the ability to tokenize and search text efficiently. However, a common issue arises when dealing with diacritics in Latin script characters. The offsets function, which is crucial for identifying the positions of matched terms in the text, can return inconsistent results when diacritics are present. This discrepancy occurs because the offsets function returns byte offsets in UTF-8 encoding, and diacritics can alter the byte length of the text. This post will delve into the nuances of this issue, explore the underlying causes, and provide detailed troubleshooting steps and solutions to ensure accurate offset calculations in the presence of diacritics.

The Role of UTF-8 Encoding and Diacritics in FTS4 Offsets

The core of the issue lies in the interaction between UTF-8 encoding and the offsets function in SQLite’s FTS4 extension. UTF-8 is a variable-width character encoding, meaning that different characters can occupy different numbers of bytes. For example, the character ‘í’ (Latin small letter i with acute) in the word ‘Así’ occupies two bytes in UTF-8, whereas the character ‘i’ in ‘Asi’ occupies only one byte. This difference in byte length directly affects the byte offsets returned by the offsets function.

When you create an FTS4 table with the tokenize=unicode61 "remove_diacritics=2" option, SQLite is configured to remove diacritics during tokenization. However, this does not change the underlying byte representation of the text stored in the database. As a result, when you query the offsets function, it returns the byte offsets based on the original UTF-8 encoded text, which includes the diacritics. This leads to a mismatch between the expected character offsets and the actual byte offsets, especially when diacritics are present.

For instance, consider the following example:

CREATE VIRTUAL TABLE fts_table USING fts4 (text_column, tokenize=unicode61 "remove_diacritics=2");
INSERT INTO fts_table (text_column) VALUES ('Así volvió de los campos en el principio');
INSERT INTO fts_table (text_column) VALUES ('Asi volvio de los campos en el principio');
SELECT offsets(fts_table), text_column FROM fts_table WHERE text_column MATCH '"en el principio"';

The result of this query shows different byte offsets for the same phrase ‘en el principio’ in the two rows:

offsets(fts_table) text_column
0 0 27 2 0 1 30 2 0 2 33 9 Así volvió de los campos en el principio
0 0 25 2 0 1 28 2 0 2 31 9 Asi volvio de los campos en el principio

In the first row, the phrase ‘en el principio’ starts at byte offset 27, whereas in the second row, it starts at byte offset 25. This discrepancy is due to the additional bytes occupied by the diacritics in the first row.

Strategies for Accurate Offset Calculation with Diacritics

To address the issue of inconsistent byte offsets caused by diacritics, several strategies can be employed. These strategies involve understanding the relationship between character offsets and byte offsets, and implementing techniques to ensure that the offsets returned by the offsets function align with the actual positions of the matched terms in the text.

One approach is to convert the byte offsets returned by the offsets function into character offsets. This can be achieved by iterating through the text and counting the number of characters up to each byte offset. However, this method can be computationally expensive, especially for large texts, as it requires processing the entire text for each query.

Another approach is to normalize the text before inserting it into the FTS4 table. Normalization involves converting the text to a form where diacritics are removed or replaced with their base characters. This can be done using Unicode normalization forms, such as NFC (Normalization Form C) or NFD (Normalization Form D). By normalizing the text, you can ensure that the byte offsets returned by the offsets function are consistent, regardless of the presence of diacritics.

For example, you can use the unaccent function in SQLite to remove diacritics from the text before inserting it into the FTS4 table:

INSERT INTO fts_table (text_column) VALUES (unaccent('Así volvió de los campos en el principio'));
INSERT INTO fts_table (text_column) VALUES (unaccent('Asi volvio de los campos en el principio'));

This approach ensures that the text stored in the FTS4 table is free of diacritics, and the byte offsets returned by the offsets function will be consistent with the character offsets.

Implementing a Robust Solution for FTS4 Offsets with Diacritics

To implement a robust solution for handling FTS4 offsets with diacritics, you need to combine the strategies discussed above. The first step is to normalize the text before inserting it into the FTS4 table. This can be done using a combination of Unicode normalization and the unaccent function. By normalizing the text, you ensure that the byte offsets returned by the offsets function are consistent with the character offsets.

The next step is to convert the byte offsets returned by the offsets function into character offsets. This can be achieved by writing a custom function in SQLite that iterates through the text and counts the number of characters up to each byte offset. This function can then be used in your queries to return the correct character offsets for the matched terms.

Here is an example of how you can implement this solution:

-- Step 1: Normalize the text before inserting it into the FTS4 table
CREATE VIRTUAL TABLE fts_table USING fts4 (text_column, tokenize=unicode61 "remove_diacritics=2");

-- Step 2: Insert normalized text into the FTS4 table
INSERT INTO fts_table (text_column) VALUES (unaccent('Así volvió de los campos en el principio'));
INSERT INTO fts_table (text_column) VALUES (unaccent('Asi volvio de los campos en el principio'));

-- Step 3: Create a custom function to convert byte offsets to character offsets
CREATE FUNCTION byte_to_char_offset(text TEXT, byte_offset INTEGER) RETURNS INTEGER AS $$
DECLARE
    char_offset INTEGER := 0;
    current_byte_offset INTEGER := 0;
    i INTEGER := 1;
BEGIN
    WHILE i <= LENGTH(text) AND current_byte_offset < byte_offset LOOP
        char_offset := char_offset + 1;
        current_byte_offset := current_byte_offset + LENGTH(SUBSTR(text, i, 1));
        i := i + 1;
    END LOOP;
    RETURN char_offset;
END;
$$ LANGUAGE plpgsql;

-- Step 4: Use the custom function in your queries to return the correct character offsets
SELECT 
    byte_to_char_offset(text_column, SUBSTR(offsets(fts_table), 1, INSTR(offsets(fts_table), ' ')-1)) AS start_char_offset,
    byte_to_char_offset(text_column, SUBSTR(offsets(fts_table), INSTR(offsets(fts_table), ' ')+1, INSTR(offsets(fts_table), ' ', INSTR(offsets(fts_table), ' ')+1)-INSTR(offsets(fts_table), ' ')-1)) AS end_char_offset,
    text_column 
FROM 
    fts_table 
WHERE 
    text_column MATCH '"en el principio"';

In this example, the byte_to_char_offset function is used to convert the byte offsets returned by the offsets function into character offsets. The function iterates through the text and counts the number of characters up to each byte offset, ensuring that the returned offsets are accurate, even in the presence of diacritics.

By following these steps, you can ensure that the offsets returned by the offsets function in SQLite’s FTS4 extension are consistent and accurate, regardless of the presence of diacritics in the text. This approach provides a robust solution for handling FTS4 offsets with diacritics, ensuring that your text search functionality works as expected.