customSelectExpression with joins and summaryFunctions: bug?

[claudiobosticco]

SmartClient Version: SNAPSHOT_v13.0d_2021-11-14/Enterprise Development Only (built 2021-11-14)
SmartClient Version: v12.1p_2021-11-13/Enterprise Development Only (built 2021-11-13)

Hello, I think I have found a bug, when there's a customSelectExpression (which uses aliases generated when joining to include fields) and I use a summary function.
It seems that the joins aren't always included when filtering.
I've put together a test case based on the sqlIncludeVia sample.

moneyTransfer.ds.xml:

<DataSource isSampleDS="true"
    ID="moneyTransfer"
    serverType="sql"
    tableName="moneyTransfer"
    >
    <fields>
        <field name="id" type="integer" primaryKey="true" />
        <field name="name" type="text" />
        <field name="sourceCurrencyId" foreignKey="currency.id" relatedTableAlias="source"/>
        <field name="sourceCurrencySymbol" includeFrom="currency.symbol" includeVia="sourceCurrencyId" />
        <field name="paymentAmount" type="float" />
        <field name="paymentCurrencyId" foreignKey="currency.id" relatedTableAlias="payment" />
        <field name="paymentCurrencySymbol" includeFrom="currency.symbol" includeVia="paymentCurrencyId" />

        <field name="sourceCode_symbol" type="text" customSelectExpression="CONCAT(source.code,' - ',source.symbol)" />
    </fields>
</DataSource>

isc.ListGrid.create({
    ID: "moneyTransferList",
    dataSource: moneyTransfer,
    width: 700,
    height: 224,
    showGridSummary: true,
    showFilterEditor: true,
    alternateRecordStyles: true,
    autoFetchData: true,
    dataPageSize: 50,
    canEdit: true,
    editEvent: "click",
    canRemoveRecords: true,
    filterOnKeypress: true,
    filterLocalData: false,
    summaryRowDataSource: "moneyTransfer",
    summaryRowFetchRequestProperties: {
        summaryFunctions: {
            "name": "count"
        }
    },
    fields: [
        { name: "name" },
        { name: "paymentAmount" },
        {
            name: "sourceCode_symbol",
            filterEditorProperties: {
                optionDataSource: "currency",
                displayField: "symbol",
                valueField: "symbol",
                editorType: "SelectItem",
                textMatchStyle: "substring",
                sortField: "code_symbol"
            }
        },
        {
            name: "paymentCurrencySymbol",
            width: 170,
            filterEditorProperties: {
                optionDataSource: "currency",
                displayField: "symbol",
                valueField: "symbol",
                editorType: "SelectItem",
                textMatchStyle: "substring",
                sortField: "symbol"
            }
        }
    ]
});

If you run it, you'll see a (working) summary fetch, without filtering ,which doesn't make any join:

=== 2021-11-15 10:18:43,053 [c-10] INFO RequestContext - URL: '/isomorphic/IDACall', User-Agent: 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/95.0.4638.69 Safari/537.36': Safari with Accept-Encoding header
=== 2021-11-15 10:18:43,097 [c-10] 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/95.0.4638.69 Safari/537.36]
=== 2021-11-15 10:18:43,097 [c-10] INFO DSRequest - Executing moneyTransferList_summaryRow->moneyTransfer.fetch rows: 0->1 with
criteria: {}
=== 2021-11-15 10:18:43,098 [c-10] INFO SQLWhereClause - empty condition
=== 2021-11-15 10:18:43,099 [c-10] INFO SQLDataSource - 1163: Executing query on 'HSQLDB':
    SELECT
        LIMIT 0 1 COUNT(moneyTransfer.name) AS name
    FROM
        moneyTransfer
    WHERE
        (
            '1'='1'
        )
=== 2021-11-15 10:18:43,100 [c-10] INFO DSResponse - DSResponse: List with 1 items
=== 2021-11-15 10:18:43,100 [c-10] INFO Compression - /isomorphic/IDACall: 198 -> 165
=== 2021-11-15 10:18:44,064 [ec-6] INFO RequestContext - URL: '/isomorphic/system/reference/skin/images/opener_opened.png', User-Agent: 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/95.0.4638.69 Safari/537.36': Safari with Accept-Encoding header
=== 2021-11-15 10:18:44,065 [ec-6] INFO Download - File /Users/bosticco/librerie/isc/SmartClient_v121p_2021-11-13_Enterprise/smartclientSDK/isomorphic/system/reference/skin/images/opener_opened.png not found, sending 404

Then, if you filter on the last column (paymentCurrencySymbol) ie select '€' in the filterEditor, you'll see a summary fetch with the only necessary join:

=== 2021-11-15 10:20:01,668 [ec-3] INFO RequestContext - URL: '/isomorphic/IDACall', User-Agent: 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/95.0.4638.69 Safari/537.36': Safari with Accept-Encoding header
=== 2021-11-15 10:20:01,707 [ec-3] 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/95.0.4638.69 Safari/537.36]
=== 2021-11-15 10:20:01,707 [ec-3] INFO DSRequest - Executing moneyTransferList_summaryRow->moneyTransfer.fetch rows: 0->1 with
criteria: {paymentCurrencySymbol:"€"}
=== 2021-11-15 10:20:01,710 [ec-3] INFO SQLDataSource - 1163: Executing query on 'HSQLDB':
    SELECT
        LIMIT 0 1 COUNT(moneyTransfer.name) AS name
    FROM
        moneyTransfer, currency payment
    WHERE
        (
            LOWER(payment.symbol) LIKE '%€%' ESCAPE '\'
            AND payment.symbol IS NOT NULL
        )
        AND moneyTransfer.paymentCurrencyId = payment.id
=== 2021-11-15 10:20:01,711 [ec-3] INFO DSResponse - DSResponse: List with 1 items
=== 2021-11-15 10:20:01,713 [ec-3] INFO Compression - /isomorphic/IDACall: 198 -> 164
=== 2021-11-15 10:20:02,071 [ec-6] INFO RequestContext - URL: '/isomorphic/system/reference/skin/images/opener_opened.png', User-Agent: 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/95.0.4638.69 Safari/537.36': Safari with Accept-Encoding header
=== 2021-11-15 10:20:02,072 [ec-6] INFO Download - File /Users/bosticco/librerie/isc/SmartClient_v121p_2021-11-13_Enterprise/smartclientSDK/isomorphic/system/reference/skin/images/opener_opened.png not found, sending 404

But if you filter on the sourceCode_symbol, you'll see a summary fetch error because it lacks the necessary join:

=== 2021-11-15 10:21:27,511 [ec-8] INFO RequestContext - URL: '/isomorphic/IDACall', User-Agent: 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/95.0.4638.69 Safari/537.36': Safari with Accept-Encoding header
=== 2021-11-15 10:21:27,514 [ec-8] 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/95.0.4638.69 Safari/537.36]
=== 2021-11-15 10:21:27,514 [ec-8] INFO DSRequest - Executing moneyTransferList_summaryRow->moneyTransfer.fetch rows: 0->1 with
criteria: {sourceCode_symbol:"€"}
=== 2021-11-15 10:21:27,515 [ec-8] INFO SQLDataSource - 1163: Executing query on 'HSQLDB':
    SELECT
        LIMIT 0 1 COUNT(moneyTransfer.name) AS name
    FROM
        moneyTransfer
    WHERE
        (
            LOWER(CONCAT(source.code,' - ',source.symbol)) LIKE '%€%' ESCAPE '\'
            AND CONCAT(source.code,' - ',source.symbol) IS NOT NULL
        )
=== 2021-11-15 10:21:27,517 [ec-8] WARN RequestContext - dsRequest.execute() failed:
java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: SOURCE.CODE
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.executeQuery(Unknown Source)
    at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
    at com.isomorphic.sql.SQLDataSource.executeWindowedSelect(SQLDataSource.java:2583)
    at com.isomorphic.sql.SQLDataSource.SQLExecute(SQLDataSource.java:1812)
    at com.isomorphic.sql.SQLDataSource.processRequest(SQLDataSource.java:441)
    at com.isomorphic.sql.SQLDataSource.executeFetch(SQLDataSource.java:386)
    at com.isomorphic.datasource.DataSource.execute(DataSource.java:2499)
    at com.isomorphic.application.AppBase.executeDefaultDSOperation(AppBase.java:658)
    at com.isomorphic.application.AppBase.executeAppOperation(AppBase.java:555)
    at com.isomorphic.application.AppBase.execute(AppBase.java:498)
    at com.isomorphic.datasource.DSRequest.execute(DSRequest.java:3071)
    at com.isomorphic.servlet.IDACall.handleDSRequest(IDACall.java:226)
    at com.isomorphic.servlet.IDACall.processRPCTransaction(IDACall.java:183)
    at com.isomorphic.servlet.IDACall.processRequest(IDACall.java:148)
    at com.isomorphic.servlet.IDACall._processRequest(IDACall.java:119)
    at com.isomorphic.servlet.IDACall.doPost(IDACall.java:79)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:681)
    at com.isomorphic.servlet.BaseServlet.service(BaseServlet.java:178)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:764)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at com.isomorphic.servlet.URIRegexFilter.ignore(URIRegexFilter.java:483)
    at com.isomorphic.servlet.URIRegexFilter.ignore(URIRegexFilter.java:478)
    at com.isomorphic.servlet.URIRegexFilter.matchedRule(URIRegexFilter.java:441)
    at com.isomorphic.servlet.URIRegexFilter._doFilter(URIRegexFilter.java:349)
    at com.isomorphic.servlet.BaseFilter.doFilter(BaseFilter.java:91)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at com.isomorphic.js.JSSyntaxScannerFilter._doFilter(JSSyntaxScannerFilter.java:262)
    at com.isomorphic.servlet.BaseFilter.doFilter(BaseFilter.java:91)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at com.isomorphic.servlet.CompressionFilter._doFilter(CompressionFilter.java:263)
    at com.isomorphic.servlet.BaseFilter.doFilter(BaseFilter.java:91)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:196)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:542)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:364)
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:624)
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:831)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1650)
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
    at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
    at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:748)
Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: SOURCE.CODE
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.ExpressionColumn.checkColumnsResolved(Unknown Source)
    at org.hsqldb.QueryExpression.resolve(Unknown Source)
    at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source)
    at org.hsqldb.ParserCommand.compilePart(Unknown Source)
    at org.hsqldb.ParserCommand.compileStatements(Unknown Source)
    at org.hsqldb.Session.executeDirectStatement(Unknown Source)
    at org.hsqldb.Session.execute(Unknown Source)
    ... 56 more
=== 2021-11-15 10:21:27,519 [ec-8] INFO Compression - /isomorphic/IDACall: 189 -> 166
=== 2021-11-15 10:21:28,074 [ec-9] INFO RequestContext - URL: '/isomorphic/system/reference/skin/images/opener_opened.png', User-Agent: 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/95.0.4638.69 Safari/537.36': Safari with Accept-Encoding header
=== 2021-11-15 10:21:28,075 [ec-9] INFO Download - File /Users/bosticco/librerie/isc/SmartClient_v121p_2021-11-13_Enterprise/smartclientSDK/isomorphic/system/reference/skin/images/opener_opened.png not found, sending 404

In the past it was working, ie this works:
SmartClient Version: v12.1p_2021-03-21/Enterprise Development Only (built 2021-03-21)

and this version doesn't work:
SmartClient Version: v12.1p_2021-03-25/Enterprise Development Only (built 2021-03-25)

[Isomorphic]

The Smartclient SQL engine generally seeks to generate the bare minimum of SQL joins necessary to get the requested data for the efficiency. We've made some optimizations that changed this behavior in the builds since March 2021. So, as we also explain in this thread, this is expected behavior.

As also explained in the thread mentioned above, there's option of mixed includeFrom + customSelectExpression usage, so that SQL joins would be triggered by the includeFrom attribute, but still the customSelectExpression would be used in the select clause. In your case that would also require the includeVia setting:

<field name="sourceCode_symbol" type="text" customSelectExpression="CONCAT(source.code,' - ',source.symbol)"
             includeFrom="currency.symbol" includeVia="sourceCurrencyId" />

There's also a possibility to request dedicated attribute for this via Feature Sponsorship. An abstract idea was suggested in the same thread, i.e. introduce something like "DSField.dependencies" attribute listing all relations needed for the custom SQL.

[claudiobosticco]

Hello, thank you for the explanation.

I noticed the change in behaviour as updating SmartClient broke some query in my application, but the suggested workaround works for my use case, thank you very much.

[claudiobosticco]

Hello, I've got some (possibly many) operationBindings where I do something like

<ansiJoinClause>
                $defaultAnsiJoinClause
                -- some other custom ansi join
</ansiJoinClause>

and when I use it with setSummaryFunctions it breaks (because of the optimizations) because it's printing the string $defaultAnsiJoinClause in the generated SQL.
As the defaultAnsiJoinClause is actually empty, I was expecting the framework to print an empty string. Instead this seems an even more disruptive change.
Is it actually intended behaviour?

[Isomorphic]

This was not intended, it is fixed now and available for download in nightly builds since Jan 04 (today). In the absence of automatic joins $defaultAnsiJoinClause variable will result in empty string, so it won't break the SQL.

[claudiobosticco]

v12.1p_2022-01-04/Enterprise Deployment (2022-01-04)

I can confirm it's fixed, thank you very much.