MySQL Syntax Error Exception with eclipselink

Question

I'm using jpa eclipselink with mysql 5.7 database when create query contain union i get the error

Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION ALL (SELECT t1.id, t1.fullName FROM PatientTable t1))' at line 1

where the query code is

EntityManager entityManager = DataMain.createEntityManager();

    String queryString = "";

    queryString = "" +
        "select " +
        "   PatientTable.id, PatientTable.fullName " +
        "from " +
        "   PatientTable as PatientTable " +
        "UNION ALL " +
        "select " +
        "   PatientTable.id, PatientTable.fullName " +
        "from " +
        "   PatientTable as PatientTable";


    Query query = entityManager.createQuery(queryString);
    query.getResultList().forEach(System.out::println);

the full error stack

Thu Apr 06 14:34:56 EDT 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Exception in thread "main" javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): 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 MySQL server version for the right syntax to use near 'UNION ALL (SELECT t1.id, t1.fullName FROM PatientTable t1))' at line 1
Error Code: 1064
Call: (SELECT t0.id, t0.fullName FROM PatientTable t0 UNION ALL (SELECT t1.id, t1.fullName FROM PatientTable t1))
Query: ReportQuery(referenceClass=PatientTable sql="(SELECT t0.id, t0.fullName FROM PatientTable t0 UNION ALL (SELECT t1.id, t1.fullName FROM PatientTable t1))")
    at org.eclipse.persistence.internal.jpa.QueryImpl.getDetailedException(QueryImpl.java:378)
    at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:260)
    at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:469)
    at com.tawaaq.app.data.persistence.query.patient.PatientQuery.main(PatientQuery.java:49)
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): 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 MySQL server version for the right syntax to use near 'UNION ALL (SELECT t1.id, t1.fullName FROM PatientTable t1))' at line 1
Error Code: 1064
Call: (SELECT t0.id, t0.fullName FROM PatientTable t0 UNION ALL (SELECT t1.id, t1.fullName FROM PatientTable t1))
Query: ReportQuery(referenceClass=PatientTable sql="(SELECT t0.id, t0.fullName FROM PatientTable t0 UNION ALL (SELECT t1.id, t1.fullName FROM PatientTable t1))")
    at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:682)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:558)
    at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2002)
    at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:570)
    at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:250)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:242)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:228)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:299)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:694)
    at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2738)
    at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllReportQueryRows(ExpressionQueryMechanism.java:2675)
    at org.eclipse.persistence.queries.ReportQuery.executeDatabaseQuery(ReportQuery.java:848)
    at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:899)
    at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1127)
    at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:403)
    at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1215)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2896)
    at org.eclipse.persistence.tools.profiler.PerformanceProfiler.profileExecutionOfQuery(PerformanceProfiler.java:426)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1802)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1786)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1751)
    at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:258)
    ... 2 more

The error has another query. Possible, problem with brackets. — Devart, Apr 06 '17 at 12:43
try to use different aliases. For exampe PatientTable and PatientTable2 — Maxim Tulupov, Apr 06 '17 at 12:48
the generated sql in the error stack `SELECT t0.id, t0.fullName FROM PatientTable t0 UNION ALL (SELECT t1.id, t1.fullName FROM PatientTable t1)` run succesesfully in my sql — , Apr 06 '17 at 12:52
THe problem that in your generated query brcets placed incorrectly. Try to add implicit bracets something like this (select 1) union all (select 2) — Maxim Tulupov, Apr 06 '17 at 12:58
with (select 1) union all (select 2) jpa cause another error say thar query must start with select — , Apr 06 '17 at 13:08
The java-code contains correct sql-code. Why there are brackets in sql-code in error message? Has someone added them? — Devart, Apr 06 '17 at 15:03
no body added them. but sql-code run successfully with brackets and without brackets — , Apr 06 '17 at 15:05
JPA does not support UNION syntax; look at the JPA spec. EclipseLink offers it as a vendor extension but your query is utterly non-portable. — Neil Stockton, Apr 06 '17 at 15:08

score 0 · Answer 1 · edited May 23 '17 at 10:30

0

I could only recreate it with Oracle as I don't have an MySql instance right now.

It worked when I removed the as when assigning aliases.

Using your example:

queryString = "" +
    "select " +
    "   PatientTable.id, PatientTable.fullName " +
    "from " +
    "   PatientTable PatientTable " +
    "UNION ALL " +
    "select " +
    "   PatientTable.id, PatientTable.fullName " +
    "from " +
    "   PatientTable PatientTable";

By the way, both statements you are union-ing are exactly the same, is that intended?

Edit: Nvm, according to this thread JPA doesn't support union: Union in JPA query - from the same table

In this thread a workaround is described: JPQL equivalent of SQL query using unions and selecting constants

edited May 23 '17 at 10:30

Community

1
1

answered Apr 06 '17 at 12:47

Jdv

962
10
34

the generated sql in the error stack SELECT t0.id, t0.fullName FROM PatientTable t0 UNION ALL (SELECT t1.id, t1.fullName FROM PatientTable t1) run succesesfully in my sql . why fail in jpa – Apr 06 '17 at 12:55
Please try it on the database directly. Do you still get the exepction? – dtell Apr 06 '17 at 13:10
it's run successfully in database – Apr 06 '17 at 13:17

MySQL Syntax Error Exception with eclipselink

1 Answers1