May 2016 - teiid-issues - Jboss List Archives

[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: Ted Jones (was: Steven Hawkins) > 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: Ted Jones > > 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)

8 years, 7 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: Ted Jones (was: Steven Hawkins) > 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: Ted Jones > > 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)

8 years, 7 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: -------------------------------------- Another bit of information - a query just asking for multiple count distincts works without any group by. As soon as group by is added, the query no longer works. Eg: This works: {noformat}SELECT count(distinct orderid) ,count(DISTINCT hash_tid) AS transactions ,sum(sales) FROM ImpressionClickTransactionProcessDateDetail WHERE process_date_key >= '2016-05-22' AND process_date_key < '2016-05-23' AND advertiser_key = 2149{noformat} This does not work: {noformat} SELECT process_date_key ,count(distinct orderid) ,count(DISTINCT hash_tid) AS transactions ,sum(sales) FROM ImpressionClickTransactionProcessDateDetail WHERE process_date_key >= '2016-05-22' AND process_date_key < '2016-05-23' AND advertiser_key = 2149 group by process_date_key {noformat} > 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)

8 years, 7 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 commented on TEIID-3743: --------------------------------------- This is a slightly different case than before in that a group by is present. In fact the current code won't attempt the rewrite with a group by or having clause. When there is a group by present the cross join solution will not work as multiple rows can be produced by each aggregation and then would have to be joined together based upon grouping columns: select col3, count(distinct col1), count(distinct col2) from t1 group by col3: would instead look like: select v1.col1, v1.c1 result1, v2.c1 result2 from (select col3, count(distinct col1) as c1 from t1 group by col3) v1 inner join (select col3, count(distinct col2) as c1 from t1 group by col3) v2 on v1.col3 = v2.col3 or (v1.col3 is null and v2.col3 is null); Which is even messier and has even worse performance implications. I would track this as a separate 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)

8 years, 7 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: -------------------------------------- [~shawkins] let me know if you need any other details. The issue is the physical query generated isn't compatible with Impala, which doesn't allow multiple count distincts in a single query block. So I think the solution was to use the cross join / subquery syntax recommended by Cloudera (shown in description). > 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)

8 years, 7 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: -------------------------------------- Error from Designer running a query with two count distincts: {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} > 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)

8 years, 7 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: -------------------------------------- We are using translator-hive-8.13.3.jar > 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)

8 years, 7 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 commented on TEIID-3743: --------------------------------------- You'll have to provide more details about what you are seeing. > 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)

8 years, 7 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 reopened TEIID-3743: ---------------------------------- > 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)

8 years, 7 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4225) HANA translator : DAYNAME function is not pushed

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-4225?page=com.atlassian.jira.plugin... ] Steven Hawkins updated TEIID-4225: ---------------------------------- Priority: Minor (was: Major) > HANA translator : DAYNAME function is not pushed > ------------------------------------------------ > > Key: TEIID-4225 > URL: https://issues.jboss.org/browse/TEIID-4225 > Project: Teiid > Issue Type: Bug > Components: Misc. Connectors > Affects Versions: 8.12.5 > Reporter: Jan Stastny > Assignee: Steven Hawkins > Priority: Minor > > Teiid's DAYNAME function is not being pushed to HANA even though it could be as HANA's DAYNAME function. The only difference could be that HANA returns names in capital letters. > Query: > {code:sql} > SELECT DAYNAME(datevalue) FROM bqt1.smalla > {code} > Source specific query: > {code:sql} > SELECT g_0."DATEVALUE" FROM "BQT1"."SMALLA" AS g_0 > {code} -- This message was sent by Atlassian JIRA (v6.4.11#64026)

8 years, 7 months

1
0
0 / 0

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

teiid-issues May 2016