6:23 a.m.
Mark Addleman created TEIID-1991:
------------------------------------
Summary: 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
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
6:23 a.m.
[
https://issues.jboss.org/browse/TEIID-1991?page=com.atlassian.jira.plugin...
]
Mark Addleman updated TEIID-1991:
---------------------------------
Attachment: plan.txt
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
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
6:49 a.m.
[
https://issues.jboss.org/browse/TEIID-1991?page=com.atlassian.jira.plugin...
]
Mark Addleman updated TEIID-1991:
---------------------------------
Attachment: plan.txt
debug output plan
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
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
12:39 a.m.
[
https://issues.jboss.org/browse/TEIID-1991?page=com.atlassian.jira.plugin...
]
Mark Addleman updated TEIID-1991:
---------------------------------
Attachment: simplified_plan.txt
This is a simplified example of the problem
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
12:41 a.m.
[
https://issues.jboss.org/browse/TEIID-1991?page=com.atlassian.jira.plugin...
]
Mark Addleman commented on TEIID-1991:
--------------------------------------
After experimenting a bit, it appears that any UNION in the subselect prevents Teiid from
optimizing the subselect for the GROUP BY
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
1:15 a.m.
[
https://issues.jboss.org/browse/TEIID-1991?page=com.atlassian.jira.plugin...
]
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
1:35 a.m.
[
https://issues.jboss.org/browse/TEIID-1991?page=com.atlassian.jira.plugin...
]
Mark Addleman updated TEIID-1991:
---------------------------------
Attachment: efficient_rewrite.txt
Example of Teiid efficiently rewriting inline view
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
1:43 a.m.
[
https://issues.jboss.org/browse/TEIID-1991?page=com.atlassian.jira.plugin...
]
Mark Addleman commented on TEIID-1991:
--------------------------------------
When there is a single SELECT in the inline view
SELECT u.datamart_name
FROM (
SELECT 'abc' AS DATAMART_NAME, a.evttypecode
FROM SECURITY_DM_DATACOM_DEV_R140_XE59_CMGRD1.ADMACCOUNT AS a
) AS u
GROUP BY u.datamart_name
It appears that Teiid understands it only needs to know if there is *any* data coming from
the ADMACCOUNT table, so it issues SELECT 1 FROM
SECURITY_DM_DATACOM_DEV_R140_XE59_CMGRD1.ADMACCOUNT instead.
I would expect Teiid to apply the same optimization if the inline view contained a UNION.
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
1:43 a.m.
[
https://issues.jboss.org/browse/TEIID-1991?page=com.atlassian.jira.plugin...
]
Mark Addleman commented on TEIID-1991:
--------------------------------------
I may have screwed up capturing simplified_plan.txt. I'm going to upload a new one
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
1:51 a.m.
[
https://issues.jboss.org/browse/TEIID-1991?page=com.atlassian.jira.plugin...
]
Steven Hawkins commented on TEIID-1991:
---------------------------------------
Yes, it makes a difference if the column is a projected literal. That optimization would
be done on top of what I was mentioning before - otherwise you'd still be left with
extra union columns. The non-union case is much simpler as we are able to remove the view
and propagate the constant.
What you're suggesting only works since the projected value is the same for all union
branches. This also seems contrived as you are grouping on a constant column, is that
intended?
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
2:51 a.m.
[
https://issues.jboss.org/browse/TEIID-1991?page=com.atlassian.jira.plugin...
]
Mark Addleman commented on TEIID-1991:
--------------------------------------
Actually, it is not contrived. Our use case is a union of named extracts of data (we call
these data marts). This is the moral equivalent of Teiid's multi-source model. The
constant column indicates the data mart name. We create a view for each of these unions.
The goal is to get a distinct list of data mart names by querying the view.
It seems to me that the following query
SELECT t.a FROM (
SELECT 'dm1' AS a, dm1.b FROM dm1
UNION
SELECT 'dm2' AS a, dm2.b FROM ds2
) t GROUP BY a
can be rewritten as
SELECT t.a FROM (
SELECT 'dm1' AS a FROM (SELECT 1 FROM dm1.b) t
UNION
SELECT 'dm2' AS a FROM (SELECT 1 FROM dm2.b) t
) t GROUP BY a
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
3:09 a.m.
[
https://issues.jboss.org/browse/TEIID-1991?page=com.atlassian.jira.plugin...
]
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
3:26 a.m.
[
https://issues.jboss.org/browse/TEIID-1991?page=com.atlassian.jira.plugin...
]
Mark Addleman commented on TEIID-1991:
--------------------------------------
That did it! Thanks.
For future readers, my original query should be stated as
SELECT t.a FROM (
SELECT n.name, dm1.a FROM dm1, (SELECT 'abc' AS name) n WHERE
n.name='abc'
UNION ALL
SELECT n.name, dm2.a FROM dm2, (SELECT 'xyz' AS name) n WHERE
n.name='xyz'
) t
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
3:32 a.m.
[
https://issues.jboss.org/browse/TEIID-1991?page=com.atlassian.jira.plugin...
]
Mark Addleman resolved TEIID-1991.
----------------------------------
Resolution: Done
Not a Teiid bug
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
4:23 a.m.
[
https://issues.jboss.org/browse/TEIID-1991?page=com.atlassian.jira.plugin...
]
Steven Hawkins reopened TEIID-1991:
-----------------------------------
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
4:25 a.m.
[
https://issues.jboss.org/browse/TEIID-1991?page=com.atlassian.jira.plugin...
]
Steven Hawkins resolved TEIID-1991.
-----------------------------------
Resolution: Rejected
Marking as rejected.
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
3:14 a.m.
[
https://issues.jboss.org/browse/TEIID-1991?page=com.atlassian.jira.plugin...
]
Steven Hawkins closed TEIID-1991.
---------------------------------
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
4292
days inactive
4427
days old
16 comments
2 participants
participants (2)
-
Mark Addleman (JIRA) -
Steven Hawkins (JIRA)