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] Issue Comment Edited: (DERBY-3301) Incorrect result from query with nested EXIST
Date Mon, 21 Jan 2008 17:36:39 GMT

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


Mime
View raw message