| Why do subqueries with nested properties in EclipseLink always produce unnecessary/redundant joins? [message #1403020] |
Sun, 20 July 2014 00:02  |
Eclipse User |
|
|
|
I wanted to generate the following SQL through EclipseLink (2.5.2) JPA criteria.
SELECT
zonecharge0_.charge AS col_0_0_
FROM
projectdb.zone_charge zonecharge0_
WHERE
(
EXISTS (
SELECT
country1_.country_id
FROM
projectdb.country country1_
WHERE
country1_.country_id=?
and zonecharge0_.zone_id=country1_.zone_id
)
)
AND (
zonecharge0_.weight_id IN (
SELECT
MAX(weight2_.weight_id)
FROM
projectdb.weight weight2_
WHERE
weight2_.weight BETWEEN 0 AND 60
)
)
Running both of the following criteria and JPQL queries on Hibernate (4.3.5 final) produce the above clean query that one can always intuitively expect.
CriteriaBuilder criteriaBuilder=entityManager.getCriteriaBuilder();
CriteriaQuery<BigDecimal>criteriaQuery=criteriaBuilder.createQuery(BigDecimal.class);
Metamodel metamodel = entityManager.getMetamodel();
Root<ZoneCharge> root = criteriaQuery.from(metamodel.entity(ZoneCharge.class));
criteriaQuery.select(root.get(ZoneCharge_.charge));
Subquery<Long> countrySubquery = criteriaQuery.subquery(Long.class);
Root<Country> countryRoot = countrySubquery.from(metamodel.entity(Country.class));
countrySubquery.select(countryRoot.get(Country_.countryId));
Predicate[] predicates=new Predicate[2];
predicates[0]=criteriaBuilder.equal(countryRoot, country);
predicates[1]=criteriaBuilder.equal(root.get(ZoneCharge_.zoneId), countryRoot.get(Country_.zoneId));
countrySubquery.where(predicates);
Subquery<Long> weightSubquery = criteriaQuery.subquery(Long.class);
Root<Weight> weightRoot = weightSubquery.from(metamodel.entity(Weight.class));
weightSubquery.select(criteriaBuilder.max(weightRoot.get(Weight_.weightId)));
weightSubquery.where(criteriaBuilder.between(weightRoot.get(Weight_.weight), BigDecimal.ZERO, weight));
predicates=new Predicate[2];
predicates[0]=criteriaBuilder.exists(countrySubquery);
predicates[1]=criteriaBuilder.in(root.get(ZoneCharge_.weightId).get(Weight_.weightId)).value(weightSubquery);
criteriaQuery.where(predicates);
BigDecimal charge = entityManager.createQuery(criteriaQuery).getSingleResult();
The corresponding JPQL :
SELECT zc.charge
FROM ZoneCharge AS zc
WHERE EXISTS(SELECT c.countryId
FROM Country AS c
WHERE zc.zoneId = c.zoneId
AND c.countryId = 1)
AND zc.weightId.weightId IN(SELECT MAX(w.weightId)
FROM Weight AS w
WHERE w.weight BETWEEN 0 AND 60)
Running these queries (both criteria and JPQL) without any modifications on EclipseLink (2.5.2) produce the following very ugly/cluttered query.
SELECT t0.charge
FROM projectdb.zone_charge t0,
projectdb.weight t1 /*Unnecessary table listing.*/
WHERE ((EXISTS (SELECT t2.country_id
FROM projectdb.zone_table t3, /*Unnecessary table listing.*/
projectdb.country t2
WHERE ((( t2.country_id = ?)
AND( t0.zone_id = t3.zone_id))
AND(t3.zone_id = t2.zone_id))) /*Duplicate join.*/
AND t1.weight_id IN (SELECT MAX(t4.weight_id)
FROM projectdb.weight t4
WHERE (t4.weight BETWEEN ? AND ?)))
AND (t1.weight_id = t0.weight_id)) /*Duplicate join.*/
I just want to fetch a scalar value of type BigDecimal. It doesn't require a join here nor I mentioned one though it produces two superfluous joins which are completely unnecessary.
The cause of these redundant joins are two nested properties in the predicates given.
root.get(ZoneCharge_.weightId).get(Weight_.weightId)
and
root.get(ZoneCharge_.zoneId), countryRoot.get(Country_.zoneId)
Look at the Predicate array.
I have been trying to know the reason for a long time but I don't find anything mentioned anywhere about this phenomenon in EclipseLink.
I don't think this could be an oversight. It looks a lot deliberate.
I'm very much disappointed to see this kind of statements. Is there any reason to get EclipseLink to generate this kind of redundant joins? Are there some settings or something else to get rid of them that I might be missing in EclipseLink? Can they be avoided anyway?
Additional Information :
The table structures are simple. Three tables are involved here namely.
zone_charge
country
weight
The zone_charge table has a column weight_id which is a foreign key referencing the corresponding primary key in the weight table.
The country and zone_charge tables both have a zone_id column which a foreign key of the zone_id column in zone_table(which is not mentioned here) in both the tables.
The query returns a charge value in zone_charge in a specific zone based on the value of weight. A user is assumed to be unaware of zones. Therefore, a zone is selected in the background by this query based on a country supplied by a user by selecting a country in a drop-box.
[Updated on: Mon, 21 July 2014 20:59] by Moderator |
|
|
|
|
|
Powered by
FUDForum. Page generated in 0.02449 seconds
Home
