[teiid-issues] [JBoss JIRA] (TEIID-5081) FORMATTIMESTAMP is not pushed down to postgresql but formattimestamp is
Fábio Franco (JIRA)
issues at jboss.org
Tue Sep 26 19:01:00 EDT 2017
[ https://issues.jboss.org/browse/TEIID-5081?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Fábio Franco updated TEIID-5081:
--------------------------------
Summary: FORMATTIMESTAMP is not pushed down to postgresql but formattimestamp is (was: FORMATTIMESTAMP is not pushed down to postgesql 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}
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
More information about the teiid-issues
mailing list