Get rid of redundant joins produced by subqueries in JPA criteria

Question

I simply need to execute the following MySQL query using JPA criteria (fetching a list of states (from state_table) based on a country name given (in country)).

SELECT state_id, 
       state_name, 
       country_id
FROM   state_table 
WHERE  country_id IN(SELECT country_id 
                     FROM   country 
                     WHERE  country_name = ?)

I have written the following JPA criteria query.

CriteriaBuilder criteriaBuilder=entityManager.getCriteriaBuilder();
CriteriaQuery<StateTable>criteriaQuery=criteriaBuilder.createQuery(StateTable.class);
Root<StateTable> root = criteriaQuery.from(entityManager.getMetamodel().entity(StateTable.class));

Subquery<Long> subquery = criteriaQuery.subquery(Long.class);
Root<Country> subRoot = subquery.from(Country.class);
subquery.select(subRoot.get(Country_.countryId));

ParameterExpression<String>parameterExpression=criteriaBuilder.parameter(String.class);
subquery.where(criteriaBuilder.equal(subRoot.get(Country_.countryName), parameterExpression));
criteriaQuery.where(criteriaBuilder.in(root.get(StateTable_.country).get(Country_.countryId)).value(subquery));
List<StateTable> list = entityManager.createQuery(criteriaQuery).setParameter(parameterExpression, "India").getResultList();

This criteria query unnecessarily produces a redundant join in the generated SQL query as follows.

SELECT t1.state_id, 
       t1.state_name, 
       t1.country_id 
FROM   projectdb.country t0, 
       projectdb.state_table t1 
WHERE  (t0.country_id IN (SELECT t2.country_id 
                           FROM   projectdb.country t2 
                           WHERE  (t2.country_name = ? )) 
         AND (t0.country_id = t1.country_id )) 

As can be noticed, there is a redundant join AND (t0.country_id = t1.country_id )).

---

This is caused by root.get(StateTable_.countryId).get(Country_.countryId) which implies an inner join in the following line in the criteria query given above . This should not happen unnecessarily in this case.

criteriaQuery.where(criteriaBuilder.in(root.get(StateTable_.countryId).get(Country_.countryId)).value(subquery));

How to remove this redundant join?

I'm using JPA 2.0 provided by EclipseLink 2.3.2.

_{Instead of using a subquery in this case, it can better be handled by using a join between these two tables but writing such subqueries without redundant joins in them should be possible, since it is a quite basic thing.}

---

EDIT 1:

An equivalent EXISTS() query works fine as follows.

CriteriaBuilder criteriaBuilder=entityManager.getCriteriaBuilder();
CriteriaQuery<StateTable>criteriaQuery=criteriaBuilder.createQuery(StateTable.class);
Root<StateTable> root = criteriaQuery.from(entityManager.getMetamodel().entity(StateTable.class));
criteriaQuery.select(root);

Subquery<Long> subquery = criteriaQuery.subquery(Long.class);
Root<Country> subRoot = subquery.from(Country.class);
subquery.select(subRoot.get(Country_.countryId));

ParameterExpression<String>parameterExpression=criteriaBuilder.parameter(String.class);
subquery.where(criteriaBuilder.equal(root.get(StateTable_.country), subRoot), criteriaBuilder.equal(subRoot.get(Country_.countryName), parameterExpression));

criteriaQuery.where(criteriaBuilder.exists(subquery));
List<StateTable> list = entityManager.createQuery(criteriaQuery).setParameter(parameterExpression, "India").getResultList();

Resulting in producing the following correct SQL query that will in turn be delegated to MySQL.

SELECT t0.state_id, 
       t0.state_name, 
       t0.country_id 
FROM   projectdb.state_table t0 
WHERE  EXISTS (SELECT t1.country_id 
               FROM   projectdb.country t1 
               WHERE  ((t0.country_id = t1.country_id) 
                        AND (t1.country_name = ?))) 

Found no way to generate an equivalent IN() query.

---

EDIT 2:

The following JPQL,

SELECT s 
FROM   StateTable s 
WHERE  s.country IN(SELECT c 
                      FROM   Country c 
                      WHERE  c.countryname = :countryName) 

produces the correct IN() subquery.

SELECT t0.state_id, 
       t0.state_name, 
       t0.country_id 
FROM   projectdb.state_table t0 
WHERE  t0.country_id IN (SELECT t1.country_id 
                         FROM   projectdb.country t1 
                         WHERE  (t1.country_name = ?)) 

From this, I could envision the following criteria query.

CriteriaBuilder criteriaBuilder=entityManager.getCriteriaBuilder();
CriteriaQuery<StateTable>criteriaQuery=criteriaBuilder.createQuery(StateTable.class);
Root<StateTable> root = criteriaQuery.from(entityManager.getMetamodel().entity(StateTable.class));
criteriaQuery.select(root);

Subquery<Country> subquery = criteriaQuery.subquery(Country.class);
Root<Country> subRoot = subquery.from(Country.class);
subquery.select(subRoot);

ParameterExpression<String>parameterExpression=criteriaBuilder.parameter(String.class);
subquery.where(criteriaBuilder.equal(subRoot.get(Country_.countryName), parameterExpression));

criteriaQuery.where(criteriaBuilder.in(root.get(StateTable_.country)).value(subquery));
List<StateTable> list = entityManager.createQuery(criteriaQuery).setParameter(parameterExpression, "India").getResultList();

But it fails with the following exception at run time.

Exception [EclipseLink-6048] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.QueryException
Exception Description: Illegal use of getField() [projectdb.country.country_id] in expression.
Query: ReadAllQuery(referenceClass=StateTable )
    at org.eclipse.persistence.exceptions.QueryException.illegalUseOfGetField(QueryException.java:563)
    at org.eclipse.persistence.expressions.Expression.getField(Expression.java:1739)
    at org.eclipse.persistence.internal.expressions.FunctionExpression.normalize(FunctionExpression.java:476)
    at org.eclipse.persistence.internal.expressions.SQLSelectStatement.normalize(SQLSelectStatement.java:1402)
    at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.buildNormalSelectStatement(ExpressionQueryMechanism.java:549)
    at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.prepareSelectAllRows(ExpressionQueryMechanism.java:1708)
    at org.eclipse.persistence.queries.ReadAllQuery.prepareSelectAllRows(ReadAllQuery.java:785)
    at org.eclipse.persistence.queries.ReadAllQuery.prepare(ReadAllQuery.java:716)
    at org.eclipse.persistence.queries.DatabaseQuery.checkPrepare(DatabaseQuery.java:661)
    at org.eclipse.persistence.queries.ObjectLevelReadQuery.checkPrepare(ObjectLevelReadQuery.java:888)
    at org.eclipse.persistence.queries.DatabaseQuery.checkPrepare(DatabaseQuery.java:613)
    at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:867)
    at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1114)
    at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:402)
    at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1202)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2894)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1797)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1779)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1744)
    at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:258)
    at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:468)
    at client.beans.ProductDetailsBean.test(ProductDetailsBean.java:139)
    at client.beans.ProductDetailsBean.getProductList(ProductDetailsBean.java:119)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:601)
    at org.glassfish.ejb.security.application.EJBSecurityManager.runMethod(EJBSecurityManager.java:1081)
    at org.glassfish.ejb.security.application.EJBSecurityManager.invoke(EJBSecurityManager.java:1153)
    at com.sun.ejb.containers.BaseContainer.invokeBeanMethod(BaseContainer.java:4695)
    at com.sun.ejb.EjbInvocation.invokeBeanMethod(EjbInvocation.java:630)
    at com.sun.ejb.containers.interceptors.AroundInvokeChainImpl.invokeNext(InterceptorManager.java:822)
    at com.sun.ejb.EjbInvocation.proceed(EjbInvocation.java:582)
    at org.jboss.weld.ejb.AbstractEJBRequestScopeActivationInterceptor.aroundInvoke(AbstractEJBRequestScopeActivationInterceptor.java:46)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:601)
    at com.sun.ejb.containers.interceptors.AroundInvokeInterceptor.intercept(InterceptorManager.java:883)
    at com.sun.ejb.containers.interceptors.AroundInvokeChainImpl.invokeNext(InterceptorManager.java:822)
    at com.sun.ejb.EjbInvocation.proceed(EjbInvocation.java:582)
    at com.sun.ejb.containers.interceptors.SystemInterceptorProxy.doCall(SystemInterceptorProxy.java:163)
    at com.sun.ejb.containers.interceptors.SystemInterceptorProxy.aroundInvoke(SystemInterceptorProxy.java:140)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:601)
    at com.sun.ejb.containers.interceptors.AroundInvokeInterceptor.intercept(InterceptorManager.java:883)
    at com.sun.ejb.containers.interceptors.AroundInvokeChainImpl.invokeNext(InterceptorManager.java:822)
    at com.sun.ejb.containers.interceptors.InterceptorManager.intercept(InterceptorManager.java:369)
    at com.sun.ejb.containers.BaseContainer.__intercept(BaseContainer.java:4667)
    at com.sun.ejb.containers.BaseContainer.intercept(BaseContainer.java:4655)
    at com.sun.ejb.containers.EJBLocalObjectInvocationHandler.invoke(EJBLocalObjectInvocationHandler.java:212)
    at com.sun.ejb.containers.EJBLocalObjectInvocationHandlerDelegate.invoke(EJBLocalObjectInvocationHandlerDelegate.java:88)
    at $Proxy482.getProductList(Unknown Source)
    at client.bean.ProductDetailsManagedBean.load(ProductDetailsManagedBean.java:59)
    at org.primefaces.component.datagrid.DataGrid.loadLazyData(DataGrid.java:144)
    at org.primefaces.component.datagrid.DataGridRenderer.encodeMarkup(DataGridRenderer.java:54)
    at org.primefaces.component.datagrid.DataGridRenderer.encodeEnd(DataGridRenderer.java:47)
    at javax.faces.component.UIComponentBase.encodeEnd(UIComponentBase.java:919)
    at org.primefaces.renderkit.CoreRenderer.renderChild(CoreRenderer.java:74)
    at org.primefaces.renderkit.CoreRenderer.renderChildren(CoreRenderer.java:57)
    at org.primefaces.component.panel.PanelRenderer.encodeContent(PanelRenderer.java:204)
    at org.primefaces.component.panel.PanelRenderer.encodeMarkup(PanelRenderer.java:121)
    at org.primefaces.component.panel.PanelRenderer.encodeEnd(PanelRenderer.java:58)
    at javax.faces.component.UIComponentBase.encodeEnd(UIComponentBase.java:919)
    at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1864)
    at javax.faces.render.Renderer.encodeChildren(Renderer.java:176)
    at javax.faces.component.UIComponentBase.encodeChildren(UIComponentBase.java:889)
    at org.primefaces.renderkit.CoreRenderer.renderChild(CoreRenderer.java:70)
    at org.primefaces.renderkit.CoreRenderer.renderChildren(CoreRenderer.java:57)
    at org.primefaces.component.layout.LayoutUnitRenderer.encodeEnd(LayoutUnitRenderer.java:51)
    at javax.faces.component.UIComponentBase.encodeEnd(UIComponentBase.java:919)
    at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1864)
    at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1860)
    at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1860)
    at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1860)
    at com.sun.faces.application.view.FaceletViewHandlingStrategy.renderView(FaceletViewHandlingStrategy.java:461)
    at com.sun.faces.application.view.MultiViewHandler.renderView(MultiViewHandler.java:133)
    at javax.faces.application.ViewHandlerWrapper.renderView(ViewHandlerWrapper.java:337)
    at javax.faces.application.ViewHandlerWrapper.renderView(ViewHandlerWrapper.java:337)
    at com.sun.faces.lifecycle.RenderResponsePhase.execute(RenderResponsePhase.java:120)
    at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
    at com.sun.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:219)
    at javax.faces.webapp.FacesServlet.service(FacesServlet.java:647)
    at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1682)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:344)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)
    at org.primefaces.webapp.filter.FileUploadFilter.doFilter(FileUploadFilter.java:70)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:256)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:316)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:160)
    at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:734)
    at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:673)
    at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:99)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:174)
    at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:357)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:260)
    at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:188)
    at org.glassfish.grizzly.http.server.HttpHandler.runService(HttpHandler.java:191)
    at org.glassfish.grizzly.http.server.HttpHandler.doHandle(HttpHandler.java:168)
    at org.glassfish.grizzly.http.server.HttpServerFilter.handleRead(HttpServerFilter.java:189)
    at org.glassfish.grizzly.filterchain.ExecutorResolver$9.execute(ExecutorResolver.java:119)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeFilter(DefaultFilterChain.java:288)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeChainPart(DefaultFilterChain.java:206)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.execute(DefaultFilterChain.java:136)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.process(DefaultFilterChain.java:114)
    at org.glassfish.grizzly.ProcessorExecutor.execute(ProcessorExecutor.java:77)
    at org.glassfish.grizzly.nio.transport.TCPNIOTransport.fireIOEvent(TCPNIOTransport.java:838)
    at org.glassfish.grizzly.strategies.AbstractIOStrategy.fireIOEvent(AbstractIOStrategy.java:113)
    at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.run0(WorkerThreadIOStrategy.java:115)
    at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.access$100(WorkerThreadIOStrategy.java:55)
    at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy$WorkerThreadRunnable.run(WorkerThreadIOStrategy.java:135)
    at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:564)
    at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.run(AbstractThreadPool.java:544)
    at java.lang.Thread.run(Thread.java:722)

I cannot see any reason behind this exception.

---

EDIT 3:

The same criteria query in the latest revision - the one which fails with the exception, works fine with no modification at all on JPA provided by Hibernate (4.2.7 final) with the exact same table relationship in a different project.

It produces the following correct SQL query.

SELECT
    statetable0_.state_id as state_id1_24_,
    statetable0_.country_id as country_3_24_,
    statetable0_.state_name as state_na2_24_ 
FROM
    social_networking.state_table statetable0_ 
WHERE
    statetable0_.country_id IN (
        SELECT
            country1_.country_id 
        FROM
            social_networking.country country1_ 
        WHERE
            country1_.country_name=?
    )

Therefore, there should something unreliable be happening under the hood in EclipseLink.

Should I safely assume that this is an oversight in EclipseLink in the criteria API?

---

EDIT 4:

The use of EXISTS() as demonstrated above also produces a redundant join, if the query statement is reversed like,

SELECT * 
FROM   country c 
WHERE  EXISTS (SELECT s.country_id 
               FROM   state_table s 
               WHERE  s.country_id = c.country_id 
                      AND s.state_name = 'desired_state_name') 

JPA provided by Hibernate (I'm currently using 4.2.7 final) generates query statements as we usually expect with no such redundant joins.

This is a very basic query and it is quite hard to believe that this is an oversight in EclipseLink. It seems to me that I must be doing something wrong, must be missing something obvious, very basic. Please clarify.

I have recently upgraded EclipseLink to its current, latest release 2.5.1 having JPA 2.1.

---

Long story short : Referencing a nested property in an entity like root.get("property1").get("propery2InAnotherRelatedEntity") always causes a superfluous join to be generated which, in general should not happen.

_{Please forgive me, if I have made so many revisions but I had to... :)}

Answer 1

Redundant joins in your examples are produced by incorrect JPA criteria query construction and not by presence of subqueries in them. So the title of the question really has not much sense.

To get rid of redundant joins you just need to construct criteria properly. And indeed the criteria query in your EDIT 2 is the correct translation of your original SQL query at the beginning of the question to JPA criteria language, and should not produce any redundant joins. Your experimentation using Hibernate confirms it. And the QueryException which you got is definitely a bug of EclipseLink. I suggest your to report it to developers of EclipseLink: https://wiki.eclipse.org/EclipseLink/Bugs.

But in fact, the subquery is not needed here at all, and joins are usually not evil if used correctly, so I suggest to replace the SQL query with this perfectly equivalent, but more concise one:

SELECT state_id,
       state_name,
       s.country_id
FROM   state_table s, country c
WHERE  s.country_id = c.country_id AND country_name = ?

Its translation to criteria query is also much more simple and clear (and surely will not confuse EclipseLink :)):

CriteriaBuilder criteriaBuilder =
    entityManager.getCriteriaBuilder();

CriteriaQuery<StateTable> criteriaQuery =
    criteriaBuilder.createQuery(StateTable.class);

Root<StateTable> root = criteriaQuery.from(StateTable.class);
ParameterExpression<String> parameterExpression =
    criteriaBuilder.parameter(String.class);

criteriaQuery.where(criteriaBuilder.equal(
    root.get(StateTable_.country).get(Country_.countryName),
    parameterExpression));

List<StateTable> list = entityManager.createQuery(criteriaQuery)
    .setParameter(parameterExpression, "India").getResultList();

And performance of this in MySQL won't be worse than that of the original one.

Answer 2

Does JPA has native methods?
I know hibernate can do complex query by native sql, so that you can do optimization to the sql.

Answer 3

Could it be something simple like this:

    query = em.createNativeQuery("SELECT state_id, state_name, country_id 
FROM state_table,country
WHERE  state_table.country_id=country.country_id and country.country_name = ?1)").setParameter(1, country_name);
list = (List<Object>) query.getResultList();

where country_name is the parameter you are searching for and list is the list of objects returned by the native query?

Answer 4

This is just a partial / incomplete answer (I could have appended it to the question but there are already so many revisions).

Regarding EDIT 4 in the question, the following criteria query produces a correct SQL query (regarding EXISTS()).

CriteriaBuilder criteriaBuilder=entityManager.getCriteriaBuilder();
CriteriaQuery<Country> criteriaQuery = criteriaBuilder.createQuery(Country.class);
Metamodel metamodel = entityManager.getMetamodel();
Root<Country> root = criteriaQuery.from(metamodel.entity(Country.class));
criteriaQuery.select(root);

Subquery<Long> subquery = criteriaQuery.subquery(Long.class);
Root<StateTable> subRoot = subquery.from(metamodel.entity(StateTable.class));
subquery.select(subRoot.get(StateTable_.stateId));

ParameterExpression<String>parameterExpression=criteriaBuilder.parameter(String.class);
subquery.where(criteriaBuilder.equal(root, subRoot.get(StateTable_.countryId)), criteriaBuilder.equal(subRoot.get(StateTable_.stateName), parameterExpression));
criteriaQuery.where(criteriaBuilder.exists(subquery));
List<Country> list = entityManager.createQuery(criteriaQuery).setParameter(parameterExpression, "desired_state_name").getResultList();

This produces the following query.

SELECT t0.country_id, 
       t0.country_code, 
       t0.country_name, 
       t0.zone_id 
FROM   projectdb.country t0 
WHERE  EXISTS (SELECT t1.state_id 
               FROM   projectdb.state_table t1 
               WHERE  ((t0.country_id = t1.country_id)
                        AND (t1.state_name = ?)))

---

_{I'm not going to accept my own answer anymore.}