Uploaded image for project: 'Hibernate ORM'
  1. HHH-1451

Incorrect Parentheses in SQL Translation


    • Type: Bug
    • Status: Closed
    • Priority: Critical
    • Resolution: Rejected
    • Affects versions: 3.1.2
    • Fix versions: None
    • Components: query-sql
    • Labels:
    • Environment:
      Hibernate 3.1.2, Oracle9i, IBM WebSphere 6.0
    • Bug Testcase Reminder (view):

      Bug reports should generally be accompanied by a test case!

    • Last commented by a user?:
    • Sprint:


      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

      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_
      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.




            • Votes:
              0 Vote for this issue
              0 Start watching this issue


              • Created: