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

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Done
    • 8.1
    • 8.4
    • Query Engine
    • 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

      Attachments

        Activity

          People

            rhn-engineering-shawkins Steven Hawkins
            rajasekharkota Rajasekhar Kota (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: