Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » EclipseLink adding extra parens which postgreSQL doesn't like
EclipseLink adding extra parens which postgreSQL doesn't like [message #880815] Sun, 03 June 2012 03:38 Go to next message
Dan C is currently offline Dan C
Messages: 3
Registered: June 2012
Junior Member
We are using EclipseLink with a PostgreSQL Database. We are also using Spring-Data JPA to create our repositories for us. I'm not sure if the problem we're having is because of Spring-Data or EclipseLink. I do a simple findAll(Iterable<ID> ids) on a PagingAndSortingRepository, it's adding an extra set of parens which causes an error in PostgreSQL

The logs look like this:
[EL Fine]: 2012-06-02 18:43:36.911--ServerSession(794229024)--Connection(467920069)--Thread(Thread[http-8080-1,5,main])--SELECT ID, CREATED, FIRST_NAME, last_access, LAST_NAME, PASSWORD, USERNAME FROM USERS WHERE (ID IN ((?,?,?)))
	bind => [1, 2, 3]
[EL Fine]: 2012-06-02 18:43:36.949--ServerSession(794229024)--Thread(Thread[http-8080-1,5,main])--SELECT 1
[EL Warning]: 2012-06-02 18:43:36.953--UnitOfWork(1234424909)--Thread(Thread[http-8080-1,5,main])--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: arguments of row IN must all be row expressions
  Position: 97
Error Code: 0
Call: SELECT ID, CREATED, FIRST_NAME, LAST_NAME, PASSWORD, USERNAME FROM USERS WHERE (ID IN ((?,?,?)))
	bind => [1, 2, 3]


In my SQL editor, if I take of the extra set of parens after IN, it works just fine. Anyone know what's going on here? It seems like a pretty simple case. Thanks.
Re: EclipseLink adding extra parens which postgreSQL doesn't like [message #881943 is a reply to message #880815] Tue, 05 June 2012 14:24 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

Odd. What is the code for the query? What version are you using?
Check your parameter, are you adding a List within a List?


James : Wiki : Book : Blog : Twitter
Re: EclipseLink adding extra parens which postgreSQL doesn't like [message #882258 is a reply to message #881943] Wed, 06 June 2012 06:15 Go to previous messageGo to next message
Dan C is currently offline Dan C
Messages: 3
Registered: June 2012
Junior Member
This is with version 2.3.2. I also tried it with snaphots for 2.3.3 and 2.4.0 and got the same result. As far as the code for the query goes, I am using Spring-Data JPA (It won't let me post a link to it, a Google search should find it), which is actually generating all the code. It's possible that this problem lies within the Spring-Data code. Here are more logs around this issue so maybe you can definitively determine where the problem lies.
DEBUG: org.springframework.data.repository.core.support.TransactionalRepositoryProxyPostProcessor$CustomAnnotationTransactionAttributeSource - Adding transactional method 'findAll' with attribute: PROPAGATION_REQUIRED,ISOLATION_DEFAULT,readOnly; ''
[EL Fine]: 2012-06-06 00:03:06.611--ServerSession(546069071)--Connection(869161153)--Thread(Thread[http-8080-1,5,main])--SELECT ID, CREATED, FIRST_NAME, last_access, LAST_NAME, PASSWORD, USERNAME FROM USERS WHERE (ID IN ((?,?,?)))
	bind => [1, 2, 3]
[EL Fine]: 2012-06-06 00:03:06.688--ServerSession(546069071)--Thread(Thread[http-8080-1,5,main])--SELECT 1
[EL Warning]: 2012-06-06 00:03:06.692--UnitOfWork(1822923370)--Thread(Thread[http-8080-1,5,main])--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: arguments of row IN must all be row expressions
  Position: 97
Error Code: 0
Call: SELECT ID, CREATED, FIRST_NAME, last_access, LAST_NAME, PASSWORD, USERNAME FROM USERS WHERE (ID IN ((?,?,?)))
	bind => [1, 2, 3]
Query: ReadAllQuery(referenceClass=User sql="SELECT ID, CREATED, FIRST_NAME, last_access, LAST_NAME, PASSWORD, USERNAME FROM USERS WHERE (ID IN (?))")
Jun 6, 2012 12:04:15 AM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet appServlet threw exception
org.postgresql.util.PSQLException: ERROR: arguments of row IN must all be row expressions
  Position: 97
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeSelect(DatabaseAccessor.java:931)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:607)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:535)
	at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:1717)
	at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:566)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:207)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:193)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:264)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:646)
	at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2611)
	at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:2570)
	at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:420)
	at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1081)
	at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:844)
	at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1040)
	at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:392)
	at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1128)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2871)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1516)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1498)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1463)
	at org.eclipse.persistence.internal.jpa.EJBQueryImpl.executeReadQuery(EJBQueryImpl.java:485)
	at org.eclipse.persistence.internal.jpa.EJBQueryImpl.getResultList(EJBQueryImpl.java:742)
	at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll(SimpleJpaRepository.java:247)
	at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll(SimpleJpaRepository.java:55)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.executeMethodOn(RepositoryFactorySupport.java:334)
	at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:319)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:155)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	at org.springframework.data.jpa.repository.support.LockModeRepositoryPostProcessor$LockModePopulatingMethodIntercceptor.invoke(LockModeRepositoryPostProcessor.java:91)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:90)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
	at $Proxy18.findAll(Unknown Source)
	at com.my.app.web.RESTController.getEntities(RESTController.java:49)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:212)
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:126)
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:96)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:617)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:578)
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:900)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:827)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
	at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:778)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:311)
	at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:116)
	at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
	at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
	at org.springframework.security.web.authentication.rememberme.RememberMeAuthenticationFilter.doFilter(RememberMeAuthenticationFilter.java:139)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
	at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
	at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:182)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
	at com.my.app.security.SSOFilter.doFilterInternal(SSOFilter.java:56)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
	at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
	at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
	at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:173)
	at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
	at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:259)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:859)
	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:602)
	at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
	at java.lang.Thread.run(Thread.java:680)


Thanks.
Re: EclipseLink adding extra parens which postgreSQL doesn't like [message #885152 is a reply to message #882258] Tue, 12 June 2012 14:39 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

Include the generated code for the query. Is it passing a nested collection? Put in a break-point and check.


James : Wiki : Book : Blog : Twitter
Re: EclipseLink adding extra parens which postgreSQL doesn't like [message #886028 is a reply to message #885152] Thu, 14 June 2012 04:32 Go to previous messageGo to next message
Dan C is currently offline Dan C
Messages: 3
Registered: June 2012
Junior Member
Well, I debugged through the Spring-Data code and it looks like the issue is on their side. It's a little hard to follow the code through all the AOP proxy stuff, but it's passing around an Object array for the args, that I can only assume is what gets bound in the query. That Object has one entry that is the List of ids, where it probably should have an entry for each of the ids. I guess it's time to post in their forum. Smile
Re: EclipseLink adding extra parens which postgreSQL doesn't like [message #1021089 is a reply to message #886028] Tue, 19 March 2013 14:03 Go to previous message
Paul Furbacher is currently offline Paul Furbacher
Messages: 3
Registered: July 2009
Junior Member
Essentially, this issue, which I also encountered with EclipseLink 2.3.x and 2.4.x and MySQL 5.x using Spring Data JPA's "findByXxxxIn()" query, has been logged in Eclipse's bug reporter as:

Using criteria.in(...) with ParameterExpression of type Collection creates invalid SQL

on June 15, 2011(!) by Spring Data's Oliver Gierke. It's the same symptom of introducing an extra set of parens around the parameter list.

Note that Hibernate JPA does not exhibit this problem.
Previous Topic:@JoinFetch doesn't work with mappings on Embeddables
Next Topic:@XmlInverseReference with interface field
Goto Forum:
  


Current Time: Thu Oct 23 12:01:52 GMT 2014

Powered by FUDForum. Page generated in 0.02155 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software