[
https://issues.apache.org/jira/browse/DERBY-3301?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12563581#action_12563581
]
A B commented on DERBY-3301:
----------------------------
> With the two patches I get a failure in lang/subqueryFlattening.sql.
Good catch, Dyre. I was operating on the assumption that derbyall ran cleanly
with
the 4c patch; I hadn't run it myself.
> From a quick look at the diff it seems like the test dumps a query plan that
> is different
> with the patch
I confirmed that there is one query for which the query plan is different--and
for that specific query,
I think the difference is correct. Namely, the query has nested WHERE EXISTS
subqueries and so,
according to the findings for this issue, cannot be safely flattened. So that
part seems okay.
I then noticed that another query further down in the test _also_ has nested
WHERE EXISTS
subqueries, but that query _is_ in fact flattened into an exists join--which
runs counter to the
findings so far for this issue (i.e. it shouldn't be flattened into an EXISTS
due to the nested
EXISTS subqueries). The fact that the query in subqueryFlattening still
returns correct rows
suggests that maybe there are cases where flattening of EXISTS subqueries is
"safe"--but
that seems beyond the scope of this issue.
I played around with the repro for this issue and was able to write the
following query:
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 1 = 1 and 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)
);
The only difference between this query and the one in derby-3301.sql is that
I've
added a (no-op) predicate "1 = 1" alongside the inner-most EXISTS clause.
Since this creates an AndNode whose left operand is a relational op ("1 = 1")
and
whose right operand is a SubqueryNode, the code in the 4c patch that checks
specifically for a SubqueryNode will not detect it:
+ if (sn.originalWhereClause != null &&
+ sn.originalWhereClause instanceof SubqueryNode) {
+ ...
Since whereClause here is actually an AndNode, not a SubqueryNode, the "if"
statement
above is not triggered, thus the query is incorrectly flattened into an EXISTS
joins. The
end result is that the query only returns 5 rows when it should return 7 (the
extra "1 = 1"
predicate shouldn't affect the results).
I think I mentioned a few comments ago that it might be possible to use a
CollectNodesVisitor
to search the whereClause for any SubqueryNodes. The above example
demonstrates why
such a visitor would be helpful, as opposed to just checking directly for a
SubqueryNode...
So to answer Dyre's question, yes, I think a master update will be needed. But
further
inspection of subqueryFlattening.sql shows that the 4c patch may need a few more
tweaks, after all...
> Incorrect result from query with nested EXIST
> ---------------------------------------------
>
> Key: DERBY-3301
> URL: https://issues.apache.org/jira/browse/DERBY-3301
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.1.6, 10.3.2.1
> Reporter: Craig Russell
> Assignee: Thomas Nielsen
> Attachments: d3301-queryplan.log, derby-3301-1.diff,
> derby-3301-1.stat, derby-3301-2.diff, derby-3301-3.diff, derby-3301-3b.diff,
> derby-3301-4.diff, derby-3301-4b.diff, derby-3301-4b.stat,
> derby-3301-4c.diff, derby-3301-test-1.diff, derby-3301-test-1.stat,
> derby-3301-test-2.diff, derby-3301-test-3.diff, derby-3301-test-3.stat,
> derby-3301.sql
>
>
> Derby returns unexpected results from a query with embedded EXIST clauses.
> The query SQL is generated by the JPOX jdo implementation and is supposed to
> return all of the PERSONS and PROJECTS where there is an entry in the join
> table. This query works as expected when using MySQL.
> Here's the query:
> SELECT UNBOUND_E.PERSONID, UNBOUND_P.PROJID
> FROM applicationidentity0.DEPARTMENTS THIS,
> applicationidentity0.PERSONS UNBOUND_E,
> applicationidentity0.PROJECTS UNBOUND_P
> WHERE EXISTS (
> SELECT 1 FROM applicationidentity0.PERSONS THIS_EMPLOYEES_E
> WHERE EXISTS (
> SELECT 1 FROM applicationidentity0.PROJECT_MEMBER
> THIS_EMPLOYEES_E_PROJECTS_P
> WHERE THIS_EMPLOYEES_E_PROJECTS_P."MEMBER" =
> THIS_EMPLOYEES_E.PERSONID
> AND THIS_EMPLOYEES_E_PROJECTS_P."MEMBER" = THIS_EMPLOYEES_E.PERSONID
> AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID
> AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID
> AND UNBOUND_P.PROJID = THIS_EMPLOYEES_E_PROJECTS_P.PROJID
> AND UNBOUND_E.PERSONID = THIS_EMPLOYEES_E.PERSONID)
> );
> PERSONID |PROJID
> -----------------------
> 3 |1
> 5 |3
> 4 |3
> 2 |1
> 1 |1
> 5 rows selected
> I'm expecting 7 rows to be returned here, one row for each row in the join
> table.
> Here's the schema:
> CREATE TABLE departments (
> ID INTEGER NOT NULL,
> NAME VARCHAR(32) NOT NULL,
> EMP_OF_THE_MONTH INTEGER,
> COMPANYID INTEGER,
> DISCRIMINATOR VARCHAR(255),
> CONSTRAINT DEPTS_COMP_FK FOREIGN KEY (COMPANYID) REFERENCES companies,
> CONSTRAINT DEPTS_PK PRIMARY KEY (ID)
> );
> CREATE TABLE persons (
> PERSONID INTEGER NOT NULL,
> FIRSTNAME VARCHAR(32) NOT NULL,
> LASTNAME VARCHAR(32) NOT NULL,
> MIDDLENAME VARCHAR(32),
> BIRTHDATE TIMESTAMP NOT NULL,
> ADDRID INTEGER,
> STREET VARCHAR(64),
> CITY VARCHAR(64),
> STATE CHAR(2),
> ZIPCODE CHAR(5),
> COUNTRY VARCHAR(64),
> HIREDATE TIMESTAMP,
> WEEKLYHOURS REAL,
> DEPARTMENT INTEGER,
> FUNDINGDEPT INTEGER,
> MANAGER INTEGER,
> MENTOR INTEGER,
> HRADVISOR INTEGER,
> SALARY REAL,
> WAGE REAL,
> DISCRIMINATOR varchar(255) NOT NULL,
> CONSTRAINT PERS_DEPT_FK FOREIGN KEY (DEPARTMENT) REFERENCES departments,
> CONSTRAINT PERS_FUNDDEPT_FK FOREIGN KEY (FUNDINGDEPT) REFERENCES
> departments,
> CONSTRAINT PERS_MANAGER_FK FOREIGN KEY (MANAGER) REFERENCES persons,
> CONSTRAINT PERS_MENTOR_FK FOREIGN KEY (MENTOR) REFERENCES persons,
> CONSTRAINT PERS_HRADVISOR_FK FOREIGN KEY (HRADVISOR) REFERENCES persons,
> CONSTRAINT EMPS_PK PRIMARY KEY (PERSONID)
> );
> CREATE TABLE projects (
> PROJID INTEGER NOT NULL,
> NAME VARCHAR(32) NOT NULL,
> BUDGET DECIMAL(11,2) NOT NULL,
> DISCRIMINATOR VARCHAR(255),
> CONSTRAINT PROJS_PK PRIMARY KEY (PROJID)
> );
> CREATE TABLE project_member (
> PROJID INTEGER REFERENCES projects NOT NULL,
> MEMBER INTEGER REFERENCES persons NOT NULL
> );
> ij> connect
> 'jdbc:derby:/Users/clr/apache/jdo/trunk/tck2/target/database/derby/jdotckdb';
> ij> set schema applicationidentity0;
> 0 rows inserted/updated/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.
