[teiid-issues] [JBoss JIRA] (TEIID-3636) SalesForce.com aggregate function query issues

[Steven Hawkins (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-3636?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13111874#comment-13111874 ] 

Steven Hawkins commented on TEIID-3636:
---------------------------------------

Even the limit / offset solution is not ideal as the query performance could suffer if not much aggregation is being performed - as each fetch would be a separate query.  In many instances it would be just as fast to pull all of the data and perform the aggregation in Teiid.  The ideal solution would probably be to attempt the one shot query and then fall back on pulling all of the rows.  However this doesn't fit well with our planning structures.

> 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
>             Fix For: 8.12
>
>
> 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.4.11#64026)