November 2018 - teiid-issues - Jboss List Archives

[JBoss JIRA] (TEIID-5541) Incorrect parsing of xml with XmlTable

by Steven Hawkins (Jira)

[ https://issues.jboss.org/browse/TEIID-5541?page=com.atlassian.jira.plugin... ] Steven Hawkins commented on TEIID-5541: --------------------------------------- Locally changing for TEIID-5542 //root works, but root()/root does not. There are a couple of issues. The path map created for root() is very inefficient - it matches all elements. And even though it matches all elements it still produces the wrong output - very strange. > Incorrect parsing of xml with XmlTable > -------------------------------------- > > Key: TEIID-5541 > URL: https://issues.jboss.org/browse/TEIID-5541 > Project: Teiid > Issue Type: Bug > Components: Query Engine > Affects Versions: 11.1 > Environment: teiid-11.1.0 (from 01.09.2018) on WildFly Full 11.0.0.Final (WildFly Core 3.0.8.Final) > Reporter: dalex dalex > Assignee: Steven Hawkins > Priority: Blocker > > Running the query: > {code:sql} > Begin > Declare xml xcontent = '<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> > <report> > <data> > <year xsi:type="decimal">2018</year> > </data> > </report> > <waitSeconds xsi:type="decimal">13</waitSeconds> > </root>' ; > Select * > From > XmlTable( > XmlNamespaces ('http://www.w3.org/2001/XMLSchema-instance' as xsi), > '/root/report/data' > PASSING xcontent > Columns > "year" integer, > waitSeconds xml Path 'root()/root/waitSeconds', > waitSecondsStr string Path 'root()/root/waitSeconds' > )a ; > End ;; > {code} > and having a look at the structure we can see that the value 13 should be returned, while the value is not picked from xml for result. -- This message was sent by Atlassian Jira (v7.12.1#712002)

6 years, 1 month

1
0
0 / 0

[JBoss JIRA] (TEIID-5542) XMLTable path assumptions

by Steven Hawkins (Jira)

Steven Hawkins created TEIID-5542: ------------------------------------- Summary: XMLTable path assumptions Key: TEIID-5542 URL: https://issues.jboss.org/browse/TEIID-5542 Project: Teiid Issue Type: Quality Risk Components: Query Engine Reporter: Steven Hawkins Assignee: Steven Hawkins Fix For: 12.0 By the spec, and for example with Postgresql, a column PATH that begins with / or // is not relative to the context item. Teiid modeled its initial implementation on Oracle where that is the case. For greater compliance we should add an option to change the handling to be spec compliant. -- This message was sent by Atlassian Jira (v7.12.1#712002)

6 years, 1 month

1
0
0 / 0

[JBoss JIRA] (TEIID-5541) Incorrect parsing of xml with XmlTable

by Steven Hawkins (Jira)

[ https://issues.jboss.org/browse/TEIID-5541?page=com.atlassian.jira.plugin... ] Steven Hawkins commented on TEIID-5541: --------------------------------------- This is an issue with document projection when using the root() expression. > Incorrect parsing of xml with XmlTable > -------------------------------------- > > Key: TEIID-5541 > URL: https://issues.jboss.org/browse/TEIID-5541 > Project: Teiid > Issue Type: Bug > Components: Query Engine > Affects Versions: 11.1 > Environment: teiid-11.1.0 (from 01.09.2018) on WildFly Full 11.0.0.Final (WildFly Core 3.0.8.Final) > Reporter: dalex dalex > Assignee: Steven Hawkins > Priority: Blocker > > Running the query: > {code:sql} > Begin > Declare xml xcontent = '<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> > <report> > <data> > <year xsi:type="decimal">2018</year> > </data> > </report> > <waitSeconds xsi:type="decimal">13</waitSeconds> > </root>' ; > Select * > From > XmlTable( > XmlNamespaces ('http://www.w3.org/2001/XMLSchema-instance' as xsi), > '/root/report/data' > PASSING xcontent > Columns > "year" integer, > waitSeconds xml Path 'root()/root/waitSeconds', > waitSecondsStr string Path 'root()/root/waitSeconds' > )a ; > End ;; > {code} > and having a look at the structure we can see that the value 13 should be returned, while the value is not picked from xml for result. -- This message was sent by Atlassian Jira (v7.12.1#712002)

6 years, 1 month

1
0
0 / 0

[JBoss JIRA] (TEIID-5541) Incorrect parsing of xml with XmlTable

by Steven Hawkins (Jira)

[ https://issues.jboss.org/browse/TEIID-5541?page=com.atlassian.jira.plugin... ] Work on TEIID-5541 started by Steven Hawkins. --------------------------------------------- > Incorrect parsing of xml with XmlTable > -------------------------------------- > > Key: TEIID-5541 > URL: https://issues.jboss.org/browse/TEIID-5541 > Project: Teiid > Issue Type: Bug > Components: Query Engine > Affects Versions: 11.1 > Environment: teiid-11.1.0 (from 01.09.2018) on WildFly Full 11.0.0.Final (WildFly Core 3.0.8.Final) > Reporter: dalex dalex > Assignee: Steven Hawkins > Priority: Blocker > > Running the query: > {code:sql} > Begin > Declare xml xcontent = '<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> > <report> > <data> > <year xsi:type="decimal">2018</year> > </data> > </report> > <waitSeconds xsi:type="decimal">13</waitSeconds> > </root>' ; > Select * > From > XmlTable( > XmlNamespaces ('http://www.w3.org/2001/XMLSchema-instance' as xsi), > '/root/report/data' > PASSING xcontent > Columns > "year" integer, > waitSeconds xml Path 'root()/root/waitSeconds', > waitSecondsStr string Path 'root()/root/waitSeconds' > )a ; > End ;; > {code} > and having a look at the structure we can see that the value 13 should be returned, while the value is not picked from xml for result. -- This message was sent by Atlassian Jira (v7.12.1#712002)

6 years, 1 month

1
0
0 / 0

[JBoss JIRA] (TEIID-5541) Incorrect parsing of xml with XmlTable

by dalex dalex (Jira)

dalex dalex created TEIID-5541: ---------------------------------- Summary: Incorrect parsing of xml with XmlTable Key: TEIID-5541 URL: https://issues.jboss.org/browse/TEIID-5541 Project: Teiid Issue Type: Bug Components: Query Engine Affects Versions: 11.1 Environment: teiid-11.1.0 (from 01.09.2018) on WildFly Full 11.0.0.Final (WildFly Core 3.0.8.Final) Reporter: dalex dalex Assignee: Steven Hawkins Running the query: {code:sql} Begin Declare xml xcontent = '<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <report> <data> <year xsi:type="decimal">2018</year> </data> </report> <waitSeconds xsi:type="decimal">13</waitSeconds> </root>' ; Select * From XmlTable( XmlNamespaces ('http://www.w3.org/2001/XMLSchema-instance' as xsi), '/root/report/data' PASSING xcontent Columns "year" integer, waitSeconds xml Path 'root()/root/waitSeconds', waitSecondsStr string Path 'root()/root/waitSeconds' )a ; End ;; {code} and having a look at the structure we can see that the value 13 should be returned, while the value is not picked from xml for result. -- This message was sent by Atlassian Jira (v7.12.1#712002)

6 years, 1 month

1
0
0 / 0

[JBoss JIRA] (TEIID-5532) Common table projection minimization does not account for usage in a subquery nested in an aggregate or in some view scenarios

by Steven Hawkins (Jira)

[ https://issues.jboss.org/browse/TEIID-5532?page=com.atlassian.jira.plugin... ] Steven Hawkins resolved TEIID-5532. ----------------------------------- Resolution: Done Thanks [~dalex005] marking as resolved again. > Common table projection minimization does not account for usage in a subquery nested in an aggregate or in some view scenarios > ------------------------------------------------------------------------------------------------------------------------------ > > Key: TEIID-5532 > URL: https://issues.jboss.org/browse/TEIID-5532 > Project: Teiid > Issue Type: Bug > Components: Query Engine > Reporter: Steven Hawkins > Assignee: Steven Hawkins > Priority: Blocker > Fix For: 11.0.3, 11.1.2, 12.0, 11.2.1 > > > Nesting the usage of a common table in an aggregate means that the columns can be removed inappropriately by projection minimization. A query such as: > with CTE1 as /*+ no_inline */ (SELECT e1, e2, e3 from pm1.g1) > select array_agg((select e3 from cte1 where e1=pm1.g2.e1 and e2=pm1.g2.e2)) from pm1.g2 > will return null values for all e3 as projection minimization of CTE1 will replace the column with a null value. > A similar situation occurs when the query above is defined instead in a view. A similar issue was captured on TEIID-5535 -- This message was sent by Atlassian Jira (v7.12.1#712002)

6 years, 1 month

1
0
0 / 0

[JBoss JIRA] (TEIID-5532) Common table projection minimization does not account for usage in a subquery nested in an aggregate or in some view scenarios

by dalex dalex (Jira)

[ https://issues.jboss.org/browse/TEIID-5532?page=com.atlassian.jira.plugin... ] dalex dalex commented on TEIID-5532: ------------------------------------ [~shawkins] thx a lot! I've just checked the next commit and it worked. My query started to work faster in twice than before the your latest commit and seems the query works with the same speed than before the entire issue. > Common table projection minimization does not account for usage in a subquery nested in an aggregate or in some view scenarios > ------------------------------------------------------------------------------------------------------------------------------ > > Key: TEIID-5532 > URL: https://issues.jboss.org/browse/TEIID-5532 > Project: Teiid > Issue Type: Bug > Components: Query Engine > Reporter: Steven Hawkins > Assignee: Steven Hawkins > Priority: Blocker > Fix For: 11.0.3, 11.1.2, 12.0, 11.2.1 > > > Nesting the usage of a common table in an aggregate means that the columns can be removed inappropriately by projection minimization. A query such as: > with CTE1 as /*+ no_inline */ (SELECT e1, e2, e3 from pm1.g1) > select array_agg((select e3 from cte1 where e1=pm1.g2.e1 and e2=pm1.g2.e2)) from pm1.g2 > will return null values for all e3 as projection minimization of CTE1 will replace the column with a null value. > A similar situation occurs when the query above is defined instead in a view. A similar issue was captured on TEIID-5535 -- This message was sent by Atlassian Jira (v7.12.1#712002)

6 years, 1 month

1
0
0 / 0

[JBoss JIRA] (TEIID-5532) Common table projection minimization does not account for usage in a subquery nested in an aggregate or in some view scenarios

by Steven Hawkins (Jira)

[ https://issues.jboss.org/browse/TEIID-5532?page=com.atlassian.jira.plugin... ] Steven Hawkins commented on TEIID-5532: --------------------------------------- [~dalex005] Please see the next commit. It narrows what is considered for usage. > Common table projection minimization does not account for usage in a subquery nested in an aggregate or in some view scenarios > ------------------------------------------------------------------------------------------------------------------------------ > > Key: TEIID-5532 > URL: https://issues.jboss.org/browse/TEIID-5532 > Project: Teiid > Issue Type: Bug > Components: Query Engine > Reporter: Steven Hawkins > Assignee: Steven Hawkins > Priority: Blocker > Fix For: 11.0.3, 11.1.2, 12.0, 11.2.1 > > > Nesting the usage of a common table in an aggregate means that the columns can be removed inappropriately by projection minimization. A query such as: > with CTE1 as /*+ no_inline */ (SELECT e1, e2, e3 from pm1.g1) > select array_agg((select e3 from cte1 where e1=pm1.g2.e1 and e2=pm1.g2.e2)) from pm1.g2 > will return null values for all e3 as projection minimization of CTE1 will replace the column with a null value. > A similar situation occurs when the query above is defined instead in a view. A similar issue was captured on TEIID-5535 -- This message was sent by Atlassian Jira (v7.12.1#712002)

6 years, 1 month

1
0
0 / 0

[JBoss JIRA] (TEIID-5532) Common table projection minimization does not account for usage in a subquery nested in an aggregate or in some view scenarios

by Steven Hawkins (Jira)

[ https://issues.jboss.org/browse/TEIID-5532?page=com.atlassian.jira.plugin... ] Steven Hawkins commented on TEIID-5532: --------------------------------------- Nearly there. I need to redo things a bit to make this work properly for cached resolved views. > Common table projection minimization does not account for usage in a subquery nested in an aggregate or in some view scenarios > ------------------------------------------------------------------------------------------------------------------------------ > > Key: TEIID-5532 > URL: https://issues.jboss.org/browse/TEIID-5532 > Project: Teiid > Issue Type: Bug > Components: Query Engine > Reporter: Steven Hawkins > Assignee: Steven Hawkins > Priority: Blocker > Fix For: 11.0.3, 11.1.2, 12.0, 11.2.1 > > > Nesting the usage of a common table in an aggregate means that the columns can be removed inappropriately by projection minimization. A query such as: > with CTE1 as /*+ no_inline */ (SELECT e1, e2, e3 from pm1.g1) > select array_agg((select e3 from cte1 where e1=pm1.g2.e1 and e2=pm1.g2.e2)) from pm1.g2 > will return null values for all e3 as projection minimization of CTE1 will replace the column with a null value. > A similar situation occurs when the query above is defined instead in a view. A similar issue was captured on TEIID-5535 -- This message was sent by Atlassian Jira (v7.12.1#712002)

6 years, 1 month

1
0
0 / 0

[JBoss JIRA] (TEIID-5532) Common table projection minimization does not account for usage in a subquery nested in an aggregate or in some view scenarios

by dalex dalex (Jira)

[ https://issues.jboss.org/browse/TEIID-5532?page=com.atlassian.jira.plugin... ] dalex dalex commented on TEIID-5532: ------------------------------------ Thx! I can check your improvements once they are committed. > Common table projection minimization does not account for usage in a subquery nested in an aggregate or in some view scenarios > ------------------------------------------------------------------------------------------------------------------------------ > > Key: TEIID-5532 > URL: https://issues.jboss.org/browse/TEIID-5532 > Project: Teiid > Issue Type: Bug > Components: Query Engine > Reporter: Steven Hawkins > Assignee: Steven Hawkins > Priority: Blocker > Fix For: 11.0.3, 11.1.2, 12.0, 11.2.1 > > > Nesting the usage of a common table in an aggregate means that the columns can be removed inappropriately by projection minimization. A query such as: > with CTE1 as /*+ no_inline */ (SELECT e1, e2, e3 from pm1.g1) > select array_agg((select e3 from cte1 where e1=pm1.g2.e1 and e2=pm1.g2.e2)) from pm1.g2 > will return null values for all e3 as projection minimization of CTE1 will replace the column with a null value. > A similar situation occurs when the query above is defined instead in a view. A similar issue was captured on TEIID-5535 -- This message was sent by Atlassian Jira (v7.12.1#712002)

6 years, 1 month

1
0
0 / 0

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

teiid-issues November 2018