Loading...

XML

Word

Printable

Details

Type: Bug
Resolution: Unresolved
Priority: Major
Fix Version/s: 16.0.1, 17.0
Affects Version/s: 16.0
Component/s: Salesforce Connector
Labels:
None

Steps to Reproduce:

Hide

1. Simple spring-boot teiid project
2. Source SQL
SELECT
Id
FROM Contact
LIMIT :limitParam OFFSET :offsetParam
3. Generated SOQL
SELECT g_0.Id FROM salesforce.Contact AS g_0 LIMIT (? + ?)

Show
1. Simple spring-boot teiid project 2. Source SQL SELECT Id FROM Contact LIMIT :limitParam OFFSET :offsetParam 3. Generated SOQL SELECT g_0.Id FROM salesforce.Contact AS g_0 LIMIT (? + ?)
Release Note Text:
Undefined

Description

example
If I would send request

SELECT
Id
FROM Contact
LIMIT 5 OFFSET 10

I would get response as 5 rows. It is right response.

But if I used named parameters in jdbc as example

SELECT
Id
FROM Contact
LIMIT :limitParam OFFSET :offsetParam

where limitParam =5 and offsetParam=10

Then I would get 10 rows in response and those response depends on offset only

Plan for request with jdbc named parameters

SELECT
Id
FROM Contact
LIMIT :limitParam OFFSET :offsetParam

PLAN:

LimitNode
  + Relational Node ID:0
  + Output Columns:Id (string)
  + Cost Estimates:Estimated Node Cardinality: -1.0
  + Child 0:
    AccessNode
      + Relational Node ID:1
      + Output Columns:Id (string)
      + Cost Estimates:Estimated Node Cardinality: -1.0
      + Query:SELECT g_0.Id FROM salesforce.Contact AS g_0 LIMIT (? + ?)
      + Model Name:salesforce
  + Row Offset:?
  + Row Limit:null
  + Data Bytes Sent:7
  + Planning Time:1
END PLAN

DEBUG LOG :


============================================================================
USER COMMAND:
SELECT salesforce.Contact.Id FROM salesforce.Contact LIMIT ?, ?

----------------------------------------------------------------------------
OPTIMIZE: 
SELECT salesforce.Contact.Id FROM salesforce.Contact LIMIT ?, ?

----------------------------------------------------------------------------
GENERATE CANONICAL: 
SELECT salesforce.Contact.Id FROM salesforce.Contact LIMIT ?, ?

CANONICAL PLAN: 
TupleLimit(groups=[], props={OFFSET_TUPLE_COUNT=?, MAX_TUPLE_LIMIT=?})
  Project(groups=[salesforce.Contact], props={PROJECT_COLS=[salesforce.Contact.Id]})
    Source(groups=[salesforce.Contact])


============================================================================
EXECUTING PlaceAccess

AFTER: 
TupleLimit(groups=[], props={OFFSET_TUPLE_COUNT=?, MAX_TUPLE_LIMIT=?})
  Project(groups=[salesforce.Contact], props={PROJECT_COLS=[salesforce.Contact.Id]})
    Access(groups=[salesforce.Contact], props={SOURCE_HINT=null, MODEL_ID=Schema name=salesforce, nameInSource=null, uuid=tid:6b7918e394e2-66cf095f-00000000})
      Source(groups=[salesforce.Contact])


============================================================================
EXECUTING RaiseAccess

AFTER: 
TupleLimit(groups=[], props={OFFSET_TUPLE_COUNT=?, MAX_TUPLE_LIMIT=null})
  Access(groups=[salesforce.Contact], props={SOURCE_HINT=null, MODEL_ID=Schema name=salesforce, nameInSource=null, uuid=tid:6b7918e394e2-66cf095f-00000000})
    TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=(? + ?), OUTPUT_COLS=null})
      Project(groups=[salesforce.Contact], props={PROJECT_COLS=[salesforce.Contact.Id]})
        Source(groups=[salesforce.Contact])


============================================================================
EXECUTING AssignOutputElements

AFTER: 
TupleLimit(groups=[], props={OFFSET_TUPLE_COUNT=?, MAX_TUPLE_LIMIT=null, OUTPUT_COLS=[salesforce.Contact.Id]})
  Access(groups=[salesforce.Contact], props={SOURCE_HINT=null, MODEL_ID=Schema name=salesforce, nameInSource=null, uuid=tid:6b7918e394e2-66cf095f-00000000, OUTPUT_COLS=[salesforce.Contact.Id]})
    TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=(? + ?), OUTPUT_COLS=[salesforce.Contact.Id]})
      Project(groups=[salesforce.Contact], props={PROJECT_COLS=[salesforce.Contact.Id], OUTPUT_COLS=[salesforce.Contact.Id]})
        Source(groups=[salesforce.Contact], props={OUTPUT_COLS=[salesforce.Contact.Id]})


============================================================================
EXECUTING PushLimit

AFTER: 
TupleLimit(groups=[], props={OFFSET_TUPLE_COUNT=?, MAX_TUPLE_LIMIT=null, OUTPUT_COLS=[salesforce.Contact.Id]})
  Access(groups=[salesforce.Contact], props={SOURCE_HINT=null, MODEL_ID=Schema name=salesforce, nameInSource=null, uuid=tid:6b7918e394e2-66cf095f-00000000, OUTPUT_COLS=[salesforce.Contact.Id]})
    TupleLimit(groups=[])
      Project(groups=[salesforce.Contact])
        Source(groups=[salesforce.Contact])


============================================================================
EXECUTING CalculateCost

AFTER: 
TupleLimit(groups=[], props={OFFSET_TUPLE_COUNT=?, MAX_TUPLE_LIMIT=null, OUTPUT_COLS=[salesforce.Contact.Id], EST_CARDINALITY=-1.0, EST_COL_STATS={salesforce.Contact.Id=[-1.0, -1.0, -1.0]}})
  Access(groups=[salesforce.Contact], props={SOURCE_HINT=null, MODEL_ID=Schema name=salesforce, nameInSource=null, uuid=tid:6b7918e394e2-66cf095f-00000000, OUTPUT_COLS=[salesforce.Contact.Id], EST_CARDINALITY=-1.0, EST_COL_STATS={salesforce.Contact.Id=[-1.0, -1.0, -1.0]}})
    TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=(? + ?), OUTPUT_COLS=[salesforce.Contact.Id], EST_CARDINALITY=-1.0, EST_COL_STATS={salesforce.Contact.Id=[-1.0, -1.0, -1.0]}})
      Project(groups=[salesforce.Contact], props={PROJECT_COLS=[salesforce.Contact.Id], OUTPUT_COLS=[salesforce.Contact.Id], EST_CARDINALITY=-1.0, EST_COL_STATS={salesforce.Contact.Id=[-1.0, -1.0, -1.0]}})
        Source(groups=[salesforce.Contact], props={OUTPUT_COLS=[salesforce.Contact.Id], EST_COL_STATS={salesforce.Contact.Id=[-1.0, -1.0, -1.0]}, EST_CARDINALITY=-1.0})


============================================================================
EXECUTING PlanSubqueries

AFTER: 
TupleLimit(groups=[])
  Access(groups=[salesforce.Contact])
    TupleLimit(groups=[])
      Project(groups=[salesforce.Contact])
        Source(groups=[salesforce.Contact])


============================================================================
EXECUTING PlanSorts

AFTER: 
TupleLimit(groups=[])
  Access(groups=[salesforce.Contact])
    TupleLimit(groups=[])
      Project(groups=[salesforce.Contact])
        Source(groups=[salesforce.Contact])


============================================================================
EXECUTING CollapseSource

AFTER: 
TupleLimit(groups=[])
  Access(groups=[salesforce.Contact], props={SOURCE_HINT=null, MODEL_ID=Schema name=salesforce, nameInSource=null, uuid=tid:6b7918e394e2-66cf095f-00000000, OUTPUT_COLS=[salesforce.Contact.Id], EST_CARDINALITY=-1.0, EST_COL_STATS={salesforce.Contact.Id=[-1.0, -1.0, -1.0]}, ATOMIC_REQUEST=SELECT salesforce.Contact.Id FROM salesforce.Contact LIMIT (? + ?)})


============================================================================
CONVERTING PLAN TREE TO PROCESS TREE

PROCESS PLAN = 
LimitNode(0) output=[salesforce.Contact.Id]  offset ?
  AccessNode(1) output=[salesforce.Contact.Id] SELECT g_0.Id FROM salesforce.Contact AS g_0 LIMIT (? + ?)

============================================================================

----------------------------------------------------------------------------
OPTIMIZATION COMPLETE:
PROCESSOR PLAN:
LimitNode(0) output=[salesforce.Contact.Id]  offset ?
  AccessNode(1) output=[salesforce.Contact.Id] SELECT g_0.Id FROM salesforce.Contact AS g_0 LIMIT (? + ?)

============================================================================
END DEBUG LOG

Plan for nested parameters

SELECT
Id
FROM Contact
LIMIT 5 OFFSET 10

PLAN:

LimitNode
  + Relational Node ID:0
  + Output Columns:Id (string)
  + Cost Estimates:Estimated Node Cardinality: 5.0
  + Child 0:
    AccessNode
      + Relational Node ID:1
      + Output Columns:Id (string)
      + Cost Estimates:Estimated Node Cardinality: 15.0
      + Query:SELECT g_0.Id FROM salesforce.Contact AS g_0 LIMIT 15
      + Model Name:salesforce
  + Row Offset:10
  + Row Limit:null
  + Data Bytes Sent:7
  + Planning Time:4
END PLAN

DEBUG LOG :


============================================================================
USER COMMAND:
SELECT salesforce.Contact.Id FROM salesforce.Contact LIMIT 10, 5

----------------------------------------------------------------------------
OPTIMIZE: 
SELECT salesforce.Contact.Id FROM salesforce.Contact LIMIT 10, 5

----------------------------------------------------------------------------
GENERATE CANONICAL: 
SELECT salesforce.Contact.Id FROM salesforce.Contact LIMIT 10, 5

CANONICAL PLAN: 
TupleLimit(groups=[], props={OFFSET_TUPLE_COUNT=10, MAX_TUPLE_LIMIT=5})
  Project(groups=[salesforce.Contact], props={PROJECT_COLS=[salesforce.Contact.Id]})
    Source(groups=[salesforce.Contact])


============================================================================
EXECUTING PlaceAccess

AFTER: 
TupleLimit(groups=[], props={OFFSET_TUPLE_COUNT=10, MAX_TUPLE_LIMIT=5})
  Project(groups=[salesforce.Contact], props={PROJECT_COLS=[salesforce.Contact.Id]})
    Access(groups=[salesforce.Contact], props={SOURCE_HINT=null, MODEL_ID=Schema name=salesforce, nameInSource=null, uuid=tid:6b7918e394e2-66cf095f-00000000})
      Source(groups=[salesforce.Contact])


============================================================================
EXECUTING RaiseAccess

AFTER: 
TupleLimit(groups=[], props={OFFSET_TUPLE_COUNT=10, MAX_TUPLE_LIMIT=null})
  Access(groups=[salesforce.Contact], props={SOURCE_HINT=null, MODEL_ID=Schema name=salesforce, nameInSource=null, uuid=tid:6b7918e394e2-66cf095f-00000000})
    TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=15, OUTPUT_COLS=null})
      Project(groups=[salesforce.Contact], props={PROJECT_COLS=[salesforce.Contact.Id]})
        Source(groups=[salesforce.Contact])


============================================================================
EXECUTING AssignOutputElements

AFTER: 
TupleLimit(groups=[], props={OFFSET_TUPLE_COUNT=10, MAX_TUPLE_LIMIT=null, OUTPUT_COLS=[salesforce.Contact.Id]})
  Access(groups=[salesforce.Contact], props={SOURCE_HINT=null, MODEL_ID=Schema name=salesforce, nameInSource=null, uuid=tid:6b7918e394e2-66cf095f-00000000, OUTPUT_COLS=[salesforce.Contact.Id]})
    TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=15, OUTPUT_COLS=[salesforce.Contact.Id]})
      Project(groups=[salesforce.Contact], props={PROJECT_COLS=[salesforce.Contact.Id], OUTPUT_COLS=[salesforce.Contact.Id]})
        Source(groups=[salesforce.Contact], props={OUTPUT_COLS=[salesforce.Contact.Id]})


============================================================================
EXECUTING PushLimit

AFTER: 
TupleLimit(groups=[], props={OFFSET_TUPLE_COUNT=10, MAX_TUPLE_LIMIT=null, OUTPUT_COLS=[salesforce.Contact.Id]})
  Access(groups=[salesforce.Contact], props={SOURCE_HINT=null, MODEL_ID=Schema name=salesforce, nameInSource=null, uuid=tid:6b7918e394e2-66cf095f-00000000, OUTPUT_COLS=[salesforce.Contact.Id]})
    TupleLimit(groups=[])
      Project(groups=[salesforce.Contact])
        Source(groups=[salesforce.Contact])


============================================================================
EXECUTING CalculateCost

AFTER: 
TupleLimit(groups=[], props={OFFSET_TUPLE_COUNT=10, MAX_TUPLE_LIMIT=null, OUTPUT_COLS=[salesforce.Contact.Id], EST_CARDINALITY=5.0, EST_COL_STATS={salesforce.Contact.Id=[-1.0, -1.0, -1.0]}})
  Access(groups=[salesforce.Contact], props={SOURCE_HINT=null, MODEL_ID=Schema name=salesforce, nameInSource=null, uuid=tid:6b7918e394e2-66cf095f-00000000, OUTPUT_COLS=[salesforce.Contact.Id], EST_CARDINALITY=15.0, EST_COL_STATS={salesforce.Contact.Id=[-1.0, -1.0, -1.0]}})
    TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=15, OUTPUT_COLS=[salesforce.Contact.Id], EST_CARDINALITY=15.0, EST_COL_STATS={salesforce.Contact.Id=[-1.0, -1.0, -1.0]}})
      Project(groups=[salesforce.Contact], props={PROJECT_COLS=[salesforce.Contact.Id], OUTPUT_COLS=[salesforce.Contact.Id], EST_CARDINALITY=-1.0, EST_COL_STATS={salesforce.Contact.Id=[-1.0, -1.0, -1.0]}})
        Source(groups=[salesforce.Contact], props={OUTPUT_COLS=[salesforce.Contact.Id], EST_COL_STATS={salesforce.Contact.Id=[-1.0, -1.0, -1.0]}, EST_CARDINALITY=-1.0})


============================================================================
EXECUTING PlanSubqueries

AFTER: 
TupleLimit(groups=[])
  Access(groups=[salesforce.Contact])
    TupleLimit(groups=[])
      Project(groups=[salesforce.Contact])
        Source(groups=[salesforce.Contact])


============================================================================
EXECUTING PlanSorts

AFTER: 
TupleLimit(groups=[])
  Access(groups=[salesforce.Contact])
    TupleLimit(groups=[])
      Project(groups=[salesforce.Contact])
        Source(groups=[salesforce.Contact])


============================================================================
EXECUTING CollapseSource

AFTER: 
TupleLimit(groups=[])
  Access(groups=[salesforce.Contact], props={SOURCE_HINT=null, MODEL_ID=Schema name=salesforce, nameInSource=null, uuid=tid:6b7918e394e2-66cf095f-00000000, OUTPUT_COLS=[salesforce.Contact.Id], EST_CARDINALITY=15.0, EST_COL_STATS={salesforce.Contact.Id=[-1.0, -1.0, -1.0]}, ATOMIC_REQUEST=SELECT salesforce.Contact.Id FROM salesforce.Contact LIMIT 15})


============================================================================
CONVERTING PLAN TREE TO PROCESS TREE

PROCESS PLAN = 
LimitNode(0) output=[salesforce.Contact.Id]  offset 10
  AccessNode(1) output=[salesforce.Contact.Id] SELECT g_0.Id FROM salesforce.Contact AS g_0 LIMIT 15

============================================================================

----------------------------------------------------------------------------
OPTIMIZATION COMPLETE:
PROCESSOR PLAN:
LimitNode(0) output=[salesforce.Contact.Id]  offset 10
  AccessNode(1) output=[salesforce.Contact.Id] SELECT g_0.Id FROM salesforce.Contact AS g_0 LIMIT 15

============================================================================
END DEBUG LOG

We used teiid 16.0.1-SNAPSHOT

Attachments

Activity

People

Assignee:: Unassigned

Reporter:: Renat Eskenin (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2021/05/12 4:11 PM

Updated:: 2023/09/25 1:02 PM