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 18:27:58 GMT
    [ 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


Mime
View raw message