[JBoss JIRA] (TEIID-4686) PrestoDB translator - ordering of joined tables fails
by Kylin Soong (JIRA)
[ https://issues.jboss.org/browse/TEIID-4686?page=com.atlassian.jira.plugin... ]
Kylin Soong commented on TEIID-4686:
------------------------------------
I agree that we don't do any change, it seems already be fix and included in 0.163, probably QE can test against the 0.163 driver, if the issue still exist, we can leave a notes in document like: the source not support ORDER BY with JOIN well, you can use translator properties to disable Order by.
[1] https://github.com/prestodb/presto/issues/1621
> PrestoDB translator - ordering of joined tables fails
> -----------------------------------------------------
>
> Key: TEIID-4686
> URL: https://issues.jboss.org/browse/TEIID-4686
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.8.6_3
> Reporter: Juraj Duráni
> Assignee: Kylin Soong
> Fix For: 9.2.1
>
>
> Issuing query like \[1\] causes an exception in PrestoDB. This seems to be PrestoDB issue. I found similar issue \[2\], but that one should be fixed. I have created a new issue \[3\]. Removing one column from ORDER BY, query returns correct result.
> Not sure if we can do something about it without avoiding pushing JOIN.
> I suggest either to fix this (if simple enough) or add a note to the documentation.
> \[1\]
> {code:sql}
> SELECT a.intkey, b.intkey FROM BQT1.SmallA AS a INNER JOIN BQT1.SmallB AS b on a.intkey = b.intkey ORDER BY 1, 2
> {code}
> \[2\] https://github.com/prestodb/presto/issues/1621
> \[3\] https://github.com/prestodb/presto/issues/7010
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
9 years, 2 months
[JBoss JIRA] (TEIID-4687) PrestoDB translator - unsupported corelated subquery
by Kylin Soong (JIRA)
[ https://issues.jboss.org/browse/TEIID-4687?page=com.atlassian.jira.plugin... ]
Kylin Soong updated TEIID-4687:
-------------------------------
Summary: PrestoDB translator - unsupported corelated subquery (was: PrestoDB translator - unsupported corelated subquery type)
> PrestoDB translator - unsupported corelated subquery
> ----------------------------------------------------
>
> Key: TEIID-4687
> URL: https://issues.jboss.org/browse/TEIID-4687
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.8.6_3
> Reporter: Juraj Duráni
> Assignee: Kylin Soong
> Fix For: 9.2
>
>
> Running query \[1\], PrestoDB throws exception (log \[2\]) for translated query.
> {code:sql|title=\[1\]}
> SELECT intkey, stringkey, floatnum, bytenum, (SELECT bytenum FROM bqt1.smalla AS b WHERE (bytenum = a.longnum) AND (intkey = '10')) AS longnum FROM bqt1.smalla AS a
> {code}
> {code:plain|title=\[2\]}
> 15:51:58,928 DEBUG [org.teiid.TRANSPORT] (New I/O worker #2) processing message:MessageHolder: key=386 contents=Invoke interface org.teiid.client.DQP.executeRequest
> 15:51:58,930 DEBUG [org.teiid.PROCESSOR] (Worker13_QueryProcessorQueue47) Request Thread DEf6D1zaxo6u.14 with state NEW
> 15:51:58,930 DEBUG [org.teiid.PROCESSOR] (Worker13_QueryProcessorQueue47) DEf6D1zaxo6u.14 Command has no cache hint and result set cache mode is not on.
> 15:51:58,931 DEBUG [org.teiid.PROCESSOR] (Worker13_QueryProcessorQueue47) DEf6D1zaxo6u.14 executing SELECT intkey, stringkey, floatnum, bytenum, (SELECT bytenum FROM bqt1.smalla AS b WHERE (bytenum = a.longnum) AND (intkey = '10')) AS longnum FROM bqt1.smalla AS a
> 15:51:58,936 DEBUG [org.teiid.PROCESSOR] (Worker13_QueryProcessorQueue47) ProcessTree for DEf6D1zaxo6u.14 AccessNode(1) output=[SmallA__1.IntKey, SmallA__1.StringKey, convert(SmallA__1.FloatNum, float), convert(SmallA__1.ByteNum, byte), (SELECT convert(g_0.ByteNum, byte) AS c_0 FROM Source.SmallA AS g_0 WHERE (convert(convert(g_0.ByteNum, byte), long) = SmallA__1.LongNum) AND (g_0.IntKey = 10)) AS longnum] SELECT g_0.IntKey, g_0.StringKey, convert(g_0.FloatNum, float), convert(g_0.ByteNum, byte), (SELECT convert(g_1.ByteNum, byte) FROM Source.SmallA AS g_1 WHERE (convert(convert(g_1.ByteNum, byte), long) = g_0.LongNum) AND (g_1.IntKey = 10)) FROM Source.SmallA AS g_0
> 15:51:58,938 DEBUG [org.teiid.TXN_LOG] (Worker13_QueryProcessorQueue47) before getOrCreateTransactionContext:org.teiid.dqp.internal.process.TransactionServerImpl@65d4dce6(DEf6D1zaxo6u)
> 15:51:58,938 DEBUG [org.teiid.TXN_LOG] (Worker13_QueryProcessorQueue47) after getOrCreateTransactionContext : DEf6D1zaxo6u NONE ID:NONE
> 15:51:58,939 DEBUG [org.teiid.BUFFER_MGR] (Worker13_QueryProcessorQueue47) Creating TupleBuffer: 19 [a.IntKey, a.StringKey, a.FloatNum, a.ByteNum, (SELECT b.ByteNum FROM BQT1.SmallA AS b WHERE (convert(b.ByteNum, long) = a.LongNum) AND (b.IntKey = 10) LIMIT 2) AS longnum] [class java.lang.Integer, class java.lang.String, class java.lang.Float, class java.lang.Byte, class java.lang.Byte] batch size 1024 of type PROCESSOR
> 15:51:58,940 DEBUG [org.teiid.CONNECTOR] (Worker13_QueryProcessorQueue47) DEf6D1zaxo6u.14.1.14 Create State
> 15:51:58,941 DEBUG [org.teiid.BUFFER_MGR] (Worker13_QueryProcessorQueue47) DEf6D1zaxo6u.14.1.14 Blocking on source query DEf6D1zaxo6u.14.1.14
> 15:51:58,942 DEBUG [org.teiid.BUFFER_MGR] (Worker13_QueryProcessorQueue47) DEf6D1zaxo6u.14 Blocking on source request(s).
> 15:51:58,942 DEBUG [org.teiid.PROCESSOR] (Worker13_QueryProcessorQueue47) Request Thread DEf6D1zaxo6u.14 - processor blocked
> 15:51:58,942 DEBUG [org.teiid.PROCESSOR] (Worker14_QueryProcessorQueue48) Running task for parent thread Worker13_QueryProcessorQueue47
> 15:51:58,943 DEBUG [org.teiid.CONNECTOR] (Worker14_QueryProcessorQueue48) DEf6D1zaxo6u.14.1.14 Processing NEW request: SELECT g_0.IntKey, g_0.StringKey, convert(g_0.FloatNum, float), convert(g_0.ByteNum, byte), (SELECT convert(g_1.ByteNum, byte) FROM Source.SmallA AS g_1 WHERE (convert(convert(g_1.ByteNum, byte), long) = g_0.LongNum) AND (g_1.IntKey = 10)) FROM Source.SmallA AS g_0
> 15:51:58,943 DEBUG [org.teiid.CONNECTOR] (Worker14_QueryProcessorQueue48) DEf6D1zaxo6u.14.1.14 Obtained execution
> 15:51:58,944 DEBUG [org.teiid.CONNECTOR] (Worker14_QueryProcessorQueue48) Source-specific command: SELECT g_0.intkey, g_0.stringkey, g_0.floatnum, g_0.bytenum, (SELECT g_1.bytenum FROM smalla AS g_1 WHERE cast(g_1.bytenum AS bigint) = g_0.longnum AND g_1.intkey = 10) FROM smalla AS g_0
> 15:51:59,443 WARN [org.teiid.CONNECTOR] (Worker14_QueryProcessorQueue48) Connector worker process failed for atomic-request=DEf6D1zaxo6u.14.1.14: org.teiid.translator.jdbc.JDBCExecutionException: 65536 TEIID11008:TEIID11004 Error executing statement(s): [SQL: SELECT g_0.intkey, g_0.stringkey, g_0.floatnum, g_0.bytenum, (SELECT g_1.bytenum FROM smalla AS g_1 WHERE cast(g_1.bytenum AS bigint) = g_0.longnum AND g_1.intkey = 10) FROM smalla AS g_0]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:364)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.8.0_92]
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) [rt.jar:1.8.0_92]
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_92]
> at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_92]
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy48.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at java.util.concurrent.FutureTask.run(FutureTask.java:266) [rt.jar:1.8.0_92]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [rt.jar:1.8.0_92]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [rt.jar:1.8.0_92]
> at java.lang.Thread.run(Thread.java:745) [rt.jar:1.8.0_92]
> Caused by: java.sql.SQLException: Query failed (#20170106_145159_04181_k4ss5): Unsupported correlated subquery type
> at com.facebook.presto.jdbc.PrestoResultSet.resultsException(PrestoResultSet.java:1799)
> at com.facebook.presto.jdbc.PrestoResultSet.getColumns(PrestoResultSet.java:1747)
> at com.facebook.presto.jdbc.PrestoResultSet.<init>(PrestoResultSet.java:125)
> at com.facebook.presto.jdbc.PrestoStatement.execute(PrestoStatement.java:212)
> at com.facebook.presto.jdbc.PrestoStatement.executeQuery(PrestoStatement.java:69)
> at org.jboss.jca.adapters.jdbc.WrappedStatement.executeQuery(WrappedStatement.java:344)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:119) [translator-jdbc-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> ... 18 more
> Caused by: java.lang.IllegalArgumentException: Unsupported correlated subquery type
> at com.facebook.presto.sql.planner.sanity.NoApplyNodeLeftChecker$1.visitApply(NoApplyNodeLeftChecker.java:42)
> at com.facebook.presto.sql.planner.plan.ApplyNode.accept(ApplyNode.java:142)
> at com.facebook.presto.sql.planner.SimplePlanVisitor.visitPlan(SimplePlanVisitor.java:26)
> at com.facebook.presto.sql.planner.SimplePlanVisitor.visitPlan(SimplePlanVisitor.java:19)
> at com.facebook.presto.sql.planner.plan.PlanVisitor.visitProject(PlanVisitor.java:40)
> at com.facebook.presto.sql.planner.plan.ProjectNode.accept(ProjectNode.java:81)
> at com.facebook.presto.sql.planner.SimplePlanVisitor.visitPlan(SimplePlanVisitor.java:26)
> at com.facebook.presto.sql.planner.SimplePlanVisitor.visitPlan(SimplePlanVisitor.java:19)
> at com.facebook.presto.sql.planner.plan.PlanVisitor.visitExchange(PlanVisitor.java:175)
> at com.facebook.presto.sql.planner.plan.ExchangeNode.accept(ExchangeNode.java:209)
> at com.facebook.presto.sql.planner.SimplePlanVisitor.visitPlan(SimplePlanVisitor.java:26)
> at com.facebook.presto.sql.planner.SimplePlanVisitor.visitPlan(SimplePlanVisitor.java:19)
> at com.facebook.presto.sql.planner.plan.PlanVisitor.visitOutput(PlanVisitor.java:50)
> at com.facebook.presto.sql.planner.plan.OutputNode.accept(OutputNode.java:81)
> at com.facebook.presto.sql.planner.sanity.NoApplyNodeLeftChecker.validate(NoApplyNodeLeftChecker.java:33)
> at com.facebook.presto.sql.planner.sanity.PlanSanityChecker.lambda$validate$0(PlanSanityChecker.java:44)
> at java.lang.Iterable.forEach(Iterable.java:75) [rt.jar:1.8.0_92]
> at com.facebook.presto.sql.planner.sanity.PlanSanityChecker.validate(PlanSanityChecker.java:44)
> at com.facebook.presto.sql.planner.LogicalPlanner.plan(LogicalPlanner.java:127)
> at com.facebook.presto.sql.planner.LogicalPlanner.plan(LogicalPlanner.java:111)
> at com.facebook.presto.execution.SqlQueryExecution.doAnalyzeQuery(SqlQueryExecution.java:292)
> at com.facebook.presto.execution.SqlQueryExecution.analyzeQuery(SqlQueryExecution.java:271)
> at com.facebook.presto.execution.SqlQueryExecution.start(SqlQueryExecution.java:229)
> at com.facebook.presto.execution.QueuedExecution.lambda$start$1(QueuedExecution.java:62)
> ... 3 more
> 15:51:59,452 DEBUG [org.teiid.PROCESSOR] (Worker13_QueryProcessorQueue49) Request Thread DEf6D1zaxo6u.14 with state PROCESSING
> 15:51:59,452 DEBUG [org.teiid.CONNECTOR] (Worker13_QueryProcessorQueue49) DEf6D1zaxo6u.14.1.14 Remove State
> 15:51:59,452 DEBUG [org.teiid.CONNECTOR] (Worker13_QueryProcessorQueue49) DEf6D1zaxo6u.14.1.14 Processing Close : SELECT g_0.IntKey, g_0.StringKey, convert(g_0.FloatNum, float), convert(g_0.ByteNum, byte), (SELECT convert(g_1.ByteNum, byte) FROM Source.SmallA AS g_1 WHERE (convert(convert(g_1.ByteNum, byte), long) = g_0.LongNum) AND (g_1.IntKey = 10)) FROM Source.SmallA AS g_0
> 15:51:59,453 DEBUG [org.teiid.CONNECTOR] (Worker13_QueryProcessorQueue49) DEf6D1zaxo6u.14.1.14 Closed execution
> 15:51:59,453 DEBUG [org.teiid.CONNECTOR] (Worker13_QueryProcessorQueue49) DEf6D1zaxo6u.14.1.14 Closed connection
> 15:51:59,454 DEBUG [org.teiid.PROCESSOR] (Worker13_QueryProcessorQueue49) QueryProcessor: closing processor
> 15:51:59,454 WARN [org.teiid.PROCESSOR] (Worker13_QueryProcessorQueue49) TEIID30020 Processing exception for request DEf6D1zaxo6u.14 'TEIID30504 Source: 65536 TEIID11008:TEIID11004 Error executing statement(s): [SQL: SELECT g_0.intkey, g_0.stringkey, g_0.floatnum, g_0.bytenum, (SELECT g_1.bytenum FROM smalla AS g_1 WHERE cast(g_1.bytenum AS bigint) = g_0.longnum AND g_1.intkey = 10) FROM smalla AS g_0]'. Originally TeiidProcessingException 'Unsupported correlated subquery type' NoApplyNodeLeftChecker.java:42.: org.teiid.core.TeiidProcessingException: TEIID30504 Source: 65536 TEIID11008:TEIID11004 Error executing statement(s): [SQL: SELECT g_0.intkey, g_0.stringkey, g_0.floatnum, g_0.bytenum, (SELECT g_1.bytenum FROM smalla AS g_1 WHERE cast(g_1.bytenum AS bigint) = g_0.longnum AND g_1.intkey = 10) FROM smalla AS g_0]
> at org.teiid.dqp.internal.process.DataTierTupleSource.exceptionOccurred(DataTierTupleSource.java:401) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:161) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:391) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:145) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:164) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:472) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:348) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:274) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [rt.jar:1.8.0_92]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [rt.jar:1.8.0_92]
> at java.lang.Thread.run(Thread.java:745) [rt.jar:1.8.0_92]
> Caused by: org.teiid.translator.jdbc.JDBCExecutionException: 65536 TEIID11008:TEIID11004 Error executing statement(s): [SQL: SELECT g_0.intkey, g_0.stringkey, g_0.floatnum, g_0.bytenum, (SELECT g_1.bytenum FROM smalla AS g_1 WHERE cast(g_1.bytenum AS bigint) = g_0.longnum AND g_1.intkey = 10) FROM smalla AS g_0]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:364) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.8.0_92]
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) [rt.jar:1.8.0_92]
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_92]
> at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_92]
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at com.sun.proxy.$Proxy48.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at java.util.concurrent.FutureTask.run(FutureTask.java:266) [rt.jar:1.8.0_92]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> ... 6 more
> Caused by: java.sql.SQLException: Query failed (#20170106_145159_04181_k4ss5): Unsupported correlated subquery type
> at com.facebook.presto.jdbc.PrestoResultSet.resultsException(PrestoResultSet.java:1799)
> at com.facebook.presto.jdbc.PrestoResultSet.getColumns(PrestoResultSet.java:1747)
> at com.facebook.presto.jdbc.PrestoResultSet.<init>(PrestoResultSet.java:125)
> at com.facebook.presto.jdbc.PrestoStatement.execute(PrestoStatement.java:212)
> at com.facebook.presto.jdbc.PrestoStatement.executeQuery(PrestoStatement.java:69)
> at org.jboss.jca.adapters.jdbc.WrappedStatement.executeQuery(WrappedStatement.java:344)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:119)
> ... 18 more
> Caused by: java.lang.IllegalArgumentException: Unsupported correlated subquery type
> at com.facebook.presto.sql.planner.sanity.NoApplyNodeLeftChecker$1.visitApply(NoApplyNodeLeftChecker.java:42)
> at com.facebook.presto.sql.planner.plan.ApplyNode.accept(ApplyNode.java:142)
> at com.facebook.presto.sql.planner.SimplePlanVisitor.visitPlan(SimplePlanVisitor.java:26)
> at com.facebook.presto.sql.planner.SimplePlanVisitor.visitPlan(SimplePlanVisitor.java:19)
> at com.facebook.presto.sql.planner.plan.PlanVisitor.visitProject(PlanVisitor.java:40)
> at com.facebook.presto.sql.planner.plan.ProjectNode.accept(ProjectNode.java:81)
> at com.facebook.presto.sql.planner.SimplePlanVisitor.visitPlan(SimplePlanVisitor.java:26)
> at com.facebook.presto.sql.planner.SimplePlanVisitor.visitPlan(SimplePlanVisitor.java:19)
> at com.facebook.presto.sql.planner.plan.PlanVisitor.visitExchange(PlanVisitor.java:175)
> at com.facebook.presto.sql.planner.plan.ExchangeNode.accept(ExchangeNode.java:209)
> at com.facebook.presto.sql.planner.SimplePlanVisitor.visitPlan(SimplePlanVisitor.java:26)
> at com.facebook.presto.sql.planner.SimplePlanVisitor.visitPlan(SimplePlanVisitor.java:19)
> at com.facebook.presto.sql.planner.plan.PlanVisitor.visitOutput(PlanVisitor.java:50)
> at com.facebook.presto.sql.planner.plan.OutputNode.accept(OutputNode.java:81)
> at com.facebook.presto.sql.planner.sanity.NoApplyNodeLeftChecker.validate(NoApplyNodeLeftChecker.java:33)
> at com.facebook.presto.sql.planner.sanity.PlanSanityChecker.lambda$validate$0(PlanSanityChecker.java:44)
> at java.lang.Iterable.forEach(Iterable.java:75) [rt.jar:1.8.0_92]
> at com.facebook.presto.sql.planner.sanity.PlanSanityChecker.validate(PlanSanityChecker.java:44)
> at com.facebook.presto.sql.planner.LogicalPlanner.plan(LogicalPlanner.java:127)
> at com.facebook.presto.sql.planner.LogicalPlanner.plan(LogicalPlanner.java:111)
> at com.facebook.presto.execution.SqlQueryExecution.doAnalyzeQuery(SqlQueryExecution.java:292)
> at com.facebook.presto.execution.SqlQueryExecution.analyzeQuery(SqlQueryExecution.java:271)
> at com.facebook.presto.execution.SqlQueryExecution.start(SqlQueryExecution.java:229)
> at com.facebook.presto.execution.QueuedExecution.lambda$start$1(QueuedExecution.java:62)
> ... 3 more
> 15:51:59,460 DEBUG [org.teiid.PROCESSOR] (Worker13_QueryProcessorQueue49) Removing tuplesource for the request DEf6D1zaxo6u.14
> 15:51:59,460 DEBUG [org.teiid.BUFFER_MGR] (Worker13_QueryProcessorQueue49) Removing TupleBuffer: 19
> 15:51:59,460 DEBUG [org.teiid.PROCESSOR] (Worker13_QueryProcessorQueue49) DEf6D1zaxo6u.14 Finished Processing
> 15:51:59,460 DEBUG [org.teiid.PROCESSOR] (Worker13_QueryProcessorQueue49) org.teiid.core.TeiidProcessingException: TEIID30504 Source: 65536 TEIID11008:TEIID11004 Error executing statement(s): [SQL: SELECT g_0.intkey, g_0.stringkey, g_0.floatnum, g_0.bytenum, (SELECT g_1.bytenum FROM smalla AS g_1 WHERE cast(g_1.bytenum AS bigint) = g_0.longnum AND g_1.intkey = 10) FROM smalla AS g_0] Sending error to client DEf6D1zaxo6u.14
> {code}
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
9 years, 2 months
[JBoss JIRA] (TEIID-4703) The Oracle translator did not limit the number of values in "IN" clause, which were required by Oracle database.
by SHI HONG CHIN (JIRA)
SHI HONG CHIN created TEIID-4703:
------------------------------------
Summary: The Oracle translator did not limit the number of values in "IN" clause, which were required by Oracle database.
Key: TEIID-4703
URL: https://issues.jboss.org/browse/TEIID-4703
Project: Teiid
Issue Type: Bug
Affects Versions: 8.13.4
Reporter: SHI HONG CHIN
Assignee: Steven Hawkins
Database: Oracle + Firebird + Microsoft SQL Server.
Oracle limit the maximum number of values in "IN" clause to 1000.
In teiid, I am using the built-in Oracle translator.
When running some SQL queries, Oracle database reported an error "No more data to read from socket". I had set the log level of Teiid to "ALL". According to the WildFly log files, Teiid executed a query with more than 10000 values in the "IN" clause.
I tried to copy the whole SQL from WildFly log file into Oracle SQL Developer and execute it, the same error occurred. Then, I tried to edit the query by delete many values from the "IN" clause and then run the query again, and the query success.
For teiid, as a workaround, I overrided the oracle translator and set the property of "MaxInCriteriaSize" to 50. After that, all SQL queries run success without any issue.
I suggest that, by default, the built-in Oracle translator limit the number of values in "IN" clause to maximum 1000 values, and this behavior can be changed through properties.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
9 years, 2 months
[JBoss JIRA] (TEIID-4687) PrestoDB translator - unsupported corelated subquery type
by Kylin Soong (JIRA)
[ https://issues.jboss.org/browse/TEIID-4687?page=com.atlassian.jira.plugin... ]
Kylin Soong commented on TEIID-4687:
------------------------------------
I considered to much of this issue, from 0.163 document, "Support for correlated subqueries is limited. Not every standard form is supported." So I think we can set disable Correlated subquery
[1] https://prestodb.io/docs/current/sql/select.html
> PrestoDB translator - unsupported corelated subquery type
> ---------------------------------------------------------
>
> Key: TEIID-4687
> URL: https://issues.jboss.org/browse/TEIID-4687
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.8.6_3
> Reporter: Juraj Duráni
> Assignee: Kylin Soong
> Fix For: 9.2
>
>
> Running query \[1\], PrestoDB throws exception (log \[2\]) for translated query.
> {code:sql|title=\[1\]}
> SELECT intkey, stringkey, floatnum, bytenum, (SELECT bytenum FROM bqt1.smalla AS b WHERE (bytenum = a.longnum) AND (intkey = '10')) AS longnum FROM bqt1.smalla AS a
> {code}
> {code:plain|title=\[2\]}
> 15:51:58,928 DEBUG [org.teiid.TRANSPORT] (New I/O worker #2) processing message:MessageHolder: key=386 contents=Invoke interface org.teiid.client.DQP.executeRequest
> 15:51:58,930 DEBUG [org.teiid.PROCESSOR] (Worker13_QueryProcessorQueue47) Request Thread DEf6D1zaxo6u.14 with state NEW
> 15:51:58,930 DEBUG [org.teiid.PROCESSOR] (Worker13_QueryProcessorQueue47) DEf6D1zaxo6u.14 Command has no cache hint and result set cache mode is not on.
> 15:51:58,931 DEBUG [org.teiid.PROCESSOR] (Worker13_QueryProcessorQueue47) DEf6D1zaxo6u.14 executing SELECT intkey, stringkey, floatnum, bytenum, (SELECT bytenum FROM bqt1.smalla AS b WHERE (bytenum = a.longnum) AND (intkey = '10')) AS longnum FROM bqt1.smalla AS a
> 15:51:58,936 DEBUG [org.teiid.PROCESSOR] (Worker13_QueryProcessorQueue47) ProcessTree for DEf6D1zaxo6u.14 AccessNode(1) output=[SmallA__1.IntKey, SmallA__1.StringKey, convert(SmallA__1.FloatNum, float), convert(SmallA__1.ByteNum, byte), (SELECT convert(g_0.ByteNum, byte) AS c_0 FROM Source.SmallA AS g_0 WHERE (convert(convert(g_0.ByteNum, byte), long) = SmallA__1.LongNum) AND (g_0.IntKey = 10)) AS longnum] SELECT g_0.IntKey, g_0.StringKey, convert(g_0.FloatNum, float), convert(g_0.ByteNum, byte), (SELECT convert(g_1.ByteNum, byte) FROM Source.SmallA AS g_1 WHERE (convert(convert(g_1.ByteNum, byte), long) = g_0.LongNum) AND (g_1.IntKey = 10)) FROM Source.SmallA AS g_0
> 15:51:58,938 DEBUG [org.teiid.TXN_LOG] (Worker13_QueryProcessorQueue47) before getOrCreateTransactionContext:org.teiid.dqp.internal.process.TransactionServerImpl@65d4dce6(DEf6D1zaxo6u)
> 15:51:58,938 DEBUG [org.teiid.TXN_LOG] (Worker13_QueryProcessorQueue47) after getOrCreateTransactionContext : DEf6D1zaxo6u NONE ID:NONE
> 15:51:58,939 DEBUG [org.teiid.BUFFER_MGR] (Worker13_QueryProcessorQueue47) Creating TupleBuffer: 19 [a.IntKey, a.StringKey, a.FloatNum, a.ByteNum, (SELECT b.ByteNum FROM BQT1.SmallA AS b WHERE (convert(b.ByteNum, long) = a.LongNum) AND (b.IntKey = 10) LIMIT 2) AS longnum] [class java.lang.Integer, class java.lang.String, class java.lang.Float, class java.lang.Byte, class java.lang.Byte] batch size 1024 of type PROCESSOR
> 15:51:58,940 DEBUG [org.teiid.CONNECTOR] (Worker13_QueryProcessorQueue47) DEf6D1zaxo6u.14.1.14 Create State
> 15:51:58,941 DEBUG [org.teiid.BUFFER_MGR] (Worker13_QueryProcessorQueue47) DEf6D1zaxo6u.14.1.14 Blocking on source query DEf6D1zaxo6u.14.1.14
> 15:51:58,942 DEBUG [org.teiid.BUFFER_MGR] (Worker13_QueryProcessorQueue47) DEf6D1zaxo6u.14 Blocking on source request(s).
> 15:51:58,942 DEBUG [org.teiid.PROCESSOR] (Worker13_QueryProcessorQueue47) Request Thread DEf6D1zaxo6u.14 - processor blocked
> 15:51:58,942 DEBUG [org.teiid.PROCESSOR] (Worker14_QueryProcessorQueue48) Running task for parent thread Worker13_QueryProcessorQueue47
> 15:51:58,943 DEBUG [org.teiid.CONNECTOR] (Worker14_QueryProcessorQueue48) DEf6D1zaxo6u.14.1.14 Processing NEW request: SELECT g_0.IntKey, g_0.StringKey, convert(g_0.FloatNum, float), convert(g_0.ByteNum, byte), (SELECT convert(g_1.ByteNum, byte) FROM Source.SmallA AS g_1 WHERE (convert(convert(g_1.ByteNum, byte), long) = g_0.LongNum) AND (g_1.IntKey = 10)) FROM Source.SmallA AS g_0
> 15:51:58,943 DEBUG [org.teiid.CONNECTOR] (Worker14_QueryProcessorQueue48) DEf6D1zaxo6u.14.1.14 Obtained execution
> 15:51:58,944 DEBUG [org.teiid.CONNECTOR] (Worker14_QueryProcessorQueue48) Source-specific command: SELECT g_0.intkey, g_0.stringkey, g_0.floatnum, g_0.bytenum, (SELECT g_1.bytenum FROM smalla AS g_1 WHERE cast(g_1.bytenum AS bigint) = g_0.longnum AND g_1.intkey = 10) FROM smalla AS g_0
> 15:51:59,443 WARN [org.teiid.CONNECTOR] (Worker14_QueryProcessorQueue48) Connector worker process failed for atomic-request=DEf6D1zaxo6u.14.1.14: org.teiid.translator.jdbc.JDBCExecutionException: 65536 TEIID11008:TEIID11004 Error executing statement(s): [SQL: SELECT g_0.intkey, g_0.stringkey, g_0.floatnum, g_0.bytenum, (SELECT g_1.bytenum FROM smalla AS g_1 WHERE cast(g_1.bytenum AS bigint) = g_0.longnum AND g_1.intkey = 10) FROM smalla AS g_0]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:364)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.8.0_92]
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) [rt.jar:1.8.0_92]
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_92]
> at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_92]
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy48.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at java.util.concurrent.FutureTask.run(FutureTask.java:266) [rt.jar:1.8.0_92]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [rt.jar:1.8.0_92]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [rt.jar:1.8.0_92]
> at java.lang.Thread.run(Thread.java:745) [rt.jar:1.8.0_92]
> Caused by: java.sql.SQLException: Query failed (#20170106_145159_04181_k4ss5): Unsupported correlated subquery type
> at com.facebook.presto.jdbc.PrestoResultSet.resultsException(PrestoResultSet.java:1799)
> at com.facebook.presto.jdbc.PrestoResultSet.getColumns(PrestoResultSet.java:1747)
> at com.facebook.presto.jdbc.PrestoResultSet.<init>(PrestoResultSet.java:125)
> at com.facebook.presto.jdbc.PrestoStatement.execute(PrestoStatement.java:212)
> at com.facebook.presto.jdbc.PrestoStatement.executeQuery(PrestoStatement.java:69)
> at org.jboss.jca.adapters.jdbc.WrappedStatement.executeQuery(WrappedStatement.java:344)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:119) [translator-jdbc-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> ... 18 more
> Caused by: java.lang.IllegalArgumentException: Unsupported correlated subquery type
> at com.facebook.presto.sql.planner.sanity.NoApplyNodeLeftChecker$1.visitApply(NoApplyNodeLeftChecker.java:42)
> at com.facebook.presto.sql.planner.plan.ApplyNode.accept(ApplyNode.java:142)
> at com.facebook.presto.sql.planner.SimplePlanVisitor.visitPlan(SimplePlanVisitor.java:26)
> at com.facebook.presto.sql.planner.SimplePlanVisitor.visitPlan(SimplePlanVisitor.java:19)
> at com.facebook.presto.sql.planner.plan.PlanVisitor.visitProject(PlanVisitor.java:40)
> at com.facebook.presto.sql.planner.plan.ProjectNode.accept(ProjectNode.java:81)
> at com.facebook.presto.sql.planner.SimplePlanVisitor.visitPlan(SimplePlanVisitor.java:26)
> at com.facebook.presto.sql.planner.SimplePlanVisitor.visitPlan(SimplePlanVisitor.java:19)
> at com.facebook.presto.sql.planner.plan.PlanVisitor.visitExchange(PlanVisitor.java:175)
> at com.facebook.presto.sql.planner.plan.ExchangeNode.accept(ExchangeNode.java:209)
> at com.facebook.presto.sql.planner.SimplePlanVisitor.visitPlan(SimplePlanVisitor.java:26)
> at com.facebook.presto.sql.planner.SimplePlanVisitor.visitPlan(SimplePlanVisitor.java:19)
> at com.facebook.presto.sql.planner.plan.PlanVisitor.visitOutput(PlanVisitor.java:50)
> at com.facebook.presto.sql.planner.plan.OutputNode.accept(OutputNode.java:81)
> at com.facebook.presto.sql.planner.sanity.NoApplyNodeLeftChecker.validate(NoApplyNodeLeftChecker.java:33)
> at com.facebook.presto.sql.planner.sanity.PlanSanityChecker.lambda$validate$0(PlanSanityChecker.java:44)
> at java.lang.Iterable.forEach(Iterable.java:75) [rt.jar:1.8.0_92]
> at com.facebook.presto.sql.planner.sanity.PlanSanityChecker.validate(PlanSanityChecker.java:44)
> at com.facebook.presto.sql.planner.LogicalPlanner.plan(LogicalPlanner.java:127)
> at com.facebook.presto.sql.planner.LogicalPlanner.plan(LogicalPlanner.java:111)
> at com.facebook.presto.execution.SqlQueryExecution.doAnalyzeQuery(SqlQueryExecution.java:292)
> at com.facebook.presto.execution.SqlQueryExecution.analyzeQuery(SqlQueryExecution.java:271)
> at com.facebook.presto.execution.SqlQueryExecution.start(SqlQueryExecution.java:229)
> at com.facebook.presto.execution.QueuedExecution.lambda$start$1(QueuedExecution.java:62)
> ... 3 more
> 15:51:59,452 DEBUG [org.teiid.PROCESSOR] (Worker13_QueryProcessorQueue49) Request Thread DEf6D1zaxo6u.14 with state PROCESSING
> 15:51:59,452 DEBUG [org.teiid.CONNECTOR] (Worker13_QueryProcessorQueue49) DEf6D1zaxo6u.14.1.14 Remove State
> 15:51:59,452 DEBUG [org.teiid.CONNECTOR] (Worker13_QueryProcessorQueue49) DEf6D1zaxo6u.14.1.14 Processing Close : SELECT g_0.IntKey, g_0.StringKey, convert(g_0.FloatNum, float), convert(g_0.ByteNum, byte), (SELECT convert(g_1.ByteNum, byte) FROM Source.SmallA AS g_1 WHERE (convert(convert(g_1.ByteNum, byte), long) = g_0.LongNum) AND (g_1.IntKey = 10)) FROM Source.SmallA AS g_0
> 15:51:59,453 DEBUG [org.teiid.CONNECTOR] (Worker13_QueryProcessorQueue49) DEf6D1zaxo6u.14.1.14 Closed execution
> 15:51:59,453 DEBUG [org.teiid.CONNECTOR] (Worker13_QueryProcessorQueue49) DEf6D1zaxo6u.14.1.14 Closed connection
> 15:51:59,454 DEBUG [org.teiid.PROCESSOR] (Worker13_QueryProcessorQueue49) QueryProcessor: closing processor
> 15:51:59,454 WARN [org.teiid.PROCESSOR] (Worker13_QueryProcessorQueue49) TEIID30020 Processing exception for request DEf6D1zaxo6u.14 'TEIID30504 Source: 65536 TEIID11008:TEIID11004 Error executing statement(s): [SQL: SELECT g_0.intkey, g_0.stringkey, g_0.floatnum, g_0.bytenum, (SELECT g_1.bytenum FROM smalla AS g_1 WHERE cast(g_1.bytenum AS bigint) = g_0.longnum AND g_1.intkey = 10) FROM smalla AS g_0]'. Originally TeiidProcessingException 'Unsupported correlated subquery type' NoApplyNodeLeftChecker.java:42.: org.teiid.core.TeiidProcessingException: TEIID30504 Source: 65536 TEIID11008:TEIID11004 Error executing statement(s): [SQL: SELECT g_0.intkey, g_0.stringkey, g_0.floatnum, g_0.bytenum, (SELECT g_1.bytenum FROM smalla AS g_1 WHERE cast(g_1.bytenum AS bigint) = g_0.longnum AND g_1.intkey = 10) FROM smalla AS g_0]
> at org.teiid.dqp.internal.process.DataTierTupleSource.exceptionOccurred(DataTierTupleSource.java:401) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:161) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:391) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:145) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:164) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:472) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:348) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:274) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [rt.jar:1.8.0_92]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [rt.jar:1.8.0_92]
> at java.lang.Thread.run(Thread.java:745) [rt.jar:1.8.0_92]
> Caused by: org.teiid.translator.jdbc.JDBCExecutionException: 65536 TEIID11008:TEIID11004 Error executing statement(s): [SQL: SELECT g_0.intkey, g_0.stringkey, g_0.floatnum, g_0.bytenum, (SELECT g_1.bytenum FROM smalla AS g_1 WHERE cast(g_1.bytenum AS bigint) = g_0.longnum AND g_1.intkey = 10) FROM smalla AS g_0]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:364) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.8.0_92]
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) [rt.jar:1.8.0_92]
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_92]
> at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_92]
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at com.sun.proxy.$Proxy48.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at java.util.concurrent.FutureTask.run(FutureTask.java:266) [rt.jar:1.8.0_92]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> ... 6 more
> Caused by: java.sql.SQLException: Query failed (#20170106_145159_04181_k4ss5): Unsupported correlated subquery type
> at com.facebook.presto.jdbc.PrestoResultSet.resultsException(PrestoResultSet.java:1799)
> at com.facebook.presto.jdbc.PrestoResultSet.getColumns(PrestoResultSet.java:1747)
> at com.facebook.presto.jdbc.PrestoResultSet.<init>(PrestoResultSet.java:125)
> at com.facebook.presto.jdbc.PrestoStatement.execute(PrestoStatement.java:212)
> at com.facebook.presto.jdbc.PrestoStatement.executeQuery(PrestoStatement.java:69)
> at org.jboss.jca.adapters.jdbc.WrappedStatement.executeQuery(WrappedStatement.java:344)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:119)
> ... 18 more
> Caused by: java.lang.IllegalArgumentException: Unsupported correlated subquery type
> at com.facebook.presto.sql.planner.sanity.NoApplyNodeLeftChecker$1.visitApply(NoApplyNodeLeftChecker.java:42)
> at com.facebook.presto.sql.planner.plan.ApplyNode.accept(ApplyNode.java:142)
> at com.facebook.presto.sql.planner.SimplePlanVisitor.visitPlan(SimplePlanVisitor.java:26)
> at com.facebook.presto.sql.planner.SimplePlanVisitor.visitPlan(SimplePlanVisitor.java:19)
> at com.facebook.presto.sql.planner.plan.PlanVisitor.visitProject(PlanVisitor.java:40)
> at com.facebook.presto.sql.planner.plan.ProjectNode.accept(ProjectNode.java:81)
> at com.facebook.presto.sql.planner.SimplePlanVisitor.visitPlan(SimplePlanVisitor.java:26)
> at com.facebook.presto.sql.planner.SimplePlanVisitor.visitPlan(SimplePlanVisitor.java:19)
> at com.facebook.presto.sql.planner.plan.PlanVisitor.visitExchange(PlanVisitor.java:175)
> at com.facebook.presto.sql.planner.plan.ExchangeNode.accept(ExchangeNode.java:209)
> at com.facebook.presto.sql.planner.SimplePlanVisitor.visitPlan(SimplePlanVisitor.java:26)
> at com.facebook.presto.sql.planner.SimplePlanVisitor.visitPlan(SimplePlanVisitor.java:19)
> at com.facebook.presto.sql.planner.plan.PlanVisitor.visitOutput(PlanVisitor.java:50)
> at com.facebook.presto.sql.planner.plan.OutputNode.accept(OutputNode.java:81)
> at com.facebook.presto.sql.planner.sanity.NoApplyNodeLeftChecker.validate(NoApplyNodeLeftChecker.java:33)
> at com.facebook.presto.sql.planner.sanity.PlanSanityChecker.lambda$validate$0(PlanSanityChecker.java:44)
> at java.lang.Iterable.forEach(Iterable.java:75) [rt.jar:1.8.0_92]
> at com.facebook.presto.sql.planner.sanity.PlanSanityChecker.validate(PlanSanityChecker.java:44)
> at com.facebook.presto.sql.planner.LogicalPlanner.plan(LogicalPlanner.java:127)
> at com.facebook.presto.sql.planner.LogicalPlanner.plan(LogicalPlanner.java:111)
> at com.facebook.presto.execution.SqlQueryExecution.doAnalyzeQuery(SqlQueryExecution.java:292)
> at com.facebook.presto.execution.SqlQueryExecution.analyzeQuery(SqlQueryExecution.java:271)
> at com.facebook.presto.execution.SqlQueryExecution.start(SqlQueryExecution.java:229)
> at com.facebook.presto.execution.QueuedExecution.lambda$start$1(QueuedExecution.java:62)
> ... 3 more
> 15:51:59,460 DEBUG [org.teiid.PROCESSOR] (Worker13_QueryProcessorQueue49) Removing tuplesource for the request DEf6D1zaxo6u.14
> 15:51:59,460 DEBUG [org.teiid.BUFFER_MGR] (Worker13_QueryProcessorQueue49) Removing TupleBuffer: 19
> 15:51:59,460 DEBUG [org.teiid.PROCESSOR] (Worker13_QueryProcessorQueue49) DEf6D1zaxo6u.14 Finished Processing
> 15:51:59,460 DEBUG [org.teiid.PROCESSOR] (Worker13_QueryProcessorQueue49) org.teiid.core.TeiidProcessingException: TEIID30504 Source: 65536 TEIID11008:TEIID11004 Error executing statement(s): [SQL: SELECT g_0.intkey, g_0.stringkey, g_0.floatnum, g_0.bytenum, (SELECT g_1.bytenum FROM smalla AS g_1 WHERE cast(g_1.bytenum AS bigint) = g_0.longnum AND g_1.intkey = 10) FROM smalla AS g_0] Sending error to client DEf6D1zaxo6u.14
> {code}
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
9 years, 2 months
[JBoss JIRA] (TEIID-3952) Update to updatable internal materialized view should update the materialized view as well as the database
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3952?page=com.atlassian.jira.plugin... ]
Work on TEIID-3952 started by Steven Hawkins.
---------------------------------------------
> Update to updatable internal materialized view should update the materialized view as well as the database
> ----------------------------------------------------------------------------------------------------------
>
> Key: TEIID-3952
> URL: https://issues.jboss.org/browse/TEIID-3952
> Project: Teiid
> Issue Type: Enhancement
> Components: VDB
> Affects Versions: 8.7
> Environment: Red Hat JBoss Data Virtualization 6.2 based on Teiid 8.7.x
> Reporter: Andy Yuen
> Assignee: Steven Hawkins
> Labels: jboss
> Fix For: 9.2
>
>
> Updating an updatable internal materized view updates the database but not the materialized view at present. The requested enhancement is that they, both, should be updated.
> Setup
> Client - SquirrelSQL to access JDV
> JDV 6.2 - with an updatable nternal materialized view of one table from the data source
> Data Source - just one data source: MySQL
> I can see from the console that the target table/view has been materialized
> Then I did the following:
> 1) select a row from the materialized table
> 2) update a field in a row in the materialized view
> 3) select that row (value unchanged ie, same as int 1) - tried multiple times
> 4) issue EXEC SYSADMIN.refreshMatViewRow(...) using primary key for the row that has been changed
> 5) select that row (value unchanged) - now I can see the changed data
> This behaviour is counter-intuitive because I was expecting that since it is an updatable materialized view, my change will write through the materialized view ie, change both the materialized view as well as the database. But in this case, it changed the database and not the materialized view?
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
9 years, 2 months
[JBoss JIRA] (TEIID-4686) PrestoDB translator - ordering of joined tables fails
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-4686?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-4686:
-------------------------------------
I do not think it worth the effort right now given PrestoDB needs to fix the support order by multiple order by. My comment is leave disable configuration to the user directly as it is fine in single order by case.
> PrestoDB translator - ordering of joined tables fails
> -----------------------------------------------------
>
> Key: TEIID-4686
> URL: https://issues.jboss.org/browse/TEIID-4686
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.8.6_3
> Reporter: Juraj Duráni
> Assignee: Kylin Soong
> Fix For: 9.2.1
>
>
> Issuing query like \[1\] causes an exception in PrestoDB. This seems to be PrestoDB issue. I found similar issue \[2\], but that one should be fixed. I have created a new issue \[3\]. Removing one column from ORDER BY, query returns correct result.
> Not sure if we can do something about it without avoiding pushing JOIN.
> I suggest either to fix this (if simple enough) or add a note to the documentation.
> \[1\]
> {code:sql}
> SELECT a.intkey, b.intkey FROM BQT1.SmallA AS a INNER JOIN BQT1.SmallB AS b on a.intkey = b.intkey ORDER BY 1, 2
> {code}
> \[2\] https://github.com/prestodb/presto/issues/1621
> \[3\] https://github.com/prestodb/presto/issues/7010
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
9 years, 2 months
[JBoss JIRA] (TEIID-4687) PrestoDB translator - unsupported corelated subquery type
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4687?page=com.atlassian.jira.plugin... ]
Steven Hawkins reopened TEIID-4687:
-----------------------------------
I resolved this too soon. There are two existing capabilities that are relevant here. 1. we can disable all correlated subqueires 2. we can disable scalar subquery projection
So the question is does prestodb support correlated subqueries in other places in the query?
> PrestoDB translator - unsupported corelated subquery type
> ---------------------------------------------------------
>
> Key: TEIID-4687
> URL: https://issues.jboss.org/browse/TEIID-4687
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.8.6_3
> Reporter: Juraj Duráni
> Assignee: Kylin Soong
> Fix For: 9.2
>
>
> Running query \[1\], PrestoDB throws exception (log \[2\]) for translated query.
> {code:sql|title=\[1\]}
> SELECT intkey, stringkey, floatnum, bytenum, (SELECT bytenum FROM bqt1.smalla AS b WHERE (bytenum = a.longnum) AND (intkey = '10')) AS longnum FROM bqt1.smalla AS a
> {code}
> {code:plain|title=\[2\]}
> 15:51:58,928 DEBUG [org.teiid.TRANSPORT] (New I/O worker #2) processing message:MessageHolder: key=386 contents=Invoke interface org.teiid.client.DQP.executeRequest
> 15:51:58,930 DEBUG [org.teiid.PROCESSOR] (Worker13_QueryProcessorQueue47) Request Thread DEf6D1zaxo6u.14 with state NEW
> 15:51:58,930 DEBUG [org.teiid.PROCESSOR] (Worker13_QueryProcessorQueue47) DEf6D1zaxo6u.14 Command has no cache hint and result set cache mode is not on.
> 15:51:58,931 DEBUG [org.teiid.PROCESSOR] (Worker13_QueryProcessorQueue47) DEf6D1zaxo6u.14 executing SELECT intkey, stringkey, floatnum, bytenum, (SELECT bytenum FROM bqt1.smalla AS b WHERE (bytenum = a.longnum) AND (intkey = '10')) AS longnum FROM bqt1.smalla AS a
> 15:51:58,936 DEBUG [org.teiid.PROCESSOR] (Worker13_QueryProcessorQueue47) ProcessTree for DEf6D1zaxo6u.14 AccessNode(1) output=[SmallA__1.IntKey, SmallA__1.StringKey, convert(SmallA__1.FloatNum, float), convert(SmallA__1.ByteNum, byte), (SELECT convert(g_0.ByteNum, byte) AS c_0 FROM Source.SmallA AS g_0 WHERE (convert(convert(g_0.ByteNum, byte), long) = SmallA__1.LongNum) AND (g_0.IntKey = 10)) AS longnum] SELECT g_0.IntKey, g_0.StringKey, convert(g_0.FloatNum, float), convert(g_0.ByteNum, byte), (SELECT convert(g_1.ByteNum, byte) FROM Source.SmallA AS g_1 WHERE (convert(convert(g_1.ByteNum, byte), long) = g_0.LongNum) AND (g_1.IntKey = 10)) FROM Source.SmallA AS g_0
> 15:51:58,938 DEBUG [org.teiid.TXN_LOG] (Worker13_QueryProcessorQueue47) before getOrCreateTransactionContext:org.teiid.dqp.internal.process.TransactionServerImpl@65d4dce6(DEf6D1zaxo6u)
> 15:51:58,938 DEBUG [org.teiid.TXN_LOG] (Worker13_QueryProcessorQueue47) after getOrCreateTransactionContext : DEf6D1zaxo6u NONE ID:NONE
> 15:51:58,939 DEBUG [org.teiid.BUFFER_MGR] (Worker13_QueryProcessorQueue47) Creating TupleBuffer: 19 [a.IntKey, a.StringKey, a.FloatNum, a.ByteNum, (SELECT b.ByteNum FROM BQT1.SmallA AS b WHERE (convert(b.ByteNum, long) = a.LongNum) AND (b.IntKey = 10) LIMIT 2) AS longnum] [class java.lang.Integer, class java.lang.String, class java.lang.Float, class java.lang.Byte, class java.lang.Byte] batch size 1024 of type PROCESSOR
> 15:51:58,940 DEBUG [org.teiid.CONNECTOR] (Worker13_QueryProcessorQueue47) DEf6D1zaxo6u.14.1.14 Create State
> 15:51:58,941 DEBUG [org.teiid.BUFFER_MGR] (Worker13_QueryProcessorQueue47) DEf6D1zaxo6u.14.1.14 Blocking on source query DEf6D1zaxo6u.14.1.14
> 15:51:58,942 DEBUG [org.teiid.BUFFER_MGR] (Worker13_QueryProcessorQueue47) DEf6D1zaxo6u.14 Blocking on source request(s).
> 15:51:58,942 DEBUG [org.teiid.PROCESSOR] (Worker13_QueryProcessorQueue47) Request Thread DEf6D1zaxo6u.14 - processor blocked
> 15:51:58,942 DEBUG [org.teiid.PROCESSOR] (Worker14_QueryProcessorQueue48) Running task for parent thread Worker13_QueryProcessorQueue47
> 15:51:58,943 DEBUG [org.teiid.CONNECTOR] (Worker14_QueryProcessorQueue48) DEf6D1zaxo6u.14.1.14 Processing NEW request: SELECT g_0.IntKey, g_0.StringKey, convert(g_0.FloatNum, float), convert(g_0.ByteNum, byte), (SELECT convert(g_1.ByteNum, byte) FROM Source.SmallA AS g_1 WHERE (convert(convert(g_1.ByteNum, byte), long) = g_0.LongNum) AND (g_1.IntKey = 10)) FROM Source.SmallA AS g_0
> 15:51:58,943 DEBUG [org.teiid.CONNECTOR] (Worker14_QueryProcessorQueue48) DEf6D1zaxo6u.14.1.14 Obtained execution
> 15:51:58,944 DEBUG [org.teiid.CONNECTOR] (Worker14_QueryProcessorQueue48) Source-specific command: SELECT g_0.intkey, g_0.stringkey, g_0.floatnum, g_0.bytenum, (SELECT g_1.bytenum FROM smalla AS g_1 WHERE cast(g_1.bytenum AS bigint) = g_0.longnum AND g_1.intkey = 10) FROM smalla AS g_0
> 15:51:59,443 WARN [org.teiid.CONNECTOR] (Worker14_QueryProcessorQueue48) Connector worker process failed for atomic-request=DEf6D1zaxo6u.14.1.14: org.teiid.translator.jdbc.JDBCExecutionException: 65536 TEIID11008:TEIID11004 Error executing statement(s): [SQL: SELECT g_0.intkey, g_0.stringkey, g_0.floatnum, g_0.bytenum, (SELECT g_1.bytenum FROM smalla AS g_1 WHERE cast(g_1.bytenum AS bigint) = g_0.longnum AND g_1.intkey = 10) FROM smalla AS g_0]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:364)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.8.0_92]
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) [rt.jar:1.8.0_92]
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_92]
> at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_92]
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy48.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at java.util.concurrent.FutureTask.run(FutureTask.java:266) [rt.jar:1.8.0_92]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [rt.jar:1.8.0_92]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [rt.jar:1.8.0_92]
> at java.lang.Thread.run(Thread.java:745) [rt.jar:1.8.0_92]
> Caused by: java.sql.SQLException: Query failed (#20170106_145159_04181_k4ss5): Unsupported correlated subquery type
> at com.facebook.presto.jdbc.PrestoResultSet.resultsException(PrestoResultSet.java:1799)
> at com.facebook.presto.jdbc.PrestoResultSet.getColumns(PrestoResultSet.java:1747)
> at com.facebook.presto.jdbc.PrestoResultSet.<init>(PrestoResultSet.java:125)
> at com.facebook.presto.jdbc.PrestoStatement.execute(PrestoStatement.java:212)
> at com.facebook.presto.jdbc.PrestoStatement.executeQuery(PrestoStatement.java:69)
> at org.jboss.jca.adapters.jdbc.WrappedStatement.executeQuery(WrappedStatement.java:344)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:119) [translator-jdbc-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> ... 18 more
> Caused by: java.lang.IllegalArgumentException: Unsupported correlated subquery type
> at com.facebook.presto.sql.planner.sanity.NoApplyNodeLeftChecker$1.visitApply(NoApplyNodeLeftChecker.java:42)
> at com.facebook.presto.sql.planner.plan.ApplyNode.accept(ApplyNode.java:142)
> at com.facebook.presto.sql.planner.SimplePlanVisitor.visitPlan(SimplePlanVisitor.java:26)
> at com.facebook.presto.sql.planner.SimplePlanVisitor.visitPlan(SimplePlanVisitor.java:19)
> at com.facebook.presto.sql.planner.plan.PlanVisitor.visitProject(PlanVisitor.java:40)
> at com.facebook.presto.sql.planner.plan.ProjectNode.accept(ProjectNode.java:81)
> at com.facebook.presto.sql.planner.SimplePlanVisitor.visitPlan(SimplePlanVisitor.java:26)
> at com.facebook.presto.sql.planner.SimplePlanVisitor.visitPlan(SimplePlanVisitor.java:19)
> at com.facebook.presto.sql.planner.plan.PlanVisitor.visitExchange(PlanVisitor.java:175)
> at com.facebook.presto.sql.planner.plan.ExchangeNode.accept(ExchangeNode.java:209)
> at com.facebook.presto.sql.planner.SimplePlanVisitor.visitPlan(SimplePlanVisitor.java:26)
> at com.facebook.presto.sql.planner.SimplePlanVisitor.visitPlan(SimplePlanVisitor.java:19)
> at com.facebook.presto.sql.planner.plan.PlanVisitor.visitOutput(PlanVisitor.java:50)
> at com.facebook.presto.sql.planner.plan.OutputNode.accept(OutputNode.java:81)
> at com.facebook.presto.sql.planner.sanity.NoApplyNodeLeftChecker.validate(NoApplyNodeLeftChecker.java:33)
> at com.facebook.presto.sql.planner.sanity.PlanSanityChecker.lambda$validate$0(PlanSanityChecker.java:44)
> at java.lang.Iterable.forEach(Iterable.java:75) [rt.jar:1.8.0_92]
> at com.facebook.presto.sql.planner.sanity.PlanSanityChecker.validate(PlanSanityChecker.java:44)
> at com.facebook.presto.sql.planner.LogicalPlanner.plan(LogicalPlanner.java:127)
> at com.facebook.presto.sql.planner.LogicalPlanner.plan(LogicalPlanner.java:111)
> at com.facebook.presto.execution.SqlQueryExecution.doAnalyzeQuery(SqlQueryExecution.java:292)
> at com.facebook.presto.execution.SqlQueryExecution.analyzeQuery(SqlQueryExecution.java:271)
> at com.facebook.presto.execution.SqlQueryExecution.start(SqlQueryExecution.java:229)
> at com.facebook.presto.execution.QueuedExecution.lambda$start$1(QueuedExecution.java:62)
> ... 3 more
> 15:51:59,452 DEBUG [org.teiid.PROCESSOR] (Worker13_QueryProcessorQueue49) Request Thread DEf6D1zaxo6u.14 with state PROCESSING
> 15:51:59,452 DEBUG [org.teiid.CONNECTOR] (Worker13_QueryProcessorQueue49) DEf6D1zaxo6u.14.1.14 Remove State
> 15:51:59,452 DEBUG [org.teiid.CONNECTOR] (Worker13_QueryProcessorQueue49) DEf6D1zaxo6u.14.1.14 Processing Close : SELECT g_0.IntKey, g_0.StringKey, convert(g_0.FloatNum, float), convert(g_0.ByteNum, byte), (SELECT convert(g_1.ByteNum, byte) FROM Source.SmallA AS g_1 WHERE (convert(convert(g_1.ByteNum, byte), long) = g_0.LongNum) AND (g_1.IntKey = 10)) FROM Source.SmallA AS g_0
> 15:51:59,453 DEBUG [org.teiid.CONNECTOR] (Worker13_QueryProcessorQueue49) DEf6D1zaxo6u.14.1.14 Closed execution
> 15:51:59,453 DEBUG [org.teiid.CONNECTOR] (Worker13_QueryProcessorQueue49) DEf6D1zaxo6u.14.1.14 Closed connection
> 15:51:59,454 DEBUG [org.teiid.PROCESSOR] (Worker13_QueryProcessorQueue49) QueryProcessor: closing processor
> 15:51:59,454 WARN [org.teiid.PROCESSOR] (Worker13_QueryProcessorQueue49) TEIID30020 Processing exception for request DEf6D1zaxo6u.14 'TEIID30504 Source: 65536 TEIID11008:TEIID11004 Error executing statement(s): [SQL: SELECT g_0.intkey, g_0.stringkey, g_0.floatnum, g_0.bytenum, (SELECT g_1.bytenum FROM smalla AS g_1 WHERE cast(g_1.bytenum AS bigint) = g_0.longnum AND g_1.intkey = 10) FROM smalla AS g_0]'. Originally TeiidProcessingException 'Unsupported correlated subquery type' NoApplyNodeLeftChecker.java:42.: org.teiid.core.TeiidProcessingException: TEIID30504 Source: 65536 TEIID11008:TEIID11004 Error executing statement(s): [SQL: SELECT g_0.intkey, g_0.stringkey, g_0.floatnum, g_0.bytenum, (SELECT g_1.bytenum FROM smalla AS g_1 WHERE cast(g_1.bytenum AS bigint) = g_0.longnum AND g_1.intkey = 10) FROM smalla AS g_0]
> at org.teiid.dqp.internal.process.DataTierTupleSource.exceptionOccurred(DataTierTupleSource.java:401) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:161) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:391) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:145) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:164) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:472) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:348) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:274) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [rt.jar:1.8.0_92]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [rt.jar:1.8.0_92]
> at java.lang.Thread.run(Thread.java:745) [rt.jar:1.8.0_92]
> Caused by: org.teiid.translator.jdbc.JDBCExecutionException: 65536 TEIID11008:TEIID11004 Error executing statement(s): [SQL: SELECT g_0.intkey, g_0.stringkey, g_0.floatnum, g_0.bytenum, (SELECT g_1.bytenum FROM smalla AS g_1 WHERE cast(g_1.bytenum AS bigint) = g_0.longnum AND g_1.intkey = 10) FROM smalla AS g_0]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:364) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.8.0_92]
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) [rt.jar:1.8.0_92]
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_92]
> at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_92]
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at com.sun.proxy.$Proxy48.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> at java.util.concurrent.FutureTask.run(FutureTask.java:266) [rt.jar:1.8.0_92]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]
> ... 6 more
> Caused by: java.sql.SQLException: Query failed (#20170106_145159_04181_k4ss5): Unsupported correlated subquery type
> at com.facebook.presto.jdbc.PrestoResultSet.resultsException(PrestoResultSet.java:1799)
> at com.facebook.presto.jdbc.PrestoResultSet.getColumns(PrestoResultSet.java:1747)
> at com.facebook.presto.jdbc.PrestoResultSet.<init>(PrestoResultSet.java:125)
> at com.facebook.presto.jdbc.PrestoStatement.execute(PrestoStatement.java:212)
> at com.facebook.presto.jdbc.PrestoStatement.executeQuery(PrestoStatement.java:69)
> at org.jboss.jca.adapters.jdbc.WrappedStatement.executeQuery(WrappedStatement.java:344)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:119)
> ... 18 more
> Caused by: java.lang.IllegalArgumentException: Unsupported correlated subquery type
> at com.facebook.presto.sql.planner.sanity.NoApplyNodeLeftChecker$1.visitApply(NoApplyNodeLeftChecker.java:42)
> at com.facebook.presto.sql.planner.plan.ApplyNode.accept(ApplyNode.java:142)
> at com.facebook.presto.sql.planner.SimplePlanVisitor.visitPlan(SimplePlanVisitor.java:26)
> at com.facebook.presto.sql.planner.SimplePlanVisitor.visitPlan(SimplePlanVisitor.java:19)
> at com.facebook.presto.sql.planner.plan.PlanVisitor.visitProject(PlanVisitor.java:40)
> at com.facebook.presto.sql.planner.plan.ProjectNode.accept(ProjectNode.java:81)
> at com.facebook.presto.sql.planner.SimplePlanVisitor.visitPlan(SimplePlanVisitor.java:26)
> at com.facebook.presto.sql.planner.SimplePlanVisitor.visitPlan(SimplePlanVisitor.java:19)
> at com.facebook.presto.sql.planner.plan.PlanVisitor.visitExchange(PlanVisitor.java:175)
> at com.facebook.presto.sql.planner.plan.ExchangeNode.accept(ExchangeNode.java:209)
> at com.facebook.presto.sql.planner.SimplePlanVisitor.visitPlan(SimplePlanVisitor.java:26)
> at com.facebook.presto.sql.planner.SimplePlanVisitor.visitPlan(SimplePlanVisitor.java:19)
> at com.facebook.presto.sql.planner.plan.PlanVisitor.visitOutput(PlanVisitor.java:50)
> at com.facebook.presto.sql.planner.plan.OutputNode.accept(OutputNode.java:81)
> at com.facebook.presto.sql.planner.sanity.NoApplyNodeLeftChecker.validate(NoApplyNodeLeftChecker.java:33)
> at com.facebook.presto.sql.planner.sanity.PlanSanityChecker.lambda$validate$0(PlanSanityChecker.java:44)
> at java.lang.Iterable.forEach(Iterable.java:75) [rt.jar:1.8.0_92]
> at com.facebook.presto.sql.planner.sanity.PlanSanityChecker.validate(PlanSanityChecker.java:44)
> at com.facebook.presto.sql.planner.LogicalPlanner.plan(LogicalPlanner.java:127)
> at com.facebook.presto.sql.planner.LogicalPlanner.plan(LogicalPlanner.java:111)
> at com.facebook.presto.execution.SqlQueryExecution.doAnalyzeQuery(SqlQueryExecution.java:292)
> at com.facebook.presto.execution.SqlQueryExecution.analyzeQuery(SqlQueryExecution.java:271)
> at com.facebook.presto.execution.SqlQueryExecution.start(SqlQueryExecution.java:229)
> at com.facebook.presto.execution.QueuedExecution.lambda$start$1(QueuedExecution.java:62)
> ... 3 more
> 15:51:59,460 DEBUG [org.teiid.PROCESSOR] (Worker13_QueryProcessorQueue49) Removing tuplesource for the request DEf6D1zaxo6u.14
> 15:51:59,460 DEBUG [org.teiid.BUFFER_MGR] (Worker13_QueryProcessorQueue49) Removing TupleBuffer: 19
> 15:51:59,460 DEBUG [org.teiid.PROCESSOR] (Worker13_QueryProcessorQueue49) DEf6D1zaxo6u.14 Finished Processing
> 15:51:59,460 DEBUG [org.teiid.PROCESSOR] (Worker13_QueryProcessorQueue49) org.teiid.core.TeiidProcessingException: TEIID30504 Source: 65536 TEIID11008:TEIID11004 Error executing statement(s): [SQL: SELECT g_0.intkey, g_0.stringkey, g_0.floatnum, g_0.bytenum, (SELECT g_1.bytenum FROM smalla AS g_1 WHERE cast(g_1.bytenum AS bigint) = g_0.longnum AND g_1.intkey = 10) FROM smalla AS g_0] Sending error to client DEf6D1zaxo6u.14
> {code}
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
9 years, 2 months
[JBoss JIRA] (TEIID-4686) PrestoDB translator - ordering of joined tables fails
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4686?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-4686:
---------------------------------------
> I would suggest making this as translator override and adding a note in the document about it, that way we can leave it for the user configuration.
Do you mean just disabling order by? That is available already. Or do you mean adding an override/capability specific to this scenario "supports order by multiple ..."?
> PrestoDB translator - ordering of joined tables fails
> -----------------------------------------------------
>
> Key: TEIID-4686
> URL: https://issues.jboss.org/browse/TEIID-4686
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.8.6_3
> Reporter: Juraj Duráni
> Assignee: Kylin Soong
> Fix For: 9.2.1
>
>
> Issuing query like \[1\] causes an exception in PrestoDB. This seems to be PrestoDB issue. I found similar issue \[2\], but that one should be fixed. I have created a new issue \[3\]. Removing one column from ORDER BY, query returns correct result.
> Not sure if we can do something about it without avoiding pushing JOIN.
> I suggest either to fix this (if simple enough) or add a note to the documentation.
> \[1\]
> {code:sql}
> SELECT a.intkey, b.intkey FROM BQT1.SmallA AS a INNER JOIN BQT1.SmallB AS b on a.intkey = b.intkey ORDER BY 1, 2
> {code}
> \[2\] https://github.com/prestodb/presto/issues/1621
> \[3\] https://github.com/prestodb/presto/issues/7010
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
9 years, 2 months