IBM Support

ClearQuest Query using CONTAINS operator with a multiline string field may not return expected results when SQL Server FTS is enabled

Troubleshooting


Problem

Queries against fields using "contains" operator in the IBM Rational ClearQuest (CQ) query wizard may not return expected results when Microsoft SQL Server (SQL Server) FTS (Full Text Search) is enabled database-side.

Symptom

When SQL Server's Full Text Search (a database-side indexing, not to be confused with ClearQuest Full-Text Search) feature is enabled against a table, the ClearQuest query wizard will generate SQL in a different manner when the operator "contains" is used for a SQL Server database with SQL Server FTS vs. SQL with SQL Server FTS unenabled.

Non-SQL Server FTS enabled record types would have a SQL statement based on the SQL "like" operator with a % wildcard:
select T1.dbid,T1.id,T1.description,T1.headline from Defect T1 where T1.dbid <> 0 and ((T1.description like '%this is my search text%'))

When SQL FTS is enabled, the SQL statement is written using the SQL "contains" operator and uses a different wildcard:
select T1.dbid,T1.id,T1.description,T1.headline from Defect T1 where T1.dbid <> 0 and contains(T1.description, '"this is my search text*"')

These two different statements return different results.

When enabling SQL Server FTS, it is best to only search for a single term. When searching for multiple terms such as this example: "this is my search text" you may receive no hits.

Environment

This concern only applies to Microsoft SQL Server when SQL Server FTS is enabled. This does not apply to any other ClearQuest supported database vendor platform.

SQL Server Full Text Search is not enabled by default in a SQL Server database and deliberate SQL Server administration steps would have been taken to enable this feature in a ClearQuest database hosted by SQL Server.

Resolving The Problem

If the SQL Server FTS enabled queries are not returning expected results, it is recommended that SQL Server FTS be disabled against the SQL server table representing that CQ record type.

If text searching is a desired functionality, it is recommended to use ClearQuest's Full-Text Search functionality which can be enabled on the ClearQuest Web Change Management (CM) server. This feature allows faster, more complex search capabilities than using "contains" against a string field within a SQL Server.

[{"Product":{"code":"SSSH5A","label":"Rational ClearQuest"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Database Configuration\/Connectivity - SQL Server","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"7.1.2;8.0;8.0.1","Edition":"","Line of Business":{"code":"LOB45","label":"Automation"}}]

Document Information

Modified date:
01 August 2018

UID

swg21679424