Problem with COUNT(*) and new parser

sven · **Posted:** Mon Apr 04, 2005 9:02 am

Hibernate version: 3.0 final
Name and version of the database you are using: MySQL 4.0.24

I would like to get the number of elements of a certain object.
The following query works fine with the classic query parser. The exception below is thrown with the new query parser.
The documentation says that COUNT(*) is a supported aggregate function in HQL.

Code

Code:

final StringBuffer buffer = new StringBuffer(200)
      .append("SELECT COUNT(*) FROM Order AS order ")
      .append("JOIN order.versions ")
      .append("WHERE order.node = :node ");
final Session session = Li2DHibernateUtil.getSession();
final Query query = session.createQuery(buffer.toString());
query.setString("node", "12345");
final int result = ((Integer) query.uniqueResult()).intValue();

Exception
org.hibernate.QueryException: Unable to find SQL function: COUNT [SELECT COUNT(*) FROM de.company.project.persistence.Order AS order JOIN order.versions WHERE order.node = :node ]

PS: When searching for an alternative way getting the number of query results, I found the following peace of code in the Hibernate 3.0 documentation. Maybe this part should be removed or replace since Session.iterate is deprecated in Hibernate 3.0...?

Code:

( (Integer) session.iterate("select count(*) from ....").next() ).intValue()

david · **Posted:** Mon Apr 04, 2005 8:19 pm

Try lower case count(*).

sven · **Posted:** Tue Apr 05, 2005 2:50 am

That seems to work fine.
So this is obviously a bug, isn't it?

The documentation says (PDF page 114) that HQL Queries are case-insensitive, except for names of Java classes and properties.

Should I put this issue to JIRA?

david · **Posted:** Tue Apr 05, 2005 8:06 pm

I have a strong preference for lowercase function names (in fact all lowercase). You could record the issue in JIRA but I suspect the documentation will be updated as the solution.

vinodsingh · **Posted:** Wed Apr 06, 2005 12:18 am

david wrote:

I have a strong preference for lowercase function names (in fact all lowercase).

Any specific reason for this preference?

sven · **Posted:** Wed Apr 06, 2005 3:30 am

david wrote:

I have a strong preference for lowercase function names (in fact all lowercase). You could record the issue in JIRA but I suspect the documentation will be updated as the solution.

OK, i recorded this issue in JIRA (HHH-313) with the note that either the documentation or the code needs to be updated.
By the way, I have a preference for (at least SQL-similar) uppercase function names. :)

bstansberry · **Joined:** Tue May 04, 2004 5:03 pm **Posts:** 3

I have a preference not to have to go back and update all my DAO classes to use lowercase function names. There is already enough work to do to upgrade to Hibernate 3. Hibernate 2 does not have this limitation. Please update the code to support case-insensitive function names.

sven · **Posted:** Mon Apr 18, 2005 2:22 pm

This has been recently updated with the new release 3.0.1.

bstansberry · **Joined:** Tue May 04, 2004 5:03 pm **Posts:** 3

Thanks for the fix.