[teiid-issues] [JBoss JIRA] (TEIID-5532) Common table projection minimization does not account for usage in a subquery nested in an aggregate or in some view scenarios

[dalex dalex (Jira)]

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

dalex dalex commented on TEIID-5532:
------------------------------------

Hi [~shawkins], I noticed some performance degradation because of changes in scope of the issue, I mean changes related to new algorithm for gathering commands (after "common tables from the user command, and everything else from the plan" comment in RelationalPlanner.planWith method). I noticed that now the collection is getting more commands than before and as a result all these command go to planner.generatePlan method which can consume very much time. I have a very big structure with many virtual views which use CTEs and some queries started to work slower than before. If you need the structure to confirm the bad performance I'll try to prepare it.

> Common table projection minimization does not account for usage in a subquery nested in an aggregate or in some view scenarios
> ------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: TEIID-5532
>                 URL: https://issues.jboss.org/browse/TEIID-5532
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine
>            Reporter: Steven Hawkins
>            Assignee: Steven Hawkins
>            Priority: Blocker
>             Fix For: 11.0.3, 11.1.2, 12.0, 11.2.1
>
>
> Nesting the usage of a common table in an aggregate means that the columns can be removed inappropriately by projection minimization.  A query such as:
> with CTE1 as /*+ no_inline */ (SELECT e1, e2, e3 from pm1.g1) 
> select array_agg((select e3 from cte1 where e1=pm1.g2.e1 and e2=pm1.g2.e2)) from pm1.g2
> will return null values for all e3 as projection minimization of CTE1 will replace the column with a null value.
> A similar situation occurs when the query above is defined instead in a view.  A similar issue was captured on TEIID-5535



--
This message was sent by Atlassian Jira
(v7.12.1#712002)