469,112 Members | 2,010 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,112 developers. It's quick & easy.

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

Jul 23 '05 #1
1 1503

"arikatla" <ar******@gmail.com> wrote in message
news:11*********************@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
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Ravi Tallury | last post: by
3 posts views Thread by Trevor Fairchild | last post: by
10 posts views Thread by Lorenzo Gordon | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.