CODEWITHSUNDEEP
sql-serverfull-text-search
josejuan
josejuan

Reputation: 9566

Extraneous inflection behavior on SQL Server full-text search

Searching certain words (e.g. agarte) using CONTAINS on a table with only one row and one text field with only one word (e.g. te), SQL Server find that row as valid result. (I'm looking for agarte and te is valid?)

I've tested on two different SQL Server 2008 and other SQL Server 2012 at different servers.

I think is related with inflectional forms but I cannot understand why and how modify this behavior.

One complete runnable test is:

SET NOCOUNT ON
GO
SELECT version = @@VERSION
GO
CREATE TABLE foo (k int identity(1,1), f nvarchar(max) not null, CONSTRAINT PK_foo PRIMARY KEY CLUSTERED (k ASC))
GO
INSERT INTO foo (f) VALUES ('te')
GO
CREATE FULLTEXT CATALOG foo_catalog
GO
CREATE FULLTEXT INDEX ON foo(f) KEY INDEX PK_foo ON foo_catalog WITH STOPLIST = OFF
GO
ALTER FULLTEXT CATALOG foo_catalog REBUILD WITH ACCENT_SENSITIVITY = OFF
GO
WAITFOR DELAY '00:00:05'
GO
SELECT COUNT(*) FROM foo WHERE CONTAINS(*, '"agarte"', language 3082) -- 1
SELECT COUNT(*) FROM foo WHERE CONTAINS(*, '"egarte"', language 3082) -- 1
SELECT COUNT(*) FROM foo WHERE CONTAINS(*, '"ogarte"', language 3082) -- 1
SELECT COUNT(*) FROM foo WHERE CONTAINS(*, '"garte"' , language 3082) -- 1
SELECT COUNT(*) FROM foo WHERE CONTAINS(*, '"gurte"' , language 3082) -- 0
GO
DROP FULLTEXT INDEX ON foo
GO
DROP FULLTEXT CATALOG foo_catalog
GO
DROP TABLE foo
GO

I'm going to set neutral language on searchs as workaround but I would like understand why and how control this behavior. Thx!

Upvotes: 0

Views: 219

Answers (1)

josejuan
josejuan

Reputation: 9566

The problem looks to be the wordbraker

If we run

SELECT * FROM sys.dm_fts_parser (' "ugarte" ', 3082, 0, 0)

get

keyword                    group_id phrase_id occurrence special_term display_term expansion_type source_term
-------------------------- -------- --------- ---------- ------------ ------------ -------------- -----------
0x0075006700610072         1        0         1          Exact Match  ugar         0              ugarte
0x00740065                 1        0         1          Noise Word   te           0              ugarte
0x007500670061007200740065 1        0         1          Exact Match  ugarte       0              ugarte

with ogorte and others not occur.

Without down to IFilter looks not possible avoid that word break.

Related information:

Upvotes: 1

Related Questions