Skip to content

Expression Translation

Jump to bottom
Harish Butani edited this page Nov 11, 2020 · 7 revisions

Datatypes

Literals

Casts

translate Cast, PromotePrecision and CheckOverflow *

  • for PromotePrecision just return child translation.
  • for CheckOverflow
    • if nullOnOverflow is true add a case check
    • if nullOnOverflow is false: do nothing? translated oExpr will throw
  val epochTS = {
    // osql"to_timestamp_tz('1970-01-01 00:00:00 00:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM')"
    osql"from_tz(to_timestamp('1970-01-01', 'YYYY-MM-DD'), 'UTC')"
  }
  val epochDt = osql"date '1970-01-01'"

  val epochTSAtSessionTZ = osql"to_timestamp('1970-01-01', 'YYYY-MM-DD')"

  val true_bool_TS =
    osql"from_tz(to_timestamp('1970-01-01', 'YYYY-MM-DD'), 'UTC') + interval '0.001' second(0,3)"

dtToTimestamp

  • if there is no catalystExpr#zoneId then generate cast(${oraE} as timestamp)
  • else generate cast(${oraE} as timestamp) at time zone ${zoneOE}

timestampToDt

  • if there is no catalystExpr#zoneId then
    • cast input to a timestamp and set its timezone; then cast the result to a date
    • Translation expression: cast(from_tz(cast({oraE} as timestamp), {zoneOE}) as date)
  • Otherwise translation expression is cast({oraE} as date)

epochToTimestamp

Translation logic is:

millisToInterval = numtodsinterval({oraE}/1000, 'SECOND')
millisToIntervalWithTZOffset = {millisToInterval} + {epochTS} - {epochTSAtSessionTZ}
result = {epochTSAtSessionTZ} + ({millisToIntervalWitTZOffset})

For example for oraE = 1603425037802, sql is:

  to_timestamp('1970-01-01', 'YYYY-MM-DD') +
       (numtodsinterval(1603425037802/1000, 'SECOND') +
        from_tz(to_timestamp('1970-01-01', 'YYYY-MM-DD')), 'UTC') -
        to_timestamp('1970-01-01', 'YYYY-MM-DD')
       )

epochToDate

Translation logic is:

millisToInterval = numtodsinterval({oraE}/1000, 'SECOND')
millisToIntervalWithTZOffset = {millisToInterval} + {epochTS} - {epochTSAtSessionTZ}
epoch_ts = {epochTSAtSessionTZ} + {millisToIntervalWitTZOffset}
result = trunc({epoch_ts}, 'DD')

For example for oraE = 1603425037802, sql is:

  trunc(
    to_timestamp('1970-01-01', 'YYYY-MM-DD') +
       (numtodsinterval(1603425037802/1000, 'SECOND') +
        from_tz(to_timestamp('1970-01-01', 'YYYY-MM-DD')), 'UTC') -
        to_timestamp('1970-01-01', 'YYYY-MM-DD')
       ),
     'DD'
    )

timestampToEpoch

Translation logic is:

  // using ora date arithmetic: ora_ts - ora_ts -> ora_interval
  days = extract(day from ({oraE} - {epochTS}))246060
  hours = extract(hour from ({oraE} - {epochTS}))6060
  mins = extract(minute from ({oraE} - {epochTS}))6060
  secs = extract(second from ({oraE} - {epochTS}))6060
  result = ({days} + {hours} + {mins} + {secs})1000

For example for oraE = systimestamp, sql is:

  extract(day from (systimestamp - from_tz(to_timestamp('1970-01-01', 'YYYY-MM-DD')), 'UTC')))246060 +
 extract(hour from (systimestamp - from_tz(to_timestamp('1970-01-01', 'YYYY-MM-DD')), 'UTC')))6060 +
 extract(minute from (systimestamp - from_tz(to_timestamp('1970-01-01', 'YYYY-MM-DD')), 'UTC')))60 +
 extract(second from (systimestamp - from_tz(to_timestamp('1970-01-01', 'YYYY-MM-DD')), 'UTC')))
)1000

dateToEpoch

Translation logic is:

  trunc_to_days = trunc(sysdate, 'DD')
  // using ora date arithmetic: ora_date - ora_ts -> ora_interval
  interval_from_epoch = trunc_to_days - epoch_ts
  num_hours = extract(day from interval_from_epoch)24 +
              extract(hour from interval_from_epoch)
  result = num_hours60601000

For example, for sysdate:

  (extract(day from(trunc(sysdate, 'DD') - from_tz(to_timestamp('1970-01-01', 'YYYY-MM-DD')), 'UTC')))24 +
   extract(hour from(trunc(sysdate, 'DD') - from_tz(to_timestamp('1970-01-01', 'YYYY-MM-DD')), 'UTC')))
  )60601000

NumericCasting

for widening Cast: do nothing, return 'childOE'

for narrowing Cast: use the following sql expression template:

case when {childOE} > {toDT.MinValue} and {childOE} < {toDT.MaxValue}
         then cast({childOE} as {toDT})
     else null
end

StringCasting

from string:

to string:

BooleanCasting

from boolean:

to boolean:

DateCasting

from date:

  • to numeric:
    • In Spark: num_of_days since epoch.
    • translate to: {oraE} - {epochDt}. Based on oracle's date arithmetic(oraDt - oraDt -> number) this represents the number of days since start of epoch.
  • to string:
  • to timestamp:
    • In Spark: DatetimeUtils.daysToMicros(d, zoneId)
      • Converts days since 1970-01-01 at the given zone ID to microseconds since 1970-01-01 00:00:00Z.
    • translate to: cast({oraE} as timestamp) with additional at time zone {castE.timeZoneId}. See dtToTimestamp() method.
  • to boolean:
    • In Spark: null
    • translate to: null

to date:

  • from numeric:
    • In Spark it is undefined
    • translate to: {epochDt} + {oraE} Based on oracle's date arithmetic this represents the date that is {oraE} days from epoch.
  • from string:
  • from timestamp:
    • In Spark: convert timestamp at given tz to date
    • translate to: cast({oraE} as date); if {castE.timeZoneId} is specified first convert to timestamp in timeZone. See timestampToDt()
  • from boolean:
    • In Spark it is undefined
    • we throw during translation.

TimestampCasting

from timestamp:

  • to numeric:
  • to string: Sames as Timestamp -> String in StringCasting
  • to date: Same as Timestamp -> Date in DateCasting
  • to boolean:
    • In Spark: millis_since_epoch != 0 translate to: timestampToEpoch({oraE}) != 0. See timestampToEpoch()

to timestamp:

  • from numeric:
  • from string: same as String -> Date in StringCasting
  • from date:
    • In Spark: convert timestamp at given tz to date
    • translate to: See timestampToDt()
  • from boolean:
    • In Spark: true is interpreted as 1L millis_since_epoch, and false is 0L millis_since_epoch.
    • translate to: case when {oraE} then ${true_bool_TS} else ${epochTS} end

Arithmetic

Named

Conditional

Nulls

Predicates

Subquery

Clone this wiki locally