problem with "Browse Folders" in New UI

From: grouper-users-request On Behalf Of Yoann Delattre
Sent: Wednesday, September 24, 2014 10:58 AM
To: grouper-users
Subject: [grouper-users] problem with "Browse Folders" in New UI

Hi everyone,

just finish to debug a problem with the "Browse Folders" tree in New UI.

With a non-wheel member, in our environment, It's not working :

It work with a wheel member.

Stack trace show an SQL error :

2014-09-24 14:02:28,619: [main] ERROR ShellHelper.eval(91) - - Sourced file: inline evaluation of: ``Set childrenAttributeDefs = new AttributeDefFinder().assignQueryOptions(QueryOpt . . . '' : Typed variable declaration : Method Invocation findAttributes
edu.internet2.middleware.grouper.internal.dao.GrouperDAOException: Problem in HibernateSession: HibernateSession (a2576c6): new, readonly, READONLY_NEW, notActiveTransaction, session (665b6637),
Exception in list: (class edu.internet2.middleware.grouper.attr.AttributeDef), ByHqlStatic, query: 'select distinct theAttributeDef from AttributeDef theAttributeDef , MembershipEntry __attrDefMembershipSAQWQAML where theAttributeDef.stemId = :theStemId and __attrDefMembershipSAQWQAML.ownerAttrDefId = theAttributeDef.id and __attrDefMembershipSAQWQAML.fieldId in (:SAQWQAMK0, :SAQWQAMK1, :SAQWQAMK2, :SAQWQAMK3, :SAQWQAMK4, :SAQWQAMK5, :SAQWQAMK6, :SAQWQAMK7) and __attrDefMembershipSAQWQAML.memberUuid in (:SAQWQAMM0, :SAQWQAMM1) and __attrDefMembershipSAQWQAML.enabledDb = 'T'', cacheable: false, cacheRegion: edu.internet2.middleware.grouper.internal.dao.hib3.Hib3AttributeDefDAO.GetAllAttributeDefsSecure, tx type: null, options: QueryOptions: queryPaging: pageSize: 10, pageNumberOnIndexed: 1, querySort: extension asc, , tx type: nullBind var[0]: 'Param (class java.lang.String): 'theStemId'>'499ac31bdb564efda48513c106bef297', Bind var[1]: 'Param (class java.lang.String): 'SAQWQAMK0'>'84ab48012d1e47ae9d43623ba2df94e1', Bind var[2]: 'Param (class java.lang.String): 'SAQWQAMK1'>'7f25cb95fd2640f4b99fb40e76d222d5', Bind var[3]: 'Param (class java.lang.String): 'SAQWQAMK2'>'2793877ffc94407190cacde3466bb932', Bind var[4]: 'Param (class java.lang.String): 'SAQWQAMK3'>'0aa4432a8e0742ec96b2a30da1ecb487', Bind var[5]: 'Param (class java.lang.String): 'SAQWQAMK4'>'e078ddef342943369fb5236d6124c990', Bind var[6]: 'Param (class java.lang.String): 'SAQWQAMK5'>'6317e8811df448829c895b1f7f4bf5e0', Bind var[7]: 'Param (class java.lang.String): 'SAQWQAMK6'>'3975409abeb149bbb67f12711a43197b', Bind var[8]: 'Param (class java.lang.String): 'SAQWQAMK7'>'fe688f3f40554e559cdd2c964ef96064', Bind var[9]: 'Param (class java.lang.String): 'SAQWQAMM0'>'08cabad6a3cc481ba3acb6c118a6405a'Bind var[10]: 'Param (class java.lang.String): 'SAQWQAMM1'->'8ce875524d7f450ba0a9915ec145d6ee',
at edu.internet2.middleware.grouper.hibernate.HibernateSession._internal_hibernateSessionCatch(HibernateSession.java:542)
at edu.internet2.middleware.grouper.hibernate.HibernateSession.callbackHibernateSession(HibernateSession.java:664)
at edu.internet2.middleware.grouper.hibernate.ByHqlStatic.list(ByHqlStatic.java:372)
at edu.internet2.middleware.grouper.hibernate.ByHqlStatic.listSet(ByHqlStatic.java:421)
at edu.internet2.middleware.grouper.internal.dao.hib3.Hib3AttributeDefDAO.getAllAttributeDefsSecureHelper(Hib3AttributeDefDAO.java:533)
at edu.internet2.middleware.grouper.internal.dao.hib3.Hib3AttributeDefDAO.findAllAttributeDefsSecure(Hib3AttributeDefDAO.java:778)
at edu.internet2.middleware.grouper.attr.finder.AttributeDefFinder.findAttributes(AttributeDefFinder.java:263)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:622)
at bsh.Reflect.invokeMethod(Unknown Source)
at bsh.Reflect.invokeObjectMethod(Unknown Source)
at bsh.BSHPrimarySuffix.doName(Unknown Source)
at bsh.BSHPrimarySuffix.doSuffix(Unknown Source)
at bsh.BSHPrimaryExpression.eval(Unknown Source)
at bsh.BSHPrimaryExpression.eval(Unknown Source)
at bsh.BSHVariableDeclarator.eval(Unknown Source)
at bsh.BSHTypedVariableDeclaration.eval(Unknown Source)
at bsh.Interpreter.eval(Unknown Source)
at bsh.Interpreter.eval(Unknown Source)
at bsh.Interpreter.eval(Unknown Source)
at edu.internet2.middleware.grouper.app.gsh.ShellHelper.eval(ShellHelper.java:63)
at edu.internet2.middleware.grouper.app.gsh.GrouperShell.run(GrouperShell.java:429)
at edu.internet2.middleware.grouper.app.gsh.GrouperShell.grouperShellHelper(GrouperShell.java:232)
at edu.internet2.middleware.grouper.app.gsh.GrouperShell.main(GrouperShell.java:162)
at edu.internet2.middleware.grouper.app.gsh.GrouperShellWrapper.main(GrouperShellWrapper.java:31)
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2536)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
at org.hibernate.loader.Loader.list(Loader.java:2271)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:452)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1268)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
at edu.internet2.middleware.grouper.hibernate.ByHql.list(ByHql.java:349)
at edu.internet2.middleware.grouper.hibernate.ByHqlStatic$2.callback(ByHqlStatic.java:382)
at edu.internet2.middleware.grouper.hibernate.HibernateSession.callbackHibernateSession(HibernateSession.java:654)
... 25 more
Caused by: org.postgresql.util.PSQLException: ERREUR: pour SELECT DISTINCT, ORDER BY, les expressions doivent apparaître dans la
liste SELECT
Position : 1623
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:559)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1953)
at org.hibernate.loader.Loader.doQuery(Loader.java:802)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
at org.hibernate.loader.Loader.doList(Loader.java:2533)
... 35 more

With this SQL request :

select
distinct attributed0_.id as id5_,
attributed0_.hibernate_version_number as hibernate2_5_,
attributed0_.assign_to_attribute_def as assign3_5_,
attributed0_.assign_to_attribute_def_assn as assign4_5_,
attributed0_.assign_to_eff_membership as assign5_5_,
attributed0_.assign_to_eff_membership_assn as assign6_5_,
attributed0_.assign_to_group as assign7_5_,
attributed0_.assign_to_group_assn as assign8_5_,
attributed0_.assign_to_imm_membership as assign9_5_,
attributed0_.assign_to_imm_membership_assn as assign10_5_,
attributed0_.assign_to_member as assign11_5_,
attributed0_.assign_to_member_assn as assign12_5_,
attributed0_.assign_to_stem as assign13_5_,
attributed0_.assign_to_stem_assn as assign14_5_,
attributed0_.attribute_def_public as attribute15_5_,
attributed0_.attribute_def_type as attribute16_5_,
attributed0_.context_id as context17_5_,
attributed0_.created_on as created18_5_,
attributed0_.creator_id as creator19_5_,
attributed0_.description as descrip20_5_,
attributed0_.extension as extension5_,
attributed0_.last_updated as last22_5_,
attributed0_.multi_assignable as multi23_5_,
attributed0_.multi_valued as multi24_5_,
attributed0_.name as name5_,
attributed0_.stem_id as stem26_5_,
attributed0_.value_type as value27_5_,
attributed0_.id_index as id28_5_
from
grouper_attribute_def attributed0_ cross
join
grouper_memberships_all_v membership1_
where
attributed0_.stem_id=?
and membership1_.owner_attr_def_id=attributed0_.id
and (
membership1_.field_id in (
? , ? , ? , ? , ? , ? , ? , ?
)
)
and (
membership1_.member_id in (
? , ?
)
)
and membership1_.immediate_mship_enabled='T'
order by
extension asc limit ?

The problem is related with PostgreSQL.
PostgreSQL rise an exception with this part of the sql request :

order by
extension asc limit ?

PostgreSQL prefer this syntax :

order by
attributed0_. asc limit

So temporarily, we made one modification on UiV2Main.java in folderMenu class :

Set<AttributeDef> childrenAttributeDefs = new AttributeDefFinder()
.assignQueryOptions(QueryOptions.create("extension", true, 1, 10))
.assignPrivileges(AttributeDefPrivilege.ATTR_VIEW_PRIVILEGES)
.assignSubject(GrouperSession.staticGrouperSession().getSubject())
.assignParentStemId(stem.getId()).assignStemScope(Scope.ONE).findAttributes();

to

Set<AttributeDef> childrenAttributeDefs = new AttributeDefFinder()
.assignQueryOptions(QueryOptions.create("theAttributeDef.extensionDb", true, 1, 10))
.assignPrivileges(AttributeDefPrivilege.ATTR_VIEW_PRIVILEGES)
.assignSubject(GrouperSession.staticGrouperSession().getSubject())
.assignParentStemId(stem.getId()).assignStemScope(Scope.ONE).findAttributes();

And it works now

But i don't know if it's the best solution. i'm not an java expert :-/

Could you take a look ?

Thanks a lot !
Yoann.