summaryRow problem

[claudiobosticco]

SmartClient Version: v13.0p_2024-11-19/Enterprise Development Only (built 2024-11-19)
and
SmartClient Version: v13.1p_2024-11-19/Enterprise Development Only (built 2024-11-19)

Hello, please try this modified sample in the showcase (fetchOperationFS):

<DataSource isSampleDS="true"
    ID="supplyItem"
    serverType="sql"
    tableName="supplyItem"
    titleField="itemName"
    testFileName="/examples/shared/ds/test_data/supplyItem.data.xml"
    dbImportFileName="/examples/shared/ds/test_data/supplyItemLarge.data.xml"
>
    <fields>
        <field name="itemID" type="sequence" hidden="true" primaryKey="true"/>
        <field name="itemName" type="text" title="Item" length="128" required="true"/>
        <field name="SKU" type="text" title="SKU" length="10" required="true"/>
        <field name="description" type="text" title="Description" length="2000"/>
        <field name="category" type="text" title="Category" length="128" required="true"
               foreignKey="supplyCategory.categoryName"/>
        <field name="units" type="enum" title="Units" length="5">
            <valueMap>
                <value>Roll</value>
                <value>Ea</value>
                <value>Pkt</value>
                <value>Set</value>
                <value>Tube</value>
                <value>Pad</value>
                <value>Ream</value>
                <value>Tin</value>
                <value>Bag</value>
                <value>Ctn</value>
                <value>Box</value>
            </valueMap>
        </field>
        <field name="unitCost" type="float" title="Unit Cost" required="true">
            <validators>
                <validator type="floatRange" min="0" errorMessage="Please enter a valid (positive) cost"/>
                <validator type="floatPrecision" precision="2" errorMessage="The maximum allowed precision is 2"/>
            </validators>
        </field>
        <field name="inStock" type="boolean" title="In Stock"/>
        <field name="nextShipment" type="date" title="Next Shipment"/>
    </fields>
       <operationBindings>
       <operationBinding operationType="fetch" operationId="summary">
            <summaryFunctions>
                <unitCost>sum</unitCost>
                <itemName>count</itemName>
            </summaryFunctions>
        </operationBinding>
    </operationBindings>
</DataSource>

isc.ListGrid.create({
    ID: "countryList",
    height:"100%", width:"100%", alternateRecordStyles:true,
    dataSource: supplyItem,
    showFilterEditor:true,
    showGridSummary: true,
    summaryRowDataSource: supplyItem,
    summaryRowFetchRequestProperties: {
        operationId: "summary"
    },
    autoFetchData:true
})

then try to filter by typing "add" in the itemName column, you'll see this error:

=== 2024-11-20 09:47:12,285 [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/130.0.0.0 Safari/537.36': Safari with Accept-Encoding header
=== 2024-11-20 09:47:12,294 [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/130.0.0.0 Safari/537.36]
=== 2024-11-20 09:47:12,295 [ec-8] INFO DSRequest - Executing countryList->supplyItem.fetch rows: 0->75 with
criteria: {itemName:"add"}
=== 2024-11-20 09:47:12,298 [ec-8] INFO SQL - Executing SQL query on 'HSQLDB': SELECT COUNT(*) FROM supplyItem WHERE (LOWER(supplyItem.itemName) LIKE '%add%' ESCAPE '\' AND supplyItem.itemName IS NOT NULL)
=== 2024-11-20 09:47:12,313 [ec-2] 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/130.0.0.0 Safari/537.36': Safari with Accept-Encoding header
=== 2024-11-20 09:47:12,318 [ec-2] 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/130.0.0.0 Safari/537.36]
=== 2024-11-20 09:47:12,318 [ec-2] INFO DSRequest - Executing countryList_summaryRow->supplyItem.fetch[summary] rows: 0->1 with
criteria: {itemName:"add"}
=== 2024-11-20 09:47:12,319 [ec-2] INFO SQLWhereClause - empty condition
=== 2024-11-20 09:47:12,320 [ec-2] INFO SQL - 521: Executing query on 'HSQLDB': SELECT LIMIT 0 1 COUNT(supplyItem.itemName) AS itemName, SUM(supplyItem.unitCost) AS unitCost FROM supplyItem WHERE ('1'='1') HAVING (LOWER(COUNT(supplyItem.itemName)) LIKE '%add%' ESCAPE '\' AND COUNT(supplyItem.itemName) IS NOT NULL)
=== 2024-11-20 09:47:12,326 [ec-2] WARN RequestContext - dsRequest.execute() failed:
java.sql.SQLSyntaxErrorException: incompatible data type in operation
    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:2813)
    at com.isomorphic.sql.SQLDataSource.SQLExecute(SQLDataSource.java:1893)
    at com.isomorphic.sql.SQLDataSource.SQLExecute(SQLDataSource.java:1727)
    at com.isomorphic.sql.SQLDataSource.processRequest(SQLDataSource.java:522)
    at com.isomorphic.sql.SQLDataSource.executeFetch(SQLDataSource.java:458)
    at com.isomorphic.datasource.DataSource.execute(DataSource.java:2934)
    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:3493)
    at com.isomorphic.servlet.IDACall.handleDSRequest(IDACall.java:230)
    at com.isomorphic.servlet.IDACall.processRPCTransaction(IDACall.java:187)
    at com.isomorphic.servlet.IDACall.processRequest(IDACall.java:152)
    at com.isomorphic.servlet.IDACall._processRequest(IDACall.java:123)
    at com.isomorphic.servlet.IDACall.doPost(IDACall.java:79)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:555)
    at com.isomorphic.servlet.BaseServlet.service(BaseServlet.java:178)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:623)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:199)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:144)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:168)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:144)
    at com.isomorphic.servlet.URIRegexFilter.ignore(URIRegexFilter.java:566)
    at com.isomorphic.servlet.URIRegexFilter.ignore(URIRegexFilter.java:561)
    at com.isomorphic.servlet.URIRegexFilter.matchedRule(URIRegexFilter.java:524)
    at com.isomorphic.servlet.URIRegexFilter._doFilter(URIRegexFilter.java:377)
    at com.isomorphic.servlet.BaseFilter.doFilter(BaseFilter.java:91)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:168)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:144)
    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:168)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:144)
    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:168)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:144)
    at com.isomorphic.servlet.VersionedURLFilter._doFilter(VersionedURLFilter.java:83)
    at com.isomorphic.servlet.BaseFilter.doFilter(BaseFilter.java:91)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:168)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:144)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:168)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:482)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:130)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:346)
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:396)
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63)
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:937)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1791)
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52)
    at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1190)
    at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:63)
    at java.lang.Thread.run(Thread.java:748)
Caused by: org.hsqldb.HsqlException: incompatible data type in operation
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.FunctionSQL.resolveTypes(Unknown Source)
    at org.hsqldb.ExpressionLike.resolveTypes(Unknown Source)
    at org.hsqldb.ExpressionLogical.resolveTypes(Unknown Source)
    at org.hsqldb.QuerySpecification.resolveExpressionTypes(Unknown Source)
    at org.hsqldb.QuerySpecification.resolveTypesPartOne(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)
    ... 61 more
=== 2024-11-20 09:47:12,331 [ec-8] INFO SQL - 520: Executing query on 'HSQLDB': SELECT LIMIT 0 75 supplyItem.itemID, supplyItem.itemName, supplyItem.SKU, supplyItem.description, supplyItem.category, supplyItem.units, supplyItem.unitCost, supplyItem.inStock, supplyItem.nextShipment FROM supplyItem WHERE (LOWER(supplyItem.itemName) LIKE '%add%' ESCAPE '\' AND supplyItem.itemName IS NOT NULL)
=== 2024-11-20 09:47:12,346 [ec-2] INFO Compression - /isomorphic/IDACall: 171 -> 150
=== 2024-11-20 09:47:12,369 [ec-8] INFO DSResponse - DSResponse: List with 46 items
=== 2024-11-20 09:47:12,373 [ec-8] INFO Compression - /isomorphic/IDACall: 8,0k -> 2,1k

I actually see a similar error in my application, using Oracle DB.

[claudiobosticco]

Also, if you use as summaryFunctions:

<summaryFunctions>
    <unitCost>count</unitCost>
</summaryFunctions>

and try to filter the unitCost column by typing 9, you'll see this query for the summary row:

 SELECT LIMIT 0 1  COUNT(supplyItem.unitCost) AS unitCost FROM supplyItem WHERE ('1'='1') HAVING (COUNT(supplyItem.unitCost)=9)

which isn't the expected query

[Isomorphic]

Thanks for letting us know Claudio. We'll take a look

[Isomorphic]

Hi Claudio, we'll look into your SQL syntax error for your first post, but regarding your second post, that is actually the expected behavior - see the Server Summaries overview, specifically the "Criteria" header.

https://smartclient.com/smartclient-...erverSummaries

Criteria could be intended to be applied either to the summarized value or pre-summarization, but if you work through a variety of examples, we think you'll find that more often than not, developers mean to apply the value post-summarization (your example is highly synthetic). That is also the only way the client-side filtering can work on a summarized dataset, since client-side filtering will not have the values for summarized fields.

You can reverse this on a per-OperationBinding basis, as covered in the docs.