EclipseLink query with UNION causes error

[Andreas Roerig]

Hi,

I've got a problem with EclipseLink (Version 2.5 and 2.6), MySQL and a query using UNION.
Here is what I try to do:

        List<Long> result = null;
        EntityManager em = factory.createEntityManager();
        Query q = em.createQuery("select c.bxItem1Id from ContactKK c where c.bxItem2Id = :kid1 and c.kontaktDatum is not null " +
                "union select c.bxItem2Id from ContactKK c where c.bxItem1Id = :kid1 and c.kontaktDatum is not null");
        q.setParameter("kid1", kandidatId);
        result = (List<Long>) q.getResultList();
        em.close();

This produces the following error messages:

[EL Fine]: sql: 2015-05-06 16:25:33.876--ServerSession(977246141)--Thread(Thread[http-bio-8080-exec-6,5,main])--SELECT 1
[EL Warning]: 2015-05-06 16:25:33.877--UnitOfWork(233203564)--Thread(Thread[http-bio-8080-exec-6,5,main])--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.0.v20150309-bf26070): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION (SELECT t1.BXITEM2ID FROM CONTACTKK t1 WHERE ((t1.BXITEM1ID = 1) AND (t1.K' at line 1
Error Code: 1064
Call: (SELECT t0.BXITEM1ID FROM CONTACTKK t0 WHERE ((t0.BXITEM2ID = ?) AND (t0.KONTAKTDATUM IS NOT NULL)) UNION (SELECT t1.BXITEM2ID FROM CONTACTKK t1 WHERE ((t1.BXITEM1ID = ?) AND (t1.KONTAKTDATUM IS NOT NULL))))
bind => [1, 1]
Query: ReportQuery(referenceClass=ContactKK sql="(SELECT t0.BXITEM1ID FROM CONTACTKK t0 WHERE ((t0.BXITEM2ID = ?) AND (t0.KONTAKTDATUM IS NOT NULL)) UNION (SELECT t1.BXITEM2ID FROM CONTACTKK t1 WHERE ((t1.BXITEM1ID = ?) AND (t1.KONTAKTDATUM IS NOT NULL))))")
Mai 06, 2015 4:25:33 PM org.apache.catalina.core.StandardWrapperValve invoke
Schwerwiegend: Servlet.service() for servlet [Faces Servlet] in context with path [/bx-netzwerk] threw exception [Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.0.v20150309-bf26070): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION (SELECT t1.BXITEM2ID FROM CONTACTKK t1 WHERE ((t1.BXITEM1ID = 1) AND (t1.K' at line 1
Error Code: 1064
Call: (SELECT t0.BXITEM1ID FROM CONTACTKK t0 WHERE ((t0.BXITEM2ID = ?) AND (t0.KONTAKTDATUM IS NOT NULL)) UNION (SELECT t1.BXITEM2ID FROM CONTACTKK t1 WHERE ((t1.BXITEM1ID = ?) AND (t1.KONTAKTDATUM IS NOT NULL))))
bind => [1, 1]
Query: ReportQuery(referenceClass=ContactKK sql="(SELECT t0.BXITEM1ID FROM CONTACTKK t0 WHERE ((t0.BXITEM2ID = ?) AND (t0.KONTAKTDATUM IS NOT NULL)) UNION (SELECT t1.BXITEM2ID FROM CONTACTKK t1 WHERE ((t1.BXITEM1ID = ?) AND (t1.KONTAKTDATUM IS NOT NULL))))")] with root cause
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION (SELECT t1.BXITEM2ID FROM CONTACTKK t1 WHERE ((t1.BXITEM1ID = 1) AND (t1.K' at line 1

It seems EclipseLink packs the whole SQL statement into parentheses and that causes the syntax error on the database side. If I copy the statement, remove the first and the last parentheses then the statement runs fine in SQL-Explorer.
My other queries in the application run fine, but this is the first time I used UNION.

Does anybody know how to solve this?

Best regards
Andreas

[Roel De Nijs]

Someone else seems to have a similar issue, unfortunately without a solution. Seems to be an ElipseLink bug.

[Andreas Roerig]

Roel De Nijs wrote:Someone else seems to have a similar issue, unfortunately without a solution. Seems to be an ElipseLink bug.

Thanks Roel,

well, then I have to find a way around this problem.

Best regards
Andreas

[Roel De Nijs]

Andreas Roerig wrote:well, then I have to find a way around this problem.

What about something like as a starting point:select c.bxItem1Id, c.bxItem2Id from ContactKK c where (c.bxItem2Id = :kid1 or c.bxItem1Id = :kid1) and c.kontaktDatum is not null
Or maybe you can use a case expressionsselect CASE WHEN c.bxItem2Id = :kid1 THEN c.bxItem1Id WHEN c.bxItem1Id = :kid1 THEN c.bxItem2Id ELSE -1 END from ContactKK c where c.kontaktDatum is not null

Just some (crazy) thoughts

[Andreas Roerig]

Hi Roel,

thanks for your suggestions.
In the mean time I solved this the uggly way by splitting the query into two:

        List<Long> result = null;
        EntityManager em = factory.createEntityManager();
        Query q = em.createQuery("select c.bxItem1Id from ContactKK c where c.bxItem2Id = :kid1 and c.kontaktDatum is not null");
        q.setParameter("kid1", kandidatId);
        result = (List<Long>) q.getResultList();
        q = em.createQuery("select c.bxItem2Id from ContactKK c where c.bxItem1Id = :kid1 and c.kontaktDatum is not null");
        q.setParameter("kid1", kandidatId);
        List<Long> result2 = (List<Long>) q.getResultList();
        em.close();
        result.addAll(result2);

Not the nicest approach, but it works.

Best regards
Andreas

[Roel De Nijs]

Andreas Roerig wrote:Not the nicest approach, but it works.

True!

Although you could improve your code a little bit:

EntityManager em = factory.createEntityManager();
List<Long> result = new ArrayList<>();
result.addAll(getItemIds(em, "select c.bxItem1Id from ContactKK c where c.bxItem2Id = :kid1 and c.kontaktDatum is not null", kandidatId);
result.addAll(getItemIds(em, "select c.bxItem2Id from ContactKK c where c.bxItem1Id = :kid1 and c.kontaktDatum is not null", kandidatId);
em.close();

private List<Long> getItemIds(EntityManager em, String query, int kandidatId) {
  Query q = em.createQuery(query);
  q.setParameter("kid1", kandidatId);
  return (List<Long>) q.getResultList();
}