db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Erik Bengtson (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-39) Strange error in JOIN ON clause
Date Sun, 12 Feb 2006 12:10:29 GMT
    [ 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


Mime
View raw message