[SYNCOPE-1308] Exception getting users with orderBy on SyncopeClient API with Postgres 10.3

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.0.8
• Fix Version/s: 2.0.9, 2.1.0
• Component/s: core
• Environment: Windows Server 2012 R2 64 ; Java 8 ; Tomcat 8 ; Syncope 2.0.8 ; JDBC Driver 42.2.2 ; Postgres 10.3
• Assignee: Francesco Chicchiriccò
• Reporter: Paul Fullbright
• Created: 03/May/18 15:00
• Updated: 27/Jun/18 13:17
• Resolved: 07/May/18 15:24

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)