Uploaded image for project: 'Syncope'
  1. Syncope
  2. SYNCOPE-1308

Exception getting users with orderBy on SyncopeClient API with Postgres 10.3

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 2.0.8
    • 2.0.9, 2.1.0
    • core
    • None
    • Windows Server 2012 R2 64
      Java 8
      Tomcat 8
      Syncope 2.0.8
      JDBC Driver 42.2.2
      Postgres 10.3

    Description

      To reproduce, I used the following code:

          UserService service = client.getService(UserService.class); 
          AnyQuery query = new AnyQuery.Builder() 
              .page(1) 
              .size(1) 
              .orderBy("email ASC") 
              .realm("/") 
              .build(); 
                      
          return service.search(query);

      And then I received the following exception stack:

      org.apache.syncope.common.lib.SyncopeClientException: DataIntegrityViolation 
      [ERROR: UNION types integer and text cannot be matched 
         Position: 158 {prepstmnt 1156037144 SELECT u.any_id,sva.stringvalue AS 
      email FROM (SELECT DISTINCT any_id FROM user_search WHERE id IS NOT NULL) u, 
      (SELECT * FROM user_search_attr UNION SELECT * FROM user_search_null_attr) 
      sva WHERE u.any_id=sva.any_id AND sva.schema_id='email' AND u.any_id IN 
      (SELECT any_id FROM user_search WHERE realm_id IN (SELECT id AS realm_id 
      FROM Realm WHERE id=?)) ORDER BY email ASC} [code=0, state=42804]
          at 
      org.apache.syncope.common.lib.SyncopeClientException.build(SyncopeClientException.java:37) 
          at 
      org.apache.syncope.client.lib.RestClientExceptionMapper.checkSyncopeClientCompositeException(RestClientExceptionMapper.java:143) 
          at 
      org.apache.syncope.client.lib.RestClientExceptionMapper.fromResponse(RestClientExceptionMapper.java:53) 
          at 
      org.apache.syncope.client.lib.RestClientExceptionMapper.fromResponse(RestClientExceptionMapper.java:42) 
          at 
      org.apache.cxf.jaxrs.client.ClientProxyImpl.checkResponse(ClientProxyImpl.java:313) 
          at 
      org.apache.cxf.jaxrs.client.ClientProxyImpl.handleResponse(ClientProxyImpl.java:876) 
          at 
      org.apache.cxf.jaxrs.client.ClientProxyImpl.doChainedInvocation(ClientProxyImpl.java:789) 
          at 
      org.apache.cxf.jaxrs.client.ClientProxyImpl.invoke(ClientProxyImpl.java:235) 
          at com.sun.proxy.$Proxy64.search(Unknown Source) 
          at com.gal.ums.services.UsersService.getUsers(UsersService.java:65) 

       

      Attachments

        Activity

          Commit 70aa8425b0d48e08f271b380b9e40a17043640d9 in syncope's branch refs/heads/2_0_X from ilgrosso
          [ https://git-wip-us.apache.org/repos/asf?p=syncope.git;h=70aa842 ]

          SYNCOPE-1308 Now working fine with latest PostgreSQL (needs dedicated views.xml)

          jira-bot ASF subversion and git services added a comment - Commit 70aa8425b0d48e08f271b380b9e40a17043640d9 in syncope's branch refs/heads/2_0_X from ilgrosso [ https://git-wip-us.apache.org/repos/asf?p=syncope.git;h=70aa842 ] SYNCOPE-1308 Now working fine with latest PostgreSQL (needs dedicated views.xml)

          Commit 7cdeb5fa8aaae5e39e2453e7bd9fe057afc886b4 in syncope's branch refs/heads/master from ilgrosso
          [ https://git-wip-us.apache.org/repos/asf?p=syncope.git;h=7cdeb5f ]

          SYNCOPE-1308 Now working fine with latest PostgreSQL (needs dedicated views.xml)

          jira-bot ASF subversion and git services added a comment - Commit 7cdeb5fa8aaae5e39e2453e7bd9fe057afc886b4 in syncope's branch refs/heads/master from ilgrosso [ https://git-wip-us.apache.org/repos/asf?p=syncope.git;h=7cdeb5f ] SYNCOPE-1308 Now working fine with latest PostgreSQL (needs dedicated views.xml)

          PeeDub the fix is about using a customized way to build some SQL views: I have updated the reference guide (see https://ci.apache.org/projects/syncope/2_0_X/reference-guide.html#postgresql) about how to accomplish this, and set the deb distribution to work this way.

          For existing deployments, you'll need first to drop the existing user_search_null_attr, group_search_null_attr and anyObject_search_null_attr views then re-create with statements from https://github.com/apache/syncope/blob/2_0_X/fit/core-reference/src/main/resources/postgres/views.xml

          Please note that the fix is also compatible with PostgreSQL 9.6.

          ilgrosso Francesco Chicchiriccò added a comment - PeeDub the fix is about using a customized way to build some SQL views: I have updated the reference guide (see https://ci.apache.org/projects/syncope/2_0_X/reference-guide.html#postgresql) about how to accomplish this, and set the deb distribution to work this way. For existing deployments, you'll need first to drop the existing user_search_null_attr , group_search_null_attr and anyObject_search_null_attr views then re-create with statements from https://github.com/apache/syncope/blob/2_0_X/fit/core-reference/src/main/resources/postgres/views.xml Please note that the fix is also compatible with PostgreSQL 9.6.

          Bulk close for 2.0.9

          ilgrosso Francesco Chicchiriccò added a comment - Bulk close for 2.0.9

          People

            ilgrosso Francesco Chicchiriccò
            PeeDub Paul Fullbright
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: