[ https://issues.jboss.org/browse/TEIID-5856?page=com.atlassian.jira.plugin... ] Renat Eskenin edited comment on TEIID-5856 at 12/2/19 4:50 AM: --------------------------------------------------------------- Another example: {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} wrong plan {code} ProjectNode + Relational Node ID:0 + Output Columns: 0: Id (string) 1: IsActive__c (boolean) 2: Name (string) 3: PriceBook__c (string) 4: DefaultCurrency__c (string) 5: Price__c (double) 6: ProductCode__c (string) 7: ProductName__c (string) 8: Product__c (string) 9: Id (string) 10: Name (string) 11: Description (string) + Cost Estimates:Estimated Node Cardinality: -1.0 + Child 0: JoinNode + Relational Node ID:1 + Output Columns: 0: Id (string) 1: IsActive__c (boolean) 2: Name (string) 3: PriceBook__c (string) 4: DefaultCurrency__c (string) 5: Price__c (double) 6: ProductCode__c (string) 7: ProductName__c (string) 8: Product__c (string) 9: Id (string) 10: Name (string) 11: Description (string) + Cost Estimates:Estimated Node Cardinality: -1.0 + Child 0: AccessNode + Relational Node ID:2 + Output Columns: 0: Id (string) 1: IsActive__c (boolean) 2: Name (string) 3: PriceBook__c (string) 4: Price__c (double) 5: ProductCode__c (string) 6: ProductName__c (string) 7: Product__c (string) 8: Id (string) 9: Name (string) 10: Description (string) + Cost Estimates:Estimated Node Cardinality: -1.0 + Query:SELECT sf.MSRP__c.Id, sf.MSRP__c.IsActive__c, sf.MSRP__c.Name, sf.MSRP__c.PriceBook__c, sf.MSRP__c.Price__c, sf.MSRP__c.ProductCode__c, sf.MSRP__c.ProductName__c, sf.MSRP__c.Product__c, sf.Product2.Id, sf.Product2.Name, sf.Product2.Description FROM sf.MSRP__c LEFT OUTER JOIN sf.Product2 ON sf.MSRP__c.Product__c = sf.Product2.Id WHERE (sf.MSRP__c.PriceBook__c = ?) AND (sf.MSRP__c.ProductCode__c IN (?, ?, ?, ?)) AND (sf.MSRP__c.IsActive__c = TRUE) + Model Name:sf + Child 1: AccessNode + Relational Node ID:3 + Output Columns:DefaultCurrency__c (string) + Cost Estimates:Estimated Node Cardinality: 1.0 + Query:SELECT sf.Pricebook2.DefaultCurrency__c FROM sf.Pricebook2 WHERE sf.Pricebook2.Id = ? + Model Name:sf + Join Strategy:NESTED LOOP JOIN + Join Type:LEFT OUTER JOIN + Join Criteria + Select Columns: 0: price.Id 1: price.IsActive__c 2: price.Name 3: price.PriceBook__c 4: book.DefaultCurrency__c 5: price.Price__c 6: price.ProductCode__c 7: price.ProductName__c 8: price.Product__c 9: product.Id 10: product.Name 11: product.Description + Data Bytes Sent:7 + Planning Time:30 {code} It is one SOQL request but in plan we have two. {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} was (Author: i3draven): Another example: {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} wrong plan {code} ProjectNode + Relational Node ID:0 + Output Columns: 0: Id (string) 1: IsActive__c (boolean) 2: Name (string) 3: PriceBook__c (string) 4: DefaultCurrency__c (string) 5: Price__c (double) 6: ProductCode__c (string) 7: ProductName__c (string) 8: Product__c (string) 9: Id (string) 10: Name (string) 11: Description (string) + Cost Estimates:Estimated Node Cardinality: -1.0 + Child 0: JoinNode + Relational Node ID:1 + Output Columns: 0: Id (string) 1: IsActive__c (boolean) 2: Name (string) 3: PriceBook__c (string) 4: DefaultCurrency__c (string) 5: Price__c (double) 6: ProductCode__c (string) 7: ProductName__c (string) 8: Product__c (string) 9: Id (string) 10: Name (string) 11: Description (string) + Cost Estimates:Estimated Node Cardinality: -1.0 + Child 0: AccessNode + Relational Node ID:2 + Output Columns: 0: Id (string) 1: IsActive__c (boolean) 2: Name (string) 3: PriceBook__c (string) 4: Price__c (double) 5: ProductCode__c (string) 6: ProductName__c (string) 7: Product__c (string) 8: Id (string) 9: Name (string) 10: Description (string) + Cost Estimates:Estimated Node Cardinality: -1.0 + Query:SELECT sf.MSRP__c.Id, sf.MSRP__c.IsActive__c, sf.MSRP__c.Name, sf.MSRP__c.PriceBook__c, sf.MSRP__c.Price__c, sf.MSRP__c.ProductCode__c, sf.MSRP__c.ProductName__c, sf.MSRP__c.Product__c, sf.Product2.Id, sf.Product2.Name, sf.Product2.Description FROM sf.MSRP__c LEFT OUTER JOIN sf.Product2 ON sf.MSRP__c.Product__c = sf.Product2.Id WHERE (sf.MSRP__c.PriceBook__c = ?) AND (sf.MSRP__c.ProductCode__c IN (?, ?, ?, ?)) AND (sf.MSRP__c.IsActive__c = TRUE) + Model Name:sf + Child 1: AccessNode + Relational Node ID:3 + Output Columns:DefaultCurrency__c (string) + Cost Estimates:Estimated Node Cardinality: 1.0 + Query:SELECT sf.Pricebook2.DefaultCurrency__c FROM sf.Pricebook2 WHERE sf.Pricebook2.Id = ? + Model Name:sf + Join Strategy:NESTED LOOP JOIN + Join Type:LEFT OUTER JOIN + Join Criteria + Select Columns: 0: price.Id 1: price.IsActive__c 2: price.Name 3: price.PriceBook__c 4: book.DefaultCurrency__c 5: price.Price__c 6: price.ProductCode__c 7: price.ProductName__c 8: price.Product__c 9: product.Id 10: product.Name 11: product.Description + Data Bytes Sent:7 + Planning Time:30 {code} It is one SOQL request but in plan we have two. {code} [2019-12-02 12:43:54,972 MSK] DEBUG [salesforce-failover-facade,2651a98663650d10,ceeb8a13437abefd,false] org.teiid.CONNECTOR [task-4]: 9MPiB/GOwB5f.0.2.1 Processing Close : SELECT sf.MSRP__c.Id, sf.MSRP__c.IsActive__c, sf.MSRP__c.Name, sf.MSRP__c.PriceBook__c, sf.MSRP__c.Price__c, sf.MSRP__c.ProductCode__c, sf.MSRP__c.ProductName__c, sf.MSRP__c.Product__c, sf.Product2.Id, sf.Product2.Name, sf.Product2.Description FROM sf.MSRP__c LEFT OUTER JOIN sf.Product2 ON sf.MSRP__c.Product__c = sf.Product2.Id WHERE (sf.MSRP__c.PriceBook__c = 'xxx') AND (sf.MSRP__c.ProductCode__c IN (xxx)) AND (sf.MSRP__c.IsActive__c = TRUE) {code} and secod request {code} [2019-12-02 12:43:54,993 MSK] DEBUG [salesforce-failover-facade,2651a98663650d10,ceeb8a13437abefd,false] org.teiid.CONNECTOR [task-4]: 9MPiB/GOwB5f.0.3.2 Processing Close : SELECT sf.Pricebook2.DefaultCurrency__c FROM sf.Pricebook2 WHERE sf.Pricebook2.Id = 'xxxx' {code} -- This message was sent by Atlassian Jira (v7.13.8#713008)