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