You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Andy Jefferson (JIRA)" <ji...@apache.org> on 2010/04/28 19:43:48 UTC
[jira] Issue Comment Edited: (DERBY-39) Strange error in JOIN ON
clause
[ https://issues.apache.org/jira/browse/DERBY-39?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12861864#action_12861864 ]
Andy Jefferson edited comment on DERBY-39 at 4/28/10 1:43 PM:
--------------------------------------------------------------
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 ? Or alternatively tell me how i can reasonably adjust that query to get Derby to swallow it ? (obviously since the query is generated by an ORM here then there is no guarantee that any workaround will be acceptable, but I'll humour the idea)
was (Author: andy):
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.