[teiid-issues] [JBoss JIRA] (TEIID-5370) Allow TextTable to return Null for non-existent columns with specified Header

[dalex dalex (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-5370?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13589503#comment-13589503 ] 

dalex dalex commented on TEIID-5370:
------------------------------------

[~shawkins] cool! Thx for the quick fix!

> Allow TextTable to return Null for non-existent columns with specified Header
> -----------------------------------------------------------------------------
>
>                 Key: TEIID-5370
>                 URL: https://issues.jboss.org/browse/TEIID-5370
>             Project: Teiid
>          Issue Type: Quality Risk
>          Components: Query Engine
>    Affects Versions: 10.2
>         Environment: teiid-10.2.0 on WildFly Full 11.0.0.Final (WildFly Core 3.0.8.Final)
>            Reporter: dalex dalex
>            Assignee: Steven Hawkins
>             Fix For: 11.0
>
>
> In XmlTable we can have this:
> {code:sql}
> Begin
>     Declare xml ttab = 
> '<root>
>     <col1>1</col1>
>     <col2>2</col2>
> </root>';    
>  
> Select * From
>     XmlTable (
>         '/root'
>         Passing ttab
>         Columns
>             col1 string,
>             col2 string,
>             colNotExists string
>     ) t;
> End;;
> {code}
> and when a column doesn't exist in the xml it will be returned as null (colNotExists in the snippet above). The similar approach for TextTable will result in an error as this column indeed doesn't exist. I think, it may be useful, if the below snippet succeeds and returns nulls for non-existent cols (we encountered with a situation when we wanted to use TextTable in a dynamic way but it failed because of not existing cols):
> {code:sql}
> Begin
>     Declare clob ttab = 
> 'col1,col2
> 1,2
> one,two';    
> Select * From
>     TextTable (
>         ttab
>         Columns
>             col1 string,
>             col2 string,
>             colNotExists string
>         Delimiter ','
>         Header 1
>     ) t;
> End;;
> {code}
> What do you think?
> At the moment the snippet above throws out the following error message:
> {code:noformat}
> 2018-06-11 15:21:15,882 WARN  [org.teiid.PROCESSOR] (Worker3_QueryProcessorQueue11) YTMw2kYFv3Cp TEIID30020 Processing exception for request YTMw2kYFv3Cp.4 'TEIID30181 HEADER entry m
> issing for column name colNotExists in Unknown.'. Originally TeiidProcessingException TextTableNode.java:499.: org.teiid.core.TeiidProcessingException: TEIID30181 HEADER entry missin
> g for column name colNotExists in Unknown.
>         at org.teiid.query.processor.relational.TextTableNode.processHeader(TextTableNode.java:499)
>         at org.teiid.query.processor.relational.TextTableNode.initReader(TextTableNode.java:471)
>         at org.teiid.query.processor.relational.TextTableNode.nextBatchDirect(TextTableNode.java:209)
>         at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
>         at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146)
>         at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
>         at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:141)
>         at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:148)
>         at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111)
>         at org.teiid.query.processor.BatchIterator.finalRow(BatchIterator.java:65)
>         at org.teiid.common.buffer.AbstractTupleSource.getCurrentTuple(AbstractTupleSource.java:66)
>         at org.teiid.query.processor.BatchIterator.getCurrentTuple(BatchIterator.java:80)
>         at org.teiid.common.buffer.AbstractTupleSource.hasNext(AbstractTupleSource.java:88)
>         at org.teiid.query.processor.proc.ProcedurePlan.executePlan(ProcedurePlan.java:612)
>         at org.teiid.query.processor.proc.CreateCursorResultSetInstruction.process(CreateCursorResultSetInstruction.java:65)
>         at org.teiid.query.processor.proc.ProcedurePlan.processProcedure(ProcedurePlan.java:389)
>         at org.teiid.query.processor.proc.ProcedurePlan.nextBatchDirect(ProcedurePlan.java:297)
>         at org.teiid.query.processor.proc.ProcedurePlan.nextBatch(ProcedurePlan.java:269)
>         at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:148)
>         at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111)
>         at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
>         at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
>         at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:480)
>         at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:350)
>         at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:47)
>         at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:276)
>         at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277)
>         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:1142)
>         at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
>         at java.lang.Thread.run(Thread.java:745)
> {code}



--
This message was sent by Atlassian JIRA
(v7.5.0#75005)