5:52 p.m.
[
https://issues.jboss.org/browse/TEIID-5081?page=com.atlassian.jira.plugin...
]
Fábio Franco updated TEIID-5081:
--------------------------------
Description:
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}
was:
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}
FORMATTIMESTAMP is not pushed down to postgesql 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)
2925
days inactive
2925
days old
0 comments
1 participants
participants (1)
-
Fábio Franco (JIRA)