SQLSERVER FullText search troubleshooting

Question

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?

Answer 1

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!