Use of unknown value ( ? ) in selection criteria : index
Are index fields allowed to have the unknown value (?) ?
--------------------------------------------------------
Progress provides the feature of allowing users to enter the
unknown value in an index field. This feature was provided
years ago to give customers the ability to temporarily
build up indexes with "?" assuming that, later, the real
data would replace the "?" before the index was used in reporting.
What affect does the unknown value in an index have on queries?
---------------------------------------------------------------
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.
The following examples illustrate this behavior.
Example 1:
----------
Using the Progress demo database the following query will
display all records where the
cust-num is > 10 because cust-num is an indexed field and the ?
will sort high in an indexed field :
for each cust where cust-num > 10 and cust-num <= ? :
However, the following query will display ZERO records because
`cust-num` will be the chosen index for this query. Since
'zip' is not the chosen index, the ? will NOT SORT HIGH and
so the second part of this query will be false. No records
are returned when one part of an AND is FALSE:
for each cust where cust-num > 10 and cust-num <= ? and
zip > 0 and zip < ?:
The same rule can affect queries where ? is not explicitly used as
example 2 illustrates:
Example 2:
----------
Using the sports database, if you were to create three
order records where order.cust-num = 1 and order-date = ?
then the following query would return the three records:
for each order where order-date >= 1/1/96:
However, the following query would return NO records:
for each order where order-date >= 1/1/96 and cust-num = 1:
How to avoid this issue?
------------------------
As you can see from the above example that a query using < or > on an
index field that contains "?" can produce inconsistent results. 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.