Containstable with Top_n_by_rank returning less than expected rows.   

Hello,

I've got a full text index, with an empty noise word file. The table
has only 1 column + 1 timestamp for doing incremental updates (data
changes only once a day, in 1 time). Change Tracking is disabled
(OFF). SQL version is 2005 SP2 (9.0.3054)

From time to time (after a few days), ContainsTable queries with a
Top_n_by_rank clause are returning strange results :

SELECT * FROM CONTAINSTABLE(FullSearch, *, 'the')
=> Returns 12362 rows

SELECT * FROM CONTAINSTABLE(FullSearch, *, 'the', 5000)
=> Returns 569 rows

SELECT * FROM CONTAINSTABLE(FullSearch, *, 'the', 500)
=> Returns 80 rows.

I was expecting the 2nd and 3rd queries to return respectively 5000
and 500 rows, as there are 12362 rows matching the query.

Usually FTS has the correct behavior (returning 5000 and 500 results
for the query 2&3), but from time to time it seems to bug this way.
When this happens, a full or Incremental population, or even
optimizing the catalog without rebuilding it doesn't fix the problem.
In that case, the only method to fix this is to rebuild the catalog,
and the perform a population. And then it will be good for a few days,
and then start again returning less rows than expected.

Is that a known bug from FTS? Is there a way to fix this without
rebuilding the catalog? Thanks.

date: Wed, 2 Apr 2008 06:40:19 -0700 (PDT)   author:   unknown

RE: Containstable with Top_n_by_rank returning less than expected rows   

Hi,

Did you find any solutions to this problem?

Regards,

Fredrik

"pinformaticien@yahoo.fr" wrote:

> Hello,
> 
> I've got a full text index, with an empty noise word file. The table
> has only 1 column + 1 timestamp for doing incremental updates (data
> changes only once a day, in 1 time). Change Tracking is disabled
> (OFF). SQL version is 2005 SP2 (9.0.3054)
> 
> From time to time (after a few days), ContainsTable queries with a
> Top_n_by_rank clause are returning strange results :
> 
> SELECT * FROM CONTAINSTABLE(FullSearch, *, 'the')
> => Returns 12362 rows
> 
> SELECT * FROM CONTAINSTABLE(FullSearch, *, 'the', 5000)
> => Returns 569 rows
> 
> SELECT * FROM CONTAINSTABLE(FullSearch, *, 'the', 500)
> => Returns 80 rows.
> 
> I was expecting the 2nd and 3rd queries to return respectively 5000
> and 500 rows, as there are 12362 rows matching the query.
> 
> Usually FTS has the correct behavior (returning 5000 and 500 results
> for the query 2&3), but from time to time it seems to bug this way.
> When this happens, a full or Incremental population, or even
> optimizing the catalog without rebuilding it doesn't fix the problem.
> In that case, the only method to fix this is to rebuild the catalog,
> and the perform a population. And then it will be good for a few days,
> and then start again returning less rows than expected.
> 
> Is that a known bug from FTS? Is there a way to fix this without
> rebuilding the catalog? Thanks.
>

date: Wed, 30 Apr 2008 00:31:01 -0700   author:   Fredrik