CODEWITHSUNDEEP
sqlsql-server
tonysepia
tonysepia

Reputation: 3510

SQL CONTAINS query doesn't work as expected

I have a table with nchar(32) column, with FullText search and auto change tracking activated.

Data set:

Name, SuperID 
Item1, (LO}(bJ$
Item2, (LO}(bJ$
Item3, (LO}(bJ$

I run two queries:

select Name, SuperID from Asset A WHERE A.SuperID LIKE
N'%(LO}(bJ$%'

and

select Name, SuperID from Asset A WHERE (   CONTAINS
(A.SuperID ,N'"(LO}(bJ$"')  )

The first one returns 3 rows, and the second returns only 1 row.

Why is it happening? How can I analyse the contents of the Full Text search index? The column values in the first three results are identical. I have tried to update indexes/stats but that doesn't change the outcome

UPDATE: I have tried removing quotes around the CONTAINS query but I get an error message:

"Syntax error near '(' in the full-text search condition

UPDATE2:

I should have phrased my question in a different way, I think! Have a look at the SuperID column values for the three rows that I would like to return. I have converted them to VARBINARY to ensure that there is no hidden trailing space trickery going on and they are actually the same:

SuperIDasBinary
0x28004C004F007D00280062004A0024002...
0x28004C004F007D00280062004A0024002...
0x28004C004F007D00280062004A0024002...

So why does the CONTAINS query above return only one row instead of three?

Upvotes: 1

Views: 478

Answers (2)

tonysepia
tonysepia

Reputation: 3510

Whilst trying to re-create the problem with a fresh table in a separate DB, I ended up comparing the Full-Text catalogue settings. In the DB where I saw the erroneous behaviour the Table Full-Text Pending Changes value was 21219 (!).

I have then checked the FT log and found that the Asset table hasn't been updated for a long time:

An internal query to load data for a crawl on database '-' and table 'Asset' failed with error code 1205. Check the sql error code for more information about the condition causing this failure. The crawl needs to be restarted after this condition is removed.

and also

Error: 30059, Severity: 16, State: 1 A fatal error occurred during a full-text population and caused the population to be cancelled. Population type is: AUTO; database name is Media (id: 6); catalog name is - (id: 6); table name Asset (id: 1076966963). Fix the errors that are logged in the full-text crawl log. Then, resume the population. The basic Transact-SQL syntax for this is: ALTER FULLTEXT INDEX ON table_name RESUME POPULATION."

I had to manually resume the FT updates. After that the search has returned 3 items!

Upvotes: 1

HoneyBadger
HoneyBadger

Reputation: 15150

There is a list with noise words/characters which are ignored, this is known as the stoplist. You can ALTER your index with with change_tracking auto, stoplist off. You can test with select * from sys.dm_fts_parser('"(LO}(bJ$"', 1033, null, 0). This will result in 3 search tokens, rather than the 2 you have now. The null in this command represent the stoplist to use, or rather as it is null, that no stoplist should be used. This, however, only solves the $, the other characters are still being ignored. I expect this helps to fix your problem but I can't test it myself at the moment.

Upvotes: 1

Related Questions