[JBoss JIRA] (TEIID-3636) SalesForce.com aggregate function query issues
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3636?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-3636:
---------------------------------------
I don't think we have a great workaround for this. The best we could do is that with an updated api version an offset clause is supported, which would mean that we could paginate at the translator level. But support for new api versions is effectively dependent upon TEIID-3492/TEIID-3587 so this likely won't get resolved until 8.12 at the soonest, and then it could require the manual step of generating salesforce connectivity for a later api version.
> SalesForce.com aggregate function query issues
> ----------------------------------------------
>
> Key: TEIID-3636
> URL: https://issues.jboss.org/browse/TEIID-3636
> Project: Teiid
> Issue Type: Quality Risk
> Components: Query Engine
> Affects Versions: 8.7.1
> Environment: RHEL 6, AWS VM, JBoss EAP (standalone) 6.3.2.GA
> JBoss Data Virtualization 6.1.0.ER4
> Reporter: Jorge Herrera
> Assignee: Steven Hawkins
> Priority: Minor
>
> When executing a query against salesforce.com with an aggregate function (select max(), min(), sum() ) With a 'group by' clause with a 'SalesForce ID' returns with the following error:
> TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 SalesForce_Sales: com.sforce.soap.partner.UnexpectedErrorFault: EXCEEDED_ID_LIMIT: Aggregate query does not support queryMore(), use LIMIT to restrict the results to a single batch [SQL State=50000, DB Errorcode=30504]
> Example Queries That FAIL:
> ----------
> SELECT accountid, sum(amount)
> FROM salesforce_sales.salesforce.opportunity where closedate > '2016-02-01'
> group by accountid;
> ----------
> SELECT aid, sum(amt) from (
> SELECT accountid aid, amount amt FROM salesforce_sales.salesforce.opportunity where closedate > '2016-02-01'
> ) as tmp group by aid;
> Queries that DO Work:
> SELECT closedate, sum(amount)
> FROM salesforce_sales.salesforce.opportunity where closedate > '2016-02-01' group by closedate
> ;
> SELECT isdeleted, sum(amount)
> FROM salesforce_sales.salesforce.opportunity where closedate > '2016-02-01' group by isdeleted
> ;
> SELECT campaignid, sum(amount)
> FROM salesforce_sales.salesforce.opportunity where closedate > '2016-02-01' group by campaignid
> ;
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
10 years, 7 months
[JBoss JIRA] (TEIID-3639) pg metadata query not qualified
by Steven Hawkins (JIRA)
Steven Hawkins created TEIID-3639:
-------------------------------------
Summary: pg metadata query not qualified
Key: TEIID-3639
URL: https://issues.jboss.org/browse/TEIID-3639
Project: Teiid
Issue Type: Quality Risk
Components: Query Engine
Affects Versions: 8.0
Reporter: Steven Hawkins
Assignee: Steven Hawkins
Fix For: 8.12
matpg_relatt is not qualified in the internal metadata query used by odbc clients.
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
10 years, 7 months
[JBoss JIRA] (TEIID-3636) SalesForce.com aggregate function query issues
by Jorge Herrera (JIRA)
[ https://issues.jboss.org/browse/TEIID-3636?page=com.atlassian.jira.plugin... ]
Jorge Herrera commented on TEIID-3636:
--------------------------------------
Just wanted to note that that query is a very common use case for us and it would limit our capability for live reporting, that is why i had marked it as I had. But I do understand your reasoning. Thanks.
> SalesForce.com aggregate function query issues
> ----------------------------------------------
>
> Key: TEIID-3636
> URL: https://issues.jboss.org/browse/TEIID-3636
> Project: Teiid
> Issue Type: Quality Risk
> Components: Query Engine
> Affects Versions: 8.7.1
> Environment: RHEL 6, AWS VM, JBoss EAP (standalone) 6.3.2.GA
> JBoss Data Virtualization 6.1.0.ER4
> Reporter: Jorge Herrera
> Assignee: Steven Hawkins
> Priority: Minor
>
> When executing a query against salesforce.com with an aggregate function (select max(), min(), sum() ) With a 'group by' clause with a 'SalesForce ID' returns with the following error:
> TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 SalesForce_Sales: com.sforce.soap.partner.UnexpectedErrorFault: EXCEEDED_ID_LIMIT: Aggregate query does not support queryMore(), use LIMIT to restrict the results to a single batch [SQL State=50000, DB Errorcode=30504]
> Example Queries That FAIL:
> ----------
> SELECT accountid, sum(amount)
> FROM salesforce_sales.salesforce.opportunity where closedate > '2016-02-01'
> group by accountid;
> ----------
> SELECT aid, sum(amt) from (
> SELECT accountid aid, amount amt FROM salesforce_sales.salesforce.opportunity where closedate > '2016-02-01'
> ) as tmp group by aid;
> Queries that DO Work:
> SELECT closedate, sum(amount)
> FROM salesforce_sales.salesforce.opportunity where closedate > '2016-02-01' group by closedate
> ;
> SELECT isdeleted, sum(amount)
> FROM salesforce_sales.salesforce.opportunity where closedate > '2016-02-01' group by isdeleted
> ;
> SELECT campaignid, sum(amount)
> FROM salesforce_sales.salesforce.opportunity where closedate > '2016-02-01' group by campaignid
> ;
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
10 years, 7 months
[JBoss JIRA] (TEIID-3627) Infinispan-dsl-cache translator: comparison operators(GE, LE) problem with string
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3627?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-3627:
---------------------------------------
For 8.12 / 8.11.x do we need to make this a configurable capability under the assumption that a JDG will be available at some point that does support ordered comparison?
> Infinispan-dsl-cache translator: comparison operators(GE,LE) problem with string
> --------------------------------------------------------------------------------
>
> Key: TEIID-3627
> URL: https://issues.jboss.org/browse/TEIID-3627
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.7.1.6_2
> Reporter: Jan Stastny
> Assignee: Van Halbert
> Fix For: 8.7.1.6_2, 8.12, 8.11.3
>
>
> Comparison of string values provides wrong results for GE and LE operators. I provide example queries, notice the number of rows returned by the queries.
> For query: {code:sql}SELECT BQT1.SmallA.StringNum FROM BQT1.SmallA WHERE BQT1.SmallA.StringNum <= -22 ORDER BY StringNum{code}
> * Process Tree:
> {code:plain}
> LimitNode(0) output=[g_0.stringNum] limit 100
> AccessNode(1) output=[g_0.stringNum] SELECT g_0.stringNum FROM SmallAs.smallARemotecache AS g_0 WHERE g_0.stringNum <= '-22' ORDER BY g_0.stringNum
> {code}
> * SRC CMD: {code:sql}SELECT g_0.stringNum FROM SmallAs.smallARemotecache AS g_0 WHERE g_0.stringNum <= '-22' ORDER BY g_0.stringNum{code}
> * result 0 rows
> But for query: {code:sql}SELECT BQT1.SmallA.StringNum FROM BQT1.SmallA WHERE BQT1.SmallA.StringNum < -22 ORDER BY StringNum{code}
> * Process Tree:
> {code:plain}ProjectNode(0) output=[c.stringNum AS StringNum] [c.stringNum AS StringNum]
> LimitNode(1) output=[c.stringNum] limit 100
> SortNode(2) output=[c.stringNum] [SORT] [c.stringNum]
> SelectNode(3) output=[c.stringNum] c.stringNum < '-22'
> AccessNode(4) output=[c.stringNum] SELECT g_0.stringNum FROM SmallAs.smallARemotecache AS g_0{code}
> * SRC CMD: {code:sql}SELECT g_0.stringNum FROM SmallAs.smallARemotecache AS g_0{code}
> * result 14 rows
> And query: {code:sql}SELECT BQT1.SmallA.StringNum FROM BQT1.SmallA WHERE BQT1.SmallA.StringNum = -22 ORDER BY StringNum{code}
> * Process Tree:
> {code:plain}LimitNode(0) output=[c.stringNum AS StringNum] limit 100
> AccessNode(1) output=[c.stringNum AS StringNum] SELECT g_0.stringNum FROM SmallAs.smallARemotecache AS g_0 WHERE g_0.stringNum = '-22' ORDER BY g_0.stringNum{code}
> * SRC CMD: {code:sql}SELECT g_0.stringNum FROM SmallAs.smallARemotecache AS g_0 WHERE g_0.stringNum = '-22' ORDER BY g_0.stringNum{code}
> * result 1 row
> The first query should then return 15 rows instead of 0. Also the queries differ in a way they are processed, the first one is pushed down to infinispan, the other two are processed by teiid, which is probably a regression originally tracked here: TEIID-3424
> The same cause introduces problems with similar queries:
> {code:sql}Select IntKey, StringKey From BQT1.SmallA WHERE NOT(StringKey > 10 AND IntKey < 47) ORDER BY IntKey{code}
> Which is processed as:
> * Process Tree:
> {code:plain}LimitNode(0) output=[c.intKey AS IntKey, c.stringKey AS StringKey] limit 100
> AccessNode(1) output=[c.intKey AS IntKey, c.stringKey AS StringKey] SELECT g_0.intKey, g_0.stringKey FROM SmallAs.smallARemotecache AS g_0 WHERE (g_0.stringKey <= '10') OR (g_0.intKey >= 47) ORDER BY g_0.intKey{code}
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
10 years, 7 months
[JBoss JIRA] (TEIID-3627) Infinispan-dsl-cache translator: comparison operators(GE, LE) problem with string
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3627?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-3627:
----------------------------------
Fix Version/s: 8.11.3
(was: 8.11.2)
> Infinispan-dsl-cache translator: comparison operators(GE,LE) problem with string
> --------------------------------------------------------------------------------
>
> Key: TEIID-3627
> URL: https://issues.jboss.org/browse/TEIID-3627
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.7.1.6_2
> Reporter: Jan Stastny
> Assignee: Van Halbert
> Fix For: 8.7.1.6_2, 8.12, 8.11.3
>
>
> Comparison of string values provides wrong results for GE and LE operators. I provide example queries, notice the number of rows returned by the queries.
> For query: {code:sql}SELECT BQT1.SmallA.StringNum FROM BQT1.SmallA WHERE BQT1.SmallA.StringNum <= -22 ORDER BY StringNum{code}
> * Process Tree:
> {code:plain}
> LimitNode(0) output=[g_0.stringNum] limit 100
> AccessNode(1) output=[g_0.stringNum] SELECT g_0.stringNum FROM SmallAs.smallARemotecache AS g_0 WHERE g_0.stringNum <= '-22' ORDER BY g_0.stringNum
> {code}
> * SRC CMD: {code:sql}SELECT g_0.stringNum FROM SmallAs.smallARemotecache AS g_0 WHERE g_0.stringNum <= '-22' ORDER BY g_0.stringNum{code}
> * result 0 rows
> But for query: {code:sql}SELECT BQT1.SmallA.StringNum FROM BQT1.SmallA WHERE BQT1.SmallA.StringNum < -22 ORDER BY StringNum{code}
> * Process Tree:
> {code:plain}ProjectNode(0) output=[c.stringNum AS StringNum] [c.stringNum AS StringNum]
> LimitNode(1) output=[c.stringNum] limit 100
> SortNode(2) output=[c.stringNum] [SORT] [c.stringNum]
> SelectNode(3) output=[c.stringNum] c.stringNum < '-22'
> AccessNode(4) output=[c.stringNum] SELECT g_0.stringNum FROM SmallAs.smallARemotecache AS g_0{code}
> * SRC CMD: {code:sql}SELECT g_0.stringNum FROM SmallAs.smallARemotecache AS g_0{code}
> * result 14 rows
> And query: {code:sql}SELECT BQT1.SmallA.StringNum FROM BQT1.SmallA WHERE BQT1.SmallA.StringNum = -22 ORDER BY StringNum{code}
> * Process Tree:
> {code:plain}LimitNode(0) output=[c.stringNum AS StringNum] limit 100
> AccessNode(1) output=[c.stringNum AS StringNum] SELECT g_0.stringNum FROM SmallAs.smallARemotecache AS g_0 WHERE g_0.stringNum = '-22' ORDER BY g_0.stringNum{code}
> * SRC CMD: {code:sql}SELECT g_0.stringNum FROM SmallAs.smallARemotecache AS g_0 WHERE g_0.stringNum = '-22' ORDER BY g_0.stringNum{code}
> * result 1 row
> The first query should then return 15 rows instead of 0. Also the queries differ in a way they are processed, the first one is pushed down to infinispan, the other two are processed by teiid, which is probably a regression originally tracked here: TEIID-3424
> The same cause introduces problems with similar queries:
> {code:sql}Select IntKey, StringKey From BQT1.SmallA WHERE NOT(StringKey > 10 AND IntKey < 47) ORDER BY IntKey{code}
> Which is processed as:
> * Process Tree:
> {code:plain}LimitNode(0) output=[c.intKey AS IntKey, c.stringKey AS StringKey] limit 100
> AccessNode(1) output=[c.intKey AS IntKey, c.stringKey AS StringKey] SELECT g_0.intKey, g_0.stringKey FROM SmallAs.smallARemotecache AS g_0 WHERE (g_0.stringKey <= '10') OR (g_0.intKey >= 47) ORDER BY g_0.intKey{code}
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
10 years, 7 months
[JBoss JIRA] (TEIID-3627) Infinispan-dsl-cache translator: comparison operators(GE, LE) problem with string
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3627?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-3627:
----------------------------------
Fix Version/s: 8.12
> Infinispan-dsl-cache translator: comparison operators(GE,LE) problem with string
> --------------------------------------------------------------------------------
>
> Key: TEIID-3627
> URL: https://issues.jboss.org/browse/TEIID-3627
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.7.1.6_2
> Reporter: Jan Stastny
> Assignee: Van Halbert
> Fix For: 8.7.1.6_2, 8.12, 8.11.3
>
>
> Comparison of string values provides wrong results for GE and LE operators. I provide example queries, notice the number of rows returned by the queries.
> For query: {code:sql}SELECT BQT1.SmallA.StringNum FROM BQT1.SmallA WHERE BQT1.SmallA.StringNum <= -22 ORDER BY StringNum{code}
> * Process Tree:
> {code:plain}
> LimitNode(0) output=[g_0.stringNum] limit 100
> AccessNode(1) output=[g_0.stringNum] SELECT g_0.stringNum FROM SmallAs.smallARemotecache AS g_0 WHERE g_0.stringNum <= '-22' ORDER BY g_0.stringNum
> {code}
> * SRC CMD: {code:sql}SELECT g_0.stringNum FROM SmallAs.smallARemotecache AS g_0 WHERE g_0.stringNum <= '-22' ORDER BY g_0.stringNum{code}
> * result 0 rows
> But for query: {code:sql}SELECT BQT1.SmallA.StringNum FROM BQT1.SmallA WHERE BQT1.SmallA.StringNum < -22 ORDER BY StringNum{code}
> * Process Tree:
> {code:plain}ProjectNode(0) output=[c.stringNum AS StringNum] [c.stringNum AS StringNum]
> LimitNode(1) output=[c.stringNum] limit 100
> SortNode(2) output=[c.stringNum] [SORT] [c.stringNum]
> SelectNode(3) output=[c.stringNum] c.stringNum < '-22'
> AccessNode(4) output=[c.stringNum] SELECT g_0.stringNum FROM SmallAs.smallARemotecache AS g_0{code}
> * SRC CMD: {code:sql}SELECT g_0.stringNum FROM SmallAs.smallARemotecache AS g_0{code}
> * result 14 rows
> And query: {code:sql}SELECT BQT1.SmallA.StringNum FROM BQT1.SmallA WHERE BQT1.SmallA.StringNum = -22 ORDER BY StringNum{code}
> * Process Tree:
> {code:plain}LimitNode(0) output=[c.stringNum AS StringNum] limit 100
> AccessNode(1) output=[c.stringNum AS StringNum] SELECT g_0.stringNum FROM SmallAs.smallARemotecache AS g_0 WHERE g_0.stringNum = '-22' ORDER BY g_0.stringNum{code}
> * SRC CMD: {code:sql}SELECT g_0.stringNum FROM SmallAs.smallARemotecache AS g_0 WHERE g_0.stringNum = '-22' ORDER BY g_0.stringNum{code}
> * result 1 row
> The first query should then return 15 rows instead of 0. Also the queries differ in a way they are processed, the first one is pushed down to infinispan, the other two are processed by teiid, which is probably a regression originally tracked here: TEIID-3424
> The same cause introduces problems with similar queries:
> {code:sql}Select IntKey, StringKey From BQT1.SmallA WHERE NOT(StringKey > 10 AND IntKey < 47) ORDER BY IntKey{code}
> Which is processed as:
> * Process Tree:
> {code:plain}LimitNode(0) output=[c.intKey AS IntKey, c.stringKey AS StringKey] limit 100
> AccessNode(1) output=[c.intKey AS IntKey, c.stringKey AS StringKey] SELECT g_0.intKey, g_0.stringKey FROM SmallAs.smallARemotecache AS g_0 WHERE (g_0.stringKey <= '10') OR (g_0.intKey >= 47) ORDER BY g_0.intKey{code}
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
10 years, 7 months
[JBoss JIRA] (TEIID-3625) JDG translator has disabled capabilities for GT and LT
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3625?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-3625:
---------------------------------------
Actually the commit wasn't needed as it default to the ordered comparison support. This will be removed with TEIID-3627
> JDG translator has disabled capabilities for GT and LT
> ------------------------------------------------------
>
> Key: TEIID-3625
> URL: https://issues.jboss.org/browse/TEIID-3625
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.7.1
> Reporter: Filip Elias
> Assignee: Steven Hawkins
> Fix For: 8.12
>
>
> Operations GT and LT are not pushed down to JDG. Indexes are enabled for all columns. Operations GE and LE are pushed down correctly.
> Example:
> The first query is pushed down correctly. The second query doesn't push the "greater than" operator to the source.
> Query:
> {code}select intkey from smalla where intNum >= 5 order by intkey{code}
> PROCESSOR PLAN:
> {code}
> AccessNode(0) output=[c.intKey AS IntKey] SELECT g_0.intKey FROM SmallAs.smallARemotecache AS g_0 WHERE g_0.intNum >= 5 ORDER BY g_0.intKey
> {code}
> Query:
> {code}select intkey from smalla where intNum > 5 order by intkey{code}
> Plan:
> {code}
> SortNode(0) output=[c.intKey AS IntKey] [SORT] [IntKey]
> ProjectNode(1) output=[c.intKey AS IntKey] [c.intKey AS IntKey]
> SelectNode(2) output=[c.intKey] c.intNum > 5
> AccessNode(3) output=[c.intNum, c.intKey] SELECT g_0.intNum, g_0.intKey FROM SmallAs.smallARemotecache AS g_0
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
10 years, 7 months
[JBoss JIRA] (TEIID-3636) SalesForce.com aggregate function query issues
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3636?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-3636:
---------------------------------------
Updating to a minor quality risk as this is a fundamental limitation in salesforce.com, not our issue.
> SalesForce.com aggregate function query issues
> ----------------------------------------------
>
> Key: TEIID-3636
> URL: https://issues.jboss.org/browse/TEIID-3636
> Project: Teiid
> Issue Type: Quality Risk
> Components: Query Engine
> Affects Versions: 8.7.1
> Environment: RHEL 6, AWS VM, JBoss EAP (standalone) 6.3.2.GA
> JBoss Data Virtualization 6.1.0.ER4
> Reporter: Jorge Herrera
> Assignee: Steven Hawkins
> Priority: Minor
>
> When executing a query against salesforce.com with an aggregate function (select max(), min(), sum() ) With a 'group by' clause with a 'SalesForce ID' returns with the following error:
> TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 SalesForce_Sales: com.sforce.soap.partner.UnexpectedErrorFault: EXCEEDED_ID_LIMIT: Aggregate query does not support queryMore(), use LIMIT to restrict the results to a single batch [SQL State=50000, DB Errorcode=30504]
> Example Queries That FAIL:
> ----------
> SELECT accountid, sum(amount)
> FROM salesforce_sales.salesforce.opportunity where closedate > '2016-02-01'
> group by accountid;
> ----------
> SELECT aid, sum(amt) from (
> SELECT accountid aid, amount amt FROM salesforce_sales.salesforce.opportunity where closedate > '2016-02-01'
> ) as tmp group by aid;
> Queries that DO Work:
> SELECT closedate, sum(amount)
> FROM salesforce_sales.salesforce.opportunity where closedate > '2016-02-01' group by closedate
> ;
> SELECT isdeleted, sum(amount)
> FROM salesforce_sales.salesforce.opportunity where closedate > '2016-02-01' group by isdeleted
> ;
> SELECT campaignid, sum(amount)
> FROM salesforce_sales.salesforce.opportunity where closedate > '2016-02-01' group by campaignid
> ;
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
10 years, 7 months
[JBoss JIRA] (TEIID-3636) SalesForce.com aggregate function query issues
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3636?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-3636:
----------------------------------
Priority: Minor (was: Blocker)
> SalesForce.com aggregate function query issues
> ----------------------------------------------
>
> Key: TEIID-3636
> URL: https://issues.jboss.org/browse/TEIID-3636
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.7.1
> Environment: RHEL 6, AWS VM, JBoss EAP (standalone) 6.3.2.GA
> JBoss Data Virtualization 6.1.0.ER4
> Reporter: Jorge Herrera
> Assignee: Steven Hawkins
> Priority: Minor
>
> When executing a query against salesforce.com with an aggregate function (select max(), min(), sum() ) With a 'group by' clause with a 'SalesForce ID' returns with the following error:
> TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 SalesForce_Sales: com.sforce.soap.partner.UnexpectedErrorFault: EXCEEDED_ID_LIMIT: Aggregate query does not support queryMore(), use LIMIT to restrict the results to a single batch [SQL State=50000, DB Errorcode=30504]
> Example Queries That FAIL:
> ----------
> SELECT accountid, sum(amount)
> FROM salesforce_sales.salesforce.opportunity where closedate > '2016-02-01'
> group by accountid;
> ----------
> SELECT aid, sum(amt) from (
> SELECT accountid aid, amount amt FROM salesforce_sales.salesforce.opportunity where closedate > '2016-02-01'
> ) as tmp group by aid;
> Queries that DO Work:
> SELECT closedate, sum(amount)
> FROM salesforce_sales.salesforce.opportunity where closedate > '2016-02-01' group by closedate
> ;
> SELECT isdeleted, sum(amount)
> FROM salesforce_sales.salesforce.opportunity where closedate > '2016-02-01' group by isdeleted
> ;
> SELECT campaignid, sum(amount)
> FROM salesforce_sales.salesforce.opportunity where closedate > '2016-02-01' group by campaignid
> ;
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
10 years, 7 months
[JBoss JIRA] (TEIID-3636) SalesForce.com aggregate function query issues
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3636?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-3636:
----------------------------------
Issue Type: Quality Risk (was: Bug)
> SalesForce.com aggregate function query issues
> ----------------------------------------------
>
> Key: TEIID-3636
> URL: https://issues.jboss.org/browse/TEIID-3636
> Project: Teiid
> Issue Type: Quality Risk
> Components: Query Engine
> Affects Versions: 8.7.1
> Environment: RHEL 6, AWS VM, JBoss EAP (standalone) 6.3.2.GA
> JBoss Data Virtualization 6.1.0.ER4
> Reporter: Jorge Herrera
> Assignee: Steven Hawkins
> Priority: Minor
>
> When executing a query against salesforce.com with an aggregate function (select max(), min(), sum() ) With a 'group by' clause with a 'SalesForce ID' returns with the following error:
> TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 SalesForce_Sales: com.sforce.soap.partner.UnexpectedErrorFault: EXCEEDED_ID_LIMIT: Aggregate query does not support queryMore(), use LIMIT to restrict the results to a single batch [SQL State=50000, DB Errorcode=30504]
> Example Queries That FAIL:
> ----------
> SELECT accountid, sum(amount)
> FROM salesforce_sales.salesforce.opportunity where closedate > '2016-02-01'
> group by accountid;
> ----------
> SELECT aid, sum(amt) from (
> SELECT accountid aid, amount amt FROM salesforce_sales.salesforce.opportunity where closedate > '2016-02-01'
> ) as tmp group by aid;
> Queries that DO Work:
> SELECT closedate, sum(amount)
> FROM salesforce_sales.salesforce.opportunity where closedate > '2016-02-01' group by closedate
> ;
> SELECT isdeleted, sum(amount)
> FROM salesforce_sales.salesforce.opportunity where closedate > '2016-02-01' group by isdeleted
> ;
> SELECT campaignid, sum(amount)
> FROM salesforce_sales.salesforce.opportunity where closedate > '2016-02-01' group by campaignid
> ;
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
10 years, 7 months