[ http://issues.apache.org/jira/browse/DERBY-39?page=comments#action_66045
]
Rahul Merwah commented on DERBY-39:
-----------------------------------
I hit into a similar problem with the latest trunk and the analysis showed that
the ON clause is not recognizing all the prior declared table aliases -- just
the last one before.
In this example it translates to:
Select A.ID
FROM
A, B
INNER JOIN B'
ON B.ID = B'.ID <-- (1) WORKS
INNER JOIN C
ON B'.FKEY = C.ID <-- (2) WORKS
INNER JOIN D
ON A.FKEY = D.ID <-- (3) DOESNT WORK
(3) doesn't work because it is refering to anything besides C (which was the
last one it saw before the Join). You can see that (1) will stop working if you
switch the order of "FROM A, B" to "FROM B, A" as A is the last one on the
stack.
According to the specs the on clause can refer to anything table that is on the
stack prior to that point... so C can refer to A, B, and B' ... B' can only
refer to A, and B and so on.
Hope this helps... I am currently re-writing my query to do it in the order as
a workaround but this needs to be fixed in the code-base. Unfortunately my
re-order query is possible since mine was a case of (1) so this will not help
Erik who has a case of (3).
Regards,
- Rahul
> Strange error in JOIN ON clause
> -------------------------------
>
> Key: DERBY-39
> URL: http://issues.apache.org/jira/browse/DERBY-39
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.0.2.0
> Reporter: Erik Bengtson
>
> The exception:
> ---------------------------------------
> Error: An ON clause associated with a JOIN operator is not valid.
> ---------------------------------------
> happens when I run the below SQL script:
> ---------------------------------------
> SELECT
> THIS.DOSSIERTEMPLATE_ID
> FROM DOSSIERTEMPLATE THIS,
> ENTITLEMENT UNBOUND_ENTITLE
> INNER JOIN
> ENTITLEMENT II
> ON UNBOUND_ENTITLE.ENTITLEMENT_ID = II.ENTITLEMENT_ID
> INNER JOIN
> DOSSIERTEMPLATERESOURCE BB
> ON II.ENTITLED_TO_RESOURCE_ID_OID = BB.DOSSIERTEMPLATERESOURCE_ID
> INNER JOIN
> I18N THIS_LABEL
> ON THIS.LABEL_I18N_ID_OID = THIS_LABEL.I18N_ID
> ---------------------------------------
> It works fine if I run without the LABEL join
> ---------------------------------------
> SELECT
> THIS.DOSSIERTEMPLATE_ID
> FROM DOSSIERTEMPLATE THIS,
> ENTITLEMENT UNBOUND_ENTITLE
> INNER JOIN
> ENTITLEMENT II
> ON UNBOUND_ENTITLE.ENTITLEMENT_ID = II.ENTITLEMENT_ID
> INNER JOIN
> DOSSIERTEMPLATERESOURCE BB
> ON II.ENTITLED_TO_RESOURCE_ID_OID = BB.DOSSIERTEMPLATERESOURCE_ID
> ---------------------------------------
> The column LABEL_I18N_ID_OID is BIGINT and has a FK to I18N_ID, which is
> BIGINT as well
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
