Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 3.0.5

Mapping documents: (truncated)

<class
name="com.fluid.engage.model.Category"
table="category">
<id
name="id"
column="id"
type="long"
unsaved-value="-1"
>
<generator class="native">
<!--
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>
<many-to-one
name="productView"
class="com.fluid.engage.model.ProductView"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="productViewId"
not-null="true" />
<set
name="images"
table="image_category"
lazy="true"
inverse="true"
cascade="none"
sort="unsorted">
<key
column="categoryId">
</key>
<many-to-many
class="com.fluid.engage.model.Image"
column="imageId"
outer-join="auto"
/>
</set>
</class>

<class name="com.fluid.engage.model.Image" table="image" >

<id
name="id"
column="id"
type="long"
unsaved-value="-1"
>
<generator class="native">
</generator>
<many-to-one
name="productView"
class="com.fluid.engage.model.ProductView"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="productViewId"
not-null="true"
/>
<set
name="categories"
table="image_category"
lazy="true"
inverse="false"
cascade="none"
sort="unsorted"
>
<key
column="imageId"
>
</key>

<many-to-many
class="com.fluid.engage.model.Category"
column="categoryId"
outer-join="auto"
/>

</set>
</class>




Code between sessionFactory.openSession() and session.close():
tx = session.beginTransaction();

...

// Load the product view.
ProductView productView = (ProductView) session.get(ProductView.class,
new Long(productViewIds[i]));

// Figure out how many MAIN images *actually are* present.

// WORKING H2
// List imageCountResult = session.find(
// "select c.images.size from Category c where c.groupName='IMAGETYPE' and c.name='MAIN' and c.productView=?",
// productView, Hibernate.entity(ProductView.class));

// BROKEN H3
List imageCountResult = session.createQuery(
"select c.images.size from Category c where c.groupName='IMAGETYPE' and c.name='MAIN' and c.productView=?")
.setEntity(0, productView)
.list();


int actualImageCount = ((Integer) imageCountResult.get(0)).intValue();
log.debug("Actual image count for product view " +
productView.getId() + ": " + actualImageCount);
counts[i] = correctImageCount - actualImageCount;
}

...

tx.rollback()




Full stack trace of any exception that occurs: (truncated)
... 37 more^M
Caused by: java.sql.SQLException: Unknown column 'image2_.categoryId' in 'field list'^M
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1997)^M
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1167)^M
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1278)^M
at com.mysql.jdbc.Connection.execSQL(Connection.java:2251)^M
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1586)^M
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)^M
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120)^M
at org.hibernate.loader.Loader.getResultSet(Loader.java:1272)^M
at org.hibernate.loader.Loader.doQuery(Loader.java:391)^M
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)^M
at org.hibernate.loader.Loader.doList(Loader.java:1593)^M
... 43 more^M
Hibernate:

Name and version of the database you are using:4.1.7

The generated SQL (show_sql=true):
select (select count(image2_.categoryId) from image_category images1_, image image2_ where category0_.id=imag
es1_.categoryId and images1_.imageId=image2_.id) as col_0_0_ from category category0_, image_category images1_, image im
age2_ where category0_.id=images1_.categoryId and images1_.imageId=image2_.id and category0_.groupName='IMAGETYPE' and category0_.name='MAIN' and category0_.productViewId=?

---------

The SQL being generated by H3 is creating incorrect SQL. It should say select count(images1_.categoryId), I think. I fixed this and ran directly in mysql, and there were other problems. Is this maybe due to a limitation in related sub-queries with MySQL?

Also, I tried changing the HQL to "select count( c.images )" and this produced a strange SQL prefix with select count(.) instead of the usual select count(*). ?!?

This seems to be a bug.

Can anyone think of a different approach with HQL? I know that straight SQL will work.

Thanks!
Daniel

Quote from

http://www.hibernate.org/hib_docs/v3/re ... yhql-where



You can add a feature request if you like, maybe someone will try to implement it.

Thanks so much for the quick response!

While that explains the first syntax example, that doesn't explain the second syntax attempt:

"select count( c.images ) from Category c where c.groupName='IMAGETYPE' and c.name='MAIN' and c.productView=?")

This results in:


select count(.) as col_0_0_ from category category0_, image_category images1_, image image2_ where category0_.id=images1_.categoryId and images1_.imageId=image2_.id and category0_.groupName='IMAGETYPE' and category0_.name='MAIN' and category0_.productViewId=?^M


Is select count(.) valid SQL in some dialect? MySQL definitely doesn't accept it. Changing this to select count(*) would work.

"c.images" is not a valid path expression except in the from clause. again, this is explained in the docs...

P.S. The FAQ and docs explain how to count collection elements....