db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rahul Merwah (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-39) Strange error in JOIN ON clause
Date Mon, 23 May 2005 07:02:52 GMT
     [ 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


Mime
View raw message