You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Mamta Satoor <ms...@gmail.com> on 2006/01/26 18:18:27 UTC
(DERBY-655) getImportedKeys returns duplicate rows in some cases
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. Deepa provided a simple JDBC program in the
JIRA entry to call getImportedKeys and I modified it to make that call on
table t1 and the output of the program is as follows
$java org.apache.derbyTesting.functionTests.tests.lang.keys bigdb
******* 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: SQL060126090541940
DEFERRABILITY: 7
****************************************
****************************************
Imported keys# 2
****************************************
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: SQL060126090541940
DEFERRABILITY: 7
****************************************
It looks like the getImportedKeys should have stopped after finding the
direct foreign key reference by column c11_id to t2(c21_id) and should have
returned just one row from getImportedKeys. Is that correct? And if that is
correct, then I think I need to try to see how the following sql for
getImportedKeys in metadata.properties can be changed to do that. The sql
looks very daunting to me so if any of the sql experts on the list can give
some ideas on fixing this sql, I will highly appreciate that.
getImportedKeys=\
SELECT CAST ('' AS VARCHAR(128)) AS PKTABLE_CAT, \
S.SCHEMANAME AS PKTABLE_SCHEM, \
TABLENAME AS PKTABLE_NAME, \
COLS.COLUMNNAME AS PKCOLUMN_NAME, \
CAST ('' AS VARCHAR(128)) AS FKTABLE_CAT, \
FKTABLE_SCHEM, \
FKTABLE_NAME, \
FKCOLUMN_NAME, \
CAST (CONGLOMS.DESCRIPTOR.getKeyColumnPosition( \
COLS.COLUMNNUMBER) \
AS SMALLINT) AS KEY_SEQ, \
CAST ((CASE WHEN FK_UPDATERULE='S' \
THEN java.sql.DatabaseMetaData::importedKeyRestrict ELSE \
(CASE WHEN FK_UPDATERULE='R' \
THEN java.sql.DatabaseMetaData::importedKeyNoAction ELSE \
java.sql.DatabaseMetaData::importedKeyNoAction END) END) \
AS SMALLINT) AS UPDATE_RULE, \
CAST ((CASE WHEN FK_DELETERULE='S' \
THEN java.sql.DatabaseMetaData::importedKeyRestrict ELSE \
(CASE WHEN FK_DELETERULE='R' \
THEN java.sql.DatabaseMetaData::importedKeyNoAction ELSE \
(CASE WHEN FK_DELETERULE='C' \
THEN java.sql.DatabaseMetaData::importedKeyCascade ELSE \
(CASE WHEN FK_DELETERULE='U' \
THEN java.sql.DatabaseMetaData::importedKeySetNull ELSE \
java.sql.DatabaseMetaData::importedKeyNoAction END) END) END) END) \
AS SMALLINT) AS DELETE_RULE, \
FK_NAME, \
CONSTRAINTNAME AS PK_NAME, \
CAST (java.sql.DatabaseMetaData::importedKeyNotDeferrable \
AS SMALLINT) AS DEFERRABILITY \
FROM --DERBY-PROPERTIES joinOrder=FIXED \n\
(SELECT F2.keyCONSTRAINTID AS FK_ID, \
FKTB_SCHEMA AS FKTABLE_SCHEM, \
FKTB_NAME AS FKTABLE_NAME, \
COLS2.COLUMNNAME AS FKCOLUMN_NAME, \
CONGLOMS2.DESCRIPTOR.getKeyColumnPosition( \
COLS2.COLUMNNUMBER) AS KEY_SEQ, \
C2.CONSTRAINTNAME AS FK_NAME, \
F2.DELETERULE AS FK_UPDATERULE, \
F2.DELETERULE AS FK_DELETERULE \
FROM --DERBY-PROPERTIES joinOrder=FIXED \n\
(SELECT T2.TABLEID AS FKTB_ID, \
S2.SCHEMANAME AS FKTB_SCHEMA, \
T2.TABLENAME AS FKTB_NAME \
FROM \
SYS.SYSTABLES T2 --DERBY-PROPERTIES index = 'SYSTABLES_INDEX1' \n\
, SYS.SYSSCHEMAS S2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index
= 'SYSSCHEMAS_INDEX1' \n\
WHERE \
((1=1) OR ? IS NOT NULL) \
AND S2.SCHEMANAME LIKE ? \
AND T2.TABLENAME LIKE ? \
AND S2.SCHEMAID = T2.SCHEMAID \
) AS FKTB (FKTB_ID, FKTB_SCHEMA, FKTB_NAME), \
SYS.SYSCONSTRAINTS c2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP,
index = 'SYSCONSTRAINTS_INDEX3' \n\
, SYS.SYSFOREIGNKEYS F2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP,
index = 'SYSFOREIGNKEYS_INDEX1' \n\
, SYS.SYSCONGLOMERATES CONGLOMS2 --DERBY-PROPERTIES
joinStrategy=NESTEDLOOP, index = 'SYSCONGLOMERATES_INDEX1' \n\
, SYS.SYSCOLUMNS COLS2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP,
index = 'SYSCOLUMNS_INDEX1' \n\
WHERE \
FKTB.FKTB_ID = C2.TABLEID \
AND F2.CONSTRAINTID = C2.CONSTRAINTID \
AND FKTB.FKTB_ID = COLS2.REFERENCEID \
AND (CASE WHEN CONGLOMS2.DESCRIPTOR IS NOT NULL THEN \
CONGLOMS2.DESCRIPTOR.getKeyColumnPosition( \
COLS2.COLUMNNUMBER) ELSE \
0 END) <> 0 \
AND F2.CONGLOMERATEID = CONGLOMS2.CONGLOMERATEID \
) AS FKINFO(FK_ID, \
FKTABLE_SCHEM, \
FKTABLE_NAME, \
FKCOLUMN_NAME, \
KEY_SEQ, \
FK_NAME, \
FK_UPDATERULE, \
FK_DELETERULE), \
SYS.SYSCONSTRAINTS c --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index =
'SYSCONSTRAINTS_INDEX1' \n\
, SYS.SYSTABLES T --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index =
'SYSTABLES_INDEX2' \n\
, SYS.SYSSCHEMAS S --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index =
'SYSSCHEMAS_INDEX2' \n\
, SYS.SYSKEYS K --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index =
'SYSKEYS_INDEX1' \n\
, SYS.SYSCONGLOMERATES CONGLOMS --DERBY-PROPERTIES joinStrategy =
NESTEDLOOP, index = 'SYSCONGLOMERATES_INDEX1' \n\
, SYS.SYSCOLUMNS COLS --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index =
'SYSCOLUMNS_INDEX1' \n\
WHERE T.TABLEID = C.TABLEID \
AND C.CONSTRAINTID = FKINFO.FK_ID \
AND FKINFO.KEY_SEQ = CONGLOMS.DESCRIPTOR.getKeyColumnPosition( \
COLS.COLUMNNUMBER) \
AND S.SCHEMAID = T.SCHEMAID \
AND K.CONSTRAINTID = C.CONSTRAINTID \
AND (CASE WHEN CONGLOMS.DESCRIPTOR IS NOT NULL THEN \
CONGLOMS.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMBER) ELSE 0 END) <>
0 \
AND K.CONGLOMERATEID = CONGLOMS.CONGLOMERATEID \
AND C.TABLEID = COLS.REFERENCEID \
ORDER BY PKTABLE_CAT, \
PKTABLE_SCHEM, \
PKTABLE_NAME, \
PK_NAME, \
KEY_SEQ
thanks,
Mamta
Re: (DERBY-655) getImportedKeys returns duplicate rows in some cases
Posted by Satheesh Bandaram <sa...@Sourcery.Org>.
You have to change the code in two classes, in case you haven't found it
already to make the query run (MethodCallNode and
StaticClassFieldReferenceNode). You may also have to change the
sqlgrammar.jj. Search for INTERNAL_SQL.
Satheesh
Mamta Satoor wrote:
>
> 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
>
>
>
Re: (DERBY-655) getImportedKeys returns duplicate rows in some cases
Posted by Mamta Satoor <ms...@gmail.com>.
I quickly compared the sql for getImportedKeys in metadata.properties for
Derby 10.2 and 5.1.60 and didn't notice any change other than the syntax
change. I wonder if the contents of the system tables have changed between
the 2 release. First I will try to run my simple test case against
5.1.60and see what happens.
Mamta
On 1/30/06, Mamta Satoor <ms...@gmail.com> wrote:
>
> Hi Kathey,
>
> I think this info might come out very handy. Especially, since I will have
> both the databases to compare.
>
> thanks,
> Mamta
>
>
> On 1/30/06, Kathey Marsden <km...@sbcglobal.net> wrote:
> >
> > Hi Mamta,
> >
> > I have not been following the technical details of this issue, but one
> > thing that might be of help is that I know that this issue arose out of
> > a database that a customer was migrating from Cloudscape 5.1.60 to
> > Derby. When comparing the 5.1.60 and 10.0 database we found that the
> > 5.1.60 getImportedKeys() was returning the right results, but 10.x was
> > not, even though the migration was successful, so it may be helpful to
> > compare with the 5.1.60 database.
> >
> > I'll contact you off-line to get you the 5.1.60 database for
> > evaluation.
> >
> > Kathey
> >
> >
> >
>
Re: (DERBY-655) getImportedKeys returns duplicate rows in some cases
Posted by Mamta Satoor <ms...@gmail.com>.
Hi Kathey,
I think this info might come out very handy. Especially, since I will have
both the databases to compare.
thanks,
Mamta
On 1/30/06, Kathey Marsden <km...@sbcglobal.net> wrote:
>
> Hi Mamta,
>
> I have not been following the technical details of this issue, but one
> thing that might be of help is that I know that this issue arose out of
> a database that a customer was migrating from Cloudscape 5.1.60 to
> Derby. When comparing the 5.1.60 and 10.0 database we found that the
> 5.1.60 getImportedKeys() was returning the right results, but 10.x was
> not, even though the migration was successful, so it may be helpful to
> compare with the 5.1.60 database.
>
> I'll contact you off-line to get you the 5.1.60 database for evaluation.
>
> Kathey
>
>
>
Re: (DERBY-655) getImportedKeys returns duplicate rows in some cases
Posted by Kathey Marsden <km...@sbcglobal.net>.
Hi Mamta,
I have not been following the technical details of this issue, but one
thing that might be of help is that I know that this issue arose out of
a database that a customer was migrating from Cloudscape 5.1.60 to
Derby. When comparing the 5.1.60 and 10.0 database we found that the
5.1.60 getImportedKeys() was returning the right results, but 10.x was
not, even though the migration was successful, so it may be helpful to
compare with the 5.1.60 database.
I'll contact you off-line to get you the 5.1.60 database for evaluation.
Kathey
Re: (DERBY-655) getImportedKeys returns duplicate rows in some cases
Posted by Mamta Satoor <ms...@gmail.com>.
Hi Dan,
My answers inline.
Thanks for your time on it,
Mamta
On 1/27/06, Daniel John Debrunner <dj...@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
Re: (DERBY-655) getImportedKeys returns duplicate rows in some cases
Posted by Mamta Satoor <ms...@gmail.com>.
Hi Satheesh,
Thanks for all the time you spent on this.
I copied the suggested changes into my codeline and tried running derbyall
against it. The existing metadata.java and odbc_metadata.java fail and don't
return any row for getImportedKeys test. So, looks like more tweaking is
needed to fix the sql in metadata.properties for getImportedKeys. If
you/anyone else think of any tips, please let me know. In the mean time, I
will continue to work on my end too.
thanks,
Mamta
On 1/27/06, Satheesh Bandaram <sa...@sourcery.org> wrote:
>
> 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.
>
>
>
>
>
>
>
>
>
>
>
Re: (DERBY-655) getImportedKeys returns duplicate rows in some cases
Posted by Satheesh Bandaram <sa...@Sourcery.Org>.
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.
>
>
>
>
>
>
>
Re: (DERBY-655) getImportedKeys returns duplicate rows in some cases
Posted by Daniel John Debrunner <dj...@apache.org>.
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.