[3.2.0] Sorting Applications in Devportal throws error

[athiththan11]

Description:

When trying to sort the Applications from the Devportal's Applications view, the following error was observed in the wso2carbon logs (with the default shipped H2 database)

[2020-08-02 17:31:30,811] ERROR - ApiMgtDAO Error when reading the application information from the persistence store.
org.h2.jdbc.JdbcSQLSyntaxErrorException: Order by expression "ASC" must be in the result list in this case; SQL statement:
select distinct x.*,bl.ENABLED from (SELECT    APPLICATION_ID,    NAME,   APPLICATION_TIER,   APP.SUBSCRIBER_ID,     CALLBACK_URL,     DESCRIPTION,    APPLICATION_STATUS,    USER_ID,    GROUP_ID,    UUID,    APP.CREATED_BY AS CREATED_BY  FROM   AM_APPLICATION APP,    AM_SUBSCRIBER SUB   WHERE    SUB.SUBSCRIBER_ID = APP.SUBSCRIBER_ID  AND    LOWER (SUB.USER_ID) =LOWER (?) And     NAME like ? limit ? , ?  )x left join AM_BLOCK_CONDITIONS bl on  ( bl.TYPE = 'APPLICATION' AND bl.VALUE = concat(concat(x.USER_ID,':'),x.name))  ORDER BY  asc  [90068-199]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:573) ~[h2_1.4.199.wso2v1.jar:?]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:427) ~[h2_1.4.199.wso2v1.jar:?]
    ...

Furthermore, once the sorting is applied and when searching and clearing (removing the keyword using backspace) the search bar (the search bar in the table view), the table view goes to an infinity looping (find the attached gif)

Steps to reproduce:

1. Login to the Devportal
2. Go to Applications view (table view)
3. Sort the table by either Owner, Workflow Status or Subscriptions
4. An error will be thrown in the wso2carbon logs

Affected Product Version:

• API Manager v3.2.0 - RC1

[tmkasun]

Both sorting issue and infinite loader issue is not existing in APIM 3.2.0 RC pack, Hence marking as Fixed and closing the issue

[tmkasun]

Re-opening the issue since, I could reproduce the behavior when trying to sort the table by either Owner, Workflow Status or Subscriptions

[chanaka3d]

Application search works only when sortBy=name

applications?query=Default&sortBy=name&sortOrder=desc&limit=10&offset=0 works applications?query=Default&sortBy=owner&sortOrder=asc&limit=10&offset=0 fails

[chanaka3d]

This issue is fixed with carbon-apimgt 6.8.49-SNAPSHOT