[
https://issues.apache.org/jira/browse/SYNCOPE-1308?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16466034#comment-16466034
]
Francesco Chicchiriccò commented on SYNCOPE-1308:
-------------------------------------------------
[~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.
> Exception getting users with orderBy on SyncopeClient API with Postgres 10.3
> ----------------------------------------------------------------------------
>
> Key: SYNCOPE-1308
> URL: https://issues.apache.org/jira/browse/SYNCOPE-1308
> Project: Syncope
> Issue Type: Bug
> Components: core
> Affects Versions: 2.0.8
> Environment: Windows Server 2012 R2 64
> Java 8
> Tomcat 8
> Syncope 2.0.8
> JDBC Driver 42.2.2
> Postgres 10.3
> Reporter: Paul Fullbright
> Assignee: Francesco Chicchiriccò
> Priority: Major
> Fix For: 2.0.9, 2.1.0
>
>
> 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) }}
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
