You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Knut Anders Hatlen (JIRA)" <ji...@apache.org> on 2010/05/27 13:12:28 UTC
[jira] Updated: (DERBY-4679) Several left outer joins causes
unstable query with incorrect results
[ https://issues.apache.org/jira/browse/DERBY-4679?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-4679:
--------------------------------------
Bug behavior facts: [Deviation from standard, Wrong query result] (was: [Deviation from standard])
Hi David,
It would be helpful if you could post the schema for the tables involved in the query, including all constraints and indexes defined on them. Preferably also with enough rows to reproduce the problem.
(The dblook utility could be used to extract schema information. See http://db.apache.org/derby/docs/10.6/tools/ctoolsdblook.html.)
There's at least one wrong query result bug affecting joins (DERBY-4372) in 10.5.3.0 that has been fixed in the recently released 10.6.1.0, so it might be worthwhile to check if the bug is still present in the latest version of Derby.
> 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.5.3.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
>
> 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.