Loading...

XML

Word

Printable

Details

Type: Bug
Resolution: Done
Priority: Major
Fix Version/s: 10.0, 9.3.4
Affects Version/s: None
Component/s: Query Engine
Labels:
None

Steps to Reproduce:
Hide

Have a table with a column of type timestamp such as:

CREATE TABLE test_z (a timestamp); INSERT INTO test_z (a) VALUES (now());

Add pg as datasource in teiid

Execute both queries and check the query plan:

SELECT FORMATTIMESTAMP(a, 'yyyy') FROM ds_pg.test_z; SELECT formattimestamp(a, 'yyyy') FROM ds_pg.test_z;
Show
Have a table with a column of type timestamp such as: CREATE TABLE test_z ( a timestamp ); INSERT INTO test_z ( a ) VALUES (now()); Add pg as datasource in teiid Execute both queries and check the query plan: SELECT FORMATTIMESTAMP( a , 'yyyy' ) FROM ds_pg.test_z; SELECT formattimestamp( a , 'yyyy' ) FROM ds_pg.test_z;

Description

FORMATTIMESTAMP (uppercase) is NOT pushed to postgres but formattimestamp (lowercase) is correctly pushed.

FORMATTIMESTAMP (uppercase) plan:

ProjectNode
  + Relational Node ID:0
  + Output Columns:expr1 (string)
  + Statistics:
    0: Node Output Rows: 1
    1: Node Next Batch Process Time: 1
    2: Node Cumulative Next Batch Process Time: 2
    3: Node Cumulative Process Time: 23
    4: Node Next Batch Calls: 2
    5: Node Blocks: 1
  + Cost Estimates:Estimated Node Cardinality: 502.0
  + Child 0:
    AccessNode
      + Relational Node ID:1
      + Output Columns:a (timestamp)
      + Statistics:
        0: Node Output Rows: 1
        1: Node Next Batch Process Time: 1
        2: Node Cumulative Next Batch Process Time: 1
        3: Node Cumulative Process Time: 22
        4: Node Next Batch Calls: 2
        5: Node Blocks: 1
      + Cost Estimates:Estimated Node Cardinality: 502.0
      + Query:SELECT g_0.a AS c_0 FROM ds2.test_z AS g_0 LIMIT 502
      + Model Name:ds2
  + Select Columns:FORMATTIMESTAMP(ds2.test_z.a, 'yyyy-mm-dd')
  + Data Bytes Sent:26
  + Planning Time:13

============================================================================                                                                                                                                                                   
USER COMMAND:                                                                                                                                                                                                                                  
SELECT FORMATTIMESTAMP(ds2.test_z.a, 'yyyy-mm-dd') FROM ds2.test_z LIMIT 502                                                                                                                                                                   
                                                                                                                                                                                                                                               
----------------------------------------------------------------------------                                                                                                                                                                   
OPTIMIZE:                                                                                                                                                                                                                                      
SELECT FORMATTIMESTAMP(ds2.test_z.a, 'yyyy-mm-dd') FROM ds2.test_z LIMIT 502                                                                                                                                                                   
                                                                                                                                                                                                                                               
----------------------------------------------------------------------------                                                                                                                                                                   
GENERATE CANONICAL:                                                                                                                                                                                                                            
SELECT FORMATTIMESTAMP(ds2.test_z.a, 'yyyy-mm-dd') FROM ds2.test_z LIMIT 502                                                                                                                                                                   
                                                                                                                                                                                                                                               
CANONICAL PLAN:                                                                                                                                                                                                                                
TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=502})                                                                                                                                                                                             
  Project(groups=[ds2.test_z], props={PROJECT_COLS=[FORMATTIMESTAMP(ds2.test_z.a, 'yyyy-mm-dd')]})                                                                                                                                             
    Source(groups=[ds2.test_z])                                                                                                                                                                                                                
                                                                                                                                                                                                                                               
                                                                                                                                                                                                                                               
============================================================================                                                                                                                                                                   
EXECUTING PlaceAccess                                                                                                                                                                                                                          
                                                                                                                                                                                                                                               
AFTER:                                                                                                                                                                                                                                         
TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=502})                                                                                                                                                                                             
  Project(groups=[ds2.test_z], props={PROJECT_COLS=[FORMATTIMESTAMP(ds2.test_z.a, 'yyyy-mm-dd')]})                                                                                                                                             
    Access(groups=[ds2.test_z], props={SOURCE_HINT=null, MODEL_ID=Schema name=ds2, nameInSource=null, uuid=tid:15dc08a3b0c7-00018583-00000000})                                                                                                
      Source(groups=[ds2.test_z])                                                                                                                                                                                                              
                                                                                                                                                                                                                                               
                                                                                                                                                                                                                                               
============================================================================                                                                                                                                                                   
EXECUTING RaiseAccess                                                                                                                                                                                                                          
LOW [Relational Planner] FORMATTIMESTAMP literal parse 'yyyy-mm-dd' not supported by source ds2 - FORMATTIMESTAMP(ds2.test_z.a, 'yyyy-mm-dd') was not pushed                                                                                   
                                                                                                                                                                                                                                               
AFTER:                                                                                                                                                                                                                                         
TupleLimit(groups=[])                                                                                                                                                                                                                          
  Project(groups=[ds2.test_z], props={PROJECT_COLS=[FORMATTIMESTAMP(ds2.test_z.a, 'yyyy-mm-dd')]})                                                                                                                                             
    Access(groups=[ds2.test_z], props={SOURCE_HINT=null, MODEL_ID=Schema name=ds2, nameInSource=null, uuid=tid:15dc08a3b0c7-00018583-00000000})                                                                                                
      Source(groups=[ds2.test_z])                                                                                                                                                                                                              
                                                                                                                                                                                                                                               
                                                                                                                                                                                                                                               
============================================================================                                                                                                                                                                   
EXECUTING AssignOutputElements                                                                                                                                                                                                                 
                                                                                                                                                                                                                                               
AFTER:                                                                                                                                                                                                                                         
TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=502, OUTPUT_COLS=[FORMATTIMESTAMP(ds2.test_z.a, 'yyyy-mm-dd')]})                                                                                                                                  
  Project(groups=[ds2.test_z], props={PROJECT_COLS=[FORMATTIMESTAMP(ds2.test_z.a, 'yyyy-mm-dd')], OUTPUT_COLS=[FORMATTIMESTAMP(ds2.test_z.a, 'yyyy-mm-dd')]})                                                                                  
    Access(groups=[ds2.test_z], props={SOURCE_HINT=null, MODEL_ID=Schema name=ds2, nameInSource=null, uuid=tid:15dc08a3b0c7-00018583-00000000, OUTPUT_COLS=[ds2.test_z.a]})                                                                    
      Source(groups=[ds2.test_z], props={OUTPUT_COLS=[ds2.test_z.a]})                                                                                                                                                                          
                                                                                                                                                                                                                                               
                                                                                                                                                                                                                                               
============================================================================                                                                                                                                                                   
EXECUTING PushLimit                                                                                                                                                                                                                            
                                                                                                                                                                                                                                               
AFTER:                                                                                                                                                                                                                                         
Project(groups=[ds2.test_z], props={PROJECT_COLS=[FORMATTIMESTAMP(ds2.test_z.a, 'yyyy-mm-dd')], OUTPUT_COLS=[FORMATTIMESTAMP(ds2.test_z.a, 'yyyy-mm-dd')], SOURCE_HINT=null})                                                                  
  Access(groups=[ds2.test_z], props={SOURCE_HINT=null, MODEL_ID=Schema name=ds2, nameInSource=null, uuid=tid:15dc08a3b0c7-00018583-00000000, OUTPUT_COLS=[ds2.test_z.a]})                                                                      
    TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=502, OUTPUT_COLS=[ds2.test_z.a]})                                                                                                                                                             
      Source(groups=[ds2.test_z], props={OUTPUT_COLS=[ds2.test_z.a]})                                                                                                                                                                          
                                                                                                                                                                                                                                               
                                                                                                                                                                                                                                               
============================================================================                                                                                                                                                                   
EXECUTING CalculateCost                                                                                                                                                                                                                        
                                                                                                                                                                                                                                               
AFTER:                                                                                                                                                                                                                                         
Project(groups=[ds2.test_z], props={PROJECT_COLS=[FORMATTIMESTAMP(ds2.test_z.a, 'yyyy-mm-dd')], OUTPUT_COLS=[FORMATTIMESTAMP(ds2.test_z.a, 'yyyy-mm-dd')], SOURCE_HINT=null, EST_CARDINALITY=502.0, EST_COL_STATS={FORMATTIMESTAMP(ds2.test_z.a, 'yyyy-mm-dd')=[502.0, 502.0, 0.0]}})
  Access(groups=[ds2.test_z], props={SOURCE_HINT=null, MODEL_ID=Schema name=ds2, nameInSource=null, uuid=tid:15dc08a3b0c7-00018583-00000000, OUTPUT_COLS=[ds2.test_z.a], EST_CARDINALITY=502.0, EST_COL_STATS={ds2.test_z.a=[502.0, 502.0, 0.0]}})
    TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=502, OUTPUT_COLS=[ds2.test_z.a], EST_CARDINALITY=502.0, EST_COL_STATS={ds2.test_z.a=[502.0, 502.0, 0.0]}})
      Source(groups=[ds2.test_z], props={OUTPUT_COLS=[ds2.test_z.a], EST_COL_STATS={ds2.test_z.a=[-1.0, -1.0, -1.0]}, EST_CARDINALITY=-1.0})


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

AFTER: 
Project(groups=[ds2.test_z])
  Access(groups=[ds2.test_z])
    TupleLimit(groups=[])
      Source(groups=[ds2.test_z])


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

AFTER: 
Project(groups=[ds2.test_z], props={PROJECT_COLS=[FORMATTIMESTAMP(ds2.test_z.a, 'yyyy-mm-dd')], OUTPUT_COLS=[FORMATTIMESTAMP(ds2.test_z.a, 'yyyy-mm-dd')], SOURCE_HINT=null, EST_CARDINALITY=502.0, EST_COL_STATS={FORMATTIMESTAMP(ds2.test_z.a, 'yyyy-mm-dd')=[502.0, 502.0, 0.0]}})
  Access(groups=[ds2.test_z], props={SOURCE_HINT=null, MODEL_ID=Schema name=ds2, nameInSource=null, uuid=tid:15dc08a3b0c7-00018583-00000000, OUTPUT_COLS=[ds2.test_z.a], EST_CARDINALITY=502.0, EST_COL_STATS={ds2.test_z.a=[502.0, 502.0, 0.0]}, ATOMIC_REQUEST=SELECT ds2.test_z.a FROM ds2.test_z LIMIT 502})


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

PROCESS PLAN = 
ProjectNode(0) output=[FORMATTIMESTAMP(ds2.test_z.a, 'yyyy-mm-dd')] [FORMATTIMESTAMP(ds2.test_z.a, 'yyyy-mm-dd')]
  AccessNode(1) output=[ds2.test_z.a] SELECT g_0.a AS c_0 FROM ds2.test_z AS g_0 LIMIT 502

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

----------------------------------------------------------------------------
OPTIMIZATION COMPLETE:
PROCESSOR PLAN:
ProjectNode(0) output=[FORMATTIMESTAMP(ds2.test_z.a, 'yyyy-mm-dd')] [FORMATTIMESTAMP(ds2.test_z.a, 'yyyy-mm-dd')]
  AccessNode(1) output=[ds2.test_z.a] SELECT g_0.a AS c_0 FROM ds2.test_z AS g_0 LIMIT 502

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

formattimestamp (lowercase) plan:

AccessNode
  + Relational Node ID:0
  + Output Columns:expr1 (string)
  + Statistics:
    0: Node Output Rows: 1
    1: Node Next Batch Process Time: 0
    2: Node Cumulative Next Batch Process Time: 0
    3: Node Cumulative Process Time: 3
    4: Node Next Batch Calls: 2
    5: Node Blocks: 1
  + Cost Estimates:Estimated Node Cardinality: 502.0
  + Query:SELECT formattimestamp(g_0.a, 'yyyy-mm-dd') AS c_0 FROM ds2.test_z AS g_0 LIMIT 502
  + Model Name:ds2
  + Data Bytes Sent:26
  + Planning Time:3

============================================================================
USER COMMAND:
SELECT formattimestamp(ds2.test_z.a, 'yyyy-mm-dd') FROM ds2.test_z LIMIT 502

----------------------------------------------------------------------------
OPTIMIZE: 
SELECT formattimestamp(ds2.test_z.a, 'yyyy-mm-dd') FROM ds2.test_z LIMIT 502

----------------------------------------------------------------------------
GENERATE CANONICAL: 
SELECT formattimestamp(ds2.test_z.a, 'yyyy-mm-dd') FROM ds2.test_z LIMIT 502

CANONICAL PLAN: 
TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=502})
  Project(groups=[ds2.test_z], props={PROJECT_COLS=[formattimestamp(ds2.test_z.a, 'yyyy-mm-dd')]})
    Source(groups=[ds2.test_z])


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

AFTER: 
TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=502})
  Project(groups=[ds2.test_z], props={PROJECT_COLS=[formattimestamp(ds2.test_z.a, 'yyyy-mm-dd')]})
    Access(groups=[ds2.test_z], props={SOURCE_HINT=null, MODEL_ID=Schema name=ds2, nameInSource=null, uuid=tid:15dc08a3b0c7-00018583-00000000})
      Source(groups=[ds2.test_z])


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

AFTER: 
Access(groups=[ds2.test_z], props={SOURCE_HINT=null, MODEL_ID=Schema name=ds2, nameInSource=null, uuid=tid:15dc08a3b0c7-00018583-00000000})
  TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=502})
    Project(groups=[ds2.test_z], props={PROJECT_COLS=[formattimestamp(ds2.test_z.a, 'yyyy-mm-dd')]})
      Source(groups=[ds2.test_z])


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

AFTER: 
Access(groups=[ds2.test_z], props={SOURCE_HINT=null, MODEL_ID=Schema name=ds2, nameInSource=null, uuid=tid:15dc08a3b0c7-00018583-00000000, OUTPUT_COLS=[formattimestamp(ds2.test_z.a, 'yyyy-mm-dd')]})
  TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=502, OUTPUT_COLS=[formattimestamp(ds2.test_z.a, 'yyyy-mm-dd')]})
    Project(groups=[ds2.test_z], props={PROJECT_COLS=[formattimestamp(ds2.test_z.a, 'yyyy-mm-dd')], OUTPUT_COLS=[formattimestamp(ds2.test_z.a, 'yyyy-mm-dd')]})
      Source(groups=[ds2.test_z], props={OUTPUT_COLS=[ds2.test_z.a]})


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

AFTER: 
Access(groups=[ds2.test_z])
  TupleLimit(groups=[])
    Project(groups=[ds2.test_z])
      Source(groups=[ds2.test_z])


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

AFTER: 
Access(groups=[ds2.test_z], props={SOURCE_HINT=null, MODEL_ID=Schema name=ds2, nameInSource=null, uuid=tid:15dc08a3b0c7-00018583-00000000, OUTPUT_COLS=[formattimestamp(ds2.test_z.a, 'yyyy-mm-dd')], EST_CARDINALITY=502.0, EST_COL_STATS={formattimestamp(ds2.test_z.a, 'yyyy-mm-dd')=[502.0, 502.0, 0.0]}})
  TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=502, OUTPUT_COLS=[formattimestamp(ds2.test_z.a, 'yyyy-mm-dd')], EST_CARDINALITY=502.0, EST_COL_STATS={formattimestamp(ds2.test_z.a, 'yyyy-mm-dd')=[502.0, 502.0, 0.0]}})
    Project(groups=[ds2.test_z], props={PROJECT_COLS=[formattimestamp(ds2.test_z.a, 'yyyy-mm-dd')], OUTPUT_COLS=[formattimestamp(ds2.test_z.a, 'yyyy-mm-dd')], EST_CARDINALITY=-1.0})
      Source(groups=[ds2.test_z], props={OUTPUT_COLS=[ds2.test_z.a], EST_COL_STATS={ds2.test_z.a=[-1.0, -1.0, -1.0]}, EST_CARDINALITY=-1.0})


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

AFTER: 
Access(groups=[ds2.test_z])
  TupleLimit(groups=[])
    Project(groups=[ds2.test_z])
      Source(groups=[ds2.test_z])


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

AFTER: 
Access(groups=[ds2.test_z], props={SOURCE_HINT=null, MODEL_ID=Schema name=ds2, nameInSource=null, uuid=tid:15dc08a3b0c7-00018583-00000000, OUTPUT_COLS=[formattimestamp(ds2.test_z.a, 'yyyy-mm-dd')], EST_CARDINALITY=502.0, EST_COL_STATS={formattimestamp(ds2.test_z.a, 'yyyy-mm-dd')=[502.0, 502.0, 0.0]}, ATOMIC_REQUEST=SELECT formattimestamp(ds2.test_z.a, 'yyyy-mm-dd') FROM ds2.test_z LIMIT 502})


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

PROCESS PLAN = 
AccessNode(0) output=[formattimestamp(ds2.test_z.a, 'yyyy-mm-dd')] SELECT formattimestamp(g_0.a, 'yyyy-mm-dd') AS c_0 FROM ds2.test_z AS g_0 LIMIT 502

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

----------------------------------------------------------------------------
OPTIMIZATION COMPLETE:
PROCESSOR PLAN:
AccessNode(0) output=[formattimestamp(ds2.test_z.a, 'yyyy-mm-dd')] SELECT formattimestamp(g_0.a, 'yyyy-mm-dd') AS c_0 FROM ds2.test_z AS g_0 LIMIT 502

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

Attachments

Activity

People

Assignee:: Steven Hawkins

Reporter:: Fábio Franco (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2017/09/26 6:49 PM

Updated:: 2017/09/27 8:37 AM

Resolved:: 2017/09/27 8:37 AM