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 527b284e90 [improvement](jdbc catalog) Extend conjunctExprToString to
Support both 'AND' and 'OR' with Optimized DateLiteral Handling (#24537)
527b284e90 is described below
commit 527b284e90280a8b0ef37d6047f3f96c0b2c2ef5
Author: zy-kkk <[email protected]>
AuthorDate: Tue Sep 19 23:11:44 2023 +0800
[improvement](jdbc catalog) Extend conjunctExprToString to Support both
'AND' and 'OR' with Optimized DateLiteral Handling (#24537)
---
.../doris/planner/external/jdbc/JdbcScanNode.java | 70 +++++++++++++---------
.../jdbc/test_oracle_jdbc_catalog.out | 21 +++++++
.../jdbc/test_mysql_jdbc_catalog.groovy | 2 +-
.../jdbc/test_oracle_jdbc_catalog.groovy | 6 ++
4 files changed, 71 insertions(+), 28 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/planner/external/jdbc/JdbcScanNode.java
b/fe/fe-core/src/main/java/org/apache/doris/planner/external/jdbc/JdbcScanNode.java
index ccba1a165f..1fefd749d4 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/planner/external/jdbc/JdbcScanNode.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/planner/external/jdbc/JdbcScanNode.java
@@ -20,6 +20,7 @@ package org.apache.doris.planner.external.jdbc;
import org.apache.doris.analysis.Analyzer;
import org.apache.doris.analysis.BinaryPredicate;
import org.apache.doris.analysis.BoolLiteral;
+import org.apache.doris.analysis.CompoundPredicate;
import org.apache.doris.analysis.DateLiteral;
import org.apache.doris.analysis.Expr;
import org.apache.doris.analysis.ExprSubstitutionMap;
@@ -325,43 +326,58 @@ public class JdbcScanNode extends ExternalScanNode {
}
public static String conjunctExprToString(TOdbcTableType tableType, Expr
expr) {
- if (tableType.equals(TOdbcTableType.ORACLE) &&
expr.contains(DateLiteral.class)
- && (expr instanceof BinaryPredicate)) {
- ArrayList<Expr> children = expr.getChildren();
- // k1 OP '2022-12-10 20:55:59' changTo ---> k1 OP
to_date('{}','yyyy-mm-dd hh24:mi:ss')
- // oracle datetime push down is different:
https://github.com/apache/doris/discussions/15069
- if (children.get(1).isConstant() &&
(children.get(1).getType().equals(Type.DATETIME) || children
- .get(1).getType().equals(Type.DATETIMEV2))) {
- String filter = children.get(0).toMySql();
- filter += ((BinaryPredicate) expr).getOp().toString();
- filter += "to_date('" + children.get(1).getStringValue() +
"','yyyy-mm-dd hh24:mi:ss')";
- return filter;
+ if (expr instanceof CompoundPredicate) {
+ StringBuilder result = new StringBuilder();
+ CompoundPredicate compoundPredicate = (CompoundPredicate) expr;
+ for (Expr child : compoundPredicate.getChildren()) {
+ result.append(conjunctExprToString(tableType, child));
+ result.append("
").append(compoundPredicate.getOp().toString()).append(" ");
}
+ // Remove the last operator
+ result.setLength(result.length() -
compoundPredicate.getOp().toString().length() - 2);
+ return result.toString();
}
- if ((tableType.equals(TOdbcTableType.TRINO) ||
tableType.equals(TOdbcTableType.PRESTO))
- && expr.contains(DateLiteral.class) && (expr instanceof
BinaryPredicate)) {
+
+ if (expr.contains(DateLiteral.class) && expr instanceof
BinaryPredicate) {
ArrayList<Expr> children = expr.getChildren();
- if (children.get(1).isConstant() &&
(children.get(1).getType().isDate()) || children
- .get(1).getType().isDateV2()) {
- String filter = children.get(0).toMySql();
- filter += ((BinaryPredicate) expr).getOp().toString();
- filter += "date '" + children.get(1).getStringValue() + "'";
- return filter;
- }
- if (children.get(1).isConstant() &&
(children.get(1).getType().isDatetime() || children
- .get(1).getType().isDatetimeV2())) {
- String filter = children.get(0).toMySql();
- filter += ((BinaryPredicate) expr).getOp().toString();
- filter += "timestamp '" + children.get(1).getStringValue() +
"'";
- return filter;
+ String filter = children.get(0).toMySql();
+ filter += " " + ((BinaryPredicate) expr).getOp().toString() + " ";
+
+ if (tableType.equals(TOdbcTableType.ORACLE)) {
+ filter += handleOracleDateFormat(children.get(1));
+ } else if (tableType.equals(TOdbcTableType.TRINO) ||
tableType.equals(TOdbcTableType.PRESTO)) {
+ filter += handleTrinoDateFormat(children.get(1));
+ } else {
+ filter += children.get(1).toMySql();
}
+
+ return filter;
}
// only for old planner
- if (expr.contains(BoolLiteral.class) &&
expr.getStringValue().equals("1") && expr.getChildren().isEmpty()) {
+ if (expr.contains(BoolLiteral.class) &&
"1".equals(expr.getStringValue()) && expr.getChildren().isEmpty()) {
return "1 = 1";
}
return expr.toMySql();
}
+
+ private static String handleOracleDateFormat(Expr expr) {
+ if (expr.isConstant()
+ && (expr.getType().equals(Type.DATETIME) ||
expr.getType().equals(Type.DATETIMEV2))) {
+ return "to_date('" + expr.getStringValue() + "', 'yyyy-mm-dd
hh24:mi:ss')";
+ }
+ return expr.toMySql();
+ }
+
+ private static String handleTrinoDateFormat(Expr expr) {
+ if (expr.isConstant()) {
+ if (expr.getType().isDate() || expr.getType().isDateV2()) {
+ return "date '" + expr.getStringValue() + "'";
+ } else if (expr.getType().isDatetime() ||
expr.getType().isDatetimeV2()) {
+ return "timestamp '" + expr.getStringValue() + "'";
+ }
+ }
+ return expr.toMySql();
+ }
}
diff --git
a/regression-test/data/external_table_p0/jdbc/test_oracle_jdbc_catalog.out
b/regression-test/data/external_table_p0/jdbc/test_oracle_jdbc_catalog.out
index 49630c56a0..f239053cba 100644
--- a/regression-test/data/external_table_p0/jdbc/test_oracle_jdbc_catalog.out
+++ b/regression-test/data/external_table_p0/jdbc/test_oracle_jdbc_catalog.out
@@ -78,6 +78,27 @@
-- !filter3 --
1 1 china beijing alice abcdefghrjkmnopq
+-- !date1 --
+1 2022-01-21T05:23:01 \N \N
+2 2022-11-12T20:32:56 \N \N
+
+-- !date2 --
+1 2022-01-21T05:23:01 \N \N
+
+-- !date3 --
+1 2022-01-21T05:23:01 \N \N
+2 2022-11-12T20:32:56 \N \N
+
+-- !date4 --
+1 2022-01-21T05:23:01 \N \N
+
+-- !date5 --
+1 2022-01-21T05:23:01 \N \N
+
+-- !date6 --
+1 2022-01-21T05:23:01 \N \N
+2 2022-11-12T20:32:56 \N \N
+
-- !test_insert1 --
doris1 18
diff --git
a/regression-test/suites/external_table_p0/jdbc/test_mysql_jdbc_catalog.groovy
b/regression-test/suites/external_table_p0/jdbc/test_mysql_jdbc_catalog.groovy
index 3717f17bec..cc72f0e614 100644
---
a/regression-test/suites/external_table_p0/jdbc/test_mysql_jdbc_catalog.groovy
+++
b/regression-test/suites/external_table_p0/jdbc/test_mysql_jdbc_catalog.groovy
@@ -265,7 +265,7 @@ suite("test_mysql_jdbc_catalog",
"p0,external,mysql,external_docker,external_doc
contains "QUERY: SELECT `timestamp0` FROM `doris_test`.`dt`"
}
explain {
- sql ("SELECT timestamp0 from dt where
DATE_TRUNC(date_sub(timestamp0,INTERVAL 9 HOUR),'hour') > '2011-03-03 17:39:05'
and timestamp0 > '2022-01-01';;")
+ sql ("SELECT timestamp0 from dt where
DATE_TRUNC(date_sub(timestamp0,INTERVAL 9 HOUR),'hour') > '2011-03-03 17:39:05'
and timestamp0 > '2022-01-01';")
contains "QUERY: SELECT `timestamp0` FROM `doris_test`.`dt` WHERE
(timestamp0 > '2022-01-01 00:00:00')"
}
diff --git
a/regression-test/suites/external_table_p0/jdbc/test_oracle_jdbc_catalog.groovy
b/regression-test/suites/external_table_p0/jdbc/test_oracle_jdbc_catalog.groovy
index e84622b4e7..5db86c3dd4 100644
---
a/regression-test/suites/external_table_p0/jdbc/test_oracle_jdbc_catalog.groovy
+++
b/regression-test/suites/external_table_p0/jdbc/test_oracle_jdbc_catalog.groovy
@@ -74,6 +74,12 @@ suite("test_oracle_jdbc_catalog",
"p0,external,oracle,external_docker,external_d
order_qt_filter1 """ select * from TEST_CHAR where ID = 1 order by
ID; """
order_qt_filter2 """ select * from TEST_CHAR where 1 = 1 order by ID;
"""
order_qt_filter3 """ select * from TEST_CHAR where ID = 1 and 1 = 1
order by ID; """
+ order_qt_date1 """ select * from TEST_DATE where T1 > '2022-01-21
00:00:00' or T1 < '2022-01-22 00:00:00'; """
+ order_qt_date2 """ select * from TEST_DATE where T1 > '2022-01-21
00:00:00' and T1 < '2022-01-22 00:00:00'; """
+ order_qt_date3 """ select * from TEST_DATE where (T1 > '2022-01-21
00:00:00' and T1 < '2022-01-22 00:00:00') or T1 > '2022-01-20 00:00:00'; """
+ order_qt_date4 """ select * from TEST_DATE where (T1 > '2022-01-21
00:00:00' and T1 < '2022-01-22 00:00:00') or (T1 > '2022-01-20 00:00:00' and T1
< '2022-01-23 00:00:00'); """
+ order_qt_date5 """ select * from TEST_DATE where T1 < '2022-01-22
00:00:00' or T1 = '2022-01-21 05:23:01'; """
+ order_qt_date6 """ select * from TEST_DATE where (T1 < '2022-01-22
00:00:00' or T1 > '2022-01-20 00:00:00') and (T1 < '2022-01-23 00:00:00' or T1
> '2022-01-19 00:00:00'); """
// The result of TEST_RAW will change
// So instead of qt, we're using sql here.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
