Odd Casting Exception
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
