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 Thu, 29 Apr 2010 07:19:56 GMT

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

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

Thx Kristian. That build changes the generated query (with DataNucleus 2.1.0.m2) to 

SELECT DISTINCT 'org.datanucleus.test.ModeOfPaymentFlavour' AS NUCLEUS_TYPE,A0.ICON16X16DATA,A0.MODEOFPAYMENT_MODEOFPAYMENTID_OID,A0.MODEOFPAYMENT_ORGANISATIONID_OID,A0.MODEOFPAYMENTFLAVOURID,D0.MODEOFPAYMENTFLAVOURID,D0.ORGANISATIONID,D0.MODEOFPAYMENTFLAVOUR_MODEOFPAYMENTFLAVOURID_OID,D0.MODEOFPAYMENTFLAVOUR_ORGANISATIONID_OID,A0.ORGANISATIONID,A0.PRIMARYKEY
FROM JFIRETRADE_MODEOFPAYMENTFLAVOUR A0 
CROSS JOIN JFIRETRADE_CUSTOMERGROUP VAR_CUSTOMERGROUP
INNER JOIN JFIRETRADE_CUSTOMERGROUP_MODEOFPAYMENTFLAVOURS C0 ON VAR_CUSTOMERGROUP.CUSTOMERGROUPID
= C0.CUSTOMERGROUPID_OID AND VAR_CUSTOMERGROUP.ORGANISATIONID = C0.ORGANISATIONID_OID 
LEFT OUTER JOIN JFIRETRADE_MODEOFPAYMENTFLAVOURNAME D0 ON A0.MODEOFPAYMENTFLAVOURID = D0.MODEOFPAYMENTFLAVOUR_MODEOFPAYMENTFLAVOURID_OID
AND A0.ORGANISATIONID = D0.MODEOFPAYMENTFLAVOUR_ORGANISATIONID_OID 
WHERE C0.MODEOFPAYMENTFLAVOURID_VID = A0.MODEOFPAYMENTFLAVOURID 
AND C0.ORGANISATIONID_VID = A0.ORGANISATIONID 
AND VAR_CUSTOMERGROUP.ORGANISATIONID = ? 
AND VAR_CUSTOMERGROUP.CUSTOMERGROUPID = ?

and there is no longer an error about "JOIN ON clause", so the CROSS JOIN handling is seemingly
the issue, not any use of A0. The error becomes
java.sql.SQLException: Columns of type 'BLOB' may not be used in CREATE INDEX, ORDER BY, GROUP
BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported
for that type.
but that's my problem for selecting a BLOB field (A0.ICON16X16DATA) when using DISTINCT and
easily fixed.

Thx for your time. Look forward to a 10.6 release ;-)

> 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