db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Legolas Woodland <legola...@gmail.com>
Subject Re: [DERBY-39] An ON clause associated with a JOIN operator is not valid
Date Tue, 14 Feb 2006 14:03:42 GMT
Can you please tell me which tools u used to generate that graphical 
query plan ?
I would be very helpful for me to find out how i can generate such easy 
to understand plans for my queries.

Thank you.

Erik.Bengtson@intl.westernunion.com wrote:
> Someone might want see the query plan attached
>
> Erik Bengtson
>
>
> -----Original Message-----
> From: Erik Bengtson 
> Sent: Tuesday, February 14, 2006 1:17 PM
> To: derby-user@db.apache.org
> Subject: [DERBY-39] An ON clause associated with a JOIN operator is not
> valid
>
> Hi,
>
> This is issue DERBY-39, can someone give an opinion on this?
>
> It works in MSSQL and DB2
>
> SELECT UNBOUND_P.PROJID FROM applicationidentity0.PERSONS THIS,
> applicationidentity0.PROJECTS UNBOUND_P WHERE
> EXISTS (SELECT 1 FROM applicationidentity0.PROJECT_MEMBER THIS_PROJECTS_P
> LEFT OUTER JOIN applicationidentity0.PROJECTS UNBOUND_P_NAME ON
> UNBOUND_P.PROJID = UNBOUND_P_NAME.PROJID
> WHERE THIS_PROJECTS_P."MEMBER" = THIS.PERSONID AND
> THIS_PROJECTS_P."MEMBER" = THIS.PERSONID AND UNBOUND_P.PROJID =
> THIS_PROJECTS_P.PROJID AND UNBOUND_P_NAME."NAME" = ?
> AND (THIS.DISCRIMINATOR = ? OR THIS.DISCRIMINATOR = ? OR
> THIS.DISCRIMINATOR = ?))
>
> ---
>
> 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.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.prepareInt
> ernalStatement(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(NewProxyConne
> ction.java:241)
> at org.jpox.store.rdbms.RDBMSManager.getStatement(RDBMSManager.java:355)
> at org.jpox.store.StatementText.prepareStatement(StatementText.java:204)
>
> ---- SCHEMA ----
>
> 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
> );
>
>
> Erik Bengtson
>
>
> ed
>   
>
> ------------------------------------------------------------------------
>


Mime
View raw message