[AML-808] UserService.getUsers() with TQL Query fails with service exception - Admax Local

Details

Type: Bug
Status: Closed
Priority: Major
Resolution: Fixed
Affects Version/s: Aquidneck
Fix Version/s: 2.0.0, sprint 14: 12-05-2012, Aquidneck
Component/s: None
Labels:
None
Environment:

All

Sprint:
Sprint 13, Sprint 14

Description

Mike S. has flagged an issue executing calls into UserServicegetUsers() with a TQL param containing group by and order by. Here is the stack trace:

2012-11-14 12:01:40,713-76184136 [http-8080-3] INFO
org.apache.cxf.interceptor.LoggingInInterceptor - Inbound Message
----------------------------
ID: 80
Address:
http://localhost:8080/sapi/rest/users?token=OMSOHE376TFL5GP5ACNW44RTOABHEVM7S3HF7CVFDMNJISIXJ3QQNLXPQWGODVMHVQS4OURQ3QYG4PBGXCTUJEXL74D4GH63MQ3YBK3NDPBIXZFHS257SV5PFSTX66GTX3C65EZMUQDU57DIEGFKQE7GFRG4II622PGZWGASTIAR47Z4RVKA====&q=SELECT%20WHERE%20%281%20%3D%3D%201%29%20GROUP%20BY%20Type%20ORDER%20BY%20Type%20DESC
Http-Method: GET
Content-Type:
Headers:

{Accept=[*/*], accept-charset=[ISO-8859-1,utf-8;q=0.7,*;q=0.3], accept-encoding=[gzip,deflate,sdch], accept-language=[en-US,en;q=0.8], connection=[keep-alive], Content-Type=[null], host=[localhost:8080], user-agent=[Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.4 (KHTML, like Gecko) Chrome/22.0.1229.94 Safari/537.4]}

--------------------------------------
2012-11-14 12:01:40,713-76184136 [http-8080-3] INFO
com.thesearchagency.sapi.security.TokenParsingInterceptor - handleMessage
2012-11-14 12:01:40,713-76184136 [http-8080-3] DEBUG
com.thesearchagency.sapi.security.TokenParsingInterceptor - Received
query string:
token=OMSOHE376TFL5GP5ACNW44RTOABHEVM7S3HF7CVFDMNJISIXJ3QQNLXPQWGODVMHVQS4OURQ3QYG4PBGXCTUJEXL74D4GH63MQ3YBK3NDPBIXZFHS257SV5PFSTX66GTX3C65EZMUQDU57DIEGFKQE7GFRG4II622PGZWGASTIAR47Z4RVKA====&q=SELECT%20WHERE%20%281%20%3D%3D%201%29%20GROUP%20BY%20Type%20ORDER%20BY%20Type%20DESC
2012-11-14 12:01:40,713-76184136 [http-8080-3] DEBUG
com.thesearchagency.sapi.security.TokenParsingInterceptor - Extracted
parameters:

{token=[OMSOHE376TFL5GP5ACNW44RTOABHEVM7S3HF7CVFDMNJISIXJ3QQNLXPQWGODVMHVQS4OURQ3QYG4PBGXCTUJEXL74D4GH63MQ3YBK3NDPBIXZFHS257SV5PFSTX66GTX3C65EZMUQDU57DIEGFKQE7GFRG4II622PGZWGASTIAR47Z4RVKA====], q=[SELECT%20WHERE%20%281%20%3D%3D%201%29%20GROUP%20BY%20Type%20ORDER%20BY%20Type%20DESC]}

2012-11-14 12:01:40,713-76184136 [http-8080-3] DEBUG
com.thesearchagency.security.TokenizedSecurityContext -
Token:OMSOHE376TFL5GP5ACNW44RTOABHEVM7S3HF7CVFDMNJISIXJ3QQNLXPQWGODVMHVQS4OURQ3QYG4PBGXCTUJEXL74D4GH63MQ3YBK3NDPBIXZFHS257SV5PFSTX66GTX3C65EZMUQDU57DIEGFKQE7GFRG4II622PGZWGASTIAR47Z4RVKA====
2012-11-14 12:01:40,714-76184137 [http-8080-3] INFO
com.thesearchagency.sapi.security.TokenParsingInterceptor - Adding
(token 619118772748104:[e042e3b8-b281-4851-a76d-acfabb994dec,
675c1ffb-1109-4e3d-8e59-2b06c6b2ec66]:

{realm=com.optus}) context to message
2012-11-14 12:01:40,716-76184139 [http-8080-3] DEBUG
com.thesearchagency.sapi.jaxrs.providers.JAXRSContextProvider -
------------------- Beginning request conversion ----------
2012-11-14 12:01:40,716-76184139 [http-8080-3] DEBUG
com.thesearchagency.sapi.jaxrs.providers.JAXRSContextProvider - Found
security context for 675c1ffb-1109-4e3d-8e59-2b06c6b2ec66
2012-11-14 12:01:40,716-76184139 [http-8080-3] DEBUG
com.thesearchagency.sapi.jaxrs.providers.JAXRSContextProvider - Found
tokenized security context: (token
619118772748104:[e042e3b8-b281-4851-a76d-acfabb994dec,
675c1ffb-1109-4e3d-8e59-2b06c6b2ec66]:{realm=com.optus}

)
2012-11-14 12:01:40,716-76184139 [http-8080-3] DEBUG
com.thesearchagency.sapi.jaxrs.providers.JAXRSContextProvider -
------------------- End request conversion ----------
2012-11-14 12:01:40,716-76184139 [http-8080-3] DEBUG
com.thesearchagency.sapi.jaxrs.providers.JAXRSContextProvider - Getting
request context for 675c1ffb-1109-4e3d-8e59-2b06c6b2ec66 in realm com.optus
2012-11-14 12:01:40,716-76184139 [http-8080-3] DEBUG
com.thesearchagency.util.PropertiesPropertySetFactory - Property set
for settings/com.optus.properties is {}
2012-11-14 12:01:40,716-76184139 [http-8080-3] INFO
com.thesearchagency.sapi.security.PermissionsCheckingInterceptor -
handleMessage
2012-11-14 12:01:40,716-76184139 [http-8080-3] DEBUG
com.thesearchagency.sapi.security.PermissionsCheckingInterceptor -
resource: public javax.ws.rs.core.Response
com.thesearchagency.sapi.services.user.UserService.getUsers(com.thesearchagency.saas.core.services.IRequestContext,java.lang.String)
2012-11-14 12:01:40,716-76184139 [http-8080-3] DEBUG
com.thesearchagency.sapi.security.PermissionsCheckingInterceptor -
Excuting getUsers
2012-11-14 12:01:40,716-76184139 [http-8080-3] DEBUG
com.thesearchagency.sapi.security.PermissionsCheckingInterceptor -
Checking for execute permissions on
SAPI:com.thesearchagency.sapi.services.user.UserService.getUsers
2012-11-14 12:01:40,716-76184139 [http-8080-3] DEBUG
com.thesearchagency.sapi.security.PermissionsCheckingInterceptor -
Getting connection for
com_thesearchagency_saas_core_services_security_manager_TSASecurityManager
2012-11-14 12:01:40,716-76184139 [http-8080-3] DEBUG log4jdbc.debug -
driver name is H2 JDBC Driver
2012-11-14 12:01:40,717-76184140 [http-8080-3] DEBUG
com.thesearchagency.saas.core.services.security.manager.TSASecurityManager

Attempting to get permissions for principal
675c1ffb-1109-4e3d-8e59-2b06c6b2ec66
2012-11-14 12:01:40,721-76184144 [http-8080-3] INFO
com.thesearchagency.saas.core.services.security.manager.TSASecurityManager
matching
'SAPI:execute:com.thesearchagency.sapi.services.user.UserService.getUsers'.matches('.:.:.*')
-> true
2012-11-14 12:01:40,722-76184145 [http-8080-3] DEBUG
com.thesearchagency.sapi.jaxrs.providers.JAXRSContextProvider -
- - - - Beginning request conversion ----------
        2012-11-14 12:01:40,722-76184145 [http-8080-3] DEBUG
        com.thesearchagency.sapi.jaxrs.providers.JAXRSContextProvider - Found
        security context for 675c1ffb-1109-4e3d-8e59-2b06c6b2ec66
        2012-11-14 12:01:40,722-76184145 [http-8080-3] DEBUG
        com.thesearchagency.sapi.jaxrs.providers.JAXRSContextProvider - Found
        tokenized security context: (token
        619118772748104:[e042e3b8-b281-4851-a76d-acfabb994dec,
        675c1ffb-1109-4e3d-8e59-2b06c6b2ec66]: {realm=com.optus}
        )
        2012-11-14 12:01:40,722-76184145 [http-8080-3] DEBUG
        com.thesearchagency.sapi.jaxrs.providers.JAXRSContextProvider -
        
        End request conversion ----------
        2012-11-14 12:01:40,722-76184145 [http-8080-3] DEBUG
        com.thesearchagency.sapi.jaxrs.providers.JAXRSContextProvider - Getting
        request context for 675c1ffb-1109-4e3d-8e59-2b06c6b2ec66 in realm com.optus
        2012-11-14 12:01:40,730-76184153 [http-8080-3] DEBUG
        com.thesearchagency.util.PropertiesPropertySetFactory - Property set
        for settings/com.optus.properties is {}
        2012-11-14 12:01:40,731-76184154 [http-8080-3] DEBUG
        com.thesearchagency.sapi.jaxrs.providers.JAXRSContextProvider - Getting
        connection for
        com_thesearchagency_saas_core_services_user_managers_UserManager
        2012-11-14 12:01:40,731-76184154 [http-8080-3] DEBUG log4jdbc.debug -
        driver name is H2 JDBC Driver
        2012-11-14 12:01:40,731-76184154 [http-8080-3] DEBUG
        com.thesearchagency.database.storage.jdbc.JDBCStorageManager - Count
        objects using query sql:
        SELECT COUNT AS TSA_COUNT_RESULT FROM users WHERE (1 = 1) ORDER BY
        users.type DESC LIMIT 0 , 1024
        2012-11-14 12:01:40,732-76184155 [http-8080-3] ERROR
        com.thesearchagency.saas.core.services.user.UserServiceComponent -
        Exception thrown fetching users
        com.thesearchagency.database.storage.StorageManagerException: package
        com.thesearchagency.database.storage.INTERNAL_ERROR_EXCEPTION
        at
        com.thesearchagency.database.storage.jdbc.JDBCStorageManager.count(JDBCStorageManager.java:276)
        at
        com.thesearchagency.saas.core.services.user.managers.UserManager$3.run(UserManager.java:131)
        at
        com.thesearchagency.saas.core.services.user.managers.UserManager$3.run(UserManager.java:128)
        at
        com.thesearchagency.database.storage.jdbc.JDBCStorageCommandRunner.runWithReturn(JDBCStorageCommandRunner.java:16)
        at
        com.thesearchagency.saas.core.services.user.managers.UserManager.getUsers(UserManager.java:127)
        at
        com.thesearchagency.saas.core.services.user.UserServiceComponent.getUsers(UserServiceComponent.java:161)
        at
        com.thesearchagency.sapi.services.user.UserService.getUsers(UserService.java:302)
        at sun.reflect.GeneratedMethodAccessor84.invoke(Unknown Source)
        at
        sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at
        org.apache.cxf.service.invoker.AbstractInvoker.performInvocation(AbstractInvoker.java:180)
        at
        org.apache.cxf.service.invoker.AbstractInvoker.invoke(AbstractInvoker.java:96)
        at org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:167)
        at org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:94)
        at
        org.apache.cxf.interceptor.ServiceInvokerInterceptor$1.run(ServiceInvokerInterceptor.java:58)
        at
        org.apache.cxf.interceptor.ServiceInvokerInterceptor.handleMessage(ServiceInvokerInterceptor.java:94)
        at
        org.apache.cxf.phase.PhaseInterceptorChain.doIntercept(PhaseInterceptorChain.java:262)
        at
        org.apache.cxf.transport.ChainInitiationObserver.onMessage(ChainInitiationObserver.java:121)
        at
        org.apache.cxf.transport.http.AbstractHTTPDestination.invoke(AbstractHTTPDestination.java:211)
        at
        org.apache.cxf.transport.servlet.ServletController.invokeDestination(ServletController.java:213)
        at
        org.apache.cxf.transport.servlet.ServletController.invoke(ServletController.java:154)
        at
        org.apache.cxf.transport.servlet.CXFNonSpringServlet.invoke(CXFNonSpringServlet.java:130)
        at
        org.apache.cxf.transport.servlet.AbstractHTTPServlet.handleRequest(AbstractHTTPServlet.java:221)
        at
        org.apache.cxf.transport.servlet.AbstractHTTPServlet.doGet(AbstractHTTPServlet.java:146)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
        at
        org.apache.cxf.transport.servlet.AbstractHTTPServlet.service(AbstractHTTPServlet.java:197)
        at
        org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
        at
        org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at
        org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
        at
        org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
        at
        org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
        at
        org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
        at
        org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
        at
        org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
        at
        org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:859)
        at
        org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:602)
        at
        org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
        at java.lang.Thread.run(Thread.java:662)
        Caused by: org.h2.jdbc.JdbcSQLException: Column "users.TYPE" must be in
        the GROUP BY list; SQL statement:
        SELECT COUNT AS TSA_COUNT_RESULT FROM users WHERE (1 = 1) ORDER BY
        users.type DESC LIMIT 0 , 1024 [90016-168]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
        at org.h2.message.DbException.get(DbException.java:169)
        at org.h2.message.DbException.get(DbException.java:146)
        at
        org.h2.expression.ExpressionColumn.updateAggregate(ExpressionColumn.java:159)
        at org.h2.command.dml.Select.queryGroup(Select.java:339)
        at org.h2.command.dml.Select.queryWithoutCache(Select.java:613)
        at org.h2.command.dml.Query.query(Query.java:307)
        at org.h2.command.dml.Query.query(Query.java:277)
        at org.h2.command.dml.Query.query(Query.java:36)
        at org.h2.command.CommandContainer.query(CommandContainer.java:86)
        at org.h2.command.Command.executeQuery(Command.java:191)
        at
        org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:109)
        at
        org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at
        org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at
        net.sf.log4jdbc.PreparedStatementSpy.executeQuery(PreparedStatementSpy.java:735)
        at
        com.thesearchagency.database.storage.jdbc.JDBCStorageManager.count(JDBCStorageManager.java:266)
        ... 37 more
        2012-11-14 12:01:40,732-76184155 [http-8080-3] ERROR
        com.thesearchagency.sapi.services.user.UserService - Exception thrown
        getting users
        com.thesearchagency.saas.core.services.ServiceException: package
        com.thesearchagency.saas.core.services.DATABASE_EXCEPTION
        at
        com.thesearchagency.saas.core.services.user.UserServiceComponent.getUsers(UserServiceComponent.java:166)
        at
        com.thesearchagency.sapi.services.user.UserService.getUsers(UserService.java:302)
        at sun.reflect.GeneratedMethodAccessor84.invoke(Unknown Source)
        at
        sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at
        org.apache.cxf.service.invoker.AbstractInvoker.performInvocation(AbstractInvoker.java:180)
        at
        org.apache.cxf.service.invoker.AbstractInvoker.invoke(AbstractInvoker.java:96)
        at org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:167)
        at org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:94)
        at
        org.apache.cxf.interceptor.ServiceInvokerInterceptor$1.run(ServiceInvokerInterceptor.java:58)
        at
        org.apache.cxf.interceptor.ServiceInvokerInterceptor.handleMessage(ServiceInvokerInterceptor.java:94)
        at
        org.apache.cxf.phase.PhaseInterceptorChain.doIntercept(PhaseInterceptorChain.java:262)
        at
        org.apache.cxf.transport.ChainInitiationObserver.onMessage(ChainInitiationObserver.java:121)
        at
        org.apache.cxf.transport.http.AbstractHTTPDestination.invoke(AbstractHTTPDestination.java:211)
        at
        org.apache.cxf.transport.servlet.ServletController.invokeDestination(ServletController.java:213)
        at
        org.apache.cxf.transport.servlet.ServletController.invoke(ServletController.java:154)
        at
        org.apache.cxf.transport.servlet.CXFNonSpringServlet.invoke(CXFNonSpringServlet.java:130)
        at
        org.apache.cxf.transport.servlet.AbstractHTTPServlet.handleRequest(AbstractHTTPServlet.java:221)
        at
        org.apache.cxf.transport.servlet.AbstractHTTPServlet.doGet(AbstractHTTPServlet.java:146)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
        at
        org.apache.cxf.transport.servlet.AbstractHTTPServlet.service(AbstractHTTPServlet.java:197)
        at
        org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
        at
        org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at
        org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
        at
        org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
        at
        org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
        at
        org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
        at
        org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
        at
        org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
        at
        org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:859)
        at
        org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:602)
        at
        org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
        at java.lang.Thread.run(Thread.java:662)
        Caused by: org.h2.jdbc.JdbcSQLException: Column "users.TYPE" must be in
        the GROUP BY list; SQL statement:
        SELECT COUNT AS TSA_COUNT_RESULT FROM users WHERE (1 = 1) ORDER BY
        users.type DESC LIMIT 0 , 1024 [90016-168]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
        at org.h2.message.DbException.get(DbException.java:169)
        at org.h2.message.DbException.get(DbException.java:146)
        at
        org.h2.expression.ExpressionColumn.updateAggregate(ExpressionColumn.java:159)
        at org.h2.command.dml.Select.queryGroup(Select.java:339)
        at org.h2.command.dml.Select.queryWithoutCache(Select.java:613)
        at org.h2.command.dml.Query.query(Query.java:307)
        at org.h2.command.dml.Query.query(Query.java:277)
        at org.h2.command.dml.Query.query(Query.java:36)
        at org.h2.command.CommandContainer.query(CommandContainer.java:86)
        at org.h2.command.Command.executeQuery(Command.java:191)
        at
        org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:109)
        at
        org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at
        org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at
        net.sf.log4jdbc.PreparedStatementSpy.executeQuery(PreparedStatementSpy.java:735)
        at
        com.thesearchagency.database.storage.jdbc.JDBCStorageManager.count(JDBCStorageManager.java:266)
        at
        com.thesearchagency.saas.core.services.user.managers.UserManager$3.run(UserManager.java:131)
        at
        com.thesearchagency.saas.core.services.user.managers.UserManager$3.run(UserManager.java:128)
        at
        com.thesearchagency.database.storage.jdbc.JDBCStorageCommandRunner.runWithReturn(JDBCStorageCommandRunner.java:16)
        at
        com.thesearchagency.saas.core.services.user.managers.UserManager.getUsers(UserManager.java:127)
        at
        com.thesearchagency.saas.core.services.user.UserServiceComponent.getUsers(UserServiceComponent.java:161)
        ... 32 more
        2012-11-14 12:01:40,745-76184168 [http-8080-3] INFO
        org.apache.cxf.interceptor.LoggingOutInterceptor - Outbound Message
        ---------------------------
        ID: 80
        Response-Code: 500
        Content-Type: text/plain
        Headers: {Date=[Wed, 14 Nov 2012 17:01:40 GMT]}
        Payload: Internal server error
        --------------------------------------

Attachments

Activity

People

Assignee:

Gavin Nicol (Inactive)

Reporter:

Patrick Wynne

Votes:

0 Vote for this issue

Watchers:

6 Start watching this issue

Dates

Created:

14/Nov/12 12:28 PM

Updated:

23/Jul/13 9:28 AM

Resolved:

20/Nov/12 11:51 AM