Kbase P130258: Records containing unknown value(s) are not included in query's result.
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
08/04/2008 |
|
Status: Unverified
FACT(s) (Environment):
Progress 6.x
Progress 7.x
Progress 8.x
Progress 9.x
OpenEdge 10.x
All Supported Operating Systems
SYMPTOM(s):
Running same query using 2 different indexes returns different results.
Query contains a [DATE FIELD] GT (greater than) TODAY comparison.
Query contains [DATE FIELD] > TODAY... USE-INDEX [INDEX NAME].
[DATE FIELD] is not a component in index [INDEX NAME].
Table contains records where [DATE FIELD] contains the unknown value.
Query does not return any records with [DATE FIELD] containing UNKNOWN value.
Querying the same table using the same query with an index containing [DATE FIELD] as a (leading) part of the index returns records where [DATE FIELD] = ? (unknown value).
CAUSE:
In ABL expressions the UNKNOWN value (?) can be compared only using EQ (=) or NE (<>):
If both sides of the equation are UNKNOWN the normal rules apply (evaluates to TRUE).
If the sides are different (one side is UNKNOWN and other side is not UNKNOWN) the (EQ) comparison evaluates to FALSE.
All other comparisons like < and > result in UNKNOWN, which is not TRUE: ie. FALSE.
However in ABL database INDEXed searching, the rules are different. The ABL allows multiple UNKNOWN values in an index (whether unique or non-unique). They all sort at the end of the index. This then implicitly allows the < and > type of comparisons to work. For example with a date, if an INDEXed comparison is performed, [DATE FIELD] > [date value] will include the UNKNOWN values. But a non-indexed comparison of [DATE FIELD] > [ date value] will exclude the unknown values from the result.
FIX:
In order to use the ">" operator in the query to find "unknown" values:
* the field [DATE FIELD] needs to be part of an INDEX which can be used to resolve the query
(COMPILE your procedure with XREF to find out which INDEX is used in the query if no index is explicitly chosen)
OR
* rewrite your the WHERE clause of your query to eg.:
... WHERE ([FIELD] > [VALUE] OR [FIELD] = ?) ... instead of ... WHERE ([FIELD] > [VALUE]) ...