[
http://issues.apache.org/jira/browse/DERBY-39?page=comments#action_12365772 ]
Erik Bengtson commented on DERBY-39:
------------------------------------
ONE MORE EXAMPLE
-- schema --
CREATE TABLE CUSTOMER
(
CUSTOMER_ID BIGINT NOT NULL,
"NAME" VARCHAR(100)
)
ALTER TABLE CUSTOMER ADD CONSTRAINT CUSTOMER_PK PRIMARY KEY (CUSTOMER_ID)
CREATE TABLE SUPPLIER
(
SUPPLIER_ID BIGINT NOT NULL,
"NAME" VARCHAR(100)
)
ALTER TABLE SUPPLIER ADD CONSTRAINT SUPPLIER_PK PRIMARY KEY (SUPPLIER_ID)
18:45:23,884 (main) DEBUG [JPOX.RDBMS.DDL] - CREATE TABLE CUSTOMER_SUPPLIER
(
CUSTOMER_ID BIGINT NOT NULL,
SUPPLIER_ID BIGINT NOT NULL
)
ALTER TABLE CUSTOMER_SUPPLIER ADD CONSTRAINT CUSTOMER_SUPTE_PK PRIMARY KEY
(CUSTOMER_ID,SUPPLIER_ID)
ALTER TABLE CUSTOMER_SUPPLIER ADD CONSTRAINT CUSTOMER_SUPTE_FK2 FOREIGN KEY
(SUPPLIER_ID) REFERENCES SUPPLIER (SUPPLIER_ID)
ALTER TABLE CUSTOMER_SUPPLIER ADD CONSTRAINT CUSTOMER_SUPTE_FK1 FOREIGN KEY
(CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID)
-- data used --
Customer customer1 = new Customer("C1");
Customer customer2 = new Customer("C2");
Customer customer3 = new Customer("C3");
Supplier supplier1 = new Supplier("S1");
Supplier supplier2 = new Supplier("S2");
Supplier supplier3 = new Supplier("S3");
Supplier supplier4 = new Supplier("S4");
Supplier supplier5 = new Supplier("S5");
customer1.addSupplier(supplier1);
customer1.addSupplier(supplier2);
customer2.addSupplier(supplier1);
customer2.addSupplier(supplier3);
customer2.addSupplier(supplier5);
customer3.addSupplier(supplier1);
customer3.addSupplier(supplier2);
customer3.addSupplier(supplier3);
customer3.addSupplier(supplier4);
-- query --
SELECT UNBOUND_S.SUPPLIER_ID
FROM CUSTOMER THIS,
SUPPLIER UNBOUND_S
INNER JOIN CUSTOMER_SUPPLIER THIS_SUPPLIERS ON THIS_SUPPLIERS.CUSTOMER_ID =
THIS.CUSTOMER_ID
junit.framework.AssertionFailedError: Error executing JDOQL query "SELECT
UNBOUND_S.SUPPLIER_ID FROM CUSTOMER THIS,SUPPLIER UNBOUND_S INNER JOIN
CUSTOMER_SUPPLIER THIS_SUPPLIERS ON THIS_SUPPLIERS.CUSTOMER_ID =
THIS.CUSTOMER_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.JDOQLResultTest.testSetResultCartesianProductContains(JDOQLResultTest.java:868)
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 junit.framework.Assert.fail(Assert.java:47)
at
org.jpox.persistence.JDOQLResultTest.testSetResultCartesianProductContains(JDOQLResultTest.java:876)
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
