[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=12681817#comment-12681817 ] 

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

The first observation is that you should be using UNION ALL, not UNION.  See also the partitioned union logic.  That alone will get you to the initial optimization of removing the extra UNION columns:

If given (or the GROUP BY form) 
SELECT DISTINCT a FROM (SELECT 'a1' as a, b FROM dm1 UNION ALL SELECT 'a2' as a, b FROM bm2) AS t
we would produce:
SELECT DISTINCT a FROM (SELECT 'a1' as a FROM dm1 UNION ALL SELECT 'a2' as a FROM bm2) AS t

We currently see it as unnecessary then to replace one literal value with another ('a1' vs. 1), although I'm guessing that you are looking for 1 in your translator.
                
> 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: efficient_rewrite.txt, 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