11:09 p.m.
Mark Addleman created TEIID-1990:
------------------------------------
Summary: UNION pushdown
Key: TEIID-1990
URL: https://issues.jboss.org/browse/TEIID-1990
Project: Teiid
Issue Type: Bug
Reporter: Mark Addleman
Assignee: Steven Hawkins
I have SQL of the form
SELECT a FROM (
SELECT a FROM ds.t1 UNION
SELECT a FROM ds.t2
) t
The execution factory extends JDBCExecutionFactory and supports everything except row
offset. It appears that the two unioned SELECTs are being executed as separate statements
rather than the entire unioned set being pushed down to the datasource.
Attached is 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
11:11 p.m.
[
https://issues.jboss.org/browse/TEIID-1990?page=com.atlassian.jira.plugin...
]
Mark Addleman updated TEIID-1990:
---------------------------------
Attachment: plan.txt
UNION pushdown
--------------
Key: TEIID-1990
URL: https://issues.jboss.org/browse/TEIID-1990
Project: Teiid
Issue Type: Bug
Reporter: Mark Addleman
Assignee: Steven Hawkins
Attachments: plan.txt
I have SQL of the form
SELECT a FROM (
SELECT a FROM ds.t1 UNION
SELECT a FROM ds.t2
) t
The execution factory extends JDBCExecutionFactory and supports everything except row
offset. It appears that the two unioned SELECTs are being executed as separate statements
rather than the entire unioned set being pushed down to the datasource.
Attached is 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
11:13 p.m.
[
https://issues.jboss.org/browse/TEIID-1990?page=com.atlassian.jira.plugin...
]
Steven Hawkins commented on TEIID-1990:
---------------------------------------
You'll want to check the showplan debug output and see what is inhibiting pushdown.
UNION pushdown
--------------
Key: TEIID-1990
URL: https://issues.jboss.org/browse/TEIID-1990
Project: Teiid
Issue Type: Bug
Reporter: Mark Addleman
Assignee: Steven Hawkins
Attachments: plan.txt
I have SQL of the form
SELECT a FROM (
SELECT a FROM ds.t1 UNION
SELECT a FROM ds.t2
) t
The execution factory extends JDBCExecutionFactory and supports everything except row
offset. It appears that the two unioned SELECTs are being executed as separate statements
rather than the entire unioned set being pushed down to the datasource.
Attached is 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
11:28 p.m.
[
https://issues.jboss.org/browse/TEIID-1990?page=com.atlassian.jira.plugin...
]
Mark Addleman commented on TEIID-1990:
--------------------------------------
Check my TEIID-1991. I think that is a better plan and probably the same issue.
UNION pushdown
--------------
Key: TEIID-1990
URL: https://issues.jboss.org/browse/TEIID-1990
Project: Teiid
Issue Type: Bug
Reporter: Mark Addleman
Assignee: Steven Hawkins
Attachments: plan.txt
I have SQL of the form
SELECT a FROM (
SELECT a FROM ds.t1 UNION
SELECT a FROM ds.t2
) t
The execution factory extends JDBCExecutionFactory and supports everything except row
offset. It appears that the two unioned SELECTs are being executed as separate statements
rather than the entire unioned set being pushed down to the datasource.
Attached is 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
11:53 p.m.
[
https://issues.jboss.org/browse/TEIID-1990?page=com.atlassian.jira.plugin...
]
Mark Addleman updated TEIID-1990:
---------------------------------
Attachment: plan.txt
debug output plan
UNION pushdown
--------------
Key: TEIID-1990
URL: https://issues.jboss.org/browse/TEIID-1990
Project: Teiid
Issue Type: Bug
Reporter: Mark Addleman
Assignee: Steven Hawkins
Attachments: plan.txt, plan.txt
I have SQL of the form
SELECT a FROM (
SELECT a FROM ds.t1 UNION
SELECT a FROM ds.t2
) t
The execution factory extends JDBCExecutionFactory and supports everything except row
offset. It appears that the two unioned SELECTs are being executed as separate statements
rather than the entire unioned set being pushed down to the datasource.
Attached is 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:42 a.m.
[
https://issues.jboss.org/browse/TEIID-1990?page=com.atlassian.jira.plugin...
]
Steven Hawkins commented on TEIID-1990:
---------------------------------------
The debug log shows that only some of the access nodes have been raised above their parent
project nodes after RuleRaiseAccess. The most likely reason is that the source does not
support projecting non-column reference expressions. See
ExecutionFactory.supportsSelectExpression. We should though add a debug line to the debug
log stating why the pushdown has been prevented, which is typically done for criteria
pushing but has not yet been added for all other situations.
UNION pushdown
--------------
Key: TEIID-1990
URL: https://issues.jboss.org/browse/TEIID-1990
Project: Teiid
Issue Type: Bug
Reporter: Mark Addleman
Assignee: Steven Hawkins
Attachments: plan.txt, plan.txt
I have SQL of the form
SELECT a FROM (
SELECT a FROM ds.t1 UNION
SELECT a FROM ds.t2
) t
The execution factory extends JDBCExecutionFactory and supports everything except row
offset. It appears that the two unioned SELECTs are being executed as separate statements
rather than the entire unioned set being pushed down to the datasource.
Attached is 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:54 a.m.
[
https://issues.jboss.org/browse/TEIID-1990?page=com.atlassian.jira.plugin...
]
Mark Addleman commented on TEIID-1990:
--------------------------------------
The execution factory subclasses JDBCExecutionFactory and only overrides supportsRowOffect
as false. supportSelectExpression returns true.
UNION pushdown
--------------
Key: TEIID-1990
URL: https://issues.jboss.org/browse/TEIID-1990
Project: Teiid
Issue Type: Bug
Reporter: Mark Addleman
Assignee: Steven Hawkins
Attachments: plan.txt, plan.txt
I have SQL of the form
SELECT a FROM (
SELECT a FROM ds.t1 UNION
SELECT a FROM ds.t2
) t
The execution factory extends JDBCExecutionFactory and supports everything except row
offset. It appears that the two unioned SELECTs are being executed as separate statements
rather than the entire unioned set being pushed down to the datasource.
Attached is 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 p.m.
[
https://issues.jboss.org/browse/TEIID-1990?page=com.atlassian.jira.plugin...
]
Steven Hawkins updated TEIID-1990:
----------------------------------
Component/s: Query Engine
The plans show "SELECT 'abc' AS DATAMART_NAME, 'SECCM.VIEWSYSACCESS'
AS "__objecttype__", s.evttypecode, s.evtversion, s.evtcatcode, s.evtsysid,
s.evtsysplex, s.evtutctod, s.evtsystod, s.evtesmcode, s.evtpuuid, s.evtuserid,
s.evtusername, s.evtjobname, s.evtsource, s.usrprogram AS admtarget, ' ' AS
Commandstr, 0 AS oprcode, s.inf1code AS vio1code, s.inf2code AS vio2code FROM
SECURITY_DM_DATACOM_DEV_R140_XE59_CMGRD1.SYSACCESS" as not being fully pushed down.
Can you run that query by itself and see if it is still not pushed down. Perhaps as part
of a union there is an implicit conversion not being shown? Also what version is this
on?
UNION pushdown
--------------
Key: TEIID-1990
URL: https://issues.jboss.org/browse/TEIID-1990
Project: Teiid
Issue Type: Bug
Components: Query Engine
Reporter: Mark Addleman
Assignee: Steven Hawkins
Attachments: plan.txt, plan.txt
I have SQL of the form
SELECT a FROM (
SELECT a FROM ds.t1 UNION
SELECT a FROM ds.t2
) t
The execution factory extends JDBCExecutionFactory and supports everything except row
offset. It appears that the two unioned SELECTs are being executed as separate statements
rather than the entire unioned set being pushed down to the datasource.
Attached is 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
5:08 p.m.
[
https://issues.jboss.org/browse/TEIID-1990?page=com.atlassian.jira.plugin...
]
Mark Addleman updated TEIID-1990:
---------------------------------
Affects Version/s: 7.7
UNION pushdown
--------------
Key: TEIID-1990
URL: https://issues.jboss.org/browse/TEIID-1990
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 7.7
Reporter: Mark Addleman
Assignee: Steven Hawkins
Attachments: plan.txt, plan.txt
I have SQL of the form
SELECT a FROM (
SELECT a FROM ds.t1 UNION
SELECT a FROM ds.t2
) t
The execution factory extends JDBCExecutionFactory and supports everything except row
offset. It appears that the two unioned SELECTs are being executed as separate statements
rather than the entire unioned set being pushed down to the datasource.
Attached is 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
5:12 p.m.
[
https://issues.jboss.org/browse/TEIID-1990?page=com.atlassian.jira.plugin...
]
Mark Addleman commented on TEIID-1990:
--------------------------------------
I think I found the problem: The inf1code column is an integer data type while other
columns in the same position are type long.
Curious: If the execution factory declared that the data source supported CAST, would
Teiid have rewritten the select to include CAST(inf1code AS integer)?
UNION pushdown
--------------
Key: TEIID-1990
URL: https://issues.jboss.org/browse/TEIID-1990
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 7.7
Reporter: Mark Addleman
Assignee: Steven Hawkins
Attachments: plan.txt, plan.txt
I have SQL of the form
SELECT a FROM (
SELECT a FROM ds.t1 UNION
SELECT a FROM ds.t2
) t
The execution factory extends JDBCExecutionFactory and supports everything except row
offset. It appears that the two unioned SELECTs are being executed as separate statements
rather than the entire unioned set being pushed down to the datasource.
Attached is 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
5:22 p.m.
[
https://issues.jboss.org/browse/TEIID-1990?page=com.atlassian.jira.plugin...
]
Steven Hawkins commented on TEIID-1990:
---------------------------------------
Yes, that is the likely issue. Teiid is adding a convert, but marking it as implicit so
it is not immediately visible (this is for the benefit of designer). Teiid 8.0 shows
implicit conversion more clearly and also provides a mechanism for translators to declare
support for only a subset of conversions. This implicit conversion then is preventing
pushdown.
It would be good to backport showing implicit conversion to 7.7.1 and to see why there
wasn't a more specific message in the debug log.
UNION pushdown
--------------
Key: TEIID-1990
URL: https://issues.jboss.org/browse/TEIID-1990
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 7.7
Reporter: Mark Addleman
Assignee: Steven Hawkins
Attachments: plan.txt, plan.txt
I have SQL of the form
SELECT a FROM (
SELECT a FROM ds.t1 UNION
SELECT a FROM ds.t2
) t
The execution factory extends JDBCExecutionFactory and supports everything except row
offset. It appears that the two unioned SELECTs are being executed as separate statements
rather than the entire unioned set being pushed down to the datasource.
Attached is 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
5:36 p.m.
[
https://issues.jboss.org/browse/TEIID-1990?page=com.atlassian.jira.plugin...
]
Mark Addleman commented on TEIID-1990:
--------------------------------------
Do you want me to close this issue or do you want to leave it open for the 7.7.1
backport?
UNION pushdown
--------------
Key: TEIID-1990
URL: https://issues.jboss.org/browse/TEIID-1990
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 7.7
Reporter: Mark Addleman
Assignee: Steven Hawkins
Attachments: plan.txt, plan.txt
I have SQL of the form
SELECT a FROM (
SELECT a FROM ds.t1 UNION
SELECT a FROM ds.t2
) t
The execution factory extends JDBCExecutionFactory and supports everything except row
offset. It appears that the two unioned SELECTs are being executed as separate statements
rather than the entire unioned set being pushed down to the datasource.
Attached is 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:50 p.m.
[
https://issues.jboss.org/browse/TEIID-1990?page=com.atlassian.jira.plugin...
]
Steven Hawkins resolved TEIID-1990.
-----------------------------------
Fix Version/s: 7.7.1
8.0
Resolution: Done
I left off the backport of the implicit change since it alters quite a few test cases.
But there was a bug with the debug log info which has been corrected and expanded. I also
added a specific log for the limit case from the forum topic -
https://community.jboss.org/thread/197974?tstart=0
UNION pushdown
--------------
Key: TEIID-1990
URL: https://issues.jboss.org/browse/TEIID-1990
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 7.7
Reporter: Mark Addleman
Assignee: Steven Hawkins
Fix For: 7.7.1, 8.0
Attachments: plan.txt, plan.txt
I have SQL of the form
SELECT a FROM (
SELECT a FROM ds.t1 UNION
SELECT a FROM ds.t2
) t
The execution factory extends JDBCExecutionFactory and supports everything except row
offset. It appears that the two unioned SELECTs are being executed as separate statements
rather than the entire unioned set being pushed down to the datasource.
Attached is 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:51 p.m.
[
https://issues.jboss.org/browse/TEIID-1990?page=com.atlassian.jira.plugin...
]
Steven Hawkins closed TEIID-1990.
---------------------------------
UNION pushdown
--------------
Key: TEIID-1990
URL: https://issues.jboss.org/browse/TEIID-1990
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 7.7
Reporter: Mark Addleman
Assignee: Steven Hawkins
Fix For: 7.7.1, 8.0
Attachments: plan.txt, plan.txt
I have SQL of the form
SELECT a FROM (
SELECT a FROM ds.t1 UNION
SELECT a FROM ds.t2
) t
The execution factory extends JDBCExecutionFactory and supports everything except row
offset. It appears that the two unioned SELECTs are being executed as separate statements
rather than the entire unioned set being pushed down to the datasource.
Attached is 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
4518
days inactive
4658
days old
13 comments
2 participants
participants (2)
-
Mark Addleman (JIRA) -
Steven Hawkins (JIRA)