We're updating the issue view to help you get more done. 

Incorrect Parentheses in SQL Translation

Description

I tried to execute a SELECT HQL with several logic expressions with using parentheses and it's translated into a SQL. However, the returned result is unexpected and found that there has issue on the translated SQL and which those parentheses are located incorrectly!

Prepared HQL

FROM Campaign AS c
LEFT JOIN FETCH c.campInfo
WHERE c.deleted = :deleted
AND ( ( c.condProdCate IS NOT NULL
AND c.condProdCate.cateLevel = :cateLevelL1PC
AND c.condProdCate.cateId = :cateIdL1PC1 )
OR ( c.condProdCate IS NOT NULL
AND c.condProdCate.cateLevel = :cateLevelL2PC
AND c.condProdCate.parentCate.cateId = :cateIdL2PC1 )
OR ( c.condProdCate IS NOT NULL
AND c.condProdCate.cateLevel = :cateLevelL3PC
AND c.condProdCate.parentCate.parentCate.cateId = :cateIdL3PC1 )
OR ( c.condProduct IS NOT NULL
AND c.condProduct.prodCate.parentCate.parentCate.cateId = :cateIdP1 ) )
AND c.webFlag = :webFlag
AND c.cicFlag = :cicFlag
ORDER BY c.campId ASC

Translated SQL

select
campaign0_.CAMPID as CAMPID32_0_,
campinfo1_.CAMPID as CAMPID34_1_,
campaign0_.CAMPTARGET as CAMPTARGET32_0_,
campaign0_.EFFECTSTARTDATE as EFFECTST3_32_0_,
campaign0_.EFFECTENDDATE as EFFECTEN4_32_0_,
campaign0_.CAMPNAMEEN as CAMPNAMEEN32_0_,
campaign0_.CAMPNAMEZH as CAMPNAMEZH32_0_,
campaign0_.CONDPRODID as CONDPRODID32_0_,
campaign0_.CONDCATEID as CONDCATEID32_0_,
campaign0_.CONDMINAMOUNT as CONDMINA9_32_0_,
campaign0_.WEBFLAG as WEBFLAG32_0_,
campaign0_.CICFLAG as CICFLAG32_0_,
campaign0_.DISCOUNTFLAG as DISCOUN12_32_0_,
campaign0_.DISCOUNTRATE as DISCOUN13_32_0_,
campaign0_.FREESHIPFLAG as FREESHI14_32_0_,
campaign0_.EXTRAPAYFLAG as EXTRAPA15_32_0_,
campaign0_.EXTRAMETHODID as EXTRAME16_32_0_,
campaign0_.PRIORITYCATEID as PRIORIT17_32_0_,
campaign0_.PRIORITYORDER as PRIORIT18_32_0_,
campaign0_.CLOSED as CLOSED32_0_,
campaign0_.DISABLED as DISABLED32_0_,
campaign0_.CREATEUSER as CREATEUSER32_0_,
campaign0_.CREATEDATE as CREATEDATE32_0_,
campaign0_.MODIFYUSER as MODIFYUSER32_0_,
campaign0_.MODIFYDATE as MODIFYDATE32_0_,
campaign0_.DELETED as DELETED32_0_,
campaign0_.DELETEUSER as DELETEUSER32_0_,
campaign0_.DELETEDATE as DELETEDATE32_0_,
campinfo1_.SHORTDESCEN as SHORTDES2_34_1_,
campinfo1_.SHORTDESCZH as SHORTDES3_34_1_,
campinfo1_.LONGDESCEN as LONGDESCEN34_1_,
campinfo1_.LONGDESCZH as LONGDESCZH34_1_,
campinfo1_.CREATEUSER as CREATEUSER34_1_,
campinfo1_.CREATEDATE as CREATEDATE34_1_,
campinfo1_.MODIFYUSER as MODIFYUSER34_1_,
campinfo1_.MODIFYDATE as MODIFYDATE34_1_,
campinfo1_.DELETED as DELETED34_1_,
campinfo1_.DELETEUSER as DELETEUSER34_1_,
campinfo1_.DELETEDATE as DELETEDATE34_1_
from
CAMPAIGN campaign0_,
CAMPINFO campinfo1_,
PRODCATE prodcate2_,
PRODCATE prodcate7_,
PRODUCT product8_,
PRODCATE prodcate9_,
PRODCATE prodcate10_
where prodcate9_.PARENTCATEID=prodcate10_.CATEID
and product8_.CATEID=prodcate9_.CATEID
and campaign0_.CONDPRODID=product8_.PRODID
and prodcate2_.PARENTCATEID=prodcate7_.CATEID
and campaign0_.CONDCATEID=prodcate2_.CATEID
and campaign0_.CAMPID=campinfo1_.CAMPID
and campaign0_.DELETED=?
and ((campaign0_.CONDCATEID is not null)
and prodcate2_.CATELEVEL=?
and campaign0_.CONDCATEID=?
or (campaign0_.CONDCATEID is not null)
and prodcate2_.CATELEVEL=?
and prodcate2_.PARENTCATEID=?
or (campaign0_.CONDCATEID is not null)
and prodcate2_.CATELEVEL=?
and prodcate7_.PARENTCATEID=?
or (campaign0_.CONDPRODID is not null)
and prodcate10_.PARENTCATEID=?)
and campaign0_.WEBFLAG=?
and campaign0_.CICFLAG=?
order by campaign0_.CAMPID ASC

It seems normal when I tested with all "AND" are replaced by "OR" inside parentheses. However, the close parenthsis is incorrect once using "AND". I would like to modify grammar to fix the issue but not success.

Environment

Hibernate 3.1.2, Oracle9i, IBM WebSphere 6.0

Status

Assignee

Unassigned

Reporter

Bobby Tam

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

3.1.2

Priority

Critical