v12.1p_2022-01-04/Enterprise Deployment (2022-01-04)
I can confirm it's fixed, thank you very much.
Announcement
Collapse
No announcement yet.
X
-
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.
Leave a comment:
-
Hello, I've got some (possibly many) operationBindings where I do something like
Code:<ansiJoinClause> $defaultAnsiJoinClause -- some other custom ansi join </ansiJoinClause>
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?
Leave a comment:
-
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.
Leave a comment:
-
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" />
Leave a comment:
-
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" } } ] });
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->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
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->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
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->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
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)Tags: None
Leave a comment: