[DERBY-39] Strange error in JOIN ON clause

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Won't Fix
• Affects Version/s: 10.0.2.0
• Component/s: SQL
• Labels: derby_triage10_8
• Urgency: Normal
• Issue & fix info: Repro attached
• Assignee: Unassigned
• Reporter: Erik Bengtson
• Created: 11/Oct/04 10:35
• Updated: 11/Sep/13 11:47
• Resolved: 11/Sep/13 11:47

Description

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

Issue Links

• relates to: DERBY-3395 'An ON clause associated with a JOIN operator is not valid.' failure with 10.3.2.1 passes with 10.2