[DELTASPIKE-1247] Orderby clause added to count query

• Type: Bug
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 1.7.2
• Component/s: Data-Module
• Assignee: Unassigned
• Reporter: Mustafa Makebits
• Created: 25/Apr/17 12:19
• Updated: 12/Nov/17 14:19

Description

I have the following repository code:

PurchaseRequisitionRepository.java

@Repository
public interface PurchaseRequisitionRepository extends EntityRepository<PurchaseRequisition, String>, Serializable {

	@Query("select e from PurchaseRequisition e")
	QueryResult<PurchaseRequisition> findAllPurchaseRequisitions();
}

And in my service code I'm trying to set the sort order like so:

repository.findAllPurchaseRequisitions().withPageSize(pageSize).orderDesc("prrId", false);

At some point I do query.countPages() or some thing similar, which causes this sql to be generated:

select
    count(purchasere0_.id) as col_0_0_ 
from
    omis_db.purch_requisition purchasere0_ 
order by
    purchasere0_.id limit ?

which in turn cause a sql exception:

org.hibernate.exception.SQLGrammarException: ERROR: column "purchasere0_.id" must appear in the GROUP BY clause or be used in an aggregate function
Position: 96

Am I doing anything wrong to cause this behavior? is there a way to work around it so that I can set the order and do paging at the same time?