Support data type timestamp(3) with time zone

[shawnzhu]

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

Unsupported column type: timestamp(3) with time zone

[shawnzhu]

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

Unsupported column type: timestamp(3)

[shawnzhu]

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]

Need to fix this for a current task

[shawnzhu]

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 :tada: so the precision adapting logic is much simpler.

[shawnzhu]

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]

62 will fix the ☝️ error but got new problem described under https://github.com/IBM/presto-db2/pull/62#issuecomment-797864165

[shawnzhu]

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]

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]

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.