NPE on running an "execute immediate" command defined in a virtual procedure (Teiid-8.9.0-CR2)

[fox123]

Hi,

I am experiencing the following problem in Teiid-8.9.0-CR2.

I defined a virtual procedure in the VDB using the following definition:

<model visible = "true" type = "VIRTUAL" name = "test_procs">
     <metadata type = "DDL"><![CDATA[
          create virtual procedure p1(a long) returns (res long) as 
          begin
               execute immediate 'insert into pg.xyz select ''a''';
          end;                               
         ]]>
     </metadata>
</model>

When I call the virtual procedure:

exec test_procs.p1(1);

I get the following NPE:

12:56:59,868 ERROR [org.teiid.PROCESSOR] (Worker4_QueryProcessorQueue37) sAgKanG+4e4X TEIID30019 Unexpected exception for request sAgKanG+4e4X.0: java.lang.NullPointerException
        at org.teiid.query.optimizer.relational.rules.RuleAssignOutputElements.filterVirtualElements(RuleAssignOutputElements.java:477) [teiid-engine-8.9.0.CR2.jar:8.9.0.CR2]
        at org.teiid.query.optimizer.relational.rules.RuleAssignOutputElements.assignOutputElements(RuleAssignOutputElements.java:238) [teiid-engine-8.9.0.CR2.jar:8.9.0.CR2]
        at org.teiid.query.optimizer.relational.rules.RuleAssignOutputElements.assignOutputElements(RuleAssignOutputElements.java:292) [teiid-engine-8.9.0.CR2.jar:8.9.0.CR2]
        at org.teiid.query.optimizer.relational.rules.RuleAssignOutputElements.execute(RuleAssignOutputElements.java:117) [teiid-engine-8.9.0.CR2.jar:8.9.0.CR2]
        at org.teiid.query.optimizer.relational.RelationalPlanner.executeRules(RelationalPlanner.java:739) [teiid-engine-8.9.0.CR2.jar:8.9.0.CR2]
        at org.teiid.query.optimizer.relational.RelationalPlanner.optimize(RelationalPlanner.java:221) [teiid-engine-8.9.0.CR2.jar:8.9.0.CR2]
        at org.teiid.query.optimizer.QueryOptimizer.optimizePlan(QueryOptimizer.java:159) [teiid-engine-8.9.0.CR2.jar:8.9.0.CR2]
        at org.teiid.query.processor.proc.ExecDynamicSqlInstruction.process(ExecDynamicSqlInstruction.java:186) [teiid-engine-8.9.0.CR2.jar:8.9.0.CR2]
        at org.teiid.query.processor.proc.ProcedurePlan.processProcedure(ProcedurePlan.java:379) [teiid-engine-8.9.0.CR2.jar:8.9.0.CR2]
        at org.teiid.query.processor.proc.ProcedurePlan.nextBatchDirect(ProcedurePlan.java:288) [teiid-engine-8.9.0.CR2.jar:8.9.0.CR2]
        at org.teiid.query.processor.proc.ProcedurePlan.nextBatch(ProcedurePlan.java:262) [teiid-engine-8.9.0.CR2.jar:8.9.0.CR2]
        at org.teiid.query.processor.relational.PlanExecutionNode.nextBatchDirect(PlanExecutionNode.java:118) [teiid-engine-8.9.0.CR2.jar:8.9.0.CR2]
        at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.9.0.CR2.jar:8.9.0.CR2]
        at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146) [teiid-engine-8.9.0.CR2.jar:8.9.0.CR2]
        at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.9.0.CR2.jar:8.9.0.CR2]
        at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:136) [teiid-engine-8.9.0.CR2.jar:8.9.0.CR2]
        at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151) [teiid-engine-8.9.0.CR2.jar:8.9.0.CR2]
        at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114) [teiid-engine-8.9.0.CR2.jar:8.9.0.CR2]
        at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:164) [teiid-engine-8.9.0.CR2.jar:8.9.0.CR2]
        at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146) [teiid-engine-8.9.0.CR2.jar:8.9.0.CR2]
        at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:449) [teiid-engine-8.9.0.CR2.jar:8.9.0.CR2]
        at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:331) [teiid-engine-8.9.0.CR2.jar:8.9.0.CR2]
        at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.9.0.CR2.jar:8.9.0.CR2]
        at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:259) [teiid-engine-8.9.0.CR2.jar:8.9.0.CR2]
        at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.9.0.CR2.jar:8.9.0.CR2]
        at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.9.0.CR2.jar:8.9.0.CR2]
        at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-8.9.0.CR2.jar:8.9.0.CR2]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_51]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_51]
        at java.lang.Thread.run(Thread.java:744) [rt.jar:1.7.0_51]

As a workaround, if I use the clause "WITHOUT RETURN" in the execute immediate command, the procedure is correctly executed:

<model visible = "true" type = "VIRTUAL" name = "test_procs">
     <metadata type = "DDL"><![CDATA[
          create virtual procedure p1(a long) returns (res long) as 
          begin
               execute immediate 'insert into pg.xyz select ''a''' WITHOUT RETURN;
          end;                               
         ]]>
     </metadata>
</model>

[shawkins]

Yes, that is a problem.  Can you convert this to an issue?

The workaround is effective because it explicitly tells us that no result set is expected.  The logic is not correctly handling an update when the execute immediate may be returning a result set.  We are creating in invalid internal query to execute and thus the planning exception.

[fox123]

Hi Steven,

thanks for the quick reply. I created the following issue:

https://issues.jboss.org/browse/TEIID-3198

[shawkins]

That will be addressed for 8.9 CR3.

Thanks,

Steve

[fox123]

Hi Steven,

I have a similar problem in Teiid-8.10.0-Alpha1. Running the following script:

begin
  create local temporary table a1 (a integer);
  execute 'insert into a1 select 1';
  select 1;
end 

I get the following error message:

Error: TEIID30492 Remote org.teiid.api.exception.query.QueryValidatorException: TEIID30492 TEIID31121 The expected result set of the procedure null does not match the result set from returnable statement EXECUTE IMMEDIATE 'insert into a1 select 1'; use WITHOUT RETURN to indicate the statement should not be returned - TEIID30066 The definition for null does not have the correct number of projected symbols.  Expected 1, but was 0.
SQLState:  50000
ErrorCode: 30492

A similar error is thrown if the procedure is defined in the VDB, for example:

create virtual procedure p2() returns (x integer, y integer) as 
  begin
    create local temporary table a1 (a integer);
    execute 'insert into a1 select 1';
    select 1,2;
  end ;

In this case, the error message is:

Error: TEIID30168 Remote org.teiid.api.exception.query.QueryProcessingException: TEIID30168 Couldn't execute the dynamic SQL command "EXECUTE IMMEDIATE 'insert into a1 select 1'" with the SQL statement "'insert into a1 select 1'" due to: The dynamic sql string contains an incorrect number of elements.
SQLState:  50000
ErrorCode: 30168

In both cases, using "WITHOUT RETURN" helps, but I wondered if this is an expected behavior.

[shawkins]

> In both cases, using "WITHOUT RETURN" helps, but I wondered if this is an expected behavior.

Yes, that is expected and was also the pre 8.10 behavior as well.  By default we expect a dynamic sql statement to return a result set that matches the expected result set of the procedure.  The logic isn't fully aware of the statement flow, so it doesn't know that the dynamic sql can't actually return a result in these instances.  It would take an enhancement to refine this validation.  The workaround of adding the without return will work in any case.

[fox123]

Ok, thanks a lot for the quick reply!