db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-39) Strange error in JOIN ON clause
Date Tue, 14 Feb 2006 22:45:15 GMT
    [ http://issues.apache.org/jira/browse/DERBY-39?page=comments#action_12366394 ] 

Rick Hillegas commented on DERBY-39:
------------------------------------

I'm puzzled by this last query. What is it trying to do? I get the error if I pare off the
WHERE clause and just focus on the left join in the subselect:

SELECT UNBOUND_P.PROJID FROM PERSONS THIS,
PROJECTS UNBOUND_P WHERE
EXISTS
(SELECT 1 FROM PROJECT_MEMBER THIS_PROJECTS_P
LEFT OUTER JOIN PROJECTS UNBOUND_P_NAME ON
UNBOUND_P.PROJID = UNBOUND_P_NAME.PROJID
);

The ON clause does not join the tables in the subselect, which puzzles me. Instead, the ON
clause joins one of the subselected tables to a table in the outer select. The following alternative
query does make sense to me and does work:

SELECT UNBOUND_P.PROJID FROM PERSONS THIS,
PROJECTS UNBOUND_P WHERE
EXISTS
(SELECT 1 FROM PROJECT_MEMBER THIS_PROJECTS_P
LEFT OUTER JOIN PROJECTS UNBOUND_P_NAME ON
THIS_PROJECTS_P.PROJID = UNBOUND_P_NAME.PROJID
);

I would expect the ON clause to join the tables mentioned in the LEFT JOIN, but in the problem
query it doesn't. Is it possible that some machine is generating this query and is as confused
as I am?


> Strange error in JOIN ON clause
> -------------------------------
>
>          Key: DERBY-39
>          URL: http://issues.apache.org/jira/browse/DERBY-39
>      Project: Derby
>         Type: Bug
>   Components: SQL, Newcomer
>     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