eclipselink
eclipselink copied to clipboard
eclipse-ee4j
JPQL query with UNION not working using eclipselink causing MySQLSyntaxErrorException
I'm transfering this issue from bugzilla: https://bugs.eclipse.org/bugs/show_bug.cgi?id=482858
http://stackoverflow.com/questions/33873334/jpql-query-with-union-not-working-using-eclipselink-causing-mysqlsyntaxerrorexce
ECLIPSELINK version 2.6.0(updated to 2.6.1), MYSQL 5.6
this is the sql query i want to write as a jpql query. i have tested the sql query in mysql it works
SELECT * FROM task
where tid=6
and date between '2015-11-01' and '2015-11-30'
UNION
select * from task
where pid in (select pid from peopleteam
where tid=6)
and date between '2015-11-01' and '2015-11-30'
;
i am writing it as a jpql query as below
public List<Task> findTeamsTasks(int tid,Date d1, Date d2){
Query q=em.createQuery("SELECT t1 "
+ "FROM Task t1 "
+ "where (t1.team.tid=?1) "
+ "and (t1.date Between ?2 and ?3) "
+ "UNION SELECT t2 "
+ "FROM Task t2 "
+ "where (t2.people.pid IN (SELECT pt.people.pid "
+ "from Peopleteam pt "
+ "where (pt.team.tid=?1))) "
+ "and (t2.date Between ?2 and ?3)");
q.setParameter(1, tid);
q.setParameter(2, d1);
q.setParameter(3, d2);
List<Task> ftask=(List<Task>)q.getResultList();
return ftask;
}
but when i run it i get the following exception
2015-11-23T17:09:33.927+0300|Warning: Local Exception Stack:
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 MySQL server version for the right syntax to use near 'UNION (SELECT t1.TASKID, t1.DATE, t1.NAME, t1.REGION, t1.did, t1.pid, t1.tid FRO' at line 1
Error Code: 1064
Call: (SELECT t0.TASKID, t0.DATE, t0.NAME, t0.REGION, t0.did, t0.pid, t0.tid FROM TASK t0 WHERE ((t0.tid = ?) AND (t0.DATE BETWEEN ? AND ?)) UNION (SELECT t1.TASKID, t1.DATE, t1.NAME, t1.REGION, t1.did, t1.pid, t1.tid FROM PEOPLE t2, TASK t1 WHERE ((t2.PID IN (SELECT t3.PID FROM PEOPLETEAM t4, PEOPLE t3 WHERE ((t4.tid = ?) AND (t3.PID = t4.pid))) AND (t1.DATE BETWEEN ? AND ?)) AND (t2.PID = t1.pid))))
bind => [6 parameters bound]
Query: ReadAllQuery(referenceClass=Task sql="(SELECT t0.TASKID, t0.DATE, t0.NAME, t0.REGION, t0.did, t0.pid, t0.tid FROM TASK t0 WHERE ((t0.tid = ?) AND (t0.DATE BETWEEN ? AND ?)) UNION (SELECT t1.TASKID, t1.DATE, t1.NAME, t1.REGION, t1.did, t1.pid, t1.tid FROM PEOPLE t2, TASK t1 WHERE ((t2.PID IN (SELECT t3.PID FROM PEOPLETEAM t4, PEOPLE t3 WHERE ((t4.tid = ?) AND (t3.PID = t4.pid))) AND (t1.DATE BETWEEN ? AND ?)) AND (t2.PID = t1.pid))))")
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:684)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:560)
at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2055)
at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:473)
at ng.task.controller.ManagerTask.findTeamsTasks(ManagerTask.java:171)
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:606)
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:606)
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
ng.task.view.FindTeamTaskBean.getFilteredTasks(FindTeamTaskBean.java:43)
at ng.task.view.FindTeamTaskBean$Proxy$_$$_WeldClientProxy.getFilteredTasks(Unknown Source)
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:606)
at javax.el.ELUtil.invokeMethod(ELUtil.java:326)
at javax.el.BeanELResolver.invoke(BeanELResolver.java:536)
at javax.el.CompositeELResolver.invoke(CompositeELResolver.java:256)
at com.sun.el.parser.AstValue.invoke(AstValue.java:269)
at com.sun.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:304)
at org.jboss.weld.util.el.ForwardingMethodExpression.invoke(ForwardingMethodExpression.java:40)
at org.jboss.weld.el.WeldMethodExpression.invoke(WeldMethodExpression.java:50)
at com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:105)
at javax.faces.event.MethodExpressionActionListener.processAction(MethodExpressionActionListener.java:147)
at javax.faces.event.ActionEvent.processListener(ActionEvent.java:88)
at javax.faces.component.UIComponentBase.broadcast(UIComponentBase.java:818)
at javax.faces.component.UICommand.broadcast(UICommand.java:300)
at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:790)
at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:1282)
at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:81)
at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:198)
at javax.faces.webapp.FacesServlet.service(FacesServlet.java:646)
at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1682)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:318)
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:744)
Caused by: 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 (SELECT t1.TASKID, t1.DATE, t1.NAME, t1.REGION, t1.did, t1.pid, t1.tid FRO' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at
2015-11-23T17:09:33.929+0300|Warning: EJB5184:A system exception occurred during an invocation on EJB ManagerTask, method: public java.util.List ng.task.controller.ManagerTask.findTeamsTasks(int,java.util.Date,java.util.Date)
2015-11-23T17:09:33.929+0300|Warning: javax.ejb.EJBException
at com.sun.ejb.containers.EJBContainerTransactionManager.processSystemException(EJBContainerTransactionManager.java:748)
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:744)
Caused by: javax.persistence.PersistenceException: 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 MySQL server version for the right syntax to use near 'UNION (SELECT t1.TASKID, t1.DATE, t1.NAME, t1.REGION, t1.did, t1.pid, t1.tid FRO' at line 1
Error Code: 1064
Call: (SELECT t0.TASKID, t0.DATE, t0.NAME, t0.REGION, t0.did, t0.pid, t0.tid FROM TASK t0 WHERE ((t0.tid = ?) AND (t0.DATE BETWEEN ? AND ?)) UNION (SELECT t1.TASKID, t1.DATE, t1.NAME, t1.REGION, t1.did, t1.pid, t1.tid FROM PEOPLE t2, TASK t1 WHERE ((t2.PID IN (SELECT t3.PID FROM PEOPLETEAM t4, PEOPLE t3 WHERE ((t4.tid = ?) AND (t3.PID = t4.pid))) AND (t1.DATE BETWEEN ? AND ?)) AND (t2.PID = t1.pid))))
bind => [6 parameters bound]
Query: ReadAllQuery(referenceClass=Task sql="(SELECT t0.TASKID, t0.DATE, t0.NAME, t0.REGION, t0.did, t0.pid, t0.tid FROM TASK t0 WHERE ((t0.tid = ?) AND (t0.DATE BETWEEN ? AND ?)) UNION (SELECT t1.TASKID, t1.DATE, t1.NAME, t1.REGION, t1.did, t1.pid, t1.tid FROM PEOPLE t2, TASK t1 WHERE ((t2.PID IN (SELECT t3.PID FROM PEOPLETEAM t4, PEOPLE t3 WHERE ((t4.tid = ?) AND (t3.PID = t4.pid))) AND (t1.DATE BETWEEN ? AND ?)) AND (t2.PID = t1.pid))))")
at org.eclipse.persistence.internal.jpa.QueryImpl.getDetailedException(QueryImpl.java:382)
Caused by: 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 MySQL server version for the right syntax to use near 'UNION (SELECT t1.TASKID, t1.DATE, t1.NAME, t1.REGION, t1.did, t1.pid, t1.tid FRO' at line 1
Error Code: 1064
Call: (SELECT t0.TASKID, t0.DATE, t0.NAME, t0.REGION, t0.did, t0.pid, t0.tid FROM TASK t0 WHERE ((t0.tid = ?) AND (t0.DATE BETWEEN ? AND ?)) UNION (SELECT t1.TASKID, t1.DATE, t1.NAME, t1.REGION, t1.did, t1.pid, t1.tid FROM PEOPLE t2, TASK t1 WHERE ((t2.PID IN (SELECT t3.PID FROM PEOPLETEAM t4, PEOPLE t3 WHERE ((t4.tid = ?) AND (t3.PID = t4.pid))) AND (t1.DATE BETWEEN ? AND ?)) AND (t2.PID = t1.pid))))
bind => [6 parameters bound]
Query: ReadAllQuery(referenceClass=Task sql="(SELECT t0.TASKID, t0.DATE, t0.NAME, t0.REGION, t0.did, t0.pid, t0.tid FROM TASK t0 WHERE ((t0.tid = ?) AND (t0.DATE BETWEEN ? AND ?)) UNION (SELECT t1.TASKID, t1.DATE, t1.NAME, t1.REGION, t1.did, t1.pid, t1.tid FROM PEOPLE t2, TASK t1 WHERE ((t2.PID IN (SELECT t3.PID FROM PEOPLETEAM t4, PEOPLE t3 WHERE ((t4.tid = ?) AND (t3.PID = t4.pid))) AND (t1.DATE BETWEEN ? AND ?)) AND (t2.PID = t1.pid))))")
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:684)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:560)
at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2055)
at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:570)
Caused by: 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 (SELECT t1.TASKID, t1.DATE, t1.NAME, t1.REGION, t1.did, t1.pid, t1.tid FRO' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at
2015-11-23T17:09:33.931+0300|Warning: /findteamtask.xhtml @41,62 actionListener="#{fttb.getFilteredTasks()}": javax.ejb.EJBException
javax.el.ELException: /findteamtask.xhtml @41,62 actionListener="#{fttb.getFilteredTasks()}": javax.ejb.EJBException
at com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:111)
Caused by: javax.persistence.PersistenceException: 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 MySQL server version for the right syntax to use near 'UNION (SELECT t1.TASKID, t1.DATE, t1.NAME, t1.REGION, t1.did, t1.pid, t1.tid FRO' at line 1
Error Code: 1064
Call: (SELECT t0.TASKID, t0.DATE, t0.NAME, t0.REGION, t0.did, t0.pid, t0.tid FROM TASK t0 WHERE ((t0.tid = ?) AND (t0.DATE BETWEEN ? AND ?)) UNION (SELECT t1.TASKID, t1.DATE, t1.NAME, t1.REGION, t1.did, t1.pid, t1.tid FROM PEOPLE t2, TASK t1 WHERE ((t2.PID IN (SELECT t3.PID FROM PEOPLETEAM t4, PEOPLE t3 WHERE ((t4.tid = ?) AND (t3.PID = t4.pid))) AND (t1.DATE BETWEEN ? AND ?)) AND (t2.PID = t1.pid))))
bind => [6 parameters bound]
Query: ReadAllQuery(referenceClass=Task sql="(SELECT t0.TASKID, t0.DATE, t0.NAME, t0.REGION, t0.did, t0.pid, t0.tid FROM TASK t0 WHERE ((t0.tid = ?) AND (t0.DATE BETWEEN ? AND ?)) UNION (SELECT t1.TASKID, t1.DATE, t1.NAME, t1.REGION, t1.did, t1.pid, t1.tid FROM PEOPLE t2, TASK t1 WHERE ((t2.PID IN (SELECT t3.PID FROM PEOPLETEAM t4, PEOPLE t3 WHERE ((t4.tid = ?) AND (t3.PID = t4.pid))) AND (t1.DATE BETWEEN ? AND ?)) AND (t2.PID = t1.pid))))")
at org.eclipse.persistence.internal.jpa.QueryImpl.getDetailedException(QueryImpl.java:382)
Caused by: 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 MySQL server version for the right syntax to use near 'UNION (SELECT t1.TASKID, t1.DATE, t1.NAME, t1.REGION, t1.did, t1.pid, t1.tid FRO' at line 1
Error Code: 1064
Call: (SELECT t0.TASKID, t0.DATE, t0.NAME, t0.REGION, t0.did, t0.pid, t0.tid FROM TASK t0 WHERE ((t0.tid = ?) AND (t0.DATE BETWEEN ? AND ?)) UNION (SELECT t1.TASKID, t1.DATE, t1.NAME, t1.REGION, t1.did, t1.pid, t1.tid FROM PEOPLE t2, TASK t1 WHERE ((t2.PID IN (SELECT t3.PID FROM PEOPLETEAM t4, PEOPLE t3 WHERE ((t4.tid = ?) AND (t3.PID = t4.pid))) AND (t1.DATE BETWEEN ? AND ?)) AND (t2.PID = t1.pid))))
bind => [6 parameters bound]
Query: ReadAllQuery(referenceClass=Task sql="(SELECT t0.TASKID, t0.DATE, t0.NAME, t0.REGION, t0.did, t0.pid, t0.tid FROM TASK t0 WHERE ((t0.tid = ?) AND (t0.DATE BETWEEN ? AND ?)) UNION (SELECT t1.TASKID, t1.DATE, t1.NAME, t1.REGION, t1.did, t1.pid, t1.tid FROM PEOPLE t2, TASK t1 WHERE ((t2.PID IN (SELECT t3.PID FROM PEOPLETEAM t4, PEOPLE t3 WHERE ((t4.tid = ?) AND (t3.PID = t4.pid))) AND (t1.DATE BETWEEN ? AND ?)) AND (t2.PID = t1.pid))))")
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340)
i know that each one of those queries as written below work independently, so if i use either of the methods below both of them run and give the desired result
public List<Task> findTeamsTasks(int tid,Date d1, Date d2){
Query q=em.createQuery("SELECT t1 "
+ "FROM Task t1 "
+ "where (t1.team.tid=?1) "
+ "and (t1.date Between ?2 and ?3) "
);
q.setParameter(1, tid);
q.setParameter(2, d1);
q.setParameter(3, d2);
List<Task> ftask=(List<Task>)q.getResultList();
return ftask;
}
public List<Task> findTeamsTasks(int tid,Date d1, Date d2){
Query q=em.createQuery("SELECT t2 "
+ "FROM Task t2 "
+ "where (t2.people.pid IN (SELECT pt.people.pid "
+ "from Peopleteam pt "
+ "where (pt.team.tid=?1))) "
+ "and (t2.date Between ?2 and ?3)");
q.setParameter(1, tid);
q.setParameter(2, d1);
q.setParameter(3, d2);
List<Task> ftask=(List<Task>)q.getResultList();
return ftask;
}
tried this simple query, still gives me the same exception
public List<Task> findTeamsTasks(int tid,Date d1, Date d2){
Query q=em.createQuery("SELECT t1 "
+ "FROM Task t1 "
+ "where (t1.team.tid=?1) "
+ "and (t1.date Between ?2 and ?3) "
+ "UNION "
+ "SELECT t2 "
+ "FROM Task t2 "
+ "where (t1.team.tid=?4) "
+ "and (t2.date Between ?2 and ?3) ");
q.setParameter(1, tid);
q.setParameter(2, d1);
q.setParameter(3, d2);
q.setParameter(4, 2);
List<Task> ftask=(List<Task>)q.getResultList();
return ftask;
}
exception
Caused by: 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 MySQL server version for the right syntax to use near 'UNION (SELECT t1.TASKID, t1.DATE, t1.NAME, t1.REGION, t1.did, t1.pid, t1.tid FRO' at line 1
Error Code: 1064
Call: (SELECT t0.TASKID, t0.DATE, t0.NAME, t0.REGION, t0.did, t0.pid, t0.tid FROM TASK t0 WHERE ((t0.tid = ?) AND (t0.DATE BETWEEN ? AND ?)) UNION (SELECT t1.TASKID, t1.DATE, t1.NAME, t1.REGION, t1.did, t1.pid, t1.tid FROM TEAM t2, TASK t1 WHERE (((t2.TID = ?) AND (t1.DATE BETWEEN ? AND ?)) AND (t2.TID = t0.tid))))
bind => [6 parameters bound]
Query: ReadAllQuery(referenceClass=Task sql="(SELECT t0.TASKID, t0.DATE, t0.NAME, t0.REGION, t0.did, t0.pid, t0.tid FROM TASK t0 WHERE ((t0.tid = ?) AND (t0.DATE BETWEEN ? AND ?)) UNION (SELECT t1.TASKID, t1.DATE, t1.NAME, t1.REGION, t1.did, t1.pid, t1.tid FROM TEAM t2, TASK t1 WHERE (((t2.TID = ?) AND (t1.DATE BETWEEN ? AND ?)) AND (t2.TID = t0.tid))))")
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:684)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:560)
at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2055)
at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:570)
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:2740)
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:2693)
at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:541)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1173)
at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:904)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1132)
at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:442)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1220)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2896)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1857)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1839)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1804)
at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:258)
... 96 more
Caused by: 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 (SELECT t1.TASKID, t1.DATE, t1.NAME, t1.REGION, t1.did, t1.pid, t1.tid FRO' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2617)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2825)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2156)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2313)
at com.sun.gjc.spi.jdbc40.PreparedStatementWrapper40.executeQuery(PreparedStatementWrapper40.java:642)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeSelect(DatabaseAccessor.java:1009)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:644)
... 116 more
i am fairly certain that i am using the syntax of union correctly and both the queries are correct. i am not sure what is causing the exception.
running the query in mysql shown in the exception works if i remove the brackets around the select statements and insert the values
SELECT t0.TASKID, t0.DATE, t0.NAME, t0.REGION, t0.did, t0.pid, t0.tid FROM TASK t0
WHERE ((t0.tid = 6) AND (t0.DATE BETWEEN '2014-11-01' AND '2015-11-30'))
UNION SELECT t1.TASKID, t1.DATE, t1.NAME, t1.REGION, t1.did, t1.pid, t1.tid FROM PEOPLE t2, TASK t1 WHERE ((t2.PID IN
(SELECT t3.PID FROM PEOPLETEAM t4, PEOPLE t3 WHERE ((t4.tid = 6) AND (t3.PID = t4.pid))) AND (t1.DATE BETWEEN '2014-11-01' AND '2015-11-30')) AND (t2.PID = t1.pid));
query shown in the exception
(SELECT t0.TASKID, t0.DATE, t0.NAME, t0.REGION, t0.did, t0.pid, t0.tid FROM TASK t0
WHERE ((t0.tid = ?) AND (t0.DATE BETWEEN ? AND ?))
UNION (SELECT t1.TASKID, t1.DATE, t1.NAME, t1.REGION, t1.did, t1.pid, t1.tid FROM PEOPLE t2, TASK t1 WHERE ((t2.PID IN
(SELECT t3.PID FROM PEOPLETEAM t4, PEOPLE t3
WHERE ((t4.tid = ?) AND (t3.PID = t4.pid))) AND (t1.DATE BETWEEN ? AND ?)) AND (t2.PID = t1.pid))))
so cant figure the exception out from there either
Even updated my eclipselink from 2.6,0 to 2.6.1 still the same
Sorry but this is unsupported EclipseLink version. Please try 2.7.13 (javax) or 4.0.2 (Jakarta) version.
@rfelcman I also tried with 4.0.3 (Jakarta) and MySQL 5.7 and still get the same error