FIX: Full-Text Search Queries with CONTAINS Clause Search Across Columns (294809)

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 "apples" in column c2 and "oranges" in columnn c3. In SQL Server 7.0 SP2 and earlier, the following query

select c1 from ftstable where contains(*,'"apples" and "oranges"')

is incorrectly interpreted as follows:

select c1 from ftstable where contains(*,'"apples"') AND contains(*,'"oranges"')

and the row is returned.

NOTE: The correct way to interpret the query is as follows:

select c1 from ftstable where contains(c2,'"apples" and "oranges"') OR contains(c3,'"apples" and "oranges"')

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(*,'"apples"') and contains(*,'"oranges"')

Create another column that contains the values of all other full-text columns (concatenated) and use the CONTAINS clause on just this column.

MORE INFORMATION

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(*,'"apples" and "oranges"')