Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
Clear All
new posts

    customSelectExpression with joins and summaryFunctions: bug?

    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:
    Code:
    <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>
    Code:
    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:
    Code:
    === 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-&gt;moneyTransfer.fetch rows: 0-&gt;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 -&gt; 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:
    Code:
    === 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-&gt;moneyTransfer.fetch rows: 0-&gt;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 -&gt; 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:
    Code:
    === 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-&gt;moneyTransfer.fetch rows: 0-&gt;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 -&gt; 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)

    #2
    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:
    Code:
    <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.

    Comment


      #3
      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.

      Comment

      Working...
      X