Delete statement with having UDF functions in its inline view is giving issues ------------------------------------------------------------------------------ Key: TEIID-2518 URL: https://issues.jboss.org/browse/TEIID-2518 Project: Teiid Issue Type: Bug Components: Query Engine Affects Versions: 8.1 Reporter: Rajasekhar Kota Assignee: Steven Hawkins Priority: Critical Fix For: 8.4 Attachments: DeleteWithInlineQuery.java, ErrorMessage.txt, TeiidUDFExecutionFactory.java Delete queries having UDF function in it's inline view is giving issues. Please find the attached test cases to reproduce the issue. Here is the problematic query and where primary_key is an UDF function. delete from testSchema.tableA where policyfkeyxml not in (select primary_key('testSchema.tableB','policyID',policyID) as expr from testSchema.tableB) Here is the exception that we are getting when we run above query from a test case. WARNING: Connector worker process failed for atomic-request=tmG/kv17VS0S.0.0.0 org.teiid.translator.jdbc.JDBCExecutionException: 90022 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: DELETE FROM "MEM"."TESTSCHEMA"."TABLEA" WHERE "MEM"."TESTSCHEMA"."TABLEA"."POLICYFKEYXML" NOT IN (SELECT primary_key('testSchema.tableB', 'policyID', "MEM"."TESTSCHEMA"."TABLEB"."POLICYID") AS expr FROM "MEM"."TESTSCHEMA"."TABLEB")] at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:211) at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:71) at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:290) at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:257) at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:425) at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:270) at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:279) at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:279) at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:148) at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:146) at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:112) at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:153) at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:382) at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:291) at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:219) at org.teiid.dqp.internal.process.DQPCore.executeRequest(DQPCore.java:386) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:601) at org.teiid.transport.LocalServerConnection$1$1.call(LocalServerConnection.java:131) at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334) at java.util.concurrent.FutureTask.run(FutureTask.java:166) at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:249) at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:233) at org.teiid.transport.LocalServerConnection$1.invoke(LocalServerConnection.java:129) at $Proxy11.executeRequest(Unknown Source) at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:631) at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:506) at org.teiid.jdbc.StatementImpl.executeUpdate(StatementImpl.java:334) at com.ca.chorus.teiid.customfunctions.DeleteWithInlineQuery.testFailingInlineViewInDeleteQuery(DeleteWithInlineQuery.java:130) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:601) at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44) at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15) at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41) at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20) at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:76) at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50) at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193) at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52) at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191) at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42) at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184) at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28) at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:31) at org.junit.runners.ParentRunner.run(ParentRunner.java:236) at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50) at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197) Caused by: org.h2.jdbc.JdbcSQLException: Function "PRIMARY_KEY" not found; SQL statement: DELETE FROM "MEM"."TESTSCHEMA"."TABLEA" WHERE "MEM"."TESTSCHEMA"."TABLEA"."POLICYFKEYXML" NOT IN (SELECT primary_key('testSchema.tableB', 'policyID', "MEM"."TESTSCHEMA"."TABLEB"."POLICYID") AS expr FROM "MEM"."TESTSCHEMA"."TABLEB") [90022-168] at org.h2.message.DbException.getJdbcSQLException(DbException.java:329) at org.h2.message.DbException.get(DbException.java:169) at org.h2.message.DbException.get(DbException.java:146) at org.h2.command.Parser.readJavaFunction(Parser.java:2122) at org.h2.command.Parser.readFunction(Parser.java:2174) at org.h2.command.Parser.readTerm(Parser.java:2476) at org.h2.command.Parser.readFactor(Parser.java:2046) at org.h2.command.Parser.readSum(Parser.java:2033) at org.h2.command.Parser.readConcat(Parser.java:2006) at org.h2.command.Parser.readCondition(Parser.java:1871) at org.h2.command.Parser.readAnd(Parser.java:1852) at org.h2.command.Parser.readExpression(Parser.java:1844) at org.h2.command.Parser.parseSelectSimpleSelectPart(Parser.java:1757) at org.h2.command.Parser.parseSelectSimple(Parser.java:1789) at org.h2.command.Parser.parseSelectSub(Parser.java:1684) at org.h2.command.Parser.parseSelectUnion(Parser.java:1527) at org.h2.command.Parser.parseSelect(Parser.java:1515) at org.h2.command.Parser.readCondition(Parser.java:1921) at org.h2.command.Parser.readAnd(Parser.java:1852) at org.h2.command.Parser.readExpression(Parser.java:1844) at org.h2.command.Parser.parseDelete(Parser.java:738) at org.h2.command.Parser.parsePrepared(Parser.java:336) at org.h2.command.Parser.parse(Parser.java:279) at org.h2.command.Parser.parse(Parser.java:251) at org.h2.command.Parser.prepareCommand(Parser.java:217) at org.h2.engine.Session.prepareLocal(Session.java:415) at org.h2.engine.Session.prepareCommand(Session.java:364) at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1109) at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:74) at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:264) at org.teiid.translator.jdbc.JDBCBaseExecution.getPreparedStatement(JDBCBaseExecution.java:181) at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:168) ... 55 more May 28, 2013 7:14:51 PM org.teiid.logging.JavaLogger log WARNING: TEIID30020 Processing exception 'TEIID30504 h2Translator: 90022 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: DELETE FROM "MEM"."TESTSCHEMA"."TABLEA" WHERE "MEM"."TESTSCHEMA"."TABLEA"."POLICYFKEYXML" NOT IN (SELECT primary_key('testSchema.tableB', 'policyID', "MEM"."TESTSCHEMA"."TABLEB"."POLICYID") AS expr FROM "MEM"."TESTSCHEMA"."TABLEB")]' for request tmG/kv17VS0S.0. Exception type org.teiid.core.TeiidProcessingException thrown from org.h2.message.DbException.getJdbcSQLException(DbException.java:329). Enable more detailed logging to see the entire stacktrace. org.teiid.jdbc.TeiidSQLException: TEIID30504 h2Translator: 90022 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: DELETE FROM "MEM"."TESTSCHEMA"."TABLEA" WHERE "MEM"."TESTSCHEMA"."TABLEA"."POLICYFKEYXML" NOT IN (SELECT primary_key('testSchema.tableB', 'policyID', "MEM"."TESTSCHEMA"."TABLEB"."POLICYID") AS expr FROM "MEM"."TESTSCHEMA"."TABLEB")] at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:113) at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:70) at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:656) at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:62) at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:512) at org.teiid.client.util.ResultsFuture.addCompletionListener(ResultsFuture.java:139) at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:508) at org.teiid.jdbc.StatementImpl.executeUpdate(StatementImpl.java:334) at com.ca.chorus.teiid.customfunctions.DeleteWithInlineQuery.testFailingInlineViewInDeleteQuery(DeleteWithInlineQuery.java:130) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:601) at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44) at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15) at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41) at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20) at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:76) at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50) at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193) at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52) at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191) at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42) at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184) at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28) at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:31) at org.junit.runners.ParentRunner.run(ParentRunner.java:236) at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50) at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197) Caused by: org.teiid.core.TeiidProcessingException: TEIID30504 h2Translator: 90022 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: DELETE FROM "MEM"."TESTSCHEMA"."TABLEA" WHERE "MEM"."TESTSCHEMA"."TABLEA"."POLICYFKEYXML" NOT IN (SELECT primary_key('testSchema.tableB', 'policyID', "MEM"."TESTSCHEMA"."TABLEB"."POLICYID") AS expr FROM "MEM"."TESTSCHEMA"."TABLEB")] at org.teiid.dqp.internal.process.DataTierTupleSource.exceptionOccurred(DataTierTupleSource.java:502) at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:289) at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:279) at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:279) at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:148) at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:146) at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:112) at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:153) at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:382) at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:291) at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:219) at org.teiid.dqp.internal.process.DQPCore.executeRequest(DQPCore.java:386) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:601) at org.teiid.transport.LocalServerConnection$1$1.call(LocalServerConnection.java:131) at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334) at java.util.concurrent.FutureTask.run(FutureTask.java:166) at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:249) at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:233) at org.teiid.transport.LocalServerConnection$1.invoke(LocalServerConnection.java:129) at $Proxy11.executeRequest(Unknown Source) at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:631) at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:506) ... 26 more Caused by: org.teiid.translator.jdbc.JDBCExecutionException: 90022 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: DELETE FROM "MEM"."TESTSCHEMA"."TABLEA" WHERE "MEM"."TESTSCHEMA"."TABLEA"."POLICYFKEYXML" NOT IN (SELECT primary_key('testSchema.tableB', 'policyID', "MEM"."TESTSCHEMA"."TABLEB"."POLICYID") AS expr FROM "MEM"."TESTSCHEMA"."TABLEB")] at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:211) at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:71) at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:290) at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:257) at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:425) at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:270) ... 50 more Caused by: org.h2.jdbc.JdbcSQLException: Function "PRIMARY_KEY" not found; SQL statement: DELETE FROM "MEM"."TESTSCHEMA"."TABLEA" WHERE "MEM"."TESTSCHEMA"."TABLEA"."POLICYFKEYXML" NOT IN (SELECT primary_key('testSchema.tableB', 'policyID', "MEM"."TESTSCHEMA"."TABLEB"."POLICYID") AS expr FROM "MEM"."TESTSCHEMA"."TABLEB") [90022-168] at org.h2.message.DbException.getJdbcSQLException(DbException.java:329) at org.h2.message.DbException.get(DbException.java:169) at org.h2.message.DbException.get(DbException.java:146) at org.h2.command.Parser.readJavaFunction(Parser.java:2122) at org.h2.command.Parser.readFunction(Parser.java:2174) at org.h2.command.Parser.readTerm(Parser.java:2476) at org.h2.command.Parser.readFactor(Parser.java:2046) at org.h2.command.Parser.readSum(Parser.java:2033) at org.h2.command.Parser.readConcat(Parser.java:2006) at org.h2.command.Parser.readCondition(Parser.java:1871) at org.h2.command.Parser.readAnd(Parser.java:1852) at org.h2.command.Parser.readExpression(Parser.java:1844) at org.h2.command.Parser.parseSelectSimpleSelectPart(Parser.java:1757) at org.h2.command.Parser.parseSelectSimple(Parser.java:1789) at org.h2.command.Parser.parseSelectSub(Parser.java:1684) at org.h2.command.Parser.parseSelectUnion(Parser.java:1527) at org.h2.command.Parser.parseSelect(Parser.java:1515) at org.h2.command.Parser.readCondition(Parser.java:1921) at org.h2.command.Parser.readAnd(Parser.java:1852) at org.h2.command.Parser.readExpression(Parser.java:1844) at org.h2.command.Parser.parseDelete(Parser.java:738) at org.h2.command.Parser.parsePrepared(Parser.java:336) at org.h2.command.Parser.parse(Parser.java:279) at org.h2.command.Parser.parse(Parser.java:251) at org.h2.command.Parser.prepareCommand(Parser.java:217) at org.h2.engine.Session.prepareLocal(Session.java:415) at org.h2.engine.Session.prepareCommand(Session.java:364) at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1109) at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:74) at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:264) at org.teiid.translator.jdbc.JDBCBaseExecution.getPreparedStatement(JDBCBaseExecution.java:181) at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:168) ... 55 more