Mailing-List: contact dev-help@openjpa.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@openjpa.apache.org
Received-SPF: pass (athena.apache.org: domain of
 lists+1214986235816-210739@n2.nabble.com designates 216.139.236.158 as
 permitted sender)
Message-ID: <1230237510771-1803062.post@n2.nabble.com>
Date: Thu, 25 Dec 2008 12:38:30 -0800 (PST)
From: is_maximum <mnrz57@gmail.com>
To: dev@openjpa.apache.org
Subject: possible bug in building SQL statement
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_915_26135264.1230237510770"

------=_Part_915_26135264.1230237510770
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit


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.

------=_Part_915_26135264.1230237510770--