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

FORMATTIMESTAMP is not pushed down to postgresql but formattimestamp is

    Details

    • Type: Bug
    • Status: Resolved (View Workflow)
    • Priority: Major
    • Resolution: Done
    • Affects Version/s: None
    • Fix Version/s: 10.0, 9.3.4
    • Component/s: Query Engine
    • Labels:
      None
    • Environment:
      • teiid 9.3.3
      • jdbc driver: postgresql-9.4.1212.jre7.jar
      • PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
      • java 1.8.0_141-b15
    • Steps to Reproduce:
      Hide
      1. Have a table with a column of type timestamp such as:

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

      2. Add pg as datasource in teiid
      3. 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
       
      ============================================================================
      

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                mrfabiofranco Fábio Franco
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: