db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Thomas Nielsen (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-3301) Incorrect result from query with nested EXIST
Date Fri, 18 Jan 2008 22:51:34 GMT

    [ https://issues.apache.org/jira/browse/DERBY-3301?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12560582#action_12560582
] 

Thomas Nielsen commented on DERBY-3301:
---------------------------------------

Thanks for the pointers Bryan.

NesteLoopJoinResultSet.getNextRowCore() will stop pulling rows if its member oneRowRightSide
is true. oneRowRightSide is supplied to the constructor, so it's set at ResultSet generation
time.

NestedLoopJoinResultSet is generated by GenericResultSetFactory.getNestedLoopJoinResult(),
which again is only called from JoinNode.generate().
To see this you first have to find the getter to the NestedLoopJoinResultSet in GenericResultSetFactory,
then do a text(!) search for use of the getter method. In this case it's only used once -
in 
NesteLoopJoinStrategy.joinResultSetMethodName(), which again is only used in JoinNode.generate.

So why is it setting oneRowRightSide to true?

The arguments to the NestedLoopJoinResultSet constructor is pushed to the stack by JoinNode.getJoinArguments(),
where one calls JoinNode.oneRowRightSide().
oneRowRightSide() simply pushes rightResultSet.isOneRowResultSet() to the stack and continues
along merrily (JoinNode.java @ 1691)

In the debugger we see that the first hit of a breakpoint in JoinNode.java @1691 is the interesting
one.
rightResultSet is a ProjectRestrictNode over a FromBaseTable. That is the select we want to
look at.

ProjectRestrictNode.isOneRowResult() simply calls its childResult.isOneRowResult(), so we
end up in FromBaseTable.isOneRowResult().

FromBaseTable.isOneRowResult() start off with this comment and code: 
   // EXISTS FBT will only return a single row
   if (existsBaseTable)
   {
       return true;
    }

existsBaseTable is true in the failing query.

existsBaseTable is only set in FromBaseTable.setExistsBaseTable(), and this method is only
called form FromList.genExistsBaseTables()
where it is explicitly set to true.

It seems to me that the assumption made in FromBaseTable.isOneRowResultI() is wrong?
It's been there ever since derby code was donated. That's most likely the reason for all previous
versions showing identical behaviour.

With those 4 lines making the single row assumption for exists commented out, and letting
the rest of the isOneRowResult() logic make the decision, 
it returns false as expected in this case. The query returns 7 rows as Craig expects.

EMPID      |PROJID     
-----------------------
13         |101        
12         |101        
11         |101        
13         |102        
12         |102        
15         |103        
14         |103        

Any one care to comment on my analysis and especially on the exists assumption made?

If this is correct, any query involving and EXISTS *potentially* will produce wrong results,
and this could be the root cause for a couple of the other EXISTS issues?


> 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