[ https://issues.apache.org/jira/browse/DERBY-3301?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12561036#action_12561036 ]
Bryan Pendleton commented on DERBY-3301: ---------------------------------------- Perhaps there are some clues in this section of the manual: http://db.apache.org/derby/docs/10.3/tuning/ctuntransform13699.html In particular, this section: http://db.apache.org/derby/docs/10.3/tuning/ctuntransform36368.html I find it interesting that there are two levels of subquery flattening going on here; that is, we have a subquery in the where clause of a subquery which is itself in the where clause of the top-level query. Perhaps the flattening logic is evaluating the correctness rules properly for a single level of flattening but isn't handling flattening-within-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 > > > ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > 1 |emp1First |emp1Last > |emp1Middle |1970-06-09 21:00:00.0 |NULL |NULL > |NULL > |NULL|NULL |NULL > |1998-12-31 21:00:00.0 |40.0 > |NULL |NULL |NULL |NULL |NULL |20000.0 > |NULL |org.apache.jdo.tck.pc.company.FullTimeEmployee > > 2 |emp2First |emp2Last > |emp2Middle |1975-12-21 21:00:00.0 |NULL |NULL > |NULL > |NULL|NULL |NULL > |2003-06-30 21:00:00.0 |40.0 > |NULL |NULL |NULL |NULL |NULL |10000.0 > |NULL |org.apache.jdo.tck.pc.company.FullTimeEmployee > > 3 |emp3First |emp3Last > |emp3Middle |1972-09-04 21:00:00.0 |NULL |NULL > |NULL > |NULL|NULL |NULL > |2002-08-14 21:00:00.0 |19.0 > |NULL |NULL |NULL |NULL |NULL |NULL > |15.0 |org.apache.jdo.tck.pc.company.PartTimeEmployee > > 4 |emp4First |emp4Last > |emp4Middle |1973-09-05 21:00:00.0 |NULL |NULL > |NULL > |NULL|NULL |NULL > |2001-04-14 21:00:00.0 |0.0 > |NULL |NULL |NULL |NULL |NULL |NULL > |13.0 |org.apache.jdo.tck.pc.company.PartTimeEmployee > > 5 |emp5First |emp5Last > |emp5Middle |1962-07-04 21:00:00.0 |NULL |NULL > |NULL > |NULL|NULL |NULL > |1998-08-14 21:00:00.0 |0.0 > |NULL |NULL |NULL |NULL |NULL |45000.0 > |NULL |org.apache.jdo.tck.pc.company.FullTimeEmployee > > 5 rows selected > ij> select * from projects; > PROJID |NAME |BUDGET |DISCRIMINATOR > > > ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > 1 |orange |2500000.99 > |org.apache.jdo.tck.pc.company.Project > > 2 |blue |50000.00 > |org.apache.jdo.tck.pc.company.Project > > 3 |green |2000.99 > |org.apache.jdo.tck.pc.company.Project > > 3 rows selected > ij> select * from project_member; > PROJID |MEMBER > ----------------------- > 2 |3 > 1 |3 > 2 |2 > 3 |5 > 3 |4 > 1 |2 > 1 |1 > 7 rows selected -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.