Illegal use of getField() in expression.

[jansohn]

Copying from https://bugs.eclipse.org/bugs/show_bug.cgi?id=433126 as this is still an issue in the latest release 3.0.2.

===============================

Given two very basic tables in MySQL database, country holding a list of countries and state_table holding a list of states that belonging to related countries in the country table.

country_id and state_id are two key attributes in respective tables.

Considering the following SQL query (in MySQL),

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

The corresponding JPQL can be constructed as follows.

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

This JPQL does exactly as it stands for.

The following translation of this JPQL to criteria query like so,

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, "desired_country_name").getResultList();

however, fails with the following exception.

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)

More discussions can be found on the following link.

http://stackoverflow.com/q/22825644/1391249

===============================

More references of this issue:

• https://bugs.eclipse.org/bugs/show_bug.cgi?id=444610
• https://stackoverflow.com/questions/70744229/criteriaapi-subquery-creates-wrong-sql-invalid-identifier

[numeralnathan]

I just hit this exception with the following query:

update
   City city
set
   city.country = (
      select
         country
      from
         State state,
         Country country
      where
         city.state = state
         and state.country = country
   )
where
   city.country is null

city.state can only match one State. state.country can only match one Country. This means that the select statement can only return 1 row.