May 2016 - teiid-issues - Jboss List Archives

[JBoss JIRA] (TEIID-4221) HANA translator: incorrect pushdown of week function

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-4221?page=com.atlassian.jira.plugin... ] Steven Hawkins updated TEIID-4221: ---------------------------------- Fix Version/s: 8.13.5 > HANA translator: incorrect pushdown of week function > ---------------------------------------------------- > > Key: TEIID-4221 > URL: https://issues.jboss.org/browse/TEIID-4221 > Project: Teiid > Issue Type: Bug > Components: Misc. Connectors > Affects Versions: 8.12.5 > Reporter: Jan Stastny > Assignee: Steven Hawkins > Fix For: 9.0, 8.12.5, 8.13.5 > > > Hana translator pushes down function week to SAP HANA datasource as its WEEK function. But it is not in accordance with the ISO8601 rule. > For this standard, HANA has function ISOWEEK, which returns results in following format: > {code:plain} > 2000-W01 for date 'Jan 3, 2000' > {code} > So it should be possible to push Teiid's WEEK function as: > {code:sql} > SUBSTR_REGEXPR('[[:digit:]]{4}\-W([[:digit:]]{2})' IN ISOWEEK(datevalue) GROUP 1) > {code} > where 'datevalue' is column of DATE datatype. -- This message was sent by Atlassian JIRA (v6.4.11#64026)

9 years, 10 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4227) Impala Translator - Support Multiple Count Distinct with Group By

by Scott Wallace (JIRA)

Scott Wallace created TEIID-4227: ------------------------------------ Summary: Impala Translator - Support Multiple Count Distinct with Group By Key: TEIID-4227 URL: https://issues.jboss.org/browse/TEIID-4227 Project: Teiid Issue Type: Feature Request Components: Misc. Connectors Affects Versions: 8.13.3 Reporter: Scott Wallace Assignee: Steven Hawkins This is somewhat related to TEIID-3743, which added support of multiple count distinct metrics. We have a requirement to allow multiple count distinct metrics grouped by common attributes. Currently, we get a failure as follows: {noformat}org.teiid.runtime.client.TeiidClientException: java.lang.RuntimeException: Remote org.teiid.core.TeiidProcessingException: TEIID30504 vw_impression_click_transaction_process_date_detail: 500051 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.process_date_key, SUM(g_0.num_clicks), SUM(g_0.num_impressions), COUNT(DISTINCT g_0.orderid), COUNT(DISTINCT g_0.`hash_tid`), SUM(g_0.sales) FROM detail.vw_impression_click_transaction_process_date_detail g_0 WHERE g_0.process_date_key >= '2016-05-22' AND g_0.process_date_key < '2016-05-23' AND g_0.advertiser_key = 2149 GROUP BY g_0.process_date_key] {noformat} To allow this request to succeed, we could have translator write that Impala query as: {noformat} select nvl(v1.c1,v2.c1), nvl(v1.c2,v1.c2), nvl(v1.c3,v1.c3), v1.c4, v2.c5, nvl(v1.c6,v1.c6) from ( SELECT g_0.process_date_key as c1, SUM(g_0.num_clicks) as c2, SUM(g_0.num_impressions) as c3, COUNT(DISTINCT g_0.orderid) as c4, SUM(g_0.sales) as c6 FROM detail.vw_impression_click_transaction_process_date_detail g_0 WHERE g_0.process_date_key >= '2016-05-22' AND g_0.process_date_key < '2016-05-23' AND g_0.advertiser_key = 2149 GROUP BY g_0.process_date_key ) v1 full outer join ( SELECT g_0.process_date_key as c1, SUM(g_0.num_clicks) as c2, SUM(g_0.num_impressions) as c3, COUNT(DISTINCT g_0.`hash_tid`) as c5, SUM(g_0.sales) as c6 FROM detail.vw_impression_click_transaction_process_date_detail g_0 WHERE g_0.process_date_key >= '2016-05-22' AND g_0.process_date_key < '2016-05-23' AND g_0.advertiser_key = 2149 GROUP BY g_0.process_date_key ) v2 on v1.c1 = v2.c1; {noformat} FYI [~shawkins] -- This message was sent by Atlassian JIRA (v6.4.11#64026)

9 years, 10 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4221) HANA translator: incorrect pushdown of week function

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-4221?page=com.atlassian.jira.plugin... ] Steven Hawkins resolved TEIID-4221. ----------------------------------- Fix Version/s: 9.0 8.12.5 Resolution: Done Used an approach similar to the above casting the substring from isoweek to an integer. > HANA translator: incorrect pushdown of week function > ---------------------------------------------------- > > Key: TEIID-4221 > URL: https://issues.jboss.org/browse/TEIID-4221 > Project: Teiid > Issue Type: Bug > Components: Misc. Connectors > Affects Versions: 8.12.5 > Reporter: Jan Stastny > Assignee: Steven Hawkins > Fix For: 9.0, 8.12.5 > > > Hana translator pushes down function week to SAP HANA datasource as its WEEK function. But it is not in accordance with the ISO8601 rule. > For this standard, HANA has function ISOWEEK, which returns results in following format: > {code:plain} > 2000-W01 for date 'Jan 3, 2000' > {code} > So it should be possible to push Teiid's WEEK function as: > {code:sql} > SUBSTR_REGEXPR('[[:digit:]]{4}\-W([[:digit:]]{2})' IN ISOWEEK(datevalue) GROUP 1) > {code} > where 'datevalue' is column of DATE datatype. -- This message was sent by Atlassian JIRA (v6.4.11#64026)

9 years, 10 months

1
0
0 / 0

[JBoss JIRA] (TEIID-3743) Multiple Count Distinct Columns Fails for Impala

by Scott Wallace (JIRA)

[ https://issues.jboss.org/browse/TEIID-3743?page=com.atlassian.jira.plugin... ] Scott Wallace commented on TEIID-3743: -------------------------------------- Thanks [~shawkins] I'll close this and open another issue > Multiple Count Distinct Columns Fails for Impala > ------------------------------------------------ > > Key: TEIID-3743 > URL: https://issues.jboss.org/browse/TEIID-3743 > Project: Teiid > Issue Type: Bug > Components: Misc. Connectors > Affects Versions: 8.11.4 > Reporter: Scott Wallace > Assignee: Steven Hawkins > Fix For: 8.12 > > > Teiid Impala translator incorrectly allows multiple count distinct columns, which fails to execute since it is not supported by Impala. > Per Cloudera documentation: > {quote}By default, Impala only allows a single COUNT(DISTINCT columns) expression in each query. > To produce the same result as multiple COUNT(DISTINCT) expressions, you can use the following technique for queries involving a single table: > select v1.c1 result1, v2.c1 result2 from (select count(distinct col1) as c1 from t1) v1 cross join (select count(distinct col2) as c1 from t1) v2;{quote} > The Teiid-Impala translator should ideally rewrite the query as necessary so that the query does not fail executing multiple count distincts against Impala. -- This message was sent by Atlassian JIRA (v6.4.11#64026)

9 years, 10 months

1
0
0 / 0

[JBoss JIRA] (TEIID-3743) Multiple Count Distinct Columns Fails for Impala

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-3743?page=com.atlassian.jira.plugin... ] Steven Hawkins closed TEIID-3743. --------------------------------- Resolution: Done See the comment above, please open a new issue to track the cases with grouping / having. > Multiple Count Distinct Columns Fails for Impala > ------------------------------------------------ > > Key: TEIID-3743 > URL: https://issues.jboss.org/browse/TEIID-3743 > Project: Teiid > Issue Type: Bug > Components: Misc. Connectors > Affects Versions: 8.11.4 > Reporter: Scott Wallace > Assignee: Steven Hawkins > Fix For: 8.12 > > > Teiid Impala translator incorrectly allows multiple count distinct columns, which fails to execute since it is not supported by Impala. > Per Cloudera documentation: > {quote}By default, Impala only allows a single COUNT(DISTINCT columns) expression in each query. > To produce the same result as multiple COUNT(DISTINCT) expressions, you can use the following technique for queries involving a single table: > select v1.c1 result1, v2.c1 result2 from (select count(distinct col1) as c1 from t1) v1 cross join (select count(distinct col2) as c1 from t1) v2;{quote} > The Teiid-Impala translator should ideally rewrite the query as necessary so that the query does not fail executing multiple count distincts against Impala. -- This message was sent by Atlassian JIRA (v6.4.11#64026)

9 years, 10 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4226) HANA translator: boolean type converted to tinyint in each convert

by Ted Jones (JIRA)

[ https://issues.jboss.org/browse/TEIID-4226?page=com.atlassian.jira.plugin... ] Ted Jones reassigned TEIID-4226: -------------------------------- Assignee: Steven Hawkins (was: Ted Jones) > HANA translator: boolean type converted to tinyint in each convert > ------------------------------------------------------------------ > > Key: TEIID-4226 > URL: https://issues.jboss.org/browse/TEIID-4226 > Project: Teiid > Issue Type: Bug > Components: Misc. Connectors > Affects Versions: 8.12.5 > Reporter: Jan Stastny > Assignee: Steven Hawkins > Priority: Critical > > HANA translator blindly converts boolean values to tinyint when a boolean column is pushed as part of a convert function to datasource. > Example query: > {code:sql} > SELECT CONCAT2(stringNum,booleanvalue) FROM BQT1.SmallA; > {code} > and its DATA SRC COMMAND: > {code:sql} > SELECT CONCAT2(g_0.STRINGNUM, convert(g_0.BOOLEANVALUE, string)) FROM BQT1.SMALLA AS g_0 > {code} > and its SOURCE SRC COMMAND: > {code:sql} > SELECT CASE WHEN g_0."STRINGNUM" IS NULL AND cast(g_0."BOOLEANVALUE" AS tinyint) IS NULL THEN NULL ELSE concat(ifnull(g_0."STRINGNUM", ''), ifnull(cast(g_0."BOOLEANVALUE" AS tinyint), '')) END FROM "BQT1"."SMALLA" AS g_0 > {code} -- This message was sent by Atlassian JIRA (v6.4.11#64026)

9 years, 10 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4221) HANA translator: incorrect pushdown of week function

by Ted Jones (JIRA)

[ https://issues.jboss.org/browse/TEIID-4221?page=com.atlassian.jira.plugin... ] Ted Jones reassigned TEIID-4221: -------------------------------- Assignee: Steven Hawkins (was: Ted Jones) > HANA translator: incorrect pushdown of week function > ---------------------------------------------------- > > Key: TEIID-4221 > URL: https://issues.jboss.org/browse/TEIID-4221 > Project: Teiid > Issue Type: Bug > Components: Misc. Connectors > Affects Versions: 8.12.5 > Reporter: Jan Stastny > Assignee: Steven Hawkins > > Hana translator pushes down function week to SAP HANA datasource as its WEEK function. But it is not in accordance with the ISO8601 rule. > For this standard, HANA has function ISOWEEK, which returns results in following format: > {code:plain} > 2000-W01 for date 'Jan 3, 2000' > {code} > So it should be possible to push Teiid's WEEK function as: > {code:sql} > SUBSTR_REGEXPR('[[:digit:]]{4}\-W([[:digit:]]{2})' IN ISOWEEK(datevalue) GROUP 1) > {code} > where 'datevalue' is column of DATE datatype. -- This message was sent by Atlassian JIRA (v6.4.11#64026)

9 years, 10 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4223) Hana translator: incorrect pushdown of QUARTER function

by Ted Jones (JIRA)

[ https://issues.jboss.org/browse/TEIID-4223?page=com.atlassian.jira.plugin... ] Ted Jones reassigned TEIID-4223: -------------------------------- Assignee: Steven Hawkins (was: Ted Jones) > Hana translator: incorrect pushdown of QUARTER function > ------------------------------------------------------- > > Key: TEIID-4223 > URL: https://issues.jboss.org/browse/TEIID-4223 > Project: Teiid > Issue Type: Bug > Components: Misc. Connectors > Affects Versions: 8.12.5 > Reporter: Jan Stastny > Assignee: Steven Hawkins > > Teiid's QUARTER function returns integer. > SAP HANA's QUARTER function returns string in form: > {code:plain} > 2000-Q1 > {code} > So the function either shouldn't be pushed down, or it has to be pushed down together with some substring method wrapping it: > {code:sql} > SUBSTR_REGEXPR('[[:digit:]]{4}\-Q([[:digit:]]{1})' IN quarter(datevalue) GROUP 1) > {code} -- This message was sent by Atlassian JIRA (v6.4.11#64026)

9 years, 10 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4224) HANA translator : incorrect pushdown of DAYOFWEEK function

by Ted Jones (JIRA)

[ https://issues.jboss.org/browse/TEIID-4224?page=com.atlassian.jira.plugin... ] Ted Jones reassigned TEIID-4224: -------------------------------- Assignee: Steven Hawkins (was: Ted Jones) > HANA translator : incorrect pushdown of DAYOFWEEK function > ---------------------------------------------------------- > > Key: TEIID-4224 > URL: https://issues.jboss.org/browse/TEIID-4224 > Project: Teiid > Issue Type: Bug > Components: Misc. Connectors > Affects Versions: 8.12.5 > Reporter: Jan Stastny > Assignee: Steven Hawkins > > Teiid's DAYOFWEEK function: > {code:plain} > DAYOFWEEK(x) Return day of week (Sunday=1, Saturday=7) > {code} > is pushed down as: > {code:plain} > dayname(g_0."DATEVALUE") > {code} > but this function returns string representation of the day. > More appropriate would be to push DAYOFWEEK as WEEKDAY function, but the returned value has to be adapted to Teiid's representation (Sunday=1 and Saturday=7, vs. HANA's Monday=0,Sunday=6). So the solution would be to push: > {code:sql} > MOD((WEEKDAY(datevalue)+1),7)+1 > {code} -- This message was sent by Atlassian JIRA (v6.4.11#64026)

9 years, 10 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4226) HANA translator: boolean type converted to tinyint in each convert

by Ted Jones (JIRA)

[ https://issues.jboss.org/browse/TEIID-4226?page=com.atlassian.jira.plugin... ] Ted Jones reassigned TEIID-4226: -------------------------------- Assignee: Ted Jones (was: Steven Hawkins) > HANA translator: boolean type converted to tinyint in each convert > ------------------------------------------------------------------ > > Key: TEIID-4226 > URL: https://issues.jboss.org/browse/TEIID-4226 > Project: Teiid > Issue Type: Bug > Components: Misc. Connectors > Affects Versions: 8.12.5 > Reporter: Jan Stastny > Assignee: Ted Jones > Priority: Critical > > HANA translator blindly converts boolean values to tinyint when a boolean column is pushed as part of a convert function to datasource. > Example query: > {code:sql} > SELECT CONCAT2(stringNum,booleanvalue) FROM BQT1.SmallA; > {code} > and its DATA SRC COMMAND: > {code:sql} > SELECT CONCAT2(g_0.STRINGNUM, convert(g_0.BOOLEANVALUE, string)) FROM BQT1.SMALLA AS g_0 > {code} > and its SOURCE SRC COMMAND: > {code:sql} > SELECT CASE WHEN g_0."STRINGNUM" IS NULL AND cast(g_0."BOOLEANVALUE" AS tinyint) IS NULL THEN NULL ELSE concat(ifnull(g_0."STRINGNUM", ''), ifnull(cast(g_0."BOOLEANVALUE" AS tinyint), '')) END FROM "BQT1"."SMALLA" AS g_0 > {code} -- This message was sent by Atlassian JIRA (v6.4.11#64026)

9 years, 10 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 May 2016