Description Jack 2011-01-06 07:30:44 EST

Build Identifier: 2.2.0

I recently came across a query parse error when developing a more complex JPQL Query for our application. I use the latest Glassfish 3.1 nightly build which uses EclipseLink 2.2.0. But I guess this bug will also occur with previous EclipseLink versions. 

This query has the following structure:

SELECT entity_a.property
FROM entity_a, entity_b
WHERE entity_b.property = entity_a
  AND (
       entity_b.id IN (<subquery_a with group by and having>)
       OR NOT EXISTS (<simple subquery_b>)
      )
  AND (
       entity_b.id IN (<subquery_c with group by and having>)
       OR NOT EXISTS (<simple subquery_d>)
      )
  AND (
       entity_b.id IN (<subquery_e with group by and having>)
       OR NOT EXISTS (<simple subquery_f>)
      )
...

This type of query will result in: "syntax error at [IN]. Internal Exception: MissingTokenException"

As my query is too long to post I experimented a bit and the following shorter (more or less nonsense) query example with an equal structure will fail with the same exception (translations: Mitarbeiter = Employee, nachname = surname):

SELECT m
FROM Mitarbeiter m
WHERE m.nachname = 'test'
AND (m.id IN (SELECT m1.id FROM Mitarbeiter m1 WHERE m1.nachname = 'test' GROUP BY m1.id HAVING count(m1.id) = 1)
     OR NOT EXISTS (SELECT m2.id FROM Mitarbeiter m2 WHERE m2.nachname = 'test'))

This query will fail with:

java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager: Exception Description: Syntax error parsing the query [SELECT m FROM Mitarbeiter m WHERE m.nachname = 'test' AND (m.id IN (SELECT m1.id FROM Mitarbeiter m1 WHERE m1.nachname = 'test' GROUP BY m1.id HAVING count(m1.id) = 1) OR NOT EXISTS (SELECT m2.id FROM Mitarbeiter m2 WHERE m2.nachname = 'test'))], line 4, column 10: syntax error at [IN]. Internal Exception: MissingTokenException(inserted [@-1,0:0='',<84>,4:10] at IN)
at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:1376)


When removing the HAVING clause from the subquery there will be no parse error. One can also prevent the parse error by keeping the HAVING clause and removing the parentheses for each AND condition. Obviously both changes will result in a substantially different query. But maybe this information will help you to identify the bug.

The only workaround for me now is executing each subquery containing a GROUP BY .. HAVING seperatly and put the results into the corresponding IN clause and then execute the real query.

Hopefully this bug will be fixed soon as it prevents using valid JPQL syntax. Thus I marked this bug as "Major".


Reproducible: Always

Steps to Reproduce:
1. Execute a query of the same structure as above