Re: (DERBY-655) getImportedKeys returns duplicate rows in some cases

[Satheesh Bandaram]

If I add one following line, getImportedKeys returns only one row for T1. @@ -544,6 +580,7 @@                 CONGLOMS.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMBER) ELSE 0 END) <> 0  \         AND K.CONGLOMERATEID = CONGLOMS.CONGLOMERATEID  \         AND C.TABLEID = COLS.REFERENCEID  \ +    AND CONGLOMS.CONGLOMERATENAME = C.CONSTRAINTNAME \         ORDER BY PKTABLE_CAT,  \                                 PKTABLE_SCHEM, \                                 PKTABLE_NAME, \ 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. Satheesh PS: After changing metadata.properties, a new database needs to be created to see changed behavior. [bandaram:satheesh] java keys T1 ******* Call getImportedKeys **************************************** Imported keys# 1 **************************************** PKTABLE_CAT: PKTABLE_SCHEM: APP PKTABLE_NAME: T2 PKCOLUMN_NAME: C21_ID FKTABLE_CAT: FKTABLE_SCHEM: APP FKTABLE_NAME: T1 FKCOULMN_NAME: C11_ID KEY_SEQ: 1 UPDATE_RULE: 3 DELETE_RULE: 0 FK_NAME: F_12 PK_NAME: SQL060127103319020 DEFERRABILITY: 7 **************************************** [bandaram:satheesh] java keys T2 ******* Call getImportedKeys **************************************** Imported keys# 1 **************************************** PKTABLE_CAT: PKTABLE_SCHEM: APP PKTABLE_NAME: T3 PKCOLUMN_NAME: C31_ID FKTABLE_CAT: FKTABLE_SCHEM: APP FKTABLE_NAME: T2 FKCOULMN_NAME: C21_ID KEY_SEQ: 1 UPDATE_RULE: 3 DELETE_RULE: 0 FK_NAME: F_443 PK_NAME: SQL060127103320650 DEFERRABILITY: 7 **************************************** **************************************** Imported keys# 2 **************************************** PKTABLE_CAT: PKTABLE_SCHEM: APP PKTABLE_NAME: T3 PKCOLUMN_NAME: C31_ID FKTABLE_CAT: FKTABLE_SCHEM: APP FKTABLE_NAME: T2 FKCOULMN_NAME: C21_ID KEY_SEQ: 1 UPDATE_RULE: 3 DELETE_RULE: 0 FK_NAME: F_443 PK_NAME: SQL060127103320650 DEFERRABILITY: 7 **************************************** [bandaram:satheesh] java keys T3 ******* Call getImportedKeys [bandaram:satheesh] Satheesh Bandaram wrote: Daniel John Debrunner wrote: 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 ... 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 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.