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 Mon, 30 Jan 2006 05:59:08 GMT
Hi Dan,

My answers inline.

Thanks for your time on it,
Mamta


On 1/27/06, Daniel John Debrunner <djd@apache.org> wrote:
>
> 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".


Actually, when I wrote the mail, I thought Derby returns a duplicate row for
each chained foregin key reference. ie I thought t1->t2->t3->t4 will return
3 duplicate rows for the 3-level foreign key chain among t1->t2->t3->t4 but
that is not true. For both t1->t2->t3 and t1->t2->t3->t4, Derby returns 2
rows which is basically the same row twice. Which is incorrect.

<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.


Thanks. The query is also using internally available only sql syntax so
I need to hack the code to let me allow those syntaxes in my sql (which is
running at user level).

Mamta

Mime
View raw message