Microseconds precision is not respected in mysql connector

[pratyakshsharma]

Timestamp type support was recently added in mysql connector (https://github.com/prestodb/presto/pull/21937). However, it seems to only support milliseconds precision by default. We need microseconds precision as well.

Your Environment

• Presto version used: Latest master
• Storage (HDFS/S3/GCS..):
• Data source and connector used: mysql connector
• Deployment (Cloud or On-prem): local machine
• Pastebin link to the complete debug logs:

Expected Behavior

Microseconds precision should be respected and should be visible when queried after inserting.

Current Behavior

I inserted a timestamp value with microseconds precision. The insert was successful. However, the select query returns values till milliseconds precision only.

Possible Solution

Steps to Reproduce

1. create a table using

   CREATE TABLE test_timestamp_oss (x timestamp);

2. try inserting a timestamp value in some mysql table via presto-mysql connector

   INSERT INTO test_timestamp_oss VALUES (timestamp '2018-07-02 11:13:45.123456');

Now try to select from the table -

select * from test_timestamp_oss;

Screenshots (if appropriate)

Context

The issue results in incorrect values getting returned from mysql connector.

[pratyakshsharma]

cc @hantangwangd @bentonyjoe191

[hantangwangd]

Seems that's not just a MYSQL connector issue.

Currently we do not support declaring columns of timestamp type with microseconds precision. For example, when we create a table with columns of timestamp type on any connector, we will always get the type of timestamp with milliseconds.

Furthermore, the expression timestamp '2018-07-02 11:13:45.123456' will always be analyzed to a timestamp type with milliseconds precision in analyzing phase. So even we just execute the following query:

select timestamp '2018-07-02 11:13:45.123456';

We will get the result as follows:

          _col0          
-------------------------
 2018-07-02 11:13:45.123 
(1 row)

I guess initially the TIMESTAMP_MICROSECONDS is introduced for reading existing datas from external storage in some specific connector with specific file format? cc: @tdcmeehan @ZacBlanco

[pratyakshsharma]

I was thinking inline to what you are saying. Looks like TIMESTAMP_MICROSECONDS is not supported as the data type itself currently.

[hantangwangd]

Yes, it looks like so.