DB is not enforcing index uniqueness

Posted by joey eisma on 10-Apr-2018 13:38

Hi,

On a 10.2B legacy application, have a table that has one unique index (fieldA + fieldB). For some reason, I can create multiple fieldA + fieldB records and db will not complain!

Any idea how could this be possible?

I tried idxbuild and dump and load for the table affected, but db still not enforcing index uniqueness. How to fix?

TIA!

Posted by joey eisma on 10-Apr-2018 14:36

no. they are legit values.

Posted by Rob Fitzpatrick on 10-Apr-2018 13:43

Are the values of either field set to unknown?

Posted by Brian K. Maher on 10-Apr-2018 14:46
Is the index active?
 
If it is, open a case with support.  We will need the .df for the table so we can build it here and some simple code that you believe shows the problem.
 
 
Brian Maher
Principal Engineer, Technical Support
Progress
Progress
14 Oak Park | Bedford, MA 01730 | USA
phone
+1 781 280 3075
 
 
Twitter
Facebook
LinkedIn
Google+
 
 
ProgressNext2018_Email_Signature
 
Posted by Brian K. Maher on 10-Apr-2018 15:04
the unknown value is the absence of any value at all.  people commonly think it is a value in and of itself but it isn’t.  that is why there can be multiple records in a table with a unique index when the field(s) in the index are set to unknown.
 
Brian Maher
Principal Engineer, Technical Support
Progress
Progress
14 Oak Park | Bedford, MA 01730 | USA
phone
+1 781 280 3075
 
 
Twitter
Facebook
LinkedIn
Google+
 
 
ProgressNext2018_Email_Signature
 

All Replies

Posted by Rob Fitzpatrick on 10-Apr-2018 13:43

Are the values of either field set to unknown?

Posted by joey eisma on 10-Apr-2018 14:36

no. they are legit values.

Posted by Brian K. Maher on 10-Apr-2018 14:46
Is the index active?
 
If it is, open a case with support.  We will need the .df for the table so we can build it here and some simple code that you believe shows the problem.
 
 
Brian Maher
Principal Engineer, Technical Support
Progress
Progress
14 Oak Park | Bedford, MA 01730 | USA
phone
+1 781 280 3075
 
 
Twitter
Facebook
LinkedIn
Google+
 
 
ProgressNext2018_Email_Signature
 
Posted by joey eisma on 10-Apr-2018 14:54

hi,

it is an active index.

looked at the data again as per Rob's reply, and found there are indeed unknown (null) values. so, db apparently is not treating them as unique?

given the context, unknown is a perfectly legit value. it looks like there's some code revisions that needs to be done.

thank you all.

Posted by Brian K. Maher on 10-Apr-2018 15:04
the unknown value is the absence of any value at all.  people commonly think it is a value in and of itself but it isn’t.  that is why there can be multiple records in a table with a unique index when the field(s) in the index are set to unknown.
 
Brian Maher
Principal Engineer, Technical Support
Progress
Progress
14 Oak Park | Bedford, MA 01730 | USA
phone
+1 781 280 3075
 
 
Twitter
Facebook
LinkedIn
Google+
 
 
ProgressNext2018_Email_Signature
 
Posted by Thomas Mercer-Hursh on 10-Apr-2018 16:24

If you define the fields as mandatory and/or provide them with a non-null default value, you won't have the problem.  I know I was initially surprised by this behavior when I first discovered it ... maybe 30 years ago :) ... but there are circumstances where it can be desirable behavior.  An example being when a field is not known at the time that the record is created.  One may want the index to be unique once the value is known, but the value may only be determined later.

Posted by marian.edu on 11-Apr-2018 00:06
Thomas, it's amazing how we can twist our mind to explain everything regardless how odd it is... if you try hard enough any bug can be packed as a feature :)

A null is a valid value, a null equal null (damn, even javascript say so) hence having two records with the same value should not be possible in a unique index but this will probably never change to keep the backward compatibility in place. There might be some counting on this 'feature', aka they just screw their data without knowing but hey as long as you don't know it, it won't hurt you :)


Marian Edu

Acorn IT 
+40 740 036 212

Posted by slegian-bva on 11-Apr-2018 00:26

even in openedge an unknown value equals an unknown value...

DEF VAR i1 AS INT NO-UNDO INIT ?.

DEF VAR i2 AS INT NO-UNDO INIT ?.

MESSAGE i1 = i2

 VIEW-AS ALERT-BOX INFO BUTTONS OK.

Posted by Thomas Mercer-Hursh on 11-Apr-2018 08:52

Marian, I don't see the problem.  Declare the field mandatory if you want true uniqueness.

Posted by George Potemkin on 11-Apr-2018 09:02

> A null is a valid value, a null equal null (damn, even javascript say so)

Progress has 3-4 different types of the unknown values. They are not equal each other. ;-)

Posted by Simon L. Prinsloo on 11-Apr-2018 09:14

Marian, I disagree. Twice.

First of all, this behaviour is well documented and is there by design, so it is not a bug. It is in fact very useful. In some workflows a value will only become available later in a process, but when it does, it needs to be unique. Until then, it is absent and it cannot participate in the index.

Secondly, null is not a value, it is the absence of a value. The test null eq null does not "compare" two values, it checks if the value is absent.

Posted by jquerijero on 11-Apr-2018 10:04

Don't you just eventually compare two values in the end?

Posted by gus bjorklund on 11-Apr-2018 12:04

> On Apr 11, 2018, at 11:05 AM, jquerijero wrote:

>

> Don't you just eventually compare two values in the end?

>

>

>

yes.

Posted by George Potemkin on 11-Apr-2018 12:37

Eventually Progress compares the sorting /weights/ of two values. Unknown value(s) has its own sorting weight.

Posted by jquerijero on 11-Apr-2018 13:04

That makes it even more confusing, are you implying two unknown values(s) have different sorting weight? When you sort two unknown values, which value comes first?

Posted by marian.edu on 11-Apr-2018 13:44
We can always agree to disagree Simon, even twice :)

No amount of documentation will ever turn a bug in a feature, that might well be just my pov though. I don't see your use case like a compelling one, if that 'object' is not complete then it should probably be (temporary) stored in some other place and don't pollute that table with incomplete records. Just a quick question, how do you find the (right) incomplete record later on to make it complete? Are you sure there won't be more that one incomplete records that match whatever search criteria you are using? When you say it can't participate in the index you make it sound like the records are invisible for queries that uses that index which is not actually true, the records will very much be visible... and try to explain that to an SQL developer banging his head on the desk when the sql statement used returns 'invisible' records :)

Actually the test null eq null (? eq ?) doesn't even compile... "message ? eq ?" or "if ? eq ? then" gives 'incompatible data type in expression or assignment' error :)

If you compare two variables that have a null 'value' the comparison returns true so it is very much a value otherwise it should have been returned ? (unknown).

Marian Edu

Acorn IT 
+40 740 036 212

Posted by Mike Fechner on 11-Apr-2018 14:02
Marian, don’t keep calling it a bug just because you’re used to a different implementation from other RDBMS’es or just don’t like it!
 
As the Dr. said, you can get your behavior by combining mandatory with a unique index.
 
how do you find the (right) incomplete record later on to make it complete?”
 
Are you lacking the creativity to think about use cases where a record has actually two unique indexes? One on a GUID (mandatory, always assigned to a known value). And another value, meaningful to humans, that must be unique when it’s known but multiple unknowns are possible – for what ever reason.
 
 
Posted by marian.edu on 11-Apr-2018 14:32
Agreed, maybe there is a more appropriate word for this and believe it or not the same thing happens in Oracle but my feelings were the same there too... all this 'relative' uniques looks just suspicious to me.

I don't like surrogate keys much unless no natural key exists, adding one just to get along this 'whatever reason' situation seems just wrong. Anyway, most of the uniques indexes I've found in OE applications have non mandatory fields and this was most of the time just bad design :(

 
Marian Edu

Acorn IT 
+40 740 036 212

Posted by Mike Fechner on 11-Apr-2018 14:36
Anyway, most of the uniques indexes I've found in OE applications have non mandatory fields and this was most of the time just bad design”
 
We can agree that this is true in many/most – but not all – situations. .
Posted by Thomas Mercer-Hursh on 11-Apr-2018 15:37

Marian, while I tend to agree that the best possible design probably doesn't use this feature, I have seen lots of schemas that do.  A classic is something like the invoice number corresponding to a order.  Yes, better to use a join table and allow for many to many matches, but a lot of older schemas will put things like that right in the order.  The invoice number is unknown when the order is created, but it is still a valid order.  That index is also not used at that stage.  And, the order number is used to locate the record.  No one said we had to be talking about a primary index.

Note that in many use cases it is also benign.

Posted by Thomas Mercer-Hursh on 11-Apr-2018 15:39

Oh, and for some years I have **always** used a GUID regardless of any "natural" key that exists ... if for no other reason that if you create the record with the "wrong" natural key it can be awful to get it fixed.

Posted by marian.edu on 12-Apr-2018 00:52
Thomas, 

my point is that a feature should not facilitate bad design, sometime this relative uniques is by design but most of the time it really isn't and either way it most of the time confuses developers not familiar with the initial thoughts of the designer like we had here... in fact most of us discovered that with surprise much the same way Joey did.

Besides, this is 'documented' only in the knowledge base, there is nothing in data dictionary help that mention it nor that the tool gives any warning when non mandatory fields are used when creating a unique index.

Back to your classic use case, the order indeed predates the invoice that's why, the worst case, the order number should be in the invoice table and not the other way around although most of the time it's quite valid to have multiple invoices for the same order (hence the many-many table).
  
Anyway, peoples seem to see that as a feature so I'm not going to call it a bug, maybe I'm just not seeing the proper use case... much like the right to bear arms I guess, some of us just don't get it :(

Marian Edu

Acorn IT 
+40 740 036 212

Posted by joey eisma on 12-Apr-2018 10:04

Thank you all for sharing your thoughts on this issue.

Spent hours looking to see what was wrong with the database, tables, indexes. This "feature" is something that's lurks in the background, and can get to bite you bad, really bad.

We can twist words that unknown, is just that, unknown. But null is a legit value, and that the unknown is equal to null. The database should have tweeted it cannot have duplicate values. :-)

Posted by gus bjorklund on 12-Apr-2018 13:12

The reason we claim this feature is not a bug is because at the time it was implemented back in the early eighties (long before there was any sort of SQL standard btw), it was done by a deliberate design choice, not by accident. I do agree that these days, it is not a desireable feature anymore and if we had it to do over again we would do something different.

Nobody seems to read documentation anymore, either.

Posted by ske on 13-Apr-2018 02:02

gus bjorklund wrote:

> if we had it to do over again we would do something different.

What other way would be any better today?

Not having the unknown value at all?

This thread is closed