[JBoss JIRA] (TEIID-3988) Narrow when serial source access is used
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3988?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-3988.
-----------------------------------
Resolution: Done
This was effectively worked under TEIID-4076 which makes the check for isSerial also consider the source transaction support. In some cases we may want to advise sources to be marked as having local transaction support instead of XA if they can handle concurrent access - or we may need to introduce another transaction support constant for that.
> Narrow when serial source access is used
> ----------------------------------------
>
> Key: TEIID-3988
> URL: https://issues.jboss.org/browse/TEIID-3988
> Project: Teiid
> Issue Type: Quality Risk
> Components: Query Engine
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 9.0
>
>
> Both when we determine a transaction is needed and how the processing is performed under a transaction can be improved. We we likely need to reintroduce a property on the translator to tell the engine when the source is XA, local transaction, etc.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 10 months
[JBoss JIRA] (TEIID-4227) Impala Translator - Support Multiple Count Distinct with Group By
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4227?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-4227:
---------------------------------------
>From TEIID-3743 you'll also need to account for the nulls in the on clause - v1.c1 = v2.c1 or (v1.c1 is null and v2.c1 is null).
Given the complexity and performance implications of this scenario, is a viable solution to offer an option to enable the APPX_COUNT_DISTINCT mode so that ndv will be used instead?
> 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 = 12345 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 = 12345
> GROUP BY g_0.process_date_key
> ) v1 inner 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 = 12345
> 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
[JBoss JIRA] (TEIID-4227) Impala Translator - Support Multiple Count Distinct with Group By
by Scott Wallace (JIRA)
[ https://issues.jboss.org/browse/TEIID-4227?page=com.atlassian.jira.plugin... ]
Scott Wallace updated TEIID-4227:
---------------------------------
Description:
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 = 12345 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 = 12345
GROUP BY g_0.process_date_key
) v1 inner 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 = 12345
GROUP BY g_0.process_date_key
) v2 on v1.c1 = v2.c1;
{noformat}
FYI [~shawkins]
was:
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 inner 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]
> 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 = 12345 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 = 12345
> GROUP BY g_0.process_date_key
> ) v1 inner 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 = 12345
> 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
[JBoss JIRA] (TEIID-4227) Impala Translator - Support Multiple Count Distinct with Group By
by Scott Wallace (JIRA)
[ https://issues.jboss.org/browse/TEIID-4227?page=com.atlassian.jira.plugin... ]
Scott Wallace edited comment on TEIID-4227 at 5/25/16 4:29 PM:
---------------------------------------------------------------
The nvls are also probably not necessary and you could simplify by writing like this, to remove redundancy:
{noformat}
select v1.c1, v1.c2, v1.c3, v1.c4, v2.c5, 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 = 12345
GROUP BY g_0.process_date_key
) v1 inner join
(
SELECT g_0.process_date_key as c1,
COUNT(DISTINCT g_0.`hash_tid`) as c5
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 = 12345
GROUP BY g_0.process_date_key
) v2 on v1.c1 = v2.c1;
{noformat}
was (Author: walla2sl):
The nvls are also probably not necessary and you could simplify by writing like this, to remove redundancy:
{noformat}
select v1.c1, v1.c2, v1.c3, v1.c4, v2.c5, 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 inner join
(
SELECT g_0.process_date_key as c1,
COUNT(DISTINCT g_0.`hash_tid`) as c5
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}
> 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 = 12345 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 = 12345
> GROUP BY g_0.process_date_key
> ) v1 inner 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 = 12345
> 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
[JBoss JIRA] (TEIID-4227) Impala Translator - Support Multiple Count Distinct with Group By
by Scott Wallace (JIRA)
[ https://issues.jboss.org/browse/TEIID-4227?page=com.atlassian.jira.plugin... ]
Scott Wallace commented on TEIID-4227:
--------------------------------------
The nvls are also probably not necessary and you could simplify by writing like this, to remove redundancy:
{noformat}
select v1.c1, v1.c2, v1.c3, v1.c4, v2.c5, 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 inner join
(
SELECT g_0.process_date_key as c1,
COUNT(DISTINCT g_0.`hash_tid`) as c5
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}
> 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 inner 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
[JBoss JIRA] (TEIID-4227) Impala Translator - Support Multiple Count Distinct with Group By
by Scott Wallace (JIRA)
[ https://issues.jboss.org/browse/TEIID-4227?page=com.atlassian.jira.plugin... ]
Scott Wallace commented on TEIID-4227:
--------------------------------------
Nope, you're correct. In this case the tables in the subquery and where clause would be the same anyway, so we can safely inner join without losing any data.
> 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
[JBoss JIRA] (TEIID-4227) Impala Translator - Support Multiple Count Distinct with Group By
by Scott Wallace (JIRA)
[ https://issues.jboss.org/browse/TEIID-4227?page=com.atlassian.jira.plugin... ]
Scott Wallace updated TEIID-4227:
---------------------------------
Description:
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 inner 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]
was:
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]
> 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 inner 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
[JBoss JIRA] (TEIID-4228) ODBC "Parse Statements" option can result in changes in LENGTH metadata for columns
by Marc Shirley (JIRA)
Marc Shirley created TEIID-4228:
-----------------------------------
Summary: ODBC "Parse Statements" option can result in changes in LENGTH metadata for columns
Key: TEIID-4228
URL: https://issues.jboss.org/browse/TEIID-4228
Project: Teiid
Issue Type: Bug
Environment: Connections through ODBC driver
Reporter: Marc Shirley
Assignee: Steven Hawkins
Connections through ODBC driver with the driver setting "Parse Statements" option enabled can result in incorrect length values being passed to the client. In some cases (such as SQL Server linked server capabilities), this can result in the client throwing exceptions due to the expected LENGTH changing during the course of the query execution.
This may be limited to columns with precision set to 0, as the SQL Server linked server case was corrected when the precision was changed from 0 to 9 for the column specified in the error.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 10 months