Fwd: [jira] [Comment Edited] (JDO-836) TCK: QueryCancel Test class fails

[Craig Russell]

Hi,

The JDO project is having some issues with a feature that was never formally 
tested: cancel query. It is intended to allow the JDO user to cancel a query 
from a different thread. It is an optional feature enabled by a user 
configuration flag.

The problem is that it does not seem to work. I've attached a log excerpt that 
shows that query starts in one thread, query cancel is called by the user to 
the DataNucleus implementation which then is supposed to call the Derby cancel 
query via JDBC.

Without diving into the details, can you confirm that the Derby JDBC 
implementation does implement query cancel without throwing an exception?

Thanks,
Craig

> Begin forwarded message:
> 
> From: "Michael Bouschen (Jira)" 
> Subject: [jira] [Comment Edited] (JDO-836) TCK: QueryCancel Test class fails
> Date: February 27, 2024 at 11:19:00 PST
> To: jdo-...@db.apache.org
> Reply-To: jdo-...@db.apache.org
> 
> I added some lof statement to check that query cancel is called after 
> query.execute has been called:
> 
> 22:14:11,040 (Query Executor) INFO  [org.apache.jdo.tck] - Start query 
> execute 1708809251037
> 22:14:12,038 (ForkJoinPool-1-worker-19) INFO  [org.apache.jdo.tck] - Start 
> query cancel 1708809252038
> 22:14:12,039 (ForkJoinPool-1-worker-19) INFO  [org.apache.jdo.tck] - End 
> query cancel   1708809252039
> 22:15:00,860 (Query Executor) INFO  [org.apache.jdo.tck] - End query execute  
>   1708809300860
> 22:15:00,861 (Query Executor) INFO  [org.apache.jdo.tck] - query execute took 
> 49823ms
> 

Craig L Russell
c...@apache.org

Re: DB web site migration from CMS

[Craig Russell]

What the JDO team did was to open an INFRA JIRA asking for help in migration.

That worked well. There are lots of choices for web sites that do not use CMS.

Infra can point you to the relevant pages on the wiki (I do not know them right 
offhand).

Regards,
Craig

> On Feb 4, 2021, at 10:11 AM, Rick Hillegas  wrote:
> 
> Could someone please post pointers to the CMS migration effort so that I can 
> educate myself about what it entails? Once I know more, I can start a Derby 
> community discussion about what, if anything, can be done before this 
> deadline.
> 
> Thanks,
> -Rick
> 
> On 2/2/21 11:26 AM, Craig Russell wrote:
>> Hi,
>> 
>> priv...@db.apache.org in bcc...
>> 
>> The JDO project is almost complete migrating the db.apache.org/jdo site from 
>> the soon-to-be-retired CMS system to a new github site repo using GitHub 
>> Actions and .asf.yaml to publish the site.
>> 
>> Infra wants to know what the plans are for the main site, Derby, and Torque 
>> to migrate.
>> 
>> Craig
>> 
>> Craig L Russell
>> c...@apache.org
>> 
> 

Craig L Russell
c...@apache.org

Re: DB web site migration from CMS

[Craig Russell]

> On Feb 3, 2021, at 7:22 AM, Rick Hillegas  wrote:
> 
> I have not been following this issue. I believe that Bryan put some effort 
> into converting the Derby website, but I don't know where that stands. A 
> couple questions:
> 
> 1) How much effort was required to migrate JDO?

Non-trivial. We decided to use github with actions and .asf.yaml on a brand new 
repository with asciidoc as the source. Tilmann did most of the initial work 
and now there is a lot of cleanup due to the fact that there are multiple 
sources for: db.apache.org <http://db.apache.org/>, db.apache.org/torque 
<http://db.apache.org/torque>, db.apache.org/derby 
<http://db.apache.org/derby>, as well as jdo. 
> 
> 2) Can we still manage our code repo with subversion?

This is above my pay grade. ;-) 

Craig
> 
> Thanks,
> -Rick
> 
> On 2/2/21 11:26 AM, Craig Russell wrote:
>> Hi,
>> 
>> priv...@db.apache.org in bcc...
>> 
>> The JDO project is almost complete migrating the db.apache.org/jdo site from 
>> the soon-to-be-retired CMS system to a new github site repo using GitHub 
>> Actions and .asf.yaml to publish the site.
>> 
>> Infra wants to know what the plans are for the main site, Derby, and Torque 
>> to migrate.
>> 
>> Craig
>> 
>> Craig L Russell
>> c...@apache.org
>> 
> 

Craig L Russell
c...@apache.org

DB web site migration from CMS

[Craig Russell]

Hi,

priv...@db.apache.org in bcc...

The JDO project is almost complete migrating the db.apache.org/jdo site from 
the soon-to-be-retired CMS system to a new github site repo using GitHub 
Actions and .asf.yaml to publish the site.

Infra wants to know what the plans are for the main site, Derby, and Torque to 
migrate.

Craig

Craig L Russell
c...@apache.org

Release download pages

[Craig Russell]

Hi,

FYI, the download pages are fine except for one issue. MD5 is no longer 
recommended for checksums, and SHA is required. 

Announcements of Apache project releases must contain a link to the relevant 
download page. [1]
The download page must provide public download links where current official 
source releases and accompanying cryptographic files may be obtained. [2]

Announcements that contain a link to the dyn/closer page alone will be rejected 
by the moderators.

Announcements that contain a link to a web page that does not include a link to 
a mirror to the artifact plus links to the signature and at least one sha 
checksum will be rejected.

[1] http://www.apache.org/legal/release-policy.html#release-announcements
[2] https://www.apache.org/dev/release-distribution#download-links

This advice is for the next release...

Regards,

Craig

Craig L Russell
Secretary, Apache Software Foundation
c...@apache.org http://db.apache.org/jdo

Re: Cannot post a release announcement to announce@

[Craig Russell]

Hi Rick,

The announce moderation email never made it, so the problem is almost certainly 
on the sending side.

You can ssh to minotaur.apache.org  if you set up 
an ssh key. I've not send mail from my login so I can't help there, but it 
might be easier than setting up your mail client.

See if these links help you set it up.

https://blogs.apache.org/infra/entry/committer_shell_access_to_people 


http://linux.die.net/man/1/ssh-add 

http://linux.die.net/man/1/ssh-agent 

Craig

> On Oct 21, 2017, at 11:05 AM, Rick Hillegas  wrote:
> 
> Dear announce-owner,
> 
> I am forwarding this question to you because I received an unhelpful canned 
> response from the announce-help list when I sent it the following message.
> 
> Thanks in advance for your advice,
> -Rick
> 
> -
> 
> Dear announce-help,
> 
> I hope that someone will be able to help me with the following problem:
> 
> I am trying to publish a new release of Apache Derby 
> (http://db.apache.org/derby/releases/release-10.14.1.0.cgi 
> ). I have 
> successfully sent the release announcement to our user and developer lists. 
> However, I have not been able to send the release announcement to the 
> announce@ mailing list. So far, the Infrastructure team has not been able to 
> coach me through these problems: 
> https://issues.apache.org/jira/browse/INFRA-15311 
> .
> 
> I served as a release manager two years ago. At that time, I was able to ssh 
> to an Apache machine and use the command line mail tool in order to post the 
> release announcement to announce@. Unfortunately, committers can't directly 
> log onto Apache machines today.
> 
> I have failed to configure my Thunderbird mail tool to use the Apache relay 
> service, per Infrastructure's advice as described here: 
> https://reference.apache.org/committer/email#sendingemailfromyourapacheorgemailaddress
>  
> .
>  I don't know why. Maybe the Apache relay service is not a mail provider 
> supported by Thunderbird. Maybe Thunderbird requires incoming as well as 
> outgoing configuration parameters and the Apache instructions only state the 
> outgoing parameters.
> 
> I have also failed to configure sendmail on my Mac OSX machine. At this 
> point, I am very far afield, very far outside my skill set.
> 
> I would appreciate your advice on how to post a release announcement to 
> announce@.
> 
> Thanks,
> -Rick
> 
> 
> 
>  Forwarded Message 
> Subject:  Cannot post a release announcement to announce@
> Date: Sat, 21 Oct 2017 08:46:53 -0700
> From: Rick Hillegas  
> To:   announce-h...@apache.org , 
> derby-dev@db.apache.org  
>  
> 
> Dear announce-help,
> 
> I hope that someone will be able to help me with the following problem:
> 
> I am trying to publish a new release of Apache Derby 
> (http://db.apache.org/derby/releases/release-10.14.1.0.cgi 
> ). I have 
> successfully sent the release announcement to our user and developer 
> lists. However, I have not been able to send the release announcement to 
> the announce@ mailing list. So far, the Infrastructure team has not been 
> able to coach me through these problems: 
> https://issues.apache.org/jira/browse/INFRA-15311 
> .
> 
> I served as a release manager two years ago. At that time, I was able to 
> ssh to an Apache machine and use the command line mail tool in order to 
> post the release announcement to announce@. Unfortunately, committers 
> can't directly log onto Apache machines today.
> 
> I have failed to configure my Thunderbird mail tool to use the Apache 
> relay service, per Infrastructure's advice as described here: 
> https://reference.apache.org/committer/email#sendingemailfromyourapacheorgemailaddress
>  
> .
>  
> I don't know why. Maybe the Apache relay service is not a mail provider 
> supported by Thunderbird. Maybe Thunderbird requires incoming as well as 
> outgoing configuration parameters and the Apache instructions only state 
> the outgoing parameters.
> 
> I have also failed to configure sendmail on my Mac OSX machine. At this 
> point, I am very far afield, very far outside my skill set.
> 
> I would appreciate your advice on how to post a release announcement to 
> announce@.
> 
> Thanks,
> 

[jira] Commented: (DERBY-3301) Incorrect result from query with nested EXIST

[Craig Russell (JIRA)]

[ 
https://issues.apache.org/jira/browse/DERBY-3301?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=12581994#action_12581994
 ] 

Craig Russell commented on DERBY-3301:
--

Thanks for the update, Thomas. I could feel death by a thousand cuts coming...

 Incorrect result from query with nested EXIST
 -

 Key: DERBY-3301
 URL: https://issues.apache.org/jira/browse/DERBY-3301
 Project: Derby
  Issue Type: Bug
  Components: SQL
Affects Versions: 10.1.3.1, 10.2.1.6, 10.3.2.1
Reporter: Craig Russell
Assignee: Thomas Nielsen
 Fix For: 10.4.0.0

 Attachments: d3301-queryplan.log, derby-3301-1.diff, 
 derby-3301-1.stat, derby-3301-2.diff, derby-3301-3.diff, derby-3301-3b.diff, 
 derby-3301-4.diff, derby-3301-4b.diff, derby-3301-4b.stat, 
 derby-3301-4c.diff, derby-3301-5.diff, derby-3301-6.diff, derby-3301-7.diff, 
 derby-3301-8.diff, derby-3301-extra.sql, derby-3301-test-1.diff, 
 derby-3301-test-1.stat, derby-3301-test-2.diff, derby-3301-test-3.diff, 
 derby-3301-test-3.stat, derby-3301-test-master-2.diff, 
 derby-3301-test-master-3.diff, derby-3301-test-master-3.stat, 
 derby-3301-test-master.diff, derby-3301-test-master.stat, Derby-3301.html, 
 derby-3301.sql, releaseNote.html, releaseNote.html


 Derby returns unexpected results from a query with embedded EXIST clauses. 
 The query SQL is generated by the JPOX jdo implementation and is supposed to 
 return all of the PERSONS and PROJECTS where there is an entry in the join 
 table. This query works as expected when using MySQL.
 Here's the query:
 SELECT UNBOUND_E.PERSONID, UNBOUND_P.PROJID 
 FROM applicationidentity0.DEPARTMENTS THIS, 
  applicationidentity0.PERSONS UNBOUND_E, 
  applicationidentity0.PROJECTS UNBOUND_P 
 WHERE EXISTS ( 
 SELECT 1 FROM applicationidentity0.PERSONS THIS_EMPLOYEES_E 
 WHERE EXISTS ( 
 SELECT 1 FROM applicationidentity0.PROJECT_MEMBER 
 THIS_EMPLOYEES_E_PROJECTS_P 
 WHERE THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = 
 THIS_EMPLOYEES_E.PERSONID 
 AND THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = THIS_EMPLOYEES_E.PERSONID 
 AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
 AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
 AND UNBOUND_P.PROJID = THIS_EMPLOYEES_E_PROJECTS_P.PROJID 
 AND UNBOUND_E.PERSONID = THIS_EMPLOYEES_E.PERSONID) 
 );
 PERSONID   |PROJID 
 ---
 3  |1  
 5  |3  
 4  |3  
 2  |1  
 1  |1  
 5 rows selected
 I'm expecting 7 rows to be returned here, one row for each row in the join 
 table. 
 Here's the schema:
 CREATE TABLE departments (
 ID INTEGER NOT NULL,
 NAME VARCHAR(32) NOT NULL,
 EMP_OF_THE_MONTH INTEGER,
 COMPANYID INTEGER,
 DISCRIMINATOR VARCHAR(255),
 CONSTRAINT DEPTS_COMP_FK FOREIGN KEY (COMPANYID) REFERENCES companies,
 CONSTRAINT DEPTS_PK PRIMARY KEY (ID)
 );
 CREATE TABLE persons (
 PERSONID INTEGER NOT NULL,
 FIRSTNAME VARCHAR(32) NOT NULL,
 LASTNAME VARCHAR(32) NOT NULL,
 MIDDLENAME VARCHAR(32),
 BIRTHDATE TIMESTAMP NOT NULL,
 ADDRID INTEGER,
 STREET VARCHAR(64),
 CITY VARCHAR(64),
 STATE CHAR(2),
 ZIPCODE CHAR(5),
 COUNTRY VARCHAR(64),
 HIREDATE TIMESTAMP,
 WEEKLYHOURS REAL,
 DEPARTMENT INTEGER,
 FUNDINGDEPT INTEGER,
 MANAGER INTEGER,
 MENTOR INTEGER,
 HRADVISOR INTEGER,
 SALARY REAL,
 WAGE REAL,
 DISCRIMINATOR varchar(255) NOT NULL,
 CONSTRAINT PERS_DEPT_FK FOREIGN KEY (DEPARTMENT) REFERENCES departments,
 CONSTRAINT PERS_FUNDDEPT_FK FOREIGN KEY (FUNDINGDEPT) REFERENCES 
 departments,
 CONSTRAINT PERS_MANAGER_FK FOREIGN KEY (MANAGER) REFERENCES persons,
 CONSTRAINT PERS_MENTOR_FK FOREIGN KEY (MENTOR) REFERENCES persons,
 CONSTRAINT PERS_HRADVISOR_FK FOREIGN KEY (HRADVISOR) REFERENCES persons,
 CONSTRAINT EMPS_PK PRIMARY KEY (PERSONID)
 );
 CREATE TABLE projects (
 PROJID INTEGER NOT NULL,
 NAME VARCHAR(32) NOT NULL,
 BUDGET DECIMAL(11,2) NOT NULL,
 DISCRIMINATOR VARCHAR(255),
 CONSTRAINT PROJS_PK PRIMARY KEY (PROJID)
 );
 CREATE TABLE project_member (
 PROJID INTEGER REFERENCES projects NOT NULL,
 MEMBER INTEGER REFERENCES persons NOT NULL
 );
 ij connect 
 'jdbc:derby:/Users/clr/apache/jdo/trunk/tck2/target/database/derby/jdotckdb';
 ij set schema applicationidentity0;
 0 rows inserted/updated/deleted
 ij select * from persons;
 PERSONID   |FIRSTNAME   |LASTNAME
 |MIDDLENAME  |BIRTHDATE |ADDRID 
 |STREET  |CITY
 |STA|ZIPC|COUNTRY

[jira] Updated: (DERBY-3301) Incorrect result from query with nested EXIST

[Craig Russell (JIRA)]

 [ 
https://issues.apache.org/jira/browse/DERBY-3301?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Craig Russell updated DERBY-3301:
-

Attachment: Derby-3301.html

I've tried to summarize the issue with the attached release note.

 Incorrect result from query with nested EXIST
 -

 Key: DERBY-3301
 URL: https://issues.apache.org/jira/browse/DERBY-3301
 Project: Derby
  Issue Type: Bug
  Components: SQL
Affects Versions: 10.1.3.1, 10.2.1.6, 10.3.2.1
Reporter: Craig Russell
Assignee: Thomas Nielsen
 Fix For: 10.4.0.0

 Attachments: d3301-queryplan.log, derby-3301-1.diff, 
 derby-3301-1.stat, derby-3301-2.diff, derby-3301-3.diff, derby-3301-3b.diff, 
 derby-3301-4.diff, derby-3301-4b.diff, derby-3301-4b.stat, 
 derby-3301-4c.diff, derby-3301-5.diff, derby-3301-6.diff, derby-3301-7.diff, 
 derby-3301-8.diff, derby-3301-extra.sql, derby-3301-test-1.diff, 
 derby-3301-test-1.stat, derby-3301-test-2.diff, derby-3301-test-3.diff, 
 derby-3301-test-3.stat, derby-3301-test-master-2.diff, 
 derby-3301-test-master-3.diff, derby-3301-test-master-3.stat, 
 derby-3301-test-master.diff, derby-3301-test-master.stat, Derby-3301.html, 
 derby-3301.sql


 Derby returns unexpected results from a query with embedded EXIST clauses. 
 The query SQL is generated by the JPOX jdo implementation and is supposed to 
 return all of the PERSONS and PROJECTS where there is an entry in the join 
 table. This query works as expected when using MySQL.
 Here's the query:
 SELECT UNBOUND_E.PERSONID, UNBOUND_P.PROJID 
 FROM applicationidentity0.DEPARTMENTS THIS, 
  applicationidentity0.PERSONS UNBOUND_E, 
  applicationidentity0.PROJECTS UNBOUND_P 
 WHERE EXISTS ( 
 SELECT 1 FROM applicationidentity0.PERSONS THIS_EMPLOYEES_E 
 WHERE EXISTS ( 
 SELECT 1 FROM applicationidentity0.PROJECT_MEMBER 
 THIS_EMPLOYEES_E_PROJECTS_P 
 WHERE THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = 
 THIS_EMPLOYEES_E.PERSONID 
 AND THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = THIS_EMPLOYEES_E.PERSONID 
 AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
 AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
 AND UNBOUND_P.PROJID = THIS_EMPLOYEES_E_PROJECTS_P.PROJID 
 AND UNBOUND_E.PERSONID = THIS_EMPLOYEES_E.PERSONID) 
 );
 PERSONID   |PROJID 
 ---
 3  |1  
 5  |3  
 4  |3  
 2  |1  
 1  |1  
 5 rows selected
 I'm expecting 7 rows to be returned here, one row for each row in the join 
 table. 
 Here's the schema:
 CREATE TABLE departments (
 ID INTEGER NOT NULL,
 NAME VARCHAR(32) NOT NULL,
 EMP_OF_THE_MONTH INTEGER,
 COMPANYID INTEGER,
 DISCRIMINATOR VARCHAR(255),
 CONSTRAINT DEPTS_COMP_FK FOREIGN KEY (COMPANYID) REFERENCES companies,
 CONSTRAINT DEPTS_PK PRIMARY KEY (ID)
 );
 CREATE TABLE persons (
 PERSONID INTEGER NOT NULL,
 FIRSTNAME VARCHAR(32) NOT NULL,
 LASTNAME VARCHAR(32) NOT NULL,
 MIDDLENAME VARCHAR(32),
 BIRTHDATE TIMESTAMP NOT NULL,
 ADDRID INTEGER,
 STREET VARCHAR(64),
 CITY VARCHAR(64),
 STATE CHAR(2),
 ZIPCODE CHAR(5),
 COUNTRY VARCHAR(64),
 HIREDATE TIMESTAMP,
 WEEKLYHOURS REAL,
 DEPARTMENT INTEGER,
 FUNDINGDEPT INTEGER,
 MANAGER INTEGER,
 MENTOR INTEGER,
 HRADVISOR INTEGER,
 SALARY REAL,
 WAGE REAL,
 DISCRIMINATOR varchar(255) NOT NULL,
 CONSTRAINT PERS_DEPT_FK FOREIGN KEY (DEPARTMENT) REFERENCES departments,
 CONSTRAINT PERS_FUNDDEPT_FK FOREIGN KEY (FUNDINGDEPT) REFERENCES 
 departments,
 CONSTRAINT PERS_MANAGER_FK FOREIGN KEY (MANAGER) REFERENCES persons,
 CONSTRAINT PERS_MENTOR_FK FOREIGN KEY (MENTOR) REFERENCES persons,
 CONSTRAINT PERS_HRADVISOR_FK FOREIGN KEY (HRADVISOR) REFERENCES persons,
 CONSTRAINT EMPS_PK PRIMARY KEY (PERSONID)
 );
 CREATE TABLE projects (
 PROJID INTEGER NOT NULL,
 NAME VARCHAR(32) NOT NULL,
 BUDGET DECIMAL(11,2) NOT NULL,
 DISCRIMINATOR VARCHAR(255),
 CONSTRAINT PROJS_PK PRIMARY KEY (PROJID)
 );
 CREATE TABLE project_member (
 PROJID INTEGER REFERENCES projects NOT NULL,
 MEMBER INTEGER REFERENCES persons NOT NULL
 );
 ij connect 
 'jdbc:derby:/Users/clr/apache/jdo/trunk/tck2/target/database/derby/jdotckdb';
 ij set schema applicationidentity0;
 0 rows inserted/updated/deleted
 ij select * from persons;
 PERSONID   |FIRSTNAME   |LASTNAME
 |MIDDLENAME  |BIRTHDATE |ADDRID 
 |STREET  |CITY
 |STA|ZIPC|COUNTRY   
   |HIREDATE  
 |WEEKLYHOURS

[jira] Updated: (DERBY-3301) Incorrect result from query with nested EXIST

[Craig Russell (JIRA)]

 [ 
https://issues.apache.org/jira/browse/DERBY-3301?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Craig Russell updated DERBY-3301:
-

Attachment: releaseNote.html

I've changed the description to address the tense issue, and changed the name 
of the file.

 Incorrect result from query with nested EXIST
 -

 Key: DERBY-3301
 URL: https://issues.apache.org/jira/browse/DERBY-3301
 Project: Derby
  Issue Type: Bug
  Components: SQL
Affects Versions: 10.1.3.1, 10.2.1.6, 10.3.2.1
Reporter: Craig Russell
Assignee: Thomas Nielsen
 Fix For: 10.4.0.0

 Attachments: d3301-queryplan.log, derby-3301-1.diff, 
 derby-3301-1.stat, derby-3301-2.diff, derby-3301-3.diff, derby-3301-3b.diff, 
 derby-3301-4.diff, derby-3301-4b.diff, derby-3301-4b.stat, 
 derby-3301-4c.diff, derby-3301-5.diff, derby-3301-6.diff, derby-3301-7.diff, 
 derby-3301-8.diff, derby-3301-extra.sql, derby-3301-test-1.diff, 
 derby-3301-test-1.stat, derby-3301-test-2.diff, derby-3301-test-3.diff, 
 derby-3301-test-3.stat, derby-3301-test-master-2.diff, 
 derby-3301-test-master-3.diff, derby-3301-test-master-3.stat, 
 derby-3301-test-master.diff, derby-3301-test-master.stat, Derby-3301.html, 
 derby-3301.sql, releaseNote.html


 Derby returns unexpected results from a query with embedded EXIST clauses. 
 The query SQL is generated by the JPOX jdo implementation and is supposed to 
 return all of the PERSONS and PROJECTS where there is an entry in the join 
 table. This query works as expected when using MySQL.
 Here's the query:
 SELECT UNBOUND_E.PERSONID, UNBOUND_P.PROJID 
 FROM applicationidentity0.DEPARTMENTS THIS, 
  applicationidentity0.PERSONS UNBOUND_E, 
  applicationidentity0.PROJECTS UNBOUND_P 
 WHERE EXISTS ( 
 SELECT 1 FROM applicationidentity0.PERSONS THIS_EMPLOYEES_E 
 WHERE EXISTS ( 
 SELECT 1 FROM applicationidentity0.PROJECT_MEMBER 
 THIS_EMPLOYEES_E_PROJECTS_P 
 WHERE THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = 
 THIS_EMPLOYEES_E.PERSONID 
 AND THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = THIS_EMPLOYEES_E.PERSONID 
 AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
 AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
 AND UNBOUND_P.PROJID = THIS_EMPLOYEES_E_PROJECTS_P.PROJID 
 AND UNBOUND_E.PERSONID = THIS_EMPLOYEES_E.PERSONID) 
 );
 PERSONID   |PROJID 
 ---
 3  |1  
 5  |3  
 4  |3  
 2  |1  
 1  |1  
 5 rows selected
 I'm expecting 7 rows to be returned here, one row for each row in the join 
 table. 
 Here's the schema:
 CREATE TABLE departments (
 ID INTEGER NOT NULL,
 NAME VARCHAR(32) NOT NULL,
 EMP_OF_THE_MONTH INTEGER,
 COMPANYID INTEGER,
 DISCRIMINATOR VARCHAR(255),
 CONSTRAINT DEPTS_COMP_FK FOREIGN KEY (COMPANYID) REFERENCES companies,
 CONSTRAINT DEPTS_PK PRIMARY KEY (ID)
 );
 CREATE TABLE persons (
 PERSONID INTEGER NOT NULL,
 FIRSTNAME VARCHAR(32) NOT NULL,
 LASTNAME VARCHAR(32) NOT NULL,
 MIDDLENAME VARCHAR(32),
 BIRTHDATE TIMESTAMP NOT NULL,
 ADDRID INTEGER,
 STREET VARCHAR(64),
 CITY VARCHAR(64),
 STATE CHAR(2),
 ZIPCODE CHAR(5),
 COUNTRY VARCHAR(64),
 HIREDATE TIMESTAMP,
 WEEKLYHOURS REAL,
 DEPARTMENT INTEGER,
 FUNDINGDEPT INTEGER,
 MANAGER INTEGER,
 MENTOR INTEGER,
 HRADVISOR INTEGER,
 SALARY REAL,
 WAGE REAL,
 DISCRIMINATOR varchar(255) NOT NULL,
 CONSTRAINT PERS_DEPT_FK FOREIGN KEY (DEPARTMENT) REFERENCES departments,
 CONSTRAINT PERS_FUNDDEPT_FK FOREIGN KEY (FUNDINGDEPT) REFERENCES 
 departments,
 CONSTRAINT PERS_MANAGER_FK FOREIGN KEY (MANAGER) REFERENCES persons,
 CONSTRAINT PERS_MENTOR_FK FOREIGN KEY (MENTOR) REFERENCES persons,
 CONSTRAINT PERS_HRADVISOR_FK FOREIGN KEY (HRADVISOR) REFERENCES persons,
 CONSTRAINT EMPS_PK PRIMARY KEY (PERSONID)
 );
 CREATE TABLE projects (
 PROJID INTEGER NOT NULL,
 NAME VARCHAR(32) NOT NULL,
 BUDGET DECIMAL(11,2) NOT NULL,
 DISCRIMINATOR VARCHAR(255),
 CONSTRAINT PROJS_PK PRIMARY KEY (PROJID)
 );
 CREATE TABLE project_member (
 PROJID INTEGER REFERENCES projects NOT NULL,
 MEMBER INTEGER REFERENCES persons NOT NULL
 );
 ij connect 
 'jdbc:derby:/Users/clr/apache/jdo/trunk/tck2/target/database/derby/jdotckdb';
 ij set schema applicationidentity0;
 0 rows inserted/updated/deleted
 ij select * from persons;
 PERSONID   |FIRSTNAME   |LASTNAME
 |MIDDLENAME  |BIRTHDATE |ADDRID 
 |STREET  |CITY
 |STA|ZIPC|COUNTRY

[jira] Commented: (DERBY-3301) Incorrect result from query with nested EXIST

[Craig Russell (JIRA)]

[ 
https://issues.apache.org/jira/browse/DERBY-3301?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=12580155#action_12580155
 ] 

Craig Russell commented on DERBY-3301:
--

Seems like a release note might be appropriate. I could try to put together a 
description from the body of this JIRA if someone can give me a clue as to the 
format of a releaseNote.html attachment.

 Incorrect result from query with nested EXIST
 -

 Key: DERBY-3301
 URL: https://issues.apache.org/jira/browse/DERBY-3301
 Project: Derby
  Issue Type: Bug
  Components: SQL
Affects Versions: 10.1.3.1, 10.2.1.6, 10.3.2.1
Reporter: Craig Russell
Assignee: Thomas Nielsen
 Fix For: 10.4.0.0

 Attachments: d3301-queryplan.log, derby-3301-1.diff, 
 derby-3301-1.stat, derby-3301-2.diff, derby-3301-3.diff, derby-3301-3b.diff, 
 derby-3301-4.diff, derby-3301-4b.diff, derby-3301-4b.stat, 
 derby-3301-4c.diff, derby-3301-5.diff, derby-3301-6.diff, derby-3301-7.diff, 
 derby-3301-8.diff, derby-3301-extra.sql, derby-3301-test-1.diff, 
 derby-3301-test-1.stat, derby-3301-test-2.diff, derby-3301-test-3.diff, 
 derby-3301-test-3.stat, derby-3301-test-master-2.diff, 
 derby-3301-test-master-3.diff, derby-3301-test-master-3.stat, 
 derby-3301-test-master.diff, derby-3301-test-master.stat, derby-3301.sql


 Derby returns unexpected results from a query with embedded EXIST clauses. 
 The query SQL is generated by the JPOX jdo implementation and is supposed to 
 return all of the PERSONS and PROJECTS where there is an entry in the join 
 table. This query works as expected when using MySQL.
 Here's the query:
 SELECT UNBOUND_E.PERSONID, UNBOUND_P.PROJID 
 FROM applicationidentity0.DEPARTMENTS THIS, 
  applicationidentity0.PERSONS UNBOUND_E, 
  applicationidentity0.PROJECTS UNBOUND_P 
 WHERE EXISTS ( 
 SELECT 1 FROM applicationidentity0.PERSONS THIS_EMPLOYEES_E 
 WHERE EXISTS ( 
 SELECT 1 FROM applicationidentity0.PROJECT_MEMBER 
 THIS_EMPLOYEES_E_PROJECTS_P 
 WHERE THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = 
 THIS_EMPLOYEES_E.PERSONID 
 AND THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = THIS_EMPLOYEES_E.PERSONID 
 AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
 AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
 AND UNBOUND_P.PROJID = THIS_EMPLOYEES_E_PROJECTS_P.PROJID 
 AND UNBOUND_E.PERSONID = THIS_EMPLOYEES_E.PERSONID) 
 );
 PERSONID   |PROJID 
 ---
 3  |1  
 5  |3  
 4  |3  
 2  |1  
 1  |1  
 5 rows selected
 I'm expecting 7 rows to be returned here, one row for each row in the join 
 table. 
 Here's the schema:
 CREATE TABLE departments (
 ID INTEGER NOT NULL,
 NAME VARCHAR(32) NOT NULL,
 EMP_OF_THE_MONTH INTEGER,
 COMPANYID INTEGER,
 DISCRIMINATOR VARCHAR(255),
 CONSTRAINT DEPTS_COMP_FK FOREIGN KEY (COMPANYID) REFERENCES companies,
 CONSTRAINT DEPTS_PK PRIMARY KEY (ID)
 );
 CREATE TABLE persons (
 PERSONID INTEGER NOT NULL,
 FIRSTNAME VARCHAR(32) NOT NULL,
 LASTNAME VARCHAR(32) NOT NULL,
 MIDDLENAME VARCHAR(32),
 BIRTHDATE TIMESTAMP NOT NULL,
 ADDRID INTEGER,
 STREET VARCHAR(64),
 CITY VARCHAR(64),
 STATE CHAR(2),
 ZIPCODE CHAR(5),
 COUNTRY VARCHAR(64),
 HIREDATE TIMESTAMP,
 WEEKLYHOURS REAL,
 DEPARTMENT INTEGER,
 FUNDINGDEPT INTEGER,
 MANAGER INTEGER,
 MENTOR INTEGER,
 HRADVISOR INTEGER,
 SALARY REAL,
 WAGE REAL,
 DISCRIMINATOR varchar(255) NOT NULL,
 CONSTRAINT PERS_DEPT_FK FOREIGN KEY (DEPARTMENT) REFERENCES departments,
 CONSTRAINT PERS_FUNDDEPT_FK FOREIGN KEY (FUNDINGDEPT) REFERENCES 
 departments,
 CONSTRAINT PERS_MANAGER_FK FOREIGN KEY (MANAGER) REFERENCES persons,
 CONSTRAINT PERS_MENTOR_FK FOREIGN KEY (MENTOR) REFERENCES persons,
 CONSTRAINT PERS_HRADVISOR_FK FOREIGN KEY (HRADVISOR) REFERENCES persons,
 CONSTRAINT EMPS_PK PRIMARY KEY (PERSONID)
 );
 CREATE TABLE projects (
 PROJID INTEGER NOT NULL,
 NAME VARCHAR(32) NOT NULL,
 BUDGET DECIMAL(11,2) NOT NULL,
 DISCRIMINATOR VARCHAR(255),
 CONSTRAINT PROJS_PK PRIMARY KEY (PROJID)
 );
 CREATE TABLE project_member (
 PROJID INTEGER REFERENCES projects NOT NULL,
 MEMBER INTEGER REFERENCES persons NOT NULL
 );
 ij connect 
 'jdbc:derby:/Users/clr/apache/jdo/trunk/tck2/target/database/derby/jdotckdb';
 ij set schema applicationidentity0;
 0 rows inserted/updated/deleted
 ij select * from persons;
 PERSONID   |FIRSTNAME   |LASTNAME
 |MIDDLENAME  |BIRTHDATE |ADDRID 
 |STREET  |CITY

[jira] Commented: (DERBY-3301) Incorrect result from query with nested EXIST

[Craig Russell (JIRA)]

[ 
https://issues.apache.org/jira/browse/DERBY-3301?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=12569506#action_12569506
 ] 

Craig Russell commented on DERBY-3301:
--

I'm convinced that this issue is resolved. I think it should stay open until a 
release containing the fix is made. Is that correct?

 Incorrect result from query with nested EXIST
 -

 Key: DERBY-3301
 URL: https://issues.apache.org/jira/browse/DERBY-3301
 Project: Derby
  Issue Type: Bug
  Components: SQL
Affects Versions: 10.1.3.1, 10.2.1.6, 10.3.2.1
Reporter: Craig Russell
Assignee: Thomas Nielsen
 Fix For: 10.4.0.0

 Attachments: d3301-queryplan.log, derby-3301-1.diff, 
 derby-3301-1.stat, derby-3301-2.diff, derby-3301-3.diff, derby-3301-3b.diff, 
 derby-3301-4.diff, derby-3301-4b.diff, derby-3301-4b.stat, 
 derby-3301-4c.diff, derby-3301-5.diff, derby-3301-6.diff, derby-3301-7.diff, 
 derby-3301-8.diff, derby-3301-extra.sql, derby-3301-test-1.diff, 
 derby-3301-test-1.stat, derby-3301-test-2.diff, derby-3301-test-3.diff, 
 derby-3301-test-3.stat, derby-3301-test-master-2.diff, 
 derby-3301-test-master-3.diff, derby-3301-test-master-3.stat, 
 derby-3301-test-master.diff, derby-3301-test-master.stat, derby-3301.sql


 Derby returns unexpected results from a query with embedded EXIST clauses. 
 The query SQL is generated by the JPOX jdo implementation and is supposed to 
 return all of the PERSONS and PROJECTS where there is an entry in the join 
 table. This query works as expected when using MySQL.
 Here's the query:
 SELECT UNBOUND_E.PERSONID, UNBOUND_P.PROJID 
 FROM applicationidentity0.DEPARTMENTS THIS, 
  applicationidentity0.PERSONS UNBOUND_E, 
  applicationidentity0.PROJECTS UNBOUND_P 
 WHERE EXISTS ( 
 SELECT 1 FROM applicationidentity0.PERSONS THIS_EMPLOYEES_E 
 WHERE EXISTS ( 
 SELECT 1 FROM applicationidentity0.PROJECT_MEMBER 
 THIS_EMPLOYEES_E_PROJECTS_P 
 WHERE THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = 
 THIS_EMPLOYEES_E.PERSONID 
 AND THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = THIS_EMPLOYEES_E.PERSONID 
 AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
 AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
 AND UNBOUND_P.PROJID = THIS_EMPLOYEES_E_PROJECTS_P.PROJID 
 AND UNBOUND_E.PERSONID = THIS_EMPLOYEES_E.PERSONID) 
 );
 PERSONID   |PROJID 
 ---
 3  |1  
 5  |3  
 4  |3  
 2  |1  
 1  |1  
 5 rows selected
 I'm expecting 7 rows to be returned here, one row for each row in the join 
 table. 
 Here's the schema:
 CREATE TABLE departments (
 ID INTEGER NOT NULL,
 NAME VARCHAR(32) NOT NULL,
 EMP_OF_THE_MONTH INTEGER,
 COMPANYID INTEGER,
 DISCRIMINATOR VARCHAR(255),
 CONSTRAINT DEPTS_COMP_FK FOREIGN KEY (COMPANYID) REFERENCES companies,
 CONSTRAINT DEPTS_PK PRIMARY KEY (ID)
 );
 CREATE TABLE persons (
 PERSONID INTEGER NOT NULL,
 FIRSTNAME VARCHAR(32) NOT NULL,
 LASTNAME VARCHAR(32) NOT NULL,
 MIDDLENAME VARCHAR(32),
 BIRTHDATE TIMESTAMP NOT NULL,
 ADDRID INTEGER,
 STREET VARCHAR(64),
 CITY VARCHAR(64),
 STATE CHAR(2),
 ZIPCODE CHAR(5),
 COUNTRY VARCHAR(64),
 HIREDATE TIMESTAMP,
 WEEKLYHOURS REAL,
 DEPARTMENT INTEGER,
 FUNDINGDEPT INTEGER,
 MANAGER INTEGER,
 MENTOR INTEGER,
 HRADVISOR INTEGER,
 SALARY REAL,
 WAGE REAL,
 DISCRIMINATOR varchar(255) NOT NULL,
 CONSTRAINT PERS_DEPT_FK FOREIGN KEY (DEPARTMENT) REFERENCES departments,
 CONSTRAINT PERS_FUNDDEPT_FK FOREIGN KEY (FUNDINGDEPT) REFERENCES 
 departments,
 CONSTRAINT PERS_MANAGER_FK FOREIGN KEY (MANAGER) REFERENCES persons,
 CONSTRAINT PERS_MENTOR_FK FOREIGN KEY (MENTOR) REFERENCES persons,
 CONSTRAINT PERS_HRADVISOR_FK FOREIGN KEY (HRADVISOR) REFERENCES persons,
 CONSTRAINT EMPS_PK PRIMARY KEY (PERSONID)
 );
 CREATE TABLE projects (
 PROJID INTEGER NOT NULL,
 NAME VARCHAR(32) NOT NULL,
 BUDGET DECIMAL(11,2) NOT NULL,
 DISCRIMINATOR VARCHAR(255),
 CONSTRAINT PROJS_PK PRIMARY KEY (PROJID)
 );
 CREATE TABLE project_member (
 PROJID INTEGER REFERENCES projects NOT NULL,
 MEMBER INTEGER REFERENCES persons NOT NULL
 );
 ij connect 
 'jdbc:derby:/Users/clr/apache/jdo/trunk/tck2/target/database/derby/jdotckdb';
 ij set schema applicationidentity0;
 0 rows inserted/updated/deleted
 ij select * from persons;
 PERSONID   |FIRSTNAME   |LASTNAME
 |MIDDLENAME  |BIRTHDATE |ADDRID 
 |STREET  |CITY
 |STA|ZIPC|COUNTRY

[jira] Commented: (DERBY-3301) Incorrect result from query with nested EXIST

[Craig Russell (JIRA)]

[ 
https://issues.apache.org/jira/browse/DERBY-3301?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=12565805#action_12565805
 ] 

Craig Russell commented on DERBY-3301:
--

Hooray. Thanks everyone for your efforts on this.

Craig

 Incorrect result from query with nested EXIST
 -

 Key: DERBY-3301
 URL: https://issues.apache.org/jira/browse/DERBY-3301
 Project: Derby
  Issue Type: Bug
  Components: SQL
Affects Versions: 10.1.3.1, 10.2.1.6, 10.3.2.1
Reporter: Craig Russell
Assignee: Thomas Nielsen
 Attachments: d3301-queryplan.log, derby-3301-1.diff, 
 derby-3301-1.stat, derby-3301-2.diff, derby-3301-3.diff, derby-3301-3b.diff, 
 derby-3301-4.diff, derby-3301-4b.diff, derby-3301-4b.stat, 
 derby-3301-4c.diff, derby-3301-5.diff, derby-3301-6.diff, derby-3301-7.diff, 
 derby-3301-8.diff, derby-3301-extra.sql, derby-3301-test-1.diff, 
 derby-3301-test-1.stat, derby-3301-test-2.diff, derby-3301-test-3.diff, 
 derby-3301-test-3.stat, derby-3301-test-master-2.diff, 
 derby-3301-test-master.diff, derby-3301-test-master.stat, derby-3301.sql


 Derby returns unexpected results from a query with embedded EXIST clauses. 
 The query SQL is generated by the JPOX jdo implementation and is supposed to 
 return all of the PERSONS and PROJECTS where there is an entry in the join 
 table. This query works as expected when using MySQL.
 Here's the query:
 SELECT UNBOUND_E.PERSONID, UNBOUND_P.PROJID 
 FROM applicationidentity0.DEPARTMENTS THIS, 
  applicationidentity0.PERSONS UNBOUND_E, 
  applicationidentity0.PROJECTS UNBOUND_P 
 WHERE EXISTS ( 
 SELECT 1 FROM applicationidentity0.PERSONS THIS_EMPLOYEES_E 
 WHERE EXISTS ( 
 SELECT 1 FROM applicationidentity0.PROJECT_MEMBER 
 THIS_EMPLOYEES_E_PROJECTS_P 
 WHERE THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = 
 THIS_EMPLOYEES_E.PERSONID 
 AND THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = THIS_EMPLOYEES_E.PERSONID 
 AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
 AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
 AND UNBOUND_P.PROJID = THIS_EMPLOYEES_E_PROJECTS_P.PROJID 
 AND UNBOUND_E.PERSONID = THIS_EMPLOYEES_E.PERSONID) 
 );
 PERSONID   |PROJID 
 ---
 3  |1  
 5  |3  
 4  |3  
 2  |1  
 1  |1  
 5 rows selected
 I'm expecting 7 rows to be returned here, one row for each row in the join 
 table. 
 Here's the schema:
 CREATE TABLE departments (
 ID INTEGER NOT NULL,
 NAME VARCHAR(32) NOT NULL,
 EMP_OF_THE_MONTH INTEGER,
 COMPANYID INTEGER,
 DISCRIMINATOR VARCHAR(255),
 CONSTRAINT DEPTS_COMP_FK FOREIGN KEY (COMPANYID) REFERENCES companies,
 CONSTRAINT DEPTS_PK PRIMARY KEY (ID)
 );
 CREATE TABLE persons (
 PERSONID INTEGER NOT NULL,
 FIRSTNAME VARCHAR(32) NOT NULL,
 LASTNAME VARCHAR(32) NOT NULL,
 MIDDLENAME VARCHAR(32),
 BIRTHDATE TIMESTAMP NOT NULL,
 ADDRID INTEGER,
 STREET VARCHAR(64),
 CITY VARCHAR(64),
 STATE CHAR(2),
 ZIPCODE CHAR(5),
 COUNTRY VARCHAR(64),
 HIREDATE TIMESTAMP,
 WEEKLYHOURS REAL,
 DEPARTMENT INTEGER,
 FUNDINGDEPT INTEGER,
 MANAGER INTEGER,
 MENTOR INTEGER,
 HRADVISOR INTEGER,
 SALARY REAL,
 WAGE REAL,
 DISCRIMINATOR varchar(255) NOT NULL,
 CONSTRAINT PERS_DEPT_FK FOREIGN KEY (DEPARTMENT) REFERENCES departments,
 CONSTRAINT PERS_FUNDDEPT_FK FOREIGN KEY (FUNDINGDEPT) REFERENCES 
 departments,
 CONSTRAINT PERS_MANAGER_FK FOREIGN KEY (MANAGER) REFERENCES persons,
 CONSTRAINT PERS_MENTOR_FK FOREIGN KEY (MENTOR) REFERENCES persons,
 CONSTRAINT PERS_HRADVISOR_FK FOREIGN KEY (HRADVISOR) REFERENCES persons,
 CONSTRAINT EMPS_PK PRIMARY KEY (PERSONID)
 );
 CREATE TABLE projects (
 PROJID INTEGER NOT NULL,
 NAME VARCHAR(32) NOT NULL,
 BUDGET DECIMAL(11,2) NOT NULL,
 DISCRIMINATOR VARCHAR(255),
 CONSTRAINT PROJS_PK PRIMARY KEY (PROJID)
 );
 CREATE TABLE project_member (
 PROJID INTEGER REFERENCES projects NOT NULL,
 MEMBER INTEGER REFERENCES persons NOT NULL
 );
 ij connect 
 'jdbc:derby:/Users/clr/apache/jdo/trunk/tck2/target/database/derby/jdotckdb';
 ij set schema applicationidentity0;
 0 rows inserted/updated/deleted
 ij select * from persons;
 PERSONID   |FIRSTNAME   |LASTNAME
 |MIDDLENAME  |BIRTHDATE |ADDRID 
 |STREET  |CITY
 |STA|ZIPC|COUNTRY   
   |HIREDATE  
 |WEEKLYHOURS  |DEPARTMENT |FUNDINGDEPT|MANAGER|MENTOR |HRADVISOR  
 |SALARY   |WAGE |DISCRIMINATOR

[jira] Commented: (DERBY-3301) Incorrect result from query with nested EXIST

[Craig Russell (JIRA)]

[ 
https://issues.apache.org/jira/browse/DERBY-3301?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=12564256#action_12564256
 ] 

Craig Russell commented on DERBY-3301:
--

 and with that change (everything else the same), the query with 1 = 1 
 returns 7 rows, as it should. The other queries mentioned by Craig also 
 return the correct results (assuming the first one is supposed to return 2 
 rows, which I assume it is...?) 

Yes, it is supposed to return 2 rows. It's not a particularly interesting query 
since it works with or without the patch, but I included it because it also 
uses the EXISTS pattern.

And let me say again I appreciate all the effort you all are putting into this.

Craig

 Incorrect result from query with nested EXIST
 -

 Key: DERBY-3301
 URL: https://issues.apache.org/jira/browse/DERBY-3301
 Project: Derby
  Issue Type: Bug
  Components: SQL
Affects Versions: 10.1.3.1, 10.2.1.6, 10.3.2.1
Reporter: Craig Russell
Assignee: Thomas Nielsen
 Attachments: d3301-queryplan.log, derby-3301-1.diff, 
 derby-3301-1.stat, derby-3301-2.diff, derby-3301-3.diff, derby-3301-3b.diff, 
 derby-3301-4.diff, derby-3301-4b.diff, derby-3301-4b.stat, 
 derby-3301-4c.diff, derby-3301-5.diff, derby-3301-extra.sql, 
 derby-3301-test-1.diff, derby-3301-test-1.stat, derby-3301-test-2.diff, 
 derby-3301-test-3.diff, derby-3301-test-3.stat, derby-3301-test-master.diff, 
 derby-3301-test-master.stat, derby-3301.sql


 Derby returns unexpected results from a query with embedded EXIST clauses. 
 The query SQL is generated by the JPOX jdo implementation and is supposed to 
 return all of the PERSONS and PROJECTS where there is an entry in the join 
 table. This query works as expected when using MySQL.
 Here's the query:
 SELECT UNBOUND_E.PERSONID, UNBOUND_P.PROJID 
 FROM applicationidentity0.DEPARTMENTS THIS, 
  applicationidentity0.PERSONS UNBOUND_E, 
  applicationidentity0.PROJECTS UNBOUND_P 
 WHERE EXISTS ( 
 SELECT 1 FROM applicationidentity0.PERSONS THIS_EMPLOYEES_E 
 WHERE EXISTS ( 
 SELECT 1 FROM applicationidentity0.PROJECT_MEMBER 
 THIS_EMPLOYEES_E_PROJECTS_P 
 WHERE THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = 
 THIS_EMPLOYEES_E.PERSONID 
 AND THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = THIS_EMPLOYEES_E.PERSONID 
 AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
 AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
 AND UNBOUND_P.PROJID = THIS_EMPLOYEES_E_PROJECTS_P.PROJID 
 AND UNBOUND_E.PERSONID = THIS_EMPLOYEES_E.PERSONID) 
 );
 PERSONID   |PROJID 
 ---
 3  |1  
 5  |3  
 4  |3  
 2  |1  
 1  |1  
 5 rows selected
 I'm expecting 7 rows to be returned here, one row for each row in the join 
 table. 
 Here's the schema:
 CREATE TABLE departments (
 ID INTEGER NOT NULL,
 NAME VARCHAR(32) NOT NULL,
 EMP_OF_THE_MONTH INTEGER,
 COMPANYID INTEGER,
 DISCRIMINATOR VARCHAR(255),
 CONSTRAINT DEPTS_COMP_FK FOREIGN KEY (COMPANYID) REFERENCES companies,
 CONSTRAINT DEPTS_PK PRIMARY KEY (ID)
 );
 CREATE TABLE persons (
 PERSONID INTEGER NOT NULL,
 FIRSTNAME VARCHAR(32) NOT NULL,
 LASTNAME VARCHAR(32) NOT NULL,
 MIDDLENAME VARCHAR(32),
 BIRTHDATE TIMESTAMP NOT NULL,
 ADDRID INTEGER,
 STREET VARCHAR(64),
 CITY VARCHAR(64),
 STATE CHAR(2),
 ZIPCODE CHAR(5),
 COUNTRY VARCHAR(64),
 HIREDATE TIMESTAMP,
 WEEKLYHOURS REAL,
 DEPARTMENT INTEGER,
 FUNDINGDEPT INTEGER,
 MANAGER INTEGER,
 MENTOR INTEGER,
 HRADVISOR INTEGER,
 SALARY REAL,
 WAGE REAL,
 DISCRIMINATOR varchar(255) NOT NULL,
 CONSTRAINT PERS_DEPT_FK FOREIGN KEY (DEPARTMENT) REFERENCES departments,
 CONSTRAINT PERS_FUNDDEPT_FK FOREIGN KEY (FUNDINGDEPT) REFERENCES 
 departments,
 CONSTRAINT PERS_MANAGER_FK FOREIGN KEY (MANAGER) REFERENCES persons,
 CONSTRAINT PERS_MENTOR_FK FOREIGN KEY (MENTOR) REFERENCES persons,
 CONSTRAINT PERS_HRADVISOR_FK FOREIGN KEY (HRADVISOR) REFERENCES persons,
 CONSTRAINT EMPS_PK PRIMARY KEY (PERSONID)
 );
 CREATE TABLE projects (
 PROJID INTEGER NOT NULL,
 NAME VARCHAR(32) NOT NULL,
 BUDGET DECIMAL(11,2) NOT NULL,
 DISCRIMINATOR VARCHAR(255),
 CONSTRAINT PROJS_PK PRIMARY KEY (PROJID)
 );
 CREATE TABLE project_member (
 PROJID INTEGER REFERENCES projects NOT NULL,
 MEMBER INTEGER REFERENCES persons NOT NULL
 );
 ij connect 
 'jdbc:derby:/Users/clr/apache/jdo/trunk/tck2/target/database/derby/jdotckdb';
 ij set schema applicationidentity0;
 0 rows inserted/updated/deleted
 ij select * from persons;
 PERSONID   |FIRSTNAME   |LASTNAME
 |MIDDLENAME  |BIRTHDATE

[jira] Commented: (DERBY-3301) Incorrect result from query with nested EXIST

[Craig Russell (JIRA)]

[ 
https://issues.apache.org/jira/browse/DERBY-3301?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=12564312#action_12564312
 ] 

Craig Russell commented on DERBY-3301:
--

I've applied the patch derby-3301-5.diff and rebuilt the derby and derbytools 
jars.

The JDO TCK tests that fail on the released jars now pass with the patch 
applied.

Thanks so much for getting this bug resolved to this point. This is no longer a 
blocker for me, assuming that the changes can be committed to the code line and 
a patch release made in due course.

Craig



 Incorrect result from query with nested EXIST
 -

 Key: DERBY-3301
 URL: https://issues.apache.org/jira/browse/DERBY-3301
 Project: Derby
  Issue Type: Bug
  Components: SQL
Affects Versions: 10.1.3.1, 10.2.1.6, 10.3.2.1
Reporter: Craig Russell
Assignee: Thomas Nielsen
 Attachments: d3301-queryplan.log, derby-3301-1.diff, 
 derby-3301-1.stat, derby-3301-2.diff, derby-3301-3.diff, derby-3301-3b.diff, 
 derby-3301-4.diff, derby-3301-4b.diff, derby-3301-4b.stat, 
 derby-3301-4c.diff, derby-3301-5.diff, derby-3301-extra.sql, 
 derby-3301-test-1.diff, derby-3301-test-1.stat, derby-3301-test-2.diff, 
 derby-3301-test-3.diff, derby-3301-test-3.stat, derby-3301-test-master.diff, 
 derby-3301-test-master.stat, derby-3301.sql


 Derby returns unexpected results from a query with embedded EXIST clauses. 
 The query SQL is generated by the JPOX jdo implementation and is supposed to 
 return all of the PERSONS and PROJECTS where there is an entry in the join 
 table. This query works as expected when using MySQL.
 Here's the query:
 SELECT UNBOUND_E.PERSONID, UNBOUND_P.PROJID 
 FROM applicationidentity0.DEPARTMENTS THIS, 
  applicationidentity0.PERSONS UNBOUND_E, 
  applicationidentity0.PROJECTS UNBOUND_P 
 WHERE EXISTS ( 
 SELECT 1 FROM applicationidentity0.PERSONS THIS_EMPLOYEES_E 
 WHERE EXISTS ( 
 SELECT 1 FROM applicationidentity0.PROJECT_MEMBER 
 THIS_EMPLOYEES_E_PROJECTS_P 
 WHERE THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = 
 THIS_EMPLOYEES_E.PERSONID 
 AND THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = THIS_EMPLOYEES_E.PERSONID 
 AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
 AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
 AND UNBOUND_P.PROJID = THIS_EMPLOYEES_E_PROJECTS_P.PROJID 
 AND UNBOUND_E.PERSONID = THIS_EMPLOYEES_E.PERSONID) 
 );
 PERSONID   |PROJID 
 ---
 3  |1  
 5  |3  
 4  |3  
 2  |1  
 1  |1  
 5 rows selected
 I'm expecting 7 rows to be returned here, one row for each row in the join 
 table. 
 Here's the schema:
 CREATE TABLE departments (
 ID INTEGER NOT NULL,
 NAME VARCHAR(32) NOT NULL,
 EMP_OF_THE_MONTH INTEGER,
 COMPANYID INTEGER,
 DISCRIMINATOR VARCHAR(255),
 CONSTRAINT DEPTS_COMP_FK FOREIGN KEY (COMPANYID) REFERENCES companies,
 CONSTRAINT DEPTS_PK PRIMARY KEY (ID)
 );
 CREATE TABLE persons (
 PERSONID INTEGER NOT NULL,
 FIRSTNAME VARCHAR(32) NOT NULL,
 LASTNAME VARCHAR(32) NOT NULL,
 MIDDLENAME VARCHAR(32),
 BIRTHDATE TIMESTAMP NOT NULL,
 ADDRID INTEGER,
 STREET VARCHAR(64),
 CITY VARCHAR(64),
 STATE CHAR(2),
 ZIPCODE CHAR(5),
 COUNTRY VARCHAR(64),
 HIREDATE TIMESTAMP,
 WEEKLYHOURS REAL,
 DEPARTMENT INTEGER,
 FUNDINGDEPT INTEGER,
 MANAGER INTEGER,
 MENTOR INTEGER,
 HRADVISOR INTEGER,
 SALARY REAL,
 WAGE REAL,
 DISCRIMINATOR varchar(255) NOT NULL,
 CONSTRAINT PERS_DEPT_FK FOREIGN KEY (DEPARTMENT) REFERENCES departments,
 CONSTRAINT PERS_FUNDDEPT_FK FOREIGN KEY (FUNDINGDEPT) REFERENCES 
 departments,
 CONSTRAINT PERS_MANAGER_FK FOREIGN KEY (MANAGER) REFERENCES persons,
 CONSTRAINT PERS_MENTOR_FK FOREIGN KEY (MENTOR) REFERENCES persons,
 CONSTRAINT PERS_HRADVISOR_FK FOREIGN KEY (HRADVISOR) REFERENCES persons,
 CONSTRAINT EMPS_PK PRIMARY KEY (PERSONID)
 );
 CREATE TABLE projects (
 PROJID INTEGER NOT NULL,
 NAME VARCHAR(32) NOT NULL,
 BUDGET DECIMAL(11,2) NOT NULL,
 DISCRIMINATOR VARCHAR(255),
 CONSTRAINT PROJS_PK PRIMARY KEY (PROJID)
 );
 CREATE TABLE project_member (
 PROJID INTEGER REFERENCES projects NOT NULL,
 MEMBER INTEGER REFERENCES persons NOT NULL
 );
 ij connect 
 'jdbc:derby:/Users/clr/apache/jdo/trunk/tck2/target/database/derby/jdotckdb';
 ij set schema applicationidentity0;
 0 rows inserted/updated/deleted
 ij select * from persons;
 PERSONID   |FIRSTNAME   |LASTNAME
 |MIDDLENAME  |BIRTHDATE |ADDRID 
 |STREET  |CITY

[jira] Commented: (DERBY-3301) Incorrect result from query with nested EXIST

[Craig Russell (JIRA)]

[ 
https://issues.apache.org/jira/browse/DERBY-3301?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=12563575#action_12563575
 ] 

Craig Russell commented on DERBY-3301:
--

I'd like to summarize my understanding of the patch.

If you have an EXISTS nested inside an EXISTS, the optimized inversion of the 
query execution plan does not occur. Instead, the natural execution plan is 
performed. That is, the outer tables are iterated and for each candidate tuple 
of the joined tables, the EXISTS is performed. 

For each EXISTS check, the scans are reinitialized and there might not be any 
clues as to exactly where in the outer scan the EXISTS is being invoked. This 
might yield sub-optimal performance compared to an optimized inverted scan.

Also, I'm trying to get some more test cases by putting additional criteria 
into the EXISTS and running the query in debug mode and filtering the output. 
It's slower than I expected but I'll continue if it will help.

 Incorrect result from query with nested EXIST
 -

 Key: DERBY-3301
 URL: https://issues.apache.org/jira/browse/DERBY-3301
 Project: Derby
  Issue Type: Bug
  Components: SQL
Affects Versions: 10.1.3.1, 10.2.1.6, 10.3.2.1
Reporter: Craig Russell
Assignee: Thomas Nielsen
 Attachments: d3301-queryplan.log, derby-3301-1.diff, 
 derby-3301-1.stat, derby-3301-2.diff, derby-3301-3.diff, derby-3301-3b.diff, 
 derby-3301-4.diff, derby-3301-4b.diff, derby-3301-4b.stat, 
 derby-3301-4c.diff, derby-3301-test-1.diff, derby-3301-test-1.stat, 
 derby-3301-test-2.diff, derby-3301-test-3.diff, derby-3301-test-3.stat, 
 derby-3301.sql


 Derby returns unexpected results from a query with embedded EXIST clauses. 
 The query SQL is generated by the JPOX jdo implementation and is supposed to 
 return all of the PERSONS and PROJECTS where there is an entry in the join 
 table. This query works as expected when using MySQL.
 Here's the query:
 SELECT UNBOUND_E.PERSONID, UNBOUND_P.PROJID 
 FROM applicationidentity0.DEPARTMENTS THIS, 
  applicationidentity0.PERSONS UNBOUND_E, 
  applicationidentity0.PROJECTS UNBOUND_P 
 WHERE EXISTS ( 
 SELECT 1 FROM applicationidentity0.PERSONS THIS_EMPLOYEES_E 
 WHERE EXISTS ( 
 SELECT 1 FROM applicationidentity0.PROJECT_MEMBER 
 THIS_EMPLOYEES_E_PROJECTS_P 
 WHERE THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = 
 THIS_EMPLOYEES_E.PERSONID 
 AND THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = THIS_EMPLOYEES_E.PERSONID 
 AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
 AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
 AND UNBOUND_P.PROJID = THIS_EMPLOYEES_E_PROJECTS_P.PROJID 
 AND UNBOUND_E.PERSONID = THIS_EMPLOYEES_E.PERSONID) 
 );
 PERSONID   |PROJID 
 ---
 3  |1  
 5  |3  
 4  |3  
 2  |1  
 1  |1  
 5 rows selected
 I'm expecting 7 rows to be returned here, one row for each row in the join 
 table. 
 Here's the schema:
 CREATE TABLE departments (
 ID INTEGER NOT NULL,
 NAME VARCHAR(32) NOT NULL,
 EMP_OF_THE_MONTH INTEGER,
 COMPANYID INTEGER,
 DISCRIMINATOR VARCHAR(255),
 CONSTRAINT DEPTS_COMP_FK FOREIGN KEY (COMPANYID) REFERENCES companies,
 CONSTRAINT DEPTS_PK PRIMARY KEY (ID)
 );
 CREATE TABLE persons (
 PERSONID INTEGER NOT NULL,
 FIRSTNAME VARCHAR(32) NOT NULL,
 LASTNAME VARCHAR(32) NOT NULL,
 MIDDLENAME VARCHAR(32),
 BIRTHDATE TIMESTAMP NOT NULL,
 ADDRID INTEGER,
 STREET VARCHAR(64),
 CITY VARCHAR(64),
 STATE CHAR(2),
 ZIPCODE CHAR(5),
 COUNTRY VARCHAR(64),
 HIREDATE TIMESTAMP,
 WEEKLYHOURS REAL,
 DEPARTMENT INTEGER,
 FUNDINGDEPT INTEGER,
 MANAGER INTEGER,
 MENTOR INTEGER,
 HRADVISOR INTEGER,
 SALARY REAL,
 WAGE REAL,
 DISCRIMINATOR varchar(255) NOT NULL,
 CONSTRAINT PERS_DEPT_FK FOREIGN KEY (DEPARTMENT) REFERENCES departments,
 CONSTRAINT PERS_FUNDDEPT_FK FOREIGN KEY (FUNDINGDEPT) REFERENCES 
 departments,
 CONSTRAINT PERS_MANAGER_FK FOREIGN KEY (MANAGER) REFERENCES persons,
 CONSTRAINT PERS_MENTOR_FK FOREIGN KEY (MENTOR) REFERENCES persons,
 CONSTRAINT PERS_HRADVISOR_FK FOREIGN KEY (HRADVISOR) REFERENCES persons,
 CONSTRAINT EMPS_PK PRIMARY KEY (PERSONID)
 );
 CREATE TABLE projects (
 PROJID INTEGER NOT NULL,
 NAME VARCHAR(32) NOT NULL,
 BUDGET DECIMAL(11,2) NOT NULL,
 DISCRIMINATOR VARCHAR(255),
 CONSTRAINT PROJS_PK PRIMARY KEY (PROJID)
 );
 CREATE TABLE project_member (
 PROJID INTEGER REFERENCES projects NOT NULL,
 MEMBER INTEGER REFERENCES persons NOT NULL
 );
 ij connect 
 'jdbc:derby:/Users/clr/apache/jdo/trunk/tck2/target/database/derby/jdotckdb';
 ij set schema applicationidentity0;
 0 rows inserted/updated

[jira] Commented: (DERBY-3301) Incorrect result from query with nested EXIST

[Craig Russell (JIRA)]

[ 
https://issues.apache.org/jira/browse/DERBY-3301?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=12563660#action_12563660
 ] 

Craig Russell commented on DERBY-3301:
--

Here are more queries, just slightly modified from the original queries. I've 
done little editing of the queries to avoid introducing copy/paste errors. 

This one works:
SELECT UNBOUND_E.PERSONID FROM applicationidentity0.DEPARTMENTS THIS , 
applicationidentity0.PERSONS UNBOUND_E 
  WHERE EXISTS (
SELECT 1 FROM applicationidentity0.PERSONS THIS_EMPLOYEES_E 
  WHERE THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID AND UNBOUND_E.PERSONID = 
THIS_EMPLOYEES_E.PERSONID AND THIS.ID = 2)

This fails, returns 5 rows where 7 are expected. The difference between this 
and the original query is that THIS.ID is also returned in the outer select:
SELECT THIS.ID,UNBOUND_E.PERSONID,UNBOUND_P.PROJID FROM 
applicationidentity0.DEPARTMENTS THIS , applicationidentity0.PERSONS UNBOUND_E 
, applicationidentity0.PROJECTS UNBOUND_P 
  WHERE EXISTS (
SELECT 1 FROM applicationidentity0.PERSONS THIS_EMPLOYEES_E 
  WHERE EXISTS (
SELECT 1 FROM applicationidentity0.PROJECT_MEMBER 
THIS_EMPLOYEES_E_PROJECTS_P 
 WHERE THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = THIS_EMPLOYEES_E.PERSONID 
   AND UNBOUND_P.PROJID = THIS_EMPLOYEES_E_PROJECTS_P.PROJID 
   AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
   AND UNBOUND_E.PERSONID = THIS_EMPLOYEES_E.PERSONID
))

This fails, returns 3 rows where 5 are expected. 
SELECT UNBOUND_E.PERSONID,UNBOUND_P.PROJID FROM 
applicationidentity0.DEPARTMENTS THIS , applicationidentity0.PERSONS UNBOUND_E 
, applicationidentity0.PROJECTS UNBOUND_P 
  WHERE EXISTS (
SELECT 1 FROM applicationidentity0.PERSONS THIS_EMPLOYEES_E 
  WHERE EXISTS (
SELECT 1 FROM applicationidentity0.PROJECT_MEMBER 
THIS_EMPLOYEES_E_PROJECTS_P 
  WHERE THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = 
THIS_EMPLOYEES_E.PERSONID 
  AND UNBOUND_P.PROJID = THIS_EMPLOYEES_E_PROJECTS_P.PROJID 
  AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
  AND UNBOUND_E.PERSONID = THIS_EMPLOYEES_E.PERSONID 
  AND THIS.ID = 1))

This fails, returning 5 rows where 7 are expected.
SELECT UNBOUND_E.PERSONID,UNBOUND_P.PROJID FROM 
applicationidentity0.DEPARTMENTS THIS , applicationidentity0.PERSONS UNBOUND_E 
, applicationidentity0.PROJECTS UNBOUND_P 
  WHERE EXISTS (
SELECT 1 FROM applicationidentity0.PERSONS THIS_EMPLOYEES_E 
  WHERE EXISTS (
SELECT 1 FROM applicationidentity0.PROJECT_MEMBER 
THIS_EMPLOYEES_E_PROJECTS_P 
  WHERE THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = 
THIS_EMPLOYEES_E.PERSONID 
  AND UNBOUND_P.PROJID = THIS_EMPLOYEES_E_PROJECTS_P.PROJID 
  AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
  AND UNBOUND_E.PERSONID = THIS_EMPLOYEES_E.PERSONID 
  AND THIS.COMPANYID = 1))



 Incorrect result from query with nested EXIST
 -

 Key: DERBY-3301
 URL: https://issues.apache.org/jira/browse/DERBY-3301
 Project: Derby
  Issue Type: Bug
  Components: SQL
Affects Versions: 10.1.3.1, 10.2.1.6, 10.3.2.1
Reporter: Craig Russell
Assignee: Thomas Nielsen
 Attachments: d3301-queryplan.log, derby-3301-1.diff, 
 derby-3301-1.stat, derby-3301-2.diff, derby-3301-3.diff, derby-3301-3b.diff, 
 derby-3301-4.diff, derby-3301-4b.diff, derby-3301-4b.stat, 
 derby-3301-4c.diff, derby-3301-test-1.diff, derby-3301-test-1.stat, 
 derby-3301-test-2.diff, derby-3301-test-3.diff, derby-3301-test-3.stat, 
 derby-3301.sql


 Derby returns unexpected results from a query with embedded EXIST clauses. 
 The query SQL is generated by the JPOX jdo implementation and is supposed to 
 return all of the PERSONS and PROJECTS where there is an entry in the join 
 table. This query works as expected when using MySQL.
 Here's the query:
 SELECT UNBOUND_E.PERSONID, UNBOUND_P.PROJID 
 FROM applicationidentity0.DEPARTMENTS THIS, 
  applicationidentity0.PERSONS UNBOUND_E, 
  applicationidentity0.PROJECTS UNBOUND_P 
 WHERE EXISTS ( 
 SELECT 1 FROM applicationidentity0.PERSONS THIS_EMPLOYEES_E 
 WHERE EXISTS ( 
 SELECT 1 FROM applicationidentity0.PROJECT_MEMBER 
 THIS_EMPLOYEES_E_PROJECTS_P 
 WHERE THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = 
 THIS_EMPLOYEES_E.PERSONID 
 AND THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = THIS_EMPLOYEES_E.PERSONID 
 AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
 AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
 AND UNBOUND_P.PROJID = THIS_EMPLOYEES_E_PROJECTS_P.PROJID 
 AND UNBOUND_E.PERSONID = THIS_EMPLOYEES_E.PERSONID) 
 );
 PERSONID   |PROJID 
 ---
 3  |1  
 5  |3  
 4  |3  
 2  |1  
 1  |1  
 5 rows

[jira] Commented: (DERBY-3301) Incorrect result from query with nested EXIST

[Craig Russell (JIRA)]

[ 
https://issues.apache.org/jira/browse/DERBY-3301?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=12561054#action_12561054
 ] 

Craig Russell commented on DERBY-3301:
--

Bryan This slight variation of the query seems to return the correct 7 rows. 

So the intermediate flattening of the query is probably causing the problem. 
Note that for more complex mappings from JDOQL to SQL, there will be even more 
intermediate EXISTS expressions.

I also found 
http://db.apache.org/derby/docs/10.3/tuning/ctuntransform25868.html that goes 
into some detail of the EXISTS join flattening.

 Incorrect result from query with nested EXIST
 -

 Key: DERBY-3301
 URL: https://issues.apache.org/jira/browse/DERBY-3301
 Project: Derby
  Issue Type: Bug
  Components: SQL
Affects Versions: 10.1.3.1, 10.2.1.6, 10.3.2.1
Reporter: Craig Russell
 Attachments: d3301-queryplan.log, derby-3301.sql


 Derby returns unexpected results from a query with embedded EXIST clauses. 
 The query SQL is generated by the JPOX jdo implementation and is supposed to 
 return all of the PERSONS and PROJECTS where there is an entry in the join 
 table. This query works as expected when using MySQL.
 Here's the query:
 SELECT UNBOUND_E.PERSONID, UNBOUND_P.PROJID 
 FROM applicationidentity0.DEPARTMENTS THIS, 
  applicationidentity0.PERSONS UNBOUND_E, 
  applicationidentity0.PROJECTS UNBOUND_P 
 WHERE EXISTS ( 
 SELECT 1 FROM applicationidentity0.PERSONS THIS_EMPLOYEES_E 
 WHERE EXISTS ( 
 SELECT 1 FROM applicationidentity0.PROJECT_MEMBER 
 THIS_EMPLOYEES_E_PROJECTS_P 
 WHERE THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = 
 THIS_EMPLOYEES_E.PERSONID 
 AND THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = THIS_EMPLOYEES_E.PERSONID 
 AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
 AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
 AND UNBOUND_P.PROJID = THIS_EMPLOYEES_E_PROJECTS_P.PROJID 
 AND UNBOUND_E.PERSONID = THIS_EMPLOYEES_E.PERSONID) 
 );
 PERSONID   |PROJID 
 ---
 3  |1  
 5  |3  
 4  |3  
 2  |1  
 1  |1  
 5 rows selected
 I'm expecting 7 rows to be returned here, one row for each row in the join 
 table. 
 Here's the schema:
 CREATE TABLE departments (
 ID INTEGER NOT NULL,
 NAME VARCHAR(32) NOT NULL,
 EMP_OF_THE_MONTH INTEGER,
 COMPANYID INTEGER,
 DISCRIMINATOR VARCHAR(255),
 CONSTRAINT DEPTS_COMP_FK FOREIGN KEY (COMPANYID) REFERENCES companies,
 CONSTRAINT DEPTS_PK PRIMARY KEY (ID)
 );
 CREATE TABLE persons (
 PERSONID INTEGER NOT NULL,
 FIRSTNAME VARCHAR(32) NOT NULL,
 LASTNAME VARCHAR(32) NOT NULL,
 MIDDLENAME VARCHAR(32),
 BIRTHDATE TIMESTAMP NOT NULL,
 ADDRID INTEGER,
 STREET VARCHAR(64),
 CITY VARCHAR(64),
 STATE CHAR(2),
 ZIPCODE CHAR(5),
 COUNTRY VARCHAR(64),
 HIREDATE TIMESTAMP,
 WEEKLYHOURS REAL,
 DEPARTMENT INTEGER,
 FUNDINGDEPT INTEGER,
 MANAGER INTEGER,
 MENTOR INTEGER,
 HRADVISOR INTEGER,
 SALARY REAL,
 WAGE REAL,
 DISCRIMINATOR varchar(255) NOT NULL,
 CONSTRAINT PERS_DEPT_FK FOREIGN KEY (DEPARTMENT) REFERENCES departments,
 CONSTRAINT PERS_FUNDDEPT_FK FOREIGN KEY (FUNDINGDEPT) REFERENCES 
 departments,
 CONSTRAINT PERS_MANAGER_FK FOREIGN KEY (MANAGER) REFERENCES persons,
 CONSTRAINT PERS_MENTOR_FK FOREIGN KEY (MENTOR) REFERENCES persons,
 CONSTRAINT PERS_HRADVISOR_FK FOREIGN KEY (HRADVISOR) REFERENCES persons,
 CONSTRAINT EMPS_PK PRIMARY KEY (PERSONID)
 );
 CREATE TABLE projects (
 PROJID INTEGER NOT NULL,
 NAME VARCHAR(32) NOT NULL,
 BUDGET DECIMAL(11,2) NOT NULL,
 DISCRIMINATOR VARCHAR(255),
 CONSTRAINT PROJS_PK PRIMARY KEY (PROJID)
 );
 CREATE TABLE project_member (
 PROJID INTEGER REFERENCES projects NOT NULL,
 MEMBER INTEGER REFERENCES persons NOT NULL
 );
 ij connect 
 'jdbc:derby:/Users/clr/apache/jdo/trunk/tck2/target/database/derby/jdotckdb';
 ij set schema applicationidentity0;
 0 rows inserted/updated/deleted
 ij select * from persons;
 PERSONID   |FIRSTNAME   |LASTNAME
 |MIDDLENAME  |BIRTHDATE |ADDRID 
 |STREET  |CITY
 |STA|ZIPC|COUNTRY   
   |HIREDATE  
 |WEEKLYHOURS  |DEPARTMENT |FUNDINGDEPT|MANAGER|MENTOR |HRADVISOR  
 |SALARY   |WAGE |DISCRIMINATOR

[jira] Commented: (DERBY-3301) Incorrect result from query with nested EXIST

[Craig Russell (JIRA)]

[ 
https://issues.apache.org/jira/browse/DERBY-3301?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=12561141#action_12561141
 ] 

Craig Russell commented on DERBY-3301:
--

Great! I'm looking forward to trying the patch.

 Incorrect result from query with nested EXIST
 -

 Key: DERBY-3301
 URL: https://issues.apache.org/jira/browse/DERBY-3301
 Project: Derby
  Issue Type: Bug
  Components: SQL
Affects Versions: 10.1.3.1, 10.2.1.6, 10.3.2.1
Reporter: Craig Russell
 Attachments: d3301-queryplan.log, derby-3301.sql


 Derby returns unexpected results from a query with embedded EXIST clauses. 
 The query SQL is generated by the JPOX jdo implementation and is supposed to 
 return all of the PERSONS and PROJECTS where there is an entry in the join 
 table. This query works as expected when using MySQL.
 Here's the query:
 SELECT UNBOUND_E.PERSONID, UNBOUND_P.PROJID 
 FROM applicationidentity0.DEPARTMENTS THIS, 
  applicationidentity0.PERSONS UNBOUND_E, 
  applicationidentity0.PROJECTS UNBOUND_P 
 WHERE EXISTS ( 
 SELECT 1 FROM applicationidentity0.PERSONS THIS_EMPLOYEES_E 
 WHERE EXISTS ( 
 SELECT 1 FROM applicationidentity0.PROJECT_MEMBER 
 THIS_EMPLOYEES_E_PROJECTS_P 
 WHERE THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = 
 THIS_EMPLOYEES_E.PERSONID 
 AND THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = THIS_EMPLOYEES_E.PERSONID 
 AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
 AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
 AND UNBOUND_P.PROJID = THIS_EMPLOYEES_E_PROJECTS_P.PROJID 
 AND UNBOUND_E.PERSONID = THIS_EMPLOYEES_E.PERSONID) 
 );
 PERSONID   |PROJID 
 ---
 3  |1  
 5  |3  
 4  |3  
 2  |1  
 1  |1  
 5 rows selected
 I'm expecting 7 rows to be returned here, one row for each row in the join 
 table. 
 Here's the schema:
 CREATE TABLE departments (
 ID INTEGER NOT NULL,
 NAME VARCHAR(32) NOT NULL,
 EMP_OF_THE_MONTH INTEGER,
 COMPANYID INTEGER,
 DISCRIMINATOR VARCHAR(255),
 CONSTRAINT DEPTS_COMP_FK FOREIGN KEY (COMPANYID) REFERENCES companies,
 CONSTRAINT DEPTS_PK PRIMARY KEY (ID)
 );
 CREATE TABLE persons (
 PERSONID INTEGER NOT NULL,
 FIRSTNAME VARCHAR(32) NOT NULL,
 LASTNAME VARCHAR(32) NOT NULL,
 MIDDLENAME VARCHAR(32),
 BIRTHDATE TIMESTAMP NOT NULL,
 ADDRID INTEGER,
 STREET VARCHAR(64),
 CITY VARCHAR(64),
 STATE CHAR(2),
 ZIPCODE CHAR(5),
 COUNTRY VARCHAR(64),
 HIREDATE TIMESTAMP,
 WEEKLYHOURS REAL,
 DEPARTMENT INTEGER,
 FUNDINGDEPT INTEGER,
 MANAGER INTEGER,
 MENTOR INTEGER,
 HRADVISOR INTEGER,
 SALARY REAL,
 WAGE REAL,
 DISCRIMINATOR varchar(255) NOT NULL,
 CONSTRAINT PERS_DEPT_FK FOREIGN KEY (DEPARTMENT) REFERENCES departments,
 CONSTRAINT PERS_FUNDDEPT_FK FOREIGN KEY (FUNDINGDEPT) REFERENCES 
 departments,
 CONSTRAINT PERS_MANAGER_FK FOREIGN KEY (MANAGER) REFERENCES persons,
 CONSTRAINT PERS_MENTOR_FK FOREIGN KEY (MENTOR) REFERENCES persons,
 CONSTRAINT PERS_HRADVISOR_FK FOREIGN KEY (HRADVISOR) REFERENCES persons,
 CONSTRAINT EMPS_PK PRIMARY KEY (PERSONID)
 );
 CREATE TABLE projects (
 PROJID INTEGER NOT NULL,
 NAME VARCHAR(32) NOT NULL,
 BUDGET DECIMAL(11,2) NOT NULL,
 DISCRIMINATOR VARCHAR(255),
 CONSTRAINT PROJS_PK PRIMARY KEY (PROJID)
 );
 CREATE TABLE project_member (
 PROJID INTEGER REFERENCES projects NOT NULL,
 MEMBER INTEGER REFERENCES persons NOT NULL
 );
 ij connect 
 'jdbc:derby:/Users/clr/apache/jdo/trunk/tck2/target/database/derby/jdotckdb';
 ij set schema applicationidentity0;
 0 rows inserted/updated/deleted
 ij select * from persons;
 PERSONID   |FIRSTNAME   |LASTNAME
 |MIDDLENAME  |BIRTHDATE |ADDRID 
 |STREET  |CITY
 |STA|ZIPC|COUNTRY   
   |HIREDATE  
 |WEEKLYHOURS  |DEPARTMENT |FUNDINGDEPT|MANAGER|MENTOR |HRADVISOR  
 |SALARY   |WAGE |DISCRIMINATOR

[jira] Commented: (DERBY-3301) Incorrect result from query with nested EXIST

[Craig Russell (JIRA)]

[ 
https://issues.apache.org/jira/browse/DERBY-3301?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=12560902#action_12560902
 ] 

Craig Russell commented on DERBY-3301:
--

 Too late to keep working - EXIST should return only one row.
There are many ways to evaluate the query, depending on the strategy. Yes, each 
of the EXISTS clauses should return either zero or one row, depending on 
whether the EXISTS condition is true for the specific row of the outermost FROM 
clause.

For each row of the outer product of the outermost FROM clause (DEPARTMENTS, 
EMPLOYEES, PROJECTS), the middle WHERE EXISTS should return true when there is 
a match between the EMPLOYEES and PROJECTS in the join table. As an 
optimization, you could invert the scan and look at all of the rows of the join 
table. It's hard to tell from the analysis here whether Derby is inverting the 
scan.

 Incorrect result from query with nested EXIST
 -

 Key: DERBY-3301
 URL: https://issues.apache.org/jira/browse/DERBY-3301
 Project: Derby
  Issue Type: Bug
  Components: SQL
Affects Versions: 10.1.3.1, 10.2.1.6, 10.3.2.1
Reporter: Craig Russell
 Attachments: d3301-queryplan.log, derby-3301.sql


 Derby returns unexpected results from a query with embedded EXIST clauses. 
 The query SQL is generated by the JPOX jdo implementation and is supposed to 
 return all of the PERSONS and PROJECTS where there is an entry in the join 
 table. This query works as expected when using MySQL.
 Here's the query:
 SELECT UNBOUND_E.PERSONID, UNBOUND_P.PROJID 
 FROM applicationidentity0.DEPARTMENTS THIS, 
  applicationidentity0.PERSONS UNBOUND_E, 
  applicationidentity0.PROJECTS UNBOUND_P 
 WHERE EXISTS ( 
 SELECT 1 FROM applicationidentity0.PERSONS THIS_EMPLOYEES_E 
 WHERE EXISTS ( 
 SELECT 1 FROM applicationidentity0.PROJECT_MEMBER 
 THIS_EMPLOYEES_E_PROJECTS_P 
 WHERE THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = 
 THIS_EMPLOYEES_E.PERSONID 
 AND THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = THIS_EMPLOYEES_E.PERSONID 
 AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
 AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
 AND UNBOUND_P.PROJID = THIS_EMPLOYEES_E_PROJECTS_P.PROJID 
 AND UNBOUND_E.PERSONID = THIS_EMPLOYEES_E.PERSONID) 
 );
 PERSONID   |PROJID 
 ---
 3  |1  
 5  |3  
 4  |3  
 2  |1  
 1  |1  
 5 rows selected
 I'm expecting 7 rows to be returned here, one row for each row in the join 
 table. 
 Here's the schema:
 CREATE TABLE departments (
 ID INTEGER NOT NULL,
 NAME VARCHAR(32) NOT NULL,
 EMP_OF_THE_MONTH INTEGER,
 COMPANYID INTEGER,
 DISCRIMINATOR VARCHAR(255),
 CONSTRAINT DEPTS_COMP_FK FOREIGN KEY (COMPANYID) REFERENCES companies,
 CONSTRAINT DEPTS_PK PRIMARY KEY (ID)
 );
 CREATE TABLE persons (
 PERSONID INTEGER NOT NULL,
 FIRSTNAME VARCHAR(32) NOT NULL,
 LASTNAME VARCHAR(32) NOT NULL,
 MIDDLENAME VARCHAR(32),
 BIRTHDATE TIMESTAMP NOT NULL,
 ADDRID INTEGER,
 STREET VARCHAR(64),
 CITY VARCHAR(64),
 STATE CHAR(2),
 ZIPCODE CHAR(5),
 COUNTRY VARCHAR(64),
 HIREDATE TIMESTAMP,
 WEEKLYHOURS REAL,
 DEPARTMENT INTEGER,
 FUNDINGDEPT INTEGER,
 MANAGER INTEGER,
 MENTOR INTEGER,
 HRADVISOR INTEGER,
 SALARY REAL,
 WAGE REAL,
 DISCRIMINATOR varchar(255) NOT NULL,
 CONSTRAINT PERS_DEPT_FK FOREIGN KEY (DEPARTMENT) REFERENCES departments,
 CONSTRAINT PERS_FUNDDEPT_FK FOREIGN KEY (FUNDINGDEPT) REFERENCES 
 departments,
 CONSTRAINT PERS_MANAGER_FK FOREIGN KEY (MANAGER) REFERENCES persons,
 CONSTRAINT PERS_MENTOR_FK FOREIGN KEY (MENTOR) REFERENCES persons,
 CONSTRAINT PERS_HRADVISOR_FK FOREIGN KEY (HRADVISOR) REFERENCES persons,
 CONSTRAINT EMPS_PK PRIMARY KEY (PERSONID)
 );
 CREATE TABLE projects (
 PROJID INTEGER NOT NULL,
 NAME VARCHAR(32) NOT NULL,
 BUDGET DECIMAL(11,2) NOT NULL,
 DISCRIMINATOR VARCHAR(255),
 CONSTRAINT PROJS_PK PRIMARY KEY (PROJID)
 );
 CREATE TABLE project_member (
 PROJID INTEGER REFERENCES projects NOT NULL,
 MEMBER INTEGER REFERENCES persons NOT NULL
 );
 ij connect 
 'jdbc:derby:/Users/clr/apache/jdo/trunk/tck2/target/database/derby/jdotckdb';
 ij set schema applicationidentity0;
 0 rows inserted/updated/deleted
 ij select * from persons;
 PERSONID   |FIRSTNAME   |LASTNAME
 |MIDDLENAME  |BIRTHDATE |ADDRID 
 |STREET  |CITY
 |STA|ZIPC|COUNTRY   
   |HIREDATE

[jira] Updated: (DERBY-3301) Incorrect result from query with nested EXIST

[Craig Russell (JIRA)]

 [ 
https://issues.apache.org/jira/browse/DERBY-3301?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Craig Russell updated DERBY-3301:
-

Attachment: derby-3301.sql

The attached patch demonstrates the problem.

The expected result of the last query in the file is the 7 rows corresponding 
to the rows in the join table.

To duplicate the problem, run ij  derby-3301.sql

Any suggestions are welcome.

 Incorrect result from query with nested EXIST
 -

 Key: DERBY-3301
 URL: https://issues.apache.org/jira/browse/DERBY-3301
 Project: Derby
  Issue Type: Bug
  Components: SQL
Affects Versions: 10.1.3.1, 10.2.1.6, 10.3.2.1
Reporter: Craig Russell
 Attachments: derby-3301.sql


 Derby returns unexpected results from a query with embedded EXIST clauses. 
 The query SQL is generated by the JPOX jdo implementation and is supposed to 
 return all of the PERSONS and PROJECTS where there is an entry in the join 
 table. This query works as expected when using MySQL.
 Here's the query:
 SELECT UNBOUND_E.PERSONID, UNBOUND_P.PROJID 
 FROM applicationidentity0.DEPARTMENTS THIS, 
  applicationidentity0.PERSONS UNBOUND_E, 
  applicationidentity0.PROJECTS UNBOUND_P 
 WHERE EXISTS ( 
 SELECT 1 FROM applicationidentity0.PERSONS THIS_EMPLOYEES_E 
 WHERE EXISTS ( 
 SELECT 1 FROM applicationidentity0.PROJECT_MEMBER 
 THIS_EMPLOYEES_E_PROJECTS_P 
 WHERE THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = 
 THIS_EMPLOYEES_E.PERSONID 
 AND THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = THIS_EMPLOYEES_E.PERSONID 
 AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
 AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
 AND UNBOUND_P.PROJID = THIS_EMPLOYEES_E_PROJECTS_P.PROJID 
 AND UNBOUND_E.PERSONID = THIS_EMPLOYEES_E.PERSONID) 
 );
 PERSONID   |PROJID 
 ---
 3  |1  
 5  |3  
 4  |3  
 2  |1  
 1  |1  
 5 rows selected
 I'm expecting 7 rows to be returned here, one row for each row in the join 
 table. 
 Here's the schema:
 CREATE TABLE departments (
 ID INTEGER NOT NULL,
 NAME VARCHAR(32) NOT NULL,
 EMP_OF_THE_MONTH INTEGER,
 COMPANYID INTEGER,
 DISCRIMINATOR VARCHAR(255),
 CONSTRAINT DEPTS_COMP_FK FOREIGN KEY (COMPANYID) REFERENCES companies,
 CONSTRAINT DEPTS_PK PRIMARY KEY (ID)
 );
 CREATE TABLE persons (
 PERSONID INTEGER NOT NULL,
 FIRSTNAME VARCHAR(32) NOT NULL,
 LASTNAME VARCHAR(32) NOT NULL,
 MIDDLENAME VARCHAR(32),
 BIRTHDATE TIMESTAMP NOT NULL,
 ADDRID INTEGER,
 STREET VARCHAR(64),
 CITY VARCHAR(64),
 STATE CHAR(2),
 ZIPCODE CHAR(5),
 COUNTRY VARCHAR(64),
 HIREDATE TIMESTAMP,
 WEEKLYHOURS REAL,
 DEPARTMENT INTEGER,
 FUNDINGDEPT INTEGER,
 MANAGER INTEGER,
 MENTOR INTEGER,
 HRADVISOR INTEGER,
 SALARY REAL,
 WAGE REAL,
 DISCRIMINATOR varchar(255) NOT NULL,
 CONSTRAINT PERS_DEPT_FK FOREIGN KEY (DEPARTMENT) REFERENCES departments,
 CONSTRAINT PERS_FUNDDEPT_FK FOREIGN KEY (FUNDINGDEPT) REFERENCES 
 departments,
 CONSTRAINT PERS_MANAGER_FK FOREIGN KEY (MANAGER) REFERENCES persons,
 CONSTRAINT PERS_MENTOR_FK FOREIGN KEY (MENTOR) REFERENCES persons,
 CONSTRAINT PERS_HRADVISOR_FK FOREIGN KEY (HRADVISOR) REFERENCES persons,
 CONSTRAINT EMPS_PK PRIMARY KEY (PERSONID)
 );
 CREATE TABLE projects (
 PROJID INTEGER NOT NULL,
 NAME VARCHAR(32) NOT NULL,
 BUDGET DECIMAL(11,2) NOT NULL,
 DISCRIMINATOR VARCHAR(255),
 CONSTRAINT PROJS_PK PRIMARY KEY (PROJID)
 );
 CREATE TABLE project_member (
 PROJID INTEGER REFERENCES projects NOT NULL,
 MEMBER INTEGER REFERENCES persons NOT NULL
 );
 ij connect 
 'jdbc:derby:/Users/clr/apache/jdo/trunk/tck2/target/database/derby/jdotckdb';
 ij set schema applicationidentity0;
 0 rows inserted/updated/deleted
 ij select * from persons;
 PERSONID   |FIRSTNAME   |LASTNAME
 |MIDDLENAME  |BIRTHDATE |ADDRID 
 |STREET  |CITY
 |STA|ZIPC|COUNTRY   
   |HIREDATE  
 |WEEKLYHOURS  |DEPARTMENT |FUNDINGDEPT|MANAGER|MENTOR |HRADVISOR  
 |SALARY   |WAGE |DISCRIMINATOR

[jira] Updated: (DERBY-3301) Incorrect result from query with nested EXIST

[Craig Russell (JIRA)]

 [ 
https://issues.apache.org/jira/browse/DERBY-3301?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Craig Russell updated DERBY-3301:
-

Affects Version/s: 10.1.3.1

Reproduced also on 10.1.3.1. There is a slight difference in behavior (of a 
similar query) that I will have to check into, and update this report.

 Incorrect result from query with nested EXIST
 -

 Key: DERBY-3301
 URL: https://issues.apache.org/jira/browse/DERBY-3301
 Project: Derby
  Issue Type: Bug
  Components: SQL
Affects Versions: 10.1.3.1, 10.2.1.6, 10.3.2.1
Reporter: Craig Russell

 Derby returns unexpected results from a query with embedded EXIST clauses. 
 The query SQL is generated by the JPOX jdo implementation and is supposed to 
 return all of the PERSONS and PROJECTS where there is an entry in the join 
 table. This query works as expected when using MySQL.
 Here's the query:
 SELECT UNBOUND_E.PERSONID, UNBOUND_P.PROJID 
 FROM applicationidentity0.DEPARTMENTS THIS, 
  applicationidentity0.PERSONS UNBOUND_E, 
  applicationidentity0.PROJECTS UNBOUND_P 
 WHERE EXISTS ( 
 SELECT 1 FROM applicationidentity0.PERSONS THIS_EMPLOYEES_E 
 WHERE EXISTS ( 
 SELECT 1 FROM applicationidentity0.PROJECT_MEMBER 
 THIS_EMPLOYEES_E_PROJECTS_P 
 WHERE THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = 
 THIS_EMPLOYEES_E.PERSONID 
 AND THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = THIS_EMPLOYEES_E.PERSONID 
 AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
 AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
 AND UNBOUND_P.PROJID = THIS_EMPLOYEES_E_PROJECTS_P.PROJID 
 AND UNBOUND_E.PERSONID = THIS_EMPLOYEES_E.PERSONID) 
 );
 PERSONID   |PROJID 
 ---
 3  |1  
 5  |3  
 4  |3  
 2  |1  
 1  |1  
 5 rows selected
 I'm expecting 7 rows to be returned here, one row for each row in the join 
 table. 
 Here's the schema:
 CREATE TABLE departments (
 ID INTEGER NOT NULL,
 NAME VARCHAR(32) NOT NULL,
 EMP_OF_THE_MONTH INTEGER,
 COMPANYID INTEGER,
 DISCRIMINATOR VARCHAR(255),
 CONSTRAINT DEPTS_COMP_FK FOREIGN KEY (COMPANYID) REFERENCES companies,
 CONSTRAINT DEPTS_PK PRIMARY KEY (ID)
 );
 CREATE TABLE persons (
 PERSONID INTEGER NOT NULL,
 FIRSTNAME VARCHAR(32) NOT NULL,
 LASTNAME VARCHAR(32) NOT NULL,
 MIDDLENAME VARCHAR(32),
 BIRTHDATE TIMESTAMP NOT NULL,
 ADDRID INTEGER,
 STREET VARCHAR(64),
 CITY VARCHAR(64),
 STATE CHAR(2),
 ZIPCODE CHAR(5),
 COUNTRY VARCHAR(64),
 HIREDATE TIMESTAMP,
 WEEKLYHOURS REAL,
 DEPARTMENT INTEGER,
 FUNDINGDEPT INTEGER,
 MANAGER INTEGER,
 MENTOR INTEGER,
 HRADVISOR INTEGER,
 SALARY REAL,
 WAGE REAL,
 DISCRIMINATOR varchar(255) NOT NULL,
 CONSTRAINT PERS_DEPT_FK FOREIGN KEY (DEPARTMENT) REFERENCES departments,
 CONSTRAINT PERS_FUNDDEPT_FK FOREIGN KEY (FUNDINGDEPT) REFERENCES 
 departments,
 CONSTRAINT PERS_MANAGER_FK FOREIGN KEY (MANAGER) REFERENCES persons,
 CONSTRAINT PERS_MENTOR_FK FOREIGN KEY (MENTOR) REFERENCES persons,
 CONSTRAINT PERS_HRADVISOR_FK FOREIGN KEY (HRADVISOR) REFERENCES persons,
 CONSTRAINT EMPS_PK PRIMARY KEY (PERSONID)
 );
 CREATE TABLE projects (
 PROJID INTEGER NOT NULL,
 NAME VARCHAR(32) NOT NULL,
 BUDGET DECIMAL(11,2) NOT NULL,
 DISCRIMINATOR VARCHAR(255),
 CONSTRAINT PROJS_PK PRIMARY KEY (PROJID)
 );
 CREATE TABLE project_member (
 PROJID INTEGER REFERENCES projects NOT NULL,
 MEMBER INTEGER REFERENCES persons NOT NULL
 );
 ij connect 
 'jdbc:derby:/Users/clr/apache/jdo/trunk/tck2/target/database/derby/jdotckdb';
 ij set schema applicationidentity0;
 0 rows inserted/updated/deleted
 ij select * from persons;
 PERSONID   |FIRSTNAME   |LASTNAME
 |MIDDLENAME  |BIRTHDATE |ADDRID 
 |STREET  |CITY
 |STA|ZIPC|COUNTRY   
   |HIREDATE  
 |WEEKLYHOURS  |DEPARTMENT |FUNDINGDEPT|MANAGER|MENTOR |HRADVISOR  
 |SALARY   |WAGE |DISCRIMINATOR

[jira] Issue Comment Edited: (DERBY-3301) Incorrect result from query with nested EXIST

[Craig Russell (JIRA)]

[ 
https://issues.apache.org/jira/browse/DERBY-3301?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=12556033#action_12556033
 ] 

clr edited comment on DERBY-3301 at 1/4/08 11:43 AM:
---

There is a similar query that sometimes returns the expected result and 
sometimes returns a single row.

The difference in the query is that there is an extra clause in the WHERE 
clause: 
AND UNBOUND_P.NAME = 'orange'

This query is expected to return three rows
PERSONID |PROJID 
--- 
3 |1 
2 |1 
1 |1 
3 rows selected

but sometimes only one row is returned
PERSONID |PROJID 
--- 
1 |1 
1 rows selected



  was (Author: clr):
There is a similar query that sometimes returns the expected result and 
sometimes returns a single row.

The difference in the query is that there is an extra clause in the WHERE 
clause: 
AND UNBOUND_P.NAME = 'orange'

This query is expected to return three rows
PERSONID |PROJID 
--- 
3 |1 
2 |1 
1 |1 
3 rows selected

but sometimes only one row is returned
PERSONID |PROJID 
--- 
1 |1 
3 rows selected


  
 Incorrect result from query with nested EXIST
 -

 Key: DERBY-3301
 URL: https://issues.apache.org/jira/browse/DERBY-3301
 Project: Derby
  Issue Type: Bug
  Components: SQL
Affects Versions: 10.1.3.1, 10.2.1.6, 10.3.2.1
Reporter: Craig Russell

 Derby returns unexpected results from a query with embedded EXIST clauses. 
 The query SQL is generated by the JPOX jdo implementation and is supposed to 
 return all of the PERSONS and PROJECTS where there is an entry in the join 
 table. This query works as expected when using MySQL.
 Here's the query:
 SELECT UNBOUND_E.PERSONID, UNBOUND_P.PROJID 
 FROM applicationidentity0.DEPARTMENTS THIS, 
  applicationidentity0.PERSONS UNBOUND_E, 
  applicationidentity0.PROJECTS UNBOUND_P 
 WHERE EXISTS ( 
 SELECT 1 FROM applicationidentity0.PERSONS THIS_EMPLOYEES_E 
 WHERE EXISTS ( 
 SELECT 1 FROM applicationidentity0.PROJECT_MEMBER 
 THIS_EMPLOYEES_E_PROJECTS_P 
 WHERE THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = 
 THIS_EMPLOYEES_E.PERSONID 
 AND THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = THIS_EMPLOYEES_E.PERSONID 
 AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
 AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
 AND UNBOUND_P.PROJID = THIS_EMPLOYEES_E_PROJECTS_P.PROJID 
 AND UNBOUND_E.PERSONID = THIS_EMPLOYEES_E.PERSONID) 
 );
 PERSONID   |PROJID 
 ---
 3  |1  
 5  |3  
 4  |3  
 2  |1  
 1  |1  
 5 rows selected
 I'm expecting 7 rows to be returned here, one row for each row in the join 
 table. 
 Here's the schema:
 CREATE TABLE departments (
 ID INTEGER NOT NULL,
 NAME VARCHAR(32) NOT NULL,
 EMP_OF_THE_MONTH INTEGER,
 COMPANYID INTEGER,
 DISCRIMINATOR VARCHAR(255),
 CONSTRAINT DEPTS_COMP_FK FOREIGN KEY (COMPANYID) REFERENCES companies,
 CONSTRAINT DEPTS_PK PRIMARY KEY (ID)
 );
 CREATE TABLE persons (
 PERSONID INTEGER NOT NULL,
 FIRSTNAME VARCHAR(32) NOT NULL,
 LASTNAME VARCHAR(32) NOT NULL,
 MIDDLENAME VARCHAR(32),
 BIRTHDATE TIMESTAMP NOT NULL,
 ADDRID INTEGER,
 STREET VARCHAR(64),
 CITY VARCHAR(64),
 STATE CHAR(2),
 ZIPCODE CHAR(5),
 COUNTRY VARCHAR(64),
 HIREDATE TIMESTAMP,
 WEEKLYHOURS REAL,
 DEPARTMENT INTEGER,
 FUNDINGDEPT INTEGER,
 MANAGER INTEGER,
 MENTOR INTEGER,
 HRADVISOR INTEGER,
 SALARY REAL,
 WAGE REAL,
 DISCRIMINATOR varchar(255) NOT NULL,
 CONSTRAINT PERS_DEPT_FK FOREIGN KEY (DEPARTMENT) REFERENCES departments,
 CONSTRAINT PERS_FUNDDEPT_FK FOREIGN KEY (FUNDINGDEPT) REFERENCES 
 departments,
 CONSTRAINT PERS_MANAGER_FK FOREIGN KEY (MANAGER) REFERENCES persons,
 CONSTRAINT PERS_MENTOR_FK FOREIGN KEY (MENTOR) REFERENCES persons,
 CONSTRAINT PERS_HRADVISOR_FK FOREIGN KEY (HRADVISOR) REFERENCES persons,
 CONSTRAINT EMPS_PK PRIMARY KEY (PERSONID)
 );
 CREATE TABLE projects (
 PROJID INTEGER NOT NULL,
 NAME VARCHAR(32) NOT NULL,
 BUDGET DECIMAL(11,2) NOT NULL,
 DISCRIMINATOR VARCHAR(255),
 CONSTRAINT PROJS_PK PRIMARY KEY (PROJID)
 );
 CREATE TABLE project_member (
 PROJID INTEGER REFERENCES projects NOT NULL,
 MEMBER INTEGER REFERENCES persons NOT NULL
 );
 ij connect 
 'jdbc:derby:/Users/clr/apache/jdo/trunk/tck2/target/database/derby/jdotckdb';
 ij set schema applicationidentity0;
 0 rows inserted/updated/deleted
 ij select * from persons;
 PERSONID   |FIRSTNAME   |LASTNAME
 |MIDDLENAME  |BIRTHDATE |ADDRID 
 |STREET

[jira] Commented: (DERBY-3301) Incorrect result from query with nested EXIST

[Craig Russell (JIRA)]

[ 
https://issues.apache.org/jira/browse/DERBY-3301?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=12556033#action_12556033
 ] 

Craig Russell commented on DERBY-3301:
--

There is a similar query that sometimes returns the expected result and 
sometimes returns a single row.

The difference in the query is that there is an extra clause in the WHERE 
clause: 
AND UNBOUND_P.NAME = 'orange'

This query is expected to return three rows
PERSONID |PROJID 
--- 
3 |1 
2 |1 
1 |1 
3 rows selected

but sometimes only one row is returned
PERSONID |PROJID 
--- 
1 |1 
3 rows selected



 Incorrect result from query with nested EXIST
 -

 Key: DERBY-3301
 URL: https://issues.apache.org/jira/browse/DERBY-3301
 Project: Derby
  Issue Type: Bug
  Components: SQL
Affects Versions: 10.1.3.1, 10.2.1.6, 10.3.2.1
Reporter: Craig Russell

 Derby returns unexpected results from a query with embedded EXIST clauses. 
 The query SQL is generated by the JPOX jdo implementation and is supposed to 
 return all of the PERSONS and PROJECTS where there is an entry in the join 
 table. This query works as expected when using MySQL.
 Here's the query:
 SELECT UNBOUND_E.PERSONID, UNBOUND_P.PROJID 
 FROM applicationidentity0.DEPARTMENTS THIS, 
  applicationidentity0.PERSONS UNBOUND_E, 
  applicationidentity0.PROJECTS UNBOUND_P 
 WHERE EXISTS ( 
 SELECT 1 FROM applicationidentity0.PERSONS THIS_EMPLOYEES_E 
 WHERE EXISTS ( 
 SELECT 1 FROM applicationidentity0.PROJECT_MEMBER 
 THIS_EMPLOYEES_E_PROJECTS_P 
 WHERE THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = 
 THIS_EMPLOYEES_E.PERSONID 
 AND THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = THIS_EMPLOYEES_E.PERSONID 
 AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
 AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
 AND UNBOUND_P.PROJID = THIS_EMPLOYEES_E_PROJECTS_P.PROJID 
 AND UNBOUND_E.PERSONID = THIS_EMPLOYEES_E.PERSONID) 
 );
 PERSONID   |PROJID 
 ---
 3  |1  
 5  |3  
 4  |3  
 2  |1  
 1  |1  
 5 rows selected
 I'm expecting 7 rows to be returned here, one row for each row in the join 
 table. 
 Here's the schema:
 CREATE TABLE departments (
 ID INTEGER NOT NULL,
 NAME VARCHAR(32) NOT NULL,
 EMP_OF_THE_MONTH INTEGER,
 COMPANYID INTEGER,
 DISCRIMINATOR VARCHAR(255),
 CONSTRAINT DEPTS_COMP_FK FOREIGN KEY (COMPANYID) REFERENCES companies,
 CONSTRAINT DEPTS_PK PRIMARY KEY (ID)
 );
 CREATE TABLE persons (
 PERSONID INTEGER NOT NULL,
 FIRSTNAME VARCHAR(32) NOT NULL,
 LASTNAME VARCHAR(32) NOT NULL,
 MIDDLENAME VARCHAR(32),
 BIRTHDATE TIMESTAMP NOT NULL,
 ADDRID INTEGER,
 STREET VARCHAR(64),
 CITY VARCHAR(64),
 STATE CHAR(2),
 ZIPCODE CHAR(5),
 COUNTRY VARCHAR(64),
 HIREDATE TIMESTAMP,
 WEEKLYHOURS REAL,
 DEPARTMENT INTEGER,
 FUNDINGDEPT INTEGER,
 MANAGER INTEGER,
 MENTOR INTEGER,
 HRADVISOR INTEGER,
 SALARY REAL,
 WAGE REAL,
 DISCRIMINATOR varchar(255) NOT NULL,
 CONSTRAINT PERS_DEPT_FK FOREIGN KEY (DEPARTMENT) REFERENCES departments,
 CONSTRAINT PERS_FUNDDEPT_FK FOREIGN KEY (FUNDINGDEPT) REFERENCES 
 departments,
 CONSTRAINT PERS_MANAGER_FK FOREIGN KEY (MANAGER) REFERENCES persons,
 CONSTRAINT PERS_MENTOR_FK FOREIGN KEY (MENTOR) REFERENCES persons,
 CONSTRAINT PERS_HRADVISOR_FK FOREIGN KEY (HRADVISOR) REFERENCES persons,
 CONSTRAINT EMPS_PK PRIMARY KEY (PERSONID)
 );
 CREATE TABLE projects (
 PROJID INTEGER NOT NULL,
 NAME VARCHAR(32) NOT NULL,
 BUDGET DECIMAL(11,2) NOT NULL,
 DISCRIMINATOR VARCHAR(255),
 CONSTRAINT PROJS_PK PRIMARY KEY (PROJID)
 );
 CREATE TABLE project_member (
 PROJID INTEGER REFERENCES projects NOT NULL,
 MEMBER INTEGER REFERENCES persons NOT NULL
 );
 ij connect 
 'jdbc:derby:/Users/clr/apache/jdo/trunk/tck2/target/database/derby/jdotckdb';
 ij set schema applicationidentity0;
 0 rows inserted/updated/deleted
 ij select * from persons;
 PERSONID   |FIRSTNAME   |LASTNAME
 |MIDDLENAME  |BIRTHDATE |ADDRID 
 |STREET  |CITY
 |STA|ZIPC|COUNTRY   
   |HIREDATE  
 |WEEKLYHOURS  |DEPARTMENT |FUNDINGDEPT|MANAGER|MENTOR |HRADVISOR  
 |SALARY   |WAGE |DISCRIMINATOR

[jira] Created: (DERBY-3301) Incorrect result from query with nested EXIST

[Craig Russell (JIRA)]

Incorrect result from query with nested EXIST
-

 Key: DERBY-3301
 URL: https://issues.apache.org/jira/browse/DERBY-3301
 Project: Derby
  Issue Type: Bug
  Components: SQL
Reporter: Craig Russell
 Fix For: 10.2.1.6


Derby returns unexpected results from a query with embedded EXIST clauses. The 
query SQL is generated by the JPOX jdo implementation and is supposed to return 
all of the PERSONS and PROJECTS where there is an entry in the join table. This 
query works as expected when using MySQL.

Here's the query:

SELECT UNBOUND_E.PERSONID, UNBOUND_P.PROJID 
FROM applicationidentity0.DEPARTMENTS THIS, 
 applicationidentity0.PERSONS UNBOUND_E, 
 applicationidentity0.PROJECTS UNBOUND_P 
WHERE EXISTS ( 
SELECT 1 FROM applicationidentity0.PERSONS THIS_EMPLOYEES_E 
WHERE EXISTS ( 
SELECT 1 FROM applicationidentity0.PROJECT_MEMBER 
THIS_EMPLOYEES_E_PROJECTS_P 
WHERE THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = THIS_EMPLOYEES_E.PERSONID 
AND THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = THIS_EMPLOYEES_E.PERSONID 
AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
AND UNBOUND_P.PROJID = THIS_EMPLOYEES_E_PROJECTS_P.PROJID 
AND UNBOUND_E.PERSONID = THIS_EMPLOYEES_E.PERSONID) 
);
PERSONID   |PROJID 
---
3  |1  
5  |3  
4  |3  
2  |1  
1  |1  
5 rows selected

I'm expecting 7 rows to be returned here, one row for each row in the join 
table. 

Here's the schema:
CREATE TABLE departments (
ID INTEGER NOT NULL,
NAME VARCHAR(32) NOT NULL,
EMP_OF_THE_MONTH INTEGER,
COMPANYID INTEGER,
DISCRIMINATOR VARCHAR(255),
CONSTRAINT DEPTS_COMP_FK FOREIGN KEY (COMPANYID) REFERENCES companies,
CONSTRAINT DEPTS_PK PRIMARY KEY (ID)
);

CREATE TABLE persons (
PERSONID INTEGER NOT NULL,
FIRSTNAME VARCHAR(32) NOT NULL,
LASTNAME VARCHAR(32) NOT NULL,
MIDDLENAME VARCHAR(32),
BIRTHDATE TIMESTAMP NOT NULL,
ADDRID INTEGER,
STREET VARCHAR(64),
CITY VARCHAR(64),
STATE CHAR(2),
ZIPCODE CHAR(5),
COUNTRY VARCHAR(64),
HIREDATE TIMESTAMP,
WEEKLYHOURS REAL,
DEPARTMENT INTEGER,
FUNDINGDEPT INTEGER,
MANAGER INTEGER,
MENTOR INTEGER,
HRADVISOR INTEGER,
SALARY REAL,
WAGE REAL,
DISCRIMINATOR varchar(255) NOT NULL,
CONSTRAINT PERS_DEPT_FK FOREIGN KEY (DEPARTMENT) REFERENCES departments,
CONSTRAINT PERS_FUNDDEPT_FK FOREIGN KEY (FUNDINGDEPT) REFERENCES 
departments,
CONSTRAINT PERS_MANAGER_FK FOREIGN KEY (MANAGER) REFERENCES persons,
CONSTRAINT PERS_MENTOR_FK FOREIGN KEY (MENTOR) REFERENCES persons,
CONSTRAINT PERS_HRADVISOR_FK FOREIGN KEY (HRADVISOR) REFERENCES persons,
CONSTRAINT EMPS_PK PRIMARY KEY (PERSONID)
);

CREATE TABLE projects (
PROJID INTEGER NOT NULL,
NAME VARCHAR(32) NOT NULL,
BUDGET DECIMAL(11,2) NOT NULL,
DISCRIMINATOR VARCHAR(255),
CONSTRAINT PROJS_PK PRIMARY KEY (PROJID)
);
CREATE TABLE project_member (
PROJID INTEGER REFERENCES projects NOT NULL,
MEMBER INTEGER REFERENCES persons NOT NULL
);

ij connect 
'jdbc:derby:/Users/clr/apache/jdo/trunk/tck2/target/database/derby/jdotckdb';
ij set schema applicationidentity0;
0 rows inserted/updated/deleted
ij select * from persons;
PERSONID   |FIRSTNAME   |LASTNAME
|MIDDLENAME  |BIRTHDATE |ADDRID |STREET 
 |CITY  
  |STA|ZIPC|COUNTRY   
  |HIREDATE  |WEEKLYHOURS  
|DEPARTMENT |FUNDINGDEPT|MANAGER|MENTOR |HRADVISOR  |SALARY   |WAGE 
|DISCRIMINATOR  
 
-
1  |emp1First   |emp1Last
|emp1Middle  |1970-06-09 21:00:00.0 |NULL   |NULL   
 |NULL

[jira] Updated: (DERBY-3301) Incorrect result from query with nested EXIST

[Craig Russell (JIRA)]

 [ 
https://issues.apache.org/jira/browse/DERBY-3301?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Craig Russell updated DERBY-3301:
-

Affects Version/s: 10.2.1.6
   10.3.2.1
Fix Version/s: (was: 10.2.1.6)

I just tried this with 10.3.2.1 and the same error occurs.

I changed the fix version from 10.2.1.6 to unknown (oops).

 Incorrect result from query with nested EXIST
 -

 Key: DERBY-3301
 URL: https://issues.apache.org/jira/browse/DERBY-3301
 Project: Derby
  Issue Type: Bug
  Components: SQL
Affects Versions: 10.2.1.6, 10.3.2.1
Reporter: Craig Russell

 Derby returns unexpected results from a query with embedded EXIST clauses. 
 The query SQL is generated by the JPOX jdo implementation and is supposed to 
 return all of the PERSONS and PROJECTS where there is an entry in the join 
 table. This query works as expected when using MySQL.
 Here's the query:
 SELECT UNBOUND_E.PERSONID, UNBOUND_P.PROJID 
 FROM applicationidentity0.DEPARTMENTS THIS, 
  applicationidentity0.PERSONS UNBOUND_E, 
  applicationidentity0.PROJECTS UNBOUND_P 
 WHERE EXISTS ( 
 SELECT 1 FROM applicationidentity0.PERSONS THIS_EMPLOYEES_E 
 WHERE EXISTS ( 
 SELECT 1 FROM applicationidentity0.PROJECT_MEMBER 
 THIS_EMPLOYEES_E_PROJECTS_P 
 WHERE THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = 
 THIS_EMPLOYEES_E.PERSONID 
 AND THIS_EMPLOYEES_E_PROJECTS_P.MEMBER = THIS_EMPLOYEES_E.PERSONID 
 AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
 AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID 
 AND UNBOUND_P.PROJID = THIS_EMPLOYEES_E_PROJECTS_P.PROJID 
 AND UNBOUND_E.PERSONID = THIS_EMPLOYEES_E.PERSONID) 
 );
 PERSONID   |PROJID 
 ---
 3  |1  
 5  |3  
 4  |3  
 2  |1  
 1  |1  
 5 rows selected
 I'm expecting 7 rows to be returned here, one row for each row in the join 
 table. 
 Here's the schema:
 CREATE TABLE departments (
 ID INTEGER NOT NULL,
 NAME VARCHAR(32) NOT NULL,
 EMP_OF_THE_MONTH INTEGER,
 COMPANYID INTEGER,
 DISCRIMINATOR VARCHAR(255),
 CONSTRAINT DEPTS_COMP_FK FOREIGN KEY (COMPANYID) REFERENCES companies,
 CONSTRAINT DEPTS_PK PRIMARY KEY (ID)
 );
 CREATE TABLE persons (
 PERSONID INTEGER NOT NULL,
 FIRSTNAME VARCHAR(32) NOT NULL,
 LASTNAME VARCHAR(32) NOT NULL,
 MIDDLENAME VARCHAR(32),
 BIRTHDATE TIMESTAMP NOT NULL,
 ADDRID INTEGER,
 STREET VARCHAR(64),
 CITY VARCHAR(64),
 STATE CHAR(2),
 ZIPCODE CHAR(5),
 COUNTRY VARCHAR(64),
 HIREDATE TIMESTAMP,
 WEEKLYHOURS REAL,
 DEPARTMENT INTEGER,
 FUNDINGDEPT INTEGER,
 MANAGER INTEGER,
 MENTOR INTEGER,
 HRADVISOR INTEGER,
 SALARY REAL,
 WAGE REAL,
 DISCRIMINATOR varchar(255) NOT NULL,
 CONSTRAINT PERS_DEPT_FK FOREIGN KEY (DEPARTMENT) REFERENCES departments,
 CONSTRAINT PERS_FUNDDEPT_FK FOREIGN KEY (FUNDINGDEPT) REFERENCES 
 departments,
 CONSTRAINT PERS_MANAGER_FK FOREIGN KEY (MANAGER) REFERENCES persons,
 CONSTRAINT PERS_MENTOR_FK FOREIGN KEY (MENTOR) REFERENCES persons,
 CONSTRAINT PERS_HRADVISOR_FK FOREIGN KEY (HRADVISOR) REFERENCES persons,
 CONSTRAINT EMPS_PK PRIMARY KEY (PERSONID)
 );
 CREATE TABLE projects (
 PROJID INTEGER NOT NULL,
 NAME VARCHAR(32) NOT NULL,
 BUDGET DECIMAL(11,2) NOT NULL,
 DISCRIMINATOR VARCHAR(255),
 CONSTRAINT PROJS_PK PRIMARY KEY (PROJID)
 );
 CREATE TABLE project_member (
 PROJID INTEGER REFERENCES projects NOT NULL,
 MEMBER INTEGER REFERENCES persons NOT NULL
 );
 ij connect 
 'jdbc:derby:/Users/clr/apache/jdo/trunk/tck2/target/database/derby/jdotckdb';
 ij set schema applicationidentity0;
 0 rows inserted/updated/deleted
 ij select * from persons;
 PERSONID   |FIRSTNAME   |LASTNAME
 |MIDDLENAME  |BIRTHDATE |ADDRID 
 |STREET  |CITY
 |STA|ZIPC|COUNTRY   
   |HIREDATE  
 |WEEKLYHOURS  |DEPARTMENT |FUNDINGDEPT|MANAGER|MENTOR |HRADVISOR  
 |SALARY   |WAGE |DISCRIMINATOR

[jira] Commented: (DERBY-2020) Change file option for syncing log file to disk from rws to rwd

[Craig Russell (JIRA)]

[ 
https://issues.apache.org/jira/browse/DERBY-2020?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12494073
 ] 

Craig Russell commented on DERBY-2020:
--

IIRC, the reason this was changed just a few years ago from rwd to rws is as a 
workaround for a nasty Apple MacOSX bug in which you could not create a 
database and immediately access it.

Has anyone built and run Derby on OSX with this patch applied to make sure we 
don't regress all the OSX users?

 Change file option for syncing log file to disk from rws to rwd
 ---

 Key: DERBY-2020
 URL: https://issues.apache.org/jira/browse/DERBY-2020
 Project: Derby
  Issue Type: Improvement
  Components: Performance, Store
Affects Versions: 10.3.0.0
Reporter: Olav Sandstaa
 Assigned To: Olav Sandstaa
 Fix For: 10.3.0.0

 Attachments: disk-cache.png, jvmsyncbug.diff, jvmsyncbug.stat, 
 jvmsyncbug_v2.diff, jvmsyncbug_v2.stat, jvmsyncbug_v3.diff, 
 jvmsyncbug_v3.stat, no-disk-cache.png, rwd.diff, rwd.stat, rwd_pre.diff, 
 rwd_pre.stat, rwd_v2.diff


 For writing the transaction log to disk Derby uses a
 RandomAccessFile. If it is supported by the JVM, the log files are
 opened in rws mode making the file system take care of syncing
 writes to disk. rws mode will ensure that both the data and the file
 meta-data is updated for every write to the file. On some operating
 systems (e.g. Solaris) this leads to two write operation to the disk
 for every write issued by Derby. This is limiting the throughput of
 update intensive applications.  If we could change the file mode to
 rwd this could reduce the number of updates to the disk.
 I have run some simple tests where I have changed mode from rws to
 rwd for the Derby log file. When running a small numbers of
 concurrent client threads the throughput is almost doubled and the
 response time is almost halved. I will attach some graphs that show
 this when running a given number of concurrent tpc-b like clients. These
 graphs show the throughput when running with rws and rwd mode when the
 disk's write cache has been enabled and disabled.
 I am creating this Jira to have a place where we can collect
 information about issues both for and against changing the default
 mode for writing to log files.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

[jira] Closed: (DERBY-1516) Inconsistent behavior for getBytes and getSubString for embedded versus network

[Craig Russell (JIRA)]

 [ 
https://issues.apache.org/jira/browse/DERBY-1516?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Craig Russell closed DERBY-1516.



 Inconsistent behavior for getBytes and getSubString for embedded versus 
 network
 ---

 Key: DERBY-1516
 URL: https://issues.apache.org/jira/browse/DERBY-1516
 Project: Derby
  Issue Type: Bug
  Components: JDBC
Reporter: Craig Russell
 Assigned To: Craig Russell
Priority: Minor
 Attachments: DERBY-1516.patch, DERBY-1516.patch, DERBY-1516.patch, 
 DERBY-1516.patch, DERBY-1516.patch, DERBY-1516.patch, DERBY-1516.patch


 org.apache.derby.client.am.Clob.getSubString(pos, length) and 
 org.apache.derby.client.am.Blob.getBytes(pos, length) check the length for 
 less than zero. 
 if ((pos = 0) || (length  0)) {
 throw new SqlException(agent_.logWriter_, Invalid position  
 + pos +  or length  + length);
 But org.apache.derby.impl.jdbc.EmbedClob(pos, length) and 
 org.apache.derby.impl.jdbc.EmbedBlob(pos, length) check the length for less 
 than or equal to zero.
if (length = 0)
 throw Util.generateCsSQLException(
 SQLState.BLOB_NONPOSITIVE_LENGTH, new Integer(length));
 The specification does not disallow length of zero, so zero length should be 
 allowed. I believe that the implementation in org.apache.derby.client.am is 
 correct, and the implementation in org.apache.derby.impl.jdbc is incorrect. 

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

[jira] Commented: (DERBY-1691) jdbcapi/blobclob4BLOB.java fails under DerbyNet framework with JCC 2.6

[Craig Russell (JIRA)]

[ 
http://issues.apache.org/jira/browse/DERBY-1691?page=comments#action_12428209 ] 

Craig Russell commented on DERBY-1691:
--

There is a tiny difference in the message text for:

com.ibm.db2.jcc.c.SqlException
com.ibm.db2.jcc.a.SqlException

Is the difference something needs to be explained? 

Craig


 jdbcapi/blobclob4BLOB.java fails under DerbyNet framework with JCC 2.6
 --

 Key: DERBY-1691
 URL: http://issues.apache.org/jira/browse/DERBY-1691
 Project: Derby
  Issue Type: Bug
  Components: Test, Regression Test Failure
Affects Versions: 10.2.0.0
 Environment: Linux 2.6.9-5.ELsmp Sun jdk 1.5.0_07-b03
Reporter: Rajesh Kartha
 Assigned To: David Van Couvering
 Fix For: 10.2.0.0


 With JCC 2.6, the jdbcapi/blobclob4BLOB.java fails.  The diff did not show 
 anything alarming, so I am guessing it
 may be a master update. The test passed fine with DerbyClient
 *** Start: blobclob4BLOB jdk1.5.0_06 DerbyNet derbynetmats:jdbcapi 2006-08-11 
 23:29:48 ***
 466a467,474
  EXPECTED SQLSTATE(22018): Invalid character string format for type INTEGER.
  end clobTest54
  START: clobTest6
  EXPECTED SQLSTATE(null): Invalid position 0 or length 5
  EXPECTED SQLSTATE(null): Invalid position 1 or length -76
  EXPECTED SQLSTATE(null): Invalid position 1 or length -1
  EXPECTED SQLSTATE(null): Invalid position 0 or length 0
  FAIL -- unexpected exception:java.lang.StringIndexOutOfBoundsException: 
  String index out of range: -1
 468,475d475
  EXPECTED SQLSTATE(22018): Invalid character string format for type INTEGER.
  end clobTest54
  START: clobTest6
  EXPECTED SQLSTATE(null): Invalid position 0 or length 5
  EXPECTED SQLSTATE(null): Invalid position 1 or length -76
  EXPECTED SQLSTATE(null): Invalid position 1 or length -1
  EXPECTED SQLSTATE(null): Invalid position 0 or length 0
  FAIL -- unexpected exception:java.lang.StringIndexOutOfBoundsException: 
 String index out of range: -1
 775a776,782
  blobTest54 finished
  START: blobTest6
  EXPECTED SQLSTATE(null): Invalid position 0 or length 5
  EXPECTED SQLSTATE(null): Invalid position 1 or length -76
  EXPECTED SQLSTATE(null): Invalid position 1 or length -1
  EXPECTED SQLSTATE(null): Invalid position 0 or length 0
  FAIL -- unexpected exception:java.lang.NegativeArraySizeException
 777,783d783
  blobTest54 finished
  START: blobTest6
  EXPECTED SQLSTATE(null): Invalid position 0 or length 5
  EXPECTED SQLSTATE(null): Invalid position 1 or length -76
  EXPECTED SQLSTATE(null): Invalid position 1 or length -1
  EXPECTED SQLSTATE(null): Invalid position 0 or length 0
  FAIL -- unexpected exception:java.lang.NegativeArraySizeException
 789 del
  com.ibm.db2.jcc.c.SqlException: Operation 'CREATE TRIGGER' cannot be 
 performed on object 'TESTBLOB' because there is an open ResultSet dependent 
 on that object.
 789a789
  com.ibm.db2.jcc.a.SqlException: Operation 'CREATE TRIGGER' cannot be 
  performed on object 'TESTBLOB' because there is an open ResultSet dependent 
  on that object.
 Test Failed.
 *** End:   blobclob4BLOB jdk1.5.0_06 DerbyNet derbynetmats:jdbcapi 2006-08-11 
 23:30:46 ***

-- 
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

[jira] Commented: (DERBY-1516) Inconsistent behavior for getBytes and getSubString for embedded versus network

[Craig Russell (JIRA)]

[ 
http://issues.apache.org/jira/browse/DERBY-1516?page=comments#action_12427294 ] 

Craig Russell commented on DERBY-1516:
--

Hi David,

From my comments 31-Jul-2006:

 I did not update master/DerbyNet/blobclob4BLOB.out canon because this canon 
 seems out of date wrt the code. If this is incorrect, please let me know. 

I recommend checking in the new DerbyNet canon for the blobclob4BLOB output. 
The differences in the DerbyNet canon are expected due to the additional tests 
that I wrote. The unexpected exception in DerbyNet is expected in this 
scenario due to the blobTest6 stimulating the DerbyNet driver to throw an 
exception that is not a SQLException. Once the driver is fixed to properly 
handle boundary errors (length  0; pos  lob.length + 1; etc.), this 
unexpected exception will go away.

There is a general issue in many of the tests that I looked at, that result 
sets, statements, and connections are not closed in a finally block but are in 
the main line code. So unexpected exceptions in general will cause subsequent 
tests to behave unpredictably. But it's not specific to this issue.


 Inconsistent behavior for getBytes and getSubString for embedded versus 
 network
 ---

 Key: DERBY-1516
 URL: http://issues.apache.org/jira/browse/DERBY-1516
 Project: Derby
  Issue Type: Bug
  Components: JDBC
Reporter: Craig Russell
 Assigned To: Craig Russell
Priority: Minor
 Attachments: DERBY-1516.patch, DERBY-1516.patch, DERBY-1516.patch, 
 DERBY-1516.patch, DERBY-1516.patch, DERBY-1516.patch, DERBY-1516.patch


 org.apache.derby.client.am.Clob.getSubString(pos, length) and 
 org.apache.derby.client.am.Blob.getBytes(pos, length) check the length for 
 less than zero. 
 if ((pos = 0) || (length  0)) {
 throw new SqlException(agent_.logWriter_, Invalid position  
 + pos +  or length  + length);
 But org.apache.derby.impl.jdbc.EmbedClob(pos, length) and 
 org.apache.derby.impl.jdbc.EmbedBlob(pos, length) check the length for less 
 than or equal to zero.
if (length = 0)
 throw Util.generateCsSQLException(
 SQLState.BLOB_NONPOSITIVE_LENGTH, new Integer(length));
 The specification does not disallow length of zero, so zero length should be 
 allowed. I believe that the implementation in org.apache.derby.client.am is 
 correct, and the implementation in org.apache.derby.impl.jdbc is incorrect. 

-- 
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

[jira] Updated: (DERBY-1516) Inconsistent behavior for getBytes and getSubString for embedded versus network

[Craig Russell (JIRA)]

 [ http://issues.apache.org/jira/browse/DERBY-1516?page=all ]

Craig Russell updated DERBY-1516:
-

Attachment: DERBY-1516.patch

I've updated the Derby workspace as of this morning.

I've removed the formatting changes from blobclob4BLOB.java. They would have 
been duplicated by the changes anticipated by DERBY-1363.

Tests pass. Check in at will.





 Inconsistent behavior for getBytes and getSubString for embedded versus 
 network
 ---

 Key: DERBY-1516
 URL: http://issues.apache.org/jira/browse/DERBY-1516
 Project: Derby
  Issue Type: Bug
  Components: JDBC
Reporter: Craig Russell
 Assigned To: Craig Russell
Priority: Minor
 Attachments: DERBY-1516.patch, DERBY-1516.patch, DERBY-1516.patch, 
 DERBY-1516.patch, DERBY-1516.patch, DERBY-1516.patch


 org.apache.derby.client.am.Clob.getSubString(pos, length) and 
 org.apache.derby.client.am.Blob.getBytes(pos, length) check the length for 
 less than zero. 
 if ((pos = 0) || (length  0)) {
 throw new SqlException(agent_.logWriter_, Invalid position  
 + pos +  or length  + length);
 But org.apache.derby.impl.jdbc.EmbedClob(pos, length) and 
 org.apache.derby.impl.jdbc.EmbedBlob(pos, length) check the length for less 
 than or equal to zero.
if (length = 0)
 throw Util.generateCsSQLException(
 SQLState.BLOB_NONPOSITIVE_LENGTH, new Integer(length));
 The specification does not disallow length of zero, so zero length should be 
 allowed. I believe that the implementation in org.apache.derby.client.am is 
 correct, and the implementation in org.apache.derby.impl.jdbc is incorrect. 

-- 
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

[jira] Commented: (DERBY-1516) Inconsistent behavior for getBytes and getSubString for embedded versus network

[Craig Russell (JIRA)]

[ 
http://issues.apache.org/jira/browse/DERBY-1516?page=comments#action_12426647 ] 

Craig Russell commented on DERBY-1516:
--

Dan observes:

 Since this has been a tricky area to get correct, it seems that adding 
 Javadoc comments to the methods your are modifying in the Derby 
 implementations of Clob  Blob would add great value. Otherwise a few months 
 from now someone may change the code again thinking it is wrong (especially 
 since the code no longer matches the javadoc). 

Could you please be a bit more specific, so I can do this in one pass? Which 
parts of the code do you want to be better commented; e.g. just the parts I 
modified, or the entire method?

 In Derby's JDBC implementation notes this comment exists for getSubString 

 http://db.apache.org/derby/papers/JDBCImplementation.html#getSubString%28int+pos%2C+int+length%29
  

 If the pos (position) argument is greater than the length of the CLOB then an 
 exception is thrown. This matches the semantics of the SQL SUBSTR function. 

 Does that need to be corrected, do we have an issue because the new code will 
 not match SQL SUBSTR?

Yes, that needs to be corrected, and I propose:
If the pos (position) argument is greater than the length + 1 of the CLOB then 
an exception is thrown. This matches the semantics of the 
java.lang.String.subString(int beginIndex, int endIndex) method. 

A similar change is needed for Blob.getBytes
If the pos (position) argument is greater than the length of the BLOB then an 
exception is thrown. This matches the semantics of the SQL SUBSTR function.

I propose:
If the pos (position) argument is greater than the length + 1 of the BLOB then 
an exception is thrown. This matches the semantics of the 
java.lang.System.arraycopy method.

I don't think that matching SQL semantics is as important as matching java 
language semantics. After all, this API is a java language API.

 Inconsistent behavior for getBytes and getSubString for embedded versus 
 network
 ---

 Key: DERBY-1516
 URL: http://issues.apache.org/jira/browse/DERBY-1516
 Project: Derby
  Issue Type: Bug
  Components: JDBC
Reporter: Craig Russell
 Assigned To: Craig Russell
Priority: Minor
 Attachments: DERBY-1516.patch, DERBY-1516.patch, DERBY-1516.patch, 
 DERBY-1516.patch, DERBY-1516.patch, DERBY-1516.patch


 org.apache.derby.client.am.Clob.getSubString(pos, length) and 
 org.apache.derby.client.am.Blob.getBytes(pos, length) check the length for 
 less than zero. 
 if ((pos = 0) || (length  0)) {
 throw new SqlException(agent_.logWriter_, Invalid position  
 + pos +  or length  + length);
 But org.apache.derby.impl.jdbc.EmbedClob(pos, length) and 
 org.apache.derby.impl.jdbc.EmbedBlob(pos, length) check the length for less 
 than or equal to zero.
if (length = 0)
 throw Util.generateCsSQLException(
 SQLState.BLOB_NONPOSITIVE_LENGTH, new Integer(length));
 The specification does not disallow length of zero, so zero length should be 
 allowed. I believe that the implementation in org.apache.derby.client.am is 
 correct, and the implementation in org.apache.derby.impl.jdbc is incorrect. 

-- 
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

[jira] Commented: (DERBY-1516) Inconsistent behavior for getBytes and getSubString for embedded versus network

[Craig Russell (JIRA)]

[ 
http://issues.apache.org/jira/browse/DERBY-1516?page=comments#action_12426674 ] 

Craig Russell commented on DERBY-1516:
--

Here's a proposed javadoc comment for EmbedClob.getSubString:

   * Valid pos values require that 0  pos  clob.length + 1.
   * Valid length values require that 0 = length = clob.length.

If this looks ok, I can update the javadoc for the other implementation classes.


 Inconsistent behavior for getBytes and getSubString for embedded versus 
 network
 ---

 Key: DERBY-1516
 URL: http://issues.apache.org/jira/browse/DERBY-1516
 Project: Derby
  Issue Type: Bug
  Components: JDBC
Reporter: Craig Russell
 Assigned To: Craig Russell
Priority: Minor
 Attachments: DERBY-1516.patch, DERBY-1516.patch, DERBY-1516.patch, 
 DERBY-1516.patch, DERBY-1516.patch, DERBY-1516.patch


 org.apache.derby.client.am.Clob.getSubString(pos, length) and 
 org.apache.derby.client.am.Blob.getBytes(pos, length) check the length for 
 less than zero. 
 if ((pos = 0) || (length  0)) {
 throw new SqlException(agent_.logWriter_, Invalid position  
 + pos +  or length  + length);
 But org.apache.derby.impl.jdbc.EmbedClob(pos, length) and 
 org.apache.derby.impl.jdbc.EmbedBlob(pos, length) check the length for less 
 than or equal to zero.
if (length = 0)
 throw Util.generateCsSQLException(
 SQLState.BLOB_NONPOSITIVE_LENGTH, new Integer(length));
 The specification does not disallow length of zero, so zero length should be 
 allowed. I believe that the implementation in org.apache.derby.client.am is 
 correct, and the implementation in org.apache.derby.impl.jdbc is incorrect. 

-- 
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

[jira] Commented: (DERBY-1516) Inconsistent behavior for getBytes and getSubString for embedded versus network

[Craig Russell (JIRA)]

[ 
http://issues.apache.org/jira/browse/DERBY-1516?page=comments#action_12426707 ] 

Craig Russell commented on DERBY-1516:
--

Hi Dan,

Thanks for your comments.

 I mean comments clarifying the length  position arguments passed in and the 
 valid ranges etc. Though while you were in there you could see if the 
 description was correct, e.g. EmbedClob.getSubString() says: 

 NOTE: return the empty string if pos is too large 

 which I don't think is true before or after your changes. 

I think I understand your comments and will try to update the comments to 
reflect reality.

 Why length +1, why not  length? 

If 0  pos = length, then there is no way to get any bytes (even zero bytes) 
from a 0-length blob. And I don't believe that this is the intent. Nor is it 
consistent with what other vendors have implemented. 

There is no loss of functionality in the length + 1 behavior. If your 
position is length + 1, then you can only get zero-length results from it. And 
this is exactly the case of a zero-length Lob.

The title of this issue is inconsistent behavior for embedded versus network. 
So something's gotta give. And after looking at it in detail, I'm convinced 
that the right behavior is to be consistent with regard to getBytes and 
getSubString; with regard to zero-length and non-zero-length Lobs; and with 
regard to embedded versus network. And this patch does it.

 Inconsistent behavior for getBytes and getSubString for embedded versus 
 network
 ---

 Key: DERBY-1516
 URL: http://issues.apache.org/jira/browse/DERBY-1516
 Project: Derby
  Issue Type: Bug
  Components: JDBC
Reporter: Craig Russell
 Assigned To: Craig Russell
Priority: Minor
 Attachments: DERBY-1516.patch, DERBY-1516.patch, DERBY-1516.patch, 
 DERBY-1516.patch, DERBY-1516.patch, DERBY-1516.patch


 org.apache.derby.client.am.Clob.getSubString(pos, length) and 
 org.apache.derby.client.am.Blob.getBytes(pos, length) check the length for 
 less than zero. 
 if ((pos = 0) || (length  0)) {
 throw new SqlException(agent_.logWriter_, Invalid position  
 + pos +  or length  + length);
 But org.apache.derby.impl.jdbc.EmbedClob(pos, length) and 
 org.apache.derby.impl.jdbc.EmbedBlob(pos, length) check the length for less 
 than or equal to zero.
if (length = 0)
 throw Util.generateCsSQLException(
 SQLState.BLOB_NONPOSITIVE_LENGTH, new Integer(length));
 The specification does not disallow length of zero, so zero length should be 
 allowed. I believe that the implementation in org.apache.derby.client.am is 
 correct, and the implementation in org.apache.derby.impl.jdbc is incorrect. 

-- 
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

[jira] Updated: (DERBY-1516) Inconsistent behavior for getBytes and getSubString for embedded versus network

[Craig Russell (JIRA)]

 [ http://issues.apache.org/jira/browse/DERBY-1516?page=all ]

Craig Russell updated DERBY-1516:
-

Attachment: DERBY-1516.patch

I've updated the javadoc for the getBytes in EmbedBlob; and getSubString in 
EmbedClob. I've created javadoc for the methods getBytes in Blob; and 
getSubString in Clob. I've corrected the comments within the implementation of 
the methods.

No other code changes were made compared to the last patch.

 Inconsistent behavior for getBytes and getSubString for embedded versus 
 network
 ---

 Key: DERBY-1516
 URL: http://issues.apache.org/jira/browse/DERBY-1516
 Project: Derby
  Issue Type: Bug
  Components: JDBC
Reporter: Craig Russell
 Assigned To: Craig Russell
Priority: Minor
 Attachments: DERBY-1516.patch, DERBY-1516.patch, DERBY-1516.patch, 
 DERBY-1516.patch, DERBY-1516.patch, DERBY-1516.patch, DERBY-1516.patch


 org.apache.derby.client.am.Clob.getSubString(pos, length) and 
 org.apache.derby.client.am.Blob.getBytes(pos, length) check the length for 
 less than zero. 
 if ((pos = 0) || (length  0)) {
 throw new SqlException(agent_.logWriter_, Invalid position  
 + pos +  or length  + length);
 But org.apache.derby.impl.jdbc.EmbedClob(pos, length) and 
 org.apache.derby.impl.jdbc.EmbedBlob(pos, length) check the length for less 
 than or equal to zero.
if (length = 0)
 throw Util.generateCsSQLException(
 SQLState.BLOB_NONPOSITIVE_LENGTH, new Integer(length));
 The specification does not disallow length of zero, so zero length should be 
 allowed. I believe that the implementation in org.apache.derby.client.am is 
 correct, and the implementation in org.apache.derby.impl.jdbc is incorrect. 

-- 
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

[jira] Commented: (DERBY-1516) Inconsistent behavior for getBytes and getSubString for embedded versus network

[Craig Russell (JIRA)]

[ 
http://issues.apache.org/jira/browse/DERBY-1516?page=comments#action_12426397 ] 

Craig Russell commented on DERBY-1516:
--

I have run derby-all with these changes, and no errors were reported.

Any other comments before I check this in?

 Inconsistent behavior for getBytes and getSubString for embedded versus 
 network
 ---

 Key: DERBY-1516
 URL: http://issues.apache.org/jira/browse/DERBY-1516
 Project: Derby
  Issue Type: Bug
  Components: JDBC
Reporter: Craig Russell
 Assigned To: Craig Russell
Priority: Minor
 Attachments: DERBY-1516.patch, DERBY-1516.patch, DERBY-1516.patch, 
 DERBY-1516.patch, DERBY-1516.patch


 org.apache.derby.client.am.Clob.getSubString(pos, length) and 
 org.apache.derby.client.am.Blob.getBytes(pos, length) check the length for 
 less than zero. 
 if ((pos = 0) || (length  0)) {
 throw new SqlException(agent_.logWriter_, Invalid position  
 + pos +  or length  + length);
 But org.apache.derby.impl.jdbc.EmbedClob(pos, length) and 
 org.apache.derby.impl.jdbc.EmbedBlob(pos, length) check the length for less 
 than or equal to zero.
if (length = 0)
 throw Util.generateCsSQLException(
 SQLState.BLOB_NONPOSITIVE_LENGTH, new Integer(length));
 The specification does not disallow length of zero, so zero length should be 
 allowed. I believe that the implementation in org.apache.derby.client.am is 
 correct, and the implementation in org.apache.derby.impl.jdbc is incorrect. 

-- 
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

[jira] Updated: (DERBY-1516) Inconsistent behavior for getBytes and getSubString for embedded versus network

[Craig Russell (JIRA)]

 [ http://issues.apache.org/jira/browse/DERBY-1516?page=all ]

Craig Russell updated DERBY-1516:
-

Derby Info: [Patch Available]

 Inconsistent behavior for getBytes and getSubString for embedded versus 
 network
 ---

 Key: DERBY-1516
 URL: http://issues.apache.org/jira/browse/DERBY-1516
 Project: Derby
  Issue Type: Bug
  Components: JDBC
Reporter: Craig Russell
 Assigned To: Craig Russell
Priority: Minor
 Attachments: DERBY-1516.patch, DERBY-1516.patch, DERBY-1516.patch, 
 DERBY-1516.patch, DERBY-1516.patch


 org.apache.derby.client.am.Clob.getSubString(pos, length) and 
 org.apache.derby.client.am.Blob.getBytes(pos, length) check the length for 
 less than zero. 
 if ((pos = 0) || (length  0)) {
 throw new SqlException(agent_.logWriter_, Invalid position  
 + pos +  or length  + length);
 But org.apache.derby.impl.jdbc.EmbedClob(pos, length) and 
 org.apache.derby.impl.jdbc.EmbedBlob(pos, length) check the length for less 
 than or equal to zero.
if (length = 0)
 throw Util.generateCsSQLException(
 SQLState.BLOB_NONPOSITIVE_LENGTH, new Integer(length));
 The specification does not disallow length of zero, so zero length should be 
 allowed. I believe that the implementation in org.apache.derby.client.am is 
 correct, and the implementation in org.apache.derby.impl.jdbc is incorrect. 

-- 
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

[jira] Commented: (DERBY-1639) Include early-draft notice required by JDBC 4 spec license in NOTICES, release notes and documentation

[Craig Russell (JIRA)]

[ 
http://issues.apache.org/jira/browse/DERBY-1639?page=comments#action_12425901 ] 

Craig Russell commented on DERBY-1639:
--

IANAL, but I don't think there is a problem. Derby is not an application. The 
only way you can run Derby that exposes JDBC4 functionality is by running a 
User Application with Java SE 6. If a User Application runs in this 
environment, is is subject to the testing and evaluation terms of the license. 
So there is no need to encumber Derby NOTICEs with this disclaimer.



 Include early-draft notice required by JDBC 4 spec license in NOTICES, 
 release notes and documentation
 --

 Key: DERBY-1639
 URL: http://issues.apache.org/jira/browse/DERBY-1639
 Project: Derby
  Issue Type: Bug
  Components: Documentation
Affects Versions: 10.2.0.0
Reporter: Andrew McIntyre
 Assigned To: Rick Hillegas
 Fix For: 10.2.0.0

 Attachments: derby-1639-notice_v01.diff, derby-1639-notice_v02.diff


 from the license:
 3. Distribute applications written to the 
 Specification to third parties for their testing and 
 evaluation use, provided that any such application 
 includes the following notice:
   This is an application written to interoperate 
 with an early-draft specification developed under the 
 Java Community Process (JCP) and is made available for 
 testing and evaluation purposes only. The code is not 
 compatible with any specification of the JCP.
 This notice needs to be included in the NOTICES file, and should also be used 
 to frame discussion of JDBC 4.0 features in the release notes and 
 documentation.
 Here is a RELEASE NOTE for inclusion with this issue. I am editting this note 
 into the Description field so that we can amend it if necessary:
 - RELEASE NOTE 
 PROBLEM:
 Derby 10.2 exposes an implementation of an early draft version of the JDBC4 
 specification.
 SYMPTOM:
 This issue only affects you if you are running the Derby network client or 
 embedded server on Java SE 6. This issue does not affect you if you are 
 running Derby on J2SE 1.3, 1.4, or 1.5.
 When run on Java SE 6, Derby's network client and embedded server expose JDBC 
 implementations which do not conform to an approved, final version of the 
 JDBC specification. Instead, these implementations adhere to an early-draft 
 version of the JDBC4 specification. Please note the following:
 This is an application written to interoperate  with an early-draft 
 specification developed under the  Java Community Process (JCP) and is made 
 available for  testing and evaluation purposes only. The code is not  
 compatible with any specification of the JCP.
 SOLUTION:
 A follow-on release will expose JDBC4 implementations conforming to the final 
 draft of the JDBC4 specification.
 WORKAROUND:
 If you do not want to use experimental, early-draft JDBC4 interfaces, run 
 Derby on J2SE 1.3, 1.4, or 1.5 or on J2ME/CDC/Foundation 1.0.

-- 
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

[jira] Commented: (DERBY-1516) Inconsistent behavior for getBytes and getSubString for embedded versus network

[Craig Russell (JIRA)]

[ 
http://issues.apache.org/jira/browse/DERBY-1516?page=comments#action_12425588 ] 

Craig Russell commented on DERBY-1516:
--

Hi Dag,

You know, I read this a hundred times and did not read it as allowing 
t2.substring(1,1), since clearly 1 is beyond the length of the String.

substring(int beginIndex, int endIndex)
Throws:
IndexOutOfBoundsException - if the beginIndex is negative, or endIndex is 
larger than the length of this String object, or beginIndex is larger than 
endIndex.

But you are right and I'll change the implementation to support this. Much 
cleaner indeed.

I will reimplement the test for the case of pos one beyond the length of the 
Lob, which would now be legal. It would always return a zero-length result.

Thanks,

Craig

 Inconsistent behavior for getBytes and getSubString for embedded versus 
 network
 ---

 Key: DERBY-1516
 URL: http://issues.apache.org/jira/browse/DERBY-1516
 Project: Derby
  Issue Type: Bug
  Components: JDBC
Reporter: Craig Russell
 Assigned To: Craig Russell
Priority: Minor
 Attachments: DERBY-1516.patch, DERBY-1516.patch, DERBY-1516.patch, 
 DERBY-1516.patch


 org.apache.derby.client.am.Clob.getSubString(pos, length) and 
 org.apache.derby.client.am.Blob.getBytes(pos, length) check the length for 
 less than zero. 
 if ((pos = 0) || (length  0)) {
 throw new SqlException(agent_.logWriter_, Invalid position  
 + pos +  or length  + length);
 But org.apache.derby.impl.jdbc.EmbedClob(pos, length) and 
 org.apache.derby.impl.jdbc.EmbedBlob(pos, length) check the length for less 
 than or equal to zero.
if (length = 0)
 throw Util.generateCsSQLException(
 SQLState.BLOB_NONPOSITIVE_LENGTH, new Integer(length));
 The specification does not disallow length of zero, so zero length should be 
 allowed. I believe that the implementation in org.apache.derby.client.am is 
 correct, and the implementation in org.apache.derby.impl.jdbc is incorrect. 

-- 
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

[jira] Updated: (DERBY-1516) Inconsistent behavior for getBytes and getSubString for embedded versus network

[Craig Russell (JIRA)]

 [ http://issues.apache.org/jira/browse/DERBY-1516?page=all ]

Craig Russell updated DERBY-1516:
-

Attachment: DERBY-1516.patch

This patch incorporates all of the comments (thank you, Kathey and Dag) and 
therefore the logic is simpler for detecting invalid positions.

The canons for blobclob4BLOB have been changed to reflect that a request for 
bytes or a substring starting at position (length + 1) is now valid and the 
result will be of zero length, regardless of the length requested. This will 
not break existing applications except possibly regression testing applications 
that depend on Derby throwing an exception for this case. 

 Inconsistent behavior for getBytes and getSubString for embedded versus 
 network
 ---

 Key: DERBY-1516
 URL: http://issues.apache.org/jira/browse/DERBY-1516
 Project: Derby
  Issue Type: Bug
  Components: JDBC
Reporter: Craig Russell
 Assigned To: Craig Russell
Priority: Minor
 Attachments: DERBY-1516.patch, DERBY-1516.patch, DERBY-1516.patch, 
 DERBY-1516.patch, DERBY-1516.patch


 org.apache.derby.client.am.Clob.getSubString(pos, length) and 
 org.apache.derby.client.am.Blob.getBytes(pos, length) check the length for 
 less than zero. 
 if ((pos = 0) || (length  0)) {
 throw new SqlException(agent_.logWriter_, Invalid position  
 + pos +  or length  + length);
 But org.apache.derby.impl.jdbc.EmbedClob(pos, length) and 
 org.apache.derby.impl.jdbc.EmbedBlob(pos, length) check the length for less 
 than or equal to zero.
if (length = 0)
 throw Util.generateCsSQLException(
 SQLState.BLOB_NONPOSITIVE_LENGTH, new Integer(length));
 The specification does not disallow length of zero, so zero length should be 
 allowed. I believe that the implementation in org.apache.derby.client.am is 
 correct, and the implementation in org.apache.derby.impl.jdbc is incorrect. 

-- 
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

[jira] Commented: (DERBY-1516) Inconsistent behavior for getBytes and getSubString for embedded versus network

[Craig Russell (JIRA)]

[ 
http://issues.apache.org/jira/browse/DERBY-1516?page=comments#action_12425340 ] 

Craig Russell commented on DERBY-1516:
--

There is different behavior between embedded and network in other areas as well 
as the one I'm looking at here. I'd like to see what people think about this as 
well. It's hard to add test cases for zero-length requests without 
rationalizing the current behavior.

1. Both embedded and network do not return an error but truncate the result 
when going past the end of the Clob. That is, ask a Clob with length 25 for 50 
characters starting at position 1 and get 26 characters and no exception.

Proposal: Leave this behavior; we would need to add a new message, since the 
current message SQLSTATE(XJ076): The position argument '5,910' exceeds the 
size of the BLOB/CLOB cannot be used to describe running off the end of the 
Clob. I'd like to see this changed in future, to match the java.lang.String 
behavior, but this is a compatibility issue (existing applications might depend 
on this behavior).

2. Different behavior for zero-length Clobs:
Embedded Clob with zero length: throws an exception trying to get any non-zero 
length substring
Network Clob with zero length throws an exception only if the position is not 
== 1.

Proposal: Change Network to throw an exception on any request to get a non-zero 
length substring.

Craig

 Inconsistent behavior for getBytes and getSubString for embedded versus 
 network
 ---

 Key: DERBY-1516
 URL: http://issues.apache.org/jira/browse/DERBY-1516
 Project: Derby
  Issue Type: Bug
  Components: JDBC
Reporter: Craig Russell
 Assigned To: Craig Russell
Priority: Minor
 Attachments: DERBY-1516.patch, DERBY-1516.patch, DERBY-1516.patch


 org.apache.derby.client.am.Clob.getSubString(pos, length) and 
 org.apache.derby.client.am.Blob.getBytes(pos, length) check the length for 
 less than zero. 
 if ((pos = 0) || (length  0)) {
 throw new SqlException(agent_.logWriter_, Invalid position  
 + pos +  or length  + length);
 But org.apache.derby.impl.jdbc.EmbedClob(pos, length) and 
 org.apache.derby.impl.jdbc.EmbedBlob(pos, length) check the length for less 
 than or equal to zero.
if (length = 0)
 throw Util.generateCsSQLException(
 SQLState.BLOB_NONPOSITIVE_LENGTH, new Integer(length));
 The specification does not disallow length of zero, so zero length should be 
 allowed. I believe that the implementation in org.apache.derby.client.am is 
 correct, and the implementation in org.apache.derby.impl.jdbc is incorrect. 

-- 
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

[jira] Updated: (DERBY-1516) Inconsistent behavior for getBytes and getSubString for embedded versus network

[Craig Russell (JIRA)]

 [ http://issues.apache.org/jira/browse/DERBY-1516?page=all ]

Craig Russell updated DERBY-1516:
-

Attachment: DERBY-1516.patch

This patch solves the discrepancy between the treatment of getBytes and 
getSubString where the requested length is zero. This makes up 2 lines of the 
patch, in the first 10 lines of EmbedBlob and EmbedClob. Most of the rest of 
the patch is testing.

I've updated embedded and network Lobs to consistently check validity of 
position requested. The position must be between 1 and the last position of the 
Lob. I've added positive test cases for retrieving zero length byte[ ] and zero 
length Strings from position 1 and the last position of Blob and Clobs. 

I've added negative test cases for retrieving zero length byte[ ] and String 
from position 0 and one past the end of the Blob and Clob.

I've changed the message regarding invalid length so that zero length is not 
mentioned as an error.

I've removed the code to disable tests that was put in because of issues 5243 
and 5914. There is no longer any difference between the behavior of embedded 
and network drivers due to these issues. The canons for blobTest2, clobTest2, 
blobTest6, and clobTest6 are now the same for embedded and network.

I just had to reformat some of the test code which had random placement of 
beginning and ending braces, indentations, and try/catch formatting, and 
greater than 80 character lines. I removed commented-out code that represented 
the previous versions of clobTest2 and blobTest2.

I preserved the treatment of requests that started within the Lob but ran off 
the end. These cases still return a shorter result than requested, returning 
the last element of the Lob as the last element of the result. So there should 
be no backward compatibility issues. All the existing code will still work with 
this patch. 


 Inconsistent behavior for getBytes and getSubString for embedded versus 
 network
 ---

 Key: DERBY-1516
 URL: http://issues.apache.org/jira/browse/DERBY-1516
 Project: Derby
  Issue Type: Bug
  Components: JDBC
Reporter: Craig Russell
 Assigned To: Craig Russell
Priority: Minor
 Attachments: DERBY-1516.patch, DERBY-1516.patch, DERBY-1516.patch, 
 DERBY-1516.patch


 org.apache.derby.client.am.Clob.getSubString(pos, length) and 
 org.apache.derby.client.am.Blob.getBytes(pos, length) check the length for 
 less than zero. 
 if ((pos = 0) || (length  0)) {
 throw new SqlException(agent_.logWriter_, Invalid position  
 + pos +  or length  + length);
 But org.apache.derby.impl.jdbc.EmbedClob(pos, length) and 
 org.apache.derby.impl.jdbc.EmbedBlob(pos, length) check the length for less 
 than or equal to zero.
if (length = 0)
 throw Util.generateCsSQLException(
 SQLState.BLOB_NONPOSITIVE_LENGTH, new Integer(length));
 The specification does not disallow length of zero, so zero length should be 
 allowed. I believe that the implementation in org.apache.derby.client.am is 
 correct, and the implementation in org.apache.derby.impl.jdbc is incorrect. 

-- 
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

[jira] Commented: (DERBY-1516) Inconsistent behavior for getBytes and getSubString for embedded versus network

[Craig Russell (JIRA)]

[ 
http://issues.apache.org/jira/browse/DERBY-1516?page=comments#action_12424887 ] 

Craig Russell commented on DERBY-1516:
--

Thanks for the feedback. I'll make some positive and negative test cases and 
update the patch.

Yes, I'm suggesting that we follow the java.lang.String specification and allow 
for zero-length getSubString only if the position is legal. This is so there's 
no special if (length == 0) { // ignore position for 0-length ...}. It actually 
simplifies development.

 Inconsistent behavior for getBytes and getSubString for embedded versus 
 network
 ---

 Key: DERBY-1516
 URL: http://issues.apache.org/jira/browse/DERBY-1516
 Project: Derby
  Issue Type: Bug
  Components: JDBC
Reporter: Craig Russell
 Assigned To: Craig Russell
Priority: Minor
 Attachments: DERBY-1516.patch, DERBY-1516.patch, DERBY-1516.patch


 org.apache.derby.client.am.Clob.getSubString(pos, length) and 
 org.apache.derby.client.am.Blob.getBytes(pos, length) check the length for 
 less than zero. 
 if ((pos = 0) || (length  0)) {
 throw new SqlException(agent_.logWriter_, Invalid position  
 + pos +  or length  + length);
 But org.apache.derby.impl.jdbc.EmbedClob(pos, length) and 
 org.apache.derby.impl.jdbc.EmbedBlob(pos, length) check the length for less 
 than or equal to zero.
if (length = 0)
 throw Util.generateCsSQLException(
 SQLState.BLOB_NONPOSITIVE_LENGTH, new Integer(length));
 The specification does not disallow length of zero, so zero length should be 
 allowed. I believe that the implementation in org.apache.derby.client.am is 
 correct, and the implementation in org.apache.derby.impl.jdbc is incorrect. 

-- 
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

[jira] Updated: (DERBY-1516) Inconsistent behavior for getBytes and getSubString for embedded versus network

[Craig Russell (JIRA)]

 [ http://issues.apache.org/jira/browse/DERBY-1516?page=all ]

Craig Russell updated DERBY-1516:
-

Attachment: DERBY-1516.patch

I've updated the patch with changes to the 
master/DerbyNetClient/blobclob4BLOB.out canon.

I did not update master/DerbyNet/blobclob4BLOB.out canon because this canon 
seems out of date wrt the code. If this is incorrect, please let me know.


 Inconsistent behavior for getBytes and getSubString for embedded versus 
 network
 ---

 Key: DERBY-1516
 URL: http://issues.apache.org/jira/browse/DERBY-1516
 Project: Derby
  Issue Type: Bug
  Components: JDBC
Reporter: Craig Russell
Priority: Minor
 Attachments: DERBY-1516.patch, DERBY-1516.patch, DERBY-1516.patch


 org.apache.derby.client.am.Clob.getSubString(pos, length) and 
 org.apache.derby.client.am.Blob.getBytes(pos, length) check the length for 
 less than zero. 
 if ((pos = 0) || (length  0)) {
 throw new SqlException(agent_.logWriter_, Invalid position  
 + pos +  or length  + length);
 But org.apache.derby.impl.jdbc.EmbedClob(pos, length) and 
 org.apache.derby.impl.jdbc.EmbedBlob(pos, length) check the length for less 
 than or equal to zero.
if (length = 0)
 throw Util.generateCsSQLException(
 SQLState.BLOB_NONPOSITIVE_LENGTH, new Integer(length));
 The specification does not disallow length of zero, so zero length should be 
 allowed. I believe that the implementation in org.apache.derby.client.am is 
 correct, and the implementation in org.apache.derby.impl.jdbc is incorrect. 

-- 
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

[jira] Assigned: (DERBY-1516) Inconsistent behavior for getBytes and getSubString for embedded versus network

[Craig Russell (JIRA)]

 [ http://issues.apache.org/jira/browse/DERBY-1516?page=all ]

Craig Russell reassigned DERBY-1516:


Assignee: Craig Russell

 Inconsistent behavior for getBytes and getSubString for embedded versus 
 network
 ---

 Key: DERBY-1516
 URL: http://issues.apache.org/jira/browse/DERBY-1516
 Project: Derby
  Issue Type: Bug
  Components: JDBC
Reporter: Craig Russell
 Assigned To: Craig Russell
Priority: Minor
 Attachments: DERBY-1516.patch, DERBY-1516.patch, DERBY-1516.patch


 org.apache.derby.client.am.Clob.getSubString(pos, length) and 
 org.apache.derby.client.am.Blob.getBytes(pos, length) check the length for 
 less than zero. 
 if ((pos = 0) || (length  0)) {
 throw new SqlException(agent_.logWriter_, Invalid position  
 + pos +  or length  + length);
 But org.apache.derby.impl.jdbc.EmbedClob(pos, length) and 
 org.apache.derby.impl.jdbc.EmbedBlob(pos, length) check the length for less 
 than or equal to zero.
if (length = 0)
 throw Util.generateCsSQLException(
 SQLState.BLOB_NONPOSITIVE_LENGTH, new Integer(length));
 The specification does not disallow length of zero, so zero length should be 
 allowed. I believe that the implementation in org.apache.derby.client.am is 
 correct, and the implementation in org.apache.derby.impl.jdbc is incorrect. 

-- 
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

[jira] Created: (DERBY-1617) Re-enable blobclob4BLOB clobTest6 that was disabled for an old bug

[Craig Russell (JIRA)]

Re-enable blobclob4BLOB clobTest6 that was disabled for an old bug
--

 Key: DERBY-1617
 URL: http://issues.apache.org/jira/browse/DERBY-1617
 Project: Derby
  Issue Type: Test
  Components: Test
Reporter: Craig Russell
Priority: Minor


The jdbc api tests in 
org.apache.derbyTesting.functionTests.tests.jdbcapi.blobclob4BLOB.java have 
commented out clobTest6 due to an old bug that has apparently been fixed.

line 1471:
// 0 or negative position value
if (isDerbyNet)
System.out.println( negative tests for 
clob.getSubstring won't run  for network server  until 5243 is fixed);
if (! isDerbyNet)
{

The disabling code in the test should be removed, which will involve updating 
the canons to match.

-- 
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

[jira] Commented: (DERBY-1516) Inconsistent behavior for getBytes and getSubString for embedded versus network

[Craig Russell (JIRA)]

[ 
http://issues.apache.org/jira/browse/DERBY-1516?page=comments#action_12424673 ] 

Craig Russell commented on DERBY-1516:
--

Including the discussion from the alias referenced immediately above:

 One interesting test with 0 length is the case for getSubString(1,0) 
 for a zero length lob. 
 Should it throw an exception or return a zero length string? 

The API working doesn't give much help to resolve this; the wording for the 
exception in JDK 1.6 is 

 Throws: 
SQLException - if there is an error accessing the CLOB value 

 which I guess is equivalent to YMMV... A case for Lance? 

 Even if this case is allowed, should it make a difference if position is  
 (length+1), e.g. getSubString(2,0) for an empty CLOB? 

Lance has not replied to a request to update the wording, and I think time is 
running out on this to be added to the specification in progress.

The jdbc spec has followed the java.lang.String spec pretty closely, modulo 
1-origin vs. 0-origin indexing. The String spec allows accessing substrings of 
a 0-length string, as follows:

public String substring(int beginIndex,
int endIndex)
Returns a new string that is a substring of this string. The substring begins 
at the specified beginIndex and extends to the character at index endIndex - 1. 
Thus the length of the substring is endIndex-beginIndex...
IndexOutOfBoundsException - if the beginIndex is negative, or endIndex is 
larger than the length of this String object, or beginIndex is larger than 
endIndex.

For a zero-length String:
1. endIndex must be 0 or else endIndex would be larger than the length of the 
String;
2. beginIndex must be 0 or else beginIndex would be larger than endIndex.

Translating this to jdbc, for a zero-length Clob:
1. position must be 1;
2. length must be 0.

I agree we should add positive test cases to extract a zero-length substring 
from a Clob and Blob. 

I propose adding to clobTest2 and blobTest2 a test like: 
blobclob4BLOB.printInterval(clob, 1, 0, 7, i, clobLength) // zero length
blobclob4BLOB.printInterval(blob, 1, 0, 7, i, blobLength) // zero length


 Inconsistent behavior for getBytes and getSubString for embedded versus 
 network
 ---

 Key: DERBY-1516
 URL: http://issues.apache.org/jira/browse/DERBY-1516
 Project: Derby
  Issue Type: Bug
  Components: JDBC
Reporter: Craig Russell
 Assigned To: Craig Russell
Priority: Minor
 Attachments: DERBY-1516.patch, DERBY-1516.patch, DERBY-1516.patch


 org.apache.derby.client.am.Clob.getSubString(pos, length) and 
 org.apache.derby.client.am.Blob.getBytes(pos, length) check the length for 
 less than zero. 
 if ((pos = 0) || (length  0)) {
 throw new SqlException(agent_.logWriter_, Invalid position  
 + pos +  or length  + length);
 But org.apache.derby.impl.jdbc.EmbedClob(pos, length) and 
 org.apache.derby.impl.jdbc.EmbedBlob(pos, length) check the length for less 
 than or equal to zero.
if (length = 0)
 throw Util.generateCsSQLException(
 SQLState.BLOB_NONPOSITIVE_LENGTH, new Integer(length));
 The specification does not disallow length of zero, so zero length should be 
 allowed. I believe that the implementation in org.apache.derby.client.am is 
 correct, and the implementation in org.apache.derby.impl.jdbc is incorrect. 

-- 
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

[jira] Commented: (DERBY-1516) Inconsistent behavior for getBytes and getSubString for embedded versus network

[Craig Russell (JIRA)]

[ 
http://issues.apache.org/jira/browse/DERBY-1516?page=comments#action_12424448 ] 

Craig Russell commented on DERBY-1516:
--

Hi Kathey,

Thanks for the feedback on the patch. A few notes:

1. I've added myself to the derby-developers list so I can assign the issue to 
myself.
2. The differences in the master blobclob4BLOB.out files shows the 
inconsistency of the behaviors quite nicely.
3. I can update the master blobclob4BLOB.out files once we agree what we should 
test. What I understand from you is that you would like to see lengths of 0, 
-1, and -76 tested, and the master files updated. But the tests in question 
clobTest6 and blobTest6 are negative tests that look for exceptions. It's not 
clear to me that the test for 0 (which works for the network case) should be 
tested here.
4. I noticed an unusual comment in clobTest6:
// 0 or negative position value
if (isDerbyNet)
System.out.println( negative tests for 
clob.getSubstring won't run  for network server  until 5243 is fixed);
Can you translate this? What's a 5243? 

Thanks,

Craig


 Inconsistent behavior for getBytes and getSubString for embedded versus 
 network
 ---

 Key: DERBY-1516
 URL: http://issues.apache.org/jira/browse/DERBY-1516
 Project: Derby
  Issue Type: Bug
  Components: JDBC
Reporter: Craig Russell
Priority: Minor
 Attachments: DERBY-1516.patch, DERBY-1516.patch


 org.apache.derby.client.am.Clob.getSubString(pos, length) and 
 org.apache.derby.client.am.Blob.getBytes(pos, length) check the length for 
 less than zero. 
 if ((pos = 0) || (length  0)) {
 throw new SqlException(agent_.logWriter_, Invalid position  
 + pos +  or length  + length);
 But org.apache.derby.impl.jdbc.EmbedClob(pos, length) and 
 org.apache.derby.impl.jdbc.EmbedBlob(pos, length) check the length for less 
 than or equal to zero.
if (length = 0)
 throw Util.generateCsSQLException(
 SQLState.BLOB_NONPOSITIVE_LENGTH, new Integer(length));
 The specification does not disallow length of zero, so zero length should be 
 allowed. I believe that the implementation in org.apache.derby.client.am is 
 correct, and the implementation in org.apache.derby.impl.jdbc is incorrect. 

-- 
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

[jira] Updated: (DERBY-1516) Inconsistent behavior for getBytes and getSubString for embedded versus network

[Craig Russell (JIRA)]

 [ http://issues.apache.org/jira/browse/DERBY-1516?page=all ]

Craig Russell updated DERBY-1516:
-

Attachment: DERBY-1516.patch

This patch applies to the trunk. I've updated the tests and canon for this 
change as well. 

 Inconsistent behavior for getBytes and getSubString for embedded versus 
 network
 ---

 Key: DERBY-1516
 URL: http://issues.apache.org/jira/browse/DERBY-1516
 Project: Derby
  Issue Type: Bug
  Components: JDBC
Reporter: Craig Russell
Priority: Minor
 Attachments: DERBY-1516.patch, DERBY-1516.patch


 org.apache.derby.client.am.Clob.getSubString(pos, length) and 
 org.apache.derby.client.am.Blob.getBytes(pos, length) check the length for 
 less than zero. 
 if ((pos = 0) || (length  0)) {
 throw new SqlException(agent_.logWriter_, Invalid position  
 + pos +  or length  + length);
 But org.apache.derby.impl.jdbc.EmbedClob(pos, length) and 
 org.apache.derby.impl.jdbc.EmbedBlob(pos, length) check the length for less 
 than or equal to zero.
if (length = 0)
 throw Util.generateCsSQLException(
 SQLState.BLOB_NONPOSITIVE_LENGTH, new Integer(length));
 The specification does not disallow length of zero, so zero length should be 
 allowed. I believe that the implementation in org.apache.derby.client.am is 
 correct, and the implementation in org.apache.derby.impl.jdbc is incorrect. 

-- 
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

[jira] Created: (DERBY-1516) Inconsistent behavior for getBytes and getSubString for embedded versus network

[Craig Russell (JIRA)]

Inconsistent behavior for getBytes and getSubString for embedded versus network
---

 Key: DERBY-1516
 URL: http://issues.apache.org/jira/browse/DERBY-1516
 Project: Derby
  Issue Type: Bug
Reporter: Craig Russell
Priority: Minor


org.apache.derby.client.am.Clob.getSubString(pos, length) and 
org.apache.derby.client.am.Blob.getBytes(pos, length) check the length for less 
than zero. 

if ((pos = 0) || (length  0)) {
throw new SqlException(agent_.logWriter_, Invalid position  + 
pos +  or length  + length);

But org.apache.derby.impl.jdbc.EmbedClob(pos, length) and 
org.apache.derby.impl.jdbc.EmbedBlob(pos, length) check the length for less 
than or equal to zero.

   if (length = 0)
throw Util.generateCsSQLException(
SQLState.BLOB_NONPOSITIVE_LENGTH, new Integer(length));

The specification does not disallow length of zero, so zero length should be 
allowed. I believe that the implementation in org.apache.derby.client.am is 
correct, and the implementation in org.apache.derby.impl.jdbc is incorrect. 


-- 
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

[jira] Commented: (DERBY-587) Providing JDBC 4.0 support for derby

[Craig Russell (JIRA)]

[ 
http://issues.apache.org/jira/browse/DERBY-587?page=comments#action_12356471 ] 

Craig Russell commented on DERBY-587:
-

I recognize that this is an internal project issue, but:

Doesn't the grant Apache license check box on the patch upload mean anything? 
I always assumed that it allowed someone without an ICLA on file to contribute 
code. The patches uploaded for this bug have the grant Apache license 
checked...Of course, the copyright notices are required in the code for new 
files.

Craig

 Providing JDBC 4.0 support for derby
 

  Key: DERBY-587
  URL: http://issues.apache.org/jira/browse/DERBY-587
  Project: Derby
 Type: New Feature
   Components: JDBC
 Versions: 10.2.0.0
 Reporter: V.Narayanan
 Assignee: V.Narayanan
 Priority: Minor
  Fix For: 10.2.0.0
  Attachments: jdbc4.0.sxw, jdbc4.diff



-- 
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

[jira] Commented: (DERBY-587) Providing JDBC 4.0 support for derby

[Craig Russell (JIRA)]

[ 
http://issues.apache.org/jira/browse/DERBY-587?page=comments#action_12356473 ] 

Craig Russell commented on DERBY-587:
-

I have just a few comments.

1. The code does not appear to be consistent with regard to spaces, tabs, 
indents, and which line the { appears on. Are there coding standards to which 
we try to hold contributions? Other projects recognize that some files in the 
same project use different coding styles (standards?) but try to maintain 
standards for new files. The rule is that patches to existing files use the 
conventions already used, but new files have a standard approach. Are there any 
such standards for Derby?

2. The copyright notices definitely need to be there for this contribution.

3. I agree that for a contribution of this magnitude, a signed ICLA should be a 
requirement.

4. In response to Dan's comments immediately above, I'd think that the Apache 
board might want to discuss why the JIRA has a check box for contributions. If 
it's really irrelevant, it's certainly a distraction.




 Providing JDBC 4.0 support for derby
 

  Key: DERBY-587
  URL: http://issues.apache.org/jira/browse/DERBY-587
  Project: Derby
 Type: New Feature
   Components: JDBC
 Versions: 10.2.0.0
 Reporter: V.Narayanan
 Assignee: V.Narayanan
 Priority: Minor
  Fix For: 10.2.0.0
  Attachments: jdbc4.0.sxw, jdbc4.diff



-- 
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

[jira] Created: (DERBY-581) Modify SQL to skip N rows of the result and return the next M rows

[Craig Russell (JIRA)]

Modify SQL to skip N rows of the result and return the next M rows
--

 Key: DERBY-581
 URL: http://issues.apache.org/jira/browse/DERBY-581
 Project: Derby
Type: New Feature
  Components: Unknown  
 Environment: All
Reporter: Craig Russell
Priority: Minor


I agree that the information should be expressed in SQL so that the query 
optimized and execution strategy can know what the user needs in terms of 
cardinality.

I'd also like to ask that when we consider extending the SQL in this manner we 
consider skipping the first N rows and returning the next M rows.

Craig

On Sep 20, 2005, at 10:19 AM, Suavi Ali Demir wrote:

Another little detail about optimization is that Statement.setMaxRows() kind of 
functions on the JDBC side may not be sufficient since it is called after SQL 
statement is prepared and returned as an object (after query plan is built). 
Therefore, it may be necessary to have language syntax to indicate the 
intention to fetch first 1000 rows only, so that when the query is prepared, 
this intention can be taken into account.
Regards,
Ali

Mike Matrigali [EMAIL PROTECTED] wrote:
As craig points out it is important in performance testing to say
exactly what you are measuring. In general Derby will try to
stream rows to the user before it has finished looking at all rows.
So often looking at the first row will and stopping will mean that
many rows have not been processed. BUT when an order by is involved
and the query plan either has no appropriate matching index, or decides
to use a different index then all the rows are processed, then they are
sent to the sorter and finally after all rows are processed they are
streamed to the client.

So as you have seen reading the first 1000 rows of a much larger data
set can happen very quickly.

As subsequent mail threads have pointed out, returning the top 1000
sorted rows is an interesting problem which could be costed and executed
differently if that information was pushed into the optimizer and the
sorter (and medium level projects were done in those areas).


 On Sep 16, 2005, at 4:42 PM, Scott Ogden wrote:
 
 
 
 I have observed some interesting query performance behavior and am
 hoping someone here can explain. 
 
 In my scenario, it appears that an existing index is not being used for
 the 'order by' part of the operation and as a result the perfo rmance of
 certain queries is suffering. Can someone explain if this is supposed
 to be what is happening and why? Please see below for the specific
 queries and their performance characteristics. 
 
 Here are the particulars:
 
 -
 
 create table orders(
 
 order_id varchar(50) NOT NULL
 
 CONSTRAINT ORDERS_PK PRIMARY KEY,
 
 amount numeric(31,2),
 
 time date,
 
 inv_num varchar(50),
 
 line_num varchar(50),
 
 phone varchar(50),
 
 prod_num varchar(50));
  --Load a large amount of data (720,000 records) into the 'orders' table
  
 --Create an index on the time column as that will be used i n the 'where'
 clause.
 
 create index IX_ORDERS_TIME on orders(time);
  
 --When I run a query against this table returning top 1,000 records,
 this query returns very quickly, consistently less than .010 seconds.
 

 select * from orders

 where time  '10/01/2002' and time  '11/30/2002'

 order by time;

 --Now run a similarly query against same table, returning the top
 1,000 records.

 --The difference is that the results are now sorted by the primary key
 ('order_id') rather than 'time'. 

 --This query returns slowly, approximately 15 seconds. Why??

 select * from orders

 where time  '10/01/2002' and time  '11/30/2002'

 order by order_id;

 --Now run a third query against the same 'orders' table, removing the
 where clause

 --This query returns quickly, around .010 seconds. 

 

 select * from orders

 order by order_id;



-- 
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

Patch tool acts strange

[Craig Russell]

Hi Philip,Philip Wilder (JIRA) wrote:- The SVN patch tool seems to act very strangely for updatableResultSet.out, deleting then adding lines that were identical. I cannot account for this behavior.In my experience, this is due to a white space change. For example, adding or removing a blank, or replacing a tab with blanks. There is a change, just not easily viewed with the naked eye.Many "diff" editors will allow you to "enable white space diff" so you can see the changes.Craig Craig Russell Architect, Sun Java Enterprise System http://java.sun.com/products/jdo 408 276-5638 mailto:[EMAIL PROTECTED] P.S. A good JDO? O, Gasp!  

Re: Increase acceptance of Derby across Apache??

[Craig Russell]

Hi,The Apache JDO project is as open as can be, but we just haven't had the visibility in the incubator. This is mostly an administrative issue. We just yesterday posted the http://incubator.apache.org/jdo web site, although our wiki has been active for several months. Just as you might have trouble following the discussions on jdo-dev, I have a hard time keeping up with derby-dev. I'm only on the derby-dev-digest so I will miss things. If there is ever an issue that you think needs attention, please feel free to copy me directly at mailto:[EMAIL PROTECTED]. I have an apache email address but haven't had time to have it automatically forward to my sun account. :-(If it is not already well known, Apache JDO depends on Derby and we're keen on having Derby become "The Database Of Choice For Java Applications". And we're keen to do anything we can do to advance this cause.One area that we want to address is to become more aligned with Geronimo and Derby, by having a proper JDO implementation that will co-exist with EJB3 in Geronimo, using Derby for persistence.CraigFrom: Daniel John Debrunner [EMAIL PROTECTED]Date: July 15, 2005 8:43:01 AM PDTTo: Derby Development derby-dev@db.apache.orgSubject: Re: Increase acceptance of Derby across Apache??Jean T. Anderson wrote:Daniel John Debrunner wrote:I had that on my list to look at, but they seem to be the "not so open"Apache JDO project. Do they have a site at apache?I don't spot a site yet, but the jdo-dev@db.apache.org list is quiteactive -- and open. (Truthfully, I'm only intermittently lurking, butthey have enough activity on their list I can't keep up with it.)Found it, in the obvious place :-)http://incubator.apache.org/jdo/It is known that the project is not listed at the incubator site yet.Dan. Craig Russell Architect, Sun Java Enterprise System http://java.sun.com/products/jdo 408 276-5638 mailto:[EMAIL PROTECTED] P.S. A good JDO? O, Gasp!  

smime.p7s
Description: S/MIME cryptographic signature

Connection string log messages

[Craig Russell]

Hi David,My only issue is with the term "Physical Connection".Connections are logical, not physical. Ethernet cables, adapters, wires, RAM, disk drives are physical. Ports, sockets, connections, database tables and columns are logical.I really don't like using physical to describe the lowest level of connection. Isn't there another term that conveys the correct information, like Hard Connection, or Socket Connection, Port Connection, or simply Connection?CraigHere is an adjusted proposal, taking into account Kathey's *and Dan's* comments :)[ ] VOTE on toString() format for Derby embedded connections(a) Physical Connections, Pooled Connections (EmbedConnection, EmbedConnection30, BrokeredConnection, BrokeredConnection30):    classname@hashcode (DATABASE = dbname), (SESSIONID = sessionid), (DRDAID = drdaid), (XID = xid)Example:  [EMAIL PROTECTED] (DATABASE = wombat), (SESSIONID = 1), (DRDAID = null), (XID = 105)(b) Pooled Connections (EmbedPooledConnection, EmbedXAConnection):    classname@hashcode (ID=uniqueid), Physical Connection=underlying connection stringExample: [EMAIL PROTECTED] (ID=2), Physical Connection = [EMAIL PROTECTED] (DATABASE = wombat), (SESSIONID = 1), (DRDAID = null), (XID = 105) Craig Russell Architect, Sun Java Enterprise System http://java.sun.com/products/jdo 408 276-5638 mailto:[EMAIL PROTECTED] P.S. A good JDO? O, Gasp!  

smime.p7s
Description: S/MIME cryptographic signature

Re: Connection string log messages

[Craig Russell]

Hi David,I don't need to use the term; I can just call it a "Connection".   This doesn't really impact the resulting code, just what we put into the comments and the JIRA bug.I understand. Perhaps it would be more useful to avoid "physical" and use either Embedded or Network, as in "Network Connection" and "Embedded Connection".BTW, if you want to get picky, nothing is "physical" -- from the perspective of quantum physics, it's all a mass of probability.  :)The point is that I am picky about terms. :-) And let me know next time you stub your toe on a bit of probability...CraigDavid Craig Russell Architect, Sun Java Enterprise System http://java.sun.com/products/jdo 408 276-5638 mailto:[EMAIL PROTECTED] P.S. A good JDO? O, Gasp!  

smime.p7s
Description: S/MIME cryptographic signature

Re: Auto generation of database keys

[Craig Russell]

Hi Mike,Thanks for the info. It looks like I'll have to create a JDBC test case. I'll let you know when I have a test case that I can show the problem on.Currently the test is a JUnit test that is written to access Derby through JDBC, JDO, and JPOX, so it's not exactly an easy case to reproduce in a small environment. I was hoping to get some ideas without having to construct a JDBC test case.Thanks,CraigOn Jun 14, 2005, at 12:29 PM, [EMAIL PROTECTED] wrote:From: Mike Matrigali [EMAIL PROTECTED] Date: June 14, 2005 10:45:16 AM PDT To: Derby Development derby-dev@db.apache.org Subject: Re: Fwd: Auto generation of database keys   I have not been able to reproduce this issue.  Derby uses nested internal transactions when updating the IDENTITY column, it does not try to guarantee that a value won't be lost.  These locks should not be held until end of user transaction, they are committed separate from the user transaction, so the lock is held for the time it takes to update the system catalog row and commit the internal transaction.  There are some user level things the other transaction could be doing to block this:     o other transaction did ddl on this table and has not committed.      o other transaction did some sort of metadata select either       explicitly against derby system catalog or through jdbc       metadata call.  I assume you have not set any of the lock timeout properties, it is definitely possible to block on this lock for a short time so setting locktimeout to -1 will also cause this issue, but default timeout should not be happening.  The best 1st step to debugging locking issues is to use the properties to get the system to dump out more information about the lock table when you encounter the lock timeout.  Try setting derby.locks.monitor=true.  In this case the interesting information is what locks are being held by the other transaction blocking this one.  On a side note, what jvm/derby version are you using.  Getting stack traces with line numbers can help out a lot in debugging this stuff. I don't know if I get them because I am using a development build, or if it is the jvm environment I have.  This should have nothing to do with the lock timeout, just makes it easier to diagnose the issue.  Craig Russell wrote:  Hi,  I'm running into a locking issue when using generated keys. My primary key column is defined as DATASTORE_IDENTITY BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY. I don't care about the key being transactional. That is, if a transaction rolls back, I can live with the key that was allocated being permanently unused.  My application has two transactions inserting rows into the same table, and the threads have internal synchronization such that I need to have both insert statements succeed independently. The isolation level is the default.   When I run the transactions, I get a timeout exception indicating that only one of the transactions can get an autogenerated key and the other has to wait until the first transaction commits. This stack trace is from the transaction that is waiting for the first transaction to commit.       [java] ERROR 40XL1: A lock could not be obtained within the time requested      [java]      at org.apache.derby.iapi.error.StandardException.newException(StandardExcep tion.java)      [java]      at org.apache.derby.impl.services.locks.LockSet.lockObject(LockSet.java)      [java]      at org.apache.derby.impl.services.locks.SinglePool.lockAnObject(SinglePool. java)      [java]      at org.apache.derby.impl.services.locks.SinglePool.lockObject(SinglePool.ja va)      [java]      at org.apache.derby.impl.store.raw.xact.RowLocking3.lockRecordForWrite(RowL ocking3.java)      [java]      at org.apache.derby.impl.store.access.conglomerate.OpenConglomerate.lockPos itionForWrite(OpenConglomerat e.java)      [java]      at org.apache.derby.impl.store.access.conglomerate.GenericConglomerateContr oller.fetch(GenericConglomera teController.java)      [java]      at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getSetAutoincrement Value(DataDictionaryImpl.java )      [java]      at org.apache.derby.impl.sql.execute.InsertResultSet.getSetAutoincrementVal ue(InsertResultSet.java)      [java]      at org.apache.derby.impl.sql.execute.BaseActivation.getSetAutoincrementValu e(BaseActivation.java)      [java]      at org.apache.derby.exe.ac40348015x0104x675cxbca4xdab5f0bf0.e0(Unknown Source)      [java]      at org.apache.derby.impl.services.reflect.DirectCall.invoke(ReflectGenerate dClass.java)      [java]      at org.apache.derby.impl.sql.execute.RowResultSet.getNextRowCore(RowResultS et.java)      [java]      at org.apache.derby.impl.sql.execute.NormalizeResultSet.getNextRowCore(Norm alizeResultSet.java)      [java]      at org.apache.derby.impl.sql.execute.DMLWriteResultSet.getNextRowCore(DMLWr iteResultSet.java)      [java]      at org.apache.derby.impl.sql.execute.InsertResultSet.open(InsertResultSet.j ava)      [java

Fwd: Auto generation of database keys

[Craig Russell]

Hi,I'm running into a locking issue when using generated keys. My primary key column is defined as DATASTORE_IDENTITY BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY. I don't care about the key being transactional. That is, if a transaction rolls back, I can live with the key that was allocated being permanently unused.My application has two transactions inserting rows into the same table, and the threads have internal synchronization such that I need to have both insert statements succeed independently. The isolation level is the default. When I run the transactions, I get a timeout exception indicating that only one of the transactions can get an autogenerated key and the other has to wait until the first transaction commits. This stack trace is from the transaction that is waiting for the first transaction to commit.     [java] ERROR 40XL1: A lock could not be obtained within the time requested     [java]      at org.apache.derby.iapi.error.StandardException.newException(StandardException.java)     [java]      at org.apache.derby.impl.services.locks.LockSet.lockObject(LockSet.java)     [java]      at org.apache.derby.impl.services.locks.SinglePool.lockAnObject(SinglePool.java)     [java]      at org.apache.derby.impl.services.locks.SinglePool.lockObject(SinglePool.java)     [java]      at org.apache.derby.impl.store.raw.xact.RowLocking3.lockRecordForWrite(RowLocking3.java)     [java]      at org.apache.derby.impl.store.access.conglomerate.OpenConglomerate.lockPositionForWrite(OpenConglomerate.java)     [java]      at org.apache.derby.impl.store.access.conglomerate.GenericConglomerateController.fetch(GenericConglomerateController.java)     [java]      at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getSetAutoincrementValue(DataDictionaryImpl.java)     [java]      at org.apache.derby.impl.sql.execute.InsertResultSet.getSetAutoincrementValue(InsertResultSet.java)     [java]      at org.apache.derby.impl.sql.execute.BaseActivation.getSetAutoincrementValue(BaseActivation.java)     [java]      at org.apache.derby.exe.ac40348015x0104x675cxbca4xdab5f0bf0.e0(Unknown Source)     [java]      at org.apache.derby.impl.services.reflect.DirectCall.invoke(ReflectGeneratedClass.java)     [java]      at org.apache.derby.impl.sql.execute.RowResultSet.getNextRowCore(RowResultSet.java)     [java]      at org.apache.derby.impl.sql.execute.NormalizeResultSet.getNextRowCore(NormalizeResultSet.java)     [java]      at org.apache.derby.impl.sql.execute.DMLWriteResultSet.getNextRowCore(DMLWriteResultSet.java)     [java]      at org.apache.derby.impl.sql.execute.InsertResultSet.open(InsertResultSet.java)     [java]      at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java)     [java]      at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java)     [java]      at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(EmbedPreparedStatement.java)     [java]      at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(EmbedPreparedStatement.java)     [java]      at org.jpox.store.rdbms.request.Request.executeUpdate(Request.java:69)     [java]      at org.jpox.store.rdbms.request.InsertRequest.execute(InsertRequest.java:253)     [java]      at org.jpox.store.rdbms.table.ClassTable.insert(ClassTable.java:1673)     [java]      at org.jpox.store.StoreManager.insert(StoreManager.java:634)     [java]      at org.jpox.state.StateManagerImpl.internalMakePersistent(StateManagerImpl.java:2940)     [java]      at org.jpox.state.StateManagerImpl.makePersistent(StateManagerImpl.java:2913)Am I misusing the key generation? Can I get nontransactional key generation?Thanks,CraigCraig RussellArchitect, Sun Java Enterprise System http://java.sun.com/products/jdo408 276-5638 mailto:[EMAIL PROTECTED]P.S. A good JDO? O, Gasp!   Craig Russell Architect, Sun Java Enterprise System http://java.sun.com/products/jdo 408 276-5638 mailto:[EMAIL PROTECTED] P.S. A good JDO? O, Gasp!  

smime.p7s
Description: S/MIME cryptographic signature

Fwd: [PATCH] MacOS X BUILDING.txt

[Craig Russell]

Hi Andrew,

The changes look great. It would be nice if this makes it into the 10.1 release!

Thanks,

Craig

Begin forwarded message:

From: Andrew McIntyre [EMAIL PROTECTED]>
Date: May 19, 2005 8:13:15 AM PDT
To: Craig Russell [EMAIL PROTECTED]>
Subject: Re: [PATCH] MacOS X BUILDING.txt

Hi Craig,

Here 'tis:



macosx-building.diff
Description: Binary data
andrew


On May 19, 2005, at 7:30 AM, Craig Russell wrote:

Hi Andrew,

I missed your attached edits. I just looked in the digest (I don't yet have the energy to subscribe to the alias itself ;-) but couldn't find your attachment.

Could you send it directly to me?

Thanks,

Craig

From: Andrew McIntyre [EMAIL PROTECTED]>
Date: May 19, 2005 1:02:18 AM PDT
To: Derby Development derby-dev@db.apache.org>
Subject: Re: [PATCH] MacOS X BUILDING.txt



On May 18, 2005, at 3:20 PM, Craig Russell wrote:

Thanks. I've incorporated the information from DERBY-1 into the proposed update to the BUILDING.txt, which you will find attached. The patch is based on revision 170836.


Thanks for the patch, Craig. I've expanded on your original patch to include some notes on JDK 1.5 and Mac OS X 10.4, so please take a look at my attached edits. This patch has also been merged with other recent edits to BUILDING.txt. If you have no additions or objections, I'll commit this on Friday.

andrew

Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:[EMAIL PROTECTED]
P.S. A good JDO? O, Gasp!
Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:[EMAIL PROTECTED]
P.S. A good JDO? O, Gasp!


smime.p7s
Description: S/MIME cryptographic signature

[PATCH] MacOS X BUILDING.txt

[Craig Russell]

From: Andrew McIntyre [EMAIL PROTECTED]>
Date: May 17, 2005 8:03:22 PM PDT
To: Derby Development derby-dev@db.apache.org>
Subject: Re: Mac OSX build requirements



On May 17, 2005, at 2:19 PM, Craig Russell wrote:

I would be happy to write up a patch for BUILDING.txt or wherever it should be.

I just happen to be looking at some build/documentation related items right now. I think it would be fine to put this information into BUILDING, since it is key to building Derby on Mac OS X. If you produce a patch, I'll review and commit it.

Once you have it built, you'll probably want to look over DERBY-1 if you haven't already:

http://issues.apache.org/jira/browse/DERBY-1

Thanks. I've incorporated the information from DERBY-1 into the proposed update to the BUILDING.txt, which you will find attached. The patch is based on revision 170836.

Thanks,

Craig
andrew



MacOSX-BUILDING.diff
Description: Binary data


Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:[EMAIL PROTECTED]
P.S. A good JDO? O, Gasp!

smime.p7s
Description: S/MIME cryptographic signature

Mac OSX build requirements

[Craig Russell]

Hi,
It took me just a bit of scratching to find the secret of building  
Derby in MacOS X. The BUILDING.txt file has nothing about it, and it  
took me a bit of work to find the post  
http://nagoya.apache.org/eyebrowse/ReadMsg?listName=derby- 
[EMAIL PROTECTED]msgNo=31 that explains enough of what I needed to  
do. It was clear enough that I could run derby on MacOS X but it wasn't  
obvious that I could develop on it.

I'd like to see the MacOS X information right up front, because I spent  
a bit of time trying to figure out how to get hold of a JDK 1.3.1  
environment, which isn't even necessary for MacOS X because when you  
install 1.4, you also get the 1.3.1 environment for Derby.

Is there already a place for this kind of operating-system-dependent  
information? If not, would BUILDING.txt be a good place for it?

I would be happy to write up a patch for BUILDING.txt or wherever it  
should be.

Thanks,
Craig
Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:[EMAIL PROTECTED]
P.S. A good JDO? O, Gasp!

smime.p7s
Description: S/MIME cryptographic signature

[jira] Commented: (DERBY-214) Remove System.exit() calls from the DB2jServerImpl.java

[Craig Russell (JIRA)]

 [ 
http://issues.apache.org/jira/browse/DERBY-214?page=comments#action_64106 ]
 
Craig Russell commented on DERBY-214:
-

Hi,

I agree that main() should only be used from the command line, and is the only 
place in the code where System.exit() should be called. The main() method could 
simply delegate to another method that takes the String args[] parameter. For 
the purposes of this message, I'll just call it execute. 

All of the utility programs should use the same method name for the execute 
method so it's easy to remember how to invoke it. The execute then does the 
parsing of the args. There are lots of Apache commons arg parsers that could be 
used in the execute, but that's just a bit off topic.

Execute should throw exceptions, not ever call System.exit. Since the execute 
method has the same parameters as main, it's easy to remember how to invoke it. 

The second issue is whether the execute method should be static or not. For 
consistency, I think that non-static would be the way to go. If we offer 
different methods of executing, then it would be nice to have all of them 
behave the same in terms of static vs. non-static. 

The third issue is where the processing of environment variables should be 
done. It might be best if the constructor of an instance of the utility class 
processed the environment variables. That way, regardless of whether main() or 
a program  called the constructor the behavior would be the same.

The fourth issue is whether for ease of use, aother convenience methods could 
be used. One that takes a single String is desirable. This method, also called 
execute, could simply parse the input String into a String[] and call the other 
execute method. An alternative method would take a Map of parameter names and 
values with the obvious semantics.

I see a few alternatives

1. ij.execute(new String[] {{-p, 1567, -h, localhost }); 
2. ij.setPort(1567); ij.setHost(localhost);ij.execute();
3. ij.execute(-p 1567 -h localhost);
4. Map map = new HashMap(); map.put(-p, 1567); map.put(-h, localhost);  
ij.execute(Map map);

I can see value in all of these techniques depending on what you have on the 
caller side of the interface. 

Regards,

Craig

 Remove System.exit() calls from the DB2jServerImpl.java
 ---

  Key: DERBY-214
  URL: http://issues.apache.org/jira/browse/DERBY-214
  Project: Derby
 Type: Bug
   Components: Network Server
 Versions: 10.1.0.0
 Reporter: Kathey Marsden
 Assignee: David Van Couvering


 The System.exit() calls needs to be removed from the 
 DB2jServerImpl.java as this results in the entire application 
 (example - Eclipse which is running the Network Server inside 
 it) getting shut down.

-- 
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