Some Eclipse Foundation services are deprecated, or will be soon. Please ensure you've read this important communication.

Bug 317777

Summary: Bad SQL result for method countDistinct with join from criteria API
Product: z_Archived Reporter: Radek Hodain <radek.hodain>
Component: EclipselinkAssignee: Nobody - feel free to take it <nobody>
Status: NEW --- QA Contact:
Severity: normal    
Priority: P2 CC: eclipselink.orm-inbox, gordon.yorke, tom.ware, virgo47
Version: unspecified   
Target Milestone: ---   
Hardware: PC   
OS: Linux   
Whiteboard:

Description Radek Hodain CLA 2010-06-24 03:15:32 EDT 
Build Identifier: 2.1.0-RC4.2

I need to perform distinct count of entities and fetch associations by criteria API. The entity has composite primary key. These preconditions cause that the SQL generated by criteria API is wrong (syntax is OK but the result is wrong). If I don't use join fetches, EclipseLink generates SQL with EXISTS subselect, but everything work correctly. In other providers like Hibernate it works correctly. I use it with Oracle database.

I discussed it here http://www.eclipse.org/forums/index.php?t=msg&th=170219&start=0&.

Reproducible: Always

Steps to Reproduce:
Description of entities:

UserGroupAsoc is association entity between User and Group with one-to-many relationship. UserGroupAsoc has composite primary key which is build from primary key of User and Group.

Code which generated bad SQL:

CriteriaBuilder b = manager.getCriteriaBuilder();
CriteriaQuery<Long> q = b.createQuery(Long.class);
Root<UserGroupAsoc> identificationVariable = q.from(UserGroupAsoc.class);
identificationVariable.join("group", JoinType.LEFT);
q.select(b.countDistinct(identificationVariable));
q.where(b.equal(identificationVariable.get("user"), user));
TypedQuery<Long> createQuery = manager.createQuery(q);
Long count = createQuery.getSingleResult();

Bad SQL:

SELECT COUNT(t0.user_code) FROM GROUPUSER t2 LEFT OUTER JOIN GROUP t1 ON (t1.group_code = t2.group_code), GROUPUSER t0 WHERE EXISTS (SELECT t2.user_code FROM GROUPUSER t2 WHERE (((t0.user_code = t2.user_code) AND (t0.group_code = t2.group_code)) AND (t2.user_code = 1)))
Comment 1 Tom Ware CLA 2010-11-05 10:01:21 EDT 
I have not been able to recreate this issue.

Please post the code for User, Group and UserGroupAsoc and any xml files that contribute to how they are mapped.
Comment 2 Tom Ware CLA 2010-11-11 14:30:34 EST 
Deferring because I cannot recreate the exact error described here.  Note: I do get some issues creating these kinds of queries, but any change I implemented could not be verified to fix this bug, so I am deferring until more information is available.
Comment 3 Richard Richter CLA 2014-09-30 17:13:16 EDT 
Just today I hit a bug with EclipseLink that seems to be related both to this bug and this thread http://www.eclipse.org/forums/index.php/t/170219/

The trouble seems to be with the combination of DISTINCT and COUNT *with* the composite PK. For instance JPQL for entity with simple PK:

select distinct security
from Security security
  inner join security.domains as cd on cd.id = ?1

Is transformed properly into:

SELECT COUNT(DISTINCT(t0.id)) FROM Securities t0, Securities_Domains t2, Domains t1 WHERE (((t2.security_id = t0.id) AND (t1.id = t2.domain_id)) AND (t1.id = ?))

But virtually equivalent JPQL - but this time with "subInstrument" having composite PK (two Integers):

select count(distinct subInstrument)
from SubInstrument subInstrument
  inner join subInstrument.domains as cd on cd.id = ?1

Turns into this - no join to domains at all:

SELECT COUNT(t0.siid) FROM SubInstruments t0 WHERE EXISTS (SELECT t1.siid FROM SubInstruments t1 WHERE ((t0.siid = t1."siid") AND (t0."iid" = t1."iid")))

While the forum thread mentioned before says that DISTINCT and COUNT doesn't go together well, Hibernate has this solved so it seems: https://hibernate.atlassian.net/browse/HHH-3096

The DISTINCT cannot be omitted as the relationship to domains is *ToMany. It should be reproducible with the following setup:

@Entity
public class Role {
	@Id
	private Integer id;
}

@Entity
public class Right {

	@EmbeddedId
	private RightPk id;

	@ManyToMany
	private Set<Role> roles;
}

@Embeddable
public class RightPk {
	@Column	private Integer id1;
	@Column	private Integer id2;
}

And JPQL like this:
select count(distinct r)
from Right r
  inner join r.roles as rr on rr.id in (?1)
Comment 4 Richard Richter CLA 2014-10-08 06:22:01 EDT 
I have to admit that this seems not to be the bug after all. In JPA specification, at least in 2.1 version, section 4.8.5:

The use of DISTINCT with COUNT is not supported
for arguments of embeddable types or map entry
types.

Shame it's not, but it is specified.
Comment 5 Eclipse Webmaster CLA 2022-06-09 10:16:02 EDT 
The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink
Comment 6 Eclipse Webmaster CLA 2022-06-09 10:19:35 EDT 
The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink