Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-6069

Malformed SOQL for ORDER BY in salesforce connector

XMLWordPrintable

    • Hide

      Simple teiid-spring-boot project

      DDL

      CREATE DATABASE db;
USE DATABASE db;
CREATE FOREIGN DATA WRAPPER salesforce;
CREATE FOREIGN DATA WRAPPER mysalesforce type salesforce OPTIONS (supportsOrderBy true, maxFromGroups 30);
CREATE SERVER salesforce FOREIGN DATA WRAPPER mysalesforce OPTIONS ("resource-name" 'salesforce');
CREATE SCHEMA salesforce SERVER salesforce;
IMPORT FROM SERVER salesforce INTO salesforce OPTIONS("importer.useFullSchemaName" 'false', "importer.NormalizeNames" 'false', "importer.modelAuditFields" 'true', "importer.importStatstics" 'true');
      Show
      Simple teiid-spring-boot project DDL CREATE DATABASE db; USE DATABASE db; CREATE FOREIGN DATA WRAPPER salesforce; CREATE FOREIGN DATA WRAPPER mysalesforce type salesforce OPTIONS (supportsOrderBy true , maxFromGroups 30); CREATE SERVER salesforce FOREIGN DATA WRAPPER mysalesforce OPTIONS ( "resource-name" 'salesforce' ); CREATE SCHEMA salesforce SERVER salesforce; IMPORT FROM SERVER salesforce INTO salesforce OPTIONS( "importer.useFullSchemaName" ' false ' , "importer.NormalizeNames" ' false ' , "importer.modelAuditFields" ' true ' , "importer.importStatstics" ' true ' );
    • Undefined

      We have simple request

      SELECT
	AccountId,
	Account.Name
FROM Contact 
left OUTER JOIN /*+ MAKEDEP */
	Account ON
	Account.Id = Contact.AccountId 
WHERE Email = 'test@test.com' 
ORDER BY Contact.CreatedDate DESC NULLS LAST
LIMIT 1

      And for this sql generated plan by teiid is:

      PLAN:

ProjectNode
  + Relational Node ID:0
  + Output Columns:
    0: AccountId (string)
    1: Name (string)
  + Cost Estimates:Estimated Node Cardinality: 1.0
  + Child 0:
    LimitNode
      + Relational Node ID:1
      + Output Columns:
        0: AccountId (string)
        1: Name (string)
      + Cost Estimates:Estimated Node Cardinality: 1.0
      + Child 0:
        SortNode
          + Relational Node ID:2
          + Output Columns:
            0: AccountId (string)
            1: Name (string)
          + Cost Estimates:Estimated Node Cardinality: -1.0
          + Child 0:
            AccessNode
              + Relational Node ID:3
              + Output Columns:
                0: AccountId (string)
                1: Name (string)
                2: CreatedDate (timestamp)
              + Cost Estimates:Estimated Node Cardinality: -1.0
              + Query:SELECT g_0.AccountId, g_1.Name, g_0.CreatedDate FROM salesforce.Contact AS g_0 LEFT OUTER JOIN salesforce.Account AS g_1 ON g_1.Id = g_0.AccountId WHERE g_0.Email = 'test@test.com'
              + Model Name:salesforce
          + Sort Columns:[salesforce.Contact.CreatedDate DESC NULLS LAST]
          + Sort FrameMode:SORT
      + Row Offset:null
      + Row Limit:1
  + Select Columns:
    0: salesforce.Contact.AccountId
    1: salesforce.Account.Name
  + Data Bytes Sent:7
  + Planning Time:1
END PLAN

DEBUG LOG :

nullEND DEBUG LOG

      And we got exception:

      Caused by: com.sforce.soap.partner.fault.MalformedQueryFault: null
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
	at java.base/java.lang.Class.newInstance(Class.java:584)
	at com.sforce.ws.bind.TypeMapper.readSingle(TypeMapper.java:673)
	at com.sforce.ws.bind.TypeMapper.readObject(TypeMapper.java:556)
	at com.sforce.ws.transport.SoapConnection.parseDetail(SoapConnection.java:236)
	at com.sforce.ws.transport.SoapConnection.createException(SoapConnection.java:210)
	at com.sforce.ws.transport.SoapConnection.receive(SoapConnection.java:156)
	at com.sforce.ws.transport.SoapConnection.send(SoapConnection.java:99)
	at com.sforce.soap.partner.PartnerConnection.query(PartnerConnection.java:1197)
	at org.teiid.salesforce.BaseSalesforceConnection.query(BaseSalesforceConnection.java:220)

      But if we switch to supportsOrderBy false
      then plan generated well.

              rhn-engineering-shawkins Steven Hawkins
              3draven Renat Eskenin (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Created:
                Updated:
                Resolved: