[
https://issues.apache.org/jira/browse/DERBY-3301?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12564441#action_12564441
]
A B commented on DERBY-3301:
----------------------------
Hi Thomas,
I really hate to keep bringing up more issues, but as I was about to sign off
on patch 6, the
following caught my eye:
/*
* This WHERE EXISTS | ANY | IN subquery has another
* subquery in its own WHERE *clause* if the CNV is not
* empty.
*/
return cnv.getList().isEmpty();
Note how the code comments don't match the code--there's a missing "!" operator
in the
actual code. This is how I wrote it in my previous comment, but that was
wrong. The
method is supposed to return "true" if the SubqueryNode has a WHERE clause with
another subquery in it--which will be true if cnv.getList() is NOT empty. So I
missed
the negation (sorry).
With this fix in place all of Craigs queries still run correctly--but the
queries with
"1 = 1" in them start failing again. In tracing it turns out that, for those
cases,
the CollectNodesVisitor does not find the SubqueryNodes in
"sn.originalWhereClause".
I think it comes down to something you mentioned earlier, namely:
> I still need to have another look at [the "1 = 1"] variant, as it doesn't seem
> to end up with a similar querytree to the others
Upon further inspection, I think you are right about this.
For reference, this is the query in question. Note the tags on the right for
the sake of discussion, where "SN" implies "SelectNode":
select unbound_e.empid, unbound_p.projid -- SN_OUTER
from departments this,
employees unbound_e,
projects unbound_p
where exists (
select 1 from employees this_employees_e --
SN_INNER_1
where 1 = 1 and exists (
select 1 from project_employees this_employees_e_projects_p --
SN_INNER_2
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)
);
By the time we reach SubqueryNode.isWhereExistsAnyInWithWhereSubquery() for the
SubqueryNode wrapping SN_INNER_1, the clause:
where 1 = 1 and exists ( ... )
ends up as an AndNode whose left operand is "1 = 1" but whose right operand is
the
constant literal TRUE--which is not what we expect. We expect the AndNode's
right
operand to be the SubqueryNode corresponding to SN_INNER_2. That is, if "sn"
is the
SelectNode for SN_INNER_1 then sn.originalWhereClause should include the
SubqueryNode
that wraps SN_INNER_2. That is true when we first set sn.originalWhereClause
in the
init() method of SelectNode--but by the time we get to preprocessing for the
SubqueryNode
wrapping SN_INNER_1, the SubqueryNode for SN_INNER_2 is no longer in
SN_INNER_1's
originalWhereClause.
>From what I can tell, this is because sn.originalWhereClause for SN_INNER_1
>points to
the same object as sn.whereClause. So when sn.whereClause is itself
transformed due to
flattening of the subquery SN_INNER_2 (which is legal),
sn.originalWhereClause() sees
the same transformation. Thus the SubqueryNode wrapping SN_INNER_2 disappears
from
sn.whereClause, which is good--but it also disappears from
sn.originalWhereClause(),
which is bad. The fact that it disappears means that CollectNodesVisitor for
SN_INNER_1
will not find it, and thus SN_INNER_1 will be flattened, which is not legal.
With the negation operator gone (as in patch 6), the fact that we can't find
the SubqueryNode
for SN_INNER_2 causes the method to incorrectly return "true" (because CNV's
list is empty),
which is then negated by the caller and thus flattening of SN_INNER_1 is
accidentally (but
correctly) avoided. I think it's clear that the negation operator should be
there, though.
I'm not sure what the best way to address that would be--but before you go
there,
can you double-check these findings to see if you agree? Maybe I'm just missing
something obvious...
Apologies for the negation slip-up to begin with.
> 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-6.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-test-master.diff, derby-3301-test-master.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.
