Salesforce translator should support nested joins ------------------------------------------------- Key: TEIID-5861 URL: https://issues.redhat.com/browse/TEIID-5861 Project: Teiid Issue Type: Enhancement Components: Salesforce Connector Reporter: Renat Eskenin Assignee: Steven Hawkins Priority: Major Fix For: Backlog Original Estimate: 2 days Remaining Estimate: 2 days 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