Hi Dan,
My answers inline.
Thanks for your time on it,
Mamta
On 1/27/06, Daniel John Debrunner <[EMAIL PROTECTED]> 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
