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