[teiid-issues] [JBoss JIRA] (TEIID-2518) Delete statement with having UDF functions in its inline view is giving issues

Steven Hawkins (JIRA) jira-events at lists.jboss.org
Tue Jun 4 13:50:54 EDT 2013 

    [ https://issues.jboss.org/browse/TEIID-2518?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12779279#comment-12779279 ] 

Steven Hawkins commented on TEIID-2518:
---------------------------------------

By including the function in the physical schema we are currently assuming that it must be a pushdown function - we effectively only check for cannot pushdown and treat allowed/must pushdown the same.  Otherwise you would have to go through the extra step of adding the function to the supported functions list, which seems unnecessary since you've already added the function via a physical schema.

Ideally global/pushdown allowed functions would be added via a virtual model.  

That said with the change to allow physical schema to have any virtual construct these assumptions may not quite match your expectations.
                
> 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

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira

More information about the teiid-issues mailing list