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

This article was previously published under Q294809
This article has been archived. It is offered "as is" and will no longer be updated.
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.
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.
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

  1. Install Microsoft Windows NT 4.0 SP6 and SQL Server 7.0 with Full-Text Search components.
  2. In Query Analyzer, run the following script:
    -- Full text repro script.use northwindgo-- Create table.IF EXISTS (SELECT name FROM sysobjects WHERE name = 'ftstable' AND type = 'U')drop table ftstablegocreate 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'goEXEC 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'    CONTINUEENDGO					
  3. Run the following query:
    select c1 from ftstable where contains(*,'"apples" and "oranges"')					
    The query returns the row.

Article ID: 294809 - Last Review: 02/28/2014 04:27:12 - Revision: 3.2

  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 7.0 Service Pack 1
  • Microsoft SQL Server 7.0 Service Pack 2
  • kbnosurvey kbarchive kbbug kbfix KB294809