[teiid-issues] [JBoss JIRA] (TEIID-5861) Salesforce translator should support nested joins

[Steven Hawkins (Jira)]

     [ https://issues.jboss.org/browse/TEIID-5861?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins updated TEIID-5861:
----------------------------------
    Fix Version/s: 13.1
     Story Points: 2
          Summary: Salesforce translator should support nested joins  (was: Need to properly generate SOQL for multiple nested joins)


An early simplifying assumption of the translator was to limit things to a single join level.  That made things easy in the logic to identify the parent and the child, and then make decisions about the SOQL query structure.  To support arbitrary joins we'll need to validate what join structures are supported (left linear vs. a full tree).  Multiple child-to-parent joins should be no problem - that appears to be the query type highlighted in this issue.  Multiple parent-to-child joins will imply some join result expansion that the translator would have to perform.  It may be necessary to add more capabilities to capture the necessary restrictions on what is supported.

> Salesforce translator should support nested joins
> -------------------------------------------------
>
>                 Key: TEIID-5861
>                 URL: https://issues.jboss.org/browse/TEIID-5861
>             Project: Teiid
>          Issue Type: Enhancement
>          Components: Salesforce Connector
>            Reporter: Renat Eskenin
>            Assignee: Steven Hawkins
>            Priority: Major
>             Fix For: 13.1
>
>
> For this SQL we have wrong generated SOQL
> {code}
> select
> 	price.Id,
> 	price.IsActive__c,
> 	price.name,
> 	price.PriceBook__c,
> 	book.DefaultCurrency__c,
> 	price.Price__c,
> 	price.ProductCode__c,
> 	price.ProductName__c,
> 	price.Product__c,
> 	product.Id,
> 	product.Name,
> 	product.Description
> from
> 	MSRP__c as price
> left outer join /*+ MAKEDEP */ Product2 as product on
> 	price.Product__c = product.id
> left outer join /*+ MAKEDEP */ Pricebook2 as book on
> 	price.PriceBook__c = book.id
> where
> 	price.PriceBook__c =:priceBookId
> 	and ProductCode__c in(
> 		:productCodes
> 	)
> 	and IsActive__c = true"
> {code}
> {code}
> START DATA SRC COMMAND:	startTime=2019-12-02 12:43:54.368	requestID=9MPiB/GOwB5f.0	sourceCommandID=3	executionID=2	txID=null	modelName=sf	translatorName=salesforce	sessionID=9MPiB/GOwB5f	principal=anonymous	sql=SELECT sf.Pricebook2.DefaultCurrency__c FROM sf.Pricebook2 WHERE sf.Pricebook2.Id = 'xxxxx'
> [2019-12-02 12:43:54,368 MSK] DEBUG [salesforce-failover-facade,,,] org.teiid.COMMAND_LOG.SOURCE                                 [Worker0_QueryProcessorQueue1]: 	SOURCE SRC COMMAND:	endTime=2019-12-02 12:43:54.368	requestID=9MPiB/GOwB5f.0	sourceCommandID=2	executionID=1	txID=null	modelName=sf	translatorName=salesforce	sessionID=9MPiB/GOwB5f	principal=anonymous	sourceCommand=[SELECT MSRP__c.Id, MSRP__c.IsActive__c, MSRP__c.Name, MSRP__c.PriceBook__c, MSRP__c.Price__c, MSRP__c.ProductCode__c, MSRP__c.ProductName__c, MSRP__c.Product__c, Product__r.Id, Product__r.Name, Product__r.Description FROM MSRP__c WHERE (MSRP__c.PriceBook__c = 'xxxx') AND ((MSRP__c.ProductCode__c IN(xxxxx)) AND (MSRP__c.IsActive__c = true))]
> [2019-12-02 12:43:54,368 MSK] DEBUG [salesforce-failover-facade,,,] org.teiid.CONNECTOR                                          [Worker1_QueryProcessorQueue2]: salesforce.9MPiB/GOwB5f.9MPiB/GOwB5f.0.3:  Incoming Query: SELECT Pricebook2.DefaultCurrency__c FROM Pricebook2 WHERE Pricebook2.Id = 'xxxx'
> [2019-12-02 12:43:54,368 MSK] DEBUG [salesforce-failover-facade,,,] org.teiid.COMMAND_LOG.SOURCE                                 [Worker1_QueryProcessorQueue2]: 	SOURCE SRC COMMAND:	endTime=2019-12-02 12:43:54.368	requestID=9MPiB/GOwB5f.0	sourceCommandID=3	executionID=2	txID=null	modelName=sf	translatorName=salesforce	sessionID=9MPiB/GOwB5f	principal=anonymous	sourceCommand=[Using retrieve:  Pricebook2.DefaultCurrency__c Pricebook2 [xxxx]]
> {code}
> But need to generate one. Because we have two relations in MSRP__c



--
This message was sent by Atlassian Jira
(v7.13.8#713008)