Not in aggregate function or group by clause - HSQLDB doesn't like the ORDER BY clause

[Stephane Eybert]

I have an Hibernate application with the mapping:

<property name="firstname" type="string">
<column name="firstname" not-null="true" />
</property>
<property name="lastname" type="string">
<column name="lastname" not-null="true" />
</property>

I get an exception in HSQLDB only and not in MySQL.

Here is the exception:

Caused by: java.sql.SQLException: Not in aggregate function or group by clause: org.hsqldb.Expression@4c8167 in statement [select count(*) as y0_ from admin this_ order by this_.firstname asc, this_.lastname asc]

Here is the sql statement:

select count(*) as y0_ from admin this_ order by this_.firstname asc, this_.lastname asc

Any clue ?

[Stephane Eybert]

Some forum post says HSQLDB requires a String in the ORDER BY clause.

Fine. Mine is a String.

So what's wrong then ?

[Mary Chellapa]

you are using count(*) ... i wonder why are you using it with order by ??? i think you want a group by there

[Stephane Eybert]

Hello Mary,

Thanks for that comment. It's a good question.

But I don't have the answer, it's Hibernate that generates this statement.

I'm having a pagination method and I need to have a count on the number of rows. I guess that is where the statement is coming from.

	@SuppressWarnings("unchecked")
	public Page<T> getPage(final int pageNumber, final int pageSize, final Criteria criteria) {
		int totalSize = 0;
		if (pageSize > 0) {
			// Save the original Projection and ResultTransformer if any
			CriteriaImpl criteriaImpl = (CriteriaImpl) criteria;
			Projection originalProjection = criteriaImpl.getProjection();
			ResultTransformer originalResultTransformer = criteriaImpl.getResultTransformer();
			criteria.setProjection(Projections.rowCount());
			// TODO I don't like this intValue on a Long
			// Why is uniqueResult returning a Long and setFirstResult requiring an int ?
			totalSize = ((Long) criteria.uniqueResult()).intValue();
			int startIndex = Page.getStartIndex(pageNumber, pageSize, totalSize);
			criteria.setFirstResult(startIndex);
			criteria.setMaxResults(pageSize);
			criteria.setProjection(originalProjection);
			criteria.setResultTransformer(originalResultTransformer);
		}
		List<T> list = criteria.list();
		return new Page<T>(pageNumber, pageSize, totalSize, list);
	}

	@SuppressWarnings("unchecked")
	public Page<T> getPage(final int pageNumber, final int pageSize, final String statement, final Map<String, Object> parameters, Session session) {
		Query query = session.createQuery(statement);
		Set<String> keys = parameters.keySet();
		for (String key : keys) {
			query.setParameter(key, parameters.get(key));
		}
		int totalSize = 0;
		if (pageSize > 0) {
			String countStatement = "select count(1) " + removeSelect(removeOrders(statement));
			Query countQuery = session.createQuery(countStatement);
			for (String key : keys) {
				countQuery.setParameter(key, parameters.get(key));
			}
			Integer count = (Integer) countQuery.list().get(0);
			totalSize = count.intValue();
			int startIndex = Page.getStartIndex(pageNumber, pageSize, totalSize);
			query.setFirstResult(startIndex).setMaxResults(pageSize);
		}
		List<T> list = query.list();
		return new Page<T>(pageNumber, pageSize, totalSize, list);
	}

	public static String removeSelect(String hql) {
		int beginPos = hql.toLowerCase().indexOf("from");
		return hql.substring(beginPos);
	}

	private static final Pattern ORDER_PATTERN = Pattern.compile("order\\s*by[\\w|\\W|\\s|\\S]*", Pattern.CASE_INSENSITIVE);

	public static String removeOrders(String hql) {
		Matcher m = ORDER_PATTERN.matcher(hql);
		StringBuffer sb = new StringBuffer();
		while (m.find()) {
			m.appendReplacement(sb, "");
		}
		m.appendTail(sb);
		return sb.toString();
	}

And this pagination method is called by my dao method.

	@Override
	public Page<Admin> findAll(final int pageNumber, final int pageSize) {
		Criteria criteria = getSession().createCriteria(getPersistentClass());
		criteria.addOrder(Order.asc("firstname")).addOrder(Order.asc("lastname"));
		Page<Admin> page = getPage(pageNumber, pageSize, criteria);
		return page;
	}

Thanks again !

Stephane

[Daniel Doboseru]

To solve this SQL error, include in the GROUP BY clause, all the columns (at least, this is how I solved it), in order to tell the parser how to order your records.

For instance if you have the table: Student (name, age, university ....) you should have an ORDER BY like this: "ORDER BY name, university, age" or at least "name, university", so he can order by name, then by university, then by age etc. Try this!

[Stephane Eybert]

Hi Daniel,

Thanks for that but I don't see any GROUP BY statement in my source code. Do you see any ?

Stephane

[Stephane Eybert]

Here is more on the error message:

Caused by: java.sql.SQLException: Not in aggregate function or group by clause: org.hsqldb.Expression@1504a84 in statement [select count(*) as y0_ from admin this_ where (lower(this_.firstname) like ? or lower(this_.lastname) like ? or lower(this_.login) like ? or lower(this_.email) like ?) order by this_.firstname asc, this_.lastname asc]

[Stephane Eybert]

I don't know what happened, but the issue does not show up anymore.

[Rob Spoor]

I hate it when that happens. You don't know what solved the issue, so it may come back at some time. I'd rather have the error occur a little longer until I find the solution, because then I know for sure the problem won't reoccur. There's not much I hate more than sleeping bugs.

[Stephane Eybert]

Hi Rob,

I know, I feel the same, that's why I'm right now still looking at it, trying to dig around..

Stephane