This is an automated email from the ASF dual-hosted git repository.
zykkk pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 7e01f074e2 [improvement](jdbc mysql) support auto calculate the
precision of timestamp/datetime (#20788)
7e01f074e2 is described below
commit 7e01f074e2d0f0e4ad2618beed5796c94a1d241c
Author: zy-kkk <[email protected]>
AuthorDate: Tue Jun 20 10:39:34 2023 +0800
[improvement](jdbc mysql) support auto calculate the precision of
timestamp/datetime (#20788)
---
.../docker-compose/mysql/init/03-create-table.sql | 10 ++++++++++
.../docker-compose/mysql/init/04-insert.sql | 5 +++++
.../apache/doris/external/jdbc/JdbcMySQLClient.java | 20 +++++++++++++++-----
.../doris/external/jdbc/JdbcPostgreSQLClient.java | 10 +++++-----
.../data/jdbc_catalog_p0/test_mysql_jdbc_catalog.out | 3 +++
.../jdbc_catalog_p0/test_mysql_jdbc_catalog.groovy | 2 ++
6 files changed, 40 insertions(+), 10 deletions(-)
diff --git a/docker/thirdparties/docker-compose/mysql/init/03-create-table.sql
b/docker/thirdparties/docker-compose/mysql/init/03-create-table.sql
index 9fd111424d..9845db17bf 100644
--- a/docker/thirdparties/docker-compose/mysql/init/03-create-table.sql
+++ b/docker/thirdparties/docker-compose/mysql/init/03-create-table.sql
@@ -289,4 +289,14 @@ CREATE TABLE `doris_test`.`auto_default_t` (
`name` varchar(64) DEFAULT NULL,
`dt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
+) engine=innodb charset=utf8;
+
+create table doris_test.dt (
+ `timestamp0` timestamp(0),
+ `timestamp1` timestamp(1),
+ `timestamp2` timestamp(2),
+ `timestamp3` timestamp(3),
+ `timestamp4` timestamp(4),
+ `timestamp5` timestamp(5),
+ `timestamp6` timestamp(6)
) engine=innodb charset=utf8;
\ No newline at end of file
diff --git a/docker/thirdparties/docker-compose/mysql/init/04-insert.sql
b/docker/thirdparties/docker-compose/mysql/init/04-insert.sql
index 35a322134c..c164bb3b82 100644
--- a/docker/thirdparties/docker-compose/mysql/init/04-insert.sql
+++ b/docker/thirdparties/docker-compose/mysql/init/04-insert.sql
@@ -1138,3 +1138,8 @@ INSERT INTO doris_test.all_types VALUES
(203, 303, 403, 503, 603, 7.14159, 8.1415926, 9.141592, false, null, -402,
2017, -602, -902, -1102, '2012-11-02', null, '2013-10-27 08:11:18.3456712',
-5.14145000001, -6.1400000000001, -7.140000000001, 'row3', 'line3',
'09:11:09.56782346', 'text3', X'E86F6C6C6F20576F726C67', '{"name": "ChenQi",
"age": 24, "city": "ChongQing"}',
'Option2', b'101111', X'58676C6C6F', null, 'Value1');
+
+INSERT INTO doris_test.dt (`timestamp0`, `timestamp1`, `timestamp2`,
`timestamp3`, `timestamp4`, `timestamp5`, `timestamp6`)
+VALUES ('2023-06-17 10:00:00', '2023-06-17 10:00:01.1', '2023-06-17
10:00:02.22', '2023-06-17 10:00:03.333',
+ '2023-06-17 10:00:04.4444', '2023-06-17 10:00:05.55555', '2023-06-17
10:00:06.666666');
+
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcMySQLClient.java
b/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcMySQLClient.java
index 1afce4546a..5c5f33f30e 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcMySQLClient.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcMySQLClient.java
@@ -242,10 +242,11 @@ public class JdbcMySQLClient extends JdbcClient {
return Type.BIGINT;
case "BIGINT":
return Type.LARGEINT;
- case "DECIMAL":
+ case "DECIMAL": {
int precision = fieldSchema.getColumnSize() + 1;
int scale = fieldSchema.getDecimalDigits();
return createDecimalOrStringType(precision, scale);
+ }
case "DOUBLE":
// As of MySQL 8.0.17, the UNSIGNED attribute is deprecated
// for columns of type FLOAT, DOUBLE, and DECIMAL (and any
synonyms)
@@ -278,19 +279,28 @@ public class JdbcMySQLClient extends JdbcClient {
return ScalarType.createDateV2Type();
case "TIMESTAMP":
case "DATETIME":
- case "DATETIMEV2": // for jdbc catalog connecting Doris database
+ // for jdbc catalog connecting Doris database
+ case "DATETIMEV2": {
// mysql can support microsecond
- // todo(gaoxin): Get real precision of DATETIMEV2
- return ScalarType.createDatetimeV2Type(JDBC_DATETIME_SCALE);
+ // use columnSize to calculate the precision of
timestamp/datetime
+ int columnSize = fieldSchema.getColumnSize();
+ int scale = columnSize > 19 ? columnSize - 20 : 0;
+ if (scale > 6) {
+ scale = 6;
+ }
+ return ScalarType.createDatetimeV2Type(scale);
+ }
case "FLOAT":
return Type.FLOAT;
case "DOUBLE":
return Type.DOUBLE;
case "DECIMAL":
- case "DECIMALV3": // for jdbc catalog connecting Doris database
+ // for jdbc catalog connecting Doris database
+ case "DECIMALV3": {
int precision = fieldSchema.getColumnSize();
int scale = fieldSchema.getDecimalDigits();
return createDecimalOrStringType(precision, scale);
+ }
case "CHAR":
ScalarType charType =
ScalarType.createType(PrimitiveType.CHAR);
charType.setLength(fieldSchema.columnSize);
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcPostgreSQLClient.java
b/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcPostgreSQLClient.java
index 36f624372c..0db3eeaef0 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcPostgreSQLClient.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcPostgreSQLClient.java
@@ -65,14 +65,14 @@ public class JdbcPostgreSQLClient extends JdbcClient {
charType.setLength(fieldSchema.columnSize);
return charType;
case "timestamp":
- case "timestamptz":
+ case "timestamptz": {
// postgres can support microsecond
int scale = fieldSchema.getDecimalDigits();
- if (scale < 6) {
- return ScalarType.createDatetimeV2Type(scale);
- } else {
- return
ScalarType.createDatetimeV2Type(JDBC_DATETIME_SCALE);
+ if (scale > 6) {
+ scale = 6;
}
+ return ScalarType.createDatetimeV2Type(scale);
+ }
case "date":
return ScalarType.createDateV2Type();
case "bool":
diff --git a/regression-test/data/jdbc_catalog_p0/test_mysql_jdbc_catalog.out
b/regression-test/data/jdbc_catalog_p0/test_mysql_jdbc_catalog.out
index 8a6a8c3dff..e1a5431b00 100644
--- a/regression-test/data/jdbc_catalog_p0/test_mysql_jdbc_catalog.out
+++ b/regression-test/data/jdbc_catalog_p0/test_mysql_jdbc_catalog.out
@@ -249,6 +249,9 @@ VIEW_TABLE_USAGE
-- !auto_default_t --
0
+-- !dt --
+2023-06-17T10:00 2023-06-17T10:00:01.100 2023-06-17T10:00:02.220
2023-06-17T10:00:03.333 2023-06-17T10:00:04.444400
2023-06-17T10:00:05.555550 2023-06-17T10:00:06.666666
+
-- !test_insert1 --
doris1 18
diff --git
a/regression-test/suites/jdbc_catalog_p0/test_mysql_jdbc_catalog.groovy
b/regression-test/suites/jdbc_catalog_p0/test_mysql_jdbc_catalog.groovy
index c7ac3b5d40..81373b502d 100644
--- a/regression-test/suites/jdbc_catalog_p0/test_mysql_jdbc_catalog.groovy
+++ b/regression-test/suites/jdbc_catalog_p0/test_mysql_jdbc_catalog.groovy
@@ -49,6 +49,7 @@ suite("test_mysql_jdbc_catalog", "p0") {
String test_insert = "test_insert";
String test_insert2 = "test_insert2";
String auto_default_t = "auto_default_t";
+ String dt = "dt";
sql """drop catalog if exists ${catalog_name} """
@@ -101,6 +102,7 @@ suite("test_mysql_jdbc_catalog", "p0") {
order_qt_ex_tb20 """ select * from ${ex_tb20} order by
decimal_normal; """
order_qt_information_schema """ show tables from information_schema;
"""
order_qt_auto_default_t """insert into ${auto_default_t}(name)
values('a'); """
+ order_qt_dt """select * from ${dt}; """
// test insert
String uuid1 = UUID.randomUUID().toString();
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
