Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Why do subqueries with nested properties in EclipseLink always produce unnecessary/redundant joins?
Why do subqueries with nested properties in EclipseLink always produce unnecessary/redundant joins? [message #1403020] Sun, 20 July 2014 04:02 Go to next message
Tiny 2000 is currently offline Tiny 2000Friend
Messages: 3
Registered: July 2014
Junior Member
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: Tue, 22 July 2014 00:59]

Report message to a moderator

Re: Why do subqueries with nested properties in EclipseLink always produce unnecessary/redundant joi [message #1434875 is a reply to message #1403020] Tue, 30 September 2014 18:44 Go to previous messageGo to next message
Tiny 2000 is currently offline Tiny 2000Friend
Messages: 3
Registered: July 2014
Junior Member
Kindly reply Smile
Re: Why do subqueries with nested properties in EclipseLink always produce unnecessary/redundant joi [message #1435131 is a reply to message #1434875] Wed, 01 October 2014 04:04 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1059
Registered: July 2009
Senior Member
EclipseLink always uses the mappings as described in your entities. In the SQL you've shown, you can see that projectdb.zone_table t3 is an intermediary table between t2 and t0. EclipseLink code does not make assumptions about the fields involved, and so even though you can see that t3 isn't needed and (t0.zone_id = t2.zone_id) can be used directly, EclipseLink does not.

I don't know how you have this mapped, but if you want zone_table to be excluded from the generated SQL, you might try mapping your zone_charge and country tables together directly using the zone_id relationships. JPA requires foreign keys reference primary keys, while EclipseLink does not. Please also file an enhancement request to have the expressions generate more efficient SQL.
Re: Why do subqueries with nested properties in EclipseLink always produce unnecessary/redundant joi [message #1484064 is a reply to message #1435131] Sun, 23 November 2014 05:00 Go to previous message
Tiny 2000 is currently offline Tiny 2000Friend
Messages: 3
Registered: July 2014
Junior Member
I think, some issues are already reported. Some of them are can be found in the following post.

bugs.eclipse.org/bugs/show_bug.cgi?id=246211
bugs.eclipse.org/bugs/show_bug.cgi?id=298494
bugs.eclipse.org/bugs/show_bug.cgi?id=395792
bugs.eclipse.org/bugs/show_bug.cgi?id=300625
bugs.eclipse.org/bugs/show_bug.cgi?id=433126

I'm not allowed to post direct links of other sites here. Just prefix https:// to each of them. If it is a pain then, those links are available on this site (at the end of the post). I already posted the same message there.

stackoverflow.com/q/24837320/1391249

prefix http:// to the above URL. Just paste the link in the browser address bar.

Thank you Smile

[Updated on: Sun, 23 November 2014 11:08]

Report message to a moderator

Previous Topic:EclipseLink with Ucanaccess driver
Next Topic:EL cache invalidation capabilities does not work
Goto Forum:
  


Current Time: Wed Apr 22 02:04:02 GMT 2015

Powered by FUDForum. Page generated in 0.01793 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software