April 2016 - teiid-issues - Jboss List Archives

[JBoss JIRA] (TEIID-4121) Enhancing the External Materialization

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-4121?page=com.atlassian.jira.plugin... ] Steven Hawkins commented on TEIID-4121: --------------------------------------- The query you could issue would look like: select m.schemaName, m.name, s.* from sysadmin.matviews as m, table(call sysadmin.matviewstatus(m.schemaName, m.name)) as s Later we can address if this needs paginated, and that unavailability of any status table would cause the query to fail. > Enhancing the External Materialization > -------------------------------------- > > Key: TEIID-4121 > URL: https://issues.jboss.org/browse/TEIID-4121 > Project: Teiid > Issue Type: Sub-task > Components: Query Engine > Affects Versions: 9.x > Reporter: Kylin Soong > Assignee: Steven Hawkins > Fix For: 9.0 > > > The intention of move "status" table to physical database is to increase durable and fully control refresh and loading, but it increase the complexity. > The "status" table by design should unique for whole VDB, if you look the https://teiid.gitbooks.io/documents/content/caching/External_Materializat..., the table structure: > {code:sql} > CREATE TABLE status > ( > VDBName varchar(50) not null, > VDBVersion integer not null, > SchemaName varchar(50) not null, > Name varchar(256) not null, > TargetSchemaName varchar(50), > TargetName varchar(256) not null, > Valid boolean not null, > LoadState varchar(25) not null, > Cardinality long, > Updated timestamp not null, > LoadNumber long not null, > PRIMARY KEY (VDBName, VDBVersion, SchemaName, Name) > ); > {code} > but currently, one VDB may have multiple "status" table, each view may have it's own "status" table. Further more, we can consider create status table automatically, which like internal, status create once VDB start, and configured in VDB scope. > From finishedDeployment logic in MaterializationManager, MATERIALIZED_TABLE be used to determine whether the Mat is internal or external, But we lack the validation in metadata loading, in my previous test, the Internal Mat view configured lots of external view's properties like "status" table, the validation not throw excepton. -- This message was sent by Atlassian JIRA (v6.4.11#64026)

9 years, 11 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4137) Postgresql translator should support recursive common table expressions

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-4137?page=com.atlassian.jira.plugin... ] Steven Hawkins resolved TEIID-4137. ----------------------------------- Resolution: Done Added the missing support. > Postgresql translator should support recursive common table expressions > ----------------------------------------------------------------------- > > Key: TEIID-4137 > URL: https://issues.jboss.org/browse/TEIID-4137 > Project: Teiid > Issue Type: Enhancement > Components: JDBC Connector > Affects Versions: 8.9 > Reporter: Steven Hawkins > Assignee: Steven Hawkins > Fix For: 9.0, 8.12.5 > > > The support for recursive common table expressions is missing for postgresql. -- This message was sent by Atlassian JIRA (v6.4.11#64026)

9 years, 11 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4137) Postgresql translator should support recursive common table expressions

by Steven Hawkins (JIRA)

Steven Hawkins created TEIID-4137: ------------------------------------- Summary: Postgresql translator should support recursive common table expressions Key: TEIID-4137 URL: https://issues.jboss.org/browse/TEIID-4137 Project: Teiid Issue Type: Enhancement Components: JDBC Connector Affects Versions: 8.9 Reporter: Steven Hawkins Assignee: Steven Hawkins Fix For: 9.0, 8.12.5 The support for recursive common table expressions is missing for postgresql. -- This message was sent by Atlassian JIRA (v6.4.11#64026)

9 years, 11 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4135) Invalid query when using recursive CTE with translators that do not suport RCTE pushdown

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-4135?page=com.atlassian.jira.plugin... ] Steven Hawkins resolved TEIID-4135. ----------------------------------- Fix Version/s: 9.0 8.12.5 Resolution: Done The issue here is if the anchor can be pushed, but the entire cte cannot be. The planner still proceeded to allow the temporary cte group to be seen as associated with the source. The new logic will replan if recursive query if both parts cannot be pushed. > Invalid query when using recursive CTE with translators that do not suport RCTE pushdown > ---------------------------------------------------------------------------------------- > > Key: TEIID-4135 > URL: https://issues.jboss.org/browse/TEIID-4135 > Project: Teiid > Issue Type: Bug > Components: JDBC Connector > Affects Versions: 8.12.x > Reporter: Andrej Šmigala > Assignee: Steven Hawkins > Fix For: 9.0, 8.12.5 > > > When running a recursive common table expression query against a source that does not support recursive cte pushdown, only the recursive part of the query is pushed, which fails, since it references a non-existent table (the cte). > E. g. the query > {code:sql} > WITH tmp_cte(id, fk, lvl) AS ( > SELECT id, fk, 0 as lvl FROM SourceModel.cte_source WHERE fk IS NULL > UNION ALL > SELECT e.id, e.fk, lvl + 1 as lvl FROM SourceModel.cte_source AS e INNER JOIN tmp_cte AS ecte ON ecte.id = e.fk > ) > SELECT * FROM tmp_cte > {code} > against a PostreSQL source results in {noformat}Remote org.postgresql.util.PSQLException: ERROR: relation "tmp_cte" does not exist{noformat} > because this query is pushed down: > {code:sql}SELECT g_0.id, g_0.fk, g_0.lvl FROM tmp_cte AS g_0{code} -- This message was sent by Atlassian JIRA (v6.4.11#64026)

9 years, 11 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4133) SQL Server requires CAST when using NULL in anchor part of recursive CTE

by RH Bugzilla Integration (JIRA)

[ https://issues.jboss.org/browse/TEIID-4133?page=com.atlassian.jira.plugin... ] RH Bugzilla Integration commented on TEIID-4133: ------------------------------------------------ Van Halbert <vhalbert(a)redhat.com> changed the Status of [bug 1326826|https://bugzilla.redhat.com/show_bug.cgi?id=1326826] from NEW to MODIFIED > SQL Server requires CAST when using NULL in anchor part of recursive CTE > ------------------------------------------------------------------------ > > Key: TEIID-4133 > URL: https://issues.jboss.org/browse/TEIID-4133 > Project: Teiid > Issue Type: Bug > Components: JDBC Connector > Affects Versions: 8.12.x > Reporter: Andrej Šmigala > Assignee: Steven Hawkins > Fix For: 9.0, 8.12.5 > > > Running the following query in teiid: > {code:sql} > with a (intkey, stringcolumn, lvl) as > ( > select intkey, NULL as stringcolumn, 0 as lvl from bqt1.smallb where intkey = 1 > union all > select n.intkey, n.stringkey as stringcolumn, rcte.lvl + 1 as lvl from bqt1.smallb n inner join a rcte on n.intkey = rcte.intkey + 1 > ) > select * from a > {code} > results in the following source query: > {code:sql} > WITH a (intkey, stringcolumn, lvl) AS > ( > SELECT SmallB.IntKey, NULL AS stringcolumn, 0 AS lvl FROM SmallB WHERE SmallB.IntKey = 1 > UNION ALL > SELECT n.IntKey, n.StringKey AS stringcolumn, (rcte.lvl + 1) AS lvl FROM SmallB n INNER JOIN a rcte ON n.IntKey = (rcte.intkey + 1) > ) > SELECT a.intkey, a.stringcolumn, a.lvl FROM a > {code} > which fails on SQL Server with {noformat}Types don't match between the anchor and the recursive part in column "stringcolumn" of recursive query "a".{noformat}. > The source query should be > {code:sql} > WITH a (intkey, stringcolumn, lvl) AS > ( > SELECT SmallB.IntKey, CAST(NULL AS VARCHAR(10)) AS stringcolumn, 0 AS lvl FROM SmallB WHERE SmallB.IntKey = 1 > UNION ALL > SELECT n.IntKey, n.StringKey AS stringcolumn, (rcte.lvl + 1) AS lvl FROM SmallB n INNER JOIN a rcte ON n.IntKey = (rcte.intkey + 1) > ) > SELECT a.intkey, a.stringcolumn, a.lvl FROM a > {code} > i.e. the NULL has to be cast to the precise type of the column in the recursive part of the query. -- This message was sent by Atlassian JIRA (v6.4.11#64026)

9 years, 11 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4133) SQL Server requires CAST when using NULL in anchor part of recursive CTE

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-4133?page=com.atlassian.jira.plugin... ] Steven Hawkins resolved TEIID-4133. ----------------------------------- Fix Version/s: 9.0 8.12.5 Resolution: Done Added a best effort check to resolve the types. > SQL Server requires CAST when using NULL in anchor part of recursive CTE > ------------------------------------------------------------------------ > > Key: TEIID-4133 > URL: https://issues.jboss.org/browse/TEIID-4133 > Project: Teiid > Issue Type: Bug > Components: JDBC Connector > Affects Versions: 8.12.x > Reporter: Andrej Šmigala > Assignee: Steven Hawkins > Fix For: 9.0, 8.12.5 > > > Running the following query in teiid: > {code:sql} > with a (intkey, stringcolumn, lvl) as > ( > select intkey, NULL as stringcolumn, 0 as lvl from bqt1.smallb where intkey = 1 > union all > select n.intkey, n.stringkey as stringcolumn, rcte.lvl + 1 as lvl from bqt1.smallb n inner join a rcte on n.intkey = rcte.intkey + 1 > ) > select * from a > {code} > results in the following source query: > {code:sql} > WITH a (intkey, stringcolumn, lvl) AS > ( > SELECT SmallB.IntKey, NULL AS stringcolumn, 0 AS lvl FROM SmallB WHERE SmallB.IntKey = 1 > UNION ALL > SELECT n.IntKey, n.StringKey AS stringcolumn, (rcte.lvl + 1) AS lvl FROM SmallB n INNER JOIN a rcte ON n.IntKey = (rcte.intkey + 1) > ) > SELECT a.intkey, a.stringcolumn, a.lvl FROM a > {code} > which fails on SQL Server with {noformat}Types don't match between the anchor and the recursive part in column "stringcolumn" of recursive query "a".{noformat}. > The source query should be > {code:sql} > WITH a (intkey, stringcolumn, lvl) AS > ( > SELECT SmallB.IntKey, CAST(NULL AS VARCHAR(10)) AS stringcolumn, 0 AS lvl FROM SmallB WHERE SmallB.IntKey = 1 > UNION ALL > SELECT n.IntKey, n.StringKey AS stringcolumn, (rcte.lvl + 1) AS lvl FROM SmallB n INNER JOIN a rcte ON n.IntKey = (rcte.intkey + 1) > ) > SELECT a.intkey, a.stringcolumn, a.lvl FROM a > {code} > i.e. the NULL has to be cast to the precise type of the column in the recursive part of the query. -- This message was sent by Atlassian JIRA (v6.4.11#64026)

9 years, 11 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4136) Sybase/SQL Server translator varchar length incorrect

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-4136?page=com.atlassian.jira.plugin... ] Steven Hawkins resolved TEIID-4136. ----------------------------------- Resolution: Done Updated to 4000 > Sybase/SQL Server translator varchar length incorrect > ----------------------------------------------------- > > Key: TEIID-4136 > URL: https://issues.jboss.org/browse/TEIID-4136 > Project: Teiid > Issue Type: Bug > Components: JDBC Connector > Affects Versions: 7.0 > Reporter: Steven Hawkins > Assignee: Steven Hawkins > Fix For: 9.0, 8.12.5, 8.13.4 > > > The teiid string type is being mapped to varchar(40) - it should be varchar(4000). -- This message was sent by Atlassian JIRA (v6.4.11#64026)

9 years, 11 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4136) Sybase/SQL Server translator varchar length incorrect

by Steven Hawkins (JIRA)

Steven Hawkins created TEIID-4136: ------------------------------------- Summary: Sybase/SQL Server translator varchar length incorrect Key: TEIID-4136 URL: https://issues.jboss.org/browse/TEIID-4136 Project: Teiid Issue Type: Bug Components: JDBC Connector Affects Versions: 7.0 Reporter: Steven Hawkins Assignee: Steven Hawkins Fix For: 9.0, 8.12.5, 8.13.4 The teiid string type is being mapped to varchar(40) - it should be varchar(4000). -- This message was sent by Atlassian JIRA (v6.4.11#64026)

9 years, 11 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4133) SQL Server requires CAST when using NULL in anchor part of recursive CTE

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-4133?page=com.atlassian.jira.plugin... ] Steven Hawkins commented on TEIID-4133: --------------------------------------- This goes beyond null values - the exact type / type constraint must match between all columns. So even using a string literal, or another column, won't work here. This is odd as SQL Server for just a regular unions will resolve these these just fine. > SQL Server requires CAST when using NULL in anchor part of recursive CTE > ------------------------------------------------------------------------ > > Key: TEIID-4133 > URL: https://issues.jboss.org/browse/TEIID-4133 > Project: Teiid > Issue Type: Bug > Components: JDBC Connector > Affects Versions: 8.12.x > Reporter: Andrej Šmigala > Assignee: Steven Hawkins > > Running the following query in teiid: > {code:sql} > with a (intkey, stringcolumn, lvl) as > ( > select intkey, NULL as stringcolumn, 0 as lvl from bqt1.smallb where intkey = 1 > union all > select n.intkey, n.stringkey as stringcolumn, rcte.lvl + 1 as lvl from bqt1.smallb n inner join a rcte on n.intkey = rcte.intkey + 1 > ) > select * from a > {code} > results in the following source query: > {code:sql} > WITH a (intkey, stringcolumn, lvl) AS > ( > SELECT SmallB.IntKey, NULL AS stringcolumn, 0 AS lvl FROM SmallB WHERE SmallB.IntKey = 1 > UNION ALL > SELECT n.IntKey, n.StringKey AS stringcolumn, (rcte.lvl + 1) AS lvl FROM SmallB n INNER JOIN a rcte ON n.IntKey = (rcte.intkey + 1) > ) > SELECT a.intkey, a.stringcolumn, a.lvl FROM a > {code} > which fails on SQL Server with {noformat}Types don't match between the anchor and the recursive part in column "stringcolumn" of recursive query "a".{noformat}. > The source query should be > {code:sql} > WITH a (intkey, stringcolumn, lvl) AS > ( > SELECT SmallB.IntKey, CAST(NULL AS VARCHAR(10)) AS stringcolumn, 0 AS lvl FROM SmallB WHERE SmallB.IntKey = 1 > UNION ALL > SELECT n.IntKey, n.StringKey AS stringcolumn, (rcte.lvl + 1) AS lvl FROM SmallB n INNER JOIN a rcte ON n.IntKey = (rcte.intkey + 1) > ) > SELECT a.intkey, a.stringcolumn, a.lvl FROM a > {code} > i.e. the NULL has to be cast to the precise type of the column in the recursive part of the query. -- This message was sent by Atlassian JIRA (v6.4.11#64026)

9 years, 11 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4129) Wrong results with cross model join on 2 varchar fields

by Mark Tawk (JIRA)

[ https://issues.jboss.org/browse/TEIID-4129?page=com.atlassian.jira.plugin... ] Mark Tawk commented on TEIID-4129: ---------------------------------- You find attached the MetaDataStore of the 2 corresponding DBs. The corresponding fields types are VARCHAR_IGNORECASE, and the joined data values have exactly the same case. > Wrong results with cross model join on 2 varchar fields > ------------------------------------------------------- > > Key: TEIID-4129 > URL: https://issues.jboss.org/browse/TEIID-4129 > Project: Teiid > Issue Type: Bug > Reporter: Mark Tawk > Assignee: Steven Hawkins > Attachments: H2 DBs.zip, MetaDataStores.zip > > > I'm using Teiid 8.11.3 with H2 translator. > I'm joining 2 h2 tables from 2 different teiid models linked on varchar fields. > the query result is retuning empty values from the joined table even though the joined values are identical. > You find attached a zip file containing 2 h2 DBs for a simplified example > Here is the query used: > _select "Customer"."City" as "Customer_City","Customer"."CustomerID" as "Customer_CustomerID","City"."City" as "City_City","City"."CityID" as "City_CityID" > from "db2"."Customer" "Customer" > LEFT JOIN "db1"."City" "City" ON "Customer"."City" = "City"."City"_ > Note that if the 2 tables are in the same model, the query return correct results. -- This message was sent by Atlassian JIRA (v6.4.11#64026)

9 years, 11 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 April 2016