db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] Issue Comment Edited: (DERBY-4679) Several left outer joins causes unstable query with incorrect results
Date Mon, 31 May 2010 21:33:37 GMT

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

Dag H. Wanvik edited comment on DERBY-4679 at 5/31/10 5:32 PM:
---------------------------------------------------------------

While keeping the call to transforOuterJoins in FromList#preprocess disabled, I manage to
get this version of the query to fail as well. The predicate on t3.FAMILY_ITEM_ID uses explicit
equality here, but it not lead to rewrite to inner join of the LOJ with t3 in this case since
I disabled transforOuterJoins.

"select distinct t1.ITEM_ID, t1.state_id, t1.JZ_DISCRIMINATOR from 
 (((((((select * from LM.ABSTRACT_INSTANCE z1 where z1.JZ_DISCRIMINATOR = 238) t1  
        left outer join LM.LAB_RESOURCE_OPERATINGSYSTEM j1 on (t1.ITEM_ID = j1.JZ_PARENT_ID))
 
       left outer join LM.ABSTRACT_INSTANCE t2 on (j1.ITEM_ID = t2.ITEM_ID))  
      left outer join LM.OPERATING_SYSTEM_SOFTWARE_INSTALL j2 on (t2.ITEM_ID = j2.JZ_PARENT_ID))

     left outer join LM.ABSTRACT_INSTANCE t3 on (j2.ITEM_ID = t3.ITEM_ID))  
    inner join LM.FAMILY t5 on (t2.FAMILY_ITEM_ID = t5.ITEM_ID))  
   inner join LM.FAMILY t7 on (t1.FAMILY_ITEM_ID = t7.ITEM_ID))  
  where (t3.FAMILY_ITEM_ID ='_5VetVWTeEd-Q8aOqWJPEIQ' and  <------------ explicit "=" 
also fails when LOJ -> IJ is disabled
        (t5.ROOT_ITEM_ID = '_5ZDlwWTeEd-Q8aOqWJPEIQ') and  
        (t7.ROOT_ITEM_ID ='_5nN9mmTeEd-Q8aOqWJPEIQ'))"

The LOJ->IJ rewrite is not logically required and the bug is now seen to be present without
the LOJ->IJ rewriting happeing.

In the above query, we have four remaining LOJs. This is also what resulted in the original
case after IN is transformed to '=", since that happens too late for LOJ->IJ to kick in
for that case. It does happen for the two last joins, as manually replicated in the above
experiment.

I see that the reason the predicate "= _5VetVWTeEd-Q8aOqWJPEIQ" ends up on T7.ITEM_ID is because
of the transitive closure computation in PredicatList#searchClauseTransitiveClosure. 

Could it be that the precondition for that is wrong here: a predicate on t3 which has not
been flattened is applied transitively to the T7, which has been?


      was (Author: dagw):
    While keeping the call to transforOuterJoins in FromList#preprocess disabled, I manage
to get this version of the query to fail as well (explicit equality here, which does not lead
to rewrite to inner join the LOJ with t3 in this case since I disabled it):

"select distinct t1.ITEM_ID, t1.state_id, t1.JZ_DISCRIMINATOR from 
 (((((((select * from LM.ABSTRACT_INSTANCE z1 where z1.JZ_DISCRIMINATOR = 238) t1  
        left outer join LM.LAB_RESOURCE_OPERATINGSYSTEM j1 on (t1.ITEM_ID = j1.JZ_PARENT_ID))
 
       left outer join LM.ABSTRACT_INSTANCE t2 on (j1.ITEM_ID = t2.ITEM_ID))  
      left outer join LM.OPERATING_SYSTEM_SOFTWARE_INSTALL j2 on (t2.ITEM_ID = j2.JZ_PARENT_ID))

     left outer join LM.ABSTRACT_INSTANCE t3 on (j2.ITEM_ID = t3.ITEM_ID))  
    inner join LM.FAMILY t5 on (t2.FAMILY_ITEM_ID = t5.ITEM_ID))  
   inner join LM.FAMILY t7 on (t1.FAMILY_ITEM_ID = t7.ITEM_ID))  
  where (t3.FAMILY_ITEM_ID ='_5VetVWTeEd-Q8aOqWJPEIQ' and  <------------ explicit "=" 
also fails when LOJ -> IJ is disabled
        (t5.ROOT_ITEM_ID = '_5ZDlwWTeEd-Q8aOqWJPEIQ') and  
        (t7.ROOT_ITEM_ID ='_5nN9mmTeEd-Q8aOqWJPEIQ'))"

Since this rewrite is not logically required, the bug is thus seen to be present without the
LOJ->IJ rewriting happeing.
In the above query. Four remaining LOJ is also what result in the original case after IN is
transformed to '=" too  late for LOJ->IJ to kick in for that case (only happens for the
two last joins, as in the above experiment).

I see that the reason the predicate "= _5VetVWTeEd-Q8aOqWJPEIQ" ends up on T7.ITEM_ID is because
of the transitive closure computation in PredicatList#searchClauseTransitiveClosure. Could
it be that the precondition for that is wrong here: a predicate on t3 which has not been flattened
is applied transitively to the T7, which has been? 
  
> Several left outer joins causes unstable query with incorrect results
> ---------------------------------------------------------------------
>
>                 Key: DERBY-4679
>                 URL: https://issues.apache.org/jira/browse/DERBY-4679
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4,
10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0
>         Environment: ------------------ Java Information ------------------
> Java Version:    1.5.0
> Java Vendor:     IBM Corporation
> Java home:       C:\jazz-rtc-2.0.0.2\client\eclipse\jdk\jre
> Java classpath:  C:\Progra~1\Derby\Derby10.5.3/lib/derby.jar;C:\Progra~1\Derby\Derby10.5.3/lib/derbynet.jar;C:\Progra~1\Derby\Derby10.5.3/lib/derbyclient.jar;C:\Progra~1\Derby\Derby10.5.3/lib/derbytools.jar
> OS name:         Windows XP
> OS architecture: x86
> OS version:      5.1 build 2600 Service Pack 3
> Java user name:  dirichar
> Java user home:  C:\Documents and Settings\dirichar
> Java user dir:   C:\Program Files\Derby\Derby10.5.3\bin
> java.specification.name: Java Platform API Specification
> java.specification.version: 1.5
> --------- Derby Information --------
> JRE - JDBC: J2SE 5.0 - JDBC 3.0
> [C:\Program Files\Derby\Derby10.5.3\lib\derby.jar] 10.5.3.0 - (802917)
> [C:\Program Files\Derby\Derby10.5.3\lib\derbytools.jar] 10.5.3.0 - (802917)
> [C:\Program Files\Derby\Derby10.5.3\lib\derbynet.jar] 10.5.3.0 - (802917)
> [C:\Program Files\Derby\Derby10.5.3\lib\derbyclient.jar] 10.5.3.0 - (802917)
> ------------------------------------------------------
> ----------------- Locale Information -----------------
> Current Locale :  [English/United States [en_US]]
> Found support for locale: [cs]
> 	 version: 10.5.3.0 - (802917)
> Found support for locale: [de_DE]
> 	 version: 10.5.3.0 - (802917)
> Found support for locale: [es]
> 	 version: 10.5.3.0 - (802917)
> Found support for locale: [fr]
> 	 version: 10.5.3.0 - (802917)
> Found support for locale: [hu]
> 	 version: 10.5.3.0 - (802917)
> Found support for locale: [it]
> 	 version: 10.5.3.0 - (802917)
> Found support for locale: [ja_JP]
> 	 version: 10.5.3.0 - (802917)
> Found support for locale: [ko_KR]
> 	 version: 10.5.3.0 - (802917)
> Found support for locale: [pl]
> 	 version: 10.5.3.0 - (802917)
> Found support for locale: [pt_BR]
> 	 version: 10.5.3.0 - (802917)
> Found support for locale: [ru]
> 	 version: 10.5.3.0 - (802917)
> Found support for locale: [zh_CN]
> 	 version: 10.5.3.0 - (802917)
> Found support for locale: [zh_TW]
> 	 version: 10.5.3.0 - (802917)
> ------------------------------------------------------
>            Reporter: David Richards
>         Attachments: db.sql, equal-after-preprocess.log, in-after-preprocess.log, ins-after-preprocess.log,
sample.sql, trace-equal.log, trace-exec-plan.log, trace-in.log
>
>
> select distinct 
>     t1.ITEM_ID, 
>     t1.STATE_ID, 
>     t1.JZ_DISCRIMINATOR 
> from (
>     select * 
>     from 
>         LM.ABSTRACT_INSTANCE z1 
>     where 
>         z1.JZ_DISCRIMINATOR = 238
> ) t1 left outer join 
>     LM.LAB_RESOURCE_OPERATINGSYSTEM j1 on (t1.ITEM_ID = j1.JZ_PARENT_ID) left outer join

>          LM.ABSTRACT_INSTANCE t2 on (j1.ITEM_ID = t2.ITEM_ID) left outer join 
>              LM.OPERATING_SYSTEM_SOFTWARE_INSTALL j2 on (t2.ITEM_ID = j2.JZ_PARENT_ID)
left outer join 
>                  LM.ABSTRACT_INSTANCE t3 on (j2.ITEM_ID = t3.ITEM_ID) left outer join

>                      LM.FAMILY t5 on (t2.FAMILY_ITEM_ID = t5.ITEM_ID) left outer join

>                          LM.FAMILY t7 on (t1.FAMILY_ITEM_ID = t7.ITEM_ID) 
> where (
>     t3.FAMILY_ITEM_ID  in('_5VetVWTeEd-Q8aOqWJPEIQ') and 
>     (t5.ROOT_ITEM_ID = '_5ZDlwWTeEd-Q8aOqWJPEIQ') and 
>     (t7.ROOT_ITEM_ID = '_5nN9mmTeEd-Q8aOqWJPEIQ') and 
>     (t1.VISIBILITY = 0)
> )
> The above query returns no results despite the fact that the database contains results
that match the query.  
> Slight modifications to the query that shouldn't change the outcome cause it to return
the expected results.  For example: changing 
> "t3.FAMILY_ITEM_ID  in('_5VetVWTeEd-Q8aOqWJPEIQ')" 
> to 
> "t3.FAMILY_ITEM_ID  in('_5VetVWTeEd-Q8aOqWJPEIQ', 'blah')" 
> or 
> "t3.FAMILY_ITEM_ID  = '_5VetVWTeEd-Q8aOqWJPEIQ'"
> or removing  
> "(t5.ROOT_ITEM_ID = '_5ZDlwWTeEd-Q8aOqWJPEIQ') and "
> despite the fact that the results show that t5.ROOT_ITEM_ID is equal to the string above.
> Note that there is no error message associated with the incorrect query, it just returns
zero rows.
> The query below doesn't use join statements and works as expected.
> select distinct 
>    t1.item_id, 
>    t1.state_id, 
>    t1.jz_discriminator
> from (
>         select * 
>         from 
>             lm.abstract_instance z1 
>         where 
>             z1.jz_discriminator = 238
>     ) t1,
>     lm.lab_resource_operatingsystem j1,
>     lm.abstract_instance t3,
>     lm.operating_system_software_install j2,
>     lm.abstract_instance t2,
>     lm.family t5,
>     lm.family t7
> where
>     t1.item_id = j1.jz_parent_id and
>     j1.item_id = t2.item_id and
>     t2.item_id = j2.jz_parent_id and
>     j2.item_id = t3.item_id and
>     t2.family_item_id = t5.item_id and
>     t1.family_item_id = t7.item_id and
>     t3.family_item_id in ('_5VetVWTeEd-Q8aOqWJPEIQ') and
>     t5.root_item_id = '_5ZDlwWTeEd-Q8aOqWJPEIQ' and
>     t7.root_item_id = '_5nN9mmTeEd-Q8aOqWJPEIQ' and
>     t1.visibility = 0;

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