FIX: Full-text queries that use the NEAR operator may return different results if the NEAR operands are reversed in SQL Server 2000

Article translations Article translations
Article ID: 888008 - View products that this article applies to.
Bug #: 471528 (SQL Server 8.0)
Expand all | Collapse all

On This Page

SYMPTOMS

When you run a full-text query that uses the NEAR operator in Microsoft SQL Server 2000, the number of rows that are returned may be different if the NEAR operands are reversed. For example, the following two queries may return different results:
  • SELECT * FROM IndexedTable WHERE CONTAINS (*, N'"abc" near "xyz"')
  • SELECT * FROM IndexedTable WHERE CONTAINS (*, N'"xyz" near "abc"')

RESOLUTION

Service pack information

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
290211 How to obtain the latest SQL Server 2000 service pack

Hotfix information

Prerequisites

This hotfix requires SQL Server 2000 Service Pack 3.

Restart requirement

You do not have to restart your computer after you apply this hotfix.

Hotfix replacement information

This hotfix does not replace any other hotfixes.

File information

The English version of this hotfix has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in coordinated universal time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.
   Date         Time   Version            Size    File name
   --------------------------------------------------------------
   07-Aug-2004  00:28  9.107.8320.7    1,536,000  Tquery.dll       

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the "Applies to" section.

This problem was first corrected in Microsoft SQL Server 2000 Service Pack 4.

MORE INFORMATION

Steps to reproduce the behavior

To reproduce the behavior, run the following Transact-SQL script:
use master
go
If exists (Select dbid from master.dbo.sysdatabases where Name='DatabaseFT')
	Drop Database DatabaseFT
go
Create Database DatabaseFT
go
set nocount on
go
use DatabaseFT
go
CREATE TABLE [IndexedTableTwo] (
          [ID] [int] NOT NULL constraint pk_column11 primary key,
	  Column2 nvarchar(200) ) 
go

Insert Into IndexedTableTwo(ID , Column2) Values ( 1, N'CHAMPION ACQUISITION CORP ')
Insert Into IndexedTableTwo(ID , Column2) Values ( 2, N'CHAMPION BUILDING PRODUCTS ')
Insert Into IndexedTableTwo(ID , Column2) Values ( 3, N'CHAMPION CHEVROLET ')
Insert Into IndexedTableTwo(ID , Column2) Values ( 4, N'CHAMPION DODGE INC ')
Insert Into IndexedTableTwo(ID , Column2) Values ( 5, N'CHAMPION INTERNATIONAL CORP ')
Insert Into IndexedTableTwo(ID , Column2) Values ( 6, N'CHAMPIONSHIP RACING TECHNOLOGY CRT ')
Insert Into IndexedTableTwo(ID , Column2) Values ( 7, N'CHARLES CHAMPION')
Insert Into IndexedTableTwo(ID , Column2) Values ( 8, N'STUART CHAMPION')
Insert Into IndexedTableTwo(ID , Column2) Values ( 9, N'CURVE CHAMPION')
go

exec sp_fulltext_database 'enable' 
go
exec sp_fulltext_catalog 'for_upgrade_FTC1', 'create'
go
exec sp_fulltext_table 'IndexedTableTwo', 'create', 'for_upgrade_FTC1', 'pk_column11'
go
exec sp_fulltext_column 'IndexedTableTwo', 'Column2', 'add'
go
exec sp_fulltext_table 'IndexedTableTwo', 'activate'
go
exec sp_fulltext_table 'IndexedTableTwo', 'start_full'
go
waitfor delay '000:00:02'
go
while ( ( select fulltextcatalogproperty ( 'for_upgrade_FTC1' , 'populatestatus') )<>0 )
begin 
	waitfor delay '000:00:02' 
end 
go

select ID, '|' + Column2 + '|' as SurroundedColumn2 from IndexedTableTwo 
where contains (Column2, N'"CHAMPION*" near "C*"') order by ID
GO

select ID, '|' + Column2 + '|' as SurroundedColumn2 from IndexedTableTwo
where contains (Column2, N'"C*" near "CHAMPION*"') order by ID
GO


select ID, '|' + Column2 + '|' as SurroundedColumn2 from IndexedTableTwo
where contains (Column2, 'CORP near Champion')
Note After you run the Transact-SQL script, the first SELECT statement returns three rows, the second SELECT statement returns four rows, and the third SELECT statement returns two rows.

REFERENCES

For more information about full-text searches in SQL Server, visit the following Microsoft Developer Network (MSDN) Web site:
http://msdn2.microsoft.com/en-us/library/aa172842(SQL.80).aspx

Properties

Article ID: 888008 - Last Review: November 2, 2007 - Revision: 2.7
APPLIES TO
  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Personal Edition
  • Microsoft SQL Server 2000 Workgroup Edition
  • Microsoft SQL Server 2000 Desktop Engine (Windows)
  • Microsoft SQL Server 2000 Enterprise Edition 64-bit
Keywords: 
kbqfe kbquery kbfix kbsqlserv2000presp4fix kbtshoot kbprb KB888008

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com