Hi and Happy new year in the test case I removed some tables to make it simple and the OC1USER is one of them. you said it worked in trunk version, I am a bit reluctant to use nightly builds in our application because there may be some other bug that take us towards another problem and our users cannot accept it, Hope you understand me? ;) I prefer to use when they are released.
We still ask our users not to create such a big report and get along with simple ones. Would you please test it in OpenJPA 1.2.0 to see if it works ? lots of thanks Dinkar Rao wrote: > > Hi, > > Your testcase works for me with no errors on OpenJPA/trunk and > Oracle/XE 10.2. (I haven't tried it on OpenJPA 1.2.x yet). > > The generated native query you posted does not seem to match the > original JPQL. There are references to the OC1USER table, which does > not correspond to any entity in your test set, nor is it used as a > supporting table. > > I'm pasting my generated native query below - it corresponds to the > JPQL in your testcase. With this query, the problem you mention in > COMMENT1 does not occur. For COMMENT3, aliases t0, t1, t2 and t3 are > all in scope by being declared in the outermost FROM. > > Here's the generated native query I get (I replaced parameters with 1s > so I could also try it from sql+): > > SELECT > t0.G1017ID, t0.G1017APPLYDT, t0.G1017APPROVDT, t12.O1001ID, > t12.O1001COD, t12.O1001NAM, t0.G1017CASHCOLVCR, t0.G1017EXPLAN, > t0.G1017FOLIONO, t0.G1017INTBRN, t0.G1017INTCOMP, t0.G1017ISSUEDT, > t0.G1017ORIGVCHNO, t0.G1017SRLNO, t0.G1017TRANSNO, t0.G1017VCRNO, > t0.G1017VCRSUSPNO > FROM > GL1VOUCHER t0, GL1VOUCHERENT t1, > GL1ACCT t2, GL1DCTRVRET t3, OF1BRANCH t12 > WHERE ( > 1 = 1 AND > (1 = 1 OR t0.G1017BRAISSUE IS NULL) AND > (1 = 1 OR t0.G1017BRAISSUE IN (NULL)) AND > (1 = 1 OR t0.G1017ISSUEDT >= NULL) AND > (1 = 1 OR t0.G1017ISSUEDT <= NULL) AND > (1 = 1 OR t0.G1017APPLYDT >= NULL) AND > (1 = 1 OR t0.G1017APPLYDT <= NULL) AND > (1 = 1 OR t0.G1017APPROVDT >= NULL) AND > (1 = 1 OR t0.G1017APPROVDT <= NULL) AND > (1 = 1 OR t0.G1017VCRNO >= NULL) AND > (1 = 1 OR t0.G1017VCRNO <= NULL) AND > (1 = 1 OR t0.G1017TRANSNO IS NULL) AND > (1 = 1 OR t2.G1GLET2ACCT IN (NULL)) AND > (1 = 1 OR t0.G1017CASHCOLVCR IS NULL) AND > (1 = 1 OR t0.G1017INTBRN IS NULL) AND > (1 = 1 OR t2.G1BRNCH2ACCT IN (NULL)) AND > (1 = 1 OR t0.G1017BRAISSUE IN (NULL)) AND > (1 = 1 OR t0.G1017INTCOMP IS NULL) AND > (1 = 1 OR t2.G1CURR2ACCT IS NULL) AND > (1 = 1 OR t2.G1CURR2ACCT IN (NULL)) AND > (1 = 1 OR t1.G1018CURT2VCREN IS NULL) AND > (1 = 1 OR t1.G1018CURT2VCREN IN (NULL)) AND > (1 = 1 OR t1.G1018CURRRT IN (NULL)) AND > (1 = 1 OR t0.G1017VCRNO IN (NULL)) AND > (1 = 1 OR t0.G1017FOLIONO IN (NULL)) AND > (1 = 1 OR t0.G1017EXPLAN IS NULL) AND > (1 = 1 OR t1.G1018EXPLAN IS NULL) AND > (1 = 1 OR NULL >= ANY ( > SELECT t4.G1018AMNT > FROM GL1VOUCHERENT t4, GL1ACCT t5 > WHERE > (t4.G1VCR2VRCENT = t0.G1017ID AND > (1 = 1 OR t5.G1CURR2ACCT IS NULL) AND > (1 = 1 OR 1 <> 1 AND t4.G1018AMNT < 1 OR 1 <> 1 AND > t4.G1018AMNT > 1) > ) AND > t4.G1ACC2VCRENT = t5.G1004ID(+)) > ) AND > (1 = 1 OR NULL <= ANY ( > SELECT t6.G1018AMNT > FROM GL1VOUCHERENT t6, GL1ACCT t7 > WHERE ( > t6.G1VCR2VRCENT = t0.G1017ID AND > (1 = 1 OR t7.G1CURR2ACCT IS NULL) AND > (1 = 1 OR 1 <> 1 AND t6.G1018AMNT < 1 OR 1 <> 1 AND > t6.G1018AMNT > 1) > ) AND > t6.G1ACC2VCRENT = t7.G1004ID(+))) AND > (1 = 1 OR NULL <= ( > SELECT SUM(ABS(t8.G1018AMNT)) > FROM GL1VOUCHERENT t8, GL1ACCT t9 > WHERE ( > t8.G1VCR2VRCENT = t0.G1017ID AND > (1 = 1 OR t9.G1CURR2ACCT IS NULL) AND > (1 = 1 OR 1 <> 1 AND t8.G1018AMNT < 1 OR 1 <> 1 AND > t8.G1018AMNT > 1) > ) AND > t8.G1ACC2VCRENT = t9.G1004ID(+)) > ) AND > (1 = 1 OR NULL >= ( > SELECT SUM(ABS(t10.G1018AMNT)) > FROM GL1VOUCHERENT t10, GL1ACCT t11 > WHERE ( > t10.G1VCR2VRCENT = t0.G1017ID AND > (1 = 1 OR t11.G1CURR2ACCT IS NULL) AND > (1 = 1 OR 1 <> 1 AND t10.G1018AMNT < 1 OR 1 <> 1 AND > t10.G1018AMNT > 1) > ) AND > t10.G1ACC2VCRENT = t11.G1004ID(+)) > ) AND > (1 = 1 OR t2.G1004SRLNO >= NULL) AND > (1 = 1 OR t2.G1004SRLNO <= NULL) AND > (1 = 1 OR t2.G1004COD IS NULL) AND > (1 = 1 OR t3.G1CC2DCTRVRET IS NULL) AND > (1 = 1 OR t0.G1017FOLIONO IS NULL) AND > (1 = 1 OR t1.G1018FOLIONO IS NULL) AND > (1 = 1 OR t1.G1018BOP IS NULL) AND > 1 = 1 > ) AND > t0.G1017ID = t1.G1VCR2VRCENT(+) AND > t0.G1017BRAISSUE = t12.O1001ID(+) > AND t1.G1ACC2VCRENT = t2.G1004ID(+) > AND t1.G1018ID = t3.G1VRET2DCTRVRET(+) > > > Thanks, > Dinkar > > -- View this message in context: http://n2.nabble.com/possible-bug-in-building-SQL-statement-tp1803062p2098354.html Sent from the OpenJPA Developers mailing list archive at Nabble.com.
