Announcement Announcement Module
Collapse
No announcement yet.
Spring-Data JPA adding extra parens which postgreSQL doesn't like Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

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

    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:
    Code:
    [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.

  • #2
    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:
    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)

    Comment


    • #3
      Code:
      [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 (?))")

      Comment


      • #4
        Code:
        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

        Comment


        • #5
          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.

          Comment

          Working...
          X