mardi 30 juin 2015

sqlite3 with FTS4 table: Query returns wrong row

I have a weird issue with my FTS4 index in SQLite3, namely that a MATCH query for one term returns not the exact match but another, similar one, and vice versa.

Here is an example to illustrate it better:

SELECT name FROM test_idx WHERE name MATCH 'lehmbruck-museum';
-- "Lehmbruck-Archiv"
SELECT name FROM test_idx WHERE name MATCH 'lehmbruck-archiv';
-- "Lehmbruck-Museum"

It seems to have something to do with the dash, here is a similar case that exhibits the same behavior:

SELECT name FROM test_idx WHERE name MATCH 'some-thing';
-- "some-thang"
SELECT name FROM test_idx WHERE name MATCH 'some-thang';
-- "some-thing"

Here is how this test database is built, in case somebody wants to have a go at reproducing it:

CREATE VIRTUAL TABLE test_idx USING fts4(name);
INSERT INTO test_idx (name) VALUES
    ('some-thing'), ('some-thang'),
    ('Lehmbruck-Museum'), ('Lehmbruck-Archiv');

Aucun commentaire:

Enregistrer un commentaire