1 Reply Latest reply on Apr 29, 2019 3:22 PM by Steven Hawkins

    Odd Casting Exception

    Dines Madsen Newbie

      Hi all.

       

      I am 100% sure I am doing something stupid here, but I cannot for the life of me see what.

       

      I have a very simple XLS-based table deployed in Teiid 11.2.2.:

       

      <model name="Inspections">
      <source connection-jndi-name="java:/Excel-Files" name="Inspections" translator-name="excel"/>
      <metadata type="DDL"><![CDATA[
      SET NAMESPACE 'http://www.teiid.org/translator/excel/2014' AS teiid_excel;
      
      CREATE FOREIGN TABLE ATEX_INSPECTIONS (
      ROW_ID integer OPTIONS("teiid_excel:CELL_NUMBER" 'ROW_ID'),
      FIELD string(10) OPTIONS("teiid_excel:CELL_NUMBER" '1'),
      TAG_NUMBER string(50) OPTIONS("teiid_excel:CELL_NUMBER" '2'),
      ATEX_INSPECTED boolean OPTIONS("teiid_excel:CELL_NUMBER" '3'),
      DateAdded timestamp OPTIONS("teiid_excel:CELL_NUMBER" '4'),
      DateModified timestamp OPTIONS("teiid_excel:CELL_NUMBER" '5')
      ) OPTIONS(NAMEINSOURCE 'Sheet1', UPDATABLE 'TRUE', "teiid_excel:FILE" 'InputFile.xls', "teiid_excel:FIRST_DATA_ROW_NUMBER" '2');
      

       

      I am able to do simple select and inserts into the table without issues. But if I attempt to use a WHERE clause, like this query

      UPDATE "Inspections"."ATEX_INSPECTIONS"
      SET ATEX_INSPECTED = 1
      WHERE TAG_NUMBER = 'SomeValue'
      ;
      

       

      Teiid returns the following exception:

      2019-04-28 10:02:51,017 WARN  [org.teiid.PROCESSOR] (Worker86_QueryProcessorQueue5012) 3OJTK//wRHDK TEIID30020 Processing exception for request 3OJTK//wRHDK.0 'TEIID30504 Inspections: java.lang.String cannot be cast to java.lang.Integer'. Originally TeiidProcessingException ExcelQueryVisitor.java:185. Enable more detailed logging to see the entire stacktrace.

      2019-04-28 10:02:51,018 ERROR [org.jboss.as.controller.management-operation] (External Management Request Threads -- 6)  WFLYCTL0013: Operation ("execute-query") failed - address: ([("subsystem" => "teiid")]) - failure description: "org.teiid.core.TeiidProcessingException: TEIID30504 Inspections: java.lang.String cannot be cast to java.lang.Integer"

      2019-04-28 10:08:53,420 WARN  [org.teiid.CONNECTOR] (Worker90_QueryProcessorQueue5017) LIvPtM0gmU0w Connector worker process failed for atomic-request=LIvPtM0gmU0w.0.0.81: org.teiid.translator.TranslatorException: TEIID23008 Not valid column ROW_ID for comparison, only allowed on ROW_ID type columns

      at org.teiid.translator.excel.ExcelQueryVisitor.visit(ExcelQueryVisitor.java:210)

      at org.teiid.language.Comparison.acceptVisitor(Comparison.java:106)

      at org.teiid.language.visitor.AbstractLanguageVisitor.visitNode(AbstractLanguageVisitor.java:47)

      at org.teiid.language.visitor.HierarchyVisitor.visit(HierarchyVisitor.java:194)

      at org.teiid.language.Update.acceptVisitor(Update.java:55)

      at org.teiid.language.visitor.AbstractLanguageVisitor.visitNode(AbstractLanguageVisitor.java:47)

      at org.teiid.translator.excel.BaseExcelExecution.visit(BaseExcelExecution.java:90)

      at org.teiid.translator.excel.ExcelUpdateExecution.<init>(ExcelUpdateExecution.java:57)

      at org.teiid.translator.excel.ExcelExecutionFactory.createUpdateExecution(ExcelExecutionFactory.java:61)

      at org.teiid.translator.excel.ExcelExecutionFactory.createUpdateExecution(ExcelExecutionFactory.java:37)

      at org.teiid.translator.ExecutionFactory.createExecution(ExecutionFactory.java:320)

      at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:381)

      at sun.reflect.GeneratedMethodAccessor117.invoke(Unknown Source)

      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

      at java.lang.reflect.Method.invoke(Method.java:498)

      at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:229)

      at com.sun.proxy.$Proxy40.execute(Unknown Source)

      at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)

      at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)

      at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:104)

      at java.util.concurrent.FutureTask.run(FutureTask.java:266)

      at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:61)

      at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:278)

      at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)

      at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)

      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)

      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)

      at java.lang.Thread.run(Thread.java:748)

       

      Why is Teiid attempting to convert strings to integers, and how do I prevent this behavior? Any hints would be appreciated!

       

      Best wishes

      Dines Madsen

        • 1. Re: Odd Casting Exception
          Steven Hawkins Master

          > Why is Teiid attempting to convert strings to integers, and how do I prevent this behavior? Any hints would be appreciated!

           

          The translator only knows how to compare against row_ids, thus it expects integers as comparison operands.

           

          When the default excel importer runs it marks all non-id columns as unsearchable to prevent predicate pushdown against them:

           

          TAG_NUMBER string(50) OPTIONS(SEARCHABLE 'Unsearchable', "teiid_excel:CELL_NUMBER" '2')

           

          You can add that SEARCHABLE option to each of your non-id columns.