FIX: Incorrect Results From Full-Text Search on Several Columns (286787)

SYMPTOMS

A full-text search query returns incorrect results if all of the following conditions are met:

The table contains at least two full-text indexed columns (for example, col_1 and col_2).
The table contains at least one row where:
- col_1 contains a phrase with two words that are separated by a blank space
  
  -and-
- col_2 contains another phrase with two words that are separated by a blank space.
The query uses a CONTAINS predicate or a CONTAINSTABLE function with an asterisk (*) argument to search all the columns.
The search phrase combines the first word from col_1 and the second word from col_2, separated by a blank space.

The query then returns rows where no column contains the exact search phrase. All character data types are affected (CHAR, NCHAR, VARCHAR, NVARCHAR, TEXT, NTEXT).

Refer to the "More Information" section for an example and steps to reproduce the problem.

WORKAROUND

To work around this problem, use any one of the following methods:

Rewrite the query and specify a CONTAINS predicate or a CONTAINSTABLE function for each column. For example, instead of using one of the following queries

     SELECT * FROM t1 WHERE CONTAINS (*, ' "Sean Moore" ')

  /* or */ 

     SELECT * FROM t1 
            INNER JOIN CONTAINSTABLE(t1, *, ' "Sean Moore" ') AS key_tbl
            ON t1.pk = key_tbl.[key]

Change the syntax to the following:

     SELECT * FROM t1 WHERE CONTAINS (col_1, ' "Sean Moore" ') or CONTAINS (col_2, ' "Sean Moore" ')

  /* or */ 

     SELECT * FROM t1 
            INNER JOIN CONTAINSTABLE(t1, col_1, ' "Sean Moore" ') AS key_tbl 
            ON t1.pk = key_tbl.[key] 
     UNION 
     SELECT * FROM t1 
            INNER JOIN CONTAINSTABLE(t1, col_2, ' "Sean Moore" ') AS key_tbl 
            ON t1.pk = key_tbl.[key]

NOTE: This workaround may have a negative impact on the performance of the query.

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

230103 BUG: Cannot Have More Than Eight Full Text Joins and Operations

Concatenate the full-text indexed columns into a single column. For example, if columns col_1 and col_2 contain data to be indexed, then concatenate those values, store them in column col_3, and put a full-text index on col_3. The following example is based on the sample code shown in the "More Information" section:

     EXEC sp_fulltext_table 't1', 'deactivate'
     GO
     ALTER TABLE t1 ADD col_3 CHAR(61)
     GO
     /* NOTE: The "+" operator does not work for TEXT or NTEXT data types! */ 
     UPDATE t1 SET col_3 = col_1 + ' ' + col_2
     GO
     EXEC sp_fulltext_column 't1', 'col_3', 'add'
     GO
     EXEC sp_fulltext_table 't1', 'activate'
     GO
     EXEC sp_fulltext_catalog 'FTS', 'start_full'
     WHILE (SELECT fulltextcatalogproperty('FTS', 'populatestatus')) <> 0
        BEGIN
           WAITFOR DELAY '00:00:02' 
        CONTINUE
     END
     GO
     SELECT t1.* FROM t1 WHERE CONTAINS (col_3, ' "Sean Moore" ') 
       /* or */ 
     SELECT * FROM t1 INNER JOIN CONTAINSTABLE(t1, col_3, ' "Sean Moore" ') AS key_tbl ON t1.pk = key_tbl.[key]

Upgrade to SQL Server 2000, which returns the correct results.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

274799 INF: How to Obtain Service Pack 3 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.

MORE INFORMATION

Steps to Reproduce Behavior

To reproduce the behavior, use these steps:

Create a table named t1:

     CREATE TABLE t1 (pk INT NOT NULL CONSTRAINT PK_idx PRIMARY KEY, col_1 CHAR(30), col_2 CHAR(30))

Insert a row where col_1 and col_2 each contain a phrase with two words that are separated by a blank space:
```
     INSERT INTO t1 (pk, col_1, col_2) VALUES (1, "Sean Connery", "Roger Moore")
```

Enable col_1 and col_2 for full-text search:

     -- Enable full-text searching in the database.
     EXEC sp_fulltext_database 'enable'
     GO
     -- Create a new full-text catalog.
     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 't1', 'create', 'FTS', 'PK_idx'
     EXEC sp_fulltext_column 't1', 'col_1', 'add'
     EXEC sp_fulltext_column 't1', 'col_2', 'add'
     EXEC sp_fulltext_table 't1', '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

Build a query with either a CONTAINS predicate or a CONTAINSTABLE function. The search condition consists of the first word from col_1 and the second word from col_2, separated by a blank space:

     SELECT t1.* FROM t1 WHERE CONTAINS (*, ' "Sean Moore" ')

     SELECT t1.* FROM t1 INNER JOIN CONTAINSTABLE(t1, *, ' "Sean Moore" ') AS key_tbl
            ON t1.pk = key_tbl.[key]

RESULT: The row is returned even if no column contains the exact phrase "Sean Moore":


     pk    col_1             col_2             
     ----- ----------------- ------------------
     1     Sean Connery      Roger Moore