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

Delete statement with having UDF functions in its inline view is giving issues

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Critical
    • Resolution: Done
    • Affects Version/s: 8.1
    • Fix Version/s: 8.4
    • Component/s: Query Engine
    • Labels:
      None

      Description

      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

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                rajkota Rajasekhar Kota
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: