[Native] - Cast from varchar to timestamp with time zone not supported in velox

[mknegi]

It seems currently the cast from varchar to timestamp with time zone not yet supported in velox.

Expected Behavior

Following query is supposed to succeed but it currently fails against velox worker.

SELECT CAST(orderdate AS TIMESTAMP WITH TIME ZONE) FROM orders limit 1;
This query is supposed to return the output like:
        _col0
--------------------------------------
 1993-10-31 00:00:00.000 Asia/Kolkata
(1 row)

Current Behavior

presto:tpch> SELECT CAST(orderdate AS TIMESTAMP WITH TIME ZONE) FROM orders limit 1;

Query 20231106_142314_00003_css8j, FAILED, 1 node
Splits: 9 total, 0 done (0.00%)
[Latency: client-side: 370ms, server-side: 351ms] [15K rows, 5.94KB] [42.7K rows/s, 16.9KB/s]

Query 20231106_142314_00003_css8j failed: Cannot cast VARCHAR to TIMESTAMP WITH TIME ZONE. cast((orderdate) as TIMESTAMP WITH TIME ZONE)

[aditi-pandit]

This is also needed for querying system.runtime.tasks table in https://github.com/prestodb/presto/pull/21416

[ashokku2022]

@svm1 is looking into it @aditi-pandit

[ethanyzhang]

Hi @svm1, do you have an update on this? CC @ashokku2022

[svm1]

Implementation has been completed. Opened a PR - https://github.com/facebookincubator/velox/pull/8823

[aditi-pandit]

@svm has fixed the issue. But Timestamp with timezone is disabled from Prestissimo because of the limitations of logical types.

presto:tiny> SELECT CAST(orderdate AS TIMESTAMP WITH TIME ZONE) FROM orders limit 1;
Query 20240916_105640_00008_b3mvr failed: Timestamp with Timezone type is not supported in Prestissimo
java.lang.IllegalStateException: Timestamp with Timezone type is not supported in Prestissimo
    at com.google.common.base.Preconditions.checkState(Preconditions.java:512)
    at com.facebook.presto.sql.planner.sanity.CheckUnsupportedPrestissimoTypes$Visitor.lambda$visitPlan$1(CheckUnsupportedPrestissimoTypes.java:81)
    at java.util.Optional.ifPresent(Optional.java:159)
    at com.facebook.presto.sql.planner.sanity.CheckUnsupportedPrestissimoTypes$Visitor.visitPlan(CheckUnsupportedPrestissimoTypes.java:81)
    at com.facebook.presto.sql.planner.sanity.CheckUnsupportedPrestissimoTypes$Visitor.visitPlan(CheckUnsupportedPrestissimoTypes.java:67)
    at com.facebook.presto.spi.plan.PlanVisitor.visitOutput(PlanVisitor.java:25)
    at com.facebook.presto.spi.plan.OutputNode.accept(OutputNode.java:98)
    at com.facebook.presto.sql.planner.sanity.CheckUnsupportedPrestissimoTypes.validate(CheckUnsupportedPrestissimoTypes.java:64)
    at com.facebook.presto.sql.planner.sanity.PlanChecker.lambda$validateIntermediatePlan$1(PlanChecker.java:85)
    at com.google.common.collect.ImmutableList.forEach(ImmutableList.java:422)
    at com.facebook.presto.sql.planner.sanity.PlanChecker.validateIntermediatePlan(PlanChecker.java:85)
    at com.facebook.presto.sql.Optimizer.validateAndOptimizePlan(Optimizer.java:104)
    at com.facebook.presto.execution.SqlQueryExecution.lambda$createLogicalPlanAndOptimize$3(SqlQueryExecution.java:557)
    at com.facebook.presto.common.RuntimeStats.profileNanos(RuntimeStats.java:136)
    at com.facebook.presto.execution.SqlQueryExecution.createLogicalPlanAndOptimize(SqlQueryExecution.java:555)
    at com.facebook.presto.execution.SqlQueryExecution.start(SqlQueryExecution.java:464)
    at com.facebook.presto.$gen.Presto_null__testversion____20240916_091025_1814.run(Unknown Source)
    at com.facebook.presto.execution.SqlQueryManager.createQuery(SqlQueryManager.java:319)
    at com.facebook.presto.dispatcher.LocalDispatchQuery.lambda$startExecution$8(LocalDispatchQuery.java:213)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)

@ethanyzhang : Lets get to this when timestamp with timezone is re-enabled in Velox/Prestissimo.