[
https://issues.apache.org/jira/browse/DERBY-3301?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12561073#action_12561073
]
army edited comment on DERBY-3301 at 1/21/08 9:34 AM:
-----------------------------------------------------
bryan> Perhaps the flattening logic is evaluating the correctness rules properly
bryan> for a single level of flattening but isn't handling
flattening-within-flattening?
Without having done any explicit tracing myself, this is what I would guess is
the problem. Perhaps the inner-most EXISTS subquery can be considered an
"exists base table" (and thus only returns a single row) w.r.t. it's parent
query, but if it is then flattened _again_ to the outer-most query, maybe it
should no longer be marked as an "exists base table"? I don't if that's
actually true, I'm just speculating.
craig> I also found
http://db.apache.org/derby/docs/10.3/tuning/ctuntransform25868.html that
craig> goes into some detail of the EXISTS join flattening
Something from that page which stands out to me is the following condition:
"None of the predicates in the subquery, including the additional one formed
between the left side of the subquery operator and the column in the
subquery's
SELECT list (for IN or ANY subqueries), include any subqueries, method calls,
or field accesses."
If an EXISTS clause is considered a "predicate", then the query
select <...> from <...>
where exists (
select 1 from <...>
where exists (
select 1 from <...>
...
seems to violate the condition quoted above from the documentation. That is,
the
subquery for the first EXISTS has a predicate (in this case another EXISTS
query) which includes another subquery ("select 1 from ..."), and therefore the
first subquery should not be flattened. If this is the correct reading then
the bug would appear to be in the logic that checks for the aforementioned
condition--presumably because that check occurs after the inner-most subquery
has itself been flattened? Again, I'm just guessing, I haven't done much
examination of the actual codepath...
was (Author: army):
bryan> Perhaps the flattening logic is evaluating the correctness rules
properly
bryan> for a single level of flattening but isn't handling
flattening-within-flattening?
Without having done any explicit tracing myself, this is what I would guess is
the
problem. Perhaps the inner-most EXISTS subquery can be considered an "exists
base table" (and thus only returns a single row) w.r.t. it's parent query, but
if it is
then flattened _again_ to the outer-most query, maybe it should no longer be
marked
as an "exists base table"? I don't if that's actually true, I'm just
speculating.
craig> I also found
http://db.apache.org/derby/docs/10.3/tuning/ctuntransform25868.html that
craig> goes into some detail of the EXISTS join flattening
Something from that page which stands out to me is the following condition:
"None of the predicates in the subquery, including the additional one formed
between the
left side of the subquery operator and the column in the subquery's SELECT
list (for IN or
ANY subqueries), include any subqueries, method calls, or field accesses."
If an EXISTS clause is considered a "predicate", then the query
select <...> from <...>
where exists (
select 1 from <...>
where exists (
select 1 from <...>
...
seems to violate the condition quoted above from the documentation. That is,
the subquery
for the first EXISTS has a predicate (in this case another EXISTS query) which
includes
another subquery ("select 1 from ..."), and therefore the first subquery should
not be flattened.
Or am I misreading? If this is the correct reading then the bug would appear
to be in the logic
that checks for the aforementioned condition--presumably because that check
occurs after
the inner-most subquery has itself been flattened? Just guessing...feel free
to ignore.
> 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.
