10:05 a.m.
[
https://issues.jboss.org/browse/TEIID-2039?page=com.atlassian.jira.plugin...
]
Steven Hawkins commented on TEIID-2039:
---------------------------------------
The issue here is not anything explicitly bad by Teiid. Rather all the views involved in
the main query and uncorrelated subqueries are quite extensive in their layering and
together they produce an extremely large push-down query.
Possible resolutions to this issue include:
1. the built-in approach is to disable scalar subqueries on the translator in question.
The downsides is that this would disable even correlated subquery pushdown and requires a
custom translator. This would effectively inline the uncorrelated value via
pre-evaluation
1a. This first approach could be elaborated into a general fix such that there is
specifically a translator-property settable
"supportsUncorrelatedScalarSubqueries". This would not effect correlated
pushdown and does not require a custom translator.
2. add a subquery hint to not push-down the subquery with the parent plan. There are
already the subquery NO_UNNEST, MJ, and DJ hints, so adding another hint
"NO_PUSHDOWN" or as an hint option "NO_UNNEST(NO_PUSHDOWN)" would not
be difficult.
3. have the user rewrite the query using a common table expression to gather the common
execution path of the subqueries in the with clause.
3a. have the optimizer automatically pull out the common subqueries as a with clause. The
logic to determine a common root query is essentially the same as what exists in the xml
planning logic to raise the input set criteria or in correlated subquery join planning.
At this point however this seems too narrowly focused and would need to be hint driven
anyway. So it seems better to pursue something more general like 2.
Virtual table mapping with large number of UNIONs is resulting in
excessively long source query
-----------------------------------------------------------------------------------------------
Key: TEIID-2039
URL: https://issues.jboss.org/browse/TEIID-2039
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 7.4.1, 7.7
Reporter: Marc Shirley
Assignee: Steven Hawkins
Queries against a virtual table built upon several layers of UNION transformations is
resulting in what appears to be a badly formed, excessively long source query (for
example, a SQL string over 1 million characters) that results in slow query execution.
Against the 7.7 version, the query may result in the below exception:
08:45:11,878 ERROR [PROCESSOR] Unexpected exception for request 84Sg8E1i+tBi.5
java.lang.ArrayIndexOutOfBoundsException: -1
at java.util.ArrayList.elementData(ArrayList.java:338)
at java.util.ArrayList.get(ArrayList.java:351)
at
org.teiid.query.optimizer.relational.rules.RuleCollapseSource.buildQuery(RuleCollapseSource.java:338)
at
org.teiid.query.optimizer.relational.rules.RuleCollapseSource.buildQuery(RuleCollapseSource.java:373)
at
org.teiid.query.optimizer.relational.rules.RuleCollapseSource.createQuery(RuleCollapseSource.java:228)
at
org.teiid.query.optimizer.relational.rules.RuleCollapseSource.createQuery(RuleCollapseSource.java:209)
at
org.teiid.query.optimizer.relational.rules.RuleCollapseSource.buildQuery(RuleCollapseSource.java:359)
at
org.teiid.query.optimizer.relational.rules.RuleCollapseSource.buildQuery(RuleCollapseSource.java:323)
at
org.teiid.query.optimizer.relational.rules.RuleCollapseSource.buildQuery(RuleCollapseSource.java:373)
at
org.teiid.query.optimizer.relational.rules.RuleCollapseSource.createQuery(RuleCollapseSource.java:228)
at
org.teiid.query.optimizer.relational.rules.RuleCollapseSource.createQuery(RuleCollapseSource.java:209)
at
org.teiid.query.optimizer.relational.rules.RuleCollapseSource.buildQuery(RuleCollapseSource.java:359)
at
org.teiid.query.optimizer.relational.rules.RuleCollapseSource.buildQuery(RuleCollapseSource.java:328)
at
org.teiid.query.optimizer.relational.rules.RuleCollapseSource.buildQuery(RuleCollapseSource.java:373)
at
org.teiid.query.optimizer.relational.rules.RuleCollapseSource.createQuery(RuleCollapseSource.java:228)
at
org.teiid.query.optimizer.relational.rules.RuleCollapseSource.createQuery(RuleCollapseSource.java:209)
at
org.teiid.query.optimizer.relational.rules.RuleCollapseSource.buildQuery(RuleCollapseSource.java:359)
at
org.teiid.query.optimizer.relational.rules.RuleCollapseSource.buildQuery(RuleCollapseSource.java:373)
at
org.teiid.query.optimizer.relational.rules.RuleCollapseSource.createQuery(RuleCollapseSource.java:228)
at
org.teiid.query.optimizer.relational.rules.RuleCollapseSource.createQuery(RuleCollapseSource.java:209)
at
org.teiid.query.optimizer.relational.rules.RuleCollapseSource.buildQuery(RuleCollapseSource.java:359)
at
org.teiid.query.optimizer.relational.rules.RuleCollapseSource.buildQuery(RuleCollapseSource.java:323)
at
org.teiid.query.optimizer.relational.rules.RuleCollapseSource.buildQuery(RuleCollapseSource.java:373)
at
org.teiid.query.optimizer.relational.rules.RuleCollapseSource.createQuery(RuleCollapseSource.java:228)
at
org.teiid.query.optimizer.relational.rules.RuleCollapseSource.createQuery(RuleCollapseSource.java:209)
at
org.teiid.query.optimizer.relational.rules.RuleCollapseSource.execute(RuleCollapseSource.java:99)
at
org.teiid.query.optimizer.relational.RelationalPlanner.executeRules(RelationalPlanner.java:455)
at
org.teiid.query.optimizer.relational.RelationalPlanner.optimize(RelationalPlanner.java:199)
at org.teiid.query.optimizer.QueryOptimizer.optimizePlan(QueryOptimizer.java:190)
at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:436)
at org.teiid.dqp.internal.process.Request.processRequest(Request.java:459)
at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:516)
at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:276)
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:49)
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:215)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:232)
at
org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:118)
at
org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:288)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
at java.lang.Thread.run(Thread.java:679)
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira