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
Code:
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 SQLCode:
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.
Anyone can provide suggestion on that?
Thanks
Login
FAQ
Search