You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by is_maximum <mn...@gmail.com> on 2008/12/25 21:38:30 UTC
possible bug in building SQL statement
Hello,
I am using OpenJPA 1.2.0 and Oracle 9i
Until now, we had no problem with EJBQL but recently I have created a big
select statement and seems the OpenJPA creates a wrong Oracle statement:
here is the query I have created:
String s = "SELECT vchr " +
"FROM Voucher AS vchr left join vchr.entries vchrEnt left outer join
vchrEnt.dynamicCenters dc left join vchrEnt.account acc " +
"WHERE 1 = 1 AND " +
"(?1 IS NULL OR vchr.branchIssued = (?1)) AND " +
"(?2 IS NULL OR vchr.voucherStatus IN (?2)) AND " +
"(?3 IS NULL OR vchr.issueDate >= (?3)) AND " +
"(?4 IS NULL OR vchr.issueDate <= (?4)) AND " +
"(?5 IS NULL OR vchr.applyDate >= (?5)) AND " +
"(?6 IS NULL OR vchr.applyDate <= (?6)) AND " +
"(?7 IS NULL OR vchr.approveDate >= (?7)) AND " +
"(?8 IS NULL OR vchr.approveDate <= (?8)) AND " +
"(?9 IS NULL OR vchr.voucherNo >= (?9)) AND " +
"(?10 IS NULL OR vchr.voucherNo <= (?10)) AND " +
"(?11 IS NULL OR vchr.transactionNo = (?11)) AND " +
"(?12 IS NULL OR acc.generalLedgerEntry IN (?12)) AND " +
"(?13 IS NULL OR vchr.cashCollectionVoucher = (?13)) AND " +
"(?14 IS NULL OR vchr.interBranch = (?14)) AND " +
"(?15 IS NULL OR acc.branch IN (?15)) AND " +
"(?16 IS NULL OR vchr.branchIssued IN (?16)) AND " +
"(?17 IS NULL OR vchr.interCompany = (?17)) AND " +
"(?18 IS NULL OR acc.currencyCoin = (?18)) AND " +
"(?19 IS NULL OR acc.currencyCoin IN (?19)) AND " +
"(?20 IS NULL OR vchrEnt.currencyRateType = (?20)) AND " +
"(?21 IS NULL OR vchrEnt.currencyRateType IN (?21)) AND " +
"(?22 IS NULL OR vchrEnt.currencyRate IN (?22)) AND " +
"(?23 IS NULL OR vchr.approvedBy IN (?23)) AND " +
"(?24 IS NULL OR vchr.preparedBy IN (?24)) AND " +
"(?25 IS NULL OR vchr.explanation = (?25)) AND " +
"(?26 IS NULL OR vchrEnt.explanation = (?26)) AND " +
"(?27 IS NULL OR ?27 >= ANY ( select ve.amount " + //TODO abs(ve.amount)
"from VoucherEntry as ve " +
"where ve.voucher.id = vchr.id and " +
"((?39 is null) or (ve.account.currencyCoin = ?39) ) and " +
"((?38 is null) or (?38 = true and ve.amount < 0 ) or (?38 =
false and ve.amount > 0)) " +
")" +
") AND " +
"(?28 IS NULL OR ?28 <= ANY ( select ve.amount " +
"from VoucherEntry as ve " +
"where ve.voucher.id = vchr.id and " +
"((?39 is null) or (ve.account.currencyCoin = ?39) ) and " +
"((?38 is null) or (?38 = true and ve.amount < 0 ) or (?38 =
false and ve.amount > 0)) )) AND " +
"(?29 IS NULL OR ?29 <= (select sum(abs(ve.amount)) " +
"from VoucherEntry as ve " +
"where ve.voucher.id = vchr.id and " +
"(?39 is null or ve.account.currencyCoin = ?39 ) and " +
"(?38 is null or (?38 = true and ve.amount < 0 ) or (?38 = false
and ve.amount > 0))" +
")" +
") AND " +
"(?30 IS NULL OR ?30 >= (select sum(abs(ve.amount)) " +
"from VoucherEntry as ve " +
"where ve.voucher.id = vchr.id and " +
"(?39 is null or ve.account.currencyCoin = ?39 ) and " +
"(?38 is null or (?38 = true and ve.amount < 0 ) or (?38 = false
and ve.amount > 0)))) AND " +
"(?31 IS NULL OR acc.serialNo >= (?31)) AND " +
"(?32 IS NULL OR acc.serialNo <= (?32)) AND " +
"(?33 IS NULL OR acc.code = (?33)) AND " +
"(?34 IS NULL OR dc.costCenter = (?34)) AND " +
"(?35 IS NULL OR vchr.folioNo = (?35)) AND " +
"(?36 IS NULL OR vchrEnt.folioNo = (?36)) AND " +
"(?37 IS NULL OR vchrEnt.bopCode = (?37))";
in this query Voucher has a one-to-many relationship with VoucherEntry and
VoucherEntry has one-to-many relationship with DynamicCenter and
VoucherEntry has many-to-one relationship with Account
but the created Oracle statement is as follow:
SELECT t0.G1017ID, t0.G1017APPLYDT, t0.G1017APPROVDT,
t15.O1002ID, t15.O1002NAM, t16.O1001ID, t16.O1001COD, t16.O1001NAM,
t0.G1017CASHCOLVCR, t0.G1017EXPLAN, t0.G1017FOLIONO, t0.G1017INTBRN,
t0.G1017INTCOMP, t0.G1017ISSUEDT, t0.G1017ORIGVCHNO, t17.O1002ID,
t17.O1002NAM, t0.G1017SRLNO, t0.G1017TRANSNO, t18.O1004ID,
t18.O1004COD,
t18.O1004NAM, t0.G1017VCRNO, t0.G1017VCRSUSPNO, t0.G1017STAT
FROM GL1VOUCHER t0, GL1ACCT t12, GL1DCTRVRET t13, GL1VOUCHERENT t14,
OC1USER t15, OF1BRANCH t16, OC1USER t17, OS1TRNTYP t18
WHERE (? = ? AND
(1 = 1 OR t0.G1017BRAISSUE IS NULL) AND
(1 = 1 OR t0.G1017STAT 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 (?)) 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.G1APPUSR2VCR IN (NULL)) AND
(1 = 1 OR t0.G1PRPUSR2VCR 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 //COMMENT1
FROM GL1VOUCHERENT t1, GL1ACCT t2, GL1DCTRVRET t3, 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 < ? OR 1 <> 1 AND t4.G1018AMNT >
?)) AND
t4.G1ACC2VCRENT = t5.G1004ID(+))
) AND
(1 = 1 OR NULL <= ANY (SELECT t6.G1018AMNT //COMMENT2
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 < ? OR 1 <> 1 AND t6.G1018AMNT >
?)) 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 < ? OR 1 <> 1 AND t8.G1018AMNT >
?)) 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 < ? OR 1 <> 1 AND
t10.G1018AMNT > ?)) AND t10.G1ACC2VCRENT = t11.G1004ID(+))
) AND
(1 = 1 OR t12.G1004SRLNO >= NULL) AND
(1 = 1 OR t12.G1004SRLNO <= NULL) AND
(1 = 1 OR t12.G1004COD IS NULL) AND
(1 = 1 OR t13.G1CC2DCTRVRET IS NULL) AND
(1 = 1 OR t0.G1017FOLIONO IS NULL) AND
(1 = 1 OR t14.G1018FOLIONO IS NULL) AND
(1 = 1 OR t14.G1018BOP IS NULL) AND 1 = 1) AND //COMMENT3
t0.G1017ID = t1.G1VCR2VRCENT(+) AND
t0.G1APPUSR2VCR = t15.O1002ID(+) AND
t0.G1017BRAISSUE = t16.O1001ID(+) AND
t0.G1PRPUSR2VCR = t17.O1002ID(+) AND
t0.G1TRNTYP2VCR = t18.O1004ID(+) AND
t1.G1ACC2VCRENT = t2.G1004ID(+) AND
t1.G1018ID = t3.G1VRET2DCTRVRET(+)
[params=(long) 1, (long) 1, (long) 188, (long) 0, (long) 0, (long) 0,
(long) 0, (long) 0, (long) 0, (long) 0, (long) 0]} [code=904, state=42000];
in the created query everything seems ok but COMMENT1 and COMMENT3
in COMMENT1 tables GL1VOUCHERENT and GL1ACCT are used twice!!! and in
COMMENT3 t3, t2 and t1 are declared while they are not in this scope!!!
is this a bug? Do I have to post a JIRA issue?
--
View this message in context: http://n2.nabble.com/possible-bug-in-building-SQL-statement-tp1803062p1803062.html
Sent from the OpenJPA Developers mailing list archive at Nabble.com.
Re: possible bug in building SQL statement
Posted by Dinkar Rao <di...@gmail.com>.
Hi,
A happy new year to you too. Your testcase works on the latest
OpenJPA/1.2.1, but with one problem that I had to work around. The
problem is with the unique=true specification on these columns in
GeneralLedgerEntry.java:
@Column(name="G1008COD",unique = true)
@XmlElement
private String code;
and
@Column(name="G1008POS",unique = true)
@XmlElement
private String position;
The corresponding CREATE TABLE statement looks like this:
CREATE TABLE GL1GLET (
...<snip>...
PRIMARY KEY (G1008ID),
CONSTRAINT UNQ_ UNIQUE (G1008COD) DEFERRABLE,
CONSTRAINT UNQ_ UNIQUE (G1008POS) DEFERRABLE
)
This create table fails because the names of the constraints are the
same: "UNQ_".
Once I took out the unique=true clause from the @Column annotation,
the testcase worked fine.
It looks like this same-constraint-name bug has been fixed in
OpenJPA-2.0 where constraint names are composed of table+column names
to make them unique. But I could not find a JIRA issue that addressed
this problem. If you wish, please file one against 1.2.x - it's
probably a matter of backporting the fix.
FYI, I'm pasting the native query (with parameter markers retained)
generated by 1.2.1 for your test case below.
Thanks,
Dinkar
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 (
? = ? 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 < ? OR 1 <> 1 AND
t4.G1018AMNT > ?)
) 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 < ? OR 1 <> 1 AND
t6.G1018AMNT > ?)
) 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 < ? OR 1 <> 1 AND
t8.G1018AMNT > ?)
) 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 < ? OR 1 <> 1 AND
t10.G1018AMNT > ?)
) 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(+)
Re: possible bug in building SQL statement
Posted by is_maximum <mn...@gmail.com>.
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.
Re: possible bug in building SQL statement
Posted by Dinkar Rao <di...@gmail.com>.
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
Re: possible bug in building SQL statement
Posted by is_maximum <mn...@gmail.com>.
I attached a simple test case to reproduce the problem
I am using OpenJPA 1.2.0 and Oracle 9i
the error is invalid identifier from oracle which is because of the worng
sql statement created by OpenJPA
I neglected the jar files except oracle jdbc driver because of high capacity
of the uploaded file
please tell me if I have to create a JIRA issue.
is_maximum wrote:
>
> Do I have to post a test case?
>
http://n2.nabble.com/file/n2092227/test-case.rar test-case.rar
--
View this message in context: http://n2.nabble.com/possible-bug-in-building-SQL-statement-tp1803062p2092227.html
Sent from the OpenJPA Developers mailing list archive at Nabble.com.
Re: possible bug in building SQL statement
Posted by is_maximum <mn...@gmail.com>.
Do I have to post a test case?
--
View this message in context: http://n2.nabble.com/possible-bug-in-building-SQL-statement-tp1803062p2073402.html
Sent from the OpenJPA Developers mailing list archive at Nabble.com.