[
https://issues.apache.org/jira/browse/DERBY-3301?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12562169#action_12562169
]
A B commented on DERBY-3301:
----------------------------
Two comments on the 3b patch.
1) The additional comments in SubqueryNode that describe the new condition for
a flattenable
subquery seem like they might be slightly inverted. The comments say:
/* Values subquery is flattenable if:
* ...
* o It is not a nested WHERE EXISTS subquery in a WHERE EXISTS clause
(DERBY-3301)
*/
Ignoring the conditions which were already there, I read this as:
"A subquery SQ1 is flattenable if it is not a nested WHERE EXISTS subquery in
the WHERE
EXISTS clause of another subquery, SQ2".
Is that a correct reading? If so, I think that if SQ1 *is* a nested WHERE
EXISTS subquery in
the WHERE EXISTS clause of SQ2, then it's technically _SQ2_ that is not
flattenable, not SQ1.
That said, the actual code changes in the new "hasNestedWhereExistsClause()"
look correct,
so I think this is just a comment issue, not a code one. Maybe a better
condition would be:
o Either a) it (the subquery) does not appear within a WHERE clause, or b) it
appears within
a WHERE clause but does not itself contain a WHERE clause with other
subqueries in it.
This definition is quite a bit more wordy, but it matches the documentation and
it also matches
the code in hasNestedWhereExistsClause a bit more closely--though not exactly.
See #2
comment below...
2) The logic that exists in hasNestedWhereExistsClause() does exactly what its
name implies,
i.e. it checks specifically for "nested WHERE EXISTS clauses". That in itself
is good since it
addresses the issue reported. But I did notice that the documentation for
EXISTS join flattening
suggests a slightly more general condition:
http://db.apache.org/derby/docs/10.3/tuning/ctuntransform25868.html
Namely, it seems like a more complete approach would be check to see if the
subquery's
WHERE clause "includes _any_ subqueries"--not just WHERE EXISTS subqueries. For
example: if the inner-most WHERE EXISTS query was replaced with a WHERE IN or
WHERE
ANY clause, would we still get the correct results?
As an example, I re-wrote the inner-most WHERE EXISTS subquery from
derby-3301.sql to use a
WHERE IN clause that effectively duplicates one of the (already-existing)
equality predicate, and
so should in theory return the same results...I think? (I could be wrong here):
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 this_employees_e.empid in ( -- *** Army
changed this
select this_employees_e_projects_p.empid -- *** Army changed this
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)
This returns three rows:
EMPID |PROJID
-----------------------
13 |101
13 |102
15 |103
Is that correct? Or should that query be returning 7 rows, as well? What
about if
the first WHERE EXISTS query is changed to a "WHERE IN" query, as well--will
that behave correctly?
If it should return seven rows, as well, then perhaps the new method in
SubqueryNode
could be expanded to check for the existence of _any_ subquery, not just a WHERE
EXISTS subquery?
One way to do that might be to use a CollectNodesVisitor to collect all
instances of
SubqueryNode.class that appear in "sn.originalWhereSubquerys" and/or in
"sn.originalWhereClause()"--and if that visitor returns any matches, then the
method
returns "false". I'm not entirely sure that will work, but it seems like it
could...?
> 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-test-1.diff, derby-3301-test-1.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.
