db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Andy Jefferson (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-39) Strange error in JOIN ON clause
Date Wed, 28 Apr 2010 17:41:52 GMT

    [ https://issues.apache.org/jira/browse/DERBY-39?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12861864#action_12861864
] 

Andy Jefferson commented on DERBY-39:
-------------------------------------

Yet another example, 

SELECT DISTINCT A0.PRIMARYKEY 
FROM JFIRETRADE_MODEOFPAYMENTFLAVOUR A0, JFIRETRADE_CUSTOMERGROUP VAR_CUSTOMERGROUP 
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 = ?

And the tables are

CREATE TABLE JFIRETRADE_MODEOFPAYMENTFLAVOUR
{
    MODEOFPAYMENTFLAVOURID VARCHAR(100) NOT NULL,
    ORGANISATIONID VARCHAR(100) NOT NULL,
    ICON16X16DATA BLOB(2147483647),
    MODEOFPAYMENT_MODEOFPAYMENTID_OID VARCHAR(100),
    MODEOFPAYMENT_ORGANISATIONID_OID VARCHAR(100),
    PRIMARYKEY VARCHAR(255)
}

CREATE TABLE JFIRETRADE_CUSTOMERGROUP
{
    CUSTOMERGROUPID VARCHAR(100) NOT NULL,
    ORGANISATIONID VARCHAR(100) NOT NULL,
    PRIMARYKEY VARCHAR(255)
}

CREATE TABLE JFIRETRADE_CUSTOMERGROUP_MODEOFPAYMENTFLAVOURS
{
    CUSTOMERGROUPID_OID VARCHAR(100) NOT NULL,
    ORGANISATIONID_OID VARCHAR(100) NOT NULL,
    "KEY" VARCHAR(255) NOT NULL,
    MODEOFPAYMENTFLAVOURID_VID VARCHAR(100),
    ORGANISATIONID_VID VARCHAR(100)
}

CREATE TABLE JFIRETRADE_MODEOFPAYMENTFLAVOURNAME
{
    MODEOFPAYMENTFLAVOURID VARCHAR(100) NOT NULL,
    ORGANISATIONID VARCHAR(100) NOT NULL,
    MODEOFPAYMENTFLAVOUR_MODEOFPAYMENTFLAVOURID_OID VARCHAR(100),
    MODEOFPAYMENTFLAVOUR_ORGANISATIONID_OID VARCHAR(100)
}

and the exception
java.sql.SQLSyntaxErrorException: An ON clause associated with a JOIN operator is not valid.
        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
        at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source)
        at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)

with Derby 10.4.2.0.

Anyone know what *exactly* is wrong with that innocuous query ? and when Derby will finally
be capable of running it ?

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


Mime
View raw message