db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel John Debrunner <...@apache.org>
Subject Re: (DERBY-655) getImportedKeys returns duplicate rows in some cases
Date Fri, 27 Jan 2006 21:49:49 GMT
Mamta Satoor wrote:

> Hi,
>  
> I have been looking at Derby-655 getImportedKeys returns duplicate rows
> in some cases. Deepa reported that one of the databases with just toooo
> many tables was returning duplicate rows for
> DatabaseMetaData.getImportedKeys on a particular table. I was able to
> work on that database and bring it down to 3 tables which are involved
> in the getImportedKeys call. Following is the sql which will show the
> relationship between the 3 tables.
> 
> CREATE TABLE t1(c11_ID BIGINT NOT NULL);
> CREATE TABLE t2 (c21_ID BIGINT NOT NULL primary key);
> ALTER TABLE t1 ADD CONSTRAINT F_12 Foreign Key (c11_ID)
>    REFERENCES t2 (c21_ID) ON DELETE CASCADE ON UPDATE NO ACTION;
> CREATE TABLE t3(c31_ID BIGINT NOT NULL primary key);
> ALTER TABLE t2 ADD CONSTRAINT F_443 Foreign Key (c21_ID)
>    REFERENCES t3(c31_ID) ON DELETE CASCADE ON UPDATE NO ACTION;
> 
> t1(c11_id) has foreign key reference to t2(c21_id) which in turn has
> foreign key reference to t3(c31_id). Now if a jdbc program tries to
> invoke DatabaseMetaData.getImportedKeys on t1, it returns 2 rows, one
> for each chained foreign key reference. 

Is there anything significant when you say "it returns 2 rows, one
> for each chained foreign key reference"? Just that it returns the same
row twice, so I'm wondering why you say "each chained reference".

<snip - big ugly query>

My only advice is to break the query down from its inner elements out.
Ensure each of those in isolation is returning the correct data. Then
work on the next level out. Maybe even creating a view for the working
inner elements so the next one to tackle is somewhat readable.

E.g. with something like

SELECT * FROM T, (SELECT * FROM A,B WHERE ...) AS X
WHERE ...

Start with

SELECT * FROM A,B WHERE ...

ensure that works, then
do

create view SUB_AB AS SELECT * FROM A,B WHERE ...

then work on

SELECT * FROM T, SUB_AB
 WHERE ...

Hope this is clear, just an idea to make the SQL visually
understandable. Maybe remove all the optimizer overrides as well to
clear out the clutter.

Dan.




Mime
View raw message