Return-Path: Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: (qmail 47601 invoked from network); 24 Jan 2008 16:26:56 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 24 Jan 2008 16:26:56 -0000 Received: (qmail 26684 invoked by uid 500); 24 Jan 2008 16:26:46 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 26644 invoked by uid 500); 24 Jan 2008 16:26:46 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 26635 invoked by uid 99); 24 Jan 2008 16:26:46 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 24 Jan 2008 08:26:46 -0800 X-ASF-Spam-Status: No, hits=-100.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO brutus.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 24 Jan 2008 16:26:28 +0000 Received: from brutus (localhost [127.0.0.1]) by brutus.apache.org (Postfix) with ESMTP id C630471425F for ; Thu, 24 Jan 2008 08:26:34 -0800 (PST) Message-ID: <430112.1201191994807.JavaMail.jira@brutus> Date: Thu, 24 Jan 2008 08:26:34 -0800 (PST) From: "A B (JIRA)" To: derby-dev@db.apache.org Subject: [jira] Commented: (DERBY-3301) Incorrect result from query with nested EXIST In-Reply-To: <12184152.1199386354111.JavaMail.jira@brutus> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-Virus-Checked: Checked by ClamAV on apache.org [ https://issues.apache.org/jira/browse/DERBY-3301?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12562121#action_12562121 ] A B commented on DERBY-3301: ---------------------------- > Maybe I misinterpreted his comment? Yes, I think so. But probably more my fault than anything. What I meant to say was that I expect any patch which completely disables EXISTS subquery flattening to theoretically cause at least one test in the regression suite to fail. If that's not true--i.e. if all tests pass with such a patch--then it means that there are no existing test cases to verify that EXISTS subquery flattening is actually occuring, and that would warrant a separate (testing) issue. But for your 3b patch, you are *NOT* disabling EXISTS subquery flattening altogether (as discussed earlier), so it's not surprising that existing tests run without error. That said, I do think it would be good to add a new regression test to correspond to the specific patch that you've posted--ex. a new test case based on the attached repro would be good. If that's still unclear, then the short version is "Yes, I think a new test case (or set of test cases) should be added for this issue". A JUnit test based on the repro would be ideal. Apologies for any confusion cause by my earlier comment. > 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.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.