Details
-
Enhancement
-
Resolution: Done
-
Major
-
11.1
-
None
Description
Running the following query:
insert into dwh.test_my_stream select t1.* from views.v t1 join (call views.pr()) t2 on t1.a=cast(t2.a as integer) where t1.a >= (select max(t1.a) from views.v t1 join (call views.pr()) t2 on t1.a=cast(t2.a as integer)) and t1.a1 >= (select min(t1.a1) from views.v t1 left join (call views.pr()) t2 on t1.a=cast(t2.a as integer)) limit 10000 ;;
teiid server throws out the following stacktrace:
Unable to find source-code formatter for language: noformat. Available languages are: actionscript, ada, applescript, bash, c, c#, c++, cpp, css, erlang, go, groovy, haskell, html, java, javascript, js, json, lua, none, nyan, objc, perl, php, python, r, rainbow, ruby, scala, sh, sql, swift, visualbasic, xml, yaml
2018-09-03 18:56:47,795 WARN [org.teiid.PROCESSOR] (Worker4_QueryProcessorQueue32) AQhJm01u0x7c TEIID30020 Processing exception for request AQhJm01u0x7c.13 'TEIID30312 Unable to eva luate right expression of o__3.a >= (SELECT MAX(t1.a) FROM views.v AS t1 INNER JOIN (EXEC views.pr()) AS t2 ON t1.a = convert(t2.a, integer))'. Originally ExpressionEvaluationExcepti on 'netezza.max.stmt.handles' QueryExecutor.java:444. Enable more detailed logging to see the entire stacktrace. 2018-09-03 18:57:03,963 WARN [org.teiid.CONNECTOR] (Worker4_QueryProcessorQueue35) AQhJm01u0x7c Connector worker process failed for atomic-request=AQhJm01u0x7c.14.5.47: org.teiid.tr anslator.jdbc.JDBCExecutionException: 11403 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT 1 AS c_0 FROM "TEST"."ADMIN"."TEST_STREAM" AS g_0 WHE RE g_0."RULE_ID" = 1 LIMIT 1] at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:127) at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:382) at sun.reflect.GeneratedMethodAccessor101.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:229) at com.sun.proxy.$Proxy36.execute(Unknown Source) at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302) at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:138) at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:401) at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277) at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160) at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207) at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154) at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252) at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195) at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277) at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160) at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207) at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154) at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252) at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195) at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277) at org.teiid.query.processor.BatchCollector$BatchProducerTupleSource.nextTuple(BatchCollector.java:90) at org.teiid.query.processor.relational.GroupingNode.groupPhase(GroupingNode.java:593) at org.teiid.query.processor.relational.GroupingNode.nextBatchDirect(GroupingNode.java:389) at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277) at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146) at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277) at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:141) at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:148) at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111) at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160) at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142) at org.teiid.query.processor.relational.SubqueryAwareEvaluator.evaluateSubquery(SubqueryAwareEvaluator.java:354) at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:1323) at org.teiid.query.eval.Evaluator.internalEvaluate(Evaluator.java:763) at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:707) at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:344) at org.teiid.query.eval.Evaluator.internalEvaluateTVL(Evaluator.java:185) at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:297) at org.teiid.query.eval.Evaluator.internalEvaluateTVL(Evaluator.java:181) at org.teiid.query.eval.Evaluator.evaluateTVL(Evaluator.java:174) at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:168) at org.teiid.query.processor.relational.SelectNode.nextBatchDirect(SelectNode.java:118) at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277) at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160) at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142) at org.teiid.query.processor.relational.SourceState.getTupleBuffer(SourceState.java:244) at org.teiid.query.processor.relational.EnhancedSortMergeJoinStrategy.loadLeft(EnhancedSortMergeJoinStrategy.java:227) at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:228) at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:200) at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277) at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98) at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277) at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160) at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207) at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154) at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252) at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195) at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277) at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98) at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277) at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160) at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207) at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154) at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252) at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195) at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277) at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98) at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277) at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146) at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277) at org.teiid.query.processor.relational.ProjectIntoNode.nextBatchDirect(ProjectIntoNode.java:140) at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277) at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:141) at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:148) at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111) at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160) at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142) at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:492) at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:362) at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:47) at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:285) at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277) at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115) at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) Caused by: org.netezza.error.NzSQLException: netezza.max.stmt.handles at org.netezza.internal.QueryExecutor.sendQuery(QueryExecutor.java:444) at org.netezza.internal.QueryExecutor.execute(QueryExecutor.java:73) at org.netezza.sql.NzConnection.execute(NzConnection.java:2904) at org.netezza.sql.NzPreparedStatament._execute(NzPreparedStatament.java:1163) at org.netezza.sql.NzPreparedStatament.prepare(NzPreparedStatament.java:1180) at org.netezza.sql.NzPreparedStatament.<init>(NzPreparedStatament.java:96) at org.netezza.sql.NzConnection.prepareStatement(NzConnection.java:1624) at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.doPrepareStatement(BaseWrapperManagedConnection.java:757) at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:743) at org.jboss.jca.adapters.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:454) at org.teiid.translator.jdbc.JDBCBaseExecution.getPreparedStatement(JDBCBaseExecution.java:192) at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:117) ... 89 more
which is (according to https://www-01.ibm.com/support/docview.wss?uid=swg21570296 link) the following:
"When using the JDBC driver, one connection instance can only execute a single query at a time. This means that if one query is already executing on a specific connection instance, no other queries can be sent for parallel execution. If you run multiple queries using the same connection instance, you might encounter an error similar to the following:
Error[1]: Failed retrieving bean metadata.netezza.max.stmt.handles"
Another interesting thing is that running pretty the same query but not like insert, just usual select:
select t1.* from views.v t1 join (call views.pr()) t2 on t1.a=cast(t2.a as integer) where t1.a >= (select max(t1.a) from views.v t1 join (call views.pr()) t2 on t1.a=cast(t2.a as integer)) and t1.a1 >= (select min(t1.a1) from views.v t1 left join (call views.pr()) t2 on t1.a=cast(t2.a as integer)) limit 10000 ;;
will work without any errors.
Setting the threadBound translator property on the netezza source to true helps to solve the problem but
actually we should introduce a dynamic way to allow for thread bound to be turned on based upon a transactional request.
