fetch with groupBy and outputs ignores sortBy

[claudiobosticco]

SmartClient Version: SNAPSHOT_v13.1d_2024-04-04/Enterprise Deployment (built 2024-04-04)

Hello, starting a new thread from here https://forums.smartclient.com/forum...098#post272098

for now I've greatly simplified the use case in my application.

<DataSource ID="AUTH_USERS_PERMISSIONS_EXT"
            serverType="sql"
            dbName="dbJFrame"
            schema="DBJFRAME"
            tableName="AUTH_USERS_PERMISSIONS"
            useAnsiJoins="true"
>
    <fields>
        <field name="ID_PERMISSION_FK" type="integer" foreignKey="AUTH_PERMISSIONS.ID_REC" relatedTableAlias="PERMISSIONS" required="true"/>
        <field name="USER_PERMISSION_NAME" type="text" includeFrom="AUTH_PERMISSIONS.NAME" includeVia="ID_PERMISSION_FK"/>
    </fields>
    <operationBindings>
        <operationBinding operationType="fetch" operationId="fetchDistinctUsersPermissions"
                          includeAnsiJoinsInTableClause="true"
                          customValueFields="USERS_EMAILS"
                          outputs="USER_PERMISSION_NAME"
        >
            <groupBy>USER_PERMISSION_NAME</groupBy>
        </operationBinding>
    </operationBindings>
</DataSource>

this fetch:

AUTH_USERS_PERMISSIONS_EXT.fetchData(null, null,{ sortBy: ["USER_PERMISSION_NAME"], operationId:"fetchDistinctUsersPermissions" })

produces this log:

2024-04-08T12:18:46,929 DEBUG RPCManager Request #1 (DSRequest) payload: {
    criteria:{
    },
    operationConfig:{
        dataSource:"AUTH_USERS_PERMISSIONS_EXT",
        repo:null,
        operationType:"fetch",
        textMatchStyle:"exact"
    },
    sortBy:[
        "USER_PERMISSION_NAME"
    ],
    appID:"builtinApplication",
    operation:"fetchDistinctUsersPermissions",
    oldValues:null,
    tenantId:null
}
2024-04-08T12:18:46,929 INFO IDACall Performing 1 operation(s) [UA: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:124.0) Gecko/20100101 Firefox/124.0]
2024-04-08T12:18:46,929 INFO DSRequest Executing AUTH_USERS_PERMISSIONS_EXT.fetch[fetchDistinctUsersPermissions] rows: 0->-1 with
criteria: {}
sortBy: [USER_PERMISSION_NAME]
2024-04-08T12:18:46,930 DEBUG AppBase No userTypes defined, allowing anyone access to all operations for this application
2024-04-08T12:18:46,930 DEBUG AppBase No public zero-argument method named '_fetchDistinctUsersPermissions' found, performing generic datasource operation
2024-04-08T12:18:46,930 INFO SQLWhereClause empty condition
2024-04-08T12:18:46,932 DEBUG SQL 159: Executing SQL query on 'dbJFrame': SELECT PERMISSIONS.NAME AS USER_PERMISSION_NAME FROM DBJFRAME.AUTH_USERS_PERMISSIONS JOIN DBJFRAME.AUTH_PERMISSIONS PERMISSIONS ON AUTH_USERS_PERMISSIONS.ID_PERMISSION_FK = PERMISSIONS.ID_REC WHERE ('1'='1') GROUP BY PERMISSIONS.NAME
2024-04-08T12:18:46,978 DEBUG PoolableSQLConnectionFactory makeObject() created an unpooled Connection '1525426296'
2024-04-08T12:18:46,978 DEBUG SQLConnectionManager Borrowed connection '1525426296' from DBCP
2024-04-08T12:18:46,978 DEBUG SQLTransaction Started new dbJFrame transaction "1525426296"
2024-04-08T12:18:46,978 INFO SQL Executing SQL query on 'dbJFrame': SELECT PERMISSIONS.NAME AS USER_PERMISSION_NAME FROM DBJFRAME.AUTH_USERS_PERMISSIONS JOIN DBJFRAME.AUTH_PERMISSIONS PERMISSIONS ON AUTH_USERS_PERMISSIONS.ID_PERMISSION_FK = PERMISSIONS.ID_REC WHERE ('1'='1') GROUP BY PERMISSIONS.NAME

where the query lacks the ORDER BY clause.

Instead, if I remove the "outputs" attribute, ie:

<DataSource ID="AUTH_USERS_PERMISSIONS_EXT"
            serverType="sql"
            dbName="dbJFrame"
            schema="DBJFRAME"
            tableName="AUTH_USERS_PERMISSIONS"
            useAnsiJoins="true"
>
    <fields>
        <field name="ID_PERMISSION_FK" type="integer" foreignKey="AUTH_PERMISSIONS.ID_REC" relatedTableAlias="PERMISSIONS" required="true"/>
        <field name="USER_PERMISSION_NAME" type="text" includeFrom="AUTH_PERMISSIONS.NAME" includeVia="ID_PERMISSION_FK"/>
    </fields>
    <operationBindings>
        <operationBinding operationType="fetch" operationId="fetchDistinctUsersPermissions"
                          includeAnsiJoinsInTableClause="true"
                          customValueFields="USERS_EMAILS"
        >
            <groupBy>USER_PERMISSION_NAME</groupBy>
        </operationBinding>
    </operationBindings>
</DataSource>

then the log is:

2024-04-08T12:20:58,976 DEBUG RPCManager Request #1 (DSRequest) payload: {
    criteria:{
    },
    operationConfig:{
        dataSource:"AUTH_USERS_PERMISSIONS_EXT",
        repo:null,
        operationType:"fetch",
        textMatchStyle:"exact"
    },
    sortBy:[
        "USER_PERMISSION_NAME"
    ],
    appID:"builtinApplication",
    operation:"fetchDistinctUsersPermissions",
    oldValues:null,
    tenantId:null
}
2024-04-08T12:20:58,976 INFO IDACall Performing 1 operation(s) [UA: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:124.0) Gecko/20100101 Firefox/124.0]
2024-04-08T12:20:58,977 INFO DSRequest Executing AUTH_USERS_PERMISSIONS_EXT.fetch[fetchDistinctUsersPermissions] rows: 0->-1 with
criteria: {}
sortBy: [USER_PERMISSION_NAME]
2024-04-08T12:20:58,977 DEBUG AppBase No userTypes defined, allowing anyone access to all operations for this application
2024-04-08T12:20:58,977 DEBUG AppBase No public zero-argument method named '_fetchDistinctUsersPermissions' found, performing generic datasource operation
2024-04-08T12:20:58,978 INFO SQLWhereClause empty condition
2024-04-08T12:20:58,980 DEBUG SQL 160: Executing SQL query on 'dbJFrame': SELECT PERMISSIONS.NAME AS USER_PERMISSION_NAME FROM DBJFRAME.AUTH_USERS_PERMISSIONS JOIN DBJFRAME.AUTH_PERMISSIONS PERMISSIONS ON AUTH_USERS_PERMISSIONS.ID_PERMISSION_FK = PERMISSIONS.ID_REC WHERE ('1'='1') GROUP BY PERMISSIONS.NAME ORDER BY USER_PERMISSION_NAME
2024-04-08T12:20:59,031 DEBUG PoolableSQLConnectionFactory makeObject() created an unpooled Connection '85786952'
2024-04-08T12:20:59,031 DEBUG SQLConnectionManager Borrowed connection '85786952' from DBCP
2024-04-08T12:20:59,032 DEBUG SQLTransaction Started new dbJFrame transaction "85786952"
2024-04-08T12:20:59,032 INFO SQL Executing SQL query on 'dbJFrame': SELECT PERMISSIONS.NAME AS USER_PERMISSION_NAME FROM DBJFRAME.AUTH_USERS_PERMISSIONS JOIN DBJFRAME.AUTH_PERMISSIONS PERMISSIONS ON AUTH_USERS_PERMISSIONS.ID_PERMISSION_FK = PERMISSIONS.ID_REC WHERE ('1'='1') GROUP BY PERMISSIONS.NAME ORDER BY USER_PERMISSION_NAME

where you may see that there is the ORDER BY clause.

Does this shed some light?