FullTextSqlQuery returning incorrect results

Question

We are having usual behaviour with FullTextSqlQuery and wonder if anyone can help.

The following should return 3 records which represent 3 pages on the site with titles "Sample (test1)", "Sample (test2)", and "Sample (test3)".

SELECT Title, Rank, Size, Description, Write, Path
FROM portal..scope()
WHERE ( ("SCOPE" = 'myScope') )  AND CONTAINS ('"sample"')
ORDER BY "Rank" DESC

For some reason this only yeilds 1 result, say "Sample (test3)".

If we change this to use a query as follows, then this also returns a single result. However this time the result is a different record, say "Sample (test3)", which proves more than 1 entry was indexed by the crawl.

SELECT Title, Rank, Size, Description, Write, Path
FROM portal..scope()
WHERE ( ("SCOPE" = 'myScope') )  AND FREETEXT(defaultproperties, '"sample"')
ORDER BY "Rank" DESC

Why are these queries not returning all 3 results? We have tried setting TrimDuplicates to false but it makes no difference.

Answer 1

How does the duplicate document is identified when we do a search?

Document similarity for purposes of identifying duplicates is based only on a hash of the content of the document. No File properties (e.g. file name, type, author, create and modify dates) are input to this hash. The MSSDuplicateHashes table in the SSP’s search database holds, for each document, all the 64bit hashes necessary to determine if one document is a near-duplicate of another. This is read while doing a search if duplicate collapsing is enabled.

Source: http://blogs.technet.com/b/harikumh/archive/2008/11/14/some-interesting-facts-about-sharepoint-2007-search.aspx

Answer 2

have you tried expanding the scope to 'All Sites'?