db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "A B (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-3301) Incorrect result from query with nested EXIST
Date Thu, 31 Jan 2008 18:15:08 GMT

    [ 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.


Mime
View raw message