[teiid-issues] [JBoss JIRA] (TEIID-1991) GROUP BY pushdown into subselect of UNIONs

[Steven Hawkins (JIRA)]

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

Steven Hawkins commented on TEIID-1991:
---------------------------------------

The simplified plan does not show any grouping.  Also I'm not quite sure what you mean by changing a to 1 since you still need a projected.  From what I can infer from the larger plan you have the form:

select x from (select x, y, z ... union select x, y, z ... ) as VW group by x
which is equivalent to:
select distinct x from (select x, y, z ... union select x, y, z ... ) as VW

The logic sees that all columns are needed in the inline view since it is a union and not a union all.  Since there are no aggregates against the extra columns they can be safely left off of the union and for that matter the union is really the same as a union all.  Is that what you are trying to convey?

If you issue the query as a union all, then Teiid will correctly omit unnecessary columns.
                
> GROUP BY pushdown into subselect of UNIONs
> ------------------------------------------
>
>                 Key: TEIID-1991
>                 URL: https://issues.jboss.org/browse/TEIID-1991
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine
>    Affects Versions: 7.7
>            Reporter: Mark Addleman
>            Assignee: Steven Hawkins
>         Attachments: plan.txt, plan.txt, simplified_plan.txt
>
>
> If have a query of the form
> SELECT a FROM (
>    SELECT a FROM (
>        SELECT a FROM ds.t1
>    ) u
> ) t GROUP BY a
> Teiid correctly optimizes the inner SELECT to SELECT 1 FROM ds.t1.  However, if the inner subselect is a union:
> SELECT a FROM (
>    SELECT a FROM (
>        SELECT a FROM ds.t1 UNION
>        SELECT a FROM ds.t2
>    ) u
> ) t GROUP BY a
> 			
> Teiid does not optimize to 
>    SELECT 1 FROM ds.t1 UNION
>    SELECT 1 FROM ds.t2
> as I would expect.
> See attached plan

--
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