Spring-Data JPA adding extra parens which postgreSQL doesn't like

[dnc253]

We are using Spring-Data JPA 1.1.0 with EclipseLink and PostgreSQL Database. When 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? Is this an issue with EclipseLink and not Spring-Data? It seems like a pretty simple case. Thanks.

[dnc253]

I debugged through the Spring-Data code some, and it seems like this is definitely an issue with Spring-Data. Everywhere where the args for the query is referenced, it is an Object array that has just one entry that is the List of ids. It seems to me like the Object array should have an entry for each one of the ids. This is all I am doing in the code:

List<Long> ids = new ArrayList<Long>();
ids.add(1L);
ids.add(2L);
ids.add(3L);
userRepository.findAll(ids);

See my following replies for what is in the logs (The stacktrace is too long for me to post in this reply)

[dnc253]

[EL Fine]: 2012-06-13 22:44:27.379--ServerSession(187247026)--Connection(780877208)--Thread(Thread[http-8080-2,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-13 22:44:27.396--ServerSession(187247026)--Thread(Thread[http-8080-2,5,main])--SELECT 1
[EL Warning]: 2012-06-13 22:44:27.402--UnitOfWork(930668039)--Thread(Thread[http-8080-2,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 (?))")

[dnc253]

Local Exception Stack: 
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 (?))")
	at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:644)
	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:84)
	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.prometheus.torchlms.security.UserDetailsServiceImpl.loadUserByUsername(UserDetailsServiceImpl.java:46)
	at org.springframework.security.web.authentication.rememberme.TokenBasedRememberMeServices.processAutoLoginCookie(TokenBasedRememberMeServices.java:125)
	at com.prometheus.torchlms.security.StatelessAuthenticationService.processAutoLoginCookie(StatelessAuthenticationService.java:32)
	at org.springframework.security.web.authentication.rememberme.AbstractRememberMeServices.autoLogin(AbstractRememberMeServices.java:115)
	at org.springframework.security.web.authentication.rememberme.RememberMeAuthenticationFilter.doFilter(RememberMeAuthenticationFilter.java:97)
	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.prometheus.torchlms.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)
Caused by: 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)
	... 72 more

[pfurbacher]

I (initially encountered a similar error using EclipseLink 2.3 and MySQL 5. The extra parens around the parameter list makes MySQL choke. When I switched to PostgreSQL 9.2, I encountered the error cited above in this thread.

Since then, I have been tracking down this issue and finally found a bug report by Oliver Gierke, dated June 15, 2011(!):

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

Note that the problem does not exist with Hibernate JPA.

If you are still around, perhaps you might consider casting a vote for the nearly 2 year-old bug.