July 2013 - teiid-issues - Jboss List Archives

[JBoss JIRA] (TEIID-2569) Inefficient Outer Join compensation when translator restricted on KEY based joins

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-2569?page=com.atlassian.jira.plugin... ] Steven Hawkins commented on TEIID-2569: --------------------------------------- I don't think the last query is correct as support inner joins is also disabled in this case correct? > Inefficient Outer Join compensation when translator restricted on KEY based joins > --------------------------------------------------------------------------------- > > Key: TEIID-2569 > URL: https://issues.jboss.org/browse/TEIID-2569 > Project: Teiid > Issue Type: Enhancement > Components: Query Engine > Reporter: Ramesh Reddy > Assignee: Steven Hawkins > > When the following capabilities are set on ExecutionFactory > {code} > setSupportsOuterJoins(true); > setSupportedJoinCriteria(SupportedJoinCriteria.KEY); > {code} > A three table JOIN like > {code} > select G1.e1, G2.e1, G3.e1 from G1, G2, G3 where G1.e1=G2.e2 and G2.e2 = G3.e3 > {code} > gets pushed to translator as two separate queries like > {code} > SELECT G3.e3 AS c_0, G3.e1 AS c_1 FROM G3 ORDER BY c_0 > SELECT G2.e2 AS c_0, G1.e1 AS c_1, G2.e1 AS c_2 FROM G1 LEFT OUTER JOIN G2 ON G1.e1 = G2.e2 WHERE G2.e2 IS NOT NULL ORDER BY c_0 > {code} > instead of of one query which should be like > {code} > SELECT G1.e1, G2.e1, G3.e1 FROM G1 INNER JOIN G2 ON G1.e1 = G2.e2 INNER JOIN G3 ON G2.e2 = G3.e3 > {code} > When the key restriction is removed, it works fine, which should be un-related. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators For more information on JIRA, see: http://www.atlassian.com/software/jira

12 years, 9 months

1
0
0 / 0

[JBoss JIRA] (TEIID-2569) Inefficient Outer Join compensation when translator restricted on KEY based joins

by Ramesh Reddy (JIRA)

Ramesh Reddy created TEIID-2569: ----------------------------------- Summary: Inefficient Outer Join compensation when translator restricted on KEY based joins Key: TEIID-2569 URL: https://issues.jboss.org/browse/TEIID-2569 Project: Teiid Issue Type: Enhancement Components: Query Engine Reporter: Ramesh Reddy Assignee: Steven Hawkins When the following capabilities are set on ExecutionFactory {code} setSupportsOuterJoins(true); setSupportedJoinCriteria(SupportedJoinCriteria.KEY); {code} A three table JOIN like {code} select G1.e1, G2.e1, G3.e1 from G1, G2, G3 where G1.e1=G2.e2 and G2.e2 = G3.e3 {code} gets pushed to translator as two separate queries like {code} SELECT G3.e3 AS c_0, G3.e1 AS c_1 FROM G3 ORDER BY c_0 SELECT G2.e2 AS c_0, G1.e1 AS c_1, G2.e1 AS c_2 FROM G1 LEFT OUTER JOIN G2 ON G1.e1 = G2.e2 WHERE G2.e2 IS NOT NULL ORDER BY c_0 {code} instead of of one query which should be like {code} SELECT G1.e1, G2.e1, G3.e1 FROM G1 INNER JOIN G2 ON G1.e1 = G2.e2 INNER JOIN G3 ON G2.e2 = G3.e3 {code} When the key restriction is removed, it works fine, which should be un-related. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators For more information on JIRA, see: http://www.atlassian.com/software/jira

12 years, 9 months

1
0
0 / 0

[JBoss JIRA] (TEIID-2568) Order by is omitted when projection is raised above ordering with grouping

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-2568?page=com.atlassian.jira.plugin... ] Steven Hawkins resolved TEIID-2568. ----------------------------------- Resolution: Done Corrected the logic to not look for further optimization when the access node is raised. > Order by is omitted when projection is raised above ordering with grouping > -------------------------------------------------------------------------- > > Key: TEIID-2568 > URL: https://issues.jboss.org/browse/TEIID-2568 > Project: Teiid > Issue Type: Bug > Components: Query Engine > Affects Versions: 7.7 > Environment: Windows and teiid 8.1 > Reporter: RAKESH BALGURI > Assignee: Steven Hawkins > Priority: Critical > Fix For: 8.4.1, 8.5 > > Attachments: Query_Plan_SQL_1.txt, Query_Plan_SQL_2.txt > > > I have a SQL which contain constants in the Select clause and also it contains Group By, Order By and Limit clauses in the SQL. > When I execute the SQL, the Order By clause is not being pushed to the underlying translator. My translator doesn't support "SelectExpressions" i.e. supportsSelectExpression() returns false. > When I remove the constant from the Select clause then the Order By is pushed to the translator. > > Is this a bug in Teiid's query Optimizer? > > The following are the sample queries and attached are the Query Plans for both the SQL's. > > Query1: This SQL doesn't push the Order By clause to the translator. > > SELECT B.RESCLASS, COUNT(*) AS GROUPING_COUNT, 'SECURITY.BASERULE' AS "__objecttype__" FROM > SECURITY.BASERULE AS B GROUP BY B.RESCLASS ORDER BY B.RESCLASS DESC LIMIT 22 > > Query2: This SQL pushes the Order By to the translator. > > SELECT B.RESCLASS, COUNT(*) AS GROUPING_COUNT FROM > SECURITY.BASERULE AS B GROUP BY B.RESCLASS ORDER BY B.RESCLASS DESC LIMIT 22 -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators For more information on JIRA, see: http://www.atlassian.com/software/jira

12 years, 9 months

1
0
0 / 0

[JBoss JIRA] (TEIID-2336) Pushdown support for arraytable

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-2336?page=com.atlassian.jira.plugin... ] Steven Hawkins resolved TEIID-2336. ----------------------------------- Resolution: Done Added array_table decomposition logic in the relationalplanner and expanded the merge virtual logic to understand when simple projections can be merged in more situations (including when acting as a nested table). > Pushdown support for arraytable > ------------------------------- > > Key: TEIID-2336 > URL: https://issues.jboss.org/browse/TEIID-2336 > Project: Teiid > Issue Type: Feature Request > Components: Query Engine > Reporter: Mark Addleman > Assignee: Steven Hawkins > Fix For: 8.5 > > > Rewrite ARRAYTABLE queries as a series of cast and array_get functions calls. > This would allow translators to support efficient queries using ARRAYTABLE. > See https://community.jboss.org/thread/215915 for more information -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators For more information on JIRA, see: http://www.atlassian.com/software/jira

12 years, 9 months

1
0
0 / 0

[JBoss JIRA] (TEIID-1522) propagate all projected constants from non-merged view layers

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-1522?page=com.atlassian.jira.plugin... ] Steven Hawkins resolved TEIID-1522. ----------------------------------- Fix Version/s: 8.5 Resolution: Done Update the rulemergevirtual to understand merging simple projections. Added a TODO for the remaining possible optimization when there are inhibiting subqueries/non-deterministic expressions. > propagate all projected constants from non-merged view layers > ------------------------------------------------------------- > > Key: TEIID-1522 > URL: https://issues.jboss.org/browse/TEIID-1522 > Project: Teiid > Issue Type: Feature Request > Components: Query Engine > Affects Versions: 7.4 > Reporter: Steven Hawkins > Assignee: Steven Hawkins > Priority: Minor > Fix For: 8.5 > > > For non-merged view layers there are planning and costing benefits to propagating any projected constants upward in the plan. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators For more information on JIRA, see: http://www.atlassian.com/software/jira

12 years, 9 months

1
0
0 / 0

[JBoss JIRA] (TEIID-2568) Order by is omitted when projection is raised above ordering with grouping

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-2568?page=com.atlassian.jira.plugin... ] Steven Hawkins updated TEIID-2568: ---------------------------------- Summary: Order by is omitted when projection is raised above ordering with grouping (was: Teiid Query Optimizer doesn't work properly with constants) Fix Version/s: 8.4.1 8.5 Priority: Critical (was: Major) Affects Version/s: 7.7 Component/s: Query Engine > Order by is omitted when projection is raised above ordering with grouping > -------------------------------------------------------------------------- > > Key: TEIID-2568 > URL: https://issues.jboss.org/browse/TEIID-2568 > Project: Teiid > Issue Type: Bug > Components: Query Engine > Affects Versions: 7.7 > Environment: Windows and teiid 8.1 > Reporter: RAKESH BALGURI > Assignee: Steven Hawkins > Priority: Critical > Fix For: 8.4.1, 8.5 > > Attachments: Query_Plan_SQL_1.txt, Query_Plan_SQL_2.txt > > > I have a SQL which contain constants in the Select clause and also it contains Group By, Order By and Limit clauses in the SQL. > When I execute the SQL, the Order By clause is not being pushed to the underlying translator. My translator doesn't support "SelectExpressions" i.e. supportsSelectExpression() returns false. > When I remove the constant from the Select clause then the Order By is pushed to the translator. > > Is this a bug in Teiid's query Optimizer? > > The following are the sample queries and attached are the Query Plans for both the SQL's. > > Query1: This SQL doesn't push the Order By clause to the translator. > > SELECT B.RESCLASS, COUNT(*) AS GROUPING_COUNT, 'SECURITY.BASERULE' AS "__objecttype__" FROM > SECURITY.BASERULE AS B GROUP BY B.RESCLASS ORDER BY B.RESCLASS DESC LIMIT 22 > > Query2: This SQL pushes the Order By to the translator. > > SELECT B.RESCLASS, COUNT(*) AS GROUPING_COUNT FROM > SECURITY.BASERULE AS B GROUP BY B.RESCLASS ORDER BY B.RESCLASS DESC LIMIT 22 -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators For more information on JIRA, see: http://www.atlassian.com/software/jira

12 years, 9 months

1
0
0 / 0

[JBoss JIRA] (TEIID-2568) Teiid Query Optimizer doesn't work properly with constants

by RAKESH BALGURI (JIRA)

RAKESH BALGURI created TEIID-2568: ------------------------------------- Summary: Teiid Query Optimizer doesn't work properly with constants Key: TEIID-2568 URL: https://issues.jboss.org/browse/TEIID-2568 Project: Teiid Issue Type: Bug Environment: Windows and teiid 8.1 Reporter: RAKESH BALGURI Assignee: Steven Hawkins Attachments: Query_Plan_SQL_1.txt, Query_Plan_SQL_2.txt I have a SQL which contain constants in the Select clause and also it contains Group By, Order By and Limit clauses in the SQL. When I execute the SQL, the Order By clause is not being pushed to the underlying translator. My translator doesn't support "SelectExpressions" i.e. supportsSelectExpression() returns false. When I remove the constant from the Select clause then the Order By is pushed to the translator. Is this a bug in Teiid's query Optimizer? The following are the sample queries and attached are the Query Plans for both the SQL's. Query1: This SQL doesn't push the Order By clause to the translator. SELECT B.RESCLASS, COUNT(*) AS GROUPING_COUNT, 'SECURITY.BASERULE' AS "__objecttype__" FROM SECURITY.BASERULE AS B GROUP BY B.RESCLASS ORDER BY B.RESCLASS DESC LIMIT 22 Query2: This SQL pushes the Order By to the translator. SELECT B.RESCLASS, COUNT(*) AS GROUPING_COUNT FROM SECURITY.BASERULE AS B GROUP BY B.RESCLASS ORDER BY B.RESCLASS DESC LIMIT 22 -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators For more information on JIRA, see: http://www.atlassian.com/software/jira

12 years, 9 months

1
0
0 / 0

[JBoss JIRA] (TEIID-2568) Teiid Query Optimizer doesn't work properly with constants

by RAKESH BALGURI (JIRA)

[ https://issues.jboss.org/browse/TEIID-2568?page=com.atlassian.jira.plugin... ] RAKESH BALGURI updated TEIID-2568: ---------------------------------- Attachment: Query_Plan_SQL_1.txt Query_Plan_SQL_2.txt > Teiid Query Optimizer doesn't work properly with constants > ---------------------------------------------------------- > > Key: TEIID-2568 > URL: https://issues.jboss.org/browse/TEIID-2568 > Project: Teiid > Issue Type: Bug > Environment: Windows and teiid 8.1 > Reporter: RAKESH BALGURI > Assignee: Steven Hawkins > Attachments: Query_Plan_SQL_1.txt, Query_Plan_SQL_2.txt > > > I have a SQL which contain constants in the Select clause and also it contains Group By, Order By and Limit clauses in the SQL. > When I execute the SQL, the Order By clause is not being pushed to the underlying translator. My translator doesn't support "SelectExpressions" i.e. supportsSelectExpression() returns false. > When I remove the constant from the Select clause then the Order By is pushed to the translator. > > Is this a bug in Teiid's query Optimizer? > > The following are the sample queries and attached are the Query Plans for both the SQL's. > > Query1: This SQL doesn't push the Order By clause to the translator. > > SELECT B.RESCLASS, COUNT(*) AS GROUPING_COUNT, 'SECURITY.BASERULE' AS "__objecttype__" FROM > SECURITY.BASERULE AS B GROUP BY B.RESCLASS ORDER BY B.RESCLASS DESC LIMIT 22 > > Query2: This SQL pushes the Order By to the translator. > > SELECT B.RESCLASS, COUNT(*) AS GROUPING_COUNT FROM > SECURITY.BASERULE AS B GROUP BY B.RESCLASS ORDER BY B.RESCLASS DESC LIMIT 22 -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators For more information on JIRA, see: http://www.atlassian.com/software/jira

12 years, 9 months

1
0
0 / 0

[JBoss JIRA] (TEIID-2567) issues with permission element in vdb-deployer.xsd contains problematic choice node

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-2567?page=com.atlassian.jira.plugin... ] Steven Hawkins commented on TEIID-2567: --------------------------------------- > which may seem OK, but not from a schema standpoint That confuses me since it is valid and is exactly the intent. allows are logically grouped by object type, so it's perfectly natural to reflect that choice in the schema. To make this clearer, in an ideal world our permission would basically map to a grant: grant usage on language javascript grant select, insert, update on table foo or for us: "permission" allows "on" object name We currently capture name and allows, but not the object type. That's why we have an odd allows-language to convey "usage" and that the object in question is a language. See also TEIID-2167 > The simple fix/minimal change would to convert <allow-language> to an attribute and remove the choice. However that is not consistent with the other allows. Either they can all be elements, or all be attributes. > I wouldn't try to be backward compatible in this case. Backwards compatibility is a concern beyond just Designer usage. > issues with permission element in vdb-deployer.xsd contains problematic choice node > ----------------------------------------------------------------------------------- > > Key: TEIID-2567 > URL: https://issues.jboss.org/browse/TEIID-2567 > Project: Teiid > Issue Type: Task > Affects Versions: 8.4 > Reporter: Barry LaFond > Assignee: Steven Hawkins > Fix For: 8.4.1 > > > Having a lot of trouble trying to tweak our JaxB VDB element classes to accommodate the permission's choice node which contains a <sequence> and an <element>. > This structure is basically impossible to unmarshall using JAXB annotations (i.e. @XmlElements(value= {}) ) > Would clean things up to convert the <allow-language> element to an attribute: <xs:attribute name="allow-language" type="xs:boolean"/> and place it on the <permission> element AND remove the <choice> node entirely -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators For more information on JIRA, see: http://www.atlassian.com/software/jira

12 years, 9 months

1
0
0 / 0

[JBoss JIRA] (TEIID-2567) issues with permission element in vdb-deployer.xsd contains problematic choice node

by Barry LaFond (JIRA)

[ https://issues.jboss.org/browse/TEIID-2567?page=com.atlassian.jira.plugin... ] Barry LaFond commented on TEIID-2567: ------------------------------------- You introduced the choice element in Teiid 8.2 which The implementation of the "choice" between a generic/non-standard <sequence> and an <element>. Looking at it from an XML-only standpoint, you've created a scenario where a user would either enter 1-8 elements, or 1 element which may seem OK, but not from a schema standpoint. And JAXB constructs (annotations) can't handle it. The simple fix/minimal change would to convert <allow-language> to an attribute and remove the choice. There has been no Teiid Designer support for editing <allow-language> prior to our upcoming 8.2 release. So any users would be hand-editing the vdb.xml anyway to utilize that option. I wouldn't try to be backward compatible in this case. > issues with permission element in vdb-deployer.xsd contains problematic choice node > ----------------------------------------------------------------------------------- > > Key: TEIID-2567 > URL: https://issues.jboss.org/browse/TEIID-2567 > Project: Teiid > Issue Type: Task > Affects Versions: 8.4 > Reporter: Barry LaFond > Assignee: Steven Hawkins > Fix For: 8.4.1 > > > Having a lot of trouble trying to tweak our JaxB VDB element classes to accommodate the permission's choice node which contains a <sequence> and an <element>. > This structure is basically impossible to unmarshall using JAXB annotations (i.e. @XmlElements(value= {}) ) > Would clean things up to convert the <allow-language> element to an attribute: <xs:attribute name="allow-language" type="xs:boolean"/> and place it on the <permission> element AND remove the <choice> node entirely -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators For more information on JIRA, see: http://www.atlassian.com/software/jira

12 years, 9 months

1
0
0 / 0

2026

2025

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

teiid-issues July 2013