[jira] Commented: (DERBY-3301) Incorrect result from query with nested EXIST
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[jira] Commented: (DERBY-3301) Incorrect result from query with nested EXIST
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
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
[ 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
[ 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
[
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
[ 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
[
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
[ 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
[
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
[
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
[
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
[ 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
[
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
[ 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
[ 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
[
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
[ 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
[
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
[ 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
[
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
[ 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
[ 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
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
[
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
[
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
[ 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
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
[ 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
[
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
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
[jira] Commented: (DERBY-214) Remove System.exit() calls from the DB2jServerImpl.java
[
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
