CAUSE:
The OrganizationService.getUserHandler().findUsersByGroupId(groupid) call does not work with some databases (like default Hypersonic 2 database and PostgreSQL9). Some other certified databases like MySQL worked.
CONSEQUENCE: Consequence to JPP is not so big because this operation is not called from any place in JPP UI. However people may need to create portlets and call this operation from their portlets, which didn't work.
FIX: The error was fixed in Picketlink IDM library. It was caused by the fact that Hibernate HQL query generated SQL query to database, which wasn't compatible with some databases and so SQL call failed for some databases. The fix changed HQL query to the form, that generated SQL query is compatible with all databases
RESULT: Fix was added to Picketlink IDM library into version 1.4.2.CR01. So once JPP is changed to use this (or newer) version, the bug can't be reproduced anymore and it's possible to correctly invoke operation OrganizationService.getUserHandler().findUsersByGroupId(groupid)
Description of problem:
Using default setup with H2 database, calling OrganizationService.getUserHandler().findUsersByGroupId(groupid) raises an exception.
Executing the following code:
30 users = organizationService.getUserHandler().findUsersByGroupId("/platform/users");
31
32 if (users.getSize() > 0)
33 {
results in the following exception:
11:01:30,352 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http-/127.0.0.1:8080-2) SQL Error: 90068, SQLState: 90068
11:01:30,352 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http-/127.0.0.1:8080-2) Order by expression "HIBERNATEI2_.NAME" must be in the result list in this case; SQL statement:
select distinct hibernatei1_.ID as ID4_, hibernatei1_.IDENTITY_TYPE as IDENTITY2_4_, hibernatei1_.NAME as NAME4_, hibernatei1_.REALM as REALM4_ from jbid_io_rel hibernatei0_ inner join jbid_io hibernatei1_ on hibernatei0_.TO_IDENTITY=hibernatei1_.ID cross join jbid_io hibernatei2_ cross join jbid_io hibernatei3_ where hibernatei0_.TO_IDENTITY=hibernatei2_.ID and hibernatei0_.FROM_IDENTITY=hibernatei3_.ID and hibernatei2_.REALM=? and hibernatei3_.REALM=? and (hibernatei2_.NAME like ?) and hibernatei0_.FROM_IDENTITY=? order by hibernatei2_.NAME asc [90068-168]
11:01:30,355 ERROR [stderr] (http-/127.0.0.1:8080-2) org.picketlink.idm.api.query.QueryException: Failed to execute query
11:01:30,356 ERROR [stderr] (http-/127.0.0.1:8080-2) at org.picketlink.idm.impl.api.query.UserQueryExecutorImpl.execute(UserQueryExecutorImpl.java:220)
11:01:30,356 ERROR [stderr] (http-/127.0.0.1:8080-2) at org.picketlink.idm.impl.api.query.UserQueryExecutorImpl.list(UserQueryExecutorImpl.java:245)
11:01:30,356 ERROR [stderr] (http-/127.0.0.1:8080-2) at org.picketlink.idm.impl.api.session.IdentitySessionImpl.list(IdentitySessionImpl.java:364)
11:01:30,356 ERROR [stderr] (http-/127.0.0.1:8080-2) at org.exoplatform.services.organization.idm.IDMUserListAccess.getSize(IDMUserListAccess.java:123)
11:01:30,356 ERROR [stderr] (http-/127.0.0.1:8080-2) at org.exoplatform.demo.portlet.portletOrganization.doView(portletOrganization.java:32)
The same issue does not show up with other databases, such as MySQL.
Description of problem: Using default setup with H2 database, calling OrganizationService.getUserHandler().findUsersByGroupId(groupid) raises an exception. Executing the following code: 30 users = organizationService.getUserHandler().findUsersByGroupId("/platform/users"); 31 32 if (users.getSize() > 0) 33 { results in the following exception: 11:01:30,352 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http-/127.0.0.1:8080-2) SQL Error: 90068, SQLState: 90068 11:01:30,352 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http-/127.0.0.1:8080-2) Order by expression "HIBERNATEI2_.NAME" must be in the result list in this case; SQL statement: select distinct hibernatei1_.ID as ID4_, hibernatei1_.IDENTITY_TYPE as IDENTITY2_4_, hibernatei1_.NAME as NAME4_, hibernatei1_.REALM as REALM4_ from jbid_io_rel hibernatei0_ inner join jbid_io hibernatei1_ on hibernatei0_.TO_IDENTITY=hibernatei1_.ID cross join jbid_io hibernatei2_ cross join jbid_io hibernatei3_ where hibernatei0_.TO_IDENTITY=hibernatei2_.ID and hibernatei0_.FROM_IDENTITY=hibernatei3_.ID and hibernatei2_.REALM=? and hibernatei3_.REALM=? and (hibernatei2_.NAME like ?) and hibernatei0_.FROM_IDENTITY=? order by hibernatei2_.NAME asc [90068-168] 11:01:30,355 ERROR [stderr] (http-/127.0.0.1:8080-2) org.picketlink.idm.api.query.QueryException: Failed to execute query 11:01:30,356 ERROR [stderr] (http-/127.0.0.1:8080-2) at org.picketlink.idm.impl.api.query.UserQueryExecutorImpl.execute(UserQueryExecutorImpl.java:220) 11:01:30,356 ERROR [stderr] (http-/127.0.0.1:8080-2) at org.picketlink.idm.impl.api.query.UserQueryExecutorImpl.list(UserQueryExecutorImpl.java:245) 11:01:30,356 ERROR [stderr] (http-/127.0.0.1:8080-2) at org.picketlink.idm.impl.api.session.IdentitySessionImpl.list(IdentitySessionImpl.java:364) 11:01:30,356 ERROR [stderr] (http-/127.0.0.1:8080-2) at org.exoplatform.services.organization.idm.IDMUserListAccess.getSize(IDMUserListAccess.java:123) 11:01:30,356 ERROR [stderr] (http-/127.0.0.1:8080-2) at org.exoplatform.demo.portlet.portletOrganization.doView(portletOrganization.java:32) The same issue does not show up with other databases, such as MySQL.