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 "A B (JIRA)" <ji...@apache.org> on 2007/11/18 02:31:43 UTC
[jira] Updated: (DERBY-3023) Different result rows depending on the
sequence of INNER JOIN and OUTER JOIN
[ https://issues.apache.org/jira/browse/DERBY-3023?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
A B updated DERBY-3023:
-----------------------
Attachment: d3023_notTested_v1.patch
d3023_repro.sql
I did some further tracing/investigation of this issue and confirmed that search transitive closure is indeed adding incorrect predicates to the predicate list. In the case of "new-style-sql.txt", we end up with four additional predicates:
ESVNB0.NU_BUY_CPY = 99
ESVNE1.NU_BUY_CPY = 99
ESVNB0.NU_MT1_CPY = 0
ESVNE1.NU_MT1_CPY = 0
All of these predicates are incorrectly inferred from two predicates in the outermost WHERE clause, namely
O4Work.ESVN01.NU_BUY_CPY = 99 AND O4Work.ESVNA0.NU_CPY_GRP = 0
It turns out that the introduction of these invalid predicates stems from a problem that is almost identical to the one described in DERBY-2526. For details please see d2526_v1.html as attached to that issue, since pretty much everything in that document applies to this issue, as well.
In fact, the fix for that issue nearly solved this one, too. More specifically, the fix for DERBY-2526 added the following lines to the remapColumnReferencesToExpressions() method of ColumnReference.java:
/* It's not enough to just set the table number. Depending
* on the original query specified and on whether or not
* subquery flattening has occurred, it's possible that
* the expression to which we're remapping has a different
* result column list than the one to which we were mapped
* before we got here. In that case we also need to update
* the columnNumber to point to the correct column in "ft".
* That's what the following line does. See DERBY-2526 for
* details.
*/
ResultColumn ftRC =
ft.getResultColumns().getResultColumn(columnName);
columnNumber = ftRC.getColumnPosition();
That code depends on the "getColumnPosition()" method of ResultColumn, which looks as follows:
public int getColumnPosition()
{
if (columnDescriptor!=null)
return columnDescriptor.getPosition();
else
return virtualColumnId;
}
The document d2526_v1.html mentioned two things regarding this code:
1) if "columnDescriptor" is null, the method will return virtualColumnId;
2) columnDescriptor will *not* be null if the ResultColumn (ftRC in this case)
points directly to a base table. In that case the method will return the
position as stored in the column descriptor.
Both of those statements are still true. But there's another fact about this code that DERBY-2526 did not catch:
3) columnDescriptor may be NON-null *even if* ftRC points to a virtual
column.
In the case of "new-style-sql" we'll end up calling the above code for both sides of any join predicates that belong to JoinNodes which are flattened (again, see the document attached to DERBY-2526 for details). In the case where one of the flattened JoinNode's children is a left outer join (i.e. HalfOuterJoinNode) ftRC points to the HalfOuterJoinNode. If that HalfOuterJoinNode then has base tables as its children, two things will be true: a) ftRC will point to a VIRTUAL column--namely, to one of the columns in HalfOuterJoinNode's result column, and b) ftRC will have a columnDescriptor that describes the column in the underlying base table to which ftRC ultimately points. So now we have a ResultColumn ftRC which has a non-null columnDescriptor AND which points to a virtual column.
In the above code, then, when we call:
columnNumber = ftRC.getColumnPosition();
we'll see that columnDescriptor is non-null and thus columnNumber will become the column position w.r.t. the *base* table. But that's not quite correct: ftRC points to a virtual result column list (i.e. the RCL for HalfOuterJoinNode), so columnNumber has to reflect the correct position with respect to that virtual column list. In other words, we need to retrieve the *virtualColumnId*, not the base column position.
When we mis-assign the columnNumber for the join predicates, we end up confusing the transitive closure code, which leads to the eventual addition of incorrect predicates to the WHERE list, and therefore we end up with missing rows.
I made a slight tweak to the code added by DERBY-2526 to make sure that columnNumber is always set to the virtualColumnId when the ResultColumn in question points to a virtual column:
- columnNumber = ftRC.getColumnPosition();
+ /* Use the virtual column id if the ResultColumn's expression
+ * is a virtual column (DERBY-3023).
+ */
+ columnNumber =
+ (ftRC.getExpression() instanceof VirtualColumnNode)
+ ? ftRC.getVirtualColumnId()
+ : ftRC.getColumnPosition();
With this one-line change, "new-style-sql" returns the same results as "new-style-sql_without-condition". So that seems like a step in the right direction for this issue. Also with that change I ran the repro program, DerbySearchJoins.java, and all but two of the queries now return 924 rows--another good sign.
The two queries that still do not return 924 rows are:
old-style-sql.txt (returns 889 rows)
new-style-sql-only-inner.txt (returns 2 rows)
If it's true that those queries are supposed to return 924 rows, as well (Stefan, can you confirm that? Is that what DB2 shows, as well?), then further investigation is still needed before this issue can be resolved. But at least we're a step closer...
I'm attaching a simplified repro for the problem that I just described, along with a patch that includes the one-line change and a corresponding test case for the nightlies. This patch is NOT for commit, though, as I have not yet run derbyall. I did run suites.All and saw 92 failures along with 24 errors, all of which were in upgrade tests. When I re-ran the suite without my changes to ColumnReference the same tests failed, so I'm hoping it's just an environment issue.
> 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: d3023_notTested_v1.patch, d3023_repro.sql, derby-02-search-joins.zip, derby-02-search-joins2.zip, 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.