We are using SQL Server 2000 database (with sp3) and recently we faced
an interesting issue with full text search.
According to SQLServer help page
"AND | AND NOT | OR Specifies a logical operation between two contains
search conditions. When <contains_search_condition> contains
parenthesized groups, these parenthesized groups are evaluated first.
After evaluating parenthesized groups, these rules apply when using
these logical operators with contains search conditions".
Our "contains_search_condition" consists of a proximity_term and each
contains_search_condition is combined with logical OR operator.
When we execute each proximity term separately we are fetching correct
set of records. Here are the SQL statements:
select COUNT(*) from TABLE_NAME WHERE CONTAINS(COL_NAME, '"JOHN" NEAR
"JANE"') (this returns 20 records)
select COUNT(*) from TABLE_NAME WHERE CONTAINS(COL_NAME, '"JOHN" NEAR
"DOE"') (this returns 10 records)
If we join these records with an OR operator and pass them to a
CONTAINS clause, we are fetching records that are larger than the
summation
of records that correspond SQL statements given above. This really
can't happen. Here is the corresponding SQL statement:
select COUNT(*) from TABLE_NAME WHERE CONTAINS(COL_NAME, '(("JOHN"
NEAR "JANE") OR ("JOHN" NEAR "DOE"))') (this returns 80 records)
Furthermore, if I write the same statement in terms of two CONTAINS
statements with an OR operator, then I fetch correct number of records.
Corresponding SQL is:
select COUNT(*) from TABLE_NAME WHERE CONTAINS(COL_NAME, '("JOHN"
NEAR "JANE")')
OR contains(DOC_INFO, '("JOHN" NEAR "DOE")') (this returns 25
records)
Does anyone know if there is a bug in SQLServer 2000 Full Text Search?
I will appreciate if anbody can shed some
light into my problem
