IBM/trino-db2

Support data type timestamp(3) with time zone

shawnzhu opened this issue · 9 comments

shawnzhu commented

When using the function from_iso8601_timestamp(), it reports error:

Unsupported column type: timestamp(3) with time zone
shawnzhu commented

And when using CAST(xxx AS timestamp), it reports error as well:

Unsupported column type: timestamp(3)
shawnzhu commented

This might be caused by recent feature change in upstream where it supports precision on data type TIMESTAMP which cause incompatibility with Db2's type TIMESTAMP.

The fix can be borrowed from postgresql connector.

shawnzhu commented

Need to fix this for a current task

shawnzhu commented

Two problems so far:

  1. map existing timestamp data type when read. Given table contains a column named conference_start with type TIMESTAMP, in Db2, the default precision is 6, so it should read the column conference_start with type TIMESTAMP(6) instead of TIMESTAMP(3). the number 3 comes from trino where it's the default precision for type TIMESTAMP: https://github.com/trinodb/trino/blob/dd0f786b88297e8a538c67423a5f322800577c9c/presto-spi/src/main/java/io/prestosql/spi/type/TimestampType.java#L31
  2. keep the timestamp precision when write. if no precision given, use default precision 6. see Datetime values - IBM Db2 on Cloud Since Db2 10, the maximum precision of timestamp is the same as that of trino 🎉 so the precision adapting logic is much simpler.
shawnzhu commented

#60 will fix the 1) problem from ☝️ comment. See:

presto:kzhu_test> desc warehouse.kzhu_test.test_dt;
   Column   |     Type     | Extra | Comment 
------------+--------------+-------+---------
 conf_start | timestamp(6) |       |         
 conf_end   | timestamp(3) |       |         
(2 rows)

Query 20210313_023359_00005_pdzd3, FINISHED, 1 node
Splits: 19 total, 19 done (100.00%)
1.37 [2 rows, 152B] [1 rows/s, 111B/s]

However, when trying to create a table contains a column in data type timestamp with precision failed:

presto:kzhu_test> create table warehouse.kzhu_test.test_dt_2 AS select * from warehouse.kzhu_test.test_dt with no data;
Query 20210313_023537_00006_pdzd3 failed: Unsupported column type: timestamp(6)

Need to tweak the method toWriteMapping()

shawnzhu commented

#62 will fix the ☝️ error but got new problem described under #62 (comment)

shawnzhu commented

Full stacktrace of ☝️ exception:

2021-03-14T02:08:54.402Z        ERROR   SplitRunner-8-106       io.prestosql.execution.executor.TaskExecutor    Error processing Split 20210314_020853_00002_g6v7g.1.0-0 io.prestosql.plugin.jdbc.JdbcSplit@788becc6 (start = 1062339.353571, wall = 375 ms, cpu = 0 ms, wait = 0 ms, calls = 1): JDBC_ERROR: [jcc][t4][1092][13956][4.25.13] Invalid data conversion: Wrong result column type for requested conversion. ERRORCODE=-4461, SQLSTATE=42815
io.prestosql.spi.PrestoException: [jcc][t4][1092][13956][4.25.13] Invalid data conversion: Wrong result column type for requested conversion. ERRORCODE=-4461, SQLSTATE=42815
        at io.prestosql.plugin.jdbc.JdbcRecordCursor.handleSqlException(JdbcRecordCursor.java:250)
        at io.prestosql.plugin.jdbc.JdbcRecordCursor.getLong(JdbcRecordCursor.java:163)
        at io.prestosql.spi.connector.RecordPageSource.getNextPage(RecordPageSource.java:106)
        at io.prestosql.operator.TableScanOperator.getOutput(TableScanOperator.java:301)
        at io.prestosql.operator.Driver.processInternal(Driver.java:379)
        at io.prestosql.operator.Driver.lambda$processFor$8(Driver.java:283)
        at io.prestosql.operator.Driver.tryWithLock(Driver.java:675)
        at io.prestosql.operator.Driver.processFor(Driver.java:276)
        at io.prestosql.execution.SqlTaskExecution$DriverSplitRunner.processFor(SqlTaskExecution.java:1076)
        at io.prestosql.execution.executor.PrioritizedSplitRunner.process(PrioritizedSplitRunner.java:163)
        at io.prestosql.execution.executor.TaskExecutor$TaskRunner.run(TaskExecutor.java:484)
        at io.prestosql.$gen.Presto_347____20210314_020416_2.run(Unknown Source)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
        at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: [jcc][t4][1092][13956][4.25.13] Invalid data conversion: Wrong result column type for requested conversion. ERRORCODE=-4461, SQLSTATE=42815
        at com.ibm.db2.jcc.am.b6.a(b6.java:810)
        at com.ibm.db2.jcc.am.b6.a(b6.java:66)
        at com.ibm.db2.jcc.am.b6.a(b6.java:133)
        at com.ibm.db2.jcc.am.ResultSet.getObject(ResultSet.java:1961)
        at io.prestosql.plugin.jdbc.StandardColumnMappings.lambda$timestampReadFunction$23(StandardColumnMappings.java:454)
        at io.prestosql.plugin.jdbc.JdbcRecordCursor.getLong(JdbcRecordCursor.java:160)
        ... 13 more
shawnzhu commented

One more thing I learned is it also needs to support both TIMESTAMP (or called TIMESTAMP WITHOUT TIME ZONE) and TIMESTAMP WITH TIMEZONE type.

shawnzhu commented

Fixed by #62

I've tested read/write table with column in data type timestamp(x).

It will takes time to figure out how to support column with type TIMESTAMP WITH TIME ZONE in Db2.