db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <sathe...@Sourcery.Org>
Subject Re: (DERBY-655) getImportedKeys returns duplicate rows in some cases
Date Sat, 28 Jan 2006 06:42:14 GMT
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
If I add one following line, getImportedKeys returns only one row for
T1.<br>
<br>
@@ -544,6 +580,7 @@<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
CONGLOMS.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMBER) ELSE 0 END)
&lt;&gt; 0&nbsp; \<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND K.CONGLOMERATEID
= CONGLOMS.CONGLOMERATEID&nbsp; \<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND C.TABLEID = COLS.REFERENCEID&nbsp;
\<br>
<b>+&nbsp;&nbsp;&nbsp; AND CONGLOMS.CONGLOMERATENAME = C.CONSTRAINTNAME
\</b><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ORDER BY PKTABLE_CAT,&nbsp;
\<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
PKTABLE_SCHEM, \<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
PKTABLE_NAME, \<br>
<br>
With the change it returns two rows for T2 and no rows for T3. I am not
sure if this output is correct nor if the change is OK.<br>
<br>
Satheesh<br>
<br>
PS: After changing metadata.properties, a new database needs to be
created to see changed behavior.<br>
<br>
[bandaram:satheesh] java keys T1<br>
******* Call getImportedKeys<br>
****************************************<br>
Imported keys# 1<br>
****************************************<br>
PKTABLE_CAT:<br>
PKTABLE_SCHEM: APP<br>
PKTABLE_NAME: T2<br>
PKCOLUMN_NAME: C21_ID<br>
FKTABLE_CAT:<br>
FKTABLE_SCHEM: APP<br>
FKTABLE_NAME: T1<br>
FKCOULMN_NAME: C11_ID<br>
KEY_SEQ: 1<br>
UPDATE_RULE: 3<br>
DELETE_RULE: 0<br>
FK_NAME: F_12<br>
PK_NAME: SQL060127103319020<br>
DEFERRABILITY: 7<br>
****************************************<br>
<br>
[bandaram:satheesh] java keys T2<br>
******* Call getImportedKeys<br>
****************************************<br>
Imported keys# 1<br>
****************************************<br>
PKTABLE_CAT:<br>
PKTABLE_SCHEM: APP<br>
PKTABLE_NAME: T3<br>
PKCOLUMN_NAME: C31_ID<br>
FKTABLE_CAT:<br>
FKTABLE_SCHEM: APP<br>
FKTABLE_NAME: T2<br>
FKCOULMN_NAME: C21_ID<br>
KEY_SEQ: 1<br>
UPDATE_RULE: 3<br>
DELETE_RULE: 0<br>
FK_NAME: F_443<br>
PK_NAME: SQL060127103320650<br>
DEFERRABILITY: 7<br>
****************************************<br>
<br>
****************************************<br>
Imported keys# 2<br>
****************************************<br>
PKTABLE_CAT:<br>
PKTABLE_SCHEM: APP<br>
PKTABLE_NAME: T3<br>
PKCOLUMN_NAME: C31_ID<br>
FKTABLE_CAT:<br>
FKTABLE_SCHEM: APP<br>
FKTABLE_NAME: T2<br>
FKCOULMN_NAME: C21_ID<br>
KEY_SEQ: 1<br>
UPDATE_RULE: 3<br>
DELETE_RULE: 0<br>
FK_NAME: F_443<br>
PK_NAME: SQL060127103320650<br>
DEFERRABILITY: 7<br>
****************************************<br>
<br>
[bandaram:satheesh] java keys T3<br>
******* Call getImportedKeys<br>
[bandaram:satheesh]<br>
<br>
Satheesh Bandaram wrote:<br>
<blockquote cite="mid43DAF0B7.9080703@Sourcery.Org" type="cite">
  <pre wrap="">Daniel John Debrunner wrote:

  </pre>
  <blockquote type="cite">
    <pre wrap="">Mamta Satoor wrote:
 

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

 

    </pre>
  </blockquote>
  <pre wrap=""><!---->I tried to break up the query and run... The inner SELECT
is returning
just one row, which seems to be correct. So, I suspect we have a problem
with the outer query, which joins several system tables with the derived
table...  I suspect we are missing one join condition, either between
system catalogs or between one of the system catalog and the derived table.

I will try little bit more...

Satheesh

  </pre>
  <blockquote type="cite">
    <pre wrap="">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.





 

    </pre>
  </blockquote>
  <pre wrap=""><!---->


  </pre>
</blockquote>
</body>
</html>


Mime
View raw message