Hibernate version: 3.0.5

Mapping documents: (see below)

Full stack trace of any exception that occurs: (see below)

Name and version of the database you are using: postgresql 8.0

The generated SQL (show_sql=true): (see below)


I'm trying to use org.hibernate.criterion.DetachedCriteria to generate a query.

The class/table I want to query (Report) has a many-to-many relationship to class/table Category.

Category itself has a self-join, implemented in the database as a (nullable) reference to a parent Category.

When I attempt to restrict Reports to those "in" some category, the generated sql includes the Category self-join, which makes the SQL query incorrect.


In the code, "alternatives.toCriterion()" is a function that returns a Criterion.

The "obvious" code yields bad sql:
DetachedCriteria d = DetachedCriteria.forClass( Report.class ) ;
if( ! retrieve )
d = d.setProjection( Projections.rowCount() ) ;
return d.add( alternatives.toCriterion() ).createCriteria( "categories" ).add( Restrictions.idEq( new Long( 1024 ) ) ) ;


Hibernate operation: could not execute query; bad SQL grammar [select count(*) as y0_ from techex.rpt_reports this_ inner join techex.rpt_report_categories categories3_ on this_.id=categories3_.report_id inner join techex.categories category1_ on categories3_.category_id=category1_.id left outer join techex.categories children5_ on category1_.id=children5_.parent_category_id where 1=1 and category1_.id = ? order by children5_.name asc]; nested exception is java.sql.SQLException: ERROR: column "children5_.name" must appear in the GROUP BY clause or be used in an aggregate function


Adding the line:
d.setResultTransformer(DetachedCriteria.DISTINCT_ROOT_ENTITY) ;
does not change the SQL.


An attempted work-around yields an exception:

DetachedCriteria d = DetachedCriteria.forClass( Report.class ) ;
if( ! retrieve )
d = d.setProjection( Projections.rowCount() ) ;
DetachedCriteria e = DetachedCriteria.forClass( Category.class ).add( Restrictions.idEq( new Long( 1024 ) ) ) ;
d.add( Subqueries.exists( e ) ) ;
return d.add( alternatives.toCriterion() ) ;

java.lang.NullPointerException
at org.hibernate.loader.criteria.CriteriaQueryTranslator.getProjectedTypes(CriteriaQueryTranslator.java:298)
at org.hibernate.criterion.SubqueryExpression.toSqlString(SubqueryExpression.java:56)
at org.hibernate.loader.criteria.CriteriaQueryTranslator.getWhereCondition(CriteriaQueryTranslator.java:314)
at org.hibernate.loader.criteria.CriteriaLoader.<init>(CriteriaLoader.java:81)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1303)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:300)
at org.springframework.orm.hibernate3.HibernateTemplate$35.doInHibernate(HibernateTemplate.java:944)


At this point I see options but to code the join as a SQLCriterion, or to explicitly represent the many-to- many join as a java class. Am I doing something wrong, or is this a Hibernate bug?

Please see below for the Hibernate mappings for the Report and Category classes:

Report.hbm.xml:

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping
>
<class
name="com.ofi.techex.model.Report"
table="rpt_reports"
schema="techex"
>

<id
name="id"
column="id"
type="java.lang.Long"
unsaved-value="null"
>
<generator class="sequence">
<param name="sequence">techex.rpt_reports_id_seq</param>
<!--
To add non XDoclet generator parameters, create a file named
hibernate-generator-params-Report.xml
containing the additional parameters and place it in your merge dir.
-->
</generator>
</id>

<timestamp
name="updateDate"
column="update_date"
/>

<many-to-one
name="reportType"
class="com.ofi.techex.model.ReportType"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="report_type_id"
/>

<many-to-one
name="organization"
class="com.ofi.techex.model.Organization"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="org_id"
/>

<many-to-one
name="acquisitionMethod"
class="com.ofi.techex.model.AcquisitionApp"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="acquisition_method_id"
/>

<many-to-one
name="stageOfDevelopment"
class="com.ofi.techex.model.StageOfDevelopment"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="stage_of_development_id"
/>

<property
name="title"
type="java.lang.String"
update="true"
insert="true"
column="title"
length="256"
/>

<property
name="documentAbstract"
type="java.lang.String"
update="true"
insert="true"
column="abstract"
length="1000"
/>

<property
name="author"
type="java.lang.String"
update="true"
insert="true"
column="author"
length="100"
/>

<property
name="respondToEmail"
type="java.lang.String"
update="true"
insert="true"
column="respond_to_email"
length="256"
/>

<property
name="createDate"
type="java.sql.Timestamp"
update="true"
insert="false"
column="create_date"
/>

<set
name="categories"
table="rpt_report_categories"
schema="techex"
lazy="true"
inverse="true"
cascade="save-update"
sort="unsorted"
order-by="id asc"
>

<key
column="report_id"
>
</key>

<many-to-many
class="com.ofi.techex.model.Category"
column="category_id"
outer-join="auto"
/>

</set>

<set
name="attachedFiles"
table="files"
schema="techex"
lazy="true"
cascade="none"
sort="unsorted"
>

<key
column="report_id"
>
</key>

<one-to-many
class="com.ofi.techex.model.AttachedFile"
/>

</set>

<!--
To add non XDoclet property mappings, create a file named
hibernate-properties-Report.xml
containing the additional properties and place it in your merge dir.
-->

</class>

</hibernate-mapping>

Category.hbm.xml:
<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping
>
<class
name="com.ofi.techex.model.Category"
table="categories"
schema="techex"
mutable="false"
>

<id
name="id"
column="id"
type="java.lang.Long"
unsaved-value="null"
>
<generator class="sequence">
<param name="sequence">techex.categories_id_seq</param>
<!--
To add non XDoclet generator parameters, create a file named
hibernate-generator-params-Category.xml
containing the additional parameters and place it in your merge dir.
-->
</generator>
</id>

<timestamp
name="updateDate"
column="update_date"
/>

<property
name="name"
type="java.lang.String"
update="true"
insert="true"
column="name"
length="256"
/>

<property
name="description"
type="java.lang.String"
update="true"
insert="true"
column="description"
length="256"
/>

<many-to-one
name="parentCategory"
class="com.ofi.techex.model.Category"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="parent_category_id"
/>

<many-to-one
name="organization"
class="com.ofi.techex.model.Organization"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="org_id"
/>

<set
name="children"
table="categories"
schema="techex"
lazy="true"
inverse="true"
cascade="save-update"
sort="unsorted"
order-by="name asc"
outer-join="true"
>

<key
column="parent_category_id"
>
</key>

<one-to-many
class="com.ofi.techex.model.Category"
/>

</set>

<set
name="reports"
table="rpt_report_categories"
schema="techex"
lazy="true"
inverse="false"
cascade="save-update"
sort="unsorted"
order-by="report_id asc"
>

<key
column="category_id"
>
</key>

<many-to-many
class="com.ofi.techex.model.Report"
column="report_id"
outer-join="true"
/>

</set>

<property
name="createDate"
type="java.sql.Timestamp"
update="true"
insert="false"
column="create_date"
/>

<!--
To add non XDoclet property mappings, create a file named
hibernate-properties-Category.xml
containing the additional properties and place it in your merge dir.
-->

</class>

</hibernate-mapping>


--end--