dalex dalex created TEIID-5462: ---------------------------------- Summary: Netezza error: NzSQLException-netezza.max.stmt.handles Key: TEIID-5462 URL: https://issues.jboss.org/browse/TEIID-5462 Project: Teiid Issue Type: Bug Components: Query Engine Affects Versions: 11.1 Environment: teiid-11.1.0 (from 01.09.2018) on WildFly Full 11.0.0.Final (WildFly Core 3.0.8.Final) Reporter: dalex dalex Assignee: Steven Hawkins Priority: Blocker Running the following query: {code:sql} insert into dwh.test_my_stream select t1.* from views.v t1 join (call views.pr()) t2 on t1.a=t2.a where t1.a >= (select max(t1.a) from views.v t1 join (call views.pr()) t2 on t1.a=t2.a) and t1.a1 >= (select min(t1.a1) from views.v t1 left join (call views.pr()) t2 on t1.a=t2.a) limit 10000 ;; {code} teiid server throws out the following stacktrace: {code:noformat} 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 {code} 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: {code:sql} 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 ;; {code} will work without any errors. -- This message was sent by Atlassian JIRA (v7.5.0#75005)