/*
   * test for gf675, using count, group by and having fails.  This test is specific for a a use case
   * where DISTINCT is used with Count and group by
   */
  public void complexCountDistinctWithGroupByTest2() {
    oracle.toplink.essentials.ejb.cmp3.EntityManager em =
        (oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();

    // need to set the class in the expressionbuilder, as the Count(Distinct) will cause the
    // query to change and be built around the Employee class instead of the Address class.
    ExpressionBuilder expbldr = new ExpressionBuilder(Address.class);

    ReportQuery rq = new ReportQuery(Address.class, expbldr);
    Expression exp = expbldr.anyOf("employees");

    Expression exp2 = expbldr.get("city");
    rq.addAttribute("city", exp2);
    rq.addCount("COUNT1", exp, Long.class);
    rq.addCount("COUNT2", exp.get("lastName").distinct(), Long.class);
    rq.addGrouping(exp2);
    Vector expectedResult = (Vector) em.getActiveSession().executeQuery(rq);

    String ejbqlString3 =
        "SELECT a.city, COUNT( e ), COUNT( DISTINCT e.lastName ) FROM Address a JOIN a.employees e GROUP BY a.city";
    Query q = em.createQuery(ejbqlString3);
    List result = (List) q.getResultList();

    Assert.assertTrue(
        "Complex COUNT(Distinct) with Group By test failed",
        comparer.compareObjects(result, expectedResult));
  }
  /** 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));
  }