[ https://issues.apache.org/jira/browse/DERBY-3023?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dyre Tjeldvoll updated DERBY-3023: ---------------------------------- This issue has fix version 10.4 and is marked with either 'Release note needed' or 'Existing application impact', but does not have a releaseNote.html attached to it. Should it? > Different result rows depending on the sequence of INNER JOIN and OUTER JOIN > ---------------------------------------------------------------------------- > > Key: DERBY-3023 > URL: https://issues.apache.org/jira/browse/DERBY-3023 > Project: Derby > Issue Type: Bug > Components: SQL > Affects Versions: 10.1.2.1, 10.2.2.0, 10.3.1.4 > Environment: Windows XP, Java 1.4.2 > Reporter: Stefan Cordes > Assignee: A B > Fix For: 10.3.2.2, 10.4.0.0 > > Attachments: d3023_notTested_v1.patch, d3023_repro.sql, > d3023_v2.patch, derby-02-search-joins.zip, derby-02-search-joins2.zip, > DerbySearchJoins.java, RUNTIMESTATISTICS-10.3.zip, Statement10.3.1.4 - > (561794)-j1.4.2_10.zip > > > 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. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.