SQLExpressions warnings in 13.1

[claudiobosticco]

Hello, In the more recent 13.1 builds, I’ve noticed WARN messages like the following:

WARN  SQLExpressions SQLExpression for field 'EMAIL' already exists, skip.

These seem harmless, but I’d like to understand more about their meaning.

Then, there are others like:

WARN SQLExpressions Missing expression for the 'ID_ROLE_FK' field of the 'AUTH_MENU_ROLES_PERMISSIONS' DataSource.

In some cases, these seem related to a regression in at least one specific operationBinding. After this warning, the criteria for that field doesn’t appear in the WHERE clause, whereas previously it did.

The field is defined as:

 <field name="ID_ROLE_FK" type="integer" customSelectExpression="MENU_ROLES.ID_ROLE_FK" customSQL="true"/>

And the operationBinding includes it in the customCriteriaFields:

       <operationBinding operationType="fetch" operationId="fetchMenuPerGestioneUtenti"
                          serverMethod="fetchMenuPerGestioneUtenti"
                          requiresRole="ROLE_ADMIN, ROLE_USERADMIN, ROLE_ADMIN_SCOUT, ADMIN"
                          customFields="IS_MENU_FROM_ROLE,IS_MENU_FROM_PERMISSION,ID_ROLES,ROLE_NAMES,ID_PERMISSIONS,PERMISSION_NAMES"
                          customCriteriaFields="ID_PERMISSION_FK,ID_ROLE_FK">
            <serverObject lookupStyle="new" className="com.juve.auth.menu.FetchMenuPerGestioneUtenti"/>
            <tableClause>
                $defaultTableClause
                LEFT JOIN DBJFRAME.AUTH_MENU_ROLES MENU_ROLES ON TREE_MENU.ID_REC = MENU_ROLES.ID_MENU_FK
                LEFT JOIN DBJFRAME.AUTH_ROLES ROLES ON MENU_ROLES.ID_ROLE_FK = ROLES.ID_REC
                LEFT JOIN DBJFRAME.AUTH_MENU_PERMISSIONS MENU_PERMISSIONS ON TREE_MENU.ID_REC = MENU_PERMISSIONS.ID_MENU_FK
                LEFT JOIN DBJFRAME.AUTH_PERMISSIONS PERMISSIONS ON MENU_PERMISSIONS.ID_PERMISSION_FK = PERMISSIONS.ID_REC
            </tableClause>
            <whereClause>
                $defaultWhereClause
                #if ($sessionAttributes.appID != 'JcaAuth')
                    and TREE_MENU.ID_APP = $sessionAttributes.appID
                #end
            </whereClause>
            <groupBy>ID_REC</groupBy>
            <groupBy>ID_APP</groupBy>
            <groupBy>VOCE_MENU</groupBy>
            <groupBy>VOCE_MENU_EN</groupBy>
            <groupBy>ID_FUNZIONE</groupBy>
            <groupBy>ORDINAMENTO_PER_LIVELLO</groupBy>
            <groupBy>ID_VOCE_PADRE</groupBy>
            <groupBy>ICONA</groupBy>
            <groupBy>ICONA_SVG</groupBy>
            <groupBy>IS_MENU_FROM_ROLE</groupBy>
            <groupBy>IS_MENU_FROM_PERMISSION</groupBy>
            <groupBy>ID_MENU_FK</groupBy>
            <summaryFunctions>
                <ID_ROLES>concat</ID_ROLES>
                <ROLE_NAMES>concat</ROLE_NAMES>
                <ID_PERMISSIONS>concat</ID_PERMISSIONS>
                <PERMISSION_NAMES>concat</PERMISSION_NAMES>
                <IS_DASHBOARD_VISIBLE>max</IS_DASHBOARD_VISIBLE>
                <MOBILE_PHONE_VISIBLE>max</MOBILE_PHONE_VISIBLE>
                <TABLET_VISIBLE>max</TABLET_VISIBLE>
                <MOBILE_PHONE_DEFAULT>max</MOBILE_PHONE_DEFAULT>
                <MOBILE_PHONE_ONLY>max</MOBILE_PHONE_ONLY>
            </summaryFunctions>
            <orderClause>ORDINAMENTO_PER_LIVELLO</orderClause>
        </operationBinding>

There’s one caveat: the DMI is actually calling dsRequest.addToCriteria() to add the criteria for the ID_ROLE_FK field.

Please let me know if this sheds some light, or if I should try to create a test case (though that might be difficult).

[claudiobosticco]

Hello, please let me know if you may know where it's the problem, or if you need a real test case

[Isomorphic]

We have identified the issue with the ID_ROLE_FK field, and a fix will be provided shortly.

Regarding the warning about the missing EMAIL field, could you please provide steps to reproduce it? We've made some changes to the SQL engine in version 13.1, and this warning should only be logged when the system expects an expression for the field to inject into the SQL query but finds it missing. This could either be an unnecessary warning or an indication that the issue is not as harmless as it seems.

[Isomorphic]

The issue with the missing ID_ROLE_FK field has been resolved and will be available for download in the nightly builds starting today (Sep 25).

[claudiobosticco]

Hello, thanks, now I don't see the WARN about ID_ROLE_FK, but I've got a NullPointerException:

2024-09-25T12:49:54,139 WARN RequestContext dsRequest.execute() failed:
java.lang.NullPointerException: null
    at com.isomorphic.datasource.IncludeFromDefinition.isDynamicInclusion(IncludeFromDefinition.java:132) ~[isomorphic-core-rpc-13.1-d20240925.jar:?]
    at com.isomorphic.datasource.DSRequest.createIncludeFromDefinitionFromCriteria(DSRequest.java:8193) ~[isomorphic-core-rpc-13.1-d20240925.jar:?]
    at com.isomorphic.datasource.DSRequest.addToCriteria(DSRequest.java:9499) ~[isomorphic-core-rpc-13.1-d20240925.jar:?]
    at com.isomorphic.datasource.DSRequest.addToCriteria(DSRequest.java:9552) ~[isomorphic-core-rpc-13.1-d20240925.jar:?]
    at com.isomorphic.datasource.DSRequest.addToCriteria(DSRequest.java:9577) ~[isomorphic-core-rpc-13.1-d20240925.jar:?]

[claudiobosticco]

Regarding the warning about the missing EMAIL field, could you please provide steps to reproduce it? We've made some changes to the SQL engine in version 13.1, and this warning should only be logged when the system expects an expression for the field to inject into the SQL query but finds it missing. This could either be an unnecessary warning or an indication that the issue is not as harmless as it seems.

Hello, while I can confirm that this warning doesn't seem to be linked to an actual problem (I've got many of them), I haven't been able to identify a certain single cause for it. It appears that the warnings are always related to "includeFrom" fields, but they don't occur for every includeFrom field.

[claudiobosticco]

Hello, thanks, now I don't see the WARN about ID_ROLE_FK, but I've got a NullPointerException:

2024-09-25T12:49:54,139 WARN RequestContext dsRequest.execute() failed:
java.lang.NullPointerException: null
at com.isomorphic.datasource.IncludeFromDefinition.isDynamicInclusion(IncludeFromDefinition.java:132) ~[isomorphic-core-rpc-13.1-d20240925.jar:?]
at com.isomorphic.datasource.DSRequest.createIncludeFromDefinitionFromCriteria(DSRequest.java:8193) ~[isomorphic-core-rpc-13.1-d20240925.jar:?]
at com.isomorphic.datasource.DSRequest.addToCriteria(DSRequest.java:9499) ~[isomorphic-core-rpc-13.1-d20240925.jar:?]
at com.isomorphic.datasource.DSRequest.addToCriteria(DSRequest.java:9552) ~[isomorphic-core-rpc-13.1-d20240925.jar:?]
at com.isomorphic.datasource.DSRequest.addToCriteria(DSRequest.java:9577) ~[isomorphic-core-rpc-13.1-d20240925.jar:?]

Hello, I didn't specified that the error arises where my DMI calls addToCriteria to add the criteria for ID_ROLE_FK :

dsRequest.addToCriteria(new AdvancedCriteria(DefaultOperators.Or, new Criterion[]{
                new SetCriterion("ID_ROLE_FK", DefaultOperators.InSet, rolesIds),
                new SetCriterion("ID_PERMISSION_FK", DefaultOperators.InSet, permissionsIds)
        }));

do you need more details?

[Isomorphic]

Apologies for the delay. This has been fixed and will be available for download in the nightly builds starting on October 2 (tomorrow). Please let us know how it works for you.

[claudiobosticco]

SmartClient Version: SNAPSHOT_v13.1d_2024-10-02/Enterprise Development Only (built 2024-10-02)

Hello, the NullPointerException is fixed, but I still got the original problem from post #1: even though I see the DSRequest criteria logged with the correct ID_ROLE_FK criterion, I then see the "Missing expression" log, and the criteria is missing from the where clause.

This is the relevant part of the log:

2024-10-02T17:51:38,635 DEBUG RPCManager Request #3 (DSRequest) payload: {
    criteria:{
        _constructor:"AdvancedCriteria",
        operator:"and",
        criteria:[
            {
                fieldName:"emailPerMenu",
                operator:"equals",
                value:"my.work@email.com",
                _criterionId:1727883892328
            },
            {
                fieldName:"ID_APP",
                operator:"equals",
                value:"Jat",
                _criterionId:1727883892329
            }
        ],
        _criterionId:1727883892327
    },
    operationConfig:{
        dataSource:"AUTH_MENU_ROLES_PERMISSIONS",
        repo:null,
        operationType:"fetch",
        textMatchStyle:"exact"
    },
    componentId:"isc_ExpansionMenuTreeGrid_0",
    appID:"builtinApplication",
    operation:"fetchMenuPerGestioneUtenti",
    oldValues:null,
    progressiveLoading:false,
    resultTreeIdField:"ID_REC",
    resultTreeParentIdField:"ID_VOCE_PADRE",
    tenantId:null
}
2024-10-02T17:51:38,714 INFO DSRequest Executing isc_ExpansionMenuTreeGrid_0->AUTH_MENU_ROLES_PERMISSIONS.fetch[fetchMenuPerGestioneUtenti] rows: 0->-1 with
criteria: {
    _constructor:"AdvancedCriteria",
    operator:"and",
    criteria:[
        {fieldName:"emailPerMenu", operator:"equals", value:"my.work@email.com",
         _criterionId:1727883892328},
        {fieldName:"ID_APP", operator:"equals", value:"Jat", _criterionId:1727883892329}
    ],
    _criterionId:1727883892327
}
2024-10-02T17:51:38,754 INFO DSRequest Executing isc_ExpansionMenuTreeGrid_0->AUTH_MENU_ROLES_PERMISSIONS.fetch[fetchMenuPerGestioneUtenti] rows: 0->-1 with
criteria: {
    _constructor:"AdvancedCriteria",
    criteria:[
        {fieldName:"emailPerMenu", value:"my.work@email.com", operator:"equals"},
        {fieldName:"ID_APP", value:"Jat", operator:"equals"},
        {
            criteria:[
                {
                    fieldName:"ID_ROLE_FK",
                    value:[27059, 26964, 15586, 26972, 27008, 30721, 27032, 27063, 15582, 15607, 27069,
                     27072,27074, 27075, 27078, 27082, 27083, 27085, 27118, 27119, 27125, 27134, 27135,
                     27143,27144, 15564, 27153, 32675, 27163, 27165],
                    operator:"inSet"
                },
                {
                    fieldName:"ID_PERMISSION_FK",
                    value:[],
                    operator:"inSet"
                }
            ],
            operator:"or"
        }
    ],
    __normalized:true,
    operator:"and"
}
2024-10-02T17:51:38,754 DEBUG AppBase No userTypes defined, allowing anyone access to all operations for this application
2024-10-02T17:51:38,754 DEBUG AppBase No public zero-argument method named '_fetchMenuPerGestioneUtenti' found, performing generic datasource operation
2024-10-02T17:51:38,758 WARN SQLExpressions Missing expression for the 'emailPerMenu' field of the 'AUTH_MENU_ROLES_PERMISSIONS' DataSource.
2024-10-02T17:51:38,759 DEBUG SQLExpressions Missing expression request.expressionFields: [
    "ID_REC",
    "ID_APP",
    "VOCE_MENU",
    "VOCE_MENU_EN",
    "ID_FUNZIONE",
    "ORDINAMENTO_PER_LIVELLO",
    "ID_VOCE_PADRE",
    "ICONA",
    "ICONA_SVG",
    "IS_MENU_FROM_ROLE",
    "IS_MENU_FROM_PERMISSION",
    "ID_MENU_FK",
    "ID_PERMISSIONS",
    "ID_ROLES",
    "IS_DASHBOARD_VISIBLE",
    "MOBILE_PHONE_VISIBLE",
    "TABLET_VISIBLE",
    "MOBILE_PHONE_DEFAULT",
    "MOBILE_PHONE_ONLY",
    "ROLE_NAMES",
    "PERMISSION_NAMES",
    "ID_ROLE_FK",
    "ID_PERMISSION_FK"
]
2024-10-02T17:51:38,759 DEBUG SQLExpressions Missing expression generated sqlExpressions.keySet: [
    "ID_REC",
    "ID_APP",
    "VOCE_MENU",
    "VOCE_MENU_EN",
    "ID_FUNZIONE",
    "ORDINAMENTO_PER_LIVELLO",
    "ID_VOCE_PADRE",
    "ICONA",
    "ICONA_SVG",
    "IS_MENU_FROM_ROLE",
    "IS_MENU_FROM_PERMISSION",
    "ID_MENU_FK",
    "ID_PERMISSIONS",
    "ID_ROLES",
    "IS_DASHBOARD_VISIBLE",
    "MOBILE_PHONE_VISIBLE",
    "TABLET_VISIBLE",
    "MOBILE_PHONE_DEFAULT",
    "MOBILE_PHONE_ONLY",
    "ROLE_NAMES",
    "PERMISSION_NAMES"
]
2024-10-02T17:51:38,760 DEBUG SQLExpressions Missing expression stack: java.lang.Throwable
    at com.isomorphic.sql.SQLExpressions.getExpression(SQLExpressions.java:173)
    at com.isomorphic.sql.SQLWhereClause.buildAdvancedExpression(SQLWhereClause.java:977)
    at com.isomorphic.sql.SQLWhereClause.getOutput(SQLWhereClause.java:419)
    at com.isomorphic.sql.SQLWhereClause.buildCompoundExpression(SQLWhereClause.java:507)
    at com.isomorphic.sql.SQLWhereClause.getOutput(SQLWhereClause.java:407)
    at com.isomorphic.sql.SQLWhereClause.toString(SQLWhereClause.java:284)
    at com.isomorphic.sql.SQLWhereClause.getSQLString(SQLWhereClause.java:290)
    at com.isomorphic.sql.SQLDataSource.getClausesContext(SQLDataSource.java:4325)
    at com.isomorphic.sql.SQLDataSource.SQLExecute(SQLDataSource.java:1839)
    at com.isomorphic.sql.SQLDataSource.SQLExecute(SQLDataSource.java:1739)
2024-10-02T17:51:38,760 WARN SQLExpressions Missing expression for the 'ID_ROLE_FK' field of the 'AUTH_MENU_ROLES_PERMISSIONS' DataSource.
2024-10-02T17:51:38,760 DEBUG SQLExpressions Missing expression request.expressionFields: [
    "ID_REC",
    "ID_APP",
    "VOCE_MENU",
    "VOCE_MENU_EN",
    "ID_FUNZIONE",
    "ORDINAMENTO_PER_LIVELLO",
    "ID_VOCE_PADRE",
    "ICONA",
    "ICONA_SVG",
    "IS_MENU_FROM_ROLE",
    "IS_MENU_FROM_PERMISSION",
    "ID_MENU_FK",
    "ID_PERMISSIONS",
    "ID_ROLES",
    "IS_DASHBOARD_VISIBLE",
    "MOBILE_PHONE_VISIBLE",
    "TABLET_VISIBLE",
    "MOBILE_PHONE_DEFAULT",
    "MOBILE_PHONE_ONLY",
    "ROLE_NAMES",
    "PERMISSION_NAMES",
    "ID_ROLE_FK",
    "ID_PERMISSION_FK"
]
2024-10-02T17:51:38,760 DEBUG SQLExpressions Missing expression generated sqlExpressions.keySet: [
    "ID_REC",
    "ID_APP",
    "VOCE_MENU",
    "VOCE_MENU_EN",
    "ID_FUNZIONE",
    "ORDINAMENTO_PER_LIVELLO",
    "ID_VOCE_PADRE",
    "ICONA",
    "ICONA_SVG",
    "IS_MENU_FROM_ROLE",
    "IS_MENU_FROM_PERMISSION",
    "ID_MENU_FK",
    "ID_PERMISSIONS",
    "ID_ROLES",
    "IS_DASHBOARD_VISIBLE",
    "MOBILE_PHONE_VISIBLE",
    "TABLET_VISIBLE",
    "MOBILE_PHONE_DEFAULT",
    "MOBILE_PHONE_ONLY",
    "ROLE_NAMES",
    "PERMISSION_NAMES"
]
2024-10-02T17:51:38,761 DEBUG SQLExpressions Missing expression stack: java.lang.Throwable
    at com.isomorphic.sql.SQLExpressions.getExpression(SQLExpressions.java:173)
    at com.isomorphic.sql.SQLWhereClause.buildAdvancedExpression(SQLWhereClause.java:977)
    at com.isomorphic.sql.SQLWhereClause.getOutput(SQLWhereClause.java:419)
    at com.isomorphic.sql.SQLWhereClause.buildCompoundExpression(SQLWhereClause.java:507)
    at com.isomorphic.sql.SQLWhereClause.getOutput(SQLWhereClause.java:407)
    at com.isomorphic.sql.SQLWhereClause.buildCompoundExpression(SQLWhereClause.java:507)
    at com.isomorphic.sql.SQLWhereClause.getOutput(SQLWhereClause.java:407)
    at com.isomorphic.sql.SQLWhereClause.toString(SQLWhereClause.java:284)
    at com.isomorphic.sql.SQLWhereClause.getSQLString(SQLWhereClause.java:290)
    at com.isomorphic.sql.SQLDataSource.getClausesContext(SQLDataSource.java:4325)
2024-10-02T17:51:38,761 WARN SQLExpressions Missing expression for the 'ID_PERMISSION_FK' field of the 'AUTH_MENU_ROLES_PERMISSIONS' DataSource.
2024-10-02T17:51:38,761 DEBUG SQLExpressions Missing expression request.expressionFields: [
    "ID_REC",
    "ID_APP",
    "VOCE_MENU",
    "VOCE_MENU_EN",
    "ID_FUNZIONE",
    "ORDINAMENTO_PER_LIVELLO",
    "ID_VOCE_PADRE",
    "ICONA",
    "ICONA_SVG",
    "IS_MENU_FROM_ROLE",
    "IS_MENU_FROM_PERMISSION",
    "ID_MENU_FK",
    "ID_PERMISSIONS",
    "ID_ROLES",
    "IS_DASHBOARD_VISIBLE",
    "MOBILE_PHONE_VISIBLE",
    "TABLET_VISIBLE",
    "MOBILE_PHONE_DEFAULT",
    "MOBILE_PHONE_ONLY",
    "ROLE_NAMES",
    "PERMISSION_NAMES",
    "ID_ROLE_FK",
    "ID_PERMISSION_FK"
]
2024-10-02T17:51:38,761 DEBUG SQLExpressions Missing expression generated sqlExpressions.keySet: [
    "ID_REC",
    "ID_APP",
    "VOCE_MENU",
    "VOCE_MENU_EN",
    "ID_FUNZIONE",
    "ORDINAMENTO_PER_LIVELLO",
    "ID_VOCE_PADRE",
    "ICONA",
    "ICONA_SVG",
    "IS_MENU_FROM_ROLE",
    "IS_MENU_FROM_PERMISSION",
    "ID_MENU_FK",
    "ID_PERMISSIONS",
    "ID_ROLES",
    "IS_DASHBOARD_VISIBLE",
    "MOBILE_PHONE_VISIBLE",
    "TABLET_VISIBLE",
    "MOBILE_PHONE_DEFAULT",
    "MOBILE_PHONE_ONLY",
    "ROLE_NAMES",
    "PERMISSION_NAMES"
]
2024-10-02T17:51:38,761 DEBUG SQLExpressions Missing expression stack: java.lang.Throwable
    at com.isomorphic.sql.SQLExpressions.getExpression(SQLExpressions.java:173)
    at com.isomorphic.sql.SQLWhereClause.buildAdvancedExpression(SQLWhereClause.java:977)
    at com.isomorphic.sql.SQLWhereClause.getOutput(SQLWhereClause.java:419)
    at com.isomorphic.sql.SQLWhereClause.buildCompoundExpression(SQLWhereClause.java:507)
    at com.isomorphic.sql.SQLWhereClause.getOutput(SQLWhereClause.java:407)
    at com.isomorphic.sql.SQLWhereClause.buildCompoundExpression(SQLWhereClause.java:507)
    at com.isomorphic.sql.SQLWhereClause.getOutput(SQLWhereClause.java:407)
    at com.isomorphic.sql.SQLWhereClause.toString(SQLWhereClause.java:284)
    at com.isomorphic.sql.SQLWhereClause.getSQLString(SQLWhereClause.java:290)
    at com.isomorphic.sql.SQLDataSource.getClausesContext(SQLDataSource.java:4325)
2024-10-02T17:51:38,766 INFO SQL Executing SQL query on 'dbJFrame': SELECT TREE_MENU.ID_REC, TREE_MENU.ID_APP, TREE_MENU.VOCE_MENU, TREE_MENU.VOCE_MENU_EN, TREE_MENU.ID_FUNZIONE, TREE_MENU.ORDINAMENTO_PER_LIVELLO, TREE_MENU.ID_VOCE_PADRE, TREE_MENU.ICONA, TREE_MENU.ICONA_SVG,
                case
                    when (MENU_ROLES.ID_REC is not null) then 'T'
                    when (MENU_PERMISSIONS.ID_REC is not null) then 'F'
                    else null
                end
             AS IS_MENU_FROM_ROLE,
                case
                    when (MENU_PERMISSIONS.ID_REC is not null) then 'T'
                    when (MENU_ROLES.ID_REC is not null) then 'F'
                    else null
                end
             AS IS_MENU_FROM_PERMISSION, coalesce(MENU_PERMISSIONS.ID_MENU_FK, MENU_ROLES.ID_MENU_FK) AS ID_MENU_FK, MAX(CASE WHEN MENU_PERMISSIONS.TABLET_VISIBLE = 'T' OR MENU_ROLES.TABLET_VISIBLE = 'T' THEN 'T' ELSE 'F' END) AS TABLET_VISIBLE, MAX(CASE WHEN MENU_PERMISSIONS.MOBILE_PHONE_DEFAULT = 'T' OR MENU_ROLES.MOBILE_PHONE_DEFAULT = 'T' THEN 'T' ELSE 'F' END) AS MOBILE_PHONE_DEFAULT, LISTAGG ('' || MENU_ROLES.ID_ROLE_FK || '', ', ') WITHIN GROUP (ORDER BY MENU_ROLES.ID_ROLE_FK) AS ID_ROLES, MAX(CASE WHEN MENU_PERMISSIONS.MOBILE_PHONE_VISIBLE = 'T' OR MENU_ROLES.MOBILE_PHONE_VISIBLE = 'T' THEN 'T' ELSE 'F' END) AS MOBILE_PHONE_VISIBLE, LISTAGG ('' || ROLES.ROLE || '', ', ') WITHIN GROUP (ORDER BY ROLES.ROLE) AS ROLE_NAMES, MAX(CASE WHEN MENU_PERMISSIONS.IS_DASHBOARD_VISIBLE = 'T' OR MENU_ROLES.IS_DASHBOARD_VISIBLE = 'T' THEN 'T' ELSE 'F' END) AS IS_DASHBOARD_VISIBLE, MAX(CASE WHEN MENU_PERMISSIONS.MOBILE_PHONE_ONLY = 'T' OR MENU_ROLES.MOBILE_PHONE_ONLY = 'T' THEN 'T' ELSE 'F' END) AS MOBILE_PHONE_ONLY, LISTAGG ('' || MENU_PERMISSIONS.ID_PERMISSION_FK || '', ', ') WITHIN GROUP (ORDER BY MENU_PERMISSIONS.ID_PERMISSION_FK) AS ID_PERMISSIONS, LISTAGG ('' || PERMISSIONS.NAME || '', ', ') WITHIN GROUP (ORDER BY PERMISSIONS.NAME) AS PERMISSION_NAMES FROM
                DBJFRAME.TREE_MENU
                LEFT JOIN DBJFRAME.AUTH_MENU_ROLES MENU_ROLES ON TREE_MENU.ID_REC = MENU_ROLES.ID_MENU_FK
                LEFT JOIN DBJFRAME.AUTH_ROLES ROLES ON MENU_ROLES.ID_ROLE_FK = ROLES.ID_REC
                LEFT JOIN DBJFRAME.AUTH_MENU_PERMISSIONS MENU_PERMISSIONS ON TREE_MENU.ID_REC = MENU_PERMISSIONS.ID_MENU_FK
                LEFT JOIN DBJFRAME.AUTH_PERMISSIONS PERMISSIONS ON MENU_PERMISSIONS.ID_PERMISSION_FK = PERMISSIONS.ID_REC
             WHERE
                (('1'='1') AND (TREE_MENU.ID_APP = 'Jat' AND TREE_MENU.ID_APP IS NOT NULL) AND (('1'='1') OR ('1'='1')))
             GROUP BY TREE_MENU.ID_REC, TREE_MENU.ID_APP, TREE_MENU.VOCE_MENU, TREE_MENU.VOCE_MENU_EN, TREE_MENU.ID_FUNZIONE, TREE_MENU.ORDINAMENTO_PER_LIVELLO, TREE_MENU.ID_VOCE_PADRE, TREE_MENU.ICONA, TREE_MENU.ICONA_SVG,
                case
                    when (MENU_ROLES.ID_REC is not null) then 'T'
                    when (MENU_PERMISSIONS.ID_REC is not null) then 'F'
                    else null
                end
            ,
                case
                    when (MENU_PERMISSIONS.ID_REC is not null) then 'T'
                    when (MENU_ROLES.ID_REC is not null) then 'F'
                    else null
                end
            , coalesce(MENU_PERMISSIONS.ID_MENU_FK, MENU_ROLES.ID_MENU_FK) HAVING (('1'='1')) ORDER BY ORDINAMENTO_PER_LIVELLO
2024-10-02T17:51:38,938 INFO DSResponse DSResponse: List with 397 items
2024-10-02T17:51:38,948 INFO DSResponse DSResponse: List with 397 items
2024-10-02T17:51:38,949 DEBUG RPCManager Content type for RPC transaction: text/plain; charset=UTF-8
2024-10-02T17:51:38,949 DEBUG SQLTransaction Committing dbJFrame transaction "2113058690"
2024-10-02T17:51:38,953 DEBUG RPCManager non-DMI response, dropExtraFields: false
2024-10-02T17:51:38,953 DEBUG RPCManager DMI response, dropExtraFields: false
2024-10-02T17:51:38,953 DEBUG RPCManager DMI response, dropExtraFields: false
2024-10-02T17:51:38,954 DEBUG PoolManager Invalidating null object for key 'trimText
2024-10-02T17:51:38,954 DEBUG PoolManager Invalidating null object for key 'lowerTrimText
2024-10-02T17:51:38,988 DEBUG SQLTransaction getConnection() looked for transactional connection for dbJFrame: hashcode "2113058690"
2024-10-02T17:51:38,988 DEBUG SQLTransaction Ending dbJFrame transaction "2113058690"
2024-10-02T17:51:38,992 DEBUG SQLConnectionManager About to close connection with hashcode "2113058690"
2024-10-02T17:51:38,993 DEBUG PoolableSQLConnectionFactory Executing pingTest 'select 1 from dual' on connection 2113058690
2024-10-02T17:51:38,998 DEBUG SQLTransaction getConnection() looked for transactional connection for dbJFrame: (connection is null)
2024-10-02T17:51:38,998 DEBUG SQLTransaction getConnection() looked for transactional connection for dbJFrame: (connection is null)
2024-10-02T17:51:38,998 DEBUG SQLTransaction getConnection() looked for transactional connection for dbJFrame: (connection is null)
2024-10-02T17:51:38,998 DEBUG SQLTransaction getConnection() looked for transactional connection for dbJFrame: (connection is null)

[claudiobosticco]

Hello, any news on this issue?

Please let me know if you need more details and/or a test case

[claudiobosticco]

Hello, any updates would be appreciated. Please let me know if further details or a test case are needed.

[Isomorphic]

Apologies for the delay; there's been a lot going on, so this slipped off our radar. The test case would be greatly appreciated, as it would help us resolve this more quickly.

[claudiobosticco]

Hello, it seems that it's actually ignoring customCriteriaFields. Last build working seems to be SmartClient Version: SNAPSHOT_v13.1d_2024-08-05/Enterprise Deployment (built 2024-08-05), it doesn't work since 2024-08-07 (actually I can't download 2024-08-06 with maven right now)

I've reduced my ds to:

<DataSource xmlns:fmt="WEB-INF/" xmlns="http://www.smartclient.com/schema"
            ID="AUTH_MENU_ROLES_PERMISSIONS"
            dataFormat="iscServer"
            serverType="sql"
            dbName="dbJFrame"
            schema="DBJFRAME"
            tableName="TREE_MENU"
            useAnsiJoins="true"
>
    <fields>
        <field name="ID_REC" primaryKey="true" type="sequence" hidden="true" sequenceName="SEQUENCE_REC"/>
        <field name="ID_APP" type="text" required="true" length="20" hidden="true"/>
        <field name="ID_ROLE_FK" type="integer" customSQL="true"/>
    </fields>
    <operationBindings>
        <operationBinding operationType="fetch" customCriteriaFields="ID_ROLE_FK">
        </operationBinding>
    </operationBindings>
</DataSource>

and with a fetch like this:

AUTH_MENU_ROLES_PERMISSIONS.fetchData({
        operator:"and",
        criteria:[
            {
                fieldName:"ID_ROLE_FK",
                operator:"equals",
                value:16544
            },
            {
                fieldName:"ID_APP",
                operator:"equals",
                value:"Jat"
            }
        ]
    })

I got this log with SmartClient Version: SNAPSHOT_v13.1d_2024-08-07/Enterprise Deployment (built 2024-08-07) :

ott 21, 2024 5:56:07 PM org.apache.catalina.core.StandardWrapperValve invoke
INFORMAZIONI: 2024-10-21T17:56:07,215 DEBUG RPCManager Starting request parsing
2024-10-21T17:56:07,229 DEBUG PoolManager Invalidating null object for key 'transaction
2024-10-21T17:56:07,232 DEBUG PoolManager Invalidating null object for key 'elem
2024-10-21T17:56:07,233 DEBUG RPCManager Done request parsing
2024-10-21T17:56:07,233 DEBUG RPCManager Processing 1 requests.
2024-10-21T17:56:07,237 DEBUG RPCManager Request #1 (DSRequest) payload: {
    criteria:{
        operator:"and",
        criteria:[
            {
                fieldName:"ID_ROLE_FK",
                operator:"equals",
                value:16544
            },
            {
                fieldName:"ID_APP",
                operator:"equals",
                value:"Jat"
            }
        ]
    },
    operationConfig:{
        dataSource:"AUTH_MENU_ROLES_PERMISSIONS",
        repo:null,
        operationType:"fetch",
        textMatchStyle:"exact"
    },
    appID:"builtinApplication",
    operation:"fetchMenuPerGestioneUtenti",
    oldValues:{
        operator:"and",
        criteria:[
            {
                fieldName:"ID_ROLE_FK",
                operator:"equals",
                value:16544
            },
            {
                fieldName:"ID_APP",
                operator:"equals",
                value:"Jat"
            }
        ]
    },
    tenantId:null
}
2024-10-21T17:56:07,238 INFO IDACall Performing 1 operation(s) [UA: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:131.0) Gecko/20100101 Firefox/131.0]
2024-10-21T17:56:07,239 INFO DSRequest Executing AUTH_MENU_ROLES_PERMISSIONS.fetch rows: 0->-1 with
criteria: {
    operator:"and",
    criteria:[
        {fieldName:"ID_ROLE_FK", operator:"equals", value:16544},
        {fieldName:"ID_APP", operator:"equals", value:"Jat"}
    ]
}
2024-10-21T17:56:07,240 INFO AdvancedCriteria Criteria object:{
    operator:"and",
    criteria:[
        {
            fieldName:"ID_ROLE_FK",
            operator:"equals",
            value:16544
        },
        {
            fieldName:"ID_APP",
            operator:"equals",
            value:"Jat"
        }
    ]
} not explicitly marked as AdvancedCriteria but treating as Advanced based on format.
2024-10-21T17:56:07,240 INFO AdvancedCriteria Criteria object:{
    operator:"and",
    criteria:[
        {
            fieldName:"ID_ROLE_FK",
            operator:"equals",
            value:16544
        },
        {
            fieldName:"ID_APP",
            operator:"equals",
            value:"Jat"
        }
    ]
} not explicitly marked as AdvancedCriteria but treating as Advanced based on format.
2024-10-21T17:56:07,243 INFO DataSourceDMI operationBinding for DataSource: AUTH_MENU_ROLES_PERMISSIONS with operationType: fetch, operationId: fetchMenuPerGestioneUtenti does not have a <serverObject> in force and does not specify any operationBinding settings that could affect its behavior. This operation will almost certainly give the same result as a default operation, and so is almost certainly unnecessary
2024-10-21T17:56:07,244 DEBUG AppBase No userTypes defined, allowing anyone access to all operations for this application
2024-10-21T17:56:07,244 DEBUG AppBase No public zero-argument method named '_fetchMenuPerGestioneUtenti' found, performing generic datasource operation
2024-10-21T17:56:07,248 WARN SQLExpressions Missing expression for the 'ID_ROLE_FK' field of the 'AUTH_MENU_ROLES_PERMISSIONS' DataSource.
2024-10-21T17:56:07,255 DEBUG SQL 666: Executing SQL query on 'dbJFrame': SELECT TREE_MENU.ID_REC, TREE_MENU.ID_APP FROM DBJFRAME.TREE_MENU WHERE (('1'='1') AND (TREE_MENU.ID_APP = 'Jat' AND TREE_MENU.ID_APP IS NOT NULL))
2024-10-21T17:56:07,259 DEBUG PoolableSQLConnectionFactory Executing pingTest 'select 1 from dual' on connection 139334260
2024-10-21T17:56:07,273 DEBUG SQLConnectionManager Borrowed connection '139334260' from DBCP
2024-10-21T17:56:07,273 DEBUG SQLTransaction Started new dbJFrame transaction "139334260"
2024-10-21T17:56:07,273 INFO SQL Executing SQL query on 'dbJFrame': SELECT TREE_MENU.ID_REC, TREE_MENU.ID_APP FROM DBJFRAME.TREE_MENU WHERE (('1'='1') AND (TREE_MENU.ID_APP = 'Jat' AND TREE_MENU.ID_APP IS NOT NULL))
2024-10-21T17:56:07,459 INFO DSResponse DSResponse: List with 397 items
2024-10-21T17:56:07,461 DEBUG RPCManager Content type for RPC transaction: text/plain; charset=UTF-8
2024-10-21T17:56:07,461 DEBUG SQLTransaction Committing dbJFrame transaction "139334260"
2024-10-21T17:56:07,466 DEBUG RPCManager non-DMI response, dropExtraFields: false
2024-10-21T17:56:07,477 DEBUG SQLTransaction getConnection() looked for transactional connection for dbJFrame: hashcode "139334260"
2024-10-21T17:56:07,478 DEBUG SQLTransaction Ending dbJFrame transaction "139334260"
2024-10-21T17:56:07,482 DEBUG SQLConnectionManager About to close connection with hashcode "139334260"
2024-10-21T17:56:07,482 DEBUG PoolableSQLConnectionFactory Executing pingTest 'select 1 from dual' on connection 139334260

while with SmartClient Version: SNAPSHOT_v13.1d_2024-10-18/Enterprise Deployment (built 2024-10-18)

there's some extra logs:

ott 21, 2024 6:02:35 PM org.apache.catalina.core.StandardWrapperValve invoke
INFORMAZIONI: 2024-10-21T18:02:35,458 DEBUG RPCManager Starting request parsing
2024-10-21T18:02:35,463 DEBUG PoolManager Invalidating null object for key 'transaction
2024-10-21T18:02:35,464 DEBUG PoolManager Invalidating null object for key 'elem
2024-10-21T18:02:35,464 DEBUG RPCManager Done request parsing
2024-10-21T18:02:35,464 DEBUG RPCManager Processing 1 requests.
2024-10-21T18:02:35,468 DEBUG PoolManager Invalidating null object for key 'operationBinding
2024-10-21T18:02:35,471 DEBUG RPCManager Request #1 (DSRequest) payload: {
    criteria:{
        operator:"and",
        criteria:[
            {
                fieldName:"ID_ROLE_FK",
                operator:"equals",
                value:16544
            },
            {
                fieldName:"ID_APP",
                operator:"equals",
                value:"Jat"
            }
        ]
    },
    operationConfig:{
        dataSource:"AUTH_MENU_ROLES_PERMISSIONS",
        repo:null,
        operationType:"fetch",
        textMatchStyle:"exact"
    },
    appID:"builtinApplication",
    operation:"fetchMenuPerGestioneUtenti",
    oldValues:{
        operator:"and",
        criteria:[
            {
                fieldName:"ID_ROLE_FK",
                operator:"equals",
                value:16544
            },
            {
                fieldName:"ID_APP",
                operator:"equals",
                value:"Jat"
            }
        ]
    },
    tenantId:null
}
2024-10-21T18:02:35,471 INFO IDACall Performing 1 operation(s) [UA: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:131.0) Gecko/20100101 Firefox/131.0]
2024-10-21T18:02:35,472 INFO DSRequest Executing AUTH_MENU_ROLES_PERMISSIONS.fetch rows: 0->-1 with
criteria: {
    operator:"and",
    criteria:[
        {fieldName:"ID_ROLE_FK", operator:"equals", value:16544},
        {fieldName:"ID_APP", operator:"equals", value:"Jat"}
    ]
}
2024-10-21T18:02:35,473 INFO AdvancedCriteria Criteria object:{
    operator:"and",
    criteria:[
        {
            fieldName:"ID_ROLE_FK",
            operator:"equals",
            value:16544
        },
        {
            fieldName:"ID_APP",
            operator:"equals",
            value:"Jat"
        }
    ]
} not explicitly marked as AdvancedCriteria but treating as Advanced based on format.
2024-10-21T18:02:35,473 INFO AdvancedCriteria Criteria object:{
    operator:"and",
    criteria:[
        {
            fieldName:"ID_ROLE_FK",
            operator:"equals",
            value:16544
        },
        {
            fieldName:"ID_APP",
            operator:"equals",
            value:"Jat"
        }
    ]
} not explicitly marked as AdvancedCriteria but treating as Advanced based on format.
2024-10-21T18:02:35,475 INFO DataSourceDMI operationBinding for DataSource: AUTH_MENU_ROLES_PERMISSIONS with operationType: fetch, operationId: fetchMenuPerGestioneUtenti does not have a <serverObject> in force and does not specify any operationBinding settings that could affect its behavior. This operation will almost certainly give the same result as a default operation, and so is almost certainly unnecessary
2024-10-21T18:02:35,475 DEBUG AppBase No userTypes defined, allowing anyone access to all operations for this application
2024-10-21T18:02:35,475 DEBUG AppBase No public zero-argument method named '_fetchMenuPerGestioneUtenti' found, performing generic datasource operation
2024-10-21T18:02:35,477 WARN SQLExpressions Missing expression for the 'ID_ROLE_FK' field of the 'AUTH_MENU_ROLES_PERMISSIONS' DataSource.
2024-10-21T18:02:35,478 DEBUG SQLExpressions Missing expression request.expressionFields: [
    "ID_REC",
    "ID_APP",
    "ID_ROLE_FK"
]
2024-10-21T18:02:35,478 DEBUG SQLExpressions Missing expression generated sqlExpressions.keySet: [
    "ID_REC",
    "ID_APP"
]
2024-10-21T18:02:35,479 DEBUG SQLExpressions Missing expression stack: java.lang.Throwable
    at com.isomorphic.sql.SQLExpressions.getExpression(SQLExpressions.java:185)
    at com.isomorphic.sql.SQLWhereClause.buildAdvancedExpression(SQLWhereClause.java:1029)
    at com.isomorphic.sql.SQLWhereClause.getOutput(SQLWhereClause.java:419)
    at com.isomorphic.sql.SQLWhereClause.buildCompoundExpression(SQLWhereClause.java:507)
    at com.isomorphic.sql.SQLWhereClause.getOutput(SQLWhereClause.java:407)
    at com.isomorphic.sql.SQLWhereClause.toString(SQLWhereClause.java:284)
    at com.isomorphic.sql.SQLWhereClause.getSQLString(SQLWhereClause.java:290)
    at com.isomorphic.sql.SQLDataSource.getClausesContext(SQLDataSource.java:4351)
    at com.isomorphic.sql.SQLDataSource.SQLExecute(SQLDataSource.java:1845)
    at com.isomorphic.sql.SQLDataSource.SQLExecute(SQLDataSource.java:1745)
2024-10-21T18:02:35,482 DEBUG SQL 655: Executing SQL query on 'dbJFrame': SELECT TREE_MENU.ID_REC, TREE_MENU.ID_APP FROM DBJFRAME.TREE_MENU WHERE (('1'='1') AND (TREE_MENU.ID_APP = 'Jat' AND TREE_MENU.ID_APP IS NOT NULL))
2024-10-21T18:02:35,482 DEBUG PoolableSQLConnectionFactory Executing pingTest 'select 1 from dual' on connection 1010252117
2024-10-21T18:02:35,487 DEBUG SQLConnectionManager Borrowed connection '1010252117' from DBCP
2024-10-21T18:02:35,487 DEBUG SQLTransaction Started new dbJFrame transaction "1010252117"
2024-10-21T18:02:35,487 INFO SQL Executing SQL query on 'dbJFrame': SELECT TREE_MENU.ID_REC, TREE_MENU.ID_APP FROM DBJFRAME.TREE_MENU WHERE (('1'='1') AND (TREE_MENU.ID_APP = 'Jat' AND TREE_MENU.ID_APP IS NOT NULL))
2024-10-21T18:02:35,662 INFO DSResponse DSResponse: List with 397 items
2024-10-21T18:02:35,663 DEBUG RPCManager Content type for RPC transaction: text/plain; charset=UTF-8
2024-10-21T18:02:35,663 DEBUG SQLTransaction Committing dbJFrame transaction "1010252117"
2024-10-21T18:02:35,666 DEBUG RPCManager non-DMI response, dropExtraFields: false
2024-10-21T18:02:35,674 DEBUG SQLTransaction getConnection() looked for transactional connection for dbJFrame: hashcode "1010252117"
2024-10-21T18:02:35,674 DEBUG SQLTransaction Ending dbJFrame transaction "1010252117"
2024-10-21T18:02:35,678 DEBUG SQLConnectionManager About to close connection with hashcode "1010252117"
2024-10-21T18:02:35,678 DEBUG PoolableSQLConnectionFactory Executing pingTest 'select 1 from dual' on connection 1010252117

[claudiobosticco]

Hello, please let me know if you see the problem or if you need more details (or if I'm missing something obvious)

[Isomorphic]

Hi Claudio
This is currently with a developer for analysis. Nothing to report yet but we'll get you a proper status update when we have one

Regards
Isomorphic Software