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: