Wednesday, March 21, 2012

Interesting issue with Full Text Search

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"arikatla" <arikatla@.gmail.com> wrote in message
news:1111779899.572048.98240@.z14g2000cwz.googlegro ups.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_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

You should probably post this in microsoft.public.sqlserver.fulltext 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

No comments:

Post a Comment