[
https://issues.apache.org/jira/browse/DERBY-3301?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Thomas Nielsen updated DERBY-3301:
----------------------------------
Attachment: derby-3301-extra.sql
derby-3301-5.diff
Very sorry for the derbyall hickup :|
Attaching
- updated patch 'derby-3301-5.diff' which uses a CollectNodesVisitor to find
all lower SubqueryNodes as pointed out by Army.
- extra sql script in 'derby-3301-extra.sql' containig Craigs additional
queries modified to work with the original repro script schema sql'.
With the 5 patch applied both the original query and the additional queries
Craig posted return as expected. See output below.
I still need to
- have another look at Armys latest variant, as it doesn't seem to end up with
a similar querytree to the others (i.e no SubqueryNodes in the WHERE clause).
- modify the master output for lang/subqueryFlattening.sql.
ij>
select unbound_e.empid, unbound_p.projid
from departments this,
employees unbound_e,
projects unbound_p
where exists (
select 1 from employees this_employees_e
where exists (
select 1 from project_employees this_employees_e_projects_p
where this_employees_e_projects_p.empid = this_employees_e.empid
and this_employees_e.department = this.id
and unbound_p.projid = this_employees_e_projects_p.projid
and unbound_e.empid = this_employees_e.empid)
);
EMPID |PROJID
-----------------------
11 |101
12 |101
13 |101
12 |102
13 |102
14 |103
15 |103
7 rows selected
---
Craigs additional queries:
ij>
SELECT UNBOUND_E.empid FROM DEPARTMENTS THIS , employees UNBOUND_E
WHERE EXISTS (
SELECT 1 FROM employees THIS_EMPLOYEES_E
WHERE THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID AND UNBOUND_E.empid =
THIS_EMPLOYEES_E.empid AND THIS.ID = 2);
EMPID
-----------
14
15
2 rows selected
ij>
SELECT THIS.ID,UNBOUND_E.empid,UNBOUND_P.PROJID FROM DEPARTMENTS THIS ,
employees UNBOUND_E , projects UNBOUND_P
WHERE EXISTS (
SELECT 1 FROM employees THIS_EMPLOYEES_E
WHERE EXISTS (
SELECT 1 FROM project_employees THIS_EMPLOYEES_E_PROJECTS_P
WHERE THIS_EMPLOYEES_E_PROJECTS_P."EMPID" = THIS_EMPLOYEES_E.empid
AND UNBOUND_P.PROJID = THIS_EMPLOYEES_E_PROJECTS_P.PROJID
AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID
AND UNBOUND_E.empid = THIS_EMPLOYEES_E.empid
));
ID |EMPID |PROJID
-----------------------------------
1 |11 |101
1 |12 |101
1 |13 |101
1 |12 |102
1 |13 |102
2 |14 |103
2 |15 |103
7 rows selected
ij>
SELECT UNBOUND_E.empid,UNBOUND_P.PROJID FROM DEPARTMENTS THIS , employees
UNBOUND_E , PROJECTS UNBOUND_P
WHERE EXISTS (
SELECT 1 FROM employees THIS_EMPLOYEES_E
WHERE EXISTS (
SELECT 1 FROM project_employees THIS_EMPLOYEES_E_PROJECTS_P
WHERE THIS_EMPLOYEES_E_PROJECTS_P."EMPID" = THIS_EMPLOYEES_E.empid
AND UNBOUND_P.PROJID = THIS_EMPLOYEES_E_PROJECTS_P.PROJID
AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID
AND UNBOUND_E.empid = THIS_EMPLOYEES_E.empid
AND THIS.ID = 1));
EMPID |PROJID
-----------------------
11 |101
12 |101
13 |101
12 |102
13 |102
5 rows selected
ij>
SELECT UNBOUND_E.empid,UNBOUND_P.PROJID FROM DEPARTMENTS THIS , employees
UNBOUND_E , PROJECTS UNBOUND_P
WHERE EXISTS (
SELECT 1 FROM employees THIS_EMPLOYEES_E
WHERE EXISTS (
SELECT 1 FROM project_employees THIS_EMPLOYEES_E_PROJECTS_P
WHERE THIS_EMPLOYEES_E_PROJECTS_P."EMPID" = THIS_EMPLOYEES_E.empid
AND UNBOUND_P.PROJID = THIS_EMPLOYEES_E_PROJECTS_P.PROJID
AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID
AND UNBOUND_E.empid = THIS_EMPLOYEES_E.empid
AND THIS.COMPANYID = 1));
EMPID |PROJID
-----------------------
11 |101
12 |101
13 |101
12 |102
13 |102
14 |103
15 |103
7 rows selected
ij>
> 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.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.
