[Bug report] MysqlTypeConverter cannot handle the datetime data type.

[hzxiongyinke]

Version

main branch

Describe what's wrong

Gravitino with mysql catalog, MysqlTypeConverter cannot handle the datetime data type.

Error message and/or stacktrace

How to reproduce

mysql> CREATE TABLE datetime_table ( id INT PRIMARY KEY, date_time DATETIME );

Additional context

I want to participate in fixing this bug. Can anyone give me some advice?

[FANNG1]

@Clearvive @mchades could you provide some advise?

[Clearvive]

At present, the date time type is not supported and requires issue support. Can you do this @hzxiongyinke ?

[Clearvive]

@hzxiongyinke Or you can check if we can use our timestamp type instead

[mchades]

Can you describe the differences between DATETIME type and TIMESTAMP type of MySQL? @hzxiongyinke

[hzxiongyinke]

Can you describe the differences between DATETIME type and TIMESTAMP type of MySQL? @hzxiongyinke

For differences between MySQL DATETIME and TIMESTAMP types, refer to https://dev.mysql.com/doc/refman/8.0/en/datetime.html.
By the way, what rules does the data type design of Gravitino follow?

[mchades]

Can you describe the differences between DATETIME type and TIMESTAMP type of MySQL? @hzxiongyinke

For differences between MySQL DATETIME and TIMESTAMP types, refer to https://dev.mysql.com/doc/refman/8.0/en/datetime.html. By the way, what rules does the data type design of Gravitino follow?

---

Before designing the Gravitino type system, we referenced the type systems of other open-source engines such as Substrait, Spark, Hive, and Trino.

The design goal of the Gravitino type system is to provide a unified semantic and support user-defined types. As it stands, we are striving to cover the most commonly used types in data definition.

Regarding the timestamp type of Gravitino, as described in the documentation:

Timestamp type indicates a timestamp without timezone
Timestamp with timezone type indicates a timestamp with timezone

We should be mindful of the differences between datetime and timestamp in MySQL:

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)

Therefore, based on the principle of unified semantics, I believe that we should:

• map datetime of MySQL to timestamp of Gravitino
• map timestamp of MySQL to timestamp_tz type of Gravitino.

Obviously, our current mapping of MySQL timestamp type conversion is not appropriate.

However, my main concern is: Gravitino's timestamp type and MySQL's timestamp type have the same name but different semantics. Will this potentially confuse users?

What do you think? @jerryshao @FANNG1 @Clearvive

[FANNG1]

I think Gravitino should provide a clear and general type definition, the underlying catalog should follow this if there is conflict. and we should add the document to describe this.

• clear means the user could easily know the type.
• general means most systems follow this.