473,581 Members | 2,875 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_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

Jul 23 '05 #1
1 1647

"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
Jul 23 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
6799
by: Ravi Tallury | last post by:
Hi We are having issues with our application, certain portions of it stop responding while the rest of the application is fine. I am attaching the Java Core dump. If someone can let me know what the issue is. Thanks Ravi
4
6042
by: dave | last post by:
I am wondering if the following can be done. I want to setup a search page that utilizes full text searching in sql2000. I want the user to type in say "where is bill" and have the query search across multiple columns in multiple tables and return a single list of results so that i can show unique records that are returned. The problem...
0
2718
by: Tracey | last post by:
Hi there, I want to search Japanese characters with Full Text search function. I created a table (tbSearch) for Full-text search on SQL 2000, English Win2k Server. The table contains 4 fields, which are ID -- int, primary key, identity; PageName -- nvarchar; Contents -- ntext; Keywords -- ntext. Defined a Full Text index and Full Text...
3
15323
by: Trevor Fairchild | last post by:
I'm trying to execute a full-text query from a vb.net web application. The problem I have is that in SQL Server, the syntax for a full-text search is SELECT * FROM table WHERE CONTAINS( *, ' "searchstring" ') For whatever reason, VB won't run that search string unless I eliminate
10
2500
by: Lorenzo Gordon | last post by:
Hi all, I've trawled the archives in vain for a similar problem: In building a client's site, I've got a menu section where each menu heading sits in a table cell. It's _paramount_ that the text of the menu heading literally sits at the bottom of the cell, so that when you add the table border, the text looks underlined (this is not the...
1
5334
by: RAM | last post by:
Hello, I have installed SQL Server 2005 Express Edition Advanced Services and SQL Server 2005 Express Edition Toolkit SP1. I cannot use full-text search. When I called: exec sp_fulltext_service 'load_os_resources', 1; exec sp_fulltext_service 'verify_signature', 0; go I got error:
1
3012
by: Rathorevivek | last post by:
Hi All, I have been using full text search in my application for search purpose. But on certain words i.e Noise Words such as And,After,About etc. I am receiving the following error: Execution of a full-text operation failed. A clause of the query contained only ignored words. I tried a lot to sort out this issue,but this is going to...
4
2260
by: sharkyboy | last post by:
Hi, I am slightly confused. On our website www.the-ark.net there is a an advert strip down the right hand side. When viewed with IE or FF with windows XP the ad strip is visible. However the ad strip is invisible when viewed inn IE/FF with some Vista machines. This has been noticed on three seperate computers. Has anyone else come across...
0
1702
by: padmajapenmetsa | last post by:
Hi, We have the full text search table and are using CONTAINSTABLE to search in the fulltext search column, its workingfine, but we have a requirement that we need to return results which match a particular sequence in word. ex: The search text column has the word as 0203456-345567 if we give the input as " 0203456", the result is coming...
0
7882
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8157
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8181
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6564
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5366
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3835
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2309
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1410
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1145
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.