db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta Satoor" <msat...@gmail.com>
Subject Re: [Derby-655] : getImportedKeys returns duplicate rows in some cases
Date Thu, 25 May 2006 16:26:23 GMT
Hi,

I was able to make a quick change to
CreateIndexConstantAction.executeConstantAction to generate a new uuid for
conglomerateid when there is a duplicate index and that fixes the problem
query in DERBY-655. I ran the derbyall suite too and there were no new
failures. This change will cover the new duplicate indexes that will get
created after the code change, ie the new duplicate indexes will have unique
conglomerateid but will share the same physical conglomerate underneath. I
think this change by itself can go safely in all the existing Derby releases
without breaking the database compatibility.

But for the existing duplicate foreign keys in the database, there will
still be duplicate conglomerateids in SYSCONGLOMERATES and that will make
getImportedKeys return incorrect number of rows. We can solve this in
upcoming Derby10.2 release by adding a system generated primary key to
SYSCONGLOMERATES (as suggested by Stan). This will make sure that existing
duplicate rows with same conglomerateid can be uniquely identified using
system generated primary key. getImportedKeys will need to be fixed as shwon
by Stan's example query. This can only be done for Derby10.2. But for older
Derby releases, I don't think we can add a new column to a system table for
compatibility reasons and hence older releases will continue to return
incorrect number of rows from getImportedKeys for existing duplicate foreign
key indexes.

Someone should correct if I am missing something here.

Thanks,
Mamta


On 5/24/06, Stanley Bradbury <Stan.Bradbury@gmail.com> wrote:
>
> Mamta Satoor wrote:
> > Hi,
> >
> > I have researched quite a bit on Derby-655 : getImportedKeys returning
> > duplicate rows in some cases. Here are my findings and 3 possible
> > solutions to the problem. After reading the mail, if someone can think
> > of any other way of solving this problem, then please share it.
> >   ====  SNIP ==
> >
> >
> > In the above sql snippet, when the join is made on SYSCONSTRAINTS,
> > SYSKEYS, and SYSCONGLOMERATES for primary key on table t2, the query
> > finds 2 rows in SYSCONGLOMERATES with the conglomerateid and it picks
> > both those rows. This is wrong and we need to somehow return only one
> > row from SYSCONGLOMERATES, eventhough there might be duplicate rows in
> > there for a given conglomerateid.
> >
> > I have thought of following 3 solutions
> > 1)Even if a foreign key's backing index qualifies as a duplicate
> > index, have foreign key backing index create its own conglomerate
> > rather than share an existing conglomerate. But this would mean that
> > system would have to maintain 2 identical conglomerates. This can
> > impact Derby's performance negatively, although I don't know how badly
> > because it might be rare that a user creates a primary key and foreign
> > key on identical columns.
> > 2)Write a system function which will return just one row for all the
> > duplicate conglomerateids for a given constraintid and use that row in
> > the outermost select statement of metadata query for getImportedKeys.
> > Not sure how easy this would be to implement but I am leaning towards
> > this solution.
> > 3)Do not create duplicate rows in sysconglomerates for duplicate
> > indexes. Instead have just one row in sysconglomerate to represent all
> > the duplicate indexes in sysconstraints. But that seems like more of
> > an enhancement rather than a bug fix to me. Current Derby code heavily
> > relies on having a row in SYSCONGLOMERATE for each of the indexes.
> >
> > Thanks if you have made it this far :) I hope this mail is clear in
> > explaining the problem and the possible solutions. If anything is not
> > clear, then let me know and I can provide more information.
> >
> > If anyone has any ideas about some other possible fix for the problem,
> > please let me know that too.
> >
> > thanks,
> > Mamta
> The table needs a primary key - besides making conglomerateid unique it
> might be less invasive to add a system generated primary key to this
> table.  I personally avoid using tables without primary keys because you
> can run into odd-ball situations like this where multiple records can be
> identical and so it is not possible to get just one record.
>
> With a primary key you can get a single record by adding a correlated
> subquery to the query conditions that specify using the record with the
> record with max or min value of the unique column.  Say the unique key
> column is conglomeratePK - the conditional would be:
>
> select * from sys.sysconglomerates c1 where c1.conglomeratePK  = (
> select max(c2.conglomeratePK ) from sys.sysconglomerates c2 where
> c2.conglomerateid = c1.conglomerateid);
>
>

Mime
View raw message