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 Thu, 09 Feb 2006 16:18:58 GMT
    [ 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


Mime
View raw message