Description Edward Rayl 2011-05-28 17:29:15 EDT

Build Identifier: 2.2.0

The query below fails to parse correctly.  This query is on the Oracle sample schema called HR, with the appropriate entities mapped to it.

    select d from Department d group by d having COUNT(d.employees1) = 3

with the following SQL output:

    SQL: SELECT DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID 
    FROM DEPARTMENTS 

This next query works however:

    select d, COUNT(d.employees1) from Department d group by d having COUNT(d.employees1) = 3

with the following SQL output:

    SQL: SELECT t0.DEPARTMENT_ID, t0.DEPARTMENT_NAME, t0.MANAGER_ID, 
        t0.LOCATION_ID, COUNT(t1.EMPLOYEE_ID) 
    FROM DEPARTMENTS t0, EMPLOYEES t1 
    WHERE (t1.DEPARTMENT_ID = t0.DEPARTMENT_ID) 
    GROUP BY t0.DEPARTMENT_ID, t0.DEPARTMENT_NAME, t0.MANAGER_ID, t0.LOCATION_ID 
    HAVING (COUNT(t1.EMPLOYEE_ID) = ?) 

The specification should allow the first query. A portion of Section 4.7 of JSR 317: Java Persistence API, Version 2.0 says;

The HAVING clause is used to filter over the groups, and can contain aggregate functions over
attributes included in the groups and/or functions or other query language operators over the attributes that are used for grouping. It is not required that an aggregate function used in the HAVING clause also be used in the SELECT clause.


Reproducible: Always

Steps to Reproduce:
1. Create JPA entities for the Oracle HR schema
2. Create a JPQL query as shown above.
3. The result of the query should be one and only one department, not 27