Microsoft KB Archive/286787

= FIX: Incorrect Results From Full-Text Search on Several Columns =

Article ID: 286787

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q286787



BUG #: 58135 (SQLBUG_70)



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 &quot;More Information&quot; section for an example and steps to reproduce the problem.



CAUSE
Using the asterisk (*) argument to search all columns results in a query that looks for all occurrences in all columns of each single word of the search condition. The full-text engine does not account for the columns in the query. If the single words of the search condition are found in different columns, it is counted as a match anyway, and that row is returned in error.



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 (*, ' &quot;Sean Moore&quot; ')

/* or */

SELECT * FROM t1            INNER JOIN CONTAINSTABLE(t1, *, ' &quot;Sean Moore&quot; ') AS key_tbl ON t1.pk = key_tbl.[key] Change the syntax to the following: SELECT * FROM t1 WHERE CONTAINS (col_1, ' &quot;Sean Moore&quot; ') or CONTAINS (col_2, ' &quot;Sean Moore&quot; ')

/* or */

SELECT * FROM t1            INNER JOIN CONTAINSTABLE(t1, col_1, ' &quot;Sean Moore&quot; ') AS key_tbl ON t1.pk = key_tbl.[key] UNION SELECT * FROM t1            INNER JOIN CONTAINSTABLE(t1, col_2, ' &quot;Sean Moore&quot; ') 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 &quot;More Information&quot; section:

EXEC sp_fulltext_table 't1', 'deactivate' GO    ALTER TABLE t1 ADD col_3 CHAR(61) GO    /* NOTE: The &quot;+&quot; 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, ' &quot;Sean Moore&quot; ') /* or */ SELECT * FROM t1 INNER JOIN CONTAINSTABLE(t1, col_3, ' &quot;Sean Moore&quot; ') 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.



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)) </li>  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, &quot;Sean Connery&quot;, &quot;Roger Moore&quot;) </li>  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 </li>  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 (*, ' &quot;Sean Moore&quot; ')

SELECT t1.* FROM t1 INNER JOIN CONTAINSTABLE(t1, *, ' &quot;Sean Moore&quot; ') AS key_tbl ON t1.pk = key_tbl.[key] RESULT: The row is returned even if no column contains the exact phrase &quot;Sean Moore&quot;: <pre class="fixed_text"> pk   col_1             col_2 - - --    1     Sean Connery      Roger Moore       </li></ol>

Additional query words: SSrvTran_SQL fts sqlserver

Keywords: kbbug kbfix KB286787

-

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

© Microsoft Corporation. All rights reserved.