Description Jorge Cara 2016-06-09 09:22:12 EDT

We have a jpa criteria query that used to work correctly for eclipselink 2.4.2 and that did not produced the right code on the subquery for eclipselink 2.5.2.

The entities relation is:
	Person
		...
		@Column(name = "COMPANY_NUMBER", nullable = true)
		private String companyNumber;
		
	Family
		...
		@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, orphanRemoval = true)
		@BatchFetch(value = BatchFetchType.EXISTS)
		@JoinColumn(name = "FAMILY_ID", referencedColumnName = "ID", nullable = true)
		private Set<Person> members;

	Company
		...
		@OneToMany(fetch = FetchType.LAZY)
		@BatchFetch(value = BatchFetchType.EXISTS)
		@JoinColumn(name = "COMPANY_NUMBER", referencedColumnName = "COMPANY_NUMBER", nullable = true)
		private Set<Person> employees;
		
		@Column(name = "COMPANY_NUMBER", unique = true, nullable = false)
		private String companyNumber;
	
----------------------------------------------------------------------------------------------------------------------
Eclipselink 2.4.2
	private void setUnemployedCriteria(CriteriaQuery<Family> familyCriteria, Join<Family, Person> familyPersonJoin) {
		Subquery<Long> subQuery = familyCriteria.subquery(Long.class);
		Root<Company> companyRoot = subQuery.from(Company.class);        
		Join<Company, Person> companyPersonJoin = companyRoot.join("employees");
		subQuery.select(companyPersonJoin.get("id"));

		this.predicateList.add(this.criteriaBuilder.not(this.criteriaBuilder.in(familyPersonJoin.get("id")).value(subQuery)));
	}
	
	Produced the following SQL:
		SELECT DISTINCT t1.*
		FROM PERSON t0, FAMILY t1
		WHERE (((SOME OTHER MASTER QUERY CRITERIA) AND 
			NOT ( t0.ID IN 
				(SELECT t2.ID FROM COMPANY t3, PERSON t2 WHERE (t3.COMPANY_NUMBER = t2.COMPANY_NUMBER)))) 
			AND (t0.FAMILY_ID = t1.ID))
----------------------------------------------------------------------------------------------------------------------
Eclipselink 2.5.2
	The same javacode for EL 2.5.2 produced the following SQL (not relating the keys for company and person):
		SELECT DISTINCT t1.*
		FROM PERSON t0, FAMILY t1
		WHERE (((SOME_OTHER_MASTER_QUERY_CRITERIAS) AND 
			NOT ( t0.ID IN 
				(SELECT t2.ID FROM COMPANY t3, PERSON t2))) 
			AND (t0.FAMILY_ID = t1.ID))
			
	As a workaround, to make EL produce the right SQL we rewrote the subquery to manually join the entities on the subquery:
	private void setUnemployedCriteria(CriteriaQuery<Family> familyCriteria, Join<Family, Person> familyPersonJoin) {
		Subquery<Long> subQuery = familyCriteria.subquery(Long.class);
		Root<Company> companyRoot = subQuery.from(Company.class);
		Root<Person> personRoot = subQuery.from(Person.class);
		subQuery.select(personRoot.get("id"));
		subQuery.where(this.criteriaBuilder.equal(companyRoot.get("companyNumber"), personRoot.get("companyNumber")));

		this.predicateList.add(this.criteriaBuilder.not(this.criteriaBuilder.in(familyPersonJoin.get("id")).value(subQuery)));
	}