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] Commented: (DERBY-4679) Several left outer joins causes unstable query with incorrect results
Date Sat, 29 May 2010 16:11:36 GMT

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

Dag H. Wanvik commented on DERBY-4679:
--------------------------------------

Looking at in-after-preprocess.log, line 4551, I see a restriction predicate on T7 (LM.FAMILY):

T7.ITEM_ID = CharConstantNode@331['_5VetVWTeEd-Q8aOqWJPEIQ'].

This is wrong, because this constant is only ever used in the query against table T3 (LM.ABSTRACT_INSTANCE),
and then against column FAMILY_ITEM_ID. This is the cause of the wrong qualifier which leads
to 0 rows from FAMILY as seen.

On the other hand, in ins-after-preprocess (correct result), we see two predicates in the
final where clause (because the last two joins have been flattened, 4
halfouterjoinnodes remain:

a) t1.FAMILY_ITEM_ID = T7.ITEM_ID
b) t2.FAMILY_ITEM_ID = T5.ITEM_ID

For the single IN case (erroneous), we also have only 4 remaining halfouterjoinnodes, but
the b) predicate in the final where clause is missing.

In the equal-after-preprocess.log we see only three halfouterjoinnodes, since 3 joins have
been flattened, and the final where predicate contains
a) and b) plus a new

c) J2.ITEM_ID = T3.ITEM_ID

which corresponds to the third but the last join, which have been flattened because of the
equality. 
In the erroneous case, the left outer join was not converted to an inner join since it did
not contain an = operator at the time of LOJ->IJ conversion, so flattening did not happen
and we are left with 4 halfouterjoinnodes. That is less than optimal, but should still work.

Somehow the preprocessing (all is fine after bind) of the simple IN case has gone wrong.


> 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