[
http://issues.apache.org/jira/browse/DERBY-39?page=comments#action_12366102 ]
Erik Bengtson commented on DERBY-39:
------------------------------------
Once more, I got the same error. This time the SQL should work in Derby IMO. It
does in MSSQL
CREATE TABLE persons (
PERSONID INTEGER NOT NULL,
FIRSTNAME VARCHAR(32) NOT NULL,
LASTNAME VARCHAR(32) NOT NULL,
MIDDLENAME VARCHAR(32),
BIRTHDATE VARCHAR(32) NOT NULL,
ADDRID INTEGER,
STREET VARCHAR(64),
CITY VARCHAR(64),
STATE CHAR(2),
ZIPCODE CHAR(5),
COUNTRY VARCHAR(64),
HIREDATE VARCHAR(32),
WEEKLYHOURS REAL,
DEPARTMENT INTEGER,
FUNDINGDEPT INTEGER,
MANAGER INTEGER,
MENTOR INTEGER,
HRADVISOR INTEGER,
SALARY REAL,
WAGE REAL,
DISCRIMINATOR varchar(64) NOT NULL,
CONSTRAINT EMPS_PK PRIMARY KEY (PERSONID)
);
CREATE TABLE projects (
PROJID INTEGER NOT NULL,
NAME VARCHAR(32) NOT NULL,
BUDGET DECIMAL(11,2) NOT NULL,
CONSTRAINT PROJS_PK PRIMARY KEY (PROJID)
);
CREATE TABLE project_member (
PROJID INTEGER NOT NULL,
MEMBER INTEGER NOT NULL
);
SELECT
THIS.DISCRIMINATOR,THIS.BIRTHDATE,THIS.FIRSTNAME,THIS.LASTNAME,THIS.PERSONID,THIS.HIREDATE,THIS.WEEKLYHOURS
FROM
APPLICATIONIDENTITY0.PERSONS THIS,
APPLICATIONIDENTITY0.PROJECTS UNBOUND_P,
APPLICATIONIDENTITY0.PERSONS UNBOUND_E
LEFT OUTER JOIN APPLICATIONIDENTITY0.PERSONS UNBOUND_E_FIRSTNAME ON
UNBOUND_E.PERSONID = UNBOUND_E_FIRSTNAME.PERSONID
WHERE (THIS.DISCRIMINATOR = 'aa' OR THIS.DISCRIMINATOR = 'bb' OR
THIS.DISCRIMINATOR = 'cc') AND UNBOUND_E.MANAGER = THIS.PERSONID
AND
(EXISTS (SELECT 1 FROM APPLICATIONIDENTITY0.PROJECT_MEMBER THIS_PROJECTS
LEFT OUTER JOIN APPLICATIONIDENTITY0.PROJECTS UNBOUND_P_NAME ON
UNBOUND_P.PROJID = UNBOUND_P_NAME.PROJID
WHERE THIS_PROJECTS."MEMBER" = THIS.PERSONID AND THIS_PROJECTS."MEMBER" =
THIS.PERSONID
AND UNBOUND_P.PROJID = THIS_PROJECTS.PROJID AND UNBOUND_P_NAME."NAME" = 'name'
)
)
AND
(EXISTS (SELECT 1 FROM APPLICATIONIDENTITY0.PERSONS THIS_TEAM WHERE
THIS_TEAM.MANAGER = THIS.PERSONID AND UNBOUND_E_FIRSTNAME.FIRSTNAME = 'name'))
ERROR 42972: An ON clause associated with a JOIN operator is not valid.
at org.apache.derby.iapi.error.StandardException.newException(Unknown
Source)
at
org.apache.derby.impl.sql.compile.JoinNode.deferredBindExpressions(Unknown
Source)
at org.apache.derby.impl.sql.compile.JoinNode.bindResultColumns(Unknown
Source)
at org.apache.derby.impl.sql.compile.FromList.bindResultColumns(Unknown
Source)
at
org.apache.derby.impl.sql.compile.SelectNode.bindResultColumns(Unknown Source)
at
org.apache.derby.impl.sql.compile.SubqueryNode.bindExpression(Unknown Source)
at
org.apache.derby.impl.sql.compile.BinaryOperatorNode.bindExpression(Unknown
Source)
at
org.apache.derby.impl.sql.compile.BinaryLogicalOperatorNode.bindExpression(Unknown
Source)
at org.apache.derby.impl.sql.compile.AndNode.bindExpression(Unknown
Source)
at org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(Unknown
Source)
at
org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressions(Unknown
Source)
at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown
Source)
at org.apache.derby.impl.sql.compile.ReadCursorNode.bind(Unknown Source)
at org.apache.derby.impl.sql.compile.CursorNode.bind(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
at
org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(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.jdbc.Driver30.newEmbedPreparedStatement(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
Source)
at
com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConnection.java:241)
at org.jpox.store.rdbms.RDBMSManager.getStatement(RDBMSManager.java:355)
at org.jpox.store.StatementText.prepareStatement(StatementText.java:204)
at org.jpox.store.query.Query.getStatement(Query.java:797)
at org.jpox.store.query.JDOQLQuery.performExecute(JDOQLQuery.java:520)
at org.jpox.store.query.Query.executeWithMap(Query.java:1113)
at org.jpox.store.query.Query.executeWithArray(Query.java:1086)
at org.jpox.store.query.Query.execute(Query.java:1009)
at org.apache.jdo.tck.query.QueryTest.execute(QueryTest.java:1153)
at org.apache.jdo.tck.query.QueryTest.execute(QueryTest.java:1031)
at
org.apache.jdo.tck.query.QueryTest.executeAPIQuery(QueryTest.java:968)
at
org.apache.jdo.tck.query.QueryTest.executeAPIQuery(QueryTest.java:948)
at
org.apache.jdo.tck.query.jdoql.variables.MixedVariables.testPositive(MixedVariables.java:122)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at junit.framework.TestCase.runTest(TestCase.java:154)
at org.apache.jdo.tck.JDO_Test.runBare(JDO_Test.java:204)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at junit.textui.TestRunner.doRun(TestRunner.java:116)
at junit.textui.TestRunner.doRun(TestRunner.java:109)
at
org.apache.jdo.tck.util.BatchTestRunner.start(BatchTestRunner.java:120)
at org.apache.jdo.tck.util.BatchTestRunner.main(BatchTestRunner.java:95)
----------------
If the lines below are comment, the statement compiles
...
LEFT OUTER JOIN APPLICATIONIDENTITY0.PROJECTS UNBOUND_P_NAME ON
UNBOUND_P.PROJID = UNBOUND_P_NAME.PROJID
..
AND UNBOUND_P.PROJID = THIS_PROJECTS.PROJID AND UNBOUND_P_NAME."NAME" = 'name'
...
> Strange error in JOIN ON clause
> -------------------------------
>
> Key: DERBY-39
> URL: http://issues.apache.org/jira/browse/DERBY-39
> Project: Derby
> Type: Bug
> Components: SQL, Newcomer
> 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.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
