Interesting issue with Full Text Search

[arikatla]

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_searc h_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_searc h_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_NA ME, '"JOHN" NEAR
"JANE"') (this returns 20 records)
select COUNT(*) from TABLE_NAME WHERE CONTAINS(COL_NA ME, '"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_NA ME, '(("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_NA ME, '("JOHN"
NEAR "JANE")')
OR contains(DOC_IN FO, '("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

[Simon Hayes]

"arikatla" <ar******@gmail .com> wrote in message
news:11******** *************@z 14g2000cwz.goog legroups.com...

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_searc h_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_searc h_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_NA ME, '"JOHN" NEAR
"JANE"') (this returns 20 records)
select COUNT(*) from TABLE_NAME WHERE CONTAINS(COL_NA ME, '"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_NA ME, '(("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_NA ME, '("JOHN"
NEAR "JANE")')
OR contains(DOC_IN FO, '("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

You should probably post this in microsoft.publi c.sqlserver.ful ltext to get
a better answer. But I suggest you take the time first to create a repro
script which creates a table, inserts data, creates the FT indexes and then
runs a query to show the problem. If other people can reproduce what you're
seeing, you're more likely to get a response.

Simon