[teiid-issues] [JBoss JIRA] (TEIID-2039) Virtual table mapping with large number of UNIONs is resulting in excessively long source query

[Steven Hawkins (JIRA)]

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

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