db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "James Alan Shepherd (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-39) Strange error in JOIN ON clause
Date Sun, 06 Jan 2008 16:46:34 GMT

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


Mime
View raw message