That’s it, but I don’t think it is capable to connect to derby databases.

 

The graph is from a DB2 database, which might be a guideline for someone fixing the bug

 

Erik Bengtson

+32 2 639-7260

 

-----Original Message-----
From: legolas.w@gmail.com [mailto:legolas.w@gmail.com]
Sent: Tuesday, February 14, 2006 4:52 PM
To: derby-user@db.apache.org
Subject: Re: [DERBY-39] An ON clause associated with a JOIN operator is not valid

 

Do you mean something from DB2 control center ?
I have a db2 8.2  evaluation and i can install it , will the control center work on derby 10.* ?
Thank you

Erik.Bengtson@intl.westernunion.com wrote:

Using DB2’s Command Editor.

 

Erik Bengtson

+32 2 639-7260

 

-----Original Message-----
From: legolas.w@gmail.com [mailto:legolas.w@gmail.com]
Sent: Tuesday, February 14, 2006 3:04 PM
To: derby-user@db.apache.org
Subject: Re: [DERBY-39] An ON clause associated with a JOIN operator is not valid

 

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