search

eclipse-ee4j / eclipselink

Eclipselink project
https://eclipse.dev/eclipselink/
Other
197 stars 168 forks source link

JPQL query with UNION not working using eclipselink causing MySQLSyntaxErrorException #1931

Open adossas-spdrm opened 1 year ago

adossas-spdrm commented 1 year ago

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

rfelcman commented 9 months ago

Sorry but this is unsupported EclipseLink version. Please try 2.7.13 (javax) or 4.0.2 (Jakarta) version.

adossas-spdrm commented 9 months ago

I can reproduce it with version 2.7.13 (javax) and MySQL 5.7

adossas-spdrm commented 1 month ago

@rfelcman I also tried with 4.0.3 (Jakarta) and MySQL 5.7 and still get the same error