Hibernate query fails with “this_.id must appear in GROUP BY” when using projection.count or projection.rowcount

Edited to provide more detailed information.

I’m building a web service using Spring 2.5.6 and Hibernate 4. I’m dynamically building a criteria query based on input from a client. We are using Spring 2.5.6 because it is legacy code, previous attempts to upgrade to later versions of Spring by updating the versions in the Maven POM file fail, and our deadline is too tight to provide time to fully transition the project to a later version of Spring.

The web service is searching for observations made by sensors using filters sent to the service by a client over SOAP. The users of this web service have need to create filters that result in several thousand observations returned. The service is taking so long to return a response, the users’ clients are timing out. What I am attempting to do to resolve this performance issue is first query the database for how many observations would be returned by the users’ filters, then splitting the work off into several threads of execution using a cached thread pool. Each thread will query the database for a portion of the results. Then using a thread-safe queue provided by Java’s java.util.concurrent package, I have each thread encode the responses into the proper JAXB objects and add these objects to the queue. Finally, the web service returns the entire response to the client.

I’m hoping this design will reduce response time (it obviously assumes the database will handle the multiple parallel queries just fine and that returning the results from the database in pieces along several connections is faster than one bulk return on a single connection). However, when attempting to get that initial count required before creating my threads, I get an error from the database.

I’m using Hibernate criteria queries and a Hibernate projection to get the count. The criteria is generated by the code below:

    Criteria criteria = session.createCriteria(Observation.class);

    if (!filter.isSetService())
    {
        throw new JpaCriteriaFactoryException("Service required for ObservationFilter.");
    }

    criteria.createAlias("sensor", "sensor").add(Restrictions.eq("sensor.service", filter.getService()));

    criteria = criteria.setMaxResults(filter.getMax());
    criteria = criteria.setFirstResult(filter.getStart());
    criteria = criteria.addOrder(Order.desc("id"));

    if (filter.isSetOffering())
    {
        // offerings will be implemented later
    }

    if (filter.isTemplate())
    {
        criteria = criteria.add(Restrictions.eq("template", true));
    }
    else
    {
        criteria = criteria.add(Restrictions.eq("template", false));
    }

    if (filter.isSetProcedures())
    {
        criteria = criteria.add(Restrictions.in("sensor.uniqueId", filter.getProcedures()));
    }

    if (filter.isSetPhenomenons())
    {
        criteria = criteria.createAlias("phenomenon", "phenom")
                .add(Restrictions.in("phenom.id", filter.getPhenomenons()));
    }

    if (filter.isSetTemporalFilter())
    {
        criteria = criteria.add(createTemporalCriteria(filter.getTemporalFilter()));
    }

    if (filter.isSetGeospatialFilter())
    {
        criteria = criteria.createAlias("featureOfInterest", "foi")
                .add(createGeospatialCriteria("foi.geometry",
                filter.getGeospatialFilter(), geoFac));
    }

    if (filter.isSetScalarFilter())
    {
        try
        {
            criteria = criteria.createAlias(RESULTS_ALIAS, RESULTS_ALIAS)
                    .add(ScalarCriterionFactory.createScalarCriterion(filter.getScalarFilter(), RESULTS_ALIAS));
        }
        catch (ScalarCriterionFactoryException ex)
        {
            throw new JpaCriteriaFactoryException("Failed to build criterion for scalar filter!", ex);
        }
    }

    return criteria;

Then, to get the count of the results, rather than the results themselves, criteria.setProjection(Projections.rowCount()) is added. However, this results in the following exception:

org.postgresql.util.PSQLException: ERROR: column "this_.id" must appear in the GROUP BY clause or be used in an aggregate function

In hibernate, I added the following settings:

        <property name="hibernate.show_sql" value="true"/>
        <property name="hibernate.format_sql" value="true"/>
        <property name="hibernate.use_sql_comments" value="true"/>

and got the following output:

/* criteria query */ select
    count(*) as y0_
from
    Observation this_
inner join
    Sensor sensor1_
        on this_.sensor_id=sensor1_.id
where
    sensor1_.service_id=?
    and this_.template=?
    and sensor1_.uniqueId in (
        ?
    )
order by
    this_.id desc limit ?

Using the exact same filter to generate a criteria, but not adding criteria.setProjection(Projections.rowCount()), I get the exact results I’m expecting. So I do not feel that the criteria is being created incorrectly. I cannot use criteria.list().size() because the whole point of this is to get the results back in parallel rather than in serial.

Can someone please help me to resolve this issue? If a better solution then my “threading” solution is available, I am also open to suggestions.

Answer

I’ve identified the solution to this.

What I really needed was a Hibernate equivalent of SELECT count(*) FROM (SELECT ... ) Per https://docs.jboss.org/hibernate/orm/4.2/manual/en-US/html/ch16.html#queryhql-subqueries, this is not allowed in HQL. Also, based on https://docs.jboss.org/hibernate/orm/4.2/manual/en-US/html/ch17.html#querycriteria-detachedqueries, it appears that legacy Hibernate Criteria does not support this function either, since the way of creating subqueries there is to use DetachedCriteria adding these via the Subqueries class. It does appear to be doable using formal JPA CriteriaBuilder per http://docs.jboss.org/hibernate/orm/4.2/devguide/en-US/html/ch12.html#querycriteria-from, however due to the architecture currently employed in my service, I am unable at this time to use this feature.

Advertisement

Answer