Duplicate record issue

[rhi]

Can anyone help me with an explanation why it appears that the Progress database will allow duplicate records on a table with a Primary-unique index?
This is a QAD database (eB2.1 SP3). Below is the index definition.

pu pk_det 6 + pk_domain
+ pk_user
+ pk_part
+ pk_reference
+ pk_start
+ pk_end

Apparently, if any on these fields are not made mandatory, and the values inserted and unknown (?), it will allow the creation of a duplicate record.
The problem is, if your program does a "find first", then another program does a "find last" on the same table, Progress will return different records (recid's).

 

[KnutHandsome]

Apparently, if any on these fields are not made mandatory, and the values inserted and unknown (?), it will allow the creation of a duplicate record.

Yes. That is what MANDATORY is used for.

Use MANDATORY if you don't want to allow unknowns.

 

[KnutHandsome]

Here's more info from the KB (15969):

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.

 

[rhi]

My question is not really about the unknown value, but the fact that if a field is not made MANDATORY, and then that field is place on a PRIMARY UNIQUE index, then Progress allows DUPLICATE records to be inserted on a UNIQUE INDEX. My question is, WHY? This does not seem to make sense.

 

[rhi]

Also, I just tested and index rebuild does not detect and remove the duplicates. Does anyone know how to remove these?

 

[Casper]

Hi Rhi,

Since null is an undefined state Progress doesn't see it as a value, so it can't be seen as duplicate. Probably not the answer your looking for....

To resolve this issue, you can just query the record:

for each table where keyfield = ? break by keyfield:
   if not first-of(keyfield)
   then delete table.
end.

this way you end up with only one record. But since you don't know for sure all ? are equal I don't know if this is correct for your situation.


BTW: als o be carefull for sequences if you don't define sequence as cycle at limit then the next value for the highest value is also the unknown value. This way you can generate duplicates as well.....

Regards,

Casper.

 

[KnutHandsome]

My question is, WHY?

Answered in the first section of the preceding post.

this way you end up with only one record. But since you don't know for sure all ? are equal I don't know if this is correct for your situation.

Yes, I think rhi will have to have some test on the non-index fields to ensure he is deleting the records he wants - simply deleting all but the first duplicate encountered may not be correct as you say.

There is a solution pointed to here, but I don't know if it deals with genuine duplicate index bugs, or ordinary duplicate unknowns, or if it doesn't matter.

http://www.peg.com/lists/dba/history/200104/msg00703.html