3 Replies Latest reply on Apr 28, 2017 4:25 PM by shawkins

Problem with DST within virtual procedure

gadeyne.bram Apr 27, 2017 4:01 AM

Hi,

I have an issue with DST in teiid. What seems to be 2 exactly the same queries but in a different format (virtual procedure and directly on the database) give different results.

The issue is for a date '2009-10-25 04:33:00.0' On this date the time went from CEST to CET (-1 hour) at 3 am.

The first query gives the expected result:

select TIMESTAMPADD(SQL_TSI_HOUR,5,
  cast(
     cast(gd.admissiontime as date) 
  as timestamp)
) as periodend, FORMATTIMESTAMP(gd.admissiontime, 'Z') as tz
from wh_P_GeneralData gd
where gd.PatientID = 20444;

result:

periodend	tz
2009-10-25 05:00:00.0	+0100

The second (virtual procedure) gives an incorrect result:

BEGIN
  DECLARE timestamp admissiontime = select gd.admissiontime from wh_P_GeneralData gd where gd.PatientID = 20444;
  select periodend, FORMATTIMESTAMP(periodend, 'Z') as tz
  from (
      select TIMESTAMPADD(SQL_TSI_HOUR,5,
          cast(
              cast(admissiontime as date) 
          as timestamp)
      ) as periodend
  ) v;
END

result:

periodend	tz
2009-10-25 04:00:00.0	+0100

Can anyone explain this behaviour?

This is the query plan for the query:

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

USER COMMAND:

SELECT TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)) AS periodend, FORMATTIMESTAMP(gd.AdmissionTime, 'Z') AS tz FROM iziswh.wh_P_GeneralData AS gd WHERE gd.PatientID = 20444 LIMIT 100

----------------------------------------------------------------------------

OPTIMIZE:

----------------------------------------------------------------------------

GENERATE CANONICAL:

CANONICAL PLAN:

TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=100})

Select(groups=[iziswh.wh_P_GeneralData AS gd], props={SELECT_CRITERIA=gd.PatientID = 20444})

Source(groups=[iziswh.wh_P_GeneralData AS gd])

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

EXECUTING PlaceAccess

AFTER:

TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=100})

Select(groups=[iziswh.wh_P_GeneralData AS gd], props={SELECT_CRITERIA=gd.PatientID = 20444})

Access(groups=[iziswh.wh_P_GeneralData AS gd], props={SOURCE_HINT=null, MODEL_ID=Schema name=iziswh, nameInSource=null, uuid=mmuuid:43611995-84e0-4550-be48-cdeb054d06ac})

Source(groups=[iziswh.wh_P_GeneralData AS gd])

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

EXECUTING PushSelectCriteria

AFTER:

TupleLimit(groups=[])

Access(groups=[iziswh.wh_P_GeneralData AS gd], props={SOURCE_HINT=null, MODEL_ID=Schema name=iziswh, nameInSource=null, uuid=mmuuid:43611995-84e0-4550-be48-cdeb054d06ac})

Select(groups=[iziswh.wh_P_GeneralData AS gd], props={SELECT_CRITERIA=gd.PatientID = 20444})

Source(groups=[iziswh.wh_P_GeneralData AS gd])

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

EXECUTING CleanCriteria

AFTER:

TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=100, OUTPUT_COLS=null})

Access(groups=[iziswh.wh_P_GeneralData AS gd], props={SOURCE_HINT=null, MODEL_ID=Schema name=iziswh, nameInSource=null, uuid=mmuuid:43611995-84e0-4550-be48-cdeb054d06ac, OUTPUT_COLS=null})

Select(groups=[iziswh.wh_P_GeneralData AS gd], props={SELECT_CRITERIA=gd.PatientID = 20444, OUTPUT_COLS=null})

Source(groups=[iziswh.wh_P_GeneralData AS gd], props={OUTPUT_COLS=null})

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

EXECUTING RaiseAccess

LOW [Relational Planner] FORMATTIMESTAMP function not supported by source iziswh - FORMATTIMESTAMP(gd.AdmissionTime, 'Z') was not pushed

AFTER:

TupleLimit(groups=[])

Access(groups=[iziswh.wh_P_GeneralData AS gd], props={SOURCE_HINT=null, MODEL_ID=Schema name=iziswh, nameInSource=null, uuid=mmuuid:43611995-84e0-4550-be48-cdeb054d06ac, OUTPUT_COLS=null})

Select(groups=[iziswh.wh_P_GeneralData AS gd])

Source(groups=[iziswh.wh_P_GeneralData AS gd])

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

EXECUTING AssignOutputElements

AFTER:

TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=100, OUTPUT_COLS=[TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)) AS periodend, FORMATTIMESTAMP(gd.AdmissionTime, 'Z') AS tz]})

Select(groups=[iziswh.wh_P_GeneralData AS gd], props={SELECT_CRITERIA=gd.PatientID = 20444, OUTPUT_COLS=[gd.AdmissionTime]})

Source(groups=[iziswh.wh_P_GeneralData AS gd], props={OUTPUT_COLS=[gd.PatientID, gd.AdmissionTime]})

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

EXECUTING PushLimit

AFTER:

TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=100, OUTPUT_COLS=[TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)), gd.AdmissionTime]})

Select(groups=[iziswh.wh_P_GeneralData AS gd], props={SELECT_CRITERIA=gd.PatientID = 20444, OUTPUT_COLS=[gd.AdmissionTime]})

Source(groups=[iziswh.wh_P_GeneralData AS gd])

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

EXECUTING CalculateCost

AFTER:

Project(groups=[iziswh.wh_P_GeneralData AS gd], props={PROJECT_COLS=[TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)) AS periodend, FORMATTIMESTAMP(gd.AdmissionTime, 'Z') AS tz], OUTPUT_COLS=[TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)) AS periodend, FORMATTIMESTAMP(gd.AdmissionTime, 'Z') AS tz], SOURCE_HINT=null, EST_CARDINALITY=1.0, EST_COL_STATS={TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp))=[1.0, 1.0, -1.0], FORMATTIMESTAMP(gd.AdmissionTime, 'Z')=[1.0, 1.0, -1.0]}})

Access(groups=[iziswh.wh_P_GeneralData AS gd], props={SOURCE_HINT=null, MODEL_ID=Schema name=iziswh, nameInSource=null, uuid=mmuuid:43611995-84e0-4550-be48-cdeb054d06ac, OUTPUT_COLS=[TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)), gd.AdmissionTime], EST_CARDINALITY=1.0, EST_COL_STATS={TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp))=[1.0, 1.0, -1.0], gd.AdmissionTime=[1.0, 1.0, -1.0]}})

TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=100, OUTPUT_COLS=[TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)), gd.AdmissionTime], EST_CARDINALITY=1.0, EST_COL_STATS={TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp))=[1.0, 1.0, -1.0], gd.AdmissionTime=[1.0, 1.0, -1.0]}})

Select(groups=[iziswh.wh_P_GeneralData AS gd], props={SELECT_CRITERIA=gd.PatientID = 20444, OUTPUT_COLS=[gd.AdmissionTime], EST_CARDINALITY=1.0, EST_COL_STATS={gd.AdmissionTime=[1.0, 1.0, -1.0]}})

Source(groups=[iziswh.wh_P_GeneralData AS gd], props={OUTPUT_COLS=[gd.PatientID, gd.AdmissionTime], EST_COL_STATS={gd.PatientID=[45000.0, 45000.0, 0.0], gd.AdmissionTime=[213.0, 22500.0, -1.0]}, EST_CARDINALITY=45000.0})

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

EXECUTING MergeCriteria

AFTER:

Project(groups=[iziswh.wh_P_GeneralData AS gd])

Access(groups=[iziswh.wh_P_GeneralData AS gd])

TupleLimit(groups=[])

Select(groups=[iziswh.wh_P_GeneralData AS gd])

Source(groups=[iziswh.wh_P_GeneralData AS gd])

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

EXECUTING PlanSorts

AFTER:

Project(groups=[iziswh.wh_P_GeneralData AS gd])

Access(groups=[iziswh.wh_P_GeneralData AS gd])

TupleLimit(groups=[])

Select(groups=[iziswh.wh_P_GeneralData AS gd])

Source(groups=[iziswh.wh_P_GeneralData AS gd])

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

EXECUTING CollapseSource

AFTER:

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

CONVERTING PLAN TREE TO PROCESS TREE

PROCESS PLAN =

ProjectNode(0) output=[TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)) AS periodend, FORMATTIMESTAMP(gd.AdmissionTime, 'Z') AS tz] [TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)) AS periodend, FORMATTIMESTAMP(gd.AdmissionTime, 'Z') AS tz]

AccessNode(1) output=[TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)), gd.AdmissionTime] SELECT TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(g_0.AdmissionTime, date), timestamp)) AS c_0, g_0.AdmissionTime AS c_1 FROM iziswh.wh_P_GeneralData AS g_0 WHERE g_0.PatientID = 20444 LIMIT 100

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

----------------------------------------------------------------------------

OPTIMIZATION COMPLETE:

PROCESSOR PLAN:

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

This is the query plan for the virtual procedure:

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

USER COMMAND:

BEGIN

DECLARE timestamp admissiontime = (SELECT gd.AdmissionTime FROM iziswh.wh_P_GeneralData AS gd WHERE gd.PatientID = 20444 LIMIT 2);

SELECT v.periodend, FORMATTIMESTAMP(v.periodend, 'Z') AS tz FROM (SELECT TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(VARIABLES.admissiontime, date), timestamp)) AS periodend) AS v;

END

----------------------------------------------------------------------------

OPTIMIZE:

BEGIN

DECLARE timestamp admissiontime = (SELECT gd.AdmissionTime FROM iziswh.wh_P_GeneralData AS gd WHERE gd.PatientID = 20444 LIMIT 2);

SELECT v.periodend, FORMATTIMESTAMP(v.periodend, 'Z') AS tz FROM (SELECT TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(VARIABLES.admissiontime, date), timestamp)) AS periodend) AS v;

END

####################################################

PROCEDURE COMMAND: BEGIN

DECLARE timestamp admissiontime = (SELECT gd.AdmissionTime FROM iziswh.wh_P_GeneralData AS gd WHERE gd.PatientID = 20444 LIMIT 2);

SELECT v.periodend, FORMATTIMESTAMP(v.periodend, 'Z') AS tz FROM (SELECT TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(VARIABLES.admissiontime, date), timestamp)) AS periodend) AS v;

END

OPTIMIZING SUB-COMMANDS:

----------------------------------------------------------------------------

OPTIMIZE:

SELECT gd.AdmissionTime FROM iziswh.wh_P_GeneralData AS gd WHERE gd.PatientID = 20444 LIMIT 2

----------------------------------------------------------------------------

GENERATE CANONICAL:

SELECT gd.AdmissionTime FROM iziswh.wh_P_GeneralData AS gd WHERE gd.PatientID = 20444 LIMIT 2

CANONICAL PLAN:

TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=2, IS_IMPLICIT_LIMIT=true})

Project(groups=[iziswh.wh_P_GeneralData AS gd], props={PROJECT_COLS=[gd.AdmissionTime]})

Select(groups=[iziswh.wh_P_GeneralData AS gd], props={SELECT_CRITERIA=gd.PatientID = 20444})

Source(groups=[iziswh.wh_P_GeneralData AS gd])

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

EXECUTING PlaceAccess

AFTER:

TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=2, IS_IMPLICIT_LIMIT=true})

Project(groups=[iziswh.wh_P_GeneralData AS gd], props={PROJECT_COLS=[gd.AdmissionTime]})

Select(groups=[iziswh.wh_P_GeneralData AS gd], props={SELECT_CRITERIA=gd.PatientID = 20444})

Access(groups=[iziswh.wh_P_GeneralData AS gd], props={SOURCE_HINT=null, MODEL_ID=Schema name=iziswh, nameInSource=null, uuid=mmuuid:43611995-84e0-4550-be48-cdeb054d06ac})

Source(groups=[iziswh.wh_P_GeneralData AS gd])

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

EXECUTING PushSelectCriteria

AFTER:

TupleLimit(groups=[])

Project(groups=[iziswh.wh_P_GeneralData AS gd], props={PROJECT_COLS=[gd.AdmissionTime]})

Access(groups=[iziswh.wh_P_GeneralData AS gd], props={SOURCE_HINT=null, MODEL_ID=Schema name=iziswh, nameInSource=null, uuid=mmuuid:43611995-84e0-4550-be48-cdeb054d06ac})

Select(groups=[iziswh.wh_P_GeneralData AS gd], props={SELECT_CRITERIA=gd.PatientID = 20444})

Source(groups=[iziswh.wh_P_GeneralData AS gd])

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

EXECUTING CleanCriteria

AFTER:

TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=2, IS_IMPLICIT_LIMIT=true, OUTPUT_COLS=null})

Project(groups=[iziswh.wh_P_GeneralData AS gd], props={PROJECT_COLS=[gd.AdmissionTime], OUTPUT_COLS=null})

Access(groups=[iziswh.wh_P_GeneralData AS gd], props={SOURCE_HINT=null, MODEL_ID=Schema name=iziswh, nameInSource=null, uuid=mmuuid:43611995-84e0-4550-be48-cdeb054d06ac, OUTPUT_COLS=null})

Select(groups=[iziswh.wh_P_GeneralData AS gd], props={SELECT_CRITERIA=gd.PatientID = 20444, OUTPUT_COLS=null})

Source(groups=[iziswh.wh_P_GeneralData AS gd], props={OUTPUT_COLS=null})

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

EXECUTING RaiseAccess

AFTER:

Access(groups=[iziswh.wh_P_GeneralData AS gd], props={SOURCE_HINT=null, MODEL_ID=Schema name=iziswh, nameInSource=null, uuid=mmuuid:43611995-84e0-4550-be48-cdeb054d06ac, OUTPUT_COLS=null})

TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=2, IS_IMPLICIT_LIMIT=true, OUTPUT_COLS=null})

Project(groups=[iziswh.wh_P_GeneralData AS gd], props={PROJECT_COLS=[gd.AdmissionTime], OUTPUT_COLS=null})

Select(groups=[iziswh.wh_P_GeneralData AS gd], props={SELECT_CRITERIA=gd.PatientID = 20444, OUTPUT_COLS=null})

Source(groups=[iziswh.wh_P_GeneralData AS gd])

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

EXECUTING AssignOutputElements

AFTER:

TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=2, IS_IMPLICIT_LIMIT=true, OUTPUT_COLS=[gd.AdmissionTime]})

Project(groups=[iziswh.wh_P_GeneralData AS gd], props={PROJECT_COLS=[gd.AdmissionTime], OUTPUT_COLS=[gd.AdmissionTime]})

Select(groups=[iziswh.wh_P_GeneralData AS gd], props={SELECT_CRITERIA=gd.PatientID = 20444, OUTPUT_COLS=[gd.AdmissionTime]})

Source(groups=[iziswh.wh_P_GeneralData AS gd], props={OUTPUT_COLS=[gd.PatientID, gd.AdmissionTime]})

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

EXECUTING PushLimit

AFTER:

Access(groups=[iziswh.wh_P_GeneralData AS gd])

TupleLimit(groups=[])

Project(groups=[iziswh.wh_P_GeneralData AS gd])

Select(groups=[iziswh.wh_P_GeneralData AS gd])

Source(groups=[iziswh.wh_P_GeneralData AS gd])

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

EXECUTING CalculateCost

AFTER:

TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=2, IS_IMPLICIT_LIMIT=true, OUTPUT_COLS=[gd.AdmissionTime], EST_CARDINALITY=1.0, EST_COL_STATS={gd.AdmissionTime=[1.0, 1.0, -1.0]}})

Project(groups=[iziswh.wh_P_GeneralData AS gd], props={PROJECT_COLS=[gd.AdmissionTime], OUTPUT_COLS=[gd.AdmissionTime], EST_CARDINALITY=1.0, EST_COL_STATS={gd.AdmissionTime=[1.0, 1.0, -1.0]}})

Select(groups=[iziswh.wh_P_GeneralData AS gd], props={SELECT_CRITERIA=gd.PatientID = 20444, OUTPUT_COLS=[gd.AdmissionTime], EST_CARDINALITY=1.0, EST_COL_STATS={gd.AdmissionTime=[1.0, 1.0, -1.0]}})

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

EXECUTING MergeCriteria

AFTER:

Access(groups=[iziswh.wh_P_GeneralData AS gd])

TupleLimit(groups=[])

Project(groups=[iziswh.wh_P_GeneralData AS gd])

Select(groups=[iziswh.wh_P_GeneralData AS gd])

Source(groups=[iziswh.wh_P_GeneralData AS gd])

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

EXECUTING PlanSorts

AFTER:

Access(groups=[iziswh.wh_P_GeneralData AS gd])

TupleLimit(groups=[])

Project(groups=[iziswh.wh_P_GeneralData AS gd])

Select(groups=[iziswh.wh_P_GeneralData AS gd])

Source(groups=[iziswh.wh_P_GeneralData AS gd])

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

EXECUTING CollapseSource

AFTER:

Access(groups=[iziswh.wh_P_GeneralData AS gd], props={SOURCE_HINT=null, MODEL_ID=Schema name=iziswh, nameInSource=null, uuid=mmuuid:43611995-84e0-4550-be48-cdeb054d06ac, OUTPUT_COLS=[gd.AdmissionTime], EST_CARDINALITY=1.0, EST_COL_STATS={gd.AdmissionTime=[1.0, 1.0, -1.0]}, ATOMIC_REQUEST=SELECT gd.AdmissionTime FROM iziswh.wh_P_GeneralData AS gd WHERE gd.PatientID = 20444 LIMIT 2})

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

CONVERTING PLAN TREE TO PROCESS TREE

PROCESS PLAN =

AccessNode(0) output=[gd.AdmissionTime] SELECT g_0.AdmissionTime AS c_0 FROM iziswh.wh_P_GeneralData AS g_0 WHERE g_0.PatientID = 20444 LIMIT 2

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

----------------------------------------------------------------------------

OPTIMIZATION COMPLETE:

PROCESSOR PLAN:

AccessNode(0) output=[gd.AdmissionTime] SELECT g_0.AdmissionTime AS c_0 FROM iziswh.wh_P_GeneralData AS g_0 WHERE g_0.PatientID = 20444 LIMIT 2

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

----------------------------------------------------------------------------

OPTIMIZE:

SELECT v.periodend, FORMATTIMESTAMP(v.periodend, 'Z') AS tz FROM (SELECT TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(VARIABLES.admissiontime, date), timestamp)) AS periodend) AS v

----------------------------------------------------------------------------

GENERATE CANONICAL:

SELECT v.periodend, FORMATTIMESTAMP(v.periodend, 'Z') AS tz FROM (SELECT TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(VARIABLES.admissiontime, date), timestamp)) AS periodend) AS v

CANONICAL PLAN:

Project(groups=[v], props={PROJECT_COLS=[v.periodend, FORMATTIMESTAMP(v.periodend, 'Z') AS tz]})

Source(groups=[v], props={NESTED_COMMAND=SELECT TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(VARIABLES.admissiontime, date), timestamp)) AS periodend, SYMBOL_MAP={v.periodend=TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(VARIABLES.admissiontime, date), timestamp))}})

Project(groups=[], props={PROJECT_COLS=[TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(VARIABLES.admissiontime, date), timestamp)) AS periodend]})

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

EXECUTING PlaceAccess

AFTER:

Project(groups=[v], props={PROJECT_COLS=[v.periodend, FORMATTIMESTAMP(v.periodend, 'Z') AS tz]})

Project(groups=[], props={PROJECT_COLS=[TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(VARIABLES.admissiontime, date), timestamp)) AS periodend]})

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

EXECUTING AssignOutputElements

AFTER:

Project(groups=[v], props={PROJECT_COLS=[v.periodend, FORMATTIMESTAMP(v.periodend, 'Z') AS tz], OUTPUT_COLS=[v.periodend, FORMATTIMESTAMP(v.periodend, 'Z') AS tz]})

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

EXECUTING MergeVirtual

AFTER:

Project(groups=[v], props={PROJECT_COLS=[v.periodend, FORMATTIMESTAMP(v.periodend, 'Z') AS tz], OUTPUT_COLS=[v.periodend, FORMATTIMESTAMP(v.periodend, 'Z') AS tz]})

Project(groups=[])

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

EXECUTING CleanCriteria

AFTER:

Project(groups=[v], props={PROJECT_COLS=[v.periodend, FORMATTIMESTAMP(v.periodend, 'Z') AS tz], OUTPUT_COLS=null})

Project(groups=[], props={PROJECT_COLS=[TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(VARIABLES.admissiontime, date), timestamp)) AS periodend], OUTPUT_COLS=null})

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

EXECUTING RaiseAccess

AFTER:

Project(groups=[v])

Source(groups=[v])

Project(groups=[])

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

EXECUTING AssignOutputElements

AFTER:

Project(groups=[v], props={PROJECT_COLS=[v.periodend, FORMATTIMESTAMP(v.periodend, 'Z') AS tz], OUTPUT_COLS=[v.periodend, FORMATTIMESTAMP(v.periodend, 'Z') AS tz]})

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

EXECUTING CalculateCost

AFTER:

Project(groups=[v], props={PROJECT_COLS=[v.periodend, FORMATTIMESTAMP(v.periodend, 'Z') AS tz], OUTPUT_COLS=[v.periodend, FORMATTIMESTAMP(v.periodend, 'Z') AS tz], EST_CARDINALITY=1.0, EST_COL_STATS={v.periodend=[1.0, 1.0, 1.0], FORMATTIMESTAMP(v.periodend, 'Z')=[1.0, 1.0, 0.0]}})

Project(groups=[], props={PROJECT_COLS=[TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(VARIABLES.admissiontime, date), timestamp)) AS periodend], OUTPUT_COLS=[TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(VARIABLES.admissiontime, date), timestamp)) AS periodend], EST_CARDINALITY=1.0, EST_COL_STATS={TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(VARIABLES.admissiontime, date), timestamp))=[1.0, 1.0, 0.0]}})

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

EXECUTING PlanSorts

AFTER:

Project(groups=[v])

Source(groups=[v])

Project(groups=[])

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

EXECUTING CollapseSource

AFTER:

Project(groups=[v])

Source(groups=[v])

Project(groups=[])

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

CONVERTING PLAN TREE TO PROCESS TREE

PROCESS PLAN =

ProjectNode(1) output=[v.periodend, FORMATTIMESTAMP(v.periodend, 'Z') AS tz] [v.periodend, FORMATTIMESTAMP(v.periodend, 'Z') AS tz]

ProjectNode(2) output=[v.periodend] [TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(VARIABLES.admissiontime, date), timestamp)) AS periodend]

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

----------------------------------------------------------------------------

OPTIMIZATION COMPLETE:

PROCESSOR PLAN:

ProjectNode(1) output=[v.periodend, FORMATTIMESTAMP(v.periodend, 'Z') AS tz] [v.periodend, FORMATTIMESTAMP(v.periodend, 'Z') AS tz]

ProjectNode(2) output=[v.periodend] [TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(VARIABLES.admissiontime, date), timestamp)) AS periodend]

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

ASSIGNMENT

DECLARE timestamp admissiontime = (SELECT gd.AdmissionTime FROM iziswh.wh_P_GeneralData AS gd WHERE gd.PatientID = 20444 LIMIT 2);

COMMAND STATEMENT:

SELECT v.periodend, FORMATTIMESTAMP(v.periodend, 'Z') AS tz FROM (SELECT TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(VARIABLES.admissiontime, date), timestamp)) AS periodend) AS v;

STATEMENT COMMAND PROCESS PLAN:

ProjectNode(1) output=[v.periodend, FORMATTIMESTAMP(v.periodend, 'Z') AS tz] [v.periodend, FORMATTIMESTAMP(v.periodend, 'Z') AS tz]

ProjectNode(2) output=[v.periodend] [TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(VARIABLES.admissiontime, date), timestamp)) AS periodend]

####################################################

PROCEDURE PLAN :ProcedurePlan:

PROGRAM counter 0

0: ASSIGNMENT INSTRUCTION: admissiontime

1: CREATE CURSOR RESULTSET INSTRUCTION - null

####################################################

----------------------------------------------------------------------------

OPTIMIZATION COMPLETE:

PROCESSOR PLAN:

ProcedurePlan:

PROGRAM counter 0

0: ASSIGNMENT INSTRUCTION: admissiontime

1: CREATE CURSOR RESULTSET INSTRUCTION - null

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

/n============================================================================/nUSER COMMAND:/nSELECT TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)) AS periodend, FORMATTIMESTAMP(gd.AdmissionTime, 'Z') AS tz FROM iziswh.wh_P_GeneralData AS gd WHERE gd.PatientID = 20444 LIMIT 100/n/n----------------------------------------------------------------------------/nOPTIMIZE: /nSELECT TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)) AS periodend, FORMATTIMESTAMP(gd.AdmissionTime, 'Z') AS tz FROM iziswh.wh_P_GeneralData AS gd WHERE gd.PatientID = 20444 LIMIT 100/n/n----------------------------------------------------------------------------/nGENERATE CANONICAL: /nSELECT TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)) AS periodend, FORMATTIMESTAMP(gd.AdmissionTime, 'Z') AS tz FROM iziswh.wh_P_GeneralData AS gd WHERE gd.PatientID = 20444 LIMIT 100/n/nCANONICAL PLAN: /nTupleLimit(groups=[], props={MAX_TUPLE_LIMIT=100})/n Project(groups=[iziswh.wh_P_GeneralData AS gd], props={PROJECT_COLS=[TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)) AS periodend, FORMATTIMESTAMP(gd.AdmissionTime, 'Z') AS tz]})/n Select(groups=[iziswh.wh_P_GeneralData AS gd], props={SELECT_CRITERIA=gd.PatientID = 20444})/n Source(groups=[iziswh.wh_P_GeneralData AS gd])/n/n/n============================================================================/nEXECUTING PlaceAccess/n/nAFTER: /nTupleLimit(groups=[], props={MAX_TUPLE_LIMIT=100})/n Project(groups=[iziswh.wh_P_GeneralData AS gd], props={PROJECT_COLS=[TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)) AS periodend, FORMATTIMESTAMP(gd.AdmissionTime, 'Z') AS tz]})/n Select(groups=[iziswh.wh_P_GeneralData AS gd], props={SELECT_CRITERIA=gd.PatientID = 20444})/n Access(groups=[iziswh.wh_P_GeneralData AS gd], props={SOURCE_HINT=null, MODEL_ID=Schema name=iziswh, nameInSource=null, uuid=mmuuid:43611995-84e0-4550-be48-cdeb054d06ac})/n Source(groups=[iziswh.wh_P_GeneralData AS gd])/n/n/n============================================================================/nEXECUTING PushSelectCriteria/n/nAFTER: /nTupleLimit(groups=[])/n Project(groups=[iziswh.wh_P_GeneralData AS gd], props={PROJECT_COLS=[TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)) AS periodend, FORMATTIMESTAMP(gd.AdmissionTime, 'Z') AS tz]})/n Access(groups=[iziswh.wh_P_GeneralData AS gd], props={SOURCE_HINT=null, MODEL_ID=Schema name=iziswh, nameInSource=null, uuid=mmuuid:43611995-84e0-4550-be48-cdeb054d06ac})/n Select(groups=[iziswh.wh_P_GeneralData AS gd], props={SELECT_CRITERIA=gd.PatientID = 20444})/n Source(groups=[iziswh.wh_P_GeneralData AS gd])/n/n/n============================================================================/nEXECUTING CleanCriteria/n/nAFTER: /nTupleLimit(groups=[], props={MAX_TUPLE_LIMIT=100, OUTPUT_COLS=null})/n Project(groups=[iziswh.wh_P_GeneralData AS gd], props={PROJECT_COLS=[TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)) AS periodend, FORMATTIMESTAMP(gd.AdmissionTime, 'Z') AS tz], OUTPUT_COLS=null})/n Access(groups=[iziswh.wh_P_GeneralData AS gd], props={SOURCE_HINT=null, MODEL_ID=Schema name=iziswh, nameInSource=null, uuid=mmuuid:43611995-84e0-4550-be48-cdeb054d06ac, OUTPUT_COLS=null})/n Select(groups=[iziswh.wh_P_GeneralData AS gd], props={SELECT_CRITERIA=gd.PatientID = 20444, OUTPUT_COLS=null})/n Source(groups=[iziswh.wh_P_GeneralData AS gd], props={OUTPUT_COLS=null})/n/n/n============================================================================/nEXECUTING RaiseAccess/nLOW [Relational Planner] FORMATTIMESTAMP function not supported by source iziswh - FORMATTIMESTAMP(gd.AdmissionTime, 'Z') was not pushed/n/nAFTER: /nTupleLimit(groups=[])/n Project(groups=[iziswh.wh_P_GeneralData AS gd], props={PROJECT_COLS=[TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)) AS periodend, FORMATTIMESTAMP(gd.AdmissionTime, 'Z') AS tz], OUTPUT_COLS=null})/n Access(groups=[iziswh.wh_P_GeneralData AS gd], props={SOURCE_HINT=null, MODEL_ID=Schema name=iziswh, nameInSource=null, uuid=mmuuid:43611995-84e0-4550-be48-cdeb054d06ac, OUTPUT_COLS=null})/n Select(groups=[iziswh.wh_P_GeneralData AS gd])/n Source(groups=[iziswh.wh_P_GeneralData AS gd])/n/n/n============================================================================/nEXECUTING AssignOutputElements/n/nAFTER: /nTupleLimit(groups=[], props={MAX_TUPLE_LIMIT=100, OUTPUT_COLS=[TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)) AS periodend, FORMATTIMESTAMP(gd.AdmissionTime, 'Z') AS tz]})/n Project(groups=[iziswh.wh_P_GeneralData AS gd], props={PROJECT_COLS=[TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)) AS periodend, FORMATTIMESTAMP(gd.AdmissionTime, 'Z') AS tz], OUTPUT_COLS=[TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)) AS periodend, FORMATTIMESTAMP(gd.AdmissionTime, 'Z') AS tz]})/n Access(groups=[iziswh.wh_P_GeneralData AS gd], props={SOURCE_HINT=null, MODEL_ID=Schema name=iziswh, nameInSource=null, uuid=mmuuid:43611995-84e0-4550-be48-cdeb054d06ac, OUTPUT_COLS=[TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)), gd.AdmissionTime]})/n Select(groups=[iziswh.wh_P_GeneralData AS gd], props={SELECT_CRITERIA=gd.PatientID = 20444, OUTPUT_COLS=[gd.AdmissionTime]})/n Source(groups=[iziswh.wh_P_GeneralData AS gd], props={OUTPUT_COLS=[gd.PatientID, gd.AdmissionTime]})/n/n/n============================================================================/nEXECUTING PushLimit/n/nAFTER: /nProject(groups=[iziswh.wh_P_GeneralData AS gd], props={PROJECT_COLS=[TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)) AS periodend, FORMATTIMESTAMP(gd.AdmissionTime, 'Z') AS tz], OUTPUT_COLS=[TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)) AS periodend, FORMATTIMESTAMP(gd.AdmissionTime, 'Z') AS tz], SOURCE_HINT=null})/n Access(groups=[iziswh.wh_P_GeneralData AS gd], props={SOURCE_HINT=null, MODEL_ID=Schema name=iziswh, nameInSource=null, uuid=mmuuid:43611995-84e0-4550-be48-cdeb054d06ac, OUTPUT_COLS=[TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)), gd.AdmissionTime]})/n TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=100, OUTPUT_COLS=[TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)), gd.AdmissionTime]})/n Select(groups=[iziswh.wh_P_GeneralData AS gd], props={SELECT_CRITERIA=gd.PatientID = 20444, OUTPUT_COLS=[gd.AdmissionTime]})/n Source(groups=[iziswh.wh_P_GeneralData AS gd])/n/n/n============================================================================/nEXECUTING CalculateCost/n/nAFTER: /nProject(groups=[iziswh.wh_P_GeneralData AS gd], props={PROJECT_COLS=[TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)) AS periodend, FORMATTIMESTAMP(gd.AdmissionTime, 'Z') AS tz], OUTPUT_COLS=[TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)) AS periodend, FORMATTIMESTAMP(gd.AdmissionTime, 'Z') AS tz], SOURCE_HINT=null, EST_CARDINALITY=1.0, EST_COL_STATS={TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp))=[1.0, 1.0, -1.0], FORMATTIMESTAMP(gd.AdmissionTime, 'Z')=[1.0, 1.0, -1.0]}})/n Access(groups=[iziswh.wh_P_GeneralData AS gd], props={SOURCE_HINT=null, MODEL_ID=Schema name=iziswh, nameInSource=null, uuid=mmuuid:43611995-84e0-4550-be48-cdeb054d06ac, OUTPUT_COLS=[TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)), gd.AdmissionTime], EST_CARDINALITY=1.0, EST_COL_STATS={TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp))=[1.0, 1.0, -1.0], gd.AdmissionTime=[1.0, 1.0, -1.0]}})/n TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=100, OUTPUT_COLS=[TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)), gd.AdmissionTime], EST_CARDINALITY=1.0, EST_COL_STATS={TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp))=[1.0, 1.0, -1.0], gd.AdmissionTime=[1.0, 1.0, -1.0]}})/n Select(groups=[iziswh.wh_P_GeneralData AS gd], props={SELECT_CRITERIA=gd.PatientID = 20444, OUTPUT_COLS=[gd.AdmissionTime], EST_CARDINALITY=1.0, EST_COL_STATS={gd.AdmissionTime=[1.0, 1.0, -1.0]}})/n Source(groups=[iziswh.wh_P_GeneralData AS gd], props={OUTPUT_COLS=[gd.PatientID, gd.AdmissionTime], EST_COL_STATS={gd.PatientID=[45000.0, 45000.0, 0.0], gd.AdmissionTime=[213.0, 22500.0, -1.0]}, EST_CARDINALITY=45000.0})/n/n/n============================================================================/nEXECUTING MergeCriteria/n/nAFTER: /nProject(groups=[iziswh.wh_P_GeneralData AS gd])/n Access(groups=[iziswh.wh_P_GeneralData AS gd])/n TupleLimit(groups=[])/n Select(groups=[iziswh.wh_P_GeneralData AS gd])/n Source(groups=[iziswh.wh_P_GeneralData AS gd])/n/n/n============================================================================/nEXECUTING PlanSorts/n/nAFTER: /nProject(groups=[iziswh.wh_P_GeneralData AS gd])/n Access(groups=[iziswh.wh_P_GeneralData AS gd])/n TupleLimit(groups=[])/n Select(groups=[iziswh.wh_P_GeneralData AS gd])/n Source(groups=[iziswh.wh_P_GeneralData AS gd])/n/n/n============================================================================/nEXECUTING CollapseSource/n/nAFTER: /nProject(groups=[iziswh.wh_P_GeneralData AS gd], props={PROJECT_COLS=[TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)) AS periodend, FORMATTIMESTAMP(gd.AdmissionTime, 'Z') AS tz], OUTPUT_COLS=[TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)) AS periodend, FORMATTIMESTAMP(gd.AdmissionTime, 'Z') AS tz], SOURCE_HINT=null, EST_CARDINALITY=1.0, EST_COL_STATS={TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp))=[1.0, 1.0, -1.0], FORMATTIMESTAMP(gd.AdmissionTime, 'Z')=[1.0, 1.0, -1.0]}})/n Access(groups=[iziswh.wh_P_GeneralData AS gd], props={SOURCE_HINT=null, MODEL_ID=Schema name=iziswh, nameInSource=null, uuid=mmuuid:43611995-84e0-4550-be48-cdeb054d06ac, OUTPUT_COLS=[TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)), gd.AdmissionTime], EST_CARDINALITY=1.0, EST_COL_STATS={TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp))=[1.0, 1.0, -1.0], gd.AdmissionTime=[1.0, 1.0, -1.0]}, ATOMIC_REQUEST=SELECT TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)), gd.AdmissionTime FROM iziswh.wh_P_GeneralData AS gd WHERE gd.PatientID = 20444 LIMIT 100})/n/n/n============================================================================/nCONVERTING PLAN TREE TO PROCESS TREE/n/nPROCESS PLAN = /nProjectNode(0) output=[TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)) AS periodend, FORMATTIMESTAMP(gd.AdmissionTime, 'Z') AS tz] [TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)) AS periodend, FORMATTIMESTAMP(gd.AdmissionTime, 'Z') AS tz]/n AccessNode(1) output=[TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)), gd.AdmissionTime] SELECT TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(g_0.AdmissionTime, date), timestamp)) AS c_0, g_0.AdmissionTime AS c_1 FROM iziswh.wh_P_GeneralData AS g_0 WHERE g_0.PatientID = 20444 LIMIT 100/n/n============================================================================/n/n----------------------------------------------------------------------------/nOPTIMIZATION COMPLETE:/nPROCESSOR PLAN:/nProjectNode(0) output=[TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)) AS periodend, FORMATTIMESTAMP(gd.AdmissionTime, 'Z') AS tz] [TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)) AS periodend, FORMATTIMESTAMP(gd.AdmissionTime, 'Z') AS tz]/n AccessNode(1) output=[TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(gd.AdmissionTime, date), timestamp)), gd.AdmissionTime] SELECT TIMESTAMPADD(SQL_TSI_HOUR, 5, convert(convert(g_0.AdmissionTime, date), timestamp)) AS c_0, g_0.AdmissionTime AS c_1 FROM iziswh.wh_P_GeneralData AS g_0 WHERE g_0.PatientID = 20444 LIMIT 100/n/n============================================================================/n

1. Re: Problem with DST within virtual procedure

shawkins Apr 27, 2017 11:58 AM (in response to gadeyne.bram)

What is the source type? And what are the Teiid client/server timezones?
Actions
2. Re: Problem with DST within virtual procedure

gadeyne.bram Apr 28, 2017 10:17 AM (in response to shawkins)

Hi Steven,

The source is a Sybase server.

I believe that both server and client currently use the same timezones Which is CEST (UTC +2). They are synched with an internal time server.
Actions
3. Re: Problem with DST within virtual procedure

shawkins Apr 28, 2017 4:25 PM (in response to gadeyne.bram)

To recap you have something like:

2009-10-25 00:00:00.0

Then add 5 hours and get:

2009-10-25 04:00:00.0

Given the DST handling, that seems correct to me. Perhaps the Sybase server is not DST aware.
Actions

Go to original post