Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-5370

Allow TextTable to return Null for non-existent columns with specified Header

    Details

    • Type: Quality Risk
    • Status: Resolved (View Workflow)
    • Priority: Major
    • Resolution: Done
    • Affects Version/s: 10.2
    • Fix Version/s: 11.0
    • Component/s: Query Engine
    • Labels:
      None

      Description

      In XmlTable we can have this:

      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;;
      

      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):

      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;;
      

      What do you think?
      At the moment the snippet above throws out the following error message:

      Unable to find source-code formatter for language: noformat. Available languages are: actionscript, ada, applescript, bash, c, c#, c++, cpp, css, erlang, go, groovy, haskell, html, java, javascript, js, json, lua, none, nyan, objc, perl, php, python, r, rainbow, ruby, scala, sh, sql, swift, visualbasic, xml, yaml
      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)
      

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                dalex005 Dmitrii Pogorelov
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: