Kbase P4130: Does the unknown value (?) sort high or low when used in a query?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
13/02/2006 |
|
Status: Verified
SYMPTOM(s):
Does the unknown value (?) sort high or low when used in a query?
Use of unknown value (?) in selection criteria : index
Why does using field < ? return all records?
CAUSE:
The unknown value will always sort high in an indexed field; therefore a query using < or > on an index field that contains "?" will produce inconsistent results.
FIX:
The only queries allowed for indexes that have "?" entries must be "eq" or "neq".
The fact that you may have multiple records that have a "?" on a unique index is a SIDE EFFECT of the Progress feature that allows users to enter "?" into an indexed field. This side effect can only be remedied by disallowing "?" as a valid value for an index. This can be done at index design time by turning on the MANDATORY flag for all fields which make up an index. This forces the users to enter valid values so that "?" will not make it into the index. Progress has no plans to disallow "?" as a valid value for an index since 1) many users rely on this feature and 2) the mandatory flag will take care of the side effect.