Comment 1 Peter Krogh 2008-06-16 16:06:53 EDT

Unassigned bugs - orig. assigned to 1.0.  Deferring to 1.0.1

Comment 2 Peter Krogh 2008-09-02 13:34:53 EDT

Moved to 1.1 as part of 1.0.1 closedown

Comment 3 Chris Delahunt 2008-09-16 14:59:20 EDT

The glassfish bug was closed as a duplicate of 1403:
  https://glassfish.dev.java.net/issues/show_bug.cgi?id=1403

I've changed this bug to reflect the actual problem, since 1403 has not been filed in EclipseLink yet.  

EclipseLink (and TopLink Essentials) does not currently support aggregate functions in the HAVING clause unless the exact same aggregate function is in the Select.  For instance:
  "SELECT search.item 
    ... 
    GROUP BY search.item
    HAVING COUNT(search.item) = 2"
 Will fail, but 
"SELECT search.item, COUNT(search.item) 
    ... 
    GROUP BY search.item
    HAVING COUNT(search.item) = 2"

is supported.  This has been marked as a feature request to get this functionality implemented.

Comment 4 Josh Davis 2009-04-08 17:03:25 EDT

This query fails:
select city.stateId, count(city.cityId) from VCity city group by city.stateId having count(city.cityId) >= :value_0

but if I change it to this, it works:
select city.stateId, count(city.cityId) from VCity city group by city.stateId having count(city.stateId) >= :value_0

The error given in the first instance is:

invalid HAVING expression {COUNT(city.cityId) >= value_0} for query with grouping {GROUP BY city.stateId}. The HAVING clause must specify search conditions over the grouping items or aggregate functions that apply to grouping items

It isn't a big deal in this case, because it doesn't matter which field I use when I just want a simple count in the having clause.

However, it is a serious issue if I need to use sum, min, max, avg, or count(distinct) in the having clause.

Comment 5 James Sutherland 2010-05-12 11:48:18 EDT

This should be fixed now in 2.1 trunk

Comment 6 Mattias Gyllsdorff 2011-01-18 06:24:35 EST

(In reply to comment #5)
> This should be fixed now in 2.1 trunk

It seems like there have been a regression somewhere. It now seems like the HAVING COUNT gets silently ignored.

I posted a thread over at http://forums.oracle.com/forums/thread.jspa?messageID=9293029 about it was told to reopen this bug if the provided workaround worded, which it did.

I have two entities, Foo and Bar.

public class Foo {
	@Id
	@GeneratedValue(strategy=GenerationType.SEQUENCE)
	@Column(name="foo_id")
	private int fooId
 
	@OneToMany(mappedBy="parentFoo", cascade = CascadeType.ALL)
	private Set<Bar> barRecords;
}
 
public class Bar {
 
	@Id
	@GeneratedValue(strategy=GenerationType.SEQUENCE)
	@Column(name="bar_id")
	private int barId;
 
	@ManyToOne(cascade = CascadeType.ALL)
	@JoinColumn(name="foo_nr")
	private Foo parentFoo;
}



I am trying to filter the JPQL result by barRecords.size. The result should be 17 instances. There are 24 instances of Foo in the table.

List<Foo> result = em.createQuery(
				"SELECT foo " +
				"FROM Foo AS foo " +
				"GROUP BY foo.fooId " +
				"HAVING COUNT(foo.barRecords) = 63 ", Foo.class)
				.getResultList();

I get 24 instances as the result.

The generated query from eclipselink was
SELECT foo_id 
FROM Foo

If I do
List<Object[]> result = em.createQuery(
				"SELECT foo, COUNT(foo.barRecords) " +
				"FROM Foo AS foo " +
				"GROUP BY foo.fooId " +
				"HAVING COUNT(foo.barRecords) = 63 ", Foo.class)
				.getResultList();
Then I get the expected result.

The generated query is 
SELECT t0.foo_id,  COUNT(t1.bar_id) 
FROM Foo t0, Bar t1 
WHERE (t1.foo_nr = t0.foo_id)
GROUP BY t0.foo_id
HAVING (COUNT(t1.bar_id) = 63)


I am using Glassfish 3.0.1, I have tried Eclipselink 2.1.2 and the current release candidate 2.2.0.v20110114-r8831.

Is there anything else you need to know?

Comment 7 Tom Ware 2011-01-26 11:33:30 EST

Reopening based on feedback above.

Comment 8 James Sutherland 2011-01-27 09:20:14 EST

Does the test work if you give the query a where clause?

Comment 9 Mattias Gyllsdorff 2011-01-27 10:31:23 EST

(In reply to comment #8)
> Does the test work if you give the query a where clause?

Do you mean 
List<Foo> result = em.createQuery(
                "SELECT foo " +
                "FROM Foo AS foo " +
		"WHERE foo.fooId = 2 " + 
                "GROUP BY foo.fooId " +
                "HAVING COUNT(foo.barRecords) = 63 ", Foo.class)
                .getResultList();

I get one result but the generated query is
SELECT foo_id 
FROM Foo
WHERE foo_id = 2

This is with Eclipse Persistence Services - 2.2.0.v20110114-r8831 on Glassfish 3.0.1

Comment 10 James Sutherland 2012-02-29 13:36:20 EST

This is fixed in 2.4, issue was because single object select in select clause was created as a ReadAllQuery.  Needs to check group by and having as well.

Comment 11 James Sutherland 2012-04-11 10:42:47 EDT

Fixed in 2.4

Comment 12 Eclipse Webmaster 2022-06-09 10:16:56 EDT

The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink

Comment 13 Eclipse Webmaster 2022-06-09 10:22:42 EDT

The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink