db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-39) Strange error in JOIN ON clause
Date Wed, 28 Apr 2010 20:06:53 GMT

    [ https://issues.apache.org/jira/browse/DERBY-39?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12861927#action_12861927
] 

Knut Anders Hatlen commented on DERBY-39:
-----------------------------------------

In 10.6, the error message will at least give some more details about what Derby thinks is
wrong with the query:

ERROR 42X04: Column 'A0.MODEOFPAYMENTFLAVOURID' is either not in any table in the FROM list
or appears within a join specification and is outside the scope of the join specification
or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER
TABLE  statement then 'A0.MODEOFPAYMENTFLAVOURID' is not a column in the target table.

The problem with the query is that a column in the A0 table is referenced in an ON clause,
even if A0 is not in any of the operands of that JOIN operator. I haven't checked whether
or not that's allowed by the SQL standard, but Derby at least currently does not look outside
the left and right operands of the JOIN when resolving column references in an ON clause.

I think the problem can be worked around by only using JOIN operators in the FROM list. With
Derby 10.6 you can just replace the comma with "CROSS JOIN". With 10.5 and earlier, you can
use "INNER JOIN ... ON 1=1" instead, like this:

SELECT DISTINCT A0.PRIMARYKEY 
FROM JFIRETRADE_MODEOFPAYMENTFLAVOUR A0 INNER JOIN JFIRETRADE_CUSTOMERGROUP VAR_CUSTOMERGROUP
ON 1=1 
INNER JOIN JFIRETRADE_CUSTOMERGROUP_MODEOFPAYMENTFLAVOURS C0 ON C0.CUSTOMERGROUPID_OID = VAR_CUSTOMERGROUP.CUSTOMERGROUPID
AND C0.ORGANISATIONID_OID = VAR_CUSTOMERGROUP.ORGANISATIONID 
LEFT OUTER JOIN JFIRETRADE_MODEOFPAYMENTFLAVOURNAME D0 ON D0.MODEOFPAYMENTFLAVOUR_MODEOFPAYMENTFLAVOURID_OID
= A0.MODEOFPAYMENTFLAVOURID AND D0.MODEOFPAYMENTFLAVOUR_ORGANISATIONID_OID = A0.ORGANISATIONID

WHERE C0.MODEOFPAYMENTFLAVOURID_VID = A0.MODEOFPAYMENTFLAVOURID 
AND C0.ORGANISATIONID_VID = A0.ORGANISATIONID 
AND VAR_CUSTOMERGROUP.ORGANISATIONID = ? AND VAR_CUSTOMERGROUP.CUSTOMERGROUPID = ?

> Strange error in JOIN ON clause
> -------------------------------
>
>                 Key: DERBY-39
>                 URL: https://issues.apache.org/jira/browse/DERBY-39
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.0.2.0
>            Reporter: Erik Bengtson
>         Attachments: d39.sql, derby-joinon.tar.gz
>
>
> 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.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message