/** Test for partial fix of GF 932. */
public void complexHavingWithAggregate() {
oracle.toplink.essentials.ejb.cmp3.EntityManager em =
(oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
// Test using the project id in COUNT, GROUP BY and HAVING
ExpressionBuilder employeeBuilder = new ExpressionBuilder(Employee.class);
ReportQuery rq = new ReportQuery(Employee.class, employeeBuilder);
Expression projects = employeeBuilder.anyOf("projects");
Expression pid = projects.get("id");
Expression count = pid.count();
rq.addAttribute("id", pid);
rq.addAttribute("COUNT", count, Long.class);
rq.addGrouping(pid);
rq.setHavingExpression(count.greaterThan(1));
rq.setShouldReturnWithoutReportQueryResult(true);
Vector expectedResult = (Vector) em.getActiveSession().executeQuery(rq);
String jpql =
"SELECT p.id, COUNT(p.id) FROM Employee e JOIN e.projects p "
+ "GROUP BY p.id HAVING COUNT(p.id)>1";
List result = em.createQuery(jpql).getResultList();
Assert.assertTrue(
"Complex HAVING with aggregate function failed",
comparer.compareObjects(result, expectedResult));
// Test using the project itself in COUNT, GROUP BY and HAVING
employeeBuilder = new ExpressionBuilder(Employee.class);
rq = new ReportQuery(Employee.class, employeeBuilder);
projects = employeeBuilder.anyOf("projects");
count = projects.count();
rq.addAttribute("projects", projects);
rq.addAttribute("COUNT", count, Long.class);
rq.addGrouping(projects);
rq.setHavingExpression(count.greaterThan(1));
rq.setShouldReturnWithoutReportQueryResult(true);
expectedResult = (Vector) em.getActiveSession().executeQuery(rq);
jpql = "SELECT p, COUNT(p) FROM Employee e JOIN e.projects p " + "GROUP BY p HAVING COUNT(p)>1";
result = em.createQuery(jpql).getResultList();
Assert.assertTrue(
"Complex HAVING with aggregate function failed",
comparer.compareObjects(result, expectedResult));
}
