[
https://issues.apache.org/jira/browse/DERBY-39?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12556392#action_12556392
]
James Alan Shepherd commented on DERBY-39:
------------------------------------------
I might have a similar issue, with workaround:
Derby 10.3.2.1 I get the same error with:
INSERT INTO Blue (
ID , Name , Type
) SELECT
P.ID , X.Name , X.Type
FROM
Red X
JOIN Blue P
ON X.nHelp = (SELECT MAX(nHelp) FROM Red)
AND P.Name = X.ID
LEFT JOIN Blue SO
ON SO.ID = P.ID
AND SO.Name = X.Name
WHERE SO.ID IS NULL
Interestingly, I only get the error when the LEFT JOIN has some data, that is
where some rows have SO.ID NOT NULL.
Now, if I remove the Subquery (SELECT MAX(nHelp) From Red) then all is
sweetness and light and no bug.
Only, that is obviously not functionally equivalent!
Reading Rahul above, I have a cunning plan - rewrite the query so the ON clause
in the LEFT JOIN only references the table preceding it, using a derived table:
INSERT INTO Red (
ID , Name , Type
) SELECT
D.ID , D.Name , D.Type
FROM
( SELECT
P.ID , X.Name , X.Type
FROM Red X
JOIN Red P
ON X.nHelp = (SELECT MAX(nHelp) FROM Red)
AND P.Name = X.ID ) AS D
LEFT JOIN Red SO
ON SO.ID = D.ID
AND SO.Name = D.Name
WHERE SO.ID IS NULL
It would be interesting to see if Erik's cross join problem could be fixed with
the same work around.
I'm travelling at the mo, but I will see if I can put together a fuller example
later this week.
> Strange error in JOIN ON clause
> -------------------------------
>
> Key: DERBY-39
> URL: https://issues.apache.org/jira/browse/DERBY-39
> Project: Derby
> Issue Type: Bug
> Components: Newcomer, SQL
> Affects 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.
-
You can reply to this email to add a comment to the issue online.
