Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
Clear All
new posts
Previous template Next
    #1

    bug with summaryFunctions & groupBy

    SmartClient Version: v13.0p_2023-07-23/Enterprise Deployment (built 2023-07-23)

    Oracle 19c

    Hello, I think I've found a bug using summaryFunctions & groupBy with a configuration that I'm unsure if it's valid/legal, and I think it could be better a different approach, but nevertheless I don't think it's nice how it's failing and so I'm reporting it. This is the complete log:

    Code:
    2023-07-31T11:12:46,330 DEBUG RPCManager Request #1 (DSRequest) payload: {
    criteria:{
        APP_ID:"JcaAuth",
        ROLES:[
            "ROLE_ADMIN"
        ]
    },
    operationConfig:{
        dataSource:"AUTH_USERS_ROLES_EXT",
        repo:null,
        operationType:"fetch",
        textMatchStyle:"exact"
    },
    sortBy:[
        "EMAIL"
    ],
    componentId:"elencoUtentiGrid",
    appID:"builtinApplication",
    operation:"fetchUsersGroupByRoles",
    oldValues:{
        APP_ID:"JcaAuth",
        ROLES:[
            "ROLE_ADMIN"
        ]
    }
}
2023-07-31T11:12:46,330 INFO IDACall Performing 1 operation(s) [UA: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/115.0.0.0 Safari/537.36]
2023-07-31T11:12:46,330 INFO DSRequest Executing elencoUtentiGrid->AUTH_USERS_ROLES_EXT.fetch[fetchUsersGroupByRoles] rows: 0->-1 with
criteria: {
    APP_ID:"JcaAuth",
    ROLES:["ROLE_ADMIN"]
}
sortBy: [EMAIL]
2023-07-31T11:12:46,332 DEBUG AppBase No userTypes defined, allowing anyone access to all operations for this application
2023-07-31T11:12:46,332 DEBUG AppBase No public zero-argument method named '_fetchUsersGroupByRoles' found, performing generic datasource operation
2023-07-31T11:12:46,334 DEBUG SQLDataSource DataSource 19 acquired SQLDriver instance 783063289 during initialization
2023-07-31T11:12:46,336 DEBUG SQLDataSource DataSource 25 acquired SQLDriver instance 150856410 during initialization
2023-07-31T11:12:46,336 DEBUG SQLDataSource DataSource 27 acquired SQLDriver instance 397899313 during initialization
2023-07-31T11:12:46,339 DEBUG SQLDataSource DataSource 20 acquired SQLDriver instance 1158145273 during initialization
2023-07-31T11:12:46,343 DEBUG SQLDataSource 19: Executing SQL query on 'dbJFrame': SELECT ROLES_APPS.APP_ID, USERS.EMAIL FROM DBJFRAME.AUTH_USERS_ROLES JOIN DBJFRAME.AUTH_ROLES ROLES ON AUTH_USERS_ROLES.ID_ROLE_FK = ROLES.ID_REC JOIN DBJFRAME.AUTH_APPS ROLES_APPS ON ROLES.ID_APP_FK = ROLES_APPS.ID_REC JOIN DBJFRAME.AUTH_USERS USERS ON AUTH_USERS_ROLES.ID_USER_FK = USERS.ID_REC WHERE (LOWER(ROLES_APPS.APP_ID)='jcaauth') GROUP BY ROLES_APPS.APP_ID, USERS.EMAIL HAVING (((ROLES.ROLE))) ORDER BY EMAIL
2023-07-31T11:12:46,415 DEBUG PoolableSQLConnectionFactory makeObject() created an unpooled Connection '1058475363'
2023-07-31T11:12:46,415 DEBUG SQLConnectionManager Borrowed connection '1058475363'
2023-07-31T11:12:46,415 DEBUG SQLTransaction Started new dbJFrame transaction "1058475363"
2023-07-31T11:12:46,415 DEBUG SQLDataSource Setting DSRequest as being part of a transaction on connection 1058475363
2023-07-31T11:12:46,415 INFO SQLDriver Executing SQL query on 'dbJFrame' using connection '1058475363': SELECT ROLES_APPS.APP_ID, USERS.EMAIL FROM DBJFRAME.AUTH_USERS_ROLES JOIN DBJFRAME.AUTH_ROLES ROLES ON AUTH_USERS_ROLES.ID_ROLE_FK = ROLES.ID_REC JOIN DBJFRAME.AUTH_APPS ROLES_APPS ON ROLES.ID_APP_FK = ROLES_APPS.ID_REC JOIN DBJFRAME.AUTH_USERS USERS ON AUTH_USERS_ROLES.ID_USER_FK = USERS.ID_REC WHERE (LOWER(ROLES_APPS.APP_ID)='jcaauth') GROUP BY ROLES_APPS.APP_ID, USERS.EMAIL HAVING (((ROLES.ROLE))) ORDER BY EMAIL
2023-07-31T11:12:46,419 DEBUG DSRequest freeOnExecute is false for request of type fetch on DataSource AUTH_USERS_ROLES_EXT - not freeing resources!
2023-07-31T11:12:46,419 WARN RequestContext dsRequest.execute() failed:
java.sql.SQLSyntaxErrorException: ORA-00920: operatore relazionale non valido
    these are the involved dataSources:

    Code:
    <DataSource xmlns:fmt="WEB-INF/" xmlns="http://www.smartclient.com/schema"
            ID="AUTH_USERS_ROLES_EXT"
            serverType="sql"
            dbName="dbJFrame"
            schema="DBJFRAME"
            tableName="AUTH_USERS_ROLES"
            allowAdvancedCriteria="true"
            useAnsiJoins="true"
            requiresAuthentication="true"
            dropExtraFields="false">
    <fmt:bundle basename="i18nMessages" encoding="utf-8"/>
    <fields>
        <field name="ID_ROLE_FK" type="integer" foreignKey="AUTH_ROLES_EXT.ID_REC" relatedTableAlias="ROLES" required="true"/>
        <field name="ID_USER_FK" type="integer" foreignKey="AUTH_USERS_EXT.ID_REC" relatedTableAlias="USERS" required="true"/>
        <field name="APP_ID" type="text" includeFrom="AUTH_ROLES_EXT.AUTH_APPS_EXT.APP_ID" includeVia="ID_ROLE_FK"/>
        <field name="EMAIL" type="text" includeFrom="AUTH_USERS_EXT.EMAIL" includeVia="ID_USER_FK"/>

        <field name="ROLES" type="text" customCriteriaExpression="ROLES.ROLE"/>
    </fields>
    <operationBindings>
        <operationBinding operationType="fetch" operationId="fetchUsersGroupByRoles" outputs="EMAIL,APP_ID">
            <summaryFunctions>
                <ROLES>concat</ROLES>
            </summaryFunctions>
            <groupBy>EMAIL,APP_ID</groupBy>
        </operationBinding>
    </operationBindings>
</DataSource>

<DataSource xmlns:fmt="WEB-INF/" xmlns="http://www.smartclient.com/schema"
            ID="AUTH_USERS_EXT"
            serverType="sql"
            dbName="dbJFrame"
            schema="DBJFRAME"
            tableName="AUTH_USERS"
            allowAdvancedCriteria="true"
            useAnsiJoins="true"
            requiresAuthentication="true"
            dropExtraFields="false"
>
    <fmt:bundle basename="i18nMessages" encoding="utf-8"/>
    <fields>
        <field name="ID_REC" primaryKey="true" type="sequence" hidden="true" sequenceName="SEQUENCE_AUTH"/>
        <field name="EMAIL" type="trimText" length="200" required="true"/>
    </fields>
</DataSource>

<DataSource xmlns:fmt="WEB-INF/" xmlns="http://www.smartclient.com/schema"
            ID="AUTH_ROLES_EXT"
            serverType="sql"
            dbName="dbJFrame"
            schema="DBJFRAME"
            tableName="AUTH_ROLES"
            allowAdvancedCriteria="true"
            useAnsiJoins="true"
            requiresAuthentication="true"
            dropExtraFields="false"
>
    <fmt:bundle basename="i18nMessages" encoding="utf-8"/>
    <fields>
        <field name="ID_REC" primaryKey="true" type="sequence" hidden="true" sequenceName="SEQUENCE_AUTH"/>
        <field name="ROLE" type="trimText" length="200" required="true"/>
        <field name="ID_APP_FK" type="integer" foreignKey="AUTH_APPS_EXT.ID_REC" relatedTableAlias="APPS" required="true"/>
        <field name="APP_ID" type="text" includeFrom="AUTH_APPS_EXT.APP_ID" includeVia="ID_APP_FK"/>
    </fields>
</DataSource>

<DataSource xmlns:fmt="WEB-INF/" xmlns="http://www.smartclient.com/schema"
            ID="AUTH_APPS_EXT"
            serverType="sql"
            dbName="dbJFrame"
            schema="DBJFRAME"
            tableName="AUTH_APPS"
            allowAdvancedCriteria="true"
            useAnsiJoins="true"
            requiresAuthentication="true"
            dropExtraFields="false"
>
    <fmt:bundle basename="i18nMessages" encoding="utf-8"/>
    <fields>
        <field name="ID_REC" primaryKey="true" type="sequence" hidden="true" sequenceName="SEQUENCE_AUTH"/>
        <field name="APP_ID" type="trimText" length="100" required="true"/>
    </fields>
</DataSource>
    Please let me know if you need more details
    Last edited by claudiobosticco; 31 Jul 2023, 02:23.
    Tags: None
    #2
    This error comes from the database and is caused by invalid expression in the HAVING clause:
    Code:
    HAVING (((ROLES.ROLE)))
    which is injected into the generated SQL directly from the customCriteriaExpression of the "ROLES" field. From the Smartclient point of view this is not something we inspect and validate as everything Custom SQL related.

    That said, is it possible that you intended to use customSelectExpression instead of customCriteriaExpression? Cause then we would generated valid SQL for showcased criteria.

    Note! specifically Oracle concat function usage had a bug in this area, which is now fixed, so if our assumption regarding customSelectExpression is correct, please make sure you download the 2023-08-16 nightly build before trying it out.

    Comment

      #3
      SmartClient Version: v13.0p_2023-09-06/Enterprise Deployment (built 2023-09-06)

      Hello, sorry for the late reply.
      In fact it's possible that I had run into the bug related to the CONCAT function in ORACLE, but instead of noticing that, I tried to make it work by using the customCriteriaExpression in the wrong way.

      Now using customSelectExpression I see it's working fine, thank you very much!

      Comment

      Previous template Next
      Working...
      X