Uploaded image for project: 'Search and Discovery'
  1. Search and Discovery
  2. SEARCH-2316

Error is produced in Tableau when trying to produce a graph for number of nodes created by year and type

    Details

    • Type: Story
    • Status: Review
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Labels:
      None
    • Story Points:
      8

      Description

      In tableau when executing the following query--> Number of nodes created by year and type (using cm_created, type and cm_content.size from the alfresco table) it produces a table of results. 

      When trying to create a line graph (continuous) for this data it produces the following error: 
      [Latest behaviour]

      Error message in UI:

      An error occurred while communicating with the Other Databases (ODBC) data source 'alfresco (Alfresco.alfresco) (CData)'
      
      Error Code: 1E953F46
      [400] 07190003 Unable to execute the query, error caused by: No match found for function signature EXPR(<CHARACTER>, <NUMERIC>, <NUMERIC>). 
      

      See

      Also the error is produced in the logs:

      solr6_1               | 2020-08-19 17:59:33.812 ERROR (qtp1939990953-23) [   x:alfresco] o.a.c.r.CalciteException org.apache.calcite.sql.validate.SqlValidatorException: No match found for function signature EXPR(<CHARACTER>, <NUMERIC>, <NUMERIC>)
      solr6_1               | 2020-08-19 17:59:33.816 ERROR (qtp1939990953-23) [   x:alfresco] o.a.c.r.CalciteException org.apache.calcite.runtime.CalciteContextException: From line 1, column 257 to line 1, column 314: No match found for function signature EXPR(<CHARACTER>, <NUMERIC>, <NUMERIC>)
      solr6_1               | 2020-08-19 17:59:33.818 ERROR (qtp1939990953-23) [   x:alfresco] o.a.s.s.AlfrescoExceptionStream java.io.IOException: Failed to execute sqlQuery 'SELECT COUNT(*) AS `sum_Number_of_Records_ok`, DATEADD('day', FLOOR(EXPR('*', -1, EXPR('-', DAYOFYEAR(`alfresco`.`cm_created`), 1))), CAST(`alfresco`.`cm_created` AS DATE)) AS `tyr_cm_created_ok` FROM `alfresco` GROUP BY DATEADD('day', FLOOR(EXPR('*', -1, EXPR('-', alfresco.cm_created_unit_of_time_day_of_year, 1))), CAST(`alfresco`.`cm_created` AS DATE))' against JDBC connection 'jdbc:alfrescosolr:'
      solr6_1               | 	at org.alfresco.solr.stream.JDBCStream.open(JDBCStream.java:273)
      solr6_1               | 	at org.alfresco.solr.stream.AlfrescoExceptionStream.open(AlfrescoExceptionStream.java:53)
      solr6_1               | 	at org.apache.solr.handler.StreamHandler$TimerStream.open(StreamHandler.java:535)
      solr6_1               | 	at org.apache.solr.client.solrj.io.stream.TupleStream.writeMap(TupleStream.java:83)
      solr6_1               | 	at org.apache.solr.response.JSONWriter.writeMap(JSONResponseWriter.java:547)
      solr6_1               | 	at org.apache.solr.response.TextResponseWriter.writeVal(TextResponseWriter.java:193)
      solr6_1               | 	at org.apache.solr.response.JSONWriter.writeNamedListAsMapWithDups(JSONResponseWriter.java:209)
      solr6_1               | 	at org.apache.solr.response.JSONWriter.writeNamedList(JSONResponseWriter.java:325)
      solr6_1               | 	at org.apache.solr.response.JSONWriter.writeResponse(JSONResponseWriter.java:120)
      solr6_1               | 	at org.apache.solr.response.JSONResponseWriter.write(JSONResponseWriter.java:71)
      solr6_1               | 	at org.apache.solr.response.QueryResponseWriterUtil.writeQueryResponse(QueryResponseWriterUtil.java:65)
      solr6_1               | 	at org.apache.solr.servlet.HttpSolrCall.writeResponse(HttpSolrCall.java:809)
      solr6_1               | 	at org.apache.solr.servlet.HttpSolrCall.call(HttpSolrCall.java:538)
      solr6_1               | 	at org.apache.solr.servlet.SolrDispatchFilter.doFilter(SolrDispatchFilter.java:361)
      solr6_1               | 	at org.apache.solr.servlet.SolrDispatchFilter.doFilter(SolrDispatchFilter.java:305)
      solr6_1               | 	at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1759)
      solr6_1               | 	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:583)
      solr6_1               | 	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
      solr6_1               | 	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:548)
      solr6_1               | 	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:226)
      solr6_1               | 	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1180)
      solr6_1               | 	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:513)
      solr6_1               | 	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)
      solr6_1               | 	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1112)
      solr6_1               | 	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
      solr6_1               | 	at org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:213)
      solr6_1               | 	at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:119)
      solr6_1               | 	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:134)
      solr6_1               | 	at org.eclipse.jetty.rewrite.handler.RewriteHandler.handle(RewriteHandler.java:335)
      solr6_1               | 	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:134)
      solr6_1               | 	at org.eclipse.jetty.server.Server.handle(Server.java:539)
      solr6_1               | 	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:333)
      solr6_1               | 	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:251)
      solr6_1               | 	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:283)
      solr6_1               | 	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:108)
      solr6_1               | 	at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:93)
      solr6_1               | 	at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.executeProduceConsume(ExecuteProduceConsume.java:303)
      solr6_1               | 	at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceConsume(ExecuteProduceConsume.java:148)
      solr6_1               | 	at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run(ExecuteProduceConsume.java:136)
      solr6_1               | 	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:671)
      solr6_1               | 	at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:589)
      solr6_1               | 	at java.base/java.lang.Thread.run(Thread.java:834)
      solr6_1               | Caused by: java.sql.SQLException: Error while executing SQL "SELECT COUNT(*) AS `sum_Number_of_Records_ok`, DATEADD('day', FLOOR(EXPR('*', -1, EXPR('-', DAYOFYEAR(`alfresco`.`cm_created`), 1))), CAST(`alfresco`.`cm_created` AS DATE)) AS `tyr_cm_created_ok` FROM `alfresco` GROUP BY DATEADD('day', FLOOR(EXPR('*', -1, EXPR('-', alfresco.cm_created_unit_of_time_day_of_year, 1))), CAST(`alfresco`.`cm_created` AS DATE))": From line 1, column 257 to line 1, column 314: No match found for function signature EXPR(<CHARACTER>, <NUMERIC>, <NUMERIC>)
      solr6_1               | 	at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
      solr6_1               | 	at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
      solr6_1               | 	at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:163)
      solr6_1               | 	at org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:227)
      solr6_1               | 	at org.alfresco.solr.stream.JDBCStream.open(JDBCStream.java:269)
      solr6_1               | 	... 41 more
      solr6_1               | Caused by: org.apache.calcite.runtime.CalciteContextException: From line 1, column 257 to line 1, column 314: No match found for function signature EXPR(<CHARACTER>, <NUMERIC>, <NUMERIC>)
      solr6_1               | 	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
      solr6_1               | 	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
      solr6_1               | 	at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
      solr6_1               | 	at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
      solr6_1               | 	at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463)
      solr6_1               | 	at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:804)
      solr6_1               | 	at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:789)
      solr6_1               | 	at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:4393)
      solr6_1               | 	at org.apache.calcite.sql.validate.SqlValidatorImpl.handleUnresolvedFunction(SqlValidatorImpl.java:1672)
      solr6_1               | 	at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:278)
      solr6_1               | 	at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:223)
      solr6_1               | 	at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5053)
      solr6_1               | 	at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5040)
      solr6_1               | 	at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:137)
      solr6_1               | 	at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1588)
      solr6_1               | 	at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1573)
      solr6_1               | 	at org.apache.calcite.sql.SqlNode.validateExpr(SqlNode.java:225)
      solr6_1               | 	at org.apache.calcite.sql.SqlOperator.validateCall(SqlOperator.java:407)
      solr6_1               | 	at org.apache.calcite.sql.SqlFunction.validateCall(SqlFunction.java:204)
      solr6_1               | 	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateCall(SqlValidatorImpl.java:4764)
      solr6_1               | 	at org.apache.calcite.sql.SqlCall.validate(SqlCall.java:114)
      solr6_1               | 	at org.apache.calcite.sql.SqlNode.validateExpr(SqlNode.java:224)
      solr6_1               | 	at org.apache.calcite.sql.SqlOperator.validateCall(SqlOperator.java:407)
      solr6_1               | 	at org.apache.calcite.sql.SqlFunction.validateCall(SqlFunction.java:204)
      solr6_1               | 	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateCall(SqlValidatorImpl.java:4764)
      solr6_1               | 	at org.apache.calcite.sql.SqlCall.validate(SqlCall.java:114)
      solr6_1               | 	at org.apache.calcite.sql.SqlNode.validateExpr(SqlNode.java:224)
      solr6_1               | 	at org.apache.calcite.sql.SqlOperator.validateCall(SqlOperator.java:407)
      solr6_1               | 	at org.apache.calcite.sql.SqlFunction.validateCall(SqlFunction.java:204)
      solr6_1               | 	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateCall(SqlValidatorImpl.java:4764)
      solr6_1               | 	at org.apache.calcite.sql.SqlCall.validate(SqlCall.java:114)
      solr6_1               | 	at org.apache.calcite.sql.SqlNode.validateExpr(SqlNode.java:224)
      solr6_1               | 	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateGroupClause(SqlValidatorImpl.java:3564)
      solr6_1               | 	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3185)
      solr6_1               | 	at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)
      solr6_1               | 	at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
      solr6_1               | 	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:939)
      solr6_1               | 	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:920)
      solr6_1               | 	at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:220)
      solr6_1               | 	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:895)
      solr6_1               | 	at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:605)
      solr6_1               | 	at org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:550)
      solr6_1               | 	at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:264)
      solr6_1               | 	at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:228)
      solr6_1               | 	at org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:784)
      solr6_1               | 	at org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:639)
      solr6_1               | 	at org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:609)
      solr6_1               | 	at org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:214)
      solr6_1               | 	at org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:603)
      solr6_1               | 	at org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:675)
      solr6_1               | 	at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:156)
      solr6_1               | 	... 43 more
      solr6_1               | Caused by: org.apache.calcite.sql.validate.SqlValidatorException: No match found for function signature EXPR(<CHARACTER>, <NUMERIC>, <NUMERIC>)
      solr6_1               | 	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
      solr6_1               | 	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
      solr6_1               | 	at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
      solr6_1               | 	at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
      solr6_1               | 	at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463)
      solr6_1               | 	at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:572)
      solr6_1               | 	... 89 more
      
      

      [Previous behaviour]

      An error occurred while communicating with the Other Databases (ODBC) data source 'Custom_SQL_Query (CData)'.
      
      Bad Connection: Tableau could not connect to the data source.
       [400] 05220197 Unable to execute the query, error caused by: Cannot apply 'EXTRACT' to arguments of type 'EXTRACT(<INTERVAL DOY> FROM <JAVATYPE(CLASS JAVA.LANG.STRING)>)'. Supported form(s): 'EXTRACT(<DATETIME_INTERVAL> FROM <DATETIME_INTERVAL>)'
       'EXTRACT(<DATETIME_INTERVAL> FROM <DATETIME>)'. 
       SELECT `Custom_SQL_Query1`.`TYPE` AS `TYPE`,
       SUM(1) AS `sum_Number_of_Records_ok`,
       {fn TIMESTAMPADD(SQL_TSI_DAY,{fn FLOOR((-1 * (
      
      {fn DAYOFYEAR(`Custom_SQL_Query1`.`cm_created`)} - 1)))},CAST(`Custom_SQL_Query1`.`cm_created` AS DATE))} AS `tyr_cm_created_ok`
       FROM (
       select *, expense_Amount from alfresco
       ) `Custom_SQL_Query1`
       GROUP BY `Custom_SQL_Query1`.`TYPE`,
       \{fn TIMESTAMPADD(SQL_TSI_DAY,{fn FLOOR((-1 * ({fn DAYOFYEAR(`Custom_SQL_Query1`.`cm_created`)}
       - 1)))},CAST(`Custom_SQL_Query1`.`cm_created` AS DATE))}
      

      When filtering the query to use Quarter, Month and so on removing year from the results then the data produces the line graph (continuous). Also the results for this when trying to produce a graph output are very inconsistent where sometimes it works and produces the graph and sometime it produces the error. But the error is always produced when trying to create the line graph (continuous) and then will continue to appear with others when trying to go back (for example works with discrete line graph but then try continuous and you get the error, then go back to discrete and the error comes again).

      Acceptance Criteria:

      • Investigate why this error occurs when using the year and producing a graph
      • Implement a fix if this issue continues 
      • Test the fix using tableau to ensure it is fixed 

        Attachments

          Issue Links

            Structure

              Activity

                People

                • Assignee:
                  Unassigned
                  Reporter:
                  klalia Keerat Lalia
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  4 Start watching this issue

                  Dates

                  • Created:
                    Updated:

                    Structure Helper Panel