Microsoft KB Archive/888008

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

Article ID: 888008

Article Last Modified on 11/2/2007

-

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 Enterprise Edition
 * Microsoft SQL Server 2000 Workgroup Edition
 * Microsoft SQL Server 2000 Desktop Engine (Windows)
 * Microsoft SQL Server 2000 Developer Edition
 * Microsoft SQL Server 2000 Enterprise Edition 64-bit

-



Bug #: 471528 (SQL Server 8.0)



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'&quot;abc&quot; near &quot;xyz&quot;')
 * SELECT * FROM IndexedTable WHERE CONTAINS (*, N'&quot;xyz&quot; near &quot;abc&quot;')



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

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 &quot;Applies to&quot; section.

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



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'&quot;CHAMPION*&quot; near &quot;C*&quot;') order by ID GO

select ID, '|' + Column2 + '|' as SurroundedColumn2 from IndexedTableTwo where contains (Column2, N'&quot;C*&quot; near &quot;CHAMPION*&quot;') 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.

