Icon View Incident Report

Serious Serious
Reported By: Simon Beesley
Reported On: 1/24/2008
For: Version 1.07 Build 3
# 2544 CONTAINS Conditions Returning Incorrect Results in Certain Cases with AND Operators

My problem occurs when I do a full-text index search on a CLOB field called 'Notes'. Searching on single terms works fine, but when I do a search using AND, the results are sometimes incorrect.

Depending on how many rows contain one or the other term, ElevateDB doesn't always return rows that contain both terms. With this particular example, if there are only few occurrences of 'Technical' and 'Transfer' than it does select rows that contain both. But if there are more than a handful of either term it invariably selects records that only have one of the terms in it.

SELECT * FROM "GlossaryTable"
WHERE Notes CONTAINS 'Technical'
AND Notes CONTAINS 'Transfer'

Comments Comments
The issue was caused by the query optimizer rewriting one of the CONTAINS conditions into a row scan, which is not valid since a CONTAINS condition can never be executed as a row scan. The result was that the rewritten CONTAINS condition would return all rows as satisfying the condition.

Resolution Resolution
Fixed Problem on 1/25/2008 in version 1.08 build 1

Products Affected Products Affected
ElevateDB Additional Software and Utilities
ElevateDB DAC Client-Server
ElevateDB DAC Client-Server with Source
ElevateDB DAC Standard
ElevateDB DAC Standard with Source
ElevateDB DAC Trial
ElevateDB VCL Client-Server
ElevateDB VCL Client-Server with Source
ElevateDB VCL Standard
ElevateDB VCL Standard with Source
ElevateDB VCL Trial