Issue Details (XML | Word | Printable)

Key: DERBY-3023
Type: Bug Bug
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: A B
Reporter: Stefan Cordes
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Derby

Different result rows depending on the sequence of INNER JOIN and OUTER JOIN

Created: 23/Aug/07 08:32 AM   Updated: 30/Jun/09 04:12 PM
Return to search
Component/s: SQL
Affects Version/s: 10.1.2.1, 10.2.2.0, 10.3.1.4
Fix Version/s: 10.1.3.2, 10.2.2.1, 10.3.3.0, 10.4.1.3

Time Tracking:
Not Specified

File Attachments:
  File Name Date Attached Attached By Size
Text File Licensed for inclusion in ASF works d3023_notTested_v1.patch 2007-11-18 01:31 AM A B 10 kB
File Licensed for inclusion in ASF works d3023_repro.sql 2007-11-18 01:31 AM A B 1.0 kB
Text File Licensed for inclusion in ASF works d3023_v2.patch 2007-11-19 07:49 PM A B 10 kB
Zip Archive derby-02-search-joins.zip 2007-08-23 08:33 AM Stefan Cordes 528 kB
Zip Archive derby-02-search-joins2.zip 2007-08-29 12:16 PM Stefan Cordes 3.67 MB
Java Source File Licensed for inclusion in ASF works DerbySearchJoins.java 2007-12-11 02:00 PM Stefan Cordes 36 kB
HTML File Licensed for inclusion in ASF works releaseNote.html 2008-04-02 01:20 PM Rick Hillegas 4 kB
HTML File Licensed for inclusion in ASF works releaseNote.html 2008-04-02 03:31 AM A B 5 kB
Zip Archive Licensed for inclusion in ASF works RUNTIMESTATISTICS-10.3.zip 2007-08-23 08:32 AM Stefan Cordes 19 kB
Zip Archive Licensed for inclusion in ASF works Statement10.3.1.4 - (561794)-j1.4.2_10.zip 2007-08-29 12:18 PM Stefan Cordes 530 kB
Environment: Windows XP, Java 1.4.2
Issue Links:
Incorporates
 
Reference
 

Issue & fix info: Release Note Needed
Resolution Date: 16/Jan/08 04:52 PM


 Description  « Hide
We have a complex SQL joining 11 Tables via INNER JOIN and OUTER JOIN.
These SQLs were tested against an z/OS DB2 Version 8.
After moving to our local platform with Derby we found out the resultsets returned by the SQLs were too less.

I tested our "old style" SQL which results in 889 rows.
Our new style SQL expected to give similar rows but gives *0*.

After some work we found a workaround: first place all the "INNER JOIN"s in the SQL and then the "OUTER JOIN"s.

{code:title=Result of testprogram}
Derby=10.3-b561794
Test 10.3-b561794-old-style-sql
889 Rows in 1703ms
Test 10.3-b561794-new-style-sql
0 Rows in 563ms _(expected 924 rows instead)_
Test 10.3-b561794-new-style-sql-only-inner
2 Rows in 766ms _(only inner joins, no outer joins but larger result)_
Test 10.3-b561794-new-style-sql_first-inner-joins
924 Rows in 578ms
Test 10.3-b561794-new-style-sql_without-condition
924 Rows in 438ms
{code}

Here our initial used SQL:
{code:title=SQL giving wrong result (0 rows)}
SELECT O4Work.ESVN01.NU_BUY_CPY AS PO_BuyCompanyNo, O4Work.ESVN01.NU_ODR AS PO_Number, O4Work.ESVN01.FL_ODR_CAE AS PO_Type, O4Work.ESVN01.NU_MCS_SPY AS PO_SupplierNo, O4Work.ESVN01.NU_ST3 AS PO_StatusNo, O4Work.ESVN01.DA_SPY_COY_PRT AS PO_SCPrintDate, O4Work.ESVN01.FL_SAS AS PO_SeasFlag, CASE WHEN (SELECT COUNT(O4Work.ESVNA5.ID_PTE) FROM O4Work.ESVNA5 WHERE O4Work.ESVN02.NU_BUY_CPY = O4Work.ESVNA5.NU_BUY_CPY AND O4Work.ESVN02.NU_ODR = O4Work.ESVNA5.NU_ODR AND O4Work.ESVN02.NU_PST = O4Work.ESVNA5.NU_PST) = 0 THEN 'N' ELSE 'Y' END AS POPA_PictureID, CASE WHEN (SELECT COUNT(O4Work.ESVNG3.NU_ODR) FROM O4Work.ESVNG3 WHERE O4Work.ESVN01.NU_BUY_CPY = O4Work.ESVNG3.NU_BUY_CPY AND O4Work.ESVN01.NU_ODR = O4Work.ESVNG3.NU_ODR) = 0 THEN 'N' ELSE 'Y' END AS ON_ID, O4Work.ESVN02.NU_PST AS POP_Position_Id, O4Work.ESVN02.NU_CTT AS POP_ContractNo, O4Work.ESVN02.NU_ARO_CTT AS POP_ArosContractNo, O4Work.ESVN02.NU_ST3 AS POP_StatusNo, O4Work.ESVN02.DA_CAE AS POP_CreationDate, O4Work.ESVN02.DA_LAT_AMD AS POP_LastAmendDate, O4Work.ESVNA0.NU_SSN_IDE AS POPD_SeasonInd, O4Work.ESVNA0.NU_STL_ID1 AS POPD_StyleId, O4Work.ESVNA0.NU_SRY_ID1 AS POPD_StoryID, O4Work.ESVNA0.NU_LC1 AS POPD_LicenseID, O4Work.ESVP00.NU_CSY AS SER_ClassNo, O4Work.ESVP00.NU_COE AS SER_CodeNo, O4Work.ESVP00.NU_SRL AS SER_SerialNo, O4Work.ESVP00.NU_PIK_MOD AS SER_PickingM, O4Work.ESVN03.NU_MT1_CPY AS POPC_MasterCpyNo, O4Work.ESVN03.QU_ODR AS POPC_OrderedQty, O4Work.ESVN03.DA_EDD AS POPC_Edd, O4Work.ESVN03.DA_LDD AS POPC_Ldd, O4Work.ESVN03.DA_PAD AS POPC_Pad, O4Work.ESVN03.DA_SAD AS POPC_Sad, O4Work.ESVN03.PR_SCP AS POPC_SupCstPrice, O4Work.ESVN03.NU_SCP_CR1 AS POPC_SupCstPrCurr, O4Work.ESVN03.NU_ST3 AS POPC_StatusNo, O4Work.ESVN03.NU_COY_FRM_ODR AS POPC_Src_PO_Number, O4Work.ESVN03.NU_COY_UTL_ODR AS POPC_Tgt_PO_Number, O4Work.ESVN03.DA_FLR_RDY AS POPC_FRM_DATE, O4Work.ESVN03.FL_CSG AS POPC_CS_FLAG, O4Work.ESVN03.NU_PAK_MOD_SPY AS POPC_PackSupplNo, O4Work.ESVN03.NU_PAK_MOD_DCR AS POPC_PackingDCNo, O4Work.ESVN03.NU_PS2_MOD AS POPC_PresMethodNo, O4Work.ESVN04.NU_RTL_CPY AS POPRC_RetailCode, O4Work.ESVN04.PR_PLN_SEL AS POPRC_SellPrice, O4Work.ESVN04.NU_PLN_SEL_PRC_CR1 AS POPRC_SellPrCurr, O4Work.ESVN08.NU_AVE AS POPRCA_AdvertNo, O4Work.ESVQ00.ID_SHP AS SHP_ShippingID, O4Work.ESVQ00.NU_SHP AS SHP_ShippingNo, O4Work.ESVNB0.NU_NTL_PDE_ID1 AS POPDC_NationalID, O4Work.ESVNB0.NU_EQP AS POPDC_EquipNumber, O4Work.ESVNE1.PE_OMU AS POPCC_OMU, CASE WHEN (SELECT COUNT(O4Work.ESVN07.NU_ODR) FROM O4Work.ESVN07 WHERE O4Work.ESVN03.NU_BUY_CPY = O4Work.ESVN07.NU_BUY_CPY AND O4Work.ESVN03.NU_MT1_CPY = O4Work.ESVN07.NU_MT1_CPY AND O4Work.ESVN03.NU_ODR = O4Work.ESVN07.NU_ODR AND O4Work.ESVN03.NU_PST = O4Work.ESVN07.NU_PST AND O4Work.ESVN07.FL_ALE_RMK = 'Y') = 0 THEN 'N' ELSE 'Y' END AS POPCU_AllocRem FROM O4Work.ESVN02
INNER JOIN O4Work.ESVN01 ON O4Work.ESVN02.NU_BUY_CPY = O4Work.ESVN01.NU_BUY_CPY AND O4Work.ESVN02.NU_ODR = O4Work.ESVN01.NU_ODR
INNER JOIN O4Work.ESVNA0 ON O4Work.ESVN02.NU_BUY_CPY = O4Work.ESVNA0.NU_BUY_CPY AND O4Work.ESVN02.NU_ODR = O4Work.ESVNA0.NU_ODR AND O4Work.ESVN02.NU_PST = O4Work.ESVNA0.NU_PST
INNER JOIN O4Work.ESVP00 ON O4Work.ESVNA0.ID_SRL = O4Work.ESVP00.ID_SRL
LEFT OUTER JOIN O4Work.ESVNA4 ON O4Work.ESVNA0.NU_BUY_CPY = O4Work.ESVNA4.NU_BUY_CPY AND O4Work.ESVNA0.NU_ODR = O4Work.ESVNA4.NU_ODR AND O4Work.ESVNA0.NU_PST = O4Work.ESVNA4.NU_PST
INNER JOIN O4Work.ESVN03 ON O4Work.ESVN02.NU_BUY_CPY = O4Work.ESVN03.NU_BUY_CPY AND O4Work.ESVN02.NU_ODR = O4Work.ESVN03.NU_ODR AND O4Work.ESVN02.NU_PST = O4Work.ESVN03.NU_PST
LEFT OUTER JOIN O4Work.ESVN04 ON O4Work.ESVN03.NU_BUY_CPY = O4Work.ESVN04.NU_BUY_CPY AND O4Work.ESVN03.NU_MT1_CPY = O4Work.ESVN04.NU_MT1_CPY AND O4Work.ESVN03.NU_ODR = O4Work.ESVN04.NU_ODR AND O4Work.ESVN03.NU_PST = O4Work.ESVN04.NU_PST
LEFT OUTER JOIN O4Work.ESVN08 ON O4Work.ESVN04.NU_BUY_CPY = O4Work.ESVN08.NU_BUY_CPY AND O4Work.ESVN04.NU_RTL_CPY = O4Work.ESVN08.NU_RTL_CPY AND O4Work.ESVN04.NU_MT1_CPY = O4Work.ESVN08.NU_MT1_CPY AND O4Work.ESVN04.NU_ODR = O4Work.ESVN08.NU_ODR AND O4Work.ESVN04.NU_PST = O4Work.ESVN08.NU_PST
INNER JOIN O4Work.ESVQ00 ON O4Work.ESVN03.ID_SHP = O4Work.ESVQ00.ID_SHP
INNER JOIN O4Work.ESVNB0 ON O4Work.ESVN03.NU_BUY_CPY = O4Work.ESVNB0.NU_BUY_CPY AND O4Work.ESVN03.NU_MT1_CPY = O4Work.ESVNB0.NU_MT1_CPY AND O4Work.ESVN03.NU_ODR = O4Work.ESVNB0.NU_ODR AND O4Work.ESVN03.NU_PST = O4Work.ESVNB0.NU_PST
INNER JOIN O4Work.ESVNE1 ON O4Work.ESVN03.NU_BUY_CPY = O4Work.ESVNE1.NU_BUY_CPY AND O4Work.ESVN03.NU_MT1_CPY = O4Work.ESVNE1.NU_MT1_CPY AND O4Work.ESVN03.NU_ODR = O4Work.ESVNE1.NU_ODR AND O4Work.ESVN03.NU_PST = O4Work.ESVNE1.NU_PST
WHERE O4Work.ESVN01.NU_BUY_CPY = 99 AND O4Work.ESVNA0.NU_CPY_GRP = 0
{code}
and the one with moved inner joins:
{code:title=SQL giving correct result (924 rows)}
SELECT O4Work.ESVN01.NU_BUY_CPY AS PO_BuyCompanyNo, O4Work.ESVN01.NU_ODR AS PO_Number, O4Work.ESVN01.FL_ODR_CAE AS PO_Type, O4Work.ESVN01.NU_MCS_SPY AS PO_SupplierNo, O4Work.ESVN01.NU_ST3 AS PO_StatusNo, O4Work.ESVN01.DA_SPY_COY_PRT AS PO_SCPrintDate, O4Work.ESVN01.FL_SAS AS PO_SeasFlag, CASE WHEN (SELECT COUNT(O4Work.ESVNA5.ID_PTE) FROM O4Work.ESVNA5 WHERE O4Work.ESVN02.NU_BUY_CPY = O4Work.ESVNA5.NU_BUY_CPY AND O4Work.ESVN02.NU_ODR = O4Work.ESVNA5.NU_ODR AND O4Work.ESVN02.NU_PST = O4Work.ESVNA5.NU_PST) = 0 THEN 'N' ELSE 'Y' END AS POPA_PictureID, CASE WHEN (SELECT COUNT(O4Work.ESVNG3.NU_ODR) FROM O4Work.ESVNG3 WHERE O4Work.ESVN01.NU_BUY_CPY = O4Work.ESVNG3.NU_BUY_CPY AND O4Work.ESVN01.NU_ODR = O4Work.ESVNG3.NU_ODR) = 0 THEN 'N' ELSE 'Y' END AS ON_ID, O4Work.ESVN02.NU_PST AS POP_Position_Id, O4Work.ESVN02.NU_CTT AS POP_ContractNo, O4Work.ESVN02.NU_ARO_CTT AS POP_ArosContractNo, O4Work.ESVN02.NU_ST3 AS POP_StatusNo, O4Work.ESVN02.DA_CAE AS POP_CreationDate, O4Work.ESVN02.DA_LAT_AMD AS POP_LastAmendDate, O4Work.ESVNA0.NU_SSN_IDE AS POPD_SeasonInd, O4Work.ESVNA0.NU_STL_ID1 AS POPD_StyleId, O4Work.ESVNA0.NU_SRY_ID1 AS POPD_StoryID, O4Work.ESVNA0.NU_LC1 AS POPD_LicenseID, O4Work.ESVP00.NU_CSY AS SER_ClassNo, O4Work.ESVP00.NU_COE AS SER_CodeNo, O4Work.ESVP00.NU_SRL AS SER_SerialNo, O4Work.ESVP00.NU_PIK_MOD AS SER_PickingM, O4Work.ESVN03.NU_MT1_CPY AS POPC_MasterCpyNo, O4Work.ESVN03.QU_ODR AS POPC_OrderedQty, O4Work.ESVN03.DA_EDD AS POPC_Edd, O4Work.ESVN03.DA_LDD AS POPC_Ldd, O4Work.ESVN03.DA_PAD AS POPC_Pad, O4Work.ESVN03.DA_SAD AS POPC_Sad, O4Work.ESVN03.PR_SCP AS POPC_SupCstPrice, O4Work.ESVN03.NU_SCP_CR1 AS POPC_SupCstPrCurr, O4Work.ESVN03.NU_ST3 AS POPC_StatusNo, O4Work.ESVN03.NU_COY_FRM_ODR AS POPC_Src_PO_Number, O4Work.ESVN03.NU_COY_UTL_ODR AS POPC_Tgt_PO_Number, O4Work.ESVN03.DA_FLR_RDY AS POPC_FRM_DATE, O4Work.ESVN03.FL_CSG AS POPC_CS_FLAG, O4Work.ESVN03.NU_PAK_MOD_SPY AS POPC_PackSupplNo, O4Work.ESVN03.NU_PAK_MOD_DCR AS POPC_PackingDCNo, O4Work.ESVN03.NU_PS2_MOD AS POPC_PresMethodNo, O4Work.ESVN04.NU_RTL_CPY AS POPRC_RetailCode, O4Work.ESVN04.PR_PLN_SEL AS POPRC_SellPrice, O4Work.ESVN04.NU_PLN_SEL_PRC_CR1 AS POPRC_SellPrCurr, O4Work.ESVN08.NU_AVE AS POPRCA_AdvertNo, O4Work.ESVQ00.ID_SHP AS SHP_ShippingID, O4Work.ESVQ00.NU_SHP AS SHP_ShippingNo, O4Work.ESVNB0.NU_NTL_PDE_ID1 AS POPDC_NationalID, O4Work.ESVNB0.NU_EQP AS POPDC_EquipNumber, O4Work.ESVNE1.PE_OMU AS POPCC_OMU, CASE WHEN (SELECT COUNT(O4Work.ESVN07.NU_ODR) FROM O4Work.ESVN07 WHERE O4Work.ESVN03.NU_BUY_CPY = O4Work.ESVN07.NU_BUY_CPY AND O4Work.ESVN03.NU_MT1_CPY = O4Work.ESVN07.NU_MT1_CPY AND O4Work.ESVN03.NU_ODR = O4Work.ESVN07.NU_ODR AND O4Work.ESVN03.NU_PST = O4Work.ESVN07.NU_PST AND O4Work.ESVN07.FL_ALE_RMK = 'Y') = 0 THEN 'N' ELSE 'Y' END AS POPCU_AllocRem FROM O4Work.ESVN02
INNER JOIN O4Work.ESVN01 ON O4Work.ESVN02.NU_BUY_CPY = O4Work.ESVN01.NU_BUY_CPY AND O4Work.ESVN02.NU_ODR = O4Work.ESVN01.NU_ODR
INNER JOIN O4Work.ESVNA0 ON O4Work.ESVN02.NU_BUY_CPY = O4Work.ESVNA0.NU_BUY_CPY AND O4Work.ESVN02.NU_ODR = O4Work.ESVNA0.NU_ODR AND O4Work.ESVN02.NU_PST = O4Work.ESVNA0.NU_PST
INNER JOIN O4Work.ESVP00 ON O4Work.ESVNA0.ID_SRL = O4Work.ESVP00.ID_SRL
INNER JOIN O4Work.ESVN03 ON O4Work.ESVN02.NU_BUY_CPY = O4Work.ESVN03.NU_BUY_CPY AND O4Work.ESVN02.NU_ODR = O4Work.ESVN03.NU_ODR AND O4Work.ESVN02.NU_PST = O4Work.ESVN03.NU_PST
INNER JOIN O4Work.ESVNB0 ON O4Work.ESVN03.NU_BUY_CPY = O4Work.ESVNB0.NU_BUY_CPY AND O4Work.ESVN03.NU_MT1_CPY = O4Work.ESVNB0.NU_MT1_CPY AND O4Work.ESVN03.NU_ODR = O4Work.ESVNB0.NU_ODR AND O4Work.ESVN03.NU_PST = O4Work.ESVNB0.NU_PST
INNER JOIN O4Work.ESVQ00 ON O4Work.ESVN03.ID_SHP = O4Work.ESVQ00.ID_SHP
LEFT OUTER JOIN O4Work.ESVNA4 ON O4Work.ESVNA0.NU_BUY_CPY = O4Work.ESVNA4.NU_BUY_CPY AND O4Work.ESVNA0.NU_ODR = O4Work.ESVNA4.NU_ODR AND O4Work.ESVNA0.NU_PST = O4Work.ESVNA4.NU_PST
LEFT OUTER JOIN O4Work.ESVN04 ON O4Work.ESVN03.NU_BUY_CPY = O4Work.ESVN04.NU_BUY_CPY AND O4Work.ESVN03.NU_MT1_CPY = O4Work.ESVN04.NU_MT1_CPY AND O4Work.ESVN03.NU_ODR = O4Work.ESVN04.NU_ODR AND O4Work.ESVN03.NU_PST = O4Work.ESVN04.NU_PST
LEFT OUTER JOIN O4Work.ESVN08 ON O4Work.ESVN04.NU_BUY_CPY = O4Work.ESVN08.NU_BUY_CPY AND O4Work.ESVN04.NU_RTL_CPY = O4Work.ESVN08.NU_RTL_CPY AND O4Work.ESVN04.NU_MT1_CPY = O4Work.ESVN08.NU_MT1_CPY AND O4Work.ESVN04.NU_ODR = O4Work.ESVN08.NU_ODR AND O4Work.ESVN04.NU_PST = O4Work.ESVN08.NU_PST
INNER JOIN O4Work.ESVNE1 ON O4Work.ESVN03.NU_BUY_CPY = O4Work.ESVNE1.NU_BUY_CPY AND O4Work.ESVN03.NU_MT1_CPY = O4Work.ESVNE1.NU_MT1_CPY AND O4Work.ESVN03.NU_ODR = O4Work.ESVNE1.NU_ODR AND O4Work.ESVN03.NU_PST = O4Work.ESVNE1.NU_PST
WHERE O4Work.ESVN01.NU_BUY_CPY = 99 AND O4Work.ESVNA0.NU_CPY_GRP = 0
{code}

Another curious behavior is that leaving out the condition {{O4Work.ESVNA0.NU_CPY_GRP = 0}} in the first SQL will give us the result of 924 all having O4Work.ESVNA0.NU_CPY_GRP = 0. So evaluation of the condition is buggy, too.

I think there may be a dependency between this issue and these ones:
[DERBY-1681|http://issues.apache.org/jira/browse/DERBY-1681] (Regression (wrong results): Join predicate can be ignored for left-most child in a chain of nested unions.)
[DERBY-1633|https://issues.apache.org/jira/browse/DERBY-1633] (Regression: The fields of views are not being calculated properly since 10.1.2.4)

Attached is an Eclipse project with the Test-Program (without the Derby-Libraries) and the several RUNTIMESTATISTICS.

 All   Comments   Work Log   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Stefan Cordes made changes - 23/Aug/07 08:32 AM
Field Original Value New Value
Attachment RUNTIMESTATISTICS-10.3.zip [ 12364396 ]
Stefan Cordes made changes - 23/Aug/07 08:33 AM
Attachment derby-02-search-joins.zip [ 12364397 ]
Stefan Cordes made changes - 29/Aug/07 12:16 PM
Attachment derby-02-search-joins2.zip [ 12364762 ]
Stefan Cordes made changes - 29/Aug/07 12:18 PM
Attachment Statement10.3.1.4 - (561794)-j1.4.2_10.zip [ 12364763 ]
Kathey Marsden made changes - 15/Oct/07 05:07 PM
Link This issue is part of DERBY-2034 [ DERBY-2034 ]
A B made changes - 18/Nov/07 01:31 AM
Attachment d3023_notTested_v1.patch [ 12369735 ]
Attachment d3023_repro.sql [ 12369734 ]
A B made changes - 19/Nov/07 07:49 PM
Attachment d3023_v2.patch [ 12369811 ]
A B made changes - 19/Nov/07 09:58 PM
Link This issue relates to DERBY-2526 [ DERBY-2526 ]
Stefan Cordes made changes - 11/Dec/07 02:01 PM
Attachment DerbySearchJoins.java [ 12371434 ]
A B made changes - 16/Dec/07 03:26 AM
Derby Info [Existing Application Impact] [Patch Available, Existing Application Impact]
A B made changes - 16/Jan/08 04:52 PM
Resolution Fixed [ 1 ]
Assignee A B [ army ]
Fix Version/s 10.4.0.0 [ 12312540 ]
Status Open [ 1 ] Resolved [ 5 ]
Derby Info [Patch Available, Existing Application Impact] [Existing Application Impact]
A B made changes - 21/Jan/08 11:23 PM
Fix Version/s 10.3.2.2 [ 12312885 ]
A B made changes - 02/Apr/08 03:31 AM
Attachment releaseNote.html [ 12379093 ]
Rick Hillegas made changes - 02/Apr/08 01:20 PM
Attachment releaseNote.html [ 12379131 ]
Kathey Marsden made changes - 18/Sep/08 11:27 PM
Fix Version/s 10.2.2.1 [ 12312251 ]
Fix Version/s 10.1.3.2 [ 12311972 ]
Dag H. Wanvik made changes - 30/Jun/09 04:12 PM
Issue & fix info [Existing Application Impact] [Release Note Needed]

Powered by a free Atlassian JIRA open source license for Apache Software Foundation. Try JIRA - bug tracking software for your team.
Atlassian JIRA the Professional Issue Tracker. (Enterprise Edition, Version: 3.13.2-#335) - Bug/feature request - Atlassian news - Contact Administrators