[
https://issues.apache.org/jira/browse/DERBY-3023?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12523555
]
Stefan Cordes commented on DERBY-3023:
--------------------------------------
Hallo!
This bug does affect 10.1.2.1. Even 10.1.1.0 is affected.
I tested with some of the Derby releases and all have the exactly the same
(wrong) resultset depending on the join order:
Statement10.1.1.0 - (208786)-j1.4.2_10
Statement10.1.2.1 - (330608)-j1.4.2_10
Statement10.1.2.2 - (349064)-j1.4.2_10
Statement10.1.2.2 - (370021)-j1.4.2_10
Statement10.2.2.0 - (485682)-j1.4.2_10
Statement10.3.1.4 - (561794)-j1.4.2_10
In addition I mixed our Joins to all possible combinations and found out e.g.
The join order
NA0-P00-NA4-N01-N03-NE1-N04-N08-NB0-Q00 gives 924 rows,
but
NA0-P00-NA4-N01-N03-NE1-N04-N08-Q00-NB0 only 0 rows.
(Only last to tables are switched)
Comparing the statistics the sucessful SQL with Q00 as right join (Rows seen =
924)
Right result set:
Hash Scan ResultSet for ESVQ00 at read committed isolation
level using instantaneous share table locking:
Number of opens = 924
Hash table size = 60
Hash key is column number 0
Rows seen = 924
Rows filtered = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0, 6}
Number of columns fetched=2
Number of pages visited=3
Number of rows qualified=60
Number of rows visited=60
but on the other hand (Rows seen = 0) with NB0 as most right join:
Right result set:
Hash Scan ResultSet for ESVNB0 at read committed isolation
level using instantaneous share table locking:
Number of opens = 924
Hash table size = 0
Hash keys are column numbers (1,2)
Rows seen = 0
Rows filtered = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
scan information:
Bit set of columns fetched={0, 1, 2, 3, 6, 8}
Number of columns fetched=6
Number of pages visited=8
Number of rows qualified=0
Number of rows visited=240
Maybe that helps.
I attach the complete results from all versions I tested as
derby-02-search-joins2.zip
> 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
> Attachments: derby-02-search-joins.zip, derby-02-search-joins2.zip,
> RUNTIMESTATISTICS-10.3.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.
