Return-Path: Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: (qmail 1214 invoked from network); 21 Oct 2007 02:49:12 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 21 Oct 2007 02:49:12 -0000 Received: (qmail 65057 invoked by uid 500); 21 Oct 2007 02:48:59 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 65030 invoked by uid 500); 21 Oct 2007 02:48:59 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 65020 invoked by uid 99); 21 Oct 2007 02:48:59 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 20 Oct 2007 19:48:59 -0700 X-ASF-Spam-Status: No, hits=-100.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO brutus.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 21 Oct 2007 02:49:11 +0000 Received: from brutus (localhost [127.0.0.1]) by brutus.apache.org (Postfix) with ESMTP id C95C6714209 for ; Sat, 20 Oct 2007 19:48:50 -0700 (PDT) Message-ID: <15543733.1192934930822.JavaMail.jira@brutus> Date: Sat, 20 Oct 2007 19:48:50 -0700 (PDT) From: "A B (JIRA)" To: derby-dev@db.apache.org Subject: [jira] Issue Comment Edited: (DERBY-3023) Different result rows depending on the sequence of INNER JOIN and OUTER JOIN In-Reply-To: <23923613.1187857951808.JavaMail.jira@brutus> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable X-Virus-Checked: Checked by ClamAV on apache.org [ https://issues.apache.org/jira/browse/DERBY-3023?page=3Dcom.atlassian= .jira.plugin.system.issuetabpanels:comment-tabpanel#action_12536484 ]=20 army edited comment on DERBY-3023 at 10/20/07 7:47 PM: ------------------------------------------------------ I did some tracing through the execution-time result set processing for "ne= w-style-sql.txt" and while I still do not understand what is going on, I *t= hink* that part of the problem is that the two predicates for the outer-mos= t query, i.e.: WHERE O4Work.ESVN01.NU_BUY_CPY =3D 99 AND O4Work.ESVNA0.NU_CPY_GRP =3D 0 are both being applied as "scan qualifiers" to the initial scans for ESVNB0= and ESVNE1, in addition to being applied to their respective target tables= . That is to say, "NU_BUY_CPY =3D 99" translates into a qualifier on the "= first column" in ESVN01 (because NU_BUY_CPY is the first column in that tab= le); but further up the result set tree, that same qualifier is being appli= ed to the "first column" of ESVNB0 and (still later) to the "first column" = of ESVNE1--which, if that's what is actually happening, would be wrong... Similarly, "NU_CPY_GRP =3D 0" translates into a qualifier on the "fourth co= lumn" of ESVNA0 (because NU_CPY_GRP is the fourth column in that table); bu= t later that same qualifier is being applied to the "fourth column" of ESVN= B0 and (still later) the "fourth column" of ESVNE1. The reason the query returns the correct results (924 rows) if there is onl= y one predicate, "ESVN01.NU_BUY_CPY =3D 99", is because, as luck would have= it, the first column in both ESVNB0 and ESVNE1 is *also* NU_BUY_CPY, and i= t, like ESVN01, has the value 99 for all rows. So even though the predicat= e is (apparently) mis-applied, it doesn't affect the results because it doe= sn't actually filter any rows. But the second predicate, "ESVNA0.NU_CPY_GRP =3D 0", does affect the result= s because the fourth column of ESVNB0 and ESVNE1 is "NU_MT1_CPY", and that = column does not have any rows with value "0". So when the predicate is mis= -applied to ESVNB0, it eliminates all of ESVNB0's rows, thus causing the qu= ery to return no results. In an attempt to validate this somewhat bizarre theory, I updated a few of = the rows in ESVNB0 to have the value "0" for column NU_MT1_CPY: update o4work.esvnb0 set nu_mt1_cpy =3D 0 where nu_mt1_cpy =3D 2; When I re-ran "new-style-sql.txt", the query still returned 0 rows. Furthe= r tracing showed that we were now getting some rows back from ESVNB0, but t= hen we were joining them with ESVNE1--and since ESVNE1 still didn't have an= y NU_MT1_CPY columns with value 0, we were filtering out all of ESVNE1's ro= ws, leading to an empty result set. So I then updated ESVNE1 in a way simi= lar to ESVNB0, ie: update o4work.esvne1 set nu_mt1_cpy =3D 0 where nu_mt1_cpy =3D 2; When I did that, "new-style-sql.txt" returned 906 rows. This seems to sugg= est (though it's far from conclusive) that the NU_CPY_GRP qualifier is bein= g mis-applied in the hash scans for ESVNB0 and ESNB1. A look at the query plans for "new-style-sql_without-condition.txt" and "ne= w-style-sql.txt" seems to suggest a similar thing. Note in particular the = scan qualifiers for the hash scans on ESVNB0 and ESVNE1. For "new-style-sq= l_without-condition", we see a single scan qualifier on the first column: scan qualifiers: Column[0][0] Id: 0 Operator: =3D Ordered nulls: false Unknown return value: false Negate comparison result: false This corresponds to "NU_BUY_CPY =3D 99" (verified by tracing, where the qua= lifier value is '99'), which is always true for all rows of ESVNB0 and ESVN= E1. But for "new-style-sql.txt", we see *two* scan qualifiers: scan qualifiers: Column[0][0] Id: 0 Operator: =3D Ordered nulls: false Unknown return value: false Negate comparison result: false Column[0][1] Id: 3 Operator: =3D Ordered nulls: false Unknown return value: false Negate comparison result: false The second qualifier is on the fourth column and has a value of '0', which = appears to be coming from the "NU_CPY_GRP =3D 0" predicate. That qualifier= is eliminating all rows from the scans (for both ESVNB0 and ESVNE1), leadi= ng to zero results. Given this potentially erroneus theory, it appears that somehow the qualifi= ers for the outer-most WHERE clause are being mapped incorrectly onto ESVNB= 0 and ESVNE1. Maybe this is a transitive closure computation problem, mayb= e it's something else entirely. At this point I don't know, I'm just dumpi= ng what I've seen thus far... was (Author: army): I did some tracing through the execution-time result set processing for= "new-style-sql.txt" and while I still do not understand what is going on, = I *think* that part of the problem is that the two predicates for the outer= -most query, i.e.: WHERE O4Work.ESVN01.NU_BUY_CPY =3D 99 AND O4Work.ESVNA0.NU_CPY_GRP =3D 0 are both being applied as "scan qualifiers" to the initial scans for ESVNB0= and ESVNE1, in addition to being applied to their respective target tables= . That is to say, "NU_BUY_CPY =3D 99" translates into a qualifier on the "= first column" in ESVN01 (because NU_BUY_CPY is the first column in that tab= le); but further up the result set tree, that same qualifier is being appli= ed to the "first column" of ESVNB0 and (still later) to the "first column" = of ESVNE1--which, if that's what is actually happening, would be wrong... Similarly, "NU_CPY_GRP =3D 0" translates into a qualifier on the "fourth co= lumn" of ESVNA0 (because NU_CPY_GRP is the fourth column in that table); bu= t later that same qualifier is being applied to the "fourth column" of ESVN= B0 and (still later) the "fourth column" of ESVNE1. The reason the query returns the correct results (924 rows) if there is onl= y one predicate, "ESVN01.NU_BUY_CPY =3D 99", is because, as luck would have= it, the first column in both ESVNB0 and ESVNE1 is *also* NU_CPY_GRP, and i= t, like ESVN01, has the value 99 for all rows. So even though the predicat= e is (apparently) mis-applied, it doesn't affect the results because it doe= sn't actually filter any rows. But the second predicate, "ESVNA0.NU_CPY_GRP =3D 0", does affect the result= s because the fourth column of ESVNB0 and ESVNE1 is "NU_MT1_CPY", and that = column does not have any rows with value "0". So when the predicate is mis= -applied to ESVNB0, it eliminates all of ESVNB0's rows, thus causing the qu= ery to return no results. In an attempt to validate this somewhat bizarre theory, I updated a few of = the rows in ESVNB0 to have the value "0" for column NU_MT1_CPY: update o4work.esvnb0 set nu_mt1_cpy =3D 0 where nu_mt1_cpy =3D 2; When I re-ran "new-style-sql.txt", the query still returned 0 rows. Furthe= r tracing showed that we were now getting some rows back from ESVNB0, but t= hen we were joining them with ESVNE1--and since ESVNE1 still didn't have an= y NU_MT1_CPY columns with value 0, we were filtering out all of ESVNE1's ro= ws, leading to an empty result set. So I then updated ESVNE1 in a way simi= lar to ESVNB0, ie: update o4work.esvne1 set nu_mt1_cpy =3D 0 where nu_mt1_cpy =3D 2; When I did that, "new-style-sql.txt" returned 906 rows. This seems to sugg= est (though it's far from conclusive) that the NU_CPY_GRP qualifier is bein= g mis-applied in the hash scans for ESVNB0 and ESNB1. A look at the query plans for "new-style-sql_without-condition.txt" and "ne= w-style-sql.txt" seems to suggest a similar thing. Note in particular the = scan qualifiers for the hash scans on ESVNB0 and ESVNE1. For "new-style-sq= l_without-condition", we see a single scan qualifier on the first column: scan qualifiers: Column[0][0] Id: 0 Operator: =3D Ordered nulls: false Unknown return value: false Negate comparison result: false This corresponds to "NU_BUY_CPY =3D 99" (verified by tracing, where the qua= lifier value is '99'), which is always true for all rows of ESVNB0 and ESVN= E1. But for "new-style-sql.txt", we see *two* scan qualifiers: scan qualifiers: Column[0][0] Id: 0 Operator: =3D Ordered nulls: false Unknown return value: false Negate comparison result: false Column[0][1] Id: 3 Operator: =3D Ordered nulls: false Unknown return value: false Negate comparison result: false The second qualifier is on the fourth column and has a value of '0', which = appears to be coming from the "NU_CPY_GRP =3D 0" predicate. That qualifier= is eliminating all rows from the scans (for both ESVNB0 and ESVNE1), leadi= ng to zero results. Given this potentially erroneus theory, it appears that somehow the qualifi= ers for the outer-most WHERE clause are being mapped incorrectly onto ESVNB= 0 and ESVNE1. Maybe this is a transitive closure computation problem, mayb= e it's something else entirely. At this point I don't know, I'm just dumpi= ng what I've seen thus far... =20 > Different result rows depending on the sequence of INNER JOIN and OUTER J= OIN > -------------------------------------------------------------------------= --- > > 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.zi= p, 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=3DResult of testprogram} > Derby=3D10.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=3DSQL 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_SP= Y_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 =3D O4Work.ESVNA5.NU_BUY_CPY AND O4Work.ESVN02.NU_ODR =3D O4Wor= k.ESVNA5.NU_ODR AND O4Work.ESVN02.NU_PST =3D O4Work.ESVNA5.NU_PST) =3D 0 TH= EN 'N' ELSE 'Y' END AS POPA_PictureID, CASE WHEN (SELECT COUNT(O4Work.ESVNG= 3.NU_ODR) FROM O4Work.ESVNG3 WHERE O4Work.ESVN01.NU_BUY_CPY =3D O4Work.ESVN= G3.NU_BUY_CPY AND O4Work.ESVN01.NU_ODR =3D O4Work.ESVNG3.NU_ODR) =3D 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_ArosContr= actNo, O4Work.ESVN02.NU_ST3 AS POP_StatusNo, O4Work.ESVN02.DA_CAE AS POP_Cr= eationDate, O4Work.ESVN02.DA_LAT_AMD AS POP_LastAmendDate, O4Work.ESVNA0.NU= _SSN_IDE AS POPD_SeasonInd, O4Work.ESVNA0.NU_STL_ID1 AS POPD_StyleId, O4Wo= rk.ESVNA0.NU_SRY_ID1 AS POPD_StoryID, O4Work.ESVNA0.NU_LC1 AS POPD_LicenseI= D, 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_Pick= ingM, 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_S= ad, O4Work.ESVN03.PR_SCP AS POPC_SupCstPrice, O4Work.ESVN03.NU_SCP_CR1 AS P= OPC_SupCstPrCurr, O4Work.ESVN03.NU_ST3 AS POPC_StatusNo, O4Work.ESVN03.NU_C= OY_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.E= SVN03.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.P= R_PLN_SEL AS POPRC_SellPrice, O4Work.ESVN04.NU_PLN_SEL_PRC_CR1 AS POPRC_Sel= lPrCurr, O4Work.ESVN08.NU_AVE AS POPRCA_AdvertNo, O4Work.ESVQ00.ID_SHP AS S= HP_ShippingID, O4Work.ESVQ00.NU_SHP AS SHP_ShippingNo, O4Work.ESVNB0.NU_NTL= _PDE_ID1 AS POPDC_NationalID, O4Work.ESVNB0.NU_EQP AS POPDC_EquipNumber, O4= Work.ESVNE1.PE_OMU AS POPCC_OMU, CASE WHEN (SELECT COUNT(O4Work.ESVN07.NU_O= DR) FROM O4Work.ESVN07 WHERE O4Work.ESVN03.NU_BUY_CPY =3D O4Work.ESVN07.NU_= BUY_CPY AND O4Work.ESVN03.NU_MT1_CPY =3D O4Work.ESVN07.NU_MT1_CPY AND O4Wor= k.ESVN03.NU_ODR =3D O4Work.ESVN07.NU_ODR AND O4Work.ESVN03.NU_PST =3D O4Wor= k.ESVN07.NU_PST AND O4Work.ESVN07.FL_ALE_RMK =3D 'Y') =3D 0 THEN 'N' ELSE '= Y' END AS POPCU_AllocRem FROM O4Work.ESVN02=20 > INNER JOIN O4Work.ESVN01 ON O4Work.ESVN02.NU_BUY_CPY =3D O4Work.ESVN01.NU= _BUY_CPY AND O4Work.ESVN02.NU_ODR =3D O4Work.ESVN01.NU_ODR=20 > INNER JOIN O4Work.ESVNA0 ON O4Work.ESVN02.NU_BUY_CPY =3D O4Work.ESVNA0.NU= _BUY_CPY AND O4Work.ESVN02.NU_ODR =3D O4Work.ESVNA0.NU_ODR AND O4Work.ESVN0= 2.NU_PST =3D O4Work.ESVNA0.NU_PST=20 > INNER JOIN O4Work.ESVP00 ON O4Work.ESVNA0.ID_SRL =3D O4Work.ESVP00.ID_SRL= =20 > LEFT OUTER JOIN O4Work.ESVNA4 ON O4Work.ESVNA0.NU_BUY_CPY =3D O4Work.ESVN= A4.NU_BUY_CPY AND O4Work.ESVNA0.NU_ODR =3D O4Work.ESVNA4.NU_ODR AND O4Work.= ESVNA0.NU_PST =3D O4Work.ESVNA4.NU_PST=20 > INNER JOIN O4Work.ESVN03 ON O4Work.ESVN02.NU_BUY_CPY =3D O4Work.ESVN03.NU= _BUY_CPY AND O4Work.ESVN02.NU_ODR =3D O4Work.ESVN03.NU_ODR AND O4Work.ESVN0= 2.NU_PST =3D O4Work.ESVN03.NU_PST=20 > LEFT OUTER JOIN O4Work.ESVN04 ON O4Work.ESVN03.NU_BUY_CPY =3D O4Work.ESVN= 04.NU_BUY_CPY AND O4Work.ESVN03.NU_MT1_CPY =3D O4Work.ESVN04.NU_MT1_CPY AND= O4Work.ESVN03.NU_ODR =3D O4Work.ESVN04.NU_ODR AND O4Work.ESVN03.NU_PST =3D= O4Work.ESVN04.NU_PST=20 > LEFT OUTER JOIN O4Work.ESVN08 ON O4Work.ESVN04.NU_BUY_CPY =3D O4Work.ESVN= 08.NU_BUY_CPY AND O4Work.ESVN04.NU_RTL_CPY =3D O4Work.ESVN08.NU_RTL_CPY AND= O4Work.ESVN04.NU_MT1_CPY =3D O4Work.ESVN08.NU_MT1_CPY AND O4Work.ESVN04.NU= _ODR =3D O4Work.ESVN08.NU_ODR AND O4Work.ESVN04.NU_PST =3D O4Work.ESVN08.NU= _PST=20 > INNER JOIN O4Work.ESVQ00 ON O4Work.ESVN03.ID_SHP =3D O4Work.ESVQ00.ID_SHP= =20 > INNER JOIN O4Work.ESVNB0 ON O4Work.ESVN03.NU_BUY_CPY =3D O4Work.ESVNB0.NU= _BUY_CPY AND O4Work.ESVN03.NU_MT1_CPY =3D O4Work.ESVNB0.NU_MT1_CPY AND O4Wo= rk.ESVN03.NU_ODR =3D O4Work.ESVNB0.NU_ODR AND O4Work.ESVN03.NU_PST =3D O4Wo= rk.ESVNB0.NU_PST=20 > INNER JOIN O4Work.ESVNE1 ON O4Work.ESVN03.NU_BUY_CPY =3D O4Work.ESVNE1.NU= _BUY_CPY AND O4Work.ESVN03.NU_MT1_CPY =3D O4Work.ESVNE1.NU_MT1_CPY AND O4Wo= rk.ESVN03.NU_ODR =3D O4Work.ESVNE1.NU_ODR AND O4Work.ESVN03.NU_PST =3D O4Wo= rk.ESVNE1.NU_PST=20 > WHERE O4Work.ESVN01.NU_BUY_CPY =3D 99 AND O4Work.ESVNA0.NU_CPY_GRP =3D 0 > {code} > and the one with moved inner joins: > {code:title=3DSQL 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_SP= Y_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 =3D O4Work.ESVNA5.NU_BUY_CPY AND O4Work.ESVN02.NU_ODR =3D O4Wor= k.ESVNA5.NU_ODR AND O4Work.ESVN02.NU_PST =3D O4Work.ESVNA5.NU_PST) =3D 0 TH= EN 'N' ELSE 'Y' END AS POPA_PictureID, CASE WHEN (SELECT COUNT(O4Work.ESVNG= 3.NU_ODR) FROM O4Work.ESVNG3 WHERE O4Work.ESVN01.NU_BUY_CPY =3D O4Work.ESVN= G3.NU_BUY_CPY AND O4Work.ESVN01.NU_ODR =3D O4Work.ESVNG3.NU_ODR) =3D 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_ArosContr= actNo, O4Work.ESVN02.NU_ST3 AS POP_StatusNo, O4Work.ESVN02.DA_CAE AS POP_Cr= eationDate, O4Work.ESVN02.DA_LAT_AMD AS POP_LastAmendDate, O4Work.ESVNA0.NU= _SSN_IDE AS POPD_SeasonInd, O4Work.ESVNA0.NU_STL_ID1 AS POPD_StyleId, O4Wo= rk.ESVNA0.NU_SRY_ID1 AS POPD_StoryID, O4Work.ESVNA0.NU_LC1 AS POPD_LicenseI= D, 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_Pick= ingM, 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_S= ad, O4Work.ESVN03.PR_SCP AS POPC_SupCstPrice, O4Work.ESVN03.NU_SCP_CR1 AS P= OPC_SupCstPrCurr, O4Work.ESVN03.NU_ST3 AS POPC_StatusNo, O4Work.ESVN03.NU_C= OY_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.E= SVN03.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.P= R_PLN_SEL AS POPRC_SellPrice, O4Work.ESVN04.NU_PLN_SEL_PRC_CR1 AS POPRC_Sel= lPrCurr, O4Work.ESVN08.NU_AVE AS POPRCA_AdvertNo, O4Work.ESVQ00.ID_SHP AS S= HP_ShippingID, O4Work.ESVQ00.NU_SHP AS SHP_ShippingNo, O4Work.ESVNB0.NU_NTL= _PDE_ID1 AS POPDC_NationalID, O4Work.ESVNB0.NU_EQP AS POPDC_EquipNumber, O4= Work.ESVNE1.PE_OMU AS POPCC_OMU, CASE WHEN (SELECT COUNT(O4Work.ESVN07.NU_O= DR) FROM O4Work.ESVN07 WHERE O4Work.ESVN03.NU_BUY_CPY =3D O4Work.ESVN07.NU_= BUY_CPY AND O4Work.ESVN03.NU_MT1_CPY =3D O4Work.ESVN07.NU_MT1_CPY AND O4Wor= k.ESVN03.NU_ODR =3D O4Work.ESVN07.NU_ODR AND O4Work.ESVN03.NU_PST =3D O4Wor= k.ESVN07.NU_PST AND O4Work.ESVN07.FL_ALE_RMK =3D 'Y') =3D 0 THEN 'N' ELSE '= Y' END AS POPCU_AllocRem FROM O4Work.ESVN02=20 > INNER JOIN O4Work.ESVN01 ON O4Work.ESVN02.NU_BUY_CPY =3D O4Work.ESVN01.NU= _BUY_CPY AND O4Work.ESVN02.NU_ODR =3D O4Work.ESVN01.NU_ODR=20 > INNER JOIN O4Work.ESVNA0 ON O4Work.ESVN02.NU_BUY_CPY =3D O4Work.ESVNA0.NU= _BUY_CPY AND O4Work.ESVN02.NU_ODR =3D O4Work.ESVNA0.NU_ODR AND O4Work.ESVN0= 2.NU_PST =3D O4Work.ESVNA0.NU_PST=20 > INNER JOIN O4Work.ESVP00 ON O4Work.ESVNA0.ID_SRL =3D O4Work.ESVP00.ID_SRL= =20 > INNER JOIN O4Work.ESVN03 ON O4Work.ESVN02.NU_BUY_CPY =3D O4Work.ESVN03.NU= _BUY_CPY AND O4Work.ESVN02.NU_ODR =3D O4Work.ESVN03.NU_ODR AND O4Work.ESVN0= 2.NU_PST =3D O4Work.ESVN03.NU_PST=20 > INNER JOIN O4Work.ESVNB0 ON O4Work.ESVN03.NU_BUY_CPY =3D O4Work.ESVNB0.NU= _BUY_CPY AND O4Work.ESVN03.NU_MT1_CPY =3D O4Work.ESVNB0.NU_MT1_CPY AND O4Wo= rk.ESVN03.NU_ODR =3D O4Work.ESVNB0.NU_ODR AND O4Work.ESVN03.NU_PST =3D O4Wo= rk.ESVNB0.NU_PST=20 > INNER JOIN O4Work.ESVQ00 ON O4Work.ESVN03.ID_SHP =3D O4Work.ESVQ00.ID_SHP= =20 > LEFT OUTER JOIN O4Work.ESVNA4 ON O4Work.ESVNA0.NU_BUY_CPY =3D O4Work.ESVN= A4.NU_BUY_CPY AND O4Work.ESVNA0.NU_ODR =3D O4Work.ESVNA4.NU_ODR AND O4Work.= ESVNA0.NU_PST =3D O4Work.ESVNA4.NU_PST=20 > LEFT OUTER JOIN O4Work.ESVN04 ON O4Work.ESVN03.NU_BUY_CPY =3D O4Work.ESVN= 04.NU_BUY_CPY AND O4Work.ESVN03.NU_MT1_CPY =3D O4Work.ESVN04.NU_MT1_CPY AND= O4Work.ESVN03.NU_ODR =3D O4Work.ESVN04.NU_ODR AND O4Work.ESVN03.NU_PST =3D= O4Work.ESVN04.NU_PST=20 > LEFT OUTER JOIN O4Work.ESVN08 ON O4Work.ESVN04.NU_BUY_CPY =3D O4Work.ESVN= 08.NU_BUY_CPY AND O4Work.ESVN04.NU_RTL_CPY =3D O4Work.ESVN08.NU_RTL_CPY AND= O4Work.ESVN04.NU_MT1_CPY =3D O4Work.ESVN08.NU_MT1_CPY AND O4Work.ESVN04.NU= _ODR =3D O4Work.ESVN08.NU_ODR AND O4Work.ESVN04.NU_PST =3D O4Work.ESVN08.NU= _PST=20 > INNER JOIN O4Work.ESVNE1 ON O4Work.ESVN03.NU_BUY_CPY =3D O4Work.ESVNE1.NU= _BUY_CPY AND O4Work.ESVN03.NU_MT1_CPY =3D O4Work.ESVNE1.NU_MT1_CPY AND O4Wo= rk.ESVN03.NU_ODR =3D O4Work.ESVNE1.NU_ODR AND O4Work.ESVN03.NU_PST =3D O4Wo= rk.ESVNE1.NU_PST=20 > WHERE O4Work.ESVN01.NU_BUY_CPY =3D 99 AND O4Work.ESVNA0.NU_CPY_GRP =3D 0 > {code} > Another curious behavior is that leaving out the condition {{O4Work.ESVNA= 0.NU_CPY_GRP =3D 0}} in the first SQL will give us the result of 924 all ha= ving O4Work.ESVNA0.NU_CPY_GRP =3D 0. So evaluation of the condition is bugg= y, 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 cha= in 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-L= ibraries) and the several RUNTIMESTATISTICS. --=20 This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.