7.5.1 PTF3 Bug

[JPATOU]

 Posted by:  JPATOU 

Dear Sir/Madam

We have encountered a bug with SqlBase Ver. 7.5.1 PTF3
Current Setup:
Windows 2000 Server running SqlBase Ver. 7.5.1 PTF3
Non Clustered Database (Size 9Gb)

The bug is the following.
We have a table (TABLE1) with approximately 11.500.000 rows and a
primary key consisting of 3 fields as below:
Field1 VARCHAR(20)
Field2 VARCHAR(16)
Field3 SMALLINT

When we try to transfer a portion of data from TABLE1 to a similar table
TABLE2 with the same primary key (or try to reorganize the DB) the
database returns error “00805 ROW NUD Insert/update of unique
constrained columns with duplicate data”.

In order to find the duplicate row we unload a compress sql of TABLE1
and then load it without the primary key and the UNIQUE index. After the
load operation complete we found out that there was a duplicate row on
the primary key of TABLE1.
But when we execute the select statement on TABLE1 in order to find the
duplicate row the select statement returns only one row. Actually the
duplicate row is invisible.

We deleted the row from TABLE1. After that the select statement returns
0 rows for the duplicate key so we did again the same job with the
unload and load.
When the load finished we found again the invisible row that was in TABLE1.

So with this bug there is a violation at the uniqueness of the primary
key and the unique index.

Is there a fix for this bug for version 7.5.1 ?
Is it fixed in later versions of SqlBase?

Thanks in Advance

[Igor Ivanovic]

 Posted by:  Igor Ivanovic 

PTF3 was the last PTF for SQLBase 7.5.1. So the only things you can do
is either live with the bug, or upgrade to the latest version: 9.0.1.

You can test the latest version by downloading it from the Gupta site.

Igor

[FRBhote]

 Posted by:  F R Bhote 

Try this and see if it returns anything:

SELECT A.* FROM TABLE1 A, TABLE1 B WHERE A.Field1 = B.Field1 AND A.Field2 =
B.Field2 AND A.Field3 = B.Field3 AND A.ROWID != B.ROWID

[JPATOU]

 Posted by:  JPATOU 

I have already tried it and returns 0 rows. Thats why i say it is
invissible.

[JPATOU]

 Posted by:  JPATOU 

Is the bug oficially fixed with 9.0.1?

[JPATOU]

 Posted by:  JPATOU 

i also tried the following with no results again
SELECT A.FIELD1, A.FIELD2, A.FIELD3, COUNT(*)
FROM TABLE1 A, TABLE1 B
WHERE A.Field1 = B.Field1 AND
A.Field2 = B.Field2 AND
A.Field3 = B.Field3
Group By 1,2,3
Having Count(*)>1

[Mike Vandine]

 Posted by:  Mike Vandine 

There is most likely a corruption of the data on the table with a row that
was written and the index wasn't updated correctly (a crash occurred, etc.
during a critical operation). When you do a select, it would use the index
and only find one row. However, an unload of the table (or a copy of data)
would actually
produce two rows in the unload file because an index probably is not used,
the raw data is accessed.

Hope this helps.

Mike