Error 'Sorting by the column specified is not allowed' on Classic or Modern Project Section

Products

• Clarity PPM SaaS

Issue/Introduction

The corresponding errors occur in the APP logs

--Modern

ERROR 2023-04-21 14:44:04,872 [http-nio-8080-exec-4126] union.persistence (clarity:user:session:PPM_REST_API) Exception on nsql_internal statement null:5029005 org.postgresql.util.PSQLException: ERROR: syntax error at or near "distinct" Position: 507 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)

--Classic
ERROR 2023-04-21 14:43:48,963 [http-nio-8080-exec-4128] union.persistence (clarity:user:session:projmgr.projectProperties) Exception on nsql_internal statement null:5029005
org.postgresql.util.PSQLException: ERROR: syntax error at or near "distinct"
  Position: 476
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:181)
    at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:170)
    at jdk.internal.reflect.GeneratedMethodAccessor107.invoke(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:118)
    at com.sun.proxy.$Proxy85.execute(Unknown Source)
    at com.niku.union.persistence.jdbc.SQLTracePreparedStatement.execute(SQLTracePreparedStatement.java:112)

Environment

Release : 16.1.1

Resolution

This is due to a change in the NSQL.

1. Obtain the NSQL id from the logs.
Ex: 5029005 

2. Run the following query to find out which NSQL was changed.

select * from
CMN_NSQL_QUERIES
where db_id = 'Niku'
and id = 5029005 
order by last_updated_date desc

3. Review the example query:


SELECT @SELECT:distinct r.ID:ID@,
   @SELECT:r.unique_name:resource_id@,
   @SELECT:r.full_name:full_name@,
   @SELECT:r.first_name:first_name@,
   @SELECT:r.last_name:last_name@
   FROM SRM_RESOURCES r, CMN_SEC_USER_GROUPS secug, CMN_SEC_GROUPS secg
   WHERE secug.group_id = secg.id
   AND secug.user_id = r.user_id
   AND @FILTER@

In the example above, the resolution is to remove the distinct keyword