April 2016 - teiid-issues - Jboss List Archives

[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 updated TEIID-4129: ----------------------------- Attachment: MetaDataStores.zip > 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

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

by RH Bugzilla Integration (JIRA)

[ https://issues.jboss.org/browse/TEIID-4135?page=com.atlassian.jira.plugin... ] RH Bugzilla Integration updated TEIID-4135: ------------------------------------------- Bugzilla References: https://bugzilla.redhat.com/show_bug.cgi?id=1326842 Bugzilla Update: Perform > 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 > > 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-4135) Invalid query when using recursive CTE with translators that do not suport RCTE pushdown

by Andrej Šmigala (JIRA)

Andrej Šmigala created TEIID-4135: ------------------------------------- Summary: 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 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-4130) Prevent blocked exceptions from being thrown in AccessNode open

by RH Bugzilla Integration (JIRA)

[ https://issues.jboss.org/browse/TEIID-4130?page=com.atlassian.jira.plugin... ] RH Bugzilla Integration updated TEIID-4130: ------------------------------------------- Bugzilla References: https://bugzilla.redhat.com/show_bug.cgi?id=1326832 Bugzilla Update: Perform > Prevent blocked exceptions from being thrown in AccessNode open > --------------------------------------------------------------- > > Key: TEIID-4130 > URL: https://issues.jboss.org/browse/TEIID-4130 > Project: Teiid > Issue Type: Bug > Components: Query Engine > Affects Versions: 8.7 > Reporter: Steven Hawkins > Assignee: Steven Hawkins > Fix For: 9.0, 8.12.5, 8.13.4 > > > Having the access node block in open can lead to unintended consequences in parent nodes. For example with tuplesource caching it can create additional tuplesource instances such the cached source is closed prematurely (as it thinks that all readers have been satisfied). This results in exceptions similar to: > TEIID30019 Unexpected exception for request 2TLgULsEBZvO.8: java.lang.AssertionError: ASSERTION FAILED: expected reference to be not null > ... > at org.teiid.common.buffer.TupleBuffer.getBatch(TupleBuffer.java:286) \[teiid-engine-8.7.5.6_2-redhat-1.jar:8.7.5.6_2-redhat-1] > at org.teiid.dqp.internal.process.TupleSourceCache$CopyOnReadTupleSource.nextTuple(TupleSourceCache.java:103) \[teiid-engine-8.7.5.6_2-redhat-1.jar:8.7.5.6_2-redhat-1] > ... -- This message was sent by Atlassian JIRA (v6.4.11#64026)

9 years, 11 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4134) Oracle requires column alias list when using recursive CTE

by RH Bugzilla Integration (JIRA)

[ https://issues.jboss.org/browse/TEIID-4134?page=com.atlassian.jira.plugin... ] RH Bugzilla Integration updated TEIID-4134: ------------------------------------------- Bugzilla References: https://bugzilla.redhat.com/show_bug.cgi?id=1326833 Bugzilla Update: Perform > Oracle requires column alias list when using recursive CTE > ---------------------------------------------------------- > > Key: TEIID-4134 > URL: https://issues.jboss.org/browse/TEIID-4134 > Project: Teiid > Issue Type: Bug > Components: JDBC Connector > Affects Versions: 8.12.x > Reporter: Andrej Šmigala > Assignee: Steven Hawkins > > Running a recursive common table expression query against an Oracle source removes the columns alias list, which causes {noformat}ORA-32039: recursive WITH clause must have column alias list{noformat} > Note that there might be an issue with some specific versions of Oracle 11g, the columns alias list is required 11.2.0.4, but might not be in 11.2.0.2. -- This message was sent by Atlassian JIRA (v6.4.11#64026)

9 years, 11 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4134) Oracle requires column alias list when using recursive CTE

by Andrej Šmigala (JIRA)

Andrej Šmigala created TEIID-4134: ------------------------------------- Summary: Oracle requires column alias list when using recursive CTE Key: TEIID-4134 URL: https://issues.jboss.org/browse/TEIID-4134 Project: Teiid Issue Type: Bug Components: JDBC Connector Affects Versions: 8.12.x Reporter: Andrej Šmigala Assignee: Steven Hawkins Running a recursive common table expression query against an Oracle source removes the columns alias list, which causes {noformat}ORA-32039: recursive WITH clause must have column alias list{noformat} Note that there might be an issue with some specific versions of Oracle 11g, the columns alias list is required 11.2.0.4, but might not be in 11.2.0.2. -- 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 updated TEIID-4133: ------------------------------------------- Bugzilla References: https://bugzilla.redhat.com/show_bug.cgi?id=1326826 Bugzilla Update: Perform > 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-4133) SQL Server requires CAST when using NULL in anchor part of recursive CTE

by Andrej Šmigala (JIRA)

Andrej Šmigala created TEIID-4133: ------------------------------------- Summary: 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-4132) Internal Mat View's teiid_rel:MATVIEW_PREFER_MEMEORY option not take effect

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-4132?page=com.atlassian.jira.plugin... ] Steven Hawkins updated TEIID-4132: ---------------------------------- Fix Version/s: (was: 9.x) > Internal Mat View's teiid_rel:MATVIEW_PREFER_MEMEORY option not take effect > --------------------------------------------------------------------------- > > Key: TEIID-4132 > URL: https://issues.jboss.org/browse/TEIID-4132 > Project: Teiid > Issue Type: Bug > Components: Query Engine > Affects Versions: 9.x > Reporter: Kylin Soong > Assignee: Steven Hawkins > > From [https://teiid.gitbooks.io/documents/content/caching/Internal_Materializat...], the "teiid_rel:MATVIEW_PREFER_MEMEORY" should be same as the pref_mem cache hint option. But in my debug, it's not convert to cache hint, for example, > {code} > ) OPTIONS (materialized true, > UPDATABLE 'TRUE', > "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true', > "teiid_rel:MATVIEW_PREFER_MEMEORY" 'true', > "teiid_rel:MATVIEW_TTL" 300000, > "teiid_rel:MATVIEW_UPDATABLE" 'true', > "teiid_rel:MATVIEW_SCOPE" 'VDB' > ) > {code} > In TempTableDataManager's loadGlobalTable() method, the cache hint is like > {code} > /*+ cache(updatable scope:VDB) */ > {code} > also the _hint.isPrefersMemory()_ result is false. > So how to understand "teiid_rel:MATVIEW_PREFER_MEMEORY" in internal mat? does this is expect behivior? -- This message was sent by Atlassian JIRA (v6.4.11#64026)

9 years, 11 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4132) Internal Mat View's teiid_rel:MATVIEW_PREFER_MEMEORY option not take effect

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-4132?page=com.atlassian.jira.plugin... ] Steven Hawkins resolved TEIID-4132. ----------------------------------- Resolution: Rejected Not all properties are conveyed on the generated query - see GlobalTempTableStoreImpl.updateCacheHint and GlobalTableStoreImpl.createMatTable > Internal Mat View's teiid_rel:MATVIEW_PREFER_MEMEORY option not take effect > --------------------------------------------------------------------------- > > Key: TEIID-4132 > URL: https://issues.jboss.org/browse/TEIID-4132 > Project: Teiid > Issue Type: Bug > Components: Query Engine > Affects Versions: 9.x > Reporter: Kylin Soong > Assignee: Steven Hawkins > Fix For: 9.x > > > From [https://teiid.gitbooks.io/documents/content/caching/Internal_Materializat...], the "teiid_rel:MATVIEW_PREFER_MEMEORY" should be same as the pref_mem cache hint option. But in my debug, it's not convert to cache hint, for example, > {code} > ) OPTIONS (materialized true, > UPDATABLE 'TRUE', > "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true', > "teiid_rel:MATVIEW_PREFER_MEMEORY" 'true', > "teiid_rel:MATVIEW_TTL" 300000, > "teiid_rel:MATVIEW_UPDATABLE" 'true', > "teiid_rel:MATVIEW_SCOPE" 'VDB' > ) > {code} > In TempTableDataManager's loadGlobalTable() method, the cache hint is like > {code} > /*+ cache(updatable scope:VDB) */ > {code} > also the _hint.isPrefersMemory()_ result is false. > So how to understand "teiid_rel:MATVIEW_PREFER_MEMEORY" in internal mat? does this is expect behivior? -- 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