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 "Erik Bengtson (JIRA)" <de...@db.apache.org> on 2006/02/09 17:18:58 UTC
[jira] Commented: (DERBY-39) Strange error in JOIN ON clause
[ http://issues.apache.org/jira/browse/DERBY-39?page=comments#action_12365755 ]
Erik Bengtson commented on DERBY-39:
------------------------------------
Back to the same issue again. I have another sample to reproduce the problem
CREATE TABLE CONTENTTYPE
(
CONTENTTYPE_ID BIGINT NOT NULL,
"TYPE" VARCHAR(255)
)
ALTER TABLE CONTENTTYPE ADD CONSTRAINT CONTENTTYPE_PK PRIMARY KEY (CONTENTTYPE_ID)
CREATE TABLE DOCUMENT
(
DOCUMENT_ID BIGINT NOT NULL,
DOCUMENT VARCHAR(255)
)
ALTER TABLE DOCUMENT ADD CONSTRAINT DOCUMENT_PK PRIMARY KEY (DOCUMENT_ID)
CREATE TABLE CONTENT
(
CONTENT_ID BIGINT NOT NULL,
CONTENT VARCHAR(255),
CONTENT_TYPE_CONTENTTYPE_ID_OID BIGINT
)
ALTER TABLE CONTENT ADD CONSTRAINT CONTENT_PK PRIMARY KEY (CONTENT_ID)
CREATE TABLE DOCUMENT_CONTENTS
(
DOCUMENT_ID_OID BIGINT NOT NULL,
CONTENT_ID_EID BIGINT NOT NULL
)
ALTER TABLE DOCUMENT_CONTENTS ADD CONSTRAINT DOCUMENT_CONUK_PK PRIMARY KEY (DOCUMENT_ID_OID,CONTENT_ID_EID)
ALTER TABLE CONTENT ADD CONSTRAINT CONTENT_FK1 FOREIGN KEY (CONTENT_TYPE_CONTENTTYPE_ID_OID) REFERENCES CONTENTTYPE (CONTENTTYPE_ID)
ALTER TABLE DOCUMENT_CONTENTS ADD CONSTRAINT DOCUMENT_CONUK_FK2 FOREIGN KEY (CONTENT_ID_EID) REFERENCES CONTENT (CONTENT_ID)
ALTER TABLE DOCUMENT_CONTENTS ADD CONSTRAINT DOCUMENT_CONUK_FK1 FOREIGN KEY (DOCUMENT_ID_OID) REFERENCES DOCUMENT (DOCUMENT_ID)
---populate here something
INSERT INTO DOCUMENT (DOCUMENT_ID,DOCUMENT) VALUES (?,?)
INSERT INTO CONTENT (CONTENT_ID,CONTENT,CONTENT_TYPE_CONTENTTYPE_ID_OID) VALUES (?,?,?)
INSERT INTO DOCUMENT_CONTENTS (DOCUMENT_ID_OID,CONTENT_ID_EID) VALUES (?,?)
INSERT INTO CONTENT (CONTENT_ID,CONTENT,CONTENT_TYPE_CONTENTTYPE_ID_OID) VALUES (?,?,?)
INSERT INTO DOCUMENT_CONTENTS (DOCUMENT_ID_OID,CONTENT_ID_EID) VALUES (?,?)
INSERT INTO CONTENT (CONTENT_ID,CONTENT,CONTENT_TYPE_CONTENTTYPE_ID_OID) VALUES (?,?,?)
INSERT INTO DOCUMENT_CONTENTS (DOCUMENT_ID_OID,CONTENT_ID_EID) VALUES (?,?)
INSERT INTO DOCUMENT (DOCUMENT_ID,DOCUMENT) VALUES (?,?)
INSERT INTO DOCUMENT_CONTENTS (DOCUMENT_ID_OID,CONTENT_ID_EID) VALUES (?,?)
INSERT INTO DOCUMENT_CONTENTS (DOCUMENT_ID_OID,CONTENT_ID_EID) VALUES (?,?)
INSERT INTO DOCUMENT_CONTENTS (DOCUMENT_ID_OID,CONTENT_ID_EID) VALUES (?,?)
INSERT INTO DOCUMENT (DOCUMENT_ID,DOCUMENT) VALUES (?,?)
INSERT INTO DOCUMENT_CONTENTS (DOCUMENT_ID_OID,CONTENT_ID_EID) VALUES (?,?)
INSERT INTO DOCUMENT_CONTENTS (DOCUMENT_ID_OID,CONTENT_ID_EID) VALUES (?,?)
INSERT INTO DOCUMENT (DOCUMENT_ID,DOCUMENT) VALUES (?,?)
INSERT INTO DOCUMENT_CONTENTS (DOCUMENT_ID_OID,CONTENT_ID_EID) VALUES (?,?)
INSERT INTO DOCUMENT_CONTENTS (DOCUMENT_ID_OID,CONTENT_ID_EID) VALUES (?,?)
--select
SELECT
DISTINCT 'org.jpox.samples.document.Document' AS JPOXMETADATA,THIS.DOCUMENT_ID,THIS.DOCUMENT
FROM
DOCUMENT THIS,
CONTENT UNBOUND_CONTENT
LEFT OUTER JOIN CONTENT UNBOUND_CONTENT_CONTENT
ON UNBOUND_CONTENT.CONTENT_ID = UNBOUND_CONTENT_CONTENT.CONTENT_ID
INNER JOIN DOCUMENT_CONTENTS THIS_CONTENTS
ON THIS_CONTENTS.DOCUMENT_ID_OID = THIS.DOCUMENT_ID
WHERE
UNBOUND_CONTENT_CONTENT.CONTENT = ? AND UNBOUND_CONTENT.CONTENT_ID = UNBOUND_CONTENT.CONTENT_ID
-------------------------------
javax.jdo.JDODataStoreException: Error executing JDOQL query "SELECT DISTINCT 'org.jpox.samples.document.Document' AS JPOXMETADATA,THIS.DOCUMENT_ID,THIS.DOCUMENT FROM DOCUMENT THIS,CONTENT UNBOUND_CONTENT LEFT OUTER JOIN CONTENT UNBOUND_CONTENT_CONTENT ON UNBOUND_CONTENT.CONTENT_ID = UNBOUND_CONTENT_CONTENT.CONTENT_ID INNER JOIN DOCUMENT_CONTENTS THIS_CONTENTS ON THIS_CONTENTS.DOCUMENT_ID_OID = THIS.DOCUMENT_ID WHERE UNBOUND_CONTENT_CONTENT.CONTENT = ? AND UNBOUND_CONTENT.CONTENT_ID = UNBOUND_CONTENT.CONTENT_ID" : An ON clause associated with a JOIN operator is not valid.
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.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 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.jpox.persistence.JDOQLBasicTest.testQueryUnboundVariables(JDOQLBasicTest.java:2284)
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:324)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
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 org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:478)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:344)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
.
at org.jpox.store.query.JDOQLQuery.performExecute(JDOQLQuery.java:574)
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.jpox.persistence.JDOQLBasicTest.testQueryUnboundVariables(JDOQLBasicTest.java:2284)
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:324)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
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 org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:478)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:344)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
> 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