|Why do subqueries with nested properties in EclipseLink always produce unnecessary/redundant joins? [message #1403020]
||Sun, 20 July 2014 04:02
| Tiny 2000
Registered: July 2014
I wanted to generate the following SQL through EclipseLink (2.5.2) JPA criteria.|
zonecharge0_.charge AS col_0_0_
zonecharge0_.weight_id IN (
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.
Metamodel metamodel = entityManager.getMetamodel();
Root<ZoneCharge> root = criteriaQuery.from(metamodel.entity(ZoneCharge.class));
Subquery<Long> countrySubquery = criteriaQuery.subquery(Long.class);
Root<Country> countryRoot = countrySubquery.from(metamodel.entity(Country.class));
Predicate predicates=new Predicate;
Subquery<Long> weightSubquery = criteriaQuery.subquery(Long.class);
Root<Weight> weightRoot = weightSubquery.from(metamodel.entity(Weight.class));
weightSubquery.where(criteriaBuilder.between(weightRoot.get(Weight_.weight), BigDecimal.ZERO, weight));
BigDecimal charge = entityManager.createQuery(criteriaQuery).getSingleResult();
The corresponding JPQL :
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.
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.*/
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.
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.
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: Tue, 22 July 2014 00:59]
Report message to a moderator
Powered by FUDForum
. Page generated in 0.03405 seconds