Microsoft KB Archive/294809

= FIX: Full-Text Search Queries with CONTAINS Clause Search Across Columns =

Article ID: 294809

Article Last Modified on 7/16/2004

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 7.0 Service Pack 1
 * Microsoft SQL Server 7.0 Service Pack 2

-



This article was previously published under Q294809



SYMPTOMS
If there are two or more different full-text search enabled columns on a table, and if the keywords separated by the AND operator exist in two different columns, a CONTAINS predicate with the AND operator searches for the keywords across all columns.

For example, assume a table called ftstable with three columns: c1 as integer (the index column), and c2 and c3 as varchar columns that are full-text enabled. Also assume that a row contains &quot;apples&quot; in column c2 and &quot;oranges&quot; in columnn c3. In SQL Server 7.0 SP2 and earlier, the following query select c1 from ftstable where contains(*,'&quot;apples&quot; and &quot;oranges&quot;') is incorrectly interpreted as follows: select c1 from ftstable where contains(*,'&quot;apples&quot;') AND contains(*,'&quot;oranges&quot;') and the row is returned.

NOTE: The correct way to interpret the query is as follows: select c1 from ftstable where contains(c2,'&quot;apples&quot; and &quot;oranges&quot;') OR contains(c3,'&quot;apples&quot; and &quot;oranges&quot;') and to not return the row.



RESOLUTION
You can work around this problem in the following ways:   Rewrite the query as follows: select c1 from ftstable where contains(*,'&quot;apples&quot;') and contains(*,'&quot;oranges&quot;')  Create another column that contains the values of all other full-text columns (concatenated) and use the CONTAINS clause on just this column.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was first corrected in SQL Server 7.0 Service Pack 3.



Steps to Reproduce Behavior
 Install Microsoft Windows NT 4.0 SP6 and SQL Server 7.0 with Full-Text Search components.  In Query Analyzer, run the following script: -- Full text repro script. use northwind go -- Create table. IF EXISTS (SELECT name FROM sysobjects WHERE name = 'ftstable' AND type = 'U') drop table ftstable go

create table ftstable(c1 INTEGER NOT NULL identity CONSTRAINT PK_idx PRIMARY KEY, c2 varchar(50), c3 varchar(50)) go

-- Insert data. insert ftstable(c2,c3) values ('apples in the orchard', 'oranges in the orchard') go

-- Enable full-text searching in the database. EXEC sp_fulltext_database 'enable' go

-- Create a new full-text catalog. if exists(select name from sysfulltextcatalogs where name='FTS') EXEC sp_fulltext_catalog 'FTS', 'drop' go EXEC sp_fulltext_catalog 'FTS', 'create' GO -- Register the new table and columns within it for full-text querying, then activate the table. EXEC sp_fulltext_table 'ftstable', 'create', 'FTS', 'PK_idx' EXEC sp_fulltext_column 'ftstable', 'c2', 'add' EXEC sp_fulltext_column 'ftstable', 'c3', 'add' EXEC sp_fulltext_table 'ftstable', 'activate' GO -- Start full population of the full-text catalog. EXEC sp_fulltext_catalog 'FTS', 'start_full' WHILE (SELECT fulltextcatalogproperty('FTS', 'populatestatus')) <> 0 BEGIN WAITFOR DELAY '00:00:02' CONTINUE END GO   Run the following query: select c1 from ftstable where contains(*,'&quot;apples&quot; and &quot;oranges&quot;') The query returns the row. </li></ol>

Keywords: kbbug kbfix KB294809

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.