Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <26215389.5431272485213025.JavaMail.jira@thor>
Date: Wed, 28 Apr 2010 16:06:53 -0400 (EDT)
From: "Knut Anders Hatlen (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-39) Strange error in JOIN ON clause
In-Reply-To: <1434958542.1097490951278.JavaMail.apache@nagoya>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


    [ 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.