[teiid-issues] [JBoss JIRA] (TEIID-5081) FORMATTIMESTAMP is not pushed down to postgresql but formattimestamp is

FORMATTIMESTAMP is not pushed down to postgresql but formattimestamp is ----------------------------------------------------------------------- Key: TEIID-5081 URL: https://issues.jboss.org/browse/TEIID-5081 Project: Teiid Issue Type: Bug Components: Query Engine Environment: * teiid 9.3.3 * jdbc driver: postgresql-9.4.1212.jre7.jar * postgres version 9.5 * java 1.8.0_141-b15 Reporter: Fábio Franco Assignee: Steven Hawkins FORMATTIMESTAMP (uppercase) is NOT pushed to postgres but formattimestamp (lowercase) is correctly pushed. FORMATTIMESTAMP (uppercase) plan: {code} 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 ============================================================================ {code} ---- formattimestamp (lowercase) plan: {code} 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 ============================================================================ {code}