I have recently moved my DB from Oracle to SQL Server. My Application uses hibernate v5.x.
Criteria criteria = session.createCriteria(ABC.class);
setResultPaging(criteria, 1, 100);
//Add all criteria restrictions
criteria.add(Restrictions.eq("ABCId", ABCSearch.getId());
...
...
...
//All restrictions added. Now add Order by.
criteria.addOrder(Property.forName("endDate").desc());
criteria.addOrder(Property.forName("startDate").asc());
criteria.addOrder(Property.forName("ABCId").asc());
List<ABC> ABCList = criteria.list();
if (ABCList.size() > 0) {
criteria.setProjection(Projections.projectionList().add(Projections.rowCount()));
setResultPaging(criteria, 0, 1);
searchResult.setTotalResults(CountTranslate.translateCount(criteria.list())); <-- Error happens here.
}
//CountTranslate.translateCount() typecasts criteria.list into Integer
While performing the criteria.list() within the 2nd time, it gives me the error as below,
Column "ABC.END_DATE" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
2023-07-16 08:49:38,051 WARN \[services-pool housekeeper\] : services-pool - Thread starvation or clock leap detected (housekeeper delta=1m20s409ms434µs600ns).
2023-07-16 08:49:41,474 ERROR \[RMI TCP Connection(14)-192.168.43.237\] : New DAOException -
org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:103)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:37)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:67)
at org.hibernate.loader.Loader.getResultSet(Loader.java:2322)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2075)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2037)
at org.hibernate.loader.Loader.doQuery(Loader.java:956)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:357)
at org.hibernate.loader.Loader.doList(Loader.java:2868)
at org.hibernate.loader.Loader.doList(Loader.java:2850)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2682)
at org.hibernate.loader.Loader.list(Loader.java:2677)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:109)
at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1922)
at org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:370)
The Hibernate Query appearing in the logs is below,
select count(*) as y0_
from ABC this_
inner join ABC_REQUEST abcreques1_
on this_.ABC_REQUEST_ID=abcreques1_.ABC_REQUEST_ID
where
abcreques1_.FORM_TYPE=? and
this_.ABC_STATUS=? and
this_.ARCHIVED_FLAG=?
order by
this_.END_DATE desc,
this_.START_DATE asc,
this_.ABC_ID asc
offset 0 rows fetch next ? rows only
//ABC_REQUEST is an associated table.
This was working well in Oracle, but after moving to SQL Server, this errors out. I am unsure why HQL is building a query which is not recognized by SQL Server as legitimate...!!! Am I doing something wrong..??!!!
FYI, I am using the SQLServer2012Dialect
After some searching, found a way out. Using CriteriaImpl, I was able to remove the Order By clause prior to the 2nd criteria.list() call. Took reference from here - Remove dynamically an ordering to the result set in org.hibernate.Criteria. My updated code looks like below,
Note: I still feel that this is a workaround to the problem. The count query produced by hibernate isn't going well with SQL Server but it did work well with Oracle using Oracle9dialect hibernate dialect. So, the hibernate dialect - SQLServer2012Dialect should ideally be able to handle this, and it shouldn't produce a bad HQL for Projections rowcount..!!!
Criteria criteria = session.createCriteria(ABC.class);
setResultPaging(criteria, 1, 100);
//Add all criteria restrictions
criteria.add(Restrictions.eq("ABCId", ABCSearch.getId());
...
...
...
//All restrictions added. Now add Order by.
criteria.addOrder(Property.forName("endDate").desc());
criteria.addOrder(Property.forName("startDate").asc());
criteria.addOrder(Property.forName("ABCId").asc());
List<ABC> ABCList = criteria.list();
if (ABCList.size() > 0) {
criteria.setProjection(Projections.projectionList().add(Projections.rowCount()));
setResultPaging(criteria, 0, 1);
//New code to remove the Order By Clauses from criteria
Iterator<CriteriaImpl.OrderEntry> orderIter
= ((CriteriaImpl)criteria).iterateOrderings();
while (orderIter.hasNext()) {
orderIter.next();
orderIter.remove();
}
searchResult.setTotalResults(CountTranslate.translateCount(criteria.list()));
//Now the criteria.list() translates to an HQL that no more throws the 'Invlaid in Order by clause' error.
}