You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by mo...@apache.org on 2023/01/23 04:11:57 UTC
[doris] 08/11: [fix](oracle catalog) oracle catalog support `TIMESTAMP` dateType of oracle (#16113)
This is an automated email from the ASF dual-hosted git repository.
morningman pushed a commit to branch branch-1.2-lts
in repository https://gitbox.apache.org/repos/asf/doris.git
commit e35ef74f72b0a1fbbcb8267f2859157b50b36808
Author: Tiewei Fang <43...@users.noreply.github.com>
AuthorDate: Fri Jan 20 14:47:58 2023 +0800
[fix](oracle catalog) oracle catalog support `TIMESTAMP` dateType of oracle (#16113)
`TIMESTAMP` dateType of Oracle will map to `DateTime` dateType of Doris
---
be/src/vec/exec/vjdbc_connector.cpp | 5 ++++-
.../docker-compose/oracle/init/03-create-table.sql | 10 ++++++++++
.../docker-compose/oracle/init/04-insert.sql | 8 ++++++++
.../java/org/apache/doris/external/jdbc/JdbcClient.java | 5 +++++
fe/java-udf/pom.xml | 6 ++++++
.../src/main/java/org/apache/doris/udf/JdbcExecutor.java | 16 +++++++++++-----
.../data/jdbc_catalog_p0/test_oracle_jdbc_catalog.out | 12 +++++++++++-
.../jdbc_catalog_p0/test_oracle_jdbc_catalog.groovy | 1 +
8 files changed, 56 insertions(+), 7 deletions(-)
diff --git a/be/src/vec/exec/vjdbc_connector.cpp b/be/src/vec/exec/vjdbc_connector.cpp
index b7c2b388be..5752e7ab19 100644
--- a/be/src/vec/exec/vjdbc_connector.cpp
+++ b/be/src/vec/exec/vjdbc_connector.cpp
@@ -299,7 +299,8 @@ Status JdbcConnector::_check_type(SlotDescriptor* slot_desc, const std::string&
case TYPE_DATETIME:
case TYPE_DATETIMEV2: {
if (type_str != "java.sql.Timestamp" && type_str != "java.time.LocalDateTime" &&
- type_str != "java.sql.Date" && type_str != "java.time.LocalDate") {
+ type_str != "java.sql.Date" && type_str != "java.time.LocalDate" &&
+ type_str != "oracle.sql.TIMESTAMP") {
return Status::InternalError(error_msg);
}
break;
@@ -525,11 +526,13 @@ Status JdbcConnector::_insert_column_data(JNIEnv* env, jobject jobj, const TypeD
}
case TYPE_DATETIME: {
int64_t num = _jobject_to_datetime(env, jobj, false);
+ RETURN_IF_ERROR(JniUtil::GetJniExceptionMsg(env));
reinterpret_cast<vectorized::ColumnVector<vectorized::Int64>*>(col_ptr)->insert_value(num);
break;
}
case TYPE_DATETIMEV2: {
int64_t num = _jobject_to_datetime(env, jobj, true);
+ RETURN_IF_ERROR(JniUtil::GetJniExceptionMsg(env));
uint64_t num2 = static_cast<uint64_t>(num);
reinterpret_cast<vectorized::ColumnVector<vectorized::UInt64>*>(col_ptr)->insert_value(
num2);
diff --git a/docker/thirdparties/docker-compose/oracle/init/03-create-table.sql b/docker/thirdparties/docker-compose/oracle/init/03-create-table.sql
index efd648dff6..d5dd8cf1c6 100644
--- a/docker/thirdparties/docker-compose/oracle/init/03-create-table.sql
+++ b/docker/thirdparties/docker-compose/oracle/init/03-create-table.sql
@@ -68,3 +68,13 @@ t1 date,
t2 interval year(3) to month,
t3 interval day(3) to second(6)
);
+
+create table doris_test.test_timestamp(
+id int,
+t1 date,
+t2 timestamp(6),
+t3 timestamp(9),
+t4 timestamp,
+t5 interval year(3) to month,
+t6 interval day(3) to second(6)
+);
diff --git a/docker/thirdparties/docker-compose/oracle/init/04-insert.sql b/docker/thirdparties/docker-compose/oracle/init/04-insert.sql
index fd6ea2a57c..0c474e8f20 100644
--- a/docker/thirdparties/docker-compose/oracle/init/04-insert.sql
+++ b/docker/thirdparties/docker-compose/oracle/init/04-insert.sql
@@ -45,4 +45,12 @@ insert into doris_test.test_date (id, t2) values (3, interval '11' year);
insert into doris_test.test_date (id, t2) values (4, interval '223-9' year(3) to month);
insert into doris_test.test_date (id, t3) values (5, interval '12 10:23:01.1234568' day to second);
+insert into doris_test.test_timestamp (id, t1) values (1, to_date('2013-1-21 5:23:01','yyyy-mm-dd hh24:mi:ss'));
+insert into doris_test.test_timestamp (id, t1) values (2, to_date('20131112203256', 'yyyymmddhh24miss'));
+insert into doris_test.test_timestamp (id, t2) values (3, to_timestamp('20191112203357.999997623', 'yyyymmddhh24miss.ff'));
+insert into doris_test.test_timestamp (id, t3) values (4, to_timestamp_tz('20191112203357.999996623', 'yyyymmddhh24miss.ff'));
+insert into doris_test.test_timestamp (id, t4) values (5, to_timestamp_tz('20191112203357.999996623', 'yyyymmddhh24miss.ff'));
+insert into doris_test.test_timestamp (id, t5) values (6, interval '11' year);
+insert into doris_test.test_timestamp (id, t5) values (7, interval '223-9' year(3) to month);
+insert into doris_test.test_timestamp (id, t6) values (8, interval '12 10:23:01.1234568' day to second);
commit;
diff --git a/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcClient.java b/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcClient.java
index 6f420246c5..50476d5a64 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcClient.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcClient.java
@@ -558,6 +558,11 @@ public class JdbcClient {
String oracleType = fieldSchema.getDataTypeName();
if (oracleType.startsWith("INTERVAL")) {
oracleType = oracleType.substring(0, 8);
+ } else if (oracleType.startsWith("TIMESTAMP")) {
+ if (oracleType.equals("TIMESTAMPTZ") || oracleType.equals("TIMESTAMPLTZ")) {
+ return Type.UNSUPPORTED;
+ }
+ return ScalarType.getDefaultDateType(Type.DATETIME);
}
switch (oracleType) {
case "NUMBER":
diff --git a/fe/java-udf/pom.xml b/fe/java-udf/pom.xml
index 46e63ec96b..493015d49c 100644
--- a/fe/java-udf/pom.xml
+++ b/fe/java-udf/pom.xml
@@ -84,6 +84,12 @@ under the License.
<artifactId>httpclient</artifactId>
<version>4.5.13</version>
</dependency>
+ <!-- https://mvnrepository.com/artifact/com.oracle.database.jdbc/ojdbc6 -->
+ <dependency>
+ <groupId>com.oracle.database.jdbc</groupId>
+ <artifactId>ojdbc6</artifactId>
+ <version>11.2.0.4</version>
+ </dependency>
<!-- https://mvnrepository.com/artifact/org.junit.jupiter/junit-jupiter-engine -->
<dependency>
<groupId>org.junit.jupiter</groupId>
diff --git a/fe/java-udf/src/main/java/org/apache/doris/udf/JdbcExecutor.java b/fe/java-udf/src/main/java/org/apache/doris/udf/JdbcExecutor.java
index 7f2366b948..0c9894ad18 100644
--- a/fe/java-udf/src/main/java/org/apache/doris/udf/JdbcExecutor.java
+++ b/fe/java-udf/src/main/java/org/apache/doris/udf/JdbcExecutor.java
@@ -37,7 +37,6 @@ import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
-import java.sql.Timestamp;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.ArrayList;
@@ -217,13 +216,20 @@ public class JdbcExecutor {
0, 0, 0, true);
}
- public long convertDateTimeToLong(Object obj, boolean isDateTimeV2) {
- LocalDateTime date;
+ public long convertDateTimeToLong(Object obj, boolean isDateTimeV2) throws UdfRuntimeException {
+ LocalDateTime date = null;
// TODO: not for sure: https://bugs.mysql.com/bug.php?id=101413
if (obj instanceof LocalDateTime) {
date = (LocalDateTime) obj;
- } else {
- date = ((Timestamp) obj).toLocalDateTime();
+ } else if (obj instanceof java.sql.Timestamp) {
+ date = ((java.sql.Timestamp) obj).toLocalDateTime();
+ } else if (obj instanceof oracle.sql.TIMESTAMP) {
+ try {
+ date = ((oracle.sql.TIMESTAMP) obj).timestampValue().toLocalDateTime();
+ } catch (SQLException e) {
+ throw new UdfRuntimeException("Convert oracle.sql.TIMESTAMP"
+ + " to LocalDateTime failed: ", e);
+ }
}
if (isDateTimeV2) {
return UdfUtils.convertToDateTimeV2(date.getYear(), date.getMonthValue(), date.getDayOfMonth(),
diff --git a/regression-test/data/jdbc_catalog_p0/test_oracle_jdbc_catalog.out b/regression-test/data/jdbc_catalog_p0/test_oracle_jdbc_catalog.out
index 0fe8aa929c..9f45b5584c 100644
--- a/regression-test/data/jdbc_catalog_p0/test_oracle_jdbc_catalog.out
+++ b/regression-test/data/jdbc_catalog_p0/test_oracle_jdbc_catalog.out
@@ -29,5 +29,15 @@
2 2022-11-12T20:32:56 \N \N
3 \N 11-0 \N
4 \N 223-9 \N
-5 \N \N 12 10:23:1.123457000
+5 \N \N 12 10:23:1.123457
+
+-- !test6 --
+1 2013-01-21T05:23:01 \N \N \N \N \N
+2 2013-11-12T20:32:56 \N \N \N \N \N
+3 \N 2019-11-12T20:33:57 \N \N \N \N
+4 \N \N 2019-11-12T20:33:57 \N \N \N
+5 \N \N \N 2019-11-12T20:33:57 \N \N
+6 \N \N \N \N 11-0 \N
+7 \N \N \N \N 223-9 \N
+8 \N \N \N \N \N 12 10:23:1.123457
diff --git a/regression-test/suites/jdbc_catalog_p0/test_oracle_jdbc_catalog.groovy b/regression-test/suites/jdbc_catalog_p0/test_oracle_jdbc_catalog.groovy
index 9d473e7598..51a7b174f7 100644
--- a/regression-test/suites/jdbc_catalog_p0/test_oracle_jdbc_catalog.groovy
+++ b/regression-test/suites/jdbc_catalog_p0/test_oracle_jdbc_catalog.groovy
@@ -62,6 +62,7 @@ suite("test_oracle_jdbc_catalog", "p0") {
order_qt_test2 """ select * from TEST_CHAR order by ID; """
order_qt_test3 """ select * from TEST_INT order by ID; """
order_qt_test5 """ select * from TEST_DATE order by ID; """
+ order_qt_test6 """ select * from TEST_TIMESTAMP order by ID; """
// The result of TEST_RAW will change
// So instead of qt, we're using sql here.
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org