ORing in HQL

grafpoo · **Joined:** Fri Oct 15, 2004 4:54 pm **Posts:** 32

Hibernate version:
2.1.6

Mapping documents:

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >
    
<hibernate-mapping>
 <class name="tess.Division" table="DIVISION">
    <id name="id"
        type="java.lang.Integer"
        column="DIVISION_ID">
        <generator class="assigned" />
    </id>
    <property
        name="name"
        type="java.lang.String"
        column="DIVISION_NAME"
        not-null="true"
        length="60"/>
 </class>
</hibernate-mapping>

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >
    
<hibernate-mapping>
 <class name="tess.Person" table="PERSON">
    <id name="id"
        type="java.lang.Integer"
        column="PERSON_ID">
        <generator class="increment" />
    </id>
    <property
        name="name"
        type="java.lang.String"
        column="PERSON_NAME"
        not-null="true"
        length="60"/>
    <many-to-one
        name="division"
        class="tess.Division"
        not-null="true">
        <column name="DIVISION_ID" />
    </many-to-one>
 </class>
</hibernate-mapping>

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >
    
<hibernate-mapping>
 <class name="tess.Report" table="REPORT">
    <id name="id"
        type="java.lang.Integer"
        column="REPORT_ID">
        <generator class="increment" />
    </id>
    <property
        name="report"
        type="java.lang.String"
        column="REPORT"
        not-null="true"
        length="255"/>
    <property
        name="status"
        type="java.lang.String"
        column="STATUS"
        not-null="true"
        length="12"/>
    <many-to-one
        name="author"
        class="tess.Person"
        not-null="true">
        <column name="PERSON_ID" />
    </many-to-one>
 </class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

Code:

String HQL =
   "from tess.Report as report " +
   "where (report.status != 'Draft') or (report.author.division.id = ?) ";
List parms = new ArrayList();
parms.add(new Integer(1));
List list =
   session.find(HQL, parms.toArray(), new Type[] {Hibernate.INTEGER});

Full stack trace of any exception that occurs:

Name and version of the database you are using:
Sybase ASE 12.5

The generated SQL (show_sql=true):

Code:

select report0_.REPORT_ID as REPORT_ID,
       report0_.REPORT as REPORT, 
       report0_.STATUS as STATUS,
       report0_.PERSON_ID as PERSON_ID
 from REPORT report0_, PERSON person1_ 
where (((report0_.STATUS!='Draft' ))
     or((person1_.DIVISION_ID=1
     and report0_.PERSON_ID=person1_.PERSON_ID)))

Debug level Hibernate log excerpt:
n/a

the setup is i have reports that are written by people, each of whom is assigned to a division. reports can have two statuses, 'draft' or 'complete', and folks in the same division can see draft reports by each other. so my find, in english, is "give me all the complete reports and the draft reports from folks in my division". that is what the HQL above is meant to convey.

the sql that gets generated doesn't do what i want it to. the problem is that the first part doesn't get the join condition so it gets a cartesion product. bottom line is i get n copies of non-draft reports, where n is the number of person records.

basically, the sql for the where is (A | (B & C)) when it needs to be ((A | B) & C)

so, does my HQL look okay?

grafpoo · **Joined:** Fri Oct 15, 2004 4:54 pm **Posts:** 32

i found a bug report on this from the end of september that referenced two or three previous bug reports on the same subject, all of which were rejected. the comment, and i have no reason to disbelieve it, was "you should use a from clause join for your problem. There is reasoning behind the current behavior."

so my two questions are:
1) what is a "from join clause"?
2) what is the reasoning behind the current behavior? either i don't understand HQL very well (which is, of course, true), or this is unexpected behavior

thanks.

anthony · **Posted:** Fri Oct 29, 2004 2:31 pm

don't have much time but try
something like

select report
from tess.Report as report
join report.author auth
join auth.division div
where (report.status != 'Draft'
or div.id = ?);