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

Article translations Article translations
Article ID: 294809 - View products that this article applies to.
This article was previously published under Q294809
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

On This Page

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.

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.

MORE INFORMATION

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 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
    					
  3. Run the following query:
    select c1 from ftstable where contains(*,'"apples" and "oranges"')
    					
    The query returns the row.

Properties

Article ID: 294809 - Last Review: February 28, 2014 - Revision: 3.2
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
Keywords: 
kbnosurvey kbarchive kbbug kbfix KB294809

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